"""Add briefing improvements: rss_items topics/classified_at, messages metadata,
   rss_item_reactions, briefing_task_snapshot."""

from alembic import op

revision = "0028"
down_revision = "0027"


def upgrade() -> None:
    op.execute("""
        ALTER TABLE rss_items
            ADD COLUMN IF NOT EXISTS topics TEXT[] DEFAULT '{}',
            ADD COLUMN IF NOT EXISTS classified_at TIMESTAMPTZ
    """)
    op.execute("""
        ALTER TABLE messages
            ADD COLUMN IF NOT EXISTS metadata JSONB
    """)
    op.execute("""
        CREATE TABLE IF NOT EXISTS rss_item_reactions (
            id          SERIAL PRIMARY KEY,
            user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
            rss_item_id INTEGER NOT NULL REFERENCES rss_items(id) ON DELETE CASCADE,
            reaction    TEXT NOT NULL CHECK (reaction IN ('up', 'down')),
            created_at  TIMESTAMPTZ DEFAULT NOW(),
            UNIQUE (user_id, rss_item_id)
        )
    """)
    op.execute(
        "CREATE INDEX IF NOT EXISTS ix_rss_item_reactions_user_id "
        "ON rss_item_reactions(user_id)"
    )
    op.execute("""
        CREATE TABLE IF NOT EXISTS briefing_task_snapshot (
            id            SERIAL PRIMARY KEY,
            user_id       INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
            task_id       INTEGER NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
            snapshot_hash TEXT NOT NULL,
            last_briefed  TIMESTAMPTZ DEFAULT NOW(),
            UNIQUE (user_id, task_id)
        )
    """)
    op.execute(
        "CREATE INDEX IF NOT EXISTS ix_briefing_task_snapshot_user_id "
        "ON briefing_task_snapshot(user_id)"
    )


def downgrade() -> None:
    op.execute("DROP TABLE IF EXISTS briefing_task_snapshot")
    op.execute("DROP TABLE IF EXISTS rss_item_reactions")
    op.execute("ALTER TABLE messages DROP COLUMN IF EXISTS metadata")
    op.execute("ALTER TABLE rss_items DROP COLUMN IF EXISTS classified_at")
    op.execute("ALTER TABLE rss_items DROP COLUMN IF EXISTS topics")