"""manual revision schema support

Revision ID: 20260327_0002
Revises: 20260326_0001
Create Date: 2026-03-27 21:55:00.000000
"""

from collections.abc import Sequence

from alembic import op
import sqlalchemy as sa


revision: str = "20260327_0002"
down_revision: str | None = "20260326_0001"
branch_labels: Sequence[str] | None = None
depends_on: Sequence[str] | None = None


def upgrade() -> None:
    """Add manual revision columns required by the live schema."""

    with op.batch_alter_table("review_tasks") as batch_op:
        batch_op.add_column(sa.Column("latest_revision_asset_id", sa.Integer(), nullable=True))
        batch_op.add_column(sa.Column("resume_asset_id", sa.Integer(), nullable=True))

    with op.batch_alter_table("assets") as batch_op:
        batch_op.add_column(sa.Column("parent_asset_id", sa.Integer(), nullable=True))
        batch_op.add_column(sa.Column("root_asset_id", sa.Integer(), nullable=True))
        batch_op.add_column(sa.Column("version_no", sa.Integer(), nullable=False, server_default="0"))
        batch_op.add_column(
            sa.Column("is_current_version", sa.Boolean(), nullable=False, server_default=sa.false())
        )
        batch_op.create_index("ix_assets_parent_asset_id", ["parent_asset_id"], unique=False)
        batch_op.create_index("ix_assets_root_asset_id", ["root_asset_id"], unique=False)

    op.execute("UPDATE assets SET version_no = 0 WHERE version_no IS NULL")
    op.execute("UPDATE assets SET is_current_version = 0 WHERE is_current_version IS NULL")

    with op.batch_alter_table("assets") as batch_op:
        batch_op.alter_column("version_no", server_default=None)
        batch_op.alter_column("is_current_version", server_default=None)


def downgrade() -> None:
    """Remove manual revision schema additions."""

    with op.batch_alter_table("assets") as batch_op:
        batch_op.drop_index("ix_assets_root_asset_id")
        batch_op.drop_index("ix_assets_parent_asset_id")
        batch_op.drop_column("is_current_version")
        batch_op.drop_column("version_no")
        batch_op.drop_column("root_asset_id")
        batch_op.drop_column("parent_asset_id")

    with op.batch_alter_table("review_tasks") as batch_op:
        batch_op.drop_column("resume_asset_id")
        batch_op.drop_column("latest_revision_asset_id")