<?php

namespace App\Repositories;

use App\Core\Cache;

/**
 * Repositório — Comparativo de Vendas Premium.
 *
 * REGRA DE PERFORMANCE: NUNCA usar EXTRACT(YEAR FROM col) no WHERE —
 * força full scan em 1,3 M linhas. Sempre usar intervalo de datas
 * DATA_EMISSAO >= 'YYYY-01-01' AND DATA_EMISSAO < 'YYYY+1-01-01'
 */
class ComparativoRepository
{
    private int     $codEmpresa    = 0;
    private ?string $tiposVendaIn  = null;
    private ?string $tiposCompraIn = null;

    public function __construct(private \PDO $pdo)
    {
        try {
            $this->codEmpresa = \App\Core\Lojas::codEmpresaAtiva();
        } catch (\Throwable) {}
    }

    // ── helpers ───────────────────────────────────────────────────────────────

    private function fe(string $alias = ''): string
    {
        if (!$this->codEmpresa) return '';
        $col = $alias !== '' ? "{$alias}.COD_EMPRESA" : 'COD_EMPRESA';
        return "AND {$col} = {$this->codEmpresa}";
    }

    private function faixaAnos(int $ano1, int $ano2, string $alias = ''): string
    {
        $col  = $alias !== '' ? "{$alias}.DATA_EMISSAO" : 'DATA_EMISSAO';
        $yMin = min($ano1, $ano2);
        $yMax = max($ano1, $ano2);
        return "{$col} >= '{$yMin}-01-01' AND {$col} < DATEADD(DAY,1,CAST('{$yMax}-12-31' AS DATE))";
    }

    /** TTL: 1800s se nenhum dos anos é o ano atual, 120s se ano atual incluído */
    private function ttlAnos(int $ano1, int $ano2): int
    {
        $anoAtual = (int)date('Y');
        return ($ano1 === $anoAtual || $ano2 === $anoAtual) ? 120 : 1800;
    }

    private function tv(): string
    {
        if ($this->tiposVendaIn !== null) return $this->tiposVendaIn;
        // Cache persistente entre requests — TIPO_OPERACAO quase nunca muda
        // v3: filtra POR EMPRESA (PK de TIPO_OPERACAO é cod+empresa). Cache key inclui empresa.
        $emp = (int)($this->codEmpresa ?: 0);
        $empWhere = $emp > 0 ? " AND COD_EMPRESA = {$emp}" : '';
        $cached = Cache::lembrar('comp_tipos_venda_v3_' . $this->codEmpresa, 3600, function () use ($empWhere) {
            $sqlBase = "
                SELECT DISTINCT COD_TIPO_OPERACAO FROM TIPO_OPERACAO
                WHERE TIPO_NATUREZA = 'V'{$empWhere}
                  AND COD_TIPO_OPERACAO NOT IN (
                      SELECT COD_TIPO_OPERACAO FROM TIPO_OPERACAO
                      WHERE (DEVOLUCAO = 'S' OR ETRANSF = 'S' OR MOV_INTERNA = 'S' %s){$empWhere}
                  )";
            foreach (["OR GERA_FINANCEIRO = 'N'", ''] as $extra) {
                try {
                    $rows = $this->pdo->query(sprintf($sqlBase, $extra))->fetchAll(\PDO::FETCH_COLUMN);
                    if (!empty($rows)) return implode(',', array_map('intval', $rows));
                } catch (\Throwable) { /* tenta sem GERA_FINANCEIRO */ }
            }
            return '115,231,297';
        });
        // === OVERRIDES por cliente (config/database.php) ===
        $cfg = $this->configCliente();
        $extras  = (array)($cfg['tipos_venda_extras']  ?? []);
        $excluir = (array)($cfg['tipos_venda_excluir'] ?? []);
        if ($extras || $excluir) {
            $ops = $cached !== '' ? array_map('intval', explode(',', $cached)) : [];
            foreach ($extras as $e)  if (!in_array((int)$e, $ops, true)) $ops[] = (int)$e;
            $ops = array_values(array_diff($ops, array_map('intval', $excluir)));
            $cached = $ops ? implode(',', $ops) : '';
        }
        $this->tiposVendaIn = $cached;
        return $this->tiposVendaIn;
    }

    /** Lê config/database.php uma vez por request. */
    private function configCliente(): array
    {
        static $cache = null;
        if ($cache !== null) return $cache;
        $path = dirname(__DIR__, 2) . '/config/database.php';
        $cache = file_exists($path) ? (array)(@include $path) : [];
        return $cache;
    }

