-- ============================================================================
-- VISTAS PARA REPORTES DE CONCILIACIÓN BANCARIA
-- ============================================================================

-- Vista: Resumen de Conciliaciones
DROP VIEW IF EXISTS vw_resumen_conciliaciones;
CREATE VIEW vw_resumen_conciliaciones AS
SELECT 
    cb.id_conciliacion,
    cb.id_empresa,
    cb.id_cuenta_bancaria,
    cta.numero_cuenta,
    b.id_banco,
    b.nombre_banco,
    cb.fecha_inicio,
    cb.fecha_fin,
    cb.saldo_extracto,
    cb.saldo_sistema_calculado,
    cb.diferencia,
    cb.estado,
    CASE 
        WHEN cb.diferencia = 0 THEN 'PERFECTA'
        ELSE 'CON_DIFERENCIA'
    END as tipo_conciliacion,
    COUNT(DISTINCT pd.id_detalle) 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,
    cb.fecha_creacion
FROM conciliacion_bancaria cb
LEFT JOIN cuentas_bancarias cta ON cb.id_cuenta_bancaria = cta.id_cuenta_bancaria
LEFT JOIN bancos b ON cta.id_banco = b.id_banco
LEFT JOIN pda_detalle pd ON cb.id_conciliacion = pd.id_conciliacion
GROUP BY cb.id_conciliacion, cb.id_empresa, cb.id_cuenta_bancaria, cta.numero_cuenta, b.id_banco, b.nombre_banco,
         cb.fecha_inicio, cb.fecha_fin, cb.saldo_extracto, cb.saldo_sistema_calculado, cb.diferencia, cb.estado, cb.fecha_creacion
ORDER BY cb.fecha_creacion DESC;

-- Vista: Diferencias No Conciliadas
DROP VIEW IF EXISTS vw_diferencias_no_conciliadas;
CREATE VIEW vw_diferencias_no_conciliadas AS
SELECT 
    pd.id_detalle,
    pd.id_conciliacion,
    cb.id_empresa,
    b.nombre_banco,
    cta.numero_cuenta,
    cb.id_cuenta_bancaria,
    pd.monto_extracto,
    pd.monto_sistema,
    pd.diferencia,
    ABS(pd.diferencia) as diferencia_calculada,
    pd.estado_item,
    cb.fecha_creacion
FROM pda_detalle pd
JOIN conciliacion_bancaria cb ON pd.id_conciliacion = cb.id_conciliacion
JOIN cuentas_bancarias cta ON cb.id_cuenta_bancaria = cta.id_cuenta_bancaria
JOIN bancos b ON cta.id_banco = b.id_banco
WHERE pd.estado_item IN ('PENDIENTE', 'RECHAZADO')
ORDER BY cb.fecha_creacion DESC;

-- Vista: Movimientos Conciliados por Período
DROP VIEW IF EXISTS vw_movimientos_conciliados_periodo;
CREATE VIEW vw_movimientos_conciliados_periodo AS
SELECT 
    cb.id_conciliacion,
    cb.id_empresa,
    cb.id_cuenta_bancaria,
    cta.numero_cuenta,
    b.nombre_banco,
    cb.fecha_inicio,
    cb.fecha_fin,
    pd.monto_extracto,
    pd.monto_sistema,
    pd.diferencia,
    pd.estado_item,
    cb.fecha_creacion
FROM pda_detalle pd
JOIN conciliacion_bancaria cb ON pd.id_conciliacion = cb.id_conciliacion
JOIN cuentas_bancarias cta ON cb.id_cuenta_bancaria = cta.id_cuenta_bancaria
JOIN bancos b ON cta.id_banco = b.id_banco
WHERE pd.estado_item = 'CONCILIADO'
ORDER BY cb.fecha_creacion DESC;

-- Vista: Análisis de Varianza (resumen por período)
DROP VIEW IF EXISTS vw_analisis_varianza;
CREATE VIEW vw_analisis_varianza 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,
    ABS(cb.diferencia) as diferencia_absoluta,
    ROUND((ABS(cb.diferencia) / NULLIF(cb.saldo_extracto, 0) * 100), 2) as porcentaje_diferencia,
    COUNT(DISTINCT pd.id_detalle) as total_movimientos,
    SUM(CASE WHEN pd.estado_item = 'CONCILIADO' THEN 1 ELSE 0 END) as movimientos_ok,
    SUM(CASE WHEN pd.estado_item = 'PENDIENTE' THEN 1 ELSE 0 END) as movimientos_pendientes,
    cb.estado,
    cb.fecha_creacion
FROM conciliacion_bancaria cb
LEFT JOIN cuentas_bancarias cta ON cb.id_cuenta_bancaria = cta.id_cuenta_bancaria
LEFT JOIN bancos b ON cta.id_banco = b.id_banco
LEFT JOIN pda_detalle pd ON cb.id_conciliacion = pd.id_conciliacion
WHERE cb.estado = 'FINALIZADA'
GROUP BY 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, cb.fecha_creacion
ORDER BY cb.fecha_creacion DESC;

