-- 使用反引号包裹保留字表名
CREATE TABLE `user` (
                        `user_id` INT PRIMARY KEY AUTO_INCREMENT,
                        `username` VARCHAR(255) NOT NULL UNIQUE,
                        `email` VARCHAR(255) NOT NULL UNIQUE,
                        `password` VARCHAR(255) NOT NULL,
                        `address` VARCHAR(255),
                        `role` VARCHAR(50) NOT NULL DEFAULT 'user',
                        `profile_pic` VARCHAR(255),
                        `registration_date` TIMESTAMP NOT NULL,
                        `identification_number` VARCHAR(18),
                        `avatar` VARCHAR(255),
                        `isfollowed` BOOLEAN NOT NULL DEFAULT FALSE
);

-- 外键表同样使用反引号
CREATE TABLE `user_follow` (
                               `follower_id` INT NOT NULL,
                               `followed_id` INT NOT NULL,
                               `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                               PRIMARY KEY (`follower_id`, `followed_id`),
                               FOREIGN KEY (`follower_id`) REFERENCES `user`(`user_id`),
                               FOREIGN KEY (`followed_id`) REFERENCES `user`(`user_id`)
);

-- 新增任务表
CREATE TABLE `task` (
                        `task_id` INT AUTO_INCREMENT PRIMARY KEY,
                        `user_id` INT NOT NULL,
                        `type` VARCHAR(50) NOT NULL, -- "seed", "post", "comment"
                        `completed` BOOLEAN NOT NULL DEFAULT FALSE,
                        FOREIGN KEY (`user_id`) REFERENCES `user`(`user_id`)
);

-- 新增帖子表
CREATE TABLE `post` (
                        `post_id` BIGINT AUTO_INCREMENT PRIMARY KEY,
                        `user_id` INT NOT NULL,
                        `title` VARCHAR(255) NOT NULL,
                        `content` TEXT NOT NULL,
                        `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        `views` INT DEFAULT 0,
                        FOREIGN KEY (`user_id`) REFERENCES `user`(`user_id`)
);

-- 新增评论表
CREATE TABLE `comment` (
                           `comment_id` BIGINT AUTO_INCREMENT PRIMARY KEY,
                           `post_id` BIGINT NOT NULL,
                           `user_id` INT NOT NULL,
                           `content` TEXT NOT NULL,
                           `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                           FOREIGN KEY (`post_id`) REFERENCES `post`(`post_id`),
                           FOREIGN KEY (`user_id`) REFERENCES `user`(`user_id`)
);

-- 插入语句使用反引号包裹表名和列名
INSERT INTO `user` (
    `username`, `email`, `password`, `registration_date`, `identification_number`, `role`
) VALUES (
             'admin', 'admin@example.com', 'admin123', CURRENT_TIMESTAMP, '87654321', 'admin'
         );