signin+userlevelsystem
Change-Id: I4d2b2660b23521817ae2ed6a55c6c322a20385ec
diff --git a/src/main/resources/schema.sql b/src/main/resources/schema.sql
index 1ee0c87..051ce5e 100644
--- a/src/main/resources/schema.sql
+++ b/src/main/resources/schema.sql
@@ -4,7 +4,11 @@
`user_name` VARCHAR(255) NOT NULL,
`password` VARCHAR(255) NOT NULL,
`email` VARCHAR(255) NOT NULL UNIQUE,
- `passkey` VARCHAR(255) NOT NULL UNIQUE
+ `passkey` VARCHAR(255) NOT NULL UNIQUE,
+ `user_level` ENUM('lv1', 'lv2', 'lv3', 'vip') DEFAULT 'lv1',
+ `signin_count` INT DEFAULT 0,
+ `last_signin_date` DATE,
+ INDEX `idx_user_level` (`user_level`) -- 按等级查询优化
);
-- 用户统计表
CREATE TABLE IF NOT EXISTS `user_stats` (
@@ -158,4 +162,12 @@
FOREIGN KEY (`post_id`) REFERENCES `posts`(`post_id`),
FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`),
FOREIGN KEY (`resolved_by`) REFERENCES `users`(`user_id`)
-);
\ No newline at end of file
+);
+
+CREATE TABLE IF NOT EXISTS `user_signin` (
+ `signin_id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '签到记录ID',
+ `user_id` BIGINT NOT NULL COMMENT '用户ID',
+ `signin_date` DATE NOT NULL COMMENT '签到日期',
+ FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE,
+ UNIQUE KEY `unique_user_daily_signin` (`user_id`, `signin_date`) -- 唯一约束：用户每日只能签到一次
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
\ No newline at end of file