discord-fishbowl/migrations/001_critical_persistence_tables.sql

-- Phase 1: Critical Data Loss Prevention Migration
-- This migration adds essential tables to prevent data loss on application restart

-- Character state persistence (CRITICAL)
CREATE TABLE IF NOT EXISTS character_state (
    character_id INTEGER PRIMARY KEY REFERENCES characters(id) ON DELETE CASCADE,
    mood VARCHAR(50),
    energy FLOAT DEFAULT 1.0,
    conversation_count INTEGER DEFAULT 0,
    recent_interactions JSONB DEFAULT '[]'::jsonb,
    last_updated TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Enhanced character features (CRITICAL)  
CREATE TABLE IF NOT EXISTS character_knowledge_areas (
    id SERIAL PRIMARY KEY,
    character_id INTEGER REFERENCES characters(id) ON DELETE CASCADE,
    topic VARCHAR(100) NOT NULL,
    expertise_level FLOAT DEFAULT 0.5 CHECK (expertise_level >= 0 AND expertise_level <= 1),
    last_updated TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(character_id, topic)
);

CREATE TABLE IF NOT EXISTS character_goals (
    id SERIAL PRIMARY KEY,
    character_id INTEGER REFERENCES characters(id) ON DELETE CASCADE,
    goal_id VARCHAR(255) UNIQUE NOT NULL,
    description TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'completed', 'paused', 'abandoned')),
    progress FLOAT DEFAULT 0.0 CHECK (progress >= 0 AND progress <= 1),
    target_date DATE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Character reflections history (CRITICAL)
