-- ============================================================================
-- SCRIPT PARA INSERTAR ASISTENTES DE BETA A DEV-INTERNO
-- ============================================================================
-- Este script inserta los asistentes de la solicitud 1875742 desde BETA
-- a dev-interno, buscando/creando los registros relacionados necesarios.
--
-- INSTRUCCIONES:
-- 1. Cambiar el ID de solicitud_audiencia_id en dev-interno si es diferente (línea 18)
-- 2. Copiar los datos de la query de BETA y pegarlos en la sección de datos (línea 100+)
-- 3. Ejecutar en una transacción para poder hacer rollback si es necesario
-- ============================================================================

BEGIN;

-- ============================================================================
-- CONFIGURACIÓN - CAMBIAR ESTE ID SI ES NECESARIO
-- ============================================================================
DO $$
DECLARE
    v_solicitud_id INTEGER := 1875742; -- CAMBIAR SI LA SOLICITUD EN DEV TIENE OTRO ID
    v_folio_id INTEGER;
    v_institucion_id INTEGER;
BEGIN
    -- Verificar que la solicitud existe
    SELECT folio_id INTO v_folio_id 
    FROM solicitudes_audiencia 
    WHERE id = v_solicitud_id;
    
    IF v_folio_id IS NULL THEN
        RAISE EXCEPTION 'La solicitud % no existe en dev-interno', v_solicitud_id;
    END IF;
    
    -- Obtener institucion_id del folio
    SELECT institucion_id INTO v_institucion_id
    FROM folios
    WHERE id = v_folio_id;
    
    IF v_institucion_id IS NULL THEN
        RAISE EXCEPTION 'El folio % no tiene institucion_id asociado', v_folio_id;
    END IF;
    
    RAISE NOTICE 'Solicitud: %, Folio: %, Institución: %', v_solicitud_id, v_folio_id, v_institucion_id;
END $$;

-- ============================================================================
-- FUNCIONES AUXILIARES PARA BUSCAR/CREAR REGISTROS
-- ============================================================================

-- Función para obtener o crear usuario
CREATE OR REPLACE FUNCTION obtener_o_crear_usuario(
    p_pasaporte TEXT,
    p_pais_id TEXT,
    p_nombres TEXT,
    p_apellidos TEXT,
    p_email TEXT,
    p_telefono TEXT,
    p_otro_contacto TEXT,
    p_nacionalidad TEXT
) RETURNS INTEGER AS $$
DECLARE
    v_usuario_id INTEGER;
BEGIN
    -- Buscar usuario existente
    SELECT id INTO v_usuario_id
    FROM usuarios
    WHERE pasaporte = p_pasaporte
    AND pais_id = p_pais_id
    AND deleted_at IS NULL
    LIMIT 1;
    
    -- Si no existe, crearlo
    IF v_usuario_id IS NULL THEN
        INSERT INTO usuarios (
            pasaporte, pais_id, nombres, apellidos, email, telefono, 
            otro_contacto, nacionalidad, password, created_at, updated_at
        ) VALUES (
            p_pasaporte, p_pais_id, p_nombres, p_apellidos, p_email, p_telefono,
            p_otro_contacto, p_nacionalidad, 
            crypt(gen_random_uuid()::text, gen_salt('bf')),
            NOW(), NOW()
        ) RETURNING id INTO v_usuario_id;
    ELSE
        -- Actualizar datos si existen
        UPDATE usuarios SET
            nombres = COALESCE(p_nombres, nombres),
            apellidos = COALESCE(p_apellidos, apellidos),
            email = COALESCE(p_email, email),
            telefono = COALESCE(p_telefono, telefono),
            otro_contacto = COALESCE(p_otro_contacto, otro_contacto),
            nacionalidad = COALESCE(p_nacionalidad, nacionalidad),
            updated_at = NOW()
        WHERE id = v_usuario_id;
    END IF;
    
    RETURN v_usuario_id;
END;
$$ LANGUAGE plpgsql;

-- Función para obtener o crear representado
CREATE OR REPLACE FUNCTION obtener_o_crear_representado(
    p_nombre TEXT,
    p_tipo TEXT,
    p_pasaporte TEXT,
    p_pais_id TEXT,
    p_usuario_id INTEGER,
    p_actividades TEXT,
    p_giro TEXT,
    p_domicilio TEXT,
    p_representante_legal TEXT,
    p_naturaleza TEXT,
    p_directorio TEXT
) RETURNS INTEGER AS $$
DECLARE
    v_representado_id INTEGER;
