CREATE TABLE test (
                      id          SERIAL PRIMARY KEY,
                      title       VARCHAR(255) NOT NULL,
                      description TEXT,
                      total_questions INT DEFAULT 0,
                      duration_minutes INT DEFAULT 120,
                      created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE part (
                      id            SERIAL PRIMARY KEY,
                      test_id       INT NOT NULL REFERENCES test(id) ON DELETE CASCADE,
                      part_number   INT NOT NULL,
                      title         VARCHAR(100) NOT NULL,
                      question_count INT DEFAULT 0,
                      display_order INT DEFAULT 0,
                      UNIQUE (test_id, part_number)
);

CREATE TABLE tag (
                     id   SERIAL PRIMARY KEY,
                     name VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE part_tag (
                          part_id INT NOT NULL REFERENCES part(id) ON DELETE CASCADE,
                          tag_id  INT NOT NULL REFERENCES tag(id) ON DELETE CASCADE,
                          PRIMARY KEY (part_id, tag_id)
);

CREATE TABLE question_group (
                                id            SERIAL PRIMARY KEY,
                                part_id       INT NOT NULL REFERENCES part(id) ON DELETE CASCADE,
                                audio_url     VARCHAR(500),
                                image_url     VARCHAR(500),
                                passage_text  TEXT,
                                display_order INT DEFAULT 0
);

CREATE TABLE question (
                          id              SERIAL PRIMARY KEY,
                          group_id        INT NOT NULL REFERENCES question_group(id) ON DELETE CASCADE,
                          question_number INT NOT NULL,
                          question_text   TEXT,
                          display_order   INT DEFAULT 0
);

CREATE TABLE answer_choice (
                               id           SERIAL PRIMARY KEY,
                               question_id  INT NOT NULL REFERENCES question(id) ON DELETE CASCADE,
                               value        CHAR(1) NOT NULL CHECK (value IN ('A', 'B', 'C', 'D')),
                               label_text   TEXT,
                               is_correct   BOOLEAN NOT NULL DEFAULT FALSE
);