<?php
/**
 * Service CSAT (Customer Satisfaction)
 * mySupport - Plateforme de Service Client Intelligent
 */

namespace App\Services;

use Core\Database;

class CSATService
{
    private $db;
    
    public function __construct()
    {
        $this->db = Database::getInstance();
    }

    /**
     * Soumettre une réponse CSAT
     */
    public function submitResponse(array $data): array
    {
        $conversationId = $data['conversation_id'];
        $score = (int) $data['score'];
        $comment = $data['comment'] ?? null;
        $categories = $data['categories'] ?? [];
        $source = $data['source'] ?? 'widget';

        // Validation du score
        if ($score < 1 || $score > 5) {
            return ['success' => false, 'error' => 'Score invalide'];
        }

        // Récupérer la conversation
        $conversation = $this->db->fetch(
            "SELECT c.*, e.id as entreprise_id 
             FROM conversations c
             JOIN entreprises e ON c.entreprise_id = e.id
             WHERE c.id = :id",
            ['id' => $conversationId]
        );

        if (!$conversation) {
            return ['success' => false, 'error' => 'Conversation non trouvée'];
        }

        // Vérifier si déjà répondu
        $existing = $this->db->fetch(
            "SELECT id FROM csat_responses WHERE conversation_id = :cid",
            ['cid' => $conversationId]
        );

        if ($existing) {
            return ['success' => false, 'error' => 'Vous avez déjà donné votre avis'];
        }

        // Insérer la réponse
        $responseId = $this->db->insert('csat_responses', [
            'entreprise_id' => $conversation['entreprise_id'],
            'conversation_id' => $conversationId,
            'client_id' => $conversation['client_id'],
            'agent_id' => $conversation['agent_id'],
            'score' => $score,
            'comment' => $comment,
            'categories' => !empty($categories) ? json_encode($categories) : null,
            'source' => $source,
            'created_at' => date('Y-m-d H:i:s')
        ]);

        // Mettre à jour la conversation
        $this->db->query(
            "UPDATE conversations SET 
                csat_score = :score,
                csat_responded_at = NOW(),
                satisfaction_score = :score,
                satisfaction_comment = :comment
             WHERE id = :id",
            [
                'score' => $score,
                'comment' => $comment,
                'id' => $conversationId
            ]
        );

        // Mettre à jour les stats quotidiennes
        $this->updateDailyStats($conversation['entreprise_id'], $score);

        return [
            'success' => true,
            'response_id' => $responseId,
            'message' => 'Merci pour votre retour !'
        ];
    }

    /**
     * Soumettre via token (lien email)
     */
    public function submitViaToken(string $token, int $score, ?string $comment = null): array
    {
        // Vérifier le token
        $tokenData = $this->db->fetch(
            "SELECT * FROM csat_tokens WHERE token = :token AND is_used = FALSE AND expires_at > NOW()",
            ['token' => $token]
        );

        if (!$tokenData) {
            return ['success' => false, 'error' => 'Lien invalide ou expiré'];
        }

        // Soumettre la réponse
        $result = $this->submitResponse([
            'conversation_id' => $tokenData['conversation_id'],
            'score' => $score,
            'comment' => $comment,
            'source' => 'email'
        ]);

        if ($result['success']) {
            // Marquer le token comme utilisé
            $this->db->query(
                "UPDATE csat_tokens SET is_used = TRUE WHERE id = :id",
                ['id' => $tokenData['id']]
            );
        }

        return $result;
    }

    /**
     * Valider un token
     */
    public function validateToken(string $token): ?array
    {
        $tokenData = $this->db->fetch(
            "SELECT ct.*, c.sujet, c.created_at as conversation_date, e.nom as entreprise_nom
             FROM csat_tokens ct
             JOIN conversations c ON ct.conversation_id = c.id
             JOIN entreprises e ON c.entreprise_id = e.id
             WHERE ct.token = :token AND ct.is_used = FALSE AND ct.expires_at > NOW()",
            ['token' => $token]
        );

        return $tokenData ?: null;
    }

    /**
     * Mettre à jour les statistiques quotidiennes
     */
    private function updateDailyStats(int $entrepriseId, int $score): void
    {
        $today = date('Y-m-d');
        
        // Vérifier si une entrée existe
        $existing = $this->db->fetch(
            "SELECT id FROM csat_daily_stats WHERE entreprise_id = :eid AND date = :date",
            ['eid' => $entrepriseId, 'date' => $today]
        );

        $scoreColumn = "score_{$score}";

        if ($existing) {
            // Mettre à jour
            $this->db->query(
                "UPDATE csat_daily_stats SET 
                    total_responses = total_responses + 1,
                    {$scoreColumn} = {$scoreColumn} + 1,
                    average_score = (
                        SELECT AVG(score) FROM csat_responses 
                        WHERE entreprise_id = :eid AND DATE(created_at) = :date
                    ),
                    nps_promoters = nps_promoters + IF(:score >= 4, 1, 0),
                    nps_passives = nps_passives + IF(:score = 3, 1, 0),
                    nps_detractors = nps_detractors + IF(:score <= 2, 1, 0)
                 WHERE entreprise_id = :eid AND date = :date",
                ['eid' => $entrepriseId, 'date' => $today, 'score' => $score]
            );
        } else {
            // Créer
            $data = [
                'entreprise_id' => $entrepriseId,
                'date' => $today,
                'total_responses' => 1,
                'score_1' => $score == 1 ? 1 : 0,
                'score_2' => $score == 2 ? 1 : 0,
                'score_3' => $score == 3 ? 1 : 0,
                'score_4' => $score == 4 ? 1 : 0,
                'score_5' => $score == 5 ? 1 : 0,
                'average_score' => $score,
                'nps_promoters' => $score >= 4 ? 1 : 0,
                'nps_passives' => $score == 3 ? 1 : 0,
                'nps_detractors' => $score <= 2 ? 1 : 0,
                'created_at' => date('Y-m-d H:i:s')
            ];
            $this->db->insert('csat_daily_stats', $data);
        }
    }

