START TRANSACTION;

--    IAS : Distribution 09/03/2015 *
/*
- Ajout table "organismes", "type_suivis", "sites"
- Ajout clé correspondante dans table "matériels"
- Transformation des données correspondantes
- Suppression anciens champs
- Ajout table "documents"
- Sauvegarder les utilisateurs
- Transformer table "utilisateurs" en "users"
- Ajout table "configuration"
- Ajout clé étrangère emprunts (site_id)/suivis (type_suivi_id)
- Transformation des données correspondantes
- Suppression ancien champ emprunt (e_lieu_stockage), suivis (type_intervention)
*/

set foreign_key_checks=0;


-- 
-- 27/07/2015
-- Rajouter la table organismes
-- Structure de la table organismes
-- 

CREATE TABLE IF NOT EXISTS `organismes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nom` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

-- -------------------------------------------------------------------------------------------------------------
-- 27/07/2015
-- Rajouter la table type_suivis 
-- Structure de la table `type_suivis`
--

CREATE TABLE IF NOT EXISTS `type_suivis` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nom` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

-- -------------------------------------------------------------------------------------------------------------
-- 27/07/2015
-- MODIF TABLE MATERIELS

-- Structure de la table `materiels`
ALTER TABLE `materiels` ADD `date_reception` date DEFAULT NULL;
-- ALTER TABLE `materiels` ADD `organisme` varchar(20) DEFAULT NULL;
ALTER TABLE `materiels` ADD `organisme_id` int(11) DEFAULT NULL;
-- ALTER TABLE `materiels` ADD `lieu_stockage` varchar(45) DEFAULT NULL;
ALTER TABLE `materiels` ADD `site_id` int(11) DEFAULT '2';


-- -----------------------------------------------------------------------------------------------------------
-- 27/07/2015
-- Rajouter la table sites 
-- Structure de la table `sites`
--

CREATE TABLE IF NOT EXISTS `sites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nom` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

-- Contraintes pour la table `materiels`

ALTER TABLE `materiels`
  ADD CONSTRAINT `fk_materiels_organisme_id` FOREIGN KEY (`organisme_id`) REFERENCES `organismes` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_materiels_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- 27/07/2015
-- Rajouter la table documents 
-- Structure de la table `documents`

CREATE TABLE IF NOT EXISTS `documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type_doc` varchar(20) DEFAULT NULL,

  `description` varchar(100) DEFAULT NULL,
  `chemin` varchar(100) DEFAULT NULL,
  `materiel_id` int(11) DEFAULT NULL,
  `suivi_id` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`),
  KEY `fk_documents_materiel_id` (`materiel_id`),
  KEY `fk_documents_suivi_id` (`suivi_id`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

-- Contraintes pour la table `documents`

ALTER TABLE `documents`
  ADD CONSTRAINT `fk_documents_materiel_id` FOREIGN KEY (`materiel_id`) REFERENCES `materiels` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_documents_suivi_id` FOREIGN KEY (`suivi_id`) REFERENCES `suivis` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;


DROP TABLE IF EXISTS `fichiers`;

-- Utilisateurs -> Users

-- 25/04/2016
-- Modifications nom table "utilisateurs" ==> "users"
-- Ajout attribut "password" dans la table users
-- Modification attribut "login" de la table users en "username"

CREATE TABLE users AS SELECT * FROM utilisateurs ;

ALTER TABLE `users` ADD `password` VARCHAR(255) DEFAULT '$2y$10$nBQMNstgN.sgad1ZANznY.pbJI.ZG/.Q5qX4gC8SXCFQnDIZC8rcW';

ALTER TABLE `users` CHANGE `login` `username` VARCHAR(45) DEFAULT NULL;

DROP TABLE IF EXISTS `utilisateurs`;

INSERT INTO users (id, nom, username, password, email, role) VALUES

('25', 'super-admin', 'superadmin', '$2y$10$nBQMNstgN.sgad1ZANznY.pbJI.ZG/.Q5qX4gC8SXCFQnDIZC8rcW', 'superadmin@admin.fr', 'Super Administrateur');

--
-- Contraintes pour la table `users`
--
ALTER TABLE `users`
  ADD CONSTRAINT `fk_users_groupes_travails1` FOREIGN KEY (`groupes_metier_id`) REFERENCES `groupes_metiers` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;



DROP TABLE IF EXISTS `configurations`;
CREATE TABLE IF NOT EXISTS `configurations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nom` varchar(45) NOT NULL COMMENT 'obligatoire (et unique)',
  `mode_install` tinyint(1)  DEFAULT NULL,
  `mode_debug` tinyint(1)  DEFAULT NULL,
  `use_ldap` tinyint(1)  DEFAULT NULL,

  `host_ldap` text DEFAULT NULL,

  `port_ldap` varchar(10)  DEFAULT NULL,

  `authentificationType_ldap` varchar(30)  DEFAULT 'xxx',

  `baseDn_ldap` varchar(30)  DEFAULT NULL,
  `filter_ldap` varchar(30)  DEFAULT NULL,
  `labName` varchar(30)  DEFAULT NULL,
  `labNameShort` varchar(20)  DEFAULT NULL,
  `labPresent` varchar(10)  DEFAULT NULL,
  `labUmr` varchar(30)  DEFAULT NULL,
  `hasPrinter` tinyint(1)  DEFAULT NULL,

  `nom_groupe_thematique` varchar(50) DEFAULT 'Groupe thematique',
  `nom_groupe_metier` varchar(50) DEFAULT 'Groupe metier',

  `envoi_mail_management_dev` tinyint(1) DEFAULT NULL,

  `sender_mail` varchar(500) DEFAULT 'labinvent@irap.omp.eu',

  `emailGuest1` varchar(45)  DEFAULT NULL,
  `emailGuest2` varchar(45)  DEFAULT NULL,
  `emailGuest3` varchar(45)  DEFAULT NULL,

  `emailGuest4` varchar(45)  DEFAULT NULL,
  `emailGuest5` varchar(45)  DEFAULT NULL,

  `emailGuest6` varchar(45)  DEFAULT NULL,
  `emailGuest7` varchar(45)  DEFAULT NULL,
  `emailGuest8` varchar(45)  DEFAULT NULL,
  `emailGuest9` varchar(45)  DEFAULT NULL,
  `emailGuest10` varchar(45)  DEFAULT NULL,

  `test` tinyint(1) DEFAULT NULL,

  PRIMARY KEY (`id`),
  UNIQUE KEY `nom_UNIQUE` (`nom`)
);

Insert into `configurations`(`nom`, `mode_install`, `mode_debug`, `use_ldap`, `host_ldap`, `port_ldap`, `authentificationType_ldap`, `baseDn_ldap`, `filter_ldap`, `labName`, `labNameShort`, `labPresent`, `labUmr`, `hasPrinter`, `emailGuest1`, `emailGuest2`, `emailGuest3`) values ('default', '1', '0', '0', '', '', 'xxx', '', '', 'LABONAME', 'LABO', 'du ', '', '0', '', '', '');

-- EMPRUNTS
-- Structure de la table emprunts
ALTER TABLE `emprunts` ADD `site_id` int(11) DEFAULT NULL;

-- Contrainte de la table emprunts
ALTER TABLE `emprunts`
  ADD CONSTRAINT `fk_emprunts_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Suppression attribut
ALTER TABLE `emprunts` DROP `e_lieu_stockage`;

-- SUIVIS

-- Structure de la table suivis
ALTER TABLE `suivis` ADD `type_suivi_id` int(11) DEFAULT NULL;

-- Contrainte de la table suivis
ALTER TABLE `suivis`
  ADD CONSTRAINT `fk_suivis_type_suivi_id` FOREIGN KEY (`type_suivi_id`) REFERENCES `type_suivis` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

Alter table `suivis` Add `type_frequence` VARCHAR(30) DEFAULT NULL;

ALTER TABLE `suivis` ADD `panne_resolu` tinyint(1) DEFAULT 1;

ALTER TABLE `materiels` ADD `date_fin_garantie` date DEFAULT NULL;
ALTER TABLE `materiels` ADD `duree_garantie` int(10) DEFAULT NULL;
ALTER TABLE `materiels` ADD `unite_duree_garantie` varchar (30) DEFAULT NULL;

ALTER TABLE `configurations` ADD `prix_inventaire_administratif` int( 10 ) DEFAULT '800';

ALTER TABLE `users` ADD `groupe_thematique_id` int(11) DEFAULT NULL;
ALTER TABLE `users`
  ADD CONSTRAINT `fk_users_groupe_thematique_id` FOREIGN KEY (`groupe_thematique_id`) REFERENCES `groupes_thematiques` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

update emprunts set date_emprunt = NULL where date_emprunt = '1970-01-01';
update emprunts set date_retour_emprunt = NULL where date_retour_emprunt = '1970-01-01';

update suivis set date_controle = NULL where date_controle = '1970-01-01';
update suivis set date_prochain_controle = NULL where date_prochain_controle = '1970-01-01';

update materiels set date_acquisition = NULL where date_acquisition = '1970-01-01';
update materiels set date_archivage = NULL where date_archivage = '1970-01-01';
update materiels set date_reception = NULL where date_reception = '1970-01-01';

ALTER TABLE `suivis` ADD `groupes_metier_id` int(11) DEFAULT NULL;
ALTER TABLE `suivis` ADD `groupes_thematique_id` int(11) DEFAULT NULL;

ALTER TABLE `suivis` ADD CONSTRAINT `fk_suivis_groupe_thematique` FOREIGN KEY (`groupes_thematique_id`) REFERENCES `groupes_thematiques` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `suivis` ADD CONSTRAINT `fk_suivis_groupe_metier` FOREIGN KEY (`groupes_metier_id`) REFERENCES `groupes_metiers` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

INSERT INTO `organismes` (`id`, `nom`) VALUES
(1, 'CNRS'),
(2, 'UPS'),
(3, 'Autre');

update materiels set organisme_id = NULL where organisme is NULL;
update materiels set organisme_id ="1" where organisme like "CNRS%";
update materiels set organisme_id ="2" where organisme like "UPS%";
update materiels set organisme_id ="3" where organisme like "Autre%";


INSERT INTO `sites` (`id`, `nom`) VALUES
(1, 'Bat. 121'),
(2, 'Bat. 120'),
(3, 'Bat. 120 CHARRA'),
(4, 'Bat. 120 CAZES'),
(5, 'Bat. 209F'),
(6, 'Bat. 209G'),
(7, 'Bat. 105 ASTRO'),
(8, 'Bat. 105 GALILEE'),
(9, 'N/A'),
(10, 'RF');

update materiels set site_id = NULL where lieu_stockage is NULL;
update materiels set site_id ="1" where lieu_stockage like "Bat. 121%";
update materiels set site_id ="2" where lieu_stockage like "Bat. 120%";
update materiels set site_id ="3" where lieu_stockage like "Bat. 120 CHARRA%";
update materiels set site_id ="4" where lieu_stockage like "Bat. 120 CAZES%";
update materiels set site_id ="5" where lieu_stockage like "Bat. 209F%";
update materiels set site_id ="6" where lieu_stockage like "Bat. 209G%";
update materiels set site_id ="7" where lieu_stockage like "Bat. 105 ASTRO%";
update materiels set site_id ="8" where lieu_stockage like "Bat. 105 GALILEE%";
update materiels set site_id ="10" where lieu_stockage like "RF%";


INSERT INTO `type_suivis` (`id`, `nom`) VALUES
(1, 'Etalonnage'),
(2, 'Réparation'),
(3, 'Autre');

update suivis set type_suivi_id = NULL where type_intervention is NULL;
update suivis set type_suivi_id ="1" where type_intervention like "Etalonnage%";
update suivis set type_suivi_id ="2" where type_intervention like "Réparation%";
update suivis set type_suivi_id ="3" where type_intervention like "Autre%";

-- Suppression attribut
ALTER TABLE `suivis` DROP `type_intervention`;
alter table `materiels` drop `organisme`;
alter table `materiels` drop `lieu_stockage`;

COMMIT;