-- credits: André Restivo https://web.fe.up.pt/~arestivo
-- Revised by Gonçalo Gonçalves

PRAGMA foreign_keys = ON;
.headers on
.mode columns

PRAGMA foreign_keys = OFF; -- special case: FK loop between department and employee
PRAGMA foreign_keys = ON;

CREATE TABLE Department (
  id INTEGER PRIMARY KEY,            -- department id
  name TEXT NOT NULL UNIQUE,         -- department name
  id_dir INTEGER REFERENCES Employee -- department director employee id 
);

CREATE TABLE Employee (
  id INTEGER PRIMARY KEY,                        -- employee id
  name TEXT NOT NULL,                            -- employee name
  salary REAL,                                   -- employee salary
  id_dep INTEGER REFERENCES Department NOT NULL, -- the department where he works
  id_sup INTEGER REFERENCES Employee             -- his superior
);

CREATE TABLE Project (
  id INTEGER PRIMARY KEY,              -- project id
  name TEXT NOT NULL UNIQUE,           -- project name
  id_dep INTEGER REFERENCES Department -- the department that controls the project
);

CREATE TABLE Works (
  id_emp INTEGER REFERENCES Employee, -- this employee
  id_pro INTEGER REFERENCES Project,  -- works in this project
  hours INTEGER NOT NULL,             -- hours per week
  PRIMARY KEY (id_emp, id_pro)
);

INSERT INTO Department (id, name, id_dir) VALUES (1, 'Logistics', NULL);
INSERT INTO Department (id, name, id_dir) VALUES (2, 'Transports', NULL);
INSERT INTO Department (id, name, id_dir) VALUES (3, 'Cleaning', NULL);
INSERT INTO Department (id, name, id_dir) VALUES (4, 'Human Resources', NULL);
INSERT INTO Department (id, name, id_dir) VALUES (5, 'Informatics', NULL);
 
INSERT INTO Employee (id, name, salary, id_dep, id_sup) VALUES (1, 'John'     , 1500, 1, NULL);
INSERT INTO Employee (id, name, salary, id_dep, id_sup) VALUES (2, 'Mike'     , 1200, 1, 1);
INSERT INTO Employee (id, name, salary, id_dep, id_sup) VALUES (3, 'John'     , 1300, 1, 1);
INSERT INTO Employee (id, name, salary, id_dep, id_sup) VALUES (4, 'Theresa'  , 1500, 2, NULL);
INSERT INTO Employee (id, name, salary, id_dep, id_sup) VALUES (5, 'Charles'  , 1100, 2, 4);
INSERT INTO Employee (id, name, salary, id_dep, id_sup) VALUES (6, 'Mary'     , 1000, 2, 4);
INSERT INTO Employee (id, name, salary, id_dep, id_sup) VALUES (7, 'Beatrice' ,  800, 2, 4);
INSERT INTO Employee (id, name, salary, id_dep, id_sup) VALUES (8, 'Brett'    , 1700, 3, NULL);
INSERT INTO Employee (id, name, salary, id_dep, id_sup) VALUES (9, 'Alfred'   , 1200, 3, 8);
INSERT INTO Employee (id, name, salary, id_dep, id_sup) VALUES (10, 'Barry'   , 1300, 3, 8);
INSERT INTO Employee (id, name, salary, id_dep, id_sup) VALUES (11, 'Carrie'  , 1100, 3, 10);
INSERT INTO Employee (id, name, salary, id_dep, id_sup) VALUES (12, 'Thomas'  , 1900, 3, 10);
INSERT INTO Employee (id, name, salary, id_dep, id_sup) VALUES (13, 'Emily'   , 1200, 4, NULL);
INSERT INTO Employee (id, name, salary, id_dep, id_sup) VALUES (14, 'Megan'   , 1600, 4, 13);
INSERT INTO Employee (id, name, salary, id_dep, id_sup) VALUES (15, 'Sarah'   , 1200, 5, NULL);
INSERT INTO Employee (id, name, salary, id_dep, id_sup) VALUES (16, 'William' , 1400, 5, 15);
INSERT INTO Employee (id, name, salary, id_dep, id_sup) VALUES (17, 'James'   , 1100, 5, 15);
INSERT INTO Employee (id, name, salary, id_dep, id_sup) VALUES (18, 'Harry'   , 1000, 5, 16);
INSERT INTO Employee (id, name, salary, id_dep, id_sup) VALUES (19, 'Matthew' ,  500, 5, 16);
INSERT INTO Employee (id, name, salary, id_dep, id_sup) VALUES (20, 'Kate'    ,  500, 5, 17);
 
UPDATE Department SET id_dir = 1 WHERE id = 1;
UPDATE Department SET id_dir = 4 WHERE id = 2;
UPDATE Department SET id_dir = 8 WHERE id = 3;
UPDATE Department SET id_dir = 13 WHERE id = 4;
UPDATE Department SET id_dir = 15 WHERE id = 5;
 
INSERT INTO Project (id, name, id_dep) VALUES (1, 'XPTO', 1);
INSERT INTO Project (id, name, id_dep) VALUES (2, 'YPTO', 2);
INSERT INTO Project (id, name, id_dep) VALUES (3, 'ZPTO', 5);
 
INSERT INTO Works (id_emp, id_pro, hours) VALUES (1,  1, 4);
INSERT INTO Works (id_emp, id_pro, hours) VALUES (2,  1, 3);
INSERT INTO Works (id_emp, id_pro, hours) VALUES (5,  1, 4);
INSERT INTO Works (id_emp, id_pro, hours) VALUES (7,  1, 2);
INSERT INTO Works (id_emp, id_pro, hours) VALUES (10, 1, 1);
INSERT INTO Works (id_emp, id_pro, hours) VALUES (11, 1, 7);
INSERT INTO Works (id_emp, id_pro, hours) VALUES (13, 1, 3);
INSERT INTO Works (id_emp, id_pro, hours) VALUES (14, 1, 3);
INSERT INTO Works (id_emp, id_pro, hours) VALUES (20, 1, 6);
 
INSERT INTO Works (id_emp, id_pro, hours) VALUES (1,  2, 8);
INSERT INTO Works (id_emp, id_pro, hours) VALUES (2,  2, 7);
INSERT INTO Works (id_emp, id_pro, hours) VALUES (3,  2, 7);
INSERT INTO Works (id_emp, id_pro, hours) VALUES (4,  2, 2);
INSERT INTO Works (id_emp, id_pro, hours) VALUES (6,  2, 3);
INSERT INTO Works (id_emp, id_pro, hours) VALUES (11, 2, 2);
INSERT INTO Works (id_emp, id_pro, hours) VALUES (12, 2, 1);
INSERT INTO Works (id_emp, id_pro, hours) VALUES (14, 2, 3);
INSERT INTO Works (id_emp, id_pro, hours) VALUES (15, 2, 5);
INSERT INTO Works (id_emp, id_pro, hours) VALUES (17, 2, 4);
 
INSERT INTO Works (id_emp, id_pro, hours) VALUES (5,  3, 6);
INSERT INTO Works (id_emp, id_pro, hours) VALUES (12, 3, 3);
INSERT INTO Works (id_emp, id_pro, hours) VALUES (17, 3, 1);
INSERT INTO Works (id_emp, id_pro, hours) VALUES (18, 3, 2);
