-- Gamification: streak, XP, level on users + user_badges table
ALTER TABLE users
    ADD COLUMN streak_days INT UNSIGNED NOT NULL DEFAULT 0,
    ADD COLUMN streak_best INT UNSIGNED NOT NULL DEFAULT 0,
    ADD COLUMN streak_last_date DATE DEFAULT NULL,
    ADD COLUMN level INT UNSIGNED NOT NULL DEFAULT 1,
    ADD COLUMN xp INT UNSIGNED NOT NULL DEFAULT 0;

CREATE TABLE IF NOT EXISTS user_badges (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    badge_key VARCHAR(40) NOT NULL,
    earned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_user_badge (user_id, badge_key),
    KEY idx_ub_user (user_id)
);