-- Badges — gamification layer on top of user metrics.
--
-- Conceptual model:
--   - A `badge` is a referential entry edited by the BO. It declares the
--     metric it tracks (`metric` = a stable string key like `media.uploaded`
--     or `follower.received`) and whether it's a one-shot reward (`unique`)
--     or a 5-step progression (`level`). Both types share the SAME storage
--     and award mechanic; `type` is purely a UI hint so the front can
--     render a medal vs a progress bar.
--   - `badge_tier` carries one row per achievable level (1..5). Every tier
--     has its own `threshold` (the metric value required) and `xp_reward`
--     (the XP awarded when the user reaches that tier). `unique` badges
--     have exactly one tier (level 1). The xp_reward sits in DB rather
--     than env so product can tune the economy without a redeploy.
--   - `badge_translation` keeps the locale-specific label/description out
--     of the catalogue row — same shape as `topic_translation`.
--   - `user_badge` records what the user currently holds. PK composite
--     `(user_id, badge_id)` means there's at most one row per user/badge
--     pair: the column `level` is updated in place as the user climbs the
--     tiers. We do NOT keep historical rows per level — the audit trail
--     lives in the `notification` table (one row dispatched per level-up)
--     and in `user_transaction` (XP audit row).
--
-- Idempotency:
--   - Awards are monotonic — `BadgeAwardService` only writes when the
--     target level is STRICTLY GREATER than the current level. Replaying
--     the same metric value is a no-op, which matters because the calling
--     services (MediaUploadService, FollowService, …) may emit duplicate
--     awards on retries.
--
-- Future cross-tables:
--   - When badge holders need to be listed efficiently (motivation page
--     "rejoins les 1 234 utilisateurs qui ont décroché ce badge"), the
--     reverse index `(badge_id, level, user_id)` covers the COUNT and
--     the keyset pagination.

CREATE TABLE `badge` (
    `id`         BINARY(16)   NOT NULL,
    `slug`       VARCHAR(64)  NOT NULL,
    -- 'unique' = single-tier medal, 'level' = 1..5 progression. Stored as
    -- ENUM so a typo can never sneak in via BO; the application code maps
    -- this to a PHP enum at hydration time.
    `type`       ENUM('unique', 'level') NOT NULL,
    -- Stable metric key (e.g. 'media.uploaded'). NOT bound to a foreign
    -- table — services emit free strings, the BadgeMetric enum is just
    -- an in-PHP convenience for the most common callers. Indexed because
    -- BadgeAwardService::onMetric() is the hot path: "give me every
    -- active badge that tracks `<metric>`".
    `metric`     VARCHAR(64)  NOT NULL,
    `icon`       VARCHAR(64)  NULL,
    `position`   SMALLINT     NOT NULL DEFAULT 0,
    `is_active`  TINYINT(1)   NOT NULL DEFAULT 1,
    `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME     NULL,
    PRIMARY KEY (`id`),
    -- API identity. Stable across reseeds and human-readable in URLs.
    UNIQUE KEY `uq_bg_slug` (`slug`),
    -- Hot path of the award service. The trailing `position` keeps the
    -- catalogue listing tieble-deterministic.
    KEY `idx_bg_metric_active` (`metric`, `is_active`, `position`),
    -- Catalogue listing for `GET /api/badges` — `(is_active, position, id)`
    -- mirrors the topic catalogue index.
    KEY `idx_bg_active_position` (`is_active`, `position`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- One row per achievable level. PK composite kills duplicates by design,
-- and the natural sort `(badge_id, level)` is what BadgeAwardService
-- needs when computing "highest tier where threshold ≤ newValue".
--
-- `threshold` is BIGINT to comfortably hold cumulative XP values and
-- millions-scale counters without ever overflowing.
-- `xp_reward` is INT (4 billion is plenty for one tier's reward).
CREATE TABLE `badge_tier` (
    `badge_id`  BINARY(16) NOT NULL,
    `level`     TINYINT    NOT NULL,
    `threshold` BIGINT     NOT NULL,
    `xp_reward` INT        NOT NULL DEFAULT 0,
    PRIMARY KEY (`badge_id`, `level`),
    -- Defensive: level is 1..5. The CHECK is a guard rail — the service
    -- enforces it too, but the schema should refuse garbage from BO.
    CONSTRAINT `ck_bt_level_range`     CHECK (`level` BETWEEN 1 AND 5),
    -- Thresholds and xp_reward must be non-negative (CHECK lets the BO
    -- type a typo without corrupting the catalogue).
    CONSTRAINT `ck_bt_threshold_nneg`  CHECK (`threshold` >= 0),
    CONSTRAINT `ck_bt_xp_reward_nneg`  CHECK (`xp_reward` >= 0),
    CONSTRAINT `fk_bt_badge` FOREIGN KEY (`badge_id`)
        REFERENCES `badge`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Per-locale label + optional description. Same pattern as topic_translation.
CREATE TABLE `badge_translation` (
    `badge_id`    BINARY(16)   NOT NULL,
    `locale`      VARCHAR(10)  NOT NULL,
    `label`       VARCHAR(80)  NOT NULL,
    `description` VARCHAR(255) NULL,
    PRIMARY KEY (`badge_id`, `locale`),
    -- "Give me all labels for these badge ids in locale X" — covers the
    -- listing/hydrate paths.
    KEY `idx_bgt_locale_badge` (`locale`, `badge_id`),
    CONSTRAINT `fk_bgt_badge` FOREIGN KEY (`badge_id`)
        REFERENCES `badge`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Current possession. One row per user/badge pair, `level` updated in
-- place as the user climbs (monotonic, awards never go down).
--
-- `earned_at` is the FIRST acquisition timestamp (level 1) and never
-- moves; `updated_at` tracks the last upgrade so the front can show
-- "promoted to L3 two days ago".
--
-- Reverse index `(badge_id, level)` is sized for two future queries:
--   - COUNT of holders of a specific badge (badge details page)
--   - leaderboards / "users at level 5" listings
CREATE TABLE `user_badge` (
    `user_id`    BINARY(16) NOT NULL,
    `badge_id`   BINARY(16) NOT NULL,
    `level`      TINYINT    NOT NULL,
    `earned_at`  DATETIME   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME   NULL,
    PRIMARY KEY (`user_id`, `badge_id`),
    KEY `idx_ub_badge_level` (`badge_id`, `level`),
    CONSTRAINT `ck_ub_level_range` CHECK (`level` BETWEEN 1 AND 5),
    CONSTRAINT `fk_ub_user`  FOREIGN KEY (`user_id`)  REFERENCES `user`(`id`)  ON DELETE CASCADE,
    CONSTRAINT `fk_ub_badge` FOREIGN KEY (`badge_id`) REFERENCES `badge`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
