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

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

class InscriptionModuleService extends AbstractPdo
{
    protected $table = 'inscription_module';
    protected $primaryKey = 'id';

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

    /**
     * Search inscriptions with pagination
     */
    public function search($filters, $page = 1, $perPage = 10)
    {
        $sql = "SELECT im.*,
                       f.libelle as filiere_libelle,
                       f.code as filiere_code,
                       m.libelle as module_libelle,
                       m.code as module_code,
                       s.libelle as semestre_libelle,
                       s.code as semestre_code,
                       e.nom as employee_nom,
                       e.prenom as employee_prenom,
                       COUNT(imd.id) as nb_etudiants_inscrits
                FROM {$this->table} im
                LEFT JOIN filiere f ON im.filiere_id = f.id
                LEFT JOIN module m ON im.module_id = m.id
                LEFT JOIN semestre s ON m.semestre_id = s.id
                LEFT JOIN employee e ON im.employee_id = e.id
                LEFT JOIN inscription_module_detail imd ON im.id = imd.inscription_module_id
                WHERE 1=1";

        $params = [];
        $conditions = [];

        if (!empty($filters['annee_universitaire'])) {
            $conditions[] = "im.annee_universitaire = :annee_universitaire";
            $params[':annee_universitaire'] = $filters['annee_universitaire'];
        }

        if (!empty($filters['filiere_id'])) {
            $conditions[] = "im.filiere_id = :filiere_id";
            $params[':filiere_id'] = $filters['filiere_id'];
        }

        if (!empty($filters['module_id'])) {
            $conditions[] = "im.module_id = :module_id";
            $params[':module_id'] = $filters['module_id'];
        }

        if (!empty($filters['search'])) {
            $conditions[] = "(m.libelle LIKE :search OR f.libelle LIKE :search)";
            $params[':search'] = '%' . $filters['search'] . '%';
        }

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

        $sql .= " GROUP BY im.id ORDER BY im.created_at DESC";

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

        $stmt = $this->pdo->prepare($sql);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();

        $data = $stmt->fetchAll();

        // Count total
        $countSql = "SELECT COUNT(DISTINCT im.id) as total
                     FROM {$this->table} im
                     LEFT JOIN filiere f ON im.filiere_id = f.id
                     LEFT JOIN module m ON im.module_id = m.id
                     WHERE 1=1";

        $countConditions = [];
        $countParams = [];

        foreach ($params as $key => $value) {
            if ($key !== ':limit' && $key !== ':offset') {
                $countConditions[] = str_replace(':', '', $key) . " = " . $key;
                $countParams[$key] = $value;
            }
        }

        if (!empty($countConditions)) {
            $countSql .= " AND " . implode(" AND ", $countConditions);
        }

        $countStmt = $this->pdo->prepare($countSql);
        foreach ($countParams as $key => $value) {
            $countStmt->bindValue($key, $value);
        }
        $countStmt->execute();
        $total = $countStmt->fetch()['total'] ?? 0;

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

    /**
     * Get inscription by ID with details
     */
    public function getById($id)
    {
        $sql = "SELECT im.*,
                       f.libelle as filiere_libelle,
                       f.code as filiere_code,
                       m.libelle as module_libelle,
                       m.code as module_code,
                       s.libelle as semestre_libelle,
                       s.code as semestre_code,
                       e.nom as employee_nom,
                       e.prenom as employee_prenom
                FROM {$this->table} im
                LEFT JOIN filiere f ON im.filiere_id = f.id
                LEFT JOIN module m ON im.module_id = m.id
                LEFT JOIN semestre s ON m.semestre_id = s.id
                LEFT 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();
    }

    /**
     * Check if inscription already exists
     */
    public function checkDuplicate($filiereId, $moduleId, $anneeUniversitaire)
    {
        $sql = "SELECT id FROM {$this->table}
                WHERE filiere_id = :filiere_id
                  AND module_id = :module_id
                  AND annee_universitaire = :annee_universitaire
                LIMIT 1";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':filiere_id' => $filiereId,
            ':module_id' => $moduleId,
            ':annee_universitaire' => $anneeUniversitaire
        ]);

