"""phase56 — assign a follow-up re-inspection to a specific inspector

Adds `inspections.follow_up_assigned_to` (FK -> users.id, ON DELETE SET NULL).

Until now a follow-up implicitly belonged to whoever performed the original
inspection: they were the one notified, and the mobile API only ever showed
follow-ups where `inspector_id == the caller`. A director could not hand the
re-inspection to somebody else.

NULL means exactly what it meant before — the follow-up belongs to the
inspection's own inspector — so every existing row keeps its current behaviour
and no backfill is needed. `Inspection.follow_up_owner` is the one place that
resolves assignee-or-inspector.

**This is the THIRD FK from inspections to users** (inspector_id,
follow_up_requested_by, and now this). Rule 86: any relationship between the two
tables must pin `foreign_keys` explicitly or the mapper is ambiguous — and it
raises on first ORM *use*, not at import, so the app starts fine and then every
request 500s. `Inspection.follow_up_assignee` pins it; `User.inspections` was
already pinned in phase46.

MT note: ST calls this phase53; MT's chain was already past that number, so
the revision id differs. Match by NAME, not number, when comparing the trees.

INFORMATION_SCHEMA checks — safe to re-run.
"""

revision      = 'phase56_followup_assignee'
down_revision = 'phase55_template_contracts'
branch_labels = None
depends_on    = None

from alembic import op
import sqlalchemy as sa


def _has_column(conn, table, column):
    return conn.execute(sa.text(
        "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS "
        "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t AND COLUMN_NAME = :c"
    ), {'t': table, 'c': column}).scalar() > 0


def _has_constraint(conn, table, name):
    return conn.execute(sa.text(
        "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS "
        "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t AND CONSTRAINT_NAME = :n"
    ), {'t': table, 'n': name}).scalar() > 0


def upgrade():
    conn = op.get_bind()
    if not _has_column(conn, 'inspections', 'follow_up_assigned_to'):
        op.add_column('inspections',
                      sa.Column('follow_up_assigned_to', sa.Integer, nullable=True))
    if not _has_constraint(conn, 'inspections', 'fk_inspections_followup_assignee'):
        op.create_foreign_key(
            'fk_inspections_followup_assignee', 'inspections', 'users',
            ['follow_up_assigned_to'], ['id'], ondelete='SET NULL',
        )


def downgrade():
    conn = op.get_bind()
    if _has_constraint(conn, 'inspections', 'fk_inspections_followup_assignee'):
        op.drop_constraint('fk_inspections_followup_assignee', 'inspections',
                           type_='foreignkey')
    if _has_column(conn, 'inspections', 'follow_up_assigned_to'):
        # Assignments are discarded; every follow-up reverts to belonging to the
        # inspection's own inspector, which is where it started.
        op.drop_column('inspections', 'follow_up_assigned_to')