-- Nominative audit of staff actions performed ON a user account.
--
-- Lives in `hxa_bo` (back-office), alongside `staff_action_log` and `report`.
-- Distinct from the two existing journals on purpose:
--   - `admin_audit` (SQLite)  → anonymous (static token fingerprint), request-
--     oriented (method/path/status). Cannot answer "who".
--   - `staff_action_log`      → nominative but request-oriented and scoped to
--     `/admin/staff/*`. Not queryable "by target user".
--
-- This table is TARGET-oriented: indexed on `target_user_id` so the whole
-- moderation history of one account is a single keyset scan. It records the
-- semantic action (`user.ban`, `user.profile.update`, …), the effective field
-- diff (`changes`), and the acting operator — nominative when a staff bearer
-- token was used, NULL ("system"/Talend) when the static ADMIN_API_TOKEN was.
--
-- No FK on `staff_id` on purpose: the trail must survive a hard delete of the
-- operator account. The actor's `username` is denormalised for the same reason.
--
--   target_user_id → `hxa.user.id` (BINARY(16)); no cross-schema FK.
--   staff_id NULL  → action performed with the static admin token (system).
--   action         → logical label, e.g. "user.ban", "user.verified.set".
--   changes        → JSON field diff, e.g. {"isVerified":{"from":false,"to":true}}.
--   reason         → optional free-text moderation note (from the request body).
CREATE TABLE `user_action_log` (
    `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `target_user_id` BINARY(16)      NOT NULL,
    `staff_id`       INT UNSIGNED    NULL,
    `staff_username` VARCHAR(20)     NULL,
    `action`         VARCHAR(64)     NOT NULL,
    `changes`        JSON            NULL,
    `reason`         VARCHAR(500)    NULL,
    `ip_address`     VARBINARY(16)   NULL,
    `created_at`     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    -- Keyset history for one account: WHERE target_user_id = ? ORDER BY id DESC.
    KEY `idx_target` (`target_user_id`, `id`),
    KEY `idx_staff`  (`staff_id`, `created_at`),
    KEY `idx_action` (`action`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
