-- 数据库迁移脚本：统一收费模式为余额
-- 执行前请备份数据库

-- 1. 为app_users表添加balance字段（如果不存在）
ALTER TABLE app_users ADD COLUMN IF NOT EXISTS balance DECIMAL(10,2) DEFAULT 0;

-- 2. 为applications表添加相关字段（如果不存在）
ALTER TABLE applications ADD COLUMN IF NOT EXISTS deduction_cycle VARCHAR(20) DEFAULT 'per_use';
ALTER TABLE applications ADD COLUMN IF NOT EXISTS deduction_amount DECIMAL(10,2) DEFAULT 1;
ALTER TABLE applications ADD COLUMN IF NOT EXISTS trial_balance DECIMAL(10,2) DEFAULT 0;

-- 3. 为card_types表修改value字段类型（如果需要）
ALTER TABLE card_types MODIFY COLUMN value DECIMAL(10,2);

-- 4. 迁移app_users数据：将expiry_at和points转换为balance
-- 注意：这里需要根据实际业务逻辑调整转换规则

-- 4.1 对于订阅模式用户，将剩余时间转换为余额
-- 假设：1小时 = 1余额单位
UPDATE app_users au
JOIN applications a ON au.application_id = a.id
SET au.balance = 
    CASE 
        WHEN au.expiry_at IS NULL THEN 0
        WHEN au.expiry_at = '9999-12-31 23:59:59' THEN -1
        ELSE GREATEST(0, TIMESTAMPDIFF(HOUR, NOW(), au.expiry_at))
    END
WHERE a.billing_type = 'subscription' AND au.balance = 0;

-- 4.2 对于计时模式用户，将剩余时间转换为余额
-- 假设：1分钟 = 0.01余额单位
UPDATE app_users au
JOIN applications a ON au.application_id = a.id
SET au.balance = 
    CASE 
        WHEN au.expiry_at IS NULL THEN 0
        WHEN au.expiry_at = '9999-12-31 23:59:59' THEN -1
        ELSE GREATEST(0, TIMESTAMPDIFF(MINUTE, NOW(), au.expiry_at) * 0.01)
    END
WHERE a.billing_type = 'timer' AND au.balance = 0;

-- 4.3 对于点卡模式用户，将points转换为balance
UPDATE app_users au
JOIN applications a ON au.application_id = a.id
SET au.balance = 
    CASE 
        WHEN au.points = -1 THEN -1
        ELSE COALESCE(au.points, 0)
    END
WHERE a.billing_type = 'points' AND au.balance = 0;

-- 5. 更新applications表的billing_type值
-- 将'timer'保持不变，将'point'改为'count'
UPDATE applications SET billing_type = 'count' WHERE billing_type = 'point';

-- 6. 删除不再需要的字段（可选，建议保留一段时间以便回滚）
-- ALTER TABLE app_users DROP COLUMN IF EXISTS expiry_at;
-- ALTER TABLE app_users DROP COLUMN IF EXISTS points;
-- ALTER TABLE applications DROP COLUMN IF EXISTS point_deduction_cycle;
-- ALTER TABLE applications DROP COLUMN IF EXISTS points_per_cycle;
-- ALTER TABLE applications DROP COLUMN IF EXISTS point_price;
-- ALTER TABLE applications DROP COLUMN IF EXISTS trial_type;
-- ALTER TABLE applications DROP COLUMN IF EXISTS trial_duration;
-- ALTER TABLE applications DROP COLUMN IF EXISTS trial_points;
-- ALTER TABLE card_types DROP COLUMN IF EXISTS billing_type;

-- 7. 创建索引以提高查询性能
CREATE INDEX IF NOT EXISTS idx_app_users_balance ON app_users(balance);
CREATE INDEX IF NOT EXISTS idx_applications_billing_type ON applications(billing_type);

-- 迁移完成
SELECT 'Migration completed successfully!' as status;