from sqlalchemy import Column, String, Float, Integer, Boolean, ForeignKey, Date, DateTime, Text from sqlalchemy.orm import relationship from datetime import datetime from app.core.database import Base from sqlalchemy.dialects.postgresql import UUID import uuid class CustomerTypeMaster(Base): __tablename__ = 'customer_type_master' type_code = Column(String(50), primary_key=True, index=True) # e.g. REGULAR, NEW, SPARE_PARTS, GOODWILL name = Column(String(100), nullable=False) is_active = Column(Boolean, default=True) class CustomerMaster(Base): __tablename__ = 'customer_master' customer_id = Column(String(50), primary_key=True, index=True) # Dealer ID name = Column(String(200), nullable=False) type_code = Column(String(50), ForeignKey('customer_type_master.type_code')) goodwill_reason = Column(Text, nullable=True) # Only if type_code == 'GOODWILL' gstin = Column(String(50), nullable=True) pan = Column(String(50), nullable=True) billing_address = Column(Text, nullable=True) shipping_address = Column(Text, nullable=True) city = Column(String(100), nullable=True) state = Column(String(100), nullable=True) pincode = Column(String(20), nullable=True) region = Column(String(50), nullable=True) # North, South, East, West, Central is_active = Column(Boolean, default=True) portal_enabled = Column(Boolean, nullable=False, default=False) allow_chassis_tracking = Column(Boolean, nullable=False, default=False) portal_username = Column(String(100), unique=True) portal_password_hash = Column(String(255)) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) contacts = relationship('CustomerContact', back_populates='customer') customer_type = relationship('CustomerTypeMaster') class TransportMaster(Base): __tablename__ = 'transport_master' transport_id = Column(String(50), primary_key=True, index=True) name = Column(String(200), nullable=False) contact_person = Column(String(100)) contact_number = Column(String(20)) vehicle_type = Column(String(50)) active = Column(Boolean, default=True) created_at = Column(DateTime, default=datetime.utcnow) class SalesEnquiry(Base): __tablename__ = 'sales_enquiry' enquiry_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) customer_id = Column(String(50), ForeignKey('customer_master.customer_id'), nullable=False) request_details = Column(Text, nullable=False) status = Column(String(20), nullable=False, default='NEW') # NEW, ASSIGNED, QUOTED, REJECTED created_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=False) assigned_to = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Bulk order tracking (Phase 1 migration) total_chassis_ordered = Column(Integer, default=1) chassis_arrived_count = Column(Integer, default=0) expected_arrival_start = Column(Date, nullable=True) expected_arrival_end = Column(Date, nullable=True) customer = relationship('CustomerMaster') creator = relationship('LoginUsers', foreign_keys=[created_by]) assignee = relationship('LoginUsers', foreign_keys=[assigned_to]) class SparePartsOrder(Base): __tablename__ = 'spare_parts_order' order_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) customer_id = Column(String(50), ForeignKey('customer_master.customer_id'), nullable=False) total_amount = Column(Float, nullable=False, default=0.0) payment_status = Column(String(20), nullable=False, default='PENDING') # PENDING, PAID status = Column(String(20), nullable=False, default='DRAFT') # DRAFT, ISSUED created_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=False) created_at = Column(DateTime, default=datetime.utcnow) customer = relationship('CustomerMaster') creator = relationship('LoginUsers') details = relationship('SparePartsOrderDetail', back_populates='order') class SparePartsOrderDetail(Base): __tablename__ = 'spare_parts_order_detail' detail_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) order_id = Column(UUID(as_uuid=True), ForeignKey('spare_parts_order.order_id'), nullable=False) rm_id = Column(UUID(as_uuid=True), ForeignKey('rm_master.rm_id'), nullable=False) qty = Column(Float, nullable=False) unit_price = Column(Float, nullable=False) line_amount = Column(Float, nullable=False) order = relationship('SparePartsOrder', back_populates='details') rm = relationship('RmMaster') class ServiceOrder(Base): __tablename__ = 'service_order' service_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) order_id = Column(UUID(as_uuid=True), ForeignKey('dealer_order.order_id'), nullable=True) customer_id = Column(String(50), ForeignKey('customer_master.customer_id'), nullable=False) chassis_no = Column(String(100), nullable=True) service_type = Column(String(50), nullable=False) # FAPS_FITMENT, REFURBISHMENT, REPAIR status = Column(String(20), nullable=False, default='PENDING') # PENDING, IN_PROGRESS, COMPLETED notes = Column(Text, nullable=True) cost = Column(Float, nullable=False, default=0.0) created_at = Column(DateTime, default=datetime.utcnow) customer = relationship('CustomerMaster') class CustomerPaymentLog(Base): __tablename__ = 'customer_payment_log' payment_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) customer_id = Column(String(50), ForeignKey('customer_master.customer_id'), nullable=False) order_id = Column(UUID(as_uuid=True), ForeignKey('dealer_order.order_id'), nullable=True) amount = Column(Float, nullable=False) payment_mode = Column(String(50), nullable=False) # BANK_TRANSFER, CASH, CHEQUE reference_no = Column(String(100), nullable=True) status = Column(String(20), nullable=False, default='PENDING') # PENDING, CONFIRMED recorded_by = Column(UUID(as_uuid=True), nullable=False) confirmed_by = Column(UUID(as_uuid=True), nullable=True) created_at = Column(DateTime, default=datetime.utcnow) customer = relationship('CustomerMaster') class GoodwillApprovalLog(Base): __tablename__ = 'goodwill_approval_log' approval_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) entity_type = Column(String(50), nullable=False) # QUOTE, SPARE_PARTS, SERVICE entity_id = Column(UUID(as_uuid=True), nullable=False) requested_by = Column(UUID(as_uuid=True), nullable=False) reason = Column(Text, nullable=False) status = Column(String(20), nullable=False, default='PENDING') # PENDING, APPROVED, REJECTED manager_id = Column(UUID(as_uuid=True), nullable=True) manager_notes = Column(Text, nullable=True) created_at = Column(DateTime, default=datetime.utcnow) class CustomerContact(Base): __tablename__ = 'customer_contact' contact_id = Column(String(50), primary_key=True, index=True) customer_id = Column(String(50), ForeignKey('customer_master.customer_id'), nullable=False) name = Column(String(100), nullable=False) designation = Column(String(100)) email = Column(String(100)) phone = Column(String(20)) is_primary = Column(Boolean, default=False) is_active = Column(Boolean, default=True) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) customer = relationship('CustomerMaster', back_populates='contacts') class SalesActivityLog(Base): __tablename__ = 'sales_activity_log' log_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) entity_type = Column(String(50), nullable=False) entity_id = Column(UUID(as_uuid=True), nullable=False) activity_type = Column(String(50), nullable=False) old_value = Column(Text) new_value = Column(Text) remarks = Column(Text) performed_by = Column(UUID(as_uuid=True)) created_at = Column(DateTime, default=datetime.utcnow) class DaLiteHoldLog(Base): __tablename__ = 'da_lite_hold_log' log_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) order_id = Column(UUID(as_uuid=True), ForeignKey('dealer_order.order_id'), nullable=False) action = Column(String(50), nullable=False) reason = Column(Text, nullable=False) performed_by = Column(UUID(as_uuid=True)) created_at = Column(DateTime, default=datetime.utcnow) class PaymentReceipt(Base): __tablename__ = 'payment_receipt' payment_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) customer_id = Column(String(50), ForeignKey('customer_master.customer_id')) order_id = Column(UUID(as_uuid=True), ForeignKey('dealer_order.order_id'), nullable=True) amount = Column(Float, nullable=False) payment_mode = Column(String(20)) # BANK_TRANSFER, CHEQUE, CASH reference_no = Column(String(100)) status = Column(String(20), default='PENDING') # PENDING, CONFIRMED, REJECTED recorded_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id')) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) class ClientMaster(Base): __tablename__ = 'client_master' client_id = Column(String(50), primary_key=True) name = Column(String(200), nullable=False) contact_person = Column(String(150)) phone = Column(String(20)) email = Column(String(150)) address = Column(Text) city = Column(String(100)) state = Column(String(100)) is_active = Column(Boolean, nullable=False, default=True) created_by_user_id = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True) created_at = Column(DateTime(timezone=True), default=datetime.utcnow) updated_at = Column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow) class DealerClientMap(Base): __tablename__ = 'dealer_client_map' map_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) dealer_id = Column(String(50), ForeignKey('customer_master.customer_id'), nullable=False) client_id = Column(String(50), ForeignKey('client_master.client_id'), nullable=False) created_at = Column(DateTime(timezone=True), default=datetime.utcnow) class SalesVehicleTemplate(Base): """A saved vehicle configuration template for internal sales use.""" __tablename__ = 'sales_vehicle_template' template_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) name = Column(String(100), nullable=False) model_id = Column(UUID(as_uuid=True), ForeignKey('vehicle_model_master.model_id'), nullable=True) body_type_id = Column(UUID(as_uuid=True), ForeignKey('body_type_master.body_type_id'), nullable=True) segment_id = Column(UUID(as_uuid=True), ForeignKey('customer_segment_master.segment_id'), nullable=True) created_by_user_id = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True) created_at = Column(DateTime(timezone=True), default=datetime.utcnow) updated_at = Column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow) features = relationship('SalesVehicleTemplateFeature', backref='template', cascade='all, delete-orphan') class SalesVehicleTemplateFeature(Base): """Special features selected in a saved sales vehicle template.""" __tablename__ = 'sales_vehicle_template_feature' id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) template_id = Column(UUID(as_uuid=True), ForeignKey('sales_vehicle_template.template_id', ondelete='CASCADE'), nullable=False) feature_id = Column(UUID(as_uuid=True), ForeignKey('special_feature_master.feature_id', ondelete='CASCADE'), nullable=False) feature_option_id = Column(UUID(as_uuid=True), ForeignKey('special_feature_option_master.option_id', ondelete='CASCADE'), nullable=True) bool_value = Column(Boolean) text_value = Column(Text) image_url = Column(String(500), nullable=True)