Desarrollo CORE

-- create database nextcoop-bd;

create schema if not exists global;

create schema if not exists core;

create schema if not exists socioeconomico;

create schema if not exists socios;

-- =================================================================================

-- ESQUEMA GLOBAL (Catálogos)

-- Cambio: PKs a 'id' y agregado 'activo' para Soft Delete

-- =================================================================================

-- 3.A. TIPOS DE ADJUNTO

create table global.tipos_documento_adjunto (

    id int generated by default as identity,

    -- Antes: tipo_adjunto_id

    codigo varchar(20) not null,

    descripcion varchar(100) not null,

    activo boolean default true,

    -- Soft Delete

    constraint pk_tipos_documento_adjunto primary key (id),

    constraint uq_tipos_adjunto_codigo unique (codigo)

);

-- 3.B. REPOSITORIO DE ARCHIVOS (Esta es transaccional, lleva full audit)

create table global.repositorio_documentos (

    id bigint generated by default as identity,

    -- Antes: documento_id

    tipo_adjunto_id int not null,

    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),

    -- Auditoría Full

    creado_en timestamptz default now() not null,

    creado_por bigint,

    actualizado_en timestamptz,

    actualizado_por bigint,

    eliminado_en timestamptz,

    eliminado_por bigint,

    constraint pk_repositorio_documentos primary key (id),

    constraint fk_repositorio_tipo foreign key (tipo_adjunto_id) references global.tipos_documento_adjunto(id)

);

-- 1.A. MAESTRO DE ESTADOS

create table global.estados_sistema (

    id int generated by default as identity,

    -- Antes: estado_id

    dominio varchar(50) not null,

    codigo varchar(50) not null,

    descripcion varchar(100) not null,

    es_estado_final boolean default false,

    activo boolean default true,

    constraint pk_estados_sistema primary key (id),

    constraint uq_estados_dominio_codigo unique (dominio, codigo)

);

-- cargos de representantes legales

create table global.cargos (

    id int generated by default as identity,

    -- Antes: cargo_id

    descripcion varchar(100) not null,

    es_alta_gerencia boolean default false,

    activo boolean default true,

    constraint pk_cargos primary key (id),

    -- OJO: Nombre constraint original mantenido

    constraint uq_cargos_descripcion unique (descripcion)

);

-- 1.C. TIPO DE RELACIÓN LABORAL

create table global.tipos_relacion_laboral (

    id int generated by default as identity,

    -- Antes: tipo_relacion_id

    descripcion varchar(100) not null,

    activo boolean default true,

    constraint pk_tipos_relacion_laboral primary key (id)

);

-- Dropdown: Motivo

create table global.motivos_registro (

    id int generated by default as identity,

    -- Antes: motivo_id

    descripcion varchar(50) not null,

    activo boolean default true,

    constraint pk_motivos_registro primary key (id),

    constraint uq_motivos_desc unique (descripcion)

);

-- Dropdown: Sexo

create table global.sexos (

    id int generated by default as identity,

    -- Antes: sexo_id

    sigla char(1) not null,

    descripcion varchar(20) not null,

    activo boolean default true,

    constraint pk_sexos primary key (id),

    constraint uq_sexos_sigla unique (sigla)

);

-- Dropdown: Tipo Documento

create table global.tipos_documento (

    id int generated by default as identity,

    -- Antes: tipo_documento_id

    codigo varchar(10) not null,

    descripcion varchar(50) not null,

    mascara_validacion varchar(100),

    es_fiscal boolean default false,

    activo boolean default true,

    constraint pk_tipos_documento primary key (id),

    constraint uq_tipos_doc_codigo unique (codigo)

);

-- Dropdown: Zona de Residencia

create table global.zonas_geograficas (

    id int generated by default as identity,

    -- Antes: zona_id

    descripcion varchar(50) not null,

    activo boolean default true,

    constraint pk_zonas_geograficas primary key (id)

);

-- 1. PAÍS

create table global.paises (

    id int generated by default as identity,

    -- Antes: pais_id

    nombre varchar(100) not null,

    siglas char(3),

    activo boolean default true,

    constraint pk_paises primary key (id),

    constraint uq_paises_iso unique (siglas)

);

