-- ICYLAB OS v8.4 - MIGRACION DE PRODUCCION AUTOCONTENIDA
-- Incluye esquema de dashboards v8.1 y endurecimiento de ciclo comercial v8.4.
-- Es idempotente para MariaDB/MySQL compatibles con ADD COLUMN IF NOT EXISTS.
-- Generar respaldo de base de datos antes de ejecutar.

-- -----------------------------------------------------------------------------
-- 1. Ciclo base de oportunidades y trazabilidad por etapa
-- -----------------------------------------------------------------------------
ALTER TABLE crm_opportunities
    ADD COLUMN IF NOT EXISTS closed_at DATETIME NULL AFTER lost_reason,
    ADD COLUMN IF NOT EXISTS closed_by_user_id INT NULL AFTER closed_at,
    ADD COLUMN IF NOT EXISTS closure_outcome ENUM('won','lost','invoiced','cancelled','no_decision') NULL AFTER closed_by_user_id,
    ADD COLUMN IF NOT EXISTS closure_reason VARCHAR(255) NULL AFTER closure_outcome,
    ADD COLUMN IF NOT EXISTS closure_note TEXT NULL AFTER closure_reason,
    ADD COLUMN IF NOT EXISTS reopened_at DATETIME NULL AFTER closure_note,
    ADD COLUMN IF NOT EXISTS reopened_by_user_id INT NULL AFTER reopened_at;

