<commit_before>DROP TABLE IF EXISTS survey_responses;
DROP TABLE IF EXISTS survey_question_choices;
DROP TABLE IF EXISTS survey_questions;
DROP TABLE IF EXISTS survey_question_types;
DROP TABLE IF EXISTS surveys;

CREATE TABLE surveys (
    survey_id      INT           NOT NULL AUTO_INCREMENT,
    title          VARCHAR(100)  NOT NULL,
    description    TEXT,
    start_time     DATETIME      NOT NULL,
    end_time       DATETIME      NOT NULL,
    access_key     CHAR(64)      NOT NULL, -- hex string
    group_id       INT,                    -- restrict to this group if non-NULL
    auth           BOOLEAN       NOT NULL, -- require authentication (UID and birthday)
    public         BOOLEAN       NOT NULL, -- whether survey should appear in the list of active surveys
    results_shown  BOOLEAN       DEFAULT FALSE, -- whether survey should appear in the list of results
    creator        INT           NOT NULL, -- user id of survey creator

    PRIMARY KEY(survey_id),
    FOREIGN KEY(group_id) REFERENCES groups(group_id) ON DELETE SET NULL,
    FOREIGN KEY(creator) REFERENCES members(user_id) ON DELETE CASCADE
);

CREATE TABLE survey_question_types (
    type_id    INT          NOT NULL AUTO_INCREMENT,
    type_name  VARCHAR(20)  NOT NULL,
    choices    BOOLEAN      NOT NULL, -- whether there is a list of choices associated with this type of question

    PRIMARY KEY(type_id)
);

CREATE TABLE survey_questions (
    question_id  INT           NOT NULL AUTO_INCREMENT,
    survey_id    INT           NOT NULL,
    title        VARCHAR(100)  NOT NULL,
    description  TEXT,
    list_order   INT           NOT NULL, -- position in survey, zero-indexed
    type_id      INT           NOT NULL,

    PRIMARY KEY(question_id),
    FOREIGN KEY(survey_id) REFERENCES surveys(survey_id) ON DELETE CASCADE,
    FOREIGN KEY(type_id) REFERENCES survey_question_types(type_id)
);

CREATE TABLE survey_question_choices (
    choice_id    INT          NOT NULL AUTO_INCREMENT,
    question_id  INT          NOT NULL,
    choice       VARCHAR(50)  NOT NULL,

    PRIMARY KEY(choice_id),
    FOREIGN KEY(question_id) REFERENCES survey_questions(question_id) ON DELETE CASCADE
);

CREATE TABLE survey_responses (
    question_id  INT  NOT NULL,
    user_id      INT,
    response     TEXT, -- JSON
                       -- For dropdown:         choice_id (number)
                       -- For checkbox:         [choice_id (number)]
                       -- For short/long text:  text (string)
                       -- For elected position: [choice_id (number) | user_id (negative number) | NO (null)]
    PRIMARY KEY(question_id, user_id),
    FOREIGN KEY(question_id) REFERENCES survey_questions(question_id) ON DELETE CASCADE,
    FOREIGN KEY(user_id) REFERENCES members(user_id) ON DELETE CASCADE
);

INSERT INTO survey_question_types (type_name, options) VALUES
    ('Dropdown', TRUE),
    ('Checkboxes', TRUE),
    ('Elected position', TRUE),
    ('Short text', FALSE),
    ('Long text', FALSE);<commit_msg>Fixed SQL error<commit_after>DROP TABLE IF EXISTS survey_responses;
DROP TABLE IF EXISTS survey_question_choices;
DROP TABLE IF EXISTS survey_questions;
DROP TABLE IF EXISTS survey_question_types;
DROP TABLE IF EXISTS surveys;

CREATE TABLE surveys (
    survey_id      INT           NOT NULL AUTO_INCREMENT,
    title          VARCHAR(100)  NOT NULL,
    description    TEXT,
    start_time     DATETIME      NOT NULL,
    end_time       DATETIME      NOT NULL,
    access_key     CHAR(64)      NOT NULL, -- hex string
    group_id       INT,                    -- restrict to this group if non-NULL
    auth           BOOLEAN       NOT NULL, -- require authentication (UID and birthday)
    public         BOOLEAN       NOT NULL, -- whether survey should appear in the list of active surveys
    results_shown  BOOLEAN       DEFAULT FALSE, -- whether survey should appear in the list of results
    creator        INT           NOT NULL, -- user id of survey creator

    PRIMARY KEY(survey_id),
    FOREIGN KEY(group_id) REFERENCES groups(group_id) ON DELETE SET NULL,
    FOREIGN KEY(creator) REFERENCES members(user_id) ON DELETE CASCADE
);

CREATE TABLE survey_question_types (
    type_id    INT          NOT NULL AUTO_INCREMENT,
    type_name  VARCHAR(20)  NOT NULL,
    choices    BOOLEAN      NOT NULL, -- whether there is a list of choices associated with this type of question

    PRIMARY KEY(type_id)
);

CREATE TABLE survey_questions (
    question_id  INT           NOT NULL AUTO_INCREMENT,
    survey_id    INT           NOT NULL,
    title        VARCHAR(100)  NOT NULL,
    description  TEXT,
    list_order   INT           NOT NULL, -- position in survey, zero-indexed
    type_id      INT           NOT NULL,

    PRIMARY KEY(question_id),
    FOREIGN KEY(survey_id) REFERENCES surveys(survey_id) ON DELETE CASCADE,
    FOREIGN KEY(type_id) REFERENCES survey_question_types(type_id)
);

CREATE TABLE survey_question_choices (
    choice_id    INT          NOT NULL AUTO_INCREMENT,
    question_id  INT          NOT NULL,
    choice       VARCHAR(50)  NOT NULL,

    PRIMARY KEY(choice_id),
    FOREIGN KEY(question_id) REFERENCES survey_questions(question_id) ON DELETE CASCADE
);

CREATE TABLE survey_responses (
    question_id  INT  NOT NULL,
    user_id      INT,
    response     TEXT, -- JSON
                       -- For dropdown:         choice_id (number)
                       -- For checkbox:         [choice_id (number)]
                       -- For short/long text:  text (string)
                       -- For elected position: [choice_id (number) | user_id (negative number) | NO (null)]
    PRIMARY KEY(question_id, user_id),
    FOREIGN KEY(question_id) REFERENCES survey_questions(question_id) ON DELETE CASCADE,
    FOREIGN KEY(user_id) REFERENCES members(user_id) ON DELETE CASCADE
);

INSERT INTO survey_question_types (type_name, choices) VALUES
    ('Dropdown', TRUE),
    ('Checkboxes', TRUE),
    ('Elected position', TRUE),
    ('Short text', FALSE),
    ('Long text', FALSE);
{'fraction_non_alphanumeric': 0.06347107438016529, 'fraction_numerical': 0.003966942148760331, 'mean_word_length': 2.3747908533184607, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2704316', 'n_tokens_mistral': 1837, 'n_tokens_neox': 1752, 'n_words': 587}