"""Section-level index for articles, so a citation can point at the right part.

An article embeds as one vector, which is enough to find the article but not the
paragraph. Sections live in a JSON column, so they are projected into a derived
table that can carry its own vector and full-text index.

Revision ID: x6d7e8f9a0b1
Revises: w5c6d7e8f9a0
"""
from alembic import op

revision = "x6d7e8f9a0b1"
down_revision = "w5c6d7e8f9a0"
branch_labels = None
depends_on = None


def upgrade():
    op.execute("""
    CREATE TABLE IF NOT EXISTS article_section_index (
        id SERIAL PRIMARY KEY,
        article_id INTEGER NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
        section_id VARCHAR(64) NOT NULL,
        title TEXT,
        content TEXT,
        embedding vector(1024),
        embedding_model VARCHAR(120),
        embedded_at TIMESTAMP,
        search_vector tsvector GENERATED ALWAYS AS (
            setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
            setweight(to_tsvector('english', coalesce(content, '')), 'B')
        ) STORED,
        CONSTRAINT uq_article_section_index UNIQUE (article_id, section_id)
    )
    """)
    op.execute("CREATE INDEX IF NOT EXISTS ix_asi_search ON article_section_index USING GIN (search_vector)")
    op.execute("CREATE INDEX IF NOT EXISTS ix_asi_article ON article_section_index(article_id)")
    op.execute("CREATE INDEX IF NOT EXISTS ix_asi_model ON article_section_index(embedding_model)")


def downgrade():
    op.execute("DROP TABLE IF EXISTS article_section_index")