-- API for survey objects
select define_function_args('survey__new','survey_id,name,description,description_html_p;f,single_response_p;f,editable_p;f,enabled_p;f,single_section_p;t,type;general,display_type,package_id,public_p,creation_user,context_id');

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

select define_function_args('survey__remove','survey_id');

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
select define_function_args('survey_section__new','section_id,survey_id,name,description,description_html_p;f,sort_key;0,branch_p;f,branched_p;f,block_section_p;f,page_break_p;t,creation_user,context_id,pretty_id');
create or replace function survey_section__new (integer,integer,varchar,text,boolean,integer,boolean,boolean,boolean,boolean,integer,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
  new__pretty_id                alias for $13;
  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,pretty_id)
    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, new__pretty_id);

    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
    delete from survey_branches where section_id=remove__section_id;
    delete from survey_conditions where question_id in (select question_id from survey_questions where section_id=remove__section_id);
    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';


select define_function_args('survey_question__new','question_id,section_id,sort_order,question_text,abstract_data_type,required_p;t,active_p,presentation_type,presentation_options,presentation_alignment;below,question_html_p;f,summary_type,answer_description,predefined_qustion_id,creation_user,context_id,pretty_id');
create or replace function survey_question__new (integer,integer,integer,text,varchar,boolean,boolean,varchar,varchar,varchar,boolean,varchar,varchar,integer,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
  new__pretty_id                alias for $17; 
  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,pretty_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,new__pretty_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' stable;

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

-- 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
--    delete from survey_branches where section_id in (select section_id from survey_sections where survey_id=remove.survey_id);
--    delete from survey_conditions where question_id in (select question_id from survey_questions q, survey_sections s where s.section_id=q.section_id and s.survey_id=remove.survey_id);
    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,
       o2.creation_user as initial_user_id
  from survey_responses sr 
  join acs_objects o
    on (sr.response_id = o.object_id)
  join acs_objects o2
    on (coalesce(sr.initial_response_id, sr.response_id) = o2.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;
