-- /packages/news/sql/news-create.sql
--
-- @author stefan@arsdigita.com
-- @created 2000-12-13
-- @cvs-id $Id: news-create.sql,v 1.1.1.1 2002/07/09 17:35:09 rmello Exp $


-- *** PERMISSION MODEL ***
--
begin
    -- the read privilege is by default granted to 'the_public'
    -- the site-wide administrator has to change this in /permissions/ 
    -- if he wants to restrict an instance to a specific party

    -- the news_admin has all privileges: read, create, delete, approve
    -- news_admin is a child of 'admin'.
    -- 'admin' is therefore the top-administrator, news_admin is the news administrator
    -- in the context of an instance

    acs_privilege.create_privilege('news_read');
    acs_privilege.create_privilege('news_create');
    acs_privilege.create_privilege('news_delete');

    -- bind privileges to global names  
    acs_privilege.add_child('read','news_read');
    acs_privilege.add_child('create','news_create');
    acs_privilege.add_child('delete','news_delete');

    -- add this to the news_admin privilege
    acs_privilege.create_privilege('news_admin', 'News Administrator');
    -- news administrator binds to global 'admin', plus inherits news_* permissions
    acs_privilege.add_child('admin','news_admin');      
    acs_privilege.add_child('news_admin','news_read');
    acs_privilege.add_child('news_admin','news_create');
    acs_privilege.add_child('news_admin','news_delete');
end;
/
show errors

-- assign permission to defined contexts within ACS by default
--
declare
    default_context acs_objects.object_id%TYPE;
    registered_users acs_objects.object_id%TYPE;
    the_public acs_objects.object_id%TYPE;
begin
    default_context  := acs.magic_object_id('default_context');
    registered_users := acs.magic_object_id('registered_users');
    the_public       := acs.magic_object_id('the_public');
    

    -- give the public permission to read by default
    acs_permission.grant_permission (
        object_id  => default_context,
        grantee_id => the_public,
        privilege  => 'news_read'
    );

    -- give registered users permission to upload items by default
    -- However, they must await approval by users with news_admin privilege
       acs_permission.grant_permission (
         object_id  => default_context,
         grantee_id => registered_users,
         privilege  => 'news_create'
       );

end;
/
show errors



