<?php
// app/model/service/PresenceService.php

require_once __DIR__ . '/../core/AbstractPdo.php';

class PresenceService extends AbstractPdo
{
    protected $table = 'presence';
    protected $primaryKey = 'id';

    public function __construct()
    {
        parent::__construct();
    }

    // ============================================
    // RÉCUPÉRATION DES FILTRES
    // ============================================
    public function getModulesWithAffectations()
    {
        $sql = "SELECT im.id, 
                   m.libelle as module_libelle, 
                   CONCAT(e.nom, ' ', e.prenom) as employee_fullname,
                   f.libelle as filiere_libelle,
                   s.libelle as semestre_libelle,
                   im.annee_universitaire
            FROM inscription_module im
            JOIN module m ON im.module_id = m.id
            JOIN employee e ON im.employee_id = e.id
            JOIN filiere f ON im.filiere_id = f.id
            JOIN semestre s ON m.semestre_id = s.id
            ORDER BY im.annee_universitaire DESC, f.libelle, s.id";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    public function getAnneesUniversitaires()
    {
        $sql = "SELECT DISTINCT annee_universitaire 
                FROM inscription_module 
                ORDER BY annee_universitaire DESC";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_COLUMN);
    }

    public function getFiliereByAnnee($annee)
    {
        $sql = "SELECT DISTINCT f.id, f.libelle, f.code
                FROM inscription_module im
                JOIN filiere f ON im.filiere_id = f.id
                WHERE im.annee_universitaire = :annee
                ORDER BY f.libelle";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':annee' => $annee]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getSemestresByFiliere($filiereId, $annee)
    {
        $sql = "SELECT DISTINCT s.id, s.libelle, s.code
                FROM inscription_module im
                JOIN module m ON im.module_id = m.id
                JOIN semestre s ON m.semestre_id = s.id
                WHERE im.filiere_id = :filiere_id 
                AND im.annee_universitaire = :annee
                ORDER BY s.id";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':filiere_id' => $filiereId,
            ':annee' => $annee
        ]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getModulesByFiliereSemestre($filiereId, $semestreId, $annee)
    {
        $sql = "SELECT im.id as inscription_module_id, 
                       m.id as module_id, 
                       m.libelle as module_libelle, 
                       m.code as module_code,
                       e.id as employee_id,
                       CONCAT(e.nom, ' ', e.prenom) as professeur,
                       im.nb_etudiants
                FROM inscription_module im
                JOIN module m ON im.module_id = m.id
                JOIN employee e ON im.employee_id = e.id
                WHERE im.filiere_id = :filiere_id
                AND m.semestre_id = :semestre_id
                AND im.annee_universitaire = :annee
                ORDER BY m.libelle";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':filiere_id' => $filiereId,
            ':semestre_id' => $semestreId,
            ':annee' => $annee
        ]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getStudentsByInscriptionModule($inscriptionModuleId)
    {
        $sql = "SELECT s.id, s.nom, s.prenom, s.email, s.cin
                FROM inscription_module_detail imd
                JOIN student s ON imd.student_id = s.id
                WHERE imd.inscription_module_id = :inscription_module_id
                ORDER BY s.nom, s.prenom";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':inscription_module_id' => $inscriptionModuleId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getInscriptionModule($id)
    {
        $sql = "SELECT im.*, 
                       f.libelle as filiere_libelle,
                       m.libelle as module_libelle,
                       m.code as module_code,
                       CONCAT(e.nom, ' ', e.prenom) as professeur
                FROM inscription_module im
                JOIN filiere f ON im.filiere_id = f.id
                JOIN module m ON im.module_id = m.id
                JOIN employee e ON im.employee_id = e.id
                WHERE im.id = :id";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    // ============================================
    // VÉRIFICATION DES CONFLITS
    // ============================================

    public function checkConflict($inscriptionModuleId, $date, $heure, $excludeId = null)
    {
        $sql = "SELECT id, libelle, date_seance, heure_debut, nb_heures 
                FROM presence 
                WHERE inscription_module_id = :inscription_module_id 
                AND date_seance = :date 
                AND heure_debut = :heure";

        $params = [
            ':inscription_module_id' => $inscriptionModuleId,
            ':date' => $date,
            ':heure' => $heure
        ];

        if ($excludeId) {
            $sql .= " AND id != :exclude_id";
            $params[':exclude_id'] = $excludeId;
        }

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        return [
            'hasConflict' => $result !== false,
            'existingId' => $result ? $result['id'] : null,
            'existingLibelle' => $result ? $result['libelle'] : null
        ];
    }

    // ============================================
    // GESTION DES PRÉSENCES
    // ============================================

    public function findAll($order = 'p.date_seance DESC, p.heure_debut ASC', $limit = null)
    {
        $sql = "SELECT p.*,
                       p.date_seance,
                       p.heure_debut,
                       p.nb_heures,
                       p.jour_semaine,
                       p.libelle,
                       im.id as inscription_module_id,
                       m.libelle as module_libelle,
                       m.code as module_code,
                       f.libelle as filiere_libelle,
                       s.libelle as semestre_libelle,
                       CONCAT(e.nom, ' ', e.prenom) as employee_fullname,
                       im.annee_universitaire,
                       (SELECT COUNT(*) FROM presence_detail WHERE presence_id = p.id AND present = 1) as total_presents,
                       (SELECT COUNT(*) FROM presence_detail WHERE presence_id = p.id) as total_students
                FROM presence p
                INNER JOIN inscription_module im ON p.inscription_module_id = im.id
                INNER JOIN module m ON im.module_id = m.id
                INNER JOIN filiere f ON im.filiere_id = f.id
                INNER JOIN semestre s ON m.semestre_id = s.id
                INNER JOIN employee e ON im.employee_id = e.id
                ORDER BY p.date_seance DESC, p.heure_debut ASC";

        if ($limit) {
            $sql .= " LIMIT " . intval($limit);
        }

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function markPresence($inscriptionModuleId, $date, $heureDebut, $nbHeures, $libelle, $presences)
    {
        $conflict = $this->checkConflict($inscriptionModuleId, $date, $heureDebut);
        if ($conflict['hasConflict']) {
            throw new Exception("Une séance existe déjà pour ce cours à cette date et heure: " . $conflict['existingLibelle']);
        }

        $inscription = $this->getInscriptionModule($inscriptionModuleId);
        if (!$inscription) {
            throw new Exception("Inscription module non trouvée");
        }

        if (empty($libelle)) {
            $dateObj = new DateTime($date);
            $libelle = 'Séance du ' . $dateObj->format('d/m/Y') . ' à ' . $heureDebut;
        }

        $dateObj = new DateTime($date);
        $jours = ['Dimanche', 'Lundi', 'Mardi', 'Mercredi', 'Jeudi', 'Vendredi', 'Samedi'];
        $jourSemaine = $jours[(int)$dateObj->format('w')];

        $data = [
            'inscription_module_id' => $inscriptionModuleId,
            'date_seance' => $date,
            'heure_debut' => $heureDebut,
            'nb_heures' => $nbHeures,
            'jour_semaine' => $jourSemaine,
            'libelle' => $libelle,
            'description' => 'Cours de ' . ($inscription['module_libelle'] ?? 'Module')
        ];

        $presenceId = $this->insert($data);

        if (!empty($presences)) {
            foreach ($presences as $p) {
                $present = isset($p['present']) ? (int)$p['present'] : 0;
                $description = isset($p['description']) ? $p['description'] : null;

                $sql = "INSERT INTO presence_detail (presence_id, student_id, present, description) 
                        VALUES (:presence_id, :student_id, :present, :description)";
                $stmt = $this->pdo->prepare($sql);
                $stmt->execute([
                    ':presence_id' => $presenceId,
                    ':student_id' => $p['student_id'],
                    ':present' => $present,
                    ':description' => $description
                ]);
            }
        }

        return $presenceId;
    }

    public function updatePresence($presenceId, $studentPresences)
    {
        $presence = $this->findOne($presenceId);
        if (!$presence) {
            throw new Exception("Séance non trouvée");
        }

        $sql = "DELETE FROM presence_detail WHERE presence_id = :presence_id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':presence_id' => $presenceId]);

        if (!empty($studentPresences)) {
            foreach ($studentPresences as $sp) {
                if (!empty($sp['student_id'])) {
                    $present = isset($sp['present']) ? (int)$sp['present'] : 0;
                    $description = isset($sp['description']) ? $sp['description'] : null;

                    $sql = "INSERT INTO presence_detail (presence_id, student_id, present, description) 
                            VALUES (:presence_id, :student_id, :present, :description)";
                    $stmt = $this->pdo->prepare($sql);
                    $stmt->execute([
                        ':presence_id' => $presenceId,
                        ':student_id' => $sp['student_id'],
                        ':present' => $present,
                        ':description' => $description
                    ]);
                }
            }
        }

        return true;
    }

    public function deletePresence($id)
    {
        $presence = $this->findOne($id);
        if (!$presence) {
            throw new Exception("Séance non trouvée");
        }

        $sql = "DELETE FROM presence_detail WHERE presence_id = :presence_id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':presence_id' => $id]);

        return $this->delete($id);
    }

    public function getPresenceWithStudents($id)
    {
        $sql = "SELECT p.*,
                       p.date_seance,
                       p.heure_debut,
                       p.nb_heures,
                       p.jour_semaine,
                       p.libelle,
                       im.id as inscription_module_id,
                       m.libelle as module_libelle,
                       m.code as module_code,
                       f.libelle as filiere_libelle,
                       s.libelle as semestre_libelle,
                       CONCAT(e.nom, ' ', e.prenom) as employee_fullname,
                       e.nom as employee_nom,
                       e.prenom as employee_prenom,
                       e.email as employee_email,
                       im.annee_universitaire
                FROM presence p
                INNER JOIN inscription_module im ON p.inscription_module_id = im.id
                INNER JOIN module m ON im.module_id = m.id
                INNER JOIN filiere f ON im.filiere_id = f.id
                INNER JOIN semestre s ON m.semestre_id = s.id
                INNER JOIN employee e ON im.employee_id = e.id
                WHERE p.id = :id";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        $presence = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$presence) {
            return null;
        }

        $sql2 = "SELECT s.id, s.nom, s.prenom, s.email, 
                        COALESCE(pd.present, 0) as present,
                        pd.description,
                        pd.id as presence_detail_id
                 FROM student s
                 LEFT JOIN presence_detail pd ON pd.student_id = s.id AND pd.presence_id = :presence_id
                 ORDER BY s.nom, s.prenom";

        $stmt2 = $this->pdo->prepare($sql2);
        $stmt2->execute([':presence_id' => $id]);
        $presence['students'] = $stmt2->fetchAll(PDO::FETCH_ASSOC);

        $presents = array_filter($presence['students'], function($s) {
            return $s['present'] == 1;
        });
        $presence['total_presents'] = count($presents);
        $presence['total_students'] = count($presence['students']);

        return $presence;
    }

    public function search($criteria = [], $page = 1, $perPage = 10)
    {
        $sql = "SELECT p.*,
                       p.date_seance,
                       p.heure_debut,
                       p.nb_heures,
                       p.jour_semaine,
                       p.libelle,
                       im.id as inscription_module_id,
                       m.libelle as module_libelle,
                       m.code as module_code,
                       f.libelle as filiere_libelle,
                       s.libelle as semestre_libelle,
                       CONCAT(e.nom, ' ', e.prenom) as employee_fullname,
                       im.annee_universitaire,
                       (SELECT COUNT(*) FROM presence_detail WHERE presence_id = p.id AND present = 1) as total_presents,
                       (SELECT COUNT(*) FROM presence_detail WHERE presence_id = p.id) as total_students
                FROM presence p
                INNER JOIN inscription_module im ON p.inscription_module_id = im.id
                INNER JOIN module m ON im.module_id = m.id
                INNER JOIN filiere f ON im.filiere_id = f.id
                INNER JOIN semestre s ON m.semestre_id = s.id
                INNER JOIN employee e ON im.employee_id = e.id
                WHERE 1=1";

        $params = [];
        $whereConditions = [];

        if (!empty($criteria['module_libelle'])) {
            $whereConditions[] = "m.libelle LIKE :module_libelle";
            $params[':module_libelle'] = '%' . $criteria['module_libelle'] . '%';
        }

        if (!empty($criteria['employee_nom'])) {
            $whereConditions[] = "(e.nom LIKE :employee_nom OR e.prenom LIKE :employee_nom)";
            $params[':employee_nom'] = '%' . $criteria['employee_nom'] . '%';
        }

        if (!empty($criteria['date_seance'])) {
            $whereConditions[] = "p.date_seance = :date_seance";
            $params[':date_seance'] = $criteria['date_seance'];
        }

        if (!empty($criteria['heure_debut'])) {
            $whereConditions[] = "p.heure_debut = :heure_debut";
            $params[':heure_debut'] = $criteria['heure_debut'];
        }

        if (!empty($whereConditions)) {
            $sql .= " AND " . implode(" AND ", $whereConditions);
        }

        $sql .= " ORDER BY p.date_seance DESC, p.heure_debut ASC";

        $countSql = "SELECT COUNT(*) as total FROM presence p";
        if (!empty($whereConditions)) {
            $countSql .= " WHERE " . implode(" AND ", $whereConditions);
        }

        $stmt = $this->pdo->prepare($countSql);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $stmt->execute();
        $total = $stmt->fetch(PDO::FETCH_ASSOC)['total'] ?? 0;

        $offset = ($page - 1) * $perPage;
        $sql .= " LIMIT " . intval($perPage) . " OFFSET " . intval($offset);

        $stmt = $this->pdo->prepare($sql);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $stmt->execute();
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

        return [
            'data' => $results,
            'total' => $total,
            'page' => $page,
            'perPage' => $perPage,
            'totalPages' => ceil($total / $perPage)
        ];
    }

    public function getStats()
    {
        try {
            $sql = "SELECT 
                        COUNT(DISTINCT p.id) as total_seances,
                        COUNT(DISTINCT CASE WHEN pd.present = 1 THEN pd.student_id END) as total_presences,
                        COUNT(DISTINCT im.employee_id) as total_professeurs,
                        COUNT(DISTINCT im.module_id) as total_modules
                    FROM presence p
                    INNER JOIN inscription_module im ON p.inscription_module_id = im.id
                    LEFT JOIN presence_detail pd ON p.id = pd.presence_id";

            $stmt = $this->pdo->prepare($sql);
            $stmt->execute();
            $result = $stmt->fetch(PDO::FETCH_ASSOC);

            if (!$result) {
                return [
                    'total_seances' => 0,
                    'total_presences' => 0,
                    'total_professeurs' => 0,
                    'total_modules' => 0
                ];
            }

            return $result;
        } catch (PDOException $e) {
            error_log("Erreur dans getStats: " . $e->getMessage());
            return [
                'total_seances' => 0,
                'total_presences' => 0,
                'total_professeurs' => 0,
                'total_modules' => 0
            ];
        }
    }

    public function findOne($id)
    {
        $sql = "SELECT * FROM presence WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function insert($data)
    {
        $sql = "INSERT INTO presence (inscription_module_id, date_seance, heure_debut, nb_heures, jour_semaine, libelle, description) 
                VALUES (:inscription_module_id, :date_seance, :heure_debut, :nb_heures, :jour_semaine, :libelle, :description)";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':inscription_module_id' => $data['inscription_module_id'],
            ':date_seance' => $data['date_seance'],
            ':heure_debut' => $data['heure_debut'],
            ':nb_heures' => $data['nb_heures'],
            ':jour_semaine' => $data['jour_semaine'] ?? null,
            ':libelle' => $data['libelle'] ?? '',
            ':description' => $data['description'] ?? null
        ]);

        return $this->pdo->lastInsertId();
    }

    public function delete($id)
    {
        $sql = "DELETE FROM presence WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        return true;
    }
}
?>