<?php

namespace App\Models;

use Core\Model;

class Denonciation extends Model
{
    protected string $table = 'denonciations';
    
    public function getByEntreprise(int $entrepriseId, array $filters = []): array
    {
        $sql = "SELECT d.*, c.nom as client_nom, c.telephone as client_tel,
                       u.nom as agent_nom
                FROM {$this->table} d
                LEFT JOIN clients c ON d.client_id = c.id
                LEFT JOIN users u ON d.agent_id = u.id
                WHERE d.entreprise_id = :eid";
        
        $params = ['eid' => $entrepriseId];
        
        if (!empty($filters['statut'])) {
            $sql .= " AND d.statut = :statut";
            $params['statut'] = $filters['statut'];
        }
        
        if (!empty($filters['type'])) {
            $sql .= " AND d.type = :type";
            $params['type'] = $filters['type'];
        }
        
        if (!empty($filters['priorite'])) {
            $sql .= " AND d.priorite = :priorite";
            $params['priorite'] = $filters['priorite'];
        }
        
        $sql .= " ORDER BY d.created_at DESC";
        
        if (!empty($filters['limit'])) {
            $sql .= " LIMIT " . (int)$filters['limit'];
        }
        
        return $this->db->fetchAll($sql, $params);
    }
    
    public function getStats(int $entrepriseId): array
    {
        $sql = "SELECT 
                    COUNT(*) as total,
                    SUM(CASE WHEN statut = 'nouveau' THEN 1 ELSE 0 END) as nouveaux,
                    SUM(CASE WHEN statut = 'en_cours' THEN 1 ELSE 0 END) as en_cours,
                    SUM(CASE WHEN statut = 'traite' THEN 1 ELSE 0 END) as traites,
                    SUM(CASE WHEN type = 'texte' THEN 1 ELSE 0 END) as texte,
                    SUM(CASE WHEN type = 'audio' THEN 1 ELSE 0 END) as audio,
                    SUM(CASE WHEN type = 'photo' THEN 1 ELSE 0 END) as photo,
                    SUM(CASE WHEN type = 'video' THEN 1 ELSE 0 END) as video
                FROM {$this->table}
                WHERE entreprise_id = :eid";
        
        return $this->db->fetch($sql, ['eid' => $entrepriseId]) ?: [
            'total' => 0, 'nouveaux' => 0, 'en_cours' => 0, 'traites' => 0,
            'texte' => 0, 'audio' => 0, 'photo' => 0, 'video' => 0
        ];
    }
    
    public function updateStatut(int $id, string $statut, ?int $agentId = null): bool
    {
        $data = ['statut' => $statut];
        
        if ($agentId) {
            $data['agent_id'] = $agentId;
        }
        
        if ($statut === 'traite') {
            $data['date_traitement'] = date('Y-m-d H:i:s');
        }
        
        return $this->update($id, $data);
    }
    
    public function createDenonciation(array $data): ?int
    {
        // Générer un numéro de dossier unique
        $data['numero_dossier'] = $this->generateNumeroDossier($data['entreprise_id']);
        $id = $this->create($data);
        
        if ($id) {
            // Ajouter à l'historique
            $this->addHistorique($id, null, 'creation', null, 'nouveau', 'Dossier créé');
        }
        
        return $id;
    }
    
    public function generateNumeroDossier(int $entrepriseId): string
    {
        $prefix = 'DN';
        $year = date('Y');
        $month = date('m');
        
        // Compter les dénonciations de ce mois pour cette entreprise
        $sql = "SELECT COUNT(*) as count FROM {$this->table} 
                WHERE entreprise_id = :eid 
                AND YEAR(created_at) = :year 
                AND MONTH(created_at) = :month";
        $result = $this->db->fetch($sql, ['eid' => $entrepriseId, 'year' => $year, 'month' => $month]);
        $count = ($result['count'] ?? 0) + 1;
        
        return sprintf('%s-%d%s-%04d', $prefix, $year, $month, $count);
    }
    
