-- Storage Node Schema (Per Community/Node)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE IF NOT EXISTS encrypted_logs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    log_type TEXT NOT NULL, -- 'chat', 'kill', 'admin', 'ban'
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    -- E2EE Blob
    encrypted_payload BYTEA NOT NULL,
    
    -- Searchable Metadata (Blind Indexing)
    -- HMAC-SHA256 hashes of identifiers (e.g., SteamID, PlayerName)
    blind_index_hash TEXT, 
    
    -- Plaintext Metadata (Non-sensitive)
    server_id TEXT NOT NULL,
    session_id TEXT
);

CREATE INDEX idx_logs_created_at ON encrypted_logs(created_at);
CREATE INDEX idx_logs_blind_hash ON encrypted_logs(blind_index_hash);

CREATE TABLE IF NOT EXISTS players (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    -- Blind Index Hash of the SteamID/GUID for searching
    identity_hash TEXT UNIQUE NOT NULL, 
    
    -- E2EE encrypted player profile (names, notes, etc.)
    encrypted_profile BYTEA NOT NULL,
    
    last_seen TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_players_identity_hash ON players(identity_hash);