DROP SCHEMA IF EXISTS TRAVAIL;
CREATE SCHEMA TRAVAIL;
USE TRAVAIL;

-- SELECT DATEDIFF(CURDATE(), '1986-01-17') / 365;


CREATE TABLE EMPLOYE (
    id int,
    nomEmp text,
    dateN date,
    salaire int,
    deptN int,
    dateEmbauche date,
    PRIMARY KEY (id)
);

CREATE TABLE DEPARTEMENT (
    idDept int,
    nomDept text,
    mgrDept int,
    nbrEmployes int,
    PRIMARY KEY (idDept)
);


CREATE TABLE PROJET (
    idProj int,
    nomProj text,
    deptN int,
    PRIMARY KEY (idProj),
    FOREIGN KEY (deptN)
        REFERENCES DEPARTEMENT (idDept)
);

CREATE TABLE TRAVAILLE (
    idEmp int,
    idProj int,
    heures int,
    PRIMARY KEY (idEmp , idProj),
    FOREIGN KEY (idProj)
        REFERENCES PROJET (idProj)
);

set autocommit = off;
INSERT INTO EMPLOYE VALUES (1,'Jeanne','1965-12-12', 1000, 5,'2012-12-12');

INSERT INTO DEPARTEMENT VALUES (5, 'Informatique', 1, 1);

COMMIT;


INSERT INTO EMPLOYE VALUES (2, 'Erica', '1985-12-12', 1000, 5, '2012-12-12');

ROLLBACK;


SELECT 
    *
FROM
    EMPLOYE;


SELECT DATEDIFF(CURDATE(),'1986-01-17') / 365 ;


DROP TRIGGER IF EXISTS INSERT_EMPLOYE;

DELIMITER $$
CREATE TRIGGER INSERT_EMPLOYE
BEFORE INSERT ON EMPLOYE
FOR EACH ROW BEGIN
-- Un employe est majeur 
IF ((DATEDIFF(CURDATE(), NEW.dateN)) < 21*365) THEN
SIGNAL SQLSTATE '45000' SET message_text = 'Employe non majeur';
ELSE IF 
-- Un manager est plus age que ses employes
 (SELECT DATEDIFF(CURDATE(), NEW.dateN)) > (SELECT 
            (DATEDIFF(CURDATE(), dateN))
        FROM
            EMPLOYE
        WHERE
            id = (SELECT 
                    mgrDept
                FROM
                    DEPARTEMENT
                WHERE
                    idDept = NEW.deptN))
THEN
SIGNAL SQLSTATE '45000' SET message_text = 'Employe plus age que son manager';
-- Le salaire d’un employe est inferieur au salaire de son manager.
ELSE IF NEW.salaire >
(SELECT salaire FROM EMPLOYE WHERE id = (SELECT 
                    mgrDept
                FROM
                    DEPARTEMENT
                WHERE
                    idDept = NEW.deptN))
