-- Seed the "shutterbug" badge — progression medal earned as the user
-- publishes more media. Tracks `BadgeMetric::MediaUploaded` (the metric
-- value comes from `user_stats.num_media`, maintained by MySQL triggers
-- on `media` INSERT/DELETE — see UserStatsRepository).
--
-- Design:
--   - 5-tier progression (`type = 'level'`), thresholds 10/50/100/200/500.
--     The geometric-ish spacing keeps each level a meaningful step (each
--     ≈ 2..5× the previous count) so the feed never spams "L2 + L3 + L4"
--     in the same upload session.
--   - XP rewards scale with the threshold but reward the consistent
--     contributor (50/150/300/600/1500 → 2600 cumulative across L1..L5,
--     vs the 25 000 XP a user collects passively on the underlying
--     uploads via XP_PER_MEDIA_UPLOAD).
--   - `position = 20` slots the badge after `welcome-aboard` (10) in the
--     catalogue listing.
--
-- Idempotency: INSERT IGNORE + slug-as-natural-key make the migration
-- safe to re-run. The badge row uses `UUID()` for the BINARY(16) PK
-- because no caller pins the id; the tier rows resolve `badge_id` by
-- slug so the SELECT picks up either the freshly-inserted row or the
-- pre-existing one on replay.

INSERT IGNORE INTO `badge`
    (`id`, `slug`, `type`, `metric`, `position`, `is_active`, `created_at`)
VALUES
    (UNHEX(REPLACE(UUID(), '-', '')),
     'shutterbug',
     'level',
     'media.uploaded',
     20,
     1,
     NOW());

INSERT IGNORE INTO `badge_tier`
    (`badge_id`, `level`, `threshold`, `xp_reward`)
SELECT `id`, 1,  10,   50 FROM `badge` WHERE `slug` = 'shutterbug'
UNION ALL
SELECT `id`, 2,  50,  150 FROM `badge` WHERE `slug` = 'shutterbug'
UNION ALL
SELECT `id`, 3, 100,  300 FROM `badge` WHERE `slug` = 'shutterbug'
UNION ALL
SELECT `id`, 4, 200,  600 FROM `badge` WHERE `slug` = 'shutterbug'
UNION ALL
SELECT `id`, 5, 500, 1500 FROM `badge` WHERE `slug` = 'shutterbug';
