-- Initialize PostgreSQL database for Mem0 with pgvector extension

-- Create the pgvector extension for vector operations
CREATE EXTENSION IF NOT EXISTS vector;

-- Create user if not exists (in case it's needed)
DO
$do$
BEGIN
   IF NOT EXISTS (
      SELECT FROM pg_catalog.pg_roles
      WHERE  rolname = 'mem0_user') THEN

      CREATE ROLE mem0_user LOGIN PASSWORD 'mem0_password';
   END IF;
END
$do$;

-- Grant necessary permissions
GRANT ALL PRIVILEGES ON DATABASE mem0_db TO mem0_user;
GRANT ALL ON SCHEMA public TO mem0_user;

-- Create table for vector embeddings (if needed by Mem0's pgvector implementation)
CREATE TABLE IF NOT EXISTS embeddings (
    id SERIAL PRIMARY KEY,
    user_id VARCHAR(255),
    content TEXT,
    embedding VECTOR(1536),  -- OpenAI embedding dimension
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Create index for efficient vector similarity search
CREATE INDEX IF NOT EXISTS embeddings_embedding_idx ON embeddings 
USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

-- Create index for user_id lookups
CREATE INDEX IF NOT EXISTS embeddings_user_id_idx ON embeddings (user_id);

-- Create index for metadata queries
CREATE INDEX IF NOT EXISTS embeddings_metadata_idx ON embeddings USING GIN (metadata);

-- Grant permissions on the table
GRANT ALL PRIVILEGES ON TABLE embeddings TO mem0_user;
GRANT USAGE, SELECT ON SEQUENCE embeddings_id_seq TO mem0_user;

-- Create table for memory history tracking
CREATE TABLE IF NOT EXISTS memory_history (
    id SERIAL PRIMARY KEY,
    memory_id VARCHAR(255),
    user_id VARCHAR(255),
    action VARCHAR(50),
    previous_value TEXT,
    new_value TEXT,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Create indexes for memory history
CREATE INDEX IF NOT EXISTS memory_history_memory_id_idx ON memory_history (memory_id);
CREATE INDEX IF NOT EXISTS memory_history_user_id_idx ON memory_history (user_id);
CREATE INDEX IF NOT EXISTS memory_history_created_at_idx ON memory_history (created_at);

-- Grant permissions
GRANT ALL PRIVILEGES ON TABLE memory_history TO mem0_user;
GRANT USAGE, SELECT ON SEQUENCE memory_history_id_seq TO mem0_user;