-- =====================================================
-- Kviz aplikacija – kompletan model tabela
-- =====================================================
DROP TABLE IF EXISTS user_answers;
DROP TABLE IF EXISTS user_sessions;
DROP TABLE IF EXISTS answers;
DROP TABLE IF EXISTS questions;
DROP TABLE IF EXISTS quizzes;

-- Tabela kvizova
CREATE TABLE quizzes (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    category VARCHAR(100)  -- opcionalno, npr. "JavaScript", "Historija"
);

-- Tabela pitanja
CREATE TABLE questions (
    id SERIAL PRIMARY KEY,
    quiz_id INT not null REFERENCES quizzes(id) ON DELETE CASCADE,
    text VARCHAR(500) NOT NULL,
    type VARCHAR(50) NOT NULL CHECK (type IN (
        'single_choice',
        'multiple_choice',
        'text_input',
        'table_input'
    )),
    correct_answer VARCHAR(1000),  -- tačan odgovor za text_input ili table_input
    note varchar (4000)
);

-- Tabela odgovora za pitanja tipa izbor
CREATE TABLE answers (
    id SERIAL PRIMARY KEY,
    question_id INT not null REFERENCES questions(id) ON DELETE CASCADE,
    text VARCHAR(255) NOT NULL,
    is_correct BOOLEAN DEFAULT FALSE  -- koristi se za single/multiple choice
);

-- Tabela sesija korisnika (pratimo započeti kviz)
CREATE TABLE user_sessions (
    id SERIAL PRIMARY KEY,
    user_name VARCHAR(100) NOT NULL,
    quiz_id INT REFERENCES quizzes(id) ON DELETE CASCADE,
    total_questions INT NOT NULL,
    completed_questions INT DEFAULT 0,
    started_at TIMESTAMP DEFAULT now(),
    completed BOOLEAN DEFAULT FALSE
);
ALTER TABLE user_sessions ADD COLUMN correct_answers INT DEFAULT 0;

-- Tabela odgovora korisnika (samo tačnost)
CREATE TABLE user_answers (
    id SERIAL PRIMARY KEY,
    session_id INT REFERENCES user_sessions(id) ON DELETE CASCADE,  -- veza na session
    question_id INT REFERENCES questions(id) ON DELETE CASCADE,
    is_correct BOOLEAN NOT NULL
);