
<?php
// app/model/service/TypeDiplomeService.php
require_once __DIR__ . '/../core/AbstractPdo.php';

class TypeDiplomeService extends AbstractPdo {
    protected $table = 'type_diplome';
    protected $primaryKey = 'id';

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

    /**
     * Get all type diplome ordered by libelle
     */
    public function getAll() {
        return $this->findAll('libelle ASC');
    }

    /**
     * Get type diplome by ID
     */
    public function getById($id) {
        return $this->findOne($id);
    }

    /**
     * Create a new type diplome
     */
    public function createTypeDiplome($data) {
        if (empty($data['code']) || empty($data['libelle'])) {
            throw new Exception("Le code et le libellé sont obligatoires");
        }

        // Vérifier si le code existe déjà
        $existing = $this->findOneBy(['code' => $data['code']]);
        if ($existing) {
            throw new Exception("Le code '{$data['code']}' existe déjà");
        }

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

    /**
     * Update a type diplome
     */
    public function updateTypeDiplome($id, $data) {
        if (empty($data['code']) || empty($data['libelle'])) {
            throw new Exception("Le code et le libellé sont obligatoires");
        }

        // Vérifier si le code existe déjà (sauf l'enregistrement courant)
        $existing = $this->findOneBy(['code' => $data['code']]);
        if ($existing && $existing['id'] != $id) {
            throw new Exception("Le code '{$data['code']}' est déjà utilisé");
        }

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

    /**
     * Delete a type diplome
     */
    public function deleteTypeDiplome($id) {
        // Vérifier si le type est utilisé dans inscription_concours
        $sql = "SELECT COUNT(*) as count 
                FROM inscription_concours 
                WHERE type_diplome_id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($result && $result['count'] > 0) {
            throw new Exception("Ce type de diplôme est utilisé dans des inscriptions et ne peut pas être supprimé");
        }

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

    /**
     * Get types diplome with usage count
     */
    public function getWithUsageCount() {
        $sql = "SELECT td.*, COUNT(ic.id) as usage_count
                FROM type_diplome td
                LEFT JOIN inscription_concours ic ON td.id = ic.type_diplome_id
                GROUP BY td.id
                ORDER BY td.libelle ASC";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Search type diplome
     */
    public function search($keyword) {
        $sql = "SELECT * 
                FROM type_diplome
                WHERE code LIKE :keyword
                   OR libelle LIKE :keyword
                ORDER BY libelle ASC";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':keyword' => '%' . $keyword . '%']);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}
?>