begin

	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_predefined_question',
		pretty_name => 'Predefined Survey Question',
		pretty_plural => 'Predefined Survey Questions',
		table_name => 'SURVEY_PREDEFINED_QUESTIONS',
		id_column => 'PREDEFINED_QUESTION_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');

alter table surveys add (public_p char(1) default 't'
				constraint surveys_public_p_ck
				check(public_p in ('t','f')));

alter table surveys add (status_bar_color varchar(100) default null
				constraint surveys_status_bar_colors
				check(status_bar_color in ('blue','red','green','yellow')));

alter table surveys add (template integer references survey_templates(template_id));

alter table survey_sections add (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')));
alter table survey_sections add	(branched_p		char(1) default 'f'
				constraint survey_sections_branched_p_ck
				check (branched_p in ('t','f')));
alter table survey_sections add	(block_section_p		char(1) default 'f'
                                constraint survey_sections_block_p_ck
                                check (block_section_p in ('t','f')));
alter table survey_sections add	(page_break_p		char(1) default 't'
                                constraint survey_sections_break_p_ck
                                check (page_break_p in ('t','f')));

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

alter table survey_questions add (question_html_p		char(1) default 'f'
                                constraint survey_q_question_html_p_ck
				check(question_html_p in ('t','f')));
alter table survey_questions add (summary_type		varchar(20)
				constraint survey_q_summary_type check
				(summary_type in ('sum','avg','concat')));
alter table survey_questions add (answer_description	varchar(100),
	predefined_question_id	constraint survey_q_pred_q_id_fk
				references survey_predefined_questions);
create table survey_block_questions (
	block_section_id 	integer,
	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;


alter table survey_question_choices add (predef_choice_id constraint survey_qc_predef_choice_id_fk
			references survey_predef_question_choices);
alter table survey_question_choices add (presentation_alignment	varchar(15) default 'right'
			constraint survey_qc_pres_align_ck
			check (presentation_alignment in ('left','right')));
alter table survey_question_choices add (more_info_type	varchar(30) default ''
			constraint survey_qc_more_info_ck
			check (more_info_type in ('','varchar','number')));

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

alter table survey_responses add (finished_p		char(1) default 'f'
				constraint survey_resp_finished_p_ck
				check (finished_p in ('t','f')));

alter table survey_question_responses add (attachment_file_name    varchar(500));
alter table survey_question_responses add (attachment_file_type    varchar(100));
alter table survey_question_responses add (attachment_file_extension varchar(50),
	constraint surv_quest_resp_uq unique(response_id,question_id,choice_id));

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