-- Phase 5 additive migration: browser/device authorization sync sessions.
-- Applies after the accepted Phase 4 granular purchase sync baseline.
-- Additive only: no DROP, no destructive ALTER, no data deletion.

CREATE TABLE IF NOT EXISTS vectra_sync_sessions (
    sync_session_id VARCHAR(96) NOT NULL PRIMARY KEY,
    device_code_hash CHAR(64) NOT NULL,
    user_code_hash CHAR(64) NOT NULL,
    user_code_display VARCHAR(32) NOT NULL,

    product VARCHAR(64) NOT NULL,
    machine_fingerprint_hash CHAR(64) NOT NULL,
    machine_fingerprint_json TEXT NULL,
    client_version VARCHAR(64) NOT NULL,
    platform VARCHAR(64) NOT NULL,
    os_version VARCHAR(128) NOT NULL,

    status ENUM('pending','approved','completed','expired','denied') NOT NULL DEFAULT 'pending',
    wp_user_id BIGINT UNSIGNED NULL,

    approval_attempt_count INT UNSIGNED NOT NULL DEFAULT 0,
    last_approval_attempt_at DATETIME NULL,
    last_approval_ip_hash CHAR(64) NULL,
    started_ip_hash CHAR(64) NULL,
    last_poll_ip_hash CHAR(64) NULL,
    approved_by_issuer VARCHAR(128) NULL,
    approval_nonce_hash CHAR(64) NULL,
    approval_request_id VARCHAR(96) NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    approved_at DATETIME NULL,
    completed_at DATETIME NULL,
    license_issued_at DATETIME NULL,

    last_polled_at DATETIME NULL,
    poll_count INT UNSIGNED NOT NULL DEFAULT 0,
    last_error VARCHAR(128) NULL,

    license_fingerprint_hash CHAR(64) NULL,

    INDEX idx_user_code_hash (user_code_hash),
    INDEX idx_device_code_hash (device_code_hash),
    INDEX idx_status_expires (status, expires_at),
    INDEX idx_wp_user_id (wp_user_id),
    UNIQUE KEY uniq_approval_nonce_hash (approval_nonce_hash),
    INDEX idx_approval_nonce_hash (approval_nonce_hash),
    INDEX idx_license_fingerprint_hash (license_fingerprint_hash),
    INDEX idx_start_rate (started_ip_hash, created_at),
    INDEX idx_machine_rate (machine_fingerprint_hash, created_at),
    INDEX idx_approval_rate_ip (last_approval_ip_hash, last_approval_attempt_at),
    INDEX idx_poll_rate (sync_session_id, last_polled_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Active user-code uniqueness is enforced transactionally in lib/vectra_sync_session.php
-- because retained expired/completed/denied rows may legitimately share a future
-- randomly generated user_code_hash after cleanup windows.