-- 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)
 );*/