-- Migration: Add context and summary fields to memories table
-- This migration enhances the memory storage architecture by adding:
-- 1. context field for contextual information about stored content
-- 2. summary field for AI-generated summaries (≤120 words)
-- 3. Migration of metadata.tags to proper tags field
-- 4. Removal of redundant metadata field

-- Step 1: Add new columns
ALTER TABLE memories 
ADD COLUMN context TEXT,
ADD COLUMN summary TEXT;

-- Step 2: Create index on summary for potential search use
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_memories_summary 
ON memories USING gin(to_tsvector('english', summary));

-- Step 3: Migrate metadata.tags to proper tags field
-- Update records where metadata contains tags but tags field is empty
UPDATE memories 
SET tags = ARRAY(
    SELECT jsonb_array_elements_text(metadata->'tags')
)
WHERE metadata ? 'tags' 
  AND (tags IS NULL OR array_length(tags, 1) IS NULL);

-- Step 4: Verify migration - this should return 0 if migration was successful
-- SELECT COUNT(*) FROM memories 
-- WHERE metadata ? 'tags' 
--   AND (tags IS NULL OR array_length(tags, 1) IS NULL);

-- Step 5: Drop the metadata column
ALTER TABLE memories DROP COLUMN metadata;

-- Add comment to document the schema change
COMMENT ON COLUMN memories.context IS 'Contextual information about what is being stored';
COMMENT ON COLUMN memories.summary IS 'AI-generated summary of the content (max 120 words)';
COMMENT ON TABLE memories IS 'Enhanced memory storage with context, summary, and proper tag management';