-- Phase A10  -  Mobile API
-- Bearer tokens for the upcoming Android (and later iOS) app, plus device
-- registrations for FCM/APNs push notifications.

CREATE TABLE IF NOT EXISTS mobile_tokens (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id     BIGINT UNSIGNED NOT NULL,
    token_hash  CHAR(64)     NOT NULL,
    label       VARCHAR(80)  NULL,
    last_seen_at DATETIME    NULL,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    revoked_at  DATETIME     NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_token (token_hash),
    KEY idx_user (user_id),
    CONSTRAINT fk_mt_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mobile_devices (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id     BIGINT UNSIGNED NOT NULL,
    platform    ENUM('android','ios','web') NOT NULL,
    device_token VARCHAR(255) NOT NULL,
    device_label VARCHAR(120) NULL,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_device (platform, device_token),
    KEY idx_user (user_id),
    CONSTRAINT fk_md_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
