rollback;

begin;

CREATE TABLE emp
(
  id bigint NOT NULL,
  type bigint NOT NULL,
  CONSTRAINT pk_emp PRIMARY KEY (id)
);

CREATE TABLE contacts
(
  id bigserial NOT NULL,
  first_emp_id bigint NOT NULL,
  contact_type bigint NOT NULL,
  sec_emp_id bigint NOT NULL,
  CONSTRAINT pk_contacts PRIMARY KEY (id),
  CONSTRAINT fk_contacts2first_emp FOREIGN KEY (first_emp_id)
      REFERENCES emp (id),
  CONSTRAINT fk_contacts2sec_emp FOREIGN KEY (sec_emp_id)
      REFERENCES emp (id),
  CONSTRAINT uk_contacts UNIQUE (first_emp_id, contact_type, sec_emp_id)
);
-- create index contacts_first_emp_id on contacts(first_emp_id);
-- create index contacts_sec_emp_id on contacts(sec_emp_id);

CREATE TABLE tree
(
  id bigserial NOT NULL,
  root_id bigint NOT NULL,
  emp_id bigint NOT NULL,
  contact_id bigint NOT NULL,
  CONSTRAINT pk_tree PRIMARY KEY (id),
  CONSTRAINT fk_tree2root FOREIGN KEY (root_id)
      REFERENCES emp (id),
  CONSTRAINT fk_tree2contacts FOREIGN KEY (contact_id)
      REFERENCES contacts (id),
  CONSTRAINT fk_tree2emp FOREIGN KEY (emp_id)
      REFERENCES emp (id)
);
-- create index tree_root_id on tree(root_id);
-- create index tree_contact_id on tree(contact_id);
-- create index tree_emp_id on tree(emp_id);

insert into emp select gs, gs/1000 from generate_series(1,10000) as gs(gs);
insert into contacts (first_emp_id, contact_type, sec_emp_id) select id, type, id from emp;
insert into tree (root_id, emp_id, contact_id) select first_emp_id, sec_emp_id, id from contacts;

delete from tree;
delete from contacts;
delete from emp;