-- Migration 011: Memories/Learnings table with semantic search
-- Purpose: Migrate existing session_memories to memories table with enhanced schema
-- Dependencies: 001_base_schema.sql (pgvector), 010_sessions.sql (sessions table)

-- Rename existing session_memories table to memories
ALTER TABLE IF EXISTS session_memories RENAME TO memories;

-- Add missing columns
ALTER TABLE memories
    ADD COLUMN IF NOT EXISTS task_id TEXT REFERENCES tasks(id) ON DELETE SET NULL;

-- Rename source_session to session_id for consistency
ALTER TABLE memories
    RENAME COLUMN source_session TO session_id;

-- Add foreign key constraint to sessions table
ALTER TABLE memories
    ADD CONSTRAINT fk_memories_session
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE SET NULL;

-- Rename times_surfaced to access_count for consistency
ALTER TABLE memories
    RENAME COLUMN times_surfaced TO access_count;

-- Rename last_surfaced to last_accessed_at for consistency
ALTER TABLE memories
    RENAME COLUMN last_surfaced TO last_accessed_at;

-- Change varchar columns to TEXT for consistency
ALTER TABLE memories
    ALTER COLUMN project TYPE TEXT;

-- Note: Keeping embedding as vector(1536) to preserve existing data
-- Future embeddings can use vector(1024) by updating the column if needed

-- Add missing indexes
CREATE INDEX IF NOT EXISTS idx_memories_session ON memories(session_id);
CREATE INDEX IF NOT EXISTS idx_memories_task ON memories(task_id);
CREATE INDEX IF NOT EXISTS idx_memories_project ON memories(project);
CREATE INDEX IF NOT EXISTS idx_memories_category ON memories(category);
CREATE INDEX IF NOT EXISTS idx_memories_created ON memories(created_at DESC);

-- Drop old index and create HNSW index for better performance
-- Note: Existing index is ivfflat, we want hnsw
DROP INDEX IF EXISTS idx_session_memories_embedding;
CREATE INDEX IF NOT EXISTS idx_memories_embedding ON memories USING hnsw (embedding vector_cosine_ops);

-- Full-text search on title and content (for keyword search)
CREATE INDEX IF NOT EXISTS idx_memories_fts ON memories USING gin(
    to_tsvector('english', title || ' ' || content)
);

-- Comments for documentation
COMMENT ON TABLE memories IS 'Learnings and patterns discovered during development sessions';
COMMENT ON COLUMN memories.category IS 'Type of memory: pattern (reusable solution), fix (bug resolution), preference (user choice), gotcha (trap/pitfall), architecture (design decision)';
COMMENT ON COLUMN memories.embedding IS 'Vector embedding of title + content for semantic search';
COMMENT ON COLUMN memories.context IS 'Optional context describing when/where this memory applies';
COMMENT ON COLUMN memories.access_count IS 'Number of times this memory has been retrieved (for relevance ranking)';