-- 2. DEPARTAMENTOS

create table global.departamentos (

    id int generated by default as identity,

    -- Antes: departamento_id

    pais_id int not null,

    nombre varchar(100) not null,

    activo boolean default true,

    constraint pk_departamentos primary key (id),

    constraint fk_departamentos_pais foreign key (pais_id) references global.paises(id)

);

-- 3. DISTRITOS

create table global.distritos (

    id int generated by default as identity,

    -- Antes: distrito_id

    departamento_id int not null,

    nombre varchar(100) not null,

    codigo_ine varchar(4) not null,

    activo boolean default true,

    constraint pk_distritos primary key (id),

    constraint fk_distritos_departamento foreign key (departamento_id) references global.departamentos(id)

);

-- 4. BARRIOS Y LOCALIDADES

create table global.localidades (

    id int generated by default as identity,

    -- Antes: localidad_id

    distrito_id int not null,

    nombre varchar(150) not null,

    tipo_division varchar(20) default 'BARRIO',

    codigo_ine varchar(10),

    activo boolean default true,

    constraint pk_localidad primary key (id),

    constraint fk_localidad_distrito foreign key (distrito_id) references global.distritos(id)

);

-- Dropdown: Estado civil

create table global.estados_civiles (

    id int generated by default as identity,

    -- Antes: estado_civil_id

    descripcion varchar(50) not null,

    activo boolean default true,

    constraint pk_estados_civiles primary key (id)

);

-- Dropdown: Régimen Patrimonial

create table global.regimenes_matrimoniales (

    id int generated by default as identity,

    -- Antes: regimen_id

    descripcion varchar(100) not null,

    activo boolean default true,

    constraint pk_regimenes_matrimoniales primary key (id)

);

-- 5.1 RELACIONES (Vínculos)

create table global.tipos_vinculo (

    id int generated by default as identity,

    -- Antes: tipo_vinculo_id

    descripcion varchar(50) not null,

    es_reciproco boolean default false,

    categoria_aplicacion varchar(20) default 'AMBOS',

    activo boolean default true,

    constraint pk_tipos_vinculo primary key (id)

);

-- Dropdown: Nivel Educativo

create table global.niveles_educativos (

    id int generated by default as identity,

    -- Antes: nivel_educativo_id

    descripcion varchar(100) not null,

    activo boolean default true,

    constraint pk_niveles_educativos primary key (id)

);

-- Dropdown: Fuente Principal de Ingresos

create table global.fuentes_ingresos (

    id int generated by default as identity,

    -- Antes: fuente_id

    descripcion varchar(100) not null,

    activo boolean default true,

    constraint pk_fuentes_ingresos primary key (id)

);

-- Actividades Económicas

create table global.actividades_economicas (

    id int generated by default as identity,

    -- Antes: actividad_id

    descripcion varchar(150) not null,

    sector_macro varchar(50),

    aplica_persona boolean default true,

    aplica_empleador boolean default true,

    codigo_regulador varchar(20),

    activo boolean default true,

    constraint pk_actividades_economicas primary key (id),

    constraint uq_actividades_desc unique (descripcion)

);

-- Dropdown: Situación Habitacional

create table global.tipos_vivienda (

    id int generated by default as identity,

    -- Antes: tipo_vivienda_id

    descripcion varchar(100) not null,

    activo boolean default true,

    constraint pk_tipos_vivienda primary key (id)

);

-- Dropdown: Tenencia de la Tierra

create table global.tipos_tenencia_tierra (

    id int generated by default as identity,

    -- Antes: tenencia_id

    descripcion varchar(100) not null,

    activo boolean default true,

    constraint pk_tipos_tenencia_tierra primary key (id)

);

-- tabla de profesiones y ocupaciones

create table global.ocupaciones (

    id int generated by default as identity,

    -- Antes: ocupacion_id

    descripcion varchar(150) not null,

    requiere_datos_laborales boolean default true,

    activo boolean default true,

    constraint pk_ocupaciones primary key (id),

    constraint uq_ocupaciones_desc unique (descripcion)

);

