-- ============================================================================
-- SCRIPT DE CREACIÓN DE TABLAS PARA MÓDULO DE CONCILIACIÓN BANCARIA
-- ============================================================================

-- Tabla Principal de Conciliaciones
CREATE TABLE IF NOT EXISTS conciliacion_bancaria (
    id_conciliacion INT AUTO_INCREMENT PRIMARY KEY,
    id_empresa INT NOT NULL,
    id_cuenta_bancaria INT NOT NULL,
    fecha_inicio DATE NOT NULL,
    fecha_fin DATE NOT NULL,
    saldo_extracto DECIMAL(15,2) DEFAULT 0,
    saldo_sistema_calculado DECIMAL(15,2) NULL,
    diferencia DECIMAL(15,2) NULL,
    estado ENUM('PENDIENTE', 'FINALIZADA') DEFAULT 'PENDIENTE',
    usuario_creacion INT,
    usuario_finalizacion INT NULL,
    fecha_creacion DATETIME DEFAULT CURRENT_TIMESTAMP,
    fecha_finalizacion DATETIME NULL,
    observaciones TEXT NULL,
    FOREIGN KEY (id_cuenta_bancaria) REFERENCES cuentas_bancarias(id_cuenta_bancaria),
    INDEX idx_empresa (id_empresa),
    INDEX idx_cuenta (id_cuenta_bancaria),
    INDEX idx_estado (estado),
    INDEX idx_fecha (fecha_creacion)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla Maestro de PDA (Partida Doble de Asientos)
CREATE TABLE IF NOT EXISTS cb_pda_maestro (
    id_pda INT AUTO_INCREMENT PRIMARY KEY,
    id_conciliacion INT NOT NULL,
    id_movimiento INT NOT NULL,
    fecha_creacion DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_conciliacion) REFERENCES conciliacion_bancaria(id_conciliacion) ON DELETE CASCADE,
    FOREIGN KEY (id_movimiento) REFERENCES movimientos_bancos(id_movimiento) ON DELETE CASCADE,
    UNIQUE KEY unique_pda (id_conciliacion, id_movimiento),
    INDEX idx_conciliacion (id_conciliacion)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla Detalle de PDA
CREATE TABLE IF NOT EXISTS cb_pda_detalle (
    id_detalle INT AUTO_INCREMENT PRIMARY KEY,
    id_pda INT NOT NULL,
    id_conciliacion INT NOT NULL,
    id_movimiento INT NOT NULL,
    monto_extracto DECIMAL(15,2) NOT NULL,
    monto_sistema DECIMAL(15,2) NOT NULL,
    diferencia DECIMAL(15,2) NOT NULL,
    estado_item ENUM('CONCILIADO', 'PENDIENTE', 'RECHAZADO') DEFAULT 'PENDIENTE',
    fecha_creacion DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_pda) REFERENCES cb_pda_maestro(id_pda) ON DELETE CASCADE,
    FOREIGN KEY (id_conciliacion) REFERENCES conciliacion_bancaria(id_conciliacion) ON DELETE CASCADE,
    FOREIGN KEY (id_movimiento) REFERENCES movimientos_bancos(id_movimiento) ON DELETE CASCADE,
    INDEX idx_pda (id_pda),
    INDEX idx_conciliacion (id_conciliacion),
    INDEX idx_estado (estado_item)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de Extractos Bancarios (para cargar desde archivos)
CREATE TABLE IF NOT EXISTS cb_extractos_bancarios (
    id_extracto INT AUTO_INCREMENT PRIMARY KEY,
    id_conciliacion INT NOT NULL,
    id_cuenta_bancaria INT NOT NULL,
    fecha_movimiento DATE NOT NULL,
    tipo_movimiento VARCHAR(20) NOT NULL,
    referencia VARCHAR(100),
    descripcion TEXT,
    monto DECIMAL(15,2) NOT NULL,
    saldo_disponible DECIMAL(15,2) NULL,
    numero_secuencia VARCHAR(50),
    fecha_carga DATETIME DEFAULT CURRENT_TIMESTAMP,
    usuario_carga INT,
    FOREIGN KEY (id_conciliacion) REFERENCES conciliacion_bancaria(id_conciliacion),
    FOREIGN KEY (id_cuenta_bancaria) REFERENCES cuentas_bancarias(id_cuenta_bancaria),
    INDEX idx_conciliacion (id_conciliacion),
    INDEX idx_cuenta (id_cuenta_bancaria),
    INDEX idx_fecha (fecha_movimiento)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Agregar columna fecha_conciliacion a movimientos_bancos si no existe
SET @cb_col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'movimientos_bancos'
      AND COLUMN_NAME = 'fecha_conciliacion'
);
SET @cb_sql := IF(
    @cb_col_exists = 0,
    'ALTER TABLE movimientos_bancos ADD COLUMN fecha_conciliacion DATETIME NULL AFTER conciliado',
    'SELECT 1'
);
PREPARE cb_stmt FROM @cb_sql;
EXECUTE cb_stmt;
DEALLOCATE PREPARE cb_stmt;

-- Crear vistas útiles

-- Vista: Resumen de Conciliaciones
CREATE OR REPLACE VIEW vw_resumen_conciliaciones AS
SELECT 
    cb.id_conciliacion,
    cb.id_empresa,
    cb.id_cuenta_bancaria,
    cta.numero_cuenta,
    b.nombre_banco,
    cb.fecha_inicio,
    cb.fecha_fin,
    cb.saldo_extracto,
    cb.saldo_sistema_calculado,
    cb.diferencia,
    cb.estado,
    COUNT(DISTINCT pd.id_pda) as total_movimientos,
    SUM(CASE WHEN pd.estado_item = 'CONCILIADO' THEN 1 ELSE 0 END) as movimientos_conciliados,
    SUM(CASE WHEN pd.estado_item = 'PENDIENTE' THEN 1 ELSE 0 END) as movimientos_pendientes,
    CASE 
        WHEN cb.estado = 'FINALIZADA' AND cb.diferencia = 0 THEN 'PERFECTA'
        WHEN cb.estado = 'FINALIZADA' AND cb.diferencia != 0 THEN 'CON_DIFERENCIA'
        ELSE 'PENDIENTE'
    END as tipo_conciliacion,
    cb.fecha_creacion,
    cb.fecha_finalizacion
FROM conciliacion_bancaria cb
JOIN cuentas_bancarias cta ON cb.id_cuenta_bancaria = cta.id_cuenta_bancaria
JOIN bancos b ON cta.id_banco = b.id_banco
LEFT JOIN cb_pda_detalle pd ON cb.id_conciliacion = pd.id_conciliacion
GROUP BY cb.id_conciliacion, cb.id_empresa, cb.id_cuenta_bancaria;

-- Vista: Diferencias sin Conciliar
CREATE OR REPLACE VIEW vw_diferencias_no_conciliadas AS
SELECT 
    pd.id_detalle,
    pd.id_conciliacion,
    pd.id_movimiento,
    cb.id_cuenta_bancaria,
    mb.fecha_movimiento,
    mb.referencia,
    mb.concepto,
    mb.tipo_movimiento,
    pd.monto_extracto,
    pd.monto_sistema,
    pd.diferencia,
    pd.estado_item,
    (pd.monto_extracto - pd.monto_sistema) as diferencia_calculada
FROM cb_pda_detalle pd
JOIN conciliacion_bancaria cb ON pd.id_conciliacion = cb.id_conciliacion
JOIN movimientos_bancos mb ON pd.id_movimiento = mb.id_movimiento
WHERE pd.estado_item = 'PENDIENTE'
ORDER BY cb.fecha_creacion DESC, mb.fecha_movimiento;

-- Vista: Movimientos Conciliados por Período
CREATE OR REPLACE VIEW vw_movimientos_conciliados_periodo AS
SELECT 
    cb.id_conciliacion,
    cb.id_empresa,
    cb.id_cuenta_bancaria,
    b.nombre_banco,
    cta.numero_cuenta,
    cb.fecha_inicio,
    cb.fecha_fin,
    mb.id_movimiento,
    mb.fecha_movimiento,
    mb.tipo_movimiento,
    mb.referencia,
    mb.concepto,
    mb.monto,
    mb.conciliado,
    pd.monto_extracto,
    pd.monto_sistema,
    pd.diferencia,
    pd.estado_item
FROM conciliacion_bancaria cb
JOIN cuentas_bancarias cta ON cb.id_cuenta_bancaria = cta.id_cuenta_bancaria
JOIN bancos b ON cta.id_banco = b.id_banco
JOIN cb_pda_maestro pm ON cb.id_conciliacion = pm.id_conciliacion
JOIN cb_pda_detalle pd ON pm.id_pda = pd.id_pda
JOIN movimientos_bancos mb ON pm.id_movimiento = mb.id_movimiento
ORDER BY cb.fecha_inicio, mb.fecha_movimiento;

-- ============================================================================
-- INDICES PARA OPTIMIZACIÓN
-- ============================================================================

SET @idx_exists := (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'conciliacion_bancaria' AND INDEX_NAME = 'idx_conciliacion_bancaria_empresa');
SET @idx_sql := IF(@idx_exists = 0, 'CREATE INDEX idx_conciliacion_bancaria_empresa ON conciliacion_bancaria(id_empresa)', 'SELECT 1');
PREPARE cb_idx_stmt FROM @idx_sql; EXECUTE cb_idx_stmt; DEALLOCATE PREPARE cb_idx_stmt;

SET @idx_exists := (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'conciliacion_bancaria' AND INDEX_NAME = 'idx_conciliacion_bancaria_cuenta');
SET @idx_sql := IF(@idx_exists = 0, 'CREATE INDEX idx_conciliacion_bancaria_cuenta ON conciliacion_bancaria(id_cuenta_bancaria)', 'SELECT 1');
PREPARE cb_idx_stmt FROM @idx_sql; EXECUTE cb_idx_stmt; DEALLOCATE PREPARE cb_idx_stmt;

SET @idx_exists := (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'conciliacion_bancaria' AND INDEX_NAME = 'idx_conciliacion_bancaria_estado');
SET @idx_sql := IF(@idx_exists = 0, 'CREATE INDEX idx_conciliacion_bancaria_estado ON conciliacion_bancaria(estado)', 'SELECT 1');
PREPARE cb_idx_stmt FROM @idx_sql; EXECUTE cb_idx_stmt; DEALLOCATE PREPARE cb_idx_stmt;

SET @idx_exists := (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'cb_pda_detalle' AND INDEX_NAME = 'idx_cb_pda_detalle_conciliacion');
SET @idx_sql := IF(@idx_exists = 0, 'CREATE INDEX idx_cb_pda_detalle_conciliacion ON cb_pda_detalle(id_conciliacion)', 'SELECT 1');
PREPARE cb_idx_stmt FROM @idx_sql; EXECUTE cb_idx_stmt; DEALLOCATE PREPARE cb_idx_stmt;

SET @idx_exists := (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'cb_pda_detalle' AND INDEX_NAME = 'idx_cb_pda_detalle_movimiento');
SET @idx_sql := IF(@idx_exists = 0, 'CREATE INDEX idx_cb_pda_detalle_movimiento ON cb_pda_detalle(id_movimiento)', 'SELECT 1');
PREPARE cb_idx_stmt FROM @idx_sql; EXECUTE cb_idx_stmt; DEALLOCATE PREPARE cb_idx_stmt;

SET @idx_exists := (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'cb_pda_detalle' AND INDEX_NAME = 'idx_cb_pda_detalle_estado');
SET @idx_sql := IF(@idx_exists = 0, 'CREATE INDEX idx_cb_pda_detalle_estado ON cb_pda_detalle(estado_item)', 'SELECT 1');
PREPARE cb_idx_stmt FROM @idx_sql; EXECUTE cb_idx_stmt; DEALLOCATE PREPARE cb_idx_stmt;

SET @idx_exists := (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'cb_extractos_bancarios' AND INDEX_NAME = 'idx_cb_extractos_bancarios_conciliacion');
SET @idx_sql := IF(@idx_exists = 0, 'CREATE INDEX idx_cb_extractos_bancarios_conciliacion ON cb_extractos_bancarios(id_conciliacion)', 'SELECT 1');
PREPARE cb_idx_stmt FROM @idx_sql; EXECUTE cb_idx_stmt; DEALLOCATE PREPARE cb_idx_stmt;

SET @idx_exists := (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'cb_extractos_bancarios' AND INDEX_NAME = 'idx_cb_extractos_bancarios_cuenta');
SET @idx_sql := IF(@idx_exists = 0, 'CREATE INDEX idx_cb_extractos_bancarios_cuenta ON cb_extractos_bancarios(id_cuenta_bancaria)', 'SELECT 1');
PREPARE cb_idx_stmt FROM @idx_sql; EXECUTE cb_idx_stmt; DEALLOCATE PREPARE cb_idx_stmt;

SET @idx_exists := (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'movimientos_bancos' AND INDEX_NAME = 'idx_movimientos_bancos_conciliado');
SET @idx_sql := IF(@idx_exists = 0, 'CREATE INDEX idx_movimientos_bancos_conciliado ON movimientos_bancos(conciliado)', 'SELECT 1');
PREPARE cb_idx_stmt FROM @idx_sql; EXECUTE cb_idx_stmt; DEALLOCATE PREPARE cb_idx_stmt;

-- ============================================================================
-- FIN DEL SCRIPT
-- ============================================================================