-- =========================================================================
--  Esquema de base de datos: TEMARIOS HISTÓRICOS
--  Buscador Multisearch — Intervención 4 (extracción del temario)
-- =========================================================================
--
--  PostgreSQL 14+. Usa la extensión pgvector para almacenar los
--  embeddings semánticos de cada tema (permite comparar temarios entre
--  convocatorias del mismo cuerpo).
--
--  Cargar con: psql -d buscador -f 04_db_temarios_schema.sql
-- =========================================================================

-- Extensión para embeddings vectoriales
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";


-- =========================================================================
--  TABLA: temarios (cabecera de cada temario extraído de unas bases)
-- =========================================================================

CREATE TABLE temarios (
    id_temario          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Vinculación con el proceso del que provienen
    id_proceso          VARCHAR(200) REFERENCES procesos(id_proceso)
                            ON DELETE SET NULL,
    id_evento           UUID REFERENCES eventos_proceso(id_evento)
                            ON DELETE SET NULL,

    -- Identificadores normalizados (replicados aquí para búsquedas rápidas
    -- sin tener que hacer join con procesos)
    administracion_codigo   VARCHAR(80)  NOT NULL,
    cuerpo_codigo           VARCHAR(80)  NOT NULL,
    cuerpo_codigo_oficial   VARCHAR(10),
    anio_convocatoria       INTEGER      NOT NULL,
    turno                   VARCHAR(40)  NOT NULL DEFAULT 'libre',

    -- Origen
    boletin                 VARCHAR(40)  NOT NULL,
    fecha_publicacion       DATE         NOT NULL,
    url_pdf                 TEXT,
    paginas_temario         INT4RANGE,   -- rango de páginas del PDF donde está el temario

    -- Datos estructurados extraídos del PDF de bases
    num_plazas_total        INTEGER,
    num_plazas_libre        INTEGER,
    num_plazas_promo_int    INTEGER,
    num_plazas_discap       INTEGER,
    num_plazas_discap_int   INTEGER,

    sistema_selectivo       VARCHAR(40),
        -- 'oposicion', 'concurso_oposicion', 'concurso', 'concurso_meritos'
    num_ejercicios          SMALLINT,
    descripcion_ejercicios  JSONB,
        -- [{"numero": 1, "tipo": "test", "preguntas": 100, "duracion_min": 120, ...}]

    tasa_examen             NUMERIC(8,2),
    moneda_tasa             VARCHAR(5) DEFAULT 'EUR',

    requisitos_titulacion   TEXT[],
    requisitos_otros        TEXT,

    tribunal_presidente     TEXT,
    tribunal_secretario     TEXT,
    tribunal_vocales        JSONB,

    criterios_valoracion    JSONB,
        -- estructura libre con los baremos extraídos del documento

    -- Temario completo
    num_temas_total         INTEGER,
    num_temas_parte_general INTEGER,
    num_temas_parte_especifica INTEGER,
    num_temas_otras_partes  INTEGER,

    -- Hash de la estructura del temario, útil para detectar
    -- si dos temarios son sustancialmente iguales (mismo cuerpo,
    -- convocatorias consecutivas que reusan el temario tal cual)
    hash_estructura         VARCHAR(64),

    -- Estado del parser
    parser_ok               BOOLEAN     NOT NULL DEFAULT FALSE,
    parser_errores          TEXT[],
    parser_version          VARCHAR(20),

    -- Metadatos
    fecha_extraccion        TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Búsqueda full-text sobre todo el contenido del temario
    tsv                     TSVECTOR
);

-- Índices
CREATE INDEX idx_temarios_proceso       ON temarios(id_proceso);
CREATE INDEX idx_temarios_admin         ON temarios(administracion_codigo);
CREATE INDEX idx_temarios_cuerpo        ON temarios(cuerpo_codigo);
CREATE INDEX idx_temarios_anio          ON temarios(anio_convocatoria DESC);
CREATE INDEX idx_temarios_hash          ON temarios(hash_estructura);
CREATE INDEX idx_temarios_tsv           ON temarios USING GIN(tsv);
CREATE INDEX idx_temarios_datos_ejerc   ON temarios USING GIN(descripcion_ejercicios);

-- Restricción: un mismo proceso solo puede tener un temario "vivo"
-- (correcciones de errores se manejan como nuevas filas con flag)
CREATE UNIQUE INDEX idx_temarios_proceso_unico
    ON temarios(id_proceso)
    WHERE parser_ok = TRUE AND id_proceso IS NOT NULL;


-- =========================================================================
--  TABLA: temas (cada tema individual del temario)
-- =========================================================================

