"""Unify employee self-service requests under one table.

Step 1 of the asset/api_key request unification (see roadmap): rename
``asset_employee_requests`` → ``employee_requests`` and add a back-link
FK on ``api_key_assignments.employee_request_id`` so both request
families point at the same canonical row. Data backfill and
ApiKeyService dual-write come in a follow-up step; this migration
just lays the schema.

Revision ID: 030
Revises: 029
Create Date: 2026-04-19

"""
from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op

revision: str = "030"
down_revision: str | Sequence[str] | None = "029"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    # 1) Rename the table. MySQL/TiDB handle this as a metadata op; no data
    #    copy. All FK constraints pointing at asset_employee_requests update
    #    automatically in InnoDB; if any external tool hardcodes the old name
    #    it will need to be updated (we already updated all Helix callers).
    op.rename_table("asset_employee_requests", "employee_requests")

    # 2) Add the back-link from api_key_assignments → employee_requests. A
    #    1:1 relationship: each api_key_assignment will have an associated
    #    employee_request once ApiKeyService is updated to dual-write. Keep
    #    nullable for now so existing rows stay valid; we'll backfill in the
    #    follow-up migration.
    op.add_column(
        "api_key_assignments",
        sa.Column("employee_request_id", sa.CHAR(36), nullable=True),
    )
    op.create_foreign_key(
        "fk_api_key_assignments_employee_request",
        "api_key_assignments",
        "employee_requests",
        ["employee_request_id"],
        ["id"],
        ondelete="SET NULL",
    )
    op.create_index(
        "ix_api_key_assignments_employee_request_id",
        "api_key_assignments",
        ["employee_request_id"],
    )


def downgrade() -> None:
    op.drop_index(
        "ix_api_key_assignments_employee_request_id",
        table_name="api_key_assignments",
    )
    op.drop_constraint(
        "fk_api_key_assignments_employee_request",
        "api_key_assignments",
        type_="foreignkey",
    )
    op.drop_column("api_key_assignments", "employee_request_id")
    op.rename_table("employee_requests", "asset_employee_requests")