-- =========================================================================
--  Esquema de base de datos: PROCESOS Y EVENTOS
--  Buscador Multisearch — Intervención 3 (vinculación de fases)
-- =========================================================================
--
--  PostgreSQL 14+. Usa JSONB para campos semi-estructurados y tsvector
--  para búsqueda full-text. Las restricciones de integridad referencial
--  evitan procesos huérfanos.
--
--  Cargar con: psql -d buscador -f 03_db_procesos_schema.sql
-- =========================================================================

-- Extensiones necesarias
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";  -- para búsqueda fuzzy

-- =========================================================================
--  TABLA: procesos
--  Cada proceso selectivo (combinación administración + cuerpo + año + turno)
-- =========================================================================

CREATE TABLE procesos (
    -- Identificador determinista derivado de admin + cuerpo + año + turno
    -- Formato: "<admin_codigo>__<cuerpo_codigo>__<año>__<turno>"
    -- Ej: "aeat__inspectores_hacienda__2026__libre"
    id_proceso          VARCHAR(200) PRIMARY KEY,

    -- Datos identificadores
    administracion_codigo   VARCHAR(80)  NOT NULL,
    administracion_nombre   TEXT         NOT NULL,
    cuerpo_codigo           VARCHAR(80)  NOT NULL,
    cuerpo_nombre           TEXT         NOT NULL,
    cuerpo_codigo_oficial   VARCHAR(10),  -- "0011", "1166", etc.
    grupo                   VARCHAR(5),   -- A1, A2, C1, C2, AP
    categoria_tematica      VARCHAR(80)  NOT NULL,
    anio_oep                INTEGER      NOT NULL,
    turno                   VARCHAR(40)  NOT NULL DEFAULT 'libre',
        -- 'libre', 'promocion_interna', 'reserva_discapacidad',
        -- 'discapacidad_intelectual'

    -- Datos de plazas (rellenados a medida que aparecen las fases)
    num_plazas_total        INTEGER,
    num_plazas_libre        INTEGER,
    num_plazas_promo_int    INTEGER,
    num_plazas_discap       INTEGER,
    num_plazas_discap_int   INTEGER,

    -- Estado del proceso
    estado                  VARCHAR(40)  NOT NULL DEFAULT 'oep_publicada',
        -- 'oep_publicada', 'bases_publicadas', 'plazo_abierto',
        -- 'plazo_cerrado', 'en_pruebas', 'finalizado', 'archivado'

    -- Fechas clave (NULL si la fase no ha llegado todavía)
    fecha_oep               DATE,
    fecha_bases             DATE,
    fecha_apertura_plazo    DATE,
    fecha_fin_plazo         DATE,
    fecha_primer_ejercicio  DATE,
    fecha_lista_definitiva  DATE,
    fecha_nombramientos     DATE,

    -- Documentos de referencia (URL canónica de cada fase)
    url_oep                 TEXT,
    url_bases               TEXT,
    url_apertura            TEXT,

    -- Metadatos
    fecha_creacion          TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    fecha_actualizacion     TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    fecha_archivado         TIMESTAMPTZ,

    -- Para búsqueda full-text
    tsv                     TSVECTOR
);

-- Índices
CREATE INDEX idx_procesos_admin     ON procesos(administracion_codigo);
CREATE INDEX idx_procesos_cuerpo    ON procesos(cuerpo_codigo);
CREATE INDEX idx_procesos_anio      ON procesos(anio_oep);
CREATE INDEX idx_procesos_categoria ON procesos(categoria_tematica);
CREATE INDEX idx_procesos_estado    ON procesos(estado);
CREATE INDEX idx_procesos_tsv       ON procesos USING GIN(tsv);

-- Trigger para actualizar tsv y fecha_actualizacion
CREATE OR REPLACE FUNCTION procesos_update_trigger()
RETURNS TRIGGER AS $$
BEGIN
    NEW.tsv := to_tsvector('spanish',
        coalesce(NEW.administracion_nombre, '') || ' ' ||
        coalesce(NEW.cuerpo_nombre, '') || ' ' ||
        coalesce(NEW.cuerpo_codigo_oficial, '')
    );
    NEW.fecha_actualizacion := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER procesos_update_tsv
    BEFORE INSERT OR UPDATE ON procesos
    FOR EACH ROW EXECUTE FUNCTION procesos_update_trigger();


