Agent/examples/tencent_realtime_control/schema.sql

CREATE TABLE IF NOT EXISTS realtime_control_daily_state (
    control_date DATE NOT NULL PRIMARY KEY,
    morning_recovery_done BOOLEAN NOT NULL DEFAULT FALSE,
    cutoff_done BOOLEAN NOT NULL DEFAULT FALSE,
    last_observed_partition VARCHAR(20) DEFAULT NULL,
    last_observed_cpm DECIMAL(12, 4) DEFAULT NULL,
    last_decision VARCHAR(32) DEFAULT NULL,
    last_inventory_refresh_at DATETIME DEFAULT NULL,
    last_evaluated_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='实时CPM调控每日状态';

CREATE TABLE IF NOT EXISTS realtime_control_ad_state (
    account_id BIGINT NOT NULL,
    adgroup_id BIGINT NOT NULL,
    adgroup_name VARCHAR(255) DEFAULT NULL,
    bid_field VARCHAR(32) NOT NULL,
    base_bid_fen INT NOT NULL,
    initial_base_bid_fen INT DEFAULT NULL,
    boosted_date DATE DEFAULT NULL,
    bid_hold BOOLEAN NOT NULL DEFAULT FALSE,
    bid_hold_reason VARCHAR(255) DEFAULT NULL,
    last_roi_scaled_at DATETIME DEFAULT NULL,
    paused_by_strategy BOOLEAN NOT NULL DEFAULT FALSE,
    pause_reason VARCHAR(32) DEFAULT NULL,
    operator_pause_mode VARCHAR(32) DEFAULT NULL,
    operator_resume_at DATETIME DEFAULT NULL,
    operator_command_id VARCHAR(64) DEFAULT NULL,
    operator_paused_from_status VARCHAR(50) DEFAULT NULL,
    operator_paused_at DATETIME DEFAULT NULL,
    last_action VARCHAR(32) DEFAULT NULL,
    last_action_at DATETIME DEFAULT NULL,
    last_seen_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (account_id, adgroup_id),
    KEY idx_strategy_pause (paused_by_strategy),
    KEY idx_operator_pause (operator_pause_mode, operator_resume_at),
    KEY idx_boosted_date (boosted_date),
    KEY idx_last_seen (last_seen_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='实时CPM调控广告基准与状态';

CREATE TABLE IF NOT EXISTS realtime_control_account_scope (
    account_id BIGINT NOT NULL PRIMARY KEY,
    control_mode VARCHAR(32) NOT NULL,
    audience_name VARCHAR(255) DEFAULT NULL,
    bid_scene VARCHAR(64) DEFAULT NULL,
    enabled BOOLEAN NOT NULL DEFAULT TRUE,
    source VARCHAR(64) DEFAULT NULL,
    note VARCHAR(512) DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_realtime_scope_enabled_mode (enabled, control_mode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='实时CPM调控显式账户范围';

CREATE TABLE IF NOT EXISTS roi_metric_run (
    run_id VARCHAR(64) NOT NULL PRIMARY KEY,
    run_key VARCHAR(128) NOT NULL,
    metric_version VARCHAR(64) NOT NULL,
    policy_version VARCHAR(64) NOT NULL,
    fission_parameter_version VARCHAR(64) DEFAULT NULL,
    fission_cohort_date DATE DEFAULT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    config_json LONGTEXT NOT NULL,
    thresholds_json LONGTEXT DEFAULT NULL,
    status VARCHAR(32) NOT NULL,
    entity_count INT NOT NULL DEFAULT 0,
    candidate_count INT NOT NULL DEFAULT 0,
    actionable_count INT NOT NULL DEFAULT 0,
    sheet_token VARCHAR(128) DEFAULT NULL,
    sheet_url TEXT DEFAULT NULL,
    message_id VARCHAR(128) DEFAULT NULL,
    expires_at DATETIME DEFAULT NULL,
    approved_by VARCHAR(128) DEFAULT NULL,
    approved_at DATETIME DEFAULT NULL,
    rejected_by VARCHAR(128) DEFAULT NULL,
    rejected_at DATETIME DEFAULT NULL,
    executed_at DATETIME DEFAULT NULL,
    error_message TEXT DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_roi_run_key (run_key),
    KEY idx_roi_run_status_expiry (status, expires_at),
    KEY idx_roi_run_end_date (end_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='版本化ROI计算与逐行审批运行';

CREATE TABLE IF NOT EXISTS roi_fission_parameter_release (
    version VARCHAR(64) NOT NULL PRIMARY KEY,
    cohort_date DATE NOT NULL,
    observation_end_date DATE NOT NULL,
    horizon_days INT NOT NULL,
    run_suffix VARCHAR(32) NOT NULL,
    content_sha256 CHAR(64) NOT NULL,
    metadata_json LONGTEXT NOT NULL,
    published_by VARCHAR(128) DEFAULT NULL,
    published_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_roi_fission_parameter_content (content_sha256)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ROI传播裂变系数发布版本';

CREATE TABLE IF NOT EXISTS roi_fission_parameter_value (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    version VARCHAR(64) NOT NULL,
    entity_type VARCHAR(32) NOT NULL,
    match_level VARCHAR(64) NOT NULL,
    key_primary VARCHAR(255) NOT NULL DEFAULT '',
    key_secondary VARCHAR(255) NOT NULL DEFAULT '',
    multiplier DOUBLE NOT NULL,
    multiplier_vs_first DOUBLE DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_roi_fission_parameter_value
        (version, entity_type, match_level, key_primary, key_secondary),
    KEY idx_roi_fission_parameter_version (version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ROI传播裂变系数匹配明细';

CREATE TABLE IF NOT EXISTS roi_entity_snapshot (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    run_id VARCHAR(64) NOT NULL,
    entity_hash CHAR(64) NOT NULL,
    entity_type VARCHAR(32) NOT NULL,
    channel VARCHAR(128) DEFAULT NULL,
    account_id BIGINT DEFAULT NULL,
    account_name VARCHAR(255) DEFAULT NULL,
    adgroup_id BIGINT DEFAULT NULL,
    adgroup_name VARCHAR(255) DEFAULT NULL,
    dynamic_creative_id BIGINT DEFAULT NULL,
    audience_name VARCHAR(255) DEFAULT NULL,
    conversion_goal VARCHAR(255) DEFAULT NULL,
    partner_name VARCHAR(255) DEFAULT NULL,
    official_account_name VARCHAR(255) DEFAULT NULL,
    fission_parameter_version VARCHAR(64) DEFAULT NULL,
    fission_cohort_date VARCHAR(8) DEFAULT NULL,
    fission_multiplier_vs_t0 DECIMAL(18,8) DEFAULT NULL,
    fission_multiplier_vs_first DECIMAL(18,8) DEFAULT NULL,
    fission_match_level VARCHAR(64) DEFAULT NULL,
    fission_source VARCHAR(512) DEFAULT NULL,
    ad_age INT DEFAULT NULL,
    avg_first_uv DECIMAL(18,4) DEFAULT NULL,
    first_uv DECIMAL(18,4) DEFAULT NULL,
    t0_fission_count DECIMAL(18,4) DEFAULT NULL,
    t0_fission_rate DECIMAL(18,8) DEFAULT NULL,
    cost DECIMAL(20,4) DEFAULT NULL,
    efficiency_revenue DECIMAL(20,4) DEFAULT NULL,
    fission_revenue DECIMAL(20,4) DEFAULT NULL,
    actual_total_revenue DECIMAL(20,4) DEFAULT NULL,
    actual_roi DECIMAL(18,8) DEFAULT NULL,
    predicted_tail_revenue DECIMAL(20,4) DEFAULT NULL,
    predicted_fission_revenue DECIMAL(20,4) DEFAULT NULL,
    total_revenue DECIMAL(20,4) DEFAULT NULL,
    roi DECIMAL(18,8) DEFAULT NULL,
    stop_threshold DECIMAL(18,8) DEFAULT NULL,
    scale_threshold DECIMAL(18,8) DEFAULT NULL,
    recommended_action VARCHAR(64) DEFAULT NULL,
    action_reason TEXT DEFAULT NULL,
    execution_mode VARCHAR(32) NOT NULL,
    ineligible_reason TEXT DEFAULT NULL,
    daily_metrics_json LONGTEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_roi_entity (run_id, entity_hash),
    KEY idx_roi_entity_action (run_id, recommended_action),
    KEY idx_roi_entity_ad (account_id, adgroup_id),
    KEY idx_roi_entity_creative (account_id, dynamic_creative_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ROI实体指标快照';

CREATE TABLE IF NOT EXISTS roi_action_item (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    run_id VARCHAR(64) NOT NULL,
    idempotency_key VARCHAR(160) NOT NULL,
    action_type VARCHAR(32) NOT NULL,
    account_id BIGINT NOT NULL,
    adgroup_id BIGINT NOT NULL,
    dynamic_creative_id BIGINT DEFAULT NULL,
    bid_field VARCHAR(32) DEFAULT NULL,
    initial_base_bid_fen INT DEFAULT NULL,
    base_bid_fen INT DEFAULT NULL,
    before_bid_fen INT DEFAULT NULL,
    target_bid_fen INT DEFAULT NULL,
    before_status VARCHAR(50) DEFAULT NULL,
    target_status VARCHAR(50) DEFAULT NULL,
    readback_status VARCHAR(50) DEFAULT NULL,
    approval_status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
    approval_source VARCHAR(32) DEFAULT NULL,
    approved_at DATETIME DEFAULT NULL,
    rejected_at DATETIME DEFAULT NULL,
    sheet_row_number INT DEFAULT NULL,
    execution_status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
    skip_reason TEXT DEFAULT NULL,
    error_message TEXT DEFAULT NULL,
    pre_state_json LONGTEXT DEFAULT NULL,
    readback_json LONGTEXT DEFAULT NULL,
    executed_at DATETIME DEFAULT NULL,
    result_notified_at DATETIME DEFAULT NULL,
    notification_error TEXT DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_roi_action_idempotency (idempotency_key),
    KEY idx_roi_action_run_status (run_id, execution_status),
    KEY idx_roi_action_approval (run_id, approval_status),
    KEY idx_roi_action_ad (account_id, adgroup_id),
    KEY idx_roi_action_creative (account_id, dynamic_creative_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ROI审批后腾讯动作与审计';

CREATE TABLE IF NOT EXISTS roi_agency_delivery (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    run_id VARCHAR(64) NOT NULL,
    agency_name VARCHAR(255) NOT NULL,
    agency_report_version VARCHAR(64) NOT NULL,
    file_path TEXT NOT NULL,
    file_sha256 CHAR(64) NOT NULL,
    creative_rows INT NOT NULL DEFAULT 0,
    ad_rows INT NOT NULL DEFAULT 0,
    route_fingerprint CHAR(64) DEFAULT NULL,
    sheet_token VARCHAR(128) DEFAULT NULL,
    sheet_url TEXT DEFAULT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
    attempt_count INT NOT NULL DEFAULT 0,
    response_code VARCHAR(64) DEFAULT NULL,
    error_message TEXT DEFAULT NULL,
    sent_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_roi_agency_delivery
        (run_id, agency_name, agency_report_version),
    KEY idx_roi_agency_delivery_status (run_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ROI代理表上传与机器人通知审计';

CREATE TABLE IF NOT EXISTS creative_rejection_cleanup_item (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    account_id BIGINT NOT NULL,
    account_name VARCHAR(255) DEFAULT NULL,
    agency_name VARCHAR(255) DEFAULT NULL,
    adgroup_id BIGINT NOT NULL,
    adgroup_name VARCHAR(255) DEFAULT NULL,
    dynamic_creative_id BIGINT NOT NULL,
    dynamic_creative_name VARCHAR(255) DEFAULT NULL,
    check_date DATE NOT NULL,
    cleanup_action VARCHAR(32) DEFAULT NULL,
    target_component_ids_json LONGTEXT DEFAULT NULL,
    target_element_ids_json LONGTEXT DEFAULT NULL,
    recent_cost_fen BIGINT DEFAULT NULL,
    cost_start_date DATE DEFAULT NULL,
    cost_end_date DATE DEFAULT NULL,
    action_reason TEXT DEFAULT NULL,
    reject_reason TEXT NOT NULL,
    review_result_json LONGTEXT DEFAULT NULL,
    pre_state_json LONGTEXT DEFAULT NULL,
    readback_json LONGTEXT DEFAULT NULL,
    cleanup_status VARCHAR(32) NOT NULL DEFAULT 'DISCOVERED',
    error_message TEXT DEFAULT NULL,
    deleted_at DATETIME DEFAULT NULL,
    agency_notified_at DATETIME DEFAULT NULL,
    operator_notified_at DATETIME DEFAULT NULL,
    notified_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_creative_rejection_cleanup
        (account_id, dynamic_creative_id, check_date),
    KEY idx_creative_rejection_status (cleanup_status, notified_at),
    KEY idx_creative_rejection_agency (agency_name, notified_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='拒审创意清理及部分投放创意报警审计';

CREATE TABLE IF NOT EXISTS creative_rejection_delivery (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    run_id VARCHAR(64) NOT NULL,
    agency_name VARCHAR(255) NOT NULL,
    agency_report_version VARCHAR(64) NOT NULL,
    file_path TEXT NOT NULL,
    file_sha256 CHAR(64) NOT NULL,
    creative_rows INT NOT NULL DEFAULT 0,
    ad_rows INT NOT NULL DEFAULT 0,
    route_fingerprint CHAR(64) DEFAULT NULL,
    sheet_token VARCHAR(128) DEFAULT NULL,
    sheet_url TEXT DEFAULT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
    attempt_count INT NOT NULL DEFAULT 0,
    response_code VARCHAR(64) DEFAULT NULL,
    error_message TEXT DEFAULT NULL,
    sent_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_creative_rejection_delivery
        (run_id, agency_name, agency_report_version),
    KEY idx_creative_rejection_delivery_status (run_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='审核异常创意清理及人工报警通知审计';

CREATE TABLE IF NOT EXISTS realtime_control_action_log (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    run_id VARCHAR(36) NOT NULL,
    control_date DATE NOT NULL,
    observed_partition VARCHAR(20) DEFAULT NULL,
    observed_cpm DECIMAL(12, 4) DEFAULT NULL,
    decision VARCHAR(32) NOT NULL,
    account_id BIGINT NOT NULL,
    adgroup_id BIGINT NOT NULL,
    adgroup_name VARCHAR(255) DEFAULT NULL,
    bid_field VARCHAR(32) DEFAULT NULL,
    base_bid_fen INT DEFAULT NULL,
    before_bid_fen INT DEFAULT NULL,
    target_bid_fen INT DEFAULT NULL,
    before_status VARCHAR(50) DEFAULT NULL,
    target_status VARCHAR(50) DEFAULT NULL,
    apply_mode BOOLEAN NOT NULL DEFAULT FALSE,
    execution_status VARCHAR(32) NOT NULL,
    error_message TEXT DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_run_id (run_id),
    KEY idx_control_date (control_date),
    KEY idx_adgroup (account_id, adgroup_id),
    KEY idx_execution_status (execution_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='实时CPM调控动作审计';

CREATE TABLE IF NOT EXISTS operator_command (
    command_id VARCHAR(64) NOT NULL PRIMARY KEY,
    source_message_id VARCHAR(128) NOT NULL,
    chat_id VARCHAR(128) NOT NULL,
    sender_open_id VARCHAR(128) NOT NULL,
    sender_name VARCHAR(255) DEFAULT NULL,
    raw_text MEDIUMTEXT DEFAULT NULL,
    parse_source VARCHAR(32) NOT NULL DEFAULT 'deterministic',
    intent_json MEDIUMTEXT DEFAULT NULL,
    action VARCHAR(32) NOT NULL,
    scope_type VARCHAR(32) NOT NULL,
    target_account_ids TEXT NOT NULL,
    status VARCHAR(32) NOT NULL,
    preview_account_count INT NOT NULL DEFAULT 0,
    preview_ad_count INT NOT NULL DEFAULT 0,
    preview_cost_fen BIGINT NOT NULL DEFAULT 0,
    preview_impressions BIGINT NOT NULL DEFAULT 0,
    preview_clicks BIGINT NOT NULL DEFAULT 0,
    preview_conversions BIGINT NOT NULL DEFAULT 0,
    previewed_at DATETIME DEFAULT NULL,
    resume_at DATETIME DEFAULT NULL,
    expires_at DATETIME DEFAULT NULL,
    confirmed_at DATETIME DEFAULT NULL,
    executed_at DATETIME DEFAULT NULL,
    error_message TEXT DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_operator_source_message (source_message_id),
    KEY idx_operator_status_expiry (status, expires_at),
    KEY idx_operator_sender (sender_open_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='飞书运营控制命令';

CREATE TABLE IF NOT EXISTS operator_command_item (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    command_id VARCHAR(64) NOT NULL,
    account_id BIGINT NOT NULL,
    audience_name VARCHAR(200) DEFAULT NULL,
    adgroup_id BIGINT DEFAULT NULL,
    adgroup_name VARCHAR(255) DEFAULT NULL,
    before_status VARCHAR(50) DEFAULT NULL,
    target_status VARCHAR(50) DEFAULT NULL,
    preview_cost_fen BIGINT NOT NULL DEFAULT 0,
    preview_impressions BIGINT NOT NULL DEFAULT 0,
    preview_clicks BIGINT NOT NULL DEFAULT 0,
    preview_conversions BIGINT NOT NULL DEFAULT 0,
    previewed_at DATETIME DEFAULT NULL,
    readback_status VARCHAR(50) DEFAULT NULL,
    execution_status VARCHAR(32) NOT NULL,
    error_message TEXT DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_operator_item_command (command_id),
    KEY idx_operator_item_ad (account_id, adgroup_id),
    KEY idx_operator_item_status (execution_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='飞书运营控制命令广告明细';

CREATE TABLE IF NOT EXISTS operator_command_draft (
    draft_id VARCHAR(64) NOT NULL PRIMARY KEY,
    chat_id VARCHAR(128) NOT NULL,
    sender_open_id VARCHAR(128) NOT NULL,
    raw_text MEDIUMTEXT DEFAULT NULL,
    action VARCHAR(32) DEFAULT NULL,
    scope_type VARCHAR(32) NOT NULL DEFAULT 'MISSING',
    account_ids TEXT NOT NULL,
    missing_fields TEXT NOT NULL,
    source_message_ids TEXT NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
    expires_at DATETIME NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_operator_draft_conversation (chat_id, sender_open_id),
    KEY idx_operator_draft_status_expiry (status, expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='飞书运营命令多轮草稿';

CREATE TABLE IF NOT EXISTS revenue_forecast_observation (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    source_partition VARCHAR(8) NOT NULL,
    source_version VARCHAR(64) NOT NULL,
    report_time DATETIME NOT NULL,
    today_revenue DECIMAL(20, 4) NOT NULL,
    overall_cpm DECIMAL(18, 6) DEFAULT NULL,
    impressions BIGINT DEFAULT NULL,
    dau BIGINT DEFAULT NULL,
    fill_rate DECIMAL(12, 6) DEFAULT NULL,
    revenue_change_pct DECIMAL(12, 6) DEFAULT NULL,
    cpm_change_pct DECIMAL(12, 6) DEFAULT NULL,
    exposure_change_pct DECIMAL(12, 6) DEFAULT NULL,
    dau_change_pct DECIMAL(12, 6) DEFAULT NULL,
    fill_rate_change_pct DECIMAL(12, 6) DEFAULT NULL,
    source_modified_at DATETIME DEFAULT NULL,
    lag_seconds INT NOT NULL,
    quality_status VARCHAR(32) NOT NULL,
    quality_message VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_revenue_observation_version_time (report_time, source_version),
    KEY idx_revenue_observation_quality (quality_status, report_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='实时业务收入原始快照';

CREATE TABLE IF NOT EXISTS revenue_forecast_result (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    report_time DATETIME NOT NULL,
    forecast_version VARCHAR(64) NOT NULL,
    forecast_method VARCHAR(64) NOT NULL,
    parameter_version VARCHAR(64) NOT NULL,
    current_cumulative_revenue DECIMAL(20, 4) NOT NULL,
    speed_latest_weight DECIMAL(12, 8) NOT NULL,
    latest_interval_revenue DECIMAL(20, 4) NOT NULL,
    previous_interval_revenue DECIMAL(20, 4) NOT NULL,
    weighted_speed DECIMAL(20, 4) NOT NULL,
    remaining_multiplier_p10 DECIMAL(20, 8) NOT NULL,
    remaining_multiplier_p50 DECIMAL(20, 8) NOT NULL,
    remaining_multiplier_p90 DECIMAL(20, 8) NOT NULL,
    parameter_sample_count INT NOT NULL,
    forecast_revenue DECIMAL(20, 4) NOT NULL,
    forecast_lower DECIMAL(20, 4) NOT NULL,
    forecast_upper DECIMAL(20, 4) NOT NULL,
    target_cost_ratio DECIMAL(12, 6) NOT NULL,
    target_daily_cost DECIMAL(20, 4) NOT NULL,
    cost_reserve_date DATE DEFAULT NULL,
    channel_costs_json TEXT DEFAULT NULL,
    non_miniapp_reserved_cost DECIMAL(20, 4) NOT NULL DEFAULT 0,
    miniapp_target_daily_cost DECIMAL(20, 4) NOT NULL DEFAULT 0,
    forecast_status VARCHAR(32) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_revenue_forecast_result (report_time, forecast_version),
    KEY idx_revenue_forecast_version (forecast_version, report_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='实时业务收入预测结果';

CREATE TABLE IF NOT EXISTS revenue_forecast_speed_sample (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    data_date DATE NOT NULL,
    time_slot TIME NOT NULL,
    parameter_version VARCHAR(64) NOT NULL,
    cumulative_revenue DECIMAL(20, 4) NOT NULL,
    final_revenue DECIMAL(20, 4) NOT NULL,
    latest_interval_revenue DECIMAL(20, 4) NOT NULL,
    previous_interval_revenue DECIMAL(20, 4) NOT NULL,
    latest_weight DECIMAL(12, 8) NOT NULL,
    weighted_speed DECIMAL(20, 4) NOT NULL,
    remaining_revenue DECIMAL(20, 4) NOT NULL,
    remaining_multiplier DECIMAL(20, 8) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_revenue_speed_sample
        (parameter_version, data_date, time_slot),
    KEY idx_revenue_speed_sample_date (data_date, time_slot)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='收入速度模型历史日样本';

CREATE TABLE IF NOT EXISTS revenue_forecast_speed_parameter (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    parameter_version VARCHAR(64) NOT NULL,
    time_slot TIME NOT NULL,
    training_start DATE NOT NULL,
    training_end DATE NOT NULL,
    sample_count INT NOT NULL,
    latest_weight DECIMAL(12, 8) NOT NULL,
    multiplier_p10 DECIMAL(20, 8) NOT NULL,
    multiplier_p50 DECIMAL(20, 8) NOT NULL,
    multiplier_p90 DECIMAL(20, 8) NOT NULL,
    multiplier_mad DECIMAL(20, 8) NOT NULL,
    published_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_revenue_speed_parameter
        (parameter_version, time_slot),
    KEY idx_revenue_speed_parameter_training
        (training_start, training_end)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='已发布收入速度模型参数';