-- create database nextcoop-bd;

create schema if not exists global;           -- Catálogos y Configuración
create schema if not exists core;
create schema if not exists socioeconomico;
create schema if not exists socios;

-- 3.A. TIPOS DE ADJUNTO
create table global.tipos_documento_adjunto (
    tipo_adjunto_id int generated by default as identity,
    codigo varchar(20) not null, -- 'CI_FRENTE', 'FACTURA_ANDE'
    descripcion varchar(100) not null,
    
    constraint pk_tipos_documento_adjunto primary key (tipo_adjunto_id),
    constraint uq_tipos_adjunto_codigo unique (codigo)
);

-- 3.B. REPOSITORIO DE ARCHIVOS
create table global.repositorio_documentos (
    documento_id bigint generated by default as identity,
    tipo_adjunto_id int not null,
    
    -- Polimorfismo (Para vincular con Solicitud o Persona)
    tabla_origen varchar(50) not null, 
    registro_origen_id bigint not null,
    
    nombre_archivo_original varchar(255) not null,
    ruta_absoluta_servidor varchar(500) not null, 
    mime_type varchar(100),
    
    creado_en timestamptz default now() not null,
    creado_por bigint, -- ID del Usuario que cargó el dato
    
    -- 2. Actualización (Traza de cambios de sueldo/cargo)
    actualizado_en timestamptz,
    actualizado_por bigint, -- ID del Usuario que editó
    
    -- 3. Borrado Lógico (Para corregir errores o finalizar trabajos)
    eliminado_en timestamptz, 
    eliminado_por bigint,
    
    constraint pk_repositorio_documentos primary key (documento_id),
    constraint fk_repositorio_tipo foreign key (tipo_adjunto_id) references global.tipos_documento_adjunto(tipo_adjunto_id)
);

-- 1.A. MAESTRO DE ESTADOS (Falta para el fk_personas_estado)
create table global.estados_sistema (
    estado_id int generated by default as identity,
    dominio varchar(50) not null, -- 'PERSONA', 'SOLICITUD', 'SOCIO'
    codigo varchar(50) not null,  -- 'ACTIVO', 'PENDIENTE', 'BLOQUEADO'
    descripcion varchar(100) not null,
    es_estado_final boolean default false,
    
    constraint pk_estados_sistema primary key (estado_id),
    constraint uq_estados_dominio_codigo unique (dominio, codigo)
);

-- cargos de representantes legales
create table global.cargos (
    cargo_id int generated by default as identity,
    descripcion varchar(100) not null, -- 'PRESIDENTE', 'VICEPRESIDENTE', 'SÍNDICO'
    es_alta_gerencia boolean default false, 
    activo boolean default true,
    constraint pk_cargos_autoridades primary key (cargo_id),
    constraint uq_cargos_descripcion unique (descripcion)
);

-- 1.C. TIPO DE RELACIÓN LABORAL (Falta para el Perfil Laboral del PDF)
create table global.tipos_relacion_laboral (
    tipo_relacion_id int generated by default as identity,
    descripcion varchar(100) not null, 
    
    constraint pk_tipos_relacion_laboral primary key (tipo_relacion_id)
);

-- Dropdown: Motivo (Ej: "Candidato a Socio", "Garante", "Representante")
create table global.motivos_registro (
    motivo_id int generated by default as identity,
    descripcion varchar(50) not null,
    
    constraint pk_motivos_registro primary key (motivo_id),
    constraint uq_motivos_desc unique (descripcion)
);

-- Dropdown: Sexo (Ej: "Masculino", "Femenino")
create table global.sexos (
    sexo_id int generated by default as identity,
    sigla char(1) not null, 
    descripcion varchar(20) not null,
    
    constraint pk_sexos primary key (sexo_id),
    constraint uq_sexos_sigla unique (sigla)
);

-- Dropdown: Tipo Documento (Ej: "Cédula de Identidad", "Pasaporte")
create table global.tipos_documento (
    tipo_documento_id int generated by default as identity,
    codigo varchar(10) not null, 
    descripcion varchar(50) not null,
    mascara_validacion varchar(100),
    es_fiscal boolean default false,
    
    constraint pk_tipos_documento primary key (tipo_documento_id),
    constraint uq_tipos_doc_codigo unique (codigo)
);

