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='福利活动中奖记录';