76 lines
4.8 KiB
SQL
76 lines
4.8 KiB
SQL
CREATE TABLE IF NOT EXISTS `welfare_activities` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
|
`title` VARCHAR(128) NOT NULL COMMENT '活动标题',
|
|
`type` VARCHAR(16) NOT NULL COMMENT '活动类型: daily/weekly/monthly',
|
|
`threshold_amount` BIGINT NOT NULL DEFAULT 0 COMMENT '参与门槛金额(分)',
|
|
`start_time` DATETIME(3) NOT NULL COMMENT '开始时间',
|
|
`end_time` DATETIME(3) NOT NULL COMMENT '结束时间',
|
|
`draw_time` DATETIME(3) NOT NULL COMMENT '开奖时间',
|
|
`status` VARCHAR(16) NOT NULL DEFAULT 'active' COMMENT '状态: active/finished',
|
|
`description` TEXT NULL COMMENT '活动说明',
|
|
`cover_image` VARCHAR(512) NOT NULL DEFAULT '' COMMENT '封面图',
|
|
`draw_batch` VARCHAR(64) NOT NULL DEFAULT '' 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) NULL COMMENT '删除时间',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_welfare_activities_type_status` (`type`, `status`),
|
|
KEY `idx_welfare_activities_draw_time` (`draw_time`),
|
|
KEY `idx_welfare_activities_deleted_at` (`deleted_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='福利活动';
|
|
|
|
CREATE TABLE IF NOT EXISTS `welfare_activity_prizes` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
|
`activity_id` BIGINT NOT NULL COMMENT '福利活动ID',
|
|
`reward_type` VARCHAR(32) NOT NULL COMMENT '奖品类型: product/item_card/coupon',
|
|
`reward_ref_id` BIGINT NOT NULL COMMENT '奖品资源ID',
|
|
`reward_name_snapshot` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '奖品名称快照',
|
|
`reward_image_snapshot` VARCHAR(512) NOT NULL DEFAULT '' COMMENT '奖品图片快照',
|
|
`reward_value_snapshot_cents` BIGINT NOT NULL DEFAULT 0 COMMENT '奖品展示价值快照(分)',
|
|
`cost_snapshot_cents` BIGINT NOT NULL DEFAULT 0 COMMENT '奖品成本快照(分)',
|
|
`quantity` INT NOT NULL DEFAULT 0 COMMENT '初始奖品数量',
|
|
`remaining_quantity` INT NOT NULL DEFAULT 0 COMMENT '剩余数量',
|
|
`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_welfare_prizes_activity` (`activity_id`),
|
|
KEY `idx_welfare_prizes_reward` (`reward_type`, `reward_ref_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='福利活动奖品配置';
|
|
|
|
CREATE TABLE IF NOT EXISTS `welfare_activity_participants` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
|
`activity_id` BIGINT NOT NULL COMMENT '福利活动ID',
|
|
`user_id` BIGINT NOT NULL COMMENT '用户ID',
|
|
`period_key` VARCHAR(16) NOT NULL COMMENT '参与周期标识',
|
|
`paid_amount_snapshot` BIGINT NOT NULL DEFAULT 0 COMMENT '参与时周期消费快照(分)',
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_welfare_participant` (`activity_id`, `user_id`, `period_key`),
|
|
KEY `idx_welfare_participants_activity` (`activity_id`, `created_at`),
|
|
KEY `idx_welfare_participants_user` (`user_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='福利活动参与记录';
|
|
|
|
CREATE TABLE IF NOT EXISTS `welfare_activity_winners` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
|
`activity_id` BIGINT NOT NULL COMMENT '福利活动ID',
|
|
`prize_id` BIGINT NOT NULL COMMENT '奖品配置ID',
|
|
`reward_type` VARCHAR(32) NOT NULL COMMENT '奖品类型: product/item_card/coupon',
|
|
`reward_ref_id` BIGINT NOT NULL COMMENT '奖品资源ID',
|
|
`prize_name_snapshot` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '中奖奖品名称快照',
|
|
`prize_image_snapshot` VARCHAR(512) NOT NULL DEFAULT '' COMMENT '中奖奖品图片快照',
|
|
`prize_value_snapshot_cents` BIGINT NOT NULL DEFAULT 0 COMMENT '中奖奖品展示价值快照(分)',
|
|
`user_id` BIGINT NOT NULL COMMENT '中奖用户ID',
|
|
`grant_record_type` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '发放记录类型: inventory/user_item_card/user_coupon',
|
|
`grant_record_id` BIGINT NOT NULL DEFAULT 0 COMMENT '发放记录ID',
|
|
`cost_cents` BIGINT NOT NULL DEFAULT 0 COMMENT '成本(分)',
|
|
`draw_batch` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '开奖批次',
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_welfare_winner_user` (`activity_id`, `user_id`),
|
|
KEY `idx_welfare_winners_activity` (`activity_id`, `created_at`),
|
|
KEY `idx_welfare_winners_user` (`user_id`),
|
|
KEY `idx_welfare_winners_reward` (`reward_type`, `reward_ref_id`),
|
|
KEY `idx_welfare_winners_grant` (`grant_record_type`, `grant_record_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='福利活动中奖记录';
|