-- =========================================================================
--  TABLA: eventos_proceso
--  Cada hit del buscador que se vincula a un proceso. Un proceso tiene
--  múltiples eventos a lo largo del tiempo.
-- =========================================================================

CREATE TABLE eventos_proceso (
    id_evento           UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    id_proceso          VARCHAR(200) NOT NULL REFERENCES procesos(id_proceso)
                            ON DELETE CASCADE,

    -- Clasificación
    fase                VARCHAR(40)  NOT NULL,
        -- 'oep', 'bases', 'apertura', 'subfase', 'desconocida'
    subtipo             VARCHAR(80),
        -- 'correccion_errores', 'lista_provisional_admitidos', etc.

    -- Origen
    boletin             VARCHAR(40)  NOT NULL,
    numero_boletin      VARCHAR(40),
    fecha_publicacion   DATE         NOT NULL,
    pagina              INTEGER,
    codigo_cve          VARCHAR(100),
    url_pdf             TEXT,

    -- Contenido
    patron_disparado    TEXT,
    ancla_disparadas    TEXT[],  -- palabras-ancla que la clasificación detectó
    fragmento           TEXT,    -- ±300 chars del patrón
    fragmento_html      TEXT,    -- con <mark> sobre el patrón

    -- Confianza
    nivel_confianza     VARCHAR(20)  NOT NULL DEFAULT 'media',
        -- 'alta', 'media', 'baja'
    score               NUMERIC(3,2),  -- 0.00 a 1.00
    reglas_exclusion_aplicadas  TEXT[],

    -- Estado de procesamiento
    parser_ejecutado    BOOLEAN      NOT NULL DEFAULT FALSE,
    datos_extraidos     JSONB,  -- resultado del parser (plazas, tasas, etc.)

    -- Metadatos
    fecha_deteccion     TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    hash_fragmento      VARCHAR(64),  -- para deduplicación

    -- Para búsqueda full-text
    tsv                 TSVECTOR
);

-- Índices
CREATE INDEX idx_eventos_proceso       ON eventos_proceso(id_proceso);
CREATE INDEX idx_eventos_fase          ON eventos_proceso(fase);
CREATE INDEX idx_eventos_fecha         ON eventos_proceso(fecha_publicacion DESC);
CREATE INDEX idx_eventos_boletin       ON eventos_proceso(boletin);
CREATE INDEX idx_eventos_cve           ON eventos_proceso(codigo_cve)
                                            WHERE codigo_cve IS NOT NULL;
CREATE INDEX idx_eventos_hash          ON eventos_proceso(hash_fragmento);
CREATE INDEX idx_eventos_tsv           ON eventos_proceso USING GIN(tsv);
CREATE INDEX idx_eventos_datos         ON eventos_proceso USING GIN(datos_extraidos);

-- Restricción: no duplicar eventos del mismo CVE en el mismo proceso
CREATE UNIQUE INDEX idx_eventos_proceso_cve
    ON eventos_proceso(id_proceso, codigo_cve)
    WHERE codigo_cve IS NOT NULL;

