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

class DemandeDocumentService extends AbstractPdo
{
    protected $table = 'demande_document';
    protected $primaryKey = 'id';

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

    /**
     * Rechercher des demandes de document
     */
    public function search($searchData, $page = 1, $perPage = 10)
    {
        $filters = [];
        $searchFields = ['id', 'libelle', 'description'];

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

        if (!empty($searchData['employee_id'])) {
            $filters['employee_id'] = [
                'value' => $searchData['employee_id'],
                'like'  => false
            ];
        }

        if (!empty($searchData['type_demande_document_id'])) {
            $filters['type_demande_document_id'] = [
                'value' => $searchData['type_demande_document_id'],
                'like'  => false
            ];
        }

        if (!empty($searchData['etat_demande_document_id'])) {
            $filters['etat_demande_document_id'] = [
                'value' => $searchData['etat_demande_document_id'],
                'like'  => false
            ];
        }

        if (!empty($searchData['date_soumission_debut'])) {
            $filters['date_soumission_debut'] = [
                'value' => $searchData['date_soumission_debut']
            ];
        }

        if (!empty($searchData['date_soumission_fin'])) {
            $filters['date_soumission_fin'] = [
                'value' => $searchData['date_soumission_fin']
            ];
        }

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

        $demandes = $this->findWithRelations($filters, $perPage, $offset);
        $total    = $this->countWithRelations($filters);

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

    public function findWithRelations($filters = [], $limit = null, $offset = null)
    {
        $sql = "
    SELECT
        d.*,
        p.nom AS employee_nom,
        p.prenom AS employee_prenom,
        p.code AS employee_code,
        p.email AS employee_email,
        td.libelle AS type_demande_document_libelle,
        td.code AS type_demande_document_code,
        e.libelle AS etat_demande_libelle,
        e.code AS etat_demande_code
    FROM demande_document d
    LEFT JOIN employee p ON d.employee_id = p.id
    LEFT JOIN type_demande_document td ON d.type_demande_document_id = td.id
    LEFT JOIN etat_demande_document e ON d.etat_demande_document_id = e.id
";

$where = [];
$params = [];
        foreach ($filters as $key => $filter) {

            if ($key == 'date_soumission_debut') {
                $where[] = "d.date_soumission >= :date_debut";
                $params[':date_debut'] = $filter['value'];
                continue;
            }

            if ($key == 'date_soumission_fin') {

                $where[] = "d.date_soumission <= :date_fin";
                $params[':date_fin'] = $filter['value'];
                continue;

            }

            $field = "d." . $key;

            if (isset($filter['like']) && $filter['like']) {

                $param = str_replace('.', '_', $key);
                $where[] = "$field LIKE :$param";
                $params[":$param"] = "%" . $filter['value'] . "%";

            } else {

                $param = str_replace('.', '_', $key);
                $where[] = "$field = :$param";
                $params[":$param"] = $filter['value'];

            }

        }

        if (!empty($where)) {

            $sql .= " WHERE " . implode(" AND ", $where);

        }

        $sql .= " ORDER BY d.date_creation DESC";

        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();
    }

    public function countWithRelations($filters = [])
    {
        $sql = " SELECT COUNT(*) AS count  FROM demande_document d";
        $where  = [];
        $params = [];

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

            if ($key == 'date_soumission_debut') {

                $where[] = "d.date_soumission >= :date_debut";
                $params[':date_debut'] = $filter['value'];
                continue;

            }

            if ($key == 'date_soumission_fin') {

                $where[] = "d.date_soumission <= :date_fin";
                $params[':date_fin'] = $filter['value'];
                continue;

            }

            $field = "d." . $key;

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

            } else {
                $param = str_replace('.', '_', $key);
                $where[] = "$field = :$param";
                $params[":$param"] = $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 une demande par ID
     */
    public function getDemandeDocument($id)
{
    $sql = "
        SELECT
            d.*,
            p.nom AS employee_nom,
            p.prenom AS employee_prenom,
            p.code AS employee_code,
            p.email AS employee_email,
            td.libelle AS type_demande_document_libelle,
            td.code AS type_demande_document_code,
            e.libelle AS etat_demande_libelle,
            e.code AS etat_demande_code
        FROM demande_document d
        LEFT JOIN Employee p
            ON d.employee_id = p.id
        LEFT JOIN type_demande_document td
            ON d.type_demande_document_id = td.id
        LEFT JOIN etat_demande_document e
            ON d.etat_demande_document_id = e.id
        WHERE d.id = :id
    ";

    $stmt = $this->pdo->prepare($sql);
    $stmt->execute([
        ':id' => $id
    ]);

    return $stmt->fetch(PDO::FETCH_ASSOC);
}
    
    public function getDemandeDocumentByLibelle($libelle)
    {
        return $this->findOneBy([
            'libelle' => $libelle
        ]);
    }

    public function createDemandeDocument($data)
    {
        if (empty($data['libelle'])) {
            throw new Exception("Le libellé est obligatoire");
        }

        if (empty($data['employee_id'])) {
            throw new Exception("Le Employee est obligatoire");
        }

        if (empty($data['type_demande_document_id'])) {
            throw new Exception("Le type de document est obligatoire");
        }

        if (empty($data['etat_demande_document_id'])) {
            throw new Exception("L'état est obligatoire");
        }

        if (empty($data['date_soumission'])) {
            $data['date_soumission'] = date('Y-m-d');
        }
        $this->checkemployeeExists($data['employee_id']);
        $this->checkTypeDemandeDocumentExists($data['type_demande_document_id']);

        $this->checkEmployeeExists($data['employee_id']);
        $this->checkTypeDocumentExists($data['type_document_id']);

        $this->checkEtatExists($data['etat_demande_document_id']);

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

    public function updateDemandeDocument($id, $data)
    {
        $existing = $this->findOne($id);

        if (!$existing) {
            throw new Exception("Demande introuvable");
        }

        if (isset($data['employee_id'])) {
            $this->checkEmployeeExists($data['employee_id']);
        }

        if (isset($data['type_demande_document_id'])) {
            $this->checkTypeDemandeDocumentExists($data['type_demande_document_id']);
        }

        if (isset($data['etat_demande_document_id'])) {
            $this->checkEtatExists($data['etat_demande_document_id']);
        }

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

    private function checkEmployeeExists($id)
    {
        $stmt = $this->pdo->prepare("
            SELECT id FROM Employee
            WHERE id = :id ");

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

        if (!$stmt->fetch()) {
            throw new Exception("Employee introuvable");
        }
    }

    private function checkTypeDemandeDocumentExists($id)
    {
        $stmt = $this->pdo->prepare("
            SELECT id FROM type_demande_document
            WHERE id = :id
        ");

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

        if (!$stmt->fetch()) {
            throw new Exception("Type de document introuvable");
        }
    }

    private function checkEtatExists($id)
    {
        $stmt = $this->pdo->prepare("
            SELECT id FROM etat_demande_document
            WHERE id = :id
        ");

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

        if (!$stmt->fetch()) {
            throw new Exception("Etat de demande introuvable");
        }
    }

    public function deleteDemandeDocument($id)
    {
        $existing = $this->findOne($id);
        if (!$existing) {
            throw new Exception("Demande introuvable");
        }
        return $this->delete($id);
    }
        /**
     * Statistiques
     */
    public function getStats()
    {
        $sql = "SELECT COUNT(*) AS total FROM demande_document";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();

        $total = $stmt->fetch();

        $sql = "SELECT COUNT(*) AS en_attente
                FROM demande_document
                WHERE etat_demande_document_id = 1";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();

        $attente = $stmt->fetch();

        return [
            'total' => (int)$total['total'],
            'en_attente' => (int)$attente['en_attente']
        ];
    }

    /**
     * Export CSV
     */
    public function exportToCsv()
    {
        $demandes = $this->findWithRelations([], null, null);

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

        $csv = "ID,Libellé,employee,Type Demande Document,Etat,Date Soumission,Date Réponse,Description\n";

        $csv = "ID,Libellé,Employee,Type Document,Etat,Date Soumission,Date Réponse,Description\n";


        foreach ($demandes as $d) {

            $csv .= implode(",", [

                $d['id'],

                '"' . addslashes($d['libelle']) . '"',

                '"' . addslashes(trim(($d['nom'] ?? '') . ' ' . ($d['prenom'] ?? ''))) . '"',

                '"' . addslashes($d['type_demande_document_libelle'] ?? '') . '"',

                '"' . addslashes($d['etat_demande_libelle'] ?? '') . '"',

                $d['date_soumission'] ?? '',

                $d['date_reponse'] ?? '',

                '"' . addslashes($d['description'] ?? '') . '"'

            ]) . "\n";
        }

        return $csv;
    }

    /**
     * Liste des Employees
     */
    public function getEmployees()
    {
        $sql = "
            SELECT
                id,
                nom,
                prenom
            FROM Employee
            ORDER BY nom ASC, prenom ASC
        ";

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

        return $stmt->fetchAll();
    }

    /**
     * Liste des types de document
     */
    public function getTypesDemandeDocuments()
    {
        $sql = "
            SELECT
                id,
                libelle,
                code
            FROM type_demande_document
            ORDER BY libelle ASC
        ";

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

        return $stmt->fetchAll();
    }

    /**
     * Liste des états
     */
    public function getEtats()
    {
        $sql = "
            SELECT
                id,
                libelle,
                code
            FROM etat_demande_document
            ORDER BY libelle ASC
        ";

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

        return $stmt->fetchAll();
    }
}