-- ============================================================================
-- 工具额度体系（免费 / 会员 / 付费次数包）建表脚本
--
-- ⚠️ 这份脚本是「给人看的对照版」。服务端启动时会自动执行等价且幂等的迁移
--    （见 internal/logic/migrate.go），所以正常升级只需要重启后端，不用手工跑。
--    仅当你需要手工核对、或在没有服务端的环境里初始化时，才执行本文件。
--
-- 幂等：可重复执行。表用 CREATE TABLE IF NOT EXISTS，列先用 information_schema
--      判存在再 ALTER（MySQL 不支持 ADD COLUMN IF NOT EXISTS）。
-- ============================================================================

-- ---------------------------------------------------------------------------
-- 1. 工具额度配置（在「工具管理」里维护）
-- ---------------------------------------------------------------------------
SET @db := DATABASE();

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'tools' AND COLUMN_NAME = 'free_quota') = 0,
  'ALTER TABLE `tools` ADD COLUMN `free_quota` int NOT NULL DEFAULT 3 COMMENT ''免费额度（每周期次数）''',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'tools' AND COLUMN_NAME = 'member_quota') = 0,
  'ALTER TABLE `tools` ADD COLUMN `member_quota` int NOT NULL DEFAULT 100 COMMENT ''会员额度（每周期次数）''',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'tools' AND COLUMN_NAME = 'pay_cost') = 0,
  'ALTER TABLE `tools` ADD COLUMN `pay_cost` int NOT NULL DEFAULT 1 COMMENT ''付费额度每次消耗系数''',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ---------------------------------------------------------------------------
-- 2. 额度周期：用户与会员套餐各自的覆盖值（0 = 跟随全局设置）
-- ---------------------------------------------------------------------------
SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'users' AND COLUMN_NAME = 'quota_period_days') = 0,
  'ALTER TABLE `users` ADD COLUMN `quota_period_days` int NOT NULL DEFAULT 0 COMMENT ''额度重置周期（天），0=跟随全局''',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'member_plans' AND COLUMN_NAME = 'quota_period_days') = 0,
  'ALTER TABLE `member_plans` ADD COLUMN `quota_period_days` int NOT NULL DEFAULT 0 COMMENT ''购买后生效的额度周期（天），0=不调整''',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ---------------------------------------------------------------------------
-- 3. 订单表承载两类商品（会员套餐 / 次数包）
-- ---------------------------------------------------------------------------
SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'member_orders' AND COLUMN_NAME = 'order_type') = 0,
  'ALTER TABLE `member_orders` ADD COLUMN `order_type` tinyint NOT NULL DEFAULT 1 COMMENT ''1=会员套餐 2=次数包''',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'member_orders' AND COLUMN_NAME = 'pack_key') = 0,
  'ALTER TABLE `member_orders` ADD COLUMN `pack_key` varchar(64) NOT NULL DEFAULT '''' COMMENT ''次数包档位''',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'member_orders' AND COLUMN_NAME = 'tool_key') = 0,
  'ALTER TABLE `member_orders` ADD COLUMN `tool_key` varchar(64) NOT NULL DEFAULT '''' COMMENT ''次数包绑定工具''',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'member_orders' AND COLUMN_NAME = 'times') = 0,
  'ALTER TABLE `member_orders` ADD COLUMN `times` int NOT NULL DEFAULT 0 COMMENT ''次数包次数''',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ---------------------------------------------------------------------------
-- 4. 次数包档位（在「次数包管理」里维护；product_id 必须与 MP 后台道具一致）
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `quota_packs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `pack_key` varchar(64) NOT NULL COMMENT '档位标识，跨端契约',
  `name` varchar(64) NOT NULL DEFAULT '' COMMENT '展示名',
  `times` int NOT NULL DEFAULT 0 COMMENT '可用次数',
  `price_cents` bigint NOT NULL DEFAULT 0 COMMENT '价格（分）',
  `product_id` varchar(64) NOT NULL DEFAULT '' COMMENT '微信虚拟支付道具 ID',
  `valid_days` int NOT NULL DEFAULT 365 COMMENT '有效期天数，默认一年',
  `tool_key` varchar(64) NOT NULL DEFAULT '' COMMENT '绑定工具，空=通用档位',
  `badge` varchar(32) NOT NULL DEFAULT '' COMMENT '角标文案',
  `sort` int NOT NULL DEFAULT 0,
  `is_enabled` tinyint NOT NULL DEFAULT 1,
  `remark` varchar(255) NOT NULL DEFAULT '',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_pack_key` (`pack_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='次数包档位（付费额度包）';

-- ---------------------------------------------------------------------------
-- 5. 用户付费额度余额（按工具绑定，买断制）
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `user_tool_quota` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `tool_key` varchar(64) NOT NULL,
  `times_left` int NOT NULL DEFAULT 0 COMMENT '剩余次数',
  `total_bought` int NOT NULL DEFAULT 0 COMMENT '累计购买次数',
  `expire_at` datetime DEFAULT NULL COMMENT '到期时间，NULL 视为已过期',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_tool` (`user_id`,`tool_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户工具付费额度余额';

-- ---------------------------------------------------------------------------
-- 6. 用户周期用量（滚动窗口，归零由服务端在读写时判定）
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `user_quota_usage` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `tool_key` varchar(64) NOT NULL,
  `period_start` datetime NOT NULL COMMENT '当前周期起点',
  `period_end` datetime NOT NULL COMMENT '当前周期终点',
  `free_used` int NOT NULL DEFAULT 0 COMMENT '本周期已用免费额度',
  `member_used` int NOT NULL DEFAULT 0 COMMENT '本周期已用会员额度',
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_tool` (`user_id`,`tool_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户工具额度周期用量';

-- ---------------------------------------------------------------------------
-- 7. 后台可改的全局配置（额度周期等）
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `settings` (
  `k` varchar(64) NOT NULL,
  `v` varchar(255) NOT NULL DEFAULT '',
  `remark` varchar(255) NOT NULL DEFAULT '',
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='后台可改的全局配置';

-- 种子数据（服务端启动时也会幂等写入同样的内容）
INSERT IGNORE INTO `settings` (`k`, `v`, `remark`, `updated_at`)
VALUES ('quota_period_days', '7', '全局默认额度重置周期（天）', NOW());