nettown_backend/models/ExecutiveDashboardModel.php

<?php

namespace Models;

use Libs\Database;

class ExecutiveDashboardModel
{
    private \PDO $pdo;

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

    public function getDashboardData(int $companyId, array $queryParams = []): array
    {
        $anchorDate = $this->getAnchorDate($companyId);
        $latestKpi = $this->getLatestKpiSnapshot($companyId, $anchorDate);
        $previousKpi = $this->getPreviousKpiSnapshot($companyId, $anchorDate);
        $latestEmotion = $this->getLatestEmotionSnapshot($companyId, $anchorDate);
        $previousEmotion = $this->getPreviousEmotionSnapshot($companyId, $anchorDate);

        return [
            'topKpis' => $this->buildTopKpis($latestKpi, $previousKpi),
            'churnDistribution' => $this->buildChurnDistribution($latestKpi),
            'ltvRisk' => $this->buildLtvRisk($latestKpi, $previousKpi),
            'sla' => $this->buildSla($companyId, $latestKpi, $anchorDate),
            'emotions' => $this->buildEmotions($latestEmotion),
            'quickAccess' => $this->buildQuickAccess($companyId, $latestKpi, $latestEmotion, $previousEmotion, $anchorDate),
        ];
    }

    private function getAnchorDate(int $companyId): \DateTimeImmutable
    {
        $stmt = $this->pdo->prepare(
            "SELECT MAX(snapshot_date)
            FROM (
                SELECT MAX(kpi_snapshot_date)::date AS snapshot_date
                FROM kpi_snapshot
                WHERE company_id = :company_id

                UNION ALL

                SELECT MAX(emotion_snapshot_date)::date AS snapshot_date
                FROM emotion_snapshot
                WHERE company_id = :company_id

                UNION ALL

                SELECT MAX(operator_stat_date)::date AS snapshot_date
                FROM operator_daily_stats
                WHERE company_id = :company_id
            ) snapshots"
        );
        $stmt->execute(['company_id' => $companyId]);
        $anchorDate = $stmt->fetchColumn();

        if (!is_string($anchorDate) || $anchorDate === '') {
            return new \DateTimeImmutable('today');
        }

        return new \DateTimeImmutable($anchorDate);
    }

