-- @database: main
--
-- Currency catalogue — makes the set of usable currencies DATA instead of a
-- hard-coded PHP enum, so the back-office can mint a new platform currency
-- ("gems", "credits", …) without a redeployment.
--
-- Model:
--   - A currency's identity is its `code` (XP, POINTS, EUR, GEMS…). Every
--     ledger entry and every balance now carries one.
--   - `kind` is the FAMILY it belongs to (1 experience · 2 game · 3 money),
--     denormalised onto `ledger_entry.type` / `user_balance.type` so listings
--     can filter by family without joining the catalogue.
--   - `scale` is the number of decimals the currency is *used* with (0 for XP,
--     2 for EUR). Storage stays DECIMAL(20,4) everywhere; `scale` drives input
--     validation (no 1.5 XP) and display.
--   - `overdraft_floor` replaces the single global env: each currency decides
--     how far below zero a balance may go (0 = no overdraft).
--   - `is_system` marks the three built-ins: they can be tuned but never
--     renamed nor deleted (application-enforced), because code references them.
--
-- Labels live in `ledger_currency_translation` (same pattern as
-- `title_translation` / `topic_translation`): admin-created currencies need
-- per-locale labels that lang files — shipped at deploy time — cannot provide.
--
-- SAFE NOW: `ledger_entry` and `user_balance` are still empty (the ledger has
-- not been fed yet), so widening the column and re-keying the balance table are
-- free. Doing this later would mean rewriting a partitioned, high-volume table.

CREATE TABLE `ledger_currency` (
    `code`            VARCHAR(12)      NOT NULL,               -- identity: XP, EUR, GEMS…
    `kind`            TINYINT UNSIGNED NOT NULL,               -- 1 experience · 2 game · 3 money
    `scale`           TINYINT UNSIGNED NOT NULL DEFAULT 0,     -- decimals in use (<= 4)
    `symbol`          VARCHAR(8)       NULL,                   -- €, $, ✦ … (display only)
    `is_active`       TINYINT(1)       NOT NULL DEFAULT 1,     -- inactive = no new entries
    `is_system`       TINYINT(1)       NOT NULL DEFAULT 0,     -- built-in: never delete/rename
    `overdraft_floor` DECIMAL(20,4)    NOT NULL DEFAULT 0,     -- lowest a balance may reach
    `created_at`      DATETIME(3)      NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at`      DATETIME(3)      NULL ON UPDATE CURRENT_TIMESTAMP(3),
    PRIMARY KEY (`code`),
    KEY `idx_lc_active_kind` (`is_active`, `kind`),
    CONSTRAINT `chk_lc_scale` CHECK (`scale` <= 4)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Per-locale display label of a currency ("Expérience" / "Experience").
CREATE TABLE `ledger_currency_translation` (
    `currency_code` VARCHAR(12) NOT NULL,
    `locale`        VARCHAR(10) NOT NULL,
    `label`         VARCHAR(80) NOT NULL,
    PRIMARY KEY (`currency_code`, `locale`),
    KEY `idx_lct_locale` (`locale`, `currency_code`),
    CONSTRAINT `fk_lct_currency` FOREIGN KEY (`currency_code`)
        REFERENCES `ledger_currency`(`code`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Built-in currencies. `is_system = 1` → the admin API refuses to delete or
-- rename them (the application code references XP explicitly).
INSERT INTO `ledger_currency` (`code`, `kind`, `scale`, `symbol`, `is_active`, `is_system`, `overdraft_floor`) VALUES
    ('XP',     1, 0, NULL, 1, 1, 0),
    ('POINTS', 2, 0, NULL, 1, 1, 0),
    ('EUR',    3, 2, '€',  1, 1, 0);

INSERT INTO `ledger_currency_translation` (`currency_code`, `locale`, `label`) VALUES
    ('XP',     'fr-FR', 'Expérience'),
    ('XP',     'en-US', 'Experience'),
    ('POINTS', 'fr-FR', 'Points'),
    ('POINTS', 'en-US', 'Points'),
    ('EUR',    'fr-FR', 'Euro'),
    ('EUR',    'en-US', 'Euro');

-- Widen the currency columns to hold catalogue codes (was CHAR(3), ISO-only)
-- and make them mandatory: every entry/balance now names its currency, which
-- retires the old "NULL / empty string means XP or points" convention.
ALTER TABLE `ledger_entry`
    MODIFY COLUMN `currency` VARCHAR(12) NOT NULL;

-- `user_balance` is re-keyed on (user_id, currency): the code alone identifies
-- the balance, `type` stays as a denormalised family filter.
ALTER TABLE `user_balance`
    MODIFY COLUMN `currency` VARCHAR(12) NOT NULL,
    DROP PRIMARY KEY,
    ADD PRIMARY KEY (`user_id`, `currency`),
    ADD KEY `idx_ub_type` (`type`);
