-- @database: main
--
-- Ledger system — immutable, append-only journal of EVERY value movement a
-- user account experiences (XP, points, and real money), plus the
-- denormalised current balances read on the hot path.
--
-- Design decisions (agreed with the user):
--   - Identity = UUIDv7 (BINARY(16)), generated app-side. Time-ordered so it
--     preserves InnoDB insert locality (unlike uuid4) AND is globally unique
--     with no central allocator — the table is shard-ready by `user_id`
--     without offset juggling. Consistent with the rest of the schema
--     (everything is UUID BINARY(16)).
--   - Amount = DECIMAL(20,4) **signed** (+ credit / − debit). NEVER float:
--     money must be exact. XP/points ride the same column (currency NULL).
--   - `currency` = ISO 4217 for money, NULL for XP/points.
--   - Full context of each entry is traceable: `event_code` (WHY, resolved
--     against the app-side LedgerEventCode registry), `reference_type` +
--     `reference_id` (on WHICH object — polymorphic, deliberately WITHOUT a
--     FK so the journal survives deletion of the source object), `emitter_id`
--     (WHO caused it). `context_label` is an optional frozen snapshot for
--     money events (so "paid 5€ for coupon Z" stays true if Z is later
--     removed).
--   - Reversals are append-only: an annulment is a NEW inverse row pointing at
--     the original via `reverses_id`; the original is never UPDATEd (only its
--     `status` flips to 1). No row is ever edited or deleted.
--   - Balances live in `user_balance` (one row per user × type × currency),
--     updated in the SAME transaction as the entry insert. The ledger is
--     never SUM()med on the hot path.
--
-- Volume strategy:
--   - RANGE partitioning by month on `created_at` → archival by DROP PARTITION
--     (instant) instead of a massive DELETE; scans stay bounded to the hot
--     partitions. NB: MySQL requires every unique key to contain the partition
--     column, hence PRIMARY KEY (id, created_at). Idempotency (dedup of
--     replayed awards) is therefore enforced APPLICATIVELY (lookup under the
--     balance lock), not by a global UNIQUE constraint.
--   - ROW_FORMAT=COMPRESSED — rows are highly repetitive, ~2× less disk.
--   - Single business index (user_id, id): the only hot read is "one user's
--     history, newest first" (keyset). Global aggregates go through the daily
--     rollup, never a cross-partition/cross-shard scan.
--
-- NOTE: this migration only CREATES the new tables. The legacy
-- `user_transaction` table (currently written by ExperienceService) is left
-- untouched here; it is renamed to `_legacy` and seeded with opening balances
-- in a LATER migration, once the application code has switched over — so we
-- never rename a table the running code still writes to.

CREATE TABLE `ledger_entry` (
    `id`             BINARY(16)        NOT NULL,                       -- UUIDv7
    `created_at`     DATETIME(3)       NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `user_id`        BINARY(16)        NOT NULL,                       -- beneficiary (logical shard key)
    `emitter_id`     BINARY(16)        NULL,                           -- who caused it (NULL = system/self)
    `type`           TINYINT UNSIGNED  NOT NULL,                       -- 1 Experience · 2 Points · 3 Money
    `event_code`     SMALLINT UNSIGNED NOT NULL,                       -- LedgerEventCode registry
    `amount`         DECIMAL(20,4)     NOT NULL,                       -- SIGNED (+ credit / − debit)
    `currency`       CHAR(3)           NULL,                           -- ISO 4217 (money) / NULL (XP,points)
    `reference_type` TINYINT UNSIGNED  NULL,                           -- source object class (no FK)
    `reference_id`   BINARY(16)        NULL,                           -- source object id (no FK)
    `context_label`  VARCHAR(120)      NULL,                           -- frozen snapshot (money events)
    `reverses_id`    BINARY(16)        NULL,                           -- annulment → original entry id
    `status`         TINYINT UNSIGNED  NOT NULL DEFAULT 0,             -- 0 posted · 1 reversed
    PRIMARY KEY (`id`, `created_at`),
    KEY `idx_user_hist` (`user_id`, `id`),                            -- keyset history per user
    KEY `idx_reverses`  (`reverses_id`),                              -- find the annulment of an entry
    CONSTRAINT `chk_amount_nonzero` CHECK (`amount` <> 0)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  ROW_FORMAT=COMPRESSED
PARTITION BY RANGE (TO_DAYS(`created_at`)) (
    PARTITION `p2026_08` VALUES LESS THAN (TO_DAYS('2026-09-01')),
    PARTITION `p2026_09` VALUES LESS THAN (TO_DAYS('2026-10-01')),
    PARTITION `p2026_10` VALUES LESS THAN (TO_DAYS('2026-11-01')),
    PARTITION `p2026_11` VALUES LESS THAN (TO_DAYS('2026-12-01')),
    PARTITION `p2026_12` VALUES LESS THAN (TO_DAYS('2027-01-01')),
    PARTITION `p2027_01` VALUES LESS THAN (TO_DAYS('2027-02-01')),
    PARTITION `p2027_02` VALUES LESS THAN (TO_DAYS('2027-03-01')),
    PARTITION `p2027_03` VALUES LESS THAN (TO_DAYS('2027-04-01')),
    PARTITION `p2027_04` VALUES LESS THAN (TO_DAYS('2027-05-01')),
    PARTITION `p2027_05` VALUES LESS THAN (TO_DAYS('2027-06-01')),
    PARTITION `p2027_06` VALUES LESS THAN (TO_DAYS('2027-07-01')),
    PARTITION `p2027_07` VALUES LESS THAN (TO_DAYS('2027-08-01')),
    PARTITION `p2027_08` VALUES LESS THAN (TO_DAYS('2027-09-01')),
    PARTITION `p2027_09` VALUES LESS THAN (TO_DAYS('2027-10-01')),
    PARTITION `p2027_10` VALUES LESS THAN (TO_DAYS('2027-11-01')),
    PARTITION `p2027_11` VALUES LESS THAN (TO_DAYS('2027-12-01')),
    PARTITION `p2027_12` VALUES LESS THAN (TO_DAYS('2028-01-01')),
    -- Safety net so an insert past the last explicit month never fails (data
    -- loss on a ledger is unacceptable). A monthly maintenance job must keep
    -- carving real months out of it BEFORE rows land here, e.g.:
    --   ALTER TABLE `ledger_entry` REORGANIZE PARTITION `pmax` INTO (
    --     PARTITION `p2028_01` VALUES LESS THAN (TO_DAYS('2028-02-01')),
    --     PARTITION `pmax`     VALUES LESS THAN MAXVALUE);
    PARTITION `pmax` VALUES LESS THAN MAXVALUE
);

-- Denormalised current balance, one row per (user, type, currency). Read O(1)
-- on the hot path; written in the same transaction as the entry that moves it.
-- `currency` is part of the key (a user has a distinct EUR balance, XP
-- balance, …); it is '' (empty) for the currency-less types (XP, points) so
-- it can stay in the PRIMARY KEY without a NULL.
CREATE TABLE `user_balance` (
    `user_id`    BINARY(16)        NOT NULL,
    `type`       TINYINT UNSIGNED  NOT NULL,             -- mirrors ledger_entry.type
    `currency`   CHAR(3)           NOT NULL DEFAULT '',  -- '' for XP/points, ISO 4217 for money
    `balance`    DECIMAL(20,4)     NOT NULL DEFAULT 0,
    `updated_at` DATETIME(3)       NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
                                            ON UPDATE CURRENT_TIMESTAMP(3),
    PRIMARY KEY (`user_id`, `type`, `currency`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
