survey to support multiple question type

insertData.sql

BEGIN; -- Start of the transaction
-- insert data
-- Dummy Organization
INSERT INTO organizations (id,organization_name) VALUES (1,'Tennis Survey Organization');

-- Dummy Survey Header, assuming organization_id = 1
INSERT INTO survey_headers (id,organization_id, survey_name, instructions) VALUES (1,1, 'Tennis Partner Survey', 'Please complete this survey to find your ideal tennis partner.');

-- Input Types
-- Assume these are pre-inserted or insert them if not
-- Example: INSERT INTO input_types (input_type_name) VALUES ('Info'), ('TextInput'), ('NumericInput'), ('SelectionGroup'), ('MultipleSelectionGroup');

-- Dummy Survey Section, assuming survey_header_id = 1
INSERT INTO survey_sections (id,survey_header_id, section_name, section_title, section_required) VALUES (1,1, 'Main Section', 'Survey Questions', TRUE);

-- Insert input types
INSERT INTO input_types (id,input_type_name) VALUES 
(1,'Info'), 
(2,'TextInput'), 
(3,'NumericInput'), 
(4,'SelectionGroup'), 
(5,'MultipleSelectionGroup');

-- Assuming IDs for input_types
-- Info: 1, TextInput: 2, NumericInput: 3, SelectionGroup: 4, MultipleSelectionGroup: 5

-- Insert option groups
INSERT INTO option_groups (id,option_group_name) VALUES 
(1,'Playing Level'),
(2,'Favorite Tennis Players'),
(3,'Looking For');

-- Assuming IDs for option_groups
-- Playing Level: 1, Favorite Tennis Players: 2, Looking For: 3

-- Insert option choices for Playing Level with descriptions and sort_order
INSERT INTO option_choices (id, option_group_id, option_choice_name, description, sort_order) VALUES 
(1, 1, 'Beginner (NTRP 2.5)', 'Just getting started in the game', 1),
(2, 1, 'Advanced Beginner (NTRP 3.0)', 'I can rally but my strokes are not consistent yet', 2),
(3, 1, 'Intermediate (NTRP 3.5)', 'I can hit with spin and direction most of the time', 3),
(4, 1, 'Advanced (NTRP 4.0)', 'I can consistently rally with spin, direction, and pace', 4),
(5, 1, 'Advanced Plus (NTRP 4.5)', 'I have control over my shots and hit consistently with depth and pace', 5),
(6, 1, 'Expert (NTRP 5.0)', 'I have competitive experience and advanced skill levels', 6);

-- Insert option choices for Favorite Tennis Players with descriptions and sort_order
INSERT INTO option_choices (id, option_group_id, option_choice_name, description, sort_order) VALUES 
(7, 2, 'Roger Federer', 'A legendary player known for his precision and grace', 1),
(8, 2, 'Serena Williams', 'Famed for her powerful serve and determination', 2),
(9, 2, 'Novak Djokovic', 'Renowned for his exceptional agility and endurance', 3),
(10, 2, 'Naomi Osaka', 'Admired for her powerful gameplay and strategic acumen', 4),
(11, 2, 'Rafael Nadal', 'Celebrated for his dominance on clay courts and fighting spirit', 5);

-- Insert option choices for Looking For with descriptions and sort_order
INSERT INTO option_choices (id, option_group_id, option_choice_name, description, sort_order) VALUES 
(12, 3, 'Fun / social', 'Looking to enjoy the game and meet new people', 1),
(13, 3, 'Casual hitting', 'Interested in playing tennis casually without competitive pressure', 2),
(14, 3, 'Friendly competition', 'Seeking a bit of competition while having fun', 3),
(15, 3, 'High level competition', 'Aiming for serious, competitive matches', 4);

-- Assuming IDs for option_choices for Looking For starting from 12 to 15

-- Insert questions (assuming survey_section_id is 1 for all questions)
INSERT INTO questions (id,survey_section_id, input_type_id, question_text, answer_required, option_group_id) VALUES 
(1,1, 2, 'What is your name?', TRUE, NULL),
(2,1, 3, 'Location', TRUE, NULL),
(3,1, 4, 'Choose your level?', TRUE, 1),
(4,1, 5, 'Select two or three of your favorite tennis players!', TRUE, 2),
(5,1, 5, 'You are looking for...', TRUE, 3);

-- Assuming IDs for questions starting from 1 to 5

-- Insert question options for the 'Choose your level?' question
-- Assuming the question ID for 'Choose your level?' is 3
INSERT INTO question_options (id,question_id, option_choice_id) VALUES 
(1,3, 1),
(2,3, 2),
(3,3, 3),
(4,3, 4),
(5,3, 5),
(6,3, 6);

