-- =========================================================================
--  Esquema de base de datos: ENLACES EXTERNOS, NOTAS Y ADJUNTOS MANUALES
--  Buscador Multisearch — Entrega 6 (monitorización externa)
-- =========================================================================
--
--  PostgreSQL 14+. Extiende los esquemas base (03_db_procesos_schema.sql
--  y 07_db_alertas_y_reglas.sql). Las tablas nuevas referencian las
--  existentes (procesos, eventos_proceso, usuarios).
--
--  Cargar con: psql -d buscador -f 15_db_enlaces_y_notas_schema.sql
-- =========================================================================

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";


-- =========================================================================
--  TABLA: enlaces_proceso
--  URLs externas a monitorizar para cada proceso (tablón electrónico,
--  sede, web del proceso, web del tribunal, otras webs relevantes).
-- =========================================================================

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

    -- Quién lo añadió
    id_usuario_creador  UUID REFERENCES usuarios(id_usuario)
                            ON DELETE SET NULL,

    -- Clasificación
    tipo                VARCHAR(40) NOT NULL,
        -- 'tablon_electronico', 'sede_electronica', 'web_proceso',
        -- 'web_tribunal', 'otra'
    descripcion         TEXT,        -- "Página oficial del proceso TAG Madrid 2026"
    url                 TEXT NOT NULL,
    dominio             VARCHAR(200), -- extraído al insertar, para rate limit

    -- Configuración de chequeo
    activo              BOOLEAN NOT NULL DEFAULT TRUE,
    frecuencia_minutos  INTEGER NOT NULL DEFAULT 240,
        -- por defecto 4h; se puede sobreescribir manualmente
    frecuencia_auto     BOOLEAN NOT NULL DEFAULT TRUE,
        -- si TRUE, la frecuencia se adapta al estado del proceso

    -- Estado de la última comprobación
    ultimo_check        TIMESTAMPTZ,
    proximo_check       TIMESTAMPTZ,
    ultimo_hash         VARCHAR(64),     -- SHA-256 del contenido normalizado
    ultimo_cambio       TIMESTAMPTZ,     -- cuándo cambió por última vez
    num_archivos_detectados INTEGER NOT NULL DEFAULT 0,
    estado_ultimo_check VARCHAR(20),
        -- 'ok', 'sin_cambios', 'cambio_detectado', 'archivo_nuevo',
        -- 'error_red', 'error_robots', 'error_4xx', 'error_5xx'
    ultimo_error        TEXT,

    -- Notas del responsable
    notas               TEXT,

    -- Metadatos
    fecha_alta          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    fecha_baja          TIMESTAMPTZ
);

CREATE INDEX idx_enlaces_proceso        ON enlaces_proceso(id_proceso);
CREATE INDEX idx_enlaces_proximo_check  ON enlaces_proceso(proximo_check)
                                            WHERE activo = TRUE
                                              AND fecha_baja IS NULL;
CREATE INDEX idx_enlaces_dominio        ON enlaces_proceso(dominio);

-- Restricción: una misma URL no se duplica por proceso
CREATE UNIQUE INDEX idx_enlaces_url_proceso_unico
    ON enlaces_proceso(id_proceso, url)
    WHERE fecha_baja IS NULL;


-- =========================================================================
--  TABLA: snapshots_enlace
--  Histórico de capturas de cada URL. Solo se guarda cuando hay cambio.
-- =========================================================================

CREATE TABLE snapshots_enlace (
    id_snapshot         UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    id_enlace           UUID NOT NULL REFERENCES enlaces_proceso(id_enlace)
                            ON DELETE CASCADE,

    fecha_captura       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    hash_contenido      VARCHAR(64) NOT NULL,

    -- HTTP
    codigo_http         SMALLINT,
    content_type        VARCHAR(80),
    bytes_descargados   INTEGER,

    -- Contenido (solo se guarda el texto extraído, no el HTML completo,
    -- para ahorrar espacio. El HTML completo se puede guardar en disco
    -- si hace falta).
    texto_extraido      TEXT,

    -- Lista de archivos detectados en esta captura
    archivos_detectados JSONB,
        -- [{"url": "...", "nombre": "...", "tipo": "pdf"}, ...]

    -- Diff resumido respecto al snapshot anterior
    num_palabras_nuevas INTEGER,
    num_palabras_eliminadas INTEGER,
    archivos_nuevos     TEXT[],    -- nombres de archivos nuevos respecto al snapshot anterior
    archivos_eliminados TEXT[],

    -- Búsqueda full-text
    tsv                 TSVECTOR
);

