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',
    material_source VARCHAR(50) NOT NULL DEFAULT 'history' COMMENT '创意素材来源:history/ai_generated',
    ai_fallback_to_history BOOLEAN NOT NULL DEFAULT FALSE COMMENT 'AI生成失败时是否回退历史素材',
    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则按范围取值',
    bid_scene VARCHAR(50) DEFAULT NULL COMMENT '出价方式:average_cost/max_conversion',
    custom_cost_cap_fen INT DEFAULT NULL COMMENT '最大转化量控制成本(分),用于 custom_cost_cap',
    daily_budget_fen INT DEFAULT NULL COMMENT '账户级单广告日预算(分);NULL则使用投放模板默认预算',
    automatic_site_enabled BOOLEAN DEFAULT NULL COMMENT '账户级是否开启智能版位;NULL使用模板默认手动版位',
    site_set_json TEXT DEFAULT NULL COMMENT '账户级投放版位覆盖JSON;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_material_source (material_source),
    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='决策历史记录';

-- =====================================================
-- 8. 创意创建与审核扫描任务表
-- =====================================================
CREATE TABLE IF NOT EXISTS creative_creation_task (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
    account_id BIGINT NOT NULL COMMENT '腾讯广告账户ID',
    adgroup_id BIGINT DEFAULT NULL COMMENT '广告ID',
    dynamic_creative_id BIGINT NOT NULL COMMENT '动态创意ID',
    dynamic_creative_name VARCHAR(200) DEFAULT NULL COMMENT '动态创意名称',
    landing_video_id BIGINT DEFAULT NULL COMMENT '承接视频ID',
    material_id VARCHAR(100) DEFAULT NULL COMMENT '内部素材ID',
    material_image_id VARCHAR(100) DEFAULT NULL COMMENT '腾讯图片ID',
    review_status VARCHAR(50) NOT NULL DEFAULT 'submitted' COMMENT '审核状态',
    submit_status VARCHAR(50) NOT NULL DEFAULT 'submitted' COMMENT '提交状态',
    submitted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '提交时间',
    last_review_checked_at TIMESTAMP NULL DEFAULT NULL COMMENT '最近审核扫描时间',
    review_finished_at TIMESTAMP NULL DEFAULT NULL COMMENT '审核完成时间',
    error TEXT DEFAULT NULL COMMENT '提交或扫描错误',
    raw_record MEDIUMTEXT DEFAULT NULL COMMENT '创建记录JSON',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

    UNIQUE KEY uk_account_creative (account_id, dynamic_creative_id),
    KEY idx_review_status (review_status),
    KEY idx_submitted_at (submitted_at),
    KEY idx_landing_submitted (landing_video_id, submitted_at),
    KEY idx_last_review_checked_at (last_review_checked_at),
    KEY idx_account_status (account_id, review_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='创意创建与审核扫描任务';

-- =====================================================
-- 9. 腾讯动态创意审核结果表
-- =====================================================
CREATE TABLE IF NOT EXISTS creative_review_result (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
    account_id BIGINT NOT NULL COMMENT '腾讯广告账户ID',
    dynamic_creative_id BIGINT NOT NULL COMMENT '动态创意ID',
    review_status VARCHAR(50) NOT NULL COMMENT '解析后的审核状态',
    reject_messages MEDIUMTEXT DEFAULT NULL COMMENT '拒绝原因JSON数组',
    delay_messages MEDIUMTEXT DEFAULT NULL COMMENT '延迟审核原因JSON数组',
    raw_result MEDIUMTEXT NOT NULL COMMENT '腾讯审核结果原文JSON',
    checked_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '扫描时间',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

    UNIQUE KEY uk_account_creative (account_id, dynamic_creative_id),
    KEY idx_review_status (review_status),
    KEY idx_checked_at (checked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='腾讯动态创意审核结果';

-- =====================================================
-- 10. 创意审核拒绝事实表
-- =====================================================
CREATE TABLE IF NOT EXISTS creative_rejection_fact (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
    account_id BIGINT NOT NULL COMMENT '腾讯广告账户ID',
    dynamic_creative_id BIGINT NOT NULL COMMENT '动态创意ID',
    fact_type VARCHAR(50) NOT NULL COMMENT '拒绝事实类型:site/element/compose',
    fact_key VARCHAR(255) DEFAULT NULL COMMENT '元素/版位/组件标识',
    reason TEXT NOT NULL COMMENT '拒绝原因',
    site_set VARCHAR(100) DEFAULT NULL COMMENT '影响版位',
    element_type VARCHAR(100) DEFAULT NULL COMMENT '元素类型',
    component_type VARCHAR(100) DEFAULT NULL COMMENT '组件类型',
    raw_detail MEDIUMTEXT DEFAULT NULL COMMENT '原始明细JSON',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

    KEY idx_account_creative (account_id, dynamic_creative_id),
    KEY idx_fact_type (fact_type),
    KEY idx_fact_key (fact_key(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='创意审核拒绝事实';

-- =====================================================
-- 11. 视频元素特征缓存表（ODPS 最新分区缓存）
-- =====================================================
CREATE TABLE IF NOT EXISTS video_element_feature_cache (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
    video_id BIGINT NOT NULL COMMENT '业务视频ID，对应 ODPS vid',
    dt VARCHAR(8) NOT NULL COMMENT 'ODPS 分区日期 YYYYMMDD',
    point_type VARCHAR(50) NOT NULL COMMENT '点类型',
    standard_element VARCHAR(255) NOT NULL COMMENT '标准化元素',
    element_dimension VARCHAR(50) NOT NULL DEFAULT '实质' COMMENT '元素维度',
    contribution_score DOUBLE NOT NULL DEFAULT 0 COMMENT '贡献分',
    is_miss BOOLEAN NOT NULL DEFAULT FALSE COMMENT '是否为无特征负缓存',
    raw_element VARCHAR(255) DEFAULT NULL COMMENT '原始元素名称',
    element_id VARCHAR(100) DEFAULT NULL COMMENT '标准化元素ID',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

    UNIQUE KEY uk_dt_video_point_element (dt, video_id, point_type, standard_element),
    KEY idx_video_dt (video_id, dt),
    KEY idx_dt (dt),
    KEY idx_updated_at (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='视频元素特征缓存';

-- =====================================================
-- 12. 创意素材使用历史/占位表
-- =====================================================
CREATE TABLE IF NOT EXISTS creative_material_usage (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
    account_id BIGINT NOT NULL COMMENT '腾讯广告账户ID',
    adgroup_id BIGINT DEFAULT NULL COMMENT '广告ID',
    crowd_package VARCHAR(200) NOT NULL COMMENT '投放人群包名称',
    landing_video_id BIGINT DEFAULT NULL COMMENT '承接视频ID,用于同人群包近期排重',
    material_id VARCHAR(100) NOT NULL COMMENT '内部素材ID',
    material_image_id VARCHAR(100) DEFAULT NULL COMMENT '腾讯图片ID',
    dynamic_creative_id BIGINT DEFAULT NULL COMMENT '腾讯动态创意ID',
    status VARCHAR(50) NOT NULL DEFAULT 'prepared' COMMENT 'prepared/submitted/failed',
    source VARCHAR(50) DEFAULT NULL COMMENT 'primary/hot',
    raw_record MEDIUMTEXT DEFAULT NULL COMMENT '准备记录JSON',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

    KEY idx_crowd_material_created (crowd_package, material_id, created_at),
    KEY idx_crowd_landing_created (crowd_package, landing_video_id, created_at),
    KEY idx_crowd_account_ad_material (crowd_package, account_id, adgroup_id, material_id),
    KEY idx_account_created (account_id, created_at),
    KEY idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='创意素材使用历史/占位';

-- =====================================================
-- 13. AI生成创意图片素材
-- =====================================================
CREATE TABLE IF NOT EXISTS ai_generated_material (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
    account_id BIGINT NOT NULL COMMENT '腾讯广告账户ID',
    adgroup_id BIGINT DEFAULT NULL COMMENT '广告ID',
    crowd_package VARCHAR(200) NOT NULL COMMENT '投放人群包名称',
    landing_video_id BIGINT NOT NULL COMMENT '承接视频ID',
    landing_title VARCHAR(500) DEFAULT NULL COMMENT '承接视频标题',
    landing_category VARCHAR(200) DEFAULT NULL COMMENT '承接视频品类',
    prompt_type VARCHAR(50) NOT NULL COMMENT '生成角度:topic',
    prompt_text MEDIUMTEXT NOT NULL COMMENT '最终生成prompt',
    prompt_feature_json MEDIUMTEXT DEFAULT NULL COMMENT 'prompt使用的视频特征JSON',
    model VARCHAR(200) NOT NULL COMMENT 'OpenRouter模型',
    oss_object_key VARCHAR(500) DEFAULT NULL COMMENT 'OSS对象key',
    oss_url VARCHAR(1000) DEFAULT NULL COMMENT 'OSS公网URL',
    status VARCHAR(50) NOT NULL DEFAULT 'generated' COMMENT 'generated/prepared/approved/rejected/hold/skip/posted_ok/post_failed/error',
    approval_status VARCHAR(50) DEFAULT NULL COMMENT '人工审批状态',
    tencent_image_id VARCHAR(100) DEFAULT NULL COMMENT '腾讯图片ID',
    dynamic_creative_id BIGINT DEFAULT NULL COMMENT '腾讯动态创意ID',
    ai_review_status VARCHAR(50) DEFAULT NULL COMMENT 'AI审核状态:pass/reject/hold/error',
    ai_review_score INT DEFAULT NULL COMMENT 'AI审核评分0-100',
    ai_review_model VARCHAR(200) DEFAULT NULL COMMENT 'AI审核模型',
    ai_review_reason TEXT DEFAULT NULL COMMENT 'AI审核原因摘要',
    ai_review_json MEDIUMTEXT DEFAULT NULL COMMENT 'AI审核原始JSON',
    ai_reviewed_at TIMESTAMP NULL DEFAULT NULL COMMENT 'AI审核时间',
    error TEXT DEFAULT NULL COMMENT '生成/上传/创建错误',
    raw_response MEDIUMTEXT DEFAULT NULL COMMENT '模型原始响应摘要',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

    KEY idx_account_ad_landing_status (account_id, adgroup_id, landing_video_id, status),
    KEY idx_crowd_created (crowd_package, created_at),
    KEY idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI生成创意图片素材';

-- =====================================================
-- 14. 高消耗素材表现快照任务
-- =====================================================
CREATE TABLE IF NOT EXISTS material_performance_snapshot_run (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
    run_id VARCHAR(64) NOT NULL COMMENT '快照任务ID',
    window_start DATE NOT NULL COMMENT '统计窗口开始日期',
    window_end DATE NOT NULL COMMENT '统计窗口结束日期',
    top_n INT NOT NULL DEFAULT 5000 COMMENT '拉取TopN',
    source VARCHAR(64) NOT NULL DEFAULT 'odps' COMMENT '数据来源',
    sql_file VARCHAR(255) DEFAULT NULL COMMENT 'SQL文件路径',
    row_count INT NOT NULL DEFAULT 0 COMMENT '明细行数',
    total_cost_fen BIGINT NOT NULL DEFAULT 0 COMMENT '窗口内总消耗(分)',
    status VARCHAR(32) NOT NULL DEFAULT 'SUCCESS' COMMENT '任务状态',
    error_message MEDIUMTEXT DEFAULT NULL COMMENT '错误信息',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

    UNIQUE KEY uk_run_id (run_id),
    KEY idx_window (window_start, window_end)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='高消耗素材表现快照任务';

-- =====================================================
-- 15. 高消耗素材表现快照明细
-- =====================================================
CREATE TABLE IF NOT EXISTS material_performance_snapshot_item (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
    run_id VARCHAR(64) NOT NULL COMMENT '快照任务ID',
    rank_no INT NOT NULL COMMENT '消耗排名',
    account_id BIGINT NOT NULL COMMENT '腾讯广告账户ID',
    ad_id BIGINT NOT NULL COMMENT '广告ID',
    creative_id BIGINT NOT NULL COMMENT '创意ID',
    creative_name VARCHAR(255) DEFAULT NULL COMMENT '创意名称',
    ad_name VARCHAR(255) DEFAULT NULL COMMENT '广告名称',
    video_id BIGINT DEFAULT NULL COMMENT '承接视频ID',
    title VARCHAR(512) DEFAULT NULL COMMENT '素材标题',
    image_url VARCHAR(1024) DEFAULT NULL COMMENT '素材图片URL',
    image_hash VARCHAR(64) DEFAULT NULL COMMENT '图片内容hash',
    crowd_package VARCHAR(255) DEFAULT NULL COMMENT '人群包名称',
    optimization_goal VARCHAR(128) DEFAULT NULL COMMENT '优化目标',
    bid_amount_fen BIGINT DEFAULT NULL COMMENT '出价(分)',
    day_amount_fen BIGINT DEFAULT NULL COMMENT '日预算(分)',
    cost_fen BIGINT NOT NULL DEFAULT 0 COMMENT '消耗(分)',
    impressions BIGINT NOT NULL DEFAULT 0 COMMENT '曝光',
    clicks BIGINT NOT NULL DEFAULT 0 COMMENT '点击',
    ctr DECIMAL(10, 6) DEFAULT NULL COMMENT '点击率',
    key_page_view_count BIGINT NOT NULL DEFAULT 0 COMMENT '关键页访问次数',
    key_page_rate DECIMAL(10, 6) DEFAULT NULL COMMENT '关键页访问/点击',
    conversions_count BIGINT NOT NULL DEFAULT 0 COMMENT '转化数',
    conversion_rate DECIMAL(10, 6) DEFAULT NULL COMMENT '转化率',
    active_days INT NOT NULL DEFAULT 0 COMMENT '有消耗天数',
    first_dt VARCHAR(16) DEFAULT NULL COMMENT '首次投放日期',
    last_dt VARCHAR(16) DEFAULT NULL COMMENT '最后投放日期',
    raw_json MEDIUMTEXT DEFAULT NULL COMMENT '原始行JSON',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

    UNIQUE KEY uk_run_creative (run_id, creative_id),
    KEY idx_creative (creative_id),
    KEY idx_image_hash (image_hash),
    KEY idx_video (video_id),
    KEY idx_package_cost (crowd_package, cost_fen)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='高消耗素材表现快照明细';

-- =====================================================
-- 16. 素材图片结构化标注
-- =====================================================
CREATE TABLE IF NOT EXISTS material_visual_annotation (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
    image_hash VARCHAR(64) NOT NULL COMMENT '图片内容hash',
    image_url VARCHAR(1024) NOT NULL COMMENT '素材图片URL',
    annotation_version VARCHAR(64) NOT NULL COMMENT '标注版本',
    annotator VARCHAR(64) NOT NULL COMMENT '标注来源',
    creative_id BIGINT DEFAULT NULL COMMENT '样本创意ID',
    visual_template VARCHAR(128) DEFAULT NULL COMMENT '视觉模板',
    hook_category VARCHAR(255) DEFAULT NULL COMMENT '标题钩子分类',
    title_text VARCHAR(512) DEFAULT NULL COMMENT '图片/素材标题',
    title_length INT DEFAULT NULL COMMENT '标题长度',
    scene_type VARCHAR(128) DEFAULT NULL COMMENT '场景类型',
    person_type VARCHAR(128) DEFAULT NULL COMMENT '人物/主体估计',
    has_human TINYINT DEFAULT NULL COMMENT '是否有人物',
    text_area_level VARCHAR(32) DEFAULT NULL COMMENT '文字面积估计等级',
    color_style VARCHAR(128) DEFAULT NULL COMMENT '颜色/调性',
    button_like_element TINYINT NOT NULL DEFAULT 0 COMMENT '是否疑似按钮诱导',
    fake_ui_risk TINYINT NOT NULL DEFAULT 0 COMMENT '假界面风险',
    official_policy_risk TINYINT NOT NULL DEFAULT 0 COMMENT '官方/政策承诺风险',
    medical_health_risk TINYINT NOT NULL DEFAULT 0 COMMENT '医疗健康风险',
    politics_sensitive_risk TINYINT NOT NULL DEFAULT 0 COMMENT '涉政/国家情绪风险',
    celebrity_or_history_risk TINYINT NOT NULL DEFAULT 0 COMMENT '名人/历史人物风险',
    strong_inducement_risk TINYINT NOT NULL DEFAULT 0 COMMENT '强诱导风险',
    greeting_blessing_risk TINYINT NOT NULL DEFAULT 0 COMMENT '早晚安/祝福风险',
    compliance_level VARCHAR(32) NOT NULL DEFAULT 'caution' COMMENT '生成可学习等级',
    learnable_points MEDIUMTEXT DEFAULT NULL COMMENT '可学习点',
    avoid_points MEDIUMTEXT DEFAULT NULL COMMENT '避让点',
    raw_annotation MEDIUMTEXT DEFAULT NULL COMMENT '原始标注JSON',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

    UNIQUE KEY uk_image_version (image_hash, annotation_version),
    KEY idx_creative (creative_id),
    KEY idx_template (visual_template),
    KEY idx_compliance (compliance_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='素材图片结构化标注';

-- =====================================================
-- 17. AI生成素材可学习创意模式
-- =====================================================
CREATE TABLE IF NOT EXISTS material_creative_pattern (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
    pattern_version VARCHAR(64) NOT NULL COMMENT '策略版本',
    pattern_key VARCHAR(128) NOT NULL COMMENT '策略唯一键',
    pattern_name VARCHAR(128) NOT NULL COMMENT '策略名称',
    hook_category VARCHAR(128) NOT NULL COMMENT '标题钩子类型',
    visual_template VARCHAR(128) NOT NULL COMMENT '视觉模板',
    applicable_crowd_packages VARCHAR(1024) DEFAULT NULL COMMENT '适用人群包',
    applicable_placements VARCHAR(1024) DEFAULT NULL COMMENT '适用版位',
    target_age_min INT DEFAULT NULL COMMENT '适用年龄下限',
    target_age_max INT DEFAULT NULL COMMENT '适用年龄上限',
    title_hook_rule MEDIUMTEXT NOT NULL COMMENT '标题钩子规则',
    visual_rule MEDIUMTEXT NOT NULL COMMENT '视觉规则',
    relevance_rule MEDIUMTEXT NOT NULL COMMENT '视频相关性规则',
    compliance_rule MEDIUMTEXT NOT NULL COMMENT '合规规则',
    selector_config MEDIUMTEXT DEFAULT NULL COMMENT '选择器配置JSON:match/exclude/score',
    positive_examples MEDIUMTEXT DEFAULT NULL COMMENT '正例JSON',
    negative_examples MEDIUMTEXT DEFAULT NULL COMMENT '反例JSON',
    source_run_id VARCHAR(64) DEFAULT NULL COMMENT '来源快照ID',
    source_material_count INT NOT NULL DEFAULT 0 COMMENT '来源素材数',
    source_total_cost_fen BIGINT NOT NULL DEFAULT 0 COMMENT '来源素材消耗(分)',
    status VARCHAR(32) NOT NULL DEFAULT 'DRAFT' COMMENT 'DRAFT/APPROVED/REJECTED',
    reviewed_by VARCHAR(64) DEFAULT NULL COMMENT '审核人',
    reviewed_at TIMESTAMP NULL DEFAULT NULL COMMENT '审核时间',
    enabled TINYINT NOT NULL DEFAULT 0 COMMENT '是否启用',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

    UNIQUE KEY uk_version_key (pattern_version, pattern_key),
    KEY idx_status_enabled (status, enabled),
    KEY idx_hook_template (hook_category, visual_template)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI生成素材可学习创意模式';

-- =====================================================
-- 18. 素材策略学习周报
-- =====================================================
CREATE TABLE IF NOT EXISTS material_strategy_learning_report (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
    report_id VARCHAR(64) NOT NULL COMMENT '报告ID',
    run_id VARCHAR(64) NOT NULL COMMENT '快照任务ID',
    report_version VARCHAR(64) NOT NULL COMMENT '报告版本',
    summary MEDIUMTEXT NOT NULL COMMENT '报告摘要',
    top_patterns MEDIUMTEXT DEFAULT NULL COMMENT '核心模式JSON',
    risk_summary MEDIUMTEXT DEFAULT NULL COMMENT '风险摘要JSON',
    recommended_actions MEDIUMTEXT DEFAULT NULL COMMENT '建议动作JSON',
    report_path VARCHAR(512) DEFAULT NULL COMMENT '本地报告路径',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

    UNIQUE KEY uk_report_id (report_id),
    KEY idx_run_id (run_id)
) 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', '飞书审批超时时间（分钟）'),
('creative_review_scan_interval_hours', '2', '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
-- );