"""station-to-station hand-over gated by QA; QA section remarks

Revision ID: f2a3b4c5d6e7
Revises: e1f2a3b4c5d6
Create Date: 2026-10-02 15:00:00.000000

chassis_station_handoff: created when an operator marks work complete at a station.
  The next station can check the chassis in only once it is RELEASED (immediately for
  stations without QA checksheets, after QA passes and releases it otherwise).
qa_section_remark: inspector remark per checksheet section.
Safe to re-run.
"""
from typing import Sequence, Union

from alembic import op

revision: str = 'f2a3b4c5d6e7'
down_revision: Union[str, None] = 'e1f2a3b4c5d6'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.execute("""
        CREATE TABLE IF NOT EXISTS chassis_station_handoff (
            handoff_id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            chassis_id      UUID NOT NULL REFERENCES chassis_master(chassis_id) ON DELETE CASCADE,
            from_station_id UUID NOT NULL REFERENCES station_master(station_id),
            to_station_id   UUID REFERENCES station_master(station_id),
            status          VARCHAR(20) NOT NULL CONSTRAINT ck_handoff_status
                            CHECK (status IN ('AWAITING_QA', 'QA_PASSED', 'RELEASED', 'RECEIVED', 'SUPERSEDED')),
            completed_at    TIMESTAMPTZ DEFAULT now(),
            completed_by    UUID REFERENCES login_users(user_id),
            released_at     TIMESTAMPTZ,
            released_by     UUID REFERENCES login_users(user_id),
            received_at     TIMESTAMPTZ,
            remarks         TEXT
        )
    """)
    op.execute("CREATE INDEX IF NOT EXISTS ix_chassis_station_handoff_chassis_id ON chassis_station_handoff (chassis_id)")
    op.execute("CREATE INDEX IF NOT EXISTS ix_chassis_station_handoff_to_station ON chassis_station_handoff (to_station_id, status)")
    op.execute("CREATE INDEX IF NOT EXISTS ix_chassis_station_handoff_from_station ON chassis_station_handoff (from_station_id, status)")

    op.execute("""
        CREATE TABLE IF NOT EXISTS qa_section_remark (
            remark_id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            instance_id UUID NOT NULL REFERENCES qa_checksheet_instance(instance_id) ON DELETE CASCADE,
            section_id  UUID NOT NULL REFERENCES qa_checksheet_section(section_id),
            remark      TEXT NOT NULL,
            updated_by  UUID REFERENCES login_users(user_id),
            updated_at  TIMESTAMPTZ DEFAULT now(),
            CONSTRAINT uq_qa_section_remark UNIQUE (instance_id, section_id)
        )
    """)
    op.execute("CREATE INDEX IF NOT EXISTS ix_qa_section_remark_instance_id ON qa_section_remark (instance_id)")


def downgrade() -> None:
    op.execute("DROP TABLE IF EXISTS qa_section_remark")
    op.execute("DROP TABLE IF EXISTS chassis_station_handoff")