-- =============================================================================
-- Migration: application-layer encrypted channel (one key per phone)
-- Date: 2026-10-20
-- Safe to run several times (IF NOT EXISTS).
-- =============================================================================

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `device_keys` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `device_id` CHAR(32) NOT NULL UNIQUE,
    `public_key` VARCHAR(64) NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_seen_at` DATETIME NULL,
    `revoked_at` DATETIME NULL,
    INDEX `idx_device_last_seen` (`last_seen_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;