Blame for certification_flow_schema.sql

-- Phase 1: Database Schema for New Certification Flow
-- This creates tables to track structured certification sessions and their steps

-- Table 1: certification_sessions
-- Tracks each unique certification attempt with status progression
CREATE TABLE IF NOT EXISTS certification_sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    course_name TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'in_progress' 
        CHECK (status IN ('in_progress', 'assessment_completed', 'project_submitted', 'certified')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Table 2: session_steps  
-- Logs every action within a session for chat history reconstruction
CREATE TABLE IF NOT EXISTS session_steps (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    session_id UUID NOT NULL REFERENCES certification_sessions(id) ON DELETE CASCADE,
    step_type TEXT NOT NULL 
        CHECK (step_type IN ('course_selected', 'learning_started', 'assessment_submitted', 'results_viewed', 'project_generated')),
    step_data JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Row Level Security (RLS) Policies
-- Enable RLS on both tables
ALTER TABLE certification_sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE session_steps ENABLE ROW LEVEL SECURITY;

-- RLS Policy for certification_sessions: Users can only access their own sessions
CREATE POLICY "Users can only access their own certification sessions" ON certification_sessions
    FOR ALL USING (auth.uid() = user_id);

-- RLS Policy for session_steps: Users can only access steps from their own sessions
CREATE POLICY "Users can only access their own session steps" ON session_steps
    FOR ALL USING (
        EXISTS (
            SELECT 1 FROM certification_sessions 
            WHERE certification_sessions.id = session_steps.session_id 
            AND certification_sessions.user_id = auth.uid()
        )
    );

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_certification_sessions_user_id ON certification_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_certification_sessions_status ON certification_sessions(status);
CREATE INDEX IF NOT EXISTS idx_session_steps_session_id ON session_steps(session_id);
CREATE INDEX IF NOT EXISTS idx_session_steps_step_type ON session_steps(step_type);
CREATE INDEX IF NOT EXISTS idx_session_steps_created_at ON session_steps(created_at);

-- Update trigger for certification_sessions.updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_certification_sessions_updated_at 
    BEFORE UPDATE ON certification_sessions 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Sample step_data structures (for documentation):
/*
For 'course_selected':
{
  "course_name": "JavaScript Fundamentals",
  "difficulty": "Beginner",
  "estimated_duration": "2-3 hours"
}

For 'learning_started':
{
  "modules": [
    {
      "title": "Introduction to JavaScript",
      "youtube_url": "https://youtube.com/watch?v=...",
      "duration": "15 mins"
    }
  ]
}

For 'assessment_submitted':
{
  "score": 85,
  "max_score": 100,
  "assigned_difficulty": "Intermediate",
  "submission_text": "User's assessment response...",
  "evaluation_feedback": "AI feedback on submission..."
}

For 'results_viewed':
{
  "score": 85,
  "grade": "B+",
  "passed": true,
  "recommended_difficulty": "Intermediate",
  "available_difficulties": ["Beginner", "Intermediate", "Advanced"]
}

For 'project_generated':
{
  "project_title": "Build a To-Do List Application",
  "project_description": "Create a full-featured to-do list...",
  "difficulty": "Intermediate",
  "submission_form_link": "https://forms.google.com/...",
  "requirements": ["Feature 1", "Feature 2"]
}
*/