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

class DocumentDossierAdministratifService extends AbstractPdo {
    protected $table = 'document_dossier_admin';
    protected $primaryKey = 'id';

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

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

        // Filtres sur les champs
        if (!empty($searchData['id'])) {
            $filters['id'] = ['value' => $searchData['id']];
        }
        if (!empty($searchData['original_name'])) {
            $filters['original_name'] = ['value' => $searchData['original_name'], 'like' => true];
        }
        if (!empty($searchData['category_id'])) {
            $filters['category_id'] = ['value' => $searchData['category_id']];
        }
        if (!empty($searchData['etat_id'])) {
            $filters['etat_document_dossier_administratif_id'] = ['value' => $searchData['etat_id']];
        }
        if ($searchData['is_verified'] !== '') {
            $filters['is_verified'] = ['value' => $searchData['is_verified']];
        }
        if (!empty($searchData['employee'])) {
            $filters['employee_search'] = ['value' => $searchData['employee'], 'like' => true];
        }

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

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

    /**
     * Find documents with filters
     */
    public function findWithFilters($filters = [], $limit = null, $offset = null) {
        $sql = "SELECT d.*,
                e.nom as employee_nom, e.prenom as employee_prenom,
                u.nom as uploaded_nom, u.prenom as uploaded_prenom,
                v.nom as verified_nom, v.prenom as verified_prenom,
                c.libelle as category_libelle,
                et.libelle as etat_libelle,
                m.libelle as mime_libelle
                FROM {$this->table} d
                LEFT JOIN employee e ON d.employee_id = e.id
                LEFT JOIN employee u ON d.uploaded_by = u.id
                LEFT JOIN employee v ON d.verified_by = v.id
                LEFT JOIN category_document_dossier_admin c ON d.category_id = c.id
                LEFT JOIN etat_document_dossier_administratif et ON d.etat_document_dossier_administratif_id = et.id
                LEFT JOIN mime_document_dossier_admin m ON d.mime_type_id = m.id";

        $where = [];
        $params = [];

        foreach ($filters as $key => $filter) {
            if ($key === 'employee_search') {
                $where[] = "(e.nom LIKE :emp_search OR e.prenom LIKE :emp_search)";
                $params[':emp_search'] = '%' . $filter['value'] . '%';
                continue;
            }

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

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

        $sql .= " ORDER BY d.id 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();
    }

    /**
     * Count documents with filters
     */
    public function countWithFilters($filters = []) {
        $sql = "SELECT COUNT(*) as count FROM {$this->table} d
                LEFT JOIN employee e ON d.employee_id = e.id";

        $where = [];
        $params = [];

        foreach ($filters as $key => $filter) {
            if ($key === 'employee_search') {
                $where[] = "(e.nom LIKE :emp_search OR e.prenom LIKE :emp_search)";
                $params[':emp_search'] = '%' . $filter['value'] . '%';
                continue;
            }

            $field = $key;
            if (isset($filter['like']) && $filter['like'] && !empty($filter['value'])) {
                $paramKey = str_replace('.', '_', $key);
                $where[] = "d.{$field} LIKE :{$paramKey}";
                $params[":{$paramKey}"] = '%' . $filter['value'] . '%';
            } elseif (!empty($filter['value'])) {
                $paramKey = str_replace('.', '_', $key);
                $where[] = "d.{$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 document par ID
     */
    public function getDocument($id) {
        return $this->findOne($id);
    }

    /**
     * Créer un nouveau document
     */
    public function createDocument($data) {
        // Valider les données
        $this->validateDocument($data);

        // Vérifier le nom de stockage unique
        $this->checkStoredNameUnique($data['stored_name']);

        // Ajouter la date d'upload
        $data['uploaded_at'] = date('Y-m-d H:i:s');

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

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

        // Valider les données
        $this->validateDocument($data, true);

        // Vérifier le nom de stockage unique (exclure l'ID actuel)
        if (isset($data['stored_name']) && $data['stored_name'] !== $existing['stored_name']) {
            $this->checkStoredNameUnique($data['stored_name'], $id);
        }

        // Ajouter la date de modification
        $data['date_modification'] = date('Y-m-d H:i:s');

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

    /**
     * Valider les données du document
     */
    private function validateDocument($data, $isUpdate = false) {
        $required = ['employee_id', 'original_name', 'stored_name', 'mime_type_id', 'size', 'uploaded_by', 'etat_document_dossier_administratif_id', 'category_id'];
        $missing = [];

        foreach ($required as $field) {
            if (empty($data[$field])) {
                $missing[] = $field;
            }
        }

        if (!empty($missing)) {
            throw new Exception("Champs obligatoires manquants: " . implode(', ', $missing));
        }

        // Vérifier que l'employé existe
        $this->checkEmployeeExists($data['employee_id']);
        // Vérifier que la catégorie existe
        $this->checkCategoryExists($data['category_id']);
        // Vérifier que le mime type existe
        $this->checkMimeTypeExists($data['mime_type_id']);
        // Vérifier que l'état existe
        $this->checkEtatExists($data['etat_document_dossier_administratif_id']);
        // Vérifier que l'uploader existe
        $this->checkEmployeeExists($data['uploaded_by']);

        // Si vérifié, vérifier le vérificateur
        if (isset($data['is_verified']) && $data['is_verified'] == 1) {
            if (empty($data['verified_by'])) {
                throw new Exception("Le champ 'verified_by' est obligatoire lorsque le document est vérifié");
            }
            $this->checkEmployeeExists($data['verified_by']);
        }
    }

    /**
     * Vérifier qu'un employé existe
     */
    private function checkEmployeeExists($id) {
        $sql = "SELECT id FROM employee WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        if (!$stmt->fetch()) {
            throw new Exception("L'employé avec l'ID '$id' n'existe pas");
        }
    }

    /**
     * Vérifier qu'une catégorie existe
     */
    private function checkCategoryExists($id) {
        $sql = "SELECT id FROM category_document_dossier_admin WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        if (!$stmt->fetch()) {
            throw new Exception("La catégorie avec l'ID '$id' n'existe pas");
        }
    }

    /**
     * Vérifier qu'un mime type existe
     */
    private function checkMimeTypeExists($id) {
        $sql = "SELECT id FROM mime_document_dossier_admin WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        if (!$stmt->fetch()) {
            throw new Exception("Le type MIME avec l'ID '$id' n'existe pas");
        }
    }

    /**
     * Vérifier qu'un état existe
     */
    private function checkEtatExists($id) {
        $sql = "SELECT id FROM etat_document_dossier_administratif WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        if (!$stmt->fetch()) {
            throw new Exception("L'état avec l'ID '$id' n'existe pas");
        }
    }

    /**
     * Vérifier l'unicité du nom de stockage
     */
    private function checkStoredNameUnique($storedName, $excludeId = null) {
        $sql = "SELECT id FROM {$this->table} WHERE stored_name = :stored_name";
        if ($excludeId) {
            $sql .= " AND id != :exclude_id";
        }
        $stmt = $this->pdo->prepare($sql);
        $params = [':stored_name' => $storedName];
        if ($excludeId) {
            $params[':exclude_id'] = $excludeId;
        }
        $stmt->execute($params);
        if ($stmt->fetch()) {
            throw new Exception("Le nom de stockage '$storedName' est déjà utilisé par un autre document");
        }
    }

    /**
     * Supprimer un document
     */
    public function deleteDocument($id) {
        // Vérifier si le document existe
        $existing = $this->findOne($id);
        if (!$existing) {
            throw new Exception("Document non trouvé");
        }

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

    /**
     * Obtenir les statistiques
     */
    public function getStats() {
        $sql = "SELECT
                COUNT(*) as total,
                SUM(is_verified = 1) as verified,
                SUM(is_verified = 0) as pending
                FROM {$this->table}";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        $result = $stmt->fetch();

        return [
            'total' => (int)$result['total'],
            'verified' => (int)$result['verified'],
            'pending' => (int)$result['pending']
        ];
    }

    /**
     * Obtenir tous les documents
     */
    public function getAllDocuments() {
        return $this->findAll('id DESC');
    }

    /**
     * Exporter les documents (CSV)
     */
    public function exportToCsv() {
        $documents = $this->findWithFilters([], null, null);

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

        $csv = "ID,Employé,Catégorie,Nom original,État,Taille,Vérifié,Date d'upload\n";
        foreach ($documents as $d) {
            $employeeName = isset($d['employee_nom']) ? $d['employee_nom'] . ' ' . ($d['employee_prenom'] ?? '') : 'ID: ' . $d['employee_id'];
            $categoryName = $d['category_libelle'] ?? 'ID: ' . $d['category_id'];
            $etatName = $d['etat_libelle'] ?? 'ID: ' . $d['etat_document_dossier_administratif_id'];

            $csv .= implode(',', [
                $d['id'],
                '"' . addslashes($employeeName) . '"',
                '"' . addslashes($categoryName) . '"',
                '"' . addslashes($d['original_name']) . '"',
                '"' . addslashes($etatName) . '"',
                $d['size'],
                $d['is_verified'] == 1 ? 'Oui' : 'Non',
                $d['uploaded_at'] ?? ''
            ]) . "\n";
        }

        return $csv;
    }
}