Gap of the Day

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  stripe_customer_id TEXT UNIQUE,
  subscription_status TEXT DEFAULT 'free_tier' -- 'free_tier', 'premium', 'pro'
);

CREATE TABLE quizzes (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  description TEXT,
  input_text TEXT, -- The original text used for generation
  status TEXT DEFAULT 'draft', -- 'draft', 'published', 'archived'
  export_format TEXT, -- e.g., 'json', 'markdown', 'text'
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TYPE question_type AS ENUM ('multiple_choice', 'true_false', 'fill_in_the_blank');

CREATE TABLE questions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  quiz_id UUID REFERENCES quizzes(id) ON DELETE CASCADE,
  question_text TEXT NOT NULL,
  question_type question_type NOT NULL,
  options JSONB, -- For multiple_choice: array of strings. For fill_in_the_blank: array of strings for possible answers/blanks.
  correct_answer TEXT, -- For multiple_choice: the correct option text. For true_false: 'true'/'false'. For fill_in_the_blank: the answer string.
  explanation TEXT, -- Optional explanation for the answer
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE quiz_generations (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  quiz_id UUID REFERENCES quizzes(id) ON DELETE SET NULL, -- Link to the generated quiz
  input_text_length INT NOT NULL, -- Length of the input text for cost tracking
  model_used TEXT, -- e.g., 'gpt-4', 'claude-3-opus'
  tokens_used INT,
  cost DECIMAL(10, 4), -- Estimated cost of AI generation
  generated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  status TEXT DEFAULT 'success' -- 'success', 'failed'
);

CREATE INDEX idx_quizzes_user_id ON quizzes(user_id);
CREATE INDEX idx_questions_quiz_id ON questions(quiz_id);
CREATE INDEX idx_generations_user_id ON quiz_generations(user_id);