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

class ResultatConcoursService extends AbstractPdo {
    protected $table = 'resultat_concours';
    protected $primaryKey = 'id';

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

    /**
     * Rechercher des résultats de concours avec critères
     */
    public function search($searchData, $page = 1, $perPage = 10) {
        $filters = [];

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

        $offset = ($page - 1) * $perPage;
        $results = $this->findWithFilters($filters, $perPage, $offset);
        $total = $this->countWithFilters($filters);

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

    /**
     * Find results with filters
     */
    public function findWithFilters($filters = [], $limit = null, $offset = null) {
        $sql = "SELECT * FROM resultat_concours";
        $where = [];
        $params = [];

        foreach ($filters as $key => $filter) {
            $field = $key;

            if (isset($filter['like']) && $filter['like'] && !empty($filter['value'])) {
                $paramKey = str_replace('.', '_', $key);
                $where[] = "{$field} LIKE :{$paramKey}";
                $params[":{$paramKey}"] = '%' . $filter['value'] . '%';
            } elseif (!empty($filter['value'])) {
                $paramKey = str_replace('.', '_', $key);
                $where[] = "{$field} = :{$paramKey}";
                $params[":{$paramKey}"] = $filter['value'];
            }
        }

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

        $sql .= " ORDER BY order_preparation_ecrit ASC, order_preparation_orale ASC, order_resultat_finale ASC, libelle ASC";

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

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

    /**
     * Count results with filters
     */
    public function countWithFilters($filters = []) {
        $sql = "SELECT COUNT(*) as count FROM resultat_concours";
        $where = [];
        $params = [];

        foreach ($filters as $key => $filter) {
            $field = $key;

            if (isset($filter['like']) && $filter['like'] && !empty($filter['value'])) {
                $paramKey = str_replace('.', '_', $key);
                $where[] = "{$field} LIKE :{$paramKey}";
                $params[":{$paramKey}"] = '%' . $filter['value'] . '%';
            } elseif (!empty($filter['value'])) {
                $paramKey = str_replace('.', '_', $key);
                $where[] = "{$field} = :{$paramKey}";
                $params[":{$paramKey}"] = $filter['value'];
            }
        }

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

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $result = $stmt->fetch();
        return (int)$result['count'];
    }

    /**
     * Obtenir un résultat par ID
     */
    public function getResultatConcours($id) {
        $sql = "SELECT * FROM resultat_concours WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        return $stmt->fetch();
    }

    /**
     * Obtenir un résultat par code
     */
    public function getResultatByCode($code) {
        return $this->findOneBy(['code' => $code]);
    }

    /**
     * Créer un nouveau résultat de concours
     */
    public function createResultatConcours($data) {
        // Valider les données
        if (empty($data['libelle'])) {
            throw new Exception("Le libellé est obligatoire");
        }
        if (empty($data['code'])) {
            throw new Exception("Le code est obligatoire");
        }

        // Vérifier les doublons
        $this->checkDuplicates($data);

        // Normaliser les valeurs vides pour les badges
        $this->normalizeEmptyValues($data);

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

    /**
     * Mettre à jour un résultat de concours
     */
    public function updateResultatConcours($id, $data) {
        // Vérifier si le résultat existe
        $existing = $this->findOne($id);
        if (!$existing) {
            throw new Exception("Résultat de concours non trouvé");
        }

        // Vérifier les doublons (exclure l'ID actuel)
        $this->checkDuplicates($data, $id);

        // Normaliser les valeurs vides pour les badges
        $this->normalizeEmptyValues($data);

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

    /**
     * Normaliser les valeurs vides pour les badges et orders
     */
    private function normalizeEmptyValues(&$data) {
        // Si les ordres sont vides ou null, les mettre à 0
        $orderFields = ['order_preparation_ecrit', 'order_preparation_orale', 'order_resultat_finale'];
        foreach ($orderFields as $field) {
            if (!isset($data[$field]) || $data[$field] === '' || $data[$field] === null) {
                $data[$field] = 0;
            }
        }

        // Si les badges sont vides ou null, les mettre à chaîne vide
        $badgeFields = ['badge_preparation_ecrit', 'badge_preparation_orale', 'badge_resultat_finale'];
        foreach ($badgeFields as $field) {
            if (!isset($data[$field]) || $data[$field] === null) {
                $data[$field] = '';
            }
        }
    }

    /**
     * Vérifier les doublons
     */
    private function checkDuplicates($data, $excludeId = null) {
        $uniqueFields = ['code'];

        foreach ($uniqueFields as $field) {
            if (isset($data[$field]) && !empty($data[$field])) {
                $criteria = [$field => $data[$field]];
                $existing = $this->findOneBy($criteria);

                if ($existing && ($excludeId === null || $existing['id'] != $excludeId)) {
                    throw new Exception("Le champ '$field' est déjà utilisé par un autre résultat de concours");
                }
            }
        }
    }

    /**
     * Supprimer un résultat de concours
     */
    public function deleteResultatConcours($id) {
        // Vérifier si le résultat est utilisé par des candidats
        // Cette vérification dépend de votre structure de données
        // Par exemple, si vous avez une table candidat_concours qui utilise ce résultat

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

    /**
     * Obtenir les statistiques
     */
    public function getStats() {
        $sql = "SELECT COUNT(*) as total FROM {$this->table}";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetch();
    }

    /**
     * Exporter les résultats (CSV)
     */
    public function exportToCsv() {
        $results = $this->findAll('libelle ASC');

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

        $csv = "ID,Libellé,Code,Description,Ordre Écrit,Badge Écrit,Ordre Oral,Badge Oral,Ordre Final,Badge Final\n";
        foreach ($results as $r) {
            $csv .= implode(',', [
                    $r['id'],
                    '"' . addslashes($r['libelle']) . '"',
                    '"' . addslashes($r['code']) . '"',
                    '"' . addslashes($r['description'] ?? '') . '"',
                    $r['order_preparation_ecrit'] ?? 0,
                    '"' . addslashes($r['badge_preparation_ecrit'] ?? '') . '"',
                    $r['order_preparation_orale'] ?? 0,
                    '"' . addslashes($r['badge_preparation_orale'] ?? '') . '"',
                    $r['order_resultat_finale'] ?? 0,
                    '"' . addslashes($r['badge_resultat_finale'] ?? '') . '"'
                ]) . "\n";
        }

        return $csv;
    }

    /**
     * Obtenir tous les résultats (pour les selects)
     */
    public function getAllResultats() {
        return $this->findAll('libelle ASC');
    }

    /**
     * Obtenir les résultats par type (écrit, oral, final)
     */
    public function getResultatsByType($type) {
        $sql = "SELECT * FROM resultat_concours WHERE 1=1";
        $params = [];

        switch ($type) {
            case 'ecrit':
                $sql .= " AND order_preparation_ecrit > 0 ORDER BY order_preparation_ecrit ASC";
                break;
            case 'oral':
                $sql .= " AND order_preparation_orale > 0 ORDER BY order_preparation_orale ASC";
                break;
            case 'final':
                $sql .= " AND order_resultat_finale > 0 ORDER BY order_resultat_finale ASC";
                break;
            default:
                $sql .= " ORDER BY libelle ASC";
                break;
        }

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

    /**
     * Obtenir les badges disponibles
     */
    public function getAvailableBadges() {
        return [
            'badge-primary' => 'Primary (Bleu)',
            'badge-secondary' => 'Secondary (Gris)',
            'badge-success' => 'Success (Vert)',
            'badge-danger' => 'Danger (Rouge)',
            'badge-warning' => 'Warning (Jaune)',
            'badge-info' => 'Info (Cyan)',
            'badge-light' => 'Light (Blanc)',
            'badge-dark' => 'Dark (Noir)'
        ];
    }

    /**
     * Get result by phase and order
     */
    public function getResultByPhase($phase, $order) {
        $fieldMap = [
            'ecrit' => 'order_preparation_ecrit',
            'orale' => 'order_preparation_orale',
            'final' => 'order_resultat_finale'
        ];

        if (!isset($fieldMap[$phase])) {
            return null;
        }

        $sql = "SELECT * FROM resultat_concours WHERE {$fieldMap[$phase]} = :order LIMIT 1";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':order' => $order]);
        return $stmt->fetch();
    }
}
?>