-- Dropdown: Zona de Residencia (Ej: "Urbana (Capital/Central)", "Rural", "Sub-Urbana")
create table global.zonas_geograficas (
    zona_id int generated by default as identity,
    descripcion varchar(50) not null, 
    
    constraint pk_zonas_geograficas primary key (zona_id)
);

-- 1. PAÍS
-- Aunque solo operes en Paraguay, mantener esta tabla es vital para la normalización.
create table global.paises (
    pais_id int generated by default as identity,
    nombre varchar(100) not null,    
    siglas char(3), -- "PRY"
    
    constraint pk_paises primary key (pais_id),
    constraint uq_paises_iso unique (siglas)
);

-- 2. DEPARTAMENTOS (Primer Nivel Administrativo)
-- El INE define 17 Departamentos + Asunción (Capital) que tiene rango departamental.
create table global.departamentos (
    departamento_id int generated by default as identity,
    pais_id int not null,    
    nombre varchar(100) not null,    
    codigo_ine varchar(2) not null, 
    
    constraint pk_departamentos primary key (departamento_id),
    constraint fk_departamentos_pais foreign key (pais_id) references global.paises(pais_id)    
);

-- 3. DISTRITOS (Segundo Nivel - Equivalente a "Ciudad" o Municipio)
-- El INE los llama "Distritos" a las ciudades
create table global.distritos (
    distrito_id int generated by default as identity,
    departamento_id int not null,    
    nombre varchar(100) not null,    
    codigo_ine varchar(4) not null,   
        
    constraint pk_distritos primary key (distrito_id),
    constraint fk_distritos_departamento foreign key (departamento_id) references global.departamentos(departamento_id)    
);

-- 4. BARRIOS Y LOCALIDADES (Tercer Nivel)
-- El INE divide los distritos en:
-- - Barrios (Zona Urbana)
-- - Localidades/Compañías (Zona Rural)
create table global.localidades (
    localidad_id int generated by default as identity,
    distrito_id int not null,    
    nombre varchar(150) not null,    
    -- Tipo: 'BARRIO' (Urbano) o 'COMPAÑIA'/'LOCALIDAD' (Rural)
    tipo_division varchar(20) default 'BARRIO',
    codigo_ine varchar(10),     
    constraint pk_localidad primary key (localidad_id),
    constraint fk_localidad_distrito foreign key (distrito_id) references global.distritos(distrito_id)     
);

-- Dropdown: Estado civil (Ej: "Casado/a")
create table global.estados_civiles (
    estado_civil_id int generated by default as identity,
    descripcion varchar(50) not null,
    
    constraint pk_estados_civiles primary key (estado_civil_id)
);

-- Dropdown: Régimen Patrimonial (Ej: "Separación de Bienes")
create table global.regimenes_matrimoniales (
    regimen_id int generated by default as identity,
    descripcion varchar(100) not null,
    
    constraint pk_regimenes_matrimoniales primary key (regimen_id)
);

-- 5.1 RELACIONES (Vínculos)
-- Sub-sección Parentesco, representantes legales
create table global.tipos_vinculo (
    tipo_vinculo_id int generated by default as identity,
    descripcion varchar(50) not null, 
    es_reciproco boolean default false,
    categoria_aplicacion varchar(20) default 'AMBOS',    
    constraint pk_tipos_vinculo primary key (tipo_vinculo_id)
);

-- Dropdown: Nivel Educativo (Ej: "Universitario")
create table global.niveles_educativos (
    nivel_educativo_id int generated by default as identity,
    descripcion varchar(100) not null,
    
    constraint pk_niveles_educativos primary key (nivel_educativo_id)
);

-- Dropdown: Fuente Principal de Ingresos (Ej: "Empleado/a (Sector Privado)")
create table global.fuentes_ingresos (
    fuente_id int generated by default as identity,
    descripcion varchar(100) not null, -- 'Empleado Privado', 'Funcionario Público'
    
    constraint pk_fuentes_ingresos primary key (fuente_id)
);

