-- Seed the "wordsmith" badge — progression medal for users who comment
-- on OTHER users' media. Self-comments are excluded by the trigger that
-- maintains `user_stats.num_external_comments` (see
-- `…_add_user_stats_num_external_comments.sql`).
--
-- Design:
--   - 5-tier progression (`type = 'level'`), thresholds 10/50/200/500/1500.
--     Geometric-ish spacing (≈ ×3..×5) keeps each level a meaningful step
--     so a single commenting session never spams "L2 + L3 + L4" in the
--     feed.
--   - XP rewards are slightly LOWER than `shutterbug` (25/75/200/500/1200
--     → 2000 cumulative across L1..L5 vs shutterbug's 2600) because the
--     per-occurrence effort is lower.
--   - `position = 30` slots the badge after `shutterbug` (20) in the
--     catalogue listing.
--   - Metric is `comment.posted` (see `BadgeMetric::CommentPosted`). The
--     emitter is `MediaCommentService::create()` AFTER commit, fed by the
--     monotonic `user_stats.num_external_comments` counter.
--
-- Idempotency: INSERT IGNORE + slug-as-natural-key make the migration
-- safe to re-run.

INSERT IGNORE INTO `badge`
    (`id`, `slug`, `type`, `metric`, `position`, `is_active`, `created_at`)
VALUES
    (UNHEX(REPLACE(UUID(), '-', '')),
     'wordsmith',
     'level',
     'comment.posted',
     30,
     1,
     NOW());

INSERT IGNORE INTO `badge_tier`
    (`badge_id`, `level`, `threshold`, `xp_reward`)
SELECT `id`, 1,   10,   25 FROM `badge` WHERE `slug` = 'wordsmith'
UNION ALL
SELECT `id`, 2,   50,   75 FROM `badge` WHERE `slug` = 'wordsmith'
UNION ALL
SELECT `id`, 3,  200,  200 FROM `badge` WHERE `slug` = 'wordsmith'
UNION ALL
SELECT `id`, 4,  500,  500 FROM `badge` WHERE `slug` = 'wordsmith'
UNION ALL
SELECT `id`, 5, 1500, 1200 FROM `badge` WHERE `slug` = 'wordsmith';
