"""Align database documentation with model column comments.

Existing defaults, indexes, foreign keys, unique constraints and business records
are preserved. Staff-role rollback snapshots remain owned by their migration.
"""

from collections.abc import Sequence

from alembic import op

revision: str = "20261012_column_comments"
down_revision: str | None = "20261012_expiring_state"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    op.alter_column("audit_log", "id", comment="Primary key — UUID v4 generated by PostgreSQL")
    op.alter_column(
        "bill", "bill_number", comment="Human-readable invoice sequence: INV{YY}{6-digit-seq} e.g. INV260001"
    )
    op.alter_column("lab_holiday", "start_date", comment="First day of closure/holiday")
    op.alter_column("lab_holiday", "end_date", comment="Last day of closure/holiday (inclusive)")
    op.alter_column(
        "lab_holiday", "title", comment="Reason or name of holiday (e.g. Dashain Festival, Public Holiday)"
    )
    op.alter_column("lab_holiday", "notes", comment="Optional internal notes or instructions")
    op.alter_column("lab_holiday", "is_closed", comment="True if lab is completely closed for collection")
    op.alter_column("lab_holiday", "id", comment="Primary key — UUID v4 generated by PostgreSQL")
    op.alter_column("lab_holiday", "created_at", comment="Record creation timestamp (UTC)")
    op.alter_column("lab_holiday", "updated_at", comment="Record last-update timestamp (UTC) — ORM-managed")
    op.alter_column(
        "lab_holiday", "deleted_at", comment="Soft-delete timestamp. NULL = active. Non-NULL = deleted."
    )
    op.alter_column("lab_schedule_config", "open_time", comment="Daily opening time HH:MM format")
    op.alter_column("lab_schedule_config", "close_time", comment="Daily closing time HH:MM format")
    op.alter_column(
        "lab_schedule_config",
        "slot_duration_minutes",
        comment="Duration of each appointment booking window in minutes",
    )
    op.alter_column(
        "lab_schedule_config", "slot_capacity", comment="Maximum patients bookable per time window"
    )
    op.alter_column(
        "lab_schedule_config",
        "operating_days",
        comment="Comma-separated active days of week (0=Mon, 1=Tue, ..., 6=Sun)",
    )
    op.alter_column(
        "lab_schedule_config",
        "is_active",
        comment="Whether online/quick booking is actively open for this lab",
    )
    op.alter_column("lab_schedule_config", "id", comment="Primary key — UUID v4 generated by PostgreSQL")
    op.alter_column("lab_schedule_config", "created_at", comment="Record creation timestamp (UTC)")
    op.alter_column(
        "lab_schedule_config", "updated_at", comment="Record last-update timestamp (UTC) — ORM-managed"
    )
    op.alter_column(
        "order_test",
        "selected_parameter_ids",
        comment="Optional analyte scope: parameter UUID strings ordered for this test. NULL = whole panel.",
    )
    op.alter_column(
        "patient",
        "pin_hash",
        comment="bcrypt hash of the 6-character alphanumeric PIN. OVERWRITTEN on every visit (order creation). Client portal login: phone + this PIN → time-limited JWT.",
    )
    op.alter_column(
        "patient",
        "portal_pin_enc",
        comment="Fernet-encrypted copy of the current PIN for display on bill receipts and signed reports. Rotated together with pin_hash.",
    )
    op.alter_column("permission", "is_active", comment="Whether this capability/feature is globally active")
    op.alter_column(
        "radiologist_commission",
        "radiologist_id",
        comment="FK → user.id. The radiologist who verified the imaging report.",
    )
    op.alter_column(
        "radiologist_commission",
        "order_id",
        comment="FK → visit_order.id. The billed visit generating this commission.",
    )
    op.alter_column(
        "radiologist_commission",
        "report_id",
        comment="FK → report.id. One commission entry per released report.",
    )
    op.alter_column(
        "radiologist_commission",
        "order_amount",
        comment="Base bill/order amount eligible for commission calculation",
    )
    op.alter_column(
        "radiologist_commission",
        "commission_rate",
        comment="Commission percentage rate snapshot from the radiologist profile",
    )
    op.alter_column(
        "radiologist_commission", "commission_amount", comment="Calculated commission payout amount"
    )
    op.alter_column(
        "radiologist_commission",
        "is_settled",
        comment="Whether this commission has been disbursed to the radiologist",
    )
    op.alter_column(
        "radiologist_commission", "settled_at", comment="Timestamp when commission payout was disbursed"
    )
    op.alter_column(
        "radiologist_commission", "notes", comment="Settlement notes or payout transaction reference"
    )
    op.alter_column("radiologist_commission", "id", comment="Primary key — UUID v4 generated by PostgreSQL")
    op.alter_column("radiologist_commission", "created_at", comment="Record creation timestamp (UTC)")
    op.alter_column(
        "radiologist_commission", "updated_at", comment="Record last-update timestamp (UTC) — ORM-managed"
    )
    op.alter_column(
        "radiologist_commission", "lab_id", comment="Branch/unit this record belongs to (FK → lab.id)"
    )
    op.alter_column(
        "report",
        "modality",
        comment="Discipline side: pathology (specimen lab) or imaging (radiology). One report per side.",
    )
    op.alter_column(
        "report", "version", comment="Incremental version number for this order-side's reports (1, 2, ...)"
    )
    op.alter_column(
        "report",
        "public_token",
        comment="Durable no-login capability token for the printed QR code (set at release)",
    )
    op.alter_column("role", "code", comment="Unique role code identifier (e.g. 'doctor', 'receptionist')")
    op.alter_column("role", "name", comment="Human-readable role name (e.g. 'Consultant Doctor')")
    op.alter_column("role", "description", comment="Description of the role and responsibilities")
    op.alter_column("role", "is_system", comment="True if built-in system role that cannot be deleted")
    op.alter_column("role", "id", comment="Primary key — UUID v4 generated by PostgreSQL")
    op.alter_column("role", "created_at", comment="Record creation timestamp (UTC)")
    op.alter_column("role", "updated_at", comment="Record last-update timestamp (UTC) — ORM-managed")
    op.alter_column("role_permission", "role", comment="The staff role code this assignment applies to")
    op.alter_column(
        "test_category",
        "modality",
        comment="Work queue routing: pathology (specimen lab) or imaging (radiology narrative)",
    )
    op.alter_column(
        "test_package_item",
        "parameter_id",
        comment="Optional FK → test_parameter.id if bundling individual analyte",
    )
    op.alter_column("unit_of_measure", "name", comment="Unit symbol as shown on reports (e.g. 'mg/dL')")
    op.alter_column(
        "unit_of_measure",
        "description",
        comment="Optional human description (e.g. 'milligrams per deciliter')",
    )
    op.alter_column(
        "unit_of_measure",
        "is_system",
        comment="True = seeded standard unit; cannot be deleted (only deactivated)",
    )
    op.alter_column(
        "unit_of_measure", "is_active", comment="False = hidden from the unit picker for new analytes"
    )
    op.alter_column("unit_of_measure", "id", comment="Primary key — UUID v4 generated by PostgreSQL")
    op.alter_column("unit_of_measure", "created_at", comment="Record creation timestamp (UTC)")
    op.alter_column(
        "unit_of_measure", "updated_at", comment="Record last-update timestamp (UTC) — ORM-managed"
    )
    op.alter_column(
        "user",
        "signature_image_path",
        comment="Relative path of the transparent staff signature PNG under storage/signatures/{user_id}/",
    )
    op.alter_column(
        "user",
        "commission_rate",
        comment="Per-report commission % for radiologists (null = no commission). Set by superadmin.",
    )
    op.alter_column(
        "user",
        "license_no",
        comment="Professional council registration number (e.g. NMC No.). Mandatory for doctor/pathologist/radiologist; printed under the report signature.",
    )
    op.alter_column(
        "visit_order", "order_number", comment="Human-readable order ID: ORD{YY}{6-digit-seq} e.g. ORD260001"
    )