-- Funciona tanto para personas físicas y jurídicas
-- Para físicas: actividad principal (socios), actividad económica del empleador
-- Para jurídica: actividad económica
create table global.actividades_economicas (
    actividad_id int generated by default as identity,    
    -- El nombre del Rubro / Sector
    descripcion varchar(150) not null,     
    -- Clasificación Macro (Útil para reportes de concentración de riesgo)
    sector_macro varchar(50), -- 'PRIMARIO', 'SECUNDARIO', 'TERCIARIO', 'PÚBLICO'        
    -- ¿Aparece en "Actividad Principal" (Lo que hace el socio)?
    -- Ej: "Servicios Profesionales" (Sí), "Gobierno" (Sí)
    aplica_persona boolean default true,     
    -- ¿Aparece en "Actividad Económica Empleador" (De dónde viene la plata)?
    -- Ej: "Gobierno" (Sí), "Jubilado" (No, eso es una condición, no un rubro empresa)
    aplica_empleador boolean default true,    
    -- Código regulatorio (CIIU / SEPRELAD / INCOOP) para reportes
    codigo_regulador varchar(20),     
    constraint pk_actividades_economicas primary key (actividad_id),
    constraint uq_actividades_desc unique (descripcion)
);

-- Dropdown: Situación Habitacional (Ej: "Cedida/Familiar")
create table global.tipos_vivienda (
    tipo_vivienda_id int generated by default as identity,
    descripcion varchar(100) not null,
    
    constraint pk_tipos_vivienda primary key (tipo_vivienda_id)
);

-- Dropdown: Tenencia de la Tierra (Ej: "No aplica", "Propietario")
create table global.tipos_tenencia_tierra (
    tenencia_id int generated by default as identity,
    descripcion varchar(100) not null,
    
    constraint pk_tipos_tenencia_tierra primary key (tenencia_id)
);

-- tabla de profesiones y ocupaciones
create table global.ocupaciones (
    ocupacion_id int generated by default as identity,
        -- El nombre de la profesión (Ej: "Contador Público", "Estudiante")
    descripcion varchar(150) not null,
    -- COMPORTAMIENTO DEL FORMULARIO (Inteligencia de UI)
    -- Si es FALSE (Ej: Estudiante, Ama de Casa), el Frontend puede
    -- ocultar campos como "Ruc Empleador" o "Antigüedad Laboral".
    requiere_datos_laborales boolean default true,
    constraint pk_ocupaciones primary key (ocupacion_id),
    constraint uq_ocupaciones_desc unique (descripcion)
);

-- Persoja jurídica

-- 1.1 TIPO DE SOCIEDAD (Sección Identificación)
-- Dropdown: "Sociedad de Responsabilidad Limitada", "S.A.", etc.
create table global.tipos_sociedad (
    tipo_sociedad_id int generated by default as identity,
    siglas varchar(10), -- 'SRL', 'SA', 'EAS'
    descripcion varchar(100) not null,
    
    constraint pk_tipos_sociedad primary key (tipo_sociedad_id),
    constraint uq_tipos_sociedad_desc unique (descripcion)
);

-- 1.3 RÉGIMEN TRIBUTARIO (Sección Perfil de Negocio)
-- Dropdown: "IVA General", "IRE Simple"
create table global.regimenes_tributarios (
    regimen_id int generated by default as identity,
    descripcion varchar(100) not null,
    siglas varchar(20),
    
    constraint pk_regimenes_tributarios primary key (regimen_id)
);

-- 1.4 RANGOS DE INGRESOS (Sección Perfil de Negocio)
-- Dropdown: "500-1500 millones"
create table global.rangos_ingresos (
    rango_ingreso_id int generated by default as identity,
    descripcion varchar(100) not null, 
    valor_minimo numeric(19, 2),
    valor_maximo numeric(19, 2),
    moneda varchar(3) default 'PYG',
    
    constraint pk_rangos_ingresos primary key (rango_ingreso_id)
);

-- 1.5 RANGOS DE EMPLEADOS (Sección Perfil de Negocio)
-- Dropdown: "6-20", "21-50"
create table global.rangos_empleados (
    rango_empleado_id int generated by default as identity,
    descripcion varchar(50) not null, 
    min_empleados int,
    max_empleados int,
    
    constraint pk_rangos_empleados primary key (rango_empleado_id)
);

-- =================================================================================
-- ESQUEMA CORE: Personas
-- =================================================================================

