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

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

class SemestreService extends AbstractPdo {
    protected $table = 'semestre';
    protected $primaryKey = 'id';

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

    /**
     * Rechercher des semestres avec critères
     */
    public function search($searchData, $page = 1, $perPage = 10) {
        $filters = [];

        // Filtres sur les champs de semestre
        $searchFields = ['id', 'libelle', 'code', 'description', 'annee_universitaire'];
        foreach ($searchFields as $field) {
            if (!empty($searchData[$field])) {
                $filters[$field] = [
                    'value' => $searchData[$field],
                    'like' => true
                ];
            }
        }

        // Filtre par date (recherche exacte)
        if (!empty($searchData['date_debut'])) {
            $filters['date_debut'] = [
                'value' => $searchData['date_debut'],
                'like' => false
            ];
        }

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

        $offset = ($page - 1) * $perPage;
        $semestres = $this->findWithModules($filters, $perPage, $offset);
        $total = $this->countWithModules($filters);

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

    /**
     * Find semestres with module information
     */
    public function findWithModules($filters = [], $limit = null, $offset = null) {
        $sql = "SELECT s.*, 
                       COUNT(m.id) as nombre_modules,
                       SUM(m.charge_horaire_cours) as total_cours,
                       SUM(m.charge_horaire_tp) as total_tp,
                       SUM(m.charge_horaire_td) as total_td
                FROM semestre s
                LEFT JOIN module m ON s.id = m.semestre_id";

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

        foreach ($filters as $key => $filter) {
            $field = "s.{$key}";

            if (isset($filter['like']) && $filter['like'] && !empty($filter['value'])) {
                $paramKey = str_replace('.', '_', $key);
                $where[] = "{$field} LIKE :{$paramKey}";
                $params[":{$paramKey}"] = '%' . $filter['value'] . '%';
            } elseif (!empty($filter['value'])) {
                $paramKey = str_replace('.', '_', $key);
                $where[] = "{$field} = :{$paramKey}";
                $params[":{$paramKey}"] = $filter['value'];
            }
        }

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

        $sql .= " GROUP BY s.id, s.libelle, s.code, s.description, s.annee_universitaire, s.date_debut, s.date_fin, s.date_creation, s.date_modification";
        $sql .= " ORDER BY s.annee_universitaire DESC, s.date_debut ASC";

        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();
    }

    /**
     * Count semestres with filters
     */
    public function countWithModules($filters = []) {
        $sql = "SELECT COUNT(DISTINCT s.id) as count 
                FROM semestre s
                LEFT JOIN module m ON s.id = m.semestre_id";

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

        foreach ($filters as $key => $filter) {
            $field = "s.{$key}";

            if (isset($filter['like']) && $filter['like'] && !empty($filter['value'])) {
                $paramKey = str_replace('.', '_', $key);
                $where[] = "{$field} LIKE :{$paramKey}";
                $params[":{$paramKey}"] = '%' . $filter['value'] . '%';
            } elseif (!empty($filter['value'])) {
                $paramKey = str_replace('.', '_', $key);
                $where[] = "{$field} = :{$paramKey}";
                $params[":{$paramKey}"] = $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'];
    }

    /**
     * Obtenir un semestre par ID avec ses modules
     */
    public function getSemestre($id) {
        $sql = "SELECT s.*, 
                       COUNT(m.id) as nombre_modules,
                       SUM(m.charge_horaire_cours) as total_cours,
                       SUM(m.charge_horaire_tp) as total_tp,
                       SUM(m.charge_horaire_td) as total_td
                FROM semestre s
                LEFT JOIN module m ON s.id = m.semestre_id
                WHERE s.id = :id
                GROUP BY s.id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        return $stmt->fetch();
    }

    /**
     * Obtenir un semestre par code
     */
    public function getSemestreByCode($code) {
        return $this->findOneBy(['code' => $code]);
    }

    /**
     * Obtenir un semestre par année universitaire
     */
    public function getSemestresByAnnee($annee) {
        return $this->findAllBy(['annee_universitaire' => $annee], 'date_debut ASC');
    }

    /**
     * Obtenir les semestres actifs (ceux qui sont en cours)
     */
    public function getSemestresActifs() {
        $sql = "SELECT * FROM semestre 
                WHERE date_debut <= CURDATE() AND date_fin >= CURDATE() 
                ORDER BY date_debut ASC";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    /**
     * Créer un nouveau semestre
     */
    public function createSemestre($data) {
        // Valider les données
        if (empty($data['libelle'])) {
            throw new Exception("Le libellé est obligatoire");
        }
        if (empty($data['code'])) {
            throw new Exception("Le code est obligatoire");
        }
        if (empty($data['annee_universitaire'])) {
            throw new Exception("L'année universitaire est obligatoire");
        }

        // Vérifier les doublons
        $this->checkDuplicates($data);

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

    /**
     * Mettre à jour un semestre
     */
    public function updateSemestre($id, $data) {
        // Vérifier si le semestre existe
        $existing = $this->findOne($id);
        if (!$existing) {
            throw new Exception("Semestre non trouvé");
        }

        // Vérifier les doublons (exclure l'ID actuel)
        $this->checkDuplicates($data, $id);

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

    /**
     * Vérifier les doublons
     */
    private function checkDuplicates($data, $excludeId = null) {
        $uniqueFields = ['code'];

        foreach ($uniqueFields as $field) {
            if (isset($data[$field]) && !empty($data[$field])) {
                $criteria = [$field => $data[$field]];
                $existing = $this->findOneBy($criteria);

                if ($existing && ($excludeId === null || $existing['id'] != $excludeId)) {
                    throw new Exception("Le champ '$field' est déjà utilisé par un autre semestre");
                }
            }
        }
    }

    /**
     * Supprimer un semestre
     */
    public function deleteSemestre($id) {
        // Vérifier si le semestre a des modules
        $sql = "SELECT COUNT(*) as count FROM module WHERE semestre_id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        $result = $stmt->fetch();

        if ($result['count'] > 0) {
            throw new Exception("Ce semestre contient des modules et ne peut pas être supprimé");
        }

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

    /**
     * Obtenir les statistiques
     */
    public function getStats() {
        $sql = "SELECT COUNT(*) as total
            FROM semestre";

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

    /**
     * Obtenir toutes les années universitaires distinctes
     */
    public function getAnneesUniversitaires() {
        $sql = "SELECT DISTINCT annee_universitaire FROM semestre ORDER BY annee_universitaire DESC";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    /**
     * Exporter les semestres (CSV)
     */
    public function exportToCsv() {
        $semestres = $this->findWithModules([], null, null);

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

        $csv = "ID,Libellé,Code,Année Universitaire,Date Début,Date Fin,Nombre Modules,Total Cours,Total TP,Total TD,Description\n";
        foreach ($semestres as $s) {
            $csv .= implode(',', [
                $s['id'],
                '"' . addslashes($s['libelle']) . '"',
                '"' . addslashes($s['code']) . '"',
                '"' . addslashes($s['annee_universitaire'] ?? '') . '"',
                $s['date_debut'] ?? '',
                $s['date_fin'] ?? '',
                $s['nombre_modules'] ?? 0,
                $s['total_cours'] ?? 0,
                $s['total_tp'] ?? 0,
                $s['total_td'] ?? 0,
                '"' . addslashes($s['description'] ?? '') . '"'
            ]) . "\n";
        }

        return $csv;
    }

    /**
     * Obtenir tous les semestres (pour les selects)
     */
    public function getAllSemestres() {
        return $this->findAll('annee_universitaire DESC, date_debut ASC');
    }
}