"""Add tags and asset_tags tables

Revision ID: 030
Revises: 029
Create Date: 2026-07-07

新增标签表和素材-标签关联表，支持规范化多对多标签管理。
"""

import sqlalchemy as sa

from alembic import op

revision = "030"
down_revision = "029"
branch_labels = None
depends_on = None


def _table_exists(table: str) -> bool:
    ctx = op.get_context()
    if ctx.as_sql:
        return False
    conn = op.get_bind()
    result = conn.execute(
        sa.text("SELECT COUNT(*) FROM information_schema.tables WHERE table_name = :table"),
        {"table": table},
    )
    return (result.scalar() or 0) > 0


def upgrade() -> None:
    if not _table_exists("tags"):
        op.create_table(
            "tags",
            sa.Column("id", sa.String(36), primary_key=True),
            sa.Column("user_id", sa.String(36), nullable=False),
            sa.Column("name", sa.String(100), nullable=False),
            sa.Column(
                "created_at",
                sa.DateTime(),
                nullable=False,
                server_default=sa.func.now(),
            ),
            sa.UniqueConstraint("user_id", "name", name="uq_tags_user_name"),
        )
        op.create_index("ix_tags_user_id", "tags", ["user_id"])

    if not _table_exists("asset_tags"):
        op.create_table(
            "asset_tags",
            sa.Column("asset_id", sa.String(36), primary_key=True),
            sa.Column("tag_id", sa.String(36), primary_key=True),
            sa.Column(
                "created_at",
                sa.DateTime(),
                nullable=False,
                server_default=sa.func.now(),
            ),
        )
        op.create_index("ix_asset_tags_tag_id", "asset_tags", ["tag_id"])


def downgrade() -> None:
    op.drop_index("ix_asset_tags_tag_id", table_name="asset_tags")
    op.drop_table("asset_tags")
    op.drop_index("ix_tags_user_id", table_name="tags")
    op.drop_table("tags")
