58 lines
3.4 KiB
SQL
Executable File
58 lines
3.4 KiB
SQL
Executable File
-- 直播间活动表
|
|
CREATE TABLE IF NOT EXISTS `livestream_activities` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
|
`name` VARCHAR(255) NOT NULL COMMENT '活动名称',
|
|
`streamer_name` VARCHAR(128) DEFAULT '' COMMENT '主播名称',
|
|
`streamer_contact` VARCHAR(255) DEFAULT '' COMMENT '主播联系方式',
|
|
`access_code` VARCHAR(64) NOT NULL COMMENT '唯一访问码',
|
|
`douyin_product_id` VARCHAR(64) DEFAULT '' COMMENT '关联抖店商品ID',
|
|
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态:1进行中 2已结束',
|
|
`start_time` DATETIME(3) DEFAULT NULL COMMENT '开始时间',
|
|
`end_time` DATETIME(3) DEFAULT NULL COMMENT '结束时间',
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
|
|
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
|
|
`deleted_at` DATETIME(3) DEFAULT NULL COMMENT '删除时间',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_access_code` (`access_code`),
|
|
KEY `idx_product` (`douyin_product_id`),
|
|
KEY `idx_status` (`status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='直播间活动表';
|
|
|
|
-- 直播间奖品表
|
|
CREATE TABLE IF NOT EXISTS `livestream_prizes` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
|
`activity_id` BIGINT NOT NULL COMMENT '关联livestream_activities.id',
|
|
`name` VARCHAR(255) NOT NULL COMMENT '奖品名称',
|
|
`image` VARCHAR(512) DEFAULT '' COMMENT '奖品图片',
|
|
`weight` INT NOT NULL DEFAULT 1 COMMENT '抽奖权重',
|
|
`quantity` INT NOT NULL DEFAULT -1 COMMENT '库存数量(-1=无限)',
|
|
`remaining` INT NOT NULL DEFAULT -1 COMMENT '剩余数量',
|
|
`level` TINYINT NOT NULL DEFAULT 1 COMMENT '奖品等级',
|
|
`product_id` BIGINT DEFAULT NULL COMMENT '关联系统商品ID',
|
|
`sort` INT NOT NULL DEFAULT 0 COMMENT '排序',
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
|
|
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_activity` (`activity_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='直播间奖品表';
|
|
|
|
-- 直播间中奖记录表
|
|
CREATE TABLE IF NOT EXISTS `livestream_draw_logs` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
|
`activity_id` BIGINT NOT NULL COMMENT '关联livestream_activities.id',
|
|
`prize_id` BIGINT NOT NULL COMMENT '关联livestream_prizes.id',
|
|
`douyin_order_id` BIGINT DEFAULT NULL COMMENT '关联douyin_orders.id',
|
|
`local_user_id` BIGINT DEFAULT NULL COMMENT '本地用户ID',
|
|
`douyin_user_id` VARCHAR(64) DEFAULT '' COMMENT '抖音用户ID',
|
|
`prize_name` VARCHAR(255) DEFAULT '' COMMENT '中奖奖品名称快照',
|
|
`level` TINYINT DEFAULT 1 COMMENT '奖品等级',
|
|
`seed_hash` VARCHAR(128) DEFAULT '' COMMENT '哈希种子',
|
|
`rand_value` BIGINT DEFAULT 0 COMMENT '随机值',
|
|
`weights_total` BIGINT DEFAULT 0 COMMENT '权重总和',
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '中奖时间',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_activity` (`activity_id`),
|
|
KEY `idx_douyin_order` (`douyin_order_id`),
|
|
KEY `idx_user` (`local_user_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='直播间中奖记录表';
|