-- Migration: Add context_fingerprint for encoding specificity principle
-- Date: 2025-08-31
-- Description: Add context-aware fingerprinting to preserve semantic context variations
--              Implements Tulving & Thomson (1973) encoding specificity principle

-- Add context_fingerprint column for semantic deduplication
ALTER TABLE memories ADD COLUMN IF NOT EXISTS context_fingerprint VARCHAR(64);

-- Create index on context_fingerprint for efficient lookups
CREATE INDEX IF NOT EXISTS idx_memories_context_fingerprint ON memories(context_fingerprint);

-- Remove unique constraint on content_hash to allow context variations
-- This is critical - same content in different contexts should be allowed
DROP INDEX IF EXISTS memories_content_hash_key;
ALTER TABLE memories DROP CONSTRAINT IF EXISTS memories_content_hash_key;

-- Add composite unique constraint on content_hash + context_fingerprint
-- This allows same content with different contexts while preventing exact duplicates
ALTER TABLE memories ADD CONSTRAINT memories_context_dedup_key 
    UNIQUE (content_hash, context_fingerprint);

-- Create index on content_hash for content-based queries
CREATE INDEX IF NOT EXISTS idx_memories_content_hash ON memories(content_hash);

-- Update existing rows with context fingerprints (for backward compatibility)
UPDATE memories 
SET context_fingerprint = encode(
    digest(
        content || '::CONTEXT::' || 
        COALESCE(context, '') || '::SUMMARY::' || 
        COALESCE(summary, '') || '::TAGS::' || 
        COALESCE(array_to_string(tags, ','), ''), 
        'sha256'
    ), 
    'hex'
)
WHERE context_fingerprint IS NULL;

-- Make context_fingerprint NOT NULL after updating existing data
ALTER TABLE memories ALTER COLUMN context_fingerprint SET NOT NULL;