-- ICYLAB OS v8.7 - Gobierno de metas KPI y consistencia del dashboard.
-- Idempotente para MariaDB/MySQL compatibles con ADD COLUMN IF NOT EXISTS.
-- Ejecutar con respaldo previo desde phpMyAdmin.

-- -----------------------------------------------------------------------------
-- 1. Ciclo de aprobación y trazabilidad de metas
-- -----------------------------------------------------------------------------
ALTER TABLE kpi_target
    ADD COLUMN IF NOT EXISTS status ENUM('draft','approved','inactive') NOT NULL DEFAULT 'draft' AFTER red_threshold,
    ADD COLUMN IF NOT EXISTS notes VARCHAR(1000) NULL AFTER status,
    ADD COLUMN IF NOT EXISTS approved_by_user_id INT NULL AFTER created_by_user_id,
    ADD COLUMN IF NOT EXISTS approved_at DATETIME NULL AFTER approved_by_user_id,
    ADD COLUMN IF NOT EXISTS updated_by_user_id INT NULL AFTER approved_at;

CREATE TABLE IF NOT EXISTS kpi_target_history (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    target_id BIGINT UNSIGNED NOT NULL,
    version_no INT UNSIGNED NOT NULL,
    kpi_id VARCHAR(40) NOT NULL,
    scope_type ENUM('company','role','user','team') NOT NULL DEFAULT 'company',
    scope_id VARCHAR(100) NULL,
    period_from DATE NOT NULL,
    period_to DATE NOT NULL,
    target_value DECIMAL(18,4) NULL,
    amber_threshold DECIMAL(18,4) NULL,
    red_threshold DECIMAL(18,4) NULL,
    status ENUM('draft','approved','inactive') NOT NULL DEFAULT 'draft',
    notes VARCHAR(1000) NULL,
    changed_by_user_id INT NULL,
    changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_kpi_target_history_version (target_id, version_no),
    KEY idx_kpi_target_history_kpi (kpi_id, changed_at),
    KEY idx_kpi_target_history_actor (changed_by_user_id, changed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @db_name := DATABASE();
SET @idx_exists := (
    SELECT COUNT(*) FROM information_schema.statistics
    WHERE table_schema=@db_name AND table_name='kpi_target' AND index_name='idx_kpi_target_status_period'
);
SET @sql := IF(
    @idx_exists=0,
    'ALTER TABLE kpi_target ADD INDEX idx_kpi_target_status_period (status, period_from, period_to, kpi_id)',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- -----------------------------------------------------------------------------
-- 2. Catálogo de todos los KPI visibles
-- -----------------------------------------------------------------------------
INSERT INTO kpi_definition
    (kpi_id,name,definition_version,unit,favorable_direction,owner_role,formula_text,is_active)
VALUES
    ('FIN-SALES','Ventas netas','1.0','clp','higher','CEO/CFO','ventas emitidas válidas menos notas de crédito, neto de IVA',1),
    ('FIN-01','Cobertura sostenida del punto de equilibrio','1.0','pct','higher','CEO/CFO','ventas_netas / (costos_fijos / margen_contribucion_pct) * 100',1),
    ('FIN-01.1','Crecimiento CORFO','1.0','pct','higher','CEO/CFO','(ventas computables - línea base) / línea base * 100',1),
    ('FIN-01.2','Margen de contribución','1.0','pct','higher','CEO/CFO','(ventas netas - costos variables) / ventas netas * 100',1),
    ('FIN-GAP','Venta faltante a meta','1.0','clp','lower','CEO/CFO','máximo entre cero y meta de ventas menos ventas netas',1),
    ('FIN-AR','CxC vencida neta','1.0','clp','lower','CEO/CFO','saldo neto vencido de cuentas por cobrar de producción',1),
    ('FIN-AR-TOTAL','CxC total neta','1.0','clp','lower','CEO/CFO','saldo neto abierto de cuentas por cobrar de producción',1),
    ('FIN-01.5','CxC vencida neta','1.0','clp','lower','CEO/CFO','saldo neto vencido de cuentas por cobrar de producción',1),
    ('FIN-AP','CxP abierta','1.0','clp','lower','CEO/CFO','saldo bruto abierto de cuentas por pagar de producción',1),
    ('FIN-CASH','Caja y runway','1.0','months','higher','CEO/CFO','saldo conciliado / salida de caja mensual proyectada',1),
    ('COM-01','Concentración de ingresos Top 5','1.0','pct','lower','CEO/Gerencia Comercial','ventas_top_5 / ventas_totales * 100',1),
    ('COM-01.1','Clientes recurrentes','1.0','int','higher','Gerencia Comercial','clientes con dos o más compras y compra en los últimos 90 días',1),
    ('COM-01.2','Nuevos clientes','1.0','int','higher','Gerencia Comercial','clientes cuya primera factura válida pertenece al período',1),
    ('DEM-01','Cobertura de pipeline calificado','1.0','x','higher','Gerencia Comercial','pipeline_calificado_90d / brecha_90d',1),
    ('DEM-W','Pipeline ponderado','1.0','clp','informative','Gerencia Comercial','suma de monto por probabilidad de oportunidades abiertas',1),
    ('VEN-002','Cotizaciones del período','1.0','int','higher','Ventas','cotizaciones propias creadas en el período',1),
    ('VEN-003','Conversión de cierre','1.0','pct','higher','Ventas','oportunidades ganadas o facturadas / oportunidades cerradas * 100',1),
    ('VEN-004','Pipeline abierto','1.0','clp','informative','Ventas','monto de oportunidades abiertas no archivadas',1),
    ('VEN-004-C','Oportunidades abiertas','1.0','int','informative','Ventas','cantidad de oportunidades abiertas no archivadas',1),
    ('VEN-005','Cobertura de meta','1.0','x','higher','Ventas','pipeline propio / brecha de ventas',1),
    ('VEN-006','Seguimientos fuera de SLA','1.0','int','lower','Ventas','próximas acciones vencidas',1),
    ('VEN-007','Oportunidades sin próxima acción','1.0','int','lower','Ventas','oportunidades abiertas sin próxima acción válida',1),
    ('VEN-013','Cobranza vencida neta','1.0','clp','lower','Ventas','saldo neto vencido de cartera propia vinculada',1),
    ('ALT-CRM-01','Cartera sin próxima acción','1.0','int','lower','Gerencia Comercial','oportunidades abiertas sin próxima acción válida',1),
    ('MKT-001','Leads ICP','1.0','int','higher','Marketing','leads que cumplen el perfil de cliente ideal',1),
    ('MKT-002','Oportunidades originadas','1.0','int','higher','Marketing','oportunidades creadas en el período con fuente',1),
    ('MKT-003','Pipeline atribuido','1.0','clp','higher','Marketing','monto abierto de oportunidades con fuente',1),
    ('MKT-004','Costo por oportunidad','1.0','clp','lower','Marketing','inversión atribuida / oportunidades originadas',1),
    ('MKT-005','Conversión por campaña','1.0','pct','higher','Marketing','oportunidades o ventas / leads de campaña * 100',1),
    ('MKT-008','Cobertura de atribución','1.0','pct','higher','Marketing','oportunidades con fuente / oportunidades creadas * 100',1),
    ('PRD-STOCK-01','SKU activos','1.0','int','informative','Producción','productos activos del catálogo',1),
    ('PRD-STOCK-02','SKU con stock','1.0','int','higher','Producción','productos activos con stock mayor a cero',1),
    ('PRD-009','SKU sin stock','1.0','int','lower','Producción','productos activos con stock menor o igual a cero',1),
    ('OPS-01','Tasa de pedidos perfectos','1.0','pct','higher','CEO/Producción','pedidos perfectos / pedidos entregados * 100',1),
    ('OPS-01.1','Liberación a la primera','1.0','pct','higher','Producción','lotes liberados sin reproceso / lotes terminados * 100',1),
    ('OPS-01.4','Trazabilidad pedido-lote','1.0','pct','higher','Producción','pedidos entregados con vínculo completo a lote / pedidos entregados * 100',1),
    ('OPS-01.5','Utilización de capacidad','1.0','pct','higher','Producción','producción real / capacidad nominal * 100',1),
    ('OPS-01.7','Merma interna','1.0','pct','lower','Producción','merma conciliada / inputs de producción * 100',1)
ON DUPLICATE KEY UPDATE
    name=VALUES(name),
    unit=VALUES(unit),
    favorable_direction=VALUES(favorable_direction),
    owner_role=VALUES(owner_role),
    formula_text=VALUES(formula_text),
    is_active=1;

-- -----------------------------------------------------------------------------
-- 3. Reparación acotada de estados financieros inválidos
-- -----------------------------------------------------------------------------
UPDATE billing_collections
SET collection_status = CASE
        WHEN balance_amount <= 0 THEN 'pagado'
        WHEN due_date IS NOT NULL AND due_date < CURDATE() THEN 'vencido'
        ELSE 'por_pagar'
    END,
    notes = CONCAT_WS(' | ', NULLIF(notes,''), 'Estado normalizado por migración dashboard v8.7.')
WHERE environment='production'
  AND direction='payable'
  AND COALESCE(TRIM(collection_status),'')='';

-- -----------------------------------------------------------------------------
-- 4. Verificación final
-- -----------------------------------------------------------------------------
SELECT
    (SELECT COUNT(*) FROM kpi_definition WHERE is_active=1) AS active_kpi_definitions,
    (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='kpi_target' AND column_name IN ('status','notes','approved_by_user_id','approved_at','updated_by_user_id')) AS target_governance_columns,
    (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='kpi_target_history') AS target_history_table,
    (SELECT COUNT(*) FROM billing_collections WHERE environment='production' AND direction='payable' AND COALESCE(TRIM(collection_status),'')='') AS invalid_payable_statuses;
