<?php

require_once __DIR__ . '/../../core/database.php';
require_once __DIR__ . '/../treasury/TreasuryService.php';

class KpiDashboardService
{
    private $pdo;
    private $from;
    private $to;
    private $currentUser;
    private $settingsCache = null;
    private $definitionCache = null;
    private $targetCache = null;
    private $freshnessCache = array();

    public function __construct($from, $to, $currentUser)
    {
        $this->pdo = db();
        $this->from = $from;
        $this->to = $to;
        $this->currentUser = $currentUser;
    }

    public function getDashboard($key)
    {
        switch ($key) {
            case 'executive': return $this->executive();
            case 'financial': return $this->financial();
            case 'commercial': return $this->commercial();
            case 'sales': return $this->sales();
            case 'marketing': return $this->marketing();
            case 'production': return $this->production();
            case 'general':
            default: return $this->general();
        }
    }

    public function meta()
    {
        $timestamps = array();
        foreach (array('analytics', 'sales', 'crm', 'billing', 'products', 'treasury') as $source) {
            $timestamp = $this->sourceFreshness($source);
            if ($timestamp) $timestamps[] = $timestamp;
        }
        rsort($timestamps);
        return array(
            'from' => $this->from,
            'to' => $this->to,
            'freshness' => !empty($timestamps) ? $this->formatFreshness($timestamps[0]) : 'Sin actualización registrada',
            'timezone' => 'America/Santiago',
        );
    }

    private function general()
    {
        $sales = $this->salesSummary();
        $concentration = $this->concentration();
        $pipeline = $this->pipelineSummary(false);
        $recurring = $this->recurringCustomers();
        $corfo = $this->corfoGrowth();
        $breakEven = $this->breakEvenCoverage($sales['net_sales']);

        return array(
            'title' => 'Estado general de ICYLAB',
            'subtitle' => 'Resultados colectivos y prioridades, sin exposición de datos sensibles.',
            'kpis' => array(
                $this->kpi('Ventas netas', 'FIN-SALES', $sales['net_sales'], 'clp', $sales['target'], $sales['status'], 'Facturación neta del período.'),
                $this->kpi('Cobertura de equilibrio', 'FIN-01', $breakEven['value'], 'pct', 100, $breakEven['status'], $breakEven['note'], $breakEven['quality']),
                $this->kpi('Crecimiento CORFO', 'FIN-01.1', $corfo['value'], 'pct', $corfo['target_pct'] ?? 30, $corfo['status'], $corfo['note'], $corfo['quality']),
                $this->kpi('Clientes recurrentes', 'COM-01.1', $recurring, 'int', null, 'neutral', 'Clientes con al menos dos compras y una compra en los últimos 90 días.'),
                $this->kpi('Cobertura de pipeline', 'DEM-01', $pipeline['coverage'], 'x', 1.5, $pipeline['coverage_status'], $pipeline['coverage_note'], $pipeline['quality']),
                $this->kpi('Pedidos perfectos', 'OPS-01', null, 'pct', 95, 'na', 'No existen órdenes, lotes y entregas integradas para calcular este KPI.', 'missing'),
            ),
            'charts' => array(
                'sales_trend' => $this->salesTrend(6),
                'pipeline_stage' => $this->pipelineByStage(false),
            ),
            'tables' => array(
                'priorities' => $this->priorityAlerts(),
            ),
            'insight' => $this->executiveInsight($sales, $concentration, $pipeline),
        );
    }

    private function executive()
    {
        $general = $this->general();
        $sales = $this->salesSummary();
        $concentration = $this->concentration();
        $pipeline = $this->pipelineSummary(false);
        $receivables = $this->receivablesSummary();
        $breakEven = $this->breakEvenCoverage($sales['net_sales']);

        $general['title'] = 'Dashboard ejecutivo';
        $general['subtitle'] = 'Prioridades, riesgos y asignación de recursos para dirección.';
        $general['kpis'] = array(
            $this->kpi('Cobertura de equilibrio', 'FIN-01', $breakEven['value'], 'pct', 100, $breakEven['status'], $breakEven['note'], $breakEven['quality']),
            $this->kpi('Concentración Top 5', 'COM-01', $concentration['top5_pct'], 'pct', 45, $concentration['status'], 'Participación de los cinco mayores clientes en las ventas netas del período.'),
            $this->kpi('Pipeline 90 días', 'DEM-01', $pipeline['coverage'], 'x', 1.5, $pipeline['coverage_status'], $pipeline['coverage_note'], $pipeline['quality']),
            $this->kpi('Pedidos perfectos', 'OPS-01', null, 'pct', 95, 'na', 'Pendiente de integrar órdenes, lotes, despachos y reclamos.', 'missing'),
            $this->kpi('Venta faltante a meta', 'FIN-GAP', max(0, $sales['target'] - $sales['net_sales']), 'clp', 0, $sales['net_sales'] >= $sales['target'] ? 'green' : 'amber', 'Brecha respecto de la meta configurada para el período.'),
            $this->kpi('CxC vencida neta', 'FIN-AR', $receivables['overdue_balance'], 'clp', 0, $receivables['overdue_balance'] > 0 ? 'red' : 'green', 'Saldo neto vencido de cuentas por cobrar en producción.'),
        );
        $general['charts']['concentration'] = $concentration['top_customers'];
        $general['tables']['alerts'] = $this->priorityAlerts();
        return $general;
    }

    private function financial()
    {
        $sales = $this->salesSummary();
        $receivables = $this->receivablesSummary();
        $payables = $this->payablesSummary();
        $breakEven = $this->breakEvenCoverage($sales['net_sales']);
        $corfo = $this->corfoGrowth();
        $margin = $this->contributionMargin();
        $cash = $this->cashSummary();

        return array(
            'title' => 'Financiero y CORFO',
            'subtitle' => 'Rentabilidad, caja operativa, cobranza y cumplimiento del proyecto.',
            'kpis' => array(
                $this->kpi('Ventas netas', 'FIN-SALES', $sales['net_sales'], 'clp', $sales['target'], $sales['status'], 'Ventas emitidas menos notas de crédito.'),
                $this->kpi('Cobertura de equilibrio', 'FIN-01', $breakEven['value'], 'pct', 100, $breakEven['status'], $breakEven['note'], $breakEven['quality']),
                $this->kpi('Crecimiento CORFO', 'FIN-01.1', $corfo['value'], 'pct', $corfo['target_pct'] ?? 30, $corfo['status'], $corfo['note'], $corfo['quality']),
                $this->kpi('Margen contribución', 'FIN-01.2', $margin['value'], 'pct', 60, $margin['status'], $margin['note'], $margin['quality']),
                $this->kpi('CxC total neta', 'FIN-AR-TOTAL', $receivables['open_balance'], 'clp', null, $receivables['overdue_balance'] > 0 ? 'amber' : 'neutral', 'Saldo neto abierto por cobrar.'),
                $this->kpi('CxC vencida neta', 'FIN-01.5', $receivables['overdue_balance'], 'clp', 0, $receivables['overdue_balance'] > 0 ? 'red' : 'green', 'Saldo neto vencido por cobrar.'),
                $this->kpi('CxP abierta', 'FIN-AP', $payables['open_balance'], 'clp', null, $payables['overdue_balance'] > 0 ? 'amber' : 'neutral', 'Saldo abierto por pagar.'),
                $this->kpi('Caja disponible', 'FIN-CASH-BAL', $cash['cash_balance'], 'clp', null, $cash['balance_status'], $cash['note'], $cash['quality']),
                $this->kpi('Saldo mínimo 13 semanas', 'FIN-CASH-MIN', $cash['minimum_balance'], 'clp', 0, $cash['minimum_status'], $cash['note'], $cash['quality']),
                $this->kpi('Caja / runway', 'FIN-CASH', $cash['runway_months'], 'months', 3, $cash['runway_status'], $cash['note'], $cash['quality']),
                $this->kpi('Semanas con déficit', 'FIN-CASH-DEFICIT', $cash['deficit_weeks'], 'int', 0, $cash['deficit_status'], $cash['note'], $cash['quality']),
            ),
            'charts' => array(
                'sales_trend' => $this->salesTrend(12),
                'aging' => $this->receivableAging(),
            ),
            'tables' => array(
                'receivables' => $this->topReceivables(),
            ),
            'insight' => $margin['value'] === null ? 'El margen permanece N/D hasta alcanzar cobertura suficiente de líneas y costos. Caja y otros indicadores siguen separados de cifras no integradas.' : 'El margen se calcula desde fotografías de costo por línea; revisa su etiqueta de calidad antes de usarlo para decisiones.',
        );
    }