THEN
SIGNAL SQLSTATE '45000' SET message_text = 'Le salaire de l''employe est supérieur à celui de son manager';
-- La date d’embauche est anterieure a la date de naissance.
ELSE
IF DATEDIFF(NEW.dateN,NEW.dateEmbauche) > 0 THEN
SIGNAL SQLSTATE '45000' SET message_text = 'La date de naissance doit etre anterieure a la date d''embauche';
END IF;
END IF;
END IF;
END IF;
-- 9. Le nombre d’employ ́es nbrEmployes d’un d ́epartement est d ́eduit 
-- `a partir de la table Employe.
UPDATE DEPARTEMENT SET nbrEmployes = nbrEmployes + 1 WHERE idDept = NEW.deptN;
END;
$$
DELIMITER ;


DROP TRIGGER IF EXISTS UPDATE_EMPLOYE;

DELIMITER $$
CREATE TRIGGER UPDATE_EMPLOYE
BEFORE UPDATE ON EMPLOYE
FOR EACH ROW BEGIN
-- Un employe est majeur 
IF ((DATEDIFF(CURDATE(), NEW.dateN)) < 21*365) THEN
SIGNAL SQLSTATE '45000' SET message_text = 'Employe non majeur';
ELSE IF 
-- Un manager est plus age que ses employes
 (SELECT DATEDIFF(CURDATE(), NEW.dateN)) > (SELECT 
            (DATEDIFF(CURDATE(), dateN))
        FROM
            EMPLOYE
        WHERE
            id = (SELECT 
                    mgrDept
                FROM
                    DEPARTEMENT
                WHERE
                    idDept = NEW.deptN))
THEN
SIGNAL SQLSTATE '45000' SET message_text = 'Employe plus age que son manager';
-- Le salaire d’un employe est inferieur au salaire de son manager.
ELSE IF NEW.salaire >
(SELECT salaire FROM EMPLOYE WHERE id = (SELECT 
                    mgrDept
                FROM
                    DEPARTEMENT
                WHERE
                    idDept = NEW.deptN))
THEN
SIGNAL SQLSTATE '45000' SET message_text = 'Le salaire de l''employe est supérieur à celui de son manager';
-- La date d’embauche est anterieure a la date de naissance.
ELSE
IF DATEDIFF(NEW.dateN,NEW.dateEmbauche) > 0 THEN
SIGNAL SQLSTATE '45000' SET message_text = 'La date de naissance doit etre anterieure a la date d''embauche';
END IF;
END IF;
END IF;
END IF;
-- 9. Le nombre d’employ ́es nbrEmployes d’un d ́epartement est d ́eduit 
-- `a partir de la table Employe.
UPDATE DEPARTEMENT SET nbrEmployes = nbrEmployes + 1 WHERE idDept = NEW.deptN;
END;
$$
DELIMITER ;


-- 9. Le nombre d’employ ́es nbrEmployes d’un d ́epartement est d ́eduit 
-- `a partir de la table Employe.
DROP TRIGGER IF EXISTS DELETE_EMPLOYE;

DELIMITER $$
CREATE TRIGGER DELETE_EMPLOYE
BEFORE DELETE ON EMPLOYE
FOR EACH ROW
UPDATE DEPARTEMENT SET nbrEmployes = nbrEmployes - 1 WHERE idDept = OLD.deptN;
$$
DELIMITER ;

-- 5 Un manager est plus age que ses employes
INSERT INTO EMPLOYE VALUES (3, 'Bonnie', '1955-01-17', 1000, 5, '2012-12-12');
-- 4 Un employe est majeur 
INSERT INTO EMPLOYE VALUES (4, 'Yasmina', '1994-01-17', 1000, 5, '2012-12-12');
-- 6 Le salaire d’un employe est inferieur au salaire de son manager.
INSERT INTO EMPLOYE VALUES (4, 'Yasmina', '1966-01-17', 2000, 5, '2012-12-12');

SELECT * FROM DEPARTEMENT;
DROP TRIGGER IF EXISTS INSERT_DEPARTEMENT;

DELIMITER $$
CREATE TRIGGER INSERT_DEPARTEMENT
BEFORE INSERT ON DEPARTEMENT
FOR EACH ROW BEGIN
-- 7. Le manager d’un department doit etre un employe de ce department.
IF (SELECT deptN FROM EMPLOYE WHERE id = NEW.mgrDept) <> NEW.idDept THEN
SIGNAL SQLSTATE '45000' SET message_text = 'Le manager d’un department doit etre un employe de ce department';
END IF;
-- 9. Le nombre d’employ ́es nbrEmployes d’un d ́epartement est d ́eduit 
-- `a partir de la table Employe.
IF ((SELECT COUNT(id) FROM EMPLOYE WHERE deptN = NEW.idDept) != NEW.nbrEmployes)
THEN 
SIGNAL SQLSTATE '45000' SET message_text = 'Le nombre d''employes n''est pas correct.';
END IF;
END;
$$
DELIMITER ;

-- 7. Le manager d’un department doit etre un employe de ce department.
INSERT INTO DEPARTEMENT VALUES (1,'Info',1,0);

DROP TRIGGER IF EXISTS UPDATE_DEPARTEMENT;

