-- ============================================================================
-- COMANDOS ÚTILES PARA ADMINISTRACIÓN - CONCILIACIÓN BANCARIA
-- ============================================================================

-- ============================================================================
-- VERIFICACIÓN DE TABLAS
-- ============================================================================

-- Ver estructura de la tabla conciliacion_bancaria
DESCRIBE conciliacion_bancaria;

-- Ver todos los campos y tipos
SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, KEY, DEFAULT, EXTRA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'conciliacion_bancaria' AND TABLE_SCHEMA = 'erp';

-- Verificar integridad referencial
SELECT * FROM conciliacion_bancaria WHERE id_cuenta_bancaria NOT IN 
(SELECT id_cuenta FROM cuentas_bancarias);

-- ============================================================================
-- CONSULTAS DE DATOS
-- ============================================================================

-- Ver todas las conciliaciones
SELECT * FROM conciliacion_bancaria ORDER BY fecha_creacion DESC;

-- Ver conciliaciones de una cuenta específica
SELECT * FROM conciliacion_bancaria 
WHERE id_cuenta_bancaria = 1 
ORDER BY fecha_creacion DESC;

-- Ver conciliaciones pendientes
SELECT * FROM conciliacion_bancaria 
WHERE estado = 'PENDIENTE'
ORDER BY fecha_creacion ASC;

-- Ver conciliaciones finalizadas con diferencia
SELECT * FROM conciliacion_bancaria 
WHERE estado = 'FINALIZADA' AND diferencia != 0
ORDER BY fecha_creacion DESC;

-- Ver resumen de una conciliación específica
SELECT 
    cb.id_conciliacion,
    cb.fecha_inicio,
    cb.fecha_fin,
    cb.saldo_extracto,
    cb.saldo_sistema_calculado,
    cb.diferencia,
    COUNT(DISTINCT pd.id_pda) as total_movimientos,
    SUM(CASE WHEN pd.estado_item = 'CONCILIADO' THEN 1 ELSE 0 END) as conciliados,
    SUM(CASE WHEN pd.estado_item = 'PENDIENTE' THEN 1 ELSE 0 END) as pendientes
FROM conciliacion_bancaria cb
LEFT JOIN pda_detalle pd ON cb.id_conciliacion = pd.id_conciliacion
WHERE cb.id_conciliacion = 1
GROUP BY cb.id_conciliacion;

-- Ver todas las diferencias no conciliadas
SELECT 
    pd.id_detalle,
    cb.id_conciliacion,
    mb.fecha_movimiento,
    mb.referencia,
    mb.concepto,
    pd.monto_extracto,
    pd.monto_sistema,
    pd.diferencia
FROM 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;

-- Ver movimientos sin conciliar de una cuenta
SELECT 
    mb.id_movimiento,
    mb.fecha_movimiento,
    mb.tipo_movimiento,
    mb.referencia,
    mb.concepto,
    mb.monto
FROM movimientos_bancos mb
WHERE mb.id_cuenta = 1 AND mb.conciliado = 0
ORDER BY mb.fecha_movimiento DESC;

-- ============================================================================
-- ESTADÍSTICAS Y ANÁLISIS
-- ============================================================================

-- Estadísticas de conciliaciones por mes
SELECT 
    DATE_FORMAT(cb.fecha_creacion, '%Y-%m') as mes,
    COUNT(*) as total_conciliaciones,
    SUM(CASE WHEN cb.estado = 'FINALIZADA' THEN 1 ELSE 0 END) as finalizadas,
    SUM(CASE WHEN cb.diferencia = 0 THEN 1 ELSE 0 END) as perfectas,
    AVG(ABS(COALESCE(cb.diferencia, 0))) as promedio_diferencia,
    SUM(COALESCE(cb.diferencia, 0)) as diferencia_total
FROM conciliacion_bancaria cb
GROUP BY DATE_FORMAT(cb.fecha_creacion, '%Y-%m')
ORDER BY mes DESC;

-- Conciliaciones con mayor diferencia
SELECT 
    cb.id_conciliacion,
    b.nombre_banco,
    cta.numero_cuenta,
    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
