DROP SCHEMA IF EXISTS SHEET5;
CREATE SCHEMA IF NOT EXISTS SHEET5;

USE SHEET5;

CREATE TABLE IF NOT EXISTS DEPT (
    Dnumber VARCHAR(10),
    Dname VARCHAR(100),
    Founded DATE,
    Mgr_ssn VARCHAR(10),
    Budget INT,
    CONSTRAINT DEPT_PK PRIMARY KEY(Dnumber)
);

CREATE TABLE IF NOT EXISTS EMPLOYEE (
    Ssn VARCHAR(10),
    Ename VARCHAR(100),
    Bdate DATE,
    Dno VARCHAR(10),
    Salary INT,
    CONSTRAINT EMP_PK PRIMARY KEY(Ssn),
    CONSTRAINT EMP_DEPT_FK FOREIGN KEY(Dno) REFERENCES DEPT(Dnumber)
);

ALTER TABLE DEPT ADD CONSTRAINT DEPT_MGR_FK FOREIGN KEY(Mgr_ssn) REFERENCES EMPLOYEE(Ssn);

## Definitions
DELIMITER //
## REQ 1
CREATE FUNCTION Count_Emp(DNUMBER VARCHAR(10)) RETURNS INT DETERMINISTIC
BEGIN
    DECLARE cnt int;
    SET cnt = 0;
    SELECT COUNT(*) INTO cnt FROM EMPLOYEE GROUP BY Dno HAVING Dno = DNUMBER;
    RETURN cnt;
END//
## REQ 2
CREATE PROCEDURE Year()
BEGIN
    SET SQL_SAFE_UPDATES = 0;

    UPDATE DEPT AS UDEPT
    SET UDEPT.Founded = '1960-1-1'
    WHERE UDEPT.Dnumber IN (
        SELECT *
        FROM (
            SELECT Dnumber
            FROM DEPT
            WHERE FOUNDED < '1960-1-1') AS D);

    SET SQL_SAFE_UPDATES = 1;
END//
## REQ 3
CREATE TRIGGER MAX_EMP BEFORE INSERT ON EMPLOYEE
FOR EACH ROW
BEGIN
    IF (NEW.Dno IN (SELECT Dno FROM EMPLOYEE GROUP BY Dno HAVING COUNT(*) >= 8))
    THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error: A Department Can\'t Have More Than 8 Employees';
    END IF;
END//
## REQ 4
CREATE TRIGGER UPDATE_DNO_CASCADE AFTER UPDATE ON DEPT
FOR EACH ROW
BEGIN
    SET SQL_SAFE_UPDATES = 0;

    IF (NEW.Dnumber != OLD.Dnumber)
    THEN
        UPDATE EMPLOYEE
        SET Dno = NEW.Dnumber
        WHERE Dno = OLD.Dnumber;
    END IF;

    SET SQL_SAFE_UPDATES = 1;
END//
## REQ 5
CREATE TRIGGER UPDATE_MGR_SALARY BEFORE UPDATE ON EMPLOYEE
FOR EACH ROW
BEGIN
    SET SQL_SAFE_UPDATES = 0;

        UPDATE EMPLOYEE
        SET Salary = NEW.Salary
        WHERE Ssn IN (
            SELECT Ssn
            FROM DEPT
            JOIN EMPLOYEE ON Mgr_ssn = Ssn
            WHERE Dnumber = NEW.Dno
            AND Salary < NEW.Salary);

    SET SQL_SAFE_UPDATES = 1;
END//
DELIMITER ;


INSERT INTO DEPT (Dnumber, Founded) VALUES (1, '1959-1-1'), (2, '1960-1-2');
INSERT INTO EMPLOYEE(Ssn, Dno, Salary) VALUES(1, 1, 1), (2, 1, 5000), (3, 2, 1000);
INSERT INTO DEPT (Dnumber, Mgr_Ssn) VALUES (3, 1);
INSERT INTO EMPLOYEE(Ssn, Dno, Salary) VALUES (4, 3, 2);
## TEST REQ 3
#INSERT INTO EMPLOYEE(Ssn, Dno) VALUES(1, 1), (2, 1), (3, 2), (4, 1), (5, 1), (6, 1), (7, 1), (8, 1), (9, 1), (10, 1);

SELECT * FROM DEPT;

## TEST REQ 1
SELECT Count_Emp(1);
SELECT Count_Emp(2);

## TEST REQ 2
CALL Year();
SELECT * FROM DEPT;

## TEST REQ 4 (THIS WON'T WORK);
-- SET SQL_SAFE_UPDATES = 0;
-- UPDATE DEPT SET Dnumber = 4 WHERE Dnumber = 2;
-- SET SQL_SAFE_UPDATES = 1;
-- SELECT * FROM EMPLOYEE;

## TEST REQ 5 (THIS WON'T WORK);
-- SET SQL_SAFE_UPDATES = 0;
-- UPDATE EMPLOYEE SET Salary = 10000 WHERE Ssn = 4;
-- SET SQL_SAFE_UPDATES = 1;
-- SELECT * FROM EMPLOYEE;