    /**
     * Obtenir les statistiques CSAT pour une entreprise
     */
    public function getStats(int $entrepriseId, string $period = '30days'): array
    {
        $dateFrom = match($period) {
            '7days' => date('Y-m-d', strtotime('-7 days')),
            '30days' => date('Y-m-d', strtotime('-30 days')),
            '90days' => date('Y-m-d', strtotime('-90 days')),
            'year' => date('Y-m-d', strtotime('-1 year')),
            default => date('Y-m-d', strtotime('-30 days'))
        };

        // Statistiques globales
        $global = $this->db->fetch(
            "SELECT 
                COUNT(*) as total_responses,
                AVG(score) as average_score,
                SUM(CASE WHEN score >= 4 THEN 1 ELSE 0 END) as promoters,
                SUM(CASE WHEN score = 3 THEN 1 ELSE 0 END) as passives,
                SUM(CASE WHEN score <= 2 THEN 1 ELSE 0 END) as detractors,
                SUM(CASE WHEN score = 1 THEN 1 ELSE 0 END) as score_1,
                SUM(CASE WHEN score = 2 THEN 1 ELSE 0 END) as score_2,
                SUM(CASE WHEN score = 3 THEN 1 ELSE 0 END) as score_3,
                SUM(CASE WHEN score = 4 THEN 1 ELSE 0 END) as score_4,
                SUM(CASE WHEN score = 5 THEN 1 ELSE 0 END) as score_5
             FROM csat_responses
             WHERE entreprise_id = :eid AND created_at >= :from",
            ['eid' => $entrepriseId, 'from' => $dateFrom]
        );

        // Évolution par jour
        $evolution = $this->db->fetchAll(
            "SELECT 
                DATE(created_at) as date,
                COUNT(*) as responses,
                AVG(score) as avg_score
             FROM csat_responses
             WHERE entreprise_id = :eid AND created_at >= :from
             GROUP BY DATE(created_at)
             ORDER BY date ASC",
            ['eid' => $entrepriseId, 'from' => $dateFrom]
        );

        // Top commentaires négatifs
        $negativeComments = $this->db->fetchAll(
            "SELECT cr.score, cr.comment, cr.created_at, c.sujet
             FROM csat_responses cr
             JOIN conversations c ON cr.conversation_id = c.id
             WHERE cr.entreprise_id = :eid 
             AND cr.score <= 2 
             AND cr.comment IS NOT NULL
             AND cr.created_at >= :from
             ORDER BY cr.created_at DESC
             LIMIT 10",
            ['eid' => $entrepriseId, 'from' => $dateFrom]
        );

        // Stats par agent
        $byAgent = $this->db->fetchAll(
            "SELECT 
                u.id, u.nom, u.prenom,
                COUNT(cr.id) as responses,
                AVG(cr.score) as avg_score
             FROM csat_responses cr
             JOIN users u ON cr.agent_id = u.id
             WHERE cr.entreprise_id = :eid AND cr.created_at >= :from
             GROUP BY u.id
             ORDER BY avg_score DESC",
            ['eid' => $entrepriseId, 'from' => $dateFrom]
        );

        // Calcul NPS
        $total = $global['total_responses'] ?: 1;
        $nps = (($global['promoters'] / $total) - ($global['detractors'] / $total)) * 100;

        return [
            'period' => $period,
            'total_responses' => (int) $global['total_responses'],
            'average_score' => round($global['average_score'] ?? 0, 2),
            'nps' => round($nps, 1),
            'distribution' => [
                1 => (int) $global['score_1'],
                2 => (int) $global['score_2'],
                3 => (int) $global['score_3'],
                4 => (int) $global['score_4'],
                5 => (int) $global['score_5']
            ],
            'evolution' => $evolution,
            'negative_comments' => $negativeComments,
            'by_agent' => $byAgent
        ];
    }

    /**
     * Obtenir les dernières réponses
     */
    public function getLatestResponses(int $entrepriseId, int $limit = 20): array
    {
        return $this->db->fetchAll(
            "SELECT cr.*, c.sujet, cl.nom as client_nom, u.nom as agent_nom, u.prenom as agent_prenom
             FROM csat_responses cr
             JOIN conversations c ON cr.conversation_id = c.id
             LEFT JOIN clients cl ON cr.client_id = cl.id
             LEFT JOIN users u ON cr.agent_id = u.id
             WHERE cr.entreprise_id = :eid
             ORDER BY cr.created_at DESC
             LIMIT :limit",
            ['eid' => $entrepriseId, 'limit' => $limit]
        );
    }
}