-- *** DATAMODEL ***
-- we use the content repository (see http://cvs.arsdigita.com/acs/packages/acs-content-repository) plus this
create table cr_news (
    news_id                     integer
                                constraint cr_news_id_fk 
                                references cr_revisions
                                constraint cr_news_pk 
                                primary key,
    -- include package_id to provide support for multiple instances
    package_id                  integer
                                constraint cr_news_package_id_nn not null,      
    -- regarding news item
    -- *** support for dates when items are displayed or archived ***
    -- unarchived news items have archive_date null
    archive_date                date,
    -- support for approval
    approval_user               integer
                                constraint cr_news_approval_user_fk
                                references users,
    approval_date               date,
    approval_ip                 varchar2(50)
);


-- index to avoid lock situation through parent table
create  index cr_news_appuser_id_fk on cr_news(approval_user);


-- *** NEWS item defitition *** ---
begin
content_type.create_type (
    content_type  => 'news',
    pretty_name   => 'News Item',
    pretty_plural => 'News Items',
    table_name    => 'cr_news',
    id_column     => 'news_id',
    name_method   => 'news.name'
);
end;
/

declare
    attr_id acs_attributes.attribute_id%TYPE;
begin
-- create attributes for widget generation

-- website archive date of news release
attr_id := content_type.create_attribute (
    content_type   => 'news',
    attribute_name => 'archive_date',
    datatype       => 'timestamp',
    pretty_name    => 'Archive Date',
    pretty_plural  => 'Archive Dates',
    column_spec    => 'date'
);
-- authorized user for approval
attr_id := content_type.create_attribute (
    content_type   => 'news',
    attribute_name => 'approval_user',
    datatype       => 'integer',
    pretty_name    => 'Approval User',
    pretty_plural  => 'Approval Users',
    column_spec    => 'integer'
);
-- approval date
attr_id := content_type.create_attribute (
    content_type   => 'news',
    attribute_name => 'approval_date',
    datatype       => 'timestamp',
    pretty_name    => 'Approval Date',
    pretty_plural  => 'Approval Dates',
    default_value  => sysdate,
    column_spec    => 'date'
);
-- approval IP address
attr_id := content_type.create_attribute (
    content_type   => 'news',
    attribute_name => 'approval_ip',
    datatype       => 'text',
    pretty_name    => 'Approval IP',
    pretty_plural  => 'Approval IPs',
    column_spec    => 'varchar2(50)'
);
end;
/
show errors



-- *** CREATE THE NEWS FOLDER as our CONTAINER ***

-- create 1 news folder; different instances are filtered by package_id
declare
    v_folder_id cr_folders.folder_id%TYPE;
begin
    v_folder_id := content_folder.new(
        name        => 'news',
        label       => 'news',
        description => 'News Item Root Folder, all news items go in here'
    );
-- associate content types with news folder
    content_folder.register_content_type (
        folder_id        => v_folder_id,
        content_type     => 'news',
        include_subtypes => 't'
    );
    content_folder.register_content_type (
        folder_id        => v_folder_id,
        content_type     => 'content_revision',
        include_subtypes => 't'
    );
end;
/
show errors


-- *** PACKAGE NEWS, plsql to create content_item ***
create or replace package news
as 
    function new (
        item_id                 in cr_items.item_id%TYPE 	  default null,
        --
        locale                  in cr_items.locale%TYPE 	  default null, 
        --
        publish_date            in cr_revisions.publish_date%TYPE default null,
        text                    in varchar2                       default null,
        nls_language            in cr_revisions.nls_language%TYPE default null,
        title                   in cr_revisions.title%TYPE 	  default null,
        mime_type               in cr_revisions.mime_type%TYPE    default 'text/plain',
        --
        package_id              in cr_news.package_id%TYPE 	  default null,        
        archive_date            in cr_news.archive_date%TYPE      default null,
        approval_user           in cr_news.approval_user%TYPE     default null,
        approval_date           in cr_news.approval_date%TYPE     default null,
        approval_ip             in cr_news.approval_ip%TYPE       default null,      
        --
        relation_tag            in cr_child_rels.relation_tag%TYPE 
                                                                  default null,
        --
        item_subtype            in acs_object_types.object_type%TYPE 
                                                                  default 'content_revision',
        content_type            in acs_object_types.object_type%TYPE 
                                                                  default 'news',
        creation_date           in acs_objects.creation_date%TYPE default sysdate,
        creation_ip             in acs_objects.creation_ip%TYPE   default null,
        creation_user           in acs_objects.creation_user%TYPE default null,
        --
        is_live_p               in varchar2                       default 'f' 
    ) return cr_news.news_id%TYPE;

    procedure delete (
        item_id in cr_items.item_id%TYPE
    );  

    procedure archive (
        item_id in cr_items.item_id%TYPE,
        archive_date in cr_news.archive_date%TYPE default sysdate       
    );  

    procedure make_permanent (
           item_id in cr_items.item_id%TYPE
    );

   
    procedure set_approve (
        revision_id      in cr_revisions.revision_id%TYPE,       
	approve_p        in varchar2 default 't',  
        publish_date     in cr_revisions.publish_date%TYPE  	default null,
        archive_date     in cr_news.archive_date%TYPE 		default null,
        approval_user    in cr_news.approval_user%TYPE 		default null,
        approval_date    in cr_news.approval_date%TYPE 		default sysdate,
        approval_ip      in cr_news.approval_ip%TYPE 		default null, 
        live_revision_p  in varchar2 default 't'
    );



    function status (
        news_id in cr_news.news_id%TYPE
    ) return varchar2;


    function name (
	news_id in cr_news.news_id%TYPE
    ) return varchar2;   


    --  
    -- API for revisions: e.g. when the news admin wants to revise a news item
    --
    function revision_new (
        item_id                 in cr_items.item_id%TYPE,       
        --
        publish_date            in cr_revisions.publish_date%TYPE    default null,
        text                    in varchar2                   default null,
        title                   in cr_revisions.title%TYPE,
        --
        -- here goes the revision log
        description             in cr_revisions.description%TYPE,
        --
        mime_type               in cr_revisions.mime_type%TYPE 	     default 'text/plain',
        package_id              in cr_news.package_id%TYPE 	     default null,        
        archive_date            in cr_news.archive_date%TYPE         default null,
        approval_user           in cr_news.approval_user%TYPE        default null,
        approval_date           in cr_news.approval_date%TYPE        default null,
        approval_ip             in cr_news.approval_ip%TYPE          default null,      
        --
        creation_date           in acs_objects.creation_date%TYPE    default sysdate,
        creation_ip             in acs_objects.creation_ip%TYPE      default null,           
        creation_user           in acs_objects.creation_user%TYPE    default null,
        --
        make_active_revision_p  in varchar2 default 'f'
    ) return cr_revisions.revision_id%TYPE;


    procedure revision_delete (
       revision_id in cr_revisions.revision_id%TYPE
    );


    procedure revision_set_active (
       revision_id in cr_revisions.revision_id%TYPE
    );

    procedure clone (
        new_package_id          in cr_news.package_id%TYPE 	  default null,
        old_package_id          in cr_news.package_id%TYPE 	  default null
    );

end news;
/
show errors



create or replace package body news
    as
    function new (
        item_id                 in cr_items.item_id%TYPE             default null,
        --
        locale                  in cr_items.locale%TYPE              default null, 
        --
        publish_date            in cr_revisions.publish_date%TYPE    default null,
        text                    in varchar2                          default null,
        nls_language            in cr_revisions.nls_language%TYPE    default null,
        title                   in cr_revisions.title%TYPE           default null,
        mime_type               in cr_revisions.mime_type%TYPE       default 
	                					     'text/plain',
        --
        package_id              in cr_news.package_id%TYPE           default null,      
        archive_date            in cr_news.archive_date%TYPE         default null,
        approval_user           in cr_news.approval_user%TYPE        default null,
        approval_date           in cr_news.approval_date%TYPE        default null,
        approval_ip             in cr_news.approval_ip%TYPE          default null,      
        --
        relation_tag            in cr_child_rels.relation_tag%TYPE   default null,
        --
        item_subtype            in acs_object_types.object_type%TYPE default 
                                                                     'content_revision',
        content_type            in acs_object_types.object_type%TYPE default 'news',
        creation_date           in acs_objects.creation_date%TYPE    default sysdate,
        creation_ip             in acs_objects.creation_ip%TYPE      default null,
        creation_user           in acs_objects.creation_user%TYPE    default null,
        --
        is_live_p               in varchar2                          default 'f'
    ) return cr_news.news_id%TYPE
    is
        v_news_id         integer;
        v_item_id         integer;
        v_id              integer;
        v_revision_id     integer;
        v_parent_id       integer;
        v_name            varchar2(200);
        v_log_string      varchar2(400);
    begin
        select content_item.get_id('news') 
        into   v_parent_id 
        from   dual;    
        --
        -- this will be used for 2xClick protection
        if item_id is null then
            select acs_object_id_seq.nextval 
            into   v_id 
            from   dual;
        else 
            v_id := item_id;
        end if; 
        --
        select 'news' || to_char(sysdate,'YYYYMMDD') || v_id 
        into   v_name 
        from   dual;    
        -- 
        v_log_string := 'initial submission'; 
        -- 
        v_item_id := content_item.new(
            item_id       => v_id,
            name          => v_name,
            parent_id     => v_parent_id,
            locale        => locale,
            item_subtype  => item_subtype,
            content_type  => content_type,
            mime_type     => mime_type,
            nls_language  => nls_language,
            relation_tag  => relation_tag,
            creation_date => creation_date,
            creation_ip   => creation_ip,
            creation_user => creation_user
        );
        v_revision_id := content_revision.new(
            title         => title,
            description   => v_log_string,
            publish_date  => publish_date,
            mime_type     => mime_type,
            nls_language  => nls_language,
            text          => text,
            item_id       => v_item_id,
            creation_date => creation_date,
            creation_ip   => creation_ip,
            creation_user => creation_user
        );
        insert into cr_news 
            (news_id, 
             package_id, 
             archive_date,
             approval_user, 
             approval_date, 
             approval_ip)
        values
            (v_revision_id, 
             package_id, 
             archive_date,
             approval_user, 
             approval_date, 
             approval_ip);
        -- make this revision live when immediately approved
        if is_live_p = 't' then
	    update 
                cr_items
            set
                live_revision = v_revision_id,
                publish_status = 'ready'
            where 
                item_id = v_item_id;
        end if;
        v_news_id := v_revision_id;
        return v_news_id;
    end new;


    -- deletes a news item along with all its revisions and possibnle attachements
    procedure delete (
        item_id in cr_items.item_id%TYPE
    ) is
    v_item_id   cr_items.item_id%TYPE;

    cursor comment_cursor IS
        select message_id 
        from   acs_messages am, acs_objects ao
	where  am.message_id = ao.object_id
        and    ao.context_id = v_item_id;

    begin
    v_item_id := news.delete.item_id;
	dbms_output.put_line('Deleting associated comments...');
	-- delete acs_messages, images, comments to news item
	for v_cm in  comment_cursor loop
	    -- images
	    delete from images
        	where image_id in (select latest_revision
                                   from cr_items 
                                   where parent_id = v_cm.message_id);
	    acs_message.delete(v_cm.message_id);
            delete from general_comments
		where comment_id = v_cm.message_id;	 
        end loop;
        delete from cr_news 
        where news_id in (select revision_id 
                          from   cr_revisions 
                          where  item_id = v_item_id);
        content_item.delete(v_item_id);
    end delete;


    -- (re)-publish a news item out of the archive by nulling the archive_date
    -- this only applies to the currently active revision
    procedure make_permanent (
        item_id in cr_items.item_id%TYPE
     )
    is
    begin
        update cr_news
        set    archive_date = null
        where  news_id = content_item.get_live_revision(news.make_permanent.item_id);
    end make_permanent;


    -- archive a news item
    -- this only applies to the currently active revision
    procedure archive (
        item_id in cr_items.item_id%TYPE,
        archive_date in cr_news.archive_date%TYPE default sysdate       
    )
    is
    begin
        update cr_news  
        set    archive_date = news.archive.archive_date
        where  news_id = content_item.get_live_revision(news.archive.item_id);
    end archive;

  
    -- approve/unapprove a specific revision
    -- approving a revision makes it also the active revision
    procedure set_approve(  
        revision_id      in cr_revisions.revision_id%TYPE,       
	approve_p        in varchar2 default 't',  
        publish_date     in cr_revisions.publish_date%TYPE default null,
        archive_date     in cr_news.archive_date%TYPE default null,
        approval_user    in cr_news.approval_user%TYPE default null,
        approval_date    in cr_news.approval_date%TYPE default sysdate,
        approval_ip      in cr_news.approval_ip%TYPE default null, 
        live_revision_p  in varchar2 default 't'
    )
    is
        v_item_id cr_items.item_id%TYPE;
    begin
        select item_id into v_item_id 
        from   cr_revisions 
        where  revision_id = news.set_approve.revision_id;
        -- unapprove an revision (does not mean to knock out active revision)
        if news.set_approve.approve_p = 'f' then
            update  cr_news 
            set     approval_date = null,
                    approval_user = null,
                    approval_ip   = null,
                    archive_date  = null
            where   news_id = news.set_approve.revision_id;
            --
            update  cr_revisions
            set     publish_date = null
            where   revision_id  = news.set_approve.revision_id;
        else
        -- approve a revision
            update  cr_revisions
            set     publish_date  = news.set_approve.publish_date
            where   revision_id   = news.set_approve.revision_id;
            --  
            update  cr_news 
            set archive_date  = news.set_approve.archive_date,
                approval_date = news.set_approve.approval_date,
                approval_user = news.set_approve.approval_user,
                approval_ip   = news.set_approve.approval_ip
            where news_id     = news.set_approve.revision_id;
            -- 
            -- cannot use content_item.set_live_revision because it sets publish_date to sysdate
            if news.set_approve.live_revision_p = 't' then
                update  cr_items
                set     live_revision = news.set_approve.revision_id,
                        publish_status = 'ready'
                where   item_id = v_item_id;
            end if;
            --
        end if;    
    end set_approve;



    -- the status function returns information on the puplish or archive status
    -- it does not make any checks on the order of publish_date and archive_date
    function status (
        news_id in cr_news.news_id%TYPE
    ) return varchar2
    is
        v_archive_date date;
        v_publish_date date;
    begin
        -- populate variables
        select archive_date into v_archive_date 
        from   cr_news 
        where  news_id = news.status.news_id;
        --
        select publish_date into v_publish_date
        from   cr_revisions
        where  revision_id = news.status.news_id;
        
        -- if publish_date is not null the item is approved, otherwise it is not
        if v_publish_date is not null then
            if v_publish_date > sysdate  then
                -- to be published (2 cases)
                -- archive date could be null if it has not been decided when to archive
                if v_archive_date is null then 
                    return 'going live in ' || 
                    round(to_char(v_publish_date - sysdate),1) || ' days';
                else 
                    return 'going live in ' || 
                    round(to_char(v_publish_date - sysdate),1) || ' days' ||
                    ', archived in ' || round(to_char(v_archive_date - sysdate),1) || ' days';
                end if;  
            else
                -- already released or even archived (3 cases)
                if v_archive_date is null then
                     return 'published, not scheduled for archive';
                else
                    if v_archive_date - sysdate > 0 then
                         return 'published, archived in ' || 
                         round(to_char(v_archive_date - sysdate),1) || ' days';
                    else 
                        return 'archived';
                    end if;
                 end if;
            end if;     
        else 
            return 'unapproved';
        end if;
    end status;


    function name (
	news_id in cr_news.news_id%TYPE
    ) return varchar2
    is
        news_title varchar2(1000);
    begin
        select title 
	into news_title
        from cr_revisions
        where revision_id = news.name.news_id;

        return news_title;
    end name;
    

    -- 
    -- API for Revision management
    -- 
    function revision_new (
        item_id                 in cr_items.item_id%TYPE,       
        --
        publish_date            in cr_revisions.publish_date%TYPE  	default null,
        text                    in varchar2                             default null,
        title                   in cr_revisions.title%TYPE,
        --
        -- here goes the revision log
        description             in cr_revisions.description%TYPE,
        --
        mime_type               in cr_revisions.mime_type%TYPE 		default 'text/plain',
        package_id              in cr_news.package_id%TYPE 		default null,        
        archive_date            in cr_news.archive_date%TYPE 		default null,
        approval_user           in cr_news.approval_user%TYPE 		default null,
        approval_date           in cr_news.approval_date%TYPE 		default null,
        approval_ip             in cr_news.approval_ip%TYPE   		default null,      
        --
        creation_date           in acs_objects.creation_date%TYPE 	default sysdate,
        creation_ip             in acs_objects.creation_ip%TYPE 	default null,           
        creation_user           in acs_objects.creation_user%TYPE 	default null,
        --
        make_active_revision_p  in varchar2 default 'f'
    ) return cr_revisions.revision_id%TYPE
    is  
        v_revision_id    integer;
    begin
        -- create revision
        v_revision_id := content_revision.new(
            title         => title,
            description   => description,
            publish_date  => publish_date,
            mime_type     => mime_type,
            text          => text,
            item_id       => item_id,
            creation_date => creation_date,
            creation_user => creation_user,
            creation_ip   => creation_ip
        );
        -- create new news entry with new revision
        insert into cr_news
            (news_id, 
             package_id,
             archive_date, 
             approval_user, 
             approval_date, 
             approval_ip)
        values
            (v_revision_id, 
             package_id,
             archive_date, 
             approval_user, 
             approval_date,
             approval_ip);
        -- make active revision if indicated
        if make_active_revision_p = 't' then
            news.revision_set_active(v_revision_id);
        end if;
        return v_revision_id;
    end revision_new;



    procedure revision_set_active   (
        revision_id in cr_revisions.revision_id%TYPE
    )
    is
        v_news_item_p char;
        -- could be used to check if really a 'news' item
    begin
        update	
            cr_items
        set
            live_revision = news.revision_set_active.revision_id,
            publish_status = 'ready'
        where
	    item_id = (select
                           item_id
                       from
                           cr_revisions
                       where
                           revision_id = news.revision_set_active.revision_id);
    end revision_set_active; 


    procedure clone   (
        new_package_id    in cr_news.package_id%TYPE    default null,
        old_package_id    in cr_news.package_id%TYPE    default null
    )
    is
      new_news_id integer;
    begin
        for one_news in (select
                            publish_date,
                            content.blob_to_string(cr.content) as text,
                            cr.nls_language,
                            cr.title as title,
                            cr.mime_type,
                            cn.package_id,
                            archive_date,
                            approval_user,
                            approval_date,
                            approval_ip,
                            ao.creation_date,
                            ao.creation_ip,
                            ao.creation_user
                        from 
                            cr_items ci, 
                            cr_revisions cr,
                            cr_news cn,
                            acs_objects ao
                        where
                            (ci.item_id = cr.item_id
                            and ci.live_revision = cr.revision_id 
                            and cr.revision_id = cn.news_id 
                            and cr.revision_id = ao.object_id)
                        or (ci.live_revision is null 
                            and ci.item_id = cr.item_id
                            and cr.revision_id = content_item.get_latest_revision(ci.item_id)
                            and cr.revision_id = cn.news_id
                            and cr.revision_id = ao.object_id))
        loop

            new_news_id := news.new(
                publish_date      => one_news.publish_date,
                text              => one_news.text,
                nls_language      => one_news.nls_language,
                title             => one_news.title,
                mime_type         => one_news.mime_type,
                package_id        => news.clone.new_package_id,
                archive_date      => one_news.archive_date,
                approval_user     => one_news.approval_user,
                approval_date     => one_news.approval_date,
                approval_ip       => one_news.approval_ip,
                creation_date     => one_news.creation_date,
                creation_ip       => one_news.creation_ip,
                creation_user     => one_news.creation_user
            );

        end loop;
    end clone;

    -- currently not used, because we want to audit revisions
    procedure revision_delete (
        revision_id in cr_revisions.revision_id%TYPE
    )
    is
    begin
    -- delete from cr_news table
        delete from cr_news
        where  news_id = news.revision_delete.revision_id;
        -- delete revision
        content_revision.delete(
            revision_id => news.revision_delete.revision_id
        );
    end revision_delete;

end news;
/
show errors




--
--  views on 'news' application that pick from cr_news, cr_items, cr_revisions
--  Re-arrange 'joins' for performance tuning
-- 

--  Views on multiple items

-- View on all released news items in its active revision
create or replace view news_items_approved
as
select
    ci.item_id as item_id,
    cn.package_id, 
    cr.title as publish_title,
    content.blob_to_string(cr.content) as publish_body,
    cr.content as content,
    decode(cr.mime_type, 'text/html','t','f') as html_p,
    to_char(cr.publish_date, 'Mon dd, yyyy') as pretty_publish_date,
    cr.publish_date,
    ao.creation_user,
    ps.first_names || ' ' || ps.last_name as item_creator,
    cn.archive_date    
from 
    cr_items ci, 
    cr_revisions cr,
    cr_news cn,
    acs_objects ao,
    persons ps
where
    ci.item_id = cr.item_id
and ci.live_revision = cr.revision_id
and cr.revision_id = cn.news_id
and cr.revision_id = ao.object_id
and ao.creation_user = ps.person_id;


-- View of all news items in the system 
create or replace view news_items_live_or_submitted
as 
select
    ci.item_id as item_id,
    cn.news_id,
    cn.package_id,
    to_char(cr.publish_date,'MM-DD-yyyy') as publish_date,
    to_char(cn.archive_date,'MM-DD-yyyy') as archive_date,
    cr.title as publish_title,
    content.blob_to_string(cr.content) as publish_body,
    decode(cr.mime_type, 'text/html','t','f') as html_p,
    ao.creation_user,
    ps.first_names || ' ' || ps.last_name as item_creator,
    ao.creation_date,
    ci.live_revision,
    news.status(cn.news_id) as status
from 
    cr_items ci, 
    cr_revisions cr,
    cr_news cn,
    acs_objects ao,
    persons ps
where
    (ci.item_id = cr.item_id
    and ci.live_revision = cr.revision_id 
    and cr.revision_id = cn.news_id 
    and cr.revision_id = ao.object_id
    and ao.creation_user = ps.person_id)
or (ci.live_revision is null 
    and ci.item_id = cr.item_id
    and cr.revision_id = content_item.get_latest_revision(ci.item_id)
    and cr.revision_id = cn.news_id
    and cr.revision_id = ao.object_id
    and ao.creation_user = ps.person_id);


-- View of unapproved items 
create or replace view news_items_unapproved
as 
select      
    ci.item_id as item_id,
    cr.title as publish_title,
    cn.package_id as package_id,
    ao.creation_date as creation_date,
    ps.first_names || ' ' || ps.last_name as item_creator
from 
    cr_items ci,
    cr_revisions cr,
    cr_news cn,
    acs_objects ao,
    persons ps
where 
    cr.revision_id = ao.object_id
and ao.creation_user = ps.person_id
and cr.revision_id = content_item.get_live_revision(ci.item_id)
and cr.revision_id = cn.news_id
and cr.item_id = ci.item_id
and cr.publish_date is null;



-- One News Item Views
--

-- View of all revisions of a news item
create or replace view news_item_revisions
as 
select
    cr.item_id as item_id,
    cr.revision_id,
    ci.live_revision,
    cr.title as publish_title,
    content.blob_to_string(cr.content) as publish_body,
    cr.publish_date as publish_date,
    cn.archive_date as archive_date,
    cr.description as log_entry,
    decode(cr.mime_type,'text/html','t','f') as html_p,
    cr.mime_type as mime_type,
    cn.package_id,
    ao.creation_date as creation_date,
    news.status(news_id) as status,
    case when exists (select 1 from cr_news where news_id = revision_id 
         and news.status(news_id) = 'unapproved') then 1 else 0 end 
         as
         approval_needed_p,
    ps.first_names || ' ' || ps.last_name as item_creator,
    ao.creation_user,
    ao.creation_ip,
    ci.name as item_name
from
    cr_revisions cr,
    cr_news cn,
    cr_items ci,
    acs_objects ao,
    persons ps
where 
    cr.revision_id = ao.object_id
and cr.revision_id = cn.news_id
and ci.item_id = cr.item_id
and ao.creation_user = ps.person_id;



-- View of a submitted news item or active revision in unapproved state
create or replace view news_item_unapproved
as 
select
    cr.revision_id,
    ci.name as item_name,
    ps.first_names || ' ' || ps.last_name as item_creator,
    ao.creation_ip as item_creation_ip,
    ao.creation_date
from 
    cr_revisions cr,
    cr_items ci,
    acs_objects ao,
    persons ps    
where 
    ci.item_id = cr.item_id
and cr.revision_id = ao.object_id
and ao.creation_user = ps.person_id;



-- View of a news item as of its active revision
create or replace view news_item_full_active
as 
select
    ci.item_id as item_id,
    cn.package_id as package_id,
    revision_id,        
    title as publish_title,
    content.blob_to_string(cr.content) as publish_body,
    decode(cr.mime_type,'text/html','t','f') as html_p,
    cr.publish_date,
    cn.archive_date,
    news.status(cr.revision_id) as status,
    ci.name as item_name,
    ps.person_id as creator_id,
    ps.first_names || ' ' || ps.last_name as item_creator
from
    cr_items ci, 
    cr_revisions cr,
    cr_news cn,
    acs_objects ao,
    persons ps
where 
    cr.item_id = ci.item_id
and (cr.revision_id = ci.live_revision
    or (ci.live_revision is null 
    and cr.revision_id = content_item.get_latest_revision(ci.item_id)))
and cr.revision_id = cn.news_id
and ci.item_id = ao.object_id
and ao.creation_user = ps.person_id;


-- plsql to create keywords for news items
-- no additional code necessary for news items right now.

-- plsql for searches: will be covered by site-wide search
-- no additional code necessary for news  items right now.
