SyntaxSensei

CREATE TABLE users (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    email TEXT UNIQUE NOT NULL,
    password_hash TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    stripe_customer_id TEXT UNIQUE, -- Stripe customer ID for billing
    subscription_status TEXT DEFAULT 'free' -- 'free', 'active', 'canceled'
);

CREATE TABLE quizzes (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title TEXT NOT NULL,
    description TEXT,
    code_snippet TEXT NOT NULL, -- The original code block for the quiz
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    embed_id TEXT UNIQUE NOT NULL DEFAULT substr(md5(random()::text), 0, 10), -- Short unique ID for embedding
    is_published BOOLEAN DEFAULT FALSE
);

CREATE INDEX idx_quizzes_user_id ON quizzes(user_id);
CREATE INDEX idx_quizzes_embed_id ON quizzes(embed_id);

CREATE TABLE questions (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    quiz_id uuid NOT NULL REFERENCES quizzes(id) ON DELETE CASCADE,
    type TEXT NOT NULL, -- 'mcq', 'fill_in_blank', 'bug_fix'
    question_text TEXT NOT NULL,
    options JSONB, -- For MCQ: [{text: 'Option A', is_correct: true}], For fill_in_blank: [{id: 'placeholder1', value: 'correct_word'}], For bug_fix: {original: '...', fixed: '...'}
    correct_answer JSONB, -- Storing the definitive correct answer/logic for auto-grading
    explanation TEXT, -- Explanation for the correct answer
    order_index INTEGER NOT NULL, -- To maintain question order
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_questions_quiz_id ON questions(quiz_id);

CREATE TABLE quiz_attempts (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    quiz_id uuid NOT NULL REFERENCES quizzes(id) ON DELETE CASCADE,
    user_id uuid REFERENCES users(id) ON DELETE SET NULL, -- Optional for anonymous attempts
    started_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    completed_at TIMESTAMP WITH TIME ZONE,
    score INTEGER, -- Score out of total possible points
    total_questions INTEGER,
    answers JSONB, -- Stores user's responses for each question
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_quiz_attempts_quiz_id ON quiz_attempts(quiz_id);
CREATE INDEX idx_quiz_attempts_user_id ON quiz_attempts(user_id);