-- =========================================================================
--  Esquema de base de datos: ALERTAS, REGLAS Y SUSCRIPCIONES A PROCESOS
--  Buscador Multisearch — Entrega 5 (sistema de notificaciones)
-- =========================================================================
--
--  Extiende `03_db_procesos_schema.sql`. Las tablas nuevas referencian
--  las existentes (procesos, eventos_proceso, usuarios). Cargar después
--  del schema base.
--
--  Cargar con: psql -d buscador -f 07_db_alertas_y_reglas.sql
-- =========================================================================


-- =========================================================================
--  EXTENSIÓN: roles de usuario (academia tiene varios)
-- =========================================================================

-- Si la tabla usuarios ya existe (de 03_db_procesos_schema.sql),
-- añadirle el campo rol.
ALTER TABLE usuarios ADD COLUMN IF NOT EXISTS rol VARCHAR(40)
    NOT NULL DEFAULT 'opositor_individual';
    -- Valores: 'academia_direccion', 'academia_contenidos',
    -- 'academia_comercial', 'opositor_individual',
    -- 'opositor_inscrito', 'opositor_admitido', 'opositor_aprobado',
    -- 'admin_sistema'

ALTER TABLE usuarios ADD COLUMN IF NOT EXISTS organizacion VARCHAR(80);
    -- Para academias: identificador de la organización (cgd_formacion,
    -- academia_x, etc.). NULL para usuarios individuales.

CREATE INDEX IF NOT EXISTS idx_usuarios_rol ON usuarios(rol);
CREATE INDEX IF NOT EXISTS idx_usuarios_organizacion ON usuarios(organizacion);


-- =========================================================================
--  TABLA: suscripciones_proceso
--  Usuarios que siguen un proceso concreto (no solo una categoría)
-- =========================================================================

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

    -- Etiqueta libre del usuario para este proceso
    -- ("Mi oposición", "Curso 2026 Hacienda", etc.)
    etiqueta            TEXT,

    -- Preferencias por canal para ESTE proceso (sobrescriben las globales)
    email_diario        BOOLEAN NOT NULL DEFAULT TRUE,
    email_inmediato     BOOLEAN NOT NULL DEFAULT TRUE,
    incluir_en_ical     BOOLEAN NOT NULL DEFAULT TRUE,

    -- Qué fases interesan especialmente (NULL = todas)
    fases_interes       INTEGER[],  -- [2, 3, 6] = bases, presentación, examen

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

CREATE UNIQUE INDEX idx_suscripciones_proceso_unica
    ON suscripciones_proceso(id_usuario, id_proceso)
    WHERE fecha_baja IS NULL;
CREATE INDEX idx_suscripciones_proceso_usuario
    ON suscripciones_proceso(id_usuario)
    WHERE fecha_baja IS NULL;


-- =========================================================================
--  TABLA: preferencias_notificacion
--  Configuración global por usuario (nivel 1 simple)
-- =========================================================================

