migrations/001_initial_schema.sql

-- Initial database schema for chat agent
-- Migration: 001_initial_schema
-- Description: Create tables for chat sessions, messages, and language contexts

-- Enable UUID extension for PostgreSQL
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Create chat_sessions table
CREATE TABLE chat_sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL,
    language VARCHAR(50) NOT NULL DEFAULT 'python',
    last_active TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    message_count INTEGER NOT NULL DEFAULT 0,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    session_metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for chat_sessions
CREATE INDEX idx_chat_sessions_user_id ON chat_sessions(user_id);
CREATE INDEX idx_chat_sessions_last_active ON chat_sessions(last_active);
CREATE INDEX idx_chat_sessions_is_active ON chat_sessions(is_active);

-- Create messages table
CREATE TABLE messages (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    session_id UUID NOT NULL REFERENCES chat_sessions(id) ON DELETE CASCADE,
    role VARCHAR(20) NOT NULL CHECK (role IN ('user', 'assistant')),
    content TEXT NOT NULL,
    language VARCHAR(50) NOT NULL DEFAULT 'python',
    timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    message_metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for messages
CREATE INDEX idx_messages_session_id ON messages(session_id);
CREATE INDEX idx_messages_timestamp ON messages(timestamp);
CREATE INDEX idx_messages_role ON messages(role);

-- Create language_contexts table
CREATE TABLE language_contexts (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    session_id UUID NOT NULL REFERENCES chat_sessions(id) ON DELETE CASCADE,
    language VARCHAR(50) NOT NULL DEFAULT 'python',
    prompt_template TEXT,
    syntax_highlighting VARCHAR(50),
    updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for language_contexts
CREATE UNIQUE INDEX idx_language_contexts_session_id ON language_contexts(session_id);

-- Create function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Create triggers to automatically update updated_at
CREATE TRIGGER update_chat_sessions_updated_at 
    BEFORE UPDATE ON chat_sessions 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_messages_updated_at 
    BEFORE UPDATE ON messages 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_language_contexts_updated_at 
    BEFORE UPDATE ON language_contexts 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Insert default supported languages data (optional)
-- This can be used for reference or validation
CREATE TABLE supported_languages (
    code VARCHAR(50) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    syntax_highlighting VARCHAR(50),
    file_extensions TEXT[],
    prompt_template TEXT,
    created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Insert supported languages
INSERT INTO supported_languages (code, name, syntax_highlighting, file_extensions, prompt_template) VALUES
('python', 'Python', 'python', ARRAY['.py', '.pyw'], 'You are a helpful Python programming assistant. Provide clear, beginner-friendly explanations and examples.'),
('javascript', 'JavaScript', 'javascript', ARRAY['.js', '.mjs'], 'You are a helpful JavaScript programming assistant. Provide clear, beginner-friendly explanations and examples.'),
('typescript', 'TypeScript', 'typescript', ARRAY['.ts', '.tsx'], 'You are a helpful TypeScript programming assistant. Provide clear, beginner-friendly explanations and examples.'),
('java', 'Java', 'java', ARRAY['.java'], 'You are a helpful Java programming assistant. Provide clear, beginner-friendly explanations and examples.'),
('cpp', 'C++', 'cpp', ARRAY['.cpp', '.cc', '.cxx', '.h', '.hpp'], 'You are a helpful C++ programming assistant. Provide clear, beginner-friendly explanations and examples.'),
('csharp', 'C#', 'csharp', ARRAY['.cs'], 'You are a helpful C# programming assistant. Provide clear, beginner-friendly explanations and examples.'),
('go', 'Go', 'go', ARRAY['.go'], 'You are a helpful Go programming assistant. Provide clear, beginner-friendly explanations and examples.'),
('rust', 'Rust', 'rust', ARRAY['.rs'], 'You are a helpful Rust programming assistant. Provide clear, beginner-friendly explanations and examples.');

-- Add comments for documentation
COMMENT ON TABLE chat_sessions IS 'Stores user chat sessions with language context and activity tracking';
COMMENT ON TABLE messages IS 'Stores individual chat messages with role, content, and language information';
COMMENT ON TABLE language_contexts IS 'Stores session-specific language settings and prompt templates';
COMMENT ON TABLE supported_languages IS 'Reference table for supported programming languages and their configurations';

COMMENT ON COLUMN chat_sessions.user_id IS 'UUID of the user who owns this chat session';
COMMENT ON COLUMN chat_sessions.language IS 'Current programming language for this session';
COMMENT ON COLUMN chat_sessions.last_active IS 'Timestamp of last activity in this session';
COMMENT ON COLUMN chat_sessions.message_count IS 'Total number of messages in this session';
COMMENT ON COLUMN chat_sessions.is_active IS 'Whether this session is currently active';
COMMENT ON COLUMN chat_sessions.session_metadata IS 'Additional session metadata as JSON';

COMMENT ON COLUMN messages.session_id IS 'Reference to the chat session this message belongs to';
COMMENT ON COLUMN messages.role IS 'Role of the message sender: user or assistant';
COMMENT ON COLUMN messages.content IS 'The actual message content';
COMMENT ON COLUMN messages.language IS 'Programming language context when this message was sent';
COMMENT ON COLUMN messages.timestamp IS 'When this message was created';
COMMENT ON COLUMN messages.message_metadata IS 'Additional message metadata as JSON';

COMMENT ON COLUMN language_contexts.session_id IS 'Reference to the chat session this context belongs to';
COMMENT ON COLUMN language_contexts.language IS 'Current programming language for the session';
COMMENT ON COLUMN language_contexts.prompt_template IS 'Custom prompt template for this language';
COMMENT ON COLUMN language_contexts.syntax_highlighting IS 'Syntax highlighting scheme identifier';