"""add_tag_ref_and_case_tags

Revision ID: fbc309cb247f
Revises: 4410b3fd2e7e
Create Date: 2025-08-04 14:47:54.296745

"""

from collections.abc import Sequence

import sqlalchemy as sa
from slugify import slugify
from sqlalchemy.dialects import postgresql

from alembic import op

# revision identifiers, used by Alembic.
revision: str = "fbc309cb247f"
down_revision: str | None = "4410b3fd2e7e"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    # Create case_tag table
    op.create_table(
        "casetag",
        sa.Column("case_id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("tag_id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.ForeignKeyConstraint(
            ["case_id"],
            ["cases.id"],
            ondelete="CASCADE",
            name="casetag_case_id_fkey",
        ),
        sa.ForeignKeyConstraint(
            ["tag_id"],
            ["tag.id"],
            ondelete="CASCADE",
            name="casetag_tag_id_fkey",
        ),
        sa.PrimaryKeyConstraint("case_id", "tag_id", name="casetag_pkey"),
    )

    # Add ref column to tag table
    op.add_column("tag", sa.Column("ref", sa.String(), nullable=True))
    op.create_index(op.f("ix_tag_ref"), "tag", ["ref"], unique=False)

    # Populate ref values for existing tags
    connection = op.get_bind()
    result = connection.execute(sa.text("SELECT id, name FROM tag"))
    tags = result.fetchall()

    for tag_id, name in tags:
        ref = slugify(name)
        connection.execute(
            sa.text("UPDATE tag SET ref = :ref WHERE id = :id"),
            {"ref": ref, "id": tag_id},
        )

    # Make ref non-nullable after populating
    op.alter_column("tag", "ref", nullable=False)

    # Create unique constraint on ref + owner_id
    op.create_unique_constraint("uq_tag_ref_owner", "tag", ["ref", "owner_id"])
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    # Drop ref column and related constraints
    op.drop_constraint("uq_tag_ref_owner", "tag", type_="unique")
    op.drop_index(op.f("ix_tag_ref"), table_name="tag")
    op.drop_column("tag", "ref")

    # Drop case_tag table
    op.drop_table("casetag")
    # ### end Alembic commands ###
