-- ============================================================================
-- PTA — INSTALLATION PRODUCTION COMPLÈTE, EN UN SEUL FICHIER
-- Version applicative cible : V7.30
-- MariaDB 10.6+ / MySQL 8.0.16+
-- ============================================================================
-- Ce fichier remplace, à lui seul, l'enchaînement
--     database/pta_prod_schema_mariadb.sql (socle métier)
--   + database/migration_v7_8_securite_acces.sql
--   + database/migration_v7_9_tableau_de_bord.sql
--   + database/migration_v7_9b_droit_enveloppe_formation.sql
--   + database/migration_v7_9c_retrait_formation_repartition.sql
--   + database/migration_v7_9d_faq.sql
--   + database/migration_v7_9e_dsi_comptes_journal.sql
--   + database/migration_v7_9f_mot_de_passe_admin.sql
--   + database/migration_v7_9g_management.sql
--   + database/migration_v7_9h_jetons_service_enfance.sql
--
-- Il contient TOUT ce que l'application exige pour démarrer :
--   * les tables du métier, leurs clés étrangères, index et contraintes ;
--   * la matrice des droits (rôle -> pages, rôle -> permissions) ;
--   * les comptes d'accès à jeton et le journal d'activité ;
--   * les dix vues de contrôle d'accès vérifiées au démarrage ;
--   * les vues de pilotage et les procédures SQL historiques ;
--   * les seuls référentiels techniques indispensables : postes et motifs.
--
-- Il ne contient AUCUN agent, lieu, planning, quota ni jeu de démonstration.
-- La seule donnée nominative est le compte DSI d'installation (section 14.1).
--
-- ----------------------------------------------------------------------------
-- USAGE
-- ----------------------------------------------------------------------------
-- Sur une base vide, en ligne de commande :
--
--     mariadb -u pta -p pta < database/install_complet.sql
--
-- Depuis un client SQL déjà connecté à la base :
--
--     SOURCE database/install_complet.sql;
--
-- Avec la pile Docker du projet, il n'y a RIEN à faire : le socle est joué à la
-- création du volume par docker-entrypoint-initdb.d et les migrations par le
-- service db-migrate. Ce fichier vise l'installation MANUELLE sur un serveur de
-- production existant, ou la reconstruction d'une base à l'identique.
--
-- ----------------------------------------------------------------------------
-- PORTABILITÉ MySQL / MariaDB
-- ----------------------------------------------------------------------------
-- Ce fichier est joué tel quel sur les DEUX SGBD, et testé sur les deux :
-- MariaDB 11.4 (la pile Docker du projet) et MySQL 8.0 (le serveur de
-- production). Il n'utilise donc AUCUNE extension propre à l'un des deux.
--
-- Le piège à connaître avant d'y toucher : `ALTER TABLE … ADD COLUMN
-- IF NOT EXISTS …` et `DROP COLUMN IF EXISTS` sont des extensions MARIADB, que
-- MySQL rejette par une erreur 1064 de syntaxe. Les migrations du dossier
-- `database/` les emploient — elles ne visent que la pile Docker — mais ici le
-- test d'existence passe par `information_schema` et une requête préparée (voir
-- section 14). Ne pas « simplifier » ce passage en revenant à IF NOT EXISTS.
--
-- Tous les identifiants sont entourés de backticks : certains clients SQL les
-- exigent, et cela met le schéma à l'abri d'un mot devenu réservé dans une
-- version future (`role`, `action`, `source`, `zone`… sont des noms de colonnes
-- de ce schéma).
--
-- Reste une DÉPRÉCIATION, pas une erreur : `VALUES(colonne)` dans les clauses
-- ON DUPLICATE KEY UPDATE fait émettre à MySQL 8.0.20+ l'avertissement 1287.
-- La forme de remplacement (`… VALUES (…) AS n … = n.colonne`) est refusée par
-- MySQL < 8.0.19 et MariaDB < 10.3.3 ; la forme historique est conservée, comme
-- dans le socle et les migrations.
--
-- ----------------------------------------------------------------------------
-- IDEMPOTENCE
-- ----------------------------------------------------------------------------
-- Tout est écrit en CREATE TABLE IF NOT EXISTS, CREATE OR REPLACE VIEW et
-- INSERT ... ON DUPLICATE KEY UPDATE : le rejouer sur une base déjà installée
-- ne détruit aucune donnée et ne fait que remettre les référentiels et les
-- droits dans leur état de référence.
--
-- Corollaire : ce fichier ne renseigne PAS la table `schema_migration`. Si la
-- pile Docker démarre ensuite sur cette base, db-migrate rejouera les neuf
-- migrations — elles sont elles aussi idempotentes, l'opération est sans effet.
--
-- ----------------------------------------------------------------------------
-- APRÈS L'INSTALLATION — deux gestes obligatoires
-- ----------------------------------------------------------------------------
--   1. Se connecter avec le jeton d'installation PTA-INSTALLATION-DSI, puis le
--      RÉGÉNÉRER immédiatement : il est public, il est écrit en clair dans ce
--      fichier versionné (section 14.1). La page DSI alerte tant que c'est le
--      cas.
--   2. Choisir le mot de passe du compte DSI à cette première connexion : les
--      rôles SERVICE_ENFANCE et DSI exigent ce second facteur, et un compte
--      privilégié sans mot de passe ne peut ouvrir aucune session.
--
-- ----------------------------------------------------------------------------
-- NOTE MySQL #1832
-- ----------------------------------------------------------------------------
-- creneau_horaire.id_motif est créé directement en INT UNSIGNED NOT NULL, AVANT
-- la création de fk_creneau_motif. Aucun ALTER TABLE ... MODIFY id_motif n'est
-- donc exécuté après coup sur une installation neuve.
-- ============================================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- Décommenter si vous souhaitez que le script crée la base lui-même.
-- CREATE DATABASE IF NOT EXISTS pta
--     CHARACTER SET utf8mb4
--     COLLATE utf8mb4_unicode_ci;
-- USE pta;

-- ============================================================================
-- 1. LIEUX D'AFFECTATION
-- ============================================================================
CREATE TABLE IF NOT EXISTS `structure` (
    `id_structure` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(50) NOT NULL,
    `nom` VARCHAR(150) NOT NULL,
    `adresse` VARCHAR(255) NULL,
    `type_affectation` ENUM('PERISCOLAIRE', 'EXTRASCOLAIRE') NOT NULL DEFAULT 'PERISCOLAIRE',
    `actif` BOOLEAN NOT NULL DEFAULT TRUE,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT `uk_structure_code` UNIQUE (`code`),
    INDEX `idx_structure_actif_nom` (`actif`, `nom`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 2. POSTES PTA - RÉFÉRENTIEL TECHNIQUE
-- ============================================================================
CREATE TABLE IF NOT EXISTS `poste_agent` (
    `id_poste` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(50) NOT NULL,
    `libelle` VARCHAR(150) NOT NULL,
    `famille` ENUM('ANIMATION', 'RESTAURATION_ENTRETIEN') NOT NULL,
    `heures_mercredi_minutes` SMALLINT UNSIGNED NOT NULL,
    `heures_extrascolaire_minutes` SMALLINT UNSIGNED NOT NULL,
    `autorise_preparation` BOOLEAN NOT NULL DEFAULT TRUE,
    `peut_assurer_animation` BOOLEAN NOT NULL DEFAULT TRUE,
    `preparation_lundi_si_100` BOOLEAN NOT NULL DEFAULT FALSE,
    `actif` BOOLEAN NOT NULL DEFAULT TRUE,
    `ordre_affichage` SMALLINT UNSIGNED NOT NULL DEFAULT 100,

    CONSTRAINT `uk_poste_agent_code` UNIQUE (`code`),
    INDEX `idx_poste_agent_actif_ordre` (`actif`, `ordre_affichage`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Référentiel indispensable au fonctionnement de l'application.
INSERT INTO `poste_agent` (
    `code`, `libelle`, `famille`,
    `heures_mercredi_minutes`, `heures_extrascolaire_minutes`,
    `autorise_preparation`, `peut_assurer_animation`,
    `preparation_lundi_si_100`, `ordre_affichage`
) VALUES
    ('RESPONSABLE', 'Responsable d’accueil', 'ANIMATION', 570, 570, TRUE, TRUE, TRUE, 10),
    ('ANIMATION_RELAIS', 'Agent d’animation relais', 'ANIMATION', 570, 570, TRUE, TRUE, TRUE, 20),
    ('ANIMATION', 'Agent d’animation', 'ANIMATION', 570, 570, TRUE, TRUE, TRUE, 30),
    ('RESTAURATION_ENTRETIEN', 'Agent de restauration collective et d’entretien', 'RESTAURATION_ENTRETIEN', 420, 420, FALSE, FALSE, FALSE, 40)
ON DUPLICATE KEY UPDATE
    `libelle` = VALUES(`libelle`),
    `famille` = VALUES(`famille`),
    `heures_mercredi_minutes` = VALUES(`heures_mercredi_minutes`),
    `heures_extrascolaire_minutes` = VALUES(`heures_extrascolaire_minutes`),
    `autorise_preparation` = VALUES(`autorise_preparation`),
    `peut_assurer_animation` = VALUES(`peut_assurer_animation`),
    `preparation_lundi_si_100` = VALUES(`preparation_lundi_si_100`),
    `ordre_affichage` = VALUES(`ordre_affichage`),
    `actif` = TRUE;

-- ============================================================================
-- 3. AGENTS
-- ============================================================================
CREATE TABLE IF NOT EXISTS `agent` (
    `id_agent` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `matricule` VARCHAR(50) NOT NULL,
    `nom` VARCHAR(100) NOT NULL,
    `prenom` VARCHAR(100) NOT NULL,
    `email` VARCHAR(255) NULL,
    `telephone` VARCHAR(30) NULL,
    `adresse` VARCHAR(255) NULL,
    `est_diplome` BOOLEAN NOT NULL DEFAULT FALSE,
    `diplome_libelle` VARCHAR(150) NULL,
    -- V7.9b : tous les agents ne bénéficient pas de l'enveloppe de 14 h.
    `droit_enveloppe_formation` BOOLEAN NOT NULL DEFAULT TRUE
        COMMENT 'Droit à l''enveloppe annuelle de 14 h de formation',
    `date_debut_contrat` DATE NULL,
    `id_poste` INT UNSIGNED NULL,
    `type_contrat` ENUM('PERMANENT', 'TEMPORAIRE') NOT NULL DEFAULT 'PERMANENT',
    `date_fin_contrat` DATE NULL,
    `commentaire_pta` VARCHAR(1000) NULL,
    `id_structure` INT UNSIGNED NULL COMMENT 'Lieu principal de rattachement',
    `actif` BOOLEAN NOT NULL DEFAULT TRUE,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT `uk_agent_matricule` UNIQUE (`matricule`),

    CONSTRAINT `fk_agent_poste`
        FOREIGN KEY (`id_poste`) REFERENCES `poste_agent`(`id_poste`)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT `fk_agent_structure_principale`
        FOREIGN KEY (`id_structure`) REFERENCES `structure`(`id_structure`)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT `chk_agent_dates_contrat`
        CHECK (
            `date_fin_contrat` IS NULL
            OR `date_debut_contrat` IS NULL
            OR `date_fin_contrat` >= `date_debut_contrat`
        ),

    INDEX `idx_agent_poste` (`id_poste`),
    INDEX `idx_agent_structure_principale` (`id_structure`),
    INDEX `idx_agent_type_contrat` (`type_contrat`, `actif`),
    INDEX `idx_agent_actif_nom` (`actif`, `nom`, `prenom`),
    INDEX `idx_agent_date_debut_contrat` (`date_debut_contrat`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Historique des rattachements de l'agent.
CREATE TABLE IF NOT EXISTS `agent_structure` (
    `id_agent_structure` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `id_agent` INT UNSIGNED NOT NULL,
    `id_structure` INT UNSIGNED NOT NULL,
    `date_debut` DATE NULL,
    `date_fin` DATE NULL,
    `actif` BOOLEAN NOT NULL DEFAULT TRUE,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT `fk_agent_structure_agent`
        FOREIGN KEY (`id_agent`) REFERENCES `agent`(`id_agent`)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT `fk_agent_structure_structure`
        FOREIGN KEY (`id_structure`) REFERENCES `structure`(`id_structure`)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT `chk_agent_structure_dates`
        CHECK (`date_fin` IS NULL OR `date_debut` IS NULL OR `date_fin` >= `date_debut`),

    CONSTRAINT `uk_agent_structure_periode`
        UNIQUE (`id_agent`, `id_structure`, `date_debut`),

    INDEX `idx_agent_structure_agent_actif` (`id_agent`, `actif`),
    INDEX `idx_agent_structure_structure_actif` (`id_structure`, `actif`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 4. CAPACITÉS / TRANCHES D'ÂGE
-- ============================================================================
CREATE TABLE IF NOT EXISTS `tranche_age` (
    `id_tranche_age` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `libelle` VARCHAR(100) NOT NULL,
    `age_min_mois` INT UNSIGNED NULL,
    `age_max_mois` INT UNSIGNED NULL,

    CONSTRAINT `chk_tranche_age`
        CHECK (
            `age_min_mois` IS NULL
            OR `age_max_mois` IS NULL
            OR `age_max_mois` >= `age_min_mois`
        )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `structure_capacite` (
    `id_structure` INT UNSIGNED NOT NULL,
    `id_tranche_age` INT UNSIGNED NOT NULL,
    `nombre_places` INT UNSIGNED NOT NULL,

    PRIMARY KEY (`id_structure`, `id_tranche_age`),

    CONSTRAINT `fk_capacite_structure`
        FOREIGN KEY (`id_structure`) REFERENCES `structure`(`id_structure`)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT `fk_capacite_tranche_age`
        FOREIGN KEY (`id_tranche_age`) REFERENCES `tranche_age`(`id_tranche_age`)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT `chk_nombre_places` CHECK (`nombre_places` > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 5. RÈGLES DE COUVERTURE DES LIEUX
-- ============================================================================
CREATE TABLE IF NOT EXISTS `structure_couverture_horaire` (
    `id_couverture` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `id_structure` INT UNSIGNED NOT NULL,
    `jour_semaine` TINYINT UNSIGNED NOT NULL COMMENT '1=lundi, 5=vendredi',
    `heure_debut` TIME NOT NULL,
    `heure_fin` TIME NOT NULL,
    `minimum_agents` TINYINT UNSIGNED NOT NULL DEFAULT 1,

    CONSTRAINT `fk_couverture_structure`
        FOREIGN KEY (`id_structure`) REFERENCES `structure`(`id_structure`)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT `chk_couverture_jour` CHECK (`jour_semaine` BETWEEN 1 AND 5),
    CONSTRAINT `chk_couverture_ordre` CHECK (`heure_fin` > `heure_debut`),
    CONSTRAINT `chk_couverture_minimum` CHECK (`minimum_agents` BETWEEN 1 AND 50),
    CONSTRAINT `chk_couverture_quart_heure` CHECK (
        MOD(MINUTE(`heure_debut`), 15) = 0
        AND MOD(MINUTE(`heure_fin`), 15) = 0
        AND SECOND(`heure_debut`) = 0
        AND SECOND(`heure_fin`) = 0
    ),

    INDEX `idx_couverture_structure_jour`
        (`id_structure`, `jour_semaine`, `heure_debut`, `heure_fin`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Horaires de référence utilisés pour les automatismes de préparation.
CREATE TABLE IF NOT EXISTS `structure_creneau_reference` (
    `id_reference` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `id_structure` INT UNSIGNED NOT NULL,
    `code_plage` ENUM('ALP_MATIN', 'RESTAURATION', 'ALP_SOIR') NOT NULL,
    `jour_semaine` TINYINT UNSIGNED NOT NULL COMMENT '1=lundi ... 5=vendredi',
    `heure_debut` TIME NOT NULL,
    `heure_fin` TIME NOT NULL,
    `actif` BOOLEAN NOT NULL DEFAULT TRUE,

    CONSTRAINT `fk_reference_structure`
        FOREIGN KEY (`id_structure`) REFERENCES `structure`(`id_structure`)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT `chk_reference_jour` CHECK (`jour_semaine` BETWEEN 1 AND 5),
    CONSTRAINT `chk_reference_ordre` CHECK (`heure_fin` > `heure_debut`),
    CONSTRAINT `uk_reference_structure_plage_jour`
        UNIQUE (`id_structure`, `code_plage`, `jour_semaine`),

    INDEX `idx_reference_structure_jour` (`id_structure`, `jour_semaine`, `actif`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 6. MOTIFS D'AFFECTATION - RÉFÉRENTIEL TECHNIQUE
-- ============================================================================
CREATE TABLE IF NOT EXISTS `motif_planning` (
    `id_motif` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(50) NOT NULL,
    `libelle` VARCHAR(120) NOT NULL,
    `compte_dans_quota` BOOLEAN NOT NULL DEFAULT FALSE,
    `ordre_affichage` SMALLINT UNSIGNED NOT NULL DEFAULT 100,
    `actif` BOOLEAN NOT NULL DEFAULT TRUE,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT `uk_motif_planning_code` UNIQUE (`code`),
    INDEX `idx_motif_planning_actif_ordre` (`actif`, `ordre_affichage`, `libelle`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Aucun jeu d'essai : uniquement les motifs nécessaires au fonctionnement.
INSERT INTO `motif_planning` (`code`, `libelle`, `compte_dans_quota`, `ordre_affichage`, `actif`)
VALUES
    ('TRAVAIL', 'Heure de travail', TRUE, 5, TRUE),
    ('ALP_MATIN', 'ALP matin', TRUE, 10, TRUE),
    ('RESTAURATION', 'Restauration', TRUE, 20, TRUE),
    ('ALP_SOIR', 'ALP soir', TRUE, 30, TRUE),
    ('MERCREDI_PERISCOLAIRE', 'Mercredi périscolaire', TRUE, 40, TRUE),
    ('EXTRASCOLAIRE', 'Temps extrascolaire', TRUE, 50, TRUE),
    ('FORMATION', 'Formation', TRUE, 60, TRUE),
    -- compte_dans_quota = FALSE : les 1 607 h sont un volume NET dont les 25
    -- jours de congés payés ont déjà été retranchés. Les imputer au quota les
    -- décompterait une seconde fois. Idem pour les jours fériés.
    ('CONGE', 'Congé annuel (non décompté)', FALSE, 70, TRUE),
    ('JOUR_FRACTIONNEMENT', 'Journée de fractionnement', TRUE, 80, TRUE),
    ('PREPARATION_PERISCOLAIRE', 'Préparation périscolaire', TRUE, 90, TRUE),
    ('PREPARATION_EXTRASCOLAIRE', 'Préparation extrascolaire', TRUE, 100, TRUE),
    ('PREPARATION_LUNDI', 'Préparation du lundi', TRUE, 110, TRUE),
    ('MENAGE_FOND', 'Ménage de fond', TRUE, 120, TRUE),
    ('ABSENCE', 'Absence', TRUE, 130, TRUE),
    ('AUTRE_ABSENCE', 'Autre absence (non décomptée)', FALSE, 140, TRUE),
    ('JNT', 'Journée non travaillée', FALSE, 150, TRUE)
ON DUPLICATE KEY UPDATE
    `libelle` = VALUES(`libelle`),
    `compte_dans_quota` = VALUES(`compte_dans_quota`),
    `ordre_affichage` = VALUES(`ordre_affichage`),
    `actif` = TRUE;

-- ============================================================================
-- 7. QUOTAS ANNUELS - ANNÉE CIVILE 01/01 -> 31/12
-- ============================================================================
CREATE TABLE IF NOT EXISTS `quota_agent_annuel` (
    `id_quota` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `id_agent` INT UNSIGNED NOT NULL,
    `annee` SMALLINT UNSIGNED NOT NULL,
    `quotite_travail` DECIMAL(5,2) NOT NULL DEFAULT 100.00,
    `quota_reference_minutes` INT UNSIGNED NOT NULL DEFAULT 96420 COMMENT '1607 h à 100 %',
    `quota_cible_minutes` INT UNSIGNED NOT NULL,
    `commentaire` VARCHAR(255) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT `fk_quota_annuel_agent`
        FOREIGN KEY (`id_agent`) REFERENCES `agent`(`id_agent`)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT `chk_quotite_travail` CHECK (`quotite_travail` > 0 AND `quotite_travail` <= 100),
    CONSTRAINT `chk_quota_reference_positif` CHECK (`quota_reference_minutes` > 0),
    CONSTRAINT `chk_quota_cible_positif` CHECK (`quota_cible_minutes` > 0),
    CONSTRAINT `uk_quota_agent_annee` UNIQUE (`id_agent`, `annee`),

    INDEX `idx_quota_agent_annee` (`id_agent`, `annee`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `pta_annuel` (
    `id_pta` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `id_agent` INT UNSIGNED NOT NULL,
    `date_debut` DATE NOT NULL,
    `date_fin` DATE NOT NULL,
    `quotite_travail` DECIMAL(5,2) NOT NULL,
    `quota_reference_minutes` INT UNSIGNED NOT NULL DEFAULT 96420,
    `quota_cible_minutes` INT UNSIGNED NOT NULL,
    `enveloppe_formation_minutes` SMALLINT UNSIGNED NOT NULL DEFAULT 840,
    `conges_annuels_jours_cible` DECIMAL(5,2) NOT NULL DEFAULT 25.00,
    `jours_fractionnement_cible` TINYINT UNSIGNED NOT NULL DEFAULT 2,
    `statut` ENUM('BROUILLON', 'A_CONTROLER', 'VALIDE') NOT NULL DEFAULT 'BROUILLON',
    `commentaire` VARCHAR(1000) NULL,
    `date_validation` DATETIME NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT `fk_pta_annuel_agent`
        FOREIGN KEY (`id_agent`) REFERENCES `agent`(`id_agent`)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT `chk_pta_annuel_dates` CHECK (`date_fin` >= `date_debut`),
    CONSTRAINT `chk_pta_annuel_quotite` CHECK (`quotite_travail` > 0 AND `quotite_travail` <= 100),
    CONSTRAINT `chk_pta_annuel_quota` CHECK (`quota_reference_minutes` > 0 AND `quota_cible_minutes` > 0),
    CONSTRAINT `uk_pta_annuel_agent_periode` UNIQUE (`id_agent`, `date_debut`, `date_fin`),

    INDEX `idx_pta_annuel_agent_statut` (`id_agent`, `statut`),
    INDEX `idx_pta_annuel_periode` (`date_debut`, `date_fin`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 8. SEMAINES / PLANNINGS / CRÉNEAUX
-- ============================================================================
CREATE TABLE IF NOT EXISTS `semaine` (
    `id_semaine` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `annee` SMALLINT UNSIGNED NOT NULL COMMENT 'Année ISO de la semaine',
    `numero_semaine` TINYINT UNSIGNED NOT NULL,
    `date_debut` DATE NOT NULL COMMENT 'Lundi',
    `date_fin` DATE NOT NULL COMMENT 'Dimanche',
    `statut` ENUM('OUVERTE', 'VALIDEE', 'VERROUILLEE') NOT NULL DEFAULT 'OUVERTE',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT `uk_semaine_annee_numero` UNIQUE (`annee`, `numero_semaine`),
    CONSTRAINT `uk_semaine_dates` UNIQUE (`date_debut`, `date_fin`),
    CONSTRAINT `chk_numero_semaine` CHECK (`numero_semaine` BETWEEN 1 AND 53),
    CONSTRAINT `chk_dates_semaine` CHECK (`date_fin` >= `date_debut`),

    INDEX `idx_semaine_dates` (`date_debut`, `date_fin`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `planning` (
    `id_planning` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `id_agent` INT UNSIGNED NOT NULL,
    `id_semaine` INT UNSIGNED NOT NULL,
    `id_structure` INT UNSIGNED NOT NULL,
    `statut` ENUM('BROUILLON', 'VALIDE') NOT NULL DEFAULT 'BROUILLON',
    `commentaire` VARCHAR(500) NULL,
    `id_planning_source` INT UNSIGNED NULL,
    `date_creation` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `date_modification` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `date_validation` DATETIME NULL,

    CONSTRAINT `fk_planning_agent`
        FOREIGN KEY (`id_agent`) REFERENCES `agent`(`id_agent`)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT `fk_planning_semaine`
        FOREIGN KEY (`id_semaine`) REFERENCES `semaine`(`id_semaine`)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT `fk_planning_structure`
        FOREIGN KEY (`id_structure`) REFERENCES `structure`(`id_structure`)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT `fk_planning_source`
        FOREIGN KEY (`id_planning_source`) REFERENCES `planning`(`id_planning`)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT `uk_planning_agent_semaine_structure`
        UNIQUE (`id_agent`, `id_semaine`, `id_structure`),

    INDEX `idx_planning_agent_statut` (`id_agent`, `statut`),
    INDEX `idx_planning_semaine_structure` (`id_semaine`, `id_structure`, `statut`),
    INDEX `idx_planning_modification` (`date_modification`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- CORRECTION #1832 : le type de id_motif est correct dès sa création.
CREATE TABLE IF NOT EXISTS `creneau_horaire` (
    `id_creneau` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `id_planning` INT UNSIGNED NOT NULL,
    `id_motif` INT UNSIGNED NOT NULL,
    `date_jour` DATE NOT NULL,
    `heure_debut` TIME NOT NULL,
    `heure_fin` TIME NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT `fk_creneau_planning`
        FOREIGN KEY (`id_planning`) REFERENCES `planning`(`id_planning`)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT `fk_creneau_motif`
        FOREIGN KEY (`id_motif`) REFERENCES `motif_planning`(`id_motif`)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT `chk_creneau_ordre` CHECK (`heure_fin` > `heure_debut`),
    CONSTRAINT `chk_creneau_quart_heure` CHECK (
        MOD(MINUTE(`heure_debut`), 15) = 0
        AND MOD(MINUTE(`heure_fin`), 15) = 0
        AND SECOND(`heure_debut`) = 0
        AND SECOND(`heure_fin`) = 0
    ),

    INDEX `idx_creneau_planning_date` (`id_planning`, `date_jour`, `heure_debut`, `heure_fin`),
    INDEX `idx_creneau_motif` (`id_motif`),
    INDEX `idx_creneau_date` (`date_jour`, `heure_debut`, `heure_fin`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 9. SEMAINES TYPES
-- ============================================================================
CREATE TABLE IF NOT EXISTS `modele_semaine_agent` (
    `id_modele` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `id_agent` INT UNSIGNED NOT NULL,
    `nom` VARCHAR(120) NOT NULL,
    `date_creation` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `date_modification` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT `fk_modele_semaine_agent`
        FOREIGN KEY (`id_agent`) REFERENCES `agent`(`id_agent`)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT `uk_modele_semaine_agent_nom` UNIQUE (`id_agent`, `nom`),
    INDEX `idx_modele_semaine_agent` (`id_agent`, `date_modification`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `modele_semaine_creneau` (
    `id_modele_creneau` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `id_modele` INT UNSIGNED NOT NULL,
    `jour_semaine` TINYINT UNSIGNED NOT NULL COMMENT '1=lundi, 5=vendredi',
    `id_structure` INT UNSIGNED NOT NULL,
    `id_motif` INT UNSIGNED NOT NULL,
    `heure_debut` TIME NOT NULL,
    `heure_fin` TIME NOT NULL,

    CONSTRAINT `fk_modele_creneau_modele`
        FOREIGN KEY (`id_modele`) REFERENCES `modele_semaine_agent`(`id_modele`)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT `fk_modele_creneau_structure`
        FOREIGN KEY (`id_structure`) REFERENCES `structure`(`id_structure`)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT `fk_modele_creneau_motif`
        FOREIGN KEY (`id_motif`) REFERENCES `motif_planning`(`id_motif`)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT `chk_modele_creneau_jour` CHECK (`jour_semaine` BETWEEN 1 AND 5),
    CONSTRAINT `chk_modele_creneau_ordre` CHECK (`heure_fin` > `heure_debut`),
    CONSTRAINT `chk_modele_creneau_quart_heure` CHECK (
        MOD(MINUTE(`heure_debut`), 15) = 0
        AND MOD(MINUTE(`heure_fin`), 15) = 0
        AND SECOND(`heure_debut`) = 0
        AND SECOND(`heure_fin`) = 0
    ),

    INDEX `idx_modele_creneau_modele_jour` (`id_modele`, `jour_semaine`, `heure_debut`),
    INDEX `idx_modele_creneau_structure` (`id_structure`),
    INDEX `idx_modele_creneau_motif` (`id_motif`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 10. VACANCES SCOLAIRES
-- ============================================================================
CREATE TABLE IF NOT EXISTS `periode_vacance` (
    `id_periode` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `libelle` VARCHAR(150) NOT NULL,
    `date_debut` DATE NOT NULL,
    `date_fin` DATE NOT NULL COMMENT 'Date inclusive',
    `annee_scolaire` VARCHAR(9) NOT NULL,
    `academie` VARCHAR(100) NOT NULL DEFAULT '',
    `zone` VARCHAR(50) NOT NULL DEFAULT '',
    `source` ENUM('MANUEL', 'DATA_GOUV') NOT NULL DEFAULT 'MANUEL',
    `actif` BOOLEAN NOT NULL DEFAULT TRUE,
    `date_creation` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `date_modification` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT `chk_periode_vacance_dates` CHECK (`date_fin` >= `date_debut`),
    CONSTRAINT `uk_periode_vacance` UNIQUE (`date_debut`, `date_fin`, `annee_scolaire`, `academie`),

    INDEX `idx_periode_vacance_dates` (`date_debut`, `date_fin`),
    INDEX `idx_periode_vacance_annee` (`annee_scolaire`),
    INDEX `idx_periode_vacance_actif_dates` (`actif`, `date_debut`, `date_fin`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 11. PRÉFÉRENCES / INTERDICTIONS / CONTRAINTES AGENT
-- ============================================================================
CREATE TABLE IF NOT EXISTS `agent_structure_souhait` (
    `id_souhait` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `id_agent` INT UNSIGNED NOT NULL,
    `id_structure` INT UNSIGNED NOT NULL,
    `type_souhait` ENUM('PREFERENCE', 'INTERDICTION') NOT NULL,
    `priorite` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `distance_km` DECIMAL(7,2) NULL,
    `commentaire` VARCHAR(500) NULL,
    `actif` BOOLEAN NOT NULL DEFAULT TRUE,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT `fk_souhait_agent`
        FOREIGN KEY (`id_agent`) REFERENCES `agent`(`id_agent`)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT `fk_souhait_structure`
        FOREIGN KEY (`id_structure`) REFERENCES `structure`(`id_structure`)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT `chk_souhait_priorite` CHECK (`priorite` BETWEEN 1 AND 5),
    CONSTRAINT `chk_souhait_distance` CHECK (`distance_km` IS NULL OR `distance_km` >= 0),
    CONSTRAINT `uk_souhait_agent_structure` UNIQUE (`id_agent`, `id_structure`),

    INDEX `idx_souhait_agent_type` (`id_agent`, `type_souhait`, `actif`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `agent_contrainte` (
    `id_contrainte` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `id_agent` INT UNSIGNED NOT NULL,
    `type_contrainte` ENUM('MEDICALE', 'TEMPS_PARTIEL_THERAPEUTIQUE') NOT NULL,
    `date_debut` DATE NOT NULL,
    `date_fin` DATE NULL,
    `quotite_temporaire` DECIMAL(5,2) NULL,
    `maximum_minutes_jour` SMALLINT UNSIGNED NULL,
    `maximum_minutes_semaine` SMALLINT UNSIGNED NULL,
    `interdit_matin` BOOLEAN NOT NULL DEFAULT FALSE,
    `interdit_midi` BOOLEAN NOT NULL DEFAULT FALSE,
    `interdit_soir` BOOLEAN NOT NULL DEFAULT FALSE,
    `commentaire` VARCHAR(1000) NOT NULL,
    `actif` BOOLEAN NOT NULL DEFAULT TRUE,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT `fk_contrainte_agent`
        FOREIGN KEY (`id_agent`) REFERENCES `agent`(`id_agent`)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT `chk_contrainte_dates` CHECK (`date_fin` IS NULL OR `date_fin` >= `date_debut`),
    CONSTRAINT `chk_contrainte_quotite` CHECK (
        `quotite_temporaire` IS NULL OR (`quotite_temporaire` > 0 AND `quotite_temporaire` <= 100)
    ),
    CONSTRAINT `chk_contrainte_max_jour` CHECK (`maximum_minutes_jour` IS NULL OR `maximum_minutes_jour` > 0),
    CONSTRAINT `chk_contrainte_max_semaine` CHECK (`maximum_minutes_semaine` IS NULL OR `maximum_minutes_semaine` > 0),

    INDEX `idx_contrainte_agent_dates` (`id_agent`, `actif`, `date_debut`, `date_fin`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 12. ÉQUIPES PTA
-- ============================================================================
CREATE TABLE IF NOT EXISTS `equipe_pta` (
    `id_equipe` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `id_structure` INT UNSIGNED NOT NULL,
    `type_equipe` ENUM('MERCREDI_PERISCOLAIRE', 'EXTRASCOLAIRE', 'PERISCOLAIRE_SEMAINE') NOT NULL,
    `libelle` VARCHAR(180) NOT NULL,
    `date_debut` DATE NOT NULL,
    `date_fin` DATE NOT NULL,
    `nombre_enfants_moins_6` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `nombre_enfants_6_plus` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `ratio_moins_6` TINYINT UNSIGNED NOT NULL DEFAULT 8,
    `ratio_6_plus` TINYINT UNSIGNED NOT NULL DEFAULT 12,
    `minimum_agents` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `pourcentage_diplomes_min` DECIMAL(5,2) NOT NULL DEFAULT 50.00,
    `statut` ENUM('BROUILLON', 'A_CONTROLER', 'VALIDEE') NOT NULL DEFAULT 'BROUILLON',
    `commentaire` VARCHAR(1000) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT `fk_equipe_pta_structure`
        FOREIGN KEY (`id_structure`) REFERENCES `structure`(`id_structure`)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT `chk_equipe_dates` CHECK (`date_fin` >= `date_debut`),
    CONSTRAINT `chk_equipe_ratios` CHECK (`ratio_moins_6` > 0 AND `ratio_6_plus` > 0),
    CONSTRAINT `chk_equipe_minimum` CHECK (`minimum_agents` > 0),
    CONSTRAINT `chk_equipe_diplomes` CHECK (`pourcentage_diplomes_min` BETWEEN 0 AND 100),

    INDEX `idx_equipe_pta_type_dates` (`type_equipe`, `date_debut`, `date_fin`),
    INDEX `idx_equipe_pta_structure_dates` (`id_structure`, `date_debut`, `date_fin`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `equipe_pta_membre` (
    `id_equipe_membre` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `id_equipe` INT UNSIGNED NOT NULL,
    `id_agent` INT UNSIGNED NOT NULL,
    `fonction_equipe` ENUM('RESPONSABLE', 'ANIMATION', 'RESTAURATION_ENTRETIEN') NOT NULL DEFAULT 'ANIMATION',
    `date_debut` DATE NULL,
    `date_fin` DATE NULL,
    `commentaire` VARCHAR(500) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT `fk_equipe_membre_equipe`
        FOREIGN KEY (`id_equipe`) REFERENCES `equipe_pta`(`id_equipe`)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT `fk_equipe_membre_agent`
        FOREIGN KEY (`id_agent`) REFERENCES `agent`(`id_agent`)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT `chk_equipe_membre_dates`
        CHECK (`date_fin` IS NULL OR `date_debut` IS NULL OR `date_fin` >= `date_debut`),

    CONSTRAINT `uk_equipe_membre` UNIQUE (`id_equipe`, `id_agent`),
    INDEX `idx_equipe_membre_agent` (`id_agent`, `id_equipe`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 13. MATRICE DES DROITS — rôle -> pages, rôle -> permissions
-- ============================================================================
-- Depuis la V7.8, AUCUNE règle de droit n'est écrite en PHP : le contrôleur
-- pose une question à la base, la base répond oui ou non. Modifier un droit se
-- fait ici, en SQL, sans redéploiement. Une permission absente vaut « refusé ».
--
-- L'ENUM porte les quatre rôles dès la création : trois rôles métier plus la
-- DSI, administration technique sans aucun droit fonctionnel (V7.11).
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `acces_role_page` (
    `role` ENUM('AGENT', 'RESPONSABLE_STRUCTURE', 'SERVICE_ENFANCE', 'DSI') NOT NULL,
    `page` VARCHAR(50) NOT NULL,
    `ordre_affichage` SMALLINT UNSIGNED NOT NULL DEFAULT 100,

    PRIMARY KEY (`role`, `page`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `acces_role_permission` (
    `role` ENUM('AGENT', 'RESPONSABLE_STRUCTURE', 'SERVICE_ENFANCE', 'DSI') NOT NULL,
    `permission` VARCHAR(50) NOT NULL,
    `autorise` BOOLEAN NOT NULL DEFAULT FALSE,

    PRIMARY KEY (`role`, `permission`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Le MODIFY rattrape une base créée par une version antérieure de ce fichier,
-- où l'ENUM ne portait que les trois rôles métier. Sans effet sur une base
-- neuve, il rend le script rejouable sur l'existant.
ALTER TABLE `acces_role_page`
    MODIFY COLUMN `role` ENUM('AGENT', 'RESPONSABLE_STRUCTURE', 'SERVICE_ENFANCE', 'DSI') NOT NULL;

ALTER TABLE `acces_role_permission`
    MODIFY COLUMN `role` ENUM('AGENT', 'RESPONSABLE_STRUCTURE', 'SERVICE_ENFANCE', 'DSI') NOT NULL;

-- ----------------------------------------------------------------------------
-- 13.1 PAGES AUTORISÉES PAR RÔLE
-- ----------------------------------------------------------------------------
-- Une page absente de cette table est INATTEINTE : le bouton n'apparaît pas et
-- l'accès direct à l'URL renvoie vers l'accueil du rôle. Ajouter une page à
-- l'application impose donc d'ajouter sa ligne ici.
--
-- « faq » (guide d'utilisation) est ouverte aux trois rôles métier : c'est un
-- secours, pas un droit métier. Son bouton est ancré à droite de la barre, d'où
-- un ordre d'affichage purement indicatif.
--
-- La DSI n'a qu'une page, la sienne : ni saisie, ni référentiel métier.
INSERT INTO `acces_role_page` (`role`, `page`, `ordre_affichage`) VALUES
    ('AGENT', 'agents', 10),
    ('AGENT', 'structures', 20),
    ('AGENT', 'faq', 200),

    ('RESPONSABLE_STRUCTURE', 'planning', 10),
    ('RESPONSABLE_STRUCTURE', 'structures', 20),
    ('RESPONSABLE_STRUCTURE', 'agents', 30),
    ('RESPONSABLE_STRUCTURE', 'coverage', 40),
    ('RESPONSABLE_STRUCTURE', 'faq', 200),

    ('SERVICE_ENFANCE', 'dashboard', 5),
    ('SERVICE_ENFANCE', 'planning', 10),
    ('SERVICE_ENFANCE', 'pending', 20),
    ('SERVICE_ENFANCE', 'coverage', 30),
    ('SERVICE_ENFANCE', 'structures', 40),
    ('SERVICE_ENFANCE', 'agents', 50),
    ('SERVICE_ENFANCE', 'annual', 60),
    ('SERVICE_ENFANCE', 'pta', 70),
    ('SERVICE_ENFANCE', 'teams', 80),
    ('SERVICE_ENFANCE', 'vacations', 90),
    -- 95 et 96 : les deux onglets du groupe « Management » (V7.14) se rangent
    -- juste avant « Administration », dont ils reprennent l'essentiel.
    ('SERVICE_ENFANCE', 'manage_agents', 95),
    ('SERVICE_ENFANCE', 'manage_structures', 96),
    ('SERVICE_ENFANCE', 'administration', 100),
    ('SERVICE_ENFANCE', 'faq', 200),

    ('DSI', 'dsi', 10)
ON DUPLICATE KEY UPDATE `ordre_affichage` = VALUES(`ordre_affichage`);

-- Filet : une page retirée de l'application resterait sinon dans la table et
-- finirait dans le groupe de repêchage « Autres » de la barre de navigation.
-- « management » était une page unique d'une version intermédiaire de la V7.14,
-- remplacée par les deux onglets ci-dessus.
DELETE FROM `acces_role_page` WHERE `page` = 'management';

-- ----------------------------------------------------------------------------
-- 13.2 PERMISSIONS FONCTIONNELLES PAR RÔLE
-- ----------------------------------------------------------------------------
-- Les refus sont écrits explicitement plutôt que laissés absents : une ligne à
-- FALSE documente une décision, une ligne absente laisse croire à un oubli.
--
-- Deux permissions à ne pas confondre :
--   * can_manage_access       : TOUS les comptes, privilégiés compris — DSI seule ;
--   * can_manage_field_access : les jetons DE TERRAIN, agents et responsables de
--                               lieu uniquement — Service Enfance (V7.27).
-- Un compte SERVICE_ENFANCE créé par le Service Enfance serait une
-- auto-promotion, un compte DSI lui ouvrirait le journal de ses propres
-- actions : la borne est doublée en PHP (AccessAccountController::ROLES_DELEGUES)
-- et ne doit être élargie d'aucun côté.
INSERT INTO `acces_role_permission` (`role`, `permission`, `autorise`) VALUES
    ('AGENT', 'read_only', TRUE),
    ('AGENT', 'can_edit_draft', FALSE),
    ('AGENT', 'can_edit_cross_structure', FALSE),
    ('AGENT', 'can_validate', FALSE),
    ('AGENT', 'can_reopen', FALSE),
    ('AGENT', 'can_use_templates', FALSE),
    ('AGENT', 'can_copy_full_week', FALSE),
    ('AGENT', 'can_view_pending', FALSE),
    ('AGENT', 'can_manage_coverage', FALSE),
    ('AGENT', 'can_manage_agents', FALSE),
    ('AGENT', 'can_manage_structures', FALSE),
    ('AGENT', 'can_manage_vacations', FALSE),
    ('AGENT', 'can_manage_teams', FALSE),
    ('AGENT', 'can_manage_pta', FALSE),
    ('AGENT', 'can_forecast_next_year', FALSE),
    ('AGENT', 'can_manage_access', FALSE),
    ('AGENT', 'can_manage_field_access', FALSE),
    ('AGENT', 'can_read_journal', FALSE),

    ('RESPONSABLE_STRUCTURE', 'read_only', FALSE),
    ('RESPONSABLE_STRUCTURE', 'can_edit_draft', TRUE),
    ('RESPONSABLE_STRUCTURE', 'can_edit_cross_structure', FALSE),
    ('RESPONSABLE_STRUCTURE', 'can_validate', FALSE),
    ('RESPONSABLE_STRUCTURE', 'can_reopen', TRUE),
    ('RESPONSABLE_STRUCTURE', 'can_use_templates', FALSE),
    ('RESPONSABLE_STRUCTURE', 'can_copy_full_week', FALSE),
    ('RESPONSABLE_STRUCTURE', 'can_view_pending', FALSE),
    ('RESPONSABLE_STRUCTURE', 'can_manage_coverage', FALSE),
    ('RESPONSABLE_STRUCTURE', 'can_manage_agents', FALSE),
    ('RESPONSABLE_STRUCTURE', 'can_manage_structures', FALSE),
    ('RESPONSABLE_STRUCTURE', 'can_manage_vacations', FALSE),
    ('RESPONSABLE_STRUCTURE', 'can_manage_teams', FALSE),
    ('RESPONSABLE_STRUCTURE', 'can_manage_pta', FALSE),
    ('RESPONSABLE_STRUCTURE', 'can_forecast_next_year', FALSE),
    ('RESPONSABLE_STRUCTURE', 'can_manage_access', FALSE),
    ('RESPONSABLE_STRUCTURE', 'can_manage_field_access', FALSE),
    ('RESPONSABLE_STRUCTURE', 'can_read_journal', FALSE),

    ('SERVICE_ENFANCE', 'read_only', FALSE),
    ('SERVICE_ENFANCE', 'can_edit_draft', TRUE),
    ('SERVICE_ENFANCE', 'can_edit_cross_structure', TRUE),
    ('SERVICE_ENFANCE', 'can_validate', TRUE),
    ('SERVICE_ENFANCE', 'can_reopen', TRUE),
    ('SERVICE_ENFANCE', 'can_use_templates', TRUE),
    ('SERVICE_ENFANCE', 'can_copy_full_week', TRUE),
    ('SERVICE_ENFANCE', 'can_view_pending', TRUE),
    ('SERVICE_ENFANCE', 'can_manage_coverage', TRUE),
    ('SERVICE_ENFANCE', 'can_manage_agents', TRUE),
    ('SERVICE_ENFANCE', 'can_manage_structures', TRUE),
    ('SERVICE_ENFANCE', 'can_manage_vacations', TRUE),
    ('SERVICE_ENFANCE', 'can_manage_teams', TRUE),
    ('SERVICE_ENFANCE', 'can_manage_pta', TRUE),
    ('SERVICE_ENFANCE', 'can_forecast_next_year', TRUE),
    ('SERVICE_ENFANCE', 'can_manage_access', FALSE),
    ('SERVICE_ENFANCE', 'can_manage_field_access', TRUE),
    ('SERVICE_ENFANCE', 'can_read_journal', FALSE),

    ('DSI', 'read_only', FALSE),
    ('DSI', 'can_edit_draft', FALSE),
    ('DSI', 'can_edit_cross_structure', FALSE),
    ('DSI', 'can_validate', FALSE),
    ('DSI', 'can_reopen', FALSE),
    ('DSI', 'can_use_templates', FALSE),
    ('DSI', 'can_copy_full_week', FALSE),
    ('DSI', 'can_view_pending', FALSE),
    ('DSI', 'can_manage_coverage', FALSE),
    ('DSI', 'can_manage_agents', FALSE),
    ('DSI', 'can_manage_structures', FALSE),
    ('DSI', 'can_manage_vacations', FALSE),
    ('DSI', 'can_manage_teams', FALSE),
    ('DSI', 'can_manage_pta', FALSE),
    ('DSI', 'can_forecast_next_year', FALSE),
    ('DSI', 'can_manage_access', TRUE),
    -- Inutile à la DSI : can_manage_access lui ouvre déjà tous les comptes.
    ('DSI', 'can_manage_field_access', FALSE),
    ('DSI', 'can_read_journal', TRUE)
ON DUPLICATE KEY UPDATE `autorise` = VALUES(`autorise`);

-- ============================================================================
-- 14. COMPTES D'ACCÈS ET JOURNAL D'ACTIVITÉ
-- ============================================================================
-- Le jeton complet n'est JAMAIS stocké : la base ne garde qu'une empreinte
-- SHA-256 et les huit premiers caractères, qui servent uniquement à reconnaître
-- une ligne dans la liste. Un jeton perdu ne se retrouve pas, il se régénère.
-- Une fuite de ce dump ne donne donc aucun accès.
--
-- L'empreinte n'est pas salée, et c'est délibéré : le jeton est tiré au sort sur
-- 120 bits par le serveur, il n'est pas devinable par force brute comme le
-- serait un mot de passe choisi par un humain. L'absence de sel est ce qui
-- permet de retrouver le compte en une lecture d'index à la connexion.
--
-- Le MOT DE PASSE (V7.12), lui, est haché par password_hash() (bcrypt) : choisi
-- par un humain, donc attaquable par dictionnaire, et c'est précisément ce que
-- le hachage lent rend coûteux. Il n'est exigé que des comptes SERVICE_ENFANCE
-- et DSI — ceux qui valident et ceux qui distribuent les accès. NULL signifie
-- « pas encore défini » : c'est l'état d'un compte neuf comme d'un compte
-- réinitialisé par la DSI, et un compte privilégié dans cet état ne peut pas
-- ouvrir de session.
--
-- id_agent / id_structure sont UNIQUE tout en étant NULLables : MariaDB tolère
-- autant de NULL que voulu dans un index unique. Un agent ne peut donc avoir
-- qu'un seul compte, un lieu qu'un seul compte responsable, tandis que les
-- comptes SERVICE_ENFANCE et DSI (les deux colonnes à NULL) sont libres en
-- nombre.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `compte_acces` (
    `id_compte` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `role` ENUM('AGENT', 'RESPONSABLE_STRUCTURE', 'SERVICE_ENFANCE', 'DSI') NOT NULL,
    `libelle` VARCHAR(180) NOT NULL COMMENT 'Titulaire du jeton, tel qu''affiché dans la liste',
    `id_agent` INT UNSIGNED NULL COMMENT 'Obligatoire pour un compte AGENT',
    `id_structure` INT UNSIGNED NULL COMMENT 'Obligatoire pour un compte RESPONSABLE_STRUCTURE',
    `jeton_empreinte` CHAR(64) NOT NULL COMMENT 'SHA-256 hexadécimal du jeton',
    `jeton_prefixe` VARCHAR(12) NOT NULL COMMENT 'Début du jeton, pour le reconnaître sans le révéler',
    `mot_de_passe_hash` VARCHAR(255) NULL
        COMMENT 'password_hash() du mot de passe ; NULL = à définir à la prochaine connexion',
    `date_mot_de_passe` DATETIME NULL
        COMMENT 'Dernière définition ou modification du mot de passe par le titulaire',
    `actif` BOOLEAN NOT NULL DEFAULT TRUE,
    `commentaire` VARCHAR(255) NULL,
    `date_creation` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `date_rotation` DATETIME NULL COMMENT 'Dernière régénération du jeton',
    `date_derniere_connexion` DATETIME NULL,
    `nombre_connexions` INT UNSIGNED NOT NULL DEFAULT 0,

    CONSTRAINT `uk_compte_empreinte` UNIQUE (`jeton_empreinte`),
    CONSTRAINT `uk_compte_agent` UNIQUE (`id_agent`),
    CONSTRAINT `uk_compte_structure` UNIQUE (`id_structure`),
    INDEX `idx_compte_role_actif` (`role`, `actif`),

    CONSTRAINT `fk_compte_agent` FOREIGN KEY (`id_agent`)
        REFERENCES `agent` (`id_agent`) ON DELETE CASCADE,
    CONSTRAINT `fk_compte_structure` FOREIGN KEY (`id_structure`)
        REFERENCES `structure` (`id_structure`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Rattrapage d'une base créée avant la V7.12 : sans effet sur une base neuve,
-- où le CREATE TABLE ci-dessus porte déjà les deux colonnes.
--
-- La forme évidente serait `ALTER TABLE … ADD COLUMN IF NOT EXISTS …`, mais
-- `IF NOT EXISTS` sur un ADD COLUMN est une EXTENSION MARIADB : MySQL la refuse
-- avec une erreur 1064 de syntaxe. Le test d'existence passe donc par
-- information_schema et une requête préparée, comprise par les deux SGBD.
-- `DO 0` est le non-opérant : contrairement à `SELECT 1`, il ne produit pas de
-- résultat parasite dans phpMyAdmin.
SET @ajout_mot_de_passe := IF(
    (SELECT COUNT(*)
       FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME   = 'compte_acces'
        AND COLUMN_NAME  = 'mot_de_passe_hash') > 0,
    'DO 0',
    'ALTER TABLE `compte_acces`
         ADD COLUMN `mot_de_passe_hash` VARCHAR(255) NULL
             COMMENT ''password_hash() du mot de passe ; NULL = à définir à la prochaine connexion'',
         ADD COLUMN `date_mot_de_passe` DATETIME NULL
             COMMENT ''Dernière définition ou modification du mot de passe par le titulaire'''
);
PREPARE requete_ajout_mot_de_passe FROM @ajout_mot_de_passe;
EXECUTE requete_ajout_mot_de_passe;
DEALLOCATE PREPARE requete_ajout_mot_de_passe;
SET @ajout_mot_de_passe := NULL;

-- ----------------------------------------------------------------------------
-- 14.1 COMPTE DSI D'INSTALLATION
-- ----------------------------------------------------------------------------
-- Problème de l'œuf et de la poule : les jetons ne sont distribuables que par la
-- DSI, qui a elle-même besoin d'un jeton pour entrer. Cette ligne est la porte
-- d'entrée initiale.
--
--     JETON D'INSTALLATION : PTA-INSTALLATION-DSI
--
-- Il est PUBLIC — il est écrit ici, dans un fichier versionné. La page DSI
-- affiche un avertissement rouge tant que ce compte porte encore cette
-- empreinte : LE RÉGÉNÉRER EST LA PREMIÈRE ACTION À FAIRE APRÈS L'INSTALLATION.
--
-- Son mot de passe est laissé à NULL : la DSI en choisit un à sa première
-- connexion. La DSI peut effacer un mot de passe, jamais en poser un — sans quoi
-- elle connaîtrait celui de tout le monde.
--
-- L'identifiant 1 est forcé pour qu'un rejeu de ce script ne recrée pas un
-- second compte d'installation après régénération du jeton (l'empreinte, elle,
-- aura changé). Supprimer la ligne 1 puis rejouer est d'ailleurs la procédure de
-- secours documentée en cas de perte du jeton DSI.
INSERT INTO `compte_acces`
    (`id_compte`, `role`, `libelle`, `jeton_empreinte`, `jeton_prefixe`, `commentaire`)
VALUES
    (1, 'DSI', 'Compte DSI d''installation',
     '7f61ffc8efed4ed46d581a98286115ad03bb0122ffc70db3398584348aca431d',
     'PTA-INSTALL',
     'Jeton d''installation public : à régénérer immédiatement.')
ON DUPLICATE KEY UPDATE `id_compte` = `id_compte`;

-- ----------------------------------------------------------------------------
-- 14.2 JOURNAL D'ACTIVITÉ
-- ----------------------------------------------------------------------------
-- Alimenté exclusivement par App\Core\Journal, jamais par des déclencheurs SQL :
-- le journal doit nommer l'UTILISATEUR à l'origine de l'action, information que
-- la base n'a pas (la connexion MariaDB est la même pour tout le monde).
--
-- Sont tracés : les connexions et déconnexions, toutes les écritures en base et
-- tous les refus d'accès. Les lectures ne le sont pas — c'est un choix de
-- volume : elles représenteraient l'essentiel des lignes pour une valeur
-- d'audit très faible.
--
-- L'acteur est FIGÉ au moment de l'action (rôle, libellé) : renommer un agent ou
-- supprimer son compte ne doit pas réécrire l'histoire. C'est aussi pourquoi la
-- clé étrangère est en ON DELETE SET NULL et non en CASCADE.
--
-- Cette table sert également de compteur au frein anti-force-brute de la
-- connexion (JournalModel::echecsRecents()) : les actions JETON_REFUSE et
-- MOT_DE_PASSE_REFUSE sont comptées par adresse IP. Les renommer désarmerait ce
-- frein silencieusement.
CREATE TABLE IF NOT EXISTS `journal_activite` (
    `id_journal` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `date_evenement` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `categorie` ENUM('CONNEXION', 'ECRITURE', 'ACCES_REFUSE', 'ADMINISTRATION', 'SYSTEME') NOT NULL,
    `action` VARCHAR(60) NOT NULL COMMENT 'Code stable de l''action, ex. PLANNING_VALIDE',
    `resultat` ENUM('SUCCES', 'REFUS', 'ERREUR') NOT NULL DEFAULT 'SUCCES',
    `id_compte` INT UNSIGNED NULL,
    `role` VARCHAR(30) NULL COMMENT 'Rôle au moment de l''action, figé',
    `acteur` VARCHAR(180) NULL COMMENT 'Libellé de l''utilisateur au moment de l''action, figé',
    `cible_table` VARCHAR(64) NULL COMMENT 'Table principale touchée par l''écriture',
    `cible_id` INT UNSIGNED NULL,
    `description` VARCHAR(500) NULL,
    `contexte` JSON NULL COMMENT 'Détail structuré, ex. semaine et lieu concernés',
    `adresse_ip` VARCHAR(45) NULL,
    `agent_utilisateur` VARCHAR(255) NULL,

    INDEX `idx_journal_date` (`date_evenement`),
    INDEX `idx_journal_categorie` (`categorie`, `date_evenement`),
    INDEX `idx_journal_compte` (`id_compte`, `date_evenement`),
    INDEX `idx_journal_cible` (`cible_table`, `cible_id`),
    INDEX `idx_journal_ip` (`adresse_ip`, `date_evenement`),

    CONSTRAINT `fk_journal_compte` FOREIGN KEY (`id_compte`)
        REFERENCES `compte_acces` (`id_compte`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================================
-- 15. VUES DE PILOTAGE MÉTIER
-- ============================================================================

CREATE OR REPLACE VIEW `v_planning_agent_semaine` AS
SELECT
    `p`.`id_planning`,
    `p`.`id_agent`,
    `a`.`matricule`,
    `a`.`nom`,
    `a`.`prenom`,
    CONCAT(`a`.`prenom`, ' ', `a`.`nom`) AS `agent`,
    `p`.`id_semaine`,
    `s`.`annee`,
    `s`.`numero_semaine`,
    `s`.`date_debut`,
    `s`.`date_fin`,
    `p`.`id_structure`,
    `st`.`code` AS `code_structure`,
    `st`.`nom` AS `lieu_affectation`,
    `st`.`type_affectation`,
    `p`.`statut`,
    `p`.`date_creation`,
    `p`.`date_modification`,
    `p`.`date_validation`,
    COUNT(`c`.`id_creneau`) AS `nombre_creneaux`,
    COALESCE(SUM(
        TIMESTAMPDIFF(
            MINUTE,
            CONCAT(`c`.`date_jour`, ' ', `c`.`heure_debut`),
            CONCAT(`c`.`date_jour`, ' ', `c`.`heure_fin`)
        )
    ), 0) AS `minutes_planifiees`,
    COALESCE(SUM(
        CASE WHEN `m`.`compte_dans_quota` = TRUE THEN
            TIMESTAMPDIFF(
                MINUTE,
                CONCAT(`c`.`date_jour`, ' ', `c`.`heure_debut`),
                CONCAT(`c`.`date_jour`, ' ', `c`.`heure_fin`)
            )
        ELSE 0 END
    ), 0) AS `minutes_decomptees_quota`,
    ROUND(COALESCE(SUM(
        TIMESTAMPDIFF(
            MINUTE,
            CONCAT(`c`.`date_jour`, ' ', `c`.`heure_debut`),
            CONCAT(`c`.`date_jour`, ' ', `c`.`heure_fin`)
        )
    ), 0) / 60, 2) AS `heures_planifiees`
FROM `planning` `p`
INNER JOIN `agent` `a` ON `a`.`id_agent` = `p`.`id_agent`
INNER JOIN `semaine` `s` ON `s`.`id_semaine` = `p`.`id_semaine`
INNER JOIN `structure` `st` ON `st`.`id_structure` = `p`.`id_structure`
LEFT JOIN `creneau_horaire` `c` ON `c`.`id_planning` = `p`.`id_planning`
LEFT JOIN `motif_planning` `m` ON `m`.`id_motif` = `c`.`id_motif`
GROUP BY
    `p`.`id_planning`, `p`.`id_agent`, `a`.`matricule`, `a`.`nom`, `a`.`prenom`,
    `p`.`id_semaine`, `s`.`annee`, `s`.`numero_semaine`, `s`.`date_debut`, `s`.`date_fin`,
    `p`.`id_structure`, `st`.`code`, `st`.`nom`, `st`.`type_affectation`,
    `p`.`statut`, `p`.`date_creation`, `p`.`date_modification`, `p`.`date_validation`;

-- Quota annuel = année CIVILE.
CREATE OR REPLACE VIEW `v_suivi_quota_annuel` AS
SELECT
    `q`.`id_quota`,
    `q`.`id_agent`,
    `a`.`matricule`,
    `a`.`nom`,
    `a`.`prenom`,
    `a`.`date_debut_contrat`,
    `a`.`date_fin_contrat`,
    `q`.`annee`,
    `q`.`quotite_travail`,
    `q`.`quota_reference_minutes`,
    `q`.`quota_cible_minutes`,
    STR_TO_DATE(CONCAT(`q`.`annee`, '-01-01'), '%Y-%m-%d') AS `date_debut_periode`,
    STR_TO_DATE(CONCAT(`q`.`annee`, '-12-31'), '%Y-%m-%d') AS `date_fin_periode`,
    ROUND(`q`.`quota_reference_minutes` / 60, 2) AS `quota_reference_heures`,
    ROUND(`q`.`quota_cible_minutes` / 60, 2) AS `quota_cible_heures`,

    COALESCE(SUM(CASE
        WHEN `p`.`statut` = 'VALIDE' AND `m`.`compte_dans_quota` = TRUE THEN
            TIMESTAMPDIFF(
                MINUTE,
                CONCAT(`c`.`date_jour`, ' ', `c`.`heure_debut`),
                CONCAT(`c`.`date_jour`, ' ', `c`.`heure_fin`)
            )
        ELSE 0 END
    ), 0) AS `minutes_valides`,

    COALESCE(SUM(CASE
        WHEN `p`.`statut` = 'BROUILLON' AND `m`.`compte_dans_quota` = TRUE THEN
            TIMESTAMPDIFF(
                MINUTE,
                CONCAT(`c`.`date_jour`, ' ', `c`.`heure_debut`),
                CONCAT(`c`.`date_jour`, ' ', `c`.`heure_fin`)
            )
        ELSE 0 END
    ), 0) AS `minutes_brouillon`,

    COALESCE(SUM(CASE
        WHEN `m`.`compte_dans_quota` = TRUE THEN
            TIMESTAMPDIFF(
                MINUTE,
                CONCAT(`c`.`date_jour`, ' ', `c`.`heure_debut`),
                CONCAT(`c`.`date_jour`, ' ', `c`.`heure_fin`)
            )
        ELSE 0 END
    ), 0) AS `minutes_affectees`,

    `q`.`quota_cible_minutes` - COALESCE(SUM(CASE
        WHEN `m`.`compte_dans_quota` = TRUE THEN
            TIMESTAMPDIFF(
                MINUTE,
                CONCAT(`c`.`date_jour`, ' ', `c`.`heure_debut`),
                CONCAT(`c`.`date_jour`, ' ', `c`.`heure_fin`)
            )
        ELSE 0 END
    ), 0) AS `minutes_restantes`
FROM `quota_agent_annuel` `q`
INNER JOIN `agent` `a` ON `a`.`id_agent` = `q`.`id_agent`
LEFT JOIN `planning` `p` ON `p`.`id_agent` = `q`.`id_agent`
LEFT JOIN `creneau_horaire` `c`
    ON `c`.`id_planning` = `p`.`id_planning`
   AND `c`.`date_jour` BETWEEN
        STR_TO_DATE(CONCAT(`q`.`annee`, '-01-01'), '%Y-%m-%d')
        AND STR_TO_DATE(CONCAT(`q`.`annee`, '-12-31'), '%Y-%m-%d')
LEFT JOIN `motif_planning` `m` ON `m`.`id_motif` = `c`.`id_motif`
GROUP BY
    `q`.`id_quota`, `q`.`id_agent`, `a`.`matricule`, `a`.`nom`, `a`.`prenom`,
    `a`.`date_debut_contrat`, `a`.`date_fin_contrat`,
    `q`.`annee`, `q`.`quotite_travail`,
    `q`.`quota_reference_minutes`, `q`.`quota_cible_minutes`;

CREATE OR REPLACE VIEW `v_plannings_en_attente_validation` AS
SELECT
    `p`.`id_planning`,
    `p`.`id_agent`,
    `a`.`matricule`,
    `a`.`nom`,
    `a`.`prenom`,
    CONCAT(`a`.`prenom`, ' ', `a`.`nom`) AS `agent`,
    `p`.`id_structure`,
    `st`.`code` AS `code_structure`,
    `st`.`nom` AS `lieu_affectation`,
    `p`.`id_semaine`,
    `s`.`annee`,
    `s`.`numero_semaine`,
    `s`.`date_debut`,
    `s`.`date_fin`,
    `p`.`date_modification`,
    COUNT(`c`.`id_creneau`) AS `nombre_creneaux`,
    COALESCE(SUM(
        TIMESTAMPDIFF(
            MINUTE,
            CONCAT(`c`.`date_jour`, ' ', `c`.`heure_debut`),
            CONCAT(`c`.`date_jour`, ' ', `c`.`heure_fin`)
        )
    ), 0) AS `minutes_totales`,
    COALESCE(SUM(CASE
        WHEN `m`.`compte_dans_quota` = TRUE THEN
            TIMESTAMPDIFF(
                MINUTE,
                CONCAT(`c`.`date_jour`, ' ', `c`.`heure_debut`),
                CONCAT(`c`.`date_jour`, ' ', `c`.`heure_fin`)
            )
        ELSE 0 END
    ), 0) AS `minutes_decomptees`
FROM `planning` `p`
INNER JOIN `agent` `a` ON `a`.`id_agent` = `p`.`id_agent`
INNER JOIN `structure` `st` ON `st`.`id_structure` = `p`.`id_structure`
INNER JOIN `semaine` `s` ON `s`.`id_semaine` = `p`.`id_semaine`
INNER JOIN `creneau_horaire` `c` ON `c`.`id_planning` = `p`.`id_planning`
INNER JOIN `motif_planning` `m` ON `m`.`id_motif` = `c`.`id_motif`
WHERE `p`.`statut` = 'BROUILLON'
GROUP BY
    `p`.`id_planning`, `p`.`id_agent`, `a`.`matricule`, `a`.`nom`, `a`.`prenom`,
    `p`.`id_structure`, `st`.`code`, `st`.`nom`,
    `p`.`id_semaine`, `s`.`annee`, `s`.`numero_semaine`, `s`.`date_debut`, `s`.`date_fin`,
    `p`.`date_modification`;

-- Uniquement les PTA correspondant à une année civile complète.
CREATE OR REPLACE VIEW `v_pta_annuel_synthese` AS
SELECT
    `pa`.`id_pta`,
    `pa`.`id_agent`,
    `a`.`matricule`,
    `a`.`nom`,
    `a`.`prenom`,
    `po`.`code` AS `poste_code`,
    `po`.`libelle` AS `poste_libelle`,
    `a`.`type_contrat`,
    `pa`.`date_debut`,
    `pa`.`date_fin`,
    `pa`.`quotite_travail`,
    `pa`.`quota_cible_minutes`,
    COALESCE(SUM(CASE
        WHEN `mp`.`compte_dans_quota` = TRUE THEN
            TIMESTAMPDIFF(
                MINUTE,
                CONCAT(`ch`.`date_jour`, ' ', `ch`.`heure_debut`),
                CONCAT(`ch`.`date_jour`, ' ', `ch`.`heure_fin`)
            )
        ELSE 0 END
    ), 0) AS `minutes_decomptees`,
    `pa`.`quota_cible_minutes` - COALESCE(SUM(CASE
        WHEN `mp`.`compte_dans_quota` = TRUE THEN
            TIMESTAMPDIFF(
                MINUTE,
                CONCAT(`ch`.`date_jour`, ' ', `ch`.`heure_debut`),
                CONCAT(`ch`.`date_jour`, ' ', `ch`.`heure_fin`)
            )
        ELSE 0 END
    ), 0) AS `minutes_restantes`,
    `pa`.`statut`
FROM `pta_annuel` `pa`
INNER JOIN `agent` `a` ON `a`.`id_agent` = `pa`.`id_agent`
LEFT JOIN `poste_agent` `po` ON `po`.`id_poste` = `a`.`id_poste`
LEFT JOIN `planning` `p` ON `p`.`id_agent` = `pa`.`id_agent`
LEFT JOIN `creneau_horaire` `ch`
    ON `ch`.`id_planning` = `p`.`id_planning`
   AND `ch`.`date_jour` BETWEEN `pa`.`date_debut` AND `pa`.`date_fin`
LEFT JOIN `motif_planning` `mp` ON `mp`.`id_motif` = `ch`.`id_motif`
WHERE `pa`.`date_debut` = STR_TO_DATE(CONCAT(YEAR(`pa`.`date_debut`), '-01-01'), '%Y-%m-%d')
  AND `pa`.`date_fin` = STR_TO_DATE(CONCAT(YEAR(`pa`.`date_debut`), '-12-31'), '%Y-%m-%d')
GROUP BY
    `pa`.`id_pta`, `pa`.`id_agent`, `a`.`matricule`, `a`.`nom`, `a`.`prenom`,
    `po`.`code`, `po`.`libelle`, `a`.`type_contrat`,
    `pa`.`date_debut`, `pa`.`date_fin`, `pa`.`quotite_travail`,
    `pa`.`quota_cible_minutes`, `pa`.`statut`;

CREATE OR REPLACE VIEW `v_equipe_pta_controle` AS
SELECT
    `e`.`id_equipe`,
    `e`.`libelle`,
    `e`.`type_equipe`,
    `e`.`id_structure`,
    `s`.`nom` AS `structure_nom`,
    `e`.`date_debut`,
    `e`.`date_fin`,
    `e`.`nombre_enfants_moins_6`,
    `e`.`nombre_enfants_6_plus`,
    GREATEST(
        `e`.`minimum_agents`,
        CEIL(`e`.`nombre_enfants_moins_6` / `e`.`ratio_moins_6`)
        + CEIL(`e`.`nombre_enfants_6_plus` / `e`.`ratio_6_plus`)
    ) AS `agents_requis`,
    COUNT(`em`.`id_agent`) AS `agents_affectes`,
    COALESCE(SUM(CASE WHEN `a`.`est_diplome` = TRUE THEN 1 ELSE 0 END), 0) AS `agents_diplomes`,
    CASE
        WHEN COUNT(`em`.`id_agent`) = 0 THEN 0
        ELSE ROUND(
            100 * SUM(CASE WHEN `a`.`est_diplome` = TRUE THEN 1 ELSE 0 END)
            / COUNT(`em`.`id_agent`),
            2
        )
    END AS `pourcentage_diplomes`,
    `e`.`pourcentage_diplomes_min`,
    COALESCE(SUM(CASE WHEN `em`.`fonction_equipe` = 'RESPONSABLE' THEN 1 ELSE 0 END), 0) AS `responsables`,
    `e`.`statut`
FROM `equipe_pta` `e`
INNER JOIN `structure` `s` ON `s`.`id_structure` = `e`.`id_structure`
LEFT JOIN `equipe_pta_membre` `em` ON `em`.`id_equipe` = `e`.`id_equipe`
LEFT JOIN `agent` `a` ON `a`.`id_agent` = `em`.`id_agent`
GROUP BY
    `e`.`id_equipe`, `e`.`libelle`, `e`.`type_equipe`, `e`.`id_structure`, `s`.`nom`,
    `e`.`date_debut`, `e`.`date_fin`,
    `e`.`nombre_enfants_moins_6`, `e`.`nombre_enfants_6_plus`,
    `e`.`ratio_moins_6`, `e`.`ratio_6_plus`, `e`.`minimum_agents`,
    `e`.`pourcentage_diplomes_min`, `e`.`statut`;

-- ============================================================================
-- 16. VUES DE CONTRÔLE D'ACCÈS
-- ============================================================================
-- Les dix vues ci-dessous sont vérifiées au démarrage par SchemaModel et par
-- docker/db/apply-migrations.sh : si l'une manque, l'application REFUSE de
-- démarrer plutôt que de tourner sans contrôle de périmètre ni journal. Ce
-- fail-closed est volontaire, ne pas l'assouplir.
-- ----------------------------------------------------------------------------

-- 16.1 Matrice des droits ----------------------------------------------------
CREATE OR REPLACE VIEW `v_acces_role_page` AS
SELECT `role`, `page`, `ordre_affichage`
FROM `acces_role_page`;

CREATE OR REPLACE VIEW `v_acces_role_permission` AS
SELECT `role`, `permission`, `autorise`
FROM `acces_role_permission`;

-- 16.2 Identités -------------------------------------------------------------
-- Identité complète d'un agent actif : sert à construire le bandeau de profil.
-- Un agent inactif n'apparaît jamais ici, sa session se referme donc d'elle-même
-- à la requête suivante (Access relit le compte à CHAQUE requête).
CREATE OR REPLACE VIEW `v_acces_identite_agent` AS
SELECT
    `a`.`id_agent`,
    `a`.`matricule`,
    `a`.`nom`,
    `a`.`prenom`,
    `a`.`id_structure`,
    `s`.`nom`  AS `structure_nom`,
    `s`.`code` AS `structure_code`
FROM `agent` `a`
LEFT JOIN `structure` `s`
       ON `s`.`id_structure` = `a`.`id_structure`
      AND `s`.`actif` = TRUE
WHERE `a`.`actif` = TRUE;

-- Lieux d'affectation portés par un compte responsable de structure.
CREATE OR REPLACE VIEW `v_acces_identite_structure` AS
SELECT
    `s`.`id_structure`,
    `s`.`code`,
    `s`.`nom`,
    `s`.`type_affectation`
FROM `structure` `s`
WHERE `s`.`actif` = TRUE;

-- 16.3 Périmètres ------------------------------------------------------------
-- Quels agents un lieu d'affectation a-t-il le droit de consulter ?
-- Deux origines : le rattachement principal, ou une planification effective sur
-- ce lieu — un agent peut être planifié ailleurs que sur son rattachement.
CREATE OR REPLACE VIEW `v_acces_agent_par_structure` AS
SELECT DISTINCT
    `a`.`id_agent`,
    `a`.`id_structure` AS `id_structure_autorisee`,
    'RATTACHEMENT' AS `origine`
FROM `agent` `a`
WHERE `a`.`actif` = TRUE
  AND `a`.`id_structure` IS NOT NULL
UNION
SELECT DISTINCT
    `a`.`id_agent`,
    `p`.`id_structure` AS `id_structure_autorisee`,
    'PLANIFICATION' AS `origine`
FROM `agent` `a`
INNER JOIN `planning` `p` ON `p`.`id_agent` = `a`.`id_agent`
WHERE `a`.`actif` = TRUE;

-- Périmètre d'un créneau : à quel lieu et à quel planning appartient-il ?
-- Utilisé avant toute modification ou suppression d'affectation.
CREATE OR REPLACE VIEW `v_acces_perimetre_creneau` AS
SELECT
    `c`.`id_creneau`,
    `c`.`id_planning`,
    `p`.`id_structure`,
    `p`.`id_agent`,
    `p`.`statut`          AS `statut_planning`,
    `s`.`nom`             AS `structure_nom`
FROM `creneau_horaire` `c`
INNER JOIN `planning`  `p` ON `p`.`id_planning`  = `c`.`id_planning`
INNER JOIN `structure` `s` ON `s`.`id_structure` = `p`.`id_structure`;

-- Périmètre d'un planning : utilisé avant validation ou réouverture.
CREATE OR REPLACE VIEW `v_acces_perimetre_planning` AS
SELECT
    `p`.`id_planning`,
    `p`.`id_structure`,
    `p`.`id_agent`,
    `p`.`statut`          AS `statut_planning`,
    `s`.`nom`             AS `structure_nom`,
    `a`.`nom`             AS `agent_nom`,
    `a`.`prenom`          AS `agent_prenom`
FROM `planning` `p`
INNER JOIN `structure` `s` ON `s`.`id_structure` = `p`.`id_structure`
INNER JOIN `agent`     `a` ON `a`.`id_agent`     = `p`.`id_agent`;

-- 16.4 Comptes et journal ----------------------------------------------------
-- Le PHP interroge cette vue et non la table : l'identité réelle derrière un
-- compte, son activité et la règle « qui doit avoir un mot de passe » sont
-- résolues en SQL, une seule fois, au même endroit.
CREATE OR REPLACE VIEW `v_acces_compte` AS
SELECT
    `c`.`id_compte`,
    `c`.`role`,
    `c`.`libelle`,
    `c`.`id_agent`,
    `c`.`id_structure`,
    `c`.`jeton_empreinte`,
    `c`.`jeton_prefixe`,
    `c`.`actif`,
    `c`.`commentaire`,
    `c`.`date_creation`,
    `c`.`date_rotation`,
    `c`.`date_derniere_connexion`,
    `c`.`nombre_connexions`,
    `c`.`mot_de_passe_hash`,
    `c`.`date_mot_de_passe`,
    -- Les deux rôles qui engagent l'application : celui qui valide les plannings
    -- de toute la collectivité, et celui qui distribue les accès.
    CASE WHEN `c`.`role` IN ('SERVICE_ENFANCE', 'DSI') THEN 1 ELSE 0 END AS `mot_de_passe_requis`,
    CASE WHEN `c`.`mot_de_passe_hash` IS NULL THEN 0 ELSE 1 END AS `mot_de_passe_defini`,
    `a`.`matricule`,
    `a`.`nom`          AS `agent_nom`,
    `a`.`prenom`       AS `agent_prenom`,
    `s`.`code`         AS `structure_code`,
    `s`.`nom`          AS `structure_nom`,
    -- Libellé affiché : l'identité VIVE pour un agent ou un lieu (un agent
    -- renommé doit apparaître sous son nouveau nom), le libellé saisi pour les
    -- comptes Service Enfance et DSI, qui ne pointent vers rien.
    CASE `c`.`role`
        WHEN 'AGENT' THEN CONCAT(`a`.`prenom`, ' ', `a`.`nom`)
        WHEN 'RESPONSABLE_STRUCTURE' THEN `s`.`nom`
        ELSE `c`.`libelle`
    END AS `identite_libelle`,
    -- Un compte reste utilisable tant que l'identité qu'il porte est active :
    -- désactiver un agent coupe son accès sans avoir à toucher à son compte.
    CASE `c`.`role`
        WHEN 'AGENT' THEN COALESCE(`a`.`actif`, 0)
        WHEN 'RESPONSABLE_STRUCTURE' THEN COALESCE(`s`.`actif`, 0)
        ELSE 1
    END AS `identite_active`
FROM `compte_acces` `c`
LEFT JOIN `agent` `a` ON `a`.`id_agent` = `c`.`id_agent`
LEFT JOIN `structure` `s` ON `s`.`id_structure` = `c`.`id_structure`;

-- Identités actives encore dépourvues de compte : alimente le bouton
-- « générer les jetons manquants », qui permet d'équiper d'un coup tous les
-- agents et tous les lieux lors de la mise en service.
CREATE OR REPLACE VIEW `v_acces_compte_manquant` AS
SELECT
    'AGENT' AS `role`,
    `a`.`id_agent` AS `id_agent`,
    CAST(NULL AS UNSIGNED) AS `id_structure`,
    CONCAT(`a`.`prenom`, ' ', `a`.`nom`) AS `libelle`
FROM `agent` `a`
LEFT JOIN `compte_acces` `c` ON `c`.`id_agent` = `a`.`id_agent`
WHERE `a`.`actif` = 1 AND `c`.`id_compte` IS NULL
UNION ALL
SELECT
    'RESPONSABLE_STRUCTURE',
    CAST(NULL AS UNSIGNED),
    `s`.`id_structure`,
    `s`.`nom`
FROM `structure` `s`
LEFT JOIN `compte_acces` `c` ON `c`.`id_structure` = `s`.`id_structure`
WHERE `s`.`actif` = 1 AND `c`.`id_compte` IS NULL;

-- Journal enrichi du compte encore existant, quand il existe. Le libellé figé
-- (`acteur`) reste la source d'affichage : la jointure ne sert qu'à retrouver le
-- préfixe du jeton et à filtrer par compte.
CREATE OR REPLACE VIEW `v_journal_activite` AS
SELECT
    `j`.`id_journal`,
    `j`.`date_evenement`,
    `j`.`categorie`,
    `j`.`action`,
    `j`.`resultat`,
    `j`.`id_compte`,
    `j`.`role`,
    `j`.`acteur`,
    `j`.`cible_table`,
    `j`.`cible_id`,
    `j`.`description`,
    `j`.`contexte`,
    `j`.`adresse_ip`,
    `j`.`agent_utilisateur`,
    `c`.`jeton_prefixe`,
    `c`.`actif` AS `compte_actif`
FROM `journal_activite` `j`
LEFT JOIN `compte_acces` `c` ON `c`.`id_compte` = `j`.`id_compte`;

-- ============================================================================
-- 17. PROCÉDURES DE CONTRÔLE / VALIDATION (PATRIMOINE SQL, NON APPELÉ PAR LE PHP)
-- ============================================================================
DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_preparer_controles_planning`$$
CREATE PROCEDURE `sp_preparer_controles_planning`(
    IN p_id_planning INT UNSIGNED
)
controle: BEGIN
    DECLARE v_planning_existe INT DEFAULT 0;

    DROP TEMPORARY TABLE IF EXISTS `tmp_controles_planning`;

    CREATE TEMPORARY TABLE `tmp_controles_planning` (
        `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        `niveau` ENUM('ERREUR', 'ALERTE', 'INFO') NOT NULL,
        `code` VARCHAR(100) NOT NULL,
        `message` VARCHAR(1000) NOT NULL
    );

    SELECT COUNT(*)
    INTO v_planning_existe
    FROM `planning`
    WHERE `id_planning` = p_id_planning;

    IF v_planning_existe = 0 THEN
        INSERT INTO `tmp_controles_planning`(`niveau`, `code`, `message`)
        VALUES (
            'ERREUR',
            'PLANNING_INTROUVABLE',
            CONCAT('Le planning #', p_id_planning, ' n''existe pas.')
        );
        LEAVE controle;
    END IF;

    -- Un planning validable doit contenir au moins un créneau.
    IF NOT EXISTS (
        SELECT 1 FROM `creneau_horaire` WHERE `id_planning` = p_id_planning
    ) THEN
        INSERT INTO `tmp_controles_planning`(`niveau`, `code`, `message`)
        VALUES ('ERREUR', 'AUCUN_CRENEAU', 'Le planning ne contient aucun créneau horaire.');
    END IF;

    -- Créneaux hors de la semaine du planning.
    INSERT INTO `tmp_controles_planning`(`niveau`, `code`, `message`)
    SELECT
        'ERREUR',
        'CRENEAU_HORS_SEMAINE',
        CONCAT(
            'Le créneau #', `c`.`id_creneau`,
            ' du ', DATE_FORMAT(`c`.`date_jour`, '%d/%m/%Y'),
            ' est en dehors de la semaine ', `s`.`numero_semaine`,
            ' (', DATE_FORMAT(`s`.`date_debut`, '%d/%m/%Y'),
            ' au ', DATE_FORMAT(`s`.`date_fin`, '%d/%m/%Y'), ').'
        )
    FROM `planning` `p`
    INNER JOIN `semaine` `s` ON `s`.`id_semaine` = `p`.`id_semaine`
    INNER JOIN `creneau_horaire` `c` ON `c`.`id_planning` = `p`.`id_planning`
    WHERE `p`.`id_planning` = p_id_planning
      AND `c`.`date_jour` NOT BETWEEN `s`.`date_debut` AND `s`.`date_fin`;

    -- Avant le début du contrat.
    INSERT INTO `tmp_controles_planning`(`niveau`, `code`, `message`)
    SELECT
        'ERREUR',
        'AVANT_DEBUT_CONTRAT',
        CONCAT(
            'Le créneau #', `c`.`id_creneau`,
            ' du ', DATE_FORMAT(`c`.`date_jour`, '%d/%m/%Y'),
            ' est antérieur au début du contrat de ',
            CONCAT(`a`.`prenom`, ' ', `a`.`nom`),
            ' (', DATE_FORMAT(`a`.`date_debut_contrat`, '%d/%m/%Y'), ').'
        )
    FROM `planning` `p`
    INNER JOIN `agent` `a` ON `a`.`id_agent` = `p`.`id_agent`
    INNER JOIN `creneau_horaire` `c` ON `c`.`id_planning` = `p`.`id_planning`
    WHERE `p`.`id_planning` = p_id_planning
      AND `a`.`date_debut_contrat` IS NOT NULL
      AND `c`.`date_jour` < `a`.`date_debut_contrat`;

    -- Après la fin du contrat, si elle existe.
    INSERT INTO `tmp_controles_planning`(`niveau`, `code`, `message`)
    SELECT
        'ERREUR',
        'APRES_FIN_CONTRAT',
        CONCAT(
            'Le créneau #', `c`.`id_creneau`,
            ' du ', DATE_FORMAT(`c`.`date_jour`, '%d/%m/%Y'),
            ' est postérieur à la fin du contrat de ',
            CONCAT(`a`.`prenom`, ' ', `a`.`nom`),
            ' (', DATE_FORMAT(`a`.`date_fin_contrat`, '%d/%m/%Y'), ').'
        )
    FROM `planning` `p`
    INNER JOIN `agent` `a` ON `a`.`id_agent` = `p`.`id_agent`
    INNER JOIN `creneau_horaire` `c` ON `c`.`id_planning` = `p`.`id_planning`
    WHERE `p`.`id_planning` = p_id_planning
      AND `a`.`date_fin_contrat` IS NOT NULL
      AND `c`.`date_jour` > `a`.`date_fin_contrat`;

    -- Chevauchements internes.
    INSERT INTO `tmp_controles_planning`(`niveau`, `code`, `message`)
    SELECT
        'ERREUR',
        'CHEVAUCHEMENT_INTERNE',
        CONCAT(
            'Les créneaux #', `c1`.`id_creneau`,
            ' et #', `c2`.`id_creneau`,
            ' se chevauchent le ', DATE_FORMAT(`c1`.`date_jour`, '%d/%m/%Y'),
            ' (', TIME_FORMAT(`c1`.`heure_debut`, '%H:%i'), '-', TIME_FORMAT(`c1`.`heure_fin`, '%H:%i'),
            ' et ', TIME_FORMAT(`c2`.`heure_debut`, '%H:%i'), '-', TIME_FORMAT(`c2`.`heure_fin`, '%H:%i'), ').'
        )
    FROM `creneau_horaire` `c1`
    INNER JOIN `creneau_horaire` `c2`
        ON `c2`.`id_planning` = `c1`.`id_planning`
       AND `c2`.`date_jour` = `c1`.`date_jour`
       AND `c2`.`id_creneau` > `c1`.`id_creneau`
       AND `c1`.`heure_debut` < `c2`.`heure_fin`
       AND `c1`.`heure_fin` > `c2`.`heure_debut`
    WHERE `c1`.`id_planning` = p_id_planning;

    -- Double affectation de l'agent, tous lieux et tous statuts confondus.
    INSERT INTO `tmp_controles_planning`(`niveau`, `code`, `message`)
    SELECT DISTINCT
        'ERREUR',
        'CHEVAUCHEMENT_AUTRE_PLANNING',
        CONCAT(
            'Le créneau #', `c1`.`id_creneau`,
            ' chevauche le créneau #', `c2`.`id_creneau`,
            ' du planning #', `p2`.`id_planning`,
            ' au lieu « ', `st2`.`nom`, ' » le ',
            DATE_FORMAT(`c1`.`date_jour`, '%d/%m/%Y'),
            ' (', TIME_FORMAT(`c2`.`heure_debut`, '%H:%i'), '-', TIME_FORMAT(`c2`.`heure_fin`, '%H:%i'), ').'
        )
    FROM `planning` `p1`
    INNER JOIN `creneau_horaire` `c1` ON `c1`.`id_planning` = `p1`.`id_planning`
    INNER JOIN `planning` `p2`
        ON `p2`.`id_agent` = `p1`.`id_agent`
       AND `p2`.`id_planning` <> `p1`.`id_planning`
    INNER JOIN `structure` `st2` ON `st2`.`id_structure` = `p2`.`id_structure`
    INNER JOIN `creneau_horaire` `c2`
        ON `c2`.`id_planning` = `p2`.`id_planning`
       AND `c2`.`date_jour` = `c1`.`date_jour`
       AND `c1`.`heure_debut` < `c2`.`heure_fin`
       AND `c1`.`heure_fin` > `c2`.`heure_debut`
    WHERE `p1`.`id_planning` = p_id_planning;

    -- Dépassement du quota CIVIL de l'année concernée.
    INSERT INTO `tmp_controles_planning`(`niveau`, `code`, `message`)
    SELECT DISTINCT
        'ALERTE',
        'QUOTA_DEPASSE',
        CONCAT(
            'Le quota de ', ROUND(`v`.`quota_cible_minutes` / 60, 2), ' h ',
            'pour l''année civile ', `v`.`annee`,
            ' est dépassé. Total affecté : ', ROUND(`v`.`minutes_affectees` / 60, 2), ' h.'
        )
    FROM `planning` `p`
    INNER JOIN `creneau_horaire` `cp` ON `cp`.`id_planning` = `p`.`id_planning`
    INNER JOIN `v_suivi_quota_annuel` `v`
        ON `v`.`id_agent` = `p`.`id_agent`
       AND YEAR(`cp`.`date_jour`) = `v`.`annee`
    WHERE `p`.`id_planning` = p_id_planning
      AND `v`.`minutes_restantes` < 0;

    -- Aucun quota paramétré pour l'année civile touchée.
    INSERT INTO `tmp_controles_planning`(`niveau`, `code`, `message`)
    SELECT DISTINCT
        'ALERTE',
        'QUOTA_NON_PARAMETRE',
        CONCAT(
            'Aucun quota n''est paramétré pour l''agent sur l''année civile ',
            YEAR(`c`.`date_jour`), '.'
        )
    FROM `planning` `p`
    INNER JOIN `creneau_horaire` `c` ON `c`.`id_planning` = `p`.`id_planning`
    WHERE `p`.`id_planning` = p_id_planning
      AND NOT EXISTS (
          SELECT 1
          FROM `quota_agent_annuel` `q`
          WHERE `q`.`id_agent` = `p`.`id_agent`
            AND `q`.`annee` = YEAR(`c`.`date_jour`)
      );

    IF NOT EXISTS (SELECT 1 FROM `tmp_controles_planning`) THEN
        INSERT INTO `tmp_controles_planning`(`niveau`, `code`, `message`)
        VALUES ('INFO', 'OK', 'Aucune anomalie détectée. Le planning peut être validé.');
    END IF;
END$$

DROP PROCEDURE IF EXISTS `sp_controler_planning`$$
CREATE PROCEDURE `sp_controler_planning`(
    IN p_id_planning INT UNSIGNED
)
BEGIN
    CALL `sp_preparer_controles_planning`(p_id_planning);

    SELECT `niveau`, `code`, `message`
    FROM `tmp_controles_planning`
    ORDER BY FIELD(`niveau`, 'ERREUR', 'ALERTE', 'INFO'), `id`;
END$$

DROP PROCEDURE IF EXISTS `sp_valider_planning`$$
CREATE PROCEDURE `sp_valider_planning`(
    IN p_id_planning INT UNSIGNED,
    IN p_forcer_depassement_quota BOOLEAN
)
BEGIN
    DECLARE v_nb_erreurs INT DEFAULT 0;
    DECLARE v_nb_alertes_quota INT DEFAULT 0;
    DECLARE v_message VARCHAR(1000);

    CALL `sp_preparer_controles_planning`(p_id_planning);

    SELECT COUNT(*) INTO v_nb_erreurs
    FROM `tmp_controles_planning`
    WHERE `niveau` = 'ERREUR';

    IF v_nb_erreurs > 0 THEN
        SELECT `message` INTO v_message
        FROM `tmp_controles_planning`
        WHERE `niveau` = 'ERREUR'
        ORDER BY `id`
        LIMIT 1;

        SET v_message = CONCAT(
            'Validation impossible. ', v_nb_erreurs,
            ' erreur(s) détectée(s). Première erreur : ', v_message
        );

        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_message;
    END IF;

    SELECT COUNT(*) INTO v_nb_alertes_quota
    FROM `tmp_controles_planning`
    WHERE `code` = 'QUOTA_DEPASSE';

    IF v_nb_alertes_quota > 0
       AND COALESCE(p_forcer_depassement_quota, FALSE) = FALSE THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Validation suspendue : le quota annuel serait dépassé. Relancer en forçant le dépassement après confirmation.';
    END IF;

    UPDATE `planning`
    SET `statut` = 'VALIDE',
        `date_validation` = CURRENT_TIMESTAMP,
        `date_modification` = CURRENT_TIMESTAMP
    WHERE `id_planning` = p_id_planning;

    SELECT
        `p`.`id_planning`,
        `p`.`statut`,
        `p`.`date_validation`,
        'Planning validé avec succès.' AS `message`
    FROM `planning` `p`
    WHERE `p`.`id_planning` = p_id_planning;
END$$

DELIMITER ;

-- ============================================================================
-- 18. REGISTRE DES MIGRATIONS
-- ============================================================================
-- Table tenue par docker/db/apply-migrations.sh : un fichier de migration déjà
-- appliqué et inchangé est ignoré, un fichier modifié est rejoué. Elle est
-- créée ici pour qu'une base installée à la main soit strictement identique à
-- une base montée par la pile Docker.
--
-- Elle est laissée VIDE volontairement : les empreintes MD5 des fichiers de
-- migration ne peuvent pas être calculées en SQL, et les inscrire en dur ici
-- se périmerait à la première retouche d'une migration. db-migrate les rejouera
-- donc une fois, sans effet — elles sont idempotentes.
CREATE TABLE IF NOT EXISTS `schema_migration` (
    `fichier`     VARCHAR(190) NOT NULL,
    `empreinte`   CHAR(32)     NOT NULL,
    `applique_le` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`fichier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- FIN DE L'INSTALLATION — PTA V7.30
-- ============================================================================
-- Contrôles recommandés après import :
--
--   -- 21 tables métier + 2 tables de droits + compte_acces + journal_activite
--   SELECT COUNT(*) FROM information_schema.TABLES
--    WHERE TABLE_SCHEMA = DATABASE();
--
--   -- Les dix vues exigées au démarrage doivent toutes répondre présentes.
--   SELECT TABLE_NAME FROM information_schema.VIEWS
--    WHERE TABLE_SCHEMA = DATABASE()
--      AND TABLE_NAME IN (
--          'v_acces_role_page', 'v_acces_role_permission',
--          'v_acces_identite_agent', 'v_acces_identite_structure',
--          'v_acces_agent_par_structure', 'v_acces_perimetre_creneau',
--          'v_acces_perimetre_planning', 'v_acces_compte',
--          'v_acces_compte_manquant', 'v_journal_activite')
--    ORDER BY TABLE_NAME;
--
--   -- Droits en place : 4 rôles côté pages, 4 rôles côté permissions.
--   SELECT role, COUNT(*) FROM acces_role_page GROUP BY role;
--   SELECT role, COUNT(*) FROM acces_role_permission GROUP BY role;
--
--   -- Référentiels techniques : 4 postes, 16 motifs.
--   SELECT COUNT(*) FROM poste_agent;
--   SELECT COUNT(*) FROM motif_planning;
--
--   -- La correction #1832 : id_motif doit être int unsigned NOT NULL.
--   SHOW CREATE TABLE creneau_horaire;
--
--   -- La porte d'entrée initiale, à régénérer AVANT toute mise en service.
--   SELECT id_compte, role, libelle, jeton_prefixe FROM compte_acces;
-- ============================================================================