zhijiayun-pharmacy/zhijiayun-gateway/src/main/resources/db/migration-v11.sql

-- ============================================
-- 迁移 v11：运营活动统一配置（邀请有礼 / 签到送会员 / 比价抽奖）
-- 设计原则：所有活动均"可关闭"，关闭时各业务保持原有逻辑不变（往上加限制）
-- 活动时间窗统一：2026-07-10 ~ 2026-07-31
-- ============================================

-- ========== 一、邀请有礼活动 ==========
CREATE TABLE IF NOT EXISTS `t_activity_invite_config` (
    `id`                     BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `enabled`                TINYINT      NOT NULL DEFAULT 0 COMMENT '是否启用活动：0-停用(走原逻辑) 1-启用',
    `start_time`             DATETIME     NOT NULL COMMENT '活动开始时间',
    `end_time`               DATETIME     NOT NULL COMMENT '活动结束时间',
    `inviter_reward_days`    INT          NOT NULL DEFAULT 30 COMMENT '邀请人奖励会员天数',
    `invitee_reward_days`    INT          NOT NULL DEFAULT 30 COMMENT '被邀请人奖励会员天数',
    `max_inviter_reward`     INT          NOT NULL DEFAULT 30 COMMENT '邀请人最多获得邀请奖励次数',
    `reward_trigger`         VARCHAR(20)  NOT NULL DEFAULT 'REGISTRATION' COMMENT '奖励触发模式：REGISTRATION-注册即发(原逻辑) LICENSE_APPROVED-资质审核通过后发',
    `require_miniapp_inviter` TINYINT     NOT NULL DEFAULT 0 COMMENT '是否要求邀请人为开邻药惠购小程序用户：0-不要求 1-要求',
    `remark`                 VARCHAR(500) DEFAULT NULL,
    `create_time`            DATETIME     DEFAULT CURRENT_TIMESTAMP,
    `update_time`            DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='邀请有礼活动配置（全局唯一）';

INSERT INTO `t_activity_invite_config` (`id`, `enabled`, `start_time`, `end_time`,
    `inviter_reward_days`, `invitee_reward_days`, `max_inviter_reward`, `reward_trigger`, `require_miniapp_inviter`, `remark`)
VALUES (1, 0, '2026-07-10 00:00:00', '2026-07-31 23:59:59', 30, 30, 30, 'LICENSE_APPROVED', 0,
    '邀请有礼：默认关闭。启用后需被邀请人资质审核通过才发奖，邀请人最多30次')
ON DUPLICATE KEY UPDATE id = id;

-- 邀请有礼奖励发放记录（活动模式 LICENSE_APPROVED 下，邀请人在被邀请人资质通过后获得的奖励）
CREATE TABLE IF NOT EXISTS `t_activity_invite_reward_record` (
    `id`                  BIGINT       NOT NULL AUTO_INCREMENT,
    `inviter_id`          BIGINT       NOT NULL COMMENT '邀请人ID',
    `invitee_id`          BIGINT       NOT NULL COMMENT '被邀请人ID',
    `license_id`          BIGINT       DEFAULT NULL COMMENT '触发奖励的资质记录ID',
    `reward_days`         INT          NOT NULL COMMENT '奖励会员天数',
    `grant_id`            BIGINT       DEFAULT NULL COMMENT '关联会员发放记录ID(预留)',
    `status`              TINYINT      NOT NULL DEFAULT 0 COMMENT '状态：0-正常 1-已撤销(反作弊)',
    `revoke_reason`       VARCHAR(500) DEFAULT NULL COMMENT '撤销原因',
    `revoker_id`          BIGINT       DEFAULT NULL COMMENT '撤销操作人ID',
    `revoke_time`         DATETIME     DEFAULT NULL COMMENT '撤销时间',
    `create_time`         DATETIME     DEFAULT CURRENT_TIMESTAMP,
    `update_time`         DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_invitee` (`invitee_id`) COMMENT '同一被邀请人资质通过仅奖励邀请人一次'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='邀请有礼活动奖励发放记录';


-- ========== 二、签到送高级会员活动（周期制） ==========
CREATE TABLE IF NOT EXISTS `t_activity_checkin_cycle_config` (
    `id`                BIGINT       NOT NULL AUTO_INCREMENT,
    `enabled`           TINYINT      NOT NULL DEFAULT 0 COMMENT '是否启用：0-停用 1-启用',
    `start_time`        DATETIME     NOT NULL COMMENT '活动开始时间',
    `end_time`          DATETIME     NOT NULL COMMENT '活动结束时间',
    `cycle_mode`        VARCHAR(20)  NOT NULL DEFAULT 'FRI_THU' COMMENT '周期模式：FRI_THU-周五至下周四',
    `required_days`     INT          NOT NULL DEFAULT 4 COMMENT '周期内需累计签到天数',
    `reward_days`       INT          NOT NULL DEFAULT 7 COMMENT '奖励高级会员天数',
    `max_reward_count`  INT          NOT NULL DEFAULT 3 COMMENT '每用户最多领取次数',
    `remark`            VARCHAR(500) DEFAULT NULL,
    `create_time`       DATETIME     DEFAULT CURRENT_TIMESTAMP,
    `update_time`       DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='签到送会员活动配置（全局唯一）';

INSERT INTO `t_activity_checkin_cycle_config` (`id`, `enabled`, `start_time`, `end_time`,
    `cycle_mode`, `required_days`, `reward_days`, `max_reward_count`, `remark`)
VALUES (1, 0, '2026-07-10 00:00:00', '2026-07-31 23:59:59', 'FRI_THU', 4, 7, 3,
    '签到送会员：每周五至下周四为一周期，累计4天签到得7天高级会员，周期结束后第一天发放，最多3次')
ON DUPLICATE KEY UPDATE id = id;

-- 签到周期奖励记录（每用户每周期一条，记录是否已发放）
CREATE TABLE IF NOT EXISTS `t_activity_checkin_cycle_record` (
    `id`             BIGINT       NOT NULL AUTO_INCREMENT,
    `user_id`        BIGINT       NOT NULL,
    `cycle_start`    DATE         NOT NULL COMMENT '周期起始日(周五)',
    `cycle_end`      DATE         NOT NULL COMMENT '周期截止日(周四)',
    `checkin_count`  INT          NOT NULL DEFAULT 0 COMMENT '周期内签到天数',
    `reward_granted` TINYINT      NOT NULL DEFAULT 0 COMMENT '是否已发放奖励：0-未 1-已',
    `grant_time`     DATETIME     DEFAULT NULL COMMENT '发放时间',
    `create_time`    DATETIME     DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_user_cycle` (`user_id`, `cycle_start`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='签到周期奖励记录';


-- ========== 三、比价抽奖活动 ==========
CREATE TABLE IF NOT EXISTS `t_activity_lottery_config` (
    `id`                       BIGINT       NOT NULL AUTO_INCREMENT,
    `enabled`                  TINYINT      NOT NULL DEFAULT 0 COMMENT '是否启用：0-停用 1-启用',
    `start_time`               DATETIME     NOT NULL,
    `end_time`                 DATETIME     NOT NULL,
    `daily_max_chance`         INT          NOT NULL DEFAULT 1 COMMENT '每用户每日最多抽奖机会数',
    `require_membership_level` VARCHAR(20)  NOT NULL DEFAULT 'PRO' COMMENT '参与所需最低会员等级(PRO/ULTRA)',
    `remark`                   VARCHAR(500) DEFAULT NULL,
    `create_time`              DATETIME     DEFAULT CURRENT_TIMESTAMP,
    `update_time`              DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='比价抽奖活动配置（全局唯一）';

INSERT INTO `t_activity_lottery_config` (`id`, `enabled`, `start_time`, `end_time`,
    `daily_max_chance`, `require_membership_level`, `remark`)
VALUES (1, 0, '2026-07-10 00:00:00', '2026-07-31 23:59:59', 1, 'PRO',
    '比价抽奖：高级会员每自然日首次成功比价获得1次抽奖机会，先到先得')
ON DUPLICATE KEY UPDATE id = id;

-- 抽奖机会表（每用户每日首次比价产生一条机会）
CREATE TABLE IF NOT EXISTS `t_activity_lottery_chance` (
    `id`           BIGINT       NOT NULL AUTO_INCREMENT,
    `user_id`      BIGINT       NOT NULL,
    `chance_date`  DATE         NOT NULL COMMENT '获得机会的日期',
    `source`       VARCHAR(20)  NOT NULL DEFAULT 'COMPARE' COMMENT '机会来源：COMPARE-比价',
    `used`         TINYINT      NOT NULL DEFAULT 0 COMMENT '是否已使用：0-未 1-已',
    `create_time`  DATETIME     DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_user_date_source` (`user_id`, `chance_date`, `source`) COMMENT '每日每来源仅1次机会'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='比价抽奖机会表';

-- 奖品池
CREATE TABLE IF NOT EXISTS `t_activity_lottery_prize` (
    `id`                 BIGINT       NOT NULL AUTO_INCREMENT,
    `name`               VARCHAR(50)  NOT NULL COMMENT '奖品名称',
    `reward_type`        VARCHAR(20)  NOT NULL COMMENT '奖励类型：MEMBERSHIP-会员 EMPTY-未中奖',
    `reward_amount`      INT          NOT NULL DEFAULT 0 COMMENT '奖励数量(爬虫次数等)',
    `reward_days`        INT          DEFAULT NULL COMMENT '奖励会员天数(MEMBERSHIP类型)',
    `reward_level`       VARCHAR(20)  DEFAULT NULL COMMENT '奖励会员等级(MEMBERSHIP类型)',
    `probability_weight` INT          NOT NULL DEFAULT 1 COMMENT '中奖概率权重',
    `enabled`            TINYINT      NOT NULL DEFAULT 1,
    `sort`               INT          NOT NULL DEFAULT 0,
    `create_time`        DATETIME     DEFAULT CURRENT_TIMESTAMP,
    `update_time`        DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='抽奖奖品池';

-- 默认奖品池
INSERT INTO `t_activity_lottery_prize` (`name`, `reward_type`, `reward_amount`, `reward_days`, `reward_level`, `probability_weight`, `enabled`, `sort`)
VALUES
('高级会员7天', 'MEMBERSHIP', 0, 7, 'PRO', 10, 1, 1),
('高级会员3天', 'MEMBERSHIP', 0, 3, 'PRO', 20, 1, 2),
('高级会员1天', 'MEMBERSHIP', 0, 1, 'PRO', 40, 1, 3),
('谢谢参与', 'EMPTY', 0, NULL, NULL, 100, 1, 4);

-- 抽奖记录表
CREATE TABLE IF NOT EXISTS `t_activity_lottery_record` (
    `id`            BIGINT       NOT NULL AUTO_INCREMENT,
    `user_id`       BIGINT       NOT NULL,
    `prize_id`      BIGINT       DEFAULT NULL,
    `prize_name`    VARCHAR(50)  NOT NULL,
    `reward_type`   VARCHAR(20)  NOT NULL,
    `reward_amount` INT          NOT NULL DEFAULT 0,
    `reward_days`   INT          DEFAULT NULL,
    `reward_level`  VARCHAR(20)  DEFAULT NULL,
    `chance_id`     BIGINT       DEFAULT NULL COMMENT '关联抽奖机会ID',
    `create_time`   DATETIME     DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user` (`user_id`, `create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='抽奖记录表';