CREATE TABLE IF NOT EXISTS character_reflections (
    id SERIAL PRIMARY KEY,
    character_id INTEGER REFERENCES characters(id) ON DELETE CASCADE,
    reflection_content TEXT NOT NULL,
    trigger_event VARCHAR(100),
    mood_before VARCHAR(50),
    mood_after VARCHAR(50),
    insights_gained TEXT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Trust relationships between characters (CRITICAL)
CREATE TABLE IF NOT EXISTS character_trust_levels (
    id SERIAL PRIMARY KEY,
    source_character_id INTEGER REFERENCES characters(id) ON DELETE CASCADE,
    target_character_id INTEGER REFERENCES characters(id) ON DELETE CASCADE,
    trust_level FLOAT DEFAULT 0.3 CHECK (trust_level >= 0 AND trust_level <= 1),
    relationship_type VARCHAR(50) DEFAULT 'acquaintance',
    shared_experiences INTEGER DEFAULT 0,
    last_interaction TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(source_character_id, target_character_id),
    CHECK(source_character_id != target_character_id)
);

-- Vector store synchronization (CRITICAL)
-- Add vector_store_id to existing memories table if not exists
ALTER TABLE memories 
ADD COLUMN IF NOT EXISTS vector_store_id VARCHAR(255),
ADD COLUMN IF NOT EXISTS embedding_model VARCHAR(100),
ADD COLUMN IF NOT EXISTS embedding_dimension INTEGER;

-- Vector embeddings backup table
CREATE TABLE IF NOT EXISTS vector_embeddings (
    id SERIAL PRIMARY KEY,
    memory_id INTEGER REFERENCES memories(id) ON DELETE CASCADE,
    vector_id VARCHAR(255) NOT NULL,
    embedding_data BYTEA,
    vector_database VARCHAR(50) DEFAULT 'chromadb',
    collection_name VARCHAR(100),
    embedding_metadata JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(memory_id, vector_database)
);

-- Conversation context (CRITICAL)
CREATE TABLE IF NOT EXISTS conversation_context (
    conversation_id INTEGER PRIMARY KEY REFERENCES conversations(id) ON DELETE CASCADE,
    energy_level FLOAT DEFAULT 1.0 CHECK (energy_level >= 0 AND energy_level <= 1),
    conversation_type VARCHAR(50) DEFAULT 'general',
    emotional_state JSONB DEFAULT '{}'::jsonb,
    speaker_patterns JSONB DEFAULT '{}'::jsonb,
    topic_drift_score FLOAT DEFAULT 0.0,
    engagement_level FLOAT DEFAULT 0.5,
    last_updated TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Message quality tracking (CRITICAL)
CREATE TABLE IF NOT EXISTS message_quality_metrics (
    id SERIAL PRIMARY KEY,
    message_id INTEGER REFERENCES messages(id) ON DELETE CASCADE,
    creativity_score FLOAT CHECK (creativity_score >= 0 AND creativity_score <= 1),
    coherence_score FLOAT CHECK (coherence_score >= 0 AND coherence_score <= 1),
    sentiment_score FLOAT CHECK (sentiment_score >= -1 AND sentiment_score <= 1),
    engagement_potential FLOAT CHECK (engagement_potential >= 0 AND engagement_potential <= 1),
    response_time_ms INTEGER,
    calculated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Memory sharing events (HIGH PRIORITY)
CREATE TABLE IF NOT EXISTS memory_sharing_events (
    id SERIAL PRIMARY KEY,
    source_character_id INTEGER REFERENCES characters(id) ON DELETE CASCADE,
    target_character_id INTEGER REFERENCES characters(id) ON DELETE CASCADE,
    memory_id INTEGER REFERENCES memories(id) ON DELETE CASCADE,
    trust_level_at_sharing FLOAT,
    sharing_reason VARCHAR(200),
    acceptance_status VARCHAR(20) DEFAULT 'pending' CHECK (acceptance_status IN ('pending', 'accepted', 'rejected')),
    shared_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMPTZ
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_character_state_character_id ON character_state(character_id);
CREATE INDEX IF NOT EXISTS idx_character_state_last_updated ON character_state(last_updated);

CREATE INDEX IF NOT EXISTS idx_character_knowledge_character_id ON character_knowledge_areas(character_id);
CREATE INDEX IF NOT EXISTS idx_character_knowledge_topic ON character_knowledge_areas(topic);

CREATE INDEX IF NOT EXISTS idx_character_goals_character_id ON character_goals(character_id);
CREATE INDEX IF NOT EXISTS idx_character_goals_status ON character_goals(status);

CREATE INDEX IF NOT EXISTS idx_character_reflections_character_id ON character_reflections(character_id);
CREATE INDEX IF NOT EXISTS idx_character_reflections_created_at ON character_reflections(created_at);

CREATE INDEX IF NOT EXISTS idx_trust_levels_source ON character_trust_levels(source_character_id);
CREATE INDEX IF NOT EXISTS idx_trust_levels_target ON character_trust_levels(target_character_id);

CREATE INDEX IF NOT EXISTS idx_vector_embeddings_memory_id ON vector_embeddings(memory_id);
CREATE INDEX IF NOT EXISTS idx_vector_embeddings_vector_id ON vector_embeddings(vector_id);

CREATE INDEX IF NOT EXISTS idx_conversation_context_conversation_id ON conversation_context(conversation_id);
CREATE INDEX IF NOT EXISTS idx_conversation_context_updated ON conversation_context(last_updated);

CREATE INDEX IF NOT EXISTS idx_message_quality_message_id ON message_quality_metrics(message_id);

CREATE INDEX IF NOT EXISTS idx_memory_sharing_source ON memory_sharing_events(source_character_id);
CREATE INDEX IF NOT EXISTS idx_memory_sharing_target ON memory_sharing_events(target_character_id);
CREATE INDEX IF NOT EXISTS idx_memory_sharing_shared_at ON memory_sharing_events(shared_at);

-- Update updated_at timestamps automatically
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Add triggers for updated_at columns
DROP TRIGGER IF EXISTS update_character_goals_updated_at ON character_goals;
CREATE TRIGGER update_character_goals_updated_at 
    BEFORE UPDATE ON character_goals 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_character_trust_levels_updated_at ON character_trust_levels;
CREATE TRIGGER update_character_trust_levels_updated_at 
    BEFORE UPDATE ON character_trust_levels 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_vector_embeddings_updated_at ON vector_embeddings;
CREATE TRIGGER update_vector_embeddings_updated_at 
    BEFORE UPDATE ON vector_embeddings 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Insert default character states for existing characters
INSERT INTO character_state (character_id, mood, energy, conversation_count)
SELECT id, 'neutral', 1.0, 0 
FROM characters 
WHERE id NOT IN (SELECT character_id FROM character_state)
ON CONFLICT (character_id) DO NOTHING;

-- Insert default conversation contexts for existing conversations
INSERT INTO conversation_context (conversation_id, energy_level, conversation_type)
SELECT id, 1.0, 'general'
FROM conversations 
WHERE id NOT IN (SELECT conversation_id FROM conversation_context)
ON CONFLICT (conversation_id) DO NOTHING;