nettown_backend/models/AnalyticsSentimentDashboardModel.php

<?php

namespace Models;

use Libs\Database;

class AnalyticsSentimentDashboardModel
{
    private \PDO $pdo;

    public function __construct()
    {
        $this->pdo = Database::pdo();
    }

    public function getDashboardData(int $companyId, array $queryParams): array
    {
        $filters = $this->normalizeFilters($queryParams);
        $summaryRange = $this->resolveRange($filters['timeframe']);

        return [
            'summaryCards' => $this->getSummaryCards($companyId, $filters, $summaryRange),
            'alerts' => $this->getAlerts($companyId, $filters, $summaryRange),
            'timelineViews' => [
                'day' => $this->getDayTimeline($companyId, $filters),
                'week' => $this->getWeekTimeline($companyId, $filters),
                'month' => $this->getMonthTimeline($companyId, $filters),
            ],
            'aspects' => $this->getAspects($companyId, $filters, $summaryRange),
        ];
    }

    private function normalizeFilters(array $queryParams): array
    {
        $timeframe = strtolower(trim((string) ($queryParams['timeframe'] ?? 'week')));
        if (!in_array($timeframe, ['day', 'week', 'month'], true)) {
            $timeframe = 'week';
        }

        $sentiment = strtolower(trim((string) ($queryParams['sentiment'] ?? 'all')));
        if (!in_array($sentiment, ['all', 'positive', 'neutral', 'negative'], true)) {
            $sentiment = 'all';
        }

        $aspect = trim((string) ($queryParams['aspect'] ?? ''));
        if ($aspect === '' || strtolower($aspect) === 'all') {
            $aspect = null;
        }

        return [
            'timeframe' => $timeframe,
            'sentiment' => $sentiment,
            'aspect' => $aspect !== null ? mb_strtolower($aspect) : null,
        ];
    }

    private function resolveRange(string $timeframe): array
    {
        $today = new \DateTimeImmutable('today');

        if ($timeframe === 'day') {
            $start = $today;
        } elseif ($timeframe === 'month') {
            $start = $today->modify('-29 days');
        } else {
            $start = $today->modify('-6 days');
        }

        return [
            'start_datetime' => $start->format('Y-m-d 00:00:00'),
            'end_exclusive_datetime' => $today->modify('+1 day')->format('Y-m-d 00:00:00'),
        ];
    }

    private function getSummaryCards(int $companyId, array $filters, array $range): array
    {
        $atRiskClients = $this->getAlertClientCountByTypes(
            $companyId,
            ['churn_risk', 'frustration', 'risk', 'at_risk'],
            $range
        );
        $opportunities = $this->getAlertClientCountByTypes(
            $companyId,
            ['buying_intent', 'opportunity', 'upsell', 'cross_sell'],
            $range
        );
        $recentInteractions = $this->getRecentInteractionsCount($companyId, $filters, $range);
        $netTrend = $this->getNetTrend($companyId, $filters, $range);

        return [
            [
                'id' => 'atRiskClients',
                'label' => 'Clientes em risco',
                'value' => $atRiskClients,
                'image' => '/images/sentiment/risk.svg',
            ],
            [
                'id' => 'opportunities',
                'label' => 'Oportunidades',
                'value' => $opportunities,
                'image' => '/images/sentiment/opportunity.svg',
            ],
            [
                'id' => 'recentInteractions',
                'label' => 'Interacoes recentes',
                'value' => $recentInteractions,
                'image' => '/images/sentiment/interactions.svg',
            ],
            [
                'id' => 'netTrend',
                'label' => 'Tendencia liquida',
                'value' => $netTrend,
                'image' => '/images/sentiment/trend.svg',
            ],
        ];
    }

