-- Badge categories — a curated, data-driven grouping layer on top of the
-- `badge` catalogue. Each badge belongs to AT MOST ONE category (a plain
-- 1:N: `badge.category_id` → `badge_category.id`), so the front can render
-- the catalogue in labelled sections ("Onboarding", "Création", …).
--
-- Design:
--   - `badge_category` is a small BO-edited referential. `slug` is the
--     public identity (stable, human-readable, also the i18n key
--     `badgeCategories.<slug>.label` resolved from
--     `resources/lang/<locale>/badge_categories.php`) — labels are NOT
--     stored here, they live in the flat locale catalogue exactly like the
--     badge labels themselves.
--   - `position` drives the section order in the grouped listing.
--   - `badge.category_id` is NULLABLE: a badge may be temporarily
--     uncategorised (e.g. right after creation, before the BO files it).
--     The FK is `ON DELETE RESTRICT` — a category still referenced by a
--     badge cannot be dropped at the DB level; the admin delete path adds a
--     friendly 409 on top (mirrors the provider-type delete guard).
--
-- Idempotency: `INSERT IGNORE` + slug-as-natural-key make the seeds safe to
-- re-run. The ALTER guards against re-adding the column on replay via the
-- information_schema probe below.

CREATE TABLE IF NOT EXISTS `badge_category` (
    `id`         SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `slug`       VARCHAR(64)       NOT NULL,
    `position`   SMALLINT          NOT NULL DEFAULT 0,
    `created_at` DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME          NULL,
    PRIMARY KEY (`id`),
    -- API identity, stable across reseeds.
    UNIQUE KEY `uq_bgc_slug` (`slug`),
    -- Section ordering for the grouped catalogue listing.
    KEY `idx_bgc_position` (`position`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add `badge.category_id` only once (re-run safe). MySQL has no portable
-- "ADD COLUMN IF NOT EXISTS" across the versions we target, so we branch on
-- information_schema through a prepared statement.
SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'badge'
       AND COLUMN_NAME  = 'category_id'
);
SET @ddl := IF(@col_exists = 0,
    'ALTER TABLE `badge`
        ADD COLUMN `category_id` SMALLINT UNSIGNED NULL AFTER `metric`,
        ADD KEY `idx_bg_category` (`category_id`, `position`, `id`),
        ADD CONSTRAINT `fk_bg_category` FOREIGN KEY (`category_id`)
            REFERENCES `badge_category`(`id`) ON DELETE RESTRICT',
    'SELECT 1');
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Seed the four initial categories in display order.
INSERT IGNORE INTO `badge_category` (`slug`, `position`) VALUES
    ('onboarding', 10),
    ('creation',   20),
    ('social',     30),
    ('engagement', 40);

-- File the four existing badges into their category (idempotent: sets the
-- FK by slug on both sides).
UPDATE `badge` b
   JOIN `badge_category` c ON c.`slug` = 'onboarding'
   SET b.`category_id` = c.`id`
 WHERE b.`slug` = 'welcome-aboard';

UPDATE `badge` b
   JOIN `badge_category` c ON c.`slug` = 'creation'
   SET b.`category_id` = c.`id`
 WHERE b.`slug` = 'shutterbug';

UPDATE `badge` b
   JOIN `badge_category` c ON c.`slug` = 'social'
   SET b.`category_id` = c.`id`
 WHERE b.`slug` = 'wordsmith';

UPDATE `badge` b
   JOIN `badge_category` c ON c.`slug` = 'engagement'
   SET b.`category_id` = c.`id`
 WHERE b.`slug` = 'crowd-favorite';
