"""Drop the onboarding ``buddy`` concept (2026-06-15).

The manager-set onboarding "buddy" was an advisory-only field: it never
gated readiness, never drove SLA, never triggered a provisioning task,
and never notified the buddy. HRIS already reminds managers of joins /
leaves; Helix's onboarding scope is "accounts created on time + assets
delivered on time", both IT-driven. The buddy field was over-design, so
it is removed end-to-end (model / service / routers / MCP / frontend).

This migration:
  1. Deletes the leftover ``buddy_assigned`` advisory blocker rows from
     ``onboarding_blockers`` (the scanner no longer creates them).
  2. Drops the FK ``fk_onboarding_processes_buddy`` and the column
     ``onboarding_processes.default_buddy_person_id``.

Both steps are guarded against re-run (INFORMATION_SCHEMA existence
checks) so the migration converges whether or not a prior partial run
touched the schema. Downgrade re-creates the (empty) column + FK — the
deleted blocker rows and buddy assignments are not recoverable, which is
fine: they carried no operational state.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op
from app.core.migration_helpers import id_type_for

revision = "284"
down_revision = "283"
branch_labels = None
depends_on = None

_TABLE = "onboarding_processes"
_COL = "default_buddy_person_id"
_FK = "fk_onboarding_processes_buddy"


def _fk_exists(bind) -> bool:
    return bool(
        bind.execute(
            sa.text(
                """
                SELECT 1
                FROM information_schema.TABLE_CONSTRAINTS
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = :t
                  AND CONSTRAINT_NAME = :c
                  AND CONSTRAINT_TYPE = 'FOREIGN KEY'
                LIMIT 1
                """
            ),
            {"t": _TABLE, "c": _FK},
        ).scalar()
    )


def _col_exists(bind) -> bool:
    return bool(
        bind.execute(
            sa.text(
                """
                SELECT 1
                FROM information_schema.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = :t
                  AND COLUMN_NAME = :c
                LIMIT 1
                """
            ),
            {"t": _TABLE, "c": _COL},
        ).scalar()
    )


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

    # 1. Purge the advisory blocker rows the scanner no longer emits.
    op.execute(
        sa.text(
            "DELETE FROM onboarding_blockers "
            "WHERE resource_type = 'buddy_assigned'"
        )
    )

    # 2. Drop the FK then the column (MySQL/TiDB require the FK gone
    #    before the column can be dropped). Both guarded for re-run.
    if _fk_exists(bind):
        op.drop_constraint(_FK, _TABLE, type_="foreignkey")
    if _col_exists(bind):
        op.drop_column(_TABLE, _COL)


def downgrade() -> None:
    bind = op.get_bind()
    persons_id = id_type_for("identity_persons")

    if not _col_exists(bind):
        op.add_column(
            _TABLE,
            sa.Column(_COL, persons_id, nullable=True),
        )
    if not _fk_exists(bind):
        op.create_foreign_key(
            _FK,
            _TABLE,
            "identity_persons",
            [_COL],
            ["id"],
            ondelete="SET NULL",
        )