    private function getAlertClientCountByTypes(int $companyId, array $types, array $range): int
    {
        $placeholders = [];
        $params = [
            'company_id' => $companyId,
            'start_datetime' => $range['start_datetime'],
            'end_exclusive_datetime' => $range['end_exclusive_datetime'],
        ];

        foreach ($types as $index => $type) {
            $key = 'type_' . $index;
            $placeholders[] = ':' . $key;
            $params[$key] = $type;
        }

        $stmt = $this->pdo->prepare(
            "SELECT COUNT(DISTINCT client_id)
            FROM alert
            WHERE company_id = :company_id
              AND alert_deleted_at = 'infinity'
              AND alert_is_resolved = FALSE
              AND alert_created_at >= :start_datetime
              AND alert_created_at < :end_exclusive_datetime
              AND lower(alert_type) IN (" . implode(', ', $placeholders) . ')'
        );
        $stmt->execute($params);

        return (int) $stmt->fetchColumn();
    }

    private function getRecentInteractionsCount(int $companyId, array $filters, array $range): int
    {
        $sql = "SELECT COUNT(DISTINCT c.conversation_id)
            FROM conversation c
            LEFT JOIN conversation_analysis ca
                ON ca.conversation_id = c.conversation_id
               AND ca.conversation_analysis_deleted_at = 'infinity'
            WHERE c.company_id = :company_id
              AND c.conversation_deleted_at = 'infinity'
              AND c.conversation_last_message_at >= :start_datetime
              AND c.conversation_last_message_at < :end_exclusive_datetime";
        $params = [
            'company_id' => $companyId,
            'start_datetime' => $range['start_datetime'],
            'end_exclusive_datetime' => $range['end_exclusive_datetime'],
        ];
        $this->appendConversationAnalysisFilters($sql, $params, 'ca', $filters);

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

        return (int) $stmt->fetchColumn();
    }

    private function getNetTrend(int $companyId, array $filters, array $range): string
    {
        $sql = "SELECT
                COALESCE(SUM(CASE WHEN po.opinion_is_positive THEN 1 ELSE 0 END), 0) AS gains,
                COALESCE(SUM(CASE WHEN po.opinion_is_positive THEN 0 ELSE 1 END), 0) AS losses
            FROM public_opinion po
            INNER JOIN conversation c
                ON c.conversation_id = po.conversation_id
               AND c.conversation_deleted_at = 'infinity'
            LEFT JOIN conversation_analysis ca
                ON ca.conversation_id = po.conversation_id
               AND ca.conversation_analysis_deleted_at = 'infinity'
            WHERE po.company_id = :company_id
              AND po.opinion_deleted_at = 'infinity'
              AND po.opinion_classified_at >= :start_datetime
              AND po.opinion_classified_at < :end_exclusive_datetime";
        $params = [
            'company_id' => $companyId,
            'start_datetime' => $range['start_datetime'],
            'end_exclusive_datetime' => $range['end_exclusive_datetime'],
        ];
        $this->appendConversationAnalysisFilters($sql, $params, 'ca', $filters);

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC) ?: [];

        $gains = (int) ($row['gains'] ?? 0);
        $losses = (int) ($row['losses'] ?? 0);
        $total = $gains + $losses;
        if ($total === 0) {
            return '0%';
        }

        $percentage = (int) round((($gains - $losses) / $total) * 100);
        if ($percentage > 0) {
            return '+' . $percentage . '%';
        }

        return $percentage . '%';
    }

    private function getAlerts(int $companyId, array $filters, array $range): array
    {
        $sql = "SELECT
                a.alert_id,
                a.client_id,
                cl.client_name,
                a.alert_title,
                a.alert_description,
                a.alert_priority,
                a.alert_type,
                a.alert_created_at
            FROM alert a
            INNER JOIN client cl
                ON cl.client_id = a.client_id
               AND cl.client_deleted_at = 'infinity'
            WHERE a.company_id = :company_id
              AND a.alert_deleted_at = 'infinity'
              AND a.alert_is_resolved = FALSE
              AND a.alert_created_at >= :start_datetime
              AND a.alert_created_at < :end_exclusive_datetime";
        $params = [
            'company_id' => $companyId,
            'start_datetime' => $range['start_datetime'],
            'end_exclusive_datetime' => $range['end_exclusive_datetime'],
        ];

        if ($filters['aspect'] !== null || $filters['sentiment'] !== 'all') {
            $sql .= "
              AND EXISTS (
                    SELECT 1
                    FROM conversation c
                    INNER JOIN conversation_analysis ca
                        ON ca.conversation_id = c.conversation_id
                       AND ca.conversation_analysis_deleted_at = 'infinity'
                    WHERE c.company_id = a.company_id
                      AND c.client_id = a.client_id
                      AND c.conversation_deleted_at = 'infinity'
                      AND c.conversation_last_message_at >= :filter_start_datetime
                      AND c.conversation_last_message_at < :filter_end_exclusive_datetime";
            $params['filter_start_datetime'] = $range['start_datetime'];
            $params['filter_end_exclusive_datetime'] = $range['end_exclusive_datetime'];
            $this->appendConversationAnalysisFilters($sql, $params, 'ca', $filters);
            $sql .= "
              )";
        }

        $sql .= "
            ORDER BY
                CASE lower(a.alert_priority)
                    WHEN 'high' THEN 1
                    WHEN 'medium' THEN 2
                    ELSE 3
                END,
                a.alert_created_at DESC
            LIMIT 10";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];

        return array_map(function (array $row): array {
            $priority = $this->normalizeAlertPriority((string) ($row['alert_priority'] ?? 'low'));

            return [
                'id' => 'alert-' . (int) $row['alert_id'],
                'clientId' => (int) ($row['client_id'] ?? 0),
                'clientName' => $row['client_name'] ?? '',
                'title' => $row['alert_title'] ?? '',
                'description' => $row['alert_description'] ?? '',
                'priority' => $priority,
                'priorityLabel' => $this->getPriorityLabel($priority),
                'category' => $this->normalizeAlertCategory((string) ($row['alert_type'] ?? '')),
            ];
        }, $rows);
    }

