-- ============================================================================
-- SCRIPT CORREGIDO - USANDO COLUMNAS CORRECTAS DE LA TABLA LOANS
-- ============================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- Crear tabla temporal para resultados
CREATE TEMPORARY TABLE IF NOT EXISTS temp_diagnostico (
    seccion VARCHAR(100),
    detalle TEXT,
    resultado TEXT
);

-- ============================================================================
-- 1. DIAGNÓSTICO COMPLETO
-- ============================================================================

-- Verificar conteos actuales
INSERT INTO temp_diagnostico 
SELECT 'CONTEO_INICIAL', 'loan_guarantees', CONCAT('Total: ', COUNT(*), ' | Activos: ', COUNT(CASE WHEN deleted_at IS NULL THEN 1 END))
FROM loan_guarantees;

INSERT INTO temp_diagnostico 
SELECT 'CONTEO_INICIAL', 'loan_guarantee_documents', CONCAT('Total: ', COUNT(*), ' | Activos: ', COUNT(CASE WHEN deleted_at IS NULL THEN 1 END))
FROM loan_guarantee_documents;

INSERT INTO temp_diagnostico 
SELECT 'CONTEO_INICIAL', 'loans', CONCAT('Total: ', COUNT(*), ' | Activos: ', COUNT(CASE WHEN deleted_at IS NULL THEN 1 END))
FROM loans;

-- ============================================================================
-- 2. INSERTAR DATOS DE PRUEBA CORREGIDOS
-- ============================================================================

-- Insertar garantías usando la columna correcta (disbursed_amount o approved_amount)
INSERT INTO loan_guarantees (
    loan_id, type, document_number, amount, description, issue_date, due_date, status,
    guarantor_name, guarantor_document, guarantor_phone, guarantor_email, created_at, updated_at
)
SELECT 
    l.id,
    'promissory_note',
    CONCAT('PN-', LPAD(l.id, 6, '0')),
    COALESCE(l.disbursed_amount, l.approved_amount, l.requested_amount) * 0.1, -- 10% del valor del préstamo
    CONCAT('Pagaré por préstamo #', l.loan_number),
    COALESCE(l.disbursement_date, l.approval_date, l.application_date),
    DATE_ADD(COALESCE(l.disbursement_date, l.approval_date, l.application_date), INTERVAL 12 MONTH),
    'active',
    CONCAT('Garante Préstamo ', l.id),
    CONCAT('123456', LPAD(l.id, 2, '0')),
    '+57300123456',
    CONCAT('garante', l.id, '@email.com'),
    NOW(),
    NOW()
FROM loans l 
WHERE l.deleted_at IS NULL 
  AND NOT EXISTS (SELECT 1 FROM loan_guarantees WHERE deleted_at IS NULL)
LIMIT 5;

-- Insertar una segunda garantía de tipo hipoteca para el primer préstamo
INSERT INTO loan_guarantees (
    loan_id, type, document_number, amount, description, issue_date, due_date, status,
    guarantor_name, guarantor_document, guarantor_phone, guarantor_email,
    property_address, property_value, property_description, registration_number,
    created_at, updated_at
)
SELECT 
    l.id,
    'mortgage',
    CONCAT('HIP-', LPAD(l.id, 6, '0')),
    COALESCE(l.disbursed_amount, l.approved_amount, l.requested_amount) * 0.8, -- 80% del valor
    CONCAT('Hipoteca por préstamo #', l.loan_number),
    COALESCE(l.disbursement_date, l.approval_date, l.application_date),
    DATE_ADD(COALESCE(l.disbursement_date, l.approval_date, l.application_date), INTERVAL 24 MONTH),
    'active',
    CONCAT('Propietario Préstamo ', l.id),
    CONCAT('987654', LPAD(l.id, 2, '0')),
    '+57300987654',
    CONCAT('propietario', l.id, '@email.com'),
    CONCAT('Calle ', l.id, ' # 123-45, Bogotá, Colombia'),
    COALESCE(l.disbursed_amount, l.approved_amount, l.requested_amount) * 1.5, -- Valor propiedad
    CONCAT('Casa de 120 m2, 3 habitaciones, 2 baños - Garantía préstamo #', l.loan_number),
    CONCAT('MAT-', LPAD(l.id, 8, '0')),
    NOW(),
    NOW()
FROM loans l 
WHERE l.deleted_at IS NULL 
  AND EXISTS (SELECT 1 FROM loan_guarantees WHERE loan_id = l.id AND deleted_at IS NULL)
LIMIT 1;

-- Insertar documentos de prueba para las garantías creadas
INSERT INTO loan_guarantee_documents (
    loan_guarantee_id, document_type, description, original_name, file_name, 
    file_path, file_size, mime_type, uploaded_by, created_at, updated_at
)
SELECT 
    lg.id,
    'contract',
    CONCAT('Contrato de garantía - ', lg.type),
    CONCAT('contrato_garantia_', lg.id, '.pdf'),
    CONCAT('guarantee_contract_', lg.id, '_', UNIX_TIMESTAMP(), '.pdf'),
    CONCAT('/storage/guarantees/documents/', lg.id, '/contract_', UNIX_TIMESTAMP(), '.pdf'),
    245760, -- 240 KB
    'application/pdf',
    1, -- Usuario ID 1 (admin)
    NOW(),
    NOW()
