685 lines
41 KiB
Python
685 lines
41 KiB
Python
from datetime import date, datetime, time
|
|
from decimal import Decimal
|
|
|
|
from sqlalchemy import (
|
|
JSON,
|
|
Boolean,
|
|
Date,
|
|
DateTime,
|
|
ForeignKey,
|
|
Integer,
|
|
Numeric,
|
|
String,
|
|
Text,
|
|
Time,
|
|
UniqueConstraint,
|
|
func,
|
|
)
|
|
from sqlalchemy.orm import Mapped, mapped_column, relationship
|
|
|
|
from app.db.base import Base
|
|
|
|
|
|
class Car(Base):
|
|
__tablename__ = "cars"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
owner_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), index=True)
|
|
name: Mapped[str] = mapped_column(String(160))
|
|
make: Mapped[str | None] = mapped_column(String(80))
|
|
model: Mapped[str | None] = mapped_column(String(80))
|
|
trim: Mapped[str | None] = mapped_column(String(120))
|
|
generation: Mapped[str | None] = mapped_column(String(120))
|
|
body_type: Mapped[str | None] = mapped_column(String(80))
|
|
year: Mapped[int | None]
|
|
plate_number: Mapped[str | None] = mapped_column(String(32))
|
|
vin: Mapped[str | None] = mapped_column(String(32))
|
|
license_plate_display: Mapped[str | None] = mapped_column(String(32))
|
|
license_plate_normalized: Mapped[str | None] = mapped_column(String(32), index=True)
|
|
license_plate_country: Mapped[str | None] = mapped_column(String(2), index=True)
|
|
vin_normalized: Mapped[str | None] = mapped_column(String(17), unique=True, index=True)
|
|
fuel_type: Mapped[str | None] = mapped_column(String(32))
|
|
engine_volume_l: Mapped[Decimal | None] = mapped_column(Numeric(5, 2))
|
|
transmission: Mapped[str | None] = mapped_column(String(40))
|
|
drive_type: Mapped[str | None] = mapped_column(String(40))
|
|
target_consumption_l_per_100km: Mapped[Decimal | None] = mapped_column(Numeric(6, 2))
|
|
fuel_tank_volume_l: Mapped[Decimal | None] = mapped_column(Numeric(6, 2))
|
|
engine_oil_type: Mapped[str | None] = mapped_column(String(80))
|
|
engine_oil_volume_l: Mapped[Decimal | None] = mapped_column(Numeric(5, 2))
|
|
transmission_fluid_type: Mapped[str | None] = mapped_column(String(80))
|
|
transmission_fluid_volume_l: Mapped[Decimal | None] = mapped_column(Numeric(5, 2))
|
|
coolant_type: Mapped[str | None] = mapped_column(String(80))
|
|
brake_fluid_type: Mapped[str | None] = mapped_column(String(80))
|
|
tire_pressure_front_bar: Mapped[Decimal | None] = mapped_column(Numeric(4, 2))
|
|
tire_pressure_rear_bar: Mapped[Decimal | None] = mapped_column(Numeric(4, 2))
|
|
tire_size: Mapped[str | None] = mapped_column(String(80))
|
|
oil_change_interval_km: Mapped[int | None] = mapped_column(Integer)
|
|
oil_change_interval_months: Mapped[int | None] = mapped_column(Integer)
|
|
purchase_date: Mapped[date | None] = mapped_column(Date)
|
|
purchase_price: Mapped[Decimal | None] = mapped_column(Numeric(12, 2))
|
|
purchase_currency: Mapped[str | None] = mapped_column(String(3))
|
|
purchase_type: Mapped[str] = mapped_column(String(24), default="unknown", server_default="unknown")
|
|
currency: Mapped[str] = mapped_column(String(3), default="RUB", server_default="RUB")
|
|
include_depreciation: Mapped[bool] = mapped_column(Boolean, default=False, server_default="false")
|
|
expected_ownership_months: Mapped[int | None] = mapped_column(Integer)
|
|
expected_residual_value: Mapped[Decimal | None] = mapped_column(Numeric(12, 2))
|
|
loan_principal: Mapped[Decimal | None] = mapped_column(Numeric(12, 2))
|
|
loan_down_payment: Mapped[Decimal | None] = mapped_column(Numeric(12, 2))
|
|
loan_term_months: Mapped[int | None] = mapped_column(Integer)
|
|
loan_annual_interest_rate: Mapped[Decimal | None] = mapped_column(Numeric(6, 3))
|
|
loan_first_payment_date: Mapped[date | None] = mapped_column(Date)
|
|
loan_payment_day: Mapped[int | None] = mapped_column(Integer)
|
|
loan_payment_type: Mapped[str] = mapped_column(String(24), default="annuity", server_default="annuity")
|
|
loan_currency: Mapped[str | None] = mapped_column(String(3))
|
|
loan_comment: Mapped[str | None] = mapped_column(Text)
|
|
current_odometer: Mapped[int | None]
|
|
notes: Mapped[str | None] = mapped_column(Text)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
|
|
updated_at: Mapped[datetime] = mapped_column(
|
|
DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
|
|
)
|
|
|
|
owner = relationship("User", back_populates="cars")
|
|
fuel_entries = relationship("FuelEntry", back_populates="car", cascade="all, delete-orphan")
|
|
service_entries = relationship("ServiceEntry", back_populates="car", cascade="all, delete-orphan")
|
|
expense_entries = relationship("ExpenseEntry", back_populates="car", cascade="all, delete-orphan")
|
|
service_links = relationship("CarServiceLink", back_populates="car", cascade="all, delete-orphan")
|
|
odometer_history = relationship("OdometerHistory", back_populates="car", cascade="all, delete-orphan")
|
|
|
|
|
|
class CarMake(Base):
|
|
__tablename__ = "car_makes"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
name: Mapped[str] = mapped_column(String(80), unique=True, index=True)
|
|
country: Mapped[str | None] = mapped_column(String(80))
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
models = relationship("CarModel", back_populates="make", cascade="all, delete-orphan")
|
|
|
|
|
|
class CarModel(Base):
|
|
__tablename__ = "car_models"
|
|
__table_args__ = (UniqueConstraint("make_id", "name", name="uq_car_models_make_name"),)
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
make_id: Mapped[int] = mapped_column(ForeignKey("car_makes.id", ondelete="CASCADE"), index=True)
|
|
name: Mapped[str] = mapped_column(String(100), index=True)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
make = relationship("CarMake", back_populates="models")
|
|
trims = relationship("CarTrim", back_populates="model", cascade="all, delete-orphan")
|
|
|
|
|
|
class CarTrim(Base):
|
|
__tablename__ = "car_trims"
|
|
__table_args__ = (UniqueConstraint("model_id", "name", name="uq_car_trims_model_name"),)
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
model_id: Mapped[int] = mapped_column(ForeignKey("car_models.id", ondelete="CASCADE"), index=True)
|
|
name: Mapped[str] = mapped_column(String(120), index=True)
|
|
body_type: Mapped[str | None] = mapped_column(String(60))
|
|
fuel_type: Mapped[str | None] = mapped_column(String(32))
|
|
transmission: Mapped[str | None] = mapped_column(String(32))
|
|
drive_type: Mapped[str | None] = mapped_column(String(32))
|
|
year_from: Mapped[int | None] = mapped_column(Integer)
|
|
year_to: Mapped[int | None] = mapped_column(Integer)
|
|
market: Mapped[str | None] = mapped_column(String(80))
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
model = relationship("CarModel", back_populates="trims")
|
|
|
|
|
|
class ServiceCenter(Base):
|
|
__tablename__ = "service_centers"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
name: Mapped[str] = mapped_column(String(160), unique=True, index=True)
|
|
legal_name: Mapped[str | None] = mapped_column(String(240))
|
|
display_name: Mapped[str | None] = mapped_column(String(160), index=True)
|
|
country: Mapped[str | None] = mapped_column(String(2), index=True)
|
|
city: Mapped[str | None] = mapped_column(String(120))
|
|
telegram_chat_id: Mapped[str | None] = mapped_column(String(80), unique=True, index=True)
|
|
phone: Mapped[str | None] = mapped_column(String(40))
|
|
contact_phone: Mapped[str | None] = mapped_column(String(40))
|
|
address: Mapped[str | None] = mapped_column(String(240))
|
|
description: Mapped[str | None] = mapped_column(Text)
|
|
specializations: Mapped[list | None] = mapped_column(JSON)
|
|
working_hours: Mapped[str | None] = mapped_column(String(240))
|
|
facade_photo_url: Mapped[str | None] = mapped_column(String(500))
|
|
document_photo_urls: Mapped[list | None] = mapped_column(JSON)
|
|
additional_photo_urls: Mapped[list | None] = mapped_column(JSON)
|
|
contact_person: Mapped[str | None] = mapped_column(String(160))
|
|
rating_avg: Mapped[Decimal | None] = mapped_column(Numeric(3, 2))
|
|
reviews_count: Mapped[int] = mapped_column(Integer, default=0, server_default="0")
|
|
business_registration_number: Mapped[str | None] = mapped_column(String(80))
|
|
verification_status: Mapped[str] = mapped_column(String(24), default="pending", server_default="pending", index=True)
|
|
owner_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), index=True)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
|
|
verified_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
suspended_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
|
|
car_links = relationship("CarServiceLink", back_populates="service_center", cascade="all, delete-orphan")
|
|
inbox_messages = relationship("ServiceInboxMessage", back_populates="service_center")
|
|
employees = relationship("ServiceEmployee", back_populates="service_center", cascade="all, delete-orphan")
|
|
visits = relationship("ServiceVisit", back_populates="service_center")
|
|
reviews = relationship("ServiceCenterReview", back_populates="service_center", cascade="all, delete-orphan")
|
|
booking_settings = relationship(
|
|
"ServiceCenterBookingSettings",
|
|
back_populates="service_center",
|
|
cascade="all, delete-orphan",
|
|
uselist=False,
|
|
)
|
|
holidays = relationship("ServiceCenterHoliday", back_populates="service_center", cascade="all, delete-orphan")
|
|
appointments = relationship("ServiceAppointment", back_populates="service_center", cascade="all, delete-orphan")
|
|
catalog_items = relationship("WorkOrderCatalogItem", back_populates="service_center", cascade="all, delete-orphan")
|
|
|
|
|
|
class CarServiceLink(Base):
|
|
__tablename__ = "car_service_links"
|
|
__table_args__ = (UniqueConstraint("car_id", "service_center_id", name="uq_car_service_link"),)
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
car_id: Mapped[int] = mapped_column(ForeignKey("cars.id", ondelete="CASCADE"), index=True)
|
|
service_center_id: Mapped[int] = mapped_column(ForeignKey("service_centers.id", ondelete="CASCADE"), index=True)
|
|
external_vehicle_ref: Mapped[str | None] = mapped_column(String(120), index=True)
|
|
access_level: Mapped[str] = mapped_column(String(32), default="basic", server_default="basic", index=True)
|
|
status: Mapped[str] = mapped_column(String(32), default="pending", server_default="pending", index=True)
|
|
requested_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), index=True)
|
|
approved_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), index=True)
|
|
approved_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
revoked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
is_active: Mapped[bool] = mapped_column(default=True)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
car = relationship("Car", back_populates="service_links")
|
|
service_center = relationship("ServiceCenter", back_populates="car_links")
|
|
|
|
|
|
class OdometerHistory(Base):
|
|
__tablename__ = "odometer_history"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
car_id: Mapped[int] = mapped_column(ForeignKey("cars.id", ondelete="CASCADE"), index=True)
|
|
previous_odometer: Mapped[int | None] = mapped_column(Integer)
|
|
new_odometer: Mapped[int] = mapped_column(Integer)
|
|
source_record_type: Mapped[str] = mapped_column(String(40), index=True)
|
|
source_record_id: Mapped[int | None] = mapped_column(Integer, index=True)
|
|
changed_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), index=True)
|
|
changed_by: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), index=True)
|
|
confirmation_required: Mapped[bool] = mapped_column(Boolean, default=False, server_default="false")
|
|
user_confirmed: Mapped[bool] = mapped_column(Boolean, default=True, server_default="true")
|
|
|
|
car = relationship("Car", back_populates="odometer_history")
|
|
|
|
|
|
class ServiceInboxMessage(Base):
|
|
__tablename__ = "service_inbox_messages"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
service_center_id: Mapped[int | None] = mapped_column(ForeignKey("service_centers.id", ondelete="SET NULL"), index=True)
|
|
car_id: Mapped[int | None] = mapped_column(ForeignKey("cars.id", ondelete="SET NULL"), index=True)
|
|
source_chat_id: Mapped[str | None] = mapped_column(String(80), index=True)
|
|
raw_text: Mapped[str] = mapped_column(Text)
|
|
parsed_status: Mapped[str] = mapped_column(String(32), default="pending", index=True)
|
|
parsed_payload: Mapped[str | None] = mapped_column(Text)
|
|
error: Mapped[str | None] = mapped_column(Text)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
service_center = relationship("ServiceCenter", back_populates="inbox_messages")
|
|
|
|
|
|
class VehicleAccess(Base):
|
|
__tablename__ = "vehicle_access"
|
|
__table_args__ = (UniqueConstraint("vehicle_id", "user_id", "role", name="uq_vehicle_access_user_role"),)
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
vehicle_id: Mapped[int] = mapped_column(ForeignKey("cars.id", ondelete="CASCADE"), index=True)
|
|
user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), index=True)
|
|
role: Mapped[str] = mapped_column(String(24), default="owner", server_default="owner", index=True)
|
|
status: Mapped[str] = mapped_column(String(24), default="active", server_default="active", index=True)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
|
|
revoked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
|
|
|
|
class ServiceCenterVerification(Base):
|
|
__tablename__ = "service_center_verifications"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
service_center_id: Mapped[int] = mapped_column(ForeignKey("service_centers.id", ondelete="CASCADE"), index=True)
|
|
submitted_documents: Mapped[list | None] = mapped_column(JSON)
|
|
comment: Mapped[str | None] = mapped_column(Text)
|
|
status: Mapped[str] = mapped_column(String(24), default="pending", server_default="pending", index=True)
|
|
reviewed_by: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), index=True)
|
|
reviewed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
|
|
class ServiceEmployee(Base):
|
|
__tablename__ = "service_employees"
|
|
__table_args__ = (UniqueConstraint("service_center_id", "user_id", name="uq_service_employee_user"),)
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
service_center_id: Mapped[int] = mapped_column(ForeignKey("service_centers.id", ondelete="CASCADE"), index=True)
|
|
user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), index=True)
|
|
role: Mapped[str] = mapped_column(String(32), default="receptionist", server_default="receptionist", index=True)
|
|
permissions: Mapped[dict | None] = mapped_column(JSON)
|
|
status: Mapped[str] = mapped_column(String(24), default="active", server_default="active", index=True)
|
|
invite_token: Mapped[str | None] = mapped_column(String(96), unique=True, index=True)
|
|
invite_expires_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
invite_revoked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
activated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
service_center = relationship("ServiceCenter", back_populates="employees")
|
|
|
|
|
|
class ServiceCenterBookingSettings(Base):
|
|
__tablename__ = "service_center_booking_settings"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
service_center_id: Mapped[int] = mapped_column(
|
|
ForeignKey("service_centers.id", ondelete="CASCADE"), unique=True, index=True
|
|
)
|
|
working_days: Mapped[list] = mapped_column(JSON, default=lambda: [0, 1, 2, 3, 4], server_default="[0,1,2,3,4]")
|
|
open_time: Mapped[time] = mapped_column(Time, default=time(9, 0), server_default="09:00:00")
|
|
close_time: Mapped[time] = mapped_column(Time, default=time(18, 0), server_default="18:00:00")
|
|
lunch_break_start: Mapped[time | None] = mapped_column(Time)
|
|
lunch_break_end: Mapped[time | None] = mapped_column(Time)
|
|
timezone: Mapped[str] = mapped_column(String(64), default="Asia/Seoul", server_default="Asia/Seoul")
|
|
slot_duration_minutes: Mapped[int] = mapped_column(Integer, default=30, server_default="30")
|
|
booking_buffer_minutes: Mapped[int] = mapped_column(Integer, default=0, server_default="0")
|
|
max_parallel_bookings: Mapped[int] = mapped_column(Integer, default=1, server_default="1")
|
|
accepts_online_booking: Mapped[bool] = mapped_column(Boolean, default=True, server_default="true", index=True)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
|
|
updated_at: Mapped[datetime] = mapped_column(
|
|
DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
|
|
)
|
|
|
|
service_center = relationship("ServiceCenter", back_populates="booking_settings")
|
|
|
|
|
|
class ServiceCenterHoliday(Base):
|
|
__tablename__ = "service_center_holidays"
|
|
__table_args__ = (UniqueConstraint("service_center_id", "holiday_date", name="uq_service_center_holiday"),)
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
service_center_id: Mapped[int] = mapped_column(ForeignKey("service_centers.id", ondelete="CASCADE"), index=True)
|
|
holiday_date: Mapped[date] = mapped_column(Date, index=True)
|
|
reason: Mapped[str | None] = mapped_column(String(240))
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
service_center = relationship("ServiceCenter", back_populates="holidays")
|
|
|
|
|
|
class ServiceVisit(Base):
|
|
__tablename__ = "service_visits"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
work_order_number: Mapped[str | None] = mapped_column(String(40), unique=True, index=True)
|
|
service_center_id: Mapped[int] = mapped_column(ForeignKey("service_centers.id", ondelete="CASCADE"), index=True)
|
|
vehicle_id: Mapped[int] = mapped_column(ForeignKey("cars.id", ondelete="CASCADE"), index=True)
|
|
owner_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), index=True)
|
|
created_by_employee_id: Mapped[int | None] = mapped_column(ForeignKey("service_employees.id", ondelete="SET NULL"), index=True)
|
|
assigned_employee_id: Mapped[int | None] = mapped_column(ForeignKey("service_employees.id", ondelete="SET NULL"), index=True)
|
|
visit_date: Mapped[date] = mapped_column(Date, index=True)
|
|
odometer: Mapped[int | None]
|
|
status: Mapped[str] = mapped_column(String(40), default="draft", server_default="draft", index=True)
|
|
customer_complaint: Mapped[str | None] = mapped_column(Text)
|
|
diagnosis: Mapped[str | None] = mapped_column(Text)
|
|
notes: Mapped[str | None] = mapped_column(Text)
|
|
service_comment: Mapped[str | None] = mapped_column(Text)
|
|
owner_comment: Mapped[str | None] = mapped_column(Text)
|
|
recommendations_text: Mapped[str | None] = mapped_column(Text)
|
|
attachment_urls: Mapped[list | None] = mapped_column(JSON)
|
|
total_cost: Mapped[Decimal | None] = mapped_column(Numeric(12, 2))
|
|
labor_total: Mapped[Decimal] = mapped_column(Numeric(12, 2), default=0, server_default="0")
|
|
product_total: Mapped[Decimal] = mapped_column(Numeric(12, 2), default=0, server_default="0")
|
|
discount_total: Mapped[Decimal] = mapped_column(Numeric(12, 2), default=0, server_default="0")
|
|
final_total: Mapped[Decimal] = mapped_column(Numeric(12, 2), default=0, server_default="0")
|
|
price_approved_total: Mapped[Decimal | None] = mapped_column(Numeric(12, 2))
|
|
approval_required: Mapped[bool] = mapped_column(Boolean, default=False, server_default="false")
|
|
version: Mapped[int] = mapped_column(Integer, default=1, server_default="1")
|
|
completed_snapshot: Mapped[dict | None] = mapped_column(JSON)
|
|
currency: Mapped[str] = mapped_column(String(3), default="RUB", server_default="RUB")
|
|
opened_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
approved_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
completed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
owner_resolved_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
|
|
updated_at: Mapped[datetime] = mapped_column(
|
|
DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
|
|
)
|
|
|
|
service_center = relationship("ServiceCenter", back_populates="visits")
|
|
work_items = relationship("ServiceWorkItem", back_populates="visit", cascade="all, delete-orphan")
|
|
product_items = relationship("ServiceProductItem", back_populates="visit", cascade="all, delete-orphan")
|
|
status_history = relationship("WorkOrderStatusHistory", back_populates="visit", cascade="all, delete-orphan")
|
|
corrections = relationship("WorkOrderCorrection", back_populates="visit", cascade="all, delete-orphan")
|
|
|
|
|
|
class MaintenanceRecommendation(Base):
|
|
__tablename__ = "maintenance_recommendations"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
vehicle_id: Mapped[int] = mapped_column(ForeignKey("cars.id", ondelete="CASCADE"), index=True)
|
|
recommendation_type: Mapped[str] = mapped_column(String(64), index=True)
|
|
title: Mapped[str] = mapped_column(String(180))
|
|
description: Mapped[str | None] = mapped_column(Text)
|
|
due_odometer_km: Mapped[int | None] = mapped_column(Integer, index=True)
|
|
due_date: Mapped[date | None] = mapped_column(Date, index=True)
|
|
priority: Mapped[str] = mapped_column(String(24), default="medium", server_default="medium", index=True)
|
|
status: Mapped[str] = mapped_column(String(24), default="active", server_default="active", index=True)
|
|
source: Mapped[str] = mapped_column(String(40), default="system_analysis", server_default="system_analysis", index=True)
|
|
source_service_center_id: Mapped[int | None] = mapped_column(ForeignKey("service_centers.id", ondelete="SET NULL"), index=True)
|
|
source_appointment_id: Mapped[int | None] = mapped_column(Integer, index=True)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), index=True)
|
|
updated_at: Mapped[datetime] = mapped_column(
|
|
DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
|
|
)
|
|
|
|
|
|
class ServiceAppointment(Base):
|
|
__tablename__ = "service_appointments"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
service_center_id: Mapped[int] = mapped_column(ForeignKey("service_centers.id", ondelete="CASCADE"), index=True)
|
|
vehicle_id: Mapped[int] = mapped_column(ForeignKey("cars.id", ondelete="CASCADE"), index=True)
|
|
owner_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), index=True)
|
|
created_by: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), index=True)
|
|
service_type: Mapped[str] = mapped_column(String(64), index=True)
|
|
service_name: Mapped[str] = mapped_column(String(180))
|
|
requested_start_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), index=True)
|
|
requested_end_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), index=True)
|
|
confirmed_start_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), index=True)
|
|
confirmed_end_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
proposed_start_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), index=True)
|
|
proposed_end_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
estimated_duration_minutes: Mapped[int] = mapped_column(Integer, default=60, server_default="60")
|
|
status: Mapped[str] = mapped_column(String(40), default="requested", server_default="requested", index=True)
|
|
customer_comment: Mapped[str | None] = mapped_column(Text)
|
|
service_center_comment: Mapped[str | None] = mapped_column(Text)
|
|
source_recommendation_id: Mapped[int | None] = mapped_column(
|
|
ForeignKey("maintenance_recommendations.id", ondelete="SET NULL"), index=True
|
|
)
|
|
linked_work_order_id: Mapped[int | None] = mapped_column(ForeignKey("service_visits.id", ondelete="SET NULL"), index=True)
|
|
cancellation_reason: Mapped[str | None] = mapped_column(Text)
|
|
cancelled_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), index=True)
|
|
updated_at: Mapped[datetime] = mapped_column(
|
|
DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
|
|
)
|
|
|
|
service_center = relationship("ServiceCenter", back_populates="appointments")
|
|
|
|
|
|
class ServiceNotification(Base):
|
|
__tablename__ = "service_notifications"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
recipient_user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), index=True)
|
|
service_center_id: Mapped[int | None] = mapped_column(ForeignKey("service_centers.id", ondelete="SET NULL"), index=True)
|
|
appointment_id: Mapped[int | None] = mapped_column(ForeignKey("service_appointments.id", ondelete="SET NULL"), index=True)
|
|
notification_type: Mapped[str] = mapped_column(String(80), index=True)
|
|
title: Mapped[str] = mapped_column(String(180))
|
|
body: Mapped[str | None] = mapped_column(Text)
|
|
status: Mapped[str] = mapped_column(String(24), default="pending", server_default="pending", index=True)
|
|
retry_count: Mapped[int] = mapped_column(Integer, default=0, server_default="0")
|
|
last_error: Mapped[str | None] = mapped_column(Text)
|
|
idempotency_key: Mapped[str | None] = mapped_column(String(160), unique=True, index=True)
|
|
sent_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
read_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), index=True)
|
|
|
|
|
|
class AdminNotification(Base):
|
|
__tablename__ = "admin_notifications"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
event_type: Mapped[str] = mapped_column(String(80), index=True)
|
|
severity: Mapped[str] = mapped_column(String(24), default="info", server_default="info", index=True)
|
|
title: Mapped[str] = mapped_column(String(180))
|
|
body: Mapped[str | None] = mapped_column(Text)
|
|
entity_type: Mapped[str | None] = mapped_column(String(80), index=True)
|
|
entity_id: Mapped[str | None] = mapped_column(String(80), index=True)
|
|
status: Mapped[str] = mapped_column(String(24), default="unread", server_default="unread", index=True)
|
|
idempotency_key: Mapped[str] = mapped_column(String(180), unique=True, index=True)
|
|
metadata_json: Mapped[dict | None] = mapped_column(JSON)
|
|
telegram_status: Mapped[str] = mapped_column(String(24), default="pending", server_default="pending", index=True)
|
|
telegram_error: Mapped[str | None] = mapped_column(Text)
|
|
read_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
dismissed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), index=True)
|
|
updated_at: Mapped[datetime] = mapped_column(
|
|
DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
|
|
)
|
|
|
|
|
|
class OCRResult(Base):
|
|
__tablename__ = "ocr_results"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), index=True)
|
|
vehicle_id: Mapped[int | None] = mapped_column(ForeignKey("cars.id", ondelete="SET NULL"), index=True)
|
|
scope: Mapped[str] = mapped_column(String(80), index=True)
|
|
filename: Mapped[str | None] = mapped_column(String(255))
|
|
content_type: Mapped[str | None] = mapped_column(String(120))
|
|
status: Mapped[str] = mapped_column(String(24), default="preview", server_default="preview", index=True)
|
|
provider: Mapped[str | None] = mapped_column(String(80))
|
|
confidence: Mapped[Decimal | None] = mapped_column(Numeric(5, 4))
|
|
recognized_text: Mapped[str | None] = mapped_column(Text)
|
|
candidates_json: Mapped[list | None] = mapped_column(JSON)
|
|
error: Mapped[str | None] = mapped_column(Text)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), index=True)
|
|
|
|
|
|
class ServiceWorkItem(Base):
|
|
__tablename__ = "service_work_items"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
service_visit_id: Mapped[int] = mapped_column(ForeignKey("service_visits.id", ondelete="CASCADE"), index=True)
|
|
work_type: Mapped[str] = mapped_column(String(40), default="other", server_default="other", index=True)
|
|
title: Mapped[str] = mapped_column(String(180))
|
|
category: Mapped[str | None] = mapped_column(String(80))
|
|
description: Mapped[str | None] = mapped_column(Text)
|
|
quantity: Mapped[Decimal] = mapped_column(Numeric(10, 3), default=1, server_default="1")
|
|
unit: Mapped[str] = mapped_column(String(24), default="pcs", server_default="pcs")
|
|
unit_price: Mapped[Decimal | None] = mapped_column(Numeric(12, 2))
|
|
discount: Mapped[Decimal] = mapped_column(Numeric(12, 2), default=0, server_default="0")
|
|
total: Mapped[Decimal | None] = mapped_column(Numeric(12, 2))
|
|
parts: Mapped[list | None] = mapped_column(JSON)
|
|
oil_brand: Mapped[str | None] = mapped_column(String(80))
|
|
oil_viscosity: Mapped[str | None] = mapped_column(String(40))
|
|
oil_volume: Mapped[Decimal | None] = mapped_column(Numeric(5, 2))
|
|
next_due_odometer: Mapped[int | None]
|
|
next_due_date: Mapped[date | None] = mapped_column(Date)
|
|
price: Mapped[Decimal | None] = mapped_column(Numeric(12, 2))
|
|
warranty_days: Mapped[int | None] = mapped_column(Integer)
|
|
warranty_odometer_km: Mapped[int | None] = mapped_column(Integer)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
visit = relationship("ServiceVisit", back_populates="work_items")
|
|
|
|
|
|
class ServiceProductItem(Base):
|
|
__tablename__ = "service_product_items"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
service_visit_id: Mapped[int] = mapped_column(ForeignKey("service_visits.id", ondelete="CASCADE"), index=True)
|
|
title: Mapped[str] = mapped_column(String(180))
|
|
category: Mapped[str | None] = mapped_column(String(80), index=True)
|
|
product_type: Mapped[str] = mapped_column(String(40), default="other", server_default="other", index=True)
|
|
brand: Mapped[str | None] = mapped_column(String(80))
|
|
sku: Mapped[str | None] = mapped_column(String(120))
|
|
quantity: Mapped[Decimal] = mapped_column(Numeric(10, 3), default=1, server_default="1")
|
|
unit: Mapped[str] = mapped_column(String(24), default="pcs", server_default="pcs")
|
|
unit_price: Mapped[Decimal] = mapped_column(Numeric(12, 2), default=0, server_default="0")
|
|
discount: Mapped[Decimal] = mapped_column(Numeric(12, 2), default=0, server_default="0")
|
|
total: Mapped[Decimal] = mapped_column(Numeric(12, 2), default=0, server_default="0")
|
|
volume: Mapped[Decimal | None] = mapped_column(Numeric(8, 3))
|
|
viscosity: Mapped[str | None] = mapped_column(String(40))
|
|
specification: Mapped[str | None] = mapped_column(String(120))
|
|
used_volume: Mapped[Decimal | None] = mapped_column(Numeric(8, 3))
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
visit = relationship("ServiceVisit", back_populates="product_items")
|
|
|
|
|
|
class WorkOrderStatusHistory(Base):
|
|
__tablename__ = "work_order_status_history"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
service_visit_id: Mapped[int] = mapped_column(ForeignKey("service_visits.id", ondelete="CASCADE"), index=True)
|
|
from_status: Mapped[str | None] = mapped_column(String(40))
|
|
to_status: Mapped[str] = mapped_column(String(40), index=True)
|
|
changed_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), index=True)
|
|
comment: Mapped[str | None] = mapped_column(Text)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), index=True)
|
|
|
|
visit = relationship("ServiceVisit", back_populates="status_history")
|
|
|
|
|
|
class WorkOrderCorrection(Base):
|
|
__tablename__ = "work_order_corrections"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
service_visit_id: Mapped[int] = mapped_column(ForeignKey("service_visits.id", ondelete="CASCADE"), index=True)
|
|
requested_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), index=True)
|
|
reason: Mapped[str] = mapped_column(Text)
|
|
proposed_changes: Mapped[dict | None] = mapped_column(JSON)
|
|
status: Mapped[str] = mapped_column(String(24), default="pending", server_default="pending", index=True)
|
|
owner_approval_required: Mapped[bool] = mapped_column(Boolean, default=True, server_default="true")
|
|
created_version: Mapped[int] = mapped_column(Integer, default=1, server_default="1")
|
|
resolved_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), index=True)
|
|
|
|
visit = relationship("ServiceVisit", back_populates="corrections")
|
|
|
|
|
|
class InventoryTransaction(Base):
|
|
__tablename__ = "inventory_transactions"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
service_center_id: Mapped[int] = mapped_column(ForeignKey("service_centers.id", ondelete="CASCADE"), index=True)
|
|
service_visit_id: Mapped[int | None] = mapped_column(ForeignKey("service_visits.id", ondelete="SET NULL"), index=True)
|
|
product_item_id: Mapped[int | None] = mapped_column(ForeignKey("service_product_items.id", ondelete="SET NULL"), index=True)
|
|
transaction_type: Mapped[str] = mapped_column(String(32), index=True)
|
|
sku: Mapped[str | None] = mapped_column(String(120), index=True)
|
|
title: Mapped[str | None] = mapped_column(String(180))
|
|
quantity: Mapped[Decimal] = mapped_column(Numeric(10, 3), default=0, server_default="0")
|
|
unit: Mapped[str] = mapped_column(String(24), default="pcs", server_default="pcs")
|
|
actor_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), index=True)
|
|
metadata_json: Mapped[dict | None] = mapped_column(JSON)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), index=True)
|
|
|
|
|
|
class WorkOrderCatalogItem(Base):
|
|
__tablename__ = "work_order_catalog_items"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
service_center_id: Mapped[int | None] = mapped_column(ForeignKey("service_centers.id", ondelete="CASCADE"), index=True)
|
|
item_type: Mapped[str] = mapped_column(String(24), index=True)
|
|
title: Mapped[str] = mapped_column(String(180), index=True)
|
|
category: Mapped[str | None] = mapped_column(String(80), index=True)
|
|
description: Mapped[str | None] = mapped_column(Text)
|
|
work_type: Mapped[str | None] = mapped_column(String(40), index=True)
|
|
product_type: Mapped[str | None] = mapped_column(String(40), index=True)
|
|
brand: Mapped[str | None] = mapped_column(String(80))
|
|
sku: Mapped[str | None] = mapped_column(String(120), index=True)
|
|
unit: Mapped[str] = mapped_column(String(24), default="pcs", server_default="pcs")
|
|
default_quantity: Mapped[Decimal] = mapped_column(Numeric(10, 3), default=1, server_default="1")
|
|
default_unit_price: Mapped[Decimal] = mapped_column(Numeric(12, 2), default=0, server_default="0")
|
|
volume: Mapped[Decimal | None] = mapped_column(Numeric(8, 3))
|
|
viscosity: Mapped[str | None] = mapped_column(String(40))
|
|
specification: Mapped[str | None] = mapped_column(String(120))
|
|
metadata_json: Mapped[dict | None] = mapped_column(JSON)
|
|
is_active: Mapped[bool] = mapped_column(Boolean, default=True, server_default="true", index=True)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), index=True)
|
|
updated_at: Mapped[datetime] = mapped_column(
|
|
DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
|
|
)
|
|
|
|
service_center = relationship("ServiceCenter", back_populates="catalog_items")
|
|
|
|
|
|
class ServiceCenterReview(Base):
|
|
__tablename__ = "service_center_reviews"
|
|
__table_args__ = (UniqueConstraint("service_center_id", "user_id", name="uq_service_review_user"),)
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
service_center_id: Mapped[int] = mapped_column(ForeignKey("service_centers.id", ondelete="CASCADE"), index=True)
|
|
user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), index=True)
|
|
rating: Mapped[int] = mapped_column(Integer)
|
|
text: Mapped[str | None] = mapped_column(Text)
|
|
photo_urls: Mapped[list | None] = mapped_column(JSON)
|
|
status: Mapped[str] = mapped_column(String(24), default="published", server_default="published", index=True)
|
|
service_response: Mapped[str | None] = mapped_column(Text)
|
|
service_responded_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), index=True)
|
|
updated_at: Mapped[datetime] = mapped_column(
|
|
DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
|
|
)
|
|
|
|
service_center = relationship("ServiceCenter", back_populates="reviews")
|
|
comments = relationship("ServiceCenterReviewComment", back_populates="review", cascade="all, delete-orphan")
|
|
|
|
|
|
class ServiceCenterReviewComment(Base):
|
|
__tablename__ = "service_center_review_comments"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
review_id: Mapped[int] = mapped_column(ForeignKey("service_center_reviews.id", ondelete="CASCADE"), index=True)
|
|
user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), index=True)
|
|
text: Mapped[str] = mapped_column(Text)
|
|
status: Mapped[str] = mapped_column(String(24), default="published", server_default="published", index=True)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), index=True)
|
|
|
|
review = relationship("ServiceCenterReview", back_populates="comments")
|
|
|
|
|
|
class VehicleDataChangeRequest(Base):
|
|
__tablename__ = "vehicle_data_change_requests"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
vehicle_id: Mapped[int] = mapped_column(ForeignKey("cars.id", ondelete="CASCADE"), index=True)
|
|
requested_by_service_center_id: Mapped[int | None] = mapped_column(ForeignKey("service_centers.id", ondelete="SET NULL"), index=True)
|
|
requested_by_employee_id: Mapped[int | None] = mapped_column(ForeignKey("service_employees.id", ondelete="SET NULL"), index=True)
|
|
field_name: Mapped[str] = mapped_column(String(80), index=True)
|
|
old_value: Mapped[str | None] = mapped_column(Text)
|
|
new_value: Mapped[str | None] = mapped_column(Text)
|
|
status: Mapped[str] = mapped_column(String(24), default="pending", server_default="pending", index=True)
|
|
owner_user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), index=True)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
|
|
resolved_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
|
|
|
|
class AuditLog(Base):
|
|
__tablename__ = "audit_logs"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
actor_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), index=True)
|
|
actor_role: Mapped[str | None] = mapped_column(String(64))
|
|
action: Mapped[str] = mapped_column(String(120), index=True)
|
|
target_type: Mapped[str] = mapped_column(String(80), index=True)
|
|
target_id: Mapped[str | None] = mapped_column(String(80), index=True)
|
|
ip: Mapped[str | None] = mapped_column(String(64))
|
|
user_agent: Mapped[str | None] = mapped_column(String(256))
|
|
metadata_json: Mapped[dict | None] = mapped_column(JSON)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), index=True)
|
|
|
|
|
|
class AdminExportJob(Base):
|
|
__tablename__ = "admin_export_jobs"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
requested_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), index=True)
|
|
source: Mapped[str] = mapped_column(String(80), index=True)
|
|
export_format: Mapped[str] = mapped_column(String(16), default="json", server_default="json")
|
|
status: Mapped[str] = mapped_column(String(24), default="ready", server_default="ready", index=True)
|
|
reason: Mapped[str | None] = mapped_column(Text)
|
|
filters_json: Mapped[dict | None] = mapped_column(JSON)
|
|
result_text: Mapped[str | None] = mapped_column(Text)
|
|
row_count: Mapped[int] = mapped_column(Integer, default=0, server_default="0")
|
|
expires_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), index=True)
|