-- 使用反引号包裹保留字表名
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` DATETIME NOT NULL,
                        `identification_number` VARCHAR(18),
                        `avatar` VARCHAR(255),
                        `isfollowed` BOOLEAN NOT NULL DEFAULT FALSE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 外键表同样使用反引号
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`)
);

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