<?php
// app/models/InscriptionConcours.php
require_once __DIR__ . '/../core/AbstractPdo.php';
require_once 'ConcoursService.php';
require_once 'UtilService.php';

class InscriptionConcoursService extends AbstractPdo
{
    protected $table = 'inscription_concours';
    protected $primaryKey = 'id';
    protected $concoursService = null;


    public function __construct()
    {
        parent::__construct();
        $this->concoursService = new ConcoursService();
    }
    /**
     * Get all inscriptions with related data (sans pagination)
     */
    public function getAllWithDetails()
    {
        $sql = "SELECT i.*, 
                   c.code as concours_code, c.label as concours_label, c.filiere,
                   s.nom as candidat_nom, s.prenom as candidat_prenom, s.email as candidat_email,
                   td.code as diplome_code, td.libelle as diplome_libelle, td.annee_apres_bac
            FROM {$this->table} i
            LEFT JOIN concours c ON i.concours_id = c.id
            LEFT JOIN candidat s ON i.candidat_id = s.id
            LEFT JOIN type_diplome td ON i.type_diplome_obtenu = td.id
            ORDER BY i.moyenne_general_calcule DESC";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /**
     * Get types de diplôme by concours ID
     */
    public function getTypeDiplomeByConcours($concoursId)
    {
        $sql = "SELECT cd.type_diplome_id, td.libelle, td.code, td.annee_apres_bac
            FROM concours_detail cd
            INNER JOIN type_diplome td ON cd.type_diplome_id = td.id
            WHERE cd.concours_id = :concours_id
            ORDER BY td.libelle ASC";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':concours_id' => $concoursId]);
        return $stmt->fetchAll();
    }

    /**
     * Find inscription by candidat_id and concours_id
     */
    public function findInscriptionByCandidatAndConcours($candidatId, $concoursId)
    {
        $sql = "SELECT * FROM {$this->table} 
                WHERE candidat_id = :candidat_id AND concours_id = :concours_id 
                LIMIT 1";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':candidat_id' => $candidatId,
            ':concours_id' => $concoursId
        ]);
        return $stmt->fetch();
    }
    /**
     * Find inscription by candidat_id and concours_id
     */
    public function findInscriptionByCandidat($candidatId)
    {
        $sql = "SELECT * FROM {$this->table} WHERE candidat_id = :candidat_id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':candidat_id' => $candidatId
        ]);
        return $stmt->fetchAll();
    }

    /**
     * Find inscription by ID
     */
    public function getById($id)
    {
        $sql = "SELECT * FROM {$this->table} 
                WHERE id = :id
                LIMIT 1";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':id' => $id
        ]);
        return $stmt->fetch();
    }

    /**
     * Create or update an inscription
     */
    public function createOrUpdateInscription($data)
    {
        // الاحتفاظ بالـ IDs قبل الـ unset عشان نرجعوهم في الـ return بشكل صحيح
        $candidatId = $data['candidat_id'] ?? null;
        $concoursId = $data['concours_id'] ?? null;

        $existing = $this->findInscriptionByCandidatAndConcours($candidatId, $concoursId);

        if ($existing) {
            unset($data['candidat_id']);
            unset($data['concours_id']);
            $data['date_modification'] = date('Y-m-d H:i:s');

            if (isset($data['note_s1']) || isset($data['note_s2']) ||
                isset($data['note_s3']) || isset($data['note_s4'])) {
                $data['moyenne_general_calcule'] = $this->calculateMoyenne($data);
            }

            $this->update($existing['id'], $data);

            return [
                'id' => $existing['id'],
                'is_new' => false,
                'candidat_id' => $candidatId,
                'concours_id' => $concoursId,
                'cin' => $existing['cin'] ?? $data['cin'] ?? ''
            ];

        } else {
            $utilService = new \model\service\UtilService();
            $concours = $this->concoursService->findOne($concoursId);
            $utilService->createConcoursFolder($concours["code"] . '/' . $data['cin']);

            if (empty($data['code'])) {
                $data['code'] = 'INSC-' . time() . rand(100, 999);
            }

            $data['active'] = $data['active'] ?? 1;
            $data['etat_inscription'] = $data['etat_inscription'] ?? 'EN_ATTENTE';
            $data['resultat_inscription'] = $data['resultat_inscription'] ?? 'EN_COURS';
            $data['module_valide'] = $data['module_valide'] ?? 0;
            $data['date_inscription'] = $data['date_inscription'] ?? date('Y-m-d');
            $data['date_creation'] = date('Y-m-d H:i:s');

            if ($data['type_diplome_obtenu'] === '' || $data['type_diplome_obtenu'] === null) {
                $data['type_diplome_obtenu'] = 1;
            }

            if (!empty($data['note_s1']) || !empty($data['note_s2']) ||
                !empty($data['note_s3']) || !empty($data['note_s4'])) {
                $data['moyenne_general_calcule'] = $this->calculateMoyenne($data);
            }

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

            return [
                'id' => $id,
                'is_new' => true,
                'candidat_id' => $candidatId,
                'concours_id' => $concoursId,
                'cin' => $data['cin'] ?? ''
            ];
        }
    }

    /**
     * Get inscriptions grouped by type de diplôme
     */
    public function getGroupedByTypeDiplome()
    {
        $sql = "SELECT 
                    td.id as type_diplome_id,
                    td.code as type_diplome_code,
                    td.libelle as type_diplome_libelle,
                    td.annee_apres_bac,
                    COUNT(i.id) as total_inscriptions,
                    SUM(CASE WHEN i.etat_inscription = 'VALIDE' THEN 1 ELSE 0 END) as valide,
                    SUM(CASE WHEN i.etat_inscription = 'EN_ATTENTE' THEN 1 ELSE 0 END) as en_attente,
                    SUM(CASE WHEN i.etat_inscription = 'REJETE' THEN 1 ELSE 0 END) as rejete,
                    SUM(CASE WHEN i.resultat_inscription = 'ADMIS' THEN 1 ELSE 0 END) as admis,
                    SUM(CASE WHEN i.resultat_inscription = 'LISTE_ATTENTE' THEN 1 ELSE 0 END) as liste_attente,
                    SUM(CASE WHEN i.resultat_inscription = 'NON_ADMIS' THEN 1 ELSE 0 END) as non_admis,
                    AVG(i.moyenne_general_calcule) as moyenne_generale
                FROM {$this->table} i
                LEFT JOIN type_diplome td ON i.type_diplome_obtenu = td.id
                WHERE i.type_diplome_obtenu IS NOT NULL
                GROUP BY i.type_diplome_obtenu
                ORDER BY td.libelle ASC";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    /**
     * Get inscriptions by type de diplôme
     */
    public function getByTypeDiplome($typeDiplomeId)
    {
        $sql = "SELECT i.*, 
                       c.code as concours_code, c.label as concours_label,
                       s.nom as candidat_nom, s.prenom as candidat_prenom, s.email as candidat_email
                FROM {$this->table} i
                LEFT JOIN concours c ON i.concours_id = c.id
                LEFT JOIN candidat s ON i.candidat_id = s.id
                WHERE i.type_diplome_obtenu = :typeDiplomeId
                ORDER BY i.id DESC";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':typeDiplomeId' => $typeDiplomeId]);
        return $stmt->fetchAll();
    }

    /**
     * Get inscription by ID with all details
     */
    public function getInscriptionWithDetails($id)
    {
        $sql = "SELECT i.*, 
                       c.code as concours_code, c.label as concours_label, c.filiere,
                       s.cin as candidat_cin,
                       s.nom as candidat_nom, s.prenom as candidat_prenom, s.email as candidat_email,
                       td.code as diplome_code, td.libelle as diplome_libelle, td.annee_apres_bac
                FROM {$this->table} i
                LEFT JOIN concours c ON i.concours_id = c.id
                LEFT JOIN candidat s ON i.candidat_id = s.id
                LEFT JOIN type_diplome td ON i.type_diplome_obtenu = td.id
                WHERE i.id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        return $stmt->fetch();
    }
    /**
     * Get inscription by ID with all details
     */
    public function getInscriptionWithCinAndConcoursCode($id)
    {
        $sql = "SELECT i.*, 
                       c.code as concours_code, c.label as concours_label, c.filiere,
                       s.nom as candidat_nom, s.prenom as candidat_prenom, s.email as candidat_email
                FROM {$this->table} i
                LEFT JOIN concours c ON i.concours_id = c.id
                LEFT JOIN candidat s ON i.candidat_id = s.id
                WHERE i.id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        return $stmt->fetch();
    }

    public function getAllConcours()
    {
        $sql = "SELECT * FROM concours WHERE active = 1 ORDER BY code";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function getAllCandidats()
    {
        $sql = "SELECT * FROM candidat ORDER BY nom, prenom";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    /**
     * Récupère les candidats inscrits à un concours donné (CIN réel + moyenne "avant écrit" existante)
     * afin de générer un exemple de CSV d'import "avant écrit" prêt à l'emploi pour l'admin.
     */
    public function getCandidatsForAvantEcritSample($concoursId)
    {
        $sql = "SELECT s.cin, s.nom, s.prenom, i.moyenne_calcule_avant_ecrit
                FROM {$this->table} i
                LEFT JOIN candidat s ON i.candidat_id = s.id
                WHERE i.concours_id = :concours_id AND s.cin IS NOT NULL AND s.cin != ''
                ORDER BY s.nom, s.prenom";
        $stmt = $this->pdo->prepare($sql);
        $stmt->bindValue(':concours_id', (int)$concoursId, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    /**
     * Récupère les candidats inscrits à un concours donné (CIN réel + moyenne écrit + moyenne "avant orale" existantes)
     * afin de générer un exemple de CSV d'import "écrit + avant orale" prêt à l'emploi pour l'admin.
     */
    public function getCandidatsForAvantOraleSample($concoursId)
    {
        $sql = "SELECT s.cin, s.nom, s.prenom, i.moyenne_ecrit, i.moyenne_calcule_avant_orale
                FROM {$this->table} i
                LEFT JOIN candidat s ON i.candidat_id = s.id
                WHERE i.concours_id = :concours_id AND s.cin IS NOT NULL AND s.cin != ''
                ORDER BY s.nom, s.prenom";
        $stmt = $this->pdo->prepare($sql);
        $stmt->bindValue(':concours_id', (int)$concoursId, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function getAllTypeDiplome()
    {
        $sql = "SELECT * FROM type_diplome ORDER BY libelle";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function createInscription($data)
    {
        if (empty($data['code'])) {
            $data['code'] = 'INSC' . time() . rand(100, 999);
        }
        $data['active'] = $data['active'] ?? 1;
        $data['etat_inscription'] = $data['etat_inscription'] ?? 'EN_ATTENTE';
        $data['resultat_inscription'] = $data['resultat_inscription'] ?? 'EN_COURS';
        $data['module_valide'] = $data['module_valide'] ?? 0;
        $data['date_inscription'] = $data['date_inscription'] ?? date('Y-m-d');
        $data['date_creation'] = date('Y-m-d H:i:s');
        $data['moyenne_general_calcule'] = $this->calculateMoyenne($data);
        return $this->insert($data);
    }

    public function updateInscription($id, $data)
    {
        $data['date_modification'] = date('Y-m-d H:i:s');
        if (isset($data['note_s1']) || isset($data['note_s2']) || isset($data['note_s3']) || isset($data['note_s4'])) {
            $data['moyenne_general_calcule'] = $this->calculateMoyenne($data);
        }
        return $this->update($id, $data);
    }

    private function calculateMoyenne($data)
    {
        $notes = [];
        if (isset($data['note_s1']) && $data['note_s1'] !== null && $data['note_s1'] !== '') $notes[] = floatval($data['note_s1']);
        if (isset($data['note_s2']) && $data['note_s2'] !== null && $data['note_s2'] !== '') $notes[] = floatval($data['note_s2']);
        if (isset($data['note_s3']) && $data['note_s3'] !== null && $data['note_s3'] !== '') $notes[] = floatval($data['note_s3']);
        if (isset($data['note_s4']) && $data['note_s4'] !== null && $data['note_s4'] !== '') $notes[] = floatval($data['note_s4']);

        if (count($notes) > 0) {
            return round(array_sum($notes) / count($notes), 2);
        }
        return null;
    }

    public function getStats()
    {
        $sql = "SELECT COUNT(*) as total,
                    SUM(CASE WHEN etat_inscription = 'VALIDE' THEN 1 ELSE 0 END) as valide,
                    SUM(CASE WHEN etat_inscription = 'EN_ATTENTE' THEN 1 ELSE 0 END) as en_attente,
                    SUM(CASE WHEN etat_inscription = 'REJETE' THEN 1 ELSE 0 END) as rejete,
                    SUM(CASE WHEN resultat_inscription = 'ADMIS' THEN 1 ELSE 0 END) as admis,
                    SUM(CASE WHEN resultat_inscription = 'LISTE_ATTENTE' THEN 1 ELSE 0 END) as liste_attente,
                    SUM(CASE WHEN resultat_inscription = 'NON_ADMIS' THEN 1 ELSE 0 END) as non_admis,
                    AVG(moyenne_general_calcule) as moyenne_generale
                FROM {$this->table}";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetch();
    }

    /**
     * Build search conditions for inscriptions
     */
    private function buildSearchConditions($filters, $dataMode = '')
    {
        $sql = "SELECT i.*, c.code as concours_code, c.label as concours_label,
                   s.nom as candidat_nom, s.prenom as candidat_prenom, s.email as candidat_email, s.cin as candidat_cin,
                   td.libelle as type_diplome_libelle, td.code as type_diplome_code
            FROM {$this->table} i
            LEFT JOIN concours c ON i.concours_id = c.id
            LEFT JOIN candidat s ON i.candidat_id = s.id
            LEFT JOIN type_diplome td ON i.type_diplome_obtenu = td.id
            WHERE 1=1";

        $params = [];
        $conditions = [];
        if (!empty($filters['candidat_search'])) {
            $conditions[] = "(s.nom LIKE :candidat_search 
                          OR s.prenom LIKE :candidat_search 
                          OR s.email LIKE :candidat_search 
                          OR s.cin LIKE :candidat_search)";
            $params[':candidat_search'] = '%' . $filters['candidat_search'] . '%';
        }
        // Search by code
        if (!empty($filters['code'])) {
            $conditions[] = "i.code LIKE :code";
            $params[':code'] = '%' . $filters['code'] . '%';
        }

        // Search by label
        if (!empty($filters['label'])) {
            $conditions[] = "i.label LIKE :label";
            $params[':label'] = '%' . $filters['label'] . '%';
        }

        // Search by concours ID
        if (!empty($filters['concours_id'])) {
            $conditions[] = "i.concours_id = :concours_id";
            $params[':concours_id'] = (int)$filters['concours_id'];
        }

        // Search by type diplome ID
        if (!empty($filters['type_diplome_id']) || !empty($filters['diplome_id'])) {
            $diplomeId = !empty($filters['diplome_id']) ? $filters['diplome_id'] : $filters['type_diplome_id'];
            $conditions[] = "i.type_diplome_obtenu = :type_diplome_id";
            $params[':type_diplome_id'] = (int)$diplomeId;
        }

        // Search by etat inscription
        if (!empty($filters['etat_inscription'])) {
            $conditions[] = "i.etat_inscription = :etat_inscription";
            $params[':etat_inscription'] = $filters['etat_inscription'];
        }

        // Search by resultat inscription
        if (!empty($filters['resultat_inscription'])) {
            $conditions[] = "i.resultat_inscription = :resultat_inscription";
            $params[':resultat_inscription'] = $filters['resultat_inscription'];
        }

        // Search by active status
        if (isset($filters['active']) && $filters['active'] !== '') {
            $conditions[] = "i.active = :active";
            $params[':active'] = (int)$filters['active'];
        }

        // Add data mode constraint
        $resultatInscriptionConstraint = '';
        if ($dataMode === 'preparation-ecrit') {
            $resultatInscriptionConstraint = "('ELLIBGIBLE_ECRIT','NON_ELLIBGIBLE_ECRIT','EN_COURS')";
        } elseif ($dataMode === 'preparation-orale') {
            $resultatInscriptionConstraint = "('ELIGIBLE_ORALE','NON_ELIGIBLE_ORALE','ELLIBGIBLE_ECRIT')";
        } elseif ($dataMode === 'resultat-final') {
            $resultatInscriptionConstraint = "('ADMIS_ORALE','NON_ADMIS_ECRIT','ELIGIBLE_ORALE')";
        }

        if (!empty($resultatInscriptionConstraint)) {
            $conditions[] = "i.resultat_inscription IN $resultatInscriptionConstraint";
        }

        // Build final SQL with conditions
        if (!empty($conditions)) {
            $sql .= " AND " . implode(" AND ", $conditions);
        }

        return [
            'sql' => $sql,
            'params' => $params,
            'conditions' => $conditions
        ];
    }

    /**
     * Search inscriptions with pagination
     */
    public function search($filters)
    {
        $page = isset($filters['page']) ? (int)$filters['page'] : 1;
        $singlePage = isset($filters['singlePage']) ? (bool)$filters['singlePage'] : false;
        $perPage = isset($filters['per_page']) ? (int)$filters['per_page'] : 10;
        $offset = ($page - 1) * $perPage;
        $dataMode = isset($filters['dataMode']) ? $filters['dataMode'] : '';

        // Build search conditions
        $searchResult = $this->buildSearchConditions($filters, $dataMode);
        $sql = $searchResult['sql'];
        $params = $searchResult['params'];

        // Count total
        $countSql = "SELECT COUNT(*) as total 
                 FROM {$this->table} i
                 LEFT JOIN concours c ON i.concours_id = c.id
                 LEFT JOIN candidat s ON i.candidat_id = s.id
                 LEFT JOIN type_diplome td ON i.type_diplome_obtenu = td.id
                 WHERE 1=1";

        // Add the same conditions to count query
        $conditions = $searchResult['conditions'];
        if (!empty($conditions)) {
            $countSql .= " AND " . implode(" AND ", $conditions);
        }

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

        // Add ordering and pagination to main query
        if ($dataMode === 'preparation-ecrit') {
            // Classement des candidats par moyenne "avant écrit" (les valeurs NULL sont automatiquement placées en dernier avec DESC)
            $sql .= " ORDER BY i.moyenne_calcule_avant_ecrit DESC";
        } elseif ($dataMode === 'preparation-orale') {
            // Classement des candidats par moyenne "avant orale" (les valeurs NULL sont automatiquement placées en dernier avec DESC)
            $sql .= " ORDER BY i.moyenne_calcule_avant_orale DESC";
        } else {
            $sql .= " ORDER BY i.moyenne_general_calcule DESC";
        }

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

        $stmt = $this->pdo->prepare($sql);
        foreach ($params as $key => $value) {
            if ($key !== ':limit' && $key !== ':offset') {
                $stmt->bindValue($key, $value);
            }
        }

        if (!$singlePage) {
            $stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
            $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        }

        $stmt->execute();

        // ------------------ التعديل هنا ------------------
        // إذا كان هاد الملف داخل model/api/، خاصو يطلع بـ 3 ديال المستويات باش يوصل للـ racine
        $uploadDir = dirname(__DIR__, 3) . '/concours/';
        // -------------------------------------------------

        $data = [];
        while ($row = $stmt->fetch()) {
            $row['concours'] = ['code' => $row['concours_code'], 'label' => $row['concours_label']];
            $row['candidat'] = ['nom' => $row['candidat_nom'], 'prenom' => $row['candidat_prenom'], 'email' => $row['candidat_email'], 'cin' => $row['candidat_cin']];

            $concoursCode = $row['concours_code'];
            $candidatCin = $row['candidat_cin'] ?? $row['cin'] ?? '';
            $folderPath = $uploadDir . $concoursCode . '/' . $candidatCin . '/';

            $hasPhysicalFiles = false;
            $fileFields = ['cv', 'diplome', 'releves_notes', 'demande_maniscrite', 'cin_recto', 'cin_verso', 'photo'];

            foreach ($fileFields as $field) {
                if (!empty($row[$field])) {
                    if (file_exists($folderPath . $row[$field])) {
                        $hasPhysicalFiles = true;
                        break;
                    }
                }
            }
            $row['has_physical_files'] = $hasPhysicalFiles;

            unset($row['concours_code'], $row['concours_label'], $row['candidat_nom'], $row['candidat_prenom'], $row['candidat_email'], $row['candidat_cin']);
            $data[] = $row;
        }

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

    /**
     * Download inscriptions with filters
     */
    public function download($filters, $filterType = 'ALL', $dataMode = '')
    {
        try {
            // Build search conditions
            $searchResult = $this->buildSearchConditions($filters, $dataMode);
            $sql = $searchResult['sql'];
            $params = $searchResult['params'];

            // Add filter condition for fitness
            // Add filter condition for fitness
            if ($filterType === 'EN_COURS') {
                $sql .= " AND (i.resultat_inscription = 'EN_COURS' OR i.resultat_inscription = 'ELLIBGIBLE_ECRIT' OR  i.resultat_inscription = 'ELLIBGIBLE_ORALE' OR  i.resultat_inscription = 'NON_ELLIBGIBLE_ORALE' OR  i.resultat_inscription = 'ADMIS_ORALE' OR  i.resultat_inscription = 'NON_ADMIS_ORALE' )";
            } else if ($filterType === 'ELLIBGIBLE_ECRIT') {
                $sql .= " AND (i.resultat_inscription = 'ELLIBGIBLE_ECRIT' OR  i.resultat_inscription = 'ELLIBGIBLE_ORALE' OR  i.resultat_inscription = 'NON_ELLIBGIBLE_ORALE' OR  i.resultat_inscription = 'ADMIS_ORALE' OR  i.resultat_inscription = 'NON_ADMIS_ORALE' )";
            } else if ($filterType === 'ELLIBGIBLE_ORALE' || $filterType === 'ELIGIBLE_ORALE') {
                // أضفنا ELIGIBLE_ORALE هنا ليتطابق مع ما يرسله زر الـ HTML
                $sql .= " AND (i.resultat_inscription = 'ELIGIBLE_ORALE' OR i.resultat_inscription = 'ELLIBGIBLE_ORALE' OR  i.resultat_inscription = 'NON_ELLIBGIBLE_ORALE' OR  i.resultat_inscription = 'ADMIS_ORALE' OR  i.resultat_inscription = 'NON_ADMIS_ORALE' )";
            } else if ($filterType === 'ADMIS_ORALE') {
                $sql .= " AND (i.resultat_inscription = 'ADMIS_ORALE')";
            }

            // Classement du CSV téléchargé par moyenne "avant écrit" pour la phase de préparation de l'écrit
            if ($dataMode === 'preparation-ecrit') {
                $sql .= " ORDER BY i.moyenne_calcule_avant_ecrit DESC";
            } elseif ($dataMode === 'preparation-orale') {
                $sql .= " ORDER BY i.moyenne_calcule_avant_orale DESC";
            } else {
                $sql .= " ORDER BY i.id DESC";
            }

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

            $data = [];
            while ($row = $stmt->fetch()) {
                $row['concours'] = [
                    'code' => $row['concours_code'],
                    'label' => $row['concours_label']
                ];
                $row['candidat'] = [
                    'nom' => $row['candidat_nom'],
                    'prenom' => $row['candidat_prenom'],
                    'email' => $row['candidat_email']
                ];
                $row['type_diplome'] = [
                    'libelle' => $row['type_diplome_libelle'],
                    'code' => $row['type_diplome_code']
                ];

                unset(
                    $row['concours_code'],
                    $row['concours_label'],
                    $row['candidat_nom'],
                    $row['candidat_prenom'],
                    $row['candidat_email'],
                    $row['type_diplome_libelle'],
                    $row['type_diplome_code']
                );

                $data[] = $row;
            }

            return [
                'success' => true,
                'data' => $data,
                'count' => count($data),
                'filter' => $filterType
            ];
        } catch (Exception $e) {
            return [
                'success' => false,
                'message' => 'Erreur lors du téléchargement: ' . $e->getMessage()
            ];
        }
    }

    public function deleteInscription($id)
    {
        return $this->delete($id);
    }

    /**
     * Get diplomes
     */
    public function getDiplomes()
    {
        try {
            $query = "SELECT id, code, libelle, annee_apres_bac FROM type_diplome ORDER BY libelle";
            $stmt = $this->pdo->prepare($query);
            $stmt->execute();
            return ['success' => true, 'data' => $stmt->fetchAll(\PDO::FETCH_ASSOC)];
        } catch (Exception $e) {
            return ['success' => false, 'message' => 'Erreur: ' . $e->getMessage()];
        }
    }


    public function updateUpload($result, $id)
    {
        // Récupérer chaque nom de fichier, ou null s'il n'existe pas
        $cv = isset($result["cv"]["name"]) ? $result["cv"]["name"] : null;
        $diplome = isset($result["diplome"]["name"]) ? $result["diplome"]["name"] : null;
        $releves_notes = isset($result["releves_notes"]["name"]) ? $result["releves_notes"]["name"] : null;
        $demande_maniscrite = isset($result["demande_maniscrite"]["name"]) ? $result["demande_maniscrite"]["name"] : null;
        $cin_recto = isset($result["cin_recto"]["name"]) ? $result["cin_recto"]["name"] : null;
        $cin_verso = isset($result["cin_verso"]["name"]) ? $result["cin_verso"]["name"] : null;
        $photo = isset($result["photo"]["name"]) ? $result["photo"]["name"] : null;

        $sql = "UPDATE inscription_concours SET cv = :cv, diplome = :diplome, releves_notes = :releves_notes,
            demande_maniscrite = :demande_maniscrite, cin_recto = :cin_recto, cin_verso = :cin_verso,
            photo = :photo WHERE id = :id";

        $data = [
            ':id' => $id,
            ':cv' => $cv,
            ':diplome' => $diplome,
            ':releves_notes' => $releves_notes,
            ':demande_maniscrite' => $demande_maniscrite,
            ':cin_recto' => $cin_recto,
            ':cin_verso' => $cin_verso,
            ':photo' => $photo
        ];
        $this->execute($sql, $data);
        return $data;
    }


    public function batchUpdateResultat($ids, $resultat)
    {
        try {
            $allowedResults = ['EN_COURS', 'ADMIS', 'LISTE_ATTENTE', 'NON_ADMIS', 'ELLIBGIBLE_ECRIT', 'NON_ELLIBGIBLE_ECRIT', 'ELIGIBLE_ORALE', 'NON_ELIGIBLE_ORALE', 'ADMIS_ORALE', 'NON_ADMIS_ECRIT', 'NON_ADMIS_ORALE'];
            if (!in_array($resultat, $allowedResults)) {
                return ['success' => false, 'message' => 'Résultat invalide'];
            }
            if (!is_array($ids) || empty($ids)) {
                return ['success' => false, 'message' => 'Tableau IDs vide'];
            }

            $ids = array_filter(array_map('intval', $ids));
            if (empty($ids)) return ['success' => false, 'message' => 'Aucun ID valide'];

            $placeholders = implode(',', array_fill(0, count($ids), '?'));
            $query = "UPDATE inscription_concours SET resultat_inscription = ?, date_modification = NOW() WHERE id IN ($placeholders)";
            $stmt = $this->pdo->prepare($query);

            $params = array_merge([$resultat], $ids);
            if ($stmt->execute($params)) {
                return ['success' => true, 'message' => $stmt->rowCount() . " inscription(s) mise(s) à jour.", 'updated_count' => $stmt->rowCount()];
            }
            return ['success' => false, 'message' => 'Erreur d\'exécution'];
        } catch (Exception $e) {
            return ['success' => false, 'message' => 'Erreur: ' . $e->getMessage()];
        }
    }

    public function batchUpdateResultatWithTransaction($ids, $resultat)
    {
        try {
            $this->pdo->beginTransaction();
            $res = $this->batchUpdateResultat($ids, $resultat);
            if ($res['success']) {
                $this->pdo->commit();
                return $res;
            }
            $this->pdo->rollBack();
            return $res;
        } catch (Exception $e) {
            if ($this->pdo->inTransaction()) $this->pdo->rollBack();
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }

    /**
     * Import notes (Ecrit and/or Oral) from CSV
     */
    public function importNotes($concoursId, $notesData)
    {
        try {
            $updatedCount = 0;
            $sql = "SELECT i.*, s.cin 
                    FROM {$this->table} i
                    INNER JOIN candidat s ON i.candidat_id = s.id
                    WHERE i.concours_id = :concours_id";

            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([':concours_id' => $concoursId]);
            $inscriptions = $stmt->fetchAll(\PDO::FETCH_ASSOC);

            $updateSql = "UPDATE {$this->table} 
                          SET moyenne_ecrit = :ecrit, 
                              moyenne_orale = :orale,
                              moyenne_general_calcule = :moyenne_general,
                              date_modification = NOW()
                          WHERE id = :id";
            $updateStmt = $this->pdo->prepare($updateSql);

            foreach ($inscriptions as $insc) {
                $cin = trim($insc['cin']);

                if (isset($notesData[$cin])) {
                    $newEcrit = $notesData[$cin]['ecrit'];
                    $newOrale = $notesData[$cin]['orale'];

                    $finalEcrit = ($newEcrit !== null) ? $newEcrit : (!empty($insc['moyenne_ecrit']) ? floatval($insc['moyenne_ecrit']) : null);
                    $finalOrale = ($newOrale !== null) ? $newOrale : (!empty($insc['moyenne_orale']) ? floatval($insc['moyenne_orale']) : null);

                    $semestreNotes = [];
                    foreach (['note_s1', 'note_s2', 'note_s3', 'note_s4'] as $field) {
                        if (array_key_exists($field, $insc) && !empty($insc[$field]) && floatval($insc[$field]) > 0) {
                            $semestreNotes[] = floatval($insc[$field]);
                        }
                    }
                    $moyenneSemestres = count($semestreNotes) > 0 ? (array_sum($semestreNotes) / count($semestreNotes)) : 0;

                    $components = [];
                    if ($moyenneSemestres > 0) $components[] = $moyenneSemestres;
                    if ($finalEcrit !== null) $components[] = $finalEcrit;
                    if ($finalOrale !== null) $components[] = $finalOrale;

                    $moyenneGenerale = count($components) > 0 ? (array_sum($components) / count($components)) : 0;

                    $updateStmt->execute([
                        ':ecrit' => $finalEcrit,
                        ':orale' => $finalOrale,
                        ':moyenne_general' => round($moyenneGenerale, 2),
                        ':id' => $insc['id']
                    ]);
                    $updatedCount++;
                }
            }
            return ['success' => true, 'message' => "Importation réussie. $updatedCount candidats mis à jour."];
        } catch (Exception $e) {
            return ['success' => false, 'message' => "Erreur SQL: " . $e->getMessage()];
        }
    }

    /**
     * Import "moyenne_calcule_avant_ecrit" from CSV by CIN, scoped to a specific concours.
     * $notesData is an associative array: [cin => moyenne_calcule_avant_ecrit]
     */
    public function importAvantEcrit($concoursId, $notesData)
    {
        try {
            $updatedCount = 0;

            $sql = "SELECT i.id, s.cin 
                    FROM {$this->table} i
                    INNER JOIN candidat s ON i.candidat_id = s.id
                    WHERE i.concours_id = :concours_id";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([':concours_id' => $concoursId]);
            $inscriptions = $stmt->fetchAll(\PDO::FETCH_ASSOC);

            $updateSql = "UPDATE {$this->table} 
                          SET moyenne_calcule_avant_ecrit = :avant_ecrit,
                              date_modification = NOW()
                          WHERE id = :id";
            $updateStmt = $this->pdo->prepare($updateSql);

            $remaining = $notesData;
            foreach ($inscriptions as $insc) {
                $cin = trim($insc['cin']);
                if (isset($remaining[$cin])) {
                    $updateStmt->execute([
                        ':avant_ecrit' => $remaining[$cin],
                        ':id' => $insc['id']
                    ]);
                    $updatedCount++;
                    unset($remaining[$cin]);
                }
            }

            $message = "Importation réussie. $updatedCount candidat(s) mis à jour.";
            if (!empty($remaining)) {
                $message .= " " . count($remaining) . " CIN non trouvé(s) pour ce concours: " . implode(', ', array_keys($remaining)) . ".";
            }

            return [
                'success' => true,
                'message' => $message,
                'updated' => $updatedCount,
                'not_found' => array_keys($remaining)
            ];
        } catch (Exception $e) {
            return ['success' => false, 'message' => "Erreur SQL: " . $e->getMessage()];
        }
    }

    /**
     * Import "moyenne_ecrit" et "moyenne_calcule_avant_orale" from CSV by CIN, scoped to a specific concours.
     * $notesData is an associative array: [cin => ['ecrit' => float|null, 'avant_orale' => float|null]]
     * Une valeur null pour l'un des deux champs conserve la valeur déjà enregistrée (mise à jour partielle).
     */
    public function importEcritAvantOrale($concoursId, $notesData)
    {
        try {
            $updatedCount = 0;

            $sql = "SELECT i.id, i.moyenne_ecrit, i.moyenne_calcule_avant_orale, s.cin 
                    FROM {$this->table} i
                    INNER JOIN candidat s ON i.candidat_id = s.id
                    WHERE i.concours_id = :concours_id";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([':concours_id' => $concoursId]);
            $inscriptions = $stmt->fetchAll(\PDO::FETCH_ASSOC);

            $updateSql = "UPDATE {$this->table} 
                          SET moyenne_ecrit = :ecrit,
                              moyenne_calcule_avant_orale = :avant_orale,
                              date_modification = NOW()
                          WHERE id = :id";
            $updateStmt = $this->pdo->prepare($updateSql);

            $remaining = $notesData;
            foreach ($inscriptions as $insc) {
                $cin = trim($insc['cin']);
                if (isset($remaining[$cin])) {
                    $newEcrit = $remaining[$cin]['ecrit'];
                    $newAvantOrale = $remaining[$cin]['avant_orale'];

                    $finalEcrit = ($newEcrit !== null) ? $newEcrit : (!empty($insc['moyenne_ecrit']) ? floatval($insc['moyenne_ecrit']) : null);
                    $finalAvantOrale = ($newAvantOrale !== null) ? $newAvantOrale : (!empty($insc['moyenne_calcule_avant_orale']) ? floatval($insc['moyenne_calcule_avant_orale']) : null);

                    $updateStmt->execute([
                        ':ecrit' => $finalEcrit,
                        ':avant_orale' => $finalAvantOrale,
                        ':id' => $insc['id']
                    ]);
                    $updatedCount++;
                    unset($remaining[$cin]);
                }
            }

            $message = "Importation réussie. $updatedCount candidat(s) mis à jour.";
            if (!empty($remaining)) {
                $message .= " " . count($remaining) . " CIN non trouvé(s) pour ce concours: " . implode(', ', array_keys($remaining)) . ".";
            }

            return [
                'success' => true,
                'message' => $message,
                'updated' => $updatedCount,
                'not_found' => array_keys($remaining)
            ];
        } catch (Exception $e) {
            return ['success' => false, 'message' => "Erreur SQL: " . $e->getMessage()];
        }
    }

    /**
     * Import "moyenne_orale" et "moyenne_calcule_avant_finale" from CSV by CIN, scoped to a specific concours.
     * $notesData is an associative array: [cin => ['orale' => float|null, 'avant_finale' => float|null]]
     * Une valeur null pour l'un des deux champs conserve la valeur déjà enregistrée (mise à jour partielle).
     */
    public function importOraleAvantFinale($concoursId, $notesData)
    {
        try {
            $updatedCount = 0;

            $sql = "SELECT i.id, i.moyenne_orale, i.moyenne_calcule_avant_finale, s.cin 
                    FROM {$this->table} i
                    INNER JOIN candidat s ON i.candidat_id = s.id
                    WHERE i.concours_id = :concours_id";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([':concours_id' => $concoursId]);
            $inscriptions = $stmt->fetchAll(\PDO::FETCH_ASSOC);

            $updateSql = "UPDATE {$this->table} 
                          SET moyenne_orale = :orale,
                              moyenne_calcule_avant_finale = :avant_finale,
                              date_modification = NOW()
                          WHERE id = :id";
            $updateStmt = $this->pdo->prepare($updateSql);

            $remaining = $notesData;
            foreach ($inscriptions as $insc) {
                $cin = trim($insc['cin']);
                if (isset($remaining[$cin])) {
                    $newOrale = $remaining[$cin]['orale'];
                    $newAvantFinale = $remaining[$cin]['avant_finale'];

                    $finalOrale = ($newOrale !== null) ? $newOrale : (!empty($insc['moyenne_orale']) ? floatval($insc['moyenne_orale']) : null);
                    $finalAvantFinale = ($newAvantFinale !== null) ? $newAvantFinale : (!empty($insc['moyenne_calcule_avant_finale']) ? floatval($insc['moyenne_calcule_avant_finale']) : null);

                    $updateStmt->execute([
                        ':orale' => $finalOrale,
                        ':avant_finale' => $finalAvantFinale,
                        ':id' => $insc['id']
                    ]);
                    $updatedCount++;
                    unset($remaining[$cin]);
                }
            }

            $message = "Importation réussie. $updatedCount candidat(s) mis à jour.";
            if (!empty($remaining)) {
                $message .= " " . count($remaining) . " CIN non trouvé(s) pour ce concours: " . implode(', ', array_keys($remaining)) . ".";
            }

            return [
                'success' => true,
                'message' => $message,
                'updated' => $updatedCount,
                'not_found' => array_keys($remaining)
            ];
        } catch (Exception $e) {
            return ['success' => false, 'message' => "Erreur SQL: " . $e->getMessage()];
        }
    }

    /**
     * Import inscriptions from CSV file - Direct mapping
     */
    public function importCsv($filePath, $concoursId, $options = [])
    {
        try {
            $defaults = [
                'etat_inscription' => 'VALIDE',
                'resultat_inscription' => 'EN_COURS',
                'default_password' => 'default123',
                'active' => 1,
                'module_valide' => 1
            ];
            $options = array_merge($defaults, $options);

            $concours = $this->getConcoursById($concoursId);
            if (!$concours) {
                return ['success' => false, 'message' => 'Concours non trouvé.'];
            }

            $inserted = 0;
            $updated = 0;
            $errors = [];
            $rowNumber = 0;

            if (($handle = fopen($filePath, "r")) === false) {
                return ['success' => false, 'message' => 'Impossible d\'ouvrir le fichier.'];
            }

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

            $header = fgetcsv($handle, 0, $delimiter);
            if (!$header) {
                fclose($handle);
                return ['success' => false, 'message' => 'Le fichier CSV est vide ou invalide.'];
            }

            $header = array_map(function ($col) {
                $col = trim($col);
                $col = str_replace("\xEF\xBB\xBF", '', $col);
                return $col;
            }, $header);

            $inscriptionColumns = $this->getTableColumns('inscription_concours');
            $candidatColumns = $this->getTableColumns('candidat');

            $requiredColumns = ['email', 'cin', 'nom', 'prenom'];
            foreach ($requiredColumns as $required) {
                if (!in_array($required, $header)) {
                    fclose($handle);
                    return [
                        'success' => false,
                        'message' => "Colonne requise '$required' manquante dans le fichier CSV."
                    ];
                }
            }

            $this->pdo->beginTransaction();

            while (($row = fgetcsv($handle, 0, $delimiter)) !== false) {
                $rowNumber++;

                if (count($row) < 2 || empty(array_filter($row))) {
                    continue;
                }

                try {
                    $rowData = [];
                    foreach ($header as $index => $column) {
                        if (isset($row[$index]) && trim($row[$index]) !== '' && trim($row[$index]) !== 'NULL') {
                            $rowData[$column] = trim($row[$index]);
                        }
                    }

                    if (empty($rowData['email']) || empty($rowData['cin'])) {
                        $errors[] = "Ligne $rowNumber: Email ou CIN manquant.";
                        continue;
                    }

                    $candidatData = $this->extractCandidatData($rowData, $candidatColumns, $options);
                    $inscriptionData = $this->extractInscriptionData($rowData, $inscriptionColumns, $concoursId, $options);

                    $candidatId = $this->findOrCreateCandidatDirect($candidatData);
                    if (!$candidatId) {
                        $errors[] = "Ligne $rowNumber: Impossible de créer/trouver l'étudiant.";
                        continue;
                    }

                    $inscriptionData['candidat_id'] = $candidatId;
                    $existing = $this->findInscriptionByCandidatAndConcours($candidatId, $concoursId);

                    if ($existing) {
                        unset($inscriptionData['code']);
                        unset($inscriptionData['date_creation']);
                        $inscriptionData['date_modification'] = date('Y-m-d H:i:s');

                        $this->update($existing['id'], $inscriptionData);
                        $updated++;
                    } else {
                        if (empty($inscriptionData['code'])) {
                            $inscriptionData['code'] = 'INSC-' . time() . '-' . rand(1000, 9999) . '-' . $rowNumber;
                        }

                        try {
                            $utilService = new \model\service\UtilService();
                            $utilService->createConcoursFolder(
                                $concours['code'] . '/' . ($candidatData['cin'] ?? 'unknown_' . time())
                            );
                        } catch (Exception $e) {
                            error_log("Erreur création dossier: " . $e->getMessage());
                        }

                        $this->insert($inscriptionData);
                        $inserted++;
                    }

                } catch (Exception $e) {
                    $errors[] = "Ligne $rowNumber: " . $e->getMessage();
                }
            }

            fclose($handle);
            $this->pdo->commit();

            return [
                'success' => true,
                'message' => "Importation terminée. $inserted inscription(s) créée(s), $updated mise(s) à jour.",
                'inserted' => $inserted,
                'updated' => $updated,
                'errors' => $errors,
                'total_rows' => $rowNumber,
                'has_errors' => !empty($errors)
            ];

        } catch (Exception $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            return [
                'success' => false,
                'message' => 'Erreur lors de l\'importation: ' . $e->getMessage()
            ];
        }
    }

    /**
     * Get table columns from database
     */
    private function getTableColumns($tableName)
    {
        try {
            $sql = "SHOW COLUMNS FROM $tableName";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute();
            $columns = [];
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $columns[] = $row['Field'];
            }
            return $columns;
        } catch (Exception $e) {
            return [];
        }
    }

    /**
     * Extract candidat data from CSV row
     */
    private function extractCandidatData($rowData, $candidatColumns, $options)
    {
        $candidatData = [];
        $candidatFields = [
            'email' => null,
            'nom' => null,
            'prenom' => null,
            'cin' => null,
            'phone' => null,
            'password' => password_hash($options['default_password'], PASSWORD_DEFAULT)
        ];

        foreach ($candidatFields as $field => $default) {
            if (isset($rowData[$field]) && in_array($field, $candidatColumns)) {
                $candidatData[$field] = $rowData[$field];
            } elseif ($default !== null) {
                $candidatData[$field] = $default;
            }
        }

        foreach ($rowData as $key => $value) {
            if (in_array($key, $candidatColumns) && !isset($candidatData[$key])) {
                $candidatData[$key] = $value;
            }
        }

        return $candidatData;
    }

    /**
     * Convert field value based on field type
     */
    private function convertFieldValue($field, $value)
    {
        if (strpos($field, 'date') !== false || $field === 'date_naissance') {
            return $this->parseDate($value);
        }

        if ($field === 'type_diplome_obtenu') {
            return $this->getTypeDiplomeId($value);
        }

        if (in_array($field, ['annee_bac', 'anne_obtention_diplome', 'duree_diplome', 'module_valide', 'active'])) {
            return is_numeric($value) ? intval($value) : null;
        }

        if (in_array($field, ['note_s1', 'note_s2', 'note_s3', 'note_s4', 'note_s5', 'note_s6',
            'moyenne_general_calcule', 'moyenne_ecrit', 'moyenne_orale', 'moyenne_bac'])) {
            return is_numeric($value) ? floatval(str_replace(',', '.', $value)) : null;
        }

        if (in_array($field, ['module_valide', 'active'])) {
            if (is_numeric($value)) return intval($value);
            return (strtolower($value) === 'oui' || strtolower($value) === 'true' || $value == '1') ? 1 : 0;
        }

        return $value;
    }

    /**
     * Find existing candidat or create new one - Direct version
     */
    private function findOrCreateCandidatDirect($candidatData)
    {
        if (!empty($candidatData['cin'])) {
            $sql = "SELECT id FROM candidat WHERE cin = :cin LIMIT 1";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([':cin' => $candidatData['cin']]);
            $result = $stmt->fetch();
            if ($result) {
                return $result['id'];
            }
        }

        if (!empty($candidatData['email'])) {
            $sql = "SELECT id FROM candidat WHERE email = :email LIMIT 1";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([':email' => $candidatData['email']]);
            $result = $stmt->fetch();
            if ($result) {
                return $result['id'];
            }
        }

        $validFields = $this->getTableColumns('candidat');
        $insertData = [];

        foreach ($candidatData as $key => $value) {
            if (in_array($key, $validFields) && $value !== null && $value !== '') {
                $insertData[$key] = $value;
            }
        }

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

        $fields = array_keys($insertData);
        $placeholders = array_map(function ($field) {
            return ":$field";
        }, $fields);

        $sql = "INSERT INTO candidat (" . implode(', ', $fields) . ") 
            VALUES (" . implode(', ', $placeholders) . ")";

        $stmt = $this->pdo->prepare($sql);
        foreach ($insertData as $key => $value) {
            $stmt->bindValue(":$key", $value);
        }
        $stmt->execute();

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

    /**
     * Get concours by ID
     */
    public function getConcoursById($id)
    {
        $sql = "SELECT * FROM concours WHERE id = :id AND active = 1";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        return $stmt->fetch();
    }


    /**
     * Parse date from common formats
     */
    private function parseDate($dateStr)
    {
        if (empty($dateStr) || $dateStr === 'NULL' || $dateStr === 'null') {
            return null;
        }

        $dateStr = trim($dateStr);

        if (preg_match('/^(\d{4})-(\d{2})-(\d{2})$/', $dateStr, $matches)) {
            return $dateStr;
        }

        if (preg_match('/^(\d{2})\/(\d{2})\/(\d{4})$/', $dateStr, $matches)) {
            return $matches[3] . '-' . $matches[2] . '-' . $matches[1];
        }

        if (preg_match('/^(\d{2})-(\d{2})-(\d{4})$/', $dateStr, $matches)) {
            return $matches[3] . '-' . $matches[2] . '-' . $matches[1];
        }

        $timestamp = strtotime($dateStr);
        if ($timestamp !== false && $timestamp > 0) {
            return date('Y-m-d', $timestamp);
        }

        return null;
    }

    /**
     * Get type diplome ID from code or libelle
     */
    private function getTypeDiplomeId($value)
    {
        if (empty($value)) {
            return null;
        }

        if (is_numeric($value)) {
            return (int)$value;
        }

        try {
            $sql = "SELECT id FROM type_diplome WHERE code = :value OR libelle = :value LIMIT 1";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([':value' => trim($value)]);
            $result = $stmt->fetch();

            if ($result) {
                return (int)$result['id'];
            }

            $sql = "SELECT id, libelle FROM type_diplome WHERE libelle LIKE :value LIMIT 1";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([':value' => '%' . trim($value) . '%']);
            $result = $stmt->fetch();

            if ($result) {
                return (int)$result['id'];
            }

        } catch (Exception $e) {
            error_log("Error getting type diplome ID: " . $e->getMessage());
            return null;
        }
        return null;
    }

    /**
     * Extract inscription data from CSV row
     */
    private function extractInscriptionData($rowData, $inscriptionColumns, $concoursId, $options)
    {
        $inscriptionData = [
            'concours_id' => $concoursId,
            'etat_inscription' => $options['etat_inscription'],
            'resultat_inscription' => $options['resultat_inscription'],
            'active' => $options['active'],
            'module_valide' => $options['module_valide'],
            'date_creation' => date('Y-m-d H:i:s'),
            'date_inscription' => date('Y-m-d')
        ];

        foreach ($rowData as $key => $value) {
            if (in_array($key, $inscriptionColumns) && !isset($inscriptionData[$key])) {
                if ($key === 'type_diplome_obtenu') {
                    $inscriptionData[$key] = $this->getTypeDiplomeId($value);
                } else {
                    $inscriptionData[$key] = $this->convertFieldValue($key, $value);
                }
            }
        }

        if (!isset($inscriptionData['moyenne_general_calcule']) || empty($inscriptionData['moyenne_general_calcule'])) {
            $notes = [];
            foreach (['note_s1', 'note_s2', 'note_s3', 'note_s4', 'note_s5', 'note_s6'] as $noteField) {
                if (!empty($rowData[$noteField]) && is_numeric($rowData[$noteField])) {
                    $notes[] = floatval($rowData[$noteField]);
                }
            }
            if (count($notes) > 0) {
                $inscriptionData['moyenne_general_calcule'] = round(array_sum($notes) / count($notes), 2);
            }
        }

        foreach ($inscriptionData as $key => $value) {
            if ($value === '' || $value === null || $value === 'NULL') {
                unset($inscriptionData[$key]);
            }
        }

        return $inscriptionData;
    }
    /**
     * @param int $inscriptionId
     * @return void
     */
    public function downloadAllCandidatDocs($inscriptionId)
    {
        try {
            $query = "SELECT i.cv, i.diplome, i.releves_notes, i.cin_recto, i.cin_verso, i.photo, i.demande_maniscrite, 
                             s.cin, c.code as concours_code 
                      FROM {$this->table} i
                      LEFT JOIN candidat s ON i.candidat_id = s.id
                      LEFT JOIN concours c ON i.concours_id = c.id
                      WHERE i.id = :id LIMIT 1";

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

            if (!$inscription) {
                header('Content-Type: application/json');
                echo json_encode(['success' => false, 'message' => 'Inscription non trouvée']);
                exit;
            }

            $candidatCin = strtoupper(preg_replace('/[^A-Za-z0-9]/', '', $inscription['cin'] ?? ''));
            $concoursCode = $inscription['concours_code'] ?? '';

            if (empty($candidatCin) || empty($concoursCode)) {
                header('Content-Type: application/json');
                echo json_encode(['success' => false, 'message' => "Données incomplètes (CIN ou Code Concours manquant)."]);
                exit;
            }
            $baseDir = dirname(__DIR__, 2) . '/concours/';
            $candidatFolder = $baseDir . $concoursCode . '/' . $candidatCin . '/';

            if (!is_dir($candidatFolder)) {
                header('Content-Type: application/json');
                echo json_encode(['success' => false, 'message' => "Le dossier physique de l'étudiant n'existe pas."]);
                exit;
            }
            $archiveName = sys_get_temp_dir() . '/' . $candidatCin . '_dossier.zip';
            if (file_exists($archiveName)) {
                unlink($archiveName);
            }

            $zip = new ZipArchive();
            if ($zip->open($archiveName, ZipArchive::CREATE | ZipArchive::OVERWRITE) !== TRUE) {
                header('Content-Type: application/json');
                echo json_encode(['success' => false, 'message' => "Impossible de créer le fichier ZIP."]);
                exit;
            }

            $hasFiles = false;
            $filesToInclude = [
                'CV'                 => $inscription['cv'] ?? '',
                'DIPLOME'            => $inscription['diplome'] ?? '',
                'RELEVE_NOTES'       => $inscription['releves_notes'] ?? '',
                'CIN_RECTO'          => $inscription['cin_recto'] ?? '',
                'CIN_VERSO'          => $inscription['cin_verso'] ?? '',
                'BAC'                => $inscription['bac_file'] ?? '',
                'PHOTO'              => $inscription['photo'] ?? '',
                'DEMANDE_MANiSCRITE' => $inscription['demande_maniscrite'] ?? ''
            ];

            foreach ($filesToInclude as $prefix => $dbFileName) {
                if (!empty($dbFileName)) {
                    $fullFilePath = $candidatFolder . basename($dbFileName);

                    if (file_exists($fullFilePath)) {
                        $extension = pathinfo($fullFilePath, PATHINFO_EXTENSION);
                        $localName = $prefix . '_' . $candidatCin . '.' . $extension;

                        $zip->addFile($fullFilePath, $localName);
                        $hasFiles = true;
                    }
                }
            }

            $zip->close();

            if (!$hasFiles) {
                if (file_exists($archiveName)) {
                    unlink($archiveName);
                }
                header('Content-Type: application/json');
                echo json_encode(['success' => false, 'message' => "Aucun fichier physique trouvé dans le dossier."]);
                exit;
            }
            if (ob_get_level()) ob_end_clean();

            header('Content-Description: File Transfer');
            header('Content-Type: application/zip');
            header('Content-Disposition: attachment; filename="' . $candidatCin . '.zip"');
            header('Expires: 0');
            header('Cache-Control: must-revalidate');
            header('Pragma: public');
            header('Content-Length: ' . filesize($archiveName));

            readfile($archiveName);
            unlink($archiveName);
            exit;

        } catch (Exception $e) {
            header('Content-Type: application/json');
            echo json_encode(['success' => false, 'message' => 'Erreur: ' . $e->getMessage()]);
            exit;
        }
    }
    public function downloadAllConcoursDocs($concoursId) {
        $sql = "SELECT i.*, s.cin, s.nom, s.prenom, c.code as concours_code 
            FROM {$this->table} i 
            JOIN candidat s ON i.candidat_id = s.id 
            JOIN concours c ON i.concours_id = c.id
            WHERE i.concours_id = ?";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$concoursId]);
        $inscriptions = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if (empty($inscriptions)) return;

        $concoursCode = $inscriptions[0]['concours_code'];
        $zipName = " " . $concoursCode . ".zip";
        $archivePath = sys_get_temp_dir() . '/' . $zipName;

        $zip = new ZipArchive();
        if ($zip->open($archivePath, ZipArchive::CREATE | ZipArchive::OVERWRITE) === TRUE) {

            $baseDir = dirname(__DIR__, 2) . '/concours/' . $concoursCode . '/';

            foreach ($inscriptions as $ins) {
                $candidatFolder = $baseDir . $ins['cin'] . '/';
                $folderNameInsideZip = $ins['cin'] . '_' . $ins['nom'] . '/';

                $files = [
                    'photo'      => 'PhotoID',
                    'cv'                  => 'CV',
                    'diplome'             => 'Diplome',
                    'releves_notes'       => 'ReleveNotes',
                    'cin_recto'           => 'CIN_Recto',
                    'cin_verso'           => 'CIN_Verso',
                    'demande_maniscrite'  => 'DemandeManuscrite'
                ];

                foreach ($files as $field => $prefix) {
                    if (!empty($ins[$field]) && file_exists($candidatFolder . $ins[$field])) {
                        $extension = pathinfo($ins[$field], PATHINFO_EXTENSION);

                        $fileNameInsideZip = $folderNameInsideZip . $prefix . '.' . $extension;

                        $zip->addFile($candidatFolder . $ins[$field], $fileNameInsideZip);
                    }
                }
            }
            $zip->close();
        }
        header('Content-Type: application/zip');
        header('Content-Disposition: attachment; filename="' . $zipName . '"');
        header('Content-Length: ' . filesize($archivePath));
        readfile($archivePath);
        unlink($archivePath); // حذف الملف من السيرفر بعد التحميل
        exit;
    }
}