# app/models/settlement.py from datetime import datetime from typing import Optional from sqlalchemy import Column, Integer, String, DateTime, JSON, Enum, Text from app.core.database import Base class VasSettlementPeriod(Base): __tablename__ = "vas_settlement_period" __ddl__ = """ CREATE TABLE IF NOT EXISTS `vas_settlement_period` ( `id` VARCHAR(32) NOT NULL COMMENT '账期ID如 2026-10', `name` VARCHAR(128) NOT NULL, `start_date` DATETIME NOT NULL, `end_date` DATETIME NOT NULL, `total_orders` INT NOT NULL DEFAULT 0, `total_revenue` INT NOT NULL DEFAULT 0, `total_cost` INT NOT NULL DEFAULT 0, `avg_cost` INT NOT NULL DEFAULT 0, `net_profit` INT NOT NULL DEFAULT 0, `status` ENUM('draft', 'confirmed', 'paid') NOT NULL DEFAULT 'draft', `summary_data` JSON NULL, `confirmed_by` VARCHAR(128) NULL, `confirmed_at` DATETIME NULL, `paid_at` DATETIME NULL, `notes` TEXT NULL, `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='结算账期总表'; """ id = Column(String(32), primary_key=True) # 账期ID,如 2026-10 或 2026-10-Q1 name = Column(String(128), nullable=False) # 账期显示名称 start_date = Column(DateTime, nullable=False) end_date = Column(DateTime, nullable=False) total_orders = Column(Integer, default=0, nullable=False) # 本账期履约完成订单总数 total_revenue = Column(Integer, default=0, nullable=False) # 本期实收总额 (分) total_cost = Column(Integer, default=0, nullable=False) # 本期耗材总成本 (分) avg_cost = Column(Integer, default=0, nullable=False) # 单均摊薄耗材 (分) net_profit = Column(Integer, default=0, nullable=False) # 本期总净毛利 (分) status = Column( Enum('draft', 'confirmed', 'paid', name='settlement_period_status'), default='draft', nullable=False ) # 包含甲乙分红、各对接人提成总计、开发者版权总计、过失扣除明细的完整汇总 JSON summary_data = Column(JSON, nullable=True) confirmed_by = Column(String(128), nullable=True) confirmed_at = Column(DateTime, nullable=True) paid_at = Column(DateTime, nullable=True) notes = Column(Text, nullable=True) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) class VasSettlementDetail(Base): __tablename__ = "vas_settlement_detail" __ddl__ = """ CREATE TABLE IF NOT EXISTS `vas_settlement_detail` ( `id` INT NOT NULL AUTO_INCREMENT, `period_id` VARCHAR(32) NOT NULL, `order_id` VARCHAR(128) NOT NULL, `channel_type` VARCHAR(32) NOT NULL, `agency_id` VARCHAR(64) NULL, `handler_id` VARCHAR(128) NULL, `developer_id` VARCHAR(128) NULL, `order_amount` INT NOT NULL, `avg_cost` INT NOT NULL, `net_profit` INT NOT NULL, `tech_fee` INT NOT NULL DEFAULT 0, `sales_fee` INT NOT NULL DEFAULT 0, `pool_fee` INT NOT NULL DEFAULT 0, `partner_a_amount` INT NOT NULL DEFAULT 0, `partner_b_amount` INT NOT NULL DEFAULT 0, `snapshot` JSON NULL, `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), INDEX `idx_vas_settle_det_period` (`period_id`), INDEX `idx_vas_settle_det_order` (`order_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='单笔订单分润快照表'; """ id = Column(Integer, primary_key=True, autoincrement=True) period_id = Column(String(32), nullable=False, index=True) order_id = Column(String(128), nullable=False, index=True) channel_type = Column(String(32), nullable=False) agency_id = Column(String(64), nullable=True) handler_id = Column(String(128), nullable=True) # 40%提成归属人 developer_id = Column(String(128), nullable=True) # 20%技术版权归属人 order_amount = Column(Integer, nullable=False) # 订单实收 avg_cost = Column(Integer, nullable=False) # 均摊耗材 net_profit = Column(Integer, nullable=False) # 单单毛利 tech_fee = Column(Integer, default=0, nullable=False) # 20% 技术版权费 sales_fee = Column(Integer, default=0, nullable=False) # 40% 销售包干提成 pool_fee = Column(Integer, default=0, nullable=False) # 40% 合伙分红池 partner_a_amount = Column(Integer, default=0, nullable=False) # 甲应得金额 partner_b_amount = Column(Integer, default=0, nullable=False) # 乙应得金额 # 包含该单完整计算快照 snapshot = Column(JSON, nullable=True) created_at = Column(DateTime, default=datetime.utcnow) @property def handler_name(self) -> Optional[str]: if self.snapshot and isinstance(self.snapshot, dict): return self.snapshot.get("handler_name") return None @property def developer_name(self) -> Optional[str]: if self.snapshot and isinstance(self.snapshot, dict): return self.snapshot.get("developer_name") return None @property def original_amount(self) -> Optional[int]: if self.snapshot and isinstance(self.snapshot, dict): return self.snapshot.get("original_amount") return None @property def original_currency(self) -> Optional[str]: if self.snapshot and isinstance(self.snapshot, dict): return self.snapshot.get("original_currency") return None @property def currency(self) -> str: if self.snapshot and isinstance(self.snapshot, dict): return self.snapshot.get("currency") or "CNY" return "CNY"