Dense misses: "ERR_0x4A2F", "v1.2.3-beta", "userId", "getServerSideProps"
Sparse misses: "how do I handle connection pooling" → misses "database concurrency"
Hybrid catches: both — dense handles intent, sparse handles exact matches

-- Add tsvector column for BM25 full-text search
ALTER TABLE chunks ADD COLUMN fts_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(heading, '') || ' ' || content)
  ) STORED;

-- GIN index for fast full-text lookups
CREATE INDEX CONCURRENTLY chunks_fts_gin_idx ON chunks USING gin(fts_vector);

RRF_score(document d) = Σ(1 / (k + rank(d, query_i)))

-- ✅ Hybrid RRF search — single PostgreSQL query, no external services
-- Parameters bound at application layer: $1=embedding, $2=query_text, $3=org_id, $4=limit, $5=k

WITH
-- ── Dense retrieval: vector ANN ─────────────────────────────────────────────
dense_results AS (
  SELECT
    id,
    content,
    heading,
    document_id,
    token_count,
    RANK() OVER (ORDER BY embedding <#> $1::vector) AS rank
  FROM chunks
  WHERE org_id = $3::uuid   -- RLS also enforces this
  ORDER BY embedding <#> $1::vector
  LIMIT 50   -- Retrieve 50 candidates for fusion (more than final limit)
),

-- ── Sparse retrieval: BM25 full-text ────────────────────────────────────────
sparse_results AS (
  SELECT
    id,
    content,
    heading,
    document_id,
    token_count,
    RANK() OVER (ORDER BY ts_rank_cd(fts_vector, query) DESC) AS rank
  FROM chunks,
       plainto_tsquery('english', $2) AS query
  WHERE
    org_id = $3::uuid
    AND fts_vector @@ query
  ORDER BY ts_rank_cd(fts_vector, query) DESC
  LIMIT 50
),

-- ── RRF fusion ──────────────────────────────────────────────────────────────
rrf_scores AS (
  SELECT
    COALESCE(d.id, s.id)           AS id,
    COALESCE(d.content, s.content) AS content,
    COALESCE(d.heading, s.heading) AS heading,
    COALESCE(d.document_id, s.document_id) AS document_id,
    COALESCE(d.token_count, s.token_count) AS token_count,
    COALESCE(1.0 / ($5 + d.rank), 0.0) +   -- k = 60 smoothing constant
    COALESCE(1.0 / ($5 + s.rank), 0.0) AS rrf_score
  FROM dense_results d
  FULL OUTER JOIN sparse_results s ON d.id = s.id
)

SELECT id, content, heading, document_id, token_count, rrf_score
FROM rrf_scores
ORDER BY rrf_score DESC
LIMIT $4;

// ✅ Hybrid search with type-safe parameter binding
// file: packages/db/src/retrieval/HybridSearch.ts

import { db } from '../drizzle'
import { sql } from 'drizzle-orm'
import { embedAndNormalise } from '../embeddings'

export interface HybridSearchResult {
  id: string
  content: string
  heading: string
  documentId: string
  tokenCount: number
  rrfScore: number
}

export async function hybridSearch(
  orgId: string,
  queryText: string,
  limit = 10,
  k = 60,
): Promise<HybridSearchResult[]> {
  // Embed the user query — same model and normalisation as stored chunks
  const queryEmbedding = await embedAndNormalise(queryText)
  const vectorLiteral = `[${queryEmbedding.join(',')}]`

  const results = await db.execute(sql`
    WITH
    dense_results AS (
      SELECT id, content, heading, document_id, token_count,
        RANK() OVER (ORDER BY embedding <#> ${vectorLiteral}::vector) AS rank
      FROM chunks
      WHERE org_id = ${orgId}::uuid
      ORDER BY embedding <#> ${vectorLiteral}::vector
      LIMIT 50
    ),
    sparse_results AS (
      SELECT id, content, heading, document_id, token_count,
        RANK() OVER (ORDER BY ts_rank_cd(fts_vector, query) DESC) AS rank
      FROM chunks, plainto_tsquery('english', ${queryText}) AS query
      WHERE org_id = ${orgId}::uuid AND fts_vector @@ query
      ORDER BY ts_rank_cd(fts_vector, query) DESC
      LIMIT 50
    ),
    rrf_scores AS (
      SELECT
        COALESCE(d.id, s.id) AS id,
        COALESCE(d.content, s.content) AS content,
        COALESCE(d.heading, s.heading) AS heading,
        COALESCE(d.document_id, s.document_id) AS document_id,
        COALESCE(d.token_count, s.token_count) AS token_count,
        COALESCE(1.0 / (${k} + d.rank), 0.0) +
        COALESCE(1.0 / (${k} + s.rank), 0.0) AS rrf_score
      FROM dense_results d
      FULL OUTER JOIN sparse_results s ON d.id = s.id
    )
    SELECT id, content, heading, document_id, token_count, rrf_score
    FROM rrf_scores
    ORDER BY rrf_score DESC
    LIMIT ${limit}
  `)

  return results.rows as HybridSearchResult[]
}

// ✅ Debug helper — see which signal found which result
export async function hybridSearchWithDebug(orgId: string, queryText: string) {
  const results = await db.execute(sql`
    WITH
    dense_results AS (
      SELECT id, RANK() OVER (ORDER BY embedding <#> ${await embedLiteral(queryText)}::vector) AS dense_rank
      FROM chunks WHERE org_id = ${orgId}::uuid
      ORDER BY embedding <#> ${await embedLiteral(queryText)}::vector LIMIT 50
    ),
    sparse_results AS (
      SELECT id, RANK() OVER (ORDER BY ts_rank_cd(fts_vector, q) DESC) AS sparse_rank
      FROM chunks, plainto_tsquery('english', ${queryText}) q
      WHERE org_id = ${orgId}::uuid AND fts_vector @@ q LIMIT 50
    )
    SELECT
      c.id, c.content,
      d.dense_rank,
      s.sparse_rank,
      COALESCE(1.0/(60+d.dense_rank),0) + COALESCE(1.0/(60+s.sparse_rank),0) AS rrf_score,
      CASE WHEN d.id IS NOT NULL AND s.id IS NOT NULL THEN 'both'
           WHEN d.id IS NOT NULL THEN 'dense_only'
           ELSE 'sparse_only' END AS found_by
    FROM chunks c
    LEFT JOIN dense_results d ON c.id = d.id
    LEFT JOIN sparse_results s ON c.id = s.id
    WHERE d.id IS NOT NULL OR s.id IS NOT NULL
    ORDER BY rrf_score DESC
    LIMIT 10
  `)
  return results.rows
}