nettown_backend/models/PersonasModel.php

<?php

namespace Models;

use Libs\Database;

class PersonasModel
{
    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']);
        $personaRows = $this->getPersonaRows($companyId, $filters, $range);
        $stats = $this->getStats($companyId, $filters, $range);

        return [
            'kpis' => $this->buildKpis($personaRows, $stats),
            'stats' => $stats,
            'personas' => array_map(fn (array $row): array => $this->formatPersonaRow($row), $personaRows),
        ];
    }

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

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

        $area = trim((string) ($queryParams['area'] ?? 'any'));
        if ($area === '') {
            $area = 'any';
        }
        $area = mb_strtolower($area);
        if ($area === 'all') {
            $area = 'any';
        }

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

        return [
            'period' => $period,
            'unit' => $unit,
            'area' => $area,
            'area_token' => $this->normalizeToken($area),
            'sentiment' => $sentiment,
        ];
    }

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

        if ($period === 'quarter') {
            $start = $today->modify('-89 days');
        } elseif ($period === '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 getPersonaRows(int $companyId, array $filters, array $range): array
    {
        $sql = "SELECT
                p.persona_id,
                p.persona_name,
                p.persona_type,
                p.persona_description,
                p.persona_details,
                p.persona_risk_level,
                p.persona_churn_risk_pct,
                p.persona_expansion_potential,
                COALESCE((
                    SELECT ba.best_action_idea
                    FROM best_action ba
                    WHERE ba.persona_id = p.persona_id
                      AND ba.best_action_deleted_at = 'infinity'
                      AND lower(ba.best_action_type) IN ('expansao', 'expansão', 'expansion', 'upsell', 'cross_sell')
                    ORDER BY ba.best_action_created_at DESC
                    LIMIT 1
                ), p.persona_expansion_strategy) AS expansao,
                COALESCE((
                    SELECT ba.best_action_idea
                    FROM best_action ba
                    WHERE ba.persona_id = p.persona_id
                      AND ba.best_action_deleted_at = 'infinity'
                      AND lower(ba.best_action_type) IN ('engajamento', 'engagement', 'retencao', 'retenção', 'churn', 'save_churn')
                    ORDER BY ba.best_action_created_at DESC
                    LIMIT 1
                ), p.persona_engagement_strategy) AS engajamento,
                COUNT(DISTINCT CASE WHEN c.conversation_id IS NOT NULL THEN cp.client_id END) AS matched_clients
            FROM persona p
            LEFT JOIN client_persona cp
                ON cp.persona_id = p.persona_id
               AND cp.client_persona_deleted_at = 'infinity'
            LEFT JOIN conversation c
                ON c.client_id = cp.client_id
               AND c.company_id = p.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
            LEFT JOIN conversation_analysis ca
                ON ca.conversation_id = c.conversation_id
               AND ca.conversation_analysis_deleted_at = 'infinity'
            WHERE p.company_id = :company_id
              AND p.persona_deleted_at = 'infinity'";
        $params = [
            'company_id' => $companyId,
            'start_datetime' => $range['start_datetime'],
            'end_exclusive_datetime' => $range['end_exclusive_datetime'],
        ];
        $this->appendPersonaActivityFilters($sql, $params, 'ca', $filters);

        $sql .= "
            GROUP BY
                p.persona_id,
                p.persona_name,
                p.persona_type,
                p.persona_description,
                p.persona_details,
                p.persona_risk_level,
                p.persona_churn_risk_pct,
                p.persona_expansion_potential,
                p.persona_expansion_strategy,
                p.persona_engagement_strategy,
                p.persona_created_at
            ORDER BY matched_clients DESC, p.persona_created_at DESC, p.persona_id ASC";

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

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

    private function getStats(int $companyId, array $filters, array $range): array
    {
        $sql = "SELECT
                COUNT(DISTINCT CASE WHEN c.conversation_id IS NOT NULL THEN cp.client_id END) AS identified,
                COUNT(DISTINCT m.message_id) AS messages,
                COUNT(DISTINCT NULLIF(ca.conversation_analysis_aspect, '')) AS aspects,
                COUNT(DISTINCT NULLIF(ca.conversation_analysis_sub_aspect, '')) AS subaspects
            FROM persona p
            LEFT JOIN client_persona cp
                ON cp.persona_id = p.persona_id
               AND cp.client_persona_deleted_at = 'infinity'
            LEFT JOIN conversation c
                ON c.client_id = cp.client_id
               AND c.company_id = p.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
            LEFT JOIN conversation_analysis ca
                ON ca.conversation_id = c.conversation_id
               AND ca.conversation_analysis_deleted_at = 'infinity'
            LEFT JOIN message m
                ON m.conversation_id = c.conversation_id
               AND m.message_deleted_at = 'infinity'
               AND m.message_sent_at >= :start_datetime
               AND m.message_sent_at < :end_exclusive_datetime
            WHERE p.company_id = :company_id
              AND p.persona_deleted_at = 'infinity'";
        $params = [
            'company_id' => $companyId,
            'start_datetime' => $range['start_datetime'],
            'end_exclusive_datetime' => $range['end_exclusive_datetime'],
        ];
        $this->appendPersonaActivityFilters($sql, $params, 'ca', $filters);

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

        return [
            'identified' => (int) ($row['identified'] ?? 0),
            'messages' => (int) ($row['messages'] ?? 0),
            'aspects' => (int) ($row['aspects'] ?? 0),
            'subaspects' => (int) ($row['subaspects'] ?? 0),
        ];
    }

    private function buildKpis(array $personaRows, array $stats): array
    {
        $activeRows = array_values(array_filter($personaRows, static function (array $row): bool {
            return (int) ($row['matched_clients'] ?? 0) > 0;
        }));
        $sourceRows = $activeRows !== [] ? $activeRows : $personaRows;

        if ($sourceRows === []) {
            return [
                'active' => 0,
                'churn' => 0.0,
                'loss' => 0.0,
                'potentialLabel' => 'Neutro',
            ];
        }

        $avgChurn = array_sum(array_map(static function (array $row): float {
            return (float) ($row['persona_churn_risk_pct'] ?? 0);
        }, $sourceRows)) / count($sourceRows);

        $avgPotentialScore = array_sum(array_map(function (array $row): float {
            return $this->mapPotentialLabelToScore((string) ($row['persona_expansion_potential'] ?? ''));
        }, $sourceRows)) / count($sourceRows);

        $estimatedLoss = round($stats['identified'] * ($avgChurn / 100) * 450, 2);

        return [
            'active' => count($activeRows),
            'churn' => round($avgChurn, 1),
            'loss' => $estimatedLoss,
            'potentialLabel' => $this->mapScoreToPotentialLabel($avgPotentialScore),
        ];
    }

    private function formatPersonaRow(array $row): array
    {
        return [
            'id' => (string) ($row['persona_id'] ?? ''),
            'nome' => $row['persona_name'] ?? '',
            'tipo' => $row['persona_type'] ?? 'O PERFIL',
            'descricao' => $row['persona_description'] ?? '',
            'detalhes' => $row['persona_details'] ?? '',
            'expansao' => $row['expansao'] ?? '',
            'engajamento' => $row['engajamento'] ?? '',
            'risco' => $row['persona_risk_level'] ?? 'Médio',
        ];
    }

    private function appendPersonaActivityFilters(string &$sql, array &$params, string $analysisAlias, array $filters): void
    {
        if ($filters['area'] !== 'any' && $filters['area_token'] !== '') {
            $sql .= ' AND ('
                . $this->getNormalizedTextSql("{$analysisAlias}.conversation_analysis_aspect") . ' = :area_token'
                . ' OR '
                . $this->getNormalizedTextSql("{$analysisAlias}.conversation_analysis_sub_aspect") . ' = :area_token'
                . ')';
            $params['area_token'] = $filters['area_token'];
        }

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

    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 "(
            {$analysisAlias}.conversation_id IS NOT NULL
            AND 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 getNormalizedTextSql(string $column): string
    {
        return "regexp_replace(translate(lower(COALESCE({$column}, '')), 'áàâãäéèêëíìîïóòôõöúùûüçñ', 'aaaaaeeeeiiiiooooouuuucn'), '[^a-z0-9]+', '', 'g')";
    }

    private function normalizeToken(string $value): string
    {
        $normalized = trim($value);
        if ($normalized === '' || $normalized === 'any') {
            return '';
        }

        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) ?? '';

        return $normalized;
    }

    private function mapPotentialLabelToScore(string $label): float
    {
        $normalized = $this->normalizeToken($label);

        if ($normalized === 'muitoalto') {
            return 1.5;
        }

        if ($normalized === 'alto') {
            return 1.0;
        }

        if ($normalized === 'medio') {
            return 0.35;
        }

        if ($normalized === 'baixo') {
            return -1.0;
        }

        return 0.0;
    }

    private function mapScoreToPotentialLabel(float $score): string
    {
        if ($score >= 1.2) {
            return 'Muito alto';
        }

        if ($score >= 0.5) {
            return 'Alto';
        }

        if ($score <= -0.35) {
            return 'Baixo';
        }

        return 'Neutro';
    }
}