nettown_backend/models/DashboardOverviewModel.php

<?php

namespace Models;

use Libs\Database;

class DashboardOverviewModel
{
    private \PDO $pdo;

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

    public function getOverviewData(int $companyId, array $queryParams): array
    {
        $filters = $this->normalizeFilters($queryParams);
        $range = $this->resolveDateRange($filters['period']);

        return [
            'kpis' => $this->getKpis($companyId, $filters, $range),
            'priorityQueue' => $this->getPriorityQueue($companyId, $filters, $range),
            'radarData' => $this->getRadarData($companyId, $range),
            'volumeData' => $this->getVolumeData($companyId, $range),
            'aspectsData' => $this->getAspectsData($companyId, $filters, $range),
            'aspectsDrilldown' => $this->getAspectsDrilldown($companyId, $filters, $range),
        ];
    }

    private function normalizeFilters(array $queryParams): array
    {
        $period = strtolower((string) ($queryParams['period'] ?? 'week'));
        $unit = strtolower((string) ($queryParams['unit'] ?? 'all'));
        $area = strtolower((string) ($queryParams['area'] ?? 'all'));
        $sentiment = strtolower((string) ($queryParams['sentiment'] ?? 'all'));
        $volumeView = strtolower((string) ($queryParams['volume_view'] ?? 'day'));

        if (!in_array($period, ['today', 'yesterday', 'week'], true)) {
            $period = 'week';
        }

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

        if (!in_array($volumeView, ['hour', 'day'], true)) {
            $volumeView = 'day';
        }

        return [
            'period' => $period,
            'unit' => $unit,
            'area' => $area,
            'sentiment' => $sentiment,
            'volume_view' => $volumeView,
        ];
    }

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

        if ($period === 'today') {
            $start = $today;
            $end = $today;
        } elseif ($period === 'yesterday') {
            $start = $today->modify('-1 day');
            $end = $start;
        } else {
            $start = $today->modify('-6 days');
            $end = $today;
        }

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

    private function getKpis(int $companyId, array $filters, array $range): array
    {
        $registeredStmt = $this->pdo->prepare(
            "SELECT COUNT(*)
            FROM client
            WHERE company_id = :company_id
              AND client_deleted_at = 'infinity'
              AND client_is_registered = TRUE"
        );
        $registeredStmt->execute(['company_id' => $companyId]);

        $unregisteredStmt = $this->pdo->prepare(
            "SELECT COUNT(*)
            FROM client
            WHERE company_id = :company_id
              AND client_deleted_at = 'infinity'
              AND client_is_registered = FALSE"
        );
        $unregisteredStmt->execute(['company_id' => $companyId]);

        $activeOperatorsSql = "SELECT COUNT(*)
            FROM operator
            WHERE company_id = :company_id
              AND operator_deleted_at = 'infinity'
              AND lower(operator_status) <> 'inativo'";
        $activeOperatorsParams = ['company_id' => $companyId];

        if ($filters['area'] !== 'all') {
            $activeOperatorsSql .= " AND lower(operator_department) = :area";
            $activeOperatorsParams['area'] = $filters['area'];
        }

        $activeOperatorsStmt = $this->pdo->prepare($activeOperatorsSql);
        $activeOperatorsStmt->execute($activeOperatorsParams);

        $conversationSql = "SELECT
                COUNT(DISTINCT c.conversation_id) AS total_conversations,
                COALESCE(AVG(ca.conversation_analysis_sentiment_score), 0) AS general_sentiment_score
            FROM conversation c
            INNER JOIN operator o ON o.operator_id = c.operator_id
            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_started_at >= :start_datetime
              AND c.conversation_started_at < :end_exclusive_datetime
              AND o.operator_deleted_at = 'infinity'";
        $conversationParams = [
            'company_id' => $companyId,
            'start_datetime' => $range['start_datetime'],
            'end_exclusive_datetime' => $range['end_exclusive_datetime'],
        ];

        if ($filters['area'] !== 'all') {
            $conversationSql .= " AND lower(o.operator_department) = :area";
            $conversationParams['area'] = $filters['area'];
        }

        if ($filters['sentiment'] !== 'all') {
            $conversationSql .= ' AND ' . $this->getSentimentWhereClause('ca', $filters['sentiment']);
        }

        $conversationStmt = $this->pdo->prepare($conversationSql);
        $conversationStmt->execute($conversationParams);
        $conversationMetrics = $conversationStmt->fetch(\PDO::FETCH_ASSOC) ?: [];

        return [
            'registeredUsers' => (int) $registeredStmt->fetchColumn(),
            'activeAgents' => (int) $activeOperatorsStmt->fetchColumn(),
            'totalConversations' => (int) ($conversationMetrics['total_conversations'] ?? 0),
            'generalSentimentScore' => round((float) ($conversationMetrics['general_sentiment_score'] ?? 0), 2),
            'unregisteredUsers' => (int) $unregisteredStmt->fetchColumn(),
        ];
    }