    private function getDayTimeline(int $companyId, array $filters): array
    {
        $today = new \DateTimeImmutable('today');
        $start = $today->modify('-6 days');
        $range = [
            'start_datetime' => $start->format('Y-m-d 00:00:00'),
            'end_exclusive_datetime' => $today->modify('+1 day')->format('Y-m-d 00:00:00'),
        ];
        $stats = $this->getOpinionBuckets(
            $companyId,
            $filters,
            $range,
            "TO_CHAR(DATE(po.opinion_classified_at), 'YYYY-MM-DD')"
        );

        $items = [];
        for ($i = 0; $i < 7; $i++) {
            $bucketDate = $start->modify('+' . $i . ' days')->format('Y-m-d');
            $items[] = [
                'period' => 'Dia ' . ($i + 1),
                'gains' => (int) ($stats[$bucketDate]['gains'] ?? 0),
                'losses' => (int) ($stats[$bucketDate]['losses'] ?? 0),
            ];
        }

        return $items;
    }

    private function getWeekTimeline(int $companyId, array $filters): array
    {
        $currentWeek = new \DateTimeImmutable('monday this week');
        $start = $currentWeek->modify('-5 weeks');
        $range = [
            'start_datetime' => $start->format('Y-m-d 00:00:00'),
            'end_exclusive_datetime' => $currentWeek->modify('+1 week')->format('Y-m-d 00:00:00'),
        ];
        $stats = $this->getOpinionBuckets(
            $companyId,
            $filters,
            $range,
            "TO_CHAR(DATE_TRUNC('week', po.opinion_classified_at), 'YYYY-MM-DD')"
        );

        $items = [];
        for ($i = 0; $i < 6; $i++) {
            $bucketDate = $start->modify('+' . $i . ' weeks')->format('Y-m-d');
            $items[] = [
                'period' => 'Sem ' . ($i + 1),
                'gains' => (int) ($stats[$bucketDate]['gains'] ?? 0),
                'losses' => (int) ($stats[$bucketDate]['losses'] ?? 0),
            ];
        }

        return $items;
    }

    private function getMonthTimeline(int $companyId, array $filters): array
    {
        $currentMonth = new \DateTimeImmutable('first day of this month');
        $start = $currentMonth->modify('-5 months');
        $range = [
            'start_datetime' => $start->format('Y-m-d 00:00:00'),
            'end_exclusive_datetime' => $currentMonth->modify('+1 month')->format('Y-m-d 00:00:00'),
        ];
        $stats = $this->getOpinionBuckets(
            $companyId,
            $filters,
            $range,
            "TO_CHAR(DATE_TRUNC('month', po.opinion_classified_at), 'YYYY-MM-DD')"
        );

        $items = [];
        for ($i = 0; $i < 6; $i++) {
            $bucketDate = $start->modify('+' . $i . ' months')->format('Y-m-d');
            $items[] = [
                'period' => $this->formatMonthLabel($start->modify('+' . $i . ' months')),
                'gains' => (int) ($stats[$bucketDate]['gains'] ?? 0),
                'losses' => (int) ($stats[$bucketDate]['losses'] ?? 0),
            ];
        }

        return $items;
    }

