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

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

class AffectationModuleService extends AbstractPdo
{
    protected $table = 'affectation_module';
    protected $primaryKey = 'id';

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

    public function getPdo()
    {
        return $this->pdo;
    }

    public function search($searchData, $page = 1, $perPage = 10)
    {
        $filters = [];

        $searchFields = [
            'id',
            'module_id',
            'filiere_id',
            'semestre_id',
            'employee_id',
            'annee_universitaire'
        ];

        foreach ($searchFields as $field) {
            if (!empty($searchData[$field])) {
                $filters[$field] = [
                    'value' => $searchData[$field],
                    'like' => false
                ];
            }
        }

        if (!empty($searchData['module_libelle'])) {
            $filters['module_libelle'] = [
                'value' => $searchData['module_libelle'],
                'like' => true
            ];
        }

        if (!empty($searchData['employee_nom'])) {
            $filters['employee_nom'] = [
                'value' => $searchData['employee_nom'],
                'like' => true
            ];
        }

        $offset = ($page - 1) * $perPage;

        $data = $this->findWithDetails($filters, $perPage, $offset);
        $total = $this->countWithFilters($filters);

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

    public function findWithDetails($filters = [], $limit = null, $offset = null)
    {
        $sql = "SELECT
                    am.*,
                    m.libelle AS module_libelle,
                    m.code AS module_code,
                    f.libelle AS filiere_libelle,
                    f.code AS filiere_code,
                    s.libelle AS semestre_libelle,
                    s.code AS semestre_code,
                    e.nom AS employee_nom,
                    e.prenom AS employee_prenom,
                    e.code AS employee_code,
                    CONCAT(e.nom, ' ', e.prenom) AS employee_fullname
                FROM affectation_module am
                LEFT JOIN module m ON am.module_id = m.id
                LEFT JOIN filiere f ON am.filiere_id = f.id
                LEFT JOIN semestre s ON am.semestre_id = s.id
                LEFT JOIN employee e ON am.employee_id = e.id";

        $where = [];
        $params = [];

        foreach ($filters as $key => $filter) {
            if ($key === 'module_libelle') {
                $where[] = "m.libelle LIKE :module_libelle";
                $params[':module_libelle'] = "%" . $filter['value'] . "%";
                continue;
            }

            if ($key === 'employee_nom') {
                $where[] = "(e.nom LIKE :employee_nom OR e.prenom LIKE :employee_nom)";
                $params[':employee_nom'] = "%" . $filter['value'] . "%";
                continue;
            }

            $field = "am." . $key;

            if (isset($filter['like']) && $filter['like']) {
                $where[] = "{$field} LIKE :{$key}";
                $params[":{$key}"] = "%" . $filter['value'] . "%";
            } else {
                $where[] = "{$field} = :{$key}";
                $params[":{$key}"] = $filter['value'];
            }
        }

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

        $sql .= " ORDER BY am.id DESC";

        if ($limit !== null) {
            $sql .= " LIMIT " . (int)$limit;
            if ($offset !== null) {
                $sql .= " OFFSET " . (int)$offset;
            }
        }

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

    public function countWithFilters($filters = [])
    {
        $sql = "SELECT COUNT(DISTINCT am.id) as count
                FROM affectation_module am
                LEFT JOIN module m ON am.module_id = m.id
                LEFT JOIN employee e ON am.employee_id = e.id";

        $where = [];
        $params = [];

        foreach ($filters as $key => $filter) {
            if ($key === 'module_libelle') {
                $where[] = "m.libelle LIKE :module_libelle";
                $params[':module_libelle'] = "%" . $filter['value'] . "%";
                continue;
            }

            if ($key === 'employee_nom') {
                $where[] = "(e.nom LIKE :employee_nom OR e.prenom LIKE :employee_nom)";
                $params[':employee_nom'] = "%" . $filter['value'] . "%";
                continue;
            }

            $field = "am." . $key;

            if (isset($filter['like']) && $filter['like']) {
                $where[] = "{$field} LIKE :{$key}";
                $params[":{$key}"] = "%" . $filter['value'] . "%";
            } else {
                $where[] = "{$field} = :{$key}";
                $params[":{$key}"] = $filter['value'];
            }
        }

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

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $result = $stmt->fetch();
        return (int)$result['count'];
    }

    public function getAffectation($id)
    {
        $sql = "SELECT
                    am.*,
                    m.libelle AS module_libelle,
                    m.code AS module_code,
                    f.libelle AS filiere_libelle,
                    f.code AS filiere_code,
                    s.libelle AS semestre_libelle,
                    s.code AS semestre_code,
                    e.nom AS employee_nom,
                    e.prenom AS employee_prenom,
                    e.code AS employee_code,
                    e.email AS employee_email,
                    CONCAT(e.nom, ' ', e.prenom) AS employee_fullname
                FROM affectation_module am
                LEFT JOIN module m ON am.module_id = m.id
                LEFT JOIN filiere f ON am.filiere_id = f.id
                LEFT JOIN semestre s ON am.semestre_id = s.id
                LEFT JOIN employee e ON am.employee_id = e.id
                WHERE am.id = :id";

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

    public function getAffectationsByModule($moduleId)
    {
        return $this->findAllBy(['module_id' => $moduleId]);
    }

    public function getAffectationsByEmployee($employeeId, $anneeUniv = null)
    {
        $criteria = ['employee_id' => $employeeId];
        if ($anneeUniv) {
            $criteria['annee_universitaire'] = $anneeUniv;
        }
        return $this->findAllBy($criteria, 'id DESC');
    }

    public function getAffectationsByAnnee($anneeUniv)
    {
        return $this->findAllBy(['annee_universitaire' => $anneeUniv]);
    }

    public function createAffectation($data)
    {
        $required = ['module_id', 'filiere_id', 'semestre_id', 'employee_id', 'annee_universitaire'];
        foreach ($required as $field) {
            if (empty($data[$field])) {
                throw new Exception("Le champ '$field' est obligatoire");
            }
        }

        $this->checkDuplicates($data);
        return $this->insert($data);
    }

    public function updateAffectation($id, $data)
    {
        $existing = $this->findOne($id);
        if (!$existing) {
            throw new Exception("Affectation non trouvée");
        }

        $this->checkDuplicates($data, $id);
        return $this->update($id, $data);
    }

    private function checkDuplicates($data, $excludeId = null)
    {
        if (isset($data['module_id']) && isset($data['employee_id']) && isset($data['annee_universitaire'])) {
            $criteria = [
                'module_id' => $data['module_id'],
                'employee_id' => $data['employee_id'],
                'annee_universitaire' => $data['annee_universitaire']
            ];
            $existing = $this->findOneBy($criteria);

            if ($existing && ($excludeId === null || $existing['id'] != $excludeId)) {
                throw new Exception("Ce professeur est déjà affecté à ce module pour cette année universitaire");
            }
        }
    }

    public function deleteAffectation($id)
    {
        $sql = "SELECT COUNT(*) as count FROM presence WHERE module_id = (SELECT module_id FROM affectation_module WHERE id = :id) 
                AND employee_id = (SELECT employee_id FROM affectation_module WHERE id = :id)";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        $result = $stmt->fetch();

        if ($result['count'] > 0) {
            throw new Exception("Cette affectation a des séances de présence et ne peut pas être supprimée");
        }

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

    public function getStats()
    {
        try {
            $sql = "SELECT 
                        COUNT(*) as total,
                        COUNT(DISTINCT employee_id) as total_professeurs,
                        COUNT(DISTINCT module_id) as total_modules,
                        COUNT(DISTINCT filiere_id) as total_filieres
                    FROM affectation_module";

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

            $sql2 = "SELECT annee_universitaire, COUNT(*) as total 
                    FROM affectation_module 
                    GROUP BY annee_universitaire 
                    ORDER BY annee_universitaire DESC";

            $stmt2 = $this->pdo->prepare($sql2);
            $stmt2->execute();
            $result['par_annee'] = $stmt2->fetchAll();

            return $result;
        } catch (PDOException $e) {
            error_log("Erreur dans getStats: " . $e->getMessage());
            throw $e;
        }
    }

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

    public function getProfesseursAffectes()
    {
        $sql = "SELECT DISTINCT 
                    e.id, 
                    e.nom, 
                    e.prenom,
                    CONCAT(e.nom, ' ', e.prenom) as fullname
                FROM affectation_module am
                JOIN employee e ON am.employee_id = e.id
                ORDER BY e.nom, e.prenom";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function exportToCsv()
    {
        $affectations = $this->findWithDetails([], null, null);

        if (empty($affectations)) {
            return null;
        }

        $csv = "ID,Module,Filière,Semestre,Professeur,Année Universitaire\n";
        foreach ($affectations as $a) {
            $csv .= implode(',', [
                    $a['id'],
                    '"' . addslashes($a['module_libelle'] ?? '') . '"',
                    '"' . addslashes($a['filiere_libelle'] ?? '') . '"',
                    '"' . addslashes($a['semestre_libelle'] ?? '') . '"',
                    '"' . addslashes($a['employee_fullname'] ?? '') . '"',
                    '"' . addslashes($a['annee_universitaire'] ?? '') . '"'
                ]) . "\n";
        }

        return $csv;
    }
}