BEGIN
    -- Buscar representado existente
    SELECT id INTO v_representado_id
    FROM representados
    WHERE nombre = p_nombre
    AND tipo = p_tipo
    AND COALESCE(pasaporte, '') = COALESCE(p_pasaporte, '')
    AND COALESCE(pais_id, '') = COALESCE(p_pais_id, '')
    AND usuario_id = p_usuario_id
    AND deleted_at IS NULL
    LIMIT 1;
    
    -- Si no existe, crearlo
    IF v_representado_id IS NULL THEN
        INSERT INTO representados (
            nombre, tipo, pasaporte, pais_id, usuario_id, actividades,
            giro, domicilio, representante_legal, naturaleza, directorio,
            created_at, updated_at
        ) VALUES (
            p_nombre, p_tipo, p_pasaporte, p_pais_id, p_usuario_id, p_actividades,
            p_giro, p_domicilio, p_representante_legal, p_naturaleza, p_directorio,
            NOW(), NOW()
        ) RETURNING id INTO v_representado_id;
    ELSE
        -- Actualizar datos si existen
        UPDATE representados SET
            actividades = COALESCE(p_actividades, actividades),
            giro = COALESCE(p_giro, giro),
            domicilio = COALESCE(p_domicilio, domicilio),
            representante_legal = COALESCE(p_representante_legal, representante_legal),
            naturaleza = COALESCE(p_naturaleza, naturaleza),
            directorio = COALESCE(p_directorio, directorio),
            updated_at = NOW()
        WHERE id = v_representado_id;
    END IF;
    
    RETURN v_representado_id;
END;
$$ LANGUAGE plpgsql;

-- Función para obtener o crear cargo activo
CREATE OR REPLACE FUNCTION obtener_o_crear_cargo_activo(
    p_usuario_id INTEGER,
    p_institucion_id INTEGER,
    p_folio_id INTEGER,
    p_remunerado BOOLEAN,
    p_voluntario BOOLEAN
) RETURNS INTEGER AS $$
DECLARE
    v_cargo_activo_id INTEGER;
BEGIN
    -- Buscar cargo activo existente
    SELECT id INTO v_cargo_activo_id
    FROM cargos_activos
    WHERE usuario_id = p_usuario_id
    AND institucion_id = p_institucion_id
    AND tipo = 'persona_natural'
    AND remunerado = p_remunerado
    AND deleted_at IS NULL
    LIMIT 1;
    
    -- Si no existe, crearlo
    IF v_cargo_activo_id IS NULL THEN
        INSERT INTO cargos_activos (
            tipo, fecha_inicio, institucion_id, relacion_id, usuario_id,
            folio_id, remunerado, voluntario, created_at, updated_at
        ) VALUES (
            'persona_natural', CURRENT_DATE, p_institucion_id, p_usuario_id, p_usuario_id,
            p_folio_id, p_remunerado, p_voluntario, NOW(), NOW()
        ) RETURNING id INTO v_cargo_activo_id;
    ELSE
        -- Actualizar datos si existen
        UPDATE cargos_activos SET
            voluntario = COALESCE(p_voluntario, voluntario),
            updated_at = NOW()
        WHERE id = v_cargo_activo_id;
    END IF;
    
    RETURN v_cargo_activo_id;
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- PROCESAR DATOS E INSERTAR EN TABLA PIVOT
-- ============================================================================
-- COPIAR AQUÍ LOS DATOS DE LA QUERY DE BETA
-- Formato: Para cada fila, extraer:
-- - De "rut": pasaporte (antes del espacio) y pais_id (entre paréntesis)
--   Ejemplo: "20.440.400-2 (VEN)" -> pasaporte: '20.440.400-2', pais_id: 'VEN'
-- - De "nombre_completo": nombres y apellidos (split por espacio, tomar primeros como nombres, últimos como apellidos)
--   Ejemplo: "Eduardo Alberto Repillosa Fornaro" -> nombres: 'Eduardo Alberto', apellidos: 'Repillosa Fornaro'
-- - usuario_email, usuario_telefono, usuario_otro_contacto, usuario_nacionalidad
-- - representado_nombre, representado_tipo, representado_pasaporte, representado_pais_id
-- - representado_actividades, representado_giro, representado_domicilio
-- - representado_representante_legal, representado_naturaleza, representado_directorio
-- - remunerado (true/false), voluntario (true/false)

DO $$
DECLARE
    v_solicitud_id INTEGER := 1875742; -- CAMBIAR SI ES NECESARIO
    v_folio_id INTEGER;
    v_institucion_id INTEGER;
    v_usuario_id INTEGER;
    v_representado_id INTEGER;
    v_cargo_activo_id INTEGER;
