-- Titles — derived from `user.level` (which is itself derived from
-- `user.experience` via UserLevelCalculator). NOT stored per-user; the
-- displayed `displayTitle` is resolved at read time from this catalogue.
--
-- Mechanic (matches the product spec given by the user):
--   - 5 levels per title bucket. titleIndex = floor((level - 1) / 5).
--   - Within a bucket, rank = (level - 1) % 5 → 0..4, mapped to a fixed
--     set of 5 sub-title templates ("", "averti", "confirmé", "expert",
--     "légendaire"). The templates live in the i18n catalogue
--     (`resources/lang/<locale>/titles.php`), NOT in this schema — the
--     user confirmed they stay forever fixed (no BO knob).
--   - Beyond the last seeded title, the resolver caps to the highest
--     active title (the user keeps progressing through the rank ladder
--     of the top bucket — "Légende légendaire", in effect).
--
-- Why a separate catalogue table:
--   - The BO progressively adds buckets ("Touriste", "Explorateur",
--     "Pionnier", …). A seed file would force a redeploy.
--   - `position` is the bucket index (0 = lv 1..5, 1 = lv 6..10, …).
--     UNIQUE so two titles can't fight for the same band.
--   - `is_active` lets the BO retire an outdated title without breaking
--     history; the resolver falls back to the highest *active* lower-or-
--     equal bucket so a user already past that level doesn't "lose" a
--     visible title.
--   - i18n labels live in `title_translation` (single round-trip with
--     COALESCE fallback, same pattern as `topic_translation`).

CREATE TABLE `title` (
    `id`         BINARY(16)   NOT NULL,
    `slug`       VARCHAR(64)  NOT NULL,
    -- titleIndex: 0 ⇔ lv 1..5, 1 ⇔ lv 6..10, … min level = position*5+1.
    `position`   SMALLINT     NOT NULL,
    `is_active`  TINYINT(1)   NOT NULL DEFAULT 1,
    `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME     NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_t_slug`     (`slug`),
    UNIQUE KEY `uq_t_position` (`position`),
    -- "Walk active titles bottom-up to cap a level" — the hot path of
    -- the resolver. Composite (is_active, position) so MySQL can skip
    -- straight to the active subset in ascending position order.
    KEY `idx_t_active_position` (`is_active`, `position`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Per-locale label of the BUCKET (not the displayed line). E.g. fr-FR
-- "Touriste" + en-US "Tourist". The displayed line is computed by the
-- resolver as a Translator-rendered template `{title}` / `{title} averti`
-- using the rank index. PK composite (title, locale).
CREATE TABLE `title_translation` (
    `title_id` BINARY(16)  NOT NULL,
    `locale`   VARCHAR(10) NOT NULL,
    `label`    VARCHAR(80) NOT NULL,
    PRIMARY KEY (`title_id`, `locale`),
    KEY `idx_tt_locale_title` (`locale`, `title_id`),
    CONSTRAINT `fk_tt_title` FOREIGN KEY (`title_id`)
        REFERENCES `title`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed example (not run automatically — paste into BO once tables exist):
--
-- INSERT INTO `title` (`id`, `slug`, `position`, `is_active`)
-- VALUES
--   (UNHEX(REPLACE(UUID(),'-','')), 'tourist',   0, 1),
--   (UNHEX(REPLACE(UUID(),'-','')), 'explorer',  1, 1),
--   (UNHEX(REPLACE(UUID(),'-','')), 'pioneer',   2, 1);
--
-- INSERT INTO `title_translation` (`title_id`, `locale`, `label`)
-- SELECT `id`, 'fr-FR', 'Touriste'   FROM `title` WHERE `slug` = 'tourist'
-- UNION ALL SELECT `id`, 'en-US', 'Tourist'    FROM `title` WHERE `slug` = 'tourist'
-- UNION ALL SELECT `id`, 'fr-FR', 'Explorateur' FROM `title` WHERE `slug` = 'explorer'
-- UNION ALL SELECT `id`, 'en-US', 'Explorer'    FROM `title` WHERE `slug` = 'explorer'
-- UNION ALL SELECT `id`, 'fr-FR', 'Pionnier'   FROM `title` WHERE `slug` = 'pioneer'
-- UNION ALL SELECT `id`, 'en-US', 'Pioneer'    FROM `title` WHERE `slug` = 'pioneer';
