-- based on student work from 6.916 in Fall 1999
-- which was in turn based on problem set 4
-- in http://photo.net/teaching/one-term-web.html
--
-- by philg@mit.edu and raj@alum.mit.edu on February 9, 2000
-- converted to ACS 4.0 by nstrug@arsdigita.com on 29th September 2000
-- modified for dotLRN/OpenACS4.5 and renamed from "simple-survey" to "survey" 
--    by dave@thedesignexperience.org on 13 July 2002
--
-- $Id: survey-create.sql,v 1.3 2005/01/13 13:54:39 jeffd Exp $

begin

-- 	acs_privilege.create_privilege('survey_create_survey');
-- 	acs_privilege.create_privilege('survey_modify_survey');
-- 	acs_privilege.create_privilege('survey_delete_survey');
-- 	acs_privilege.create_privilege('survey_create_question');
-- 	acs_privilege.create_privilege('survey_modify_question');
-- 	acs_privilege.create_privilege('survey_delete_question');
-- 	acs_privilege.create_privilege('survey_take_survey');

-- 	acs_privilege.create_privilege('survey_admin_survey');

-- 	acs_privilege.add_child('survey_admin_survey','survey_create_survey');
-- 	acs_privilege.add_child('survey_admin_survey','survey_modify_survey');
-- 	acs_privilege.add_child('survey_admin_survey','survey_delete_survey');
-- 	acs_privilege.add_child('survey_admin_survey','survey_create_question');
-- 	acs_privilege.add_child('survey_admin_survey','survey_modify_question');
-- 	acs_privilege.add_child('survey_admin_survey','survey_delete_question');

-- 	acs_privilege.create_privilege('survey_create_section');
-- 	acs_privilege.create_privilege('survey_modify_section');
-- 	acs_privilege.create_privilege('survey_delete_section');

-- 	acs_privilege.add_child('survey_admin_survey','survey_create_section');
-- 	acs_privilege.add_child('survey_admin_survey','survey_modify_section');
-- 	acs_privilege.add_child('survey_admin_survey','survey_delete_section');

-- 	acs_privilege.add_child('write','survey_take_survey');
-- 	acs_privilege.add_child('admin','survey_admin_survey');

	acs_object_type.create_type (
		supertype => 'acs_object',
		object_type => 'survey',
		pretty_name => 'Survey',
		pretty_plural => 'Surveys',
		table_name => 'SURVEYS',
		id_column => 'SURVEY_ID',
		name_method => 'survey.name'
	);
	acs_object_type.create_type (
		supertype => 'acs_object',
		object_type => 'survey_section',
		pretty_name => 'Survey_Section',
		pretty_plural => 'Survey_Sections',
		table_name => 'SURVEY_SECTIONS',
		id_column => 'SECTION_ID'
	);

	acs_object_type.create_type (
		supertype => 'acs_object',
		object_type => 'survey_predefined_question',
		pretty_name => 'Predefined Survey Question',
		pretty_plural => 'Predefined Survey Questions',
		table_name => 'SURVEY_PREDEFINED_QUESTIONS',
		id_column => 'PREDEFINED_QUESTION_ID'
	);

	acs_object_type.create_type (
		supertype => 'acs_object',
		object_type => 'survey_question',
		pretty_name => 'Survey Question',
		pretty_plural => 'Survey Questions',
		table_name => 'SURVEY_QUESTIONS',
		id_column => 'QUESTION_ID'
	);

	acs_object_type.create_type (
		supertype => 'acs_object',
		object_type => 'survey_response',
		pretty_name => 'Survey Response',
		pretty_plural => 'Survey Responses',
		table_name => 'SURVEY_RESPONSES',
		id_column => 'RESPONSE_ID'
	);

end;
/
show errors

create table survey_templates (
-- until we got a better solution
	template_id	integer primary key,
	template_file  	varchar(400) unique,
	description	varchar(4000)
);

insert into survey_templates(template_id,template_file,description) values (acs_object_id_seq.nextval,'plain','Plain');
insert into survey_templates(template_id,template_file,description) values (acs_object_id_seq.nextval,'standard','Standard Blue');
insert into survey_templates(template_id,template_file,description) values (acs_object_id_seq.nextval,'standard-lars','Silver');
insert into survey_templates(template_id,template_file,description) values (acs_object_id_seq.nextval,'two-column','Two Column');


