42 lines
3.0 KiB
SQL
42 lines
3.0 KiB
SQL
-- hot_hour 相关表结构升级（2026-05-11）
|
||
-- 目的：
|
||
-- 1) 支持总部配置学时套餐（原价、活动价、套餐说明、课时时长、适用类型）
|
||
-- 2) 支持企业端/移动端通过企业钱包或个人钱包购买并记录钱包流水关联
|
||
-- 3) 支持课时扣减链路（总/已用/剩余、使用时间、扣减前后值、并发控制）
|
||
|
||
ALTER TABLE `hot_hour_package`
|
||
ADD COLUMN `origin_price` DECIMAL(18, 2) NULL COMMENT '原价格（元）' AFTER `hour_count`,
|
||
ADD COLUMN `package_price` DECIMAL(18, 2) NULL COMMENT '套餐价格（元）' AFTER `origin_price`,
|
||
ADD COLUMN `valid_start_time` DATETIME NULL COMMENT '活动有效开始时间' AFTER `unit_price`,
|
||
ADD COLUMN `valid_end_time` DATETIME NULL COMMENT '活动有效结束时间' AFTER `valid_start_time`,
|
||
ADD COLUMN `applicable_types` VARCHAR(255) NULL COMMENT '适用类型(多选),逗号拼接' AFTER `valid_end_time`,
|
||
ADD COLUMN `package_desc` VARCHAR(500) NULL COMMENT '套餐说明' AFTER `applicable_types`;
|
||
|
||
ALTER TABLE `hot_hour_package_purchase`
|
||
ADD COLUMN `origin_price` DECIMAL(18, 2) NULL COMMENT '原价格（元）' AFTER `hour_count`,
|
||
ADD COLUMN `package_price` DECIMAL(18, 2) NULL COMMENT '套餐价格（元）' AFTER `origin_price`,
|
||
ADD COLUMN `total_hours` INT NULL COMMENT '总课时数' AFTER `quantity`,
|
||
ADD COLUMN `wallet_type` TINYINT NULL COMMENT '钱包类型：1=个人钱包 2=企业钱包' AFTER `pay_method`,
|
||
ADD COLUMN `wallet_id` BIGINT UNSIGNED NULL COMMENT '钱包ID' AFTER `wallet_type`,
|
||
ADD COLUMN `wallet_transaction_id` BIGINT UNSIGNED NULL COMMENT '钱包流水ID' AFTER `wallet_id`,
|
||
ADD COLUMN `wallet_transaction_no` VARCHAR(64) NULL COMMENT '钱包流水号' AFTER `wallet_transaction_id`,
|
||
ADD COLUMN `package_snapshot` TEXT NULL COMMENT '套餐快照（JSON）' AFTER `pay_time`,
|
||
ADD KEY `idx_wallet_id` (`wallet_id`),
|
||
ADD KEY `idx_wallet_transaction_id` (`wallet_transaction_id`);
|
||
|
||
ALTER TABLE `hot_hour_purchase_detail`
|
||
ADD COLUMN `purchase_id` BIGINT UNSIGNED NULL COMMENT '学时套餐购买记录ID' AFTER `buyer_company_name`,
|
||
ADD COLUMN `total_hours` INT NULL COMMENT '总课时数' AFTER `purchase_count`,
|
||
ADD COLUMN `wallet_type` TINYINT NULL COMMENT '钱包类型：1=个人钱包 2=企业钱包' AFTER `remaining_hours`,
|
||
ADD COLUMN `wallet_id` BIGINT UNSIGNED NULL COMMENT '钱包ID' AFTER `wallet_type`,
|
||
ADD COLUMN `version` INT NULL DEFAULT 0 COMMENT '乐观锁版本号' AFTER `wallet_id`,
|
||
ADD KEY `idx_purchase_id` (`purchase_id`),
|
||
ADD KEY `idx_wallet_id` (`wallet_id`);
|
||
|
||
ALTER TABLE `hot_hour_usage_detail`
|
||
ADD COLUMN `operator_id` BIGINT UNSIGNED NULL COMMENT '操作人ID' AFTER `user_name`,
|
||
ADD COLUMN `operator_name` VARCHAR(64) NULL COMMENT '操作人' AFTER `operator_id`,
|
||
ADD COLUMN `before_remaining_hours` INT NULL COMMENT '扣减前剩余学时' AFTER `used_hours`,
|
||
ADD COLUMN `after_remaining_hours` INT NULL COMMENT '扣减后剩余学时' AFTER `before_remaining_hours`,
|
||
ADD KEY `idx_operator_id` (`operator_id`);
|