-- MemeMind Server: Auth 模块数据库迁移
-- 新增 wx_users 表和 wx_user_level_progress 表
-- 执行时间: 2026-04-05

-- ==========================================
-- 1. 创建 wx_users 表
-- ==========================================
CREATE TABLE IF NOT EXISTS `wx_users` (
  `id` varchar(36) NOT NULL,
  `openid` varchar(128) NOT NULL,
  `session_key` varchar(255) DEFAULT NULL,
  `nickname` varchar(100) DEFAULT NULL,
  `avatar_url` text DEFAULT NULL,
  `points` int NOT NULL DEFAULT 10,
  `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updated_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_openid` (`openid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==========================================
-- 2. 创建 wx_user_level_progress 表
-- ==========================================
CREATE TABLE IF NOT EXISTS `wx_user_level_progress` (
  `id` varchar(36) NOT NULL,
  `user_id` varchar(191) NOT NULL,
  `level_id` varchar(191) NOT NULL,
  `completed_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_level` (`user_id`, `level_id`),
  KEY `FK_wx_ulp_user` (`user_id`),
  CONSTRAINT `FK_wx_ulp_user` FOREIGN KEY (`user_id`) REFERENCES `wx_users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;