1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/db/survey.sql	Thu Feb 03 15:57:22 2022 +0100
     1.3 @@ -0,0 +1,59 @@
     1.4 +CREATE TABLE survey (
     1.5 +  id SERIAL4 PRIMARY KEY,
     1.6 +  title TEXT NOT NULL,
     1.7 +  text TEXT NOT NULL,
     1.8 +  finished_title TEXT NOT NULL,
     1.9 +  finished_text TEXT NOT NULL,
    1.10 +  created TIMESTAMPTZ NOT NULL DEFAULT now(),
    1.11 +  open_from TIMESTAMPTZ NOT NULL,
    1.12 +  open_until TIMESTAMPTZ NOT NULL
    1.13 +);
    1.14 +
    1.15 +CREATE TABLE survey_question (
    1.16 +  id SERIAL4 PRIMARY KEY,
    1.17 +  survey_id INT4 NOT NULL REFERENCES survey(id),
    1.18 +  position INT4 NOT NULL,
    1.19 +  question TEXT NOT NULL,
    1.20 +  description TEXT,
    1.21 +  answer_type TEXT NOT NULL,
    1.22 +  answer_options json
    1.23 +);
    1.24 +
    1.25 +CREATE TABLE survey_answer_set (
    1.26 +  ident TEXT NOT NULL PRIMARY KEY,
    1.27 +  survey_id INT4 NOT NULL REFERENCES survey(id),
    1.28 +  data JSON
    1.29 +);
    1.30 +
    1.31 +CREATE TABLE survey_answer (
    1.32 +  id SERIAL8 PRIMARY KEY,
    1.33 +  survey_answer_set_ident TEXT NOT NULL REFERENCES survey_answer_set(ident) ON DELETE CASCADE,
    1.34 +  survey_question_id INT4 NOT NULL REFERENCES survey_question(id),
    1.35 +  answer TEXT NOT NULL
    1.36 +);
    1.37 +
    1.38 +CREATE TABLE survey_member (
    1.39 +  id SERIAL4 PRIMARY KEY,
    1.40 +  survey_id INT4 NOT NULL REFERENCES survey(id),
    1.41 +  member_id INT4 NOT NULL REFERENCES member(id),
    1.42 +  survey_answer_set_ident TEXT REFERENCES survey_answer_set(ident),
    1.43 +  started TIMESTAMPTZ DEFAULT now(),
    1.44 +  finished TIMESTAMPTZ,
    1.45 +  rejected TIMESTAMPTZ
    1.46 +);
    1.47 +
    1.48 +INSERT INTO survey (id, title, text, finished_text, open_from, open_until) VALUES (
    1.49 +  1,
    1.50 +  'Example survey',
    1.51 +  'This is just an example',
    1.52 +  '<strong>Done!</strong><br>You finished it. Thank you very much.',
    1.53 +  '2021-10-06 12:00',
    1.54 +  '2021-10-14 12:00'
    1.55 +);
    1.56 +
    1.57 +INSERT INTO survey_question (survey_id, position, question, answer_type, answer_options) VALUES
    1.58 +  (1, 1, 'What color do you like?', 'radio', '[ "Red", "Green", "Blue" ]'),
    1.59 +  (1, 2, 'What form do you like?', 'radio', '[ "Square", "Circle", "Hexagon" ]')
    1.60 +;
    1.61 +
    1.62 +