create table surveys (
	survey_id		constraint surveys_survey_id_fk
				references acs_objects (object_id)
				on delete cascade
                                constraint surveys_pk
				primary key,
	name			varchar(4000)
				constraint surveys_name_nn
				not null,
	description		varchar(4000),
        description_html_p      char(1)
                                constraint surveys_desc_html_p_ck
				check(description_html_p in ('t','f')),
	enabled_p               char(1)
				constraint surveys_enabled_p_ck
				check(enabled_p in ('t','f')),
	-- limit to one response per user
	single_response_p	char(1)
				constraint surveys_single_resp_p_ck
				check(single_response_p in ('t','f')),
	editable_p		char(1)
				constraint surveys_single_edit_p_ck
				check(editable_p in ('t','f')),
        single_section_p        char(1)
				constraint surveys_single_section_p_ck
				check(single_section_p in ('t','f')),
	type                    varchar(20),
        display_type            varchar(20),
        package_id              integer
                                constraint surveys_package_id_nn not null
                                constraint surveys_package_id_fk references
                                apm_packages (package_id) on delete cascade,
	public_p		char(1) default 't'
				constraint surveys_public_p_ck
				check(public_p in ('t','f')),
	status_bar_color	varchar(100) default null
				constraint surveys_status_bar_colors
				check(status_bar_color in ('blue','red','green','yellow')),
	template		integer references survey_templates(template_id)
);

create table survey_sections (
	section_id		constraint survey_sections_section_id_fk
				references acs_objects (object_id)
                                constraint survey_sections_pk
				primary key,
	survey_id		integer
				constraint survey_sections_survey_id_nn
				not null
				constraint survey_sections_survey_id_fk
				references surveys,
	name			varchar(4000)
				constraint survey_sections_name_nn
				not null,
	description		clob,
        description_html_p      char(1)
                                constraint survey_sections_desc_html_p_ck
				check(description_html_p in ('t','f')),
	sort_key		integer default 0 not null,
	branch_p		char(1) default 'f'
				constraint survey_sections_branch_p_ck
				check (branch_p in ('t','f')),
	-- are there any branches dependant from this section?
	branched_p		char(1) default 'f'
				constraint survey_sections_branched_p_ck
				check (branched_p in ('t','f')),
	block_section_p		char(1) default 'f'
                                constraint survey_sections_block_p_ck
                                check (block_section_p in ('t','f')),
	page_break_p		char(1) default 't'
                                constraint survey_sections_break_p_ck
                                check (page_break_p in ('t','f'))
);

create index survey_sections_survey_id_fk on survey_sections(survey_id);

create table survey_predefined_questions (
	predefined_question_id	constraint survey_pq_pquestion_id_fk
                                references acs_objects (object_id)
                                constraint survey_pq_pquestion_id_pk
                                primary key,
	question_text		clob
				constraint survey_pq_question_text_nn
				not null,
	question_html_p		char(1) default 'f'
                                constraint survey_pq_question_html_p_ck
				check(question_html_p in ('t','f')),
        abstract_data_type      varchar(30)
				constraint survey_pq_abs_data_type_ck
				check (abstract_data_type in ('text', 'shorttext', 'boolean', 'number', 'integer', 'choice', 'date','blob')),
	presentation_type	varchar(20)
				constraint survey_pq_pres_type_nn
				not null
				constraint survey_pq_pres_type_ck
				check(presentation_type in ('textbox','textarea','select','radio', 'checkbox', 'date', 'upload_file')),
	-- for text, "small", "medium", "large" sizes
	-- for textarea, "rows=X cols=X"
	presentation_options	varchar(50),
	presentation_alignment	varchar(15)
				constraint survey_pq_pres_alignment_ck
            			check(presentation_alignment in ('below','beside')),
	summary_type		varchar(20)
				constraint survey_pq_summary_type check
				(summary_type in ('sum','avg','concat')),
	action_type		varchar(20)
                                constraint survey_pq_action_type_ck
                                check(action_type in ('tcl','db')),
	tcl			clob,
	table_name		varchar(100),
	column_name		varchar(100),
	key_name		varchar(100)
	);
 

