-- Data model to keep a journal of all actions on objects.
-- 
--
-- @author Lars Pind (lars@pinds.com)
-- @creation-date 2000-22-18
-- @cvs-id journal-create.sql,v 1.7.2.1 2000/12/08 00:38:24 yon Exp
--
-- Copyright (C) 1999-2000 ArsDigita Corporation
--
-- This is free software distributed under the terms of the GNU Public
-- License.  Full text of the license is available from the GNU Project:
-- http://www.fsf.org/copyleft/gpl.html


create function inline_0 ()
returns integer as '
begin
  PERFORM acs_object_type__create_type (
    ''journal_entry'',
    ''Journal Entry'',
    ''Journal Entries'',
    ''acs_object'',
    ''journal_entries'',
    ''journal_id'',
    ''journal_entry'',
    ''f'',
    null,
    null
    );

  -- XXX fill in all the attributes in later.
  return 0;
end;' language 'plpgsql';

select inline_0 ();

drop function inline_0 ();


-- show errors

create table journal_entries (
  journal_id		integer constraint journal_entries_journal_id_fk
			references acs_objects (object_id)
			constraint journal_entries_pk
			primary key,
  object_id 		integer
			constraint journal_entries_object_fk
			references acs_objects on delete cascade,
  action                varchar(100) default '' not null,
  action_pretty         text default '' not null,
  msg    		text default '' not null
);

create index journal_entries_object_idx on journal_entries (object_id);

comment on table journal_entries is '
  Keeps track of actions performed on objects, e.g. banning a user,
  starting or finishing a workflow task, etc.
';


-- create or replace package journal_entry
-- as
-- 
--     function new (
--         journal_id	in journal_entries.journal_id%TYPE default null,
--         object_id	in journal_entries.object_id%TYPE,
--         action		in journal_entries.action%TYPE,
-- 	action_pretty   in journal_entries.action_pretty%TYPE default null,
--         creation_date	in acs_objects.creation_date%TYPE default sysdate,
--         creation_user	in acs_objects.creation_user%TYPE default null,
--         creation_ip	in acs_objects.creation_ip%TYPE default null,
--         msg		in journal_entries.msg%TYPE default null
--     ) return journal_entries.journal_id%TYPE;
-- 
--     procedure delete(
-- 	journal_id	in journal_entries.journal_id%TYPE
--     );
-- 
--     procedure delete_for_object(
-- 	object_id       in acs_objects.object_id%TYPE
--     );
-- 
-- end journal_entry;

-- show errors

-- create or replace package body journal_entry
-- function new
create function journal_entry__new (integer,integer,varchar,varchar,timestamp,integer,varchar,varchar)
returns integer as '
declare
  new__journal_id             alias for $1;  -- default null  
  new__object_id              alias for $2;  
  new__action                 alias for $3;  
  new__action_pretty          alias for $4;  -- default null
  new__creation_date          alias for $5;  -- default now()
  new__creation_user          alias for $6;  -- default null
  new__creation_ip            alias for $7;  -- default null
  new__msg                    alias for $8;  -- default null
  v_journal_id                journal_entries.journal_id%TYPE;
begin
	v_journal_id := acs_object__new (
	  new__journal_id,
	  ''journal_entry'',
	  new__creation_date,
	  new__creation_user,
	  new__creation_ip,
	  new__object_id
	);

        insert into journal_entries (
            journal_id, object_id, action, action_pretty, msg
        ) values (
            v_journal_id, new__object_id, coalesce(new__action,''''), 
            coalesce(new__action_pretty,''''), coalesce(new__msg,'''')
        );

        return v_journal_id;
     
end;' language 'plpgsql';


-- procedure delete
create function journal_entry__delete (integer)
returns integer as '
declare
  delete__journal_id             alias for $1;  
begin
	delete from journal_entries where journal_id = delete__journal_id;
	PERFORM acs_object__delete(delete__journal_id);

        return 0; 
end;' language 'plpgsql';


-- procedure delete_for_object
create function journal_entry__delete_for_object (integer)
returns integer as '
declare
  delete_for_object__object_id          alias for $1;  
  journal_rec                           record;
begin
        for journal_rec in select journal_id 
                             from journal_entries 
                            where object_id = delete_for_object__object_id  
        LOOP
	    PERFORM journal_entry__delete(journal_rec.journal_id);
	end loop;

        return 0; 
end;' language 'plpgsql';



-- show errors