-- Ajout d'une nouvelle table projets

use database;

-- On execute TOUT ou RIEN, c'est plus simple
START TRANSACTION;

-- Creation de la table projets
CREATE TABLE projets (
  id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  nom varchar(45) NOT NULL UNIQUE,
  description text NULL,
  groupes_thematique_id int(11) NULL,
  chef_science_id int(11) NULL,
  chef_projet_id int(11) NULL,
  date_start date NULL,
  date_stop date NULL
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
-- Si on voulait plutôt pointer les FK sur users(username) au lieu de users(id) :
-- chef_science_id varchar(45) NULL
-- chef_projet_id varchar(45) NULL 


-- Ajout des contraintes d’unicité sur la (les) clé de la table users : nom, mais aussi username (ca serait mieux que nom)
-- Dans tous les cas, cette contrainte d’unicité se justifie, mais elle est aussi NECESSAIRE si on veut que ces champs soient
-- des clés (primaires) de la table users et que des FK puissent pointer dessus
ALTER TABLE users ADD UNIQUE(username);
ALTER TABLE users ADD UNIQUE(nom);


-- Ajout des contraintes FK pour cette table
ALTER TABLE projets
	ADD CONSTRAINT fk_projets_groupes_thematique_id FOREIGN KEY(groupes_thematique_id) REFERENCES groupes_thematiques(id) on DELETE set null,
	ADD CONSTRAINT fk_projets_chef_science_id FOREIGN KEY(chef_science_id) REFERENCES users(id) on DELETE no ACTION,
	ADD CONSTRAINT fk_projets_chef_projet_id FOREIGN KEY(chef_projet_id) REFERENCES users(id) on DELETE no ACTION;
-- TODO: on pourrait aussi utiliser le champ username au lieu de id (modif future ?) :
	-- ADD CONSTRAINT fk_projets_chef_science_id FOREIGN KEY(chef_science_id) REFERENCES users(username) on DELETE no ACTION,
	-- ADD CONSTRAINT fk_projets_chef_projet_id FOREIGN KEY(chef_projet_id) REFERENCES users(username) on DELETE no ACTION;


-- Ajout dans la table materiels d’une FK vers cette nouvelle table
ALTER TABLE materiels
    ADD projet_id INT( 11 ) NULL after groupes_metier_id,
    ADD CONSTRAINT fk_materiels_projet_id FOREIGN KEY (projet_id) REFERENCES projets(id) on DELETE set null;


COMMIT;