CREATE INDEX idx_snapshots_enlace   ON snapshots_enlace(id_enlace, fecha_captura DESC);
CREATE INDEX idx_snapshots_tsv      ON snapshots_enlace USING GIN(tsv);

CREATE OR REPLACE FUNCTION snapshots_update_trigger()
RETURNS TRIGGER AS $$
BEGIN
    NEW.tsv := to_tsvector('spanish', coalesce(NEW.texto_extraido, ''));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER snapshots_update_tsv
    BEFORE INSERT OR UPDATE ON snapshots_enlace
    FOR EACH ROW EXECUTE FUNCTION snapshots_update_trigger();


-- =========================================================================
--  TABLA: archivos_detectados
--  Archivos PDF/DOCX/DOC detectados en URLs monitorizadas. Pueden estar
--  procesados o no por el parser, descargados o no.
-- =========================================================================

CREATE TABLE archivos_detectados (
    id_archivo          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    id_enlace           UUID NOT NULL REFERENCES enlaces_proceso(id_enlace)
                            ON DELETE CASCADE,
    id_proceso          VARCHAR(200) NOT NULL REFERENCES procesos(id_proceso)
                            ON DELETE CASCADE,

    -- Origen
    url_archivo         TEXT NOT NULL,
    nombre_archivo      VARCHAR(300),
    tipo                VARCHAR(20),    -- 'pdf', 'docx', 'doc', 'xlsx', 'otro'
    bytes               INTEGER,

    -- Estado
    descargado          BOOLEAN NOT NULL DEFAULT FALSE,
    descargado_a        TEXT,           -- ruta local relativa
    hash_archivo        VARCHAR(64),    -- SHA-256 del archivo descargado
    procesado           BOOLEAN NOT NULL DEFAULT FALSE,
    procesador_usado    VARCHAR(40),    -- 'parser_bases', 'clasificador_subfases', null
    resultado_proceso   JSONB,          -- output del parser/clasificador

    -- Clasificación derivada
    fase_detectada      VARCHAR(40),
    modificador_detectado VARCHAR(40),
    id_evento_creado    UUID REFERENCES eventos_proceso(id_evento)
                            ON DELETE SET NULL,

    -- Acción para el usuario (cuando el sistema no sabe qué hacer)
    requiere_revision   BOOLEAN NOT NULL DEFAULT FALSE,
    motivo_revision     TEXT,

    -- Metadatos
    fecha_primera_deteccion TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    fecha_descarga          TIMESTAMPTZ,
    fecha_procesamiento     TIMESTAMPTZ
);

CREATE INDEX idx_archivos_enlace        ON archivos_detectados(id_enlace);
CREATE INDEX idx_archivos_proceso       ON archivos_detectados(id_proceso);
CREATE INDEX idx_archivos_pendientes    ON archivos_detectados(procesado, descargado)
                                            WHERE procesado = FALSE;
CREATE INDEX idx_archivos_revision      ON archivos_detectados(requiere_revision)
                                            WHERE requiere_revision = TRUE;
CREATE INDEX idx_archivos_hash          ON archivos_detectados(hash_archivo)
                                            WHERE hash_archivo IS NOT NULL;

-- Restricción: no duplicar archivos por enlace (mismo URL)
CREATE UNIQUE INDEX idx_archivos_url_enlace_unico
    ON archivos_detectados(id_enlace, url_archivo);


