| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475 |
- # app/core/migration.py
- from sqlalchemy import text
- from sqlalchemy.ext.asyncio import AsyncConnection
- from app.core.logger import logger
- async def run_settlement_migrations(conn: AsyncConnection):
- """
- 检查并为已有数据表增量添加字段,保证已有生产/开发数据库无缝平滑升级
- """
- logger.info("⏳ [MIGRATION] Checking database schema for settlement fields...")
- try:
- # 1. 检查 vas_order 表的列
- res = await conn.execute(text("SHOW COLUMNS FROM vas_order"))
- existing_order_cols = {row[0] for row in res.fetchall()}
- # 待添加字段及其 DDL
- order_cols_to_add = [
- ("channel_type", "ALTER TABLE vas_order ADD COLUMN channel_type VARCHAR(32) NOT NULL DEFAULT 'direct' COMMENT 'direct:社交自收直客, agency:中介渠道, website:官网公海'"),
- ("agency_id", "ALTER TABLE vas_order ADD COLUMN agency_id VARCHAR(64) NULL COMMENT '中介机构ID'"),
- ("handler_id", "ALTER TABLE vas_order ADD COLUMN handler_id VARCHAR(128) NULL COMMENT '40%销售提成归属人ID'"),
- ("developer_id", "ALTER TABLE vas_order ADD COLUMN developer_id VARCHAR(128) NULL COMMENT '20%技术版权归属人ID'"),
- ("settlement_status", "ALTER TABLE vas_order ADD COLUMN settlement_status VARCHAR(32) NOT NULL DEFAULT 'unsettled' COMMENT '结算状态: unsettled, risk_period, eligible, settled'"),
- ("completed_at", "ALTER TABLE vas_order ADD COLUMN completed_at DATETIME NULL COMMENT '履约完成时间'"),
- ("risk_cleared_at", "ALTER TABLE vas_order ADD COLUMN risk_cleared_at DATETIME NULL COMMENT '退款风险期截止时间'"),
- ("settlement_period_id", "ALTER TABLE vas_order ADD COLUMN settlement_period_id VARCHAR(32) NULL COMMENT '结算账期ID'"),
- ]
- for col_name, ddl in order_cols_to_add:
- if col_name not in existing_order_cols:
- logger.info(f"➕ [MIGRATION] Adding column '{col_name}' to table 'vas_order'...")
- await conn.execute(text(ddl))
- # 为 handler_id 补建索引(若未建)
- try:
- await conn.execute(text("CREATE INDEX idx_vas_order_handler ON vas_order (handler_id)"))
- except Exception:
- pass # 索引已存在时忽略
- # 为 settlement_status 补建索引
- try:
- await conn.execute(text("CREATE INDEX idx_vas_order_settlement_status ON vas_order (settlement_status)"))
- except Exception:
- pass
- # 2. 检查 vas_product 是否有 min_floor_price
- res_prod = await conn.execute(text("SHOW COLUMNS FROM vas_product"))
- existing_prod_cols = {row[0] for row in res_prod.fetchall()}
- if "min_floor_price" not in existing_prod_cols:
- logger.info("➕ [MIGRATION] Adding column 'min_floor_price' to table 'vas_product'...")
- await conn.execute(text("ALTER TABLE vas_product ADD COLUMN min_floor_price INT NOT NULL DEFAULT 0 COMMENT '内部最低底价(分)'"))
- if "developer_id" not in existing_prod_cols:
- logger.info("➕ [MIGRATION] Adding column 'developer_id' to table 'vas_product'...")
- await conn.execute(text("ALTER TABLE vas_product ADD COLUMN developer_id VARCHAR(128) NULL COMMENT '该产品默认脚本开发者'"))
- # 3. 检查 plugins 是否有 class_name 和 protocol
- res_plug = await conn.execute(text("SHOW COLUMNS FROM plugins"))
- existing_plug_cols = {row[0] for row in res_plug.fetchall()}
- if "class_name" not in existing_plug_cols:
- logger.info("➕ [MIGRATION] Adding column 'class_name' to table 'plugins'...")
- await conn.execute(text("ALTER TABLE plugins ADD COLUMN class_name VARCHAR(128) NOT NULL DEFAULT '' COMMENT '插件入口类名' AFTER filename"))
- if "protocol" not in existing_plug_cols:
- logger.info("➕ [MIGRATION] Adding column 'protocol' to table 'plugins'...")
- await conn.execute(text("ALTER TABLE plugins ADD COLUMN protocol VARCHAR(64) NOT NULL DEFAULT 'IPlugin' COMMENT '插件协议标识' AFTER class_name"))
- # 4. 检查 vas_payment 表 provider 列类型是否已扩充支持 creem 等新渠道
- res_pay = await conn.execute(text("SHOW COLUMNS FROM vas_payment LIKE 'provider'"))
- pay_col = res_pay.fetchone()
- if pay_col and "enum" in str(pay_col[1]).lower():
- logger.info("➕ [MIGRATION] Modifying 'provider' column in 'vas_payment' to VARCHAR(64)...")
- await conn.execute(text("ALTER TABLE vas_payment MODIFY COLUMN provider VARCHAR(64) NOT NULL COMMENT '支付服务商: stripe, wechat, alipay, creem'"))
- logger.info("✅ [MIGRATION] Settlement & Plugin schema migration completed successfully.")
- except Exception as e:
- logger.error(f"❌ [MIGRATION ERROR] Failed to run settlement migrations: {e}", exc_info=True)
|