schema.sql

-- Drop existing tables if they exist to prevent schema mismatch
DROP TABLE IF EXISTS user_reviews CASCADE;
DROP TABLE IF EXISTS user_streaks CASCADE;
DROP TABLE IF EXISTS problems CASCADE;

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

-- Table: problems
CREATE TABLE problems (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    problem_number INTEGER, -- Tracked for Journey Path
    pattern VARCHAR(100) NOT NULL,
    difficulty VARCHAR(20) NOT NULL DEFAULT 'Medium' CHECK (difficulty IN ('Easy', 'Medium', 'Hard')),
    google_doc_url TEXT,
    google_doc_id VARCHAR(100), -- Tracks file
    optimal_time VARCHAR(50) NOT NULL DEFAULT 'O(N)',
    optimal_space VARCHAR(50) NOT NULL DEFAULT 'O(N)',
    description TEXT,
    reference_code TEXT, -- Stores reference code synced from Doc
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_user_problem UNIQUE (user_id, name)
);

-- Table: user_reviews
CREATE TABLE user_reviews (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    problem_id UUID NOT NULL REFERENCES problems(id) ON DELETE CASCADE,
    box_level INTEGER NOT NULL DEFAULT 1 CHECK (box_level BETWEEN 1 AND 5),
    last_reviewed TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    next_review TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    times_correct INTEGER NOT NULL DEFAULT 0,
    total_attempts INTEGER NOT NULL DEFAULT 0,
    CONSTRAINT unique_user_problem_review UNIQUE (user_id, problem_id)
);

-- Table: user_streaks
CREATE TABLE user_streaks (
    user_id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    current_streak INTEGER NOT NULL DEFAULT 0 CHECK (current_streak >= 0),
    longest_streak INTEGER NOT NULL DEFAULT 0 CHECK (longest_streak >= 0),
    last_active_date DATE,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Enable RLS on all tables
ALTER TABLE problems ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_reviews ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_streaks ENABLE ROW LEVEL SECURITY;

-- RLS Policies for problems
CREATE POLICY select_problems ON problems FOR SELECT USING (true);
CREATE POLICY insert_problems ON problems FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
CREATE POLICY update_problems ON problems FOR UPDATE TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
CREATE POLICY delete_problems ON problems FOR DELETE TO authenticated USING (auth.uid() = user_id);

-- RLS Policies for user_reviews
CREATE POLICY select_reviews ON user_reviews FOR SELECT TO authenticated USING (auth.uid() = user_id);
CREATE POLICY insert_reviews ON user_reviews FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
CREATE POLICY update_reviews ON user_reviews FOR UPDATE TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
CREATE POLICY delete_reviews ON user_reviews FOR DELETE TO authenticated USING (auth.uid() = user_id);

-- RLS Policies for user_streaks
CREATE POLICY select_streaks ON user_streaks FOR SELECT TO authenticated USING (auth.uid() = user_id);
CREATE POLICY insert_streaks ON user_streaks FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
CREATE POLICY update_streaks ON user_streaks FOR UPDATE TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
CREATE POLICY delete_streaks ON user_streaks FOR DELETE TO authenticated USING (auth.uid() = user_id);

-- Table: user_journey_progress
CREATE TABLE user_journey_progress (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    problem_number INTEGER NOT NULL,
    viewed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_user_journey_problem UNIQUE (user_id, problem_number)
);

-- Enable RLS on new table
ALTER TABLE user_journey_progress ENABLE ROW LEVEL SECURITY;

-- RLS Policies for user_journey_progress
CREATE POLICY select_journey ON user_journey_progress FOR SELECT TO authenticated USING (auth.uid() = user_id);
CREATE POLICY insert_journey ON user_journey_progress FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
CREATE POLICY update_journey ON user_journey_progress FOR UPDATE TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
CREATE POLICY delete_journey ON user_journey_progress FOR DELETE TO authenticated USING (auth.uid() = user_id);

-- Table: user_scores
CREATE TABLE IF NOT EXISTS user_scores (
    user_id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    total_score INTEGER DEFAULT 0 CHECK (total_score >= 0),
    python_challenges_completed INTEGER DEFAULT 0,
    sql_challenges_completed INTEGER DEFAULT 0,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Table: challenge_history
CREATE TABLE IF NOT EXISTS challenge_history (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    challenge_type VARCHAR(20) NOT NULL CHECK (challenge_type IN ('python', 'mysql')),
    challenge_title VARCHAR(255) NOT NULL,
    points_awarded INTEGER NOT NULL DEFAULT 0,
    completed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Enable RLS on new tables
ALTER TABLE user_scores ENABLE ROW LEVEL SECURITY;
ALTER TABLE challenge_history ENABLE ROW LEVEL SECURITY;

-- RLS Policies for user_scores
CREATE POLICY select_scores ON user_scores FOR SELECT TO authenticated USING (auth.uid() = user_id);
CREATE POLICY insert_scores ON user_scores FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
CREATE POLICY update_scores ON user_scores FOR UPDATE TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
CREATE POLICY delete_scores ON user_scores FOR DELETE TO authenticated USING (auth.uid() = user_id);

-- RLS Policies for challenge_history
CREATE POLICY select_history ON challenge_history FOR SELECT TO authenticated USING (auth.uid() = user_id);
CREATE POLICY insert_history ON challenge_history FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
CREATE POLICY update_history ON challenge_history FOR UPDATE TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
CREATE POLICY delete_history ON challenge_history FOR DELETE TO authenticated USING (auth.uid() = user_id);