<?php

declare(strict_types=1);

namespace App\Models;

use DateTimeImmutable;
use Throwable;

final class PlanningModel extends BaseModel
{
    public function findByContext(int $structureId, int $agentId, int $year, int $week): ?array
    {
        $stmt = $this->pdo->prepare(
            "SELECT p.id_planning, p.statut, p.commentaire, s.date_debut, s.date_fin
             FROM planning p
             INNER JOIN semaine s ON s.id_semaine = p.id_semaine
             WHERE p.id_structure = :structure_id
               AND p.id_agent = :agent_id
               AND s.annee = :annee
               AND s.numero_semaine = :semaine
             LIMIT 1"
        );
        $stmt->execute([
            'structure_id' => $structureId,
            'agent_id' => $agentId,
            'annee' => $year,
            'semaine' => $week,
        ]);
        return $stmt->fetch() ?: null;
    }

    public function entries(int $planningId): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT c.id_creneau, c.date_jour,
                    TIME_FORMAT(c.heure_debut, '%H:%i') AS heure_debut,
                    TIME_FORMAT(c.heure_fin, '%H:%i') AS heure_fin,
                    m.id_motif, m.code AS motif_code, m.libelle AS motif_libelle,
                    m.compte_dans_quota
             FROM creneau_horaire c
             INNER JOIN motif_planning m ON m.id_motif = c.id_motif
             WHERE c.id_planning = :planning_id
             ORDER BY c.date_jour, c.heure_debut, c.heure_fin"
        );
        $stmt->execute(['planning_id' => $planningId]);
        return $stmt->fetchAll();
    }

    public function otherLocationEntries(int $agentId, int $year, int $week, int $structureId): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT p.id_planning, p.statut,
                    st.id_structure, st.code AS structure_code, st.nom AS structure_nom,
                    st.type_affectation AS structure_type_affectation,
                    c.id_creneau, c.date_jour,
                    TIME_FORMAT(c.heure_debut, '%H:%i') AS heure_debut,
                    TIME_FORMAT(c.heure_fin, '%H:%i') AS heure_fin,
                    m.id_motif, m.code AS motif_code, m.libelle AS motif_libelle,
                    m.compte_dans_quota
             FROM planning p
             INNER JOIN semaine sem ON sem.id_semaine = p.id_semaine
             INNER JOIN structure st ON st.id_structure = p.id_structure
             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.id_agent = :agent_id
               AND sem.annee = :annee
               AND sem.numero_semaine = :semaine
               AND p.id_structure <> :structure_id
             ORDER BY c.date_jour, c.heure_debut, st.nom"
        );
        $stmt->execute([
            'agent_id' => $agentId,
            'annee' => $year,
            'semaine' => $week,
            'structure_id' => $structureId,
        ]);
        return $stmt->fetchAll();
    }

    public function saveDraft(int $structureId, int $agentId, int $year, int $weekNumber, array $entries): int
    {
        $weekStart = (new DateTimeImmutable())->setISODate($year, $weekNumber, 1)->setTime(0, 0);
        $weekEnd = $weekStart->modify('+6 days');

        try {
            $this->pdo->beginTransaction();

            $weekStmt = $this->pdo->prepare(
                "INSERT INTO semaine (annee, numero_semaine, date_debut, date_fin, statut)
                 VALUES (:annee, :numero, :date_debut, :date_fin, 'OUVERTE')
                 ON DUPLICATE KEY UPDATE id_semaine = LAST_INSERT_ID(id_semaine)"
            );
            $weekStmt->execute([
                'annee' => $year,
                'numero' => $weekNumber,
                'date_debut' => $weekStart->format('Y-m-d'),
                'date_fin' => $weekEnd->format('Y-m-d'),
            ]);
            $weekId = (int) $this->pdo->lastInsertId();

            if ($weekId === 0) {
                $findWeek = $this->pdo->prepare(
                    'SELECT id_semaine FROM semaine WHERE annee = :annee AND numero_semaine = :numero'
                );
                $findWeek->execute(['annee' => $year, 'numero' => $weekNumber]);
                $weekId = (int) $findWeek->fetchColumn();
            }

            $planningStmt = $this->pdo->prepare(
                "SELECT id_planning, statut
                 FROM planning
                 WHERE id_agent = :agent_id
                   AND id_semaine = :semaine_id
                   AND id_structure = :structure_id
                 FOR UPDATE"
            );
            $planningStmt->execute([
                'agent_id' => $agentId,
                'semaine_id' => $weekId,
                'structure_id' => $structureId,
            ]);
            $planning = $planningStmt->fetch();

            if ($planning && $planning['statut'] === 'VALIDE') {
                throw new \DomainException('Ce planning est déjà validé et doit être rouvert avant modification.');
            }

            if ($planning) {
                $planningId = (int) $planning['id_planning'];
                $delete = $this->pdo->prepare('DELETE FROM creneau_horaire WHERE id_planning = :planning_id');
                $delete->execute(['planning_id' => $planningId]);
            } else {
                $insertPlanning = $this->pdo->prepare(
                    "INSERT INTO planning (id_agent, id_semaine, id_structure, statut)
                     VALUES (:agent_id, :semaine_id, :structure_id, 'BROUILLON')"
                );
                $insertPlanning->execute([
                    'agent_id' => $agentId,
                    'semaine_id' => $weekId,
                    'structure_id' => $structureId,
                ]);
                $planningId = (int) $this->pdo->lastInsertId();
            }

            $insertEntry = $this->pdo->prepare(
                "INSERT INTO creneau_horaire (id_planning, id_motif, date_jour, heure_debut, heure_fin)
                 VALUES (:planning_id, :motif_id, :date_jour, :heure_debut, :heure_fin)"
            );

            foreach ($entries as $entry) {
                $insertEntry->execute([
                    'planning_id' => $planningId,
                    'motif_id' => $entry['motif_id'],
                    'date_jour' => $entry['date'],
                    'heure_debut' => $entry['start'],
                    'heure_fin' => $entry['end'],
                ]);
            }

            $this->pdo->commit();
            return $planningId;
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            throw $e;
        }
    }

    public function pendingValidation(): array
    {
        $stmt = $this->pdo->query(
            "SELECT p.id_planning, p.id_agent, p.id_structure, p.statut,
                    p.date_creation, p.date_modification,
                    a.matricule, a.nom AS agent_nom, a.prenom AS agent_prenom,
                    st.nom AS structure_nom, st.type_affectation AS structure_type_affectation,
                    sem.annee, sem.numero_semaine, sem.date_debut, sem.date_fin,
                    DATE_ADD(sem.date_debut, INTERVAL 4 DAY) AS date_fin_ouvrable,
                    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 total_minutes,
                    COALESCE(SUM(CASE WHEN m.compte_dans_quota = 1 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 sem ON sem.id_semaine = p.id_semaine
             LEFT JOIN creneau_horaire c ON c.id_planning = p.id_planning
             LEFT JOIN motif_planning m ON m.id_motif = c.id_motif
             WHERE p.statut = 'BROUILLON'
             GROUP BY p.id_planning, p.id_agent, p.id_structure, p.statut,
                      p.date_creation, p.date_modification,
                      a.matricule, a.nom, a.prenom,
                      st.nom, st.type_affectation,
                      sem.annee, sem.numero_semaine, sem.date_debut, sem.date_fin
             HAVING COUNT(c.id_creneau) > 0
             ORDER BY sem.date_debut ASC, a.nom ASC, a.prenom ASC, st.nom ASC"
        );

        return $stmt->fetchAll();
    }


    /**
     * Plannings en brouillon de l'agent qui contiennent au moins un créneau
     * dans l'année civile demandée.
     */
    public function pendingValidationForAgentYear(int $agentId, int $year): array
    {
        $start = sprintf('%04d-01-01', $year);
        $end = sprintf('%04d-12-31', $year);
        $stmt = $this->pdo->prepare(
            "SELECT p.id_planning, p.id_agent, p.id_structure, p.statut,
                    st.nom AS structure_nom,
                    sem.annee, sem.numero_semaine, sem.date_debut, sem.date_fin,
                    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 total_minutes
             FROM planning p
             INNER JOIN structure st ON st.id_structure = p.id_structure
             INNER JOIN semaine sem ON sem.id_semaine = p.id_semaine
             LEFT JOIN creneau_horaire c ON c.id_planning = p.id_planning
             WHERE p.id_agent = :agent_id
               AND p.statut = 'BROUILLON'
               AND EXISTS (
                    SELECT 1
                    FROM creneau_horaire cy
                    WHERE cy.id_planning = p.id_planning
                      AND cy.date_jour BETWEEN :date_debut AND :date_fin
               )
             GROUP BY p.id_planning, p.id_agent, p.id_structure, p.statut,
                      st.nom, sem.annee, sem.numero_semaine, sem.date_debut, sem.date_fin
             HAVING COUNT(c.id_creneau) > 0
             ORDER BY sem.date_debut, st.nom"
        );
        $stmt->execute([
            'agent_id' => $agentId,
            'date_debut' => $start,
            'date_fin' => $end,
        ]);
        return $stmt->fetchAll();
    }

    /**
     * Chevauchements avec n'importe quel autre planning de l'agent,
     * qu'il soit déjà validé ou encore en brouillon.
     */
    public function externalOverlaps(int $planningId): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT DISTINCT c1.id_creneau AS c1, c2.id_creneau AS c2,
                    c1.date_jour, st.nom AS structure_nom, p2.statut AS autre_statut
             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 st ON st.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 = :planning_id"
        );
        $stmt->execute(['planning_id' => $planningId]);
        return $stmt->fetchAll();
    }

    public function findDetailed(int $planningId): ?array
    {
        $stmt = $this->pdo->prepare(
            "SELECT p.id_planning, p.id_agent, p.id_structure, p.statut,
                    s.annee, s.numero_semaine, s.date_debut, s.date_fin
             FROM planning p
             INNER JOIN semaine s ON s.id_semaine = p.id_semaine
             WHERE p.id_planning = :planning_id
             LIMIT 1"
        );
        $stmt->execute(['planning_id' => $planningId]);
        return $stmt->fetch() ?: null;
    }

    public function countEntries(int $planningId): int
    {
        $stmt = $this->pdo->prepare('SELECT COUNT(*) FROM creneau_horaire WHERE id_planning = :planning_id');
        $stmt->execute(['planning_id' => $planningId]);
        return (int) $stmt->fetchColumn();
    }

    public function entriesOutsideWeek(int $planningId, string $start, string $end): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT id_creneau, date_jour
             FROM creneau_horaire
             WHERE id_planning = :planning_id
               AND date_jour NOT BETWEEN :date_debut AND :date_fin"
        );
        $stmt->execute(['planning_id' => $planningId, 'date_debut' => $start, 'date_fin' => $end]);
        return $stmt->fetchAll();
    }

    public function internalOverlaps(int $planningId): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT c1.id_creneau AS c1, c2.id_creneau AS c2, c1.date_jour
             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 = :planning_id"
        );
        $stmt->execute(['planning_id' => $planningId]);
        return $stmt->fetchAll();
    }

    public function validatedExternalOverlaps(int $planningId): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT DISTINCT c1.id_creneau AS c1, c2.id_creneau AS c2, c1.date_jour, st.nom AS structure_nom
             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
               AND p2.statut = 'VALIDE'
             INNER JOIN structure st ON st.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 = :planning_id"
        );
        $stmt->execute(['planning_id' => $planningId]);
        return $stmt->fetchAll();
    }

    public function findEntryDetailed(int $entryId): ?array
    {
        $stmt = $this->pdo->prepare(
            "SELECT c.id_creneau, c.id_planning, c.id_motif, c.date_jour,
                    TIME_FORMAT(c.heure_debut, '%H:%i') AS heure_debut,
                    TIME_FORMAT(c.heure_fin, '%H:%i') AS heure_fin,
                    p.id_agent, p.id_structure, p.id_semaine, p.statut,
                    sem.annee, sem.numero_semaine, sem.date_debut, sem.date_fin
             FROM creneau_horaire c
             INNER JOIN planning p ON p.id_planning = c.id_planning
             INNER JOIN semaine sem ON sem.id_semaine = p.id_semaine
             WHERE c.id_creneau = :entry_id
             LIMIT 1"
        );
        $stmt->execute(['entry_id' => $entryId]);
        return $stmt->fetch() ?: null;
    }

    /**
     * Modifie un créneau depuis la vue agent, y compris vers une autre semaine.
     * Le planning cible est créé si nécessaire et tout planning validé modifié
     * repasse automatiquement en brouillon.
     */
    public function updateEntryFromAgentView(
        int $entryId,
        int $targetStructureId,
        int $motifId,
        string $date,
        string $start,
        string $end
    ): array {
        try {
            $this->pdo->beginTransaction();

            $entryStmt = $this->pdo->prepare(
                "SELECT c.id_creneau, c.id_planning, c.id_motif, c.date_jour, c.heure_debut, c.heure_fin,
                        p.id_agent, p.id_structure, p.id_semaine, p.statut,
                        sem.annee, sem.numero_semaine
                 FROM creneau_horaire c
                 INNER JOIN planning p ON p.id_planning = c.id_planning
                 INNER JOIN semaine sem ON sem.id_semaine = p.id_semaine
                 WHERE c.id_creneau = :entry_id
                 FOR UPDATE"
            );
            $entryStmt->execute(['entry_id' => $entryId]);
            $entry = $entryStmt->fetch();
            if (!$entry) {
                throw new \DomainException('Le créneau à modifier est introuvable.');
            }

            $targetDate = new DateTimeImmutable($date);
            if ((int) $targetDate->format('N') > 5) {
                throw new \DomainException('Le créneau doit être positionné entre le lundi et le vendredi.');
            }

            $conflictStmt = $this->pdo->prepare(
                "SELECT c.id_creneau, p.statut, st.nom AS structure_nom,
                        TIME_FORMAT(c.heure_debut, '%H:%i') AS heure_debut,
                        TIME_FORMAT(c.heure_fin, '%H:%i') AS heure_fin
                 FROM creneau_horaire c
                 INNER JOIN planning p ON p.id_planning = c.id_planning
                 INNER JOIN structure st ON st.id_structure = p.id_structure
                 WHERE p.id_agent = :agent_id
                   AND c.id_creneau <> :entry_id
                   AND c.date_jour = :date_jour
                   AND c.heure_debut < :heure_fin
                   AND c.heure_fin > :heure_debut
                 LIMIT 1
                 FOR UPDATE"
            );
            $conflictStmt->execute([
                'agent_id' => (int) $entry['id_agent'],
                'entry_id' => $entryId,
                'date_jour' => $date,
                'heure_debut' => $start,
                'heure_fin' => $end,
            ]);
            $conflict = $conflictStmt->fetch();
            if ($conflict) {
                throw new \DomainException(sprintf(
                    'Double affectation impossible : un autre créneau existe déjà sur le lieu « %s » de %s à %s (%s).',
                    $conflict['structure_nom'],
                    $conflict['heure_debut'],
                    $conflict['heure_fin'],
                    strtolower((string) $conflict['statut'])
                ));
            }

            $targetYear = (int) $targetDate->format('o');
            $targetWeekNumber = (int) $targetDate->format('W');
            $targetWeekStart = $targetDate->modify('-' . ((int) $targetDate->format('N') - 1) . ' days')->setTime(0, 0);
            $targetWeekId = $this->ensureWeek($targetYear, $targetWeekNumber, $targetWeekStart, $targetWeekStart->modify('+6 days'));

            $sourcePlanningId = (int) $entry['id_planning'];
            $targetPlanning = $this->findOrCreatePlanning(
                (int) $entry['id_agent'],
                $targetWeekId,
                $targetStructureId
            );
            $targetPlanningId = (int) $targetPlanning['id_planning'];

            $updateEntry = $this->pdo->prepare(
                "UPDATE creneau_horaire
                 SET id_planning = :planning_id,
                     id_motif = :motif_id,
                     date_jour = :date_jour,
                     heure_debut = :heure_debut,
                     heure_fin = :heure_fin
                 WHERE id_creneau = :entry_id"
            );
            $updateEntry->execute([
                'planning_id' => $targetPlanningId,
                'motif_id' => $motifId,
                'date_jour' => $date,
                'heure_debut' => $start,
                'heure_fin' => $end,
                'entry_id' => $entryId,
            ]);

            $reopenedPlanningIds = [];
            if ((string) $entry['statut'] === 'VALIDE') {
                $reopenedPlanningIds[] = $sourcePlanningId;
            }
            if ((string) $targetPlanning['statut'] === 'VALIDE') {
                $reopenedPlanningIds[] = $targetPlanningId;
            }
            $reopenedPlanningIds = $this->reopenPlanningIds($reopenedPlanningIds);

            $this->pdo->commit();

            return [
                'entry_id' => $entryId,
                'agent_id' => (int) $entry['id_agent'],
                'year' => $targetYear,
                'week' => $targetWeekNumber,
                'source_planning_id' => $sourcePlanningId,
                'target_planning_id' => $targetPlanningId,
                'reopened_planning_ids' => $reopenedPlanningIds,
            ];
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            throw $e;
        }
    }

    /**
     * Supprime un créneau depuis la vue par agent.
     * Si le planning était validé, il repasse en brouillon afin de signaler
     * que son contenu a été modifié. Le planning est conservé même s'il ne
     * contient plus aucun créneau.
     */
    public function deleteEntryFromAgentView(int $entryId): array
    {
        try {
            $this->pdo->beginTransaction();

            $entryStmt = $this->pdo->prepare(
                "SELECT c.id_creneau, c.id_planning, c.date_jour,
                        p.id_agent, p.statut,
                        sem.annee, sem.numero_semaine
                 FROM creneau_horaire c
                 INNER JOIN planning p ON p.id_planning = c.id_planning
                 INNER JOIN semaine sem ON sem.id_semaine = p.id_semaine
                 WHERE c.id_creneau = :entry_id
                 FOR UPDATE"
            );
            $entryStmt->execute(['entry_id' => $entryId]);
            $entry = $entryStmt->fetch();

            if (!$entry) {
                throw new \DomainException('Le créneau à supprimer est introuvable.');
            }

            $deleteStmt = $this->pdo->prepare(
                'DELETE FROM creneau_horaire WHERE id_creneau = :entry_id'
            );
            $deleteStmt->execute(['entry_id' => $entryId]);

            if ($deleteStmt->rowCount() !== 1) {
                throw new \DomainException('Le créneau a déjà été supprimé ou modifié. Rechargez la page.');
            }

            $reopened = (string) $entry['statut'] === 'VALIDE';
            if ($reopened) {
                $reopenStmt = $this->pdo->prepare(
                    "UPDATE planning
                     SET statut = 'BROUILLON'
                     WHERE id_planning = :planning_id"
                );
                $reopenStmt->execute(['planning_id' => (int) $entry['id_planning']]);
            }

            $countStmt = $this->pdo->prepare(
                'SELECT COUNT(*) FROM creneau_horaire WHERE id_planning = :planning_id'
            );
            $countStmt->execute(['planning_id' => (int) $entry['id_planning']]);
            $remainingEntries = (int) $countStmt->fetchColumn();

            $this->pdo->commit();

            return [
                'entry_id' => $entryId,
                'planning_id' => (int) $entry['id_planning'],
                'agent_id' => (int) $entry['id_agent'],
                'year' => (int) $entry['annee'],
                'week' => (int) $entry['numero_semaine'],
                'date' => (string) $entry['date_jour'],
                'reopened' => $reopened,
                'remaining_entries' => $remainingEntries,
            ];
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            throw $e;
        }
    }

    /**
     * Déplace ou duplique un créneau vers n'importe quel jour ouvré.
     * Le lieu, le motif et les horaires sont conservés, y compris lors d'un
     * changement de semaine.
     */
    public function moveOrDuplicateEntry(int $entryId, string $targetDate, bool $duplicate): array
    {
        try {
            $this->pdo->beginTransaction();

            $entryStmt = $this->pdo->prepare(
                "SELECT c.id_creneau, c.id_planning, c.id_motif, c.date_jour, c.heure_debut, c.heure_fin,
                        p.id_agent, p.id_structure, p.id_semaine, p.statut,
                        sem.annee, sem.numero_semaine
                 FROM creneau_horaire c
                 INNER JOIN planning p ON p.id_planning = c.id_planning
                 INNER JOIN semaine sem ON sem.id_semaine = p.id_semaine
                 WHERE c.id_creneau = :entry_id
                 FOR UPDATE"
            );
            $entryStmt->execute(['entry_id' => $entryId]);
            $entry = $entryStmt->fetch();
            if (!$entry) {
                throw new \DomainException('Le créneau à déplacer est introuvable.');
            }

            $targetDateObject = new DateTimeImmutable($targetDate);
            if ((int) $targetDateObject->format('N') > 5) {
                throw new \DomainException('Le jour cible doit être compris entre le lundi et le vendredi.');
            }

            if (!$duplicate && $targetDate === (string) $entry['date_jour']) {
                $this->pdo->commit();
                return [
                    'entry_id' => $entryId,
                    'created_entry_id' => null,
                    'agent_id' => (int) $entry['id_agent'],
                    'year' => (int) $entry['annee'],
                    'week' => (int) $entry['numero_semaine'],
                    'planning_id' => (int) $entry['id_planning'],
                    'target_planning_id' => (int) $entry['id_planning'],
                    'reopened_planning_ids' => [],
                    'action' => 'noop',
                ];
            }

            $excludeEntryId = $duplicate ? 0 : $entryId;
            $conflictStmt = $this->pdo->prepare(
                "SELECT c.id_creneau, p.statut, st.nom AS structure_nom,
                        TIME_FORMAT(c.heure_debut, '%H:%i') AS heure_debut,
                        TIME_FORMAT(c.heure_fin, '%H:%i') AS heure_fin
                 FROM creneau_horaire c
                 INNER JOIN planning p ON p.id_planning = c.id_planning
                 INNER JOIN structure st ON st.id_structure = p.id_structure
                 WHERE p.id_agent = :agent_id
                   AND (:exclude_entry_id = 0 OR c.id_creneau <> :exclude_entry_id_compare)
                   AND c.date_jour = :date_jour
                   AND c.heure_debut < :heure_fin
                   AND c.heure_fin > :heure_debut
                 LIMIT 1
                 FOR UPDATE"
            );
            $conflictStmt->execute([
                'agent_id' => (int) $entry['id_agent'],
                'exclude_entry_id' => $excludeEntryId,
                'exclude_entry_id_compare' => $excludeEntryId,
                'date_jour' => $targetDate,
                'heure_fin' => $entry['heure_fin'],
                'heure_debut' => $entry['heure_debut'],
            ]);
            $conflict = $conflictStmt->fetch();
            if ($conflict) {
                throw new \DomainException(sprintf(
                    'Action impossible : l’agent possède déjà un créneau sur le lieu « %s » de %s à %s (%s).',
                    $conflict['structure_nom'],
                    $conflict['heure_debut'],
                    $conflict['heure_fin'],
                    strtolower((string) $conflict['statut'])
                ));
            }

            $targetYear = (int) $targetDateObject->format('o');
            $targetWeekNumber = (int) $targetDateObject->format('W');
            $targetMonday = $targetDateObject->modify('-' . ((int) $targetDateObject->format('N') - 1) . ' days')->setTime(0, 0);
            $targetWeekId = $this->ensureWeek($targetYear, $targetWeekNumber, $targetMonday, $targetMonday->modify('+6 days'));
            $targetPlanning = $this->findOrCreatePlanning(
                (int) $entry['id_agent'],
                $targetWeekId,
                (int) $entry['id_structure']
            );
            $targetPlanningId = (int) $targetPlanning['id_planning'];
            $sourcePlanningId = (int) $entry['id_planning'];

            $createdEntryId = null;
            if ($duplicate) {
                $insertStmt = $this->pdo->prepare(
                    "INSERT INTO creneau_horaire (id_planning, id_motif, date_jour, heure_debut, heure_fin)
                     VALUES (:planning_id, :motif_id, :date_jour, :heure_debut, :heure_fin)"
                );
                $insertStmt->execute([
                    'planning_id' => $targetPlanningId,
                    'motif_id' => (int) $entry['id_motif'],
                    'date_jour' => $targetDate,
                    'heure_debut' => $entry['heure_debut'],
                    'heure_fin' => $entry['heure_fin'],
                ]);
                $createdEntryId = (int) $this->pdo->lastInsertId();
            } else {
                $moveStmt = $this->pdo->prepare(
                    "UPDATE creneau_horaire
                     SET id_planning = :target_planning_id,
                         date_jour = :date_jour
                     WHERE id_creneau = :entry_id"
                );
                $moveStmt->execute([
                    'target_planning_id' => $targetPlanningId,
                    'date_jour' => $targetDate,
                    'entry_id' => $entryId,
                ]);
            }

            $reopenedPlanningIds = [];
            if (!$duplicate && (string) $entry['statut'] === 'VALIDE') {
                $reopenedPlanningIds[] = $sourcePlanningId;
            }
            if ((string) $targetPlanning['statut'] === 'VALIDE') {
                $reopenedPlanningIds[] = $targetPlanningId;
            }
            $reopenedPlanningIds = $this->reopenPlanningIds($reopenedPlanningIds);

            $this->pdo->commit();

            return [
                'entry_id' => $entryId,
                'created_entry_id' => $createdEntryId,
                'agent_id' => (int) $entry['id_agent'],
                'year' => $targetYear,
                'week' => $targetWeekNumber,
                'planning_id' => $sourcePlanningId,
                'target_planning_id' => $targetPlanningId,
                'reopened_planning_ids' => $reopenedPlanningIds,
                'action' => $duplicate ? 'duplicate' : 'move',
            ];
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            throw $e;
        }
    }

    public function copyAgentWeek(
        int $agentId,
        int $sourceYear,
        int $sourceWeekNumber,
        int $targetYear,
        int $targetWeekNumber,
        string $mode
    ): array {
        if (!in_array($mode, ['merge', 'replace'], true)) {
            throw new \DomainException('Mode de duplication invalide.');
        }
        if ($sourceYear === $targetYear && $sourceWeekNumber === $targetWeekNumber) {
            throw new \DomainException('La semaine cible doit être différente de la semaine source.');
        }

        $sourceStmt = $this->pdo->prepare(
            "SELECT WEEKDAY(c.date_jour) + 1 AS jour_semaine,
                    p.id_structure, c.id_motif,
                    TIME_FORMAT(c.heure_debut, '%H:%i:%s') AS heure_debut,
                    TIME_FORMAT(c.heure_fin, '%H:%i:%s') AS heure_fin
             FROM planning p
             INNER JOIN semaine sem ON sem.id_semaine = p.id_semaine
             INNER JOIN creneau_horaire c ON c.id_planning = p.id_planning
             WHERE p.id_agent = :agent_id
               AND sem.annee = :annee
               AND sem.numero_semaine = :semaine
               AND WEEKDAY(c.date_jour) BETWEEN 0 AND 4
             ORDER BY c.date_jour, c.heure_debut"
        );
        $sourceStmt->execute([
            'agent_id' => $agentId,
            'annee' => $sourceYear,
            'semaine' => $sourceWeekNumber,
        ]);
        $sourceEntries = $sourceStmt->fetchAll();
        if (!$sourceEntries) {
            throw new \DomainException('La semaine source ne contient aucun créneau à dupliquer.');
        }

        $targetMonday = (new DateTimeImmutable())->setISODate($targetYear, $targetWeekNumber, 1)->setTime(0, 0);
        $targetEntries = array_map(static function (array $entry) use ($targetMonday): array {
            return [
                'date_jour' => $targetMonday->modify('+' . ((int) $entry['jour_semaine'] - 1) . ' days')->format('Y-m-d'),
                'id_structure' => (int) $entry['id_structure'],
                'id_motif' => (int) $entry['id_motif'],
                'heure_debut' => (string) $entry['heure_debut'],
                'heure_fin' => (string) $entry['heure_fin'],
            ];
        }, $sourceEntries);

        try {
            $this->pdo->beginTransaction();
            $targetWeekId = $this->ensureWeek($targetYear, $targetWeekNumber, $targetMonday, $targetMonday->modify('+6 days'));

            $existingPlanningStmt = $this->pdo->prepare(
                "SELECT id_planning, id_structure, statut
                 FROM planning
                 WHERE id_agent = :agent_id AND id_semaine = :week_id
                 FOR UPDATE"
            );
            $existingPlanningStmt->execute(['agent_id' => $agentId, 'week_id' => $targetWeekId]);
            $existingPlannings = $existingPlanningStmt->fetchAll();

            $reopened = [];
            if ($mode === 'replace') {
                if ($existingPlannings) {
                    $ids = array_map(static fn(array $row): int => (int) $row['id_planning'], $existingPlannings);
                    foreach ($existingPlannings as $planning) {
                        if ((string) $planning['statut'] === 'VALIDE') {
                            $reopened[] = (int) $planning['id_planning'];
                        }
                    }
                    $placeholders = implode(',', array_fill(0, count($ids), '?'));
                    $delete = $this->pdo->prepare("DELETE FROM creneau_horaire WHERE id_planning IN ($placeholders)");
                    $delete->execute($ids);
                }
            } else {
                $existingEntriesStmt = $this->pdo->prepare(
                    "SELECT c.date_jour, c.heure_debut, c.heure_fin, st.nom AS structure_nom
                     FROM planning p
                     INNER JOIN creneau_horaire c ON c.id_planning = p.id_planning
                     INNER JOIN structure st ON st.id_structure = p.id_structure
                     WHERE p.id_agent = :agent_id AND p.id_semaine = :week_id
                     FOR UPDATE"
                );
                $existingEntriesStmt->execute(['agent_id' => $agentId, 'week_id' => $targetWeekId]);
                $existingEntries = $existingEntriesStmt->fetchAll();
                foreach ($targetEntries as $candidate) {
                    foreach ($existingEntries as $existing) {
                        if ($candidate['date_jour'] === $existing['date_jour']
                            && $candidate['heure_debut'] < $existing['heure_fin']
                            && $candidate['heure_fin'] > $existing['heure_debut']) {
                            throw new \DomainException(sprintf(
                                'Duplication impossible : un créneau existe déjà le %s de %s à %s sur « %s ».',
                                $candidate['date_jour'],
                                substr((string) $existing['heure_debut'], 0, 5),
                                substr((string) $existing['heure_fin'], 0, 5),
                                $existing['structure_nom']
                            ));
                        }
                    }
                }
            }

            $planningCache = [];
            foreach ($existingPlannings as $planning) {
                $planningCache[(int) $planning['id_structure']] = [
                    'id_planning' => (int) $planning['id_planning'],
                    'statut' => (string) $planning['statut'],
                ];
            }

            $insertEntry = $this->pdo->prepare(
                "INSERT INTO creneau_horaire (id_planning, id_motif, date_jour, heure_debut, heure_fin)
                 VALUES (:planning_id, :motif_id, :date_jour, :heure_debut, :heure_fin)"
            );

            foreach ($targetEntries as $candidate) {
                $structureId = (int) $candidate['id_structure'];
                if (!isset($planningCache[$structureId])) {
                    $planningCache[$structureId] = $this->findOrCreatePlanning($agentId, $targetWeekId, $structureId);
                }
                $planning = $planningCache[$structureId];
                if ((string) $planning['statut'] === 'VALIDE') {
                    $reopened[] = (int) $planning['id_planning'];
                }
                $insertEntry->execute([
                    'planning_id' => (int) $planning['id_planning'],
                    'motif_id' => (int) $candidate['id_motif'],
                    'date_jour' => $candidate['date_jour'],
                    'heure_debut' => $candidate['heure_debut'],
                    'heure_fin' => $candidate['heure_fin'],
                ]);
            }

            $reopened = $this->reopenPlanningIds($reopened);
            $this->pdo->commit();

            return [
                'agent_id' => $agentId,
                'year' => $targetYear,
                'week' => $targetWeekNumber,
                'copied_entries' => count($targetEntries),
                'reopened_planning_ids' => $reopened,
                'mode' => $mode,
            ];
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            throw $e;
        }
    }

    private function ensureWeek(int $year, int $weekNumber, DateTimeImmutable $weekStart, DateTimeImmutable $weekEnd): int
    {
        $stmt = $this->pdo->prepare(
            "INSERT INTO semaine (annee, numero_semaine, date_debut, date_fin, statut)
             VALUES (:annee, :numero, :date_debut, :date_fin, 'OUVERTE')
             ON DUPLICATE KEY UPDATE id_semaine = LAST_INSERT_ID(id_semaine)"
        );
        $stmt->execute([
            'annee' => $year,
            'numero' => $weekNumber,
            'date_debut' => $weekStart->format('Y-m-d'),
            'date_fin' => $weekEnd->format('Y-m-d'),
        ]);
        $weekId = (int) $this->pdo->lastInsertId();
        if ($weekId > 0) {
            return $weekId;
        }

        $find = $this->pdo->prepare('SELECT id_semaine FROM semaine WHERE annee = :annee AND numero_semaine = :numero');
        $find->execute(['annee' => $year, 'numero' => $weekNumber]);
        $weekId = (int) $find->fetchColumn();
        if ($weekId <= 0) {
            throw new \RuntimeException('Impossible de créer ou retrouver la semaine cible.');
        }
        return $weekId;
    }

    /** @return array{id_planning:int,statut:string} */
    private function findOrCreatePlanning(int $agentId, int $weekId, int $structureId): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT id_planning, statut
             FROM planning
             WHERE id_agent = :agent_id AND id_semaine = :week_id AND id_structure = :structure_id
             FOR UPDATE"
        );
        $stmt->execute([
            'agent_id' => $agentId,
            'week_id' => $weekId,
            'structure_id' => $structureId,
        ]);
        $planning = $stmt->fetch();
        if ($planning) {
            return ['id_planning' => (int) $planning['id_planning'], 'statut' => (string) $planning['statut']];
        }

        $insert = $this->pdo->prepare(
            "INSERT INTO planning (id_agent, id_semaine, id_structure, statut)
             VALUES (:agent_id, :week_id, :structure_id, 'BROUILLON')"
        );
        $insert->execute([
            'agent_id' => $agentId,
            'week_id' => $weekId,
            'structure_id' => $structureId,
        ]);
        return ['id_planning' => (int) $this->pdo->lastInsertId(), 'statut' => 'BROUILLON'];
    }

    /** @return int[] */
    private function reopenPlanningIds(array $planningIds): array
    {
        $planningIds = array_values(array_unique(array_map('intval', array_filter($planningIds))));
        if (!$planningIds) {
            return [];
        }
        $placeholders = implode(',', array_fill(0, count($planningIds), '?'));
        $stmt = $this->pdo->prepare(
            "UPDATE planning SET statut = 'BROUILLON', date_validation = NULL WHERE id_planning IN ($placeholders)"
        );
        $stmt->execute($planningIds);
        return $planningIds;
    }


    /** @param int[] $planningIds */
    public function markValidatedMany(array $planningIds): int
    {
        $planningIds = array_values(array_unique(array_filter(array_map('intval', $planningIds), static fn(int $id): bool => $id > 0)));
        if ($planningIds === []) {
            return 0;
        }
        $placeholders = implode(',', array_fill(0, count($planningIds), '?'));
        $stmt = $this->pdo->prepare(
            "UPDATE planning
             SET statut = 'VALIDE', date_validation = CURRENT_TIMESTAMP
             WHERE statut = 'BROUILLON' AND id_planning IN ($placeholders)"
        );
        $stmt->execute($planningIds);
        return $stmt->rowCount();
    }

    public function markValidated(int $planningId): void
    {
        $stmt = $this->pdo->prepare(
            "UPDATE planning SET statut = 'VALIDE', date_validation = CURRENT_TIMESTAMP WHERE id_planning = :planning_id"
        );
        $stmt->execute(['planning_id' => $planningId]);
    }

    public function reopen(int $planningId): bool
    {
        $stmt = $this->pdo->prepare(
            "UPDATE planning
             SET statut = 'BROUILLON', date_validation = NULL
             WHERE id_planning = :planning_id AND statut = 'VALIDE'"
        );
        $stmt->execute(['planning_id' => $planningId]);
        return $stmt->rowCount() === 1;
    }
}