-- Migration: Finalize Simple Storage Architecture
-- Date: 2025-01-27
-- Description: Remove unused cognitive tiering fields and finalize simple storage model
--              This resolves the migration 004/005 conflict by committing to simple architecture

-- Drop cognitive tiering columns if they exist (cleanup from any partial migrations)
ALTER TABLE memories 
    DROP COLUMN IF EXISTS tier,
    DROP COLUMN IF EXISTS last_accessed,
    DROP COLUMN IF EXISTS access_count,
    DROP COLUMN IF EXISTS importance_score,
    DROP COLUMN IF EXISTS consolidation_strength;

-- Drop the memory_tier enum type if it exists
DROP TYPE IF EXISTS memory_tier CASCADE;

-- Drop tier-related indexes if they exist
DROP INDEX IF EXISTS idx_memories_tier;
DROP INDEX IF EXISTS idx_memories_last_accessed;
DROP INDEX IF EXISTS idx_memories_tier_accessed;
DROP INDEX IF EXISTS idx_memories_access_count;
DROP INDEX IF EXISTS idx_memories_importance;

-- Drop tier transitions table if it exists

-- Drop cognitive views if they exist
DROP VIEW IF EXISTS memory_tier_stats;
DROP VIEW IF EXISTS working_memory_pressure;

-- Ensure all required columns exist with proper constraints
ALTER TABLE memories 
    ALTER COLUMN content SET NOT NULL,
    ALTER COLUMN content_hash SET NOT NULL,
    ALTER COLUMN context SET NOT NULL,
    ALTER COLUMN summary SET NOT NULL;

-- Add context_fingerprint if it doesn't exist (from migration 005B)
ALTER TABLE memories ADD COLUMN IF NOT EXISTS context_fingerprint VARCHAR(64);

-- Update any NULL context_fingerprints
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 update
ALTER TABLE memories ALTER COLUMN context_fingerprint SET NOT NULL;

-- Recreate clean indexes for simple storage model
CREATE INDEX IF NOT EXISTS idx_memories_content_hash ON memories(content_hash);
CREATE INDEX IF NOT EXISTS idx_memories_context_fingerprint ON memories(context_fingerprint);
CREATE INDEX IF NOT EXISTS idx_memories_created_at ON memories(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_memories_updated_at ON memories(updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_memories_parent_id ON memories(parent_id) WHERE parent_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_memories_tags ON memories USING gin(tags);

-- Add composite unique constraint for context-aware deduplication
-- Drop old constraint if it exists
ALTER TABLE memories DROP CONSTRAINT IF EXISTS memories_context_dedup_key;
ALTER TABLE memories ADD CONSTRAINT memories_context_dedup_key 
    UNIQUE (content_hash, context_fingerprint);

-- Create simple statistics view for monitoring
CREATE OR REPLACE VIEW memory_statistics AS
SELECT 
    COUNT(*) as total_memories,
    COUNT(DISTINCT content_hash) as unique_contents,
    COUNT(DISTINCT context_fingerprint) as unique_contexts,
    AVG(LENGTH(content)) as avg_content_length,
    COUNT(DISTINCT parent_id) as parent_documents,
    SUM(CASE WHEN chunk_index IS NOT NULL THEN 1 ELSE 0 END) as chunked_memories,
    MIN(created_at) as oldest_memory,
    MAX(created_at) as newest_memory
FROM memories;

-- Add comment documenting the final schema
COMMENT ON TABLE memories IS 'Simple memory storage with context-aware deduplication and semantic chunking support';