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),    -- 身份证号建议用 VARCHAR(18)
                        `avatar` VARCHAR(255),
                        `isfollowed` BOOLEAN NOT NULL DEFAULT FALSE,
                        INDEX `idx_user_id` (`user_id`)          -- 可选：主键通常自带索引
) 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'
         );