-- Trigger para tsv
CREATE OR REPLACE FUNCTION eventos_update_trigger()
RETURNS TRIGGER AS $$
BEGIN
    NEW.tsv := to_tsvector('spanish',
        coalesce(NEW.boletin, '') || ' ' ||
        coalesce(NEW.patron_disparado, '') || ' ' ||
        coalesce(NEW.fragmento, '')
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER eventos_update_tsv
    BEFORE INSERT OR UPDATE ON eventos_proceso
    FOR EACH ROW EXECUTE FUNCTION eventos_update_trigger();


-- =========================================================================
--  TABLA: tribunales
--  Composición del tribunal de un proceso (extraído de las bases o de
--  publicaciones específicas).
-- =========================================================================

CREATE TABLE tribunales (
    id_tribunal         UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    id_proceso          VARCHAR(200) NOT NULL REFERENCES procesos(id_proceso)
                            ON DELETE CASCADE,

    presidente          TEXT,
    secretario          TEXT,
    vocales             JSONB,  -- array de strings con los vocales

    fecha_publicacion   DATE NOT NULL,
    url_pdf             TEXT,

    -- Si hubo modificaciones del tribunal (sustituciones, etc.)
    es_modificacion     BOOLEAN NOT NULL DEFAULT FALSE,
    tribunal_anterior   UUID REFERENCES tribunales(id_tribunal),

    fecha_creacion      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_tribunales_proceso ON tribunales(id_proceso);


-- =========================================================================
--  TABLA: fechas_clave
--  Hitos del calendario de un proceso (fin de plazo, exámenes, etc.)
-- =========================================================================

CREATE TABLE fechas_clave (
    id_fecha            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    id_proceso          VARCHAR(200) NOT NULL REFERENCES procesos(id_proceso)
                            ON DELETE CASCADE,

    tipo                VARCHAR(60)  NOT NULL,
        -- 'fin_plazo_solicitudes', 'lista_provisional', 'lista_definitiva',
        -- 'primer_ejercicio', 'segundo_ejercicio', 'tercer_ejercicio',
        -- 'fin_concurso_meritos', 'publicacion_aprobados',
        -- 'eleccion_destinos', 'toma_posesion'
    fecha               DATE         NOT NULL,
    hora                TIME,
    lugar               TEXT,
    notas               TEXT,

    fuente_url          TEXT,
    fuente_fecha        DATE,
    estimada            BOOLEAN      NOT NULL DEFAULT FALSE,
        -- TRUE si la fecha es una estimación del sistema, no oficial

    fecha_creacion      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    fecha_actualizacion TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_fechas_proceso     ON fechas_clave(id_proceso);
CREATE INDEX idx_fechas_tipo        ON fechas_clave(tipo);
CREATE INDEX idx_fechas_fecha       ON fechas_clave(fecha);


-- =========================================================================
--  TABLA: usuarios
--  Suscriptores del sistema
-- =========================================================================

CREATE TABLE usuarios (
    id_usuario          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email               VARCHAR(200) NOT NULL UNIQUE,
    nombre              VARCHAR(200),
    estado              VARCHAR(20)  NOT NULL DEFAULT 'activo',
        -- 'activo', 'pausado', 'baja'
    fecha_alta          TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    fecha_ultima_apertura  TIMESTAMPTZ
);

CREATE INDEX idx_usuarios_email ON usuarios(email);


-- =========================================================================
--  TABLA: suscripciones
--  Qué categorías sigue cada usuario
-- =========================================================================

CREATE TABLE suscripciones (
    id_suscripcion      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    id_usuario          UUID NOT NULL REFERENCES usuarios(id_usuario)
                            ON DELETE CASCADE,
    categoria           VARCHAR(80) NOT NULL,
        -- corresponde a uno de los códigos del diccionario maestro
    fecha_alta          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    activa              BOOLEAN     NOT NULL DEFAULT TRUE
);

CREATE UNIQUE INDEX idx_suscripciones_unica
    ON suscripciones(id_usuario, categoria);


-- =========================================================================
--  TABLA: alertas
--  Cola de alertas a enviar (recordatorios de fechas, cambios de fase)
-- =========================================================================

CREATE TABLE alertas (
    id_alerta           UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    id_usuario          UUID NOT NULL REFERENCES usuarios(id_usuario)
                            ON DELETE CASCADE,
    id_proceso          VARCHAR(200) REFERENCES procesos(id_proceso)
                            ON DELETE SET NULL,

    tipo                VARCHAR(60) NOT NULL,
        -- 'cambio_fase', 'recordatorio_plazo', 'recordatorio_examen',
        -- 'proceso_huerfano', 'nueva_oferta_categoria'
    fecha_disparo       TIMESTAMPTZ NOT NULL,
    estado              VARCHAR(20) NOT NULL DEFAULT 'pendiente',
        -- 'pendiente', 'enviada', 'cancelada', 'fallida'

    asunto              TEXT,
    cuerpo              TEXT,

    fecha_envio         TIMESTAMPTZ,
    error               TEXT,

    fecha_creacion      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_alertas_usuario    ON alertas(id_usuario);
CREATE INDEX idx_alertas_estado     ON alertas(estado);
CREATE INDEX idx_alertas_disparo    ON alertas(fecha_disparo)
                                        WHERE estado = 'pendiente';


-- =========================================================================
--  TABLA: hit_feedback
--  Registro de cada "Coincidencia correcta" / "Falso positivo" pulsado
-- =========================================================================

CREATE TABLE hit_feedback (
    id_feedback         UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    id_evento           UUID NOT NULL REFERENCES eventos_proceso(id_evento)
                            ON DELETE CASCADE,
    id_usuario          UUID REFERENCES usuarios(id_usuario)
                            ON DELETE SET NULL,
    valor               VARCHAR(10) NOT NULL,  -- 'ok' o 'no'
    comentario          TEXT,
    user_agent          TEXT,
    fecha               TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_feedback_evento ON hit_feedback(id_evento);
CREATE INDEX idx_feedback_usuario ON hit_feedback(id_usuario);
CREATE INDEX idx_feedback_valor ON hit_feedback(valor);


-- =========================================================================
--  TABLA: resumenes_enviados
--  Histórico de emails enviados (para tracking)
-- =========================================================================

CREATE TABLE resumenes_enviados (
    id_resumen          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    id_usuario          UUID NOT NULL REFERENCES usuarios(id_usuario)
                            ON DELETE CASCADE,
    categoria           VARCHAR(80) NOT NULL,
    fecha               DATE        NOT NULL,
    num_hits_bases      INTEGER     NOT NULL DEFAULT 0,
    num_hits_apertura   INTEGER     NOT NULL DEFAULT 0,
    num_hits_oep        INTEGER     NOT NULL DEFAULT 0,
    num_hits_subfases   INTEGER     NOT NULL DEFAULT 0,
    num_hits_fp         INTEGER     NOT NULL DEFAULT 0,
    fecha_envio         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    fecha_apertura      TIMESTAMPTZ  -- cuando se carga el píxel de tracking
);

CREATE INDEX idx_resumenes_usuario_fecha
    ON resumenes_enviados(id_usuario, fecha DESC);
CREATE UNIQUE INDEX idx_resumenes_unico
    ON resumenes_enviados(id_usuario, categoria, fecha);


-- =========================================================================
--  VISTA: procesos_huerfanos
--  Procesos que llevan demasiado tiempo sin cambio de fase
-- =========================================================================

CREATE OR REPLACE VIEW procesos_huerfanos AS
SELECT
    p.*,
    EXTRACT(EPOCH FROM (NOW() - p.fecha_actualizacion)) / 86400
        AS dias_sin_actualizacion
FROM procesos p
WHERE p.estado NOT IN ('finalizado', 'archivado')
  AND (
      -- OEP publicada hace > 8 meses sin bases
      (p.estado = 'oep_publicada' AND p.fecha_oep < NOW() - INTERVAL '8 months')
      OR
      -- Bases publicadas hace > 4 meses sin apertura de plazo
      (p.estado = 'bases_publicadas' AND p.fecha_bases < NOW() - INTERVAL '4 months')
      OR
      -- Plazo cerrado hace > 12 meses sin pruebas
      (p.estado = 'plazo_cerrado' AND p.fecha_fin_plazo < NOW() - INTERVAL '12 months')
  );


-- =========================================================================
--  VISTA: procesos_activos_resumen
--  Resumen rápido para la página de ciclo de vida
-- =========================================================================

CREATE OR REPLACE VIEW procesos_activos_resumen AS
SELECT
    p.id_proceso,
    p.administracion_nombre,
    p.cuerpo_nombre,
    p.grupo,
    p.categoria_tematica,
    p.anio_oep,
    p.turno,
    p.num_plazas_total,
    p.estado,
    p.fecha_oep,
    p.fecha_bases,
    p.fecha_apertura_plazo,
    p.fecha_fin_plazo,
    (SELECT COUNT(*) FROM eventos_proceso e WHERE e.id_proceso = p.id_proceso)
        AS num_eventos_total,
    (SELECT MAX(fecha_publicacion) FROM eventos_proceso e
        WHERE e.id_proceso = p.id_proceso) AS ultima_actividad
FROM procesos p
WHERE p.estado NOT IN ('archivado');