database/supabase_schema.sql

-- Supabase Database Schema for Topcoder Challenge Steward Agent
-- This file contains the SQL schema needed to set up the database tables in Supabase

-- =========================================
-- TABLES
-- =========================================

-- Users table to store email addresses and preferences
CREATE TABLE users (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    preferences_text TEXT,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Notifications table to track sent notifications and avoid duplicates
CREATE TABLE notifications (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    challenge_id VARCHAR(255),
    sent_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    challenge_title VARCHAR(500),
    challenge_url TEXT,
    prize_amount DECIMAL(10,2),
    challenge_track VARCHAR(100)
);

-- Agent runs log to track system performance and debugging
CREATE TABLE agent_runs (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    run_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    challenges_found INTEGER DEFAULT 0,
    notifications_sent INTEGER DEFAULT 0,
    status VARCHAR(50) DEFAULT 'completed',
    error_message TEXT
);

-- =========================================
-- INDEXES FOR PERFORMANCE
-- =========================================

-- Users table indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_active ON users(active);

-- Notifications table indexes
CREATE INDEX idx_notifications_user_id ON notifications(user_id);
CREATE INDEX idx_notifications_sent_at ON notifications(sent_at);
CREATE INDEX idx_notifications_challenge_id ON notifications(challenge_id);

-- Agent runs table indexes
CREATE INDEX idx_agent_runs_user_id ON agent_runs(user_id);
CREATE INDEX idx_agent_runs_run_at ON agent_runs(run_at);
CREATE INDEX idx_agent_runs_status ON agent_runs(status);

-- =========================================
-- ROW LEVEL SECURITY (RLS) POLICIES
-- =========================================

-- Enable RLS on all tables
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE notifications ENABLE ROW LEVEL SECURITY;
ALTER TABLE agent_runs ENABLE ROW LEVEL SECURITY;

-- Policy to allow service role to read/write all data
CREATE POLICY "Service role has full access to users" ON users
    FOR ALL USING (true);

CREATE POLICY "Service role has full access to notifications" ON notifications
    FOR ALL USING (true);

CREATE POLICY "Service role has full access to agent_runs" ON agent_runs
    FOR ALL USING (true);

-- =========================================
-- FUNCTIONS AND TRIGGERS
-- =========================================

-- Function to automatically update the updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Trigger to auto-update updated_at on users table
CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- =========================================
-- SAMPLE DATA FOR TESTING (OPTIONAL)
-- =========================================

-- Uncomment the lines below if you want to insert sample data for testing

-- INSERT INTO users (email, preferences_text, active) VALUES 
--     ('test@example.com', 'I am interested in AI and machine learning challenges with prizes over $1000', true),
--     ('developer@example.com', 'Looking for development challenges, especially web development and mobile app challenges', true),
--     ('designer@example.com', 'Design challenges, UI/UX, and creative challenges preferred', false);

-- =========================================
-- SETUP INSTRUCTIONS
-- =========================================

-- To set up this schema in Supabase:
-- 
-- 1. Go to your Supabase project dashboard
-- 2. Navigate to the SQL Editor
-- 3. Copy and paste this entire file contents
-- 4. Click "Run" to execute the schema
-- 
-- Note: Make sure you have the proper environment variables set:
-- - SUPABASE_URL: Your Supabase project URL
-- - SUPABASE_ANON_KEY: Your Supabase anonymous key (for the service role)
-- 
-- The service role key should have permissions to read/write all tables.