<?php

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

class ConcoursService extends AbstractPdo
{
    protected $table = 'concours';
    protected $primaryKey = 'id';

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

    /**
     * Recherche
     */
    public function search($searchData, $page = 1, $perPage = 10)
    {
        $filters = [];
        $searchFields = ['code', 'label', 'filliere', 'etat_concours'];

        foreach ($searchFields as $field) {
            if (!empty($searchData[$field])) {
                $filters[$field] = [
                    'value' => $searchData[$field],
                    'like'  => true
                ];
            }
        }

        if (isset($searchData['active']) && $searchData['active'] !== '') {
            $filters['active'] = $searchData['active'];
        }

        $offset = ($page - 1) * $perPage;

        $concours = $this->findMultiple(
            $filters,
            'id DESC',
            $perPage,
            $offset
        );

        $total = $this->count($filters);

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

    /**
     * Concours + détails + membres
     */
    public function getConcoursWithDetails($id)
    {
        $concours = $this->findOne($id);

        if (!$concours) {
            return null;
        }

        $concours['members'] = $this->getCommitteeMembers($id);

        $concours['details'] = $this->getConcoursDetails($id);

        return $concours;
    }

    /**
     * Membres
     */
    public function getCommitteeMembers($concoursId)
    {
        $sql = "
            SELECT
                cm.employee_id,
                e.code,
                e.nom,
                e.prenom
            FROM commite_organisation_membre cm
            INNER JOIN employee e
                ON e.id = cm.employee_id
            WHERE cm.concours_id = :id
            ORDER BY e.nom,e.prenom
        ";

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

        $stmt->execute([
            ':id' => $concoursId
        ]);

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Détails concours
     */
    public function getConcoursDetails($concoursId)
    {
        $sql = "
            SELECT
                cd.*,
                td.code AS type_code,
                td.libelle AS type_libelle,
                td.annee_apres_bac
            FROM concours_detail cd
            LEFT JOIN type_diplome td
                ON td.id = cd.type_diplome_id
            WHERE cd.concours_id = :id
            ORDER BY td.libelle
        ";

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

        $stmt->execute([
            ':id' => $concoursId
        ]);

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Employés
     */
    public function getProfessors()
    {
        $sql = "
            SELECT *
            FROM employee
            ORDER BY nom,prenom
        ";

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

        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Types diplôme
     */
    public function getAllTypeDiplome()
    {
        $stmt = $this->pdo->prepare(
            "SELECT * FROM type_diplome ORDER BY libelle"
        );

        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    /**
 * Création concours
 */
public function createConcours($data)
{
    try {

        $this->beginTransaction();
         $concoursData = [
    'code'                       => $data['code'],
    'label'                      => $data['label'],
    'description'                => $data['description'] ?? null,
    'active'                     => $data['active'] ?? 1,
    'filliere'                   => $data['filliere'] ?? null,

    'date_lancement'             => $data['date_lancement'] ?? null,
    'date_fin_inscription'       => $data['date_fin_inscription'] ?? null,
    'date_affichage_ecrit'       => $data['date_affichage_ecrit'] ?? null,
    'date_ecrit'                 => $data['date_ecrit'] ?? null,
    'date_affichage_orale'       => $data['date_affichage_orale'] ?? null,
    'date_orale'                 => $data['date_orale'] ?? null,
    'date_affichage_resultat'    => $data['date_affichage_resultat'] ?? null,
    'date_fin'                   => $data['date_fin'] ?? null,

    'chef_commite_organisation'  => $data['chef_commite_organisation'] ?? null,

    'etat_concours'              => $data['etat_concours'] ?? 'OUVERT'
];

        // Création concours
        $concoursId = $this->insert($concoursData);

        // Création dossier
        $utilService = new \model\service\UtilService();
        $utilService->createConcoursFolder($data['code']);

        // Membres
        if (!empty($data['members']) && is_array($data['members'])) {

            foreach ($data['members'] as $employeeId) {

                $this->addCommitteeMember(
                    $concoursId,
                    $employeeId
                );

            }

        }

        // Détails
        if (!empty($data['details']) && is_array($data['details'])) {

            foreach ($data['details'] as $detail) {

                if (!empty($detail['type_diplome_id'])) {

                    $this->addConcoursDetail(
                        $concoursId,
                        $detail
                    );

                }

            }

        }

        $this->commit();

        return $concoursId;

    } catch (Exception $e) {

        $this->rollback();

        throw $e;

    }
}
/**
 * Mise à jour concours
 */
public function updateConcours($id, $data)
{
    try {

        $this->pdo->beginTransaction();

         $concoursData = [
    'code'                       => $data['code'],
    'label'                      => $data['label'],
    'description'                => $data['description'] ?? null,
    'active'                     => $data['active'] ?? 1,
    'filliere'                   => $data['filliere'] ?? null,

    'date_lancement'             => $data['date_lancement'] ?? null,
    'date_fin_inscription'       => $data['date_fin_inscription'] ?? null,
    'date_affichage_ecrit'       => $data['date_affichage_ecrit'] ?? null,
    'date_ecrit'                 => $data['date_ecrit'] ?? null,
    'date_affichage_orale'       => $data['date_affichage_orale'] ?? null,
    'date_orale'                 => $data['date_orale'] ?? null,
    'date_affichage_resultat'    => $data['date_affichage_resultat'] ?? null,
    'date_fin'                   => $data['date_fin'] ?? null,

    'chef_commite_organisation'  => $data['chef_commite_organisation'] ?? null,

    'etat_concours'              => $data['etat_concours'] ?? 'OUVERT'
];

        // Mise à jour concours
        $this->update($id, $concoursData);

        // Supprimer tous les anciens membres
        $this->removeAllCommitteeMembers($id);

        // Ajouter les nouveaux membres
        if (!empty($data['members']) && is_array($data['members'])) {

            foreach ($data['members'] as $employeeId) {

                $this->addCommitteeMember(
                    $id,
                    $employeeId
                );

            }

        }

        // Supprimer les anciens détails
        $this->removeAllConcoursDetails($id);

        // Ajouter les nouveaux détails
        if (!empty($data['details']) && is_array($data['details'])) {

            foreach ($data['details'] as $detail) {

                if (!empty($detail['type_diplome_id'])) {

                    $this->addConcoursDetail(
                        $id,
                        $detail
                    );

                }

            }

        }

        $this->pdo->commit();

        return true;

    } catch (Exception $e) {

        $this->pdo->rollBack();

        throw $e;

    }
}
/**
 * Ajouter un membre
 */
public function addCommitteeMember($concoursId, $employeeId)
{
    $sql = "INSERT INTO commite_organisation_membre
            (concours_id, employee_id)
            VALUES
            (:concoursId, :employeeId)";

    return $this->execute($sql, [
        ':concoursId' => $concoursId,
        ':employeeId' => $employeeId
    ]);
}

/**
 * Supprimer tous les membres d'un concours
 */
public function removeAllCommitteeMembers($concoursId)
{
    $sql = "DELETE
            FROM commite_organisation_membre
            WHERE concours_id = :concoursId";

    return $this->execute($sql, [
        ':concoursId' => $concoursId
    ]);
}

/**
 * Ajouter un détail
 */
public function addConcoursDetail($concoursId, $detail)
{
    $sql = "INSERT INTO concours_detail
            (
                concours_id,
                type_diplome_id,
                pourcentage,
                description
            )
            VALUES
            (
                :concoursId,
                :type_diplome_id,
                :pourcentage,
                :description
            )";

    return $this->execute($sql, [
        ':concoursId'      => $concoursId,
        ':type_diplome_id' => $detail['type_diplome_id'],
        ':pourcentage'     => $detail['pourcentage'] ?? null,
        ':description'     => $detail['description'] ?? null
    ]);
}

/**
 * Supprimer tous les détails
 */
public function removeAllConcoursDetails($concoursId)
{
    $sql = "DELETE
            FROM concours_detail
            WHERE concours_id = :concoursId";

    return $this->execute($sql, [
        ':concoursId' => $concoursId
    ]);
}

/**
 * Supprimer un concours
 */
public function deleteConcours($id)
{
    try {

        $this->pdo->beginTransaction();

        $this->removeAllCommitteeMembers($id);

        $this->removeAllConcoursDetails($id);

        $this->delete($id);

        $this->pdo->commit();

        return true;

    } catch (Exception $e) {

        $this->pdo->rollBack();

        throw $e;

    }
}

/**
 * Statistiques
 */
public function getStats()
{
    $sql = "SELECT
                COUNT(*) total,
                SUM(CASE WHEN etat_concours='OUVERT' THEN 1 ELSE 0 END) ouvert,
                SUM(CASE WHEN etat_concours='FERME' THEN 1 ELSE 0 END) ferme,
                SUM(CASE WHEN etat_concours='EN_COURS' THEN 1 ELSE 0 END) en_cours,
                SUM(CASE WHEN etat_concours='TERMINE' THEN 1 ELSE 0 END) termine
            FROM concours";

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

    return $stmt->fetch(PDO::FETCH_ASSOC);
}
}
