--
-- wp_slide__delete/1
--
create or replace function wp_slide__delete(
  integer
) returns int4 as $$

declare
    p_slide_item_id             alias for $1;
    del_rec                     record;
    v_sort_key                  cr_wp_slides.sort_key%TYPE;
    v_pres_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;
begin
    for del_rec in 
        select item_id as attach_item_id
        from cr_items
        where content_type in 
            ('cr_wp_image_attachment', 'cr_wp_file_attachment')
        and parent_id = p_slide_item_id
    loop
        perform wp_attachment__delete(del_rec.attach_item_id);
    end loop;

    select item_id into v_preamble_item_id
    from cr_items
    where content_type = 'cr_wp_slide_preamble'
    and parent_id = p_slide_item_id;
    
    perform wp_slide__delete_preamble(v_preamble_item_id);
    
    select item_id into v_postamble_item_id
    from cr_items
    where content_type = 'cr_wp_slide_postamble'
    and parent_id = p_slide_item_id;

    perform wp_slide__delete_postamble(v_postamble_item_id);
    
    select item_id into v_bullet_items_item_id
    from cr_items
    where content_type = 'cr_wp_slide_bullet_items'
    and parent_id = p_slide_item_id;
    
    perform wp_slide__delete_bullet_items(v_bullet_items_item_id);

    -- sort_key of all revisions should be the same
    select max(s.sort_key), max(i.parent_id) into v_sort_key, v_pres_item_id
    from cr_wp_slides s, cr_revisions r, cr_items i
    where r.item_id = p_slide_item_id
    and   r.revision_id = s.slide_id
    and   i.item_id = r.item_id;
    
    delete from cr_wp_slides where exists (select 1 from cr_revisions
    where cr_revisions.revision_id = cr_wp_slides.slide_id
    and cr_revisions.item_id = p_slide_item_id);

    update cr_wp_slides set sort_key = sort_key - 1 
    where sort_key > v_sort_key and exists 
      (select 1 from cr_revisions r, cr_items i 
      where i.parent_id = v_pres_item_id and i.item_id = r.item_id
      and r.revision_id = cr_wp_slides.slide_id);    

--    update acs_objects set context_id=null
--    where context_id = p_slide_item_id;

    delete from cr_item_publish_audit where item_id = p_slide_item_id;

    perform content_item__delete(p_slide_item_id);

    return 0;
end;$$ language plpgsql;