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

-- ============================================================
-- tencent-baidu-tracking 数据库建表语句
-- 数据库: adx_tencent
-- ============================================================

CREATE DATABASE IF NOT EXISTS adx_tencent DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE adx_tencent;

-- ------------------------------------------------------------
-- 1. 竞价事件表（百度ADX竞价记录）
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tencent_ad_bid_events (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    qk VARCHAR(128) NOT NULL COMMENT '唯一业务键(query key)',
    media VARCHAR(64) COMMENT '媒体来源(tencent)',
    media_trace_id VARCHAR(255) COMMENT '媒体追踪ID(click_id/oaid/imei/idfa)',
    platform VARCHAR(16) COMMENT '平台(android/ios)',
    account_id VARCHAR(64) COMMENT '腾讯广告主ID',
    event_type VARCHAR(16) COMMENT '事件类型(impression/click)，仅保留排障所需',
    tag_id VARCHAR(128) COMMENT '百度广告位ID',
    price BIGINT UNSIGNED COMMENT '竞价价格(分)',
    package_name VARCHAR(255) COMMENT '应用包名',
    media_params JSON COMMENT '媒体参数精简白名单(回传所需设备标识等)',
    created_at TIMESTAMP(3) NOT NULL COMMENT '创建时间',
    UNIQUE KEY uk_tencent_ad_bid_events_qk (qk),
    KEY idx_tencent_ad_bid_events_media_trace (media, media_trace_id),
    KEY idx_tencent_ad_bid_events_platform_tag (platform, tag_id),
    KEY idx_tencent_ad_bid_events_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='竞价事件记录';

-- ------------------------------------------------------------
-- 2. 上报结果表（曝光/点击上报百度DSP的结果）
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tencent_tracking_reports (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    qk VARCHAR(128) COMMENT '关联的业务键',
    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_tencent_tracking_reports_qk_kind_created_at (qk, kind, created_at),
    KEY idx_tencent_tracking_reports_created_at_id (created_at, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='曝光/点击上报结果';

-- ------------------------------------------------------------
-- 3. 百度转化数据表（从百度拉取的转化记录）
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tencent_baidu_conversions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    dedupe_key VARCHAR(255) COMMENT '去重键',
    qk VARCHAR(128) COMMENT '关联的业务键',
    media VARCHAR(64) COMMENT '媒体来源',
    tag_id VARCHAR(128) COMMENT '百度广告位ID',
    date_value VARCHAR(16) COMMENT '转化日期',
    appsid VARCHAR(128) COMMENT '百度应用ID',
    customer_name VARCHAR(128) COMMENT '客户名称',
    device_id VARCHAR(255) COMMENT '设备标识',
    conv DOUBLE COMMENT '转化数',
    payment DOUBLE COMMENT '付费金额',
    gmv DOUBLE COMMENT '扩展信息中的GMV',
    act INT COMMENT '行为类型(1=激活,2=付费,3=注册...)',
    tu VARCHAR(128) COMMENT '追踪URL参数',
    clk_time VARCHAR(32) COMMENT '点击时间',
    created_at TIMESTAMP(3) NOT NULL COMMENT '创建时间',
    UNIQUE KEY uk_tencent_baidu_conversions_dedupe (dedupe_key),
    KEY idx_tencent_baidu_conversions_qk_act_created_at (qk, act, created_at),
    KEY idx_tencent_baidu_conversions_tag_id (tag_id),
    KEY idx_tencent_baidu_conversions_device_id (device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='百度转化数据';

-- ------------------------------------------------------------
-- 4. 媒体回传记录表（向腾讯回传转化的结果）
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tencent_media_callbacks (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    dedupe_key VARCHAR(255) COMMENT '去重键',
    media VARCHAR(64) NOT NULL COMMENT '媒体(tencent)',
    qk VARCHAR(128) COMMENT '关联的业务键',
    callback_url TEXT NOT NULL COMMENT '回传的API URL',
    event_type INT NOT NULL COMMENT '事件类型(百度act)',
    event_time_ms BIGINT NOT NULL COMMENT '事件时间(毫秒时间戳)',
    purchase DOUBLE COMMENT '付费金额',
    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 '请求体',
    dispatch_status VARCHAR(32) NOT NULL DEFAULT 'SENT' COMMENT '回传处理状态(SENT/DEDUCTED)',
    tracking_version VARCHAR(16) NOT NULL DEFAULT '0' COMMENT '腾讯扣量值(0=不扣量)',
    created_at TIMESTAMP(3) NOT NULL COMMENT '创建时间',
    KEY idx_tencent_media_callbacks_dedupe_ok_created_at (dedupe_key, ok, created_at),
    KEY idx_tencent_media_callbacks_qk_created_at (qk, created_at),
    KEY idx_tencent_media_callbacks_media_event_created_at (media, event_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='媒体转化回传记录';

-- ------------------------------------------------------------
-- 5. 广告位回传方式配置表
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tencent_tag_event (
    tag_id VARCHAR(32) NOT NULL COMMENT '广告位ID',
    baidu_act INT NOT NULL COMMENT '百度转化行为',
    tencent_action_type VARCHAR(128) NOT NULL COMMENT '腾讯回传行为',
    PRIMARY KEY (tag_id, baidu_act)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='广告位回传方式配置';

-- ------------------------------------------------------------
-- 6. 腾讯账户级广告位回传方式配置表
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tencent_account_tag_event (
    tag_id VARCHAR(128) NOT NULL COMMENT '百度广告位ID',
    account_id VARCHAR(64) NOT NULL COMMENT '腾讯广告主ID',
    baidu_act INT NOT NULL COMMENT '百度转化行为',
    tencent_action_type VARCHAR(128) NOT NULL COMMENT '腾讯回传行为',
    created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
    updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
    PRIMARY KEY (tag_id, account_id, baidu_act),
    KEY idx_tencent_account_tag_event_account_act (account_id, baidu_act)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='腾讯账户级广告位回传方式配置';

-- ------------------------------------------------------------
-- 字段补丁（兼容旧表升级）
-- ------------------------------------------------------------
-- 如果 req_id 原来是 VARCHAR(128)，扩展为 512
-- ALTER TABLE tencent_ad_bid_events MODIFY COLUMN req_id VARCHAR(512);

-- 如果旧表没有 platform 字段
-- ALTER TABLE tencent_ad_bid_events ADD COLUMN platform VARCHAR(16) AFTER media_trace_id;
-- ALTER TABLE tencent_ad_bid_events ADD KEY idx_tencent_ad_bid_events_platform_tag (platform, tag_id);

-- 如果旧表没有 ad_id / account_id 字段
-- ALTER TABLE tencent_ad_bid_events ADD COLUMN ad_id VARCHAR(128) COMMENT '腾讯广告ID' AFTER platform;
-- ALTER TABLE tencent_ad_bid_events ADD COLUMN account_id VARCHAR(64) COMMENT '腾讯广告主ID' AFTER ad_id;

-- 如果旧表没有 tag_id 字段
-- ALTER TABLE tencent_baidu_conversions ADD COLUMN tag_id VARCHAR(128) AFTER media;
-- ALTER TABLE tencent_baidu_conversions ADD KEY idx_tencent_baidu_conversions_tag_id (tag_id);

-- 如果旧表没有 request_body / dispatch_status 字段
-- ALTER TABLE tencent_media_callbacks ADD COLUMN request_body TEXT AFTER error_message;
-- ALTER TABLE tencent_media_callbacks ADD COLUMN dispatch_status VARCHAR(32) NOT NULL DEFAULT 'SENT' AFTER request_body;
-- ALTER TABLE tencent_media_callbacks ADD COLUMN tracking_version VARCHAR(16) NOT NULL DEFAULT '0' AFTER dispatch_status;

-- 如果旧表没有 event_type 字段
-- ALTER TABLE tencent_ad_bid_events ADD COLUMN event_type VARCHAR(16) COMMENT '事件类型(impression/click)' AFTER account_id;

-- tencent_tag_event 升级为 (tag_id, baidu_act) 维度
-- ALTER TABLE tencent_tag_event ADD COLUMN baidu_act INT NULL COMMENT '百度转化行为' AFTER tag_id;
-- ALTER TABLE tencent_tag_event CHANGE COLUMN event_type tencent_action_type VARCHAR(128) NOT NULL COMMENT '腾讯回传行为';
-- UPDATE tencent_tag_event SET baidu_act = 0 WHERE baidu_act IS NULL;
-- ALTER TABLE tencent_tag_event MODIFY COLUMN baidu_act INT NOT NULL;
-- ALTER TABLE tencent_tag_event DROP PRIMARY KEY;
-- ALTER TABLE tencent_tag_event ADD PRIMARY KEY (tag_id, baidu_act);