-- ported to OpenACS 4 by Gilbert Wong (gwong@orchardlabs.com) on 2001-05-20
--
-- 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.2.1 2004/09/02 15:17:47 maltes Exp $



begin;
        -- temporarily drop this trigger to avoid a data-change violation
        -- on acs_privilege_hierarchy_index while updating the child privileges.
--         drop trigger acs_priv_hier_ins_del_tr on acs_privilege_hierarchy;

--         -- re-enable the trigger before the last insert to force the
--         -- acs_privilege_hierarchy_index table to be updated.

--         create trigger acs_priv_hier_ins_del_tr after insert or delete
--         on acs_privilege_hierarchy for each row
--         execute procedure acs_priv_hier_ins_del_tr ();

commit;



  select acs_object_type__create_type (
    'survey',
    'Survey',
    'Surveys',
    'acs_object',
    'surveys',
    'survey_id',
    null,
    'f',
    null,
    'survey__name'
   );

  select acs_object_type__create_type (
    'survey_section',
    'Survey Section',
    'Survey Sections',
    'acs_object',
    'survey_sections',
    'section_id',
    null,
    'f',
    null,
    null
   );

  select acs_object_type__create_type (
    'survey_predefined_question',
    'Predefined Survey Question',
    'Predefined Survey Questions',
    'acs_object',
    'survey_predefined_questions',
    'predefined_question_id',
    null,
    'f',
    null,
    null
  );

  select acs_object_type__create_type (
    'survey_question',
    'Survey Question',
    'Survey Questions',
    'acs_object',
    'survey_questions',
    'question_id',
    null,
    'f',
    null,
    null
  );

  select acs_object_type__create_type (
    'survey_response',
    'Survey Response',
    'Survey Responses',
    'acs_object',
    'survey_responses',
    'response_id',
    null,
    'f',
    null,
    null
  );


create table survey_templates (
-- until we got a better solution
	template_id	integer 
			constraint survey_templates_pk
			primary key,
	template_file  	varchar(400)
			constraint survey_templates_tpl_file_un
			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               integer 
				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             text
                                constraint surveys_desc_nn
                                not null,
        description_html_p      boolean not null,
        enabled_p               boolean not null,
        -- limit to one response per user
        single_response_p       boolean not null,
        editable_p              boolean not null,
	single_section_p	boolean not null,
        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		boolean default TRUE,
	status_bar_color	varchar(100) default null
				constraint surveys_status_bar_colors
				check(status_bar_color in ('blue','red','green','yellow')),
	template		integer
				constraint surveys_template_fk
				references survey_templates(template_id)
);
         

create table survey_sections (
	section_id		integer 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		text 
				constraint survey_sections_desc_nn
				not null,
        description_html_p      boolean,
        pretty_id               varchar(100) constraint survey_sections_pretty_id_un
			        unique (pretty_id, section_id)
	sort_key		integer default 0 not null,
	branch_p		boolean default FALSE,
	-- are there any branches dependant from this section?
	branched_p		boolean default FALSE,
	block_section_p		boolean default FALSE,
	page_break_p		boolean default TRUE
);

create index survey_sections_survey_id_fk on survey_sections(survey_id);

create table survey_predefined_questions (
	predefined_question_id	integer
				constraint survey_pq_pquestion_id_fk
                                references acs_objects (object_id)
                                constraint survey_pq_pquestion_id_pk
                                primary key,
	question_text		text
				constraint survey_pq_question_text_nn
				not null,
	question_html_p		boolean default FALSE,
        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			text,
	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_pk
                        primary key,
        question_id     integer
			constraint survey_pqc_question_id_nn
                        not null
                        constraint survey_pqc_question_id_fk
                        references survey_predefined_questions(predefined_question_id),
        -- human readable
        label           varchar(500) 
			constraint survey_pqc_label_nn
                        not null,
        -- might be useful for averaging or whatever, generally null
        numeric_value   numeric,
	-- 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             integer
				constraint survey_q_question_id_fk
                                references acs_objects (object_id)
                                on delete cascade
                                constraint survey_q_question_id_pk
                                primary key,
        section_id              integer
				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           text
                                constraint survey_q_question_text_nn
                                not null,
	question_html_p		boolean default FALSE,
        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              boolean,
        active_p                boolean,
        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),
        pretty_id               varchar(100) constraint survey_q_pretty_id_un
			        unique (pretty_id, section_id),
	predefined_question_id	integer
				constraint survey_q_pred_q_id_fk
				references survey_predefined_questions
);

