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,
    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 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,
    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_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,
    partner_name VARCHAR(255) DEFAULT NULL,
    official_account_name VARCHAR(255) 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,
    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,
    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,
    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_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 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,
    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,
    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,
    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,
    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='飞书运营控制命令广告明细';