bsw@1735: CREATE TABLE survey (
bsw@1735:   id SERIAL4 PRIMARY KEY,
bsw@1735:   title TEXT NOT NULL,
bsw@1735:   text TEXT NOT NULL,
bsw@1746:   finished_title TEXT NOT NULL,
bsw@1735:   finished_text TEXT NOT NULL,
bsw@1735:   created TIMESTAMPTZ NOT NULL DEFAULT now(),
bsw@1735:   open_from TIMESTAMPTZ NOT NULL,
bsw@1735:   open_until TIMESTAMPTZ NOT NULL
bsw@1735: );
bsw@1735: 
bsw@1735: CREATE TABLE survey_question (
bsw@1735:   id SERIAL4 PRIMARY KEY,
bsw@1735:   survey_id INT4 NOT NULL REFERENCES survey(id),
bsw@1735:   position INT4 NOT NULL,
bsw@1735:   question TEXT NOT NULL,
bsw@1735:   description TEXT,
bsw@1735:   answer_type TEXT NOT NULL,
bsw@1735:   answer_options json
bsw@1735: );
bsw@1735: 
bsw@1735: CREATE TABLE survey_answer_set (
bsw@1735:   ident TEXT NOT NULL PRIMARY KEY,
bsw@1735:   survey_id INT4 NOT NULL REFERENCES survey(id)
bsw@1735: );
bsw@1735: 
bsw@1735: CREATE TABLE survey_answer (
bsw@1735:   id SERIAL8 PRIMARY KEY,
bsw@1735:   survey_answer_set_ident TEXT NOT NULL REFERENCES survey_answer_set(ident) ON DELETE CASCADE,
bsw@1735:   survey_question_id INT4 NOT NULL REFERENCES survey_question(id),
bsw@1735:   answer TEXT NOT NULL
bsw@1735: );
bsw@1735: 
bsw@1735: CREATE TABLE survey_member (
bsw@1735:   id SERIAL4 PRIMARY KEY,
bsw@1735:   survey_id INT4 NOT NULL REFERENCES survey(id),
bsw@1735:   member_id INT4 NOT NULL REFERENCES member(id),
bsw@1735:   survey_answer_set_ident TEXT REFERENCES survey_answer_set(ident),
bsw@1735:   started TIMESTAMPTZ DEFAULT now(),
bsw@1735:   finished TIMESTAMPTZ,
bsw@1735:   rejected TIMESTAMPTZ
bsw@1735: );
bsw@1735: 
bsw@1735: INSERT INTO survey (id, title, text, finished_text, open_from, open_until) VALUES (
bsw@1735:   1,
bsw@1735:   'Example survey',
bsw@1735:   'This is just an example',
bsw@1735:   '<strong>Done!</strong><br>You finished it. Thank you very much.',
bsw@1735:   '2021-10-06 12:00',
bsw@1735:   '2021-10-14 12:00'
bsw@1735: );
bsw@1735: 
bsw@1735: INSERT INTO survey_question (survey_id, position, question, answer_type, answer_options) VALUES
bsw@1735:   (1, 1, 'What color do you like?', 'radio', '[ "Red", "Green", "Blue" ]'),
bsw@1735:   (1, 2, 'What form do you like?', 'radio', '[ "Square", "Circle", "Hexagon" ]')
bsw@1735: ;
bsw@1735: 
bsw@1735: