bakery-ia/services/orders/app/models/customer.py

# ================================================================
# services/orders/app/models/customer.py
# ================================================================
"""
Customer-related database models for Orders Service
"""

import uuid
from datetime import datetime
from decimal import Decimal
from typing import Optional, List
from sqlalchemy import Column, String, Boolean, DateTime, Numeric, Text, ForeignKey, Integer
from sqlalchemy.dialects.postgresql import UUID, JSONB
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func

from shared.database.base import Base


class Customer(Base):
    """Customer model for managing customer information"""
    __tablename__ = "customers"
    
    # Primary identification
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    tenant_id = Column(UUID(as_uuid=True), nullable=False, index=True)
    customer_code = Column(String(50), nullable=False, index=True)  # Human-readable code
    
    # Basic information
    name = Column(String(200), nullable=False)
    business_name = Column(String(200), nullable=True)
    customer_type = Column(String(50), nullable=False, default="individual")  # individual, business, central_bakery
    
    # Contact information
    email = Column(String(255), nullable=True)
    phone = Column(String(50), nullable=True)
    
    # Address information
    address_line1 = Column(String(255), nullable=True)
    address_line2 = Column(String(255), nullable=True)
    city = Column(String(100), nullable=True)
    state = Column(String(100), nullable=True)
    postal_code = Column(String(20), nullable=True)
    country = Column(String(100), nullable=False, default="US")
    
    # Business information
    tax_id = Column(String(50), nullable=True)
    business_license = Column(String(100), nullable=True)
    
    # Customer status and preferences
    is_active = Column(Boolean, nullable=False, default=True)
    preferred_delivery_method = Column(String(50), nullable=False, default="delivery")  # delivery, pickup
    payment_terms = Column(String(50), nullable=False, default="immediate")  # immediate, net_30, net_60
    credit_limit = Column(Numeric(10, 2), nullable=True)
    discount_percentage = Column(Numeric(5, 2), nullable=False, default=Decimal("0.00"))
    
    # Customer categorization
    customer_segment = Column(String(50), nullable=False, default="regular")  # vip, regular, wholesale
    priority_level = Column(String(20), nullable=False, default="normal")  # high, normal, low
    
    # Preferences and special requirements
    special_instructions = Column(Text, nullable=True)
    delivery_preferences = Column(JSONB, nullable=True)  # Time windows, special requirements
    product_preferences = Column(JSONB, nullable=True)  # Favorite products, allergies
    
    # Customer metrics
    total_orders = Column(Integer, nullable=False, default=0)
    total_spent = Column(Numeric(12, 2), nullable=False, default=Decimal("0.00"))
    average_order_value = Column(Numeric(10, 2), nullable=False, default=Decimal("0.00"))
    last_order_date = Column(DateTime(timezone=True), nullable=True)
    
    # Audit fields
    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False)
    created_by = Column(UUID(as_uuid=True), nullable=True)
    updated_by = Column(UUID(as_uuid=True), nullable=True)
    
    # Relationships
    contacts = relationship("CustomerContact", back_populates="customer", cascade="all, delete-orphan")
    orders = relationship("CustomerOrder", back_populates="customer")


class CustomerContact(Base):
    """Additional contact persons for business customers"""
    __tablename__ = "customer_contacts"
    
    # Primary identification
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    customer_id = Column(UUID(as_uuid=True), ForeignKey("customers.id", ondelete="CASCADE"), nullable=False)
    
    # Contact information
    name = Column(String(200), nullable=False)
    title = Column(String(100), nullable=True)
    department = Column(String(100), nullable=True)
    
    # Contact details
    email = Column(String(255), nullable=True)
    phone = Column(String(50), nullable=True)
    mobile = Column(String(50), nullable=True)
    
    # Contact preferences
    is_primary = Column(Boolean, nullable=False, default=False)
    contact_for_orders = Column(Boolean, nullable=False, default=True)
    contact_for_delivery = Column(Boolean, nullable=False, default=False)
    contact_for_billing = Column(Boolean, nullable=False, default=False)
    contact_for_support = Column(Boolean, nullable=False, default=False)
    
    # Preferred contact methods
    preferred_contact_method = Column(String(50), nullable=False, default="email")  # email, phone, sms
    contact_time_preferences = Column(JSONB, nullable=True)  # Time windows for contact
    
    # Notes and special instructions
    notes = Column(Text, nullable=True)
    
    # Status
    is_active = Column(Boolean, nullable=False, default=True)
    
    # Audit fields
    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False)
    
    # Relationships
    customer = relationship("Customer", back_populates="contacts")