# 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")) 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)