-- Back-office staff foundation.
--
-- Lives in `hxa_bo` (back-office), alongside `report` and the media side-data.
-- This migration turns the minimal legacy `staff` table (id/username/password/
-- email/name/firstname/status) into the basis of an authenticated, role-aware
-- back-office API and adds the two tables it needs:
--   - `staff_token`       — bearer sessions (one row per active login)
--   - `staff_action_log`  — append-only audit of every staff action
--
-- Roles (column `staff.role`, TINYINT UNSIGNED) — ordered by privilege so a
-- numeric `>=` check expresses "at least this level":
--   1 = consultant  — read-only access to certain stats
--   2 = moderator   — limited write access (moderation)
--   3 = admin       — absolute access, incl. staff management
-- New accounts default to 1 (least privilege). Pre-existing rows predate the
-- role system and are the founders, so they are promoted to admin below — this
-- guarantees at least one operator able to manage the others.

ALTER TABLE `staff`
    ADD COLUMN `role`       TINYINT UNSIGNED NOT NULL DEFAULT 1                                          AFTER `status`,
    ADD COLUMN `created_at` DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP                          AFTER `role`,
    ADD COLUMN `updated_at` DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`,
    ADD UNIQUE KEY `uniq_username` (`username`);

-- Promote the founding account(s) so the new surface is reachable from day one.
UPDATE `staff` SET `role` = 3;

-- Bearer sessions. Mirrors `hydrogen.user_session`: only the SHA-256 hash of
-- the token is stored (never the plaintext). `id` is a UUID (BINARY(16)) so an
-- operator can revoke a single session by id without exposing the secret.
CREATE TABLE `staff_token` (
    `id`           BINARY(16)    NOT NULL,
    `staff_id`     INT UNSIGNED  NOT NULL,
    `token_hash`   BINARY(32)    NOT NULL,
    `created_at`   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_used_at` DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expires_at`   DATETIME      NOT NULL,
    `user_agent`   VARCHAR(255)  NULL,
    `ip_address`   VARBINARY(16) NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_token_hash` (`token_hash`),
    KEY `idx_staff_id`   (`staff_id`),
    KEY `idx_expires_at` (`expires_at`),
    CONSTRAINT `fk_staff_token_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Append-only audit log of staff actions. Distinct from the legacy `staff_log`
-- (a state-diff of the staff record itself); this records WHO did WHAT.
--
-- No FK on `staff_id` on purpose: the log must survive a hard delete of the
-- staff account (we still want to know what a now-removed operator did). The
-- actor's `username` is denormalised for the same reason.
--
--   staff_id NULL  → unauthenticated event (e.g. a failed login attempt)
--   action         → logical label, e.g. "staff.login", "staff.create"
--   metadata       → optional JSON context (target id, changed fields, …)
CREATE TABLE `staff_action_log` (
    `id`         BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
    `staff_id`   INT UNSIGNED      NULL,
    `username`   VARCHAR(20)       NULL,
    `action`     VARCHAR(64)       NOT NULL,
    `method`     VARCHAR(10)       NOT NULL,
    `path`       VARCHAR(255)      NOT NULL,
    `status`     SMALLINT UNSIGNED NOT NULL,
    `metadata`   JSON              NULL,
    `ip_address` VARBINARY(16)     NULL,
    `user_agent` VARCHAR(255)      NULL,
    `created_at` DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_staff`      (`staff_id`, `created_at`),
    KEY `idx_action`     (`action`, `created_at`),
    KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