-- 1.1 TIPO DE SOCIEDAD

create table global.tipos_sociedad (

    id int generated by default as identity,

    -- Antes: tipo_sociedad_id

    siglas varchar(10),

    descripcion varchar(100) not null,

    activo boolean default true,

    constraint pk_tipos_sociedad primary key (id),

    constraint uq_tipos_sociedad_desc unique (descripcion)

);

-- 1.3 RÉGIMEN TRIBUTARIO

create table global.regimenes_tributarios (

    id int generated by default as identity,

    -- Antes: regimen_id

    descripcion varchar(100) not null,

    siglas varchar(20),

    activo boolean default true,

    constraint pk_regimenes_tributarios primary key (id)

);

-- 1.4 RANGOS DE INGRESOS

create table global.rangos_ingresos (

    id int generated by default as identity,

    -- Antes: rango_ingreso_id

    descripcion varchar(100) not null,

    valor_minimo numeric(19, 2),

    valor_maximo numeric(19, 2),

    moneda varchar(3) default 'PYG',

    activo boolean default true,

    constraint pk_rangos_ingresos primary key (id)

);

-- 1.5 RANGOS DE EMPLEADOS

create table global.rangos_empleados (

    id int generated by default as identity,

    -- Antes: rango_empleado_id

    descripcion varchar(50) not null,

    min_empleados int,

    max_empleados int,

    activo boolean default true,

    constraint pk_rangos_empleados primary key (id)

);

-- =================================================================================

-- ESQUEMA CORE: Personas (Transaccional - Full Audit)

-- =================================================================================

create table core.personas (

    id bigint generated by default as identity,

    -- Antes: persona_id

    id_legado varchar(50),

    tipo_persona char(1) not null,

    motivo_id int not null,

    pais_emision_id int not null,

    tipo_documento_id int not null,

    numero_documento varchar(30) not null,

    nombre_completo_normalizado varchar(255) not null,

    fecha_nacimiento date not null,

    actividad_economica_id int,

    regimen_tributario_id int,

    email_principal varchar(150),

    telefono_principal varchar(50),

    sitio_web varchar(150),

    estado_id int not null,

    version int default 1,

    -- Auditoría Full

    creado_en timestamptz default now() not null,

    creado_por bigint,

    actualizado_en timestamptz,

    actualizado_por bigint,

    eliminado_en timestamptz,

    eliminado_por bigint,

    constraint pk_personas primary key (id),

    -- Foreign Keys actualizadas a (id)

    constraint fk_personas_pais foreign key (pais_emision_id) references global.paises(id),

    constraint fk_personas_tipo_doc foreign key (tipo_documento_id) references global.tipos_documento(id),

    constraint fk_personas_actividad foreign key (actividad_economica_id) references global.actividades_economicas(id),

    constraint fk_personas_regimen foreign key (regimen_tributario_id) references global.regimenes_tributarios(id),

    constraint fk_personas_estado foreign key (estado_id) references global.estados_sistema(id),

    constraint fk_personas_motivo foreign key (motivo_id) references global.motivos_registro(id),

    constraint uq_personas_identidad unique (

        pais_emision_id,

        tipo_documento_id,

        numero_documento

    ),

    constraint ck_personas_tipo check (tipo_persona in ('F', 'J'))

);

create unique index uq_personas_legado on core.personas(id_legado)

where id_legado is not null;

create index idx_personas_nombre on core.personas(nombre_completo_normalizado);

-- 2. TABLA HIJA: PERSONAS FÍSICAS

