-- Fase 1: Extensión de arquitectura de Gastos Fijos (dimensiones + versionado)
-- Fecha: 2026-02-28
-- Objetivo:
--  1) Añadir clasificación multidimensional al catálogo
--  2) Añadir versionado/escenarios de presupuestación
--  3) Mantener compatibilidad con APIs/UI actuales

START TRANSACTION;

USE contabilidad;

/* =========================================================
   1) Maestros de soporte (centros de costo y proveedores)
   ========================================================= */
CREATE TABLE IF NOT EXISTS gastos_fijos_centros_costo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(40) NOT NULL,
    nombre VARCHAR(150) NOT NULL,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_gf_cc_codigo (codigo),
    KEY idx_gf_cc_activo (activo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gastos_fijos_proveedores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    rif VARCHAR(25) NULL,
    nombre VARCHAR(180) NOT NULL,
    email VARCHAR(180) NULL,
    telefono VARCHAR(40) NULL,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_gf_prov_rif (rif),
    KEY idx_gf_prov_nombre (nombre),
    KEY idx_gf_prov_activo (activo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* =========================================================
   2) Dimensiones en catálogo
   ========================================================= */
SET @has_cc_id := (
  SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema='contabilidad' AND table_name='gastos_fijos_catalogo' AND column_name='centro_costo_id'
);
SET @sql_cc_id := IF(@has_cc_id=0,
  'ALTER TABLE gastos_fijos_catalogo ADD COLUMN centro_costo_id INT NULL AFTER categoria',
  'SELECT 1'
);
PREPARE stmt_cc_id FROM @sql_cc_id; EXECUTE stmt_cc_id; DEALLOCATE PREPARE stmt_cc_id;

SET @has_prov_id := (
  SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema='contabilidad' AND table_name='gastos_fijos_catalogo' AND column_name='proveedor_id'
);
SET @sql_prov_id := IF(@has_prov_id=0,
  'ALTER TABLE gastos_fijos_catalogo ADD COLUMN proveedor_id INT NULL AFTER centro_costo_id',
  'SELECT 1'
);
PREPARE stmt_prov_id FROM @sql_prov_id; EXECUTE stmt_prov_id; DEALLOCATE PREPARE stmt_prov_id;

SET @has_naturaleza := (
  SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema='contabilidad' AND table_name='gastos_fijos_catalogo' AND column_name='naturaleza_gasto'
);
SET @sql_naturaleza := IF(@has_naturaleza=0,
  "ALTER TABLE gastos_fijos_catalogo ADD COLUMN naturaleza_gasto VARCHAR(30) NULL COMMENT 'estructural|operativo|humano|tecnologico|regulatorio' AFTER proveedor_id",
  'SELECT 1'
);
PREPARE stmt_nat FROM @sql_naturaleza; EXECUTE stmt_nat; DEALLOCATE PREPARE stmt_nat;

SET @has_comportamiento := (
  SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema='contabilidad' AND table_name='gastos_fijos_catalogo' AND column_name='comportamiento_gasto'
);
SET @sql_comportamiento := IF(@has_comportamiento=0,
  "ALTER TABLE gastos_fijos_catalogo ADD COLUMN comportamiento_gasto VARCHAR(30) NULL COMMENT 'fijo|semi_fijo|indexado' AFTER naturaleza_gasto",
  'SELECT 1'
);
PREPARE stmt_comp FROM @sql_comportamiento; EXECUTE stmt_comp; DEALLOCATE PREPARE stmt_comp;

SET @has_idx_tipo := (
  SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema='contabilidad' AND table_name='gastos_fijos_catalogo' AND column_name='tipo_indexacion'
);
SET @sql_idx_tipo := IF(@has_idx_tipo=0,
  "ALTER TABLE gastos_fijos_catalogo ADD COLUMN tipo_indexacion VARCHAR(30) NULL COMMENT 'ipc|usd|commodities|manual' AFTER comportamiento_gasto",
  'SELECT 1'
);
PREPARE stmt_idx_tipo FROM @sql_idx_tipo; EXECUTE stmt_idx_tipo; DEALLOCATE PREPARE stmt_idx_tipo;

SET @has_idx_ref := (
  SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema='contabilidad' AND table_name='gastos_fijos_catalogo' AND column_name='indice_referencia'
);
SET @sql_idx_ref := IF(@has_idx_ref=0,
  'ALTER TABLE gastos_fijos_catalogo ADD COLUMN indice_referencia VARCHAR(80) NULL AFTER tipo_indexacion',
  'SELECT 1'
);
PREPARE stmt_idx_ref FROM @sql_idx_ref; EXECUTE stmt_idx_ref; DEALLOCATE PREPARE stmt_idx_ref;

SET @has_idx_pct := (
  SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema='contabilidad' AND table_name='gastos_fijos_catalogo' AND column_name='porcentaje_indexacion_anual'
);
SET @sql_idx_pct := IF(@has_idx_pct=0,
  'ALTER TABLE gastos_fijos_catalogo ADD COLUMN porcentaje_indexacion_anual DECIMAL(8,4) NULL AFTER indice_referencia',
  'SELECT 1'
);
PREPARE stmt_idx_pct FROM @sql_idx_pct; EXECUTE stmt_idx_pct; DEALLOCATE PREPARE stmt_idx_pct;

SET @has_moneda := (
  SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema='contabilidad' AND table_name='gastos_fijos_catalogo' AND column_name='moneda_base'
);
SET @sql_moneda := IF(@has_moneda=0,
  "ALTER TABLE gastos_fijos_catalogo ADD COLUMN moneda_base VARCHAR(10) NOT NULL DEFAULT 'USD' AFTER porcentaje_indexacion_anual",
  'SELECT 1'
);
PREPARE stmt_moneda FROM @sql_moneda; EXECUTE stmt_moneda; DEALLOCATE PREPARE stmt_moneda;

/* Índices */
SET @idx_cat_naturaleza := (
  SELECT COUNT(*) FROM information_schema.statistics
  WHERE table_schema='contabilidad' AND table_name='gastos_fijos_catalogo' AND index_name='idx_gf_cat_naturaleza'
);
SET @sql_idx_cat_naturaleza := IF(@idx_cat_naturaleza=0,
  'CREATE INDEX idx_gf_cat_naturaleza ON gastos_fijos_catalogo (naturaleza_gasto)',
  'SELECT 1'
);
PREPARE stmt_idx_cat_n FROM @sql_idx_cat_naturaleza; EXECUTE stmt_idx_cat_n; DEALLOCATE PREPARE stmt_idx_cat_n;

SET @idx_cat_comportamiento := (
  SELECT COUNT(*) FROM information_schema.statistics
  WHERE table_schema='contabilidad' AND table_name='gastos_fijos_catalogo' AND index_name='idx_gf_cat_comportamiento'
);
SET @sql_idx_cat_comportamiento := IF(@idx_cat_comportamiento=0,
  'CREATE INDEX idx_gf_cat_comportamiento ON gastos_fijos_catalogo (comportamiento_gasto)',
  'SELECT 1'
);
PREPARE stmt_idx_cat_c FROM @sql_idx_cat_comportamiento; EXECUTE stmt_idx_cat_c; DEALLOCATE PREPARE stmt_idx_cat_c;

/* FKs (solo si no existen) */
SET @fk_cat_cc := (
  SELECT COUNT(*) FROM information_schema.referential_constraints
  WHERE constraint_schema='contabilidad' AND constraint_name='fk_gf_catalogo_cc'
);
SET @sql_fk_cat_cc := IF(@fk_cat_cc=0,
  'ALTER TABLE gastos_fijos_catalogo ADD CONSTRAINT fk_gf_catalogo_cc FOREIGN KEY (centro_costo_id) REFERENCES gastos_fijos_centros_costo(id) ON UPDATE CASCADE ON DELETE SET NULL',
  'SELECT 1'
);
PREPARE stmt_fk_cc FROM @sql_fk_cat_cc; EXECUTE stmt_fk_cc; DEALLOCATE PREPARE stmt_fk_cc;

SET @fk_cat_prov := (
  SELECT COUNT(*) FROM information_schema.referential_constraints
  WHERE constraint_schema='contabilidad' AND constraint_name='fk_gf_catalogo_prov'
);
SET @sql_fk_cat_prov := IF(@fk_cat_prov=0,
  'ALTER TABLE gastos_fijos_catalogo ADD CONSTRAINT fk_gf_catalogo_prov FOREIGN KEY (proveedor_id) REFERENCES gastos_fijos_proveedores(id) ON UPDATE CASCADE ON DELETE SET NULL',
  'SELECT 1'
);
PREPARE stmt_fk_prov FROM @sql_fk_cat_prov; EXECUTE stmt_fk_prov; DEALLOCATE PREPARE stmt_fk_prov;

/* =========================================================
   3) Versionado y escenarios de presupuestación
   ========================================================= */
CREATE TABLE IF NOT EXISTS gastos_fijos_versiones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    anio SMALLINT NOT NULL,
    codigo VARCHAR(40) NOT NULL,
    nombre VARCHAR(120) NOT NULL,
    estado VARCHAR(20) NOT NULL DEFAULT 'draft' COMMENT 'draft|approved|archived',
    es_base TINYINT(1) NOT NULL DEFAULT 0,
    created_by VARCHAR(120) NULL,
    approved_by VARCHAR(120) NULL,
    approved_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_gf_version_codigo (codigo),
    KEY idx_gf_version_anio (anio),
    KEY idx_gf_version_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gastos_fijos_escenarios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    version_id INT NOT NULL,
    codigo VARCHAR(40) NOT NULL,
    nombre VARCHAR(120) NOT NULL,
    ajuste_global_pct DECIMAL(8,4) NOT NULL DEFAULT 0.0000,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_gf_escenario_version_codigo (version_id, codigo),
    KEY idx_gf_escenario_activo (activo),
    CONSTRAINT fk_gf_escenario_version FOREIGN KEY (version_id)
        REFERENCES gastos_fijos_versiones(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* =========================================================
   4) Enlaces en periodos para versionado/escenarios
   ========================================================= */
SET @has_version_id := (
  SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema='contabilidad' AND table_name='gastos_fijos_periodos' AND column_name='version_id'
);
SET @sql_version_id := IF(@has_version_id=0,
  'ALTER TABLE gastos_fijos_periodos ADD COLUMN version_id INT NULL AFTER periodo',
  'SELECT 1'
);
PREPARE stmt_version_id FROM @sql_version_id; EXECUTE stmt_version_id; DEALLOCATE PREPARE stmt_version_id;

SET @has_escenario_id := (
  SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema='contabilidad' AND table_name='gastos_fijos_periodos' AND column_name='escenario_id'
);
SET @sql_escenario_id := IF(@has_escenario_id=0,
  'ALTER TABLE gastos_fijos_periodos ADD COLUMN escenario_id INT NULL AFTER version_id',
  'SELECT 1'
);
PREPARE stmt_escenario_id FROM @sql_escenario_id; EXECUTE stmt_escenario_id; DEALLOCATE PREPARE stmt_escenario_id;

SET @idx_gp_version := (
  SELECT COUNT(*) FROM information_schema.statistics
  WHERE table_schema='contabilidad' AND table_name='gastos_fijos_periodos' AND index_name='idx_gf_periodo_version'
);
SET @sql_idx_gp_version := IF(@idx_gp_version=0,
  'CREATE INDEX idx_gf_periodo_version ON gastos_fijos_periodos (version_id)',
  'SELECT 1'
);
PREPARE stmt_idx_gp_version FROM @sql_idx_gp_version; EXECUTE stmt_idx_gp_version; DEALLOCATE PREPARE stmt_idx_gp_version;

SET @idx_gp_escenario := (
  SELECT COUNT(*) FROM information_schema.statistics
  WHERE table_schema='contabilidad' AND table_name='gastos_fijos_periodos' AND index_name='idx_gf_periodo_escenario'
);
SET @sql_idx_gp_escenario := IF(@idx_gp_escenario=0,
  'CREATE INDEX idx_gf_periodo_escenario ON gastos_fijos_periodos (escenario_id)',
  'SELECT 1'
);
PREPARE stmt_idx_gp_escenario FROM @sql_idx_gp_escenario; EXECUTE stmt_idx_gp_escenario; DEALLOCATE PREPARE stmt_idx_gp_escenario;

SET @fk_gp_version := (
  SELECT COUNT(*) FROM information_schema.referential_constraints
  WHERE constraint_schema='contabilidad' AND constraint_name='fk_gf_periodo_version'
);
SET @sql_fk_gp_version := IF(@fk_gp_version=0,
  'ALTER TABLE gastos_fijos_periodos ADD CONSTRAINT fk_gf_periodo_version FOREIGN KEY (version_id) REFERENCES gastos_fijos_versiones(id) ON UPDATE CASCADE ON DELETE SET NULL',
  'SELECT 1'
);
PREPARE stmt_fk_gp_version FROM @sql_fk_gp_version; EXECUTE stmt_fk_gp_version; DEALLOCATE PREPARE stmt_fk_gp_version;

SET @fk_gp_escenario := (
  SELECT COUNT(*) FROM information_schema.referential_constraints
  WHERE constraint_schema='contabilidad' AND constraint_name='fk_gf_periodo_escenario'
);
SET @sql_fk_gp_escenario := IF(@fk_gp_escenario=0,
  'ALTER TABLE gastos_fijos_periodos ADD CONSTRAINT fk_gf_periodo_escenario FOREIGN KEY (escenario_id) REFERENCES gastos_fijos_escenarios(id) ON UPDATE CASCADE ON DELETE SET NULL',
  'SELECT 1'
);
PREPARE stmt_fk_gp_escenario FROM @sql_fk_gp_escenario; EXECUTE stmt_fk_gp_escenario; DEALLOCATE PREPARE stmt_fk_gp_escenario;

/* =========================================================
   5) Semillas mínimas
   ========================================================= */
INSERT INTO gastos_fijos_versiones (anio, codigo, nombre, estado, es_base, created_by)
SELECT YEAR(CURDATE()), CONCAT('BASE-', YEAR(CURDATE())), CONCAT('Base ', YEAR(CURDATE())), 'approved', 1, 'system'
WHERE NOT EXISTS (
  SELECT 1 FROM gastos_fijos_versiones WHERE codigo = CONCAT('BASE-', YEAR(CURDATE()))
);

INSERT INTO gastos_fijos_escenarios (version_id, codigo, nombre, ajuste_global_pct, activo)
SELECT v.id, 'REAL', 'Escenario Real', 0.0000, 1
FROM gastos_fijos_versiones v
WHERE v.codigo = CONCAT('BASE-', YEAR(CURDATE()))
  AND NOT EXISTS (
    SELECT 1 FROM gastos_fijos_escenarios e WHERE e.version_id = v.id AND e.codigo = 'REAL'
  );

COMMIT;
