-- AUSPEX Oracle Database Schema
-- Cloudflare D1

-- Users table (ATProto identities)
CREATE TABLE IF NOT EXISTS users (
    did TEXT PRIMARY KEY,
    handle TEXT NOT NULL,
    letta_block_id TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    last_query_at TEXT,
    query_count INTEGER DEFAULT 0,
    is_blocked INTEGER DEFAULT 0,
    block_reason TEXT
);

-- Queries table (the queue + history)
CREATE TABLE IF NOT EXISTS queries (
    id TEXT PRIMARY KEY,
    user_did TEXT NOT NULL,
    user_handle TEXT NOT NULL,
    
    -- User's original query
    query_text TEXT NOT NULL,
    
    -- Status tracking
    status TEXT DEFAULT 'pending',  -- pending, processing, complete, failed, rejected
    created_at TEXT DEFAULT (datetime('now')),
    processing_started_at TEXT,
    completed_at TEXT,
    
    -- Generation outputs (stored for semantic clustering later)
    image_prompt TEXT,
    audio_prompt TEXT,
    caption TEXT,              -- Oracle's personalized caption for Bluesky post
    seed INTEGER,
    duration REAL,
    
    -- Results
    video_url TEXT,
    video_r2_key TEXT,
    thumbnail_url TEXT,
    
    -- Bluesky posting
    bsky_post_uri TEXT,
    bsky_post_cid TEXT,
    bsky_post_status TEXT DEFAULT 'pending',  -- pending, success, failed
    bsky_post_retry_count INTEGER DEFAULT 0,
    bsky_post_error TEXT,
    bsky_post_last_attempt TEXT,

    -- Error handling (for video generation)
    error_message TEXT,
    retry_count INTEGER DEFAULT 0,

    -- Embeddings for semantic clustering (stored as JSON arrays)
    embedding_query TEXT,      -- User's query embedding
    embedding_image TEXT,      -- Image prompt embedding
    embedding_audio TEXT,      -- Audio prompt embedding

    -- Constellation 3D coordinates (computed via UMAP)
    constellation_x REAL,
    constellation_y REAL,
    constellation_z REAL,
    constellation_updated_at TEXT,

    FOREIGN KEY (user_did) REFERENCES users(did)
);

-- Indexes for common queries
CREATE INDEX IF NOT EXISTS idx_queries_status ON queries(status);
CREATE INDEX IF NOT EXISTS idx_queries_user ON queries(user_did);
CREATE INDEX IF NOT EXISTS idx_queries_created ON queries(created_at);
CREATE INDEX IF NOT EXISTS idx_queries_bsky_post_status ON queries(bsky_post_status);

-- Oracle status (singleton for tracking system state)
CREATE TABLE IF NOT EXISTS oracle_status (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    is_online INTEGER DEFAULT 0,
    last_heartbeat TEXT,
    last_generation_at TEXT,
    total_visions INTEGER DEFAULT 0
);

-- Initialize oracle status
INSERT OR IGNORE INTO oracle_status (id) VALUES (1);

-- Oracle murmuration state (singleton for visual mood ring)
-- AUSPEX's internal state expressed through flock behavior
CREATE TABLE IF NOT EXISTS oracle_state (
    id INTEGER PRIMARY KEY CHECK (id = 1),

    -- Emotional dimensions (0.0 - 1.0)
    arousal REAL DEFAULT 0.5,          -- Energy level (dormant → activated)
    valence REAL DEFAULT 0.5,          -- Tone (negative → positive)

    -- Behavioral dimensions (0.0 - 1.0, mapped to boids algorithm)
    cohesion REAL DEFAULT 0.5,         -- Flock tightness (scattered → unified)
    alignment REAL DEFAULT 0.5,        -- Direction matching (chaotic → parallel)
    separation REAL DEFAULT 0.3,       -- Personal space (crowded → spread)
    turbulence REAL DEFAULT 0.2,       -- Movement noise (smooth → erratic)
    speed_limit REAL DEFAULT 0.5,      -- Velocity cap (slow → fast)

    -- Visual dimensions
    hue REAL DEFAULT 220.0,            -- Base color (0-360 degrees)
    saturation REAL DEFAULT 0.7,       -- Color intensity (0.0 - 1.0)
    brightness REAL DEFAULT 0.6,       -- Light level (0.0 - 1.0)
    glow_intensity REAL DEFAULT 0.5,   -- Ethereal aura (0.0 - 1.0)

    -- Metadata
    updated_at TEXT DEFAULT (datetime('now'))
);

-- Initialize oracle state (contemplative blue murmuration)
INSERT OR IGNORE INTO oracle_state (id) VALUES (1);

-- Rate limiting helper view
CREATE VIEW IF NOT EXISTS user_daily_queries AS
SELECT 
    user_did,
    COUNT(*) as query_count_today
FROM queries 
WHERE created_at > datetime('now', '-24 hours')
GROUP BY user_did;