-- Seed the "welcome-aboard" badge — awarded when a user confirms their
-- email address (the `EmailConfirmationService::confirm()` path fires
-- `BadgeMetric::UserConfirmed` with newValue=1 right after marking the
-- user confirmed).
--
-- Design:
--   - One-shot medal (`type = 'unique'`) with a single tier at threshold 1.
--     The metric is treated as a counter for symmetry with the rest of the
--     catalogue: 0 = pending, 1 = confirmed → tier 1 unlocks.
--   - The XP reward (25) sits in `badge_tier.xp_reward` rather than env so
--     product can rebalance without a redeploy.
--   - `position = 10` reserves the early slots for other "first-step"
--     onboarding badges (first upload, first follow…).
--
-- Idempotent: the slug is the natural key (`uq_bg_slug`) so this migration
-- can be re-run safely without violating UNIQUE — `INSERT IGNORE` skips
-- when the row already exists. We use UUID() for the BINARY(16) PK because
-- the BO assigns ids dynamically — re-running on a DB where the badge
-- already exists must NOT create a duplicate row.

INSERT IGNORE INTO `badge`
    (`id`, `slug`, `type`, `metric`, `position`, `is_active`, `created_at`)
VALUES
    (UNHEX(REPLACE(UUID(), '-', '')),
     'welcome-aboard',
     'unique',
     'user.confirmed',
     10,
     1,
     NOW());

-- The tier row uses the badge_id we just inserted (or the pre-existing
-- one when re-running). We resolve it by slug — the UNIQUE key guarantees
-- a single match.
INSERT IGNORE INTO `badge_tier`
    (`badge_id`, `level`, `threshold`, `xp_reward`)
SELECT `id`, 1, 1, 25
  FROM `badge`
 WHERE `slug` = 'welcome-aboard';