    private function tc(): string
    {
        if ($this->tiposCompraIn !== null) return $this->tiposCompraIn;
        $cached = Cache::lembrar('comp_tipos_compra_' . $this->codEmpresa, 3600, function () {
            try {
                $rows = $this->pdo->query("
                    SELECT DISTINCT COD_TIPO_OPERACAO FROM TIPO_OPERACAO
                    WHERE TIPO_NATUREZA = 'C'
                      AND COD_TIPO_OPERACAO NOT IN (
                          SELECT COD_TIPO_OPERACAO FROM TIPO_OPERACAO
                          WHERE DEVOLUCAO = 'S' OR ETRANSF = 'S' OR MOV_INTERNA = 'S'
                      )
                ")->fetchAll(\PDO::FETCH_COLUMN);
                return !empty($rows) ? implode(',', array_map('intval', $rows)) : '168,235,302';
            } catch (\Throwable) { return '168,235,302'; }
        });
        $this->tiposCompraIn = $cached;
        return $this->tiposCompraIn;
    }

    // ── Métodos existentes (anuais) ────────────────────────────────────────────

    public function anosDisponiveis(): array
    {
        return Cache::lembrar('comp_anos_' . $this->codEmpresa, 1800, function () {
            $fe = $this->fe();
            $tv = $this->tv();
            $row = $this->pdo->query("
                SELECT
                    EXTRACT(YEAR FROM MIN(DATA_EMISSAO)) MIN_ANO,
                    EXTRACT(YEAR FROM MAX(DATA_EMISSAO)) MAX_ANO
                FROM NOTAS_CAB
                WHERE DATA_EMISSAO >= DATEADD(YEAR, -10, CURRENT_DATE)
                  AND DATA_CANC IS NULL
                  AND COD_TIPO_OPERACAO IN ({$tv})
                  {$fe}
            ")->fetch(\PDO::FETCH_ASSOC);

            if (!$row || !$row['MAX_ANO']) return [];
            $min = max((int)$row['MIN_ANO'], (int)date('Y') - 10);
            $max = (int)$row['MAX_ANO'];
            return array_reverse(range($min, $max));
        });
    }

    public function faturamentoMensal(int $ano1, int $ano2): array
    {
        $key = "comp_fat_mensal_{$ano1}_{$ano2}_{$this->codEmpresa}";
        return Cache::lembrar($key, $this->ttlAnos($ano1, $ano2), function () use ($ano1, $ano2) {
            $fe   = $this->fe();
            $tv   = $this->tv();
            $faix = $this->faixaAnos($ano1, $ano2);
            $st = $this->pdo->query("
                SELECT EXTRACT(MONTH FROM DATA_EMISSAO) MES,
                       EXTRACT(YEAR  FROM DATA_EMISSAO) ANO,
                       SUM(TOTAL_NOTA) FAT
                FROM NOTAS_CAB
                WHERE {$faix}
                  AND DATA_CANC IS NULL
                  AND COD_TIPO_OPERACAO IN ({$tv})
                  {$fe}
                GROUP BY EXTRACT(MONTH FROM DATA_EMISSAO), EXTRACT(YEAR FROM DATA_EMISSAO)
                ORDER BY ANO, MES
            ");
            return $this->pivotar($st->fetchAll(\PDO::FETCH_ASSOC), $ano1, $ano2, 'FAT');
        });
    }

    public function comprasMensal(int $ano1, int $ano2): array
    {
        $key = "comp_comp_mensal_{$ano1}_{$ano2}_{$this->codEmpresa}";
        return Cache::lembrar($key, $this->ttlAnos($ano1, $ano2), function () use ($ano1, $ano2) {
            $fe   = $this->fe();
            $tc   = $this->tc();
            $faix = $this->faixaAnos($ano1, $ano2);
            $st = $this->pdo->query("
                SELECT EXTRACT(MONTH FROM DATA_EMISSAO) MES,
                       EXTRACT(YEAR  FROM DATA_EMISSAO) ANO,
                       SUM(TOTAL_NOTA) FAT
                FROM NOTAS_CAB
                WHERE {$faix}
                  AND DATA_CANC IS NULL
                  AND COD_TIPO_OPERACAO IN ({$tc})
                  {$fe}
                GROUP BY EXTRACT(MONTH FROM DATA_EMISSAO), EXTRACT(YEAR FROM DATA_EMISSAO)
                ORDER BY ANO, MES
            ");
            return $this->pivotar($st->fetchAll(\PDO::FETCH_ASSOC), $ano1, $ano2, 'FAT');
        });
    }

    public function mesasMensal(int $ano1, int $ano2): array
    {
        $key = "comp_mesas_mensal_{$ano1}_{$ano2}_{$this->codEmpresa}";
        return Cache::lembrar($key, $this->ttlAnos($ano1, $ano2), function () use ($ano1, $ano2) {
            try {
                $yMin = min($ano1, $ano2);
                $yMax = max($ano1, $ano2);
                $st = $this->pdo->query("
                    SELECT EXTRACT(MONTH FROM DATA_ABERTURA) MES,
                           EXTRACT(YEAR  FROM DATA_ABERTURA) ANO,
                           SUM(COALESCE(TOTAL_AVISTA,0)+COALESCE(TOTA_APRAZO,0)
                              +COALESCE(TOTAL_CHEQUE,0)+COALESCE(TOTAL_CARTAO,0)) FAT
                    FROM ABERT_MESA
                    WHERE DATA_ABERTURA >= '{$yMin}-01-01'
                      AND DATA_ABERTURA < DATEADD(DAY,1,CAST('{$yMax}-12-31' AS DATE))
                      AND STATUS = 'F'
                    GROUP BY EXTRACT(MONTH FROM DATA_ABERTURA), EXTRACT(YEAR FROM DATA_ABERTURA)
                    ORDER BY ANO, MES
                ");
                return $this->pivotar($st->fetchAll(\PDO::FETCH_ASSOC), $ano1, $ano2, 'FAT');
            } catch (\Throwable) {
                return $this->pivotar([], $ano1, $ano2, 'FAT');
            }
        });
    }

    public function resumoAnual(int $ano1, int $ano2): array
    {
        $key = "comp_resumo_anual_{$ano1}_{$ano2}_{$this->codEmpresa}";
        return Cache::lembrar($key, $this->ttlAnos($ano1, $ano2), function () use ($ano1, $ano2) {
            $fe   = $this->fe();
            $tv   = $this->tv();
            $faix = $this->faixaAnos($ano1, $ano2);
            $rows = $this->pdo->query("
                SELECT EXTRACT(YEAR FROM DATA_EMISSAO)        ANO,
                       COUNT(*)                               QTD_NOTAS,
                       SUM(TOTAL_NOTA)                        FAT,
                       SUM(TOTAL_NOTA) / NULLIF(COUNT(*), 0)  TICKET
                FROM NOTAS_CAB
                WHERE {$faix}
                  AND DATA_CANC IS NULL
                  AND COD_TIPO_OPERACAO IN ({$tv})
                  {$fe}
                GROUP BY EXTRACT(YEAR FROM DATA_EMISSAO)
                ORDER BY ANO
            ")->fetchAll(\PDO::FETCH_ASSOC);
            $out = [$ano1 => [], $ano2 => []];
            foreach ($rows as $r) { $out[(int)$r['ANO']] = $r; }
            return $out;
        });
    }

    public function garcomAnual(int $ano1, int $ano2): array
    {
        $key = "comp_garcom_{$ano1}_{$ano2}_{$this->codEmpresa}";
        return Cache::lembrar($key, $this->ttlAnos($ano1, $ano2), function () use ($ano1, $ano2) {
            try {
                $yMin = min($ano1, $ano2);
                $yMax = max($ano1, $ano2);
                return $this->pdo->query("
                    SELECT p.NOME GARCOM,
                        SUM(CASE WHEN EXTRACT(YEAR FROM am.DATA_ABERTURA)={$ano1}
                            THEN COALESCE(am.TOTAL_AVISTA,0)+COALESCE(am.TOTA_APRAZO,0)
                                +COALESCE(am.TOTAL_CHEQUE,0)+COALESCE(am.TOTAL_CARTAO,0)
                            ELSE 0 END) FAT1,
                        SUM(CASE WHEN EXTRACT(YEAR FROM am.DATA_ABERTURA)={$ano2}
                            THEN COALESCE(am.TOTAL_AVISTA,0)+COALESCE(am.TOTA_APRAZO,0)
                                +COALESCE(am.TOTAL_CHEQUE,0)+COALESCE(am.TOTAL_CARTAO,0)
                            ELSE 0 END) FAT2
                    FROM ABERT_MESA am
                    JOIN GARCOM  g ON am.COD_GARCOM = g.COD_GARCOM
                    JOIN PESSOAS p ON g.COD_PESSOA  = p.COD_PESSOA
                    WHERE am.DATA_ABERTURA >= '{$yMin}-01-01'
                      AND am.DATA_ABERTURA < DATEADD(DAY,1,CAST('{$yMax}-12-31' AS DATE))
                      AND am.STATUS = 'F'
                    GROUP BY p.NOME
                    ORDER BY (FAT1+FAT2) DESC
                ")->fetchAll(\PDO::FETCH_ASSOC);
            } catch (\Throwable) { return []; }
        });
    }

    public function topProdutos(int $ano1, int $ano2, int $top = 15): array
    {
        $key = "comp_top_prod_{$ano1}_{$ano2}_{$top}_{$this->codEmpresa}";
        return Cache::lembrar($key, $this->ttlAnos($ano1, $ano2), function () use ($ano1, $ano2, $top) {
            $tv   = $this->tv();
            $fe   = $this->codEmpresa ? "AND nc.COD_EMPRESA = {$this->codEmpresa}" : '';
            $faix = $this->faixaAnos($ano1, $ano2, 'nc');
            $colunas = [
                ['ni.DESCRICAO',      'ni.PRECO_UNIT'],
                ['ni.DESCRICAO',      'ni.VLR_UNITARIO'],
                ['ni.DESCRICAO',      'ni.VALOR_UNITARIO'],
                ['ni.DESCRICAO_ITEM', 'ni.PRECO_UNIT'],
                ['ni.DESCRICAO_ITEM', 'ni.VLR_UNITARIO'],
            ];
            foreach ($colunas as [$colDesc, $colPreco]) {
                try {
                    return $this->pdo->query("
                        SELECT FIRST {$top}
                            {$colDesc} DESCRICAO,
                            SUM(CASE WHEN EXTRACT(YEAR FROM nc.DATA_EMISSAO)={$ano1}
                                     THEN ni.QTD * {$colPreco} ELSE 0 END) FAT1,
                            SUM(CASE WHEN EXTRACT(YEAR FROM nc.DATA_EMISSAO)={$ano2}
                                     THEN ni.QTD * {$colPreco} ELSE 0 END) FAT2
                        FROM NOTAS_ITENS ni
                        JOIN NOTAS_CAB nc ON nc.NR_NOTA = ni.NR_NOTA AND nc.COD_EMPRESA = ni.COD_EMPRESA
                        WHERE {$faix}
                          AND nc.DATA_CANC IS NULL
                          AND nc.COD_TIPO_OPERACAO IN ({$tv})
                          {$fe}
                        GROUP BY {$colDesc}
                        ORDER BY (
                            SUM(CASE WHEN EXTRACT(YEAR FROM nc.DATA_EMISSAO)={$ano1} THEN ni.QTD * {$colPreco} ELSE 0 END)
                          + SUM(CASE WHEN EXTRACT(YEAR FROM nc.DATA_EMISSAO)={$ano2} THEN ni.QTD * {$colPreco} ELSE 0 END)
                        ) DESC
                    ")->fetchAll(\PDO::FETCH_ASSOC);
                } catch (\Throwable) {}
            }
            return [];
        });
    }

    // ── COMPARATIVOS DE PERÍODO ────────────────────────────────────────────────

    /** Hoje vs Ontem — KPIs instantâneos */
    public function hojeVsOntem(): array
    {
        $today = date('Y-m-d');
        $key   = "comp_hoje_ontem_{$today}_{$this->codEmpresa}";
        return Cache::lembrar($key, 60, function () {
            $fe = $this->fe();
            $tv = $this->tv();
            $row = $this->pdo->query("
                SELECT
                    SUM(CASE WHEN DATA_EMISSAO >= CAST(CURRENT_DATE AS DATE)
                             THEN TOTAL_NOTA ELSE 0 END)                             FAT_HOJE,
                    COUNT(CASE WHEN DATA_EMISSAO >= CAST(CURRENT_DATE AS DATE)
                                AND TOTAL_NOTA > 0 THEN 1 END)                       QTD_HOJE,
                    SUM(CASE WHEN DATA_EMISSAO >= DATEADD(DAY,-1,CAST(CURRENT_DATE AS DATE))
                              AND DATA_EMISSAO <  CAST(CURRENT_DATE AS DATE)
                             THEN TOTAL_NOTA ELSE 0 END)                             FAT_ONTEM,
                    COUNT(CASE WHEN DATA_EMISSAO >= DATEADD(DAY,-1,CAST(CURRENT_DATE AS DATE))
                                AND DATA_EMISSAO <  CAST(CURRENT_DATE AS DATE)
                               AND TOTAL_NOTA > 0 THEN 1 END)                        QTD_ONTEM
                FROM NOTAS_CAB
                WHERE DATA_EMISSAO >= DATEADD(DAY,-1,CAST(CURRENT_DATE AS DATE))
                  AND DATA_CANC IS NULL
                  AND COD_TIPO_OPERACAO IN ({$tv})
                  {$fe}
            ")->fetch(\PDO::FETCH_ASSOC);

            $fatH = (float)($row['FAT_HOJE']  ?? 0);
            $fatO = (float)($row['FAT_ONTEM'] ?? 0);
            $qtdH = (int)  ($row['QTD_HOJE']  ?? 0);
            $qtdO = (int)  ($row['QTD_ONTEM'] ?? 0);
            $tkH  = $qtdH > 0 ? $fatH / $qtdH : 0;
            $tkO  = $qtdO > 0 ? $fatO / $qtdO : 0;

            return [
                'hoje_data'    => date('d/m/Y'),
                'ontem_data'   => date('d/m/Y', strtotime('yesterday')),
                'fat_hoje'     => $fatH,
                'fat_ontem'    => $fatO,
                'qtd_hoje'     => $qtdH,
                'qtd_ontem'    => $qtdO,
                'ticket_hoje'  => $tkH,
                'ticket_ontem' => $tkO,
                'var_fat'      => $fatO > 0 ? ($fatH - $fatO) / $fatO * 100 : null,
                'var_qtd'      => $qtdO > 0 ? ($qtdH - $qtdO) / $qtdO * 100 : null,
                'var_ticket'   => $tkO  > 0 ? ($tkH  - $tkO)  / $tkO  * 100 : null,
            ];
        });
    }

    /** Semana atual vs semana anterior — dia a dia */
    public function semanaComparativo(): array
    {
        $key = "comp_semana_" . date('Y-W') . "_{$this->codEmpresa}";
        return Cache::lembrar($key, 120, function () {
            $fe = $this->fe();
            $tv = $this->tv();

            $hoje     = new \DateTime('today');
            $dow      = (int)$hoje->format('N');
            $iniSem   = (clone $hoje)->modify('-' . ($dow - 1) . ' days');
            $fimSem   = (clone $iniSem)->modify('+6 days');
            $iniAnt   = (clone $iniSem)->modify('-7 days');
            $fimAnt   = (clone $iniSem)->modify('-1 day');

            $ini1 = $iniAnt->format('Y-m-d');
            $fim2 = $fimSem->format('Y-m-d');

            $rows = $this->pdo->query("
                SELECT DATA_EMISSAO, SUM(TOTAL_NOTA) FAT, COUNT(DISTINCT NR_NOTA) QTD
                FROM NOTAS_CAB
                WHERE DATA_EMISSAO >= '{$ini1}'
                  AND DATA_EMISSAO < DATEADD(DAY,1,CAST('{$fim2}' AS DATE))
                  AND DATA_CANC IS NULL
                  AND COD_TIPO_OPERACAO IN ({$tv})
                  {$fe}
                GROUP BY DATA_EMISSAO
                ORDER BY DATA_EMISSAO
            ")->fetchAll(\PDO::FETCH_ASSOC);

            $byDate = [];
            foreach ($rows as $r) {
                $d = substr((string)$r['DATA_EMISSAO'], 0, 10);
                $byDate[$d] = ['fat' => (float)$r['FAT'], 'qtd' => (int)$r['QTD']];
            }

            $dias  = ['Seg','Ter','Qua','Qui','Sex','Sáb','Dom'];
            $atual = [];
            $ant   = [];
            for ($i = 0; $i < 7; $i++) {
                $dA = (clone $iniSem)->modify("+{$i} days")->format('Y-m-d');
                $dB = (clone $iniAnt)->modify("+{$i} days")->format('Y-m-d');
                $atual[] = $byDate[$dA]['fat'] ?? 0;
                $ant[]   = $byDate[$dB]['fat'] ?? 0;
            }

            $totAtual = array_sum($atual);
            $totAnt   = array_sum($ant);
            return [
                'dias'        => $dias,
                'semana_atual'=> $atual,
                'semana_ant'  => $ant,
                'label_atual' => $iniSem->format('d/m') . ' – ' . min($hoje, $fimSem)->format('d/m'),
                'label_ant'   => $iniAnt->format('d/m') . ' – ' . $fimAnt->format('d/m'),
                'total_atual' => $totAtual,
                'total_ant'   => $totAnt,
                'var_pct'     => $totAnt > 0 ? ($totAtual - $totAnt) / $totAnt * 100 : null,
            ];
        });
    }

    /** Mês atual vs mês anterior — dia a dia (mesmo número de dias) */
    public function mesComparativo(): array
    {
        $key = "comp_mes_" . date('Y-m') . "_" . date('d') . "_{$this->codEmpresa}";
        return Cache::lembrar($key, 90, function () {
            $fe = $this->fe();
            $tv = $this->tv();

            $hoje    = new \DateTime('today');
            $anoA    = (int)$hoje->format('Y');
            $mesA    = (int)$hoje->format('m');
            $diaHoje = (int)$hoje->format('j');

            $iniMes  = "{$anoA}-" . str_pad($mesA, 2, '0', STR_PAD_LEFT) . "-01";
            $fimMes  = $hoje->format('Y-m-d');

            $dtAnt   = (clone $hoje)->modify('first day of last month');
            $anoAnt  = (int)$dtAnt->format('Y');
            $mesAnt  = (int)$dtAnt->format('m');
            $diasMesAnt = (int)(new \DateTime('last day of ' . $dtAnt->format('Y-m')))->format('j');
            $diaFimAnt  = min($diaHoje, $diasMesAnt);
            $iniAnt  = "{$anoAnt}-" . str_pad($mesAnt, 2, '0', STR_PAD_LEFT) . "-01";
            $fimAnt  = "{$anoAnt}-" . str_pad($mesAnt, 2, '0', STR_PAD_LEFT) . "-" . str_pad($diaFimAnt, 2, '0', STR_PAD_LEFT);

            $rows = $this->pdo->query("
                SELECT
                    EXTRACT(DAY   FROM DATA_EMISSAO) DIA,
                    EXTRACT(MONTH FROM DATA_EMISSAO) MES,
                    EXTRACT(YEAR  FROM DATA_EMISSAO) ANO,
                    SUM(TOTAL_NOTA) FAT
                FROM NOTAS_CAB
                WHERE (
                    (DATA_EMISSAO >= '{$iniAnt}' AND DATA_EMISSAO < DATEADD(DAY,1,CAST('{$fimAnt}' AS DATE)))
                    OR
                    (DATA_EMISSAO >= '{$iniMes}' AND DATA_EMISSAO < DATEADD(DAY,1,CAST('{$fimMes}' AS DATE)))
                )
                  AND DATA_CANC IS NULL
                  AND COD_TIPO_OPERACAO IN ({$tv})
                  {$fe}
                GROUP BY EXTRACT(DAY FROM DATA_EMISSAO), EXTRACT(MONTH FROM DATA_EMISSAO), EXTRACT(YEAR FROM DATA_EMISSAO)
                ORDER BY ANO, MES, DIA
            ")->fetchAll(\PDO::FETCH_ASSOC);

            $atual = array_fill(1, 31, 0.0);
            $ant   = array_fill(1, 31, 0.0);
            foreach ($rows as $r) {
                $d = (int)$r['DIA']; $a = (int)$r['ANO']; $m = (int)$r['MES']; $f = (float)$r['FAT'];
                if ($a === $anoA  && $m === $mesA)   $atual[$d] = $f;
                if ($a === $anoAnt && $m === $mesAnt) $ant[$d]  = $f;
            }

            $dias       = range(1, $diaHoje);
            $serAtual   = array_values(array_slice($atual, 1, $diaHoje));
            $serAnt     = array_values(array_slice($ant,   1, $diaHoje));
            $totAtual   = array_sum($serAtual);
            $totAnt     = array_sum($serAnt);

            return [
                'dias'         => $dias,
                'mes_atual'    => $serAtual,
                'mes_anterior' => $serAnt,
                'total_atual'  => $totAtual,
                'total_ant'    => $totAnt,
                'var_pct'      => $totAnt > 0 ? ($totAtual - $totAnt) / $totAnt * 100 : null,
                'label_atual'  => $hoje->format('M/Y'),
                'label_ant'    => $dtAnt->format('M/Y'),
            ];
        });
    }

    /** Heatmap hora × dia da semana — retorna array de [hora, diasemana, total] */
    public function heatmapHoras(string $ini, string $fim): array
    {
        $key = "comp_heatmap_{$ini}_{$fim}_{$this->codEmpresa}";
        $ttl = ($fim >= date('Y-m-d')) ? 120 : 1800;
        return Cache::lembrar($key, $ttl, function () use ($ini, $fim) {
            $fe = $this->fe();
            $tv = $this->tv();

            $sqls = [
                "SELECT
                    EXTRACT(HOUR    FROM DATA_EMISSAO) HORA,
                    EXTRACT(WEEKDAY FROM DATA_EMISSAO) DIA_SEMANA,
                    SUM(TOTAL_NOTA)                    TOTAL
                 FROM NOTAS_CAB
                 WHERE DATA_EMISSAO >= '{$ini}'
                   AND DATA_EMISSAO < DATEADD(DAY,1,CAST('{$fim}' AS DATE))
                   AND DATA_CANC IS NULL
                   AND COD_TIPO_OPERACAO IN ({$tv})
                   {$fe}
                 GROUP BY EXTRACT(HOUR FROM DATA_EMISSAO), EXTRACT(WEEKDAY FROM DATA_EMISSAO)
                 ORDER BY HORA, DIA_SEMANA",

                "SELECT
                    0                                  HORA,
                    EXTRACT(WEEKDAY FROM DATA_EMISSAO) DIA_SEMANA,
                    SUM(TOTAL_NOTA)                    TOTAL
                 FROM NOTAS_CAB
                 WHERE DATA_EMISSAO >= '{$ini}'
                   AND DATA_EMISSAO < DATEADD(DAY,1,CAST('{$fim}' AS DATE))
                   AND DATA_CANC IS NULL
                   AND COD_TIPO_OPERACAO IN ({$tv})
                   {$fe}
                 GROUP BY EXTRACT(WEEKDAY FROM DATA_EMISSAO)
                 ORDER BY DIA_SEMANA",
            ];

            foreach ($sqls as $sql) {
                try {
                    $rows = $this->pdo->query($sql)->fetchAll(\PDO::FETCH_ASSOC);
                    if (!empty($rows)) {
                        return array_map(fn($r) => [
                            'HORA'       => (int)$r['HORA'],
                            'DIA_SEMANA' => (int)$r['DIA_SEMANA'],
                            'TOTAL'      => round((float)$r['TOTAL'], 2),
                        ], $rows);
                    }
                } catch (\Throwable) {}
            }
            return [];
        });
    }

    /** Ranking de vendedores — período atual vs anterior */
    public function vendedoresComparativo(string $ini, string $fim, string $iniAnt, string $fimAnt): array
    {
        $key = "comp_vendedores_{$ini}_{$fim}_{$this->codEmpresa}";
        $ttl = ($fim >= date('Y-m-d')) ? 120 : 1800;
        return Cache::lembrar($key, $ttl, function () use ($ini, $fim, $iniAnt, $fimAnt) {
            $fe = $this->fe();
            $tv = $this->tv();

            $sqls = [
                "SELECT FIRST 12
                    TRIM(CAST(COD_VENDEDOR AS VARCHAR(80))) NOME,
                    SUM(CASE WHEN DATA_EMISSAO >= '{$ini}'    AND DATA_EMISSAO < DATEADD(DAY,1,CAST('{$fim}' AS DATE))    THEN TOTAL_NOTA ELSE 0 END) FAT_ATU,
                    SUM(CASE WHEN DATA_EMISSAO >= '{$iniAnt}' AND DATA_EMISSAO < DATEADD(DAY,1,CAST('{$fimAnt}' AS DATE)) THEN TOTAL_NOTA ELSE 0 END) FAT_ANT,
                    COUNT(CASE WHEN DATA_EMISSAO >= '{$ini}'  AND DATA_EMISSAO < DATEADD(DAY,1,CAST('{$fim}' AS DATE)) AND TOTAL_NOTA > 0 THEN 1 END) QTD
                FROM NOTAS_CAB
                WHERE DATA_EMISSAO >= '{$iniAnt}'
                  AND DATA_EMISSAO < DATEADD(DAY,1,CAST('{$fim}' AS DATE))
                  AND DATA_CANC IS NULL AND COD_TIPO_OPERACAO IN ({$tv}) {$fe}
                  AND COD_VENDEDOR IS NOT NULL AND COD_VENDEDOR > 0
                GROUP BY COD_VENDEDOR
                HAVING SUM(CASE WHEN DATA_EMISSAO >= '{$ini}' THEN TOTAL_NOTA ELSE 0 END) > 0
                ORDER BY FAT_ATU DESC",

                "SELECT FIRST 12
                    TRIM(NOME_VENDEDOR) NOME,
                    SUM(CASE WHEN DATA_EMISSAO >= '{$ini}'    AND DATA_EMISSAO < DATEADD(DAY,1,CAST('{$fim}' AS DATE))    THEN TOTAL_NOTA ELSE 0 END) FAT_ATU,
                    SUM(CASE WHEN DATA_EMISSAO >= '{$iniAnt}' AND DATA_EMISSAO < DATEADD(DAY,1,CAST('{$fimAnt}' AS DATE)) THEN TOTAL_NOTA ELSE 0 END) FAT_ANT,
                    COUNT(CASE WHEN DATA_EMISSAO >= '{$ini}'  AND DATA_EMISSAO < DATEADD(DAY,1,CAST('{$fim}' AS DATE)) AND TOTAL_NOTA > 0 THEN 1 END) QTD
                FROM NOTAS_CAB
                WHERE DATA_EMISSAO >= '{$iniAnt}'
                  AND DATA_EMISSAO < DATEADD(DAY,1,CAST('{$fim}' AS DATE))
                  AND DATA_CANC IS NULL AND COD_TIPO_OPERACAO IN ({$tv}) {$fe}
                  AND NOME_VENDEDOR IS NOT NULL AND TRIM(NOME_VENDEDOR) <> ''
                GROUP BY NOME_VENDEDOR
                HAVING SUM(CASE WHEN DATA_EMISSAO >= '{$ini}' THEN TOTAL_NOTA ELSE 0 END) > 0
                ORDER BY FAT_ATU DESC",
            ];

            foreach ($sqls as $sql) {
                try {
                    $rows = $this->pdo->query($sql)->fetchAll(\PDO::FETCH_ASSOC);
                    if (!empty($rows)) {
                        $max = max(array_column($rows, 'FAT_ATU')) ?: 1;
                        foreach ($rows as &$r) {
                            $ant = (float)$r['FAT_ANT'];
                            $atu = (float)$r['FAT_ATU'];
                            $r['var'] = $ant > 0 ? ($atu - $ant) / $ant * 100 : null;
                            $r['pct_max'] = $max > 0 ? $atu / $max * 100 : 0;
                        }
                        return $rows;
                    }
                } catch (\Throwable) {}
            }
            return [];
        });
    }

    /** Produtos em alta e em queda */
    public function produtosTendencia(string $ini, string $fim, string $iniAnt, string $fimAnt, int $top = 8): array
    {
        $key = "comp_tendencias_{$ini}_{$fim}_{$top}_{$this->codEmpresa}";
        $ttl = ($fim >= date('Y-m-d')) ? 180 : 1800;
        return Cache::lembrar($key, $ttl, function () use ($ini, $fim, $iniAnt, $fimAnt, $top) {
            $tv = $this->tv();
            $fe = $this->codEmpresa ? "AND nc.COD_EMPRESA = {$this->codEmpresa}" : '';

            $colunas = [
                ['ni.DESCRICAO',      'ni.PRECO_UNIT'],
                ['ni.DESCRICAO',      'ni.VLR_UNITARIO'],
                ['ni.DESCRICAO_ITEM', 'ni.PRECO_UNIT'],
            ];

            foreach ($colunas as [$colDesc, $colPreco]) {
                try {
                    $rows = $this->pdo->query("
                        SELECT FIRST " . ($top * 4) . "
                            {$colDesc} DESCRICAO,
                            SUM(CASE WHEN nc.DATA_EMISSAO >= '{$ini}'    AND nc.DATA_EMISSAO < DATEADD(DAY,1,CAST('{$fim}' AS DATE))    THEN ni.QTD * {$colPreco} ELSE 0 END) FAT_ATU,
                            SUM(CASE WHEN nc.DATA_EMISSAO >= '{$iniAnt}' AND nc.DATA_EMISSAO < DATEADD(DAY,1,CAST('{$fimAnt}' AS DATE)) THEN ni.QTD * {$colPreco} ELSE 0 END) FAT_ANT
                        FROM NOTAS_ITENS ni
                        JOIN NOTAS_CAB nc ON nc.NR_NOTA = ni.NR_NOTA AND nc.COD_EMPRESA = ni.COD_EMPRESA
                        WHERE nc.DATA_EMISSAO >= '{$iniAnt}'
                          AND nc.DATA_EMISSAO < DATEADD(DAY,1,CAST('{$fim}' AS DATE))
                          AND nc.DATA_CANC IS NULL
                          AND nc.COD_TIPO_OPERACAO IN ({$tv})
                          {$fe}
                        GROUP BY {$colDesc}
                        HAVING SUM(ni.QTD * {$colPreco}) > 0
                    ")->fetchAll(\PDO::FETCH_ASSOC);

                    if (!empty($rows)) {
                        $alta = []; $queda = [];
                        foreach ($rows as $r) {
                            $ant = (float)$r['FAT_ANT']; $atu = (float)$r['FAT_ATU'];
                            if ($ant > 0 && $atu > 0) {
                                $var = ($atu - $ant) / $ant * 100;
                                $r['var'] = $var;
                                if ($var > 0) $alta[]  = $r;
                                else          $queda[] = $r;
                            }
                        }
                        usort($alta,  fn($a,$b) => $b['var'] <=> $a['var']);
                        usort($queda, fn($a,$b) => $a['var'] <=> $b['var']);
                        return ['alta' => array_slice($alta, 0, $top), 'queda' => array_slice($queda, 0, $top)];
                    }
                } catch (\Throwable) {}
            }
            return ['alta' => [], 'queda' => []];
        });
    }

    /** Insights automáticos baseados em dados reais */
    public function insightsAutomaticos(): array
    {
        // Reutiliza cache de hojeVsOntem e semanaComparativo — sem queries extras
        $key = "comp_insights_" . date('Y-m-d_H') . "_{$this->codEmpresa}";
        return Cache::lembrar($key, 60, function () {
            $insights = [];
            try {
                $hvo = $this->hojeVsOntem();
                if ($hvo['var_fat'] !== null) {
                    $v = round(abs($hvo['var_fat']), 1);
                    if ($hvo['var_fat'] >= 15)
                        $insights[] = ['tipo'=>'positivo','icone'=>'bi-graph-up-arrow','texto'=>"Faturamento de hoje {$v}% acima de ontem — ritmo forte de vendas."];
                    elseif ($hvo['var_fat'] <= -15)
                        $insights[] = ['tipo'=>'atencao','icone'=>'bi-graph-down-arrow','texto'=>"Faturamento de hoje {$v}% abaixo de ontem — monitorar o ritmo."];
                }
                if ($hvo['var_ticket'] !== null) {
                    $vt = round(abs($hvo['var_ticket']), 1);
                    if ($hvo['var_ticket'] >= 8)
                        $insights[] = ['tipo'=>'positivo','icone'=>'bi-receipt','texto'=>"Ticket médio subiu {$vt}% hoje — clientes comprando mais por visita."];
                    elseif ($hvo['var_ticket'] <= -8)
                        $insights[] = ['tipo'=>'info','icone'=>'bi-receipt','texto'=>"Ticket médio caiu {$vt}% hoje em relação a ontem."];
                }
                $sem = $this->semanaComparativo();
                if ($sem['var_pct'] !== null) {
                    $vs = round(abs($sem['var_pct']), 1);
                    if ($sem['var_pct'] > 0)
                        $insights[] = ['tipo'=>'positivo','icone'=>'bi-calendar-week','texto'=>"Semana atual {$vs}% acima da semana anterior."];
                    else
                        $insights[] = ['tipo'=>'atencao','icone'=>'bi-calendar-week','texto'=>"Semana atual {$vs}% abaixo da semana anterior — tendência requer atenção."];

                    $maxIdx = array_search(max($sem['semana_atual']), $sem['semana_atual']);
                    $nomes  = ['Segunda','Terça','Quarta','Quinta','Sexta','Sábado','Domingo'];
                    if (($sem['semana_atual'][$maxIdx] ?? 0) > 0)
                        $insights[] = ['tipo'=>'info','icone'=>'bi-star','texto'=>"Melhor dia desta semana: {$nomes[$maxIdx]} com R$ " . number_format($sem['semana_atual'][$maxIdx], 2, ',', '.') . "."];
                }
            } catch (\Throwable) {}

            if (empty($insights))
                $insights[] = ['tipo'=>'info','icone'=>'bi-info-circle','texto'=>'Sem movimentação suficiente para gerar insights hoje.'];

            return $insights;
        });
    }

    // ── privado ────────────────────────────────────────────────────────────────

    private function pivotar(array $rows, int $ano1, int $ano2, string $campo): array
    {
        $out = [];
        for ($m = 1; $m <= 12; $m++) $out[$m] = ['MES' => $m, 'V1' => 0, 'V2' => 0];
        foreach ($rows as $r) {
            $m = (int)$r['MES']; $ano = (int)$r['ANO']; $v = (float)$r[$campo];
            if ($ano === $ano1) $out[$m]['V1'] = $v;
            if ($ano === $ano2) $out[$m]['V2'] = $v;
        }
        return $out;
    }
}