    private function getLatestKpiSnapshot(int $companyId, \DateTimeImmutable $anchorDate): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT *
            FROM kpi_snapshot
            WHERE company_id = :company_id
              AND kpi_snapshot_date <= :anchor_date
            ORDER BY kpi_snapshot_date DESC
            LIMIT 1"
        );
        $stmt->execute([
            'company_id' => $companyId,
            'anchor_date' => $anchorDate->format('Y-m-d'),
        ]);

        return $stmt->fetch(\PDO::FETCH_ASSOC) ?: [];
    }

    private function getPreviousKpiSnapshot(int $companyId, \DateTimeImmutable $anchorDate): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT *
            FROM kpi_snapshot
            WHERE company_id = :company_id
              AND kpi_snapshot_date < :anchor_date
            ORDER BY kpi_snapshot_date DESC
            LIMIT 1"
        );
        $stmt->execute([
            'company_id' => $companyId,
            'anchor_date' => $anchorDate->format('Y-m-d'),
        ]);

        return $stmt->fetch(\PDO::FETCH_ASSOC) ?: [];
    }

    private function getLatestEmotionSnapshot(int $companyId, \DateTimeImmutable $anchorDate): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT *
            FROM emotion_snapshot
            WHERE company_id = :company_id
              AND emotion_snapshot_date <= :anchor_date
            ORDER BY emotion_snapshot_date DESC
            LIMIT 1"
        );
        $stmt->execute([
            'company_id' => $companyId,
            'anchor_date' => $anchorDate->format('Y-m-d'),
        ]);

        return $stmt->fetch(\PDO::FETCH_ASSOC) ?: [];
    }

    private function getPreviousEmotionSnapshot(int $companyId, \DateTimeImmutable $anchorDate): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT *
            FROM emotion_snapshot
            WHERE company_id = :company_id
              AND emotion_snapshot_date < :anchor_date
            ORDER BY emotion_snapshot_date DESC
            LIMIT 1"
        );
        $stmt->execute([
            'company_id' => $companyId,
            'anchor_date' => $anchorDate->format('Y-m-d'),
        ]);

        return $stmt->fetch(\PDO::FETCH_ASSOC) ?: [];
    }

    private function buildTopKpis(array $latestKpi, array $previousKpi): array
    {
        $currentSales = (float) ($latestKpi['kpi_current_sales'] ?? 0);
        $previousSales = (float) ($previousKpi['kpi_current_sales'] ?? $currentSales);
        $avgTicket = (float) ($latestKpi['kpi_avg_ticket'] ?? 0);
        $previousAvgTicket = (float) ($previousKpi['kpi_avg_ticket'] ?? $avgTicket);
        $ltvAtRisk = (float) ($latestKpi['kpi_ltv_at_risk'] ?? 0);
        $previousLtvAtRisk = (float) ($previousKpi['kpi_ltv_at_risk'] ?? $ltvAtRisk);
        $criticalClients = (int) ($latestKpi['kpi_critical_risk_clients'] ?? 0);
        $previousCriticalClients = (int) ($previousKpi['kpi_critical_risk_clients'] ?? $criticalClients);

        return [
            [
                'title' => 'Venda Atual',
                'value' => $this->formatCurrency($currentSales),
                'trendLabel' => $this->formatTrendLabel($currentSales, $previousSales, 'vs ontem', false),
                'danger' => false,
            ],
            [
                'title' => 'Ticket Médio',
                'value' => $this->formatCurrency($avgTicket),
                'trendLabel' => $this->formatTrendLabel($avgTicket, $previousAvgTicket, 'vs ontem', false),
                'danger' => false,
            ],
            [
                'title' => 'Lifetime em Risco',
                'value' => $this->formatCurrency($ltvAtRisk),
                'trendLabel' => $this->formatTrendLabel($ltvAtRisk, $previousLtvAtRisk, 'vs ontem', true),
                'danger' => $ltvAtRisk > 0,
            ],
            [
                'title' => 'Clientes em Risco Crítico',
                'value' => (string) $criticalClients,
                'trendLabel' => $this->formatAbsoluteTrendLabel($criticalClients - $previousCriticalClients, 'desde ontem', true),
                'danger' => $criticalClients > 0,
            ],
        ];
    }

    private function buildChurnDistribution(array $latestKpi): array
    {
        return [
            [
                'label' => 'Baixo',
                'value' => (int) round((float) ($latestKpi['kpi_churn_low_pct'] ?? 0)),
                'color' => '#10b981',
            ],
            [
                'label' => 'Moderado',
                'value' => (int) round((float) ($latestKpi['kpi_churn_moderate_pct'] ?? 0)),
                'color' => '#f59e0b',
            ],
            [
                'label' => 'Alto',
                'value' => (int) round((float) ($latestKpi['kpi_churn_high_pct'] ?? 0)),
                'color' => '#f97316',
            ],
            [
                'label' => 'Crítico',
                'value' => (int) round((float) ($latestKpi['kpi_churn_critical_pct'] ?? 0)),
                'color' => '#ef4444',
            ],
        ];
    }

    private function buildLtvRisk(array $latestKpi, array $previousKpi): array
    {
        $ltvTotal = (float) ($latestKpi['kpi_ltv_total'] ?? 0);
        $ltvAtRisk = (float) ($latestKpi['kpi_ltv_at_risk'] ?? 0);
        $criticalClients = (int) ($latestKpi['kpi_critical_risk_clients'] ?? 0);
        $previousCriticalClients = (int) ($previousKpi['kpi_critical_risk_clients'] ?? $criticalClients);
        $delta = $criticalClients - $previousCriticalClients;

        return [
            'ltvTotal' => round($ltvTotal, 2),
            'ltvAtRisk' => round($ltvAtRisk, 2),
            'ltvRiskPct' => $ltvTotal > 0 ? (int) round(($ltvAtRisk / $ltvTotal) * 100) : 0,
            'criticalClients' => $criticalClients,
            'avgTicket' => (int) round((float) ($latestKpi['kpi_avg_ticket'] ?? 0)),
            'trendText' => $this->formatCriticalTrendText($delta),
        ];
    }

    private function buildSla(int $companyId, array $latestKpi, \DateTimeImmutable $anchorDate): array
    {
        return [
            'withinPct' => (int) round((float) ($latestKpi['kpi_sla_compliance_pct'] ?? 0)),
            'breachPct' => max(0, 100 - (int) round((float) ($latestKpi['kpi_sla_compliance_pct'] ?? 0))),
            'byDepartment' => $this->getSlaByDepartment($companyId, $anchorDate),
        ];
    }

    private function getSlaByDepartment(int $companyId, \DateTimeImmutable $anchorDate): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT
                o.operator_department AS department,
                ROUND(AVG(ods.operator_sla_compliance_pct))::int AS value
            FROM operator_daily_stats ods
            INNER JOIN operator o
                ON o.operator_id = ods.operator_id
               AND o.operator_deleted_at = 'infinity'
            WHERE ods.company_id = :company_id
              AND ods.operator_stat_date = :anchor_date
            GROUP BY o.operator_department
            ORDER BY o.operator_department ASC"
        );
        $stmt->execute([
            'company_id' => $companyId,
            'anchor_date' => $anchorDate->format('Y-m-d'),
        ]);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];

        return array_map(static function (array $row): array {
            return [
                'department' => $row['department'] ?? '',
                'value' => (int) ($row['value'] ?? 0),
            ];
        }, $rows);
    }

    private function buildEmotions(array $latestEmotion): array
    {
        $totalAnalyzed = (int) ($latestEmotion['emotion_total_analyzed'] ?? 0);
        $emotionMap = [
            ['label' => 'Alegria', 'column' => 'emotion_happiness', 'color' => '#10b981'],
            ['label' => 'Confiança', 'column' => 'emotion_confidence', 'color' => '#6366f1'],
            ['label' => 'Antecipação', 'column' => 'emotion_anticipation', 'color' => '#8b5cf6'],
            ['label' => 'Surpresa', 'column' => 'emotion_surprise', 'color' => '#f59e0b'],
            ['label' => 'Medo', 'column' => 'emotion_fear', 'color' => '#f97316'],
            ['label' => 'Raiva', 'column' => 'emotion_anger', 'color' => '#ef4444'],
            ['label' => 'Tristeza', 'column' => 'emotion_sadness', 'color' => '#64748b'],
        ];

        $items = [];
        foreach ($emotionMap as $emotion) {
            $value = (float) ($latestEmotion[$emotion['column']] ?? 0);
            $items[] = [
                'label' => $emotion['label'],
                'value' => (int) round($value),
                'count' => (int) round(($totalAnalyzed * $value) / 100),
                'color' => $emotion['color'],
            ];
        }

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

        return [
            'items' => $items,
            'avgSentimentScore' => round($this->calculateEmotionSentimentScore($latestEmotion), 2),
        ];
    }

    private function buildQuickAccess(int $companyId, array $latestKpi, array $latestEmotion, array $previousEmotion, \DateTimeImmutable $anchorDate): array
    {
        return [
            'conversationsToday' => $this->getConversationsToday($companyId, $anchorDate),
            'activePersonas' => $this->getActivePersonas($companyId),
            'activeAgents' => $this->getActiveAgents($companyId, $latestKpi),
            'activePlaybooks' => $this->getActivePlaybooks($companyId),
            'pendingSettings' => $this->getPendingSettings($companyId),
            'evolutionDelta' => $this->formatSignedPercent($this->calculateEmotionDeltaPercent($latestEmotion, $previousEmotion)),
        ];
    }

    private function getConversationsToday(int $companyId, \DateTimeImmutable $anchorDate): int
    {
        $stmt = $this->pdo->prepare(
            "SELECT COALESCE(SUM(operator_attendances_count), 0)
            FROM operator_daily_stats
            WHERE company_id = :company_id
              AND operator_stat_date = :anchor_date"
        );
        $stmt->execute([
            'company_id' => $companyId,
            'anchor_date' => $anchorDate->format('Y-m-d'),
        ]);

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

    private function getActivePersonas(int $companyId): int
    {
        $stmt = $this->pdo->prepare(
            "SELECT COUNT(*)
            FROM persona
            WHERE company_id = :company_id
              AND persona_deleted_at = 'infinity'"
        );
        $stmt->execute(['company_id' => $companyId]);

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

    private function getActiveAgents(int $companyId, array $latestKpi): int
    {
        $count = (int) ($latestKpi['kpi_total_active_operators'] ?? 0);
        if ($count > 0) {
            return $count;
        }

        $stmt = $this->pdo->prepare(
            "SELECT COUNT(*)
            FROM operator
            WHERE company_id = :company_id
              AND operator_deleted_at = 'infinity'
              AND lower(operator_status) <> 'inativo'"
        );
        $stmt->execute(['company_id' => $companyId]);

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

    private function getActivePlaybooks(int $companyId): int
    {
        $stmt = $this->pdo->prepare(
            "SELECT COUNT(*)
            FROM ai_action
            WHERE company_id = :company_id
              AND ai_action_deleted_at = 'infinity'
              AND ai_action_created_at >= NOW() - INTERVAL '30 days'"
        );
        $stmt->execute(['company_id' => $companyId]);

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

    private function getPendingSettings(int $companyId): int
    {
        $departmentsStmt = $this->pdo->prepare(
            "SELECT COUNT(DISTINCT lower(operator_department))
            FROM operator
            WHERE company_id = :company_id
              AND operator_deleted_at = 'infinity'"
        );
        $departmentsStmt->execute(['company_id' => $companyId]);
        $departmentCount = (int) $departmentsStmt->fetchColumn();

        $configsStmt = $this->pdo->prepare(
            "SELECT COUNT(DISTINCT lower(sla_config_department))
            FROM sla_config
            WHERE company_id = :company_id
              AND sla_config_deleted_at = 'infinity'"
        );
        $configsStmt->execute(['company_id' => $companyId]);
        $configCount = (int) $configsStmt->fetchColumn();

        return max(0, $departmentCount - $configCount);
    }

    private function calculateEmotionSentimentScore(array $emotionSnapshot): float
    {
        $weights = [
            'emotion_happiness' => 0.9,
            'emotion_confidence' => 0.6,
            'emotion_anticipation' => 0.3,
            'emotion_surprise' => 0.1,
            'emotion_fear' => -0.6,
            'emotion_anger' => -1.0,
            'emotion_sadness' => -0.7,
        ];

        $score = 0.0;
        foreach ($weights as $column => $weight) {
            $score += ((float) ($emotionSnapshot[$column] ?? 0) / 100) * $weight;
        }

        return max(-1, min(1, $score));
    }

    private function calculateEmotionDeltaPercent(array $latestEmotion, array $previousEmotion): float
    {
        $latest = $this->calculateEmotionSentimentScore($latestEmotion);
        $previous = $this->calculateEmotionSentimentScore($previousEmotion);

        if (abs($previous) < 0.00001) {
            return $latest > 0 ? 100.0 : 0.0;
        }

        return (($latest - $previous) / abs($previous)) * 100;
    }

    private function formatCurrency(float $value): string
    {
        return 'R$ ' . number_format($value, 2, ',', '.');
    }

    private function formatTrendLabel(float $current, float $previous, string $suffix, bool $dangerOnIncrease): string
    {
        if (abs($current - $previous) < 0.00001) {
            return '→ estável';
        }

        $deltaPct = $this->calculateDeltaPercent($current, $previous);
        $arrow = $deltaPct >= 0 ? '↑' : '↓';
        $prefix = $deltaPct >= 0 ? '+' : '';

        return sprintf('%s %s%s%% %s', $arrow, $prefix, round($deltaPct), $suffix);
    }

    private function formatAbsoluteTrendLabel(int $delta, string $suffix, bool $dangerOnIncrease): string
    {
        if ($delta === 0) {
            return '→ estável';
        }

        $arrow = $delta > 0 ? '↑' : '↓';
        $prefix = $delta > 0 ? '+' : '';

        return sprintf('%s %s%d %s', $arrow, $prefix, $delta, $suffix);
    }

    private function formatCriticalTrendText(int $delta): string
    {
        if ($delta > 0) {
            return sprintf('+%d clientes entraram em risco crítico desde ontem', $delta);
        }

        if ($delta < 0) {
            return sprintf('%d clientes saíram do risco crítico desde ontem', abs($delta));
        }

        return 'Nenhuma mudança no risco crítico desde ontem';
    }

    private function formatSignedPercent(float $value): string
    {
        $rounded = round($value);
        if ($rounded > 0) {
            return '+' . $rounded . '%';
        }

        if ($rounded < 0) {
            return (string) $rounded . '%';
        }

        return '0%';
    }

    private function calculateDeltaPercent(float $current, float $previous): float
    {
        if (abs($previous) < 0.00001) {
            return $current > 0 ? 100.0 : 0.0;
        }

        return (($current - $previous) / abs($previous)) * 100;
    }
}