CREATE TABLE `username_history` (
    `id`          BINARY(16)  NOT NULL,
    `user_id`     BINARY(16)  NOT NULL,
    `username`    VARCHAR(32) NOT NULL,
    `assigned_at` DATETIME    NOT NULL,
    `released_at` DATETIME    NULL,
    PRIMARY KEY (`id`),
    KEY `idx_user_assigned` (`user_id`, `assigned_at`),
    KEY `idx_username_released` (`username`, `released_at`),
    CONSTRAINT `fk_username_history_user`
        FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Backfill: each existing user gets a single open history row for their current
-- username, using joined_at as the assignment timestamp (best-effort — we have
-- no record of an earlier change). UUID() generates a fresh value per row.
INSERT INTO `username_history` (`id`, `user_id`, `username`, `assigned_at`, `released_at`)
SELECT UNHEX(REPLACE(UUID(), '-', '')), `id`, `username`, `joined_at`, NULL
FROM `user`;