DELIMITER $$
CREATE TRIGGER UPDATE_DEPARTEMENT
BEFORE UPDATE ON DEPARTEMENT
FOR EACH ROW BEGIN
-- 7. Le manager d’un department doit etre un employe de ce department.
IF (SELECT deptN FROM EMPLOYE WHERE id = NEW.mgrDept) <> NEW.idDept THEN
SIGNAL SQLSTATE '45000' SET message_text = 'Le manager d’un department doit etre un employe de ce department';
END IF;
-- 9. Le nombre d’employ ́es nbrEmployes d’un d ́epartement est d ́eduit 
-- `a partir de la table Employe.
IF ((SELECT COUNT(id) FROM EMPLOYE WHERE deptN = NEW.idDept) != NEW.nbrEmployes)
THEN 
SIGNAL SQLSTATE '45000' SET message_text = 'Le nombre d''employes n''est pas correct.';
END IF;
END;
$$
DELIMITER ;

SELECT * FROM DEPARTEMENT;
UPDATE DEPARTEMENT SET nbrEmployes = 2 WHERE idDept = 5;

INSERT INTO DEPARTEMENT VALUES (6, 'Psychologie', 1, 10);

INSERT INTO EMPLOYE VALUES (4, 'Yasmina', '1986-01-17', 1000, 5, '2012-01-17');
DELETE FROM EMPLOYE WHERE id = 4;

INSERT INTO DEPARTEMENT VALUES (5, 'Informatique', 1, 2);

-- 9. Le nombre d’employ ́es nbrEmployes d’un d ́epartement est d ́eduit 
-- `a partir de la table Employe.
-- a chaque insertion d'employe, mettre à jour le count :-)

INSERT INTO EMPLOYE VALUES (5, 'Raphael', '1986-01-17', 1000, 5, '2011-01-17');
DELETE FROM EMPLOYE WHERE id = 5;
SELECT 
    *
FROM
    DEPARTEMENT;

INSERT INTO DEPARTEMENT VALUES (6, 'Informatique', NULL, 0);
INSERT INTO EMPLOYE VALUES (6, 'John', '1986-01-17', 1000, 6, '2012-01-17');

DELETE FROM DEPARTEMENT 
WHERE
    idDept = 6;

-- 10. Un employe peut travailler au plus sur 4 projets

SELECT 
    *
FROM
    PROJET;
INSERT INTO PROJET VALUES (1, 'Projet1', 5);
INSERT INTO PROJET VALUES (2, 'Projet2', 5);
INSERT INTO PROJET VALUES (3, 'Projet3', 5);
INSERT INTO PROJET VALUES (4, 'Projet4', 5);
INSERT INTO PROJET VALUES (5, 'Projet5', 5);


INSERT INTO TRAVAILLE VALUES (1,1,5);

DROP TRIGGER IF EXISTS INSERT_TRAVAILLE;
DELIMITER $$
CREATE TRIGGER INSERT_TRAVAILLE
BEFORE INSERT ON TRAVAILLE
FOR EACH ROW BEGIN
-- 10. Un employe peut travailler au plus sur 4 projets
IF (SELECT count(idProj) FROM TRAVAILLE WHERE idEmp = NEW.idEmp) >= 4 THEN
SIGNAL SQLSTATE '45000' SET message_text = 'L''employe travaille deja sur 4 projets.';
END IF;
-- 11. L’employ ́e d’un d ́epartement d1 ne peut pas travailler sur un projet du d ́epartement d2 si d1 ̸= d2.
IF (SELECT deptN FROM PROJET WHERE idProj = NEW.idProj)
	<> (SELECT deptN FROM EMPLOYE WHERE id = NEW.idEmp)
THEN SIGNAL SQLSTATE '45000' SET message_text 
	= 'L’employe d’un departement ne peut pas travailler sur un projet d''un autre departement';
END IF;
-- 12. Un employ ́e travaille au moins 20 heures par semaine et au plus 40 heures par semaine sur tous les projets.
IF ((SELECT SUM(heures) FROM TRAVAILLE WHERE idEmp = NEW.idEmp) + NEW.heures) < 20
THEN SIGNAL SQLSTATE '45000' SET message_text 
	= 'Un employe travaille au moins 20 heures par semaine';
END IF;
IF ((SELECT SUM(heures) FROM TRAVAILLE WHERE idEmp = NEW.idEmp) + NEW.heures) > 40
THEN SIGNAL SQLSTATE '45000' SET message_text 
	= 'Un employ ́e travaille au plus 40 heures par semaine';
END IF;
END;
$$
DELIMITER ;

