ylrz_his_scrm_adminUI/docs/sql/fs_store_promotion_init.sql

-- ============================================================
-- 多店铺阶梯满减活动 - 初始化 / 增量升级 SQL
-- 文档: docs/multi-store-tiered-promotion-design.md v1.2
-- 执行前请备份数据库；菜单 parent_id 请按实际 hisStore 父菜单 ID 调整
--
-- 使用说明：
--   1. 全新环境：从「一、建表」起顺序执行即可
--   2. 已建活动表、仅缺订单字段：执行「二、订单表扩展字段（增量）」
--   3. 增量语句可重复执行，字段/索引已存在时自动跳过
-- ============================================================

-- ============================================================
-- 一、建表（CREATE TABLE IF NOT EXISTS，可重复执行）
-- ============================================================

-- 1. 活动主表
CREATE TABLE IF NOT EXISTS `fs_store_promotion_activity` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `title` VARCHAR(100) NOT NULL COMMENT '活动名称',
  `store_id` BIGINT(20) NOT NULL COMMENT '店铺ID',
  `start_time` DATETIME NOT NULL COMMENT '活动开始时间',
  `end_time` DATETIME NOT NULL COMMENT '活动结束时间',
  `scope_type` TINYINT(2) NOT NULL DEFAULT 1 COMMENT '适用范围：1全场 2指定分类 3指定商品',
  `tier_type` TINYINT(2) NOT NULL DEFAULT 1 COMMENT '阶梯类型：1金额 2折扣',
  `is_stackable` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否可叠加优惠券：0否 1是',
  `is_capped` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否上不封顶：0否 1是',
  `limit_per_user` INT(11) NOT NULL DEFAULT 0 COMMENT '每人限参与次数，0=不限',
  `status` TINYINT(2) NOT NULL DEFAULT 0 COMMENT '状态：0草稿 1启用 3已结束',
  `manual_status` TINYINT(1) DEFAULT NULL COMMENT '手动开关：NULL未操作 1启用 0关闭',
  `version` INT(11) NOT NULL DEFAULT 0 COMMENT '乐观锁版本号',
  `remark` VARCHAR(500) DEFAULT NULL COMMENT '备注',
  `is_del` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除：0正常 1删除',
  `create_by` VARCHAR(64) DEFAULT NULL COMMENT '创建者',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` VARCHAR(64) DEFAULT NULL COMMENT '更新者',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_store_id` (`store_id`),
  KEY `idx_status_time` (`status`, `manual_status`, `start_time`, `end_time`),
  KEY `idx_store_status` (`store_id`, `status`, `manual_status`, `is_del`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='店铺阶梯满减活动主表';

-- 2. 满减档位表
CREATE TABLE IF NOT EXISTS `fs_store_promotion_tier` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `activity_id` BIGINT(20) NOT NULL COMMENT '活动ID',
  `sort_order` INT(11) NOT NULL DEFAULT 1 COMMENT '档位序号',
  `threshold_amount` DECIMAL(10,2) NOT NULL COMMENT '门槛金额（元）',
  `discount_amount` DECIMAL(10,2) NOT NULL COMMENT '减扣金额(元)/折扣(折)，含义由活动 tier_type 决定',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_activity_id` (`activity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='阶梯满减档位表';

-- 3. 适用范围表（指定分类/指定商品写入此表，全场通用不写记录）
CREATE TABLE IF NOT EXISTS `fs_store_promotion_scope` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `activity_id` BIGINT(20) NOT NULL COMMENT '活动ID',
  `scope_type` TINYINT(2) NOT NULL COMMENT '范围类型：2分类 3商品',
  `target_id` BIGINT(20) NOT NULL COMMENT '分类ID或商品ID',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_activity_id` (`activity_id`),
  KEY `idx_target` (`scope_type`, `target_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='活动适用范围明细表';

-- 4. 用户参与记录表
CREATE TABLE IF NOT EXISTS `fs_store_promotion_usage` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `activity_id` BIGINT(20) NOT NULL COMMENT '活动ID',
  `user_id` BIGINT(20) NOT NULL COMMENT '用户ID',
  `order_id` BIGINT(20) NOT NULL COMMENT '订单ID',
  `order_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '订单原金额',
  `discount_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '优惠金额',
  `tier_id` BIGINT(20) DEFAULT NULL COMMENT '命中档位ID',
  `usage_status` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0待支付 1已生效 2已回滚',
  `usage_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '参与时间',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_activity_order` (`activity_id`, `order_id`),
  KEY `idx_activity_user` (`activity_id`, `user_id`),
  KEY `idx_order_id` (`order_id`),
  KEY `idx_usage_time` (`usage_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户活动参与记录表';

-- ============================================================
-- 二、订单表扩展字段（增量，可重复执行）
-- 表：fs_store_order_scrm
-- ============================================================

SET @db_name = DATABASE();

-- promotion_activity_id
SET @sql_add_promotion_activity_id = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = @db_name
        AND TABLE_NAME = 'fs_store_order_scrm'
        AND COLUMN_NAME = 'promotion_activity_id'
    ),
    'SELECT ''skip: promotion_activity_id exists'' AS msg',
    'ALTER TABLE `fs_store_order_scrm` ADD COLUMN `promotion_activity_id` BIGINT(20) DEFAULT NULL COMMENT ''阶梯满减活动ID'' AFTER `user_coupon_id`'
  )
);
PREPARE stmt FROM @sql_add_promotion_activity_id;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- promotion_tier_id
SET @sql_add_promotion_tier_id = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = @db_name
        AND TABLE_NAME = 'fs_store_order_scrm'
        AND COLUMN_NAME = 'promotion_tier_id'
    ),
    'SELECT ''skip: promotion_tier_id exists'' AS msg',
    'ALTER TABLE `fs_store_order_scrm` ADD COLUMN `promotion_tier_id` BIGINT(20) DEFAULT NULL COMMENT ''命中档位ID'' AFTER `promotion_activity_id`'
  )
);
PREPARE stmt FROM @sql_add_promotion_tier_id;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- promotion_discount_amount
SET @sql_add_promotion_discount_amount = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = @db_name
        AND TABLE_NAME = 'fs_store_order_scrm'
        AND COLUMN_NAME = 'promotion_discount_amount'
    ),
    'SELECT ''skip: promotion_discount_amount exists'' AS msg',
    'ALTER TABLE `fs_store_order_scrm` ADD COLUMN `promotion_discount_amount` DECIMAL(10,2) DEFAULT NULL COMMENT ''满减优惠金额'' AFTER `promotion_tier_id`'
  )
);
PREPARE stmt FROM @sql_add_promotion_discount_amount;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================================
-- 二点五、活动主表 tier_type 字段（旧库增量，可重复执行）
-- 说明：CREATE TABLE IF NOT EXISTS 不会给已存在的表加新字段，旧环境必须执行本段
-- ============================================================

SET @sql_add_tier_type = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = @db_name
        AND TABLE_NAME = 'fs_store_promotion_activity'
        AND COLUMN_NAME = 'tier_type'
    ),
    'SELECT ''skip: tier_type exists'' AS msg',
    'ALTER TABLE `fs_store_promotion_activity` ADD COLUMN `tier_type` TINYINT(2) NOT NULL DEFAULT 1 COMMENT ''阶梯类型：1金额 2折扣'' AFTER `scope_type`'
  )
);
PREPARE stmt FROM @sql_add_tier_type;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

UPDATE `fs_store_promotion_activity`
SET `tier_type` = 1
WHERE `tier_type` IS NULL OR `tier_type` = 0;

-- ============================================================
-- 三、参与记录表索引补全（旧库若已建表但缺 idx_usage_time 可执行）
-- ============================================================

SET @sql_add_idx_usage_time = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.STATISTICS
      WHERE TABLE_SCHEMA = @db_name
        AND TABLE_NAME = 'fs_store_promotion_usage'
        AND INDEX_NAME = 'idx_usage_time'
    ),
    'SELECT ''skip: idx_usage_time exists'' AS msg',
    'ALTER TABLE `fs_store_promotion_usage` ADD INDEX `idx_usage_time` (`usage_time`)'
  )
);
PREPARE stmt FROM @sql_add_idx_usage_time;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================================
-- 四、菜单与按钮权限（parent_id 请替换为商城/hisStore 父菜单 ID）
-- 注意：菜单 INSERT 仅首次执行，重复执行会插入重复菜单
-- ============================================================
-- SET @parent_menu_id = (SELECT menu_id FROM sys_menu WHERE menu_name = '商城管理' LIMIT 1);

INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES ('阶梯满减', 0, 1, 'storePromotion', 'hisStore/storePromotion/index', 1, 0, 'C', '0', '0', 'store:storePromotion:list', 'money', 'admin', NOW(), '', NULL, '阶梯满减活动');

SET @promotion_menu_id = LAST_INSERT_ID();

INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`) VALUES
('阶梯满减查询', @promotion_menu_id, 1, '#', '', 1, 0, 'F', '0', '0', 'store:storePromotion:query', '#', 'admin', NOW(), ''),
('阶梯满减新增', @promotion_menu_id, 2, '#', '', 1, 0, 'F', '0', '0', 'store:storePromotion:add', '#', 'admin', NOW(), ''),
('阶梯满减修改', @promotion_menu_id, 3, '#', '', 1, 0, 'F', '0', '0', 'store:storePromotion:edit', '#', 'admin', NOW(), ''),
('阶梯满减删除', @promotion_menu_id, 4, '#', '', 1, 0, 'F', '0', '0', 'store:storePromotion:remove', '#', 'admin', NOW(), ''),
('阶梯满减导出', @promotion_menu_id, 5, '#', '', 1, 0, 'F', '0', '0', 'store:storePromotion:export', '#', 'admin', NOW(), ''),
('阶梯满减启用', @promotion_menu_id, 6, '#', '', 1, 0, 'F', '0', '0', 'store:storePromotion:enable', '#', 'admin', NOW(), ''),
('阶梯满减停用', @promotion_menu_id, 7, '#', '', 1, 0, 'F', '0', '0', 'store:storePromotion:disable', '#', 'admin', NOW(), '');