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

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

class InscriptionModuleDetailService extends AbstractPdo
{
    protected $table = 'inscription_module_detail';
    protected $primaryKey = 'id';

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

    /**
     * Create a detail
     */
    public function createDetail($data)
    {
        if (empty($data['inscription_module_id'])) {
            throw new Exception("L'inscription module est obligatoire");
        }
        if (empty($data['student_id'])) {
            throw new Exception("L'étudiant est obligatoire");
        }

        // Check if student already exists
        if ($this->studentAlreadyInscribed($data['inscription_module_id'], $data['student_id'])) {
            throw new Exception("Cet étudiant est déjà inscrit");
        }

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

    /**
     * Get students by inscription id
     */
    public function getStudentsByInscriptionId($inscriptionModuleId)
    {
        $sql = "SELECT imd.*,
                       s.id as student_id,
                       s.nom,
                       s.prenom,
                       s.cin,
                       s.email,
                       s.phone
                FROM {$this->table} imd
                JOIN student s ON imd.student_id = s.id
                WHERE imd.inscription_module_id = :inscription_module_id
                ORDER BY s.nom ASC, s.prenom ASC";

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

    /**
     * Delete all details by inscription id
     */
    public function deleteByInscriptionId($inscriptionModuleId)
    {
        $sql = "DELETE FROM {$this->table}
                WHERE inscription_module_id = :inscription_module_id";

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

    /**
     * Delete a student from inscription
     */
    public function deleteStudent($inscriptionModuleId, $studentId)
    {
        $sql = "DELETE FROM {$this->table}
                WHERE inscription_module_id = :inscription_module_id
                  AND student_id = :student_id";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':inscription_module_id' => $inscriptionModuleId,
            ':student_id' => $studentId
        ]);

        $this->updateNbEtudiants($inscriptionModuleId);
        return $stmt->rowCount();
    }

    /**
     * Check if student is already inscribed
     */
    public function studentAlreadyInscribed($inscriptionModuleId, $studentId)
    {
        $sql = "SELECT id FROM {$this->table}
                WHERE inscription_module_id = :inscription_module_id
                  AND student_id = :student_id
                LIMIT 1";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':inscription_module_id' => $inscriptionModuleId,
            ':student_id' => $studentId
        ]);

        return $stmt->fetch() !== false;
    }

    /**
     * Count students by inscription id
     */
    public function countStudentsByInscriptionId($inscriptionModuleId)
    {
        $sql = "SELECT COUNT(*) as nb FROM {$this->table}
                WHERE inscription_module_id = :inscription_module_id";

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

    /**
     * Update nb_etudiants in inscription_module
     */
    public function updateNbEtudiants($inscriptionModuleId)
    {
        $nb = $this->countStudentsByInscriptionId($inscriptionModuleId);

        $sql = "UPDATE inscription_module
                SET nb_etudiants = :nb
                WHERE id = :id";

        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([
            ':nb' => $nb,
            ':id' => $inscriptionModuleId
        ]);
    }
     public function getStats()
     {
         $sql = "SELECT
                     COUNT(*) AS total_inscriptions_detail,
                     COUNT(DISTINCT student_id) AS total_etudiants
                 FROM {$this->table}";

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

         return $stmt->fetch(PDO::FETCH_ASSOC);
     }
     /**
      * Export students of one inscription to CSV
      */
     public function exportByInscriptionToCsv($inscriptionModuleId)
     {
         $students = $this->getStudentsByInscriptionId($inscriptionModuleId);

         header('Content-Type: text/csv; charset=utf-8');
         header('Content-Disposition: attachment; filename="inscription-module-' . $inscriptionModuleId . '.csv"');

         $output = fopen('php://output', 'w');

         fputcsv($output, [
             'CIN',
             'Nom',
             'Prénom',
             'Email',
             'Téléphone'
         ], ';');

         foreach ($students as $student) {
             fputcsv($output, [
                 $student['cin'],
                 $student['nom'],
                 $student['prenom'],
                 $student['email'],
                 $student['phone']
             ], ';');
         }

         fclose($output);
         exit;
     }
}