order.py 3.5 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889
  1. from sqlalchemy import Column, String, Integer, DateTime, JSON, Enum
  2. from datetime import datetime
  3. from app.core.database import Base
  4. class VasOrder(Base):
  5. __tablename__ = "vas_order"
  6. __ddl__ = """
  7. CREATE TABLE IF NOT EXISTS `vas_order` (
  8. `id` VARCHAR(128) NOT NULL,
  9. `user_id` VARCHAR(128) NOT NULL,
  10. `product_id` INT NOT NULL,
  11. `product_name` VARCHAR(100) NULL,
  12. `user_name` VARCHAR(100) NULL,
  13. `base_amount` INT NOT NULL,
  14. `adjustment_delta` INT NOT NULL DEFAULT 0,
  15. `final_amount` INT NOT NULL,
  16. `base_currency` VARCHAR(10) NOT NULL,
  17. `status` ENUM('pending', 'paid', 'completed', 'closed') NOT NULL DEFAULT 'pending',
  18. `channel_type` VARCHAR(32) NOT NULL DEFAULT 'direct' COMMENT 'direct:社交自收直客, agency:中介渠道, website:官网公海',
  19. `agency_id` VARCHAR(64) NULL COMMENT '中介机构ID',
  20. `handler_id` VARCHAR(128) NULL COMMENT '40%销售提成对接人ID',
  21. `developer_id` VARCHAR(128) NULL COMMENT '20%技术版权脚本开发者ID',
  22. `settlement_status` VARCHAR(32) NOT NULL DEFAULT 'unsettled' COMMENT '结算状态: unsettled, risk_period, eligible, settled',
  23. `completed_at` DATETIME NULL,
  24. `risk_cleared_at` DATETIME NULL,
  25. `settlement_period_id` VARCHAR(32) NULL,
  26. `user_inputs` JSON NULL,
  27. `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  28. `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  29. PRIMARY KEY (`id`),
  30. INDEX `idx_vas_order_user_id` (`user_id`),
  31. INDEX `idx_vas_order_product_id` (`product_id`),
  32. INDEX `idx_vas_order_status` (`status`),
  33. INDEX `idx_vas_order_handler_id` (`handler_id`),
  34. INDEX `idx_vas_order_settlement_status` (`settlement_status`),
  35. INDEX `idx_vas_order_completed_at` (`completed_at`)
  36. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='主订单表';
  37. """
  38. id = Column(String(128), primary_key=True)
  39. user_id = Column(String(128), nullable=False)
  40. product_id = Column(Integer, nullable=False)
  41. product_name = Column(String(100))
  42. user_name = Column(String(100))
  43. base_amount = Column(Integer, nullable=False)
  44. adjustment_delta = Column(Integer, default=0)
  45. # 负数 = 优惠,正数 = 加价
  46. # 订单最终价(冗余字段,方便查询)
  47. final_amount = Column(Integer, nullable=False)
  48. base_currency = Column(String(10), nullable=False)
  49. status = Column(
  50. Enum('pending','paid','completed','closed'),
  51. default='pending',
  52. nullable=False
  53. )
  54. # 渠道来源: direct(社交自收直客), agency(中介渠道), website(官网公海)
  55. channel_type = Column(
  56. String(32),
  57. default='direct',
  58. nullable=False
  59. )
  60. # 中介机构ID (仅中介单时有值)
  61. agency_id = Column(String(64), nullable=True)
  62. # 40%销售提成对接人 (直客为录单对接人,中介单为该中介拓展人,公海单为NULL)
  63. handler_id = Column(String(128), nullable=True, index=True)
  64. # 20%技术版权脚本开发者ID
  65. developer_id = Column(String(128), nullable=True)
  66. # 结算状态: unsettled(未结算), risk_period(风险期冻结), eligible(待结算), settled(已结算)
  67. settlement_status = Column(
  68. String(32),
  69. default='unsettled',
  70. nullable=False,
  71. index=True
  72. )
  73. completed_at = Column(DateTime, nullable=True)
  74. risk_cleared_at = Column(DateTime, nullable=True)
  75. settlement_period_id = Column(String(32), nullable=True)
  76. user_inputs = Column(JSON)
  77. created_at = Column(DateTime, default=datetime.utcnow)
  78. updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)