-- 用户-软件订阅表：支持多软件共用一套用户表，每个用户在每个软件下独立卡密/到期时间
-- 执行前请确认 software 表已存在且存在 code='buff-xp-steam-sh' 的记录

CREATE TABLE IF NOT EXISTS `user_software_subscription` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` int NOT NULL COMMENT '用户ID',
  `software_id` int NOT NULL COMMENT '软件ID',
  `card_id` int DEFAULT NULL COMMENT '当前卡密ID',
  `expire_time` datetime DEFAULT NULL COMMENT '到期时间',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除: 0-否, 1-是',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_software` (`user_id`,`software_id`),
  KEY `idx_user_software_subscription_user_id` (`user_id`),
  KEY `idx_user_software_subscription_software_id` (`software_id`),
  KEY `idx_user_software_subscription_expire_time` (`expire_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户软件订阅：多软件共用用户表时，每用户每软件一条';

-- 将现有用户的 card_id/expire_time 迁移到订阅表（默认归属 buff-xp-steam-sh）
INSERT INTO `user_software_subscription` (`user_id`, `software_id`, `card_id`, `expire_time`, `is_deleted`, `created_at`, `updated_at`)
SELECT u.`id`, s.`id`, u.`card_id`, u.`expire_time`, 0, NOW(), NOW()
FROM `users` u
CROSS JOIN (SELECT `id` FROM `software` WHERE `code` = 'buff-xp-steam-sh' AND `is_deleted` = 0 LIMIT 1) s
WHERE u.`is_deleted` = 0
  AND (u.`card_id` IS NOT NULL OR u.`expire_time` IS NOT NULL)
  AND NOT EXISTS (
    SELECT 1 FROM `user_software_subscription` sub
    WHERE sub.`user_id` = u.`id` AND sub.`software_id` = s.`id`
  );