    private function getOpinionBuckets(int $companyId, array $filters, array $range, string $bucketSql): array
    {
        $sql = "SELECT
                {$bucketSql} AS bucket_key,
                COALESCE(SUM(CASE WHEN po.opinion_is_positive THEN 1 ELSE 0 END), 0) AS gains,
                COALESCE(SUM(CASE WHEN po.opinion_is_positive THEN 0 ELSE 1 END), 0) AS losses
            FROM public_opinion po
            INNER JOIN conversation c
                ON c.conversation_id = po.conversation_id
               AND c.conversation_deleted_at = 'infinity'
            LEFT JOIN conversation_analysis ca
                ON ca.conversation_id = po.conversation_id
               AND ca.conversation_analysis_deleted_at = 'infinity'
            WHERE po.company_id = :company_id
              AND po.opinion_deleted_at = 'infinity'
              AND po.opinion_classified_at >= :start_datetime
              AND po.opinion_classified_at < :end_exclusive_datetime";
        $params = [
            'company_id' => $companyId,
            'start_datetime' => $range['start_datetime'],
            'end_exclusive_datetime' => $range['end_exclusive_datetime'],
        ];
        $this->appendConversationAnalysisFilters($sql, $params, 'ca', $filters);

        $sql .= "
            GROUP BY bucket_key
            ORDER BY bucket_key ASC";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];

        $mapped = [];
        foreach ($rows as $row) {
            $mapped[$row['bucket_key']] = [
                'gains' => (int) ($row['gains'] ?? 0),
                'losses' => (int) ($row['losses'] ?? 0),
            ];
        }

        return $mapped;
    }

    private function getAspects(int $companyId, array $filters, array $range): array
    {
        $sql = "SELECT
                af.aspect_feedback_aspect,
                af.aspect_feedback_sentiment,
                af.aspect_feedback_text,
                cl.client_name,
                cl.client_phone,
                af.aspect_feedback_created_at
            FROM aspect_feedback af
            INNER JOIN conversation c
                ON c.conversation_id = af.conversation_id
               AND c.conversation_deleted_at = 'infinity'
            INNER JOIN client cl
                ON cl.client_id = c.client_id
               AND cl.client_deleted_at = 'infinity'
            WHERE af.company_id = :company_id
              AND af.aspect_feedback_deleted_at = 'infinity'
              AND af.aspect_feedback_created_at >= :start_datetime
              AND af.aspect_feedback_created_at < :end_exclusive_datetime";
        $params = [
            'company_id' => $companyId,
            'start_datetime' => $range['start_datetime'],
            'end_exclusive_datetime' => $range['end_exclusive_datetime'],
        ];

        if ($filters['aspect'] !== null) {
            $sql .= " AND lower(af.aspect_feedback_aspect) = :aspect";
            $params['aspect'] = $filters['aspect'];
        }

        if ($filters['sentiment'] !== 'all') {
            $sql .= ' AND ' . $this->getAspectSentimentWhereClause('af', $filters['sentiment']);
        }

        $sql .= "
            ORDER BY af.aspect_feedback_aspect ASC, af.aspect_feedback_created_at DESC";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];

        $grouped = [];
        foreach ($rows as $row) {
            $name = (string) ($row['aspect_feedback_aspect'] ?? '');
            $id = $this->slugify($name);
            if (!isset($grouped[$id])) {
                $grouped[$id] = [
                    'id' => $id,
                    'name' => $name,
                    'volume' => 0,
                    'positive' => [],
                    'neutral' => [],
                    'negative' => [],
                ];
            }

            $sentiment = $this->normalizeAspectSentiment((string) ($row['aspect_feedback_sentiment'] ?? 'neutral'));
            $grouped[$id]['volume']++;
            $grouped[$id][$sentiment][] = [
                'text' => $row['aspect_feedback_text'] ?? '',
                'client' => $row['client_name'] !== '' ? $row['client_name'] : ($row['client_phone'] ?? ''),
            ];
        }

        usort($grouped, static function (array $left, array $right): int {
            return $right['volume'] <=> $left['volume'];
        });

        return array_values($grouped);
    }

