"""Merge legacy Lab Technician accounts into Phlebotomist.

Revision ID: 20261003_merge_staff_roles
Revises: 20260930_data_integrity
Create Date: 2026-10-03

The FE had presented `general_user` as Lab Technician even though its grants
were a restricted generic role. Phlebotomist already owns specimen and result
entry; this migration adds report submission, moves existing accounts while
preserving per-user overrides, and removes the obsolete role catalog rows.
"""

from collections.abc import Sequence

from alembic import op

revision: str = "20261003_merge_staff_roles"
down_revision: str | None = "20260930_data_integrity"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    # Keep enough information to downgrade without changing accounts created as
    # Phlebotomists after this migration.
    op.execute(
        "CREATE TABLE _role_merge_general_user_backup AS SELECT id FROM \"user\" WHERE role = 'general_user'"
    )
    op.execute(
        "CREATE TABLE _role_merge_role_backup AS "
        "SELECT id, code, name, description, is_system, created_at, updated_at "
        "FROM role WHERE code = 'general_user'"
    )
    op.execute(
        "CREATE TABLE _role_merge_permissions_backup AS "
        "SELECT id, role, permission_id, default_granted, created_at, updated_at "
        "FROM role_permission WHERE role = 'general_user'"
    )
    op.execute(
        "CREATE TABLE _role_merge_phlebotomist_report_write_backup AS "
        "SELECT rp.id, rp.role, rp.permission_id, rp.default_granted, rp.created_at, rp.updated_at "
        "FROM role_permission rp JOIN permission p ON p.id = rp.permission_id "
        "WHERE rp.role = 'phlebotomist' AND p.code = 'report:write'"
    )

    # Fold current Lab Technician accounts into the retained clinical staff
    # role. User-specific permission overrides remain keyed to the same IDs.
    op.execute("UPDATE \"user\" SET role = 'phlebotomist' WHERE role = 'general_user'")

    # Ensure existing databases grant the submission step, while preserving a
    # missing row for downgrade if the permission catalog was not seeded yet.
    op.execute(
        "UPDATE role_permission SET default_granted = TRUE, updated_at = NOW() "
        "WHERE role = 'phlebotomist' AND permission_id = "
        "(SELECT id FROM permission WHERE code = 'report:write')"
    )
    op.execute(
        "INSERT INTO role_permission (id, role, permission_id, default_granted, created_at, updated_at) "
        "SELECT gen_random_uuid(), 'phlebotomist', p.id, TRUE, NOW(), NOW() FROM permission p "
        "WHERE p.code = 'report:write' AND NOT EXISTS ("
        "SELECT 1 FROM role_permission rp WHERE rp.role = 'phlebotomist' AND rp.permission_id = p.id)"
    )

    op.execute("DELETE FROM role_permission WHERE role = 'general_user'")
    op.execute("DELETE FROM role WHERE code = 'general_user'")


def downgrade() -> None:
    op.execute(
        "INSERT INTO role (id, code, name, description, is_system, created_at, updated_at) "
        "SELECT id, code, name, description, is_system, created_at, updated_at "
        "FROM _role_merge_role_backup ON CONFLICT (code) DO NOTHING"
    )
    op.execute(
        "INSERT INTO role_permission (id, role, permission_id, default_granted, created_at, updated_at) "
        "SELECT id, role, permission_id, default_granted, created_at, updated_at "
        "FROM _role_merge_permissions_backup ON CONFLICT (id) DO NOTHING"
    )
    op.execute(
        "UPDATE \"user\" SET role = 'general_user' WHERE id IN "
        "(SELECT id FROM _role_merge_general_user_backup)"
    )

    # Restore the original Phlebotomist report-write state (or remove the row
    # if it did not exist before the upgrade).
    op.execute(
        "DELETE FROM role_permission WHERE role = 'phlebotomist' AND permission_id IN "
        "(SELECT id FROM permission WHERE code = 'report:write')"
    )
    op.execute(
        "INSERT INTO role_permission (id, role, permission_id, default_granted, created_at, updated_at) "
        "SELECT id, role, permission_id, default_granted, created_at, updated_at "
        "FROM _role_merge_phlebotomist_report_write_backup ON CONFLICT (id) DO NOTHING"
    )
    op.execute("DROP TABLE _role_merge_phlebotomist_report_write_backup")
    op.execute("DROP TABLE _role_merge_permissions_backup")
    op.execute("DROP TABLE _role_merge_role_backup")
    op.execute("DROP TABLE _role_merge_general_user_backup")