    private function getPriorityQueue(int $companyId, array $filters, array $range): array
    {
        $sql = "SELECT
                c.conversation_id AS id,
                cl.client_name AS customer_name,
                cl.client_segment AS segment,
                COALESCE(NULLIF(a.alert_title, ''), ca.conversation_analysis_sentiment, c.conversation_status) AS status_label,
                c.conversation_sla_deadline,
                c.conversation_last_message_at,
                o.operator_name AS seller_name,
                c.conversation_last_message_preview AS last_message,
                COALESCE(NULLIF(a.alert_description, ''), CONCAT(ca.conversation_analysis_aspect, ' — ', ca.conversation_analysis_sub_aspect), c.conversation_last_message_preview) AS motive,
                c.conversation_impact_value,
                c.conversation_ticket_value,
                c.conversation_conversion_chance,
                c.conversation_optimum_window,
                c.client_id,
                c.operator_id
            FROM conversation c
            INNER JOIN client cl ON cl.client_id = c.client_id AND cl.client_deleted_at = 'infinity'
            INNER JOIN operator o ON o.operator_id = c.operator_id AND o.operator_deleted_at = 'infinity'
            LEFT JOIN conversation_analysis ca
                ON ca.conversation_id = c.conversation_id
               AND ca.conversation_analysis_deleted_at = 'infinity'
            LEFT JOIN LATERAL (
                SELECT alert_title, alert_description
                FROM alert
                WHERE company_id = c.company_id
                  AND client_id = c.client_id
                  AND alert_deleted_at = 'infinity'
                  AND alert_is_resolved = FALSE
                ORDER BY alert_created_at DESC
                LIMIT 1
            ) a ON TRUE
            WHERE c.company_id = :company_id
              AND c.conversation_deleted_at = 'infinity'
              AND c.conversation_started_at >= :start_datetime
              AND c.conversation_started_at < :end_exclusive_datetime";
        $params = [
            'company_id' => $companyId,
            'start_datetime' => $range['start_datetime'],
            'end_exclusive_datetime' => $range['end_exclusive_datetime'],
        ];

        if ($filters['area'] !== 'all') {
            $sql .= " AND lower(o.operator_department) = :area";
            $params['area'] = $filters['area'];
        }

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

        $sql .= " ORDER BY (c.conversation_sla_deadline < NOW()) DESC,
                         ROUND(c.conversation_impact_value * (c.conversation_conversion_chance / 100.0)) DESC,
                         c.conversation_last_message_at ASC
                  LIMIT 10";

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

        return array_map(function (array $row): array {
            $impact = (float) ($row['conversation_impact_value'] ?? 0);
            $chance = (int) ($row['conversation_conversion_chance'] ?? 0);

            return [
                'id' => (int) $row['id'],
                'customerName' => $row['customer_name'] ?? '',
                'segment' => $row['segment'] ?? '',
                'status' => $this->normalizeStatusLabel((string) ($row['status_label'] ?? 'open')),
                'slaStatus' => $this->formatSlaStatus($row['conversation_sla_deadline'] ?? null),
                'timeAgo' => $this->formatRelativeTime($row['conversation_last_message_at'] ?? null),
                'sellerName' => $row['seller_name'] ?? '',
                'lastMessage' => $row['last_message'] ?? '',
                'motive' => $row['motive'] ?? '',
                'impact' => (int) round($impact),
                'ticket' => (int) round((float) ($row['conversation_ticket_value'] ?? 0)),
                'chance' => $chance,
                'optimumWindow' => $row['conversation_optimum_window'] ?? '',
                'score' => (int) round($impact * ($chance / 100)),
                'conversationId' => (int) $row['id'],
                'clientId' => (int) ($row['client_id'] ?? 0),
                'operatorId' => (int) ($row['operator_id'] ?? 0),
            ];
        }, $rows);
    }

