--Create a pretty_id we can use to name variables in a report.

alter table survey_questions add pretty_id varchar(100);

--Needs to be unique
alter table survey_questions add  constraint survey_q_pretty_id_un
			      unique (pretty_id, section_id);

--Since the prety_id is only unique for a section each section needs a pretty_id
alter table survey_sections add pretty_id varchar(100);

alter table survey_sections add  constraint survey_sections_pretty_id_un
			      unique (pretty_id, section_id);


-- API for survey_section objects

--Create a new survey with a pretty_id

create or replace function survey_section__new (integer,integer,varchar,text,boolean,integer,boolean,boolean,boolean,boolean,integer,integer,text)
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;  -- default section_id
  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';

--Have the old API default to using the section_id

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

    return v_section_id;

end;' language 'plpgsql';

--API for a question with a pretty_id passed in.

create or replace function survey_question__new (integer,integer,integer,text,varchar,boolean,boolean,varchar,varchar,varchar,boolean,varchar,varchar,integer,integer,integer,text)
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';

--Default the old api to use question_id

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 f
  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, 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, v_question_id);

    return v_question_id;

end;' language 'plpgsql';