        return $stmt->fetch();
    }

    /**
     * Create inscription
     */
    public function create($data)
    {
        // Validate
        if (empty($data['filiere_id'])) {
            throw new Exception("La filière est obligatoire");
        }
        if (empty($data['module_id'])) {
            throw new Exception("Le module est obligatoire");
        }
        if (empty($data['annee_universitaire'])) {
            throw new Exception("L'année universitaire est obligatoire");
        }
        if (empty($data['employee_id'])) {
            throw new Exception("Le professeur est obligatoire");
        }

        // Check duplicate
        $existing = $this->checkDuplicate($data['filiere_id'], $data['module_id'], $data['annee_universitaire']);
        if ($existing) {
            throw new Exception("Une inscription existe déjà pour ce module, cette filière et cette année universitaire");
        }

        $data['created_at'] = date('Y-m-d H:i:s');
        return $this->insert($data);
    }

    /**
     * Update inscription
     */
    public function update($id, $data)
    {
        $existing = $this->findOne($id);
        if (!$existing) {
            throw new Exception("Inscription non trouvée");
        }

        $data['updated_at'] = date('Y-m-d H:i:s');
        return $this->update($id, $data);
    }

    /**
     * Delete inscription and its details
     */
    public function delete($id)
    {
        $existing = $this->findOne($id);
        if (!$existing) {
            throw new Exception("Inscription non trouvée");
        }

        // Delete details first
        $detailService = new InscriptionModuleDetailService();
        $detailService->deleteByInscriptionId($id);

        return parent::delete($id);
    }

    /**
     * Get statistics
     */
    public function getStats()
    {
        $sql = "SELECT
                    COUNT(*) as total_inscriptions,
                    COUNT(DISTINCT filiere_id) as total_filieres,
                    COUNT(DISTINCT module_id) as total_modules,
                    SUM(nb_etudiants) as total_etudiants
                FROM {$this->table}";

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

    /**
     * Get distinct years
     */
    public function getAnneesUniversitaires()
    {
        $sql = "SELECT DISTINCT annee_universitaire
                FROM {$this->table}
                ORDER BY annee_universitaire DESC";

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

    /**
     * Import CSV and create inscription with details
     */
    public function importCsv($filePath, $data, $employeeId)
    {
        try {
            $this->pdo->beginTransaction();

            // Create inscription
            $inscriptionData = [
                'filiere_id' => $data['filiere_id'],
                'module_id' => $data['module_id'],
                'annee_universitaire' => $data['annee_universitaire'],
                'employee_id' => $employeeId,
                'description' => $data['description'] ?? null,
                'created_at' => date('Y-m-d H:i:s')
            ];

            $inscriptionId = $this->insert($inscriptionData);

            // Read CSV
            $handle = fopen($filePath, 'r');
            if ($handle === false) {
                throw new Exception("Impossible d'ouvrir le fichier CSV");
            }

            $firstLine = fgets($handle);
            rewind($handle);
            $delimiter = (strpos($firstLine, ';') !== false) ? ';' : ',';

            $header = fgetcsv($handle, 0, $delimiter);
            if (!$header) {
                fclose($handle);
                throw new Exception("Le fichier CSV est vide ou invalide");
            }

            $header = array_map('trim', $header);
            $columnName = isset($data['column_name']) ? $data['column_name'] : 'cin';

            if (!in_array($columnName, $header)) {
                fclose($handle);
                throw new Exception("La colonne '$columnName' n'existe pas dans le fichier CSV");
            }

            $studentCodes = [];
            $rowNumber = 0;

            while (($row = fgetcsv($handle, 0, $delimiter)) !== false) {
                $rowNumber++;
                $rowData = [];
                foreach ($header as $index => $col) {
                    if (isset($row[$index])) {
                        $rowData[$col] = trim($row[$index]);
                    }
                }

                if (!empty($rowData[$columnName])) {
                    $studentCodes[] = $rowData[$columnName];
                }
            }
            fclose($handle);

            if (empty($studentCodes)) {
                throw new Exception("Aucun étudiant trouvé dans le fichier CSV");
            }

            // Find students by CIN
            $studentIds = [];
            $notFound = [];

            foreach ($studentCodes as $code) {
                $sql = "SELECT id FROM student WHERE cin = :cin";
                $stmt = $this->pdo->prepare($sql);
                $stmt->execute([':cin' => $code]);
                $student = $stmt->fetch();

                if ($student) {
                    $studentIds[] = $student['id'];
                } else {
                    $notFound[] = $code;
                }
            }

            // Create details
            $detailService = new InscriptionModuleDetailService();
            $inserted = 0;
            $skipped = 0;

            foreach ($studentIds as $studentId) {
                try {
                    $detailService->createDetail([
                        'inscription_module_id' => $inscriptionId,
                        'student_id' => $studentId,
                        'description' => "Importé depuis CSV",
                        'created_at' => date('Y-m-d H:i:s')
                    ]);
                    $inserted++;
                } catch (Exception $e) {
                    $skipped++;
                }
            }

            // Update nb_etudiants
            $detailService->updateNbEtudiants($inscriptionId);

            $this->pdo->commit();

            return [
                'success' => true,
                'inscription_id' => $inscriptionId,
                'inserted' => $inserted,
                'skipped' => $skipped,
                'not_found' => $notFound,
                'total_rows' => $rowNumber
            ];

        } catch (Exception $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            throw $e;
        }
    }
}