    private function commercial()
    {
        $concentration = $this->concentration();
        $pipeline = $this->pipelineSummary(false);
        $recurring = $this->recurringCustomers();
        $newCustomers = $this->newCustomers();
        $conversion = $this->conversionSummary(false);

        return array(
            'title' => 'Dashboard comercial',
            'subtitle' => 'Cartera, recurrencia, diversificación y cobertura de venta futura.',
            'kpis' => array(
                $this->kpi('Concentración Top 5', 'COM-01', $concentration['top5_pct'], 'pct', 45, $concentration['status'], 'Menor es mejor. Alerta adicional si Top 1 supera 25%.'),
                $this->kpi('Clientes recurrentes', 'COM-01.1', $recurring, 'int', null, 'neutral', 'Al menos dos compras y una compra en los últimos 90 días.'),
                $this->kpi('Nuevos clientes', 'COM-01.2', $newCustomers, 'int', null, 'neutral', 'Primera factura válida dentro del período.'),
                $this->kpi('Pipeline abierto', 'VEN-004', $pipeline['amount'], 'clp', null, 'neutral', 'Oportunidades abiertas no archivadas.'),
                $this->kpi('Cobertura pipeline', 'DEM-01', $pipeline['coverage'], 'x', 1.5, $pipeline['coverage_status'], $pipeline['coverage_note'], $pipeline['quality']),
                $this->kpi('Conversión cierre', 'VEN-003', $conversion['conversion_pct'], 'pct', 70, $conversion['status'], 'Ganadas o facturadas sobre oportunidades cerradas del período.'),
                $this->kpi('Sin próxima acción', 'ALT-CRM-01', $pipeline['without_next_action'], 'int', 0, $pipeline['without_next_action'] > 0 ? 'red' : 'green', 'Oportunidades abiertas sin próxima acción o con fecha vencida.'),
                $this->kpi('Pipeline ponderado', 'DEM-W', $pipeline['weighted_amount'], 'clp', null, 'neutral', 'Monto multiplicado por probabilidad registrada.'),
            ),
            'charts' => array(
                'pipeline_stage' => $this->pipelineByStage(false),
                'concentration' => $concentration['top_customers'],
                'sources' => $this->pipelineBySource(false),
            ),
            'tables' => array(
                'stale' => $this->staleOpportunities(false),
                'losses' => $this->lossReasons(),
            ),
            'insight' => $this->commercialInsight($concentration, $pipeline, $conversion),
        );
    }

    private function sales()
    {
        $pipeline = $this->pipelineSummary(true);
        $conversion = $this->conversionSummary(true);
        $quotes = $this->quoteSummary(true);
        $receivables = $this->receivablesSummary(true);

        return array(
            'title' => 'Mi dashboard de ventas',
            'subtitle' => 'Acciones y cartera propia. El backend aplica el filtro obligatorio de responsable.',
            'kpis' => array(
                $this->kpi('Oportunidades abiertas', 'VEN-004-C', $pipeline['count'], 'int', null, 'neutral', 'Oportunidades propias abiertas.'),
                $this->kpi('Pipeline propio', 'VEN-004', $pipeline['amount'], 'clp', null, 'neutral', 'Monto total de oportunidades propias abiertas.'),
                $this->kpi('Cobertura de meta', 'VEN-005', $pipeline['coverage'], 'x', 1.5, $pipeline['coverage_status'], $pipeline['coverage_note'], $pipeline['quality']),
                $this->kpi('Cotizaciones del período', 'VEN-002', $quotes['count'], 'int', null, 'neutral', 'Cotizaciones asociadas a oportunidades propias.'),
                $this->kpi('Conversión a venta', 'VEN-003', $conversion['conversion_pct'], 'pct', 70, $conversion['status'], 'Ganadas o facturadas sobre cerradas.'),
                $this->kpi('Seguimientos fuera de SLA', 'VEN-006', $pipeline['overdue_actions'], 'int', 0, $pipeline['overdue_actions'] > 0 ? 'red' : 'green', 'Próximas acciones vencidas.'),
                $this->kpi('Sin próxima acción', 'VEN-007', $pipeline['without_next_action'], 'int', 0, $pipeline['without_next_action'] > 0 ? 'red' : 'green', 'Oportunidades sin una próxima acción válida.'),
                $this->kpi('Cobranza vencida neta', 'VEN-013', $receivables['overdue_balance'], 'clp', 0, $receivables['overdue_balance'] > 0 ? 'red' : 'green', 'Saldo neto vencido de cartera propia vinculada.'),
            ),
            'charts' => array(
                'pipeline_stage' => $this->pipelineByStage(true),
            ),
            'tables' => array(
                'stale' => $this->staleOpportunities(true),
                'upcoming' => $this->upcomingActions(true),
            ),
            'insight' => $pipeline['without_next_action'] > 0
                ? 'La prioridad es sanear la cartera: completar próxima acción y fecha antes de aumentar el volumen de oportunidades.'
                : 'La cartera tiene próximas acciones registradas; la siguiente palanca es aumentar oportunidades calificadas y cierre.',
        );
    }

    private function marketing()
    {
        $source = $this->marketingSourceSummary();
        return array(
            'title' => 'Dashboard de marketing',
            'subtitle' => 'Demanda atribuible y calidad del traspaso hacia ventas.',
            'kpis' => array(
                $this->kpi('Oportunidades originadas', 'MKT-002', $source['opportunities'], 'int', null, $source['opportunities'] > 0 ? 'green' : 'neutral', 'Oportunidades creadas en el período con fuente registrada.'),
                $this->kpi('Pipeline atribuido', 'MKT-003', $source['pipeline'], 'clp', null, 'neutral', 'Monto abierto originado por fuentes registradas.'),
                $this->kpi('Cobertura de atribución', 'MKT-008', $source['attribution_pct'], 'pct', 95, $source['attribution_pct'] === null ? 'na' : ($source['attribution_pct'] >= 95 ? 'green' : ($source['attribution_pct'] >= 75 ? 'amber' : 'red')), 'Oportunidades con fuente sobre el total creado.', $source['attribution_pct'] === null ? 'missing' : 'validated'),
                $this->kpi('Leads ICP', 'MKT-001', null, 'int', null, 'na', 'No existe entidad de leads e ICP integrada.', 'missing'),
                $this->kpi('Costo por oportunidad', 'MKT-004', null, 'clp', null, 'na', 'No existen gastos por campaña integrados.', 'missing'),
                $this->kpi('Conversión por campaña', 'MKT-005', null, 'pct', null, 'na', 'No existe campaign_id persistente en el CRM.', 'missing'),
            ),
            'charts' => array(
                'sources' => $this->pipelineBySource(false),
                'created_sources' => $source['by_source'],
            ),
            'tables' => array(
                'unattributed' => $this->unattributedOpportunities(),
            ),
            'insight' => 'La medición actual permite atribución por fuente comercial, pero no por campaña, contenido ni costo. No se deben usar impresiones o seguidores como sustituto.',
        );
    }