create table core.personas_fisicas (

    id bigint not null,

    -- PK y FK al mismo tiempo. Apunta a core.personas(id)

    nombres varchar(100) not null,

    apellidos varchar(100) not null,

    sexo_id int not null,

    estado_civil_id int,

    regimen_matrimonial_id int,

    distrito_nacimiento_id int,

    nivel_educativo_id int,

    ocupacion_id int,

    cantidad_dependientes int default 0,

    tiene_hijos_escolar boolean default false,

    situacion_vivienda_id int,

    tenencia_tierra_id int,

    posee_seguro_medico boolean default false,

    -- Auditoría Full

    creado_en timestamptz default now() not null,

    creado_por bigint,

    actualizado_en timestamptz,

    actualizado_por bigint,

    eliminado_en timestamptz,

    eliminado_por bigint,

    constraint pk_personas_fisicas primary key (id),

    -- Herencia (Apunta a id)

    constraint fk_fisicas_persona foreign key (id) references core.personas(id) on delete cascade,

    -- Referencias Globales (Apuntan a id)

    constraint fk_fisicas_sexo foreign key (sexo_id) references global.sexos(id),

    constraint fk_fisicas_est_civil foreign key (estado_civil_id) references global.estados_civiles(id),

    constraint fk_fisicas_regimen foreign key (regimen_matrimonial_id) references global.regimenes_matrimoniales(id),

    constraint fk_fisicas_educacion foreign key (nivel_educativo_id) references global.niveles_educativos(id),

    constraint fk_fisicas_distrito foreign key (distrito_nacimiento_id) references global.distritos(id),

    constraint fk_fisicas_vivienda foreign key (situacion_vivienda_id) references global.tipos_vivienda(id),

    constraint fk_fisicas_tierra foreign key (tenencia_tierra_id) references global.tipos_tenencia_tierra(id),

    constraint fk_fisicas_ocupacion foreign key (ocupacion_id) references global.ocupaciones(id)

);

-- 3. TABLA HIJA: PERSONAS JURÍDICAS

create table core.personas_juridicas (

    id bigint not null,

    -- PK y FK

    razon_social varchar(200) not null,

    nombre_fantasia varchar(200),

    tipo_sociedad_id int not null,

    rango_ingreso_anual_id int,

    rango_cantidad_empleados_id int,

    -- Auditoría Full

    creado_en timestamptz default now() not null,

    creado_por bigint,

    actualizado_en timestamptz,

    actualizado_por bigint,

    eliminado_en timestamptz,

    eliminado_por bigint,

    constraint pk_personas_juridicas primary key (id),

    -- Herencia (Apunta a id)

    constraint fk_juridicas_persona foreign key (id) references core.personas(id) on delete cascade,

    constraint fk_juridicas_tipo_soc foreign key (tipo_sociedad_id) references global.tipos_sociedad(id),

    constraint fk_juridicas_rango_ing foreign key (rango_ingreso_anual_id) references global.rangos_ingresos(id),

    constraint fk_juridicas_rango_emp foreign key (rango_cantidad_empleados_id) references global.rangos_empleados(id)

);

-- 4. TABLA: DIRECCIONES

create table core.direcciones (

    id bigint generated by default as identity,

    -- Antes: direccion_id

    persona_id bigint not null,

    categoria_uso varchar(20) not null default 'DOMICILIO',

    es_principal boolean default false,

    distrito_id int not null,

    barrio varchar(100),

    calle_principal varchar(150) not null,

    numeracion varchar(20),

    referencia_ubicacion text,

    latitud decimal(10, 8),

    longitud decimal(11, 8),

    -- Auditoría Full

    creado_en timestamptz default now() not null,

    creado_por bigint,

    actualizado_en timestamptz,

    actualizado_por bigint,

    eliminado_en timestamptz,

    eliminado_por bigint,

    constraint pk_direcciones primary key (id),

    constraint fk_direcciones_persona foreign key (persona_id) references core.personas(id),

    constraint fk_direcciones_distrito foreign key (distrito_id) references global.distritos(id)

);

-- VÍNCULOS

create table core.vinculos_personas (

    id bigint generated by default as identity,

    -- Antes: vinculo_id

    persona_origen_id bigint not null,

    persona_destino_id bigint not null,

    tipo_vinculo_id int not null,

    cargo_id int,

    -- Auditoría Full

    creado_en timestamptz default now() not null,

    creado_por bigint,

    actualizado_en timestamptz,

    actualizado_por bigint,

    eliminado_en timestamptz,

    eliminado_por bigint,

    constraint pk_vinculos_personas primary key (id),

    constraint fk_vinculos_origen foreign key (persona_origen_id) references core.personas(id),

    constraint fk_vinculos_destino foreign key (persona_destino_id) references core.personas(id),

    constraint fk_vinculos_tipo foreign key (tipo_vinculo_id) references global.tipos_vinculo(id),

    constraint fk_vinculos_cargo foreign key (cargo_id) references global.cargos(id),

    -- Apunta a global.cargos(id)

    constraint ck_vinculos_bucle check (persona_origen_id <> persona_destino_id)

);

