init_schema.sql

CREATE TABLE groups (
  id    serial primary key,
  name  varchar(255),
  created_at  TIMESTAMP NOT NULL DEFAULT current_timestamp,
  updated_at  TIMESTAMP NOT NULL DEFAULT current_timestamp
);

CREATE TABLE users (
  id    serial primary key,
  name  varchar(255),
  email varchar(255) NOT NULL,
  created_at  TIMESTAMP NOT NULL DEFAULT current_timestamp,
  updated_at  TIMESTAMP NOT NULL DEFAULT current_timestamp
);

CREATE TABLE group_users (
  id SERIAL PRIMARY KEY,
  group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMP NOT NULL DEFAULT current_timestamp,
  UNIQUE(group_id, user_id)
);

CREATE TABLE group_subgroups (
  id SERIAL PRIMARY KEY,
  parent_group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
  child_group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
  created_at TIMESTAMP NOT NULL DEFAULT current_timestamp,
  UNIQUE(parent_group_id, child_group_id),
  CHECK (parent_group_id != child_group_id)
);

CREATE INDEX idx_group_users_user_id ON group_users(user_id);
CREATE INDEX idx_group_subgroups_child ON group_subgroups(child_group_id);

INSERT INTO groups(name) VALUES ('Test Group');
INSERT INTO groups(name) VALUES ('Europe');
INSERT INTO groups(name) VALUES ('Denmark');
INSERT INTO groups(name) VALUES ('Germany');
INSERT INTO groups(name) VALUES ('Sweden');
INSERT INTO groups(name) VALUES ('Product');
INSERT INTO groups(name) VALUES ('Finance');
INSERT INTO groups(name) VALUES ('Sales');

INSERT INTO users(name, email) VALUES ('Testy McTestTest', 'test@test.com');
INSERT INTO users(name, email) VALUES ('Phil', 'phil@test.com');
INSERT INTO users(name, email) VALUES ('Lukas', 'lukas@test.com');
INSERT INTO users(name, email) VALUES ('Anders', 'anders@test.com');
INSERT INTO users(name, email) VALUES ('Chris', 'chris@test.com');

INSERT INTO group_subgroups(parent_group_id, child_group_id) VALUES (2, 3);
INSERT INTO group_subgroups(parent_group_id, child_group_id) VALUES (2, 4);
INSERT INTO group_subgroups(parent_group_id, child_group_id) VALUES (2, 5);
INSERT INTO group_subgroups(parent_group_id, child_group_id) VALUES (3, 6);
INSERT INTO group_subgroups(parent_group_id, child_group_id) VALUES (3, 7);
INSERT INTO group_subgroups(parent_group_id, child_group_id) VALUES (4, 8);

INSERT INTO group_users(group_id, user_id) VALUES (3, 1);
INSERT INTO group_users(group_id, user_id) VALUES (3, 2);
INSERT INTO group_users(group_id, user_id) VALUES (4, 3);
INSERT INTO group_users(group_id, user_id) VALUES (4, 4);