135 lines
6.2 KiB
SQL
135 lines
6.2 KiB
SQL
-- 数据库迁移脚本：添加优惠码和订阅升级相关表
|
||
-- 执行时间：2025-xx-xx
|
||
-- 作者：Claude Code
|
||
-- 说明：此脚本添加了优惠码、优惠码使用记录和订阅升级记录三张新表，并扩展了 payment_orders 表
|
||
|
||
-- ============================================
|
||
-- 1. 创建优惠码表
|
||
-- ============================================
|
||
CREATE TABLE IF NOT EXISTS `promo_codes` (
|
||
`id` INT PRIMARY KEY AUTO_INCREMENT,
|
||
`code` VARCHAR(50) UNIQUE NOT NULL COMMENT '优惠码（唯一）',
|
||
`description` VARCHAR(200) DEFAULT NULL COMMENT '优惠码描述',
|
||
|
||
-- 折扣类型和值
|
||
`discount_type` VARCHAR(20) NOT NULL COMMENT '折扣类型: percentage（百分比） 或 fixed_amount（固定金额）',
|
||
`discount_value` DECIMAL(10, 2) NOT NULL COMMENT '折扣值',
|
||
|
||
-- 适用范围
|
||
`applicable_plans` VARCHAR(200) DEFAULT NULL COMMENT '适用套餐（JSON格式），如 ["pro", "max"]，null表示全部适用',
|
||
`applicable_cycles` VARCHAR(50) DEFAULT NULL COMMENT '适用周期（JSON格式），如 ["monthly", "yearly"]，null表示全部适用',
|
||
`min_amount` DECIMAL(10, 2) DEFAULT NULL COMMENT '最低消费金额',
|
||
|
||
-- 使用限制
|
||
`max_uses` INT DEFAULT NULL COMMENT '最大使用次数，null表示无限制',
|
||
`max_uses_per_user` INT DEFAULT 1 COMMENT '每个用户最多使用次数',
|
||
`current_uses` INT DEFAULT 0 COMMENT '当前已使用次数',
|
||
|
||
-- 有效期
|
||
`valid_from` DATETIME NOT NULL COMMENT '生效时间',
|
||
`valid_until` DATETIME NOT NULL COMMENT '失效时间',
|
||
|
||
-- 状态
|
||
`is_active` BOOLEAN DEFAULT TRUE COMMENT '是否启用',
|
||
`created_by` INT DEFAULT NULL COMMENT '创建人（管理员ID）',
|
||
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
||
INDEX idx_code (`code`),
|
||
INDEX idx_valid_dates (`valid_from`, `valid_until`),
|
||
INDEX idx_is_active (`is_active`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='优惠码表';
|
||
|
||
|
||
-- ============================================
|
||
-- 2. 创建优惠码使用记录表
|
||
-- ============================================
|
||
CREATE TABLE IF NOT EXISTS `promo_code_usage` (
|
||
`id` INT PRIMARY KEY AUTO_INCREMENT,
|
||
`promo_code_id` INT NOT NULL COMMENT '优惠码ID',
|
||
`user_id` INT NOT NULL COMMENT '用户ID',
|
||
`order_id` INT NOT NULL COMMENT '订单ID',
|
||
|
||
`original_amount` DECIMAL(10, 2) NOT NULL COMMENT '原价',
|
||
`discount_amount` DECIMAL(10, 2) NOT NULL COMMENT '优惠金额',
|
||
`final_amount` DECIMAL(10, 2) NOT NULL COMMENT '实付金额',
|
||
|
||
`used_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '使用时间',
|
||
|
||
FOREIGN KEY (`promo_code_id`) REFERENCES `promo_codes`(`id`) ON DELETE CASCADE,
|
||
FOREIGN KEY (`order_id`) REFERENCES `payment_orders`(`id`) ON DELETE CASCADE,
|
||
|
||
INDEX idx_user_id (`user_id`),
|
||
INDEX idx_promo_code_id (`promo_code_id`),
|
||
INDEX idx_order_id (`order_id`),
|
||
INDEX idx_used_at (`used_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='优惠码使用记录表';
|
||
|
||
|
||
-- ============================================
|
||
-- 3. 创建订阅升级记录表
|
||
-- ============================================
|
||
CREATE TABLE IF NOT EXISTS `subscription_upgrades` (
|
||
`id` INT PRIMARY KEY AUTO_INCREMENT,
|
||
`user_id` INT NOT NULL COMMENT '用户ID',
|
||
`order_id` INT NOT NULL COMMENT '订单ID',
|
||
|
||
-- 原订阅信息
|
||
`from_plan` VARCHAR(20) NOT NULL COMMENT '原套餐',
|
||
`from_cycle` VARCHAR(10) NOT NULL COMMENT '原周期',
|
||
`from_end_date` DATETIME DEFAULT NULL COMMENT '原到期日',
|
||
|
||
-- 新订阅信息
|
||
`to_plan` VARCHAR(20) NOT NULL COMMENT '新套餐',
|
||
`to_cycle` VARCHAR(10) NOT NULL COMMENT '新周期',
|
||
`to_end_date` DATETIME NOT NULL COMMENT '新到期日',
|
||
|
||
-- 价格计算
|
||
`remaining_value` DECIMAL(10, 2) NOT NULL COMMENT '剩余价值',
|
||
`upgrade_amount` DECIMAL(10, 2) NOT NULL COMMENT '升级应付金额',
|
||
`actual_amount` DECIMAL(10, 2) NOT NULL COMMENT '实际支付金额',
|
||
|
||
`upgrade_type` VARCHAR(20) NOT NULL COMMENT '升级类型: plan_upgrade（套餐升级）, cycle_change（周期变更）, both（都变更）',
|
||
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
|
||
FOREIGN KEY (`order_id`) REFERENCES `payment_orders`(`id`) ON DELETE CASCADE,
|
||
|
||
INDEX idx_user_id (`user_id`),
|
||
INDEX idx_order_id (`order_id`),
|
||
INDEX idx_created_at (`created_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订阅升级/降级记录表';
|
||
|
||
|
||
-- ============================================
|
||
-- 4. 扩展 payment_orders 表（添加新字段）
|
||
-- ============================================
|
||
-- 注意：这些字段是可选的扩展，用于记录优惠码和升级信息
|
||
-- 如果字段已存在会报错，可以忽略
|
||
|
||
ALTER TABLE `payment_orders`
|
||
ADD COLUMN `promo_code_id` INT DEFAULT NULL COMMENT '使用的优惠码ID' AFTER `remark`,
|
||
ADD COLUMN `original_amount` DECIMAL(10, 2) DEFAULT NULL COMMENT '原价（使用优惠码前）' AFTER `promo_code_id`,
|
||
ADD COLUMN `discount_amount` DECIMAL(10, 2) DEFAULT 0 COMMENT '优惠金额' AFTER `original_amount`,
|
||
ADD COLUMN `is_upgrade` BOOLEAN DEFAULT FALSE COMMENT '是否为升级订单' AFTER `discount_amount`,
|
||
ADD COLUMN `upgrade_from_plan` VARCHAR(20) DEFAULT NULL COMMENT '从哪个套餐升级' AFTER `is_upgrade`;
|
||
|
||
-- 添加外键约束
|
||
ALTER TABLE `payment_orders`
|
||
ADD CONSTRAINT `fk_payment_orders_promo_code`
|
||
FOREIGN KEY (`promo_code_id`) REFERENCES `promo_codes`(`id`) ON DELETE SET NULL;
|
||
|
||
|
||
-- ============================================
|
||
-- 5. 插入示例优惠码（供测试使用）
|
||
-- ============================================
|
||
-- 10% 折扣优惠码，适用所有套餐和周期
|
||
INSERT INTO `promo_codes`
|
||
(`code`, `description`, `discount_type`, `discount_value`, `applicable_plans`, `applicable_cycles`, `min_amount`, `max_uses`, `max_uses_per_user`, `valid_from`, `valid_until`, `is_active`)
|
||
VALUES
|
||
('WELCOME10', '新用户欢迎优惠 - 10%折扣', 'percentage', 10.00, NULL, NULL, NULL, NULL, 1, NOW(), DATE_ADD(NOW(), INTERVAL 1 YEAR), TRUE),
|
||
('ANNUAL20', '年付专享 - 20%折扣', 'percentage', 20.00, NULL, '["yearly"]', NULL, 100, 1, NOW(), DATE_ADD(NOW(), INTERVAL 1 YEAR), TRUE),
|
||
('SUMMER50', '夏季促销 - 减免50元', 'fixed_amount', 50.00, '["max"]', NULL, 100.00, 50, 1, NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), TRUE);
|
||
|
||
-- 完成
|
||
SELECT 'Migration completed successfully!' AS status;
|