BEGIN
    -- Obtener folio_id e institucion_id
    SELECT folio_id INTO v_folio_id 
    FROM solicitudes_audiencia 
    WHERE id = v_solicitud_id;
    
    SELECT institucion_id INTO v_institucion_id
    FROM folios
    WHERE id = v_folio_id;
    
    -- ========================================================================
    -- INSERTAR AQUÍ LOS DATOS DE BETA
    -- ========================================================================
    -- Para cada fila de la query de BETA, crear un bloque similar al siguiente:
    -- (Copiar y pegar, ajustando los valores)
    
    -- Ejemplo 1:
    v_usuario_id := obtener_o_crear_usuario(
        '20.440.400-2',                    -- usuario_pasaporte (de rut, antes del espacio)
        'VEN',                              -- usuario_pais_id (de rut, entre paréntesis)
        'Eduardo Alberto',                  -- usuario_nombres (de nombre_completo)
        'Repillosa Fornaro',                -- usuario_apellidos (de nombre_completo)
        'elacion@transparencia.gob.cl',     -- usuario_email
        NULL,                               -- usuario_telefono
        NULL,                               -- usuario_otro_contacto
        NULL                                -- usuario_nacionalidad
    );
    
    v_representado_id := obtener_o_crear_representado(
        'Asociacion Civil Transparencia Electoral',  -- representado_nombre
        'persona_juridica',                         -- representado_tipo
        '30-71100435-0',                             -- representado_pasaporte
        'ARG',                                       -- representado_pais_id
        v_usuario_id,                                -- usuario_id
        NULL,                                        -- representado_actividades
        NULL,                                        -- representado_giro
        'Moreno 506, Piso 3 Oficina 5, Ciudad Autonoma de Buenos Aires, Argentina',  -- representado_domicilio
        'Eduardo Repillosa F.',                      -- representado_representante_legal
        'Asociacion Civil',                          -- representado_naturaleza
        NULL                                         -- representado_directorio
    );
    
    v_cargo_activo_id := obtener_o_crear_cargo_activo(
        v_usuario_id,
        v_institucion_id,
        v_folio_id,
        true,   -- remunerado (true = lobista, false = gestor)
        false   -- voluntario
    );
    
    -- Insertar en tabla pivot
    INSERT INTO solicitudes_audiencia_has_cargos_activos (
        solicitud_audiencia_id,
        cargo_activo_id,
        representado_id,
        created_at,
        updated_at
    )
    SELECT 
        v_solicitud_id,
        v_cargo_activo_id,
        v_representado_id,
        NOW(),
        NOW()
    WHERE NOT EXISTS (
        SELECT 1 FROM solicitudes_audiencia_has_cargos_activos
        WHERE solicitud_audiencia_id = v_solicitud_id
        AND cargo_activo_id = v_cargo_activo_id
        AND representado_id = v_representado_id
    );
    
    -- Agregar más bloques aquí para cada fila de BETA...
    -- (Copiar el bloque de ejemplo y ajustar los valores)
    
    RAISE NOTICE 'Inserción completada para solicitud %', v_solicitud_id;
END $$;

-- Limpiar funciones temporales
DROP FUNCTION IF EXISTS obtener_o_crear_usuario(TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT);
DROP FUNCTION IF EXISTS obtener_o_crear_representado(TEXT, TEXT, TEXT, TEXT, INTEGER, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT);
DROP FUNCTION IF EXISTS obtener_o_crear_cargo_activo(INTEGER, INTEGER, INTEGER, BOOLEAN, BOOLEAN);

-- ============================================================================
-- VERIFICAR REGISTROS INSERTADOS
-- ============================================================================
SELECT 
    'DEV-INTERNO (DESPUÉS)' AS ambiente,
    sahca.solicitud_audiencia_id,
    sahca.cargo_activo_id,
    ca.tipo AS tipo_cargo,
    ca.remunerado,
    u.nombres || ' ' || u.apellidos AS nombre_completo,
    u.pasaporte || COALESCE(' (' || u.pais_id || ')', '') AS rut,
    r.nombre AS representado_nombre,
    r.tipo AS representado_tipo
FROM solicitudes_audiencia_has_cargos_activos sahca
INNER JOIN cargos_activos ca ON ca.id = sahca.cargo_activo_id AND ca.deleted_at IS NULL
INNER JOIN usuarios u ON u.id = COALESCE(ca.usuario_id, ca.relacion_id) AND u.deleted_at IS NULL
LEFT JOIN representados r ON r.id = sahca.representado_id AND r.deleted_at IS NULL
WHERE sahca.solicitud_audiencia_id = 1875742
ORDER BY sahca.cargo_activo_id;

-- ============================================================================
-- FINALIZAR TRANSACCIÓN
-- ============================================================================
-- Si todo está bien, descomentar la siguiente línea para hacer COMMIT:
-- COMMIT;

-- Si hay problemas, descomentar la siguiente línea para hacer ROLLBACK:
-- ROLLBACK;
