-- API for survey objects

create or replace function survey__new (integer,varchar,text,boolean,boolean,boolean,boolean,boolean,varchar,varchar,integer,boolean,integer,integer)
returns integer as '
declare
  new__survey_id                alias for $1;  -- default null
  new__name                     alias for $2;
  new__description              alias for $3;
  new__description_html_p       alias for $4;  -- default f
  new__single_response_p        alias for $5;  -- default f
  new__editable_p               alias for $6;  -- default t
  new__enabled_p                alias for $7;  -- default f
  new__single_section_p         alias for $8;  -- default t
  new__type                     alias for $9;  -- default general
  new__display_type             alias for $10;
  new__package_id               alias for $11;
  new__public_p			alias for $12;  -- default t
  new__creation_user            alias for $13; -- default null
  new__context_id               alias for $14; -- default null
  v_survey_id                   integer;
begin
    v_survey_id := acs_object__new (
        new__survey_id,
        ''survey'',
        now(),
        new__creation_user,
        null,
        new__context_id
    );

    insert into surveys
    (survey_id, name, description, 
    description_html_p, single_response_p, editable_p, 
    enabled_p, single_section_p, type, display_type, package_id, public_p)
    values
    (v_survey_id, new__name, new__description, 
    new__description_html_p, new__single_response_p, new__editable_p, 
    new__enabled_p, new__single_section_p, new__type, new__display_type, new__package_id, new__public_p);

    return v_survey_id;

end;' language 'plpgsql';

create or replace function survey__remove (integer)
returns integer as '
declare
  remove__survey_id             alias for $1;
  v_response_row                survey_responses%ROWTYPE;
  v_section_row                 survey_sections%ROWTYPE;
begin

    for v_response_row in SELECT
		response_id	
               from survey_responses
               where survey_id=remove__survey_id
               and initial_response_id is NULL
    loop
	PERFORM survey_response__remove(v_response_row.response_id);
    end loop;

    for v_section_row in select section_id
		from survey_sections
		where survey_id=remove__survey_id
    loop
	PERFORM survey_section__remove(v_section_row.section_id);
    end loop;

    delete from surveys
    where survey_id = remove__survey_id;

    PERFORM acs_object__delete(remove__survey_id);

    return 0;

end;' language 'plpgsql';

create or replace function survey__name (integer)
returns varchar as '
declare
  name__survey_id		alias for $1;
  v_name			surveys.name%TYPE;
begin
    select into v_name name
    from surveys
    where survey_id = name__survey_id;

    return v_name;
end;' language 'plpgsql';


-- API for survey_section objects

create or replace function survey_section__new (integer,integer,varchar,text,boolean,integer,boolean,boolean,boolean,boolean,integer,integer)
returns integer as '
declare
  new__section_id               alias for $1;  -- default null
  new__survey_id                alias for $2;  -- default null
  new__name                     alias for $3;  -- default null
  new__description              alias for $4;  -- default null
  new__description_html_p       alias for $5;  -- default f
  new__sort_key			alias for $6;  -- default 0
  new__branch_p			alias for $7;  -- default f
  new__branched_p		alias for $8;  -- default f
  new__block_section_p		alias for $9;  -- default f
  new__page_break_p		alias for $10; -- default t
  new__creation_user            alias for $11;  -- default null
  new__context_id               alias for $12;  -- default null
  v_section_id                  integer;
begin
    v_section_id := acs_object__new (
        new__section_id,
        ''survey_section'',
        now(),
        new__creation_user,
        null,
        new__context_id
    );

    insert into survey_sections
    (section_id, survey_id, name, description, description_html_p, sort_key, branch_p, branched_p, page_break_p, block_section_p)
    values
    (v_section_id, new__survey_id, new__name, new__description, new__description_html_p, new__sort_key, new__branch_p, new__branched_p, new__page_break_p, new__block_section_p);

    return v_section_id;

end;' language 'plpgsql';

create or replace function survey_section__remove (integer)
returns integer as '
declare
  remove__section_id             alias for $1;
  v_question_row         	 survey_questions%ROWTYPE;
begin
    for v_question_row in select question_id
		from survey_questions
		where section_id=remove__section_id
    loop
	PERFORM survey_question__remove(v_question_row.question_id);
    end loop;

    delete from survey_sections
    where section_id = remove__section_id;

    PERFORM acs_object__delete(remove__section_id);

    return 0;

