--
-- wp_slide__new/14
--
create or replace function wp_slide__new(
  integer,
  timestamp with time zone,
  integer,
  character varying,
  character varying,
  integer,
  integer,
  integer,
  character varying,
  character varying,
  character varying,
  boolean,
  boolean,
  integer
) returns int4 as $$

declare
    p_pres_item_id              alias for $1; 
    p_creation_date             alias for $2; 
    p_creation_user             alias for $3; 
    p_creation_ip               alias for $4; 
    p_slide_title               alias for $5;
    p_style                     alias for $6;
    p_original_slide_id         alias for $7;
    p_sort_key                  alias for $8;
    p_preamble                  alias for $9;
    p_bullet_items              alias for $10;
    p_postamble                 alias for $11;
    p_include_in_outline_p      alias for $12;
    p_context_break_after_p     alias for $13;
    p_context_id                alias for $14;
    v_item_id                   cr_items.item_id%TYPE;
    v_preamble_item_id          cr_items.item_id%TYPE;
    v_postamble_item_id         cr_items.item_id%TYPE;
    v_bullet_items_item_id      cr_items.item_id%TYPE;
    v_revision_id               cr_revisions.revision_id%TYPE;
    v_preamble_revision_id      cr_revisions.revision_id%TYPE;
    v_postamble_revision_id     cr_revisions.revision_id%TYPE;
    v_bullet_items_revision_id  cr_revisions.revision_id%TYPE;
    v_max_id                    integer;
    v_name                      varchar;
begin
    select coalesce(max(item_id),0) into v_max_id
    from cr_items
    where content_type = 'cr_wp_slide'
    and   name like p_slide_title || '%';
    
    v_name := p_slide_title || '_' || v_max_id;
  
    v_item_id := content_item__new(
        v_name,
        p_pres_item_id,
        null,
        null,
        p_creation_date,
        p_creation_user,
        null,
        p_creation_ip,
        'content_item',
        'cr_wp_slide',      
        null,
        null,
        'text/plain',
        null,
        null,
        'text'
    );

    v_revision_id := content_revision__new(
        null,
        null, 
        current_timestamp,
        'text/plain', 
        null, 
        null,
        v_item_id,
        null,
        p_creation_date,
        p_creation_user,
        p_creation_ip          
    );
      
    perform content_item__set_live_revision(v_revision_id);

    update cr_wp_slides
    set sort_key = sort_key + 1
    where sort_key >= p_sort_key
    and  exists (select 1 from cr_items, cr_revisions 
                 where parent_id = p_pres_item_id 
                 and cr_items.item_id = cr_revisions.item_id 
                 and cr_revisions.revision_id=cr_wp_slides.slide_id);
        
    insert into cr_wp_slides ( 
        slide_id,
        original_slide_id,
        sort_key,
        slide_title,
        include_in_outline_p,
        context_break_after_p,
        style  
    ) values ( 
        v_revision_id,
        p_original_slide_id,
        p_sort_key,
        p_slide_title,
        p_include_in_outline_p,
        p_context_break_after_p,
        p_style
    );  
    
    v_preamble_item_id := content_item__new(
        'preamble',
        v_item_id,
        null,
        null,
        p_creation_date,
        p_creation_user,
        null,
        p_creation_ip,
        'content_item',
        'cr_wp_slide_preamble',
        null,
        null,
        'text/plain',
        null,
        null,
        'text'
    );  

    v_preamble_revision_id := content_revision__new(
        null,
        null, 
        current_timestamp,
        'text/plain', 
        null, 
        p_preamble,
        v_preamble_item_id,
        null,
        p_creation_date,
        p_creation_user,
        p_creation_ip          
    );
    
    perform content_item__set_live_revision(v_preamble_revision_id);

    insert into cr_wp_slides_preamble
    (id, slide_id) 
    values 
    (v_preamble_revision_id, v_revision_id);

    v_postamble_item_id := content_item__new(
        'postamble',
        v_item_id,
        null,
        null,
        p_creation_date,
        p_creation_user,
        null,
        p_creation_ip,
        'content_item',
        'cr_wp_slide_postamble',
        null,
        null,
        'text/plain',
        null,
        null,
        'text'      
    );

    v_postamble_revision_id := content_revision__new(
        null,
        null, 
        current_timestamp,
        'text/plain', 
        null, 
        p_postamble,
        v_postamble_item_id,
        null,
        p_creation_date,
        p_creation_user,
        p_creation_ip          
    );

    perform content_item__set_live_revision(v_postamble_revision_id);
    
    insert into cr_wp_slides_postamble
    (id, slide_id) 
    values 
    (v_postamble_revision_id, v_revision_id);

    v_bullet_items_item_id := content_item__new(
        'bullet_items',
        v_item_id,
        null,
        null,
        p_creation_date,
        p_creation_user,
        null,
        p_creation_ip,
        'content_item',
        'cr_wp_slide_bullet_items',
        null,
        null,
        null,
        'text/plain',
        null,  
        'text'      
    );

    v_bullet_items_revision_id := content_revision__new(
        null,
        null, 
        current_timestamp,
        'text/plain', 
        null, 
        p_bullet_items,
        v_bullet_items_item_id,
        null,
        p_creation_date,
        p_creation_user,
        p_creation_ip          
    );

    perform content_item__set_live_revision(v_bullet_items_revision_id);

    insert into cr_wp_slides_bullet_items
    (id, slide_id) 
    values 
    (v_bullet_items_revision_id, v_revision_id);

    return v_item_id;
end;$$ language plpgsql;