create table survey_predef_question_choices (
	choice_id       integer constraint survey_pqc_choice_id_nn
                        not null
                        constraint survey_pqc_choice_id_pk
                        primary key,
        question_id     constraint survey_pqc_question_id_nn
                        not null
                        constraint survey_pqc_question_id_fk
                        references survey_predefined_questions,
        -- human readable
        label           varchar(500) constraint survey_pqc_label_nn
                        not null,
        -- might be useful for averaging or whatever, generally null
        numeric_value   number,
	-- lower is earlier
        sort_order      integer,
        presentation_alignment  varchar(15) default 'right'
                        constraint survey_pqc_pres_align_ck
                        check (presentation_alignment in ('left','right')),
        more_info_type  varchar(30) default ''
                        constraint survey_pqc_more_info_ck
                        check (more_info_type in ('','varchar','number'))
);


create table survey_questions (
	question_id		constraint survey_q_question_id_fk
				references acs_objects (object_id)
				on delete cascade
				constraint survey_q_question_id_pk
				primary key,
	section_id		constraint survey_q_section_id_fk
				references survey_sections
				on delete cascade,
	sort_order		integer
				constraint survey_q_sort_order_nn
				not null,
	question_text		clob
				constraint survey_q_question_text_nn
				not null,
	question_html_p		char(1) default 'f'
                                constraint survey_q_question_html_p_ck
				check(question_html_p in ('t','f')),
        abstract_data_type      varchar(30)
				constraint survey_q_abs_data_type_ck
				check (abstract_data_type in ('text', 'shorttext', 'boolean', 'number', 'integer', 'choice', 'date','blob')),
	required_p		char(1)
				constraint survey_q_required_p_ck
				check (required_p in ('t','f')),
	active_p		char(1)
				constraint survey_q_qctive_p_ck
				check (active_p in ('t','f')),
	presentation_type	varchar(20)
				constraint survey_q_pres_type_nn
				not null
				constraint survey_q_pres_type_ck
				check(presentation_type in ('textbox','textarea','select','radio', 'checkbox', 'date', 'upload_file')),
	-- for text, "small", "medium", "large" sizes
	-- for textarea, "rows=X cols=X"
	presentation_options	varchar(50),
	presentation_alignment	varchar(15)
				constraint survey_q_pres_alignment_ck
            			check(presentation_alignment in ('below','beside')),
	summary_type		varchar(20)
				constraint survey_q_summary_type check
				(summary_type in ('sum','avg','concat')),
	-- describing the options of radio button set
	answer_description	varchar(100),
	predefined_question_id	constraint survey_q_pred_q_id_fk
				references survey_predefined_questions
);

create table survey_block_sections (
	block_section_id        integer primary key
	section_id              integer
                                constraint survey_b_q_section_id_fk
                                references survey_sections,
	answer_description	varchar(400)
);

create table survey_block_questions (
	block_section_id 	references survey_block_sections,
	section_id		integer 
				constraint survey_b_q_section_id_fk
		   		references survey_sections,
	choice_id 		integer
				constraint survey_b_q_choice_id_nn
				not null,
	label 			varchar(200),
	sort_order 		integer
				constraint survey_b_q_sort_order_nn
				not null
);
create sequence survey_block_section_id_seq;
create sequence survey_block_choice_id_seq;
create index survey_q_sort_order on survey_questions(sort_order);
create index survey_q_active_p on survey_questions(active_p);

-- for when a question has a fixed set of responses

create sequence survey_choice_id_sequence start with 1;

create table survey_question_choices (
        choice_id       integer constraint survey_qc_choice_id_nn
                        not null
                        constraint survey_qc_choice_id_pk
                        primary key,
	predef_choice_id constraint survey_qc_predef_choice_id_fk
			references survey_predef_question_choices,
        question_id     constraint survey_qc_question_id_nn
                        not null
                        constraint survey_qc_question_id_fk
                        references survey_questions
			on delete cascade,
        -- human readable
        label           varchar(500) constraint survey_qc_label_nn
			not null,
        -- might be useful for averaging or whatever, generally null
        numeric_value   number,
        -- lower is earlier
        sort_order      integer,
	presentation_alignment	varchar(15) default 'right'
			constraint survey_qc_pres_align_ck
			check (presentation_alignment in ('left','right')),
	more_info_type	varchar(30) default ''
			constraint survey_qc_more_info_ck
			check (more_info_type in ('','varchar','number'))
);

