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 invite_code (
                             code VARCHAR(20) PRIMARY KEY,
                             max_uses INT NOT NULL,
                             remaining_uses INT NOT NULL,
                             expiry_time DATETIME NOT NULL,
                             created_by INT NOT NULL,
                             created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                             FOREIGN KEY (created_by) REFERENCES user(user_id)
);

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'
         );

INSERT INTO invite_code (
    code, max_uses, remaining_uses, expiry_time, created_by
) VALUES
      ('WELCOME2025', 5, 5, '2026-01-01 00:00:00', 1),
      ('INVITEONLY', 3, 3, '2025-12-31 23:59:59', 1);