-- Migration: Création de la table programmations
-- Date: 2025-01-XX
-- Description: Crée la table programmations identique à conventions mais sans le statut "Votée"

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

-- Vérifier si la table existe déjà
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
  ) > 0,
  'SELECT "La table programmations existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `numero_programmation` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    `type_convention` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
    `decision_id` int NULL DEFAULT NULL,
    `session_id` int NULL DEFAULT NULL,
    `plan_id` int NULL DEFAULT NULL,
    `hors_pdr` tinyint(1) NULL DEFAULT 0,
    `intitule_fr` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    `intitule_ar` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    `nature` enum(\'Matérielle\',\'Immatérielle\') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
    `statut` enum(\'Votée\',\'En cours de signature\',\'En cours de Visa\',\'Visé\') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT \'Votée\',
    `date_signature` date NULL DEFAULT NULL,
    `date_visa` date NULL DEFAULT NULL,
    `date_visionne` date NULL DEFAULT NULL,
    `document_convention_votee` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
    `fiche_technique` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
    `phase` int NULL DEFAULT 1,
    `delai_realisation_mois` decimal(5, 2) NULL DEFAULT NULL,
    `vice_president_crcs_id` int NULL DEFAULT NULL,
    `president_commission_permanente_id` int NULL DEFAULT NULL,
    `secretaire_president_id` int NULL DEFAULT NULL,
    `created_by` int NOT NULL,
    `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
    `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `numero_programmation`(`numero_programmation` ASC) USING BTREE,
    INDEX `created_by`(`created_by` ASC) USING BTREE,
    INDEX `idx_numero`(`numero_programmation` ASC) USING BTREE,
    INDEX `idx_type`(`type_convention` ASC) USING BTREE,
    INDEX `idx_decision`(`decision_id` ASC) USING BTREE,
    INDEX `idx_statut`(`statut` ASC) USING BTREE,
    INDEX `idx_plan`(`plan_id` ASC) USING BTREE,
    INDEX `idx_hors_pdr`(`hors_pdr` ASC) USING BTREE,
    INDEX `idx_session`(`session_id` ASC) USING BTREE,
    INDEX `idx_vice_president`(`vice_president_crcs_id` ASC) USING BTREE,
    INDEX `idx_president_commission`(`president_commission_permanente_id` ASC) USING BTREE,
    INDEX `idx_secretaire_president`(`secretaire_president_id` ASC) USING BTREE,
    CONSTRAINT `programmations_ibfk_1` FOREIGN KEY (`decision_id`) REFERENCES `decisions` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT,
    CONSTRAINT `programmations_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
    CONSTRAINT `programmations_ibfk_3` FOREIGN KEY (`plan_id`) REFERENCES `plans_pdr` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT,
    CONSTRAINT `programmations_ibfk_4` FOREIGN KEY (`session_id`) REFERENCES `sessions_crcs` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT,
    CONSTRAINT `programmations_ibfk_vice_president_pc` FOREIGN KEY (`vice_president_crcs_id`) REFERENCES `presidents_commissions` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT,
    CONSTRAINT `programmations_ibfk_president_commission_pc` FOREIGN KEY (`president_commission_permanente_id`) REFERENCES `presidents_commissions` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT,
    CONSTRAINT `programmations_ibfk_secretaire_president` FOREIGN KEY (`secretaire_president_id`) REFERENCES `presidents_commissions` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT
  ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;')
));
PREPARE createIfNotExists FROM @preparedStatement;
EXECUTE createIfNotExists;
DEALLOCATE PREPARE createIfNotExists;

-- Message de confirmation
SELECT "Migration 047: Création de la table programmations terminée avec succès." AS message;