create index survey_q_c_question_id on survey_question_choices(question_id);
create index survey_q_c_sort_order on survey_question_choices(sort_order);

create sequence survey_branch_id_sequence start with 1;
create sequence survey_condition_id_sequence start with 1;

create table survey_conditions (
       condition_id             integer 
       				constraint survey_cond_pk
       				primary key,
       question_id              constraint survey_cond_question_id_nn
				not null
				constraint survey_cond_question_id_fk
				references survey_questions,
       -- like question_responses
       choice_id 		constraint survey_cond_choice_id_fk
       				references survey_question_choices,
       boolean_answer           char(1)
                                constraint survey_cond_boolean_answer_ck
				check(boolean_answer in ('t','f'))
);

create table survey_branches (
       branch_id               integer
       			       constraint survey_branches_pk
			       primary key,
       section_id              constraint survey_branches_section_id_fk
			       references survey_sections,
       after                   constraint survey_branches_after_nn
			       not null
			       constraint survey_branches_after_fk
			       references survey_sections,
       -- null means always
       condition               constraint survey_branches_condition_fk
			       references survey_conditions
);


-- this records a response by one user to one survey
-- (could also be a proposal in which case we'll do funny 
--  things like let the user give it a title, send him or her
--  email if someone comments on it, etc.)
create table survey_responses (
	response_id		constraint survey_resp_response_id_fk
				references acs_objects (object_id)
				on delete cascade
				constraint survey_resp_response_id_pk
				primary key,
	initial_response_id	constraint init_resp_id_fk
				references survey_responses (response_id),
	survey_id		constraint survey_resp_survey_id_fk
				references surveys
				on delete cascade,
	title			varchar(100),
	notify_on_comment_p	char(1)
				constraint survey_resp_noton_com_p_ck
				check(notify_on_comment_p in ('t','f')),
	finished_p		char(1) default 'f'
				constraint survey_resp_finished_p_ck
				check (finished_p in ('t','f'))
);



-- this table stores the answers to each question for a survey
-- we want to be able to hold different data types in one long skinny table 
-- but we also may want to do averages, etc., so we can't just use CLOBs

create table survey_question_responses (
	response_id		constraint survey_qr_response_id_nn
				not null
				constraint survey_qr_response_id_fk
				references survey_responses,
	question_id		constraint survey_qr_question_id_nn
				not null
				constraint survey_qr_question_id_fk
				references survey_questions
				on delete cascade,
	-- if the user picked a canned response
	choice_id		constraint survey_qr_choice_id_fk
                                references survey_question_choices
                                on delete cascade,
	boolean_answer		char(1) check(boolean_answer in ('t','f')),
	clob_answer		clob,
	number_answer		number,
	varchar_answer		varchar(4000),
	date_answer		date,
	attachment_answer	integer
				constraint survey_q_resp_rev_id_fk
                                references cr_revisions(revision_id)
                                on delete cascade,
        -- file name including extension but not path
        attachment_file_name    varchar(500),
        attachment_file_type    varchar(100),   -- this is a MIME type (e.g., image/jpeg)
        attachment_file_extension varchar(50),   -- e.g., "jpg"
	constraint surv_quest_resp_uq unique(response_id,question_id,choice_id)
);
	
create index survey_response_index on survey_question_responses (response_id, question_id);
create index survey_q_r_choice_id on survey_question_responses(choice_id);
create index survey_q_r_attachment_answer on survey_question_responses(attachment_answer);


create or replace function survey_question_all_choices
(v_question_id in integer) return varchar
is
	v_answers varchar(4000);
	first_p integer;
	cursor choice_cursor is
	select label
	from survey_question_choices
	where question_id = v_question_id
	order by sort_order;
begin
	v_answers := '';
	first_p := 0;
	for choice_rec in choice_cursor loop
	   if first_p = 0 then
	     v_answers := choice_rec.label;
	     first_p := 1;
     	   else
	     v_answers := v_answers || ',' || choice_rec.label;
	   end if;
	end loop;

	return v_answers;
end;
/
show errors

@@ survey-package-create.sql
@@ survey-notifications-init.sql
@@ survey-portlet-create.sql