-- 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='决策历史记录';

-- =====================================================
-- 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_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_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='创意素材使用历史/占位';

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

-- 插入默认系统配置
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
-- );