"""Replace asset_software_catalog.is_ai_tool with category column.

Why: ``is_ai_tool`` is a single-purpose boolean — useful for the AI
spending dashboard but useless for the broader software catalog UX.
Admins want to bucket Slack/Lark/Zoom as ``im``, Gmail/Outlook as
``mail``, AWS/GCP as ``cloud``, etc. — not just "AI vs everything
else". Category tags also drive a future "filter by category" lens
on the catalog page.

Migration is destructive (drops the boolean) but lossless: every row
that had ``is_ai_tool=TRUE`` lands as ``category='ai'``, every other
row defaults to ``other``. Downgrade re-creates the boolean and
restores the AI flag.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "082"
down_revision = "081"
branch_labels = None
depends_on = None


_TABLE = "asset_software_catalog"
_NEW_COL = "category"
_OLD_COL = "is_ai_tool"


def upgrade() -> None:
    bind = op.get_bind()

    # 1. Add the new column (NOT NULL with default 'other' so existing
    #    rows backfill without a UNIQUE / FK race).
    op.add_column(
        _TABLE,
        sa.Column(
            _NEW_COL,
            sa.String(length=32),
            nullable=False,
            server_default="other",
        ),
    )

    # 2. Backfill: every previously-flagged AI tool keeps its semantics
    #    under the new column.
    bind.execute(
        sa.text(
            f"UPDATE {_TABLE} SET {_NEW_COL} = 'ai' "
            f"WHERE {_OLD_COL} = TRUE"
        )
    )

    # 3. Drop the legacy boolean now that the dedup queries
    #    (ai_spending/service.py) and the admin UI both read category.
    op.drop_column(_TABLE, _OLD_COL)


def downgrade() -> None:
    bind = op.get_bind()
    op.add_column(
        _TABLE,
        sa.Column(
            _OLD_COL,
            sa.Boolean,
            nullable=False,
            server_default=sa.text("0"),
        ),
    )
    bind.execute(
        sa.text(
            f"UPDATE {_TABLE} SET {_OLD_COL} = TRUE "
            f"WHERE {_NEW_COL} = 'ai'"
        )
    )
    op.drop_column(_TABLE, _NEW_COL)