end;' language 'plpgsql';

-- API for predefined questions

create or replace function survey_predefined_question__new (integer,text,varchar,varchar,varchar,varchar,boolean,varchar,varchar,text,varchar,varchar,varchar,integer,integer)
returns integer as '
declare
  new__predefined_question_id	alias for $1;	-- default null
  new__question_text 		alias for $2;    -- default null
  new__abstract_data_type	alias for $3;	-- default null
  new__presentation_type	alias for $4;	-- default null
  new__presentation_options	alias for $5;	-- default null
  new__presentation_alignment	alias for $6;	-- default below
  new__question_html_p 		alias for $7;	-- default f
  new__summary_type		alias for $8;
  new__action_type		alias for $9;	-- default null
  new__tcl			alias for $10;	-- default null
  new__table_name		alias for $11;	-- default null
  new__column_name		alias for $12;	-- default null
  new__key_name			alias for $13; 	-- default null
  new__creation_user		alias for $14;	-- default null
  new__context_id 		alias for $15;	-- default null
  v_predefined_question_id	survey_predefined_questions.predefined_question_id%TYPE;
begin
    v_predefined_question_id := acs_object__new (
        new__predefined_question_id,
        ''survey_predefined_question'',
        now(),
        new__creation_user,
        null,
        new__context_id
    );

    insert into survey_predefined_questions
    (predefined_question_id,question_text,abstract_data_type,presentation_type,presentation_options,presentation_alignment,question_html_p,summary_type,action_type,tcl,table_name,column_name,key_name)
    values
    (v_predefined_question_id,new__question_text,new__abstract_data_type,new__presentation_type,new__presentation_options,new__presentation_alignment,new__question_html_p,new__summary_type,new__action_type,new__tcl,new__table_name,new__column_name,new__key_name);
    
    return v_predefined_question_id;

end;' language 'plpgsql';

-- API for Survey questions

create or replace function survey_question__new (integer,integer,integer,text,varchar,boolean,boolean,varchar,varchar,varchar,boolean,varchar,varchar,integer,integer,integer)
returns integer as '
declare
  new__question_id              alias for $1; -- default null
  new__section_id               alias for $2; -- default null
  new__sort_order               alias for $3; -- default null
  new__question_text            alias for $4; -- default null
  new__abstract_data_type       alias for $5; -- default null
  new__required_p               alias for $6; -- default t
  new__active_p                 alias for $7; -- default 
  new__presentation_type        alias for $8; -- default null
  new__presentation_options     alias for $9; -- default null
  new__presentation_alignment   alias for $10; -- default below
  new__question_html_p		alias for $11; -- default f
  new__summary_type		alias for $12;
  new__answer_description	alias for $13;
  new__predefined_question_id	alias for $14; -- default null
  new__creation_user            alias for $15; -- default null
  new__context_id               alias for $16; -- default null
  v_question_id                 integer;
begin
    v_question_id := acs_object__new (
        new__question_id,
        ''survey_question'',
        now(),
        new__creation_user,
        null,
        new__context_id
    );

    insert into survey_questions
    (question_id, section_id, sort_order, question_text, 
    abstract_data_type, required_p, active_p, 
    presentation_type, presentation_options,
    presentation_alignment, predefined_question_id)
    values
    (v_question_id, new__section_id, new__sort_order, new__question_text, 
    new__abstract_data_type, new__required_p, new__active_p, 
    new__presentation_type, new__presentation_options,
    new__presentation_alignment, new__predefined_question_id);

    return v_question_id;

end;' language 'plpgsql';

create or replace function survey_question__remove (integer)
returns integer as '
declare
  remove__question_id           alias for $1;
begin

	delete from survey_question_responses
	    where question_id=remove__question_id;

	delete from survey_question_choices
	    where question_id=remove__question_id;

	delete from survey_questions
            where question_id = remove__question_id;

    PERFORM acs_object__delete(remove__question_id);

    return 0;

end;' language 'plpgsql';



-- API for survey responses

