"""workshop overlays

Revision ID: 0002_workshop_overlays
Revises: b2c684fddbd3
Create Date: 2026-05-07
"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


revision: str = "0002_workshop_overlays"
down_revision: Union[str, Sequence[str], None] = "b2c684fddbd3"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def _existing_overlays_table() -> sa.Table:
    """Pre-migration shape used as `copy_from` so batch_alter_table rebuilds
    overlays without the inline UNIQUE on `name` (replaced by partial unique
    indexes after the recreate)."""
    metadata = sa.MetaData()
    return sa.Table(
        "overlays",
        metadata,
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("name", sa.String(length=128), nullable=False),
        sa.Column("path", sa.String(length=512), nullable=False),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
    )


def upgrade() -> None:
    # 1. Recreate `overlays` with `type`, `user_id`, autoincrement, and no inline UNIQUE on name.
    with op.batch_alter_table(
        "overlays",
        recreate="always",
        copy_from=_existing_overlays_table(),
        table_kwargs={"sqlite_autoincrement": True},
    ) as batch_op:
        batch_op.add_column(
            sa.Column(
                "type",
                sa.String(length=16),
                nullable=False,
                server_default="external",
            )
        )
        batch_op.add_column(
            sa.Column(
                "user_id",
                sa.Integer(),
                sa.ForeignKey("users.id", name="fk_overlays_user_id_users"),
                nullable=True,
            )
        )
        batch_op.create_index("ix_overlays_type_user_id", ["type", "user_id"])

    # Drop the temporary server_default once existing rows are backfilled.
    with op.batch_alter_table("overlays") as batch_op:
        batch_op.alter_column("type", server_default=None)

    # 2. Partial unique indexes for name uniqueness:
    #    - system overlays (user_id IS NULL): globally unique by name
    #    - user overlays (user_id IS NOT NULL): unique per user by name
    op.create_index(
        "uq_overlay_name_system",
        "overlays",
        ["name"],
        unique=True,
        sqlite_where=sa.text("user_id IS NULL"),
    )
    op.create_index(
        "uq_overlay_name_per_user",
        "overlays",
        ["name", "user_id"],
        unique=True,
        sqlite_where=sa.text("user_id IS NOT NULL"),
    )

    # 3. workshop_items registry (global, deduplicated by steam_id).
    op.create_table(
        "workshop_items",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("steam_id", sa.String(length=20), nullable=False, unique=True),
        sa.Column("title", sa.String(length=255), nullable=False, server_default=""),
        sa.Column("filename", sa.String(length=255), nullable=False, server_default=""),
        sa.Column("file_url", sa.Text(), nullable=False, server_default=""),
        sa.Column("file_size", sa.BigInteger(), nullable=False, server_default="0"),
        sa.Column("time_updated", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("preview_url", sa.Text(), nullable=False, server_default=""),
        sa.Column("last_downloaded_at", sa.DateTime(), nullable=True),
        sa.Column("last_error", sa.Text(), nullable=False, server_default=""),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
    )

    # 4. overlay_workshop_items association.
    op.create_table(
        "overlay_workshop_items",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column(
            "overlay_id",
            sa.Integer(),
            sa.ForeignKey("overlays.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "workshop_item_id",
            sa.Integer(),
            sa.ForeignKey("workshop_items.id", ondelete="RESTRICT"),
            nullable=False,
        ),
        sa.UniqueConstraint(
            "overlay_id", "workshop_item_id", name="uq_overlay_workshop_item"
        ),
    )
    op.create_index(
        "ix_owi_workshop_item",
        "overlay_workshop_items",
        ["workshop_item_id"],
    )

    # 5. Add overlay_id to jobs for build_overlay tracking.
    with op.batch_alter_table("jobs") as batch_op:
        batch_op.add_column(
            sa.Column(
                "overlay_id",
                sa.Integer(),
                sa.ForeignKey("overlays.id", name="fk_jobs_overlay_id_overlays"),
                nullable=True,
            )
        )


def downgrade() -> None:
    with op.batch_alter_table("jobs") as batch_op:
        batch_op.drop_column("overlay_id")

    op.drop_index("ix_owi_workshop_item", table_name="overlay_workshop_items")
    op.drop_table("overlay_workshop_items")
    op.drop_table("workshop_items")

    op.drop_index("uq_overlay_name_per_user", table_name="overlays")
    op.drop_index("uq_overlay_name_system", table_name="overlays")
    op.drop_index("ix_overlays_type_user_id", table_name="overlays")

    # Recreate `overlays` to drop type/user_id and restore single-column UNIQUE on name.
    current_overlays = sa.Table(
        "overlays",
        sa.MetaData(),
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("name", sa.String(length=128), nullable=False),
        sa.Column("path", sa.String(length=512), nullable=False),
        sa.Column("type", sa.String(length=16), nullable=False),
        sa.Column(
            "user_id",
            sa.Integer(),
            sa.ForeignKey("users.id", name="fk_overlays_user_id_users"),
            nullable=True,
        ),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
    )
    with op.batch_alter_table(
        "overlays",
        recreate="always",
        copy_from=current_overlays,
    ) as batch_op:
        batch_op.drop_column("user_id")
        batch_op.drop_column("type")
        batch_op.create_unique_constraint("uq_overlays_name", ["name"])
