-- Migration: Ajout des champs manquants à la table projets
-- Date: 2025-01-XX
-- Description: Ajoute les champs conventionne, etude_faisabilite, validation_conseils, foncier_assaini, statut_juridique, etat_juridique_foncier, occupation_terrain à la table projets.

SET @dbname = DATABASE();
SET @tablename = 'projets';

-- Vérifier et ajouter conventionne
SET @column1 = 'conventionne';
SET @preparedStatement1 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename) AND (COLUMN_NAME = @column1)
  ) > 0,
  'SELECT "La colonne conventionne existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @column1, '` tinyint(1) NOT NULL DEFAULT 0 COMMENT "Projet conventionné (1) ou non (0)" AFTER `longitude`;')
));
PREPARE alterIfNotExists1 FROM @preparedStatement1;
EXECUTE alterIfNotExists1;
DEALLOCATE PREPARE alterIfNotExists1;

-- Vérifier et ajouter etude_faisabilite
SET @column2 = 'etude_faisabilite';
SET @preparedStatement2 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename) AND (COLUMN_NAME = @column2)
  ) > 0,
  'SELECT "La colonne etude_faisabilite existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @column2, '` tinyint(1) NOT NULL DEFAULT 0 COMMENT "Étude de faisabilité réalisée" AFTER `', @column1, '`;')
));
PREPARE alterIfNotExists2 FROM @preparedStatement2;
EXECUTE alterIfNotExists2;
DEALLOCATE PREPARE alterIfNotExists2;

-- Vérifier et ajouter validation_conseils
SET @column3 = 'validation_conseils';
SET @preparedStatement3 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename) AND (COLUMN_NAME = @column3)
  ) > 0,
  'SELECT "La colonne validation_conseils existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @column3, '` tinyint(1) NOT NULL DEFAULT 0 COMMENT "Validation par les conseils" AFTER `', @column2, '`;')
));
PREPARE alterIfNotExists3 FROM @preparedStatement3;
EXECUTE alterIfNotExists3;
DEALLOCATE PREPARE alterIfNotExists3;

-- Vérifier et ajouter foncier_assaini
SET @column4 = 'foncier_assaini';
SET @preparedStatement4 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename) AND (COLUMN_NAME = @column4)
  ) > 0,
  'SELECT "La colonne foncier_assaini existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @column4, '` tinyint(1) NOT NULL DEFAULT 0 COMMENT "Foncier assaini" AFTER `', @column3, '`;')
));
PREPARE alterIfNotExists4 FROM @preparedStatement4;
EXECUTE alterIfNotExists4;
DEALLOCATE PREPARE alterIfNotExists4;

-- Vérifier et ajouter statut_juridique
SET @column5 = 'statut_juridique';
SET @preparedStatement5 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename) AND (COLUMN_NAME = @column5)
  ) > 0,
  'SELECT "La colonne statut_juridique existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @column5, '` ENUM(\'Melk\',\'Collectif\',\'Domanial\',\'Communal\',\'Habous\') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT "Statut juridique" AFTER `', @column4, '`;')
));
PREPARE alterIfNotExists5 FROM @preparedStatement5;
EXECUTE alterIfNotExists5;
DEALLOCATE PREPARE alterIfNotExists5;

-- Vérifier et ajouter etat_juridique_foncier
SET @column6 = 'etat_juridique_foncier';
SET @preparedStatement6 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename) AND (COLUMN_NAME = @column6)
  ) > 0,
  'SELECT "La colonne etat_juridique_foncier existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @column6, '` ENUM(\'Acquis\',\'Non Acquis\',\'En cours\') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT "État juridique foncier" AFTER `', @column5, '`;')
));
PREPARE alterIfNotExists6 FROM @preparedStatement6;
EXECUTE alterIfNotExists6;
DEALLOCATE PREPARE alterIfNotExists6;

-- Vérifier et ajouter occupation_terrain
SET @column7 = 'occupation_terrain';
SET @preparedStatement7 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename) AND (COLUMN_NAME = @column7)
  ) > 0,
  'SELECT "La colonne occupation_terrain existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @column7, '` ENUM(\'Oui\',\'Non\',\'Partiellement\') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT "Occupation du terrain" AFTER `', @column6, '`;')
));
PREPARE alterIfNotExists7 FROM @preparedStatement7;
EXECUTE alterIfNotExists7;
DEALLOCATE PREPARE alterIfNotExists7;

-- Mettre à jour conventionne basé sur convention_id existant
SET @updateStatement = CONCAT('UPDATE `', @tablename, '` SET `', @column1, '` = 1 WHERE `convention_id` IS NOT NULL;');
PREPARE updateConventionne FROM @updateStatement;
EXECUTE updateConventionne;
DEALLOCATE PREPARE updateConventionne;

-- Message de confirmation
SELECT "Migration 019: Ajout des champs manquants à la table projets terminée avec succès." AS message;