create index survey_q_sort_order on survey_questions(sort_order);
create index survey_q_active_p on survey_questions(active_p);

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      text
);

create table survey_block_questions (
	block_section_id 	integer 
                                constraint survey_b_q_section_id_fk
                                references survey_block_sections,
	choice_id 		integer
				constraint survey_b_q_choice_id_nn
				not null,
	label 			text,
        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;

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

create sequence survey_choice_id_sequence;

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 integer
			constraint survey_qc_predef_choice_id_fk
			references survey_predef_question_choices(choice_id),
        question_id     integer
			constraint survey_qc_question_id_nn
                        not null 
                        constraint survey_qc_question_id_fk
                        references survey_questions
                        on delete cascade,
        -- human readable 
        label           text
			constraint survey_qc_label_nn
                        not null,
        -- might be useful for averaging or whatever, generally null
        numeric_value   numeric,
        -- 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 1;
create sequence survey_condition_id_sequence start 1;

create table survey_conditions (
       condition_id             integer
       				constraint survey_conditions_pk
				primary key,
       question_id              integer
				constraint survey_conditions_question_id_nn
				not null
				constraint survey_conditions_question_id_fk
				references survey_questions,
       -- like question_responses
       choice_id 		integer
       				constraint survey_conditions_choice_id_fk
				references survey_question_choices,
       boolean_answer           char(1),
                                constraint survey_c_boolean_answer_ck
				check(boolean_answer in ('t','f'))
);

create table survey_branches (
       branch_id               integer
       			       constraint survey_branches_pk
			       primary key,
       section_id              integer
       			       constraint survey_branches_section_id_fk
			       references survey_sections,
       after                   integer
       			       constraint survey_branches_after_nn
			       not null
			       constraint survey_branches_after_fk
			       references survey_sections,
       -- null means always
       condition               integer
       			       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             integer
				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     integer
				constraint survey_resp_initial_fk
                                references survey_responses(response_id),
        survey_id              	integer
				constraint survey_resp_survey_id_fk
                                references surveys
                                on delete cascade,
        title                   varchar(100),
        notify_on_comment_p     boolean,
	finished_p		boolean default FALSE
);

-- 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             integer
				constraint survey_qr_response_id_nn
                                not null 
                                constraint survey_qr_response_id_fk
                                references survey_responses
                                on delete cascade,
        question_id             integer
				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               integer
				constraint survey_qr_choice_id_fk
                                references survey_question_choices
                                on delete cascade,
        boolean_answer          boolean,
        clob_answer             text,
        number_answer           numeric,
        varchar_answer          text,
        date_answer             timestamptz,
	attachment_answer       integer
				constraint survey_q_response_item_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_q_r_choice_id on survey_question_responses(choice_id);
create index survey_q_r_attachment_answer on survey_question_responses(attachment_answer);
create index survey_response_index on survey_question_responses (response_id, question_id);

create or replace function survey_question_all_choices
(integer)
returns text as '
declare
	v_answers		text;
	first_p			integer;
	choice_cursor   	RECORD;
	v_question_id		alias for $1;	
begin
	v_answers := '''';
	first_p := 0;

	for choice_cursor in
		SELECT cast (label as text)
	    	from survey_question_choices
	    	where question_id = v_question_id
	    	order by sort_order
	loop
	   if first_p = 0 then
	     v_answers := choice_cursor.label::text;
	     first_p := 1;
     	   else
	     v_answers := v_answers || '','' || choice_cursor.label::text;
	   end if;
	end loop;

	return v_answers;
end' language 'plpgsql';


\i survey-package-create.sql
\i survey-notifications-init.sql

