Embeddings

-- Configure OpenAI
ALTER SYSTEM SET entropy_openai_api_key = 'sk-...';

-- Generate embedding for text
SELECT entropy_embedding_openai(
  'text-embedding-3-large',
  'entropyDB is a multi-model database'
) as embedding;

-- Store embeddings in table
CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  title TEXT,
  content TEXT,
  embedding VECTOR(3072)  -- text-embedding-3-large dimensions
);

-- Generate and store
INSERT INTO documents (title, content, embedding)
VALUES (
  'Getting Started',
  'entropyDB supports SQL, documents, graphs, key-value, and time-series...',
  entropy_embedding_openai('text-embedding-3-large', 'entropyDB supports...')
);

-- Batch generate embeddings
UPDATE documents
SET embedding = entropy_embedding_openai('text-embedding-3-large', content)
WHERE embedding IS NULL;

-- Use cheaper model for smaller vectors
SELECT entropy_embedding_openai(
  'text-embedding-ada-002',  -- 1536 dimensions
  'Sample text'
) as embedding;

-- Configure Cohere
ALTER SYSTEM SET entropy_cohere_api_key = 'your-api-key';

-- Generate embeddings
SELECT entropy_embedding_cohere(
  'embed-english-v3.0',
  'entropyDB is a multi-model database',
  input_type => 'search_document'
) as embedding;

-- Multilingual support
SELECT entropy_embedding_cohere(
  'embed-multilingual-v3.0',
  'Base de datos multimodelo',
  input_type => 'search_document'
) as embedding;

-- Different input types
SELECT entropy_embedding_cohere(
  'embed-english-v3.0',
  'What is entropyDB?',
  input_type => 'search_query'  -- For queries
) as query_embedding;

-- Batch processing
SELECT 
  id,
  content,
  entropy_embedding_cohere('embed-english-v3.0', content, 'search_document') as embedding
FROM documents
WHERE embedding IS NULL
LIMIT 1000;

-- Deploy local embedding model
SELECT entropy_deploy_local_model(
  model_name => 'sentence-transformers/all-MiniLM-L6-v2',
  model_type => 'sentence-transformer',
  device => 'cuda:0'  -- or 'cpu'
);

-- Generate embeddings locally (no API costs)
SELECT entropy_embedding_local(
  'all-MiniLM-L6-v2',
  'entropyDB is a multi-model database'
) as embedding;

-- Load ONNX model for faster inference
SELECT entropy_deploy_onnx_model(
  model_path => '/models/bge-base-en-v1.5.onnx',
  model_name => 'bge-base'
);

-- Use ONNX model
SELECT entropy_embedding_onnx(
  'bge-base',
  'Sample text'
) as embedding;

-- Hugging Face models
SELECT entropy_embedding_huggingface(
  'sentence-transformers/paraphrase-multilingual-MiniLM-L12-v2',
  'Text in any language'
) as embedding;

-- Process large batches efficiently
CREATE OR REPLACE FUNCTION generate_embeddings_batch()
RETURNS void AS $$
DECLARE
  batch_size INT := 100;
  processed INT := 0;
BEGIN
  LOOP
    -- Process batch
    WITH to_process AS (
      SELECT id, content
      FROM documents
      WHERE embedding IS NULL
      LIMIT batch_size
    )
    UPDATE documents d
    SET embedding = entropy_embedding_openai('text-embedding-3-large', tp.content),
        updated_at = NOW()
    FROM to_process tp
    WHERE d.id = tp.id;
    
    GET DIAGNOSTICS processed = ROW_COUNT;
    EXIT WHEN processed = 0;
    
    -- Log progress
    RAISE NOTICE 'Processed % documents', processed;
    
    -- Rate limiting (avoid API limits)
    PERFORM pg_sleep(1);
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Run batch job
SELECT generate_embeddings_batch();

-- Schedule periodic embedding generation
SELECT cron.schedule(
  'generate-embeddings',
  '*/15 * * * *',  -- Every 15 minutes
  'SELECT generate_embeddings_batch()'
);

-- Create embedding cache
CREATE TABLE embedding_cache (
  text_hash TEXT PRIMARY KEY,
  model_name TEXT,
  embedding VECTOR(3072),
  created_at TIMESTAMP DEFAULT NOW()
);

-- Function with caching
CREATE OR REPLACE FUNCTION get_cached_embedding(
  p_text TEXT,
  p_model TEXT DEFAULT 'text-embedding-3-large'
) RETURNS VECTOR AS $$
DECLARE
  v_hash TEXT;
  v_embedding VECTOR;
BEGIN
  -- Compute hash
  v_hash := encode(digest(p_text || p_model, 'sha256'), 'hex');
  
  -- Check cache
  SELECT embedding INTO v_embedding
  FROM embedding_cache
  WHERE text_hash = v_hash AND model_name = p_model;
  
  IF FOUND THEN
    RETURN v_embedding;
  END IF;
  
  -- Generate new embedding
  v_embedding := entropy_embedding_openai(p_model, p_text);
  
  -- Cache it
  INSERT INTO embedding_cache (text_hash, model_name, embedding)
  VALUES (v_hash, p_model, v_embedding)
  ON CONFLICT (text_hash) DO NOTHING;
  
  RETURN v_embedding;
END;
$$ LANGUAGE plpgsql;

-- Use cached embedding
SELECT get_cached_embedding('Sample text') as embedding;

-- Cache statistics
SELECT 
  model_name,
  COUNT(*) as cached_embeddings,
  pg_size_pretty(pg_total_relation_size('embedding_cache')) as cache_size
FROM embedding_cache
GROUP BY model_name;

-- Find similar documents
WITH query_embedding AS (
  SELECT entropy_embedding_openai(
    'text-embedding-3-large',
    'How do I query graphs in entropyDB?'
  ) as embedding
)
SELECT 
  d.title,
  d.content,
  1 - (d.embedding <=> qe.embedding) as similarity
FROM documents d
CROSS JOIN query_embedding qe
ORDER BY d.embedding <=> qe.embedding
LIMIT 10;

-- Hybrid search (semantic + keyword)
WITH query_embedding AS (
  SELECT entropy_embedding_openai('text-embedding-3-large', 'graph queries') as emb
)
SELECT 
  d.title,
  -- Combine semantic and text search scores
  (1 - (d.embedding <=> qe.emb)) * 0.7 +
  ts_rank(to_tsvector('english', d.content), plainto_tsquery('english', 'graph queries')) * 0.3
  as combined_score
FROM documents d
CROSS JOIN query_embedding qe
WHERE to_tsvector('english', d.content) @@ plainto_tsquery('english', 'graph queries')
   OR (d.embedding <=> qe.emb) < 0.5
ORDER BY combined_score DESC
LIMIT 10;

-- Filtered semantic search
SELECT 
  d.title,
  1 - (d.embedding <=> qe.embedding) as similarity
FROM documents d
CROSS JOIN (
  SELECT entropy_embedding_openai('text-embedding-3-large', 'transactions') as embedding
) qe
WHERE d.category = 'technical'
  AND d.published_date > '2024-01-01'
ORDER BY d.embedding <=> qe.embedding
LIMIT 10;