-- Ragreen - Migration v3: mobile (JWT) auth
--
-- Adds refresh-token storage for the Flutter app. Access tokens are
-- stateless JWTs (never stored - see api/includes/jwt.php); only refresh
-- tokens are persisted, hashed, so a lost/stolen device's session can be
-- revoked without needing to touch every other logged-in device.
--
-- Safe to re-run: CREATE TABLE IF NOT EXISTS.

CREATE TABLE IF NOT EXISTS auth_refresh_tokens (
    id                INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id           INT UNSIGNED NOT NULL,
    token_hash        VARCHAR(255) NOT NULL,
    device_label      VARCHAR(191) NULL,      -- optional, e.g. "iPhone 14" - set by the client if it wants to show a device list later
    expires_at        DATETIME NOT NULL,
    revoked_at        DATETIME NULL,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_used_at      DATETIME NULL,
    PRIMARY KEY (id),
    KEY idx_user_id (user_id),
    KEY idx_token_hash (token_hash),
    CONSTRAINT fk_auth_refresh_tokens_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
