-- Add tsvector column with automatic update trigger
ALTER TABLE documents ADD COLUMN IF NOT EXISTS search_vector tsvector;

-- Populate from existing data (weighted: title=A, content=B)
UPDATE documents SET search_vector =
  setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
  setweight(to_tsvector('english', coalesce(content, '')), 'B');

-- GIN index for fast full-text search
CREATE INDEX IF NOT EXISTS idx_documents_search
  ON documents USING GIN (search_vector);

-- Trigger to keep search_vector updated on INSERT/UPDATE
CREATE OR REPLACE FUNCTION documents_search_vector_update() RETURNS trigger AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(NEW.content, '')), 'B');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_documents_search_vector
  BEFORE INSERT OR UPDATE OF title, content ON documents
  FOR EACH ROW EXECUTE FUNCTION documents_search_vector_update();

from sqlalchemy import func, select, text
from sqlalchemy.ext.asyncio import AsyncSession

from src.db.models import Document


async def search_documents(
    db: AsyncSession,
    query: str,
    limit: int = 20,
    offset: int = 0,
    user_id: str | None = None,
) -> dict:
    """Search documents using pre-computed tsvector with GIN index."""
    tsquery = func.websearch_to_tsquery("english", query)

    stmt = (
        select(
            Document.id,
            Document.title,
            Document.content,
            func.ts_rank_cd(Document.search_vector, tsquery, 32).label("rank"),
            func.ts_headline(
                "english",
                Document.content,
                tsquery,
                text("'MaxWords=30, MinWords=15, MaxFragments=2'"),
            ).label("snippet"),
        )
        .where(Document.search_vector.op("@@")(tsquery))
        .order_by(text("rank DESC"))
        .limit(limit)
        .offset(offset)
    )

    if user_id:
        stmt = stmt.where(Document.user_id == user_id)

    result = await db.execute(stmt)
    rows = result.all()

    return {
        "results": [
            {"id": str(r.id), "title": r.title, "rank": float(r.rank), "snippet": r.snippet}
            for r in rows
        ],
        "total": len(rows),
    }

async def hybrid_search(
    db: AsyncSession,
    query: str,
    query_embedding: list[float],
    limit: int = 20,
    text_weight: float = 0.4,
    vector_weight: float = 0.6,
) -> list[dict]:
    """Combine full-text and vector search using RRF."""
    # Full-text results
    text_results = await search_documents(db, query, limit=limit * 2)
    text_ids = [r["id"] for r in text_results["results"]]

    # Vector results (from vector-search skill)
    vector_stmt = (
        select(Document.id, Document.title)
        .order_by(Document.embedding.cosine_distance(query_embedding))
        .limit(limit * 2)
    )
    vector_result = await db.execute(vector_stmt)
    vector_ids = [str(r.id) for r in vector_result.all()]

    # Reciprocal Rank Fusion: score = sum(1 / (k + rank))
    k = 60  # RRF constant
    scores: dict[str, float] = {}
    for rank, doc_id in enumerate(text_ids):
        scores[doc_id] = scores.get(doc_id, 0) + text_weight / (k + rank)
    for rank, doc_id in enumerate(vector_ids):
        scores[doc_id] = scores.get(doc_id, 0) + vector_weight / (k + rank)

    ranked = sorted(scores.items(), key=lambda x: x[1], reverse=True)[:limit]
    return [{"id": doc_id, "rrf_score": score} for doc_id, score in ranked]

-- Enable pg_trgm extension (already available with pgvector)
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Trigram index for autocomplete
CREATE INDEX IF NOT EXISTS idx_documents_title_trgm
  ON documents USING GIN (title gin_trgm_ops);

async def autocomplete(
    db: AsyncSession, prefix: str, limit: int = 5
) -> list[str]:
    """Return title suggestions matching prefix."""
    stmt = (
        select(Document.title)
        .where(Document.title.ilike(f"{prefix}%"))
        .order_by(func.similarity(Document.title, prefix).desc())
        .limit(limit)
    )
    result = await db.execute(stmt)
    return [r.title for r in result.all()]

-- Reindex after bulk inserts (run during low-traffic windows)
REINDEX INDEX CONCURRENTLY idx_documents_search;

-- Update search statistics for query planner
ANALYZE documents;

### Search Indexer Implementation

**Engine**: PostgreSQL tsvector + GIN
**Tables**: [documents / custom]
**Weights**: title=A, content=B, [metadata=C]
**Query Parser**: [websearch_to_tsquery / plainto_tsquery]
**Hybrid**: [enabled / disabled], text_weight=[0.4], vector_weight=[0.6]
**Autocomplete**: [pg_trgm / disabled]
**Maintenance**: [scheduled reindex / manual]