import uuid from datetime import datetime, timezone from sqlalchemy import Column, String, Boolean, DateTime, Date, Time, Text, Integer, SmallInteger, Numeric, ForeignKey, UniqueConstraint from sqlalchemy.dialects.postgresql import UUID from app.core.database import Base def now_utc(): return datetime.now(timezone.utc) class YardSlotMaster(Base): """Physical parking bays in the 32-acre yard. Tracks occupancy.""" __tablename__ = 'yard_slot_master' slot_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) slot_code = Column(String(20), unique=True, nullable=False) zone = Column(String(5)) slot_type = Column(String(20), default='STANDARD') is_occupied = Column(Boolean, nullable=False, default=False) current_chassis = Column(String(30)) notes = Column(Text) is_active = Column(Boolean, nullable=False, default=True) created_at = Column(DateTime(timezone=True), default=now_utc) class YardCheckpointMaster(Base): """ The named stops a chassis physically passes through (34 Acre Yard, Unit-2 Gate Receiving, Audi Main Gate Receipt, Bus Dispatched, etc). Editable so new stations can be added without a code change. """ __tablename__ = 'yard_checkpoint_master' checkpoint_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) checkpoint_code = Column(String(50), unique=True, nullable=False) checkpoint_name = Column(String(150), nullable=False) sequence_no = Column(Integer, nullable=False, default=0) is_active = Column(Boolean, nullable=False, default=True) created_at = Column(DateTime(timezone=True), default=now_utc) class MovementChecklistItemMaster(Base): """The itemized checks on the Chassis Movement Check Sheet (Invoice, Horn, ECCO switch, etc).""" __tablename__ = 'movement_checklist_item_master' item_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) item_code = Column(String(50), unique=True, nullable=False) item_label = Column(String(150), nullable=False) sequence_no = Column(Integer, nullable=False, default=0) is_active = Column(Boolean, nullable=False, default=True) class YardQaChecklistItemMaster(Base): """ The Yard QA / FVI inspection checklist (Bus Interior Cluster Meter, Steering function, Brake functionality, etc, each with multiple checkpoints). The exact same list is used at Yard inward time and again at Final Vehicle Inspection. """ __tablename__ = 'yard_qa_checklist_item_master' item_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) zone_name = Column(String(150), nullable=False) checkpoint_text = Column(Text, nullable=False) sequence_no = Column(Integer, nullable=False, default=0) is_active = Column(Boolean, nullable=False, default=True) class VehicleModelMaster(Base): """ The plain vehicle model numbers (2050 D, 2075 H CNG, 3016 R, ...) the yard picks from on chassis intake. Body type / variant configuration is a production-side concern; this table only exists to give the yard a clean master to select from. """ __tablename__ = 'vehicle_model_master' model_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) model_number = Column(String(100), unique=True, nullable=False) image_url = Column(String(255), nullable=True) is_active = Column(Boolean, nullable=False, default=True) created_at = Column(DateTime(timezone=True), default=now_utc) class ChassisMaster(Base): """ The chassis itself, as a first-class trackable entity — from yard inward through handover to production. Previously this only existed as a free-text field on build_order; this is the real record. """ __tablename__ = 'chassis_master' chassis_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) chassis_no = Column(String(100), unique=True, nullable=False) model_id = Column(UUID(as_uuid=True), ForeignKey('vehicle_model_master.model_id'), nullable=True) fuel_type = Column(String(20), nullable=False, default='DIESEL') # DIESEL / CNG — determines the unit light_type = Column(String(30), nullable=True) # SQUARE_LIGHT / ROUND_LIGHT # fuel_receiving_qty is recorded in: Litres for DIESEL, kg for CNG. voltage = Column(String(20)) transport_id = Column(String(50), ForeignKey('transport_master.transport_id'), nullable=True) dealer_id = Column(String(50), ForeignKey('customer_master.customer_id'), nullable=True) client_id = Column(String(50), ForeignKey('client_master.client_id'), nullable=True) source_type = Column(String(20), nullable=False, default='SALES_ORDER') # SALES_ORDER / SPARE_STOCK build_order_id = Column(UUID(as_uuid=True), nullable=True) dealer_order_vehicle_id = Column(UUID(as_uuid=True), ForeignKey('dealer_order_vehicle.order_vehicle_id'), nullable=True) from sqlalchemy.orm import relationship # build_order = relationship('BuildOrder', foreign_keys=[build_order_id]) dealer_order_vehicle = relationship('DealerOrderVehicle', foreign_keys=[dealer_order_vehicle_id]) model = relationship('VehicleModelMaster', foreign_keys=[model_id]) fuel_receiving_qty = Column(Numeric(10, 2)) arrival_date = Column(Date, nullable=False) arrival_remark = Column(Text, nullable=True) yard_location = Column(String(100), nullable=True) # '34 Acre Yard' or 'Tarpura Yard' da_lite_status = Column(String(20), nullable=False, default='PENDING') # PENDING / OK / HOLD da_lite_checked_at = Column(DateTime(timezone=True)) da_lite_checked_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True) da_lite_photo_url = Column(Text, nullable=True) da_lite_remarks = Column(Text, nullable=True) current_status = Column(String(30), nullable=False, default='YARD_PENDING_CHECK') procurement_status = Column(String(30), nullable=False, default='PENDING') # YARD_PENDING_CHECK -> YARD_CHECKED -> HANDED_TO_PRODUCTION -> DISPATCHED # QR tracking status — separate from yard workflow statuses # CHASSIS_IN -> WIP -> DISPATCHED tracking_status = Column(String(20), nullable=False, default='CHASSIS_IN') dispatch_photo_url = Column(Text, nullable=True) dispatched_at = Column(DateTime(timezone=True), nullable=True) handed_to_production_at = Column(DateTime(timezone=True)) handed_to_production_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True) scheduled_induction_date = Column(Date, nullable=True) scheduled_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True) scheduled_at = Column(DateTime(timezone=True), nullable=True) remarks = Column(Text) created_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True) created_at = Column(DateTime(timezone=True), default=now_utc) updated_at = Column(DateTime(timezone=True), default=now_utc, onupdate=now_utc) # ── Extended fields (Phase 1 migration) ─────────────── driver_name = Column(String(150)) driver_license = Column(String(50)) driver_phone = Column(String(20)) inbound_vehicle_no = Column(String(20)) chassis_condition = Column(String(20), default='OK') # OK / MINOR_DAMAGE / MAJOR_DAMAGE chassis_odometer = Column(Integer) inspection_done = Column(Boolean, default=False) inspection_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id')) inspection_at = Column(DateTime(timezone=True)) yard_slot_id = Column(UUID(as_uuid=True), ForeignKey('yard_slot_master.slot_id')) enquiry_id = Column(UUID(as_uuid=True), ForeignKey('sales_enquiry.enquiry_id')) oem = Column(String(50)) # EICHER / TATA / ASHOK LEYLAND year_of_manufacture = Column(SmallInteger) transport_company = Column(String(100)) build_spec_id = Column(UUID(as_uuid=True), nullable=True) current_location_id = Column(UUID(as_uuid=True), ForeignKey('chassis_location_master.location_id'), nullable=True) current_station_id = Column(UUID(as_uuid=True), ForeignKey('station_master.station_id'), nullable=True) # Production tracking fields production_hold_status = Column(String(20), nullable=False, default='NONE') # NONE / ON_HOLD / RELEASED station_arrived_at = Column(DateTime(timezone=True), nullable=True) station_completed_at = Column(DateTime(timezone=True), nullable=True) class ChassisMovementLog(Base): """One row per (chassis, checkpoint) — the Fuel Data Sheet + Movement Check Sheet header per stop.""" __tablename__ = 'chassis_movement_log' movement_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) chassis_id = Column(UUID(as_uuid=True), ForeignKey('chassis_master.chassis_id'), nullable=False) checkpoint_id = Column(UUID(as_uuid=True), ForeignKey('yard_checkpoint_master.checkpoint_id'), nullable=False) responsible_person = Column(String(150)) movement_date = Column(Date) in_time = Column(Time) out_time = Column(Time) fuel_qty = Column(Numeric(10, 2)) remark = Column(Text) signature_name = Column(String(150)) created_at = Column(DateTime(timezone=True), default=now_utc) class ChassisMovementChecklistResult(Base): """The itemized OK/not-OK checks at a given movement stop (Invoice, Horn, ECCO switch...).""" __tablename__ = 'chassis_movement_checklist_result' result_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) movement_id = Column(UUID(as_uuid=True), ForeignKey('chassis_movement_log.movement_id'), nullable=False) item_id = Column(UUID(as_uuid=True), ForeignKey('movement_checklist_item_master.item_id'), nullable=False) is_ok = Column(Boolean) value_text = Column(String(100)) # for checks recorded as a value rather than yes/no (e.g. quantity "4") class ChassisQaChecklistResult(Base): """ One row per (chassis, QA checklist item). The same row accumulates remarks across the chassis's life: filled at Yard, then again at Final Vehicle Inspection (FVI). Each stage has a tick (_ok) for the fast "OK" path, plus a free-text remark for anything else (defect notes, rework detail, etc). """ __tablename__ = 'chassis_qa_checklist_result' result_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) chassis_id = Column(UUID(as_uuid=True), ForeignKey('chassis_master.chassis_id'), nullable=False) item_id = Column(UUID(as_uuid=True), ForeignKey('yard_qa_checklist_item_master.item_id'), nullable=False) yard_ok = Column(Boolean) yard_remark = Column(String(255)) bhagirath_ok = Column(Boolean) bhagirath_remark = Column(String(255)) fvi_ok = Column(Boolean) fvi_remark = Column(String(255)) fvi_rework_ok = Column(Boolean) fvi_rework_remark = Column(String(255)) fvi_rework_pending_ok = Column(Boolean) fvi_rework_pending_remark = Column(String(255)) updated_at = Column(DateTime(timezone=True), default=now_utc, onupdate=now_utc) class StoreHandoverPartMaster(Base): """ The store handover checklist — parts confirmed present when a chassis moves from the yard to the store/production, per the physical "Store - Part Handover from chassis yard check sheet". Editable so items can be added without a code change. """ __tablename__ = 'store_handover_part_master' part_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) sr_no = Column(Integer) part_name = Column(String(150), nullable=False) fuel_applicability = Column(String(20), nullable=False, default='ALL') # ALL / DIESEL / CNG is_active = Column(Boolean, nullable=False, default=True) created_at = Column(DateTime(timezone=True), default=now_utc) class ChassisStoreHandover(Base): """One handover record per chassis — the header/signatures of the store handover sheet.""" __tablename__ = 'chassis_store_handover' handover_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) chassis_id = Column(UUID(as_uuid=True), ForeignKey('chassis_master.chassis_id'), unique=True, nullable=False) store_keeper_id = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True) handover_date = Column(Date) is_locked = Column(Boolean, nullable=False, default=False) signed_store_person = Column(Boolean, nullable=False, default=False) signed_yard_supervisor = Column(Boolean, nullable=False, default=False) # Store Manager Approval store_manager_approved_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True) store_manager_approved_at = Column(DateTime(timezone=True), nullable=True) # Yard Supervisor Approval yard_supervisor_approved_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True) yard_supervisor_approved_at = Column(DateTime(timezone=True), nullable=True) created_at = Column(DateTime(timezone=True), default=now_utc) class ChassisStoreHandoverPart(Base): """The itemized part-by-part status (OK / NOT_OK / NA) + quantity for a handover.""" __tablename__ = 'chassis_store_handover_part' result_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) handover_id = Column(UUID(as_uuid=True), ForeignKey('chassis_store_handover.handover_id'), nullable=False) part_id = Column(UUID(as_uuid=True), ForeignKey('store_handover_part_master.part_id'), nullable=False) status = Column(String(10)) # OK / NOT_OK / NA qty = Column(Integer) created_at = Column(DateTime(timezone=True), default=now_utc) __table_args__ = (UniqueConstraint('handover_id', 'part_id', name='uq_handover_part'),) class ChassisStatusLog(Base): """ Audit trail for QR-based tracking status transitions. Every scan (at Yard, Production, or Dispatch) creates one row. Powers the timeline view on the public tracking page. """ __tablename__ = 'chassis_status_log' log_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) chassis_id = Column(UUID(as_uuid=True), ForeignKey('chassis_master.chassis_id'), nullable=False) station_id = Column(UUID(as_uuid=True), ForeignKey('station_master.station_id'), nullable=True) station_code = Column(String(50), nullable=True) scan_mode = Column(String(20), default='MANUAL_ENTRY', nullable=True) # QR_SCAN / MANUAL_ENTRY from_status = Column(String(20), nullable=True) # null for initial creation to_status = Column(String(20), nullable=False) changed_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True) changed_at = Column(DateTime(timezone=True), default=now_utc, nullable=False) scan_location = Column(String(150)) # YARD / PRODUCTION / DISPATCH / station name remarks = Column(Text) class ChassisArrivalDocument(Base): """E-Way Bill, Insurance, Driver Challan — uploaded and verified at yard entry.""" __tablename__ = 'chassis_arrival_document' doc_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) chassis_id = Column(UUID(as_uuid=True), ForeignKey('chassis_master.chassis_id'), nullable=False) document_type = Column(String(30), nullable=False) # E_WAY_BILL | INSURANCE | DRIVER_CHALLAN document_number = Column(String(100)) issue_date = Column(Date) valid_until = Column(Date) issuing_authority = Column(String(150)) file_path = Column(String(500)) verification_status = Column(String(20), nullable=False, default='RECEIVED') # RECEIVED | VERIFIED | REJECTED rejection_reason = Column(Text) verified_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id')) verified_at = Column(DateTime(timezone=True)) created_at = Column(DateTime(timezone=True), default=now_utc) class CustomerSuppliedPart(Base): """Parts sent by the customer with the chassis (FAPS, AC, seats, etc).""" __tablename__ = 'customer_supplied_part' csp_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) chassis_id = Column(UUID(as_uuid=True), ForeignKey('chassis_master.chassis_id'), nullable=False) rm_id = Column(UUID(as_uuid=True), ForeignKey('rm_master.rm_id'), nullable=True) item_description = Column(String(255), nullable=False) item_code = Column(String(100)) quantity_received = Column(Numeric(14, 3), nullable=False) quantity_verified = Column(Numeric(14, 3)) unit_of_measurement = Column(String(50)) target_station = Column(String(30)) storage_location = Column(String(100)) status = Column(String(25), nullable=False, default='RECEIVED') # RECEIVED | VERIFIED | STORED | ISSUED | RETURNED | DAMAGED damaged_qty = Column(Numeric(14, 3)) damage_description = Column(Text) issued_to_station = Column(String(30)) issued_at = Column(DateTime(timezone=True)) issued_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id')) received_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id')) received_at = Column(DateTime(timezone=True)) notes = Column(Text) created_at = Column(DateTime(timezone=True), default=now_utc)