create table core.personas (
    persona_id bigint generated by default as identity,
    
    -- MIGRACIÓN SIDE-BY-SIDE (Identidad Dual)
    -- Permite coexistencia con el sistema legado y búsqueda por código viejo.
    id_legado varchar(50), 
    
    -- DISCRIMINADOR Y MOTIVO
    tipo_persona char(1) not null, -- 'F' (Física) o 'J' (Jurídica)
    motivo_id int not null, -- 'CANDIDATO', 'GARANTE', 'PROSPECTO'
    
    -- IDENTIDAD LEGAL (Unique Constraint abajo)
    pais_emision_id int not null,
    tipo_documento_id int not null, -- FK a global.tipos_documento
    numero_documento varchar(30) not null,
    nombre_completo_normalizado varchar(255) not null, -- Para búsquedas rápidas sin JOINs
    
    -- DATOS PROMOVIDOS (Comunes a Físicas y Jurídicas)
    -- "Fecha de Nacimiento" (Física) o "Fecha Constitución" (Jurídica)
    fecha_nacimiento date not null, 
    
    -- "Actividad Económica": Unificada en una sola tabla global
    actividad_economica_id int, 
    
    -- "Régimen Tributario": Aplica a empresas y unipersonales
    regimen_tributario_id int,
    
    -- CONTACTO PRINCIPAL (Normalizado en el padre para acceso rápido)
    email_principal varchar(150),
    telefono_principal varchar(50),
    sitio_web varchar(150),
    
    -- ESTADO Y CONTROL
    estado_id int not null, -- FK a global.estados_sistema
    version int default 1,  -- Optimistic Locking (Para prevenir sobreescritura concurrente)

    -- AUDITORÍA
    creado_en timestamptz default now() not null,
    creado_por bigint, 
    actualizado_en timestamptz,
    actualizado_por bigint,
    eliminado_en timestamptz, -- Borrado Lógico
    eliminado_por bigint,

    -- CONSTRAINTS
    constraint pk_personas primary key (persona_id),
    
    constraint fk_personas_pais foreign key (pais_emision_id) references global.paises(pais_id),
    constraint fk_personas_tipo_doc foreign key (tipo_documento_id) references global.tipos_documento(tipo_documento_id),
    
    -- FK a la tabla UNIFICADA de actividades (Soporta rubros de personas y empresas)
    constraint fk_personas_actividad foreign key (actividad_economica_id) references global.actividades_economicas(actividad_id),
    constraint fk_personas_regimen foreign key (regimen_tributario_id) references global.regimenes_tributarios(regimen_id),
    constraint fk_personas_estado foreign key (estado_id) references global.estados_sistema(estado_id),
    constraint fk_personas_motivo foreign key (motivo_id) references global.motivos_registro(motivo_id),
    
    -- Regla de Integridad de Identidad (No pueden existir dos CI iguales en el mismo país)
    constraint uq_personas_identidad unique (pais_emision_id, tipo_documento_id, numero_documento),
    
    -- Validación de Discriminador
    constraint ck_personas_tipo check (tipo_persona in ('F', 'J'))
);

-- ÍNDICES ESTRATÉGICOS
-- Vital para la migración y la búsqueda por código viejo
create unique index uq_personas_legado on core.personas(id_legado) where id_legado is not null;
-- Vital para búsquedas tipo "Like" por nombre
create index idx_personas_nombre on core.personas(nombre_completo_normalizado);

