scripts/supabase-schema.sql

-- Create messages table for chat history
CREATE TABLE IF NOT EXISTS messages (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    role TEXT NOT NULL CHECK (role IN ('user', 'assistant')),
    content TEXT NOT NULL,
    provider TEXT CHECK (provider IN ('openai', 'openrouter', 'groq', 'grok')),
    session_id UUID,
    file_name TEXT,
    file_type TEXT,
    file_content TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create chat sessions table
CREATE TABLE IF NOT EXISTS chat_sessions (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    title TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Add foreign key relationship
ALTER TABLE messages 
ADD CONSTRAINT fk_session 
FOREIGN KEY (session_id) REFERENCES chat_sessions(id) 
ON DELETE CASCADE;

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_messages_session_id ON messages(session_id);
CREATE INDEX IF NOT EXISTS idx_messages_created_at ON messages(created_at);
CREATE INDEX IF NOT EXISTS idx_sessions_created_at ON chat_sessions(created_at);

-- Enable Row Level Security (RLS)
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE chat_sessions ENABLE ROW LEVEL SECURITY;

-- Create policies (adjust based on your auth requirements)
CREATE POLICY "Users can view their own messages" ON messages
    FOR SELECT USING (true); -- Adjust based on your auth setup

CREATE POLICY "Users can insert their own messages" ON messages
    FOR INSERT WITH CHECK (true); -- Adjust based on your auth setup

CREATE POLICY "Users can view their own sessions" ON chat_sessions
    FOR SELECT USING (true); -- Adjust based on your auth setup

CREATE POLICY "Users can create their own sessions" ON chat_sessions
    FOR INSERT WITH CHECK (true); -- Adjust based on your auth setup