--
--  Copyright (C) 2001, 2002 MIT
--
--  This file is part of dotLRN.
--
--  dotLRN is free software; you can redistribute it and/or modify it under the
--  terms of the GNU General Public License as published by the Free Software
--  Foundation; either version 2 of the License, or (at your option) any later
--  version.
--
--  dotLRN is distributed in the hope that it will be useful, but WITHOUT ANY
--  WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
--  FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more
--  details.
--

--
-- create portal pages package
--
-- @author <a href="mailto:arjun@openforce.net">arjun@openforce.net</a>
-- @author <a href="mailto:yon@openforce.net">yon@openforce.net</a>
-- @creation-date 2001-10-01
-- @version $Id: page-package-create.sql,v 1.3 2004/01/15 02:58:52 donb Exp $
--

select define_function_args('portal_page__new', 'page_id,name,portal_id,layout_id,object_type;portal_page,creation_date,creation_user,creation_ip,context_id');

create or replace function portal_page__new (integer,varchar,integer,integer,varchar,timestamptz,integer,varchar,integer)
returns integer as '
declare
    p_page_id                       alias for $1;
    p_name                          alias for $2;
    p_portal_id                     alias for $3;
    p_layout_id                     alias for $4;
    p_object_type                   alias for $5;
    p_creation_date                 alias for $6;
    p_creation_user                 alias for $7;
    p_creation_ip                   alias for $8;
    p_context_id                    alias for $9;
    v_page_id                       portal_pages.page_id%TYPE;
    v_layout_id                     portal_pages.layout_id%TYPE;
    v_sort_key                      portal_pages.sort_key%TYPE;
begin

    v_page_id := acs_object__new(
        p_page_id,
        p_object_type,
        p_creation_date,
        p_creation_user,
        p_creation_ip,
        coalesce(p_context_id, p_portal_id),
        ''t''
    );

    if p_layout_id is null then
        select min(layout_id)
        into v_layout_id
        from portal_layouts;
    else
        v_layout_id := p_layout_id;
    end if;

    select coalesce(max(sort_key) + 1, 0)
    into v_sort_key
    from portal_pages
    where portal_id = p_portal_id;

    insert into portal_pages
    (page_id, name, portal_id, layout_id, sort_key)
    values
    (v_page_id, p_name, p_portal_id, v_layout_id, v_sort_key);

    return v_page_id;

end;' language 'plpgsql';

select define_function_args('portal_page__del', 'page_id');

create or replace function portal_page__del(integer)
returns integer as '
declare
    p_page_id                       alias for $1;
    v_portal_id                     portal_pages.portal_id%TYPE;
    v_sort_key                      portal_pages.sort_key%TYPE;
    v_curr_sort_key                 portal_pages.sort_key%TYPE;
    v_page_count_from_0             integer;
begin

    -- IMPORTANT: sort keys MUST be an unbroken sequence from 0 to max(sort_key)

    select portal_id, sort_key
    into v_portal_id, v_sort_key
    from portal_pages
    where page_id = p_page_id;

    select (count(*) - 1)
    into v_page_count_from_0
    from portal_pages
    where portal_id = v_portal_id;

    for i in 0 .. v_page_count_from_0 loop

        if i = v_sort_key then

            delete
            from portal_pages
            where page_id = p_page_id;

        elsif i > v_sort_key then

            update portal_pages
            set sort_key = -1
            where sort_key = i;

            update portal_pages
            set sort_key = i - 1
            where sort_key = -1;

        end if;

    end loop;

    perform acs_object__delete(p_page_id);

    return 0;

end;' language 'plpgsql';