CREATE TABLE IF NOT EXISTS crm_opportunity_stage_history (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    opportunity_id INT NOT NULL,
    from_stage VARCHAR(40) NULL,
    to_stage VARCHAR(40) NOT NULL,
    changed_by_user_id INT NULL,
    changed_by_name VARCHAR(255) NULL,
    change_source ENUM('kanban','form','close','reopen','system') NOT NULL DEFAULT 'system',
    changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_stage_history_opportunity (opportunity_id, changed_at),
    KEY idx_stage_history_stage (to_stage, changed_at),
    KEY idx_stage_history_user (changed_by_user_id, changed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 2. Gobierno de dashboards y KPIs
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS dashboard_user_access (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    dashboard_key ENUM('general','executive','financial','commercial','sales','marketing','production') NOT NULL,
    can_view TINYINT(1) NOT NULL DEFAULT 1,
    can_configure TINYINT(1) NOT NULL DEFAULT 0,
    can_export TINYINT(1) NOT NULL DEFAULT 0,
    granted_by_user_id INT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_dashboard_access_user_key (user_id, dashboard_key),
    KEY idx_dashboard_access_key (dashboard_key, can_view)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dashboard_settings (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    setting_key VARCHAR(100) NOT NULL,
    setting_value LONGTEXT NOT NULL,
    status ENUM('draft','approved','inactive') NOT NULL DEFAULT 'draft',
    valid_from DATE NULL,
    valid_to DATE NULL,
    approved_by_user_id INT NULL,
    approved_at DATETIME NULL,
    updated_by_user_id INT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_dashboard_setting_key (setting_key),
    KEY idx_dashboard_setting_status (status, valid_from, valid_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kpi_definition (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    kpi_id VARCHAR(40) NOT NULL,
    name VARCHAR(255) NOT NULL,
    definition_version VARCHAR(30) NOT NULL DEFAULT '1.0',
    unit VARCHAR(30) NULL,
    favorable_direction ENUM('higher','lower','range','informative') NOT NULL DEFAULT 'informative',
    owner_role VARCHAR(80) NULL,
    formula_text TEXT NULL,
    inclusions TEXT NULL,
    exclusions TEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    valid_from DATE NULL,
    valid_to DATE NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_kpi_definition_version (kpi_id, definition_version),
    KEY idx_kpi_definition_active (is_active, kpi_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kpi_target (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    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,
    created_by_user_id INT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_kpi_target_scope_period (kpi_id, scope_type, scope_id, period_from, period_to),
    KEY idx_kpi_target_lookup (kpi_id, period_from, period_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kpi_snapshot (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    kpi_id VARCHAR(40) NOT NULL,
    definition_version VARCHAR(30) NOT NULL,
    period_from DATE NOT NULL,
    period_to DATE NOT NULL,
    scope_type VARCHAR(30) NOT NULL DEFAULT 'company',
    scope_id VARCHAR(100) NULL,
    value DECIMAL(20,6) NULL,
    numerator DECIMAL(20,6) NULL,
    denominator DECIMAL(20,6) NULL,
    status ENUM('green','amber','red','neutral','na','covered') NOT NULL DEFAULT 'neutral',
    quality_status ENUM('validated','provisional','missing','failed') NOT NULL DEFAULT 'provisional',
    data_freshness_at DATETIME NULL,
    filters_json LONGTEXT NULL,
    published_by_user_id INT NULL,
    published_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_kpi_snapshot_lookup (kpi_id, period_from, period_to, scope_type, scope_id),
    KEY idx_kpi_snapshot_published (published_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dashboard_audit_log (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT NULL,
    action_key VARCHAR(100) NOT NULL,
    dashboard_key VARCHAR(40) NULL,
    entity_type VARCHAR(80) NULL,
    entity_id VARCHAR(100) NULL,
    before_json LONGTEXT NULL,
    after_json LONGTEXT NULL,
    filters_json LONGTEXT NULL,
    ip_address VARCHAR(64) NULL,
    session_key VARCHAR(128) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_dashboard_audit_user (user_id, created_at),
    KEY idx_dashboard_audit_action (action_key, created_at),
    KEY idx_dashboard_audit_dashboard (dashboard_key, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 3. Separacion entre cierre comercial y archivo del kanban
-- -----------------------------------------------------------------------------
ALTER TABLE crm_opportunities
    ADD COLUMN IF NOT EXISTS commercial_closed_at DATETIME NULL AFTER lost_reason,
    ADD COLUMN IF NOT EXISTS archived_at DATETIME NULL AFTER reopened_by_user_id,
    ADD COLUMN IF NOT EXISTS archived_by_user_id INT NULL AFTER archived_at,
    ADD COLUMN IF NOT EXISTS archive_source ENUM('manual','month_end','system','migration') NULL AFTER archived_by_user_id,
    ADD COLUMN IF NOT EXISTS archive_exempt_until DATE NULL AFTER archive_source,
    ADD COLUMN IF NOT EXISTS archive_hold_reason VARCHAR(255) NULL AFTER archive_exempt_until;

CREATE TABLE IF NOT EXISTS crm_opportunity_lifecycle_history (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    opportunity_id INT NOT NULL,
    action_key VARCHAR(80) NOT NULL,
    source_key VARCHAR(80) NOT NULL DEFAULT 'system',
    actor_user_id INT NULL,
    actor_name VARCHAR(255) NULL,
    before_json LONGTEXT NULL,
    after_json LONGTEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_opp_lifecycle_opportunity (opportunity_id, created_at),
    KEY idx_opp_lifecycle_action (action_key, created_at),
    KEY idx_opp_lifecycle_actor (actor_user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

UPDATE crm_opportunities
SET commercial_closed_at = COALESCE(commercial_closed_at, closed_at)
WHERE commercial_closed_at IS NULL AND closed_at IS NOT NULL;

UPDATE crm_opportunities
SET commercial_closed_at = COALESCE(commercial_closed_at, updated_at, created_at),
    closed_at = COALESCE(closed_at, commercial_closed_at, updated_at, created_at),
    closure_outcome = COALESCE(closure_outcome,
        CASE WHEN stage='facturada' THEN 'invoiced' WHEN stage='ganada' THEN 'won' WHEN stage='perdida' THEN 'lost' ELSE NULL END)
WHERE deleted_at IS NULL AND stage IN ('ganada','perdida','facturada');

-- Las filas ocultadas masivamente por la migracion v8.1 vuelven a sus columnas terminales.
UPDATE crm_opportunities
SET archived_at=NULL, archived_by_user_id=NULL, archive_source=NULL
WHERE deleted_at IS NULL AND closure_reason='Migración de estado terminal';

-- Los cierres manuales previos se conservan como archivo historico.
UPDATE crm_opportunities
SET archived_at=COALESCE(archived_at,closed_at,updated_at,created_at),
    archive_source=COALESCE(archive_source,'migration')
WHERE deleted_at IS NULL
  AND closed_at IS NOT NULL
  AND COALESCE(closure_reason,'')<>'Migración de estado terminal'
  AND archived_at IS NULL;

-- -----------------------------------------------------------------------------
-- 4. Configuracion, accesos y catalogo base
-- -----------------------------------------------------------------------------
INSERT INTO dashboard_settings
    (setting_key, setting_value, status, approved_by_user_id, approved_at, updated_by_user_id)
VALUES
    ('monthly_sales_target', '16000000', 'approved', 1, NOW(), 1),
    ('fixed_costs_monthly', '4500000', 'draft', NULL, NULL, 1),
    ('contribution_margin_pct', '60', 'draft', NULL, NULL, 1),
    ('corfo_baseline_sales', 'null', 'draft', NULL, NULL, 1),
    ('corfo_project_start', 'null', 'draft', NULL, NULL, 1),
    ('corfo_project_end', 'null', 'draft', NULL, NULL, 1),
    ('crm_month_end_archive_enabled', '1', 'approved', 1, NOW(), 1),
    ('crm_month_end_archive_day', '1', 'approved', 1, NOW(), 1),
    ('crm_month_end_archive_require_valid_dte', '1', 'approved', 1, NOW(), 1)
ON DUPLICATE KEY UPDATE setting_key=VALUES(setting_key);

INSERT INTO dashboard_user_access
    (user_id, dashboard_key, can_view, can_configure, can_export, granted_by_user_id)
VALUES
    (1,'general',1,1,1,1),(1,'executive',1,1,1,1),(1,'financial',1,1,1,1),
    (1,'commercial',1,1,1,1),(1,'sales',1,1,1,1),(1,'marketing',1,1,1,1),(1,'production',1,1,1,1),
    (2,'general',1,0,0,1),(2,'executive',1,0,0,1),(2,'financial',1,0,0,1),(2,'commercial',1,0,0,1),
    (3,'general',1,0,0,1),(3,'marketing',1,0,0,1),
    (5,'general',1,0,0,1),(5,'production',1,0,0,1),
    (7,'general',1,0,0,1),(7,'sales',1,0,0,1)
ON DUPLICATE KEY UPDATE can_view=VALUES(can_view), can_configure=VALUES(can_configure), can_export=VALUES(can_export), granted_by_user_id=VALUES(granted_by_user_id);

INSERT INTO kpi_definition
    (kpi_id,name,definition_version,unit,favorable_direction,owner_role,formula_text)
VALUES
    ('FIN-01','Cobertura sostenida del punto de equilibrio','1.0','%','higher','CEO/CFO','ventas_netas / (costos_fijos / margen_contribucion_pct) * 100'),
    ('COM-01','Concentración de ingresos Top 5','1.0','%','lower','CEO/Gerencia Comercial','ventas_top_5 / ventas_totales * 100'),
    ('DEM-01','Cobertura de pipeline calificado','1.0','x','higher','Gerencia Comercial','pipeline_calificado_90d / brecha_90d'),
    ('OPS-01','Tasa de pedidos perfectos','1.0','%','higher','CEO/Producción','pedidos_perfectos / pedidos_entregados * 100')
ON DUPLICATE KEY UPDATE name=VALUES(name), formula_text=VALUES(formula_text), is_active=1;

-- -----------------------------------------------------------------------------
-- 5. Indices idempotentes
-- -----------------------------------------------------------------------------
SET @db_name := DATABASE();
SET @idx_exists := (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=@db_name AND table_name='crm_opportunities' AND index_name='idx_crm_opportunities_board_archive');
SET @sql := IF(@idx_exists=0,'ALTER TABLE crm_opportunities ADD INDEX idx_crm_opportunities_board_archive (archived_at,deleted_at,stage,owner_user_id)','SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=@db_name AND table_name='crm_opportunities' AND index_name='idx_crm_opportunities_commercial_close');
SET @sql := IF(@idx_exists=0,'ALTER TABLE crm_opportunities ADD INDEX idx_crm_opportunities_commercial_close (commercial_closed_at,closure_outcome,owner_user_id)','SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=@db_name AND table_name='crm_opportunities' AND index_name='idx_crm_opportunities_month_end_archive');
SET @sql := IF(@idx_exists=0,'ALTER TABLE crm_opportunities ADD INDEX idx_crm_opportunities_month_end_archive (stage,commercial_closed_at,archived_at,archive_exempt_until)','SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- -----------------------------------------------------------------------------
-- 6. Verificacion final
-- -----------------------------------------------------------------------------
SELECT
    (SELECT COUNT(*) FROM dashboard_user_access) AS dashboard_access_rows,
    (SELECT COUNT(*) FROM kpi_definition WHERE is_active=1) AS active_kpi_definitions,
    (SELECT COUNT(*) FROM crm_opportunities WHERE deleted_at IS NULL AND archived_at IS NULL) AS visible_board_rows,
    (SELECT COUNT(*) FROM crm_opportunities WHERE deleted_at IS NULL AND archived_at IS NOT NULL) AS archived_rows,
    (SELECT COUNT(*) FROM crm_opportunities WHERE deleted_at IS NULL AND stage IN ('ganada','perdida','facturada') AND commercial_closed_at IS NULL) AS terminal_without_commercial_close,
    (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='crm_opportunities' AND column_name IN ('commercial_closed_at','archived_at','archived_by_user_id','archive_source','archive_exempt_until','archive_hold_reason')) AS lifecycle_columns,
    (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='crm_opportunity_lifecycle_history') AS lifecycle_history_table;
