-- Migration 017: bearer tokens for the mobile API door.
-- One row per signed-in device. Only a SHA-256 hash of the token is stored,
-- so a leaked table cannot impersonate a device. Expiry slides forward while
-- a device stays active, and revoked_at backs sign-out (this device) and
-- sign-out-everywhere. identity_type keeps one table serving both the admin
-- and, later, the renter app. MySQL syntax (not MariaDB).

CREATE TABLE device_tokens (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    identity_type VARCHAR(10) NOT NULL,
    identity_id   INT UNSIGNED NOT NULL,
    token_hash    CHAR(64) NOT NULL,
    device_name   VARCHAR(120) NULL,
    last_used_at  DATETIME NULL,
    expires_at    DATETIME NOT NULL,
    revoked_at    DATETIME NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_device_tokens_hash (token_hash),
    KEY idx_device_tokens_identity (identity_type, identity_id),
    KEY idx_device_tokens_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;