def downgrade() -> None:
    op.alter_column(
        "visit_order",
        "order_number",
        comment="Human-readable order ID: ORD/{YY}/{6-digit-seq} e.g. ORD/26/000001",
    )
    op.alter_column("user", "license_no", comment=None)
    op.alter_column("user", "commission_rate", comment=None)
    op.alter_column("user", "signature_image_path", comment=None)
    op.alter_column("unit_of_measure", "updated_at", comment=None)
    op.alter_column("unit_of_measure", "created_at", comment=None)
    op.alter_column("unit_of_measure", "id", comment=None)
    op.alter_column("unit_of_measure", "is_active", comment=None)
    op.alter_column("unit_of_measure", "is_system", comment=None)
    op.alter_column("unit_of_measure", "description", comment=None)
    op.alter_column("unit_of_measure", "name", comment=None)
    op.alter_column(
        "test_package_item",
        "parameter_id",
        comment="Optional FK to test_parameter.id if bundling individual analyte",
    )
    op.alter_column("test_category", "modality", comment=None)
    op.alter_column("role_permission", "role", comment="The staff role this assignment applies to")
    op.alter_column("role", "updated_at", comment=None)
    op.alter_column("role", "created_at", comment=None)
    op.alter_column("role", "id", comment=None)
    op.alter_column("role", "is_system", comment=None)
    op.alter_column("role", "description", comment=None)
    op.alter_column("role", "name", comment=None)
    op.alter_column("role", "code", comment=None)
    op.alter_column("report", "public_token", comment=None)
    op.alter_column(
        "report", "version", comment="Incremental version number for this order's reports (1, 2, ...)"
    )
    op.alter_column("report", "modality", comment=None)
    op.alter_column("radiologist_commission", "lab_id", comment=None)
    op.alter_column("radiologist_commission", "updated_at", comment=None)
    op.alter_column("radiologist_commission", "created_at", comment=None)
    op.alter_column("radiologist_commission", "id", comment=None)
    op.alter_column("radiologist_commission", "notes", comment=None)
    op.alter_column("radiologist_commission", "settled_at", comment=None)
    op.alter_column("radiologist_commission", "is_settled", comment=None)
    op.alter_column("radiologist_commission", "commission_amount", comment=None)
    op.alter_column("radiologist_commission", "commission_rate", comment=None)
    op.alter_column("radiologist_commission", "order_amount", comment=None)
    op.alter_column("radiologist_commission", "report_id", comment=None)
    op.alter_column("radiologist_commission", "order_id", comment=None)
    op.alter_column("radiologist_commission", "radiologist_id", comment=None)
    op.alter_column("permission", "is_active", comment=None)
    op.alter_column("patient", "portal_pin_enc", comment=None)
    op.alter_column(
        "patient",
        "pin_hash",
        comment="bcrypt hash of the 6-digit PIN printed on the bill receipt. OVERWRITTEN each time a new bill is generated. Client portal login: phone + this PIN → Redis session token.",
    )
    op.alter_column("order_test", "selected_parameter_ids", comment=None)
    op.alter_column("lab_schedule_config", "updated_at", comment=None)
    op.alter_column("lab_schedule_config", "created_at", comment=None)
    op.alter_column("lab_schedule_config", "id", comment=None)
    op.alter_column("lab_schedule_config", "is_active", comment=None)
    op.alter_column("lab_schedule_config", "operating_days", comment=None)
    op.alter_column("lab_schedule_config", "slot_capacity", comment=None)
    op.alter_column("lab_schedule_config", "slot_duration_minutes", comment=None)
    op.alter_column("lab_schedule_config", "close_time", comment=None)
    op.alter_column("lab_schedule_config", "open_time", comment=None)
    op.alter_column("lab_holiday", "deleted_at", comment=None)
    op.alter_column("lab_holiday", "updated_at", comment=None)
    op.alter_column("lab_holiday", "created_at", comment=None)
    op.alter_column("lab_holiday", "id", comment=None)
    op.alter_column("lab_holiday", "is_closed", comment=None)
    op.alter_column("lab_holiday", "notes", comment=None)
    op.alter_column("lab_holiday", "title", comment=None)
    op.alter_column("lab_holiday", "end_date", comment=None)
    op.alter_column("lab_holiday", "start_date", comment=None)
    op.alter_column(
        "bill",
        "bill_number",
        comment="Human-readable invoice sequence: INV/{YY}/{6-digit-seq} e.g. INV/26/000001",
    )
    op.alter_column("audit_log", "id", comment=None)