-- Migration: Restore Memory Tiering System
-- Date: 2025-08-31
-- Description: Re-implement cognitive memory tiering system based on Atkinson-Shiffrin model
--
-- Research Foundation:
-- - Atkinson, R. C., & Shiffrin, R. M. (1968). Human memory: A proposed system and its control processes
-- - Miller, G. A. (1956). The magical number seven, plus or minus two
-- - Ebbinghaus, H. (1885). Memory: A contribution to experimental psychology
-- 
-- This migration restores the tier system with proper cognitive science backing

-- Create memory tier enum based on Atkinson-Shiffrin multi-store model
DO $$ BEGIN
    CREATE TYPE memory_tier AS ENUM ('working', 'warm', 'cold', 'frozen');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- Add tiering columns to memories table
ALTER TABLE memories 
    ADD COLUMN IF NOT EXISTS tier memory_tier DEFAULT 'working',
    ADD COLUMN IF NOT EXISTS last_accessed TIMESTAMPTZ DEFAULT NOW(),
    ADD COLUMN IF NOT EXISTS access_count BIGINT DEFAULT 1,
    ADD COLUMN IF NOT EXISTS importance_score DOUBLE PRECISION DEFAULT 1.0,
    ADD COLUMN IF NOT EXISTS consolidation_strength DOUBLE PRECISION DEFAULT 1.0;

-- Update existing records with default values
UPDATE memories 
SET 
    tier = 'working',
    last_accessed = GREATEST(updated_at, created_at),
    access_count = 1,
    importance_score = 1.0,
    consolidation_strength = 1.0
WHERE tier IS NULL;

-- Create indexes for tier-based queries (performance optimization)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_memories_tier 
    ON memories(tier);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_memories_last_accessed 
    ON memories(last_accessed DESC);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_memories_tier_accessed 
    ON memories(tier, last_accessed DESC);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_memories_access_count 
    ON memories(access_count DESC);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_memories_importance 
    ON memories(importance_score DESC);

-- Create tier transitions tracking table for consolidation analysis
CREATE TABLE IF NOT EXISTS memory_tier_transitions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    memory_id UUID NOT NULL REFERENCES memories(id) ON DELETE CASCADE,
    from_tier memory_tier NOT NULL,
    to_tier memory_tier NOT NULL,
    reason TEXT NOT NULL,
    confidence DOUBLE PRECISION NOT NULL CHECK (confidence >= 0 AND confidence <= 1),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for tier transition analysis
