-- Fichier texte encodé en UTF8 with BOM

-- ------------------------------------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------------------------------
-- MLD : 
-- employes(id, nom, fonction, date_embauche, salaire, commission, id_departement, id_employe_chef(null))
-- departements (id, nom, ville)
-- tache(id, nom_tache, duree_theorique, #id_projet, #id_employe(null))
-- projet(id, nom_projet, date_debut, date_fin)

-- ------------------------------------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------------------------------
-- Graphe des tables :    

--                 taches
--                 /    \ 
--                /      \ 
--             projets    \ 
--                         0          
--                  r--0> employes       le 0 veut dire que ça peut être null
--             chef |     /   \           
--                  |____/     \        
--                            departements 


-- on a un lien circulaire : on peut multiplier les tables employés   
--                         
--                      employes 
--                      /     \           
--                     /       \        
--        employes-chef        departements 
--             /     \
--            /       \
--  employes-chef     departements-chef


--  les employes qui ne travaille pas dans la même ville que leur chef
--
--                      employes 
--                      /      \           
--                     /        \        
--              employes-chef  departements 
--                   /            \
--                  /            ville
--          departements-chef
--                /
--            ville-chef


-- ------------------------------------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------------------------------
-- DDL : Création de la BD  : on supprime la database, on la recrée, on l'utilise

-- DDL : create BD
DROP DATABASE if exists BD_employes_departements;
CREATE DATABASE BD_employes_departements;
USE BD_employes_departements;


-- ------------------------------------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------------------------------
-- DDL : creation des tables

CREATE TABLE employes(
   id integer auto_increment, 
   nom_employe varchar(30) not null,
   fonction varchar(30) not null, 
   date_embauche date not null, -- type date
   salaire integer not null,
   commission integer,
   id_departement integer not null, -- clé étrangère pas obligatoire
   id_employe_chef integer, -- clé étrangère réflexive
   primary key(id)
) engine innodb;

CREATE TABLE departements(
   id integer auto_increment, 
   nom_departement varchar(20) not null,
   ville varchar(20) not null,
   primary key(id)
) engine innodb;

CREATE TABLE projets (
    id integer AUTO_INCREMENT,
    nom_projet varchar(255) NOT NULL,
    date_debut date NOT NULL,
    date_fin date NOT NULL,
    PRIMARY KEY(id)
);

CREATE TABLE taches (
    id integer AUTO_INCREMENT,
    nom_tache varchar(255) NOT NULL,
    duree_theorique integer NOT NULL,  -- Durée théorique en jours
    id_projet integer NOT NULL,  -- clé étrangère pas obligatoire
    id_employe integer, -- clé étrangère
    PRIMARY KEY(id)
);


-- ------------------------------------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------------------------------
-- DML : insert
INSERT INTO 
employes (id, nom_employe, fonction, date_embauche, salaire, commission, id_departement, id_employe_chef) VALUES
(1, 'KING',   'PRESIDENT', '1981-11-17', 5000, NULL, 10, NULL),
(2, 'BLAKE',  'MANAGER',   '1981-05-1',  2850, NULL, 30, 1),
(3, 'CLARK',  'MANAGER',   '1981-06-9',  2450, NULL, 10, 1),
(4, 'JONES',  'MANAGER',   '1981-04-2',  2975, NULL, 20, 1),
(5, 'ALLEN',  'SALESMAN',  '1981-02-20', 1600,  300, 30, 2),
(6, 'BLAKE',  'SALESMAN',  '1981-02-22', 1250,  500, 30, 2);

-- on fait un insert séparé pour FORD pour pouvoir accéder à son id avec le last_insert_id()
-- et que SMITH ait bien FORD comme chef
INSERT INTO employes VALUES
(NULL, 'FORD',   'ANALYST',   '1981-12-3',  3000, NULL, 20, 4);
INSERT INTO employes VALUES
(NULL, 'SMITH',  'CLERK',     '1980-12-17',  800, NULL, 20, last_insert_id());

INSERT INTO employes VALUES
(NULL, 'JAMES',  'CLERK',     '1981-12-3',   950, NULL, 30, 2),
(NULL, 'MARTIN', 'SALESMAN',  '1981-09-28', 1250, 1400, 30, 2),
(NULL, 'MILLER', 'CLERK',     '1982-01-23', 1300, NULL, 10, 3);

-- on fait un insert séparé pour SCOTT pour pouvoir accéder à son id avec le last_insert_id()
-- et que ADAMS ait bien SCOTT comme chef
INSERT INTO employes VALUES
(NULL, 'SCOTT',  'ANALYST',   '1982-12-09', 3000, NULL, 20, 4);
INSERT INTO employes VALUES
(NULL, 'ADAMS',  'CLERK',     '1983-01-12', 1100, NULL, 20, last_insert_id());

INSERT INTO employes VALUES
(NULL, 'TURNER', 'SALESMAN',  '1981-09-8',  1500,    0, 30, 2);


INSERT INTO 
departements (id, nom_departement, ville) VALUES
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH',   'DALLAS'),
(30, 'SALES',      'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');


INSERT INTO 
projets (id, nom_projet, date_debut, date_fin) VALUES
(1, 'Refonte Site Web', '2025-01-01', '2025-06-30'),
(2, 'Développement App Mobile', '2024-01-01', '2024-03-31'),
(3, 'Migration Cloud', '2024-04-01', '2024-12-31');


-- Projet 1 : 'Refonte Site Web' (tâches non affectées pour l'instant)
INSERT INTO taches (nom_tache, duree_theorique, id_projet, id_employe) VALUES
('Analyse des besoins',    15, 1, NULL),  -- Tâche sans employé affecté
('Développement Frontend', 30, 1, NULL),
('Développement Backend',  30, 1, NULL),
('Tests et validation',    10, 1, NULL);

-- Projet 2 : 'Développement App Mobile' 
-- Assignation de tâches à des employés spécifiques
INSERT INTO taches (nom_tache, duree_theorique, id_projet, id_employe) VALUES
('Conception UI/UX',       20, 2, 2),     -- BLAKE (Manager de l'équipe)
('Développement Frontend', 40, 2, 3),     -- CLARK
('Développement Backend',  40, 2, NULL),  -- Tâche sans employé affecté
('Tests et validation',    20, 2, NULL);  

-- Projet 3 : 'Migration Cloud' 
-- Affectation des tâches pour la migration Cloud
INSERT INTO taches (nom_tache, duree_theorique, id_projet, id_employe) VALUES
('Planification et stratégie',  25, 3, 1),  -- KING (PRESIDENT) pour la planification stratégique
('Migration des données',       50, 3, 2),  -- BLAKE
('Migration des services',      50, 3, 3),  -- CLARK
('Vérification post-migration', 15, 3, 4);  -- JONES


-- ------------------------------------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------------------------------
-- DDL : création des clés étrangères
-- Graphe des tables :    

--                 taches
--                 /    \ 
--                /      \ 
--             projets    \ 
--                         0          
--                  r--0> employes       le 0 veut dire que ça peut être null
--             chef |     /   \           
--                  |____/     \        
--                            departements 

ALTER TABLE taches
ADD FOREIGN KEY (id_projet) 
REFERENCES projets(id);

ALTER TABLE taches
ADD FOREIGN KEY (id_employe) 
REFERENCES employes(id);

ALTER TABLE employes 
ADD FOREIGN KEY (id_departement)
REFERENCES departements(id);

ALTER TABLE employes 
ADD FOREIGN KEY (id_employe_chef)
REFERENCES employes(id);



-- ------------------------------------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------------------------------
-- DML pour voir : 

-- select * de toutes les tables

SELECT * -- * veut dire toutes les colonnes
FROM employes; -- le FROM c'est toutes les lignes
-- On vérifie que les chefs sont les bons, surtout pour les last_insert_id()

SELECT * 
FROM departements; 

SELECT * 
FROM taches; 

SELECT * 
FROM projets; 


-- count(*)  de toutes les tables
SELECT count(*) as nb_employes
FROM employes; 

SELECT count(*) as nb_departements
FROM departements; 


SELECT count(*) as nb_taches
FROM taches; 

SELECT count(*) as nb_projets
FROM projets; 



-- Une jointure avec left multiples
SELECT 
   t.id id_tache, t.nom_tache, 
   e.id id_e, e.nom_employe, 
   ec.id id_chef, ec.nom_employe nom_employe_chef, 
   dc.id id_departement_chef, dc.nom_departement
FROM taches t
left JOIN employes e ON e.id=t.id_employe
left JOIN employes ec on ec.id = e.id_employe_chef
left JOIN departements dc on dc.id = ec.id_departement;