"""Media as a searchable corpus, ready for a vision embedder.

Images are currently found only by the filename someone typed. This gives them a
caption, alt text and tags to search on now, and an embedding column that a
vision-capable model can fill later without another migration — `embedding_model`
records which model produced each vector, so a text-embedded caption and a
vision-embedded image are distinguishable rather than silently mixed.

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

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


def upgrade():
    op.execute("""
    CREATE TABLE IF NOT EXISTS media_assets (
        id SERIAL PRIMARY KEY,
        path VARCHAR(500) UNIQUE NOT NULL,
        title VARCHAR(300),
        caption TEXT,
        alt_text TEXT,
        kind VARCHAR(20) NOT NULL DEFAULT 'image',
        category_id INTEGER REFERENCES question_categories(id) ON DELETE SET NULL,
        user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
        embedding vector(1024),
        embedding_model VARCHAR(120),
        embedded_at TIMESTAMP,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        search_vector tsvector GENERATED ALWAYS AS (
            setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
            setweight(to_tsvector('english', coalesce(caption, '')), 'B') ||
            setweight(to_tsvector('english', coalesce(alt_text, '')), 'C')
        ) STORED
    )
    """)
    op.execute("CREATE INDEX IF NOT EXISTS ix_media_search ON media_assets USING GIN (search_vector)")
    op.execute("CREATE INDEX IF NOT EXISTS ix_media_model ON media_assets(embedding_model)")
    op.execute("""
    CREATE TABLE IF NOT EXISTS media_tag_links (
        id SERIAL PRIMARY KEY,
        media_id INTEGER NOT NULL REFERENCES media_assets(id) ON DELETE CASCADE,
        tag_id INTEGER NOT NULL REFERENCES question_tags(id) ON DELETE CASCADE,
        CONSTRAINT uq_media_tag UNIQUE (media_id, tag_id)
    )
    """)
    op.execute("CREATE INDEX IF NOT EXISTS ix_media_tag_media ON media_tag_links(media_id)")


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