-- =========================================================================
--  TABLA: notas_proceso
--  Notas manuales, pendientes, contactos y observaciones asociados a
--  un proceso.
-- =========================================================================

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

    id_usuario_autor    UUID REFERENCES usuarios(id_usuario)
                            ON DELETE SET NULL,

    -- Clasificación
    tipo                VARCHAR(20) NOT NULL DEFAULT 'nota',
        -- 'nota', 'pendiente', 'contacto', 'observacion', 'alerta_interna'

    -- Contenido
    titulo              VARCHAR(200),
    contenido           TEXT NOT NULL,
        -- texto libre, Markdown ligero (negrita, listas, enlaces)

    -- Específico de tipo='pendiente'
    completado          BOOLEAN NOT NULL DEFAULT FALSE,
    fecha_completado    TIMESTAMPTZ,
    id_usuario_asignado UUID REFERENCES usuarios(id_usuario)
                            ON DELETE SET NULL,
    fecha_limite        DATE,

    -- Específico de tipo='contacto'
    contacto_nombre     VARCHAR(200),
    contacto_rol        VARCHAR(200),
    contacto_telefono   VARCHAR(40),
    contacto_email      VARCHAR(200),
    contacto_organismo  VARCHAR(200),

    -- Prioridad
    prioridad           VARCHAR(10) NOT NULL DEFAULT 'normal',
        -- 'baja', 'normal', 'alta'

    -- Visibilidad (privada al autor o compartida con la organización)
    visibilidad         VARCHAR(20) NOT NULL DEFAULT 'organizacion',
        -- 'privada', 'organizacion'

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

    -- Búsqueda full-text
    tsv                 TSVECTOR
);

CREATE INDEX idx_notas_proceso          ON notas_proceso(id_proceso, fecha_creacion DESC);
CREATE INDEX idx_notas_tipo             ON notas_proceso(id_proceso, tipo);
CREATE INDEX idx_notas_pendientes       ON notas_proceso(id_usuario_asignado, completado, fecha_limite)
                                            WHERE tipo = 'pendiente' AND completado = FALSE;
CREATE INDEX idx_notas_tsv              ON notas_proceso USING GIN(tsv);

CREATE OR REPLACE FUNCTION notas_update_trigger()
RETURNS TRIGGER AS $$
BEGIN
    NEW.tsv := to_tsvector('spanish',
        coalesce(NEW.titulo, '') || ' ' ||
        coalesce(NEW.contenido, '') || ' ' ||
        coalesce(NEW.contacto_nombre, '') || ' ' ||
        coalesce(NEW.contacto_organismo, '')
    );
    NEW.fecha_actualizacion := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER notas_update_tsv
    BEFORE INSERT OR UPDATE ON notas_proceso
    FOR EACH ROW EXECUTE FUNCTION notas_update_trigger();


-- =========================================================================
--  TABLA: etiquetas_proceso
--  Etiquetas libres por usuario u organización (para filtrar luego).
-- =========================================================================

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

    etiqueta            VARCHAR(80) NOT NULL,
    color               VARCHAR(20),   -- "#hexcolor" opcional

    -- Ámbito
    id_usuario          UUID REFERENCES usuarios(id_usuario)
                            ON DELETE CASCADE,
    organizacion        VARCHAR(80),
    -- (NULL en ambos = etiqueta del sistema)

    fecha_creacion      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_etiquetas_proceso  ON etiquetas_proceso(id_proceso);
CREATE INDEX idx_etiquetas_texto    ON etiquetas_proceso USING GIN(etiqueta gin_trgm_ops);
CREATE INDEX idx_etiquetas_usuario  ON etiquetas_proceso(id_usuario)
                                        WHERE id_usuario IS NOT NULL;

-- Restricción: no duplicar etiqueta-proceso para mismo ámbito
CREATE UNIQUE INDEX idx_etiquetas_unica
    ON etiquetas_proceso(id_proceso, etiqueta,
                         COALESCE(id_usuario::text, ''),
                         COALESCE(organizacion, ''));


