from datetime import datetime, timezone from sqlalchemy import Column, String, Integer, Float, Numeric, Boolean, DateTime, Text, ForeignKey, UniqueConstraint from sqlalchemy.orm import relationship import ulid from app.core.database.db_session import Base from app.models.audit_base import AuditMixin class CrmCustomer(Base, AuditMixin): __tablename__ = "crm_customers" customer_id = Column(String(36), primary_key=True, default=lambda: str(ulid.ULID())) tenant_id = Column(String(36), nullable=False, default="tenant_default", index=True) name = Column(String(255), nullable=False) email = Column(String(255), nullable=True, index=True) phone = Column(String(50), nullable=False, index=True) sales_tier = Column(String(50), nullable=False, default="BRONZE") # BRONZE, SILVER, GOLD, PLATINUM service_tier = Column(String(50), nullable=False, default="STANDARD") # STANDARD, PRIORITY, VIP total_orders_count = Column(Integer, nullable=False, default=0) total_repairs_count = Column(Integer, nullable=False, default=0) total_lifetime_value_inr = Column(Float, nullable=False, default=0.0) is_dual_customer = Column(Boolean, nullable=False, default=False) notes = Column(Text, nullable=True) contacts = relationship("CrmContact", back_populates="customer", cascade="all, delete-orphan") leads = relationship("CrmLead", back_populates="customer", cascade="all, delete-orphan") deals = relationship("CrmDeal", back_populates="customer", cascade="all, delete-orphan") __table_args__ = ( UniqueConstraint("tenant_id", "phone", name="uq_tenant_customer_phone"), ) class CrmContact(Base, AuditMixin): __tablename__ = "crm_contacts" contact_id = Column(String(36), primary_key=True, default=lambda: str(ulid.ULID())) tenant_id = Column(String(36), nullable=False, default="tenant_default", index=True) customer_id = Column(String(36), ForeignKey("crm_customers.customer_id", ondelete="CASCADE"), nullable=False, index=True) address = Column(String(500), nullable=True) city = Column(String(100), nullable=True) state = Column(String(100), nullable=True) pincode = Column(String(20), nullable=True) alternate_phone = Column(String(50), nullable=True) customer = relationship("CrmCustomer", back_populates="contacts") class CrmLead(Base, AuditMixin): __tablename__ = "crm_leads" lead_id = Column(String(36), primary_key=True, default=lambda: str(ulid.ULID())) tenant_id = Column(String(36), nullable=False, default="tenant_default", index=True) customer_id = Column(String(36), ForeignKey("crm_customers.customer_id", ondelete="SET NULL"), nullable=True, index=True) title = Column(String(255), nullable=False) phone = Column(String(50), nullable=True) email = Column(String(255), nullable=True) first_name = Column(String(100), nullable=True) last_name = Column(String(100), nullable=True) source = Column(String(50), nullable=False, default="STOREFRONT") # STOREFRONT, WHATSAPP, WALK_IN, PHONE, SOCIAL status = Column(String(50), nullable=False, default="NEW") # NEW, CONTACTED, QUALIFIED, QUOTE_DRAFT, QUOTE_SENT, CONVERTED, LOST score = Column(Integer, nullable=False, default=10) assigned_user_id = Column(String(36), nullable=True, index=True) notes = Column(Text, nullable=True) # Structured Quotation Fields quote_amount = Column(Numeric(12, 2), nullable=True) quote_title = Column(String(255), nullable=True) quote_terms = Column(Text, nullable=True) quote_sent_at = Column(DateTime, nullable=True) quote_sent_by = Column(String(36), nullable=True, index=True) customer = relationship("CrmCustomer", back_populates="leads") deals = relationship("CrmDeal", back_populates="lead", cascade="all, delete-orphan") class CrmDeal(Base, AuditMixin): __tablename__ = "crm_deals" deal_id = Column(String(36), primary_key=True, default=lambda: str(ulid.ULID())) tenant_id = Column(String(36), nullable=False, default="tenant_default", index=True) lead_id = Column(String(36), ForeignKey("crm_leads.lead_id", ondelete="SET NULL"), nullable=True, index=True) customer_id = Column(String(36), ForeignKey("crm_customers.customer_id", ondelete="CASCADE"), nullable=True, index=True) title = Column(String(255), nullable=False) stage = Column(String(50), nullable=False, default="DISCOVERY") # DISCOVERY, REQUIREMENT_AUDIT, BULK_QUOTE_SENT, NEGOTIATION, WON, LOST estimated_value = Column(Float, nullable=False, default=0.0) probability_percent = Column(Integer, nullable=False, default=50) assigned_user_id = Column(String(36), nullable=True, index=True) notes = Column(Text, nullable=True) lead = relationship("CrmLead", back_populates="deals") customer = relationship("CrmCustomer", back_populates="deals") class CrmTask(Base, AuditMixin): __tablename__ = "crm_tasks" task_id = Column(String(36), primary_key=True, default=lambda: str(ulid.ULID())) tenant_id = Column(String(36), nullable=False, default="tenant_default", index=True) location_id = Column(String(36), ForeignKey("crm_locations.location_id"), nullable=True, index=True) title = Column(String(255), nullable=False) description = Column(Text, nullable=True) assignee_id = Column(String(36), nullable=True, index=True) # Polymorphic Entity Linking entity_type = Column(String(50), nullable=True, index=True) # LEAD, CUSTOMER, DEAL, REPAIR_TICKET, QUOTATION, ORDER entity_id = Column(String(36), nullable=True, index=True) priority = Column(String(20), nullable=False, default="MEDIUM") # HIGH, MEDIUM, LOW status = Column(String(20), nullable=False, default="PENDING") # PENDING, COMPLETED due_at = Column(DateTime, nullable=True, index=True) completed_at = Column(DateTime, nullable=True) calendar_events = relationship("CrmCalendarEvent", back_populates="task") class CrmCalendarEvent(Base, AuditMixin): __tablename__ = "crm_calendar_events" event_id = Column(String(36), primary_key=True, default=lambda: str(ulid.ULID())) tenant_id = Column(String(36), nullable=False, default="tenant_default", index=True) location_id = Column(String(36), ForeignKey("crm_locations.location_id"), nullable=True, index=True) task_id = Column(String(36), ForeignKey("crm_tasks.task_id", ondelete="SET NULL"), nullable=True, index=True) title = Column(String(255), nullable=False) description = Column(Text, nullable=True) # Idempotent Sync Source source_type = Column(String(50), nullable=True, index=True) # QUOTATION, REPAIR_TICKET, CRM_LEAD, MANUAL source_id = Column(String(36), nullable=True, index=True) start_time = Column(DateTime, nullable=False, index=True) end_time = Column(DateTime, nullable=False) is_all_day = Column(Boolean, nullable=False, default=False) status = Column(String(20), nullable=False, default="SCHEDULED") # SCHEDULED, COMPLETED, CANCELLED task = relationship("CrmTask", back_populates="calendar_events") __table_args__ = ( UniqueConstraint("tenant_id", "source_type", "source_id", name="uq_calendar_source"), )