CREATE SCHEMA IF NOT EXISTS quiz;
SET search_path TO quiz, public;
-- CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;

-- drop everything
DROP TABLE IF EXISTS quiz.answer;
DROP TABLE IF EXISTS quiz.proposition;
DROP TABLE IF EXISTS quiz.question;
DROP TABLE IF EXISTS quiz.quiz;
DROP TABLE IF EXISTS quiz.quiz_user;

CREATE TABLE IF NOT EXISTS quiz.quiz_user (
  user_id TEXT PRIMARY KEY CHECK (char_length(user_id) > 4),
  firstname TEXT NOT NULL,
  lastname TEXT NOT NULL,
  api_key UUID UNIQUE NULL
);

CREATE TABLE IF NOT EXISTS quiz.quiz (
  quiz_id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  title TEXT UNIQUE NOT NULL,
  description TEXT NOT NULL,
  owner_id TEXT NOT NULL REFERENCES quiz.quiz_user(user_id)
      ON DELETE CASCADE ON UPDATE CASCADE,
  open BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE IF NOT EXISTS quiz.question (
  PRIMARY KEY (quiz_id, question_id),
  quiz_id INTEGER REFERENCES quiz.quiz
    ON DELETE CASCADE ON UPDATE CASCADE,
  question_id INTEGER,
  sentence TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS quiz.proposition (
  PRIMARY KEY (quiz_id, question_id, proposition_id),
  FOREIGN KEY (quiz_id, question_id) REFERENCES quiz.question
    ON DELETE CASCADE ON UPDATE CASCADE,
  quiz_id INTEGER,
  question_id INTEGER,
  proposition_id INTEGER,
  content TEXT NOT NULL,
  correct BOOLEAN NOT NULL
);

CREATE TABLE IF NOT EXISTS quiz.answer (
  PRIMARY KEY (quiz_id, question_id, user_id),
  FOREIGN KEY (quiz_id, question_id, proposition_id) REFERENCES quiz.proposition
    ON DELETE CASCADE ON UPDATE CASCADE,
  quiz_id INTEGER,
  question_id INTEGER,
  user_id TEXT REFERENCES quiz.quiz_user,
  proposition_id INTEGER NOT NULL,
  answered_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);
