"""global map overlays

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

from alembic import op
import sqlalchemy as sa


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


def upgrade() -> None:
    with op.batch_alter_table("jobs") as batch_op:
        batch_op.alter_column("user_id", existing_type=sa.Integer(), nullable=True)

    op.create_table(
        "global_overlay_sources",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column(
            "overlay_id",
            sa.Integer(),
            sa.ForeignKey("overlays.id", ondelete="CASCADE"),
            nullable=False,
            unique=True,
        ),
        sa.Column("source_key", sa.String(length=64), nullable=False, unique=True),
        sa.Column("source_type", sa.String(length=32), nullable=False),
        sa.Column("source_url", sa.Text(), nullable=False),
        sa.Column("last_manifest_hash", sa.String(length=64), nullable=False, server_default=""),
        sa.Column("last_refreshed_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),
    )
    op.create_index("ix_global_overlay_sources_type", "global_overlay_sources", ["source_type"])

    op.create_table(
        "global_overlay_items",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column(
            "source_id",
            sa.Integer(),
            sa.ForeignKey("global_overlay_sources.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("item_key", sa.String(length=255), nullable=False),
        sa.Column("display_name", sa.String(length=255), nullable=False, server_default=""),
        sa.Column("download_url", sa.Text(), nullable=False),
        sa.Column("expected_vpk_name", sa.String(length=255), nullable=False, server_default=""),
        sa.Column("expected_size", sa.BigInteger(), nullable=True),
        sa.Column("expected_md5", sa.String(length=32), nullable=False, server_default=""),
        sa.Column("etag", sa.String(length=255), nullable=False, server_default=""),
        sa.Column("last_modified", sa.String(length=255), nullable=False, server_default=""),
        sa.Column("content_length", sa.BigInteger(), nullable=True),
        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),
        sa.UniqueConstraint("source_id", "item_key", name="uq_global_overlay_item_source_key"),
    )
    op.create_index("ix_global_overlay_items_source", "global_overlay_items", ["source_id"])

    op.create_table(
        "global_overlay_item_files",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column(
            "item_id",
            sa.Integer(),
            sa.ForeignKey("global_overlay_items.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("vpk_name", sa.String(length=255), nullable=False),
        sa.Column("cache_path", sa.Text(), nullable=False),
        sa.Column("size", sa.BigInteger(), nullable=False),
        sa.Column("md5", sa.String(length=32), nullable=False, server_default=""),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.UniqueConstraint("item_id", "vpk_name", name="uq_global_overlay_item_file_name"),
    )
    op.create_index("ix_global_overlay_item_files_item", "global_overlay_item_files", ["item_id"])


def downgrade() -> None:
    op.drop_index("ix_global_overlay_item_files_item", table_name="global_overlay_item_files")
    op.drop_table("global_overlay_item_files")
    op.drop_index("ix_global_overlay_items_source", table_name="global_overlay_items")
    op.drop_table("global_overlay_items")
    op.drop_index("ix_global_overlay_sources_type", table_name="global_overlay_sources")
    op.drop_table("global_overlay_sources")

    op.execute(
        "DELETE FROM job_logs WHERE job_id IN "
        "(SELECT id FROM jobs WHERE user_id IS NULL)"
    )
    op.execute("DELETE FROM jobs WHERE user_id IS NULL")

    with op.batch_alter_table("jobs") as batch_op:
        batch_op.alter_column("user_id", existing_type=sa.Integer(), nullable=False)