CREATE TABLE chantier (id INT AUTO_INCREMENT NOT NULL, entreprise_id INT NOT NULL, conducteur_travaux_id INT NOT NULL, client_id INT DEFAULT NULL, adresse VARCHAR(255) NOT NULL, adresse2 VARCHAR(255) DEFAULT NULL, code_postal VARCHAR(10) NOT NULL, ville VARCHAR(100) NOT NULL, pays VARCHAR(180) NOT NULL, date_ouverture DATETIME NOT NULL, date_reception_pvleve_reserve DATETIME DEFAULT NULL, date_cloture_previsionnelle DATETIME DEFAULT NULL, date_cloture_reelle DATETIME DEFAULT NULL, date_documentation DATETIME DEFAULT NULL, montant_marche_initial NUMERIC(10, 2) DEFAULT NULL, montant_caution NUMERIC(10, 2) DEFAULT NULL, pourcentage_retenue_definitive NUMERIC(10, 2) DEFAULT NULL, date_create DATETIME NOT NULL, date_update DATETIME DEFAULT NULL, date_delete DATETIME DEFAULT NULL, INDEX IDX_636F27F6A4AEAFEA (entreprise_id), INDEX IDX_636F27F6EE042C0A (conducteur_travaux_id), INDEX IDX_636F27F619EB6921 (client_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;
CREATE TABLE client (id INT AUTO_INCREMENT NOT NULL, entreprise_id INT DEFAULT NULL, user_create_id INT NOT NULL, user_update_id INT DEFAULT NULL, user_delete_id INT DEFAULT NULL, nom VARCHAR(100) NOT NULL, adresse1 VARCHAR(255) NOT NULL, adresse2 VARCHAR(255) DEFAULT NULL, code_postal VARCHAR(20) NOT NULL, ville VARCHAR(100) NOT NULL, pays VARCHAR(100) NOT NULL, telephone VARCHAR(20) NOT NULL, mobile VARCHAR(20) DEFAULT NULL, email VARCHAR(120) DEFAULT NULL, actif TINYINT(1) NOT NULL, client_commun TINYINT(1) NOT NULL, date_create DATETIME NOT NULL, date_update DATETIME DEFAULT NULL, date_delete DATETIME DEFAULT NULL, INDEX IDX_C7440455A4AEAFEA (entreprise_id), INDEX IDX_C7440455EEFE5067 (user_create_id), INDEX IDX_C7440455D5766755 (user_update_id), INDEX IDX_C7440455F2E80259 (user_delete_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;
CREATE TABLE pjchantier (id INT AUTO_INCREMENT NOT NULL, chantier_id INT DEFAULT NULL, deleted_by_id INT DEFAULT NULL, file_name VARCHAR(150) NOT NULL, date_create DATETIME NOT NULL, date_update DATETIME DEFAULT NULL, date_delete DATETIME DEFAULT NULL, files JSON DEFAULT NULL, INDEX IDX_DAC31E4BD0C0049D (chantier_id), INDEX IDX_DAC31E4BC76F1F52 (deleted_by_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;
ALTER TABLE chantier ADD CONSTRAINT FK_636F27F6A4AEAFEA FOREIGN KEY (entreprise_id) REFERENCES entreprise (id);
ALTER TABLE chantier ADD CONSTRAINT FK_636F27F6EE042C0A FOREIGN KEY (conducteur_travaux_id) REFERENCES salarie (id);
ALTER TABLE chantier ADD CONSTRAINT FK_636F27F619EB6921 FOREIGN KEY (client_id) REFERENCES client (id);
ALTER TABLE client ADD CONSTRAINT FK_C7440455A4AEAFEA FOREIGN KEY (entreprise_id) REFERENCES entreprise (id);
ALTER TABLE client ADD CONSTRAINT FK_C7440455EEFE5067 FOREIGN KEY (user_create_id) REFERENCES `user` (id);
ALTER TABLE client ADD CONSTRAINT FK_C7440455D5766755 FOREIGN KEY (user_update_id) REFERENCES `user` (id);
ALTER TABLE client ADD CONSTRAINT FK_C7440455F2E80259 FOREIGN KEY (user_delete_id) REFERENCES `user` (id);
ALTER TABLE pjchantier ADD CONSTRAINT FK_DAC31E4BD0C0049D FOREIGN KEY (chantier_id) REFERENCES chantier (id);
ALTER TABLE pjchantier ADD CONSTRAINT FK_DAC31E4BC76F1F52 FOREIGN KEY (deleted_by_id) REFERENCES `user` (id);