"""initial_schema Revision ID: 6d38ab84d20f Revises: Create Date: 2026-09-20 04:46:59.247048+00:00 """ from collections.abc import Sequence import sqlalchemy as sa from alembic import op from sqlalchemy.dialects import postgresql # revision identifiers, used by Alembic. revision: str = "6d38ab84d20f" down_revision: str | None = None branch_labels: str | Sequence[str] | None = None depends_on: str | Sequence[str] | None = None def upgrade() -> None: # ### commands auto generated by Alembic - please adjust! ### op.create_table( "lab", sa.Column( "name", sa.String(length=200), nullable=False, comment="Lab or branch display name (appears on reports)", ), sa.Column("address", sa.Text(), nullable=True, comment="Physical address of this branch"), sa.Column( "logo_url", sa.String(length=500), nullable=True, comment="Storage path or URL for the lab logo (used in PDF reports)", ), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record creation timestamp (UTC)", ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record last-update timestamp (UTC) — ORM-managed", ), sa.Column( "deleted_at", sa.DateTime(timezone=True), nullable=True, comment="Soft-delete timestamp. NULL = active. Non-NULL = deleted.", ), sa.PrimaryKeyConstraint("id", name=op.f("pk_lab")), ) op.create_table( "permission", sa.Column( "code", sa.String(length=100), nullable=False, comment="Machine-readable permission key (e.g. 'manage_catalog')", ), sa.Column( "description", sa.String(length=500), nullable=False, comment="Human-readable description shown in the permission management UI", ), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record creation timestamp (UTC)", ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record last-update timestamp (UTC) — ORM-managed", ), sa.PrimaryKeyConstraint("id", name=op.f("pk_permission")), ) op.create_index(op.f("ix_permission_code"), "permission", ["code"], unique=True) op.create_table( "appointment_slot", sa.Column( "start_at", postgresql.TIMESTAMP(timezone=True), nullable=False, comment="Slot start time (TIMESTAMPTZ) — index supports date-range queries", ), sa.Column( "end_at", postgresql.TIMESTAMP(timezone=True), nullable=False, comment="Slot end time (TIMESTAMPTZ)", ), sa.Column( "capacity", sa.Integer(), server_default="1", nullable=False, comment="Maximum number of appointments allowed in this slot", ), sa.Column( "booked_count", sa.Integer(), server_default="0", nullable=False, comment="Denormalized booking count. Incremented/decremented by the service layer inside a transaction with SELECT FOR UPDATE on this row.", ), sa.Column( "is_active", sa.Boolean(), server_default="true", nullable=False, comment="False = slot is closed to new bookings (e.g., holiday, maintenance)", ), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record creation timestamp (UTC)", ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record last-update timestamp (UTC) — ORM-managed", ), sa.Column( "deleted_at", sa.DateTime(timezone=True), nullable=True, comment="Soft-delete timestamp. NULL = active. Non-NULL = deleted.", ), sa.Column( "lab_id", sa.UUID(), nullable=False, comment="Branch/unit this record belongs to (FK → lab.id)" ), sa.ForeignKeyConstraint( ["lab_id"], ["lab.id"], name=op.f("fk_appointment_slot_lab_id_lab"), ondelete="RESTRICT" ), sa.PrimaryKeyConstraint("id", name=op.f("pk_appointment_slot")), ) op.create_index(op.f("ix_appointment_slot_lab_id"), "appointment_slot", ["lab_id"], unique=False) op.create_index(op.f("ix_appointment_slot_start_at"), "appointment_slot", ["start_at"], unique=False) op.create_table( "discount_rule", sa.Column( "name", sa.String(length=100), nullable=False, comment="Human-readable rule name (e.g. 'Senior Citizen Discount')", ), sa.Column( "code", sa.String(length=30), nullable=True, comment="Optional short discount code (e.g. 'SR10')" ), sa.Column( "discount_type", sa.Enum("percentage", "fixed", name="discount_type", create_constraint=True), server_default="percentage", nullable=False, comment="Calculation method: percentage or fixed amount", ), sa.Column( "discount_value", sa.Numeric(precision=10, scale=2), nullable=False, comment="Value: percentage rate (e.g. 10.00) or fixed currency amount (e.g. 500.00)", ), sa.Column( "is_active", sa.Boolean(), server_default=sa.text("true"), nullable=False, comment="Flag indicating if this discount rule is currently selectable at billing", ), sa.Column( "description", sa.Text(), nullable=True, comment="Internal notes or eligibility criteria for the discount", ), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record creation timestamp (UTC)", ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record last-update timestamp (UTC) — ORM-managed", ), sa.Column( "deleted_at", sa.DateTime(timezone=True), nullable=True, comment="Soft-delete timestamp. NULL = active. Non-NULL = deleted.", ), sa.Column( "lab_id", sa.UUID(), nullable=False, comment="Branch/unit this record belongs to (FK → lab.id)" ), sa.ForeignKeyConstraint( ["lab_id"], ["lab.id"], name=op.f("fk_discount_rule_lab_id_lab"), ondelete="RESTRICT" ), sa.PrimaryKeyConstraint("id", name=op.f("pk_discount_rule")), sa.UniqueConstraint("lab_id", "name", name="uq_discount_rule_lab_id_name"), ) op.create_index(op.f("ix_discount_rule_code"), "discount_rule", ["code"], unique=False) op.create_index(op.f("ix_discount_rule_is_active"), "discount_rule", ["is_active"], unique=False) op.create_index(op.f("ix_discount_rule_lab_id"), "discount_rule", ["lab_id"], unique=False) op.create_table( "patient", sa.Column( "patient_code", sa.String(length=30), nullable=False, comment="Human-readable code: P/{YY}/{10-digit-seq} e.g. P/26/0000000001", ), sa.Column("name", sa.String(length=200), nullable=False, comment="Full name of the patient"), sa.Column( "date_of_birth", sa.Date(), nullable=True, comment="Date of birth — preferred over age field when available", ), sa.Column( "age", sa.Integer(), nullable=True, comment="Age in years — fallback when DOB is unknown. Not recalculated automatically.", ), sa.Column( "gender", sa.Enum("male", "female", "other", name="gender", create_constraint=True), nullable=False, comment="Biological sex — used for test parameter reference range selection", ), sa.Column( "phone", sa.String(length=20), nullable=False, comment="Phone number — primary contact AND client portal username", ), sa.Column( "email", sa.String(length=255), nullable=True, comment="Email address — optional, used for report delivery", ), sa.Column("address", sa.Text(), nullable=True, comment="Residential or mailing address"), sa.Column( "country", sa.String(length=100), nullable=True, comment="Country — printed on reports for patients traveling abroad", ), sa.Column( "height_cm", sa.Numeric(precision=5, scale=2), nullable=True, comment="Height in centimetres — used for body-metric reference ranges", ), sa.Column( "weight_kg", sa.Numeric(precision=5, scale=2), nullable=True, comment="Weight in kilograms — used for body-metric reference ranges", ), sa.Column( "pin_hash", sa.String(length=255), nullable=True, 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.", ), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record creation timestamp (UTC)", ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record last-update timestamp (UTC) — ORM-managed", ), sa.Column( "deleted_at", sa.DateTime(timezone=True), nullable=True, comment="Soft-delete timestamp. NULL = active. Non-NULL = deleted.", ), sa.Column( "lab_id", sa.UUID(), nullable=False, comment="Branch/unit this record belongs to (FK → lab.id)" ), sa.ForeignKeyConstraint( ["lab_id"], ["lab.id"], name=op.f("fk_patient_lab_id_lab"), ondelete="RESTRICT" ), sa.PrimaryKeyConstraint("id", name=op.f("pk_patient")), sa.UniqueConstraint("lab_id", "patient_code", name="uq_patient_lab_id_patient_code"), sa.UniqueConstraint("lab_id", "phone", name="uq_patient_lab_id_phone"), ) op.create_index(op.f("ix_patient_gender"), "patient", ["gender"], unique=False) op.create_index(op.f("ix_patient_lab_id"), "patient", ["lab_id"], unique=False) op.create_index(op.f("ix_patient_patient_code"), "patient", ["patient_code"], unique=False) op.create_index(op.f("ix_patient_phone"), "patient", ["phone"], unique=False) op.create_table( "referral", sa.Column( "name", sa.String(length=200), nullable=False, comment="Full name of referring doctor, hospital, clinic, or agent", ), sa.Column( "referral_type", sa.Enum( "doctor", "hospital", "clinic", "agent", "other", name="referral_type", create_constraint=True ), server_default="doctor", nullable=False, comment="Type of referral source", ), sa.Column( "code", sa.String(length=30), nullable=True, comment="Partner identifier code (e.g. 'DOC-001', 'HOSP-KTM')", ), sa.Column("phone", sa.String(length=20), nullable=True, comment="Contact phone number"), sa.Column("email", sa.String(length=255), nullable=True, comment="Contact email address"), sa.Column("address", sa.Text(), nullable=True, comment="Physical clinic or office address"), sa.Column( "commission_rate", sa.Numeric(precision=5, scale=2), server_default="0.00", nullable=False, comment="Default commission percentage rate (e.g. 10.00 for 10%)", ), sa.Column( "is_active", sa.Boolean(), server_default=sa.text("true"), nullable=False, comment="Active status flag for booking and order referral selection", ), sa.Column("notes", sa.Text(), nullable=True, comment="Internal notes or partnership agreements"), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record creation timestamp (UTC)", ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record last-update timestamp (UTC) — ORM-managed", ), sa.Column( "deleted_at", sa.DateTime(timezone=True), nullable=True, comment="Soft-delete timestamp. NULL = active. Non-NULL = deleted.", ), sa.Column( "lab_id", sa.UUID(), nullable=False, comment="Branch/unit this record belongs to (FK → lab.id)" ), sa.ForeignKeyConstraint( ["lab_id"], ["lab.id"], name=op.f("fk_referral_lab_id_lab"), ondelete="RESTRICT" ), sa.PrimaryKeyConstraint("id", name=op.f("pk_referral")), sa.UniqueConstraint("lab_id", "code", name="uq_referral_lab_id_code"), ) op.create_index(op.f("ix_referral_code"), "referral", ["code"], unique=False) op.create_index(op.f("ix_referral_is_active"), "referral", ["is_active"], unique=False) op.create_index(op.f("ix_referral_lab_id"), "referral", ["lab_id"], unique=False) op.create_index(op.f("ix_referral_name"), "referral", ["name"], unique=False) op.create_index(op.f("ix_referral_referral_type"), "referral", ["referral_type"], unique=False) op.create_table( "role_permission", sa.Column( "role", sa.Enum( "superadmin", "admin", "manager", "doctor", "pathologist", "radiologist", "receptionist", "phlebotomist", "accountant", "general_user", name="user_role", create_constraint=True, ), nullable=False, comment="The staff role this assignment applies to", ), sa.Column("permission_id", sa.UUID(), nullable=False, comment="FK → permission.id"), sa.Column( "default_granted", sa.Boolean(), server_default="true", nullable=False, comment="True = granted to this role by default. False = denied by default.", ), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record creation timestamp (UTC)", ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record last-update timestamp (UTC) — ORM-managed", ), sa.ForeignKeyConstraint( ["permission_id"], ["permission.id"], name=op.f("fk_role_permission_permission_id_permission"), ondelete="CASCADE", ), sa.PrimaryKeyConstraint("id", name=op.f("pk_role_permission")), sa.UniqueConstraint("role", "permission_id", name="uq_role_permission_role_permission_id"), ) op.create_index( op.f("ix_role_permission_permission_id"), "role_permission", ["permission_id"], unique=False ) op.create_index(op.f("ix_role_permission_role"), "role_permission", ["role"], unique=False) op.create_table( "test_category", sa.Column( "name", sa.String(length=200), nullable=False, comment="Category display name (e.g., 'Hematology')", ), sa.Column( "description", sa.Text(), nullable=True, comment="Optional description shown in the catalog UI" ), sa.Column( "is_active", sa.Boolean(), server_default="true", nullable=False, comment="False = hidden from new order creation", ), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record creation timestamp (UTC)", ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record last-update timestamp (UTC) — ORM-managed", ), sa.Column( "deleted_at", sa.DateTime(timezone=True), nullable=True, comment="Soft-delete timestamp. NULL = active. Non-NULL = deleted.", ), sa.Column( "lab_id", sa.UUID(), nullable=False, comment="Branch/unit this record belongs to (FK → lab.id)" ), sa.ForeignKeyConstraint( ["lab_id"], ["lab.id"], name=op.f("fk_test_category_lab_id_lab"), ondelete="RESTRICT" ), sa.PrimaryKeyConstraint("id", name=op.f("pk_test_category")), sa.UniqueConstraint("lab_id", "name", name="uq_test_category_lab_id_name"), ) op.create_index(op.f("ix_test_category_lab_id"), "test_category", ["lab_id"], unique=False) op.create_index(op.f("ix_test_category_name"), "test_category", ["name"], unique=False) op.create_table( "user", sa.Column("name", sa.String(length=200), nullable=False, comment="Full name of the staff member"), sa.Column( "phone", sa.String(length=20), nullable=False, comment="Phone number — primary login identifier" ), sa.Column( "email", sa.String(length=255), nullable=True, comment="Email address — optional, used for notifications", ), sa.Column( "role", sa.Enum( "superadmin", "admin", "manager", "doctor", "pathologist", "radiologist", "receptionist", "phlebotomist", "accountant", "general_user", name="user_role", create_constraint=True, ), nullable=False, comment="Staff role — determines default permissions via role_permission", ), sa.Column( "hashed_password", sa.String(length=255), nullable=False, comment="bcrypt hash of the user's password — NEVER store plain text", ), sa.Column( "is_active", sa.Boolean(), server_default="true", nullable=False, comment="False = account suspended (login blocked, records retained)", ), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record creation timestamp (UTC)", ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record last-update timestamp (UTC) — ORM-managed", ), sa.Column( "deleted_at", sa.DateTime(timezone=True), nullable=True, comment="Soft-delete timestamp. NULL = active. Non-NULL = deleted.", ), sa.Column( "lab_id", sa.UUID(), nullable=False, comment="Branch/unit this record belongs to (FK → lab.id)" ), sa.ForeignKeyConstraint(["lab_id"], ["lab.id"], name=op.f("fk_user_lab_id_lab"), ondelete="RESTRICT"), sa.PrimaryKeyConstraint("id", name=op.f("pk_user")), sa.UniqueConstraint("lab_id", "email", name="uq_user_lab_id_email"), sa.UniqueConstraint("lab_id", "phone", name="uq_user_lab_id_phone"), ) op.create_index(op.f("ix_user_lab_id"), "user", ["lab_id"], unique=False) op.create_index(op.f("ix_user_phone"), "user", ["phone"], unique=False) op.create_index(op.f("ix_user_role"), "user", ["role"], unique=False) op.create_table( "appointment", sa.Column("patient_id", sa.UUID(), nullable=False, comment="FK → patient.id"), sa.Column( "slot_id", sa.UUID(), nullable=True, comment="FK → appointment_slot.id. NULL for walk-in appointments.", ), sa.Column( "visit_order_id", sa.UUID(), nullable=True, comment="FK → visit_order.id. Set when the receptionist creates an order. NULL = appointment exists but no order yet.", ), sa.Column( "status", sa.Enum( "pending", "confirmed", "checked_in", "completed", "cancelled", "no_show", name="appointment_status", create_constraint=True, ), server_default="pending", nullable=False, comment="Appointment lifecycle state", ), sa.Column( "booked_via", sa.Enum("walk_in", "phone", "online", "portal", name="booking_channel", create_constraint=True), server_default="walk_in", nullable=False, comment="How this appointment was created", ), sa.Column( "contact_name", sa.String(length=200), nullable=True, comment="Name of person who made the booking (if different from patient)", ), sa.Column( "contact_phone", sa.String(length=20), nullable=True, comment="Phone of booking contact (if different from patient)", ), sa.Column("notes", sa.Text(), nullable=True, comment="Optional internal notes from staff"), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record creation timestamp (UTC)", ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record last-update timestamp (UTC) — ORM-managed", ), sa.Column( "deleted_at", sa.DateTime(timezone=True), nullable=True, comment="Soft-delete timestamp. NULL = active. Non-NULL = deleted.", ), sa.Column( "lab_id", sa.UUID(), nullable=False, comment="Branch/unit this record belongs to (FK → lab.id)" ), sa.ForeignKeyConstraint( ["lab_id"], ["lab.id"], name=op.f("fk_appointment_lab_id_lab"), ondelete="RESTRICT" ), sa.ForeignKeyConstraint( ["patient_id"], ["patient.id"], name=op.f("fk_appointment_patient_id_patient"), ondelete="RESTRICT", ), sa.ForeignKeyConstraint( ["slot_id"], ["appointment_slot.id"], name=op.f("fk_appointment_slot_id_appointment_slot"), ondelete="SET NULL", ), sa.ForeignKeyConstraint( ["visit_order_id"], ["visit_order.id"], name="fk_appointment_visit_order_id_visit_order", ondelete="SET NULL", use_alter=True, ), sa.PrimaryKeyConstraint("id", name=op.f("pk_appointment")), ) op.create_index(op.f("ix_appointment_lab_id"), "appointment", ["lab_id"], unique=False) op.create_index(op.f("ix_appointment_patient_id"), "appointment", ["patient_id"], unique=False) op.create_index(op.f("ix_appointment_slot_id"), "appointment", ["slot_id"], unique=False) op.create_index(op.f("ix_appointment_status"), "appointment", ["status"], unique=False) op.create_index(op.f("ix_appointment_visit_order_id"), "appointment", ["visit_order_id"], unique=False) op.create_table( "test", sa.Column("category_id", sa.UUID(), nullable=False, comment="FK → test_category.id"), sa.Column( "name", sa.String(length=200), nullable=False, comment="Full test name (e.g., 'Complete Blood Count')", ), sa.Column( "code", sa.String(length=50), nullable=False, comment="Short code used in orders and on reports (e.g., 'CBC', 'LFT')", ), sa.Column("description", sa.Text(), nullable=True, comment="Optional clinical description or note"), sa.Column( "price", sa.Numeric(precision=10, scale=2), nullable=False, comment="Current price for this test — copied to order_test.price_at_order on booking", ), sa.Column( "result_type", sa.Enum("numeric", "text", "composite", name="test_result_type", create_constraint=True), nullable=False, comment="How this test's result is captured (numeric / text / composite)", ), sa.Column( "is_active", sa.Boolean(), server_default="true", nullable=False, comment="False = hidden from new order creation", ), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record creation timestamp (UTC)", ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record last-update timestamp (UTC) — ORM-managed", ), sa.Column( "deleted_at", sa.DateTime(timezone=True), nullable=True, comment="Soft-delete timestamp. NULL = active. Non-NULL = deleted.", ), sa.Column( "lab_id", sa.UUID(), nullable=False, comment="Branch/unit this record belongs to (FK → lab.id)" ), sa.ForeignKeyConstraint( ["category_id"], ["test_category.id"], name=op.f("fk_test_category_id_test_category"), ondelete="RESTRICT", ), sa.ForeignKeyConstraint(["lab_id"], ["lab.id"], name=op.f("fk_test_lab_id_lab"), ondelete="RESTRICT"), sa.PrimaryKeyConstraint("id", name=op.f("pk_test")), sa.UniqueConstraint("lab_id", "code", name="uq_test_lab_id_code"), ) op.create_index(op.f("ix_test_category_id"), "test", ["category_id"], unique=False) op.create_index(op.f("ix_test_code"), "test", ["code"], unique=False) op.create_index(op.f("ix_test_lab_id"), "test", ["lab_id"], unique=False) op.create_index(op.f("ix_test_name"), "test", ["name"], unique=False) op.create_table( "test_package", sa.Column( "category_id", sa.UUID(), nullable=True, comment="Optional FK → test_category.id for catalog grouping", ), sa.Column( "name", sa.String(length=200), nullable=False, comment="Package display name (e.g., 'Full Body Checkup')", ), sa.Column( "code", sa.String(length=50), nullable=True, comment="Optional short code for quick lookup (e.g., 'FBC')", ), sa.Column( "description", sa.Text(), nullable=True, comment="Package description shown to receptionists in the order UI", ), sa.Column( "price", sa.Numeric(precision=10, scale=2), nullable=False, comment="Combined price — copied to order_package.price_at_order at booking", ), sa.Column( "is_active", sa.Boolean(), server_default="true", nullable=False, comment="False = hidden from new order creation", ), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record creation timestamp (UTC)", ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record last-update timestamp (UTC) — ORM-managed", ), sa.Column( "deleted_at", sa.DateTime(timezone=True), nullable=True, comment="Soft-delete timestamp. NULL = active. Non-NULL = deleted.", ), sa.Column( "lab_id", sa.UUID(), nullable=False, comment="Branch/unit this record belongs to (FK → lab.id)" ), sa.ForeignKeyConstraint( ["category_id"], ["test_category.id"], name=op.f("fk_test_package_category_id_test_category"), ondelete="SET NULL", ), sa.ForeignKeyConstraint( ["lab_id"], ["lab.id"], name=op.f("fk_test_package_lab_id_lab"), ondelete="RESTRICT" ), sa.PrimaryKeyConstraint("id", name=op.f("pk_test_package")), sa.UniqueConstraint("lab_id", "code", name="uq_test_package_lab_id_code"), ) op.create_index(op.f("ix_test_package_category_id"), "test_package", ["category_id"], unique=False) op.create_index(op.f("ix_test_package_code"), "test_package", ["code"], unique=False) op.create_index(op.f("ix_test_package_lab_id"), "test_package", ["lab_id"], unique=False) op.create_index(op.f("ix_test_package_name"), "test_package", ["name"], unique=False) op.create_table( "user_permission_override", sa.Column("user_id", sa.UUID(), nullable=False, comment="The staff user this override applies to"), sa.Column( "permission_id", sa.UUID(), nullable=False, comment="The permission being explicitly granted or denied", ), sa.Column( "is_granted", sa.Boolean(), nullable=False, comment="True = explicitly granted. False = explicitly denied.", ), sa.Column( "set_by", sa.UUID(), nullable=True, comment="FK → user.id of the admin who created this override (audit trail)", ), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record creation timestamp (UTC)", ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record last-update timestamp (UTC) — ORM-managed", ), sa.ForeignKeyConstraint( ["permission_id"], ["permission.id"], name=op.f("fk_user_permission_override_permission_id_permission"), ondelete="CASCADE", ), sa.ForeignKeyConstraint( ["set_by"], ["user.id"], name=op.f("fk_user_permission_override_set_by_user"), ondelete="SET NULL" ), sa.ForeignKeyConstraint( ["user_id"], ["user.id"], name=op.f("fk_user_permission_override_user_id_user"), ondelete="CASCADE", ), sa.PrimaryKeyConstraint("id", name=op.f("pk_user_permission_override")), sa.UniqueConstraint( "user_id", "permission_id", name="uq_user_permission_override_user_id_permission_id" ), ) op.create_index( op.f("ix_user_permission_override_permission_id"), "user_permission_override", ["permission_id"], unique=False, ) op.create_index( op.f("ix_user_permission_override_set_by"), "user_permission_override", ["set_by"], unique=False ) op.create_index( op.f("ix_user_permission_override_user_id"), "user_permission_override", ["user_id"], unique=False ) op.create_table( "test_package_item", sa.Column("package_id", sa.UUID(), nullable=False, comment="FK → test_package.id"), sa.Column( "test_id", sa.UUID(), nullable=False, comment="FK → test.id — the test included in this package" ), sa.Column( "sort_order", sa.Integer(), server_default="0", nullable=False, comment="Display order of this test within the package (ascending)", ), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.ForeignKeyConstraint( ["package_id"], ["test_package.id"], name=op.f("fk_test_package_item_package_id_test_package"), ondelete="CASCADE", ), sa.ForeignKeyConstraint( ["test_id"], ["test.id"], name=op.f("fk_test_package_item_test_id_test"), ondelete="RESTRICT" ), sa.PrimaryKeyConstraint("id", name=op.f("pk_test_package_item")), sa.UniqueConstraint("package_id", "test_id", name="uq_test_package_item_package_id_test_id"), ) op.create_index( op.f("ix_test_package_item_package_id"), "test_package_item", ["package_id"], unique=False ) op.create_index(op.f("ix_test_package_item_test_id"), "test_package_item", ["test_id"], unique=False) op.create_table( "test_parameter", sa.Column( "test_id", sa.UUID(), nullable=False, comment="FK → test.id — the test this parameter belongs to" ), sa.Column( "name", sa.String(length=200), nullable=False, comment="Parameter name shown on the report (e.g., 'Haemoglobin')", ), sa.Column( "unit", sa.String(length=50), nullable=True, comment="Measurement unit (e.g., 'mg/dL', 'g/L', 'cells/μL')", ), sa.Column( "decimal_places", sa.Integer(), server_default="2", nullable=False, comment="Number of decimal places for display formatting", ), sa.Column( "calc_factor", sa.Numeric(precision=10, scale=6), nullable=True, comment="Optional multiplier applied to raw instrument reading before storing", ), sa.Column( "method_note", sa.Text(), nullable=True, comment="Analytical method or instrument note printed on the report", ), sa.Column( "sort_order", sa.Integer(), server_default="0", nullable=False, comment="Display ordering within the test (ascending)", ), sa.Column( "is_active", sa.Boolean(), server_default="true", nullable=False, comment="False = parameter hidden from result entry and reports", ), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record creation timestamp (UTC)", ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record last-update timestamp (UTC) — ORM-managed", ), sa.Column( "deleted_at", sa.DateTime(timezone=True), nullable=True, comment="Soft-delete timestamp. NULL = active. Non-NULL = deleted.", ), sa.ForeignKeyConstraint( ["test_id"], ["test.id"], name=op.f("fk_test_parameter_test_id_test"), ondelete="RESTRICT" ), sa.PrimaryKeyConstraint("id", name=op.f("pk_test_parameter")), ) op.create_index(op.f("ix_test_parameter_sort_order"), "test_parameter", ["sort_order"], unique=False) op.create_index(op.f("ix_test_parameter_test_id"), "test_parameter", ["test_id"], unique=False) op.create_table( "visit_order", sa.Column("patient_id", sa.UUID(), nullable=False, comment="FK → patient.id"), sa.Column( "appointment_id", sa.UUID(), nullable=True, comment="FK → appointment.id. NULL for walk-in orders.", ), sa.Column( "referral_id", sa.UUID(), nullable=True, comment="FK → referral.id. NULL if not referred via a registered referral source.", ), sa.Column( "order_number", sa.String(length=30), nullable=False, comment="Human-readable order ID: ORD/{YY}/{6-digit-seq} e.g. ORD/26/000001", ), sa.Column( "status", sa.Enum( "pending", "confirmed", "sample_collected", "processing", "completed", "cancelled", name="order_status", create_constraint=True, ), server_default="pending", nullable=False, comment="Order lifecycle state", ), sa.Column( "referred_by_custom", sa.String(length=300), nullable=True, comment="Free-text referral note (e.g., 'Dr. Smith, City Hospital') when not using referral table", ), sa.Column("next_date", sa.Date(), nullable=True, comment="Optional follow-up date for the patient"), sa.Column( "destination_country", sa.String(length=100), nullable=True, comment="Country to which the report will be submitted (printed on report header)", ), sa.Column("notes", sa.Text(), nullable=True, comment="Internal notes from staff"), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record creation timestamp (UTC)", ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record last-update timestamp (UTC) — ORM-managed", ), sa.Column( "deleted_at", sa.DateTime(timezone=True), nullable=True, comment="Soft-delete timestamp. NULL = active. Non-NULL = deleted.", ), sa.Column( "lab_id", sa.UUID(), nullable=False, comment="Branch/unit this record belongs to (FK → lab.id)" ), sa.ForeignKeyConstraint( ["appointment_id"], ["appointment.id"], name=op.f("fk_visit_order_appointment_id_appointment"), ondelete="SET NULL", ), sa.ForeignKeyConstraint( ["lab_id"], ["lab.id"], name=op.f("fk_visit_order_lab_id_lab"), ondelete="RESTRICT" ), sa.ForeignKeyConstraint( ["patient_id"], ["patient.id"], name=op.f("fk_visit_order_patient_id_patient"), ondelete="RESTRICT", ), sa.ForeignKeyConstraint( ["referral_id"], ["referral.id"], name=op.f("fk_visit_order_referral_id_referral"), ondelete="SET NULL", ), sa.PrimaryKeyConstraint("id", name=op.f("pk_visit_order")), sa.UniqueConstraint("lab_id", "order_number", name="uq_visit_order_lab_id_order_number"), ) op.create_index(op.f("ix_visit_order_appointment_id"), "visit_order", ["appointment_id"], unique=False) op.create_index(op.f("ix_visit_order_lab_id"), "visit_order", ["lab_id"], unique=False) op.create_index(op.f("ix_visit_order_order_number"), "visit_order", ["order_number"], unique=False) op.create_index(op.f("ix_visit_order_patient_id"), "visit_order", ["patient_id"], unique=False) op.create_index(op.f("ix_visit_order_referral_id"), "visit_order", ["referral_id"], unique=False) op.create_index(op.f("ix_visit_order_status"), "visit_order", ["status"], unique=False) op.create_table( "bill", sa.Column("order_id", sa.UUID(), nullable=False, comment="FK → visit_order.id (1:1 with VisitOrder)"), sa.Column( "patient_id", sa.UUID(), nullable=False, comment="FK → patient.id. Denormalized for rapid patient invoice queries.", ), sa.Column( "discount_rule_id", sa.UUID(), nullable=True, comment="FK → discount_rule.id. Null if manual or no discount applied.", ), sa.Column( "bill_number", sa.String(length=30), nullable=False, comment="Human-readable invoice sequence: INV/{YY}/{6-digit-seq} e.g. INV/26/000001", ), sa.Column( "status", sa.Enum("unpaid", "partial", "paid", "void", name="bill_status", create_constraint=True), server_default="unpaid", nullable=False, comment="Settlement status of the bill", ), sa.Column( "subtotal", sa.Numeric(precision=12, scale=2), server_default="0.00", nullable=False, comment="Sum of prices (price_at_order) of all ordered tests and packages", ), sa.Column( "discount_amount", sa.Numeric(precision=12, scale=2), server_default="0.00", nullable=False, comment="Total discount subtracted from subtotal", ), sa.Column( "tax_amount", sa.Numeric(precision=12, scale=2), server_default="0.00", nullable=False, comment="Tax/VAT amount applied to the invoice", ), sa.Column( "total_amount", sa.Numeric(precision=12, scale=2), server_default="0.00", nullable=False, comment="Final payable amount: (subtotal - discount_amount + tax_amount)", ), sa.Column( "paid_amount", sa.Numeric(precision=12, scale=2), server_default="0.00", nullable=False, comment="Total payments received and settled against this bill", ), sa.Column( "due_amount", sa.Numeric(precision=12, scale=2), server_default="0.00", nullable=False, comment="Outstanding balance due: (total_amount - paid_amount)", ), sa.Column("notes", sa.Text(), nullable=True, comment="Billing remarks, terms, or cashier notes"), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record creation timestamp (UTC)", ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record last-update timestamp (UTC) — ORM-managed", ), sa.Column( "deleted_at", sa.DateTime(timezone=True), nullable=True, comment="Soft-delete timestamp. NULL = active. Non-NULL = deleted.", ), sa.Column( "lab_id", sa.UUID(), nullable=False, comment="Branch/unit this record belongs to (FK → lab.id)" ), sa.ForeignKeyConstraint( ["discount_rule_id"], ["discount_rule.id"], name=op.f("fk_bill_discount_rule_id_discount_rule"), ondelete="SET NULL", ), sa.ForeignKeyConstraint(["lab_id"], ["lab.id"], name=op.f("fk_bill_lab_id_lab"), ondelete="RESTRICT"), sa.ForeignKeyConstraint( ["order_id"], ["visit_order.id"], name=op.f("fk_bill_order_id_visit_order"), ondelete="RESTRICT" ), sa.ForeignKeyConstraint( ["patient_id"], ["patient.id"], name=op.f("fk_bill_patient_id_patient"), ondelete="RESTRICT" ), sa.PrimaryKeyConstraint("id", name=op.f("pk_bill")), sa.UniqueConstraint("lab_id", "bill_number", name="uq_bill_lab_id_bill_number"), sa.UniqueConstraint("order_id", name="uq_bill_order_id"), ) op.create_index(op.f("ix_bill_bill_number"), "bill", ["bill_number"], unique=False) op.create_index(op.f("ix_bill_discount_rule_id"), "bill", ["discount_rule_id"], unique=False) op.create_index(op.f("ix_bill_lab_id"), "bill", ["lab_id"], unique=False) op.create_index(op.f("ix_bill_order_id"), "bill", ["order_id"], unique=True) op.create_index(op.f("ix_bill_patient_id"), "bill", ["patient_id"], unique=False) op.create_index(op.f("ix_bill_status"), "bill", ["status"], unique=False) op.create_table( "commission_entry", sa.Column( "referral_id", sa.UUID(), nullable=False, comment="FK → referral.id. The partner receiving this commission.", ), sa.Column( "order_id", sa.UUID(), nullable=False, comment="FK → visit_order.id. The visit order generating this commission.", ), sa.Column( "created_by", sa.UUID(), nullable=True, comment="FK → user.id. Staff member who calculated or approved this commission.", ), sa.Column( "order_amount", sa.Numeric(precision=12, scale=2), server_default="0.00", nullable=False, comment="Base order amount eligible for commission calculation", ), sa.Column( "commission_rate", sa.Numeric(precision=5, scale=2), server_default="0.00", nullable=False, comment="Commission percentage rate applied (snapshot from referral or override)", ), sa.Column( "commission_amount", sa.Numeric(precision=12, scale=2), nullable=False, comment="Calculated commission payout amount", ), sa.Column( "is_settled", sa.Boolean(), server_default=sa.text("false"), nullable=False, comment="Whether this commission has been disbursed/settled to partner", ), sa.Column( "settled_at", postgresql.TIMESTAMP(timezone=True), nullable=True, comment="Timestamp when commission payout was disbursed (TIMESTAMPTZ)", ), sa.Column( "notes", sa.Text(), nullable=True, comment="Accounting settlement notes or payout transaction reference", ), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record creation timestamp (UTC)", ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record last-update timestamp (UTC) — ORM-managed", ), sa.Column( "lab_id", sa.UUID(), nullable=False, comment="Branch/unit this record belongs to (FK → lab.id)" ), sa.ForeignKeyConstraint( ["created_by"], ["user.id"], name=op.f("fk_commission_entry_created_by_user"), ondelete="SET NULL" ), sa.ForeignKeyConstraint( ["lab_id"], ["lab.id"], name=op.f("fk_commission_entry_lab_id_lab"), ondelete="RESTRICT" ), sa.ForeignKeyConstraint( ["order_id"], ["visit_order.id"], name=op.f("fk_commission_entry_order_id_visit_order"), ondelete="RESTRICT", ), sa.ForeignKeyConstraint( ["referral_id"], ["referral.id"], name=op.f("fk_commission_entry_referral_id_referral"), ondelete="RESTRICT", ), sa.PrimaryKeyConstraint("id", name=op.f("pk_commission_entry")), sa.UniqueConstraint("order_id", "referral_id", name="uq_commission_entry_order_id_referral_id"), ) op.create_index(op.f("ix_commission_entry_created_by"), "commission_entry", ["created_by"], unique=False) op.create_index(op.f("ix_commission_entry_is_settled"), "commission_entry", ["is_settled"], unique=False) op.create_index(op.f("ix_commission_entry_lab_id"), "commission_entry", ["lab_id"], unique=False) op.create_index(op.f("ix_commission_entry_order_id"), "commission_entry", ["order_id"], unique=False) op.create_index( op.f("ix_commission_entry_referral_id"), "commission_entry", ["referral_id"], unique=False ) op.create_table( "order_package", sa.Column("order_id", sa.UUID(), nullable=False, comment="FK → visit_order.id"), sa.Column("package_id", sa.UUID(), nullable=False, comment="FK → test_package.id"), sa.Column( "price_at_order", sa.Numeric(precision=10, scale=2), nullable=False, comment="Price snapshot from package.price at order creation. Immutable.", ), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record creation timestamp (UTC)", ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record last-update timestamp (UTC) — ORM-managed", ), sa.ForeignKeyConstraint( ["order_id"], ["visit_order.id"], name=op.f("fk_order_package_order_id_visit_order"), ondelete="CASCADE", ), sa.ForeignKeyConstraint( ["package_id"], ["test_package.id"], name=op.f("fk_order_package_package_id_test_package"), ondelete="RESTRICT", ), sa.PrimaryKeyConstraint("id", name=op.f("pk_order_package")), sa.UniqueConstraint("order_id", "package_id", name="uq_order_package_order_id_package_id"), ) op.create_index(op.f("ix_order_package_order_id"), "order_package", ["order_id"], unique=False) op.create_index(op.f("ix_order_package_package_id"), "order_package", ["package_id"], unique=False) op.create_table( "order_test", sa.Column("order_id", sa.UUID(), nullable=False, comment="FK → visit_order.id"), sa.Column("test_id", sa.UUID(), nullable=False, comment="FK → test.id"), sa.Column( "price_at_order", sa.Numeric(precision=10, scale=2), nullable=False, comment="Price snapshot from test.price at order creation. Immutable.", ), sa.Column( "status", sa.Enum( "draft", "processing", "verified", "released", "cancelled", name="order_test_status", create_constraint=True, ), server_default="draft", nullable=False, comment="Per-test status within this order", ), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record creation timestamp (UTC)", ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record last-update timestamp (UTC) — ORM-managed", ), sa.ForeignKeyConstraint( ["order_id"], ["visit_order.id"], name=op.f("fk_order_test_order_id_visit_order"), ondelete="CASCADE", ), sa.ForeignKeyConstraint( ["test_id"], ["test.id"], name=op.f("fk_order_test_test_id_test"), ondelete="RESTRICT" ), sa.PrimaryKeyConstraint("id", name=op.f("pk_order_test")), sa.UniqueConstraint("order_id", "test_id", name="uq_order_test_order_id_test_id"), ) op.create_index(op.f("ix_order_test_order_id"), "order_test", ["order_id"], unique=False) op.create_index(op.f("ix_order_test_status"), "order_test", ["status"], unique=False) op.create_index(op.f("ix_order_test_test_id"), "order_test", ["test_id"], unique=False) op.create_table( "report", sa.Column( "order_id", sa.UUID(), nullable=False, comment="FK → visit_order.id. Scopes the report to the order and lab branch.", ), sa.Column( "verified_by", sa.UUID(), nullable=True, comment="FK → user.id. Pathologist/doctor who verified and signed this report.", ), sa.Column( "previous_report_id", sa.UUID(), nullable=True, comment="FK → report.id. Points to prior version if this report was amended or re-run.", ), sa.Column( "version", sa.Integer(), server_default="1", nullable=False, comment="Incremental version number for this order's reports (1, 2, ...)", ), sa.Column( "status", sa.Enum( "draft", "pending_verification", "verified", "released", "rejected", name="report_status", create_constraint=True, ), server_default="draft", nullable=False, comment="Report verification and release lifecycle status", ), sa.Column( "verification_reason", sa.Text(), nullable=True, comment="Clinical remarks, verification notes, or rejection rationale", ), sa.Column( "verified_at", postgresql.TIMESTAMP(timezone=True), nullable=True, comment="Timestamp when the report was verified by medical authority (TIMESTAMPTZ)", ), sa.Column( "released_at", postgresql.TIMESTAMP(timezone=True), nullable=True, comment="Timestamp when the report was released to patient (TIMESTAMPTZ)", ), sa.Column( "file_url", sa.String(length=500), nullable=True, comment="Object storage key/URI for the generated PDF report file", ), sa.Column( "notes", sa.Text(), nullable=True, comment="Clinical remarks or pathology interpretation notes included in the report", ), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record creation timestamp (UTC)", ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record last-update timestamp (UTC) — ORM-managed", ), sa.Column( "deleted_at", sa.DateTime(timezone=True), nullable=True, comment="Soft-delete timestamp. NULL = active. Non-NULL = deleted.", ), sa.ForeignKeyConstraint( ["order_id"], ["visit_order.id"], name=op.f("fk_report_order_id_visit_order"), ondelete="RESTRICT" ), sa.ForeignKeyConstraint( ["previous_report_id"], ["report.id"], name=op.f("fk_report_previous_report_id_report"), ondelete="SET NULL", ), sa.ForeignKeyConstraint( ["verified_by"], ["user.id"], name=op.f("fk_report_verified_by_user"), ondelete="SET NULL" ), sa.PrimaryKeyConstraint("id", name=op.f("pk_report")), sa.UniqueConstraint("order_id", "version", name="uq_report_order_id_version"), ) op.create_index(op.f("ix_report_order_id"), "report", ["order_id"], unique=False) op.create_index(op.f("ix_report_previous_report_id"), "report", ["previous_report_id"], unique=False) op.create_index(op.f("ix_report_status"), "report", ["status"], unique=False) op.create_index(op.f("ix_report_verified_by"), "report", ["verified_by"], unique=False) op.create_table( "sample", sa.Column( "order_id", sa.UUID(), nullable=False, comment="FK → visit_order.id — the order this sample was collected for", ), sa.Column( "sample_code", sa.String(length=30), nullable=False, comment="Human-readable code: S/{YY}/{6-digit-seq} e.g. S/26/000001", ), sa.Column( "barcode", sa.String(length=50), nullable=False, comment="Scanner-readable barcode value: SAM-{YYYYMMDD}-{NNNNNN} Printed on tube label. Lookup by this field on scan.", ), sa.Column( "sample_type", sa.String(length=100), nullable=False, comment="Sample type (e.g., blood, urine, stool). Free-text for flexibility.", ), sa.Column( "status", sa.Enum( "pending", "collected", "processing", "completed", name="sample_status", create_constraint=True, ), server_default="pending", nullable=False, comment="Physical sample lifecycle state", ), sa.Column( "collected_by", sa.UUID(), nullable=True, comment="FK → user.id (phlebotomist). Set at sample collection time.", ), sa.Column( "collected_at", postgresql.TIMESTAMP(timezone=True), nullable=True, comment="Actual sample collection timestamp (TIMESTAMPTZ). Set at collection.", ), sa.Column( "notes", sa.Text(), nullable=True, comment="Haemolysis, lipemia, quantity notes, or collection remarks", ), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record creation timestamp (UTC)", ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record last-update timestamp (UTC) — ORM-managed", ), sa.Column( "lab_id", sa.UUID(), nullable=False, comment="Branch/unit this record belongs to (FK → lab.id)" ), sa.ForeignKeyConstraint( ["collected_by"], ["user.id"], name=op.f("fk_sample_collected_by_user"), ondelete="SET NULL" ), sa.ForeignKeyConstraint( ["lab_id"], ["lab.id"], name=op.f("fk_sample_lab_id_lab"), ondelete="RESTRICT" ), sa.ForeignKeyConstraint( ["order_id"], ["visit_order.id"], name=op.f("fk_sample_order_id_visit_order"), ondelete="RESTRICT" ), sa.PrimaryKeyConstraint("id", name=op.f("pk_sample")), sa.UniqueConstraint("barcode", name="uq_sample_barcode"), sa.UniqueConstraint("lab_id", "sample_code", name="uq_sample_lab_id_sample_code"), ) op.create_index(op.f("ix_sample_barcode"), "sample", ["barcode"], unique=False) op.create_index(op.f("ix_sample_collected_by"), "sample", ["collected_by"], unique=False) op.create_index(op.f("ix_sample_lab_id"), "sample", ["lab_id"], unique=False) op.create_index(op.f("ix_sample_order_id"), "sample", ["order_id"], unique=False) op.create_index(op.f("ix_sample_sample_code"), "sample", ["sample_code"], unique=False) op.create_index(op.f("ix_sample_sample_type"), "sample", ["sample_type"], unique=False) op.create_index(op.f("ix_sample_status"), "sample", ["status"], unique=False) op.create_table( "test_parameter_range", sa.Column("parameter_id", sa.UUID(), nullable=False, comment="FK → test_parameter.id"), sa.Column( "gender", sa.Enum("male", "female", "other", name="gender", create_constraint=True), nullable=True, comment="Gender this range applies to. NULL = applies to all genders.", ), sa.Column( "age_min", sa.Integer(), nullable=True, comment="Minimum age in years (inclusive). NULL = no lower age bound.", ), sa.Column( "age_max", sa.Integer(), nullable=True, comment="Maximum age in years (inclusive). NULL = no upper age bound.", ), sa.Column( "numeric_low", sa.Numeric(precision=10, scale=4), nullable=True, comment="Lower bound of normal range. NULL = no lower bound.", ), sa.Column( "numeric_high", sa.Numeric(precision=10, scale=4), nullable=True, comment="Upper bound of normal range. NULL = no upper bound.", ), sa.Column( "text_normal", sa.String(length=200), nullable=True, comment="Expected normal value for TEXT-type parameters (e.g., 'Negative')", ), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record creation timestamp (UTC)", ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record last-update timestamp (UTC) — ORM-managed", ), sa.ForeignKeyConstraint( ["parameter_id"], ["test_parameter.id"], name=op.f("fk_test_parameter_range_parameter_id_test_parameter"), ondelete="CASCADE", ), sa.PrimaryKeyConstraint("id", name=op.f("pk_test_parameter_range")), ) op.create_index( op.f("ix_test_parameter_range_parameter_id"), "test_parameter_range", ["parameter_id"], unique=False ) op.create_table( "notification", sa.Column( "patient_id", sa.UUID(), nullable=False, comment="FK → patient.id. Target recipient of the notification.", ), sa.Column( "order_id", sa.UUID(), nullable=True, comment="FK → visit_order.id. Optional order context if message is order-related.", ), sa.Column( "report_id", sa.UUID(), nullable=True, comment="FK → report.id. Optional report context if message is report-release link.", ), sa.Column( "channel", sa.Enum("sms", "email", "portal", name="notification_channel", create_constraint=True), nullable=False, comment="Transport channel: SMS, Email, or Portal message", ), sa.Column( "status", sa.Enum("pending", "sent", "failed", name="notification_status", create_constraint=True), server_default="pending", nullable=False, comment="Dispatch and delivery status", ), sa.Column( "recipient", sa.String(length=255), nullable=False, comment="Destination address: phone number (E.164) or email address", ), sa.Column( "subject", sa.String(length=255), nullable=True, comment="Subject line for email, or title for portal notification", ), sa.Column( "message", sa.Text(), nullable=False, comment="Body content of the notification message or SMS text", ), sa.Column( "sent_at", postgresql.TIMESTAMP(timezone=True), nullable=True, comment="Timestamp when successfully dispatched to provider (TIMESTAMPTZ)", ), sa.Column( "error_message", sa.Text(), nullable=True, comment="Failure reason or gateway response error if dispatch failed", ), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record creation timestamp (UTC)", ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record last-update timestamp (UTC) — ORM-managed", ), sa.ForeignKeyConstraint( ["order_id"], ["visit_order.id"], name=op.f("fk_notification_order_id_visit_order"), ondelete="SET NULL", ), sa.ForeignKeyConstraint( ["patient_id"], ["patient.id"], name=op.f("fk_notification_patient_id_patient"), ondelete="CASCADE", ), sa.ForeignKeyConstraint( ["report_id"], ["report.id"], name=op.f("fk_notification_report_id_report"), ondelete="SET NULL" ), sa.PrimaryKeyConstraint("id", name=op.f("pk_notification")), ) op.create_index(op.f("ix_notification_channel"), "notification", ["channel"], unique=False) op.create_index(op.f("ix_notification_order_id"), "notification", ["order_id"], unique=False) op.create_index(op.f("ix_notification_patient_id"), "notification", ["patient_id"], unique=False) op.create_index(op.f("ix_notification_report_id"), "notification", ["report_id"], unique=False) op.create_index(op.f("ix_notification_status"), "notification", ["status"], unique=False) op.create_table( "payment", sa.Column( "bill_id", sa.UUID(), nullable=False, comment="FK → bill.id. The invoice to which this payment is credited.", ), sa.Column( "received_by", sa.UUID(), nullable=True, comment="FK → user.id. Staff member (accountant/receptionist) who took payment.", ), sa.Column( "amount", sa.Numeric(precision=12, scale=2), nullable=False, comment="Tendered payment transaction amount", ), sa.Column( "payment_mode", sa.Enum( "cash", "card", "transfer", "cheque", "mobile_banking", "other", name="payment_mode", create_constraint=True, ), server_default="cash", nullable=False, comment="Tender mode: cash, card, bank transfer, mobile wallet, or cheque", ), sa.Column( "transaction_reference", sa.String(length=100), nullable=True, comment="Card slip number, UPI/mobile transaction ID, or bank reference", ), sa.Column( "paid_at", postgresql.TIMESTAMP(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Exact timestamp when payment was tendered (TIMESTAMPTZ)", ), sa.Column("notes", sa.Text(), nullable=True, comment="Cashier remarks or transaction notes"), sa.Column( "id", sa.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False, comment="Primary key — UUID v4 generated by PostgreSQL", ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record creation timestamp (UTC)", ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, comment="Record last-update timestamp (UTC) — ORM-managed", ), sa.Column( "lab_id", sa.UUID(), nullable=False, comment="Branch/unit this record belongs to (FK → lab.id)" ), sa.ForeignKeyConstraint( ["bill_id"], ["bill.id"], name=op.f("fk_payment_bill_id_bill"), ondelete="RESTRICT" ), sa.ForeignKeyConstraint( ["lab_id"], ["lab.id"], name=op.f("fk_payment_lab_id_lab"), ondelete="RESTRICT" ), sa.ForeignKeyConstraint( ["received_by"], ["user.id"], name=op.f("fk_payment_received_by_user"), ondelete="SET NULL" ), sa.PrimaryKeyConstraint("id", name=op.f("pk_payment")), ) op.create_index(op.f("ix_payment_bill_id"), "payment", ["bill_id"], unique=False) op.create_index(op.f("ix_payment_lab_id"), "payment", ["lab_id"], unique=False) op.create_index(op.f("ix_payment_paid_at"), "payment", ["paid_at"], unique=False) op.create_index(op.f("ix_payment_payment_mode"), "payment", ["payment_mode"], unique=False) op.create_index(op.f("ix_payment_received_by"), "payment", ["received_by"], unique=False) op.create_foreign_key( op.f("fk_appointment_visit_order_id_visit_order"), "appointment", "visit_order", ["visit_order_id"], ["id"], ondelete="SET NULL", ) # ### end Alembic commands ### def downgrade() -> None: # ### commands auto generated by Alembic - please adjust! ### op.drop_constraint( op.f("fk_appointment_visit_order_id_visit_order"), "appointment", type_="foreignkey", ) op.drop_index(op.f("ix_payment_received_by"), table_name="payment") op.drop_index(op.f("ix_payment_payment_mode"), table_name="payment") op.drop_index(op.f("ix_payment_paid_at"), table_name="payment") op.drop_index(op.f("ix_payment_lab_id"), table_name="payment") op.drop_index(op.f("ix_payment_bill_id"), table_name="payment") op.drop_table("payment") op.drop_index(op.f("ix_notification_status"), table_name="notification") op.drop_index(op.f("ix_notification_report_id"), table_name="notification") op.drop_index(op.f("ix_notification_patient_id"), table_name="notification") op.drop_index(op.f("ix_notification_order_id"), table_name="notification") op.drop_index(op.f("ix_notification_channel"), table_name="notification") op.drop_table("notification") op.drop_index(op.f("ix_test_parameter_range_parameter_id"), table_name="test_parameter_range") op.drop_table("test_parameter_range") op.drop_index(op.f("ix_sample_status"), table_name="sample") op.drop_index(op.f("ix_sample_sample_type"), table_name="sample") op.drop_index(op.f("ix_sample_sample_code"), table_name="sample") op.drop_index(op.f("ix_sample_order_id"), table_name="sample") op.drop_index(op.f("ix_sample_lab_id"), table_name="sample") op.drop_index(op.f("ix_sample_collected_by"), table_name="sample") op.drop_index(op.f("ix_sample_barcode"), table_name="sample") op.drop_table("sample") op.drop_index(op.f("ix_report_verified_by"), table_name="report") op.drop_index(op.f("ix_report_status"), table_name="report") op.drop_index(op.f("ix_report_previous_report_id"), table_name="report") op.drop_index(op.f("ix_report_order_id"), table_name="report") op.drop_table("report") op.drop_index(op.f("ix_order_test_test_id"), table_name="order_test") op.drop_index(op.f("ix_order_test_status"), table_name="order_test") op.drop_index(op.f("ix_order_test_order_id"), table_name="order_test") op.drop_table("order_test") op.drop_index(op.f("ix_order_package_package_id"), table_name="order_package") op.drop_index(op.f("ix_order_package_order_id"), table_name="order_package") op.drop_table("order_package") op.drop_index(op.f("ix_commission_entry_referral_id"), table_name="commission_entry") op.drop_index(op.f("ix_commission_entry_order_id"), table_name="commission_entry") op.drop_index(op.f("ix_commission_entry_lab_id"), table_name="commission_entry") op.drop_index(op.f("ix_commission_entry_is_settled"), table_name="commission_entry") op.drop_index(op.f("ix_commission_entry_created_by"), table_name="commission_entry") op.drop_table("commission_entry") op.drop_index(op.f("ix_bill_status"), table_name="bill") op.drop_index(op.f("ix_bill_patient_id"), table_name="bill") op.drop_index(op.f("ix_bill_order_id"), table_name="bill") op.drop_index(op.f("ix_bill_lab_id"), table_name="bill") op.drop_index(op.f("ix_bill_discount_rule_id"), table_name="bill") op.drop_index(op.f("ix_bill_bill_number"), table_name="bill") op.drop_table("bill") op.drop_index(op.f("ix_visit_order_status"), table_name="visit_order") op.drop_index(op.f("ix_visit_order_referral_id"), table_name="visit_order") op.drop_index(op.f("ix_visit_order_patient_id"), table_name="visit_order") op.drop_index(op.f("ix_visit_order_order_number"), table_name="visit_order") op.drop_index(op.f("ix_visit_order_lab_id"), table_name="visit_order") op.drop_index(op.f("ix_visit_order_appointment_id"), table_name="visit_order") op.drop_table("visit_order") op.drop_index(op.f("ix_test_parameter_test_id"), table_name="test_parameter") op.drop_index(op.f("ix_test_parameter_sort_order"), table_name="test_parameter") op.drop_table("test_parameter") op.drop_index(op.f("ix_test_package_item_test_id"), table_name="test_package_item") op.drop_index(op.f("ix_test_package_item_package_id"), table_name="test_package_item") op.drop_table("test_package_item") op.drop_index(op.f("ix_user_permission_override_user_id"), table_name="user_permission_override") op.drop_index(op.f("ix_user_permission_override_set_by"), table_name="user_permission_override") op.drop_index(op.f("ix_user_permission_override_permission_id"), table_name="user_permission_override") op.drop_table("user_permission_override") op.drop_index(op.f("ix_test_package_name"), table_name="test_package") op.drop_index(op.f("ix_test_package_lab_id"), table_name="test_package") op.drop_index(op.f("ix_test_package_code"), table_name="test_package") op.drop_index(op.f("ix_test_package_category_id"), table_name="test_package") op.drop_table("test_package") op.drop_index(op.f("ix_test_name"), table_name="test") op.drop_index(op.f("ix_test_lab_id"), table_name="test") op.drop_index(op.f("ix_test_code"), table_name="test") op.drop_index(op.f("ix_test_category_id"), table_name="test") op.drop_table("test") op.drop_index(op.f("ix_appointment_visit_order_id"), table_name="appointment") op.drop_index(op.f("ix_appointment_status"), table_name="appointment") op.drop_index(op.f("ix_appointment_slot_id"), table_name="appointment") op.drop_index(op.f("ix_appointment_patient_id"), table_name="appointment") op.drop_index(op.f("ix_appointment_lab_id"), table_name="appointment") op.drop_table("appointment") op.drop_index(op.f("ix_user_role"), table_name="user") op.drop_index(op.f("ix_user_phone"), table_name="user") op.drop_index(op.f("ix_user_lab_id"), table_name="user") op.drop_table("user") op.drop_index(op.f("ix_test_category_name"), table_name="test_category") op.drop_index(op.f("ix_test_category_lab_id"), table_name="test_category") op.drop_table("test_category") op.drop_index(op.f("ix_role_permission_role"), table_name="role_permission") op.drop_index(op.f("ix_role_permission_permission_id"), table_name="role_permission") op.drop_table("role_permission") op.drop_index(op.f("ix_referral_referral_type"), table_name="referral") op.drop_index(op.f("ix_referral_name"), table_name="referral") op.drop_index(op.f("ix_referral_lab_id"), table_name="referral") op.drop_index(op.f("ix_referral_is_active"), table_name="referral") op.drop_index(op.f("ix_referral_code"), table_name="referral") op.drop_table("referral") op.drop_index(op.f("ix_patient_phone"), table_name="patient") op.drop_index(op.f("ix_patient_patient_code"), table_name="patient") op.drop_index(op.f("ix_patient_lab_id"), table_name="patient") op.drop_index(op.f("ix_patient_gender"), table_name="patient") op.drop_table("patient") op.drop_index(op.f("ix_discount_rule_lab_id"), table_name="discount_rule") op.drop_index(op.f("ix_discount_rule_is_active"), table_name="discount_rule") op.drop_index(op.f("ix_discount_rule_code"), table_name="discount_rule") op.drop_table("discount_rule") op.drop_index(op.f("ix_appointment_slot_start_at"), table_name="appointment_slot") op.drop_index(op.f("ix_appointment_slot_lab_id"), table_name="appointment_slot") op.drop_table("appointment_slot") op.drop_index(op.f("ix_permission_code"), table_name="permission") op.drop_table("permission") op.drop_table("lab") # Drop all 15 native PostgreSQL ENUM types for enum_name in [ "booking_channel", "appointment_status", "payment_mode", "discount_type", "bill_status", "notification_status", "notification_channel", "sample_status", "order_test_status", "order_status", "gender", "referral_type", "report_status", "test_result_type", "user_role", ]: sa.Enum(name=enum_name).drop(op.get_bind(), checkfirst=True) # ### end Alembic commands ###