-- Migración inicial para módulo de gastos fijos normalizado
-- Fecha: 2025-10-03
-- Ejecutar en la base de datos `admin` (usar: USE admin;)
-- IMPORTANTE: Revisar y adaptar antes de producción.
-- Modo seguro: primero ejecutar en staging.

/* ###############################################################
   1. PREPARACIÓN
################################################################ */
-- (Opcional) Crear un schema de respaldo lógico rápido
-- mysqldump -u usuario -p admin gastos_fijos > backup_gastos_fijos_$(date +%F).sql

START TRANSACTION;

/* ###############################################################
   2. NUEVAS TABLAS
################################################################ */

-- Catálogo de gastos fijos
CREATE TABLE IF NOT EXISTS gastos_fijos_catalogo (
  id INT AUTO_INCREMENT PRIMARY KEY,
  codigo VARCHAR(50) NOT NULL UNIQUE,
  descripcion VARCHAR(255) NOT NULL,
  categoria_id INT NULL,
  moneda CHAR(3) NOT NULL DEFAULT 'USD',
  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,
  INDEX idx_activo (activo),
  INDEX idx_categoria (categoria_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Periodos mensuales de presupuesto
CREATE TABLE IF NOT EXISTS gastos_fijos_periodos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  gasto_fijo_id INT NOT NULL,
  periodo CHAR(7) NOT NULL, -- Formato YYYY-MM
  monto_presupuestado DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  monto_ejecutado DECIMAL(12,2) NULL DEFAULT NULL,  -- Cache opcional
  rollover_anterior DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  cerrado TINYINT(1) NOT NULL DEFAULT 0,
  notas VARCHAR(300) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_gasto_periodo (gasto_fijo_id, periodo),
  INDEX idx_periodo (periodo),
  INDEX idx_cerrado (cerrado),
  CONSTRAINT fk_periodo_gasto_catalogo FOREIGN KEY (gasto_fijo_id) REFERENCES gastos_fijos_catalogo(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Ajustes (opcional para auditoría)
CREATE TABLE IF NOT EXISTS gastos_fijos_ajustes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  gastos_fijos_periodo_id INT NOT NULL,
  tipo ENUM('INCREMENTO','DISMINUCION','ROLLOVER','AJUSTE_CAMBIO') NOT NULL,
  monto DECIMAL(12,2) NOT NULL,
  comentario VARCHAR(255) NULL,
  usuario VARCHAR(80) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_tipo (tipo),
  INDEX idx_periodo_ref (gastos_fijos_periodo_id),
  CONSTRAINT fk_ajuste_periodo FOREIGN KEY (gastos_fijos_periodo_id) REFERENCES gastos_fijos_periodos(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Categorías (crear sólo si no existe algo similar)
CREATE TABLE IF NOT EXISTS gastos_categorias (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(100) NOT NULL UNIQUE,
  color VARCHAR(7) NULL,
  orden INT DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* ###############################################################
   3. MIGRACIÓN DE DATOS DESDE TABLA ACTUAL
################################################################ */
-- Asumimos tabla existente: admin.gastos_fijos
-- Reglas:
--  - Filas con descripcion vacía se ignoran.
--  - Descripciones duplicadas se consolidan: primer registro define el catalogo.
--  - periodo = DATE_FORMAT(fecha, '%Y-%m')
--  - monto_presupuestado = cantidad_asignada
--  - monto_ejecutado se deja NULL (se recalculará en consultas) o se puede copiar cantidad_usada si se desea snapshot.

-- Tabla temporal para normalizar descripciones
CREATE TEMPORARY TABLE tmp_gastos_fijos_src AS
SELECT id, descripcion, cantidad_asignada, cantidad_usada, fecha
FROM gastos_fijos
WHERE descripcion IS NOT NULL AND descripcion <> '';

-- Insertar catálogo (una fila por descripcion única)
INSERT INTO gastos_fijos_catalogo (codigo, descripcion)
SELECT DISTINCT LOWER(REPLACE(REPLACE(TRIM(descripcion), ' ', '_'), '-', '_')) AS codigo,
       descripcion
FROM tmp_gastos_fijos_src
ORDER BY descripcion;

-- Insertar periodos
INSERT INTO gastos_fijos_periodos (gasto_fijo_id, periodo, monto_presupuestado, monto_ejecutado, rollover_anterior, cerrado, notas)
SELECT c.id AS gasto_fijo_id,
       DATE_FORMAT(s.fecha, '%Y-%m') AS periodo,
       s.cantidad_asignada AS monto_presupuestado,
       NULL AS monto_ejecutado, -- O usar s.cantidad_usada si quieres snapshot inicial
       0.00 AS rollover_anterior,
       0 AS cerrado,
       CONCAT('Migrado desde gastos_fijos.id=', s.id) AS notas
FROM tmp_gastos_fijos_src s
JOIN gastos_fijos_catalogo c ON c.descripcion = s.descripcion
ON DUPLICATE KEY UPDATE monto_presupuestado = VALUES(monto_presupuestado);

/* ###############################################################
   4. (OPCIONAL) RESPALDO Y/O RENOMBRAR TABLA ORIGINAL
################################################################ */
-- Puedes elegir entre:
--  A) Renombrar original para referencia histórica
--      RENAME TABLE gastos_fijos TO gastos_fijos_legacy;
--  B) Mantenerla sin cambios temporalmente
--  C) Eliminarla luego de validar
-- Por seguridad no eliminamos automáticamente.

/* ###############################################################
   5. VISTA DE EJECUCIÓN (on-demand)
################################################################ */
DROP VIEW IF EXISTS vw_gastos_fijos_ejecucion;
CREATE VIEW vw_gastos_fijos_ejecucion AS
SELECT 
  gf.id AS gasto_fijo_id,
  gf.codigo,
  gf.descripcion,
  gp.periodo,
  gp.monto_presupuestado,
  gp.rollover_anterior,
  gp.cerrado,
  COALESCE(SUM(
    CASE 
      WHEN LOWER(g.banco) IN ('mercantil','venezuela') THEN 
        g.monto / (
          SELECT td.tasa_oficial 
          FROM contabilidad.tasas_dolar td 
          WHERE td.fecha <= g.fecha 
          ORDER BY td.fecha DESC 
          LIMIT 1
        )
      ELSE g.monto
    END
  ),0) AS monto_ejecutado_calculado,
  (gp.monto_presupuestado + gp.rollover_anterior) AS monto_disponible,
  (gp.monto_presupuestado + gp.rollover_anterior) - COALESCE(SUM(
    CASE 
      WHEN LOWER(g.banco) IN ('mercantil','venezuela') THEN 
        g.monto / (
          SELECT td.tasa_oficial 
          FROM contabilidad.tasas_dolar td 
          WHERE td.fecha <= g.fecha 
          ORDER BY td.fecha DESC 
          LIMIT 1
        )
      ELSE g.monto
    END
  ),0) AS monto_restante
FROM gastos_fijos_catalogo gf
JOIN gastos_fijos_periodos gp ON gp.gasto_fijo_id = gf.id
LEFT JOIN contabilidad.gastos g 
  ON g.grupo = gf.codigo
  AND DATE_FORMAT(g.fecha,'%Y-%m') = gp.periodo
GROUP BY gf.id, gp.periodo;

/* ###############################################################
   6. ÍNDICES RECOMENDADOS EXTRA (después de validar volumen)
################################################################ */
-- ALTER TABLE contabilidad.gastos ADD INDEX idx_grupo_fecha (grupo, fecha);
-- ALTER TABLE contabilidad.tasas_dolar ADD INDEX idx_fecha (fecha);

/* ###############################################################
   7. CONFIRMAR
################################################################ */
COMMIT;

/* ###############################################################
   8. ROLLBACK MANUAL (si algo sale mal y aún no hiciste otros cambios)
################################################################ */
-- Si necesitas revertir:
-- DROP VIEW IF EXISTS vw_gastos_fijos_ejecucion;
-- DROP TABLE IF EXISTS gastos_fijos_ajustes;
-- DROP TABLE IF EXISTS gastos_fijos_periodos;
-- DROP TABLE IF EXISTS gastos_fijos_catalogo;
-- (Tu tabla original gastos_fijos seguiría intacta si no la renombraste/eliminaste.)

/* ###############################################################
   9. CONSULTAS DE VALIDACIÓN POST-MIGRACIÓN
################################################################ */
-- 1) Conteo catálogo vs descripciones originales
-- SELECT COUNT(DISTINCT descripcion) FROM gastos_fijos; -- original
-- SELECT COUNT(*) FROM gastos_fijos_catalogo; -- debe coincidir

-- 2) Ver periodos migrados
-- SELECT * FROM gastos_fijos_periodos ORDER BY periodo, gasto_fijo_id LIMIT 20;

-- 3) Ver ejecución on-demand
-- SELECT * FROM vw_gastos_fijos_ejecucion WHERE periodo = '2024-09' ORDER BY monto_ejecutado_calculado DESC;

-- 4) Detectar sobre-ejecuciones
-- SELECT *, ROUND((monto_ejecutado_calculado / NULLIF(monto_presupuestado + rollover_anterior,0))*100,2) AS pct
-- FROM vw_gastos_fijos_ejecucion
-- WHERE monto_ejecutado_calculado > (monto_presupuestado + rollover_anterior);

-- 5) Gastos sin presupuesto (si agregas nuevos grupos en contabilidad.gastos)
-- SELECT DISTINCT g.grupo FROM contabilidad.gastos g
-- LEFT JOIN gastos_fijos_catalogo c ON c.codigo = g.grupo
-- WHERE c.id IS NULL AND g.grupo IS NOT NULL AND g.grupo <> '';

