#!/usr/bin/env python3 # -*- coding: utf-8 -*- """ 廠商缺貨通知系統 - 資料庫模型 包含廠商缺貨表、廠商清單、廠商郵件、郵件發送記錄 """ from sqlalchemy import Column, Integer, String, DateTime, Boolean, ForeignKey, Numeric, Date, Text from sqlalchemy.orm import relationship from database.models import Base from datetime import datetime class VendorStockout(Base): """廠商缺貨表 - 儲存匯入的缺貨資料""" __tablename__ = 'vendor_stockout' # 主鍵 id = Column(Integer, primary_key=True, autoincrement=True) # 匯入批次資訊 batch_id = Column(String(50), nullable=False, index=True, comment='批次編號 (格式: YYYYMMDD_HHMMSS)') import_date = Column(Date, nullable=False, index=True, comment='匯入日期') import_time = Column(DateTime, nullable=False, default=datetime.now, comment='匯入時間') # 組織資訊 department = Column(String(100), comment='部別') section = Column(String(100), comment='課別') pm_name = Column(String(100), index=True, comment='PM 姓名') zone_id = Column(String(100), comment='區ID') zone_name = Column(String(200), comment='區名稱') # 商品資訊 product_code = Column(String(100), nullable=False, index=True, comment='商品料號') product_name = Column(String(500), nullable=False, comment='商品名稱') product_spec = Column(Text, comment='商品規格') borrow_transfer = Column(String(100), comment='借採轉') sale_price = Column(Numeric(10, 2), comment='售價') cost_price = Column(Numeric(10, 2), comment='成本') # 廠商資訊 vendor_code = Column(String(100), nullable=False, index=True, comment='廠商代碼') vendor_name = Column(String(200), nullable=False, index=True, comment='廠商名稱') # 業績資訊 monthly_sales_qty = Column(Integer, comment='全月銷量') monthly_sales_amount = Column(Numeric(12, 2), comment='全月業績') daily_avg_sales = Column(Numeric(10, 2), comment='日均銷量') # 庫存資訊 current_stock = Column(Integer, comment='現有庫存') stockout_date = Column(Date, comment='缺貨日期') stockout_days = Column(Integer, comment='缺貨天數') safe_stock_days = Column(Integer, comment='安全庫存天數') # 狀態追蹤 status = Column(String(20), nullable=False, default='pending', index=True, comment='狀態: pending(待發送), sent(已發送), failed(失敗), duplicate(重複)') is_duplicate = Column(Boolean, default=False, index=True, comment='是否為重複資料') duplicate_count = Column(Integer, default=0, comment='重複次數') # 發送記錄 sent_date = Column(DateTime, comment='發送時間') sent_by = Column(String(100), comment='發送人員') error_message = Column(Text, comment='錯誤訊息') # 備註與時間戳記 notes = Column(Text, comment='備註') created_at = Column(DateTime, default=datetime.now, nullable=False, comment='建立時間') updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now, nullable=False, comment='更新時間') # 關聯設定 email_logs = relationship("EmailSendLog", back_populates="stockout_item", cascade="all, delete-orphan") class VendorList(Base): """廠商清單表 - 管理廠商基本資料""" __tablename__ = 'vendor_list' # 主鍵 id = Column(Integer, primary_key=True, autoincrement=True) # 廠商基本資訊 vendor_code = Column(String(100), unique=True, nullable=False, index=True, comment='廠商代碼') vendor_name = Column(String(200), nullable=False, comment='廠商名稱') # 狀態 is_active = Column(Boolean, default=True, nullable=False, index=True, comment='是否啟用') # 時間戳記 created_at = Column(DateTime, default=datetime.now, nullable=False, comment='建立時間') updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now, nullable=False, comment='更新時間') # 關聯設定 emails = relationship("VendorEmail", back_populates="vendor", cascade="all, delete-orphan") email_logs = relationship("EmailSendLog", back_populates="vendor", cascade="all, delete-orphan") class VendorEmail(Base): """廠商郵件表 - 管理廠商的多個聯絡郵件""" __tablename__ = 'vendor_emails' # 主鍵 id = Column(Integer, primary_key=True, autoincrement=True) # 外鍵 vendor_id = Column(Integer, ForeignKey('vendor_list.id'), nullable=False, index=True, comment='廠商ID') # 郵件資訊 email = Column(String(255), nullable=False, comment='電子郵件地址') contact_name = Column(String(100), comment='聯絡人姓名') email_type = Column(String(20), nullable=False, default='primary', comment='郵件類型: primary(主要), cc(副本), bcc(密件副本)') # 狀態 is_active = Column(Boolean, default=True, nullable=False, index=True, comment='是否啟用') # 備註與時間戳記 notes = Column(Text, comment='備註') created_at = Column(DateTime, default=datetime.now, nullable=False, comment='建立時間') updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now, nullable=False, comment='更新時間') # 關聯設定 vendor = relationship("VendorList", back_populates="emails") class EmailSendLog(Base): """郵件發送記錄表 - 完整稽核追蹤""" __tablename__ = 'email_send_log' # 主鍵 id = Column(Integer, primary_key=True, autoincrement=True) # 外鍵 vendor_id = Column(Integer, ForeignKey('vendor_list.id'), nullable=False, index=True, comment='廠商ID') stockout_id = Column(Integer, ForeignKey('vendor_stockout.id'), index=True, comment='缺貨記錄ID') # 批次資訊 batch_id = Column(String(50), nullable=False, index=True, comment='發送批次編號') # 郵件資訊 sender_email = Column(String(255), nullable=False, comment='寄件者郵件') recipient_email = Column(String(255), nullable=False, comment='收件者郵件') cc_emails = Column(Text, comment='CC 郵件清單 (JSON 格式)') bcc_emails = Column(Text, comment='BCC 郵件清單 (JSON 格式)') subject = Column(String(500), nullable=False, comment='郵件主旨') # 內容資訊 product_count = Column(Integer, nullable=False, comment='商品數量') attachment_filename = Column(String(255), comment='附件檔名') attachment_size = Column(Integer, comment='附件大小 (bytes)') # 發送狀態 status = Column(String(20), nullable=False, default='pending', index=True, comment='狀態: pending(待發送), sent(成功), failed(失敗)') error_message = Column(Text, comment='錯誤訊息') retry_count = Column(Integer, default=0, comment='重試次數') # 時間戳記 sent_at = Column(DateTime, comment='發送時間') created_at = Column(DateTime, default=datetime.now, nullable=False, comment='建立時間') # 關聯設定 vendor = relationship("VendorList", back_populates="email_logs") stockout_item = relationship("VendorStockout", back_populates="email_logs")