-- Vista: Resumen por Banco
DROP VIEW IF EXISTS vw_resumen_por_banco;
CREATE VIEW vw_resumen_por_banco AS
SELECT 
    b.id_banco,
    b.nombre_banco,
    1 as id_empresa,
    COUNT(DISTINCT cb.id_conciliacion) as total_conciliaciones,
    COUNT(DISTINCT cta.id_cuenta_bancaria) as total_cuentas,
    SUM(CASE WHEN cb.estado = 'FINALIZADA' THEN 1 ELSE 0 END) as conciliaciones_finalizadas,
    SUM(CASE WHEN cb.estado = 'PENDIENTE' THEN 1 ELSE 0 END) as conciliaciones_pendientes,
    COALESCE(SUM(cta.saldo_actual), 0) as saldo_total,
    MAX(cb.fecha_creacion) as ultima_conciliacion
FROM bancos b
LEFT JOIN cuentas_bancarias cta ON b.id_banco = cta.id_banco
LEFT JOIN conciliacion_bancaria cb ON cta.id_cuenta_bancaria = cb.id_cuenta_bancaria
GROUP BY b.id_banco, b.nombre_banco
ORDER BY b.nombre_banco;

-- Vista: Detalle de Movimientos sin Conciliar
DROP VIEW IF EXISTS vw_movimientos_sin_conciliar;
CREATE VIEW vw_movimientos_sin_conciliar AS
SELECT 
    mb.id_movimiento,
    mb.id_empresa,
    mb.id_cuenta_bancaria,
    cta.numero_cuenta,
    b.nombre_banco,
    mb.fecha_movimiento,
    mb.tipo_movimiento,
    mb.referencia,
    mb.concepto,
    mb.monto,
    mb.conciliado,
    u.nombre as usuario_creacion,
    mb.fecha_creacion
FROM movimientos_bancos mb
LEFT JOIN cuentas_bancarias cta ON mb.id_cuenta_bancaria = cta.id_cuenta_bancaria
LEFT JOIN bancos b ON cta.id_banco = b.id_banco
LEFT JOIN usuarios u ON mb.usuario_id = u.id
WHERE mb.conciliado = 0
ORDER BY mb.fecha_movimiento DESC;

-- Vista: Extractos sin Conciliar
DROP VIEW IF EXISTS vw_extractos_sin_conciliar;
CREATE VIEW vw_extractos_sin_conciliar AS
SELECT 
    e.id_extracto,
    e.id_empresa,
    e.id_cuenta_bancaria,
    cta.numero_cuenta,
    b.nombre_banco,
    e.periodo,
    e.fecha_movimiento,
    e.tipo_movimiento,
    e.referencia,
    e.descripcion,
    e.monto,
    e.fecha_importacion,
    u.nombre as usuario_importacion
FROM extractos_bancarios e
LEFT JOIN cuentas_bancarias cta ON e.id_cuenta_bancaria = cta.id_cuenta_bancaria
LEFT JOIN bancos b ON cta.id_banco = b.id_banco
LEFT JOIN usuarios u ON e.usuario_importacion = u.id
WHERE e.id_conciliacion IS NULL
ORDER BY e.fecha_importacion DESC;

-- Vista: Dashboard de Conciliación (resumen ejecutivo)
DROP VIEW IF EXISTS vw_dashboard_conciliacion;
CREATE VIEW vw_dashboard_conciliacion AS
SELECT 
    cb.id_empresa,
    COUNT(DISTINCT cb.id_conciliacion) as total_conciliaciones,
    COUNT(DISTINCT CASE WHEN cb.estado = 'FINALIZADA' THEN cb.id_conciliacion END) as conciliaciones_completadas,
    COUNT(DISTINCT CASE WHEN cb.estado = 'PENDIENTE' THEN cb.id_conciliacion END) as conciliaciones_pendientes,
    COUNT(DISTINCT CASE WHEN cb.diferencia = 0 THEN cb.id_conciliacion END) as conciliaciones_perfectas,
    COUNT(DISTINCT CASE WHEN cb.diferencia != 0 THEN cb.id_conciliacion END) as conciliaciones_con_diferencia,
    COALESCE(SUM(ABS(cb.diferencia)), 0) as total_diferencias,
    COUNT(DISTINCT pd.id_detalle) as total_movimientos_conciliados,
    MAX(cb.fecha_creacion) as ultima_conciliacion,
    MIN(cb.fecha_creacion) as primera_conciliacion
FROM conciliacion_bancaria cb
LEFT JOIN pda_detalle pd ON cb.id_conciliacion = pd.id_conciliacion AND pd.estado_item = 'CONCILIADO'
GROUP BY cb.id_empresa;

-- Verificar que las vistas se crearon correctamente
SELECT 'Vistas creadas exitosamente' as resultado;