"""add role table and update role columns to varchar

Revision ID: d4e5f6a7b8c9
Revises: c3d4e5f6a7b8
Create Date: 2026-09-26 12:00:00.000000

"""

from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql

revision: str = "d4e5f6a7b8c9"
down_revision: str | None = "c3d4e5f6a7b8"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    op.execute('ALTER TABLE "user" ALTER COLUMN role TYPE VARCHAR(50) USING role::text;')
    op.execute("ALTER TABLE role_permission ALTER COLUMN role TYPE VARCHAR(50) USING role::text;")

    op.create_table(
        "role",
        sa.Column(
            "id", postgresql.UUID(as_uuid=True), primary_key=True, server_default=sa.text("gen_random_uuid()")
        ),
        sa.Column("code", sa.String(length=50), nullable=False, unique=True),
        sa.Column("name", sa.String(length=100), nullable=False),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("is_system", sa.Boolean(), nullable=False, server_default=sa.text("false")),
        sa.Column(
            "created_at", postgresql.TIMESTAMP(timezone=True), nullable=False, server_default=sa.func.now()
        ),
        sa.Column(
            "updated_at", postgresql.TIMESTAMP(timezone=True), nullable=False, server_default=sa.func.now()
        ),
    )
    op.create_index("ix_role_code", "role", ["code"])

    op.execute("""
        INSERT INTO role (code, name, description, is_system) VALUES
        ('superadmin', 'Super Administrator', 'Full platform control, audit trail access, and administrative governance', true),
        ('admin', 'Administrator', 'Full lab operations management, catalog management, and personnel management', true),
        ('manager', 'Lab Manager', 'Operations supervisor, worklist allocation, and quality control', true),
        ('doctor', 'Consultant Doctor', 'Clinical evaluation, report interpretation, and consultation orders', true),
        ('pathologist', 'Pathologist', 'Diagnostic verification, result authorization, and clinical reports', true),
        ('radiologist', 'Radiologist', 'Imaging evaluation and radiology interpretation', true),
        ('receptionist', 'Front Desk Receptionist', 'Patient registration, appointment booking, and sample reception', true),
        ('phlebotomist', 'Phlebotomist', 'Sample collection, tube labelling, and accessioning', true),
        ('accountant', 'Billing & Accountant', 'Invoicing, payment processing, refunds, and financial ledger', true),
        ('general_user', 'Patient / General User', 'Public patient portal account', true)
        ON CONFLICT (code) DO NOTHING;
    """)


def downgrade() -> None:
    op.drop_index("ix_role_code", table_name="role")
    op.drop_table("role")