Agent/examples/auto_put_ad_mini/db/schema.sql

-- auto_put_ad_mini 数据库表结构
-- 数据库类型：MySQL 5.7+
-- 字符集：utf8mb4

-- =====================================================
-- 1. 账户白名单表
-- =====================================================
CREATE TABLE IF NOT EXISTS account_whitelist (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
    account_id BIGINT NOT NULL COMMENT '腾讯广告账户ID',
    account_name VARCHAR(200) DEFAULT NULL COMMENT '账户名称（备注用）',
    business_line VARCHAR(100) DEFAULT NULL COMMENT '业务线（用于分组管理）',
    enabled BOOLEAN DEFAULT TRUE COMMENT '是否启用（1=启用，0=禁用）',
    remark TEXT DEFAULT NULL COMMENT '备注说明',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(100) DEFAULT NULL COMMENT '创建人',
    updated_by VARCHAR(100) DEFAULT NULL COMMENT '更新人',

    UNIQUE KEY uk_account_id (account_id),
    KEY idx_enabled (enabled),
    KEY idx_business_line (business_line),
    KEY idx_updated_at (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='广告账户白名单';

-- =====================================================
-- 2. 广告创建投放版本模板
-- =====================================================
CREATE TABLE IF NOT EXISTS ad_delivery_template (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
    delivery_version VARCHAR(100) NOT NULL COMMENT '投放版本',
    enabled BOOLEAN DEFAULT TRUE COMMENT '是否启用',
    description VARCHAR(500) DEFAULT NULL COMMENT '模板说明',
    site_set_json TEXT NOT NULL COMMENT '版位 JSON',
    location_types_json TEXT NOT NULL COMMENT '地域类型 JSON',
    region_ids_json MEDIUMTEXT NOT NULL COMMENT '地域 region_id JSON',
    daily_budget_fen INT NOT NULL COMMENT '单广告日预算(分)',
    time_series_json MEDIUMTEXT NOT NULL COMMENT '投放时段字符串 JSON',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    updated_by VARCHAR(100) DEFAULT NULL COMMENT '更新人',

    UNIQUE KEY uk_delivery_version (delivery_version),
    KEY idx_enabled (enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='广告创建投放版本模板';

-- =====================================================
-- 3. 待投放账户广告创建配置
-- =====================================================
CREATE TABLE IF NOT EXISTS ad_creation_account_config (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
    account_id BIGINT NOT NULL COMMENT '腾讯广告账户ID',
    enabled BOOLEAN DEFAULT TRUE COMMENT '是否启用创建',
    delivery_version VARCHAR(100) NOT NULL COMMENT '投放版本',
    brand_key VARCHAR(100) DEFAULT NULL COMMENT '品牌资产模板key',
    feedback_key VARCHAR(100) DEFAULT NULL COMMENT '监测链接模板key',
    audience_name VARCHAR(200) NOT NULL COMMENT '人工配置的人群包名称',
    audience_pack_id BIGINT DEFAULT NULL COMMENT '系统解析/验证后的人群包ID;NULL表示泛人群',
    audience_tier_label VARCHAR(100) DEFAULT NULL COMMENT '审批/命名展示用人群标签',
    bid_min_fen INT NOT NULL COMMENT '最低出价(分)',
    bid_max_fen INT NOT NULL COMMENT '最高出价(分)',
    bid_amount_fen INT DEFAULT NULL COMMENT '固定出价(分);NULL则按范围取值',
    daily_budget_fen INT DEFAULT NULL COMMENT '账户级单广告日预算(分);NULL则使用投放模板默认预算',
    age_min INT NOT NULL COMMENT '最小年龄',
    age_max INT NOT NULL COMMENT '最大年龄',
    audience_source_account_id BIGINT DEFAULT NULL COMMENT '人群包来源账户;默认可由 TENCENT_AUDIENCE_SOURCE_ACCOUNT_ID 提供',
    audience_grant_status VARCHAR(50) DEFAULT NULL COMMENT '授权/验证状态:not_required_no_audience_pack/resolved/grant_submitted/verified',
    audience_verified_at TIMESTAMP NULL DEFAULT NULL COMMENT '目标账户可见验证时间',
    remark TEXT DEFAULT NULL COMMENT '备注',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(100) DEFAULT NULL COMMENT '创建人',
    updated_by VARCHAR(100) DEFAULT NULL COMMENT '更新人',

    UNIQUE KEY uk_account_id (account_id),
    KEY idx_enabled (enabled),
    KEY idx_delivery_version (delivery_version),
    KEY idx_brand_key (brand_key),
    KEY idx_feedback_key (feedback_key),
    KEY idx_audience_name (audience_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='待投放账户广告创建配置';

-- =====================================================
-- 4. 统一品牌资产模板
-- =====================================================
CREATE TABLE IF NOT EXISTS brand_asset_template (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
    brand_key VARCHAR(100) NOT NULL COMMENT '品牌模板key',
    brand_name VARCHAR(100) NOT NULL COMMENT '品牌名称',
    brand_image_url VARCHAR(1000) NOT NULL COMMENT '品牌图片公网URL',
    enabled BOOLEAN DEFAULT TRUE COMMENT '是否启用',
    remark TEXT DEFAULT NULL COMMENT '备注',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    updated_by VARCHAR(100) DEFAULT NULL COMMENT '更新人',

    UNIQUE KEY uk_brand_key (brand_key),
    KEY idx_enabled (enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='统一品牌资产模板';

-- =====================================================
-- 5. 统一监测链接模板
-- =====================================================
CREATE TABLE IF NOT EXISTS feedback_asset_template (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
    feedback_key VARCHAR(100) NOT NULL COMMENT '监测模板key',
    feedback_name VARCHAR(200) NOT NULL COMMENT '监测链接组名称',
    second_category_type VARCHAR(100) NOT NULL COMMENT '二级类目类型，如 WECHAT_MINI_PROGRAM',
    feedback_type VARCHAR(50) NOT NULL COMMENT '监测类型，如 click',
    feedback_url TEXT NOT NULL COMMENT '回传链接模板',
    enabled BOOLEAN DEFAULT TRUE COMMENT '是否启用',
    remark TEXT DEFAULT NULL COMMENT '备注',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    updated_by VARCHAR(100) DEFAULT NULL COMMENT '更新人',

    UNIQUE KEY uk_feedback_key (feedback_key),
    KEY idx_enabled (enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='统一监测链接模板';

-- =====================================================
-- 6. 系统配置表（Key-Value 存储）
-- =====================================================
CREATE TABLE IF NOT EXISTS system_config (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
    config_key VARCHAR(100) NOT NULL COMMENT '配置键（如 execution_enabled, cron_schedule）',
    config_value TEXT NOT NULL COMMENT '配置值（JSON 或字符串）',
    value_type ENUM('string', 'boolean', 'int', 'json') DEFAULT 'string' COMMENT '值类型',
    description VARCHAR(500) DEFAULT NULL COMMENT '配置说明',
    enabled BOOLEAN DEFAULT TRUE COMMENT '是否启用',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    updated_by VARCHAR(100) DEFAULT NULL COMMENT '更新人',

    UNIQUE KEY uk_config_key (config_key),
    KEY idx_enabled (enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统配置表';

-- =====================================================
-- 7. 决策历史记录表（可选，用于审计和分析）
-- =====================================================
CREATE TABLE IF NOT EXISTS decision_history (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
    decision_date DATE NOT NULL COMMENT '决策日期',
    account_id BIGINT NOT NULL COMMENT '账户ID',
    ad_id BIGINT NOT NULL COMMENT '广告ID',
    ad_name VARCHAR(500) DEFAULT NULL COMMENT '广告名称',
    action VARCHAR(50) NOT NULL COMMENT '决策动作（pause, bid_down, bid_up, scale_up, observe, hold）',
    dimension VARCHAR(100) DEFAULT NULL COMMENT '决策维度（roi, fission, decay等）',
    reason TEXT DEFAULT NULL COMMENT '决策理由',
    current_bid DECIMAL(10, 2) DEFAULT NULL COMMENT '当前出价（元）',
    recommended_bid DECIMAL(10, 2) DEFAULT NULL COMMENT '推荐出价（元）',
    recommended_change_pct DECIMAL(6, 2) DEFAULT NULL COMMENT '调整幅度（%）',
    roi_7d DECIMAL(10, 4) DEFAULT NULL COMMENT '7日ROI',
    dynamic_roi DECIMAL(10, 4) DEFAULT NULL COMMENT '动态ROI',
    cost_7d_avg DECIMAL(10, 2) DEFAULT NULL COMMENT '7日均消耗（元）',
    executed BOOLEAN DEFAULT FALSE COMMENT '是否已执行',
    execution_status VARCHAR(50) DEFAULT NULL COMMENT '执行状态（success, failed, pending, skipped）',
    execution_error TEXT DEFAULT NULL COMMENT '执行错误信息',
    executed_at TIMESTAMP NULL DEFAULT NULL COMMENT '执行时间',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

    KEY idx_decision_date (decision_date),
    KEY idx_account_id (account_id),
    KEY idx_ad_id (ad_id),
    KEY idx_action (action),
    KEY idx_executed (executed),
    KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='决策历史记录';

-- =====================================================
-- 初始化数据
-- =====================================================

-- 插入默认系统配置
INSERT INTO system_config (config_key, config_value, value_type, description) VALUES
('execution_enabled', 'false', 'boolean', '是否启用实际执行（生产环境谨慎开启）'),
('whitelist_enabled', 'true', 'boolean', '是否启用白名单机制'),
('cron_schedule', '0 2 * * *', 'string', 'APScheduler定时调度表达式（每天凌晨2点UTC）'),
('run_on_startup', 'false', 'boolean', '服务启动时是否立即执行一次'),
('max_concurrent_tasks', '1', 'int', '最大并发任务数'),
('approval_timeout_minutes', '30', 'int', '飞书审批超时时间（分钟）'),
('roi_low_factor', '0.75', 'string', '关停线系数（ROI < 渠道P50 * 0.75 触发关停）'),
('bid_down_roi_factor', '0.90', 'string', '降价线系数（ROI < 渠道P50 * 0.90 触发降价）'),
('bid_up_roi_factor', '1.05', 'string', '提价线系数（ROI > 渠道P50 * 1.05 触发提价）')
ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;

-- 插入示例白名单账户（请根据实际情况修改）
INSERT INTO account_whitelist (account_id, account_name, business_line, enabled, remark, created_by) VALUES
(80769799, '测试账户1', '小程序投流', TRUE, '初始白名单账户', 'system'),
(71305011, '测试账户2', '小程序投流', TRUE, '初始白名单账户', 'system')
ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;

-- =====================================================
-- 索引优化说明
-- =====================================================
-- 1. account_whitelist.uk_account_id: 保证账户唯一性
-- 2. system_config.uk_config_key: 保证配置键唯一性
-- 3. decision_history.idx_decision_date: 按日期查询决策记录
-- 4. decision_history.idx_ad_id: 查询某个广告的历史决策
-- 5. decision_history.idx_action: 按决策类型统计分析

-- =====================================================
-- 表分区建议（如数据量大）
-- =====================================================
-- ALTER TABLE decision_history PARTITION BY RANGE (YEAR(decision_date) * 100 + MONTH(decision_date)) (
--     PARTITION p202604 VALUES LESS THAN (202605),
--     PARTITION p202605 VALUES LESS THAN (202606),
--     PARTITION p202606 VALUES LESS THAN (202607),
--     PARTITION pmax VALUES LESS THAN MAXVALUE
-- );