-- ============================================================
-- V6: 添加积分充值系统
-- 描述: 支持用户直接购买积分（支付宝/微信支付）
-- 作者: 1818AI
-- 日期: 2025-10-21
-- ============================================================

-- 1. 创建积分套餐表
CREATE TABLE IF NOT EXISTS `points_package` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(64) NOT NULL COMMENT '套餐名称',
  `description` varchar(255) DEFAULT NULL COMMENT '套餐描述',
  `points` int NOT NULL COMMENT '基础积分数量',
  `bonus_points` int NOT NULL DEFAULT 0 COMMENT '赠送积分数量',
  `total_points` int NOT NULL COMMENT '总积分（基础+赠送）',
  `price` decimal(10,2) NOT NULL COMMENT '价格（元）',
  `original_price` decimal(10,2) DEFAULT NULL COMMENT '原价（用于显示优惠）',
  `points_expire_days` int NOT NULL DEFAULT 365 COMMENT '积分有效期（天）',
  `discount_label` varchar(32) DEFAULT NULL COMMENT '优惠标签（如：首充特惠、限时优惠）',
  `sort_order` int NOT NULL DEFAULT 0 COMMENT '排序（数字越小越靠前）',
  `is_hot` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否热门推荐',
  `is_active` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否上架',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除标识',
  PRIMARY KEY (`id`),
  KEY `idx_points_package_active` (`is_active`),
  KEY `idx_points_package_sort` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分套餐表';

-- 2. 扩展订单表，添加积分订单相关字段（使用IF NOT EXISTS避免重复）
-- 检查并添加 order_type 字段
SET @col_exists = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
      AND TABLE_NAME = 'order' 
      AND COLUMN_NAME = 'order_type'
);

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE `order` ADD COLUMN `order_type` tinyint NOT NULL DEFAULT 1 COMMENT ''订单类型（1-会员订单/2-积分订单）'' AFTER `order_no`',
    'SELECT ''Column order_type already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 检查并添加 points_package_id 字段
SET @col_exists = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
      AND TABLE_NAME = 'order' 
      AND COLUMN_NAME = 'points_package_id'
);

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE `order` ADD COLUMN `points_package_id` bigint DEFAULT NULL COMMENT ''积分套餐ID（积分订单）'' AFTER `plan_id`',
    'SELECT ''Column points_package_id already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 检查并添加 points_amount 字段
SET @col_exists = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
      AND TABLE_NAME = 'order' 
      AND COLUMN_NAME = 'points_amount'
);

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE `order` ADD COLUMN `points_amount` int DEFAULT NULL COMMENT ''积分数量（积分订单）'' AFTER `points_package_id`',
    'SELECT ''Column points_amount already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 添加索引（如果不存在）
SET @index_exists = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.STATISTICS 
    WHERE TABLE_SCHEMA = DATABASE() 
      AND TABLE_NAME = 'order' 
      AND INDEX_NAME = 'idx_order_type'
);

SET @sql = IF(@index_exists = 0,
    'CREATE INDEX `idx_order_type` ON `order`(`order_type`)',
    'SELECT ''Index idx_order_type already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_exists = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.STATISTICS 
    WHERE TABLE_SCHEMA = DATABASE() 
      AND TABLE_NAME = 'order' 
      AND INDEX_NAME = 'idx_order_points_package'
);

SET @sql = IF(@index_exists = 0,
    'CREATE INDEX `idx_order_points_package` ON `order`(`points_package_id`)',
    'SELECT ''Index idx_order_points_package already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 3. 扩展积分消费记录表，支持充值类型
-- points_consumption_log 表已存在，只需确保支持 change_type='recharge'
-- 修改注释以明确支持的类型
ALTER TABLE `points_consumption_log`
MODIFY COLUMN `change_type` varchar(32) NOT NULL 
    COMMENT '变动类型 (recharge:充值, consume:消费, refund:退款, expire:过期, admin_adjust:管理员调整)';

-- 4. 插入默认积分套餐数据
INSERT INTO `points_package` 
(name, description, points, bonus_points, total_points, price, original_price, points_expire_days, discount_label, sort_order, is_hot, is_active) 
VALUES
-- 基础套餐
('体验包', '新手体验，小额充值', 100, 0, 100, 10.00, NULL, 365, NULL, 1, 0, 1),
('标准包', '日常使用推荐', 500, 50, 550, 48.00, 50.00, 365, '赠送50积分', 2, 1, 1),
('超值包', '性价比之选', 1000, 150, 1150, 88.00, 100.00, 365, '赠送150积分', 3, 1, 1),
('豪华包', '重度用户首选', 3000, 500, 3500, 258.00, 300.00, 365, '赠送500积分', 4, 0, 1),
('至尊包', '超值优惠', 5000, 1000, 6000, 398.00, 500.00, 365, '赠送1000积分', 5, 1, 1),
('旗舰包', '一次购买全年无忧', 10000, 3000, 13000, 688.00, 1000.00, 365, '赠送3000积分', 6, 0, 1)
ON DUPLICATE KEY UPDATE 
    description = VALUES(description),
    points = VALUES(points),
    bonus_points = VALUES(bonus_points),
    total_points = VALUES(total_points),
    price = VALUES(price),
    original_price = VALUES(original_price),
    update_time = NOW();

-- 5. 初始化系统配置（积分充值相关）
INSERT INTO `system_config` (`config_key`, `config_value`, `description`) VALUES
    ('points.recharge.min_amount', '10.00', '最低充值金额（元）'),
    ('points.recharge.max_amount', '10000.00', '最高充值金额（元）'),
    ('points.default_expire_days', '365', '默认积分有效期（天）'),
    ('points.first_recharge_bonus', '0.1', '首次充值额外赠送比例（10%）')
ON DUPLICATE KEY UPDATE `config_value` = VALUES(`config_value`);

-- 6. 创建积分充值统计视图（便于管理员查看）
CREATE OR REPLACE VIEW `v_points_recharge_stats` AS
SELECT 
    DATE(o.create_time) as recharge_date,
    COUNT(o.id) as order_count,
    SUM(o.amount) as total_amount,
    SUM(o.points_amount) as total_points,
    AVG(o.amount) as avg_amount
FROM `order` o
WHERE o.order_type = 2 
  AND o.status = 1 
  AND o.is_deleted = 0
GROUP BY DATE(o.create_time)
ORDER BY recharge_date DESC;

-- 7. 记录迁移日志
INSERT INTO `migration_log` (`version`, `description`, `executed_at`) 
VALUES ('V6', '添加积分充值系统（积分套餐、支付购买）', NOW())
ON DUPLICATE KEY UPDATE `executed_at` = NOW();

-- ============================================================
-- V6脚本结束
-- ============================================================