-- =================================================================================
-- 2. TABLA HIJA: PERSONAS FÍSICAS (Especialización)
-- =================================================================================
create table core.personas_fisicas (
    persona_id bigint not null, -- PK y FK al mismo tiempo
    
    -- IDENTIDAD DETALLADA
    nombres varchar(100) not null,
    apellidos varchar(100) not null,
    sexo_id int not null,
    
    -- ESTADO CIVIL Y FAMILIAR
    estado_civil_id int,
    regimen_matrimonial_id int, -- Solo si es Casado
    
    -- ORIGEN
    distrito_nacimiento_id int,
    
    -- PERFIL SOCIOECONÓMICO BÁSICO (El detalle laboral va en socioeconomico.perfiles)
    nivel_educativo_id int,
    ocupacion_id int, -- profesión/ocupación
    
    -- CARGAS FAMILIARES (Para análisis de capacidad de pago)
    cantidad_dependientes int default 0,
    tiene_hijos_escolar boolean default false,
    
    -- PERFIL DE VIVIENDA Y SALUD
    situacion_vivienda_id int, -- Propia, Alquilada
    tenencia_tierra_id int,    -- Propietario, Ocupante
    posee_seguro_medico boolean default false,
    
    -- AUDITORÍA DE EDICIÓN
    creado_en timestamptz default now() not null,
    creado_por bigint,
    actualizado_en timestamptz,
    actualizado_por bigint,

    constraint pk_personas_fisicas primary key (persona_id),
    
    -- Herencia (1 a 1 estricta)
    constraint fk_fisicas_persona foreign key (persona_id) references core.personas(persona_id) on delete cascade,
    
    -- Referencias Globales
    constraint fk_fisicas_sexo foreign key (sexo_id) references global.sexos(sexo_id),
    constraint fk_fisicas_est_civil foreign key (estado_civil_id) references global.estados_civiles(estado_civil_id),
    constraint fk_fisicas_regimen foreign key (regimen_matrimonial_id) references global.regimenes_matrimoniales(regimen_id),
    constraint fk_fisicas_educacion foreign key (nivel_educativo_id) references global.niveles_educativos(nivel_educativo_id),
    constraint fk_fisicas_distrito foreign key (distrito_nacimiento_id) references global.distritos(distrito_id),
    constraint fk_fisicas_vivienda foreign key (situacion_vivienda_id) references global.tipos_vivienda(tipo_vivienda_id),
    constraint fk_fisicas_tierra foreign key (tenencia_tierra_id) references global.tipos_tenencia_tierra(tenencia_id),
    constraint fk_fisicas_ocupacion foreign key (ocupacion_id) references global.ocupaciones(ocupacion_id)
);

-- =================================================================================
-- 3. TABLA HIJA: PERSONAS JURÍDICAS (Especialización)
-- =================================================================================
create table core.personas_juridicas (
    persona_id bigint not null, -- PK y FK
    
    -- IDENTIDAD CORPORATIVA
    razon_social varchar(200) not null,
    nombre_fantasia varchar(200), -- Nombre Comercial
    
    -- TIPO SOCIETARIO (SA, SRL, EAS)
    tipo_sociedad_id int not null,
    
    -- VOLUMETRÍA Y SEGMENTACIÓN (Dropdowns de Rango)
    rango_ingreso_anual_id int,
    rango_cantidad_empleados_id int,
    
    -- AUDITORÍA
    creado_en timestamptz default now() not null,
    creado_por bigint, 
    actualizado_en timestamptz,
    actualizado_por bigint,

    constraint pk_personas_juridicas primary key (persona_id),
    
    -- Herencia
    constraint fk_juridicas_persona foreign key (persona_id) references core.personas(persona_id) on delete cascade,
    
    -- Referencias Globales
    constraint fk_juridicas_tipo_soc foreign key (tipo_sociedad_id) references global.tipos_sociedad(tipo_sociedad_id),
    constraint fk_juridicas_rango_ing foreign key (rango_ingreso_anual_id) references global.rangos_ingresos(rango_ingreso_id),
    constraint fk_juridicas_rango_emp foreign key (rango_cantidad_empleados_id) references global.rangos_empleados(rango_empleado_id)
);

-- =================================================================================
-- 4. TABLA: DIRECCIONES (Polimórfica y Georreferenciada)
-- =================================================================================
create table core.direcciones (
    direccion_id bigint generated by default as identity,
    
    persona_id bigint not null,
    
    -- CLASIFICACIÓN
    categoria_uso varchar(20) not null default 'DOMICILIO', -- 'DOMICILIO', 'LABORAL', 'SUCURSAL'
    es_principal boolean default false,
    
    -- UBICACIÓN
    distrito_id int not null, -- Distrito según INE
    barrio varchar(100),    -- Barrio o Localidad (Texto o futuro ID si se normaliza)
    calle_principal varchar(150) not null,
    numeracion varchar(20),
    referencia_ubicacion text, -- "Frente a la plaza..."
    
    -- GIS (Google Maps)
    latitud decimal(10, 8),
    longitud decimal(11, 8),
    
    -- AUDITORÍA
    creado_en timestamptz default now() not null,
    creado_por bigint,
    actualizado_en timestamptz,
    actualizado_por bigint,

    constraint pk_direcciones primary key (direccion_id),
    constraint fk_direcciones_persona foreign key (persona_id) references core.personas(persona_id),
    constraint fk_direcciones_distrito foreign key (distrito_id) references global.distritos(distrito_id)
);