    private function production()
    {
        $stock = $this->stockSummary();
        return array(
            'title' => 'Dashboard de producción',
            'subtitle' => 'Disponibilidad, calidad, trazabilidad y preparación de capacidad.',
            'kpis' => array(
                $this->kpi('SKU activos', 'PRD-STOCK-01', $stock['active_skus'], 'int', null, 'neutral', 'Productos activos del catálogo.'),
                $this->kpi('SKU sin stock', 'PRD-009', $stock['out_of_stock'], 'int', 0, $stock['out_of_stock'] > 0 ? 'red' : 'green', 'Productos activos con stock menor o igual a cero.'),
                $this->kpi('SKU con stock', 'PRD-STOCK-02', $stock['with_stock'], 'int', null, 'neutral', 'Productos activos con disponibilidad mayor a cero.'),
                $this->kpi('Pedidos perfectos', 'OPS-01', null, 'pct', 95, 'na', 'No existen órdenes, lotes, entrega y reclamos integrados.', 'missing'),
                $this->kpi('Liberación a la primera', 'OPS-01.1', null, 'pct', 95, 'na', 'No existe fact_batch con liberación y reproceso.', 'missing'),
                $this->kpi('Utilización capacidad', 'OPS-01.5', null, 'pct', 70, 'na', 'Capacidad nominal y litros producidos aún no están integrados.', 'missing'),
                $this->kpi('Merma interna', 'OPS-01.7', null, 'pct', null, 'na', 'No existe conciliación de inputs, output, reproceso y merma.', 'missing'),
                $this->kpi('Trazabilidad', 'OPS-01.4', null, 'pct', 100, 'na', 'No existe vínculo pedido-lote en el modelo actual.', 'missing'),
            ),
            'charts' => array(
                'stock_categories' => $this->stockByCategory(),
            ),
            'tables' => array(
                'low_stock' => $this->lowStockProducts(),
            ),
            'insight' => 'Esta vista no inventa KPIs productivos. Muestra stock disponible y deja explícitos los contratos de datos necesarios para activar calidad, OTIF, capacidad y merma.',
        );
    }

