"""phase49 — follow-up request attribution (customer-raised follow-ups)

Ports single-tenant phase46 onto the multi-tenant chain. Adds to `inspections`:

    follow_up_requested_by  INT NULL  FK → users(id) ON DELETE SET NULL
    follow_up_requested_at  DATETIME NULL

Customers can now request a follow-up re-inspection of a completed inspection at
their own facilities (previously admin/director only), so `follow_up_required`
alone is no longer enough — staff need to see WHO is waiting on the
re-inspection, and a client request must be visibly distinct from an internal
one. `flag_followup()` sets both columns; `clear_followup()` nulls them.

No backfill: legacy rows keep NULL, which the UI renders as an unattributed
follow-up exactly as it did before. FK is SET NULL so deleting a user never
deletes inspection history.

Revision id note
----------------
`alembic_version.version_num` is VARCHAR(32); the id below is 23 characters.
The filename stays descriptive — Alembic keys on the `revision` string.

Uses INFORMATION_SCHEMA checks — safe to re-run on every tenant DB. Additive
only: nothing is renamed, retyped or dropped.
"""

revision      = 'phase49_followup_req_by'
down_revision = 'phase48_sched_parent_insp'
branch_labels = None
depends_on    = None

from alembic import op
import sqlalchemy as sa


_TABLE   = 'inspections'
_FK_NAME = 'fk_inspections_follow_up_requested_by'


def _table_exists(conn, table):
    return conn.execute(sa.text(
        "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES "
        "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t"
    ), {"t": table}).scalar() > 0


def _column_exists(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 _fk_exists(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 AND CONSTRAINT_TYPE = 'FOREIGN KEY'"
    ), {"t": table, "n": name}).scalar() > 0


def upgrade():
    bind = op.get_bind()
    if not _table_exists(bind, _TABLE):
        return

    if not _column_exists(bind, _TABLE, 'follow_up_requested_by'):
        op.execute(sa.text(
            f"ALTER TABLE {_TABLE} "
            f"ADD COLUMN follow_up_requested_by INT NULL AFTER follow_up_note"
        ))
    if not _column_exists(bind, _TABLE, 'follow_up_requested_at'):
        op.execute(sa.text(
            f"ALTER TABLE {_TABLE} "
            f"ADD COLUMN follow_up_requested_at DATETIME NULL "
            f"AFTER follow_up_requested_by"
        ))
    if not _fk_exists(bind, _TABLE, _FK_NAME):
        op.execute(sa.text(
            f"ALTER TABLE {_TABLE} ADD CONSTRAINT {_FK_NAME} "
            f"FOREIGN KEY (follow_up_requested_by) REFERENCES users(id) "
            f"ON DELETE SET NULL"
        ))


def downgrade():
    bind = op.get_bind()
    if not _table_exists(bind, _TABLE):
        return

    # FK first — MySQL refuses to drop a column still referenced by one.
    if _fk_exists(bind, _TABLE, _FK_NAME):
        op.execute(sa.text(f"ALTER TABLE {_TABLE} DROP FOREIGN KEY {_FK_NAME}"))
    for col in ('follow_up_requested_at', 'follow_up_requested_by'):
        if _column_exists(bind, _TABLE, col):
            op.execute(sa.text(f"ALTER TABLE {_TABLE} DROP COLUMN {col}"))