app/order/models/model.py

"""
SQLAlchemy models for sales orders.
Maps to trans.sales_orders, trans.sales_order_items, and trans.sales_order_addresses tables.
Uses UUID for all ID fields.
No foreign key constraints or relationships - managed at application level.
"""
from sqlalchemy import Column, String, Integer, Numeric, DateTime, Text, ARRAY, Date
from sqlalchemy.dialects.postgresql import UUID
from datetime import datetime
import uuid

from app.core.database import Base


class SalesOrder(Base):
    """Sales order model - trans.sales_orders table"""
    __tablename__ = "sales_orders"
    __table_args__ = {"schema": "trans"}
    
    sales_order_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    order_number = Column(String(50), unique=True, nullable=False, index=True)
    merchant_id = Column(UUID(as_uuid=True), nullable=False, index=True)
    order_date = Column(DateTime(timezone=True), nullable=False, default=datetime.utcnow)
    status = Column(String(50), nullable=False, default="pending")
    
    # Customer information
    customer_id = Column(UUID(as_uuid=True), nullable=False, index=True)
    customer_name = Column(String(255), nullable=True)
    customer_type = Column(String(50), nullable=True, default="retail")
    customer_phone = Column(String(20), nullable=True)
    customer_email = Column(String(255), nullable=True)
    customer_gstin = Column(String(15), nullable=True)
    
    # Financial information
    subtotal = Column(Numeric(15, 2), nullable=False, default=0.0)
    total_discount = Column(Numeric(15, 2), nullable=False, default=0.0)
    total_tax = Column(Numeric(15, 2), nullable=False, default=0.0)
    shipping_charges = Column(Numeric(15, 2), nullable=False, default=0.0)
    grand_total = Column(Numeric(15, 2), nullable=False, default=0.0)
    cgst = Column(Numeric(15, 2), nullable=True, default=0.0)
    sgst = Column(Numeric(15, 2), nullable=True, default=0.0)
    igst = Column(Numeric(15, 2), nullable=True, default=0.0)
    
    # Payment information
    payment_type = Column(String(50), nullable=True)
    payment_status = Column(String(50), nullable=False, default="pending")
    payment_method = Column(String(50), nullable=True)
    payment_date = Column(DateTime(timezone=True), nullable=True)
    payment_reference = Column(String(255), nullable=True)
    amount_paid = Column(Numeric(15, 2), nullable=False, default=0.0)
    amount_due = Column(Numeric(15, 2), nullable=False, default=0.0)
    credit_terms = Column(String(100), nullable=True)
    credit_limit = Column(Numeric(15, 2), nullable=True)
    
    # Fulfillment information
    fulfillment_status = Column(String(50), nullable=False, default="pending")
    expected_delivery_date = Column(DateTime(timezone=True), nullable=True)
    actual_delivery_date = Column(DateTime(timezone=True), nullable=True)
    
    # Invoice information
    invoice_id = Column(UUID(as_uuid=True), nullable=True)
    invoice_number = Column(String(50), nullable=True)
    invoice_date = Column(DateTime(timezone=True), nullable=True)
    invoice_pdf_url = Column(Text, nullable=True)
    
    # Additional information
    notes = Column(Text, nullable=True)
    internal_notes = Column(Text, nullable=True)
    
    # Audit fields
    created_by = Column(String(100), nullable=True)
    created_at = Column(DateTime(timezone=True), nullable=False, default=datetime.utcnow)
    updated_at = Column(DateTime(timezone=True), nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow)
    approved_by = Column(String(100), nullable=True)
    approved_at = Column(DateTime(timezone=True), nullable=True)
    
    # Metadata
    source = Column(String(50), nullable=True, default="ecommerce")
    channel = Column(String(50), nullable=True, default="web")
    tags = Column(ARRAY(Text), nullable=True)
    version = Column(Integer, nullable=False, default=1)


class SalesOrderItem(Base):
    """Sales order item model - trans.sales_order_items table"""
    __tablename__ = "sales_order_items"
    __table_args__ = {"schema": "trans"}
    
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    sales_order_id = Column(UUID(as_uuid=True), nullable=False, index=True)
    
    # Product information
    sku = Column(String(100), nullable=True)
    product_id = Column(String(100), nullable=False)
    product_name = Column(String(255), nullable=False)
    item_type = Column(String(50), nullable=True, default="product")
    
    # Quantity and pricing
    quantity = Column(Numeric(15, 3), nullable=False)
    unit_price = Column(Numeric(15, 2), nullable=False)
    tax_percent = Column(Numeric(5, 2), nullable=False, default=0.0)
    discount_percent = Column(Numeric(5, 2), nullable=False, default=0.0)
    line_total = Column(Numeric(15, 2), nullable=False)
    
    # Additional information
    hsn_code = Column(String(20), nullable=True)
    uom = Column(String(20), nullable=True, default="PCS")
    batch_no = Column(String(100), nullable=True)
    serials = Column(ARRAY(Text), nullable=True)
    
    # Service-related fields
    staff_id = Column(String(100), nullable=True)
    staff_name = Column(String(255), nullable=True)
    
    # Metadata
    remarks = Column(Text, nullable=True)
    created_at = Column(DateTime(timezone=True), nullable=False, default=datetime.utcnow)


class SalesOrderAddress(Base):
    """Sales order address model - trans.sales_order_addresses table"""
    __tablename__ = "sales_order_addresses"
    __table_args__ = {"schema": "trans"}
    
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    sales_order_id = Column(UUID(as_uuid=True), nullable=False, index=True)
    
    # Address type (shipping, billing)
    address_type = Column(String(50), nullable=False)
    
    # Address fields
    line1 = Column(String(255), nullable=False)
    line2 = Column(String(255), nullable=True)
    city = Column(String(100), nullable=False)
    state = Column(String(100), nullable=False)
    postal_code = Column(String(20), nullable=False)
    country = Column(String(100), nullable=False, default="India")
    landmark = Column(String(255), nullable=True)
    
    # Metadata
    created_at = Column(DateTime(timezone=True), nullable=False, default=datetime.utcnow)