<?php
/**
 * Model Conversation
 * mySupport - Plateforme de Service Client Intelligent
 */

namespace App\Models;

use Core\Model;

class Conversation extends Model
{
    protected string $table = 'conversations';

    public function getByEntreprise(int $entrepriseId, ?string $statut = null, int $limit = 50): array
    {
        $sql = "SELECT c.*, cl.nom as client_nom, cl.telephone as client_telephone,
                       u.nom as agent_nom, u.prenom as agent_prenom
                FROM {$this->table} c
                LEFT JOIN clients cl ON c.client_id = cl.id
                LEFT JOIN users u ON c.agent_id = u.id
                WHERE c.entreprise_id = :entreprise_id";
        $params = ['entreprise_id' => $entrepriseId];
        
        if ($statut) {
            $sql .= " AND c.statut = :statut";
            $params['statut'] = $statut;
        }
        
        $sql .= " ORDER BY c.updated_at DESC LIMIT {$limit}";
        
        return $this->db->fetchAll($sql, $params);
    }

    public function getByAgent(int $agentId, ?string $statut = null): array
    {
        $sql = "SELECT c.*, cl.nom as client_nom, cl.telephone as client_telephone
                FROM {$this->table} c
                LEFT JOIN clients cl ON c.client_id = cl.id
                WHERE c.agent_id = :agent_id";
        $params = ['agent_id' => $agentId];
        
        if ($statut) {
            $sql .= " AND c.statut = :statut";
            $params['statut'] = $statut;
        }
        
        $sql .= " ORDER BY c.updated_at DESC";
        
        return $this->db->fetchAll($sql, $params);
    }

    public function getOpenConversations(int $entrepriseId): array
    {
        return $this->getByEntreprise($entrepriseId, 'open');
    }

    public function getUnassigned(int $entrepriseId): array
    {
        $sql = "SELECT c.*, cl.nom as client_nom, cl.telephone as client_telephone
                FROM {$this->table} c
                LEFT JOIN clients cl ON c.client_id = cl.id
                WHERE c.entreprise_id = :entreprise_id
                AND c.agent_id IS NULL
                AND c.statut IN ('open', 'in_progress')
                ORDER BY c.created_at ASC";
        
        return $this->db->fetchAll($sql, ['entreprise_id' => $entrepriseId]);
    }

    public function assignToAgent(int $conversationId, int $agentId): bool
    {
        return $this->update($conversationId, [
            'agent_id' => $agentId,
            'statut' => 'in_progress'
        ]) > 0;
    }

    public function escalate(int $conversationId, string $reason): bool
    {
        return $this->update($conversationId, [
            'is_escalated' => true,
            'escalation_reason' => $reason,
            'agent_id' => null,
            'statut' => 'open'
        ]) > 0;
    }

    public function resolve(int $conversationId, ?int $satisfactionScore = null): bool
    {
        $conversation = $this->find($conversationId);
        $now = time();
        $createdAt = strtotime($conversation['created_at']);
        $resolutionTime = $now - $createdAt;
        
        $data = [
            'statut' => 'resolved',
            'resolution_time' => $resolutionTime,
            'closed_at' => date('Y-m-d H:i:s')
        ];
        
        if ($satisfactionScore !== null) {
            $data['satisfaction_score'] = $satisfactionScore;
        }
        
        return $this->update($conversationId, $data) > 0;
    }

    public function close(int $conversationId): bool
    {
        return $this->update($conversationId, [
            'statut' => 'closed',
            'closed_at' => date('Y-m-d H:i:s')
        ]) > 0;
    }
    
    /**
     * Fermer automatiquement les conversations inactives
     * @param int $minutes Minutes d'inactivité (défaut: 60 = 1 heure)
     * @return int Nombre de conversations fermées
     */
    public function closeInactiveConversations(int $minutes = 60): int
    {
        $sql = "UPDATE {$this->table} 
                SET statut = 'closed', 
                    closed_at = NOW(),
                    notes = CONCAT(IFNULL(notes, ''), ' [Fermée automatiquement après inactivité]')
                WHERE statut IN ('open', 'in_progress')
                AND updated_at < DATE_SUB(NOW(), INTERVAL :minutes MINUTE)";
        
        return $this->db->execute($sql, ['minutes' => $minutes]);
    }
    
    /**
     * Obtenir les conversations inactives (pour preview avant fermeture)
     */
    public function getInactiveConversations(int $minutes = 60): array
    {
        $sql = "SELECT c.*, cl.nom as client_nom
                FROM {$this->table} c
                LEFT JOIN clients cl ON c.client_id = cl.id
                WHERE c.statut IN ('open', 'in_progress')
                AND c.updated_at < DATE_SUB(NOW(), INTERVAL :minutes MINUTE)";
        
        return $this->db->fetchAll($sql, ['minutes' => $minutes]);
    }

    public function getStatistiques(int $entrepriseId, ?string $dateFrom = null, ?string $dateTo = null): array
    {
        $params = ['entreprise_id' => $entrepriseId];
        $dateCondition = "";
        
        if ($dateFrom) {
            $dateCondition .= " AND c.created_at >= :date_from";
            $params['date_from'] = $dateFrom;
        }
        if ($dateTo) {
            $dateCondition .= " AND c.created_at <= :date_to";
            $params['date_to'] = $dateTo;
        }
        
        $sql = "SELECT 
                    COUNT(*) as total,
                    SUM(CASE WHEN statut = 'open' THEN 1 ELSE 0 END) as ouvertes,
                    SUM(CASE WHEN statut = 'in_progress' THEN 1 ELSE 0 END) as en_cours,
                    SUM(CASE WHEN statut = 'resolved' THEN 1 ELSE 0 END) as resolues,
                    SUM(CASE WHEN statut = 'closed' THEN 1 ELSE 0 END) as fermees,
                    SUM(CASE WHEN is_escalated = TRUE THEN 1 ELSE 0 END) as escaladees,
                    AVG(resolution_time) as temps_resolution_moyen,
                    AVG(satisfaction_score) as satisfaction_moyenne
                FROM {$this->table} c
                WHERE entreprise_id = :entreprise_id {$dateCondition}";
        
        return $this->db->fetch($sql, $params);
    }

    public function countByCanal(int $entrepriseId): array
    {
        $sql = "SELECT canal, COUNT(*) as count 
                FROM {$this->table} 
                WHERE entreprise_id = :entreprise_id 
                GROUP BY canal";
        
        return $this->db->fetchAll($sql, ['entreprise_id' => $entrepriseId]);
    }

    public function getConversationWithMessages(int $id): ?array
    {
        $conversation = $this->find($id);
        if (!$conversation) {
            return null;
        }
        
        $sql = "SELECT m.*, 
                       CASE WHEN m.sender_type = 'agent' THEN u.nom ELSE NULL END as agent_nom
                FROM messages m
                LEFT JOIN users u ON m.sender_id = u.id AND m.sender_type = 'agent'
                WHERE m.conversation_id = :id
                ORDER BY m.created_at ASC";
        
        $conversation['messages'] = $this->db->fetchAll($sql, ['id' => $id]);
        
        return $conversation;
    }
}
