209 lines
11 KiB
Python
209 lines
11 KiB
Python
from __future__ import annotations
|
|
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
|
|
revision = "202607100001"
|
|
down_revision = None
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
user_role = sa.Enum("admin", "employee", "auditor", name="userrole")
|
|
customer_type = sa.Enum("practice", "clinic", name="customertype")
|
|
equipment_kind = sa.Enum("temperature_logger", "pressure_logger", "sensor", name="equipmentkind")
|
|
equipment_status = sa.Enum("green", "yellow", "red", name="equipmentstatus")
|
|
validation_status = sa.Enum("draft", "in_progress", "ready_for_report", "completed", name="validationstatus")
|
|
document_owner_type = sa.Enum("customer", "device", "validation", "equipment", name="documentownertype")
|
|
|
|
op.create_table(
|
|
"users",
|
|
sa.Column("email", sa.String(length=255), nullable=False),
|
|
sa.Column("full_name", sa.String(length=160), nullable=False),
|
|
sa.Column("role", user_role, nullable=False),
|
|
sa.Column("password_hash", sa.String(length=255), nullable=False),
|
|
sa.Column("is_active", sa.Boolean(), nullable=False),
|
|
sa.Column("id", sa.String(), nullable=False),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
|
|
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
|
|
sa.PrimaryKeyConstraint("id"),
|
|
sa.UniqueConstraint("email"),
|
|
)
|
|
op.create_index("ix_users_email", "users", ["email"])
|
|
|
|
op.create_table(
|
|
"customers",
|
|
sa.Column("customer_type", customer_type, nullable=False),
|
|
sa.Column("name", sa.String(length=220), nullable=False),
|
|
sa.Column("street", sa.String(length=220), nullable=True),
|
|
sa.Column("postal_code", sa.String(length=20), nullable=True),
|
|
sa.Column("city", sa.String(length=120), nullable=True),
|
|
sa.Column("phone", sa.String(length=80), nullable=True),
|
|
sa.Column("email", sa.String(length=255), nullable=True),
|
|
sa.Column("hygiene_officer", sa.String(length=160), nullable=True),
|
|
sa.Column("quality_manager", sa.String(length=160), nullable=True),
|
|
sa.Column("notes", sa.Text(), nullable=True),
|
|
sa.Column("id", sa.String(), nullable=False),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
|
|
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
|
|
sa.PrimaryKeyConstraint("id"),
|
|
)
|
|
op.create_index("ix_customers_name", "customers", ["name"])
|
|
|
|
op.create_table(
|
|
"equipment",
|
|
sa.Column("kind", equipment_kind, nullable=False),
|
|
sa.Column("manufacturer", sa.String(length=140), nullable=True),
|
|
sa.Column("model", sa.String(length=140), nullable=True),
|
|
sa.Column("serial_number", sa.String(length=140), nullable=False),
|
|
sa.Column("calibrated_on", sa.Date(), nullable=True),
|
|
sa.Column("calibration_due_on", sa.Date(), nullable=True),
|
|
sa.Column("certificate_document_id", sa.String(length=80), nullable=True),
|
|
sa.Column("status", equipment_status, nullable=False),
|
|
sa.Column("id", sa.String(), nullable=False),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
|
|
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
|
|
sa.PrimaryKeyConstraint("id"),
|
|
sa.UniqueConstraint("serial_number"),
|
|
)
|
|
op.create_index("ix_equipment_serial_number", "equipment", ["serial_number"])
|
|
|
|
op.create_table(
|
|
"contacts",
|
|
sa.Column("customer_id", sa.String(), nullable=False),
|
|
sa.Column("full_name", sa.String(length=160), nullable=False),
|
|
sa.Column("function", sa.String(length=120), nullable=True),
|
|
sa.Column("email", sa.String(length=255), nullable=True),
|
|
sa.Column("phone", sa.String(length=80), nullable=True),
|
|
sa.Column("id", sa.String(), nullable=False),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
|
|
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
|
|
sa.ForeignKeyConstraint(["customer_id"], ["customers.id"], ondelete="CASCADE"),
|
|
sa.PrimaryKeyConstraint("id"),
|
|
)
|
|
op.create_index("ix_contacts_customer_id", "contacts", ["customer_id"])
|
|
|
|
op.create_table(
|
|
"locations",
|
|
sa.Column("customer_id", sa.String(), nullable=False),
|
|
sa.Column("name", sa.String(length=160), nullable=False),
|
|
sa.Column("street", sa.String(length=220), nullable=True),
|
|
sa.Column("postal_code", sa.String(length=20), nullable=True),
|
|
sa.Column("city", sa.String(length=120), nullable=True),
|
|
sa.Column("room", sa.String(length=120), nullable=True),
|
|
sa.Column("id", sa.String(), nullable=False),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
|
|
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
|
|
sa.ForeignKeyConstraint(["customer_id"], ["customers.id"], ondelete="CASCADE"),
|
|
sa.PrimaryKeyConstraint("id"),
|
|
)
|
|
op.create_index("ix_locations_customer_id", "locations", ["customer_id"])
|
|
|
|
op.create_table(
|
|
"documents",
|
|
sa.Column("owner_type", document_owner_type, nullable=False),
|
|
sa.Column("owner_id", sa.String(length=80), nullable=False),
|
|
sa.Column("filename", sa.String(length=255), nullable=False),
|
|
sa.Column("content_type", sa.String(length=120), nullable=False),
|
|
sa.Column("storage_path", sa.String(length=500), nullable=False),
|
|
sa.Column("id", sa.String(), nullable=False),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
|
|
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
|
|
sa.PrimaryKeyConstraint("id"),
|
|
)
|
|
op.create_index("ix_documents_owner_id", "documents", ["owner_id"])
|
|
op.create_index("ix_documents_owner_type", "documents", ["owner_type"])
|
|
|
|
op.create_table(
|
|
"devices",
|
|
sa.Column("customer_id", sa.String(), nullable=False),
|
|
sa.Column("location_id", sa.String(), nullable=True),
|
|
sa.Column("manufacturer", sa.String(length=140), nullable=False),
|
|
sa.Column("model", sa.String(length=140), nullable=False),
|
|
sa.Column("device_type", sa.String(length=120), nullable=True),
|
|
sa.Column("serial_number", sa.String(length=140), nullable=False),
|
|
sa.Column("year_built", sa.Integer(), nullable=True),
|
|
sa.Column("commissioned_on", sa.Date(), nullable=True),
|
|
sa.Column("chamber_volume_liters", sa.Integer(), nullable=True),
|
|
sa.Column("steam_generation", sa.String(length=220), nullable=True),
|
|
sa.Column("water_treatment", sa.String(length=220), nullable=True),
|
|
sa.Column("documentation", sa.Text(), nullable=True),
|
|
sa.Column("supplier", sa.String(length=180), nullable=True),
|
|
sa.Column("id", sa.String(), nullable=False),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
|
|
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
|
|
sa.ForeignKeyConstraint(["customer_id"], ["customers.id"]),
|
|
sa.ForeignKeyConstraint(["location_id"], ["locations.id"]),
|
|
sa.PrimaryKeyConstraint("id"),
|
|
sa.UniqueConstraint("serial_number"),
|
|
)
|
|
op.create_index("ix_devices_customer_id", "devices", ["customer_id"])
|
|
op.create_index("ix_devices_location_id", "devices", ["location_id"])
|
|
op.create_index("ix_devices_serial_number", "devices", ["serial_number"])
|
|
|
|
op.create_table(
|
|
"programs",
|
|
sa.Column("device_id", sa.String(), nullable=False),
|
|
sa.Column("name", sa.String(length=160), nullable=False),
|
|
sa.Column("temperature_celsius", sa.Integer(), nullable=True),
|
|
sa.Column("holding_time_minutes", sa.Integer(), nullable=True),
|
|
sa.Column("drying_time_minutes", sa.Integer(), nullable=True),
|
|
sa.Column("id", sa.String(), nullable=False),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
|
|
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
|
|
sa.ForeignKeyConstraint(["device_id"], ["devices.id"]),
|
|
sa.PrimaryKeyConstraint("id"),
|
|
)
|
|
op.create_index("ix_programs_device_id", "programs", ["device_id"])
|
|
|
|
op.create_table(
|
|
"validations",
|
|
sa.Column("report_number", sa.String(length=80), nullable=False),
|
|
sa.Column("customer_id", sa.String(), nullable=False),
|
|
sa.Column("location_id", sa.String(), nullable=True),
|
|
sa.Column("device_id", sa.String(), nullable=True),
|
|
sa.Column("validation_type", sa.String(length=120), nullable=False),
|
|
sa.Column("scheduled_on", sa.Date(), nullable=True),
|
|
sa.Column("performed_on", sa.Date(), nullable=True),
|
|
sa.Column("next_validation_on", sa.Date(), nullable=True),
|
|
sa.Column("examiner_id", sa.String(), nullable=True),
|
|
sa.Column("status", validation_status, nullable=False),
|
|
sa.Column("result", sa.String(length=120), nullable=True),
|
|
sa.Column("notes", sa.Text(), nullable=True),
|
|
sa.Column("id", sa.String(), nullable=False),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
|
|
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
|
|
sa.ForeignKeyConstraint(["customer_id"], ["customers.id"]),
|
|
sa.ForeignKeyConstraint(["device_id"], ["devices.id"]),
|
|
sa.ForeignKeyConstraint(["examiner_id"], ["users.id"]),
|
|
sa.ForeignKeyConstraint(["location_id"], ["locations.id"]),
|
|
sa.PrimaryKeyConstraint("id"),
|
|
sa.UniqueConstraint("report_number"),
|
|
)
|
|
op.create_index("ix_validations_customer_id", "validations", ["customer_id"])
|
|
op.create_index("ix_validations_device_id", "validations", ["device_id"])
|
|
op.create_index("ix_validations_location_id", "validations", ["location_id"])
|
|
op.create_index("ix_validations_report_number", "validations", ["report_number"])
|
|
|
|
|
|
def downgrade() -> None:
|
|
op.drop_table("validations")
|
|
op.drop_table("programs")
|
|
op.drop_table("devices")
|
|
op.drop_table("documents")
|
|
op.drop_table("locations")
|
|
op.drop_table("contacts")
|
|
op.drop_table("equipment")
|
|
op.drop_table("customers")
|
|
op.drop_table("users")
|
|
for enum_name in [
|
|
"documentownertype",
|
|
"validationstatus",
|
|
"equipmentstatus",
|
|
"equipmentkind",
|
|
"customertype",
|
|
"userrole",
|
|
]:
|
|
sa.Enum(name=enum_name).drop(op.get_bind(), checkfirst=True)
|
|
|