--
-- lars-blogger-create.sql
-- 
-- @author Lars Pind
-- 
-- @cvs-id $Id: lars-blogger-create.sql,v 1.2.2.7 2003/06/13 13:51:00 simonc Exp $
--

select acs_object_type__create_type (
    'pinds_blog_entry',             -- object_type
    'Blog Entry',                   -- pretty_name
    'Blog Entries',                 -- pretty_plural
    'acs_object',                   -- supertype
    'pinds_blog_entries',           -- table_name
    'entry_id',                     -- id_column
    null,                           -- package_name
    'f',                            -- abstract_p
    null,                           -- type_extension_table
    'pinds_blog_entry__title'       -- name_method
);

create table pinds_blog_entries (
  entry_id              integer
                        constraint pinds_blog_entry_id_fk
                        references acs_objects(object_id)
                        constraint pinds_blog_entries_pk
                        primary key,
  package_id            integer
                        constraint pinds_blog_entry_package_id_kf
                        references apm_packages(package_id),
  title                 varchar(500),
  title_url             varchar(500),
  content               varchar(32000),
  content_format        varchar(50) 
                        default 'text/html'
                        constraint pinds_blog_entr_cnt_format_nn
                        not null,
  entry_date            timestamptz,
  posted_date           timestamptz,
  draft_p               char(1) default 'f'
                        constraint pinds_blog_entries_draft_ck
                        check (draft_p in ('t','f')),
  deleted_p             char(1) default 'f'
                        constraint pinds_blog_entries_deleted_ck
                        check (deleted_p in ('t','f'))
);

create index pinds_blog_entry_pck_entr_idx on pinds_blog_entries (package_id, entry_date);

create or replace function pinds_blog_entry__title (integer)
returns varchar as '
declare
    p_entry_id        alias for $1;
    v_title           varchar;
begin
    select title into v_title
        from pinds_blog_entries
        where entry_id = p_entry_id;
    return v_title;
end;
' language 'plpgsql';


create or replace function pinds_blog_entry__new (
    integer,     -- entry_id
    integer,     -- package_id
    varchar,     -- title
    varchar,     -- title_url
    varchar,     -- content
    varchar,     -- content_format
    timestamptz, -- entry_date
    char,        -- draft_p
    integer,     -- creation_user
    varchar      -- creation_ip
) returns integer as '
declare
    p_entry_id             alias for $1;
    p_package_id           alias for $2;
    p_title                alias for $3;
    p_title_url            alias for $4;
    p_content              alias for $5;
    p_content_format       alias for $6;
    p_entry_date           alias for $7;
    p_draft_p              alias for $8;
    p_creation_user        alias for $9;
    p_creation_ip          alias for $10;
    v_entry_id             integer;
begin
    v_entry_id := acs_object__new (
        p_entry_id,
        ''pinds_blog_entry'',
        current_timestamp,
        p_creation_user,
        p_creation_ip,
        p_package_id
    );

    insert into pinds_blog_entries (
      entry_id, 
      package_id,
      title,
      title_url,
      content,
      content_format,
      entry_date,
      posted_date,
      draft_p,
      deleted_p
    ) values (
      v_entry_id, 
      p_package_id,
      p_title,
      p_title_url,
      p_content,
      p_content_format,
      p_entry_date,
      current_timestamp,
      p_draft_p,
      ''f''
    );

    PERFORM acs_permission__grant_permission(
        v_entry_id,
	p_creation_user,
	''admin''
    );

    return v_entry_id;   
end;
' language 'plpgsql';


create or replace function pinds_blog_entry__delete (integer)
returns integer as '
declare
    p_entry_id alias for $1;
begin
    delete from pinds_blog_entries
        where entry_id = p_entry_id;
    PERFORM acs_object__delete(p_entry_id);
    return 0;
end;
' language 'plpgsql';


select acs_object_type__create_type (
    'weblogger_channel',        -- object_type
    'Weblogger Channel',        -- pretty_name
    'Weblogger Channels',       -- pretty_plural
    'acs_object',               -- supertype
    'weblogger_channels',       -- table_name
    'channel_id',       	-- id_column
    null,               	-- package_name
    'f',        	        -- abstract_p
    null,   	                -- type_extension_table
    'weblogger_channels.title'	-- name_method
);

create table weblogger_channels (
  channel_id    	integer
                        constraint channel_id_fk
                        references acs_objects(object_id)
                        constraint weblogger_channels_pk
                        primary key,
  package_id            integer
                        constraint weblogger_channels_package_id_kf
                        references apm_packages(package_id),
  user_id		integer		
);



create or replace function weblogger_channel__new (
    integer,     -- channel_id
    integer,     -- package_id
    integer,     -- user_id
    integer,     -- creation_user
    varchar      -- creation_ip
) returns integer as '
declare
    p_channel_id           alias for $1;
    p_package_id           alias for $2;
    p_user_id		   alias for $3;
    p_creation_user        alias for $4;
    p_creation_ip          alias for $5;
    v_channel_id           integer;
begin
    v_channel_id := acs_object__new (
        p_channel_id,
        ''weblogger_channel'',
        current_timestamp,
        p_creation_user,
        p_creation_ip,
        p_package_id
    );

    insert into weblogger_channels (
      channel_id, 
      package_id,
      user_id      
    ) values (
      v_channel_id, 
      p_package_id,
      p_user_id
    );

    return v_channel_id;   
end;
' language 'plpgsql';

create or replace function weblogger_channel__delete (integer)
returns integer as '
declare
    p_channel_id alias for $1;
begin
    delete from weblogger_channels
        where channel_id = p_channel_id;
    PERFORM acs_object__delete(p_channel_id);
    return 0;
end;
' language 'plpgsql';

\i rss-register.sql
\i notifications-init.sql