FROM loan_guarantees lg 
WHERE lg.deleted_at IS NULL
  AND NOT EXISTS (SELECT 1 FROM loan_guarantee_documents WHERE loan_guarantee_id = lg.id);

-- Insertar documento adicional de tipo identificación
INSERT INTO loan_guarantee_documents (
    loan_guarantee_id, document_type, description, original_name, file_name, 
    file_path, file_size, mime_type, uploaded_by, created_at, updated_at
)
SELECT 
    lg.id,
    'identification',
    CONCAT('Cédula del garante - ', lg.guarantor_name),
    CONCAT('cedula_garante_', lg.id, '.jpg'),
    CONCAT('guarantor_id_', lg.id, '_', UNIX_TIMESTAMP(), '.jpg'),
    CONCAT('/storage/guarantees/documents/', lg.id, '/id_', UNIX_TIMESTAMP(), '.jpg'),
    1048576, -- 1 MB
    'image/jpeg',
    1, -- Usuario ID 1 (admin)
    NOW(),
    NOW()
FROM loan_guarantees lg 
WHERE lg.deleted_at IS NULL
  AND lg.guarantor_document IS NOT NULL
LIMIT 3;

-- ============================================================================
-- 3. VERIFICACIÓN FINAL Y RESULTADOS
-- ============================================================================

-- Actualizar conteos finales
INSERT INTO temp_diagnostico 
SELECT 'CONTEO_FINAL', 'loan_guarantees', CONCAT('Total: ', COUNT(*), ' | Activos: ', COUNT(CASE WHEN deleted_at IS NULL THEN 1 END))
FROM loan_guarantees;

INSERT INTO temp_diagnostico 
SELECT 'CONTEO_FINAL', 'loan_guarantee_documents', CONCAT('Total: ', COUNT(*), ' | Activos: ', COUNT(CASE WHEN deleted_at IS NULL THEN 1 END))
FROM loan_guarantee_documents;

-- Insertar información de las garantías creadas
INSERT INTO temp_diagnostico 
SELECT 'GARANTIAS_CREADAS', 
       CONCAT('ID: ', id, ' | Préstamo: ', loan_id, ' | Tipo: ', type),
       CONCAT('Garante: ', COALESCE(guarantor_name, 'N/A'), ' | Monto: $', FORMAT(amount, 0))
FROM loan_guarantees 
WHERE deleted_at IS NULL 
ORDER BY id;

-- Insertar información de documentos creados
INSERT INTO temp_diagnostico 
SELECT 'DOCUMENTOS_CREADOS',
       CONCAT('Garantía ID: ', loan_guarantee_id, ' | Tipo: ', document_type),
       CONCAT('Archivo: ', file_name, ' | Tamaño: ', FORMAT(file_size, 0), ' bytes')
FROM loan_guarantee_documents 
WHERE deleted_at IS NULL 
ORDER BY loan_guarantee_id, id;

-- ============================================================================
-- 4. MOSTRAR TODOS LOS RESULTADOS
-- ============================================================================

SELECT 
    CONCAT('=== ', seccion, ' ===') as SECCION,
    detalle as DETALLE,
    resultado as RESULTADO
FROM temp_diagnostico 
ORDER BY 
    CASE seccion 
        WHEN 'CONTEO_INICIAL' THEN 1
        WHEN 'CONTEO_FINAL' THEN 2  
        WHEN 'GARANTIAS_CREADAS' THEN 3
        WHEN 'DOCUMENTOS_CREADOS' THEN 4
        ELSE 5 
    END,
    detalle;

-- Mostrar un resumen final más limpio
SELECT '======================== RESUMEN FINAL ========================' as RESULTADO;

SELECT 
    CONCAT('✓ GARANTÍAS ACTIVAS: ', COUNT(*)) as RESULTADO
FROM loan_guarantees 
WHERE deleted_at IS NULL;

SELECT 
    CONCAT('✓ DOCUMENTOS ACTIVOS: ', COUNT(*)) as RESULTADO  
FROM loan_guarantee_documents 
WHERE deleted_at IS NULL;

SELECT 
    CONCAT('✓ PRÉSTAMOS DISPONIBLES: ', COUNT(*)) as RESULTADO
FROM loans 
WHERE deleted_at IS NULL;

-- Mostrar ejemplos de garantías para verificar en la interfaz
SELECT 
    CONCAT('▶ Garantía ID: ', id, ' - Tipo: ', type, ' - Monto: $', FORMAT(amount, 0)) as GARANTIAS_DISPONIBLES
FROM loan_guarantees 
WHERE deleted_at IS NULL 
ORDER BY id 
LIMIT 5;

-- Verificar estructura de préstamos para confirmar
SELECT 'ESTRUCTURA PRÉSTAMOS DISPONIBLES:' as INFO;
SELECT 
    id,
    loan_number,
    COALESCE(disbursed_amount, approved_amount, requested_amount) as monto_disponible,
    status,
    application_date
FROM loans 
WHERE deleted_at IS NULL 
LIMIT 3;

-- Limpiar tabla temporal
DROP TEMPORARY TABLE temp_diagnostico;

SET FOREIGN_KEY_CHECKS = 1;

SELECT '======================== SCRIPT COMPLETADO ========================' as RESULTADO;
SELECT 'Ahora puedes probar la URL: /app/loans/guarantees' as INSTRUCCION;
SELECT 'El problema era que usaba l.amount en lugar de l.disbursed_amount' as SOLUCION;