--- parentesco, representantes legales (lista)
create table core.vinculos_personas (
    vinculo_id bigint generated by default as identity,
    persona_origen_id bigint not null, -- La Empresa (o el Padre)
    persona_destino_id bigint not null, -- El Representante (o el Hijo)
    tipo_vinculo_id int not null, -- FK a global.tipos_vinculo ('REPRESENTANTE', 'ACCIONISTA')
    cargo_id int, -- FK a global.cargos_autoridades ('PRESIDENTE')
    -- 6. AUDITORÍA COMPLETA (Estándar Gold)
    creado_en timestamptz default now() not null,
    creado_por bigint, 
    actualizado_en timestamptz,
    actualizado_por bigint,
    eliminado_en timestamptz, -- Borrado Lógico (Mantiene el histórico)
    eliminado_por bigint,
    
    constraint pk_vinculos_personas primary key (vinculo_id),
    
    constraint fk_vinculos_origen 
        foreign key (persona_origen_id) references core.personas(persona_id),
        
    constraint fk_vinculos_destino 
        foreign key (persona_destino_id) references core.personas(persona_id),
        
    constraint fk_vinculos_tipo 
        foreign key (tipo_vinculo_id) references global.tipos_vinculo(tipo_vinculo_id),
        
    constraint fk_vinculos_cargo 
        foreign key (cargo_id) references global.cargos(cargo_id),
        
    -- Validación: No puede ser el mismo origen y destino (Yo no soy mi propio padre)
    constraint ck_vinculos_bucle check (persona_origen_id <> persona_destino_id)
);



create table socioeconomico.perfiles_laborales (
    laboral_id bigint generated by default as identity,
    persona_id bigint not null, 
    fuente_ingresos_id int not null,      -- Ej: "Dependiente", "Cuenta Propia"
    tipo_relacion_id int,                 -- Ej: "Nombrado", "Contratado"    
    -- DOBLE REFERENCIA A ACTIVIDADES ECONÓMICAS
    actividad_principal_id int not null,       -- Lo que hace el SOCIO (Ej: "Servicios Profesionales")
    actividad_rubro_empleador_id int,          -- Lo que hace la EMPRESA (Ej: "Banca", "Ganadería")
    nombre_empresa_empleador varchar(150),
    cargo_puesto_ocupado varchar(150),         -- Ej: "Gerente de Logística" (Distinto a su profesión)
    antiguedad_anos int default 0,
    antiguedad_meses int default 0,
    ingreso_neto_mensual numeric(19, 2) not null default 0,
    es_ingreso_principal boolean default true, -- Flag para sumar capacidad de pago    
    creado_en timestamptz default now() not null,
    creado_por bigint, -- ID del Usuario que cargó el dato    
    actualizado_en timestamptz,
    actualizado_por bigint, -- ID del Usuario que editó    
    eliminado_en timestamptz, 
    eliminado_por bigint,
    constraint pk_perfiles_laborales primary key (laboral_id),
    constraint fk_laboral_persona 
        foreign key (persona_id) references core.personas(persona_id) on delete cascade,
    constraint fk_laboral_fuente 
        foreign key (fuente_ingresos_id) references global.fuentes_ingresos(fuente_id),
    constraint fk_laboral_relacion 
        foreign key (tipo_relacion_id) references global.tipos_relacion_laboral(tipo_relacion_id),
    constraint fk_laboral_act_propia 
        foreign key (actividad_principal_id) references global.actividades_economicas(actividad_id),
    constraint fk_laboral_act_empleador 
        foreign key (actividad_rubro_empleador_id) references global.actividades_economicas(actividad_id)    
);

---------- SOCIOS