    public function getWithDetails(int $id): ?array
    {
        $sql = "SELECT d.*, 
                       c.nom as client_nom, c.telephone as client_tel, c.email as client_email,
                       u.nom as agent_nom, u.prenom as agent_prenom,
                       e.nom as entreprise_nom
                FROM {$this->table} d
                LEFT JOIN clients c ON d.client_id = c.id
                LEFT JOIN users u ON d.agent_id = u.id
                LEFT JOIN entreprises e ON d.entreprise_id = e.id
                WHERE d.id = :id";
        
        return $this->db->fetch($sql, ['id' => $id]);
    }
    
    public function addHistorique(int $denonciationId, ?int $userId, string $action, ?string $ancienStatut, ?string $nouveauStatut, ?string $commentaire = null): int
    {
        $sql = "INSERT INTO denonciation_historique (denonciation_id, user_id, action, ancien_statut, nouveau_statut, commentaire) 
                VALUES (:did, :uid, :action, :ancien, :nouveau, :comment)";
        
        $this->db->query($sql, [
            'did' => $denonciationId,
            'uid' => $userId,
            'action' => $action,
            'ancien' => $ancienStatut,
            'nouveau' => $nouveauStatut,
            'comment' => $commentaire
        ]);
        
        return $this->db->lastInsertId();
    }
    
    public function getHistorique(int $denonciationId): array
    {
        $sql = "SELECT h.*, u.nom as user_nom, u.prenom as user_prenom
                FROM denonciation_historique h
                LEFT JOIN users u ON h.user_id = u.id
                WHERE h.denonciation_id = :did
                ORDER BY h.created_at DESC";
        
        return $this->db->fetchAll($sql, ['did' => $denonciationId]);
    }
    
    public function updateStatutWithHistory(int $id, string $nouveauStatut, int $userId, ?string $commentaire = null): bool
    {
        $denonciation = $this->find($id);
        if (!$denonciation) return false;
        
        $ancienStatut = $denonciation['statut'];
        
        $data = ['statut' => $nouveauStatut];
        
        if ($nouveauStatut === 'en_cours' && empty($denonciation['agent_id'])) {
            $data['agent_id'] = $userId;
        }
        
        if (in_array($nouveauStatut, ['traite', 'cloture', 'rejete'])) {
            $data['date_cloture'] = date('Y-m-d H:i:s');
        }
        
        $result = $this->update($id, $data);
        
        if ($result) {
            $this->addHistorique($id, $userId, 'changement_statut', $ancienStatut, $nouveauStatut, $commentaire);
        }
        
        return $result;
    }
    
    public function assignerAgent(int $denonciationId, int $agentId, int $assignePar): bool
    {
        // Désactiver les anciennes assignations
        $sql = "UPDATE denonciation_assignations SET est_actif = FALSE, date_fin = NOW() 
                WHERE denonciation_id = :did AND est_actif = TRUE";
        $this->db->query($sql, ['did' => $denonciationId]);
        
        // Créer nouvelle assignation
        $sql = "INSERT INTO denonciation_assignations (denonciation_id, user_id, assigne_par) 
                VALUES (:did, :uid, :par)";
        $this->db->query($sql, ['did' => $denonciationId, 'uid' => $agentId, 'par' => $assignePar]);
        
        // Mettre à jour la dénonciation
        $this->update($denonciationId, ['agent_id' => $agentId]);
        
        // Historique
        $this->addHistorique($denonciationId, $assignePar, 'assignation', null, null, "Assigné à l'agent #$agentId");
        
        return true;
    }
    
    public function getAssignations(int $denonciationId): array
    {
        $sql = "SELECT a.*, u.nom as agent_nom, u.prenom as agent_prenom,
                       u2.nom as assigne_par_nom
                FROM denonciation_assignations a
                LEFT JOIN users u ON a.user_id = u.id
                LEFT JOIN users u2 ON a.assigne_par = u2.id
                WHERE a.denonciation_id = :did
                ORDER BY a.date_assignation DESC";
        
        return $this->db->fetchAll($sql, ['did' => $denonciationId]);
    }
    
