from datetime import datetime, timezone from sqlalchemy import Column, String, Integer, Float, 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 CrmWarehouse(Base, AuditMixin): __tablename__ = "crm_warehouses" warehouse_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) code = Column(String(50), nullable=False, index=True) address = Column(String(500), nullable=True) city = Column(String(100), nullable=True) state = Column(String(100), nullable=True) is_primary = Column(Boolean, nullable=False, default=False) stocks = relationship("CrmInventoryStock", back_populates="warehouse", cascade="all, delete-orphan") pos_sessions = relationship("CrmPosSession", back_populates="warehouse") __table_args__ = ( UniqueConstraint("tenant_id", "code", name="uq_tenant_warehouse_code"), ) class CrmSupplier(Base, AuditMixin): __tablename__ = "crm_suppliers" supplier_id = Column(String(36), primary_key=True, default=lambda: str(ulid.ULID())) tenant_id = Column(String(36), nullable=False, default="tenant_default", index=True) company_name = Column(String(255), nullable=False, index=True) contact_name = Column(String(150), nullable=True) phone = Column(String(50), nullable=True) email = Column(String(255), nullable=True) gstin = Column(String(50), nullable=True) address = Column(Text, nullable=True) __table_args__ = ( UniqueConstraint("tenant_id", "company_name", name="uq_tenant_supplier_company"), ) class CrmInventoryStock(Base, AuditMixin): __tablename__ = "crm_inventory_stocks" stock_id = Column(String(36), primary_key=True, default=lambda: str(ulid.ULID())) tenant_id = Column(String(36), nullable=False, default="tenant_default", index=True) warehouse_id = Column(String(36), ForeignKey("crm_warehouses.warehouse_id", ondelete="CASCADE"), nullable=False, index=True) sku = Column(String(100), nullable=False, index=True) quantity_on_hand = Column(Integer, nullable=False, default=0) quantity_reserved = Column(Integer, nullable=False, default=0) reorder_threshold = Column(Integer, nullable=False, default=5) warehouse = relationship("CrmWarehouse", back_populates="stocks") __table_args__ = ( UniqueConstraint("tenant_id", "warehouse_id", "sku", name="uq_tenant_warehouse_sku"), ) class PosTerminal(Base, AuditMixin): __tablename__ = "pos_terminals" terminal_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", ondelete="CASCADE"), nullable=False, index=True) code = Column(String(50), nullable=False, index=True) # e.g. "POS-01" name = Column(String(100), nullable=False) # e.g. "Main Counter Checkout 1" is_active = Column(Boolean, nullable=False, default=True) location = relationship("CrmLocation", back_populates="pos_terminals") sessions = relationship("CrmPosSession", back_populates="terminal") __table_args__ = ( UniqueConstraint("tenant_id", "location_id", "code", name="uq_location_terminal_code"), ) class CrmPosSession(Base, AuditMixin): __tablename__ = "crm_pos_sessions" pos_session_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", ondelete="CASCADE"), nullable=True, index=True) terminal_id = Column(String(36), ForeignKey("pos_terminals.terminal_id", ondelete="CASCADE"), nullable=True, index=True) warehouse_id = Column(String(36), ForeignKey("crm_warehouses.warehouse_id", ondelete="SET NULL"), nullable=True, index=True) user_id = Column(String(36), nullable=False, default="usr_admin", index=True) opened_by_user_id = Column(String(36), nullable=False, default="usr_admin", index=True) opening_cash = Column(Float, nullable=False, default=0.0) closing_cash = Column(Float, nullable=True) expected_cash = Column(Float, nullable=True) cash_variance = Column(Float, nullable=True) # Domain Sales Breakdown ecom_sales_count = Column(Integer, nullable=False, default=0) ecom_sales_amount = Column(Float, nullable=False, default=0.0) service_sales_count = Column(Integer, nullable=False, default=0) service_sales_amount = Column(Float, nullable=False, default=0.0) courier_charges = Column(Float, nullable=False, default=0.0) # Payment Method Breakdown cash_sales = Column(Float, nullable=False, default=0.0) upi_sales = Column(Float, nullable=False, default=0.0) card_sales = Column(Float, nullable=False, default=0.0) other_sales = Column(Float, nullable=False, default=0.0) status = Column(String(50), nullable=False, default="OPEN") # OPEN, CLOSED opened_at = Column(DateTime, nullable=False, default=lambda: datetime.now(timezone.utc)) session_duration_minutes = Column(Integer, nullable=False, default=540) # 9 hours expires_at = Column(DateTime, nullable=True) closed_at = Column(DateTime, nullable=True) notes = Column(Text, nullable=True) location = relationship("CrmLocation", back_populates="pos_sessions") terminal = relationship("PosTerminal", back_populates="sessions") warehouse = relationship("CrmWarehouse", back_populates="pos_sessions") extensions = relationship("PosSessionExtension", back_populates="session", cascade="all, delete-orphan") class PosSessionExtension(Base, AuditMixin): __tablename__ = "crm_pos_session_extensions" id = Column(String(36), primary_key=True, default=lambda: str(ulid.ULID())) session_id = Column(String(36), ForeignKey("crm_pos_sessions.pos_session_id", ondelete="CASCADE"), nullable=False, index=True) extension_minutes = Column(Integer, nullable=False, default=60) extended_by = Column(String(36), nullable=False) reason = Column(String(500), nullable=True) created_at = Column(DateTime, nullable=False, default=lambda: datetime.now(timezone.utc)) session = relationship("CrmPosSession", back_populates="extensions")