FROM conciliacion_bancaria cb
JOIN cuentas_bancarias cta ON cb.id_cuenta_bancaria = cta.id_cuenta
JOIN bancos b ON cta.id_banco = b.id_banco
WHERE cb.estado = 'FINALIZADA'
ORDER BY ABS(cb.diferencia) DESC
LIMIT 10;

-- Análisis por banco
SELECT 
    b.nombre_banco,
    COUNT(cb.id_conciliacion) as total_conciliaciones,
    SUM(CASE WHEN cb.estado = 'FINALIZADA' THEN 1 ELSE 0 END) as finalizadas,
    SUM(CASE WHEN cb.diferencia = 0 THEN 1 ELSE 0 END) as perfectas,
    ROUND(AVG(ABS(COALESCE(cb.diferencia, 0))), 2) as promedio_diferencia
FROM conciliacion_bancaria cb
JOIN cuentas_bancarias cta ON cb.id_cuenta_bancaria = cta.id_cuenta
JOIN bancos b ON cta.id_banco = b.id_banco
GROUP BY b.nombre_banco
ORDER BY total_conciliaciones DESC;

-- ============================================================================
-- OPERACIONES DE MANTENIMIENTO
-- ============================================================================

-- Limpiar conciliaciones borrador sin movimientos (más de 30 días)
DELETE FROM conciliacion_bancaria 
WHERE estado = 'PENDIENTE' 
AND fecha_creacion < DATE_SUB(NOW(), INTERVAL 30 DAY)
AND id_conciliacion NOT IN (
    SELECT DISTINCT id_conciliacion FROM pda_detalle
);

-- Recalcular diferencias de todas las conciliaciones
UPDATE conciliacion_bancaria cb
SET diferencia = (
    SELECT COALESCE(SUM(pd.diferencia), 0)
    FROM pda_detalle pd
    WHERE pd.id_conciliacion = cb.id_conciliacion
)
WHERE estado = 'FINALIZADA';

-- Resetear estado de movimientos (útil para re-conciliar)
UPDATE movimientos_bancos 
SET conciliado = 0, fecha_conciliacion = NULL
WHERE id_cuenta = 1 
AND fecha_movimiento BETWEEN '2025-12-01' AND '2025-12-31';

-- ============================================================================
-- BÚSQUEDAS ESPECÍFICAS
-- ============================================================================

-- Buscar diferencias mayores a un monto específico
SELECT 
    pd.id_detalle,
    cb.id_conciliacion,
    mb.fecha_movimiento,
    mb.referencia,
    pd.monto_extracto,
    pd.monto_sistema,
    pd.diferencia
FROM 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 ABS(pd.diferencia) > 100.00
ORDER BY ABS(pd.diferencia) DESC;

-- Buscar movimientos duplicados en sistema
SELECT 
    fecha_movimiento,
    tipo_movimiento,
    referencia,
    monto,
    COUNT(*) as cantidad
FROM movimientos_bancos
GROUP BY fecha_movimiento, tipo_movimiento, referencia, monto
HAVING COUNT(*) > 1;

-- Buscar extractos sin conciliar
SELECT 
    eb.id_extracto,
    b.nombre_banco,
    cta.numero_cuenta,
    eb.fecha_movimiento,
    eb.tipo_movimiento,
    eb.referencia,
    eb.monto
FROM extractos_bancarios eb
JOIN conciliacion_bancaria cb ON eb.id_conciliacion = cb.id_conciliacion
JOIN cuentas_bancarias cta ON eb.id_cuenta = cta.id_cuenta
JOIN bancos b ON cta.id_banco = b.id_banco
WHERE eb.id_extracto NOT IN (
    SELECT DISTINCT id_movimiento FROM pda_detalle
)
ORDER BY eb.fecha_movimiento DESC;

-- ============================================================================
-- EXPORTACIÓN Y BACKUP
-- ============================================================================

-- Exportar conciliaciones a CSV
SELECT 
    id_conciliacion,
    fecha_inicio,
    fecha_fin,
    saldo_extracto,
    saldo_sistema_calculado,
    diferencia,
    estado,
    fecha_creacion,
    fecha_finalizacion