DROP TRIGGER IF EXISTS UPDATE_TRAVAILLE;
DELIMITER $$
CREATE TRIGGER UPDATE_TRAVAILLE
BEFORE UPDATE ON TRAVAILLE
FOR EACH ROW BEGIN
-- 10. Un employe peut travailler au plus sur 4 projets
IF (SELECT count(idProj) FROM TRAVAILLE WHERE idEmp = NEW.idEmp) >= 4 THEN
SIGNAL SQLSTATE '45000' SET message_text = 'L''employe travaille deja sur 4 projets.';
END IF;
-- 11. L’employ ́e d’un d ́epartement d1 ne peut pas travailler sur un projet du d ́epartement d2 si d1 ̸= d2.
IF (SELECT deptN FROM PROJET WHERE idProj = NEW.idProj)
	<> (SELECT deptN FROM EMPLOYE WHERE id = NEW.idEmp)
THEN SIGNAL SQLSTATE '45000' SET message_text 
	= 'L’employe d’un departement ne peut pas travailler sur un projet d''un autre departement';
END IF;
-- 12. Un employ ́e travaille au moins 20 heures par semaine et au plus 40 heures par semaine sur tous les projets.
IF ((SELECT SUM(heures) FROM TRAVAILLE WHERE idEmp = NEW.idEmp) + NEW.heures) < 20
THEN SIGNAL SQLSTATE '45000' SET message_text 
	= 'Un employe travaille au moins 20 heures par semaine';
END IF;
IF ((SELECT SUM(heures) FROM TRAVAILLE WHERE idEmp = NEW.idEmp) + NEW.heures) > 40
THEN SIGNAL SQLSTATE '45000' SET message_text 
	= 'Un employ ́e travaille au plus 40 heures par semaine';
END IF;
END;
$$
DELIMITER ;


INSERT INTO TRAVAILLE VALUES (1,2,20);
INSERT INTO TRAVAILLE VALUES (1,3,1);
INSERT INTO TRAVAILLE VALUES (1,4,1);
INSERT INTO TRAVAILLE VALUES (1,1,1);



SELECT 
    *
FROM
    TRAVAILLE;

-- 11. L’employ ́e d’un d ́epartement d1 ne peut pas travailler sur un projet du d ́epartement d2 si d1 ̸= d2.

INSERT INTO TRAVAILLE VALUES (6,5,10);

-- 12. Un employ ́e travaille au moins 20 heures par semaine et au plus 40 heures par semaine sur tous les projets.

INSERT INTO TRAVAILLE VALUES (1,4,11);

-- 13. Le manager d’un d ́epartement travaille sur tous les projets de ce d ́epartement au moins 2 heures.

DROP TRIGGER IF EXISTS INSERT_PROJET;
DELIMITER $$
CREATE TRIGGER INSERT_PROJET
AFTER INSERT ON PROJET
FOR EACH ROW BEGIN
IF NOT EXISTS (SELECT idEmp FROM TRAVAILLE WHERE idEmp = (SELECT mgrDept FROM DEPARTEMENT WHERE idDept = NEW.deptN)
and idProj = NEW.idProj)
THEN
INSERT INTO TRAVAILLE VALUES 
((SELECT mgrDept FROM DEPARTEMENT WHERE idDept = NEW.deptN), NEW.idProj, 2);
ELSE 
IF (SELECT heures FROM TRAVAILLE WHERE idProj = NEW.idProj 
AND idEmp = (SELECT mgrDept FROM DEPARTEMENT WHERE idDept = NEW.deptN)) < 2
THEN
UPDATE TRAVAILLE SET heures = 2 
WHERE idProj = NEW.idProj AND idEmp = (SELECT mgrDept FROM DEPARTEMENT WHERE idDept = NEW.deptN);
END IF;
END IF;
END;
$$
DELIMITER ;

DELETE FROM TRAVAILLE WHERE idProj = 4;
INSERT INTO TRAVAILLE VALUES (1,4,20);
INSERT INTO PROJET VALUES (6, 'Projet6', 5);
SELECT 
    *
FROM
    TRAVAILLE;

-- ALTER TABLE EMPLOYE ADD FOREIGN KEY (deptN) REFERENCES DEPARTEMENT (idDept); 
-- ALTER TABLE DEPARTEMENT ADD FOREIGN KEY (mgrDept) REFERENCES EMPLOYE (id); 