CREATE TABLE temas (
    id_tema             UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    id_temario          UUID NOT NULL REFERENCES temarios(id_temario)
                            ON DELETE CASCADE,

    -- Estructura
    parte               VARCHAR(40) NOT NULL DEFAULT 'general',
        -- 'general', 'especifica', 'idiomas', 'practica',
        -- 'derecho_constitucional', 'derecho_administrativo', etc.
    numero              INTEGER     NOT NULL,
    numero_completo     VARCHAR(20),   -- "1.3", "II.5", etc. (si hay subnumeración)

    -- Contenido
    titulo              TEXT        NOT NULL,
    texto_completo      TEXT,
        -- algunas bases listan solo títulos; otras incluyen párrafo
        -- descriptivo del tema. Si está, se guarda.

    -- Embedding semántico para diff entre temarios
    -- (dimensión 384 = sentence-transformers/paraphrase-multilingual-MiniLM-L12-v2)
    embedding           VECTOR(384),

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

    -- Búsqueda full-text del título
    tsv                 TSVECTOR
);

-- Índices
CREATE INDEX idx_temas_temario          ON temas(id_temario);
CREATE INDEX idx_temas_parte            ON temas(id_temario, parte, numero);
CREATE INDEX idx_temas_tsv              ON temas USING GIN(tsv);

-- Índice vectorial para búsqueda por similaridad (KNN)
CREATE INDEX idx_temas_embedding
    ON temas USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 100);

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

CREATE TRIGGER temas_update_tsv
    BEFORE INSERT OR UPDATE ON temas
    FOR EACH ROW EXECUTE FUNCTION temas_update_trigger();


-- =========================================================================
--  TABLA: diff_temarios (resultado de comparar dos temarios)
-- =========================================================================

