-- 48_hybrid_search_filter_unsearchable.sql
-- Rewrite hybrid_search_chunks to skip chunks with is_searchable=FALSE.
-- This is the same function as before, just with an extra AND c.is_searchable
-- in BOTH the bm25 and dense CTEs.

CREATE OR REPLACE FUNCTION public.hybrid_search_chunks(
  q_text text,
  q_embedding vector,
  q_lang text DEFAULT 'pt',
  q_doc_id text DEFAULT NULL,
  q_type text DEFAULT NULL,
  q_classification text DEFAULT NULL,
  q_ufo_only boolean DEFAULT FALSE,
  k integer DEFAULT 100,
  rrf_k integer DEFAULT 60
)
RETURNS TABLE(
  chunk_pk bigint,
  doc_id text,
  chunk_id text,
  page integer,
  type text,
  bbox jsonb,
  content_en text,
  content_pt text,
  classification text,
  score double precision,
  bm25_rank integer,
  dense_rank integer
)
LANGUAGE plpgsql STABLE
AS $function$
BEGIN
  RETURN QUERY
  WITH
  ts_q AS (
    SELECT CASE WHEN q_lang = 'en'
                THEN websearch_to_tsquery('public.en_unaccent'::regconfig, q_text)
                ELSE websearch_to_tsquery('public.pt_unaccent'::regconfig, q_text)
            END AS q
  ),
  bm25 AS (
    SELECT c.chunk_pk,
           row_number() OVER (ORDER BY
             ts_rank_cd(
               CASE WHEN q_lang = 'en' THEN c.ts_en ELSE c.ts_pt END,
               (SELECT q FROM ts_q)
             ) DESC NULLS LAST
           )::INT AS r
    FROM public.chunks c
    WHERE c.is_searchable
      AND (CASE WHEN q_lang = 'en' THEN c.ts_en ELSE c.ts_pt END) @@ (SELECT q FROM ts_q)
      AND (q_doc_id IS NULL OR c.doc_id = q_doc_id)
      AND (q_type IS NULL OR c.type = q_type)
      AND (q_classification IS NULL OR c.classification = q_classification)
      AND (NOT q_ufo_only OR c.ufo_anomaly = TRUE)
    LIMIT k
  ),
  dense AS (
    SELECT c.chunk_pk,
           row_number() OVER (ORDER BY c.embedding <=> q_embedding)::INT AS r
    FROM public.chunks c
    WHERE c.is_searchable
      AND c.embedding IS NOT NULL
      AND (q_doc_id IS NULL OR c.doc_id = q_doc_id)
      AND (q_type IS NULL OR c.type = q_type)
      AND (q_classification IS NULL OR c.classification = q_classification)
      AND (NOT q_ufo_only OR c.ufo_anomaly = TRUE)
    ORDER BY c.embedding <=> q_embedding
    LIMIT k
  ),
  fused AS (
    SELECT COALESCE(b.chunk_pk, d.chunk_pk) AS chunk_pk,
           ((1.0::DOUBLE PRECISION / (rrf_k + COALESCE(b.r, k + 1))::DOUBLE PRECISION) +
            (1.0::DOUBLE PRECISION / (rrf_k + COALESCE(d.r, k + 1))::DOUBLE PRECISION)) AS score,
           b.r AS bm25_rank,
           d.r AS dense_rank
    FROM bm25 b
    FULL OUTER JOIN dense d USING (chunk_pk)
  )
  SELECT c.chunk_pk, c.doc_id, c.chunk_id, c.page, c.type, c.bbox,
         c.content_en, c.content_pt, c.classification,
         f.score, f.bm25_rank, f.dense_rank
  FROM fused f
  JOIN public.chunks c USING (chunk_pk)
  ORDER BY f.score DESC
  LIMIT k;
END
$function$;