-- Insert question options for 'Select two or three of your favorite tennis players'
-- Assuming the question ID for this question is 4
INSERT INTO question_options (question_id, option_choice_id) VALUES 
(4, 7),
(4, 8),
(4, 9),
(4, 10),
(4, 11);

-- Insert question options for 'You are looking for...'
-- Assuming the question ID for this question is 5
INSERT INTO question_options (question_id, option_choice_id) VALUES 
(5, 12),
(5, 13),
(5, 14),
(5, 15);

COMMIT; -- End of the transaction

survey.sql

-- Organizations Table
CREATE TABLE organizations (
    id SERIAL PRIMARY KEY,
    organization_name VARCHAR(80) NOT NULL
);

-- Creating an index on the organization_name as suggested by the ER diagram.
CREATE INDEX idx_organizations_name ON organizations(organization_name);
-- Create ENUM type for survey status
CREATE TYPE survey_status AS ENUM ('draft', 'published', 'archived');

-- Survey Headers Table
CREATE TABLE survey_headers (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id),
    survey_name VARCHAR(80) NOT NULL,
    instructions TEXT,
    other_header_info VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    status survey_status DEFAULT 'draft'
);

-- Input Types Table
CREATE TABLE input_types (
    id SERIAL PRIMARY KEY,
    input_type_name VARCHAR(80) NOT NULL
);

-- Creating an index on the input_type_name as suggested by the ER diagram.
CREATE INDEX idx_input_types_name ON input_types(input_type_name);

-- Option Groups Table
CREATE TABLE option_groups (
    id SERIAL PRIMARY KEY,
    option_group_name VARCHAR(45) NOT NULL
);

-- Survey Sections Table
CREATE TABLE survey_sections (
    id SERIAL PRIMARY KEY,
    survey_header_id INTEGER NOT NULL REFERENCES survey_headers(id),
    section_name VARCHAR(80) NOT NULL,
    section_title VARCHAR(45),
    section_subheading VARCHAR(45),
    section_required BOOLEAN NOT NULL,
    sort_order INTEGER
);

-- Questions Table
CREATE TABLE questions (
    id SERIAL PRIMARY KEY,
    survey_section_id INTEGER NOT NULL REFERENCES survey_sections(id),
    input_type_id INTEGER NOT NULL REFERENCES input_types(id),
    question_subtext VARCHAR(500),
    question_text VARCHAR(500) NOT NULL,
    answer_required BOOLEAN NOT NULL,
    option_group_id INTEGER REFERENCES option_groups(id),
    allow_multiple_option_answers BOOLEAN,
    sort_order INTEGER
);

-- Option Choices Table
CREATE TABLE option_choices (
    id SERIAL PRIMARY KEY,
    option_group_id INTEGER NOT NULL REFERENCES option_groups(id),
    option_choice_name VARCHAR(45) NOT NULL,
    description TEXT, -- Added description column for option choices
    sort_order INTEGER -- Added sort_order column to define the display order of the options
);


-- Question Options Table
CREATE TABLE question_options (
    id SERIAL PRIMARY KEY,
    question_id INTEGER NOT NULL REFERENCES questions(id),
    option_choice_id INTEGER NOT NULL REFERENCES option_choices(id)
);


-- User Survey Sections Table
CREATE TABLE user_survey_sections (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    survey_section_id INTEGER NOT NULL REFERENCES survey_sections(id),
    completed_on TIMESTAMP
);

-- Survey Comments Table
CREATE TABLE survey_comments (
    id SERIAL PRIMARY KEY,
    survey_header_id INTEGER NOT NULL REFERENCES survey_headers(id),
    user_id INTEGER NOT NULL REFERENCES users(id),
    comments TEXT
);

-- Unit of Measures Table
CREATE TABLE unit_of_measures (
    id SERIAL PRIMARY KEY,
    unit_of_measures_name VARCHAR(80) NOT NULL
);

-- Creating an index on the unit_of_measures_name as suggested by the ER diagram.
CREATE INDEX idx_unit_of_measures_name ON unit_of_measures(unit_of_measures_name);

-- Answers Table
CREATE TABLE answers (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    question_option_id INTEGER NOT NULL REFERENCES question_options(id),
    answer_numeric INTEGER,
    answer_text VARCHAR(255),
    answer_boolean BOOLEAN,
    question_id INTEGER REFERENCES questions(id),
    unit_of_measure_id INTEGER REFERENCES unit_of_measures(id), 
    answer_date DATE,
    answer_datetime TIMESTAMP,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    answer_file VARCHAR(255)
);