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

declare
    p_revision_id     alias for $1;
    p_approve_p       alias for $2; -- default 't'
    p_publish_date    alias for $3; -- default null
    p_archive_date    alias for $4; -- default null
    p_approval_user   alias for $5; -- default null
    p_approval_date   alias for $6; -- default current_timestamp
    p_approval_ip     alias for $7; -- default null
    p_live_revision_p alias for $8; -- default 't'
    v_item_id         cr_items.item_id%TYPE;
begin
    select item_id into v_item_id
    from   cr_revisions 
    where  revision_id = p_revision_id;
    -- unapprove an revision (does not mean to knock out active revision)
    if p_approve_p = 'f' then
        update  cr_news 
        set     approval_date = null,
                approval_user = null,
                approval_ip   = null,
                archive_date  = null
        where   news_id = p_revision_id;
        --
        update  cr_revisions
        set     publish_date = null
        where   revision_id  = p_revision_id;
    else
    -- approve a revision
        update  cr_revisions
        set     publish_date  = p_publish_date
        where   revision_id   = p_revision_id;
        --  
        update  cr_news 
        set archive_date  = p_archive_date,
            approval_date = p_approval_date,
            approval_user = p_approval_user,
            approval_ip   = p_approval_ip
        where news_id     = p_revision_id;
        -- 
        -- cannot use content_item.set_live_revision because it sets publish_date to sysdate
        if p_live_revision_p = 't' then
            update  cr_items
            set     live_revision = p_revision_id,
                    publish_status = 'ready'
            where   item_id = v_item_id;
        end if;
        --
    end if;

    return 0;
end;
$$ language plpgsql;