--
-- static_page__delete_stale_items/2
--
create or replace function static_page__delete_stale_items(
  integer,
  integer
) returns int4 as $$

	declare
                p_session_id	        alias for $1;
                p_package_id	        alias for $2;
                v_root_folder_id	sp_folders.folder_id%TYPE;
		v_stale_file_row        RECORD;
		v_stale_folder_row      RECORD;
        begin
                v_root_folder_id := static_page__get_root_folder(p_package_id);

           -- First delete all files that are descendants of the root folder
           -- but aren't in sp_extant_files
                
	for v_stale_file_row in 
	   select sp.static_page_id 
           from static_pages sp, sp_folders s1, sp_folders s2
	   where sp.folder_id = s1.folder_id
             and s2.folder_id = v_root_folder_id
             and s1.tree_sortkey between s2.tree_sortkey and tree_right(s2.tree_sortkey)
	     and not exists (select 1
                             from sp_extant_files sef
                             where sef.session_id = p_session_id
                             and sp.static_page_id = sef.static_page_id)
	loop
		PERFORM static_page__delete(v_stale_file_row.static_page_id);
	end loop;

-- Now delete all folders that aren't in sp_extant_folders.  There are two
-- views created on the fly here: dead (all descendants of the root
-- folder not in sp_extant_folders) and path (each folder and its depth).
-- They are joined together to get the depth of all the folders that
-- need to be deleted.  The root folder is excluded because it won't
-- show up in the filesystem search, so it will be missing from
-- sp_extant_folders.

		for v_stale_folder_row in 
                        select dead.folder_id  from
                        (select folder_id from sp_folders
                                where folder_id not in (
                                        select folder_id
                                        from sp_extant_folders
                                        where session_id = p_session_id
                                )
                        ) dead,
                        (select s1.folder_id,tree_level(s1.tree_sortkey) as depth
                         from sp_folders s1, sp_folders s2
                         where s2.folder_id = v_root_folder_id
                           and s1.tree_sortkey between s2.tree_sortkey and tree_right(s2.tree_sortkey)
                        ) path
                        where dead.folder_id = path.folder_id
                                and dead.folder_id <> v_root_folder_id
                        order by path.depth desc
                 loop
		delete from sp_folders
                        where folder_id = v_stale_folder_row.folder_id;
                        perform content_folder__delete(v_stale_folder_row.folder_id);
                end loop;

return 0;
end;$$ language plpgsql;