"""Make office-to-asset-location routing explicit.

Revision ID: 525
Revises: 524
"""

import sqlalchemy as sa
from alembic import op

revision = "525"
down_revision = "524"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.add_column(
        "asset_office_addresses",
        sa.Column("asset_location", sa.String(length=128), nullable=True),
    )
    op.create_unique_constraint(
        "uq_asset_office_addresses_asset_location",
        "asset_office_addresses", ["asset_location"],
    )
    # Prefer city (China offices), then country (single-office countries).
    # Only active managed locations are candidates, so the mapping uses the
    # same vocabulary as asset_items.location.
    bind = op.get_bind()
    bind.execute(sa.text("""
        UPDATE asset_office_addresses o
        JOIN asset_locations l ON l.name = o.city AND l.is_active = 1
        SET o.asset_location = l.name
        WHERE o.asset_location IS NULL
    """))
    bind.execute(sa.text("""
        UPDATE asset_office_addresses o
        JOIN asset_locations l ON l.name = o.country_or_region AND l.is_active = 1
        SET o.asset_location = l.name
        WHERE o.asset_location IS NULL
    """))


def downgrade() -> None:
    op.drop_constraint(
        "uq_asset_office_addresses_asset_location",
        "asset_office_addresses", type_="unique",
    )
    op.drop_column("asset_office_addresses", "asset_location")