tencent-baidu-tracking/src/main/resources/schema-vivo.sql

CREATE TABLE IF NOT EXISTS vivo_ad_bid_events (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    qk VARCHAR(128) NOT NULL COMMENT '唯一业务键(query key)',
    media VARCHAR(64) NOT NULL COMMENT '媒体来源(vivo)',
    media_trace_id VARCHAR(255) COMMENT '媒体追踪ID(callback/设备标识)',
    platform VARCHAR(16) COMMENT '平台(android/ios)',
    account_id VARCHAR(128) COMMENT 'vivo 投放账户ID',
    event_type VARCHAR(32) COMMENT '事件类型(impression/click)，仅保留排障所需',
    tag_id VARCHAR(128) NOT NULL COMMENT '百度广告位ID',
    price BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '竞价价格(分)',
    package_name VARCHAR(255) COMMENT '应用包名',
    media_params JSON COMMENT '媒体参数精简白名单(仅保留 clickId/srcId/pkgName/设备号/ts/deduction_rate)',
    created_at TIMESTAMP(3) NOT NULL COMMENT '创建时间',
    UNIQUE KEY uk_vivo_ad_bid_events_qk (qk),
    KEY idx_vivo_ad_bid_events_media_trace (media, media_trace_id),
    KEY idx_vivo_ad_bid_events_platform_tag (platform, tag_id),
    KEY idx_vivo_ad_bid_events_account_created_at (account_id, created_at),
    KEY idx_vivo_ad_bid_events_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='vivo竞价事件记录';

CREATE TABLE IF NOT EXISTS vivo_tracking_reports (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    qk VARCHAR(128) COMMENT '关联的业务键',
    tag_id VARCHAR(128) NOT NULL COMMENT '百度广告位ID',
    kind VARCHAR(32) NOT NULL COMMENT '类型(impression/click)',
    tracking_url TEXT NOT NULL COMMENT '上报的目标URL',
    status INT NOT NULL COMMENT 'HTTP响应状态码',
    ok TINYINT(1) NOT NULL COMMENT '是否成功(1=成功,0=失败)',
    created_at TIMESTAMP(3) NOT NULL COMMENT '创建时间',
    KEY idx_vivo_tracking_reports_qk_kind_created_at (qk, kind, created_at),
    KEY idx_vivo_tracking_reports_tag_created_at (tag_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='vivo曝光/点击上报结果';

CREATE TABLE IF NOT EXISTS vivo_media_callbacks (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    dedupe_key VARCHAR(255) NOT NULL COMMENT '去重键',
    qk VARCHAR(128) COMMENT '关联的业务键',
    tag_id VARCHAR(128) NOT NULL COMMENT '百度广告位ID',
    callback_url TEXT NOT NULL COMMENT '回传的API URL',
    event_type VARCHAR(64) NOT NULL COMMENT 'vivo 回传事件类型',
    event_time_ms BIGINT NOT NULL COMMENT '事件时间(毫秒时间戳)',
    purchase DOUBLE COMMENT '付费金额',
    deduction_rate INT NOT NULL DEFAULT 0 COMMENT '扣量比例(0-100)',
    is_direct_match TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否直归因(1=是,0=否)',
    dispatch_status VARCHAR(32) NOT NULL DEFAULT 'SENT' COMMENT '回传处理状态(SENT/DEDUCTED)',
    status INT NOT NULL COMMENT 'HTTP响应状态码',
    ok TINYINT(1) NOT NULL COMMENT '是否成功(1=成功,0=失败)',
    attempt INT NOT NULL DEFAULT 1 COMMENT '尝试次数',
    response_body TEXT COMMENT '响应体(截断)',
    error_message TEXT COMMENT '错误信息',
    request_body TEXT COMMENT '请求体',
    created_at TIMESTAMP(3) NOT NULL COMMENT '创建时间',
    KEY idx_vivo_media_callbacks_dedupe_ok_created_at (dedupe_key, ok, created_at),
    KEY idx_vivo_media_callbacks_qk_created_at (qk, created_at),
    KEY idx_vivo_media_callbacks_tag_created_at (tag_id, created_at),
    KEY idx_vivo_media_callbacks_event_created_at (event_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='vivo媒体转化回传记录';

CREATE TABLE IF NOT EXISTS vivo_tag_event (
    tag_id VARCHAR(128) NOT NULL COMMENT '百度广告位ID',
    baidu_act INT NOT NULL COMMENT '百度转化行为',
    event_type VARCHAR(64) NOT NULL COMMENT 'vivo 回传事件类型',
    PRIMARY KEY (tag_id, baidu_act, event_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='vivo广告位回传事件配置';

CREATE TABLE IF NOT EXISTS vivo_account_tokens (
    account_id VARCHAR(128) NOT NULL COMMENT 'vivo 二代账户ID',
    account_name VARCHAR(255) COMMENT 'vivo 二代账户名称',
    account_level INT COMMENT '账户等级(level)',
    account_status INT COMMENT '账户状态(status)',
    client_id VARCHAR(128) COMMENT '本次授权使用的vivo API client_id快照',
    client_secret VARCHAR(1024) COMMENT '本次授权使用的vivo API client_secret快照',
    access_token VARCHAR(1024) COMMENT 'access_token',
    refresh_token VARCHAR(1024) COMMENT 'refresh_token',
    token_expire_at BIGINT COMMENT 'access_token 过期时间(毫秒时间戳)',
    refresh_token_expire_at BIGINT COMMENT 'refresh_token 过期时间(毫秒时间戳)',
    authorized_at TIMESTAMP(3) NULL COMMENT '授权完成时间',
    updated_at TIMESTAMP(3) NOT NULL COMMENT '更新时间',
    PRIMARY KEY (account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='vivo 二代账户授权 token';

CREATE TABLE IF NOT EXISTS vivo_client_configs (
    client_id VARCHAR(128) NOT NULL COMMENT 'vivo API client_id',
    client_secret VARCHAR(1024) NOT NULL COMMENT 'vivo API client_secret',
    client_name VARCHAR(255) COMMENT '凭证名称/备注',
    enabled TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否启用(1=启用,0=停用)',
    updated_at TIMESTAMP(3) NOT NULL COMMENT '更新时间',
    PRIMARY KEY (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='vivo开放平台凭证配置';

CREATE TABLE IF NOT EXISTS vivo_advertiser_accounts (
    advertiser_id VARCHAR(128) NOT NULL COMMENT 'vivo 广告主账户ID(advertiser_id)',
    secondary_account_id VARCHAR(128) NOT NULL COMMENT '所属vivo二代账户ID',
    advertiser_name VARCHAR(255) COMMENT 'vivo 广告主名称',
    advertiser_status INT COMMENT '广告主状态(status)',
    last_sync_at TIMESTAMP(3) NULL COMMENT '最近同步时间',
    updated_at TIMESTAMP(3) NOT NULL COMMENT '更新时间',
    PRIMARY KEY (advertiser_id),
    KEY idx_vivo_advertiser_accounts_secondary (secondary_account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='vivo 广告主到账户映射';