create or replace function survey_response__new(integer,integer,varchar,boolean,integer,varchar,integer,integer)
returns integer as '
declare
  new__response_id              alias for $1; -- default null
  new__survey_id                alias for $2; -- default null
  new__title                    alias for $3; -- default null
  new__notify_on_comment_p      alias for $4; -- default f
  new__creation_user            alias for $5; -- default null
  new__creation_ip              alias for $6; -- default null
  new__context_id               alias for $7; -- default null
  new__initial_response_id      alias for $8; -- default null
  v_response_id                 integer;
begin
    v_response_id := acs_object__new (
        new__response_id,
        ''survey_response'',
        now(),
        new__creation_user,
        new__creation_ip,
        new__context_id
    );

    insert into survey_responses 
    (response_id, survey_id, title, notify_on_comment_p, initial_response_id)
    values
    (v_response_id, new__survey_id, new__title, new__notify_on_comment_p, new__initial_response_id);

    return v_response_id;

end;' language 'plpgsql';

--function initial_response_id
create or replace function survey_response__initial_response_id(integer)
returns integer as '
declare
  p_response_id		alias for $1;
  v_initial_response_id		integer;
begin
  select into v_initial_response_id initial_response_id
  from survey_responses
  where response_id = p_response_id;
  
  if v_initial_response_id is NULL then
	v_initial_response_id := p_response_id;
  end if;
  return v_initial_response_id;
end;' language 'plpgsql';

create or replace function survey_response__initial_user_id (integer)
returns integer as '
declare
p_response_id 	alias for $1;
v_user_id	integer;
begin
	select into v_user_id creation_user
	from acs_objects where
	object_id = survey_response__initial_response_id(p_response_id);
return v_user_id;
end;' language 'plpgsql';

-- procedure delete 
create or replace function survey_response__remove(integer)
returns integer as '
declare
  remove__response_id           alias for $1;
  v_response_row                survey_responses%ROWTYPE;
begin
    for v_response_row in select response_id from survey_responses
	where initial_response_id=remove__response_id
    loop
	PERFORM survey_response__del(v_response_row.response_id);
    end loop;

    PERFORM survey_response__del(remove__response_id);

    return 0;

end;' language 'plpgsql';

create or replace function survey_response__del (integer)
returns integer as '
declare
	del__response_id        alias for $1;
	v_question_response_row survey_question_responses%ROWTYPE;
begin

	for v_question_response_row in select item_id
		from survey_question_responses, cr_revisions
		where response_id=del__response_id
		and attachment_answer=revision_id
	loop
		PERFORM content_item__delete(item_id);
	end loop;
	
	delete from survey_question_responses
		where response_id=del__response_id;
	delete from survey_responses
		where response_id=del__response_id;
	PERFORM acs_object__delete(del__response_id);
    return 0;

end;' language 'plpgsql';

create or replace function survey_response__boolean_answer (varchar,integer)
returns varchar as '
declare
	boolean_answer__answer	      alias for $1;
	boolean_answer__question_id   alias for $2;

	v_answer		      varchar(100);
	v_presentation_options	      survey_questions.presentation_options%TYPE;
	v_split_pos		      integer;
begin
    if boolean_answer__answer is NOT NULL then
        select into v_presentation_options presentation_options 
	from survey_questions where question_id=boolean_answer__question_id;
	
	v_split_pos:= instr(v_presentation_options, ''/'');
	
	if boolean_answer__answer = ''t'' then
	    v_answer:=substr(v_presentation_options, 1, v_split_pos -1 );
	end if;   
	if boolean_answer__answer = ''f'' then
	    v_answer:=substr(v_presentation_options, v_split_pos + 1 );
	end if;
	
    else
        v_answer := '';
    end if;
    return v_answer;

end;' language 'plpgsql';

create view survey_responses_latest as
select sr.*, o.creation_date, 
       o.creation_user,
       survey_response__initial_user_id(sr.response_id) as initial_user_id
  from survey_responses sr 
  join acs_objects o
    on (sr.response_id = o.object_id)
  join (select max(response_id) as response_id
          from survey_responses
         group by survey_response__initial_response_id(response_id)) latest
    on (sr.response_id = latest.response_id);

create view survey_ques_responses_latest as
select qr.*
  from survey_question_responses qr, survey_responses_latest r
 where qr.response_id=r.response_id;