create table socioeconomico.perfiles_laborales (

    id bigint generated by default as identity,

    -- Antes: laboral_id

    persona_id bigint not null,

    fuente_ingresos_id int not null,

    tipo_relacion_id int,

    actividad_principal_id int not null,

    actividad_rubro_empleador_id int,

    nombre_empresa_empleador varchar(150),

    cargo_puesto_ocupado varchar(150),

    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,

    -- Auditoría Full

    creado_en timestamptz default now() not null,

    creado_por bigint,

    actualizado_en timestamptz,

    actualizado_por bigint,

    eliminado_en timestamptz,

    eliminado_por bigint,

    constraint pk_perfiles_laborales primary key (id),

    constraint fk_laboral_persona foreign key (persona_id) references core.personas(id) on delete cascade,

    constraint fk_laboral_fuente foreign key (fuente_ingresos_id) references global.fuentes_ingresos(id),

    constraint fk_laboral_relacion foreign key (tipo_relacion_id) references global.tipos_relacion_laboral(id),

    constraint fk_laboral_act_propia foreign key (actividad_principal_id) references global.actividades_economicas(id),

    constraint fk_laboral_act_empleador foreign key (actividad_rubro_empleador_id) references global.actividades_economicas(id)

);

-- =================================================================================

-- ESQUEMA SOCIOS (Transaccional - Full Audit)

-- =================================================================================

/*

 create table socios.solicitudes_ingreso (

 id bigint generated by default as identity,

 -- Antes: solicitud_id

 persona_id bigint not null,

 fecha_solicitud date default current_date not null,

 es_reasociacion boolean default false,

 observaciones_iniciales text,

 manifestacion_bienes_id bigint,

 pep_vinculacion_id bigint,

 -- OJO: Falta tabla riesgo.listas_control

 estado_id int not null,

 fecha_resolucion date,

 usuario_resolucion_id bigint,

 acto_administrativo_resolucion varchar(150),

 fecha_ingreso_oficial date,

 observaciones_resolucion text,

 -- Auditoría Full

 creado_en timestamptz default now() not null,

 creado_por bigint,

 actualizado_en timestamptz,

 actualizado_por bigint,

 eliminado_en timestamptz,

 eliminado_por bigint,

 constraint pk_solicitudes_ingreso primary key (id),

 constraint fk_solicitud_persona foreign key (persona_id) references core.personas(id),

 constraint fk_solicitud_estado foreign key (estado_id) references global.estados_sistema(id),

 constraint fk_solicitud_pep foreign key (pep_vinculacion_id) references riesgo.listas_control(lista_id)

 );

 create table socios.maestro_socios (

 id bigint generated by default as identity,

 -- Antes: socio_id

 persona_id bigint not null,

 solicitud_origen_id bigint not null,

 numero_socio int not null,

 codigo_socio_legado varchar(50),

 -- OJO: Según el script base, no hay calificacion_riesgo_id aquí.

 fecha_ingreso date not null,

 fecha_salida date,

 motivo_salida varchar(200),

 estado_id int not null,

 -- Auditoría Full

 creado_en timestamptz default now() not null,

 creado_por bigint,

 actualizado_en timestamptz,

 actualizado_por bigint,

 eliminado_en timestamptz,

 eliminado_por bigint,

 constraint pk_maestro_socios primary key (id),

 constraint fk_socios_persona foreign key (persona_id) references core.personas(id),

 constraint fk_socios_solicitud foreign key (solicitud_origen_id) references socios.solicitudes_ingreso(id),

 constraint fk_socios_estado foreign key (estado_id) references global.estados_sistema(id),

 constraint uq_socios_numero unique (numero_socio)

 );*/