-- =========================================================================
--  TABLA: adjuntos_manuales_proceso
--  Archivos que el equipo sube manualmente a un proceso (los que llegan
--  por otra vía que no sea el watcher ni los boletines).
-- =========================================================================

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

    nombre_archivo      VARCHAR(300) NOT NULL,
    nombre_original     VARCHAR(300),
    tipo_mime           VARCHAR(80),
    bytes               INTEGER,
    hash_archivo        VARCHAR(64),
    ruta_almacenamiento TEXT NOT NULL,

    descripcion         TEXT,

    -- ¿Procesarlo automáticamente como si viniera del watcher?
    procesar_automatico BOOLEAN NOT NULL DEFAULT FALSE,
    procesado           BOOLEAN NOT NULL DEFAULT FALSE,
    resultado_proceso   JSONB,

    fecha_subida        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    fecha_procesamiento TIMESTAMPTZ
);

CREATE INDEX idx_adjuntos_proceso   ON adjuntos_manuales_proceso(id_proceso,
                                                                 fecha_subida DESC);
CREATE INDEX idx_adjuntos_hash      ON adjuntos_manuales_proceso(hash_archivo)
                                        WHERE hash_archivo IS NOT NULL;


-- =========================================================================
--  TABLA: dominios_visitados
--  Para rate limiting global: una request cada 10s por dominio.
-- =========================================================================

CREATE TABLE dominios_visitados (
    dominio             VARCHAR(200) PRIMARY KEY,
    ultima_visita       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    requests_24h        INTEGER NOT NULL DEFAULT 0,
    fecha_reset_contador TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    -- Cache de robots.txt
    robots_txt          TEXT,
    robots_txt_fetched  TIMESTAMPTZ,
    robots_permite_bot  BOOLEAN NOT NULL DEFAULT TRUE
);


-- =========================================================================
--  VISTA: enlaces_para_chequear
--  URLs que ya toca volver a visitar.
-- =========================================================================

CREATE OR REPLACE VIEW enlaces_para_chequear AS
SELECT
    e.id_enlace,
    e.id_proceso,
    e.url,
    e.dominio,
    e.tipo,
    e.frecuencia_minutos,
    e.ultimo_check,
    e.ultimo_hash,
    p.estado AS estado_proceso
FROM enlaces_proceso e
JOIN procesos p ON p.id_proceso = e.id_proceso
WHERE e.activo = TRUE
  AND e.fecha_baja IS NULL
  AND p.estado NOT IN ('finalizado', 'archivado')
  AND (e.proximo_check IS NULL OR e.proximo_check <= NOW())
ORDER BY
    -- Priorizar procesos en estado activo
    CASE p.estado
        WHEN 'plazo_abierto' THEN 1
        WHEN 'plazo_cerrado' THEN 2
        WHEN 'en_pruebas' THEN 3
        WHEN 'bases_publicadas' THEN 4
        WHEN 'oep_publicada' THEN 5
        ELSE 9
    END,
    e.proximo_check NULLS FIRST;


-- =========================================================================
--  VISTA: pendientes_de_usuario
--  Tareas pendientes asignadas al usuario, no completadas.
-- =========================================================================

CREATE OR REPLACE VIEW pendientes_de_usuario AS
SELECT
    n.id_nota,
    n.id_proceso,
    n.titulo,
    n.contenido,
    n.prioridad,
    n.fecha_limite,
    n.fecha_creacion,
    n.id_usuario_asignado,
    p.administracion_nombre,
    p.cuerpo_nombre,
    -- Días hasta el límite (negativo = pasado)
    CASE
        WHEN n.fecha_limite IS NULL THEN NULL
        ELSE (n.fecha_limite - CURRENT_DATE)::INTEGER
    END AS dias_para_limite
FROM notas_proceso n
JOIN procesos p ON p.id_proceso = n.id_proceso
WHERE n.tipo = 'pendiente'
  AND n.completado = FALSE
ORDER BY
    CASE WHEN n.fecha_limite IS NULL THEN 1 ELSE 0 END,
    n.fecha_limite ASC,
    CASE n.prioridad
        WHEN 'alta' THEN 1
        WHEN 'normal' THEN 2
        WHEN 'baja' THEN 3
    END;


