Survey and form builder

CREATE TYPE question_kind AS ENUM ('short_text', 'long_text', 'single_choice', 'multiple_choice', 'rating', 'date');

CREATE TABLE owners (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    email text NOT NULL UNIQUE
);

CREATE TABLE surveys (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    owner_id uuid NOT NULL,
    title text NOT NULL,
    published boolean DEFAULT false NOT NULL,
    closes_at timestamptz
);
CREATE INDEX surveys_owner_id_idx ON surveys (owner_id);

CREATE TABLE questions (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    survey_id uuid NOT NULL,
    kind question_kind NOT NULL,
    prompt text NOT NULL,
    required boolean DEFAULT false NOT NULL,
    position int NOT NULL,
    UNIQUE (survey_id, position)
);

CREATE TABLE choices (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    question_id uuid NOT NULL,
    label text NOT NULL,
    position int NOT NULL
);
CREATE INDEX choices_question_id_idx ON choices (question_id);

CREATE TABLE responses (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    survey_id uuid NOT NULL,
    respondent_email text,
    submitted_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX responses_survey_id_idx ON responses (survey_id);

CREATE TABLE answers (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    response_id uuid NOT NULL,
    question_id uuid NOT NULL,
    text_value text,
    choice_id uuid,
    number_value numeric
);
CREATE INDEX answers_response_idx ON answers (response_id);
CREATE INDEX answers_question_id_idx ON answers (question_id);
CREATE INDEX answers_choice_id_idx ON answers (choice_id);

ALTER TABLE surveys ADD FOREIGN KEY (owner_id) REFERENCES owners (id) ON DELETE CASCADE;

ALTER TABLE questions ADD FOREIGN KEY (survey_id) REFERENCES surveys (id) ON DELETE CASCADE;

ALTER TABLE choices ADD FOREIGN KEY (question_id) REFERENCES questions (id) ON DELETE CASCADE;

ALTER TABLE responses ADD FOREIGN KEY (survey_id) REFERENCES surveys (id) ON DELETE CASCADE;

ALTER TABLE answers ADD FOREIGN KEY (response_id) REFERENCES responses (id) ON DELETE CASCADE;

ALTER TABLE answers ADD FOREIGN KEY (question_id) REFERENCES questions (id) ON DELETE CASCADE;

ALTER TABLE answers ADD FOREIGN KEY (choice_id) REFERENCES choices (id) ON DELETE SET NULL;