"""
SQLAlchemy 資料庫模型 - 基於進銷存系統
"""

from sqlalchemy import Column, Integer, String, Text, DECIMAL, Boolean, DateTime, ForeignKey, JSON, Date, Float, Enum
from sqlalchemy.sql import func
from sqlalchemy.orm import relationship
from backend.database.connection import Base
import enum

# 枚舉類型定義
class UserRole(str, enum.Enum):
    ADMIN = "ADMIN"
    USER = "USER"

class TransactionType(str, enum.Enum):
    PURCHASE = "PURCHASE"
    SELL = "SELL"

class TransactionStatus(str, enum.Enum):
    PENDING = "PENDING"
    PROCESSING = "PROCESSING"
    COMPLETED = "COMPLETED"
    CANCELLED = "CANCELLED"

class PurchaseOrderStatus(str, enum.Enum):
    DRAFT = "DRAFT"
    PENDING = "PENDING"
    CONFIRMED = "CONFIRMED"
    RECEIVED = "RECEIVED"
    CANCELLED = "CANCELLED"

class SalesOrderStatus(str, enum.Enum):
    DRAFT = "DRAFT"
    CONFIRMED = "CONFIRMED"
    SHIPPED = "SHIPPED"
    DELIVERED = "DELIVERED"
    CANCELLED = "CANCELLED"

class PaymentStatus(str, enum.Enum):
    UNPAID = "UNPAID"
    PARTIAL = "PARTIAL"
    PAID = "PAID"

# 基礎模型
class User(Base):
    """用戶模型"""
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(255))
    email = Column(String(255), unique=True, nullable=False)
    phoneNumber = Column(String(50))
    role = Column(Enum(UserRole), default=UserRole.USER)
    createdAt = Column(DateTime(timezone=True), server_default=func.now())
    
    # 關聯
    purchase_orders = relationship("PurchaseOrder", back_populates="purchaser")
    sales_orders = relationship("SalesOrder", back_populates="salesperson")

class Category(Base):
    """商品類別模型"""
    __tablename__ = "categories"
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(255), nullable=False)
    
    # 關聯
    products = relationship("Product", back_populates="category")

class Product(Base):
    """商品模型"""
    __tablename__ = "products"
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    productCode = Column(String(100), unique=True, nullable=False)
    productName = Column(String(255), nullable=False)
    unit = Column(String(50), nullable=False)
    warehouse = Column(String(100))
    unitWeight = Column(Float)
    barcode = Column(String(100))
    category_id = Column(Integer, ForeignKey("categories.id"))
    stock = Column(Integer, default=0)
    is_deleted = Column(Boolean, default=False)
    deleted_at = Column(DateTime(timezone=True))
    deleted_by = Column(Integer)
    createdAt = Column(DateTime(timezone=True), server_default=func.now())
    updatedAt = Column(DateTime(timezone=True), onupdate=func.now())
    
    # 關聯
    category = relationship("Category", back_populates="products")
    purchase_order_items = relationship("PurchaseOrderItem", back_populates="product")
    sales_order_items = relationship("SalesOrderItem", back_populates="product")

class Supplier(Base):
    """供應商模型"""
    __tablename__ = "suppliers"
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(255), nullable=False)
    contactInfo = Column(Text)
    address = Column(Text)
    
    # 關聯
    purchase_orders = relationship("PurchaseOrder", back_populates="supplier")

class Customer(Base):
    """客戶模型"""
    __tablename__ = "customers"
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    customerType = Column(String(100))
    salesPersonId = Column(String(100))
    salesPersonName = Column(String(255))
    customerCode = Column(String(100), unique=True, nullable=False)
    customerName = Column(String(255), nullable=False)
    contactPerson = Column(String(255))
    invoiceTitle = Column(String(255))
    taxId = Column(String(50))
    phoneNumber = Column(String(50))
    faxNumber = Column(String(50))
    deliveryAddress = Column(Text)
    businessHours = Column(String(255))
    paymentMethod = Column(String(100))
    paymentCategory = Column(String(100))
    creditLimit = Column(Float, default=0.0)
    createdDate = Column(DateTime(timezone=True), server_default=func.now())
    updatedAt = Column(DateTime(timezone=True), onupdate=func.now())
    
    # 關聯
    sales_orders = relationship("SalesOrder", back_populates="customer")

class PurchaseOrder(Base):
    """採購單模型"""
    __tablename__ = "purchase_orders"
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    po_number = Column(String(100), unique=True, nullable=False)
    purchase_date = Column(Date, nullable=False)
    expected_delivery_date = Column(Date)
    supplier_id = Column(Integer, ForeignKey("suppliers.id"))
    purchaser_id = Column(Integer, ForeignKey("users.id"))
    status = Column(Enum(PurchaseOrderStatus), default=PurchaseOrderStatus.DRAFT)
    subtotal = Column(Float, default=0.0)
    tax_amount = Column(Float, default=0.0)
    total_amount = Column(Float, default=0.0)
    payment_status = Column(Enum(PaymentStatus), default=PaymentStatus.UNPAID)
    notes = Column(Text)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    
    # 關聯
    supplier = relationship("Supplier", back_populates="purchase_orders")
    purchaser = relationship("User", back_populates="purchase_orders")
    items = relationship("PurchaseOrderItem", back_populates="purchase_order")

class PurchaseOrderItem(Base):
    """採購單明細模型"""
    __tablename__ = "purchase_order_items"
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    purchase_order_id = Column(Integer, ForeignKey("purchase_orders.id"))
    product_id = Column(Integer, ForeignKey("products.id"))
    quantity = Column(Integer, nullable=False)
    unit_price = Column(Float, nullable=False)
    line_total = Column(Float, nullable=False)
    notes = Column(Text)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    
    # 關聯
    purchase_order = relationship("PurchaseOrder", back_populates="items")
    product = relationship("Product", back_populates="purchase_order_items")

class SalesOrder(Base):
    """銷售單模型"""
    __tablename__ = "sales_orders"
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    so_number = Column(String(100), unique=True, nullable=False)
    sales_date = Column(Date, nullable=False)
    customer_id = Column(Integer, ForeignKey("customers.id"))
    salesperson_id = Column(Integer, ForeignKey("users.id"))
    status = Column(Enum(SalesOrderStatus), default=SalesOrderStatus.DRAFT)
    subtotal = Column(Float, default=0.0)
    tax_amount = Column(Float, default=0.0)
    discount_amount = Column(Float, default=0.0)
    total_amount = Column(Float, default=0.0)
    notes = Column(Text)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    
    # 關聯
    customer = relationship("Customer", back_populates="sales_orders")
    salesperson = relationship("User", back_populates="sales_orders")
    items = relationship("SalesOrderItem", back_populates="sales_order")

class SalesOrderItem(Base):
    """銷售單明細模型"""
    __tablename__ = "sales_order_items"
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    sales_order_id = Column(Integer, ForeignKey("sales_orders.id"))
    product_id = Column(Integer, ForeignKey("products.id"))
    quantity = Column(Integer, nullable=False)
    unit_price = Column(Float, nullable=False)
    line_total = Column(Float, nullable=False)
    notes = Column(Text)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    
    # 關聯
    sales_order = relationship("SalesOrder", back_populates="items")
    product = relationship("Product", back_populates="sales_order_items")

# LINE Bot 相關模型
class LineMessage(Base):
    """LINE 訊息記錄模型"""
    __tablename__ = "line_messages"
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(String(255))
    message = Column(Text, nullable=False)
    message_type = Column(String(50), default="text")
    timestamp = Column(DateTime(timezone=True), server_default=func.now())
    processed = Column(Boolean, default=False)