    public function addPiece(int $denonciationId, int $userId, string $type, string $nomFichier, string $cheminFichier, ?string $description = null): int
    {
        $sql = "INSERT INTO denonciation_pieces (denonciation_id, user_id, type, nom_fichier, chemin_fichier, description) 
                VALUES (:did, :uid, :type, :nom, :chemin, :desc)";
        
        $this->db->query($sql, [
            'did' => $denonciationId,
            'uid' => $userId,
            'type' => $type,
            'nom' => $nomFichier,
            'chemin' => $cheminFichier,
            'desc' => $description
        ]);
        
        $pieceId = $this->db->lastInsertId();
        
        $this->addHistorique($denonciationId, $userId, 'ajout_piece', null, null, "Pièce ajoutée: $nomFichier");
        
        return $pieceId;
    }
    
    public function getPieces(int $denonciationId): array
    {
        $sql = "SELECT p.*, u.nom as user_nom
                FROM denonciation_pieces p
                LEFT JOIN users u ON p.user_id = u.id
                WHERE p.denonciation_id = :did
                ORDER BY p.created_at DESC";
        
        return $this->db->fetchAll($sql, ['did' => $denonciationId]);
    }
    
    public function updatePriorite(int $id, string $priorite, int $userId): bool
    {
        $denonciation = $this->find($id);
        $anciennePriorite = $denonciation['priorite'] ?? 'normale';
        
        $result = $this->update($id, ['priorite' => $priorite]);
        
        if ($result) {
            $this->addHistorique($id, $userId, 'changement_priorite', $anciennePriorite, $priorite, null);
        }
        
        return $result;
    }
    
    public function addNoteInterne(int $id, int $userId, string $note): bool
    {
        $result = $this->update($id, ['note_interne' => $note]);
        
        if ($result) {
            $this->addHistorique($id, $userId, 'note_interne', null, null, 'Note interne mise à jour');
        }
        
        return $result;
    }
    
    public function cloturer(int $id, int $userId, string $resolution, string $statut = 'traite'): bool
    {
        $denonciation = $this->find($id);
        $ancienStatut = $denonciation['statut'];
        
        $result = $this->update($id, [
            'statut' => $statut,
            'resolution' => $resolution,
            'date_cloture' => date('Y-m-d H:i:s')
        ]);
        
        if ($result) {
            $this->addHistorique($id, $userId, 'cloture', $ancienStatut, $statut, $resolution);
        }
        
        return $result;
    }
    
    public function getStatsDetaillees(int $entrepriseId): array
    {
        $stats = $this->getStats($entrepriseId);
        
        // Stats par priorité
        $sql = "SELECT priorite, COUNT(*) as count FROM {$this->table} 
                WHERE entreprise_id = :eid GROUP BY priorite";
        $priorites = $this->db->fetchAll($sql, ['eid' => $entrepriseId]);
        $stats['par_priorite'] = [];
        foreach ($priorites as $p) {
            $stats['par_priorite'][$p['priorite'] ?? 'normale'] = $p['count'];
        }
        
        // Stats par catégorie
        $sql = "SELECT categorie, COUNT(*) as count FROM {$this->table} 
                WHERE entreprise_id = :eid AND categorie IS NOT NULL GROUP BY categorie";
        $categories = $this->db->fetchAll($sql, ['eid' => $entrepriseId]);
        $stats['par_categorie'] = [];
        foreach ($categories as $c) {
            $stats['par_categorie'][$c['categorie']] = $c['count'];
        }
        
        // Temps moyen de traitement
        $sql = "SELECT AVG(TIMESTAMPDIFF(HOUR, created_at, date_cloture)) as temps_moyen 
                FROM {$this->table} 
                WHERE entreprise_id = :eid AND date_cloture IS NOT NULL";
        $tempsResult = $this->db->fetch($sql, ['eid' => $entrepriseId]);
        $stats['temps_moyen_heures'] = round($tempsResult['temps_moyen'] ?? 0, 1);
        
        return $stats;
    }
}
