-- Ragreen - Migration v4: push notifications (Firebase Cloud Messaging)
--
-- One row per device a user has logged into the mobile app on. A user can
-- have several rows (phone + tablet, or after reinstalling without the old
-- token being cleaned up yet) - that's fine, a push is sent to every row
-- for that user and dead tokens get pruned automatically when FCM reports
-- them as invalid (see api/includes/fcm.php).
--
-- Safe to re-run: CREATE TABLE IF NOT EXISTS.

CREATE TABLE IF NOT EXISTS device_tokens (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id     INT UNSIGNED NOT NULL,
    token       VARCHAR(255) NOT NULL,
    platform    ENUM('android', 'ios') NOT NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_used_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_token (token),
    KEY idx_user_id (user_id),
    CONSTRAINT fk_device_tokens_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