    private function salesSummary()
    {
        $amount = $this->factoAmountClpSql();
        $customerKey = $this->factoCustomerKeySql();
        $sql = "SELECT COALESCE(SUM(CASE WHEN document_type_taxbureau IN ('33','34','39','56','110','111') THEN {$amount} WHEN document_type_taxbureau IN ('61','112') THEN -{$amount} ELSE 0 END),0) AS net_sales, COUNT(CASE WHEN document_type_taxbureau IN ('33','34','39','56','110','111') THEN 1 END) AS documents, COUNT(DISTINCT {$customerKey}) AS customers FROM facto_documents WHERE environment='production' AND received_issued_flag=1 AND document_status=1 AND issue_date BETWEEN ? AND ?";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array($this->from, $this->to));
        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: array();
        $monthlyTarget = (float)$this->setting('monthly_sales_target', 16000000);
        $configuredTarget = $this->approvedTargetValue('FIN-SALES');
        $target = $configuredTarget !== null ? $configuredTarget : $this->proratedTarget($monthlyTarget);
        $net = (float)($row['net_sales'] ?? 0);
        return array(
            'net_sales' => $net,
            'documents' => (int)($row['documents'] ?? 0),
            'customers' => (int)($row['customers'] ?? 0),
            'target' => $target,
            'status' => $target > 0 && $net >= $target ? 'green' : ($target > 0 && $net >= $target * 0.8 ? 'amber' : 'red'),
        );
    }

    private function proratedTarget($monthlyTarget)
    {
        $monthlyTarget = max(0, (float)$monthlyTarget);
        $rangeStart = strtotime($this->from . ' 00:00:00');
        $rangeEnd = strtotime($this->to . ' 23:59:59');
        if ($monthlyTarget <= 0 || $rangeStart === false || $rangeEnd === false || $rangeStart > $rangeEnd) return 0.0;

        $cursor = strtotime(date('Y-m-01', $rangeStart));
        $lastMonth = strtotime(date('Y-m-01', $rangeEnd));
        $target = 0.0;
        while ($cursor <= $lastMonth) {
            $monthStart = $cursor;
            $monthEnd = strtotime(date('Y-m-t 23:59:59', $cursor));
            $overlapStart = max($rangeStart, $monthStart);
            $overlapEnd = min($rangeEnd, $monthEnd);
            if ($overlapStart <= $overlapEnd) {
                $daysInMonth = (int)date('t', $cursor);
                $overlapStartDate = new DateTime(date('Y-m-d', $overlapStart));
                $overlapEndDate = new DateTime(date('Y-m-d', $overlapEnd));
                $coveredDays = (int)$overlapStartDate->diff($overlapEndDate)->days + 1;
                $coveredDays = max(0, min($daysInMonth, $coveredDays));
                $target += $monthlyTarget * ($coveredDays / $daysInMonth);
            }
            $cursor = strtotime('+1 month', $cursor);
        }
        return round($target, 0);
    }

    private function salesTrend($months)
    {
        $months = max(1, (int)$months);
        $stmt = $this->pdo->prepare("SELECT period, net_sales FROM analytics_monthly_sales ORDER BY period DESC LIMIT " . $months);
        $stmt->execute();
        return array_reverse($stmt->fetchAll(PDO::FETCH_ASSOC));
    }

    private function concentration()
    {
        $amount = $this->factoAmountClpSql();
        $customerKey = $this->factoCustomerKeySql();
        $stmt = $this->pdo->prepare("SELECT {$customerKey} AS customer_key, MAX(receiver_legal_name) AS customer_name, SUM(CASE WHEN document_type_taxbureau IN ('33','34','39','56','110','111') THEN {$amount} WHEN document_type_taxbureau IN ('61','112') THEN -{$amount} ELSE 0 END) AS net_sales FROM facto_documents WHERE environment='production' AND received_issued_flag=1 AND document_status=1 AND issue_date BETWEEN ? AND ? GROUP BY {$customerKey} HAVING net_sales > 0 ORDER BY net_sales DESC");
        $stmt->execute(array($this->from, $this->to));
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $total = 0.0;
        foreach ($rows as $row) $total += (float)$row['net_sales'];
        if ($total <= 0) return array('top5_pct' => null, 'top1_pct' => null, 'status' => 'na', 'top_customers' => array());
        $top5 = 0.0;
        foreach (array_slice($rows, 0, 5) as $row) $top5 += (float)$row['net_sales'];
        $top1 = isset($rows[0]) ? (float)$rows[0]['net_sales'] : 0;
        $pct = $top5 / $total * 100;
        $topRows = array();
        foreach (array_slice($rows, 0, 8) as $row) {
            $topRows[] = array('label' => $row['customer_name'] ?: $row['customer_key'], 'value' => (float)$row['net_sales'], 'share' => (float)$row['net_sales'] / $total * 100);
        }
        return array(
            'top5_pct' => round($pct, 1),
            'top1_pct' => round($top1 / $total * 100, 1),
            'status' => $pct < 45 ? 'green' : ($pct <= 50 ? 'amber' : 'red'),
            'top_customers' => $topRows,
        );
    }

    private function recurringCustomers()
    {
        $stmt = $this->pdo->query("SELECT COUNT(*) FROM analytics_customers WHERE invoice_count >= 2 AND last_purchase >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)");
        return (int)$stmt->fetchColumn();
    }

    private function newCustomers()
    {
        $stmt = $this->pdo->prepare("SELECT COUNT(*) FROM analytics_customers WHERE first_purchase BETWEEN ? AND ?");
        $stmt->execute(array($this->from, $this->to));
        return (int)$stmt->fetchColumn();
    }

    private function pipelineSummary($ownOnly)
    {
        $params = array();
        $ownerSql = $this->ownerSql($ownOnly, $params, 'o');
        $sql = "SELECT COUNT(*) AS opportunity_count, COALESCE(SUM(o.expected_value),0) AS amount, COALESCE(SUM(o.expected_value * o.probability / 100),0) AS weighted_amount, SUM(CASE WHEN o.next_action IS NULL OR o.next_action='' OR o.next_action_date IS NULL THEN 1 ELSE 0 END) AS without_next_action, SUM(CASE WHEN o.next_action_date < CURDATE() THEN 1 ELSE 0 END) AS overdue_actions FROM crm_opportunities o WHERE o.deleted_at IS NULL AND o.archived_at IS NULL AND o.stage IN ('nuevo','contactado','cotizando','negociacion') " . $ownerSql;
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: array();
        $amount = (float)($row['amount'] ?? 0);
        $weighted = (float)($row['weighted_amount'] ?? 0);
        $monthlyTarget = (float)$this->setting('monthly_sales_target', 16000000);
        $breach = max(0, $monthlyTarget * 3);
        $coverage = $breach > 0 ? $amount / $breach : null;
        $status = $coverage === null ? 'na' : ($coverage >= 1.5 ? 'green' : ($coverage >= 1.0 ? 'amber' : 'red'));
        return array(
            'count' => (int)($row['opportunity_count'] ?? 0),
            'amount' => $amount,
            'weighted_amount' => $weighted,
            'without_next_action' => (int)($row['without_next_action'] ?? 0),
            'overdue_actions' => (int)($row['overdue_actions'] ?? 0),
            'coverage' => $coverage === null ? null : round($coverage, 2),
            'coverage_status' => $status,
            'coverage_note' => 'Cobertura provisional sobre tres meses de meta. El forecast recurrente aún no está integrado.',
            'quality' => 'provisional',
        );
    }

    private function pipelineByStage($ownOnly)
    {
        $params = array();
        $ownerSql = $this->ownerSql($ownOnly, $params, 'o');
        $stmt = $this->pdo->prepare("SELECT o.stage AS label, COUNT(*) AS count, COALESCE(SUM(o.expected_value),0) AS value, COALESCE(SUM(o.expected_value*o.probability/100),0) AS weighted FROM crm_opportunities o WHERE o.deleted_at IS NULL AND o.archived_at IS NULL AND o.stage IN ('nuevo','contactado','cotizando','negociacion') " . $ownerSql . " GROUP BY o.stage ORDER BY FIELD(o.stage,'nuevo','contactado','cotizando','negociacion')");
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function pipelineBySource($ownOnly)
    {
        $params = array();
        $ownerSql = $this->ownerSql($ownOnly, $params, 'o');
        $stmt = $this->pdo->prepare("SELECT COALESCE(NULLIF(o.source,''),'Sin atribución') AS label, COUNT(*) AS count, COALESCE(SUM(o.expected_value),0) AS value FROM crm_opportunities o WHERE o.deleted_at IS NULL AND o.archived_at IS NULL " . $ownerSql . " GROUP BY label ORDER BY value DESC LIMIT 10");
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function conversionSummary($ownOnly)
    {
        $params = array($this->from . ' 00:00:00', $this->to . ' 23:59:59');
        $ownerSql = $this->ownerSql($ownOnly, $params, 'o');
        $stmt = $this->pdo->prepare("SELECT COUNT(*) AS closed_count, SUM(CASE WHEN o.closure_outcome IN ('won','invoiced') OR o.stage IN ('ganada','facturada') THEN 1 ELSE 0 END) AS won_count FROM crm_opportunities o WHERE o.deleted_at IS NULL AND COALESCE(o.commercial_closed_at,o.closed_at) BETWEEN ? AND ? " . $ownerSql);
        $stmt->execute($params);
        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: array();
        $closed = (int)($row['closed_count'] ?? 0);
        $won = (int)($row['won_count'] ?? 0);
        $pct = $closed > 0 ? $won / $closed * 100 : null;
        return array(
            'closed' => $closed,
            'won' => $won,
            'conversion_pct' => $pct === null ? null : round($pct, 1),
            'status' => $pct === null ? 'na' : ($pct >= 70 ? 'green' : ($pct >= 50 ? 'amber' : 'red')),
        );
    }

    private function quoteSummary($ownOnly)
    {
        $params = array($this->from . ' 00:00:00', $this->to . ' 23:59:59');
        $ownerSql = $this->ownerSql($ownOnly, $params, 'o');
        $stmt = $this->pdo->prepare("SELECT COUNT(DISTINCT q.id) AS quote_count, COALESCE(SUM(q.subtotal-q.discount_amount),0) AS net_amount FROM crm_quotes q JOIN crm_opportunities o ON o.id=q.opportunity_id WHERE q.deleted_at IS NULL AND q.created_at BETWEEN ? AND ? " . $ownerSql);
        $stmt->execute($params);
        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: array();
        return array('count' => (int)($row['quote_count'] ?? 0), 'net_amount' => (float)($row['net_amount'] ?? 0));
    }

    private function staleOpportunities($ownOnly)
    {
        $params = array();
        $ownerSql = $this->ownerSql($ownOnly, $params, 'o');
        $stmt = $this->pdo->prepare("SELECT o.id, o.title, o.customer_name, o.owner_name, o.stage, o.expected_value, o.next_action, o.next_action_date, DATEDIFF(CURDATE(), COALESCE(o.next_action_date, DATE(o.updated_at))) AS days_late FROM crm_opportunities o WHERE o.deleted_at IS NULL AND o.archived_at IS NULL AND (o.next_action_date < CURDATE() OR o.next_action_date IS NULL OR o.next_action IS NULL OR o.next_action='') " . $ownerSql . " ORDER BY CASE WHEN o.next_action_date IS NULL THEN 0 ELSE 1 END, o.next_action_date ASC, o.expected_value DESC LIMIT 20");
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function upcomingActions($ownOnly)
    {
        $params = array();
        $ownerSql = $this->ownerSql($ownOnly, $params, 'o');
        $stmt = $this->pdo->prepare("SELECT o.id, o.title, o.customer_name, o.next_action, o.next_action_date, o.expected_value, o.probability FROM crm_opportunities o WHERE o.deleted_at IS NULL AND o.archived_at IS NULL AND o.next_action_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 14 DAY) " . $ownerSql . " ORDER BY o.next_action_date ASC, o.expected_value DESC LIMIT 20");
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function lossReasons()
    {
        $stmt = $this->pdo->prepare("SELECT COALESCE(NULLIF(closure_reason,''), NULLIF(lost_reason,''), 'Sin motivo') AS reason, COUNT(*) AS count, COALESCE(SUM(expected_value),0) AS value FROM crm_opportunities WHERE deleted_at IS NULL AND COALESCE(commercial_closed_at,closed_at) BETWEEN ? AND ? AND (closure_outcome IN ('lost','cancelled','no_decision') OR stage='perdida') GROUP BY reason ORDER BY count DESC, value DESC LIMIT 10");
        $stmt->execute(array($this->from . ' 00:00:00', $this->to . ' 23:59:59'));
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function receivableNetBalanceSql($collectionAlias = 'bc', $documentAlias = 'fd')
    {
        $grossTotal = "GREATEST(COALESCE(" . $collectionAlias . ".total_amount, NULLIF(" . $documentAlias . ".total_amount_clp, 0), CASE WHEN UPPER(COALESCE(" . $documentAlias . ".currency_code, 'CLP')) <> 'CLP' THEN " . $documentAlias . ".total_amount * NULLIF(" . $documentAlias . ".exchange_rate_value, 0) ELSE " . $documentAlias . ".total_amount END, " . $documentAlias . ".total_amount, 0), 0)";
        $grossBalance = "GREATEST(COALESCE(" . $collectionAlias . ".balance_amount, " . $grossTotal . "), 0)";
        $netTotal = "GREATEST(COALESCE(NULLIF(" . $documentAlias . ".net_amount_clp, 0), CASE WHEN UPPER(COALESCE(" . $documentAlias . ".currency_code, 'CLP')) <> 'CLP' THEN " . $documentAlias . ".net_amount * NULLIF(" . $documentAlias . ".exchange_rate_value, 0) ELSE " . $documentAlias . ".net_amount END, " . $documentAlias . ".net_amount, 0), 0)";
        $openRatio = "(CASE WHEN " . $grossTotal . " > 0 THEN LEAST(1, GREATEST(" . $grossBalance . " / NULLIF(" . $grossTotal . ", 0), 0)) ELSE 0 END)";
        return "ROUND(" . $netTotal . " * " . $openRatio . ", 2)";
    }

    private function receivablesSummary($ownOnly = false)
    {
        $params = array();
        $ownerJoin = '';
        $ownerWhere = '';
        if ($ownOnly && $this->isSalesRestricted()) {
            $ownerJoin = ' LEFT JOIN crm_opportunities o ON o.id = bc.opportunity_id ';
            $ownerWhere = ' AND o.owner_user_id = ? ';
            $params[] = (int)$this->currentUser['id'];
        }
        $netBalance = $this->receivableNetBalanceSql();
        $stmt = $this->pdo->prepare("SELECT COALESCE(SUM(CASE WHEN bc.collection_status IN ('por_cobrar','atrasado','parcial') THEN " . $netBalance . " ELSE 0 END),0) AS open_balance, COALESCE(SUM(CASE WHEN bc.collection_status='atrasado' OR (bc.due_date<CURDATE() AND " . $netBalance . ">0 AND bc.collection_status NOT IN ('pagado','anulado','incobrable')) THEN " . $netBalance . " ELSE 0 END),0) AS overdue_balance FROM billing_collections bc INNER JOIN facto_documents fd ON fd.environment=bc.environment AND BINARY fd.document_id=BINARY bc.facto_document_id " . $ownerJoin . " WHERE bc.environment='production' AND bc.direction='receivable' " . $ownerWhere);
        $stmt->execute($params);
        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: array();
        return array('open_balance' => (float)($row['open_balance'] ?? 0), 'overdue_balance' => (float)($row['overdue_balance'] ?? 0));
    }

    private function payablesSummary()
    {
        $stmt = $this->pdo->query("SELECT COALESCE(SUM(CASE WHEN collection_status IN ('por_pagar','vencido','parcial') THEN balance_amount ELSE 0 END),0) AS open_balance, COALESCE(SUM(CASE WHEN collection_status='vencido' OR (due_date<CURDATE() AND balance_amount>0 AND collection_status NOT IN ('pagado','anulado','rechazado')) THEN balance_amount ELSE 0 END),0) AS overdue_balance FROM billing_collections WHERE environment='production' AND direction='payable'");
        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: array();
        return array('open_balance' => (float)($row['open_balance'] ?? 0), 'overdue_balance' => (float)($row['overdue_balance'] ?? 0));
    }

    private function receivableAging()
    {
        $netBalance = $this->receivableNetBalanceSql();
        $stmt = $this->pdo->query("SELECT CASE WHEN bc.due_date IS NULL OR bc.due_date>=CURDATE() THEN 'Vigente' WHEN DATEDIFF(CURDATE(),bc.due_date)<=30 THEN '1-30 días' WHEN DATEDIFF(CURDATE(),bc.due_date)<=60 THEN '31-60 días' WHEN DATEDIFF(CURDATE(),bc.due_date)<=90 THEN '61-90 días' ELSE '>90 días' END AS label, COALESCE(SUM(" . $netBalance . "),0) AS value FROM billing_collections bc INNER JOIN facto_documents fd ON fd.environment=bc.environment AND BINARY fd.document_id=BINARY bc.facto_document_id WHERE bc.environment='production' AND bc.direction='receivable' AND " . $netBalance . ">0 AND bc.collection_status NOT IN ('pagado','anulado','incobrable') GROUP BY label ORDER BY FIELD(label,'Vigente','1-30 días','31-60 días','61-90 días','>90 días')");
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function topReceivables()
    {
        $netBalance = $this->receivableNetBalanceSql();
        $stmt = $this->pdo->query("SELECT bc.id, bc.document_number, bc.issue_date, bc.due_date, " . $netBalance . " AS balance_amount, bc.collection_status, COALESCE(c.commercial_name,c.customer_name,'Sin cliente') AS customer_name FROM billing_collections bc INNER JOIN facto_documents fd ON fd.environment=bc.environment AND BINARY fd.document_id=BINARY bc.facto_document_id LEFT JOIN crm_customers c ON c.id=bc.customer_id WHERE bc.environment='production' AND bc.direction='receivable' AND " . $netBalance . ">0 AND bc.collection_status NOT IN ('pagado','anulado','incobrable') ORDER BY CASE WHEN bc.due_date<CURDATE() THEN 0 ELSE 1 END, bc.due_date ASC, balance_amount DESC LIMIT 20");
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function marketingSourceSummary()
    {
        $stmt = $this->pdo->prepare("SELECT COUNT(*) AS total, SUM(CASE WHEN source IS NOT NULL AND source<>'' THEN 1 ELSE 0 END) AS attributed, COALESCE(SUM(CASE WHEN source IS NOT NULL AND source<>'' AND archived_at IS NULL THEN expected_value ELSE 0 END),0) AS pipeline FROM crm_opportunities WHERE deleted_at IS NULL AND created_at BETWEEN ? AND ?");
        $stmt->execute(array($this->from . ' 00:00:00', $this->to . ' 23:59:59'));
        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: array();
        $total = (int)($row['total'] ?? 0);
        $attributed = (int)($row['attributed'] ?? 0);
        $by = $this->pdo->prepare("SELECT COALESCE(NULLIF(source,''),'Sin atribución') AS label, COUNT(*) AS count, COALESCE(SUM(expected_value),0) AS value FROM crm_opportunities WHERE deleted_at IS NULL AND created_at BETWEEN ? AND ? GROUP BY label ORDER BY count DESC");
        $by->execute(array($this->from . ' 00:00:00', $this->to . ' 23:59:59'));
        return array(
            'opportunities' => $attributed,
            'pipeline' => (float)($row['pipeline'] ?? 0),
            'attribution_pct' => $total > 0 ? round($attributed / $total * 100, 1) : null,
            'by_source' => $by->fetchAll(PDO::FETCH_ASSOC),
        );
    }

    private function unattributedOpportunities()
    {
        $stmt = $this->pdo->prepare("SELECT id, title, customer_name, owner_name, expected_value, created_at FROM crm_opportunities WHERE deleted_at IS NULL AND (source IS NULL OR source='') AND created_at BETWEEN ? AND ? ORDER BY created_at DESC LIMIT 20");
        $stmt->execute(array($this->from . ' 00:00:00', $this->to . ' 23:59:59'));
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function stockSummary()
    {
        $stmt = $this->pdo->query("SELECT COUNT(*) AS active_skus, SUM(CASE WHEN stock>0 THEN 1 ELSE 0 END) AS with_stock, SUM(CASE WHEN stock<=0 THEN 1 ELSE 0 END) AS out_of_stock FROM products WHERE is_active=1");
        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: array();
        return array('active_skus' => (int)($row['active_skus'] ?? 0), 'with_stock' => (int)($row['with_stock'] ?? 0), 'out_of_stock' => (int)($row['out_of_stock'] ?? 0));
    }

    private function stockByCategory()
    {
        $stmt = $this->pdo->query("SELECT COALESCE(c.name,'Sin categoría') AS label, COUNT(*) AS value, SUM(CASE WHEN p.stock<=0 THEN 1 ELSE 0 END) AS out_of_stock FROM products p LEFT JOIN product_categories c ON c.id=p.category_id WHERE p.is_active=1 GROUP BY label ORDER BY value DESC");
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function lowStockProducts()
    {
        $stmt = $this->pdo->query("SELECT p.id, p.sku, p.name, p.stock, p.unit, COALESCE(c.name,'Sin categoría') AS category_name FROM products p LEFT JOIN product_categories c ON c.id=p.category_id WHERE p.is_active=1 AND p.stock<=0 ORDER BY p.stock ASC, p.name ASC LIMIT 30");
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function breakEvenCoverage($sales)
    {
        $fixed = $this->approvedSetting('fixed_costs_monthly');
        $margin = $this->approvedSetting('contribution_margin_pct');
        if ($fixed === null || $margin === null || (float)$margin <= 0) {
            return array('value' => null, 'status' => 'na', 'quality' => 'missing', 'note' => 'Requiere costos fijos y margen de contribución aprobados por CFO.');
        }

        $pe = (float)$fixed / ((float)$margin / 100);
        if ($pe <= 0) {
            return array('value' => null, 'status' => 'na', 'quality' => 'failed', 'note' => 'La configuración aprobada genera un punto de equilibrio inválido.');
        }

        $endMonth = date('Y-m-01', strtotime($this->to));
        $startMonth = date('Y-m-01', strtotime($endMonth . ' -2 months'));
        $endDate = date('Y-m-t', strtotime($endMonth));
        $amount = $this->factoAmountClpSql();
        $stmt = $this->pdo->prepare("SELECT DATE_FORMAT(issue_date,'%Y-%m') AS period, COALESCE(SUM(CASE WHEN document_type_taxbureau IN ('33','34','39','56','110','111') THEN {$amount} WHEN document_type_taxbureau IN ('61','112') THEN -{$amount} ELSE 0 END),0) AS net_sales FROM facto_documents WHERE environment='production' AND received_issued_flag=1 AND document_status=1 AND issue_date BETWEEN ? AND ? GROUP BY DATE_FORMAT(issue_date,'%Y-%m')");
        $stmt->execute(array($startMonth, $endDate));
        $byPeriod = array();
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $byPeriod[(string)$row['period']] = (float)$row['net_sales'];
        }

        $coverages = array();
        for ($i = 0; $i < 3; $i++) {
            $period = date('Y-m', strtotime($startMonth . ' +' . $i . ' months'));
            $periodSales = isset($byPeriod[$period]) ? $byPeriod[$period] : 0.0;
            $coverages[] = $periodSales / $pe * 100;
        }
        $value = min($coverages);
        $isOpenMonth = $endMonth === date('Y-m-01') && date('Y-m-d') < $endDate;
        $quality = $isOpenMonth ? 'provisional' : 'validated';
        $note = 'Mínimo de cobertura de los tres meses terminados en ' . date('m-Y', strtotime($endMonth)) . '. Punto de equilibrio mensual: $' . number_format($pe, 0, ',', '.') . '.';
        if ($isOpenMonth) $note .= ' El mes actual permanece provisional hasta el cierre.';

        return array(
            'value' => round($value, 1),
            'status' => $value >= 100 ? 'green' : ($value >= 90 ? 'amber' : 'red'),
            'quality' => $quality,
            'note' => $note,
        );
    }

    private function contributionMargin()
    {
        try {
            $installed = $this->pdo->query("SHOW TABLES LIKE 'billing_line_cost_snapshots'")->fetchColumn();
            if (!$installed) {
                return array('value'=>null,'status'=>'na','quality'=>'missing','note'=>'Requiere instalar la fase 2 de costeo de productos.');
            }
            $codes = "'33','34','39','56','61','110','111','112'";
            $docStmt = $this->pdo->prepare("SELECT COUNT(DISTINCT fd.id) AS documents_total,COUNT(DISTINCT CASE WHEN l.id IS NOT NULL THEN fd.id END) AS documents_with_lines FROM facto_documents fd LEFT JOIN billing_document_lines l ON l.environment=fd.environment AND BINARY l.facto_document_id=BINARY fd.document_id WHERE fd.environment='production' AND fd.received_issued_flag=1 AND fd.document_status=1 AND fd.document_type_taxbureau IN ($codes) AND fd.issue_date BETWEEN ? AND ?");
            $docStmt->execute(array($this->from, $this->to));
            $docs = $docStmt->fetch(PDO::FETCH_ASSOC) ?: array();
            $totalDocs = (int)($docs['documents_total'] ?? 0);
            $withLines = (int)($docs['documents_with_lines'] ?? 0);
            if ($totalDocs <= 0) {
                return array('value'=>null,'status'=>'na','quality'=>'missing','note'=>'No existen ventas emitidas en el período seleccionado.');
            }

            $lineStmt = $this->pdo->prepare("SELECT COALESCE(SUM(ABS(CASE WHEN s.id IS NOT NULL THEN s.net_revenue ELSE l.net_amount*COALESCE(NULLIF(fd.exchange_rate_value,0),1) END)),0) AS revenue_total,COALESCE(SUM(CASE WHEN s.cost_quality<>'missing' THEN ABS(s.net_revenue) ELSE 0 END),0) AS revenue_costed FROM facto_documents fd JOIN billing_document_lines l ON l.environment=fd.environment AND BINARY l.facto_document_id=BINARY fd.document_id LEFT JOIN billing_line_cost_snapshots s ON s.billing_document_line_id=l.id WHERE fd.environment='production' AND fd.received_issued_flag=1 AND fd.document_status=1 AND fd.document_type_taxbureau IN ($codes) AND fd.issue_date BETWEEN ? AND ?");
            $lineStmt->execute(array($this->from, $this->to));
            $lineCoverage = $lineStmt->fetch(PDO::FETCH_ASSOC) ?: array();
            $revenueTotal = (float)($lineCoverage['revenue_total'] ?? 0);
            $documentCoverage = $withLines / $totalDocs * 100;
            $costCoverage = $revenueTotal > 0 ? (float)$lineCoverage['revenue_costed'] / $revenueTotal * 100 : 0.0;
            if ($documentCoverage < 95 || $costCoverage < 90) {
                return array(
                    'value'=>null,'status'=>'na','quality'=>'missing',
                    'note'=>'Cobertura insuficiente: ' . number_format($documentCoverage,1,',','.') . '% de documentos con líneas y ' . number_format($costCoverage,1,',','.') . '% del ingreso de líneas con costo. Umbrales: 95% y 90%.'
                );
            }

            $sumStmt = $this->pdo->prepare("SELECT COALESCE(SUM(net_revenue),0) AS revenue,COALESCE(SUM(contribution_margin),0) AS contribution,COALESCE(SUM(CASE WHEN cost_quality='provisional' THEN ABS(net_revenue) ELSE 0 END),0) AS provisional_revenue FROM billing_line_cost_snapshots WHERE environment='production' AND document_type_taxbureau IN ($codes) AND document_date BETWEEN ? AND ? AND cost_quality<>'missing'");
            $sumStmt->execute(array($this->from, $this->to));
            $sum = $sumStmt->fetch(PDO::FETCH_ASSOC) ?: array();
            $revenue = (float)($sum['revenue'] ?? 0);
            if (abs($revenue) < 0.005) return array('value'=>null,'status'=>'na','quality'=>'missing','note'=>'Las líneas cubiertas no tienen ingreso neto utilizable.');
            $value = round((float)$sum['contribution'] / $revenue * 100, 1);
            $quality = (float)($sum['provisional_revenue'] ?? 0) > 0 ? 'provisional' : 'validated';
            $note = 'Venta neta menos costo estándar congelado por línea. Cobertura documental ' . number_format($documentCoverage,1,',','.') . '% y cobertura de costo ' . number_format($costCoverage,1,',','.') . '%.';
            if ($quality === 'provisional') $note .= ' Incluye versiones heredadas pendientes de revisión.';
            return array('value'=>$value,'status'=>$value>=60?'green':($value>=48?'amber':'red'),'quality'=>$quality,'note'=>$note);
        } catch (Throwable $e) {
            error_log('dashboard contribution margin: ' . $e->getMessage());
            return array('value'=>null,'status'=>'na','quality'=>'failed','note'=>'No fue posible validar la cobertura del margen. Revisa la instalación de costeo.');
        }
    }

    private function corfoGrowth()
    {
        $baseline = $this->approvedSetting('corfo_baseline_sales');
        $start = $this->approvedSetting('corfo_project_start');
        $end = $this->approvedSetting('corfo_project_end');
        if ($baseline === null || $start === null || $end === null || (float)$baseline <= 0) {
            return array('value' => null, 'target_pct' => 30, 'status' => 'na', 'quality' => 'missing', 'note' => 'La línea base CORFO todavía no está aprobada y versionada.');
        }
        $amount = $this->factoAmountClpSql();
        $stmt = $this->pdo->prepare("SELECT COALESCE(SUM(CASE WHEN document_type_taxbureau IN ('33','34','39','56','110','111') THEN {$amount} WHEN document_type_taxbureau IN ('61','112') THEN -{$amount} ELSE 0 END),0) FROM facto_documents WHERE environment='production' AND received_issued_flag=1 AND document_status=1 AND issue_date BETWEEN ? AND ?");
        $stmt->execute(array($start, $end));
        $sales = (float)$stmt->fetchColumn();
        $baseline = (float)$baseline;
        $requiredIncrease = max($baseline * 0.30, 6000000.0);
        $targetSales = $baseline + $requiredIncrease;
        $targetPct = $requiredIncrease / $baseline * 100;
        $growth = ($sales - $baseline) / $baseline * 100;
        $status = $growth >= $targetPct ? 'green' : ($growth >= $targetPct * 0.8 ? 'amber' : 'red');
        return array(
            'value' => round($growth, 1),
            'target_pct' => round($targetPct, 1),
            'status' => $status,
            'quality' => 'validated',
            'note' => 'Ventas computables del proyecto contra línea base activa. Meta monetaria: $' . number_format($targetSales, 0, ',', '.') . '.',
        );
    }

    private function factoAmountClpSql()
    {
        return "COALESCE(net_amount_clp, CASE WHEN UPPER(COALESCE(NULLIF(currency_code,''),'CLP'))='CLP' THEN net_amount WHEN COALESCE(exchange_rate_value,0)>0 THEN ROUND(net_amount*exchange_rate_value,2) ELSE 0 END)";
    }

    private function factoCustomerKeySql()
    {
        return "COALESCE(NULLIF(REPLACE(REPLACE(UPPER(TRIM(receiver_tax_id_code)),'.',''),'-',''),''),CONCAT('CRM-',NULLIF(crm_customer_id,0)),NULLIF(UPPER(TRIM(receiver_legal_name)),'') )";
    }

    private function priorityAlerts()
    {
        $alerts = array();
        $pipeline = $this->pipelineSummary(false);
        if ($pipeline['coverage'] !== null && $pipeline['coverage'] < 1.0) {
            $alerts[] = array('severity' => 'red', 'title' => 'Pipeline insuficiente', 'detail' => 'Cobertura bajo 1,0x para los próximos 90 días.', 'owner' => 'Gerencia Comercial', 'action' => 'Prospección y campañas');
        }
        if ($pipeline['without_next_action'] > 0) {
            $alerts[] = array('severity' => 'amber', 'title' => 'Cartera sin próxima acción', 'detail' => $pipeline['without_next_action'] . ' oportunidades requieren actualización.', 'owner' => 'Ventas', 'action' => 'Actualizar o cerrar');
        }
        $concentration = $this->concentration();
        if ($concentration['top5_pct'] !== null && $concentration['top5_pct'] > 50) {
            $alerts[] = array('severity' => 'red', 'title' => 'Concentración alta', 'detail' => 'Top 5 supera 50% de ventas del período.', 'owner' => 'Gerencia Comercial', 'action' => 'Plan de diversificación');
        }
        $receivables = $this->receivablesSummary();
        if ($receivables['overdue_balance'] > 0) {
            $alerts[] = array('severity' => 'amber', 'title' => 'Cobranza vencida', 'detail' => 'Existe saldo vencido por cobrar.', 'owner' => 'CFO / Comercial', 'action' => 'Plan de cobranza');
        }
        if (empty($alerts)) {
            $alerts[] = array('severity' => 'green', 'title' => 'Sin alertas críticas calculables', 'detail' => 'No se detectaron excepciones en las fuentes integradas.', 'owner' => 'Equipo', 'action' => 'Mantener seguimiento');
        }
        return $alerts;
    }

    private function executiveInsight($sales, $concentration, $pipeline)
    {
        if ($pipeline['coverage'] !== null && $pipeline['coverage'] < 1.0) return 'La restricción principal es la cobertura futura: el pipeline abierto no cubre la meta de 90 días.';
        if ($concentration['top5_pct'] !== null && $concentration['top5_pct'] > 50) return 'La venta puede sostenerse en monto, pero la concentración aumenta el riesgo de volatilidad y dependencia.';
        if ($sales['net_sales'] < $sales['target']) return 'La brecha mensual sigue abierta; prioriza cierres de alta probabilidad y recompra antes de ampliar iniciativas.';
        return 'Las fuentes integradas no muestran una alerta dominante; mantén foco en recurrencia, calidad de pipeline y cobranza.';
    }

    private function commercialInsight($concentration, $pipeline, $conversion)
    {
        if ($pipeline['without_next_action'] > 0) return 'El primer problema no es volumen, sino higiene de CRM: existen oportunidades sin próxima acción válida.';
        if ($pipeline['coverage'] !== null && $pipeline['coverage'] < 1.0) return 'El pipeline está por debajo de la cobertura mínima; marketing y ventas deben aumentar oportunidades calificadas.';
        if ($conversion['conversion_pct'] !== null && $conversion['conversion_pct'] < 50) return 'El pipeline existe, pero la conversión es baja; revisar calificación, seguimiento y propuesta de valor.';
        if ($concentration['top5_pct'] !== null && $concentration['top5_pct'] > 50) return 'La cartera depende demasiado de pocas cuentas; priorizar reactivación y adquisición de clientes recurrentes.';
        return 'La cartera no presenta una excepción dominante en los datos disponibles; sostener cadencia semanal y cerrar registros obsoletos.';
    }

    private function cashSummary()
    {
        try {
            $metrics = (new TreasuryService($this->pdo))->dashboardCashMetrics();
            $balance = $metrics['cash_balance'];
            $minimum = $metrics['minimum_balance'];
            $runway = $metrics['runway_months'];
            $deficit = (int)$metrics['deficit_weeks'];
            return array(
                'cash_balance' => $balance,
                'minimum_balance' => $minimum,
                'runway_months' => $runway,
                'deficit_weeks' => $deficit,
                'balance_status' => $balance < 0 ? 'red' : 'neutral',
                'minimum_status' => $minimum < 0 ? 'red' : 'green',
                'runway_status' => $runway === null ? 'na' : ($runway < 1 ? 'red' : ($runway < 3 ? 'amber' : 'green')),
                'deficit_status' => $deficit > 0 ? 'red' : 'green',
                'quality' => $metrics['quality'],
                'note' => $metrics['note'],
            );
        } catch (Throwable $e) {
            return array(
                'cash_balance' => null, 'minimum_balance' => null, 'runway_months' => null, 'deficit_weeks' => null,
                'balance_status' => 'na', 'minimum_status' => 'na', 'runway_status' => 'na', 'deficit_status' => 'na',
                'quality' => 'missing', 'note' => 'Tesorería no está disponible hasta instalar y configurar la fase 3.',
            );
        }
    }

    private function ownerSql($ownOnly, &$params, $alias)
    {
        if (!$ownOnly || !$this->isSalesRestricted()) return '';
        $params[] = (int)$this->currentUser['id'];
        return ' AND ' . $alias . '.owner_user_id = ? ';
    }

    private function isSalesRestricted()
    {
        return (($this->currentUser['role'] ?? '') === 'comercial');
    }

    private function kpi($name, $id, $value, $unit, $target, $status, $note, $quality = 'validated')
    {
        $definition = $this->definitionMeta($id);
        $targetMeta = $this->approvedTargets();
        $targetRow = isset($targetMeta[$id]) ? $targetMeta[$id] : null;
        if ($targetRow && $targetRow['target_value'] !== null && $targetRow['target_value'] !== '') {
            $target = (float)$targetRow['target_value'];
            if ($value !== null && $value !== '' && $quality !== 'missing' && $quality !== 'failed') {
                $status = $this->statusForTarget((float)$value, $target, $definition['direction'], $targetRow, $status);
            }
        }
        return array(
            'name' => $name,
            'id' => $id,
            'value' => $value,
            'unit' => $unit,
            'target' => $target,
            'status' => $status,
            'note' => $note,
            'quality' => $quality,
            'freshness' => $this->freshnessForKpi($id, $quality),
            'definition_version' => $definition['version'],
            'formula' => $definition['formula'],
            'favorable_direction' => $definition['direction'],
            'target_source' => $targetRow ? 'configured' : 'default',
        );
    }

    private function definitionMeta($id)
    {
        if ($this->definitionCache === null) {
            $this->definitionCache = array();
            try {
                $rows = $this->pdo->query("SELECT kpi_id,definition_version,formula_text,favorable_direction FROM kpi_definition WHERE is_active=1 ORDER BY updated_at DESC,id DESC")->fetchAll(PDO::FETCH_ASSOC);
                foreach ($rows as $row) {
                    if (!isset($this->definitionCache[$row['kpi_id']])) {
                        $this->definitionCache[$row['kpi_id']] = array(
                            'version' => $row['definition_version'],
                            'formula' => $row['formula_text'],
                            'direction' => $row['favorable_direction'],
                        );
                    }
                }
            } catch (Throwable $e) {
                $this->definitionCache = array();
            }
        }

        if (isset($this->definitionCache[$id])) return $this->definitionCache[$id];

        $fallbackDirections = array(
            'FIN-SALES' => 'higher', 'FIN-01' => 'higher', 'FIN-01.1' => 'higher', 'FIN-01.2' => 'higher',
            'FIN-GAP' => 'lower', 'FIN-AR' => 'lower', 'FIN-AR-TOTAL' => 'lower', 'FIN-01.5' => 'lower', 'FIN-AP' => 'lower', 'FIN-CASH' => 'higher',
            'FIN-CASH-BAL' => 'higher', 'FIN-CASH-MIN' => 'higher', 'FIN-CASH-DEFICIT' => 'lower',
            'COM-01' => 'lower', 'COM-01.1' => 'higher', 'COM-01.2' => 'higher', 'DEM-01' => 'higher',
            'VEN-003' => 'higher', 'VEN-005' => 'higher', 'VEN-006' => 'lower', 'VEN-007' => 'lower', 'VEN-013' => 'lower', 'ALT-CRM-01' => 'lower',
            'MKT-001' => 'higher', 'MKT-002' => 'higher', 'MKT-003' => 'higher', 'MKT-004' => 'lower', 'MKT-005' => 'higher', 'MKT-008' => 'higher',
            'PRD-STOCK-02' => 'higher', 'PRD-009' => 'lower', 'OPS-01' => 'higher', 'OPS-01.1' => 'higher', 'OPS-01.4' => 'higher', 'OPS-01.5' => 'higher', 'OPS-01.7' => 'lower',
        );
        return array('version' => '1.0', 'formula' => null, 'direction' => $fallbackDirections[$id] ?? 'informative');
    }

    private function approvedTargets()
    {
        if ($this->targetCache !== null) return $this->targetCache;
        $this->targetCache = array();
        try {
            $stmt = $this->pdo->prepare("SELECT id,kpi_id,target_value,amber_threshold,red_threshold,period_from,period_to,updated_at
                FROM kpi_target
                WHERE scope_type='company' AND scope_id IS NULL AND status='approved'
                  AND period_from<=? AND period_to>=?
                ORDER BY period_from DESC,updated_at DESC,id DESC");
            $stmt->execute(array($this->to, $this->from));
            foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
                if (!isset($this->targetCache[$row['kpi_id']])) {
                    $this->targetCache[$row['kpi_id']] = $row;
                }
            }
        } catch (Throwable $e) {
            $this->targetCache = array();
        }
        return $this->targetCache;
    }

    private function approvedTargetValue($kpiId)
    {
        $targets = $this->approvedTargets();
        if (!isset($targets[$kpiId]) || $targets[$kpiId]['target_value'] === null || $targets[$kpiId]['target_value'] === '') return null;
        return (float)$targets[$kpiId]['target_value'];
    }

    private function statusForTarget($value, $target, $direction, array $targetRow, $fallback)
    {
        if ($direction === 'higher') {
            if ($value >= $target) return 'green';
            $amber = $targetRow['amber_threshold'] !== null ? (float)$targetRow['amber_threshold'] : $target * 0.8;
            $red = $targetRow['red_threshold'] !== null ? (float)$targetRow['red_threshold'] : null;
            if ($red !== null && $value <= $red) return 'red';
            return $value >= $amber || $red !== null ? 'amber' : 'red';
        }
        if ($direction === 'lower') {
            if ($value <= $target) return 'green';
            $amber = $targetRow['amber_threshold'] !== null ? (float)$targetRow['amber_threshold'] : ($target > 0 ? $target * 1.2 : $target);
            $red = $targetRow['red_threshold'] !== null ? (float)$targetRow['red_threshold'] : null;
            if ($red !== null && $value >= $red) return 'red';
            return $value <= $amber || $red !== null ? 'amber' : 'red';
        }
        return $fallback;
    }

    private function freshnessForKpi($kpiId, $quality)
    {
        if ($quality === 'missing') return 'Fuente pendiente';
        if (strpos($kpiId, 'PRD-') === 0 || strpos($kpiId, 'OPS-') === 0) $source = 'products';
        elseif (strpos($kpiId, 'VEN-') === 0 || strpos($kpiId, 'MKT-') === 0 || strpos($kpiId, 'DEM-') === 0 || strpos($kpiId, 'ALT-') === 0) $source = 'crm';
        elseif (in_array($kpiId, array('FIN-CASH','FIN-CASH-BAL','FIN-CASH-MIN','FIN-CASH-DEFICIT'), true)) $source = 'treasury';
        elseif (in_array($kpiId, array('FIN-AR','FIN-AR-TOTAL','FIN-01.5','FIN-AP'), true)) $source = 'billing';
        elseif (in_array($kpiId, array('COM-01.1','COM-01.2'), true)) $source = 'analytics';
        else $source = 'sales';

        $timestamp = $this->sourceFreshness($source);
        return $timestamp ? $this->formatFreshness($timestamp) : 'Sin actualización registrada';
    }

    private function sourceFreshness($source)
    {
        if (array_key_exists($source, $this->freshnessCache)) return $this->freshnessCache[$source];
        $queries = array(
            'analytics' => "SELECT MAX(updated_at) FROM analytics_customers",
            'sales' => "SELECT MAX(updated_at) FROM facto_documents WHERE environment='production'",
            'crm' => "SELECT MAX(updated_at) FROM crm_opportunities WHERE deleted_at IS NULL",
            'billing' => "SELECT MAX(updated_at) FROM billing_collections WHERE environment='production'",
            'products' => "SELECT MAX(updated_at) FROM products WHERE is_active=1",
            'treasury' => "SELECT GREATEST(COALESCE((SELECT MAX(updated_at) FROM treasury_opening_balances),'1900-01-01'),COALESCE((SELECT MAX(updated_at) FROM treasury_manual_movements),'1900-01-01'),COALESCE((SELECT MAX(updated_at) FROM treasury_statement_rows),'1900-01-01'))",
        );
        if (!isset($queries[$source])) return null;
        try {
            $value = $this->pdo->query($queries[$source])->fetchColumn();
            $this->freshnessCache[$source] = $value ? (string)$value : null;
        } catch (Throwable $e) {
            $this->freshnessCache[$source] = null;
        }
        return $this->freshnessCache[$source];
    }

    private function formatFreshness($value)
    {
        $timestamp = strtotime((string)$value);
        return $timestamp ? date('d-m-Y H:i', $timestamp) : (string)$value;
    }

    private function settings()
    {
        if ($this->settingsCache !== null) return $this->settingsCache;
        $this->settingsCache = array();
        try {
            $rows = $this->pdo->query("SELECT setting_key, setting_value, status FROM dashboard_settings")->fetchAll(PDO::FETCH_ASSOC);
            foreach ($rows as $row) {
                $value = json_decode($row['setting_value'], true);
                if (json_last_error() !== JSON_ERROR_NONE) $value = $row['setting_value'];
                $this->settingsCache[$row['setting_key']] = array('value' => $value, 'status' => $row['status']);
            }
        } catch (Throwable $e) {
            $this->settingsCache = array();
        }
        return $this->settingsCache;
    }

    private function setting($key, $default = null)
    {
        $settings = $this->settings();
        return array_key_exists($key, $settings) ? $settings[$key]['value'] : $default;
    }

    private function approvedSetting($key)
    {
        $settings = $this->settings();
        if (!isset($settings[$key]) || $settings[$key]['status'] !== 'approved') return null;
        return $settings[$key]['value'];
    }
}
