DROP TABLE IF EXISTS employees CASCADE;
CREATE TABLE employees (
  fname varchar(255),
  minit varchar(1),
  lname varchar(255),
  sin varchar(9) primary key,
  bdate date,
  address varchar(255),
  gender varchar(1),
  salary integer,
  dno integer
);

DROP TABLE IF EXISTS departments CASCADE;
CREATE TABLE departments (
  dname varchar(255),
  dnumber integer primary key,
  mgrsin varchar(9),
  mgrstartdate date
);

DROP TABLE IF EXISTS projects CASCADE;
CREATE TABLE projects (
  pname varchar(255),
  pnumber integer primary key,
  plocation varchar(255),
  dnum integer
);

DROP TABLE IF EXISTS locations CASCADE;
CREATE TABLE locations (
  dnbr integer,
  dlocation varchar(255)
);

INSERT INTO employees VALUES ('Harris', 'T', 'Chomsky', 123, '1965-12-10', 'Edmonton', 'M', 50000, 12);
INSERT INTO employees VALUES ('Kristian', 'C', 'Bohr' , 456, '1975-10-05', 'Ottawa', 'M', 28000, 11);
INSERT INTO employees VALUES ('Charlotte', 'F', 'Bouchard' , 789, '1985-08-06', 'Montreal', 'F', 40000, 11);
INSERT INTO employees VALUES ('Said', 'J', 'Ahmad', 111, '1970-09-07', 'Toronto', 'M', 30000, 12);
INSERT INTO employees VALUES ('Andrew', 'U', 'Brahe', 222, '1970-04-02', 'Winnipeg', 'M', 20000, 10);
INSERT INTO employees VALUES ('Nadia', 'O', 'Mamary', 333, '1960-01-08', 'Saskatoon', 'F', 35000, 10);
INSERT INTO employees VALUES ('Yuan', 'P', 'Nielsen', 987, '1983-02-27', 'Moncton', 'F', 32000, 11);
INSERT INTO employees VALUES ('Neil', 'A', 'Dion', 654, '1953-02-27', 'Moncton', 'M', 32000, 11);
INSERT INTO employees VALUES ('Karen', 'C', 'Ming', 321, '1963-11-16', 'Victoria', 'F', 26000, 12);
INSERT INTO departments VALUES ('ConsProd', 10, 333, '2004-10-01');
INSERT INTO departments VALUES ('InduProd', 11, 654, '2005-05-01');
INSERT INTO departments VALUES ('Research', 12, 111, '2000-06-15');
INSERT INTO projects VALUES ('Mobile University', 1, 'Ottawa', 10);
INSERT INTO projects VALUES ('Interactive TV', 2, 'Ottawa', 12);
INSERT INTO projects VALUES ('Intelligent Agent', 3, 'Athabasca', 11);
INSERT INTO projects VALUES ('E-Commerce', 4, 'Edmonton', 10);
INSERT INTO projects VALUES ('Mobile Office', 5, 'Athabasca', 11);
INSERT INTO locations VALUES (10, 'Edmonton');
INSERT INTO locations VALUES (10, 'Ottawa');
INSERT INTO locations VALUES (11, 'Athabasca');
INSERT INTO locations VALUES (12, 'Ottawa');
INSERT INTO locations VALUES (12, 'Montreal');

-- ALTER TABLE departments ADD CONSTRAINT employees_fk FOREIGN KEY (mgrsin) REFERENCES employees (sin);
-- ALTER TABLE employees ADD CONSTRAINT departments_fk FOREIGN KEY (dno) REFERENCES departments (dnumber);
-- ALTER TABLE locations ADD CONSTRAINT departments_fk FOREIGN KEY (dnbr) REFERENCES departments(dnumber);
-- ALTER TABLE projects ADD CONSTRAINT departments_fk FOREIGN KEY (dnum) REFERENCES departments (dnumber);

CREATE OR REPLACE FUNCTION update_referenced_department_numbers() RETURNS trigger AS $$
BEGIN
  IF NEW.dnumber <> OLD.dnumber THEN
    UPDATE employees SET dno = NEW.dnumber WHERE dno = OLD.dnumber;
    UPDATE locations SET dnbr = NEW.dnumber WHERE dnbr = OLD.dnumber;
    UPDATE projects SET dnum = NEW.dnumber WHERE dnum = OLD.dnumber;
  END IF;

  RETURN NEW;
END
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_department_number on departments CASCADE;
CREATE TRIGGER update_department_number AFTER UPDATE
  ON departments
  FOR EACH ROW
  WHEN (OLD.dnumber <> NEW.dnumber)
  EXECUTE PROCEDURE update_referenced_department_numbers();

CREATE OR REPLACE FUNCTION update_referenced_sin_numbers() RETURNS trigger AS $$
BEGIN
  IF NEW.sin <> OLD.sin THEN
    UPDATE departments set mgrsin = NEW.sin WHERE mgrsin = OLD.sin;
  END IF;
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_sin ON employees CASCADE;
CREATE TRIGGER update_sin AFTER UPDATE
  ON employees
  FOR EACH ROW
  WHEN (OLD.sin <> NEW.sin)
  EXECUTE PROCEDURE update_referenced_sin_numbers();

UPDATE departments SET dnumber = 14 where dnumber = 12;

SELECT departments.dname, count(distinct(employees.sin))
FROM departments
INNER JOIN employees on employees.dno = departments.dnumber
INNER JOIN projects on projects.dnum = departments.dnumber
WHERE projects.pname LIKE 'Mobile %'
GROUP BY departments.dnumber;

SELECT d.dname, count(distinct(e.sin))
FROM departments d
INNER JOIN employees e on e.dno = d.dnumber
WHERE e.gender = 'F'
AND e.salary > 27000
GROUP BY d.dnumber;

CREATE OR REPLACE VIEW dept_summary (department_id, employees_count, total_salary, average_salary) AS
  SELECT e.dno, count(e.sin), sum(e.salary), round(avg(e.salary), 2)
  FROM employees e
  INNER JOIN departments d on d.dnumber = e.dno
  INNER JOIN projects p on p.dnum = d.dnumber
  WHERE e.dno = d.dnumber
  AND p.pname LIKE 'Mobile%'
  GROUP BY e.dno;

SELECT * FROM dept_summary;