"""Add local_department.region — authoritative 清远市/县区 grouping.

Revision ID: 0004_add_local_dept_region
Revises: 0003_add_managed_by
Create Date: 2026-04-24

The admin UI groups the OA-sourced local_department rows by 一市八县区 so
that operators can pick a dept without scrolling through 300+ options. The
first iteration hardcoded the mapping in a JS static file, which meant any
data tweak required redeploying the frontend. This migration moves that
grouping into the DB so the operator can edit it via the admin CRUD API.

`region` is nullable to let future OA sync backfill depts we haven't seen
yet; a companion one-shot script (scripts/backfill_dept_region.py) seeds
the initial values from `清远市及县区政府组成部门和乡镇/*.xlsx`.
"""
from alembic import op
import sqlalchemy as sa

revision = "0004_add_local_dept_region"
down_revision = "0003_add_managed_by"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.add_column(
        "local_department",
        sa.Column("region", sa.String(length=50), nullable=True),
    )
    op.create_index(
        "ix_local_department_region",
        "local_department",
        ["region"],
    )


def downgrade() -> None:
    op.drop_index("ix_local_department_region", table_name="local_department")
    op.drop_column("local_department", "region")