-- Create user_profiles table for memory service
CREATE TABLE IF NOT EXISTS user_profiles (
    user_id VARCHAR(255) NOT NULL,
    tenant_id VARCHAR(255) NOT NULL,
    preferences JSONB DEFAULT '{}'::jsonb,
    context JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    PRIMARY KEY (user_id, tenant_id),
    INDEX idx_tenant (tenant_id)
);

-- Create conversation_history table
CREATE TABLE IF NOT EXISTS conversation_history (
    id VARCHAR(255) PRIMARY KEY,
    user_id VARCHAR(255) NOT NULL,
    tenant_id VARCHAR(255) NOT NULL,
    session_id VARCHAR(255) NOT NULL,
    messages JSONB NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    INDEX idx_user_tenant (user_id, tenant_id),
    INDEX idx_session (session_id),
    INDEX idx_created_at (created_at)
);