CREATE TABLE IF NOT EXISTS `users` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `uuid` CHAR(36) NOT NULL,
    `display_name` VARCHAR(80) NOT NULL,
    `username` VARCHAR(40) NULL,
    `email` VARCHAR(190) NOT NULL,
    `password_hash` VARCHAR(255) NULL,
    `avatar_url` VARCHAR(500) NULL,
    `role` ENUM('guest','user','trusted_user','content_creator','moderator','admin','owner') NOT NULL DEFAULT 'user',
    `reputation_score` INT NOT NULL DEFAULT 0,
    `rank` VARCHAR(40) NOT NULL DEFAULT 'Ny',
    `auto_post_enabled` TINYINT(1) NOT NULL DEFAULT 0,
    `is_content_creator` TINYINT(1) NOT NULL DEFAULT 0,
    `is_banned` TINYINT(1) NOT NULL DEFAULT 0,
    `last_login_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_users_uuid` (`uuid`),
    UNIQUE KEY `uniq_users_email` (`email`),
    UNIQUE KEY `uniq_users_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