CREATE INDEX IF NOT EXISTS idx_tier_transitions_memory_id 
    ON memory_tier_transitions(memory_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_tier_transitions_pattern 
    ON memory_tier_transitions(from_tier, to_tier, created_at);

-- Create view for tier distribution statistics
CREATE OR REPLACE VIEW memory_tier_stats AS
SELECT 
    tier,
    COUNT(*) as memory_count,
    AVG(access_count) as avg_access_count,
    AVG(importance_score) as avg_importance_score,
    AVG(consolidation_strength) as avg_consolidation_strength,
    MIN(last_accessed) as oldest_access,
    MAX(last_accessed) as newest_access,
    AVG(EXTRACT(epoch FROM (NOW() - last_accessed)) / 86400) as avg_days_since_access
FROM memories
GROUP BY tier
ORDER BY 
    CASE tier
        WHEN 'working' THEN 1
        WHEN 'warm' THEN 2  
        WHEN 'cold' THEN 3
        WHEN 'frozen' THEN 4
    END;

-- Create view for working memory capacity monitoring (Miller's 7±2 principle)
CREATE OR REPLACE VIEW working_memory_pressure AS
SELECT 
    COUNT(*) as current_working_memories,
    7 as miller_limit,
    CASE 
        WHEN COUNT(*) > 7 THEN 'EXCEEDED'
        WHEN COUNT(*) = 7 THEN 'AT_LIMIT' 
        WHEN COUNT(*) > 5 THEN 'HIGH'
        ELSE 'NORMAL'
    END as pressure_level,
    ARRAY_AGG(id ORDER BY last_accessed ASC) as oldest_memories_first
FROM memories 
WHERE tier = 'working';

-- Create function to update access statistics
CREATE OR REPLACE FUNCTION update_memory_access(memory_uuid UUID)
RETURNS void AS $$
BEGIN
    UPDATE memories 
    SET 
        last_accessed = NOW(),
        access_count = access_count + 1,
        updated_at = NOW()
    WHERE id = memory_uuid;
END;
$$ LANGUAGE plpgsql;

-- Create function to calculate memory importance score
CREATE OR REPLACE FUNCTION calculate_importance_score(
    access_count_val BIGINT,
    age_days DOUBLE PRECISION,
    days_since_access DOUBLE PRECISION
) RETURNS DOUBLE PRECISION AS $$
DECLARE
    access_density DOUBLE PRECISION;
    recency_factor DOUBLE PRECISION;
    importance DOUBLE PRECISION;
BEGIN
    -- Access density (accesses per day since creation)
    access_density := access_count_val::DOUBLE PRECISION / GREATEST(age_days, 1.0);
    
    -- Recency factor (exponential decay with 30-day half-life)
    recency_factor := EXP(-days_since_access / 30.0);
    
    -- Combined importance score
    importance := LEAST(access_density * recency_factor * 10.0, 1.0);
    
    RETURN importance;
END;
$$ LANGUAGE plpgsql;

-- Create trigger to automatically update importance scores
CREATE OR REPLACE FUNCTION update_importance_trigger()
RETURNS TRIGGER AS $$
BEGIN
    NEW.importance_score := calculate_importance_score(
        NEW.access_count,
        EXTRACT(epoch FROM (NOW() - NEW.created_at)) / 86400,
        EXTRACT(epoch FROM (NOW() - NEW.last_accessed)) / 86400
    );
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply trigger to memories table
DROP TRIGGER IF EXISTS trigger_update_importance ON memories;
CREATE TRIGGER trigger_update_importance
    BEFORE UPDATE ON memories
    FOR EACH ROW
    EXECUTE FUNCTION update_importance_trigger();

-- Create consolidation analysis view
CREATE OR REPLACE VIEW consolidation_candidates AS
SELECT 
    m.*,
    EXTRACT(epoch FROM (NOW() - m.last_accessed)) / 86400 as days_since_access,
    EXTRACT(epoch FROM (NOW() - m.created_at)) / 86400 as age_days,
    CASE 
        -- Working → Warm transitions
        WHEN m.tier = 'working' AND (
            EXTRACT(epoch FROM (NOW() - m.last_accessed)) / 3600 > 24 OR
            (SELECT COUNT(*) FROM memories WHERE tier = 'working') > 7
        ) THEN 'warm'
        
        -- Warm → Cold transitions  
        WHEN m.tier = 'warm' AND 
             m.access_count < 3 AND 
             EXTRACT(epoch FROM (NOW() - m.last_accessed)) / 86400 > 7 
        THEN 'cold'
        
        -- Cold → Frozen transitions
        WHEN m.tier = 'cold' AND 
             EXTRACT(epoch FROM (NOW() - m.last_accessed)) / 86400 > 30 
        THEN 'frozen'
        
        ELSE NULL
    END as recommended_tier,
    
    CASE 
        WHEN m.tier = 'working' THEN 0.8
        WHEN m.tier = 'warm' THEN 0.7
        WHEN m.tier = 'cold' THEN 0.9
        ELSE 0.5
    END as transition_confidence
    
FROM memories m
WHERE 
    (m.tier = 'working' AND (
        EXTRACT(epoch FROM (NOW() - m.last_accessed)) / 3600 > 24 OR
        (SELECT COUNT(*) FROM memories WHERE tier = 'working') > 7
    )) OR
    (m.tier = 'warm' AND 
     m.access_count < 3 AND 
     EXTRACT(epoch FROM (NOW() - m.last_accessed)) / 86400 > 7) OR
    (m.tier = 'cold' AND 
     EXTRACT(epoch FROM (NOW() - m.last_accessed)) / 86400 > 30)
ORDER BY 
    CASE m.tier
        WHEN 'working' THEN 1  -- Prioritize working memory management
        WHEN 'warm' THEN 2
        WHEN 'cold' THEN 3
    END,
    m.last_accessed ASC;  -- Process oldest first

-- Add comments for documentation
COMMENT ON TYPE memory_tier IS 'Cognitive memory tiers based on Atkinson-Shiffrin model: working (Miller 7±2), warm (consolidation), cold (Ebbinghaus decay), frozen (archival)';
COMMENT ON COLUMN memories.tier IS 'Memory tier classification based on cognitive science research';
COMMENT ON COLUMN memories.last_accessed IS 'Timestamp of last memory access for consolidation calculations';
COMMENT ON COLUMN memories.access_count IS 'Total number of times memory has been accessed';
COMMENT ON COLUMN memories.importance_score IS 'Calculated importance score based on access patterns (0-1)';
COMMENT ON COLUMN memories.consolidation_strength IS 'Memory consolidation strength from spaced repetition (1-5)';
COMMENT ON TABLE memory_tier_transitions IS 'Tracks memory tier transitions for consolidation analysis';
COMMENT ON VIEW memory_tier_stats IS 'Statistics on memory distribution across cognitive tiers';
COMMENT ON VIEW working_memory_pressure IS 'Working memory capacity monitoring based on Miller 7±2 principle';
COMMENT ON VIEW consolidation_candidates IS 'Memories that should be considered for tier transitions';

-- Insert initial transition records for audit trail
INSERT INTO memory_tier_transitions (memory_id, from_tier, to_tier, reason, confidence, created_at)
SELECT 
    id,
    'working'::memory_tier,
    'working'::memory_tier,
    'Initial tier assignment during migration 005',
    1.0,
    NOW()
FROM memories
ON CONFLICT DO NOTHING;

-- Final migration verification
DO $$
DECLARE
    tier_count INTEGER;
    missing_columns INTEGER;
BEGIN
    -- Verify tier enum exists
    SELECT COUNT(*) INTO tier_count
    FROM pg_type 
    WHERE typname = 'memory_tier';
    
    IF tier_count = 0 THEN
        RAISE EXCEPTION 'Memory tier enum not created properly';
    END IF;
    
    -- Verify all new columns exist
    SELECT COUNT(*) INTO missing_columns
    FROM information_schema.columns 
    WHERE table_name = 'memories' 
    AND column_name IN ('tier', 'last_accessed', 'access_count', 'importance_score', 'consolidation_strength');
    
    IF missing_columns != 5 THEN
        RAISE EXCEPTION 'Not all memory tiering columns were added properly';
    END IF;
    
    RAISE NOTICE 'Memory tiering system successfully restored - Atkinson-Shiffrin model implemented';
END $$;