nettown_backend/models/EvolutionOverviewModel.php

<?php

namespace Models;

use Libs\Database;

class EvolutionOverviewModel
{
    private \PDO $pdo;

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

    public function getOverviewData(int $companyId, array $queryParams = []): array
    {
        $anchorDate = $this->getAnchorDate($companyId);
        $range = $this->resolveRange($anchorDate);
        $sentimentSeries = $this->getSentimentSeries($companyId, $range);
        $playbooksSeries = $this->getPlaybooksSeries($companyId, $range);
        $playbooksTotals = $this->buildPlaybooksTotals($playbooksSeries);
        $kpiSnapshots = $this->getRecentKpiSnapshots($companyId, $anchorDate);
        $recentSentimentScores = $this->getRecentSentimentScores($companyId, $anchorDate);

        return [
            'kpis' => $this->buildKpis($kpiSnapshots, $recentSentimentScores, $playbooksTotals),
            'sentimentSeries' => $sentimentSeries,
            'playbooksSeries' => $playbooksSeries,
            'playbooksTotals' => $playbooksTotals,
        ];
    }

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

                UNION ALL

                SELECT MAX(playbook_snapshot_date)::date AS snapshot_date
                FROM playbooks_monitor
                WHERE company_id = :company_id

                UNION ALL

                SELECT MAX(kpi_snapshot_date)::date AS snapshot_date
                FROM kpi_snapshot
                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 resolveRange(\DateTimeImmutable $anchorDate): array
    {
        $startDate = $anchorDate->modify('-6 days');

        return [
            'start_date' => $startDate->format('Y-m-d'),
            'end_date' => $anchorDate->format('Y-m-d'),
        ];
    }

    private function getSentimentSeries(int $companyId, array $range): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT
                evolution_snapshot_date,
                evolution_sentiment_score
            FROM sentiment_evolution
            WHERE company_id = :company_id
              AND evolution_snapshot_date >= :start_date
              AND evolution_snapshot_date <= :end_date
            ORDER BY evolution_snapshot_date ASC"
        );
        $stmt->execute([
            'company_id' => $companyId,
            'start_date' => $range['start_date'],
            'end_date' => $range['end_date'],
        ]);

        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
        $mapped = [];
        foreach ($rows as $row) {
            $mapped[$row['evolution_snapshot_date']] = (float) ($row['evolution_sentiment_score'] ?? 0);
        }

        $items = [];
        $current = new \DateTimeImmutable($range['start_date']);
        $end = new \DateTimeImmutable($range['end_date']);

        while ($current <= $end) {
            $date = $current->format('Y-m-d');
            $items[] = [
                'date' => $date,
                'value' => (float) ($mapped[$date] ?? 0),
            ];
            $current = $current->modify('+1 day');
        }

        return $items;
    }

    private function getPlaybooksSeries(int $companyId, array $range): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT
                playbook_snapshot_date,
                playbook_new_detected,
                playbook_converted
            FROM playbooks_monitor
            WHERE company_id = :company_id
              AND playbook_snapshot_date >= :start_date
              AND playbook_snapshot_date <= :end_date
            ORDER BY playbook_snapshot_date ASC"
        );
        $stmt->execute([
            'company_id' => $companyId,
            'start_date' => $range['start_date'],
            'end_date' => $range['end_date'],
        ]);

        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
        $mapped = [];
        foreach ($rows as $row) {
            $mapped[$row['playbook_snapshot_date']] = [
                'novos' => (int) ($row['playbook_new_detected'] ?? 0),
                'convertidos' => (int) ($row['playbook_converted'] ?? 0),
            ];
        }

        $items = [];
        $current = new \DateTimeImmutable($range['start_date']);
        $end = new \DateTimeImmutable($range['end_date']);

        while ($current <= $end) {
            $date = $current->format('Y-m-d');
            $items[] = [
                'date' => $date,
                'novos' => (int) (($mapped[$date]['novos'] ?? 0)),
                'convertidos' => (int) (($mapped[$date]['convertidos'] ?? 0)),
            ];
            $current = $current->modify('+1 day');
        }

        return $items;
    }

    private function buildPlaybooksTotals(array $playbooksSeries): array
    {
        $novos = 0;
        $convertidos = 0;

        foreach ($playbooksSeries as $item) {
            $novos += (int) ($item['novos'] ?? 0);
            $convertidos += (int) ($item['convertidos'] ?? 0);
        }

        return [
            'novos' => $novos,
            'convertidos' => $convertidos,
        ];
    }

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

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

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

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

    private function buildKpis(array $kpiSnapshots, array $recentSentimentScores, array $playbooksTotals): array
    {
        $latestKpi = $kpiSnapshots[0] ?? [];
        $previousKpi = $kpiSnapshots[1] ?? $latestKpi;

        $latestAtRisk = (float) ($latestKpi['kpi_lifetime_at_risk'] ?? 0);
        $previousAtRisk = (float) ($previousKpi['kpi_lifetime_at_risk'] ?? $latestAtRisk);
        $currentSales = (float) ($latestKpi['kpi_current_sales'] ?? 0);

        $latestSentiment = isset($recentSentimentScores[0]['evolution_sentiment_score'])
            ? (float) $recentSentimentScores[0]['evolution_sentiment_score']
            : 0.0;
        $previousSentiment = isset($recentSentimentScores[1]['evolution_sentiment_score'])
            ? (float) $recentSentimentScores[1]['evolution_sentiment_score']
            : $latestSentiment;

        $latestScore = $this->normalizeSentimentScore($latestSentiment);
        $previousScore = $this->normalizeSentimentScore($previousSentiment);

        if ($previousScore === 0.0) {
            $evolutionRate = $latestScore > 0 ? 100.0 : 0.0;
        } else {
            $evolutionRate = (($latestScore - $previousScore) / abs($previousScore)) * 100;
        }

        $novos = (int) ($playbooksTotals['novos'] ?? 0);
        $convertidos = (int) ($playbooksTotals['convertidos'] ?? 0);

        return [
            'churnEvitado' => round(max(0, $previousAtRisk - $latestAtRisk), 2),
            'roiUpsell' => round($latestAtRisk > 0 ? ($currentSales / $latestAtRisk) : 0, 1),
            'scoreMedio' => (int) round($latestScore),
            'taxaEvolucao' => round($evolutionRate, 1),
            'conversaoEmocao' => $novos > 0 ? (int) round(($convertidos / $novos) * 100) : 0,
        ];
    }

    private function normalizeSentimentScore(float $score): float
    {
        $normalized = (($score + 1) / 2) * 100;

        return max(0, min(100, $normalized));
    }
}