    private function getRadarData(int $companyId, array $range): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT
                emotion_confidence,
                emotion_happiness,
                emotion_anticipation,
                emotion_fear,
                emotion_sadness,
                emotion_anger
            FROM emotion_snapshot
            WHERE company_id = :company_id
              AND emotion_snapshot_date BETWEEN :start_date AND :end_date
            ORDER BY emotion_snapshot_date DESC
            LIMIT 1"
        );
        $stmt->execute([
            'company_id' => $companyId,
            'start_date' => $range['start_date'],
            'end_date' => $range['end_date'],
        ]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC) ?: [];

        return [
            ['name' => 'Confiança', 'value' => (float) ($row['emotion_confidence'] ?? 0)],
            ['name' => 'Alegria', 'value' => (float) ($row['emotion_happiness'] ?? 0)],
            ['name' => 'Antecipação', 'value' => (float) ($row['emotion_anticipation'] ?? 0)],
            ['name' => 'Medo', 'value' => (float) ($row['emotion_fear'] ?? 0)],
            ['name' => 'Tristeza', 'value' => (float) ($row['emotion_sadness'] ?? 0)],
            ['name' => 'Raiva', 'value' => (float) ($row['emotion_anger'] ?? 0)],
        ];
    }

    private function getVolumeData(int $companyId, array $range): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT
                volume_snapshot_date,
                lower(volume_channel) AS volume_channel,
                SUM(volume_message_count) AS message_count
            FROM volume_snapshot
            WHERE company_id = :company_id
              AND volume_snapshot_date BETWEEN :start_date AND :end_date
            GROUP BY volume_snapshot_date, lower(volume_channel)
            ORDER BY volume_snapshot_date ASC, lower(volume_channel) ASC"
        );
        $stmt->execute([
            'company_id' => $companyId,
            'start_date' => $range['start_date'],
            'end_date' => $range['end_date'],
        ]);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];

        $grouped = [];
        foreach ($rows as $row) {
            $date = $this->formatSnapshotDate((string) $row['volume_snapshot_date']);
            if (!isset($grouped[$date])) {
                $grouped[$date] = ['date' => $date];
            }

            $grouped[$date][$row['volume_channel']] = (int) ($row['message_count'] ?? 0);
        }

        return array_values($grouped);
    }

    private function getAspectsData(int $companyId, array $filters, array $range): array
    {
        $sql = "SELECT
                aspect_feedback_aspect AS aspect,
                SUM(CASE WHEN " . $this->getAspectSentimentCase('positive') . " THEN 1 ELSE 0 END) AS positive_count,
                SUM(CASE WHEN " . $this->getAspectSentimentCase('neutral') . " THEN 1 ELSE 0 END) AS neutral_count,
                SUM(CASE WHEN " . $this->getAspectSentimentCase('negative') . " THEN 1 ELSE 0 END) AS negative_count
            FROM aspect_feedback
            WHERE company_id = :company_id
              AND aspect_feedback_deleted_at = 'infinity'
              AND aspect_feedback_created_at >= :start_datetime
              AND 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['sentiment'] !== 'all') {
            $sql .= ' AND ' . $this->getAspectFilterClause($filters['sentiment']);
        }

        $sql .= ' GROUP BY aspect_feedback_aspect ORDER BY aspect_feedback_aspect ASC';

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

        return array_map(static function (array $row): array {
            return [
                'aspect' => $row['aspect'],
                'positive' => (int) ($row['positive_count'] ?? 0),
                'neutral' => (int) ($row['neutral_count'] ?? 0),
                'negative' => (int) ($row['negative_count'] ?? 0),
            ];
        }, $rows);
    }

    private function getAspectsDrilldown(int $companyId, array $filters, array $range): array
    {
        $sql = "SELECT
                aspect_feedback_aspect AS aspect,
                aspect_feedback_sentiment AS sentiment,
                aspect_feedback_text AS label,
                COUNT(*) AS total
            FROM aspect_feedback
            WHERE company_id = :company_id
              AND aspect_feedback_deleted_at = 'infinity'
              AND aspect_feedback_created_at >= :start_datetime
              AND 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['sentiment'] !== 'all') {
            $sql .= ' AND ' . $this->getAspectFilterClause($filters['sentiment']);
        }

        $sql .= ' GROUP BY aspect_feedback_aspect, aspect_feedback_sentiment, aspect_feedback_text
                  ORDER BY aspect_feedback_aspect ASC, COUNT(*) DESC, aspect_feedback_text ASC';

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

        $drilldown = [];
        foreach ($rows as $row) {
            $aspect = $row['aspect'];
            $sentiment = $this->normalizeAspectSentiment((string) ($row['sentiment'] ?? 'neutral'));

            if (!isset($drilldown[$aspect])) {
                $drilldown[$aspect] = [
                    'positive' => [],
                    'neutral' => [],
                    'negative' => [],
                ];
            }

            if (count($drilldown[$aspect][$sentiment]) >= 5) {
                continue;
            }

            $drilldown[$aspect][$sentiment][] = [
                'label' => $row['label'],
                'value' => (int) ($row['total'] ?? 0),
            ];
        }

        return $drilldown;
    }

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

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

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

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

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

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

    private function getAspectFilterClause(string $sentiment): string
    {
        return $this->getAspectSentimentCase($sentiment);
    }

    private function normalizeAspectSentiment(string $sentiment): string
    {
        $normalized = 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 normalizeStatusLabel(string $status): string
    {
        $normalized = trim($status);
        if ($normalized === '') {
            return 'SEM STATUS';
        }

        return mb_strtoupper(str_replace('_', ' ', $normalized));
    }

    private function formatSlaStatus(?string $deadline): string
    {
        if (!$deadline) {
            return 'Sem SLA';
        }

        $deadlineTime = strtotime($deadline);
        if ($deadlineTime === false) {
            return 'Sem SLA';
        }

        $delta = $deadlineTime - time();
        if ($delta >= 0) {
            return 'Dentro do SLA';
        }

        $overdue = abs($delta);
        if ($overdue >= 86400) {
            return 'SLA ' . floor($overdue / 86400) . 'd estourado';
        }

        if ($overdue >= 3600) {
            return 'SLA ' . floor($overdue / 3600) . 'h estourado';
        }

        return 'SLA ' . max(1, floor($overdue / 60)) . 'm estourado';
    }

    private function formatRelativeTime(?string $dateTime): string
    {
        if (!$dateTime) {
            return 'agora';
        }

        $timestamp = strtotime($dateTime);
        if ($timestamp === false) {
            return 'agora';
        }

        $delta = max(0, time() - $timestamp);
        if ($delta >= 86400) {
            return 'há ' . floor($delta / 86400) . 'd';
        }

        if ($delta >= 3600) {
            return 'há ' . floor($delta / 3600) . 'h';
        }

        if ($delta >= 60) {
            return 'há ' . floor($delta / 60) . 'm';
        }

        return 'agora';
    }

    private function formatSnapshotDate(string $date): string
    {
        return $date . 'T00:00:00Z';
    }
}