-- ============================================
-- MIGRACIÓN: Agregar tabla boletas_detalle
-- Fecha: 2026-01-23
-- Descripción: Tabla hija de boletas para cargos dinámicos
-- ============================================

-- ============================================
-- PASO 1: CREAR TABLA CATÁLOGO DE TIPOS DE CARGO
-- ============================================

CREATE TABLE IF NOT EXISTS tipos_cargo (
    id_tipo_cargo INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(30) NOT NULL UNIQUE,
    nombre VARCHAR(100) NOT NULL,
    descripcion VARCHAR(255) DEFAULT NULL,
    es_descuento TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0=suma al total, 1=resta del total',
    activo TINYINT(1) NOT NULL DEFAULT 1,
    orden_impresion INT NOT NULL DEFAULT 0 COMMENT 'Orden en que aparece en la boleta',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

-- ============================================
-- PASO 2: INSERTAR TIPOS DE CARGO BASE
-- ============================================

INSERT INTO tipos_cargo (codigo, nombre, descripcion, es_descuento, orden_impresion) VALUES
('CONSUMO_BASE', 'Consumo Base', 'Consumo de agua tramo base (0-20 m³)', 0, 1),
('CONSUMO_TRAMO1', 'Consumo Tramo 1', 'Sobreconsumo tramo 1 (21+ m³)', 0, 2),
('CONSUMO_TRAMO2', 'Consumo Tramo 2', 'Sobreconsumo tramo 2', 0, 3),
('CONSUMO_TRAMO3', 'Consumo Tramo 3', 'Sobreconsumo tramo 3', 0, 4),
('CARGO_FIJO', 'Cargo Fijo', 'Cargo fijo mensual por servicio', 0, 5),
('ALCANTARILLADO', 'Alcantarillado', 'Cargo por servicio de alcantarillado', 0, 6),
('CUOTA_MORTUORIA', 'Cuota Mortuoria', 'Aporte a fondo mortuorio', 0, 7),
('DONACION', 'Donación', 'Donaciones voluntarias', 0, 8),
('MULTA_ATRASO', 'Multa por Atraso', 'Multa por mora en pago', 0, 9),
('MULTA_CORTE', 'Multa por Corte', 'Cargo por corte de servicio', 0, 10),
('MULTA_MATRIZ', 'Multa Matriz', 'Multa por daño a matriz', 0, 11),
('REPACTACION', 'Cuota Repactación', 'Cuota de convenio de pago', 0, 12),
('SUBSIDIO', 'Subsidio Estatal', 'Descuento por subsidio estatal', 1, 13),
('SUBSIDIO_CARGO_FIJO', 'Subsidio Cargo Fijo', 'Subsidio aplicado al cargo fijo', 1, 14),
('SALDO_ANTERIOR', 'Saldo Anterior', 'Deuda de períodos anteriores', 0, 15),
('CONDONACION', 'Condonación', 'Descuento por condonación de deuda', 1, 16);

-- ============================================
-- PASO 3: CREAR TABLA BOLETAS_DETALLE
-- ============================================

CREATE TABLE IF NOT EXISTS boletas_detalle (
    id_detalle INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_boleta INT UNSIGNED NOT NULL,
    id_tipo_cargo INT UNSIGNED NOT NULL,
    descripcion VARCHAR(100) DEFAULT NULL COMMENT 'Descripción adicional opcional',
    cantidad DECIMAL(10,2) NOT NULL DEFAULT 1.00 COMMENT 'Cantidad (ej: m³ de consumo)',
    valor_unitario INT NOT NULL DEFAULT 0 COMMENT 'Precio unitario',
    monto INT NOT NULL DEFAULT 0 COMMENT 'Monto total del cargo',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_boleta_detalle_boleta (id_boleta),
    INDEX idx_boleta_detalle_tipo (id_tipo_cargo),
    
    CONSTRAINT fk_boleta_detalle_boleta 
        FOREIGN KEY (id_boleta) REFERENCES boletas(id_boleta) ON DELETE CASCADE,
    CONSTRAINT fk_boleta_detalle_tipo 
        FOREIGN KEY (id_tipo_cargo) REFERENCES tipos_cargo(id_tipo_cargo)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

-- ============================================
-- PASO 4: CREAR PROCEDIMIENTO PARA INSERTAR DETALLE
-- ============================================

DROP PROCEDURE IF EXISTS insertar_boleta_detalle;

DELIMITER //

CREATE PROCEDURE insertar_boleta_detalle(
    IN p_id_boleta INT,
    IN p_codigo_cargo VARCHAR(30),
    IN p_cantidad DECIMAL(10,2),
    IN p_valor_unitario INT,
    IN p_monto INT,
    IN p_descripcion VARCHAR(100)
)
BEGIN
    DECLARE v_id_tipo_cargo INT;
    
    SELECT id_tipo_cargo INTO v_id_tipo_cargo 
    FROM tipos_cargo 
    WHERE codigo = p_codigo_cargo AND activo = 1;
    
    IF v_id_tipo_cargo IS NOT NULL AND p_monto != 0 THEN
        INSERT INTO boletas_detalle (id_boleta, id_tipo_cargo, descripcion, cantidad, valor_unitario, monto)
        VALUES (p_id_boleta, v_id_tipo_cargo, p_descripcion, p_cantidad, p_valor_unitario, p_monto);
    END IF;
END //

DELIMITER ;

-- ============================================
-- PASO 5: CREAR VISTA PARA CONSULTAR DETALLE CON NOMBRES
-- ============================================

CREATE OR REPLACE VIEW vista_boletas_detalle AS
SELECT 
    bd.id_detalle,
    bd.id_boleta,
    tc.codigo,
    tc.nombre AS tipo_cargo,
    tc.es_descuento,
    tc.orden_impresion,
    bd.descripcion,
    bd.cantidad,
    bd.valor_unitario,
    bd.monto,
    bd.created_at
FROM boletas_detalle bd
JOIN tipos_cargo tc ON bd.id_tipo_cargo = tc.id_tipo_cargo
ORDER BY bd.id_boleta, tc.orden_impresion;

-- ============================================
-- PASO 6: MIGRAR DATOS EXISTENTES (OPCIONAL)
-- ============================================
-- Este paso migra los cargos de boletas existentes a la nueva tabla
-- Ejecutar solo si se desea tener histórico completo

DROP PROCEDURE IF EXISTS migrar_boletas_a_detalle;

DELIMITER //

CREATE PROCEDURE migrar_boletas_a_detalle()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id_boleta INT;
    DECLARE v_cargo_fijo INT;
    DECLARE v_alcantarillado INT;
    DECLARE v_cuota_mortuoria INT;
    DECLARE v_donaciones INT;
    DECLARE v_multa_atraso INT;
    DECLARE v_multa_corte INT;
    DECLARE v_multa_matrz INT;
    DECLARE v_tramo_base INT;
    DECLARE v_tramo1 INT;
    DECLARE v_tramo2 INT;
    DECLARE v_tramo3 INT;
    DECLARE v_subsidio INT;
    DECLARE v_saldo_anterior INT;
    DECLARE v_repactaciones INT;
    DECLARE v_cosumo_m3 INT;
    
    DECLARE cur CURSOR FOR 
        SELECT id_boleta, cargo_fijo, alcantarillado, cuota_mortuoria, donaciones,
               multa_atraso, multa_corte, multa_matrz, tramo_base, tramo1, tramo2, tramo3,
               subsidio, saldo_anterior, repactaciones, cosumo_m3
        FROM boletas 
        WHERE id_boleta NOT IN (SELECT DISTINCT id_boleta FROM boletas_detalle);
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO v_id_boleta, v_cargo_fijo, v_alcantarillado, v_cuota_mortuoria,
                       v_donaciones, v_multa_atraso, v_multa_corte, v_multa_matrz,
                       v_tramo_base, v_tramo1, v_tramo2, v_tramo3, v_subsidio,
                       v_saldo_anterior, v_repactaciones, v_cosumo_m3;
        
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Insertar cada cargo si tiene valor
        IF v_tramo_base > 0 THEN
            CALL insertar_boleta_detalle(v_id_boleta, 'CONSUMO_BASE', LEAST(v_cosumo_m3, 20), 600, v_tramo_base, NULL);
        END IF;
        
        IF v_tramo1 > 0 THEN
            CALL insertar_boleta_detalle(v_id_boleta, 'CONSUMO_TRAMO1', GREATEST(v_cosumo_m3 - 20, 0), 750, v_tramo1, NULL);
        END IF;
        
        IF v_tramo2 > 0 THEN
            CALL insertar_boleta_detalle(v_id_boleta, 'CONSUMO_TRAMO2', 1, 0, v_tramo2, NULL);
        END IF;
        
        IF v_tramo3 > 0 THEN
            CALL insertar_boleta_detalle(v_id_boleta, 'CONSUMO_TRAMO3', 1, 0, v_tramo3, NULL);
        END IF;
        
        IF v_cargo_fijo > 0 THEN
            CALL insertar_boleta_detalle(v_id_boleta, 'CARGO_FIJO', 1, v_cargo_fijo, v_cargo_fijo, NULL);
        END IF;
        
        IF v_alcantarillado > 0 THEN
            CALL insertar_boleta_detalle(v_id_boleta, 'ALCANTARILLADO', 1, v_alcantarillado, v_alcantarillado, NULL);
        END IF;
        
        IF v_cuota_mortuoria > 0 THEN
            CALL insertar_boleta_detalle(v_id_boleta, 'CUOTA_MORTUORIA', 1, v_cuota_mortuoria, v_cuota_mortuoria, NULL);
        END IF;
        
        IF v_donaciones > 0 THEN
            CALL insertar_boleta_detalle(v_id_boleta, 'DONACION', 1, v_donaciones, v_donaciones, NULL);
        END IF;
        
        IF v_multa_atraso > 0 THEN
            CALL insertar_boleta_detalle(v_id_boleta, 'MULTA_ATRASO', 1, v_multa_atraso, v_multa_atraso, NULL);
        END IF;
        
        IF v_multa_corte > 0 THEN
            CALL insertar_boleta_detalle(v_id_boleta, 'MULTA_CORTE', 1, v_multa_corte, v_multa_corte, NULL);
        END IF;
        
        IF v_multa_matrz > 0 THEN
            CALL insertar_boleta_detalle(v_id_boleta, 'MULTA_MATRIZ', 1, v_multa_matrz, v_multa_matrz, NULL);
        END IF;
        
        IF v_repactaciones > 0 THEN
            CALL insertar_boleta_detalle(v_id_boleta, 'REPACTACION', 1, v_repactaciones, v_repactaciones, NULL);
        END IF;
        
        IF v_subsidio > 0 THEN
            CALL insertar_boleta_detalle(v_id_boleta, 'SUBSIDIO', 1, v_subsidio, v_subsidio, NULL);
        END IF;
        
        IF v_saldo_anterior > 0 THEN
            CALL insertar_boleta_detalle(v_id_boleta, 'SALDO_ANTERIOR', 1, v_saldo_anterior, v_saldo_anterior, NULL);
        END IF;
        
    END LOOP;
    
    CLOSE cur;
    
    SELECT CONCAT('Migración completada. Boletas procesadas: ', ROW_COUNT()) AS resultado;
END //

DELIMITER ;

-- ============================================
-- VERIFICACIÓN
-- ============================================
-- Ejecutar después de la migración:
-- SELECT * FROM tipos_cargo;
-- SELECT COUNT(*) FROM boletas_detalle;
-- SELECT * FROM vista_boletas_detalle WHERE id_boleta = (SELECT MAX(id_boleta) FROM boletas);

-- Para migrar datos históricos ejecutar:
-- CALL migrar_boletas_a_detalle();