FROM conciliacion_bancaria
INTO OUTFILE '/tmp/conciliaciones.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- Exportar diferencias a CSV
SELECT 
    id_conciliacion,
    id_movimiento,
    fecha_movimiento,
    monto_extracto,
    monto_sistema,
    diferencia
FROM vw_diferencias_no_conciliadas
INTO OUTFILE '/tmp/diferencias.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- ============================================================================
-- REPARACIÓN Y OPTIMIZACIÓN
-- ============================================================================

-- Verificar integridad de tabla
CHECK TABLE conciliacion_bancaria;
CHECK TABLE pda_maestro;
CHECK TABLE pda_detalle;
CHECK TABLE extractos_bancarios;

-- Reparar tabla si es necesario
REPAIR TABLE conciliacion_bancaria;

-- Optimizar tabla para mejor performance
OPTIMIZE TABLE conciliacion_bancaria;
OPTIMIZE TABLE pda_maestro;
OPTIMIZE TABLE pda_detalle;
OPTIMIZE TABLE extractos_bancarios;

-- Ver uso de espacios de tabla
SELECT 
    TABLE_NAME,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) as Size_MB
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'erp'
AND TABLE_NAME IN ('conciliacion_bancaria', 'pda_maestro', 'pda_detalle', 'extractos_bancarios')
ORDER BY (data_length + index_length) DESC;

-- ============================================================================
-- MONITOREO Y AUDITORÍA
-- ============================================================================

-- Ver último movimiento de cada conciliación
SELECT 
    cb.id_conciliacion,
    MAX(pb.fecha_creacion) as ultimo_movimiento,
    cb.estado,
    DATEDIFF(NOW(), MAX(pb.fecha_creacion)) as dias_sin_actividad
FROM conciliacion_bancaria cb
LEFT JOIN pda_maestro pb ON cb.id_conciliacion = pb.id_conciliacion
GROUP BY cb.id_conciliacion
ORDER BY ultimo_movimiento DESC;

-- Ver usuarios que realizaron conciliaciones
SELECT 
    cb.usuario_creacion,
    COUNT(*) as conciliaciones_creadas,
    MAX(cb.fecha_creacion) as ultima_conciliacion
FROM conciliacion_bancaria cb
WHERE cb.usuario_creacion IS NOT NULL
GROUP BY cb.usuario_creacion
ORDER BY conciliaciones_creadas DESC;

-- ============================================================================
-- CORRECCIONES DE DATOS
-- ============================================================================

-- Marcar una conciliación como finalizada manualmente
UPDATE conciliacion_bancaria
SET 
    estado = 'FINALIZADA',
    fecha_finalizacion = NOW(),
    usuario_finalizacion = 1
WHERE id_conciliacion = 1;

-- Actualizar observación de una conciliación
UPDATE conciliacion_bancaria
SET observaciones = 'Requiere revisión - diferencia de 500.00'
WHERE id_conciliacion = 1;

-- Marcar una diferencia como resuelta
UPDATE pda_detalle
SET estado_item = 'CONCILIADO'
WHERE id_detalle = 1;

-- Recalcular un saldo específico
UPDATE conciliacion_bancaria
SET saldo_sistema_calculado = (
    SELECT SUM(CASE 
        WHEN tipo_movimiento = 'DEPOSITO' THEN monto 
        ELSE -monto 
    END)
    FROM movimientos_bancos
    WHERE id_cuenta = (
        SELECT id_cuenta_bancaria FROM conciliacion_bancaria 
        WHERE id_conciliacion = 1
    )
    AND fecha_movimiento BETWEEN 
        (SELECT fecha_inicio FROM conciliacion_bancaria WHERE id_conciliacion = 1)
        AND 
        (SELECT fecha_fin FROM conciliacion_bancaria WHERE id_conciliacion = 1)
)
WHERE id_conciliacion = 1;

-- ============================================================================
-- FIN DE COMANDOS
-- ============================================================================