-- ============================================================================
-- SCRIPT SQL PARA GENERAR DATOS INICIALES DE GARANTÍAS DE PRÉSTAMOS
-- Fecha: 2025-08-12
-- Propósito: Insertar datos de prueba para el módulo de garantías
-- ============================================================================

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

-- ============================================================================
-- 1. VERIFICAR Y MOSTRAR ESTADO ACTUAL
-- ============================================================================

SELECT 'VERIFICANDO ESTADO ACTUAL...' as STATUS;

SELECT 
    'PRÉSTAMOS DISPONIBLES' as TABLA,
    COUNT(*) as TOTAL,
    COUNT(CASE WHEN deleted_at IS NULL THEN 1 END) as ACTIVOS
FROM loans;

SELECT 
    'GARANTÍAS EXISTENTES' as TABLA,
    COUNT(*) as TOTAL,
    COUNT(CASE WHEN deleted_at IS NULL THEN 1 END) as ACTIVOS
FROM loan_guarantees;

-- Mostrar algunos préstamos disponibles
SELECT 'PRÉSTAMOS PARA CREAR GARANTÍAS:' as INFO;
SELECT 
    id,
    loan_number,
    COALESCE(disbursed_amount, approved_amount, requested_amount) as monto,
    status,
    application_date
FROM loans 
WHERE deleted_at IS NULL 
LIMIT 5;

-- ============================================================================
-- 2. LIMPIAR DATOS EXISTENTES SI ES NECESARIO (OPCIONAL)
-- ============================================================================

-- Descomenta estas líneas solo si quieres empezar desde cero
-- DELETE FROM loan_guarantee_documents WHERE id > 0;
-- DELETE FROM loan_guarantees WHERE id > 0;

-- ============================================================================
-- 3. INSERTAR GARANTÍAS DE PRUEBA
-- ============================================================================

-- Insertar garantías tipo pagaré para los primeros 3 préstamos
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-', YEAR(CURDATE()), '-', LPAD(l.id, 4, '0')),
    ROUND(COALESCE(l.disbursed_amount, l.approved_amount, l.requested_amount) * 0.10, 2),
    CONCAT('Pagaré como garantía del préstamo ', l.loan_number, ' por valor de $', 
           FORMAT(COALESCE(l.disbursed_amount, l.approved_amount, l.requested_amount), 0)),
    COALESCE(l.disbursement_date, l.approval_date, l.application_date, CURDATE()),
    DATE_ADD(COALESCE(l.disbursement_date, l.approval_date, l.application_date, CURDATE()), INTERVAL 12 MONTH),
    'active',
    CASE 
        WHEN l.id % 3 = 1 THEN 'María García Rodríguez'
        WHEN l.id % 3 = 2 THEN 'Carlos Andrés López'
        ELSE 'Ana Patricia Martínez'
    END,
    CONCAT('1234567', LPAD(l.id, 2, '0')),
    CONCAT('+5730', (1000000 + l.id)),
    CONCAT('garante', l.id, '@gmail.com'),
    NOW(),
    NOW()
FROM loans l 
WHERE l.deleted_at IS NULL 
  AND NOT EXISTS (SELECT 1 FROM loan_guarantees lg WHERE lg.loan_id = l.id AND lg.deleted_at IS NULL)
LIMIT 3;