CREATE TABLE preferencias_notificacion (
    id_preferencia      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    id_usuario          UUID NOT NULL REFERENCES usuarios(id_usuario)
                            ON DELETE CASCADE,

    -- Canales activos
    email_diario_activo     BOOLEAN NOT NULL DEFAULT TRUE,
    email_inmediato_activo  BOOLEAN NOT NULL DEFAULT TRUE,
    push_web_activo         BOOLEAN NOT NULL DEFAULT FALSE,
    sms_activo              BOOLEAN NOT NULL DEFAULT FALSE,
    telefono_sms            VARCHAR(20),
    ical_activo             BOOLEAN NOT NULL DEFAULT TRUE,
    ical_url_token          VARCHAR(64),  -- token único para la URL del .ics

    -- Para email inmediato: ¿qué fases?
    inmediato_fase_oep              BOOLEAN NOT NULL DEFAULT FALSE,
    inmediato_fase_bases            BOOLEAN NOT NULL DEFAULT TRUE,
    inmediato_fase_presentacion     BOOLEAN NOT NULL DEFAULT TRUE,
    inmediato_fase_lista_prov       BOOLEAN NOT NULL DEFAULT TRUE,
    inmediato_fase_lista_def        BOOLEAN NOT NULL DEFAULT TRUE,
    inmediato_fase_fecha_examen     BOOLEAN NOT NULL DEFAULT TRUE,
    inmediato_fase_desarrollo       BOOLEAN NOT NULL DEFAULT FALSE,
    inmediato_fase_resultados       BOOLEAN NOT NULL DEFAULT TRUE,
    inmediato_fase_aprobados        BOOLEAN NOT NULL DEFAULT TRUE,
    inmediato_fase_nombramiento     BOOLEAN NOT NULL DEFAULT FALSE,
    inmediato_correccion_errores    BOOLEAN NOT NULL DEFAULT TRUE,
    inmediato_ampliacion_plazo      BOOLEAN NOT NULL DEFAULT TRUE,

    -- Para iCal: ¿qué tipos de fecha?
    ical_fin_plazo                  BOOLEAN NOT NULL DEFAULT TRUE,
    ical_examenes                   BOOLEAN NOT NULL DEFAULT TRUE,
    ical_subsanaciones              BOOLEAN NOT NULL DEFAULT TRUE,
    ical_toma_posesion              BOOLEAN NOT NULL DEFAULT FALSE,

    -- Antispam (anti-burst)
    max_inmediatos_por_hora         SMALLINT NOT NULL DEFAULT 10,
    max_inmediatos_por_dia          SMALLINT NOT NULL DEFAULT 30,

    -- Horario de envío del email diario
    hora_envio_diario               TIME NOT NULL DEFAULT '19:00',
    zona_horaria                    VARCHAR(40) NOT NULL DEFAULT 'Europe/Madrid',

    fecha_actualizacion             TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_preferencias_usuario
    ON preferencias_notificacion(id_usuario);


-- =========================================================================
--  TABLA: reglas_notificacion
--  Reglas avanzadas (nivel 2) en formato JSONB
-- =========================================================================

CREATE TABLE reglas_notificacion (
    id_regla            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- A quién aplica la regla
    -- Una de estas tres opciones:
    id_usuario          UUID REFERENCES usuarios(id_usuario)
                            ON DELETE CASCADE,  -- regla personal
    organizacion        VARCHAR(80),            -- regla de organización
    es_regla_sistema    BOOLEAN NOT NULL DEFAULT FALSE,  -- regla del sistema

    -- Configuración
    nombre              TEXT NOT NULL,
    descripcion         TEXT,
    activa              BOOLEAN NOT NULL DEFAULT TRUE,
    prioridad           SMALLINT NOT NULL DEFAULT 100,  -- < número = más prioridad

    -- Condición: estructura JSONB con la lógica
    -- Ejemplo:
    -- {
    --   "fase": "bases",
    --   "administracion": "ayto_madrid",
    --   "cuerpo": "tag",
    --   "porcentaje_cambio_temario_min": 20
    -- }
    condicion           JSONB NOT NULL,

    -- Acción: qué hacer cuando dispara
    -- Ejemplo:
    -- {
    --   "canales": ["email_inmediato", "dashboard_alerta"],
    --   "destinatarios": ["contenidos@academia.es", "direccion@academia.es"],
    --   "asunto_template": "🟢 Nuevas bases {cuerpo} en {administracion}",
    --   "cuerpo_extra": "Revisar temario y actualizar materiales."
    -- }
    accion              JSONB NOT NULL,

    -- Antiabuso
    max_disparos_por_dia    SMALLINT NOT NULL DEFAULT 100,
    disparos_ultimas_24h    SMALLINT NOT NULL DEFAULT 0,
    fecha_reset_contador    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    suspendida_hasta        TIMESTAMPTZ,

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

CREATE INDEX idx_reglas_usuario        ON reglas_notificacion(id_usuario)
                                            WHERE id_usuario IS NOT NULL;
CREATE INDEX idx_reglas_organizacion   ON reglas_notificacion(organizacion)
                                            WHERE organizacion IS NOT NULL;
CREATE INDEX idx_reglas_sistema        ON reglas_notificacion(es_regla_sistema)
                                            WHERE es_regla_sistema = TRUE;
CREATE INDEX idx_reglas_activas        ON reglas_notificacion(activa)
                                            WHERE activa = TRUE;
CREATE INDEX idx_reglas_condicion      ON reglas_notificacion USING GIN(condicion);

-- Restricción: cada regla pertenece a uno y solo uno de los tres ámbitos
ALTER TABLE reglas_notificacion ADD CONSTRAINT regla_ambito_unico
    CHECK (
        (id_usuario IS NOT NULL)::int +
        (organizacion IS NOT NULL)::int +
        (es_regla_sistema)::int = 1
    );


-- =========================================================================
--  TABLA: eventos_notificacion
--  Cola unificada de eventos a notificar. Punto único de entrada del
--  motor de reglas. Cada evento detectado por el buscador crea una fila.
-- =========================================================================

CREATE TABLE eventos_notificacion (
    id_evento_notif     UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Origen del evento
    id_evento_proceso   UUID REFERENCES eventos_proceso(id_evento)
                            ON DELETE CASCADE,
    id_proceso          VARCHAR(200) REFERENCES procesos(id_proceso)
                            ON DELETE CASCADE,

    -- Clasificación
    tipo                VARCHAR(40) NOT NULL,
        -- 'cambio_fase', 'modificador_detectado', 'fecha_proxima',
        -- 'diff_temario_calculado', 'proceso_huerfano'
    fase                VARCHAR(40),
    numero_fase         SMALLINT,
    modificador         VARCHAR(40),  -- 'correccion_errores', 'ampliacion_plazo', ...

    -- Urgencia (heredada de la fase, ajustable por reglas)
    urgencia            VARCHAR(20) NOT NULL DEFAULT 'media',
        -- 'critica', 'alta', 'media', 'baja'

    -- Datos del evento (estructura libre según tipo)
    datos               JSONB,
        -- Ejemplo: {"fecha_fin_plazo": "2026-06-15", "tasa": 25.50, ...}

    -- Estado del procesamiento
    estado              VARCHAR(20) NOT NULL DEFAULT 'pendiente',
        -- 'pendiente', 'procesado', 'descartado'

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

CREATE INDEX idx_evnotif_estado ON eventos_notificacion(estado)
                                     WHERE estado = 'pendiente';
CREATE INDEX idx_evnotif_proceso ON eventos_notificacion(id_proceso);
CREATE INDEX idx_evnotif_urgencia ON eventos_notificacion(urgencia, fecha_creacion);


-- =========================================================================
--  TABLA: notificaciones_enviadas
--  Histórico de cada notificación que se ha disparado a un usuario
-- =========================================================================

CREATE TABLE notificaciones_enviadas (
    id_notificacion     UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    id_evento_notif     UUID NOT NULL REFERENCES eventos_notificacion(id_evento_notif)
                            ON DELETE CASCADE,
    id_usuario          UUID NOT NULL REFERENCES usuarios(id_usuario)
                            ON DELETE CASCADE,
    id_regla            UUID REFERENCES reglas_notificacion(id_regla)
                            ON DELETE SET NULL,

    canal               VARCHAR(40) NOT NULL,
        -- 'email_diario', 'email_inmediato', 'push_web', 'sms',
        -- 'dashboard_alerta', 'ical'

    estado              VARCHAR(20) NOT NULL DEFAULT 'pendiente',
        -- 'pendiente', 'enviada', 'fallida', 'silenciada_antispam',
        -- 'silenciada_usuario'

    asunto              TEXT,
    cuerpo_html         TEXT,
    cuerpo_texto        TEXT,

    fecha_envio         TIMESTAMPTZ,
    fecha_apertura      TIMESTAMPTZ,
    fecha_clic          TIMESTAMPTZ,
    error               TEXT,

    fecha_creacion      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_notif_evento     ON notificaciones_enviadas(id_evento_notif);
CREATE INDEX idx_notif_usuario    ON notificaciones_enviadas(id_usuario,
                                                              fecha_creacion DESC);
CREATE INDEX idx_notif_estado     ON notificaciones_enviadas(estado)
                                       WHERE estado = 'pendiente';
CREATE INDEX idx_notif_canal      ON notificaciones_enviadas(canal,
                                                              fecha_creacion DESC);


-- =========================================================================
--  TABLA: silenciados
--  Lista de "no enviar más esto a este usuario" — para botón "silenciar"
-- =========================================================================

CREATE TABLE silenciados (
    id_silenciado       UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    id_usuario          UUID NOT NULL REFERENCES usuarios(id_usuario)
                            ON DELETE CASCADE,

    -- Qué silenciar: combinación de criterios (cualquiera que matchee
    -- silencia la notificación)
    id_proceso          VARCHAR(200) REFERENCES procesos(id_proceso)
                            ON DELETE CASCADE,
    fase                VARCHAR(40),
    modificador         VARCHAR(40),
    categoria           VARCHAR(80),
    canal               VARCHAR(40),

    motivo              TEXT,
    fecha_alta          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    fecha_caducidad     TIMESTAMPTZ  -- NULL = silencio permanente
);

CREATE INDEX idx_silenciados_usuario ON silenciados(id_usuario);


-- =========================================================================
--  VISTA: eventos_pendientes_inmediato
--  Eventos esperando ser procesados por el job de email inmediato
-- =========================================================================

CREATE OR REPLACE VIEW eventos_pendientes_inmediato AS
SELECT
    en.id_evento_notif,
    en.id_proceso,
    en.tipo,
    en.fase,
    en.numero_fase,
    en.modificador,
    en.urgencia,
    en.datos,
    en.fecha_creacion,
    p.administracion_codigo,
    p.administracion_nombre,
    p.cuerpo_codigo,
    p.cuerpo_nombre,
    p.categoria_tematica
FROM eventos_notificacion en
JOIN procesos p ON p.id_proceso = en.id_proceso
WHERE en.estado = 'pendiente'
  AND en.urgencia IN ('critica', 'alta')
ORDER BY
  CASE en.urgencia
      WHEN 'critica' THEN 1
      WHEN 'alta' THEN 2
      ELSE 3
  END,
  en.fecha_creacion;


-- =========================================================================
--  VISTA: procesos_seguidos_por_usuario
--  Para cada usuario, qué procesos sigue (vía suscripción directa o
--  vía suscripción a la categoría temática)
-- =========================================================================

CREATE OR REPLACE VIEW procesos_seguidos_por_usuario AS
SELECT
    u.id_usuario,
    u.email,
    p.id_proceso,
    p.administracion_nombre,
    p.cuerpo_nombre,
    p.categoria_tematica,
    p.estado,
    -- ¿Por qué la sigue?
    CASE
        WHEN sp.id_suscripcion IS NOT NULL THEN 'directa'
        WHEN s.id_suscripcion IS NOT NULL THEN 'por_categoria'
    END AS tipo_suscripcion
FROM usuarios u
JOIN procesos p ON TRUE  -- ya filtramos abajo
LEFT JOIN suscripciones_proceso sp
    ON sp.id_usuario = u.id_usuario
   AND sp.id_proceso = p.id_proceso
   AND sp.fecha_baja IS NULL
LEFT JOIN suscripciones s
    ON s.id_usuario = u.id_usuario
   AND s.categoria = p.categoria_tematica
   AND s.activa = TRUE
WHERE u.estado = 'activo'
  AND p.estado != 'archivado'
  AND (sp.id_suscripcion IS NOT NULL OR s.id_suscripcion IS NOT NULL);


-- =========================================================================
--  FUNCIÓN: evaluar_destinatarios
--  Para un evento de notificación, devuelve la lista de usuarios que
--  deben recibirlo según las reglas, silencios y antispam.
-- =========================================================================

CREATE OR REPLACE FUNCTION evaluar_destinatarios(p_id_evento_notif UUID)
RETURNS TABLE (
    id_usuario          UUID,
    canal               VARCHAR(40),
    id_regla_origen     UUID
) AS $$
BEGIN
    -- Esta función materializa la lógica del motor de reglas. La
    -- implementación completa es compleja (involucra evaluar las
    -- condiciones JSONB de cada regla). Aquí se da el esqueleto.

    RETURN QUERY
    -- 1. Suscriptores directos al proceso
    SELECT
        sp.id_usuario,
        CASE
            WHEN sp.email_inmediato THEN 'email_inmediato'
            ELSE 'email_diario'
        END::VARCHAR(40) AS canal,
        NULL::UUID AS id_regla_origen
    FROM eventos_notificacion en
    JOIN procesos p ON p.id_proceso = en.id_proceso
    JOIN suscripciones_proceso sp ON sp.id_proceso = p.id_proceso
                                  AND sp.fecha_baja IS NULL
    WHERE en.id_evento_notif = p_id_evento_notif

    UNION

    -- 2. Suscriptores a la categoría temática del proceso
    SELECT
        s.id_usuario,
        'email_diario'::VARCHAR(40) AS canal,
        NULL::UUID AS id_regla_origen
    FROM eventos_notificacion en
    JOIN procesos p ON p.id_proceso = en.id_proceso
    JOIN suscripciones s ON s.categoria = p.categoria_tematica
                         AND s.activa = TRUE
    WHERE en.id_evento_notif = p_id_evento_notif
      -- Pero NO si ya tienen suscripción directa
      AND NOT EXISTS (
          SELECT 1 FROM suscripciones_proceso sp
          WHERE sp.id_usuario = s.id_usuario
            AND sp.id_proceso = p.id_proceso
            AND sp.fecha_baja IS NULL
      );

    -- TODO: 3. Aplicar reglas_notificacion (evaluación de condicion JSONB)
    -- TODO: 4. Excluir silenciados (tabla silenciados)
    -- TODO: 5. Aplicar antispam (max_inmediatos_por_hora/dia)
END;
$$ LANGUAGE plpgsql;


-- =========================================================================
--  COMENTARIOS DE USO
-- =========================================================================
--
--  Flujo completo de notificación:
--
--  1. El buscador detecta un evento (nuevo hit) y lo persiste en
--     eventos_proceso (tabla del schema base).
--  2. Un trigger (o el propio código de la aplicación) crea una entrada
--     en eventos_notificacion con el tipo, fase y urgencia derivados.
--  3. Un job nocturno procesa los eventos para email_diario.
--     Un job cada 5 minutos procesa los críticos/altos para email_inmediato.
--  4. Para cada evento, llama a evaluar_destinatarios() y crea una fila
--     en notificaciones_enviadas por cada combinación usuario × canal.
--  5. El sender de emails toma las notificaciones pendientes, renderiza
--     la plantilla correspondiente y las envía.
--
--  Para el calendario .ics:
--  - Cada usuario con ical_activo=TRUE tiene una URL única
--    (https://buscador.example/ical/{ical_url_token}.ics) que sirve
--    un calendario dinámico generado a partir de las fechas_clave de
--    los procesos a los que está suscrito.
--
--  Para el dashboard:
--  - Lee directamente las tablas procesos, eventos_proceso,
--    fechas_clave, eventos_notificacion (cola de alertas pendientes
--    de mostrar al usuario al entrar).