create table socios.solicitudes_ingreso (
    solicitud_id bigint generated by default as identity,
    
    -- 1. SOLICITANTE
    persona_id bigint not null, -- El postulante (FK a Core)
    
    -- 2. DATOS DE LA SOLICITUD (Pantalla 1: Formulario de Entrada)
    fecha_solicitud date default current_date not null,
    es_reasociacion boolean default false, -- Checkbox del diseño
    observaciones_iniciales text, -- Notas del oficial al cargar
    
    -- 3. VINCULACIONES CRÍTICAS (Compliance y Riesgo)
    -- Corrección: Agregamos las FKs que faltaban para conectar con otros módulos
    manifestacion_bienes_id bigint, -- Referencia al futuro módulo de Bienes
    pep_vinculacion_id bigint,      -- Referencia si se marcó como PEP (FK a riesgo.listas_control)
    
    -- 4. ESTADO DEL WORKFLOW (Corrección: FK a Tabla Central)
    -- Ya no es varchar. Ahora apunta al maestro de estados para integridad.
    estado_id int not null, 
    
    -- 5. DATOS DE RESOLUCIÓN (Pantalla 2 y 3: Análisis y Aprobación/Rechazo)
    fecha_resolucion date,
    usuario_resolucion_id bigint, -- El miembro del consejo/jefe que dio el click final
    
    -- Si se APRUEBA (Datos de la Resolución del Consejo)
    acto_administrativo_resolucion varchar(150), -- "Aprobado por Res. Nro 715/2025"
    fecha_ingreso_oficial date, -- Puede ser distinta a la de resolución
    
    -- Si se RECHAZA
    observaciones_resolucion text, -- El dictamen final (Motivo del rechazo)
    
    -- 6. AUDITORÍA COMPLETA (Estándar Gold)
    creado_en timestamptz default now() not null,
    creado_por bigint, 
    actualizado_en timestamptz,
    actualizado_por bigint,
    eliminado_en timestamptz, -- Borrado lógico (Por si se cancela la solicitud antes de procesar)
    eliminado_por bigint,

    -- =========================================================================
    -- CONSTRAINTS
    -- =========================================================================
    constraint pk_solicitudes_ingreso primary key (solicitud_id),
    
    -- Integridad Referencial
    constraint fk_solicitud_persona 
        foreign key (persona_id) references core.personas(persona_id),
        
    constraint fk_solicitud_estado 
        foreign key (estado_id) references global.estados_sistema(estado_id),
        
    -- Vínculo con PEP (Asumiendo que ya creamos el esquema riesgo.listas_control)
    constraint fk_solicitud_pep 
        foreign key (pep_vinculacion_id) references riesgo.listas_control(lista_id)
);

create table socios.maestro_socios (
    socio_id bigint generated by default as identity,
    
    -- 1. VINCULACIÓN PRINCIPAL
    persona_id bigint not null,
    
    -- Origen: ¿De qué solicitud nació este registro de socio?
    -- Vital para auditoría: "Este socio ingresó por la Solicitud #1005 aprobada el..."
    solicitud_origen_id bigint not null, 
    
    -- 2. IDENTIFICACIÓN DEL SOCIO
    numero_socio int not null,           -- El Nro. impreso en el carnet (Ej: 15400)
    codigo_socio_legado varchar(50),     -- Para migración (Ej: "A-15400/2010")
    
    -- 4. CICLO DE VIDA (Historial de Membresía)
    fecha_ingreso date not null,         -- Fecha de la Resolución de Aprobación
    
    fecha_salida date,                   -- NULL = ACTIVO. Si tiene fecha, es EX-SOCIO.
    motivo_salida varchar(200),          -- "Renuncia Voluntaria", "Expulsión", "Fallecimiento"
    
    -- 5. ESTADO DEL SISTEMA
    -- FK a global.estados_sistema ('ACTIVO', 'RENUNCIANTE', 'BLOQUEADO', 'FALLECIDO')
    estado_id int not null, 
    
    -- 6. AUDITORÍA COMPLETA (Trazabilidad)
    creado_en timestamptz default now() not null,
    creado_por bigint,
    actualizado_en timestamptz,
    actualizado_por bigint, 
    -- Nota: Aquí no usamos eliminado_en porque la "baja" es un evento de negocio (fecha_salida)
    
    -- =========================================================================
    -- CONSTRAINTS
    -- =========================================================================
    constraint pk_maestro_socios primary key (socio_id),
    
    -- Integridad Referencial
    constraint fk_socios_persona 
        foreign key (persona_id) references core.personas(persona_id),
        
    constraint fk_socios_solicitud 
        foreign key (solicitud_origen_id) references socios.solicitudes_ingreso(solicitud_id),
        
    constraint fk_socios_estado 
        foreign key (estado_id) references global.estados_sistema(estado_id),
    
    -- Unicidad del Número de Socio
    constraint uq_socios_numero unique (numero_socio)
);