-- Ultimate Memory MCP Server - Kuzu Graph Database Schema
-- This defines the graph structure for storing memories and their relationships

-- Node table for Memory nodes
CREATE NODE TABLE IF NOT EXISTS Memory (
    id STRING,
    content STRING,
    summary STRING,
    memory_type STRING,  -- episodic, semantic, procedural
    confidence_score DOUBLE,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    last_accessed_at TIMESTAMP,
    access_count INT64,
    source_type STRING,
    source_id STRING,
    tags STRING[],
    retrieval_cues STRING[],
    embedding DOUBLE[],  -- Vector embedding for semantic search
    PRIMARY KEY (id)
);

-- Node table for Conversations
CREATE NODE TABLE IF NOT EXISTS Conversation (
    id STRING,
    title STRING,
    started_at TIMESTAMP,
    last_message_at TIMESTAMP,
    participant_count INT64,
    metadata STRING,  -- JSON as string
    PRIMARY KEY (id)
);

-- Node table for Clusters (memory groupings)
CREATE NODE TABLE IF NOT EXISTS Cluster (
    id STRING,
    name STRING,
    description STRING,
    cluster_embedding DOUBLE[],
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

-- Node table for Topics/Concepts
CREATE NODE TABLE IF NOT EXISTS Topic (
    id STRING,
    name STRING,
    description STRING,
    confidence DOUBLE,
    PRIMARY KEY (id)
);

-- Relationship table for memory-to-memory connections
CREATE REL TABLE IF NOT EXISTS RELATES_TO (
    FROM Memory TO Memory,
    relationship_type STRING,  -- causes, enables, contradicts, supports, similar_to, etc.
    strength DOUBLE,
    context STRING,
    bidirectional BOOLEAN,
    created_at TIMESTAMP,
    created_by STRING,  -- system, user, inference
    confidence DOUBLE
);

-- Relationship table for memory-conversation membership
CREATE REL TABLE IF NOT EXISTS BELONGS_TO_CONVERSATION (
    FROM Memory TO Conversation,
    sequence_number INT64,
    created_at TIMESTAMP
);

-- Relationship table for memory-cluster membership  
CREATE REL TABLE IF NOT EXISTS IN_CLUSTER (
    FROM Memory TO Cluster,
    membership_strength DOUBLE,
    added_at TIMESTAMP
);

-- Relationship table for memory-topic associations
CREATE REL TABLE IF NOT EXISTS ABOUT_TOPIC (
    FROM Memory TO Topic,
    relevance_score DOUBLE,
    extracted_at TIMESTAMP
);

-- Relationship table for causal relationships
CREATE REL TABLE IF NOT EXISTS CAUSES (
    FROM Memory TO Memory,
    causal_strength DOUBLE,
    mechanism STRING,
    conditions STRING
);

-- Relationship table for hierarchical relationships
CREATE REL TABLE IF NOT EXISTS CONTAINS (
    FROM Memory TO Memory,
    containment_type STRING,  -- part_of, example_of, instance_of
    specificity_level INT64
);

-- Example queries for common operations:

-- 1. Find all memories related to a specific memory with relationship details
-- MATCH (m1:Memory {id: $memory_id})-[r:RELATES_TO]->(m2:Memory)
-- RETURN m2.id, m2.content, r.relationship_type, r.strength, r.context
-- ORDER BY r.strength DESC;

-- 2. Find conversation memories in chronological order
-- MATCH (m:Memory)-[b:BELONGS_TO_CONVERSATION]->(c:Conversation {id: $conversation_id})
-- RETURN m.id, m.content, m.memory_type, b.sequence_number
-- ORDER BY b.sequence_number;

-- 3. Find memory paths (graph traversal)
-- MATCH path = (start:Memory {id: $start_id})-[:RELATES_TO*1..3]->(end:Memory)
-- WHERE ALL(rel in relationships(path) WHERE rel.strength > 0.3)
-- RETURN path, length(path) as depth
-- ORDER BY depth;

-- 4. Find memories by topic
-- MATCH (m:Memory)-[a:ABOUT_TOPIC]->(t:Topic {name: $topic_name})
-- RETURN m.id, m.content, a.relevance_score
-- ORDER BY a.relevance_score DESC;

-- 5. Find clusters and their member memories
-- MATCH (m:Memory)-[ic:IN_CLUSTER]->(c:Cluster)
-- RETURN c.name, c.description, collect(m.content) as memories
-- ORDER BY c.name;

-- 6. Find causal chains
-- MATCH path = (cause:Memory)-[:CAUSES*1..4]->(effect:Memory)
-- RETURN path, nodes(path) as causal_chain, length(path) as chain_length
-- ORDER BY chain_length;

-- 7. Temporal memory sequences
-- MATCH (m1:Memory)-[r:RELATES_TO]->(m2:Memory)
-- WHERE r.relationship_type = 'precedes'
-- RETURN m1.content, m2.content, r.strength
-- ORDER BY r.strength DESC;

-- 8. Most connected memories (centrality analysis)
-- MATCH (m:Memory)-[r:RELATES_TO]-()
-- RETURN m.id, m.content, count(r) as connection_count
-- ORDER BY connection_count DESC
-- LIMIT 10;