"""Full-text search vector on questions, for hybrid lexical + vector retrieval.

Revision ID: q9c0d1e2f364
Revises: p8b9c0d1e253
"""
from alembic import op

revision = "q9c0d1e2f364"
down_revision = "p8b9c0d1e253"
branch_labels = None
depends_on = None


def upgrade():
    # Generated column keeps the index in step with edits without a trigger.
    op.execute("""
    ALTER TABLE questions ADD COLUMN IF NOT EXISTS search_vector tsvector
    GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(question_text, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(options::text, '')), 'B') ||
        setweight(to_tsvector('english', coalesce(explanation, '')), 'C')
    ) STORED
    """)
    op.execute("CREATE INDEX IF NOT EXISTS ix_questions_search_vector ON questions USING GIN (search_vector)")


def downgrade():
    op.execute("DROP INDEX IF EXISTS ix_questions_search_vector")
    op.execute("ALTER TABLE questions DROP COLUMN IF EXISTS search_vector")