    private function appendConversationAnalysisFilters(string &$sql, array &$params, string $alias, array $filters): void
    {
        if ($filters['aspect'] !== null) {
            $sql .= " AND lower(COALESCE({$alias}.conversation_analysis_aspect, '')) = :aspect";
            $params['aspect'] = $filters['aspect'];
        }

        if ($filters['sentiment'] !== 'all') {
            $sql .= ' AND ' . $this->getConversationSentimentWhereClause($alias, $filters['sentiment']);
        }
    }

    private function getConversationSentimentWhereClause(string $alias, string $sentiment): string
    {
        if ($sentiment === 'positive') {
            return "(
                lower(COALESCE({$alias}.conversation_analysis_sentiment, '')) IN ('positive', 'positivo')
                OR {$alias}.conversation_analysis_sentiment_score >= 0.15
            )";
        }

        if ($sentiment === 'negative') {
            return "(
                lower(COALESCE({$alias}.conversation_analysis_sentiment, '')) IN ('negative', 'negativo')
                OR {$alias}.conversation_analysis_sentiment_score <= -0.15
            )";
        }

        return "(
            {$alias}.conversation_id IS NOT NULL
            AND lower(COALESCE({$alias}.conversation_analysis_sentiment, '')) NOT IN ('positive', 'positivo', 'negative', 'negativo')
            AND {$alias}.conversation_analysis_sentiment_score > -0.15
            AND {$alias}.conversation_analysis_sentiment_score < 0.15
        )";
    }

    private function getAspectSentimentWhereClause(string $alias, string $sentiment): string
    {
        if ($sentiment === 'positive') {
            return "lower({$alias}.aspect_feedback_sentiment) IN ('positive', 'positivo')";
        }

        if ($sentiment === 'negative') {
            return "lower({$alias}.aspect_feedback_sentiment) IN ('negative', 'negativo')";
        }

        return "lower({$alias}.aspect_feedback_sentiment) NOT IN ('positive', 'positivo', 'negative', 'negativo')";
    }

    private function normalizeAspectSentiment(string $sentiment): string
    {
        $normalized = mb_strtolower(trim($sentiment));

        if (in_array($normalized, ['positive', 'positivo'], true)) {
            return 'positive';
        }

        if (in_array($normalized, ['negative', 'negativo'], true)) {
            return 'negative';
        }

        return 'neutral';
    }

    private function normalizeAlertPriority(string $priority): string
    {
        $normalized = mb_strtolower(trim($priority));

        if (in_array($normalized, ['high', 'alta'], true)) {
            return 'high';
        }

        if (in_array($normalized, ['medium', 'media', 'média'], true)) {
            return 'medium';
        }

        return 'low';
    }

    private function getPriorityLabel(string $priority): string
    {
        if ($priority === 'high') {
            return 'Alta prioridade';
        }

        if ($priority === 'medium') {
            return 'Media prioridade';
        }

        return 'Baixa prioridade';
    }

    private function normalizeAlertCategory(string $type): string
    {
        $normalized = mb_strtolower(trim($type));

        if (in_array($normalized, ['buying_intent', 'opportunity', 'upsell', 'cross_sell'], true)) {
            return 'buying_intent';
        }

        if (in_array($normalized, ['frustration', 'frustracao', 'frustração'], true)) {
            return 'frustration';
        }

        return 'churn_risk';
    }

    private function formatMonthLabel(\DateTimeImmutable $date): string
    {
        $labels = [
            '01' => 'Jan',
            '02' => 'Fev',
            '03' => 'Mar',
            '04' => 'Abr',
            '05' => 'Mai',
            '06' => 'Jun',
            '07' => 'Jul',
            '08' => 'Ago',
            '09' => 'Set',
            '10' => 'Out',
            '11' => 'Nov',
            '12' => 'Dez',
        ];

        return $labels[$date->format('m')] ?? $date->format('m');
    }

    private function slugify(string $value): string
    {
        $normalized = trim($value);
        if ($normalized === '') {
            return 'aspect';
        }

        if (function_exists('iconv')) {
            $converted = iconv('UTF-8', 'ASCII//TRANSLIT', $normalized);
            if (is_string($converted) && $converted !== '') {
                $normalized = $converted;
            }
        }

        $normalized = mb_strtolower($normalized);
        $normalized = preg_replace('/[^a-z0-9]+/', '-', $normalized) ?? 'aspect';
        $normalized = trim($normalized, '-');

        return $normalized !== '' ? $normalized : 'aspect';
    }
}