-- ==============================================================================
-- UCall Database Schema (MySQL / MariaDB Compatible)
-- Migration 0004: WhatsApp-Style E2EE Keys, Device Identity & Cursor-Based Sync
-- ==============================================================================

-- 1. Device Identity & Signed PreKeys Registry
CREATE TABLE IF NOT EXISTS e2ee_device_keys (
    device_id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    identity_key VARCHAR(128) NOT NULL,
    signed_prekey_id INT NOT NULL,
    signed_prekey VARCHAR(128) NOT NULL,
    signed_prekey_sig VARCHAR(256) NOT NULL,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    KEY idx_e2ee_user (user_id),
    CONSTRAINT fk_e2ee_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_e2ee_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. One-Time Ephemeral PreKeys Pool (Consumed Atomically per X3DH Handshake)
CREATE TABLE IF NOT EXISTS e2ee_one_time_prekeys (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(36) NOT NULL,
    key_id INT NOT NULL,
    public_key VARCHAR(128) NOT NULL,
    is_consumed TINYINT(1) NOT NULL DEFAULT 0,
    consumed_at DATETIME(6) DEFAULT NULL,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    UNIQUE KEY uq_device_opk (device_id, key_id),
    KEY idx_opk_lookup (device_id, is_consumed),
    CONSTRAINT fk_opk_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;