CREATE TABLE diff_temarios (
    id_diff             UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Los dos temarios comparados (mismo cuerpo, distinto año)
    id_temario_nuevo    UUID NOT NULL REFERENCES temarios(id_temario)
                            ON DELETE CASCADE,
    id_temario_anterior UUID NOT NULL REFERENCES temarios(id_temario)
                            ON DELETE CASCADE,

    -- Resumen del diff
    num_temas_nuevos        INTEGER NOT NULL DEFAULT 0,
    num_temas_eliminados    INTEGER NOT NULL DEFAULT 0,
    num_temas_reformulados  INTEGER NOT NULL DEFAULT 0,
    num_temas_identicos     INTEGER NOT NULL DEFAULT 0,

    -- Métricas
    porcentaje_cambio       NUMERIC(5,2),
        -- (nuevos + eliminados + reformulados) / total_anterior * 100
    es_reescritura_total    BOOLEAN NOT NULL DEFAULT FALSE,
        -- TRUE si > 70% del temario cambió: probablemente un cuerpo nuevo

    -- Detalle por tema (JSONB para flexibilidad)
    detalle                 JSONB,
        -- {
        --   "nuevos": [{"numero": 23, "titulo": "...", "parte": "especifica"}, ...],
        --   "eliminados": [{"numero": 17, "titulo": "...", "parte": "especifica"}, ...],
        --   "reformulados": [
        --     {
        --       "numero_anterior": 5, "titulo_anterior": "...",
        --       "numero_nuevo": 5, "titulo_nuevo": "...",
        --       "similaridad": 0.78
        --     }, ...
        --   ],
        --   "identicos": [{"numero": 1, "titulo": "..."}, ...]
        -- }

    -- Configuración usada en el diff
    umbral_identico         NUMERIC(3,2) DEFAULT 0.95,
    umbral_reformulado      NUMERIC(3,2) DEFAULT 0.70,
    modelo_embeddings       VARCHAR(80),

    fecha_calculo           TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_diff_nuevo     ON diff_temarios(id_temario_nuevo);
CREATE INDEX idx_diff_anterior  ON diff_temarios(id_temario_anterior);

CREATE UNIQUE INDEX idx_diff_par_unico
    ON diff_temarios(id_temario_nuevo, id_temario_anterior);


-- =========================================================================
--  VISTA: ultimos_temarios_por_cuerpo
--  Para cada cuerpo, los dos temarios más recientes (útil para diff
--  automático cada vez que se extrae uno nuevo).
-- =========================================================================

CREATE OR REPLACE VIEW ultimos_temarios_por_cuerpo AS
SELECT
    cuerpo_codigo,
    administracion_codigo,
    id_temario,
    anio_convocatoria,
    fecha_publicacion,
    num_temas_total,
    ROW_NUMBER() OVER (
        PARTITION BY cuerpo_codigo, administracion_codigo
        ORDER BY fecha_publicacion DESC
    ) AS rn
FROM temarios
WHERE parser_ok = TRUE;


-- =========================================================================
--  VISTA: temarios_para_diff_pendiente
--  Temarios recién extraídos que tienen un temario anterior del mismo
--  cuerpo pero todavía no se ha calculado el diff. El job nocturno
--  procesa esta cola.
-- =========================================================================

CREATE OR REPLACE VIEW temarios_para_diff_pendiente AS
SELECT
    t_nuevo.id_temario       AS id_temario_nuevo,
    t_anterior.id_temario    AS id_temario_anterior,
    t_nuevo.cuerpo_codigo,
    t_nuevo.administracion_codigo,
    t_nuevo.anio_convocatoria AS anio_nuevo,
    t_anterior.anio_convocatoria AS anio_anterior
FROM temarios t_nuevo
JOIN temarios t_anterior
    ON t_nuevo.cuerpo_codigo = t_anterior.cuerpo_codigo
   AND t_nuevo.administracion_codigo = t_anterior.administracion_codigo
   AND t_anterior.anio_convocatoria < t_nuevo.anio_convocatoria
   AND t_anterior.parser_ok = TRUE
LEFT JOIN diff_temarios d
    ON d.id_temario_nuevo = t_nuevo.id_temario
   AND d.id_temario_anterior = t_anterior.id_temario
WHERE t_nuevo.parser_ok = TRUE
  AND d.id_diff IS NULL  -- diff no calculado todavía
  AND t_anterior.anio_convocatoria = (
      -- Solo comparar con el inmediatamente anterior, no con todos
      SELECT MAX(t2.anio_convocatoria)
      FROM temarios t2
      WHERE t2.cuerpo_codigo = t_nuevo.cuerpo_codigo
        AND t2.administracion_codigo = t_nuevo.administracion_codigo
        AND t2.anio_convocatoria < t_nuevo.anio_convocatoria
        AND t2.parser_ok = TRUE
  );


-- =========================================================================
--  FUNCIÓN: buscar_temas_similares
--  Dado un texto, devolver los N temas más similares semánticamente.
--  Útil para encontrar "este tema, ¿de qué cuerpos forma parte?"
-- =========================================================================

CREATE OR REPLACE FUNCTION buscar_temas_similares(
    query_embedding VECTOR(384),
    n_resultados    INTEGER DEFAULT 10
)
RETURNS TABLE (
    id_tema         UUID,
    id_temario      UUID,
    cuerpo_codigo   VARCHAR(80),
    anio            INTEGER,
    titulo          TEXT,
    similaridad     NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        t.id_tema,
        t.id_temario,
        tem.cuerpo_codigo,
        tem.anio_convocatoria,
        t.titulo,
        (1 - (t.embedding <=> query_embedding))::NUMERIC AS similaridad
    FROM temas t
    JOIN temarios tem ON tem.id_temario = t.id_temario
    WHERE t.embedding IS NOT NULL
    ORDER BY t.embedding <=> query_embedding
    LIMIT n_resultados;
END;
$$ LANGUAGE plpgsql;


-- =========================================================================
--  COMENTARIOS DE USO
-- =========================================================================
--
--  Flujo típico cuando se detecta una FASE 2 (bases) en el buscador:
--
--  1. El parser (`04_parser_bases.py`) procesa el PDF y devuelve un
--     objeto con todos los datos extraídos.
--  2. INSERT INTO temarios con la cabecera y los datos estructurados.
--  3. Para cada tema extraído, INSERT INTO temas con su número, parte
--     y título. Los embeddings se calculan en un paso posterior
--     (batch) con sentence-transformers.
--  4. La vista `temarios_para_diff_pendiente` señala que hay un diff
--     pendiente con el temario anterior del mismo cuerpo.
--  5. Un job ejecuta `04_diff_temarios.py` que calcula el diff
--     comparando embeddings y persiste en `diff_temarios`.
--  6. El email del día siguiente incluye el botón "Ver cambios vs
--     temario anterior" enlazando al diff.
--
--  Ejemplos de queries útiles:
--
--  -- Último temario de cada cuerpo:
--  SELECT * FROM ultimos_temarios_por_cuerpo WHERE rn = 1;
--
--  -- Diffs pendientes para procesar esta noche:
--  SELECT * FROM temarios_para_diff_pendiente;
--
--  -- Temarios sin embeddings calculados:
--  SELECT t.id_tema, t.titulo
--  FROM temas t WHERE t.embedding IS NULL
--  LIMIT 1000;
--
--  -- Buscar temas relacionados con "procedimiento administrativo":
--  -- (primero hay que calcular el embedding del query con el modelo)
--  SELECT * FROM buscar_temas_similares(
--      (SELECT calcular_embedding('procedimiento administrativo')),
--      20
--  );