-- =========================================================================
--  FUNCIÓN: calcular_proximo_check
--  Devuelve la próxima fecha de chequeo según el estado del proceso y
--  la frecuencia configurada.
-- =========================================================================

CREATE OR REPLACE FUNCTION calcular_proximo_check(
    p_id_enlace UUID
) RETURNS TIMESTAMPTZ AS $$
DECLARE
    v_minutos       INTEGER;
    v_auto          BOOLEAN;
    v_estado        VARCHAR(40);
BEGIN
    SELECT e.frecuencia_minutos, e.frecuencia_auto, p.estado
    INTO v_minutos, v_auto, v_estado
    FROM enlaces_proceso e
    JOIN procesos p ON p.id_proceso = e.id_proceso
    WHERE e.id_enlace = p_id_enlace;

    -- Si frecuencia_auto, recalcular según estado
    IF v_auto THEN
        v_minutos := CASE v_estado
            WHEN 'oep_publicada'      THEN 1440  -- 24h
            WHEN 'bases_publicadas'   THEN 720   -- 12h
            WHEN 'plazo_abierto'      THEN 240   -- 4h
            WHEN 'plazo_cerrado'      THEN 120   -- 2h
            WHEN 'en_pruebas'         THEN 240   -- 4h
            ELSE 720
        END;
    END IF;

    RETURN NOW() + (v_minutos || ' minutes')::INTERVAL;
END;
$$ LANGUAGE plpgsql;


-- =========================================================================
--  COMENTARIOS DE USO
-- =========================================================================
--
--  Flujo típico de monitorización:
--
--  1. Usuario registra una URL desde el dashboard:
--     INSERT INTO enlaces_proceso (id_proceso, tipo, url, descripcion, ...)
--
--  2. Un job nocturno (o cada 5 minutos) lee la vista
--     `enlaces_para_chequear` y procesa los pendientes:
--     - Llama a 16_web_watcher.py para cada uno
--     - El watcher comprueba robots.txt, hace rate limit, descarga,
--       compara con ultimo_hash, detecta cambios y archivos nuevos.
--
--  3. Si hay cambio sin archivos nuevos:
--     - INSERT en snapshots_enlace con el nuevo hash y texto
--     - UPDATE enlaces_proceso con ultimo_hash, ultimo_cambio, proximo_check
--     - Crea evento en eventos_notificacion para avisar al usuario
--
--  4. Si hay archivo nuevo:
--     - INSERT en archivos_detectados con la URL del archivo
--     - El watcher descarga el archivo y lo guarda en disco
--     - UPDATE archivos_detectados con descargado=TRUE, hash, ruta
--     - Heurística decide si pasar por parser_bases.py:
--       * Si parece bases (nombre contiene "bases", "convocatoria"...)
--         → parser_bases.py + crear evento de fase "bases"
--       * Si parece lista de admitidos, calificaciones, tribunal...
--         → clasificador_fases + crear evento de la sub-fase correspondiente
--       * Si no encaja → requiere_revision = TRUE
--
--  5. Para notas, contactos, etiquetas y adjuntos manuales: CRUD
--     normal desde el dashboard, sin pipeline automática.
--
--  Ejemplos de queries útiles:
--
--  -- Próximas URLs a chequear:
--  SELECT * FROM enlaces_para_chequear LIMIT 20;
--
--  -- Archivos pendientes de procesar por el parser:
--  SELECT * FROM archivos_detectados
--  WHERE descargado = TRUE AND procesado = FALSE
--  ORDER BY fecha_descarga;
--
--  -- Pendientes del usuario actual:
--  SELECT * FROM pendientes_de_usuario
--  WHERE id_usuario_asignado = '<uuid>';
--
--  -- Buscar en todas las notas el texto "tribunal retraso":
--  SELECT n.*, p.cuerpo_nombre, p.administracion_nombre
--  FROM notas_proceso n
--  JOIN procesos p ON p.id_proceso = n.id_proceso
--  WHERE n.tsv @@ plainto_tsquery('spanish', 'tribunal retraso');