-- Insertar garantías tipo hipoteca para préstamos con montos altos
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-', YEAR(CURDATE()), '-', LPAD(l.id, 4, '0')),
    ROUND(COALESCE(l.disbursed_amount, l.approved_amount, l.requested_amount) * 0.80, 2),
    CONCAT('Hipoteca sobre inmueble como garantía del préstamo ', l.loan_number),
    COALESCE(l.disbursement_date, l.approval_date, l.application_date, CURDATE()),
    DATE_ADD(COALESCE(l.disbursement_date, l.approval_date, l.application_date, CURDATE()), INTERVAL 24 MONTH),
    'active',
    CASE 
        WHEN l.id % 2 = 1 THEN 'Roberto Jiménez Silva'
        ELSE 'Claudia Moreno Vargas'
    END,
    CONCAT('9876543', LPAD(l.id, 2, '0')),
    CONCAT('+5731', (2000000 + l.id)),
    CONCAT('propietario', l.id, '@hotmail.com'),
    CONCAT('Carrera ', (10 + l.id % 50), ' # ', (100 + l.id), '-', (10 + l.id % 99), ', Bogotá D.C.'),
    ROUND(COALESCE(l.disbursed_amount, l.approved_amount, l.requested_amount) * 1.5, 2),
    CASE 
        WHEN l.id % 3 = 1 THEN 'Casa de 2 pisos, 120 m², 3 habitaciones, 2 baños, garaje'
        WHEN l.id % 3 = 2 THEN 'Apartamento 80 m², 2 habitaciones, 2 baños, balcón'
        ELSE 'Casa lote 150 m², construcción 100 m², 3 habitaciones'
    END,
    CONCAT('MAT-', LPAD((1000000 + l.id), 8, '0')),
    NOW(),
    NOW()
FROM loans l 
WHERE l.deleted_at IS NULL 
  AND COALESCE(l.disbursed_amount, l.approved_amount, l.requested_amount) > 50000
  AND EXISTS (SELECT 1 FROM loan_guarantees lg WHERE lg.loan_id = l.id AND lg.type = 'promissory_note' AND lg.deleted_at IS NULL)
LIMIT 2;

-- Insertar garantías personales para préstamos restantes
INSERT INTO loan_guarantees (
    loan_id, type, document_number, amount, description, issue_date, due_date, status,
    guarantor_name, guarantor_document, guarantor_phone, guarantor_email, guarantor_address,
    created_at, updated_at
)
SELECT 
    l.id,
    'personal_guarantee',
    CONCAT('GP-', YEAR(CURDATE()), '-', LPAD(l.id, 4, '0')),
    ROUND(COALESCE(l.disbursed_amount, l.approved_amount, l.requested_amount) * 0.15, 2),
    CONCAT('Garantía personal para el préstamo ', l.loan_number),
    COALESCE(l.disbursement_date, l.approval_date, l.application_date, CURDATE()),
    DATE_ADD(COALESCE(l.disbursement_date, l.approval_date, l.application_date, CURDATE()), INTERVAL 18 MONTH),
    'active',
    CASE 
        WHEN l.id % 4 = 1 THEN 'Pedro Alejandro Ruiz'
        WHEN l.id % 4 = 2 THEN 'Diana Carolina Herrera'
        WHEN l.id % 4 = 3 THEN 'Miguel Ángel Ospina'
        ELSE 'Laura Fernanda Castro'
    END,
    CONCAT('5555555', LPAD(l.id, 2, '0')),
    CONCAT('+5732', (3000000 + l.id)),
    CONCAT('garante.personal', l.id, '@outlook.com'),
    CONCAT('Calle ', (50 + l.id % 30), ' # ', (20 + l.id), '-', (40 + l.id % 60), ', Medellín'),
    NOW(),
    NOW()
FROM loans l 
WHERE l.deleted_at IS NULL 
  AND NOT EXISTS (SELECT 1 FROM loan_guarantees lg WHERE lg.loan_id = l.id AND lg.deleted_at IS NULL)
LIMIT 2;

-- ============================================================================
-- 4. INSERTAR DOCUMENTOS PARA LAS GARANTÍAS
-- ============================================================================

-- Documentos de contratos para todas las garantías
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, ' - ', lg.document_number),
    CONCAT('contrato_', lg.type, '_', lg.id, '.pdf'),
    CONCAT('guarantee_contract_', lg.id, '_', UNIX_TIMESTAMP(NOW()), '.pdf'),
    CONCAT('/storage/guarantees/contracts/', lg.id, '/contract_', UNIX_TIMESTAMP(NOW()), '.pdf'),
    ROUND(200000 + (RAND() * 500000)),
    'application/pdf',
    1,
    NOW(),
    NOW()
FROM loan_guarantees lg 
WHERE lg.deleted_at IS NULL;

-- Documentos de identificación para garantías con garante
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 de ciudadanía - ', lg.guarantor_name),
    CONCAT('cedula_', lg.guarantor_document, '.jpg'),
    CONCAT('guarantor_id_', lg.id, '_', UNIX_TIMESTAMP(NOW()), '.jpg'),
    CONCAT('/storage/guarantees/identification/', lg.id, '/id_', UNIX_TIMESTAMP(NOW()), '.jpg'),
    ROUND(800000 + (RAND() * 1200000)),
    'image/jpeg',
    1,
    NOW(),
    NOW()
FROM loan_guarantees lg 
WHERE lg.deleted_at IS NULL 
  AND lg.guarantor_document IS NOT NULL;

-- Documentos de título para hipotecas
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,
    'title',
    CONCAT('Escritura pública de propiedad - Matrícula ', lg.registration_number),
    CONCAT('escritura_', lg.registration_number, '.pdf'),
    CONCAT('property_title_', lg.id, '_', UNIX_TIMESTAMP(NOW()), '.pdf'),
    CONCAT('/storage/guarantees/titles/', lg.id, '/title_', UNIX_TIMESTAMP(NOW()), '.pdf'),
    ROUND(1500000 + (RAND() * 2000000)),
    'application/pdf',
    1,
    NOW(),
    NOW()
FROM loan_guarantees lg 
WHERE lg.deleted_at IS NULL 
  AND lg.type = 'mortgage';

-- ============================================================================
-- 5. VERIFICACIÓN FINAL
-- ============================================================================

SELECT '============== RESULTADOS FINALES ==============' as RESULTADO;

-- Contar garantías creadas por tipo
SELECT 
    'GARANTÍAS POR TIPO' as DETALLE,
    type as TIPO,
    COUNT(*) as CANTIDAD,
    FORMAT(SUM(amount), 0) as VALOR_TOTAL
FROM loan_guarantees 
WHERE deleted_at IS NULL 
GROUP BY type;

-- Contar documentos por tipo
SELECT 
    'DOCUMENTOS POR TIPO' as DETALLE,
    document_type as TIPO,
    COUNT(*) as CANTIDAD
FROM loan_guarantee_documents 
WHERE deleted_at IS NULL 
GROUP BY document_type;

-- Resumen general
SELECT 
    'RESUMEN GENERAL' as DETALLE,
    'Garantías activas' as CONCEPTO,
    COUNT(*) as CANTIDAD
FROM loan_guarantees 
WHERE deleted_at IS NULL
UNION ALL
SELECT 
    'RESUMEN GENERAL',
    'Documentos subidos',
    COUNT(*)
FROM loan_guarantee_documents 
WHERE deleted_at IS NULL
UNION ALL
SELECT 
    'RESUMEN GENERAL',
    'Préstamos con garantías',
    COUNT(DISTINCT loan_id)
FROM loan_guarantees 
WHERE deleted_at IS NULL;

-- Mostrar ejemplos de garantías creadas
SELECT 'EJEMPLOS DE GARANTÍAS CREADAS:' as INFO;
SELECT 
    lg.id,
    l.loan_number,
    lg.type,
    lg.document_number,
    FORMAT(lg.amount, 0) as monto,
    lg.guarantor_name,
    lg.status
FROM loan_guarantees lg
JOIN loans l ON l.id = lg.loan_id
WHERE lg.deleted_at IS NULL 
ORDER BY lg.id 
LIMIT 5;

SET FOREIGN_KEY_CHECKS = 1;

SELECT '============================================' as RESULTADO;
SELECT 'DATOS DE GARANTÍAS INSERTADOS EXITOSAMENTE' as RESULTADO;
SELECT 'Ahora puedes acceder al módulo: /app/loans/guarantees' as INSTRUCCION;