-- 000051_add_article_embedding_vector.up.sql
-- Add pgvector vector column to article_embeddings for semantic search.
-- The original embedding JSONB column is kept for backward compatibility;
-- the new vector_column stores the same data as a native pgvector type
-- enabling the <=> (cosine distance) operator for efficient similarity search.

-- Ensure pgvector extension is available (should already be installed).
CREATE EXTENSION IF NOT EXISTS vector;

-- Add a vector(1536) column to article_embeddings
ALTER TABLE article_embeddings ADD COLUMN IF NOT EXISTS vector_embedding vector(1536);

-- Create an index for efficient cosine similarity search
CREATE INDEX IF NOT EXISTS idx_article_embeddings_vector
  ON article_embeddings USING ivfflat (vector_embedding vector_cosine_ops)
  WITH (lists = 100);

-- Migrate existing JSONB embeddings to the vector column
-- This parses the JSON array into a pgvector-compatible format
UPDATE article_embeddings
SET vector_embedding = (
  SELECT array_agg(elem::real)::vector(1536)
  FROM jsonb_array_elements_text(embedding) AS elem
)
WHERE vector_embedding IS NULL AND embedding IS NOT NULL;