-- 
-- A general comment facility 
--
-- created by philg@mit.edu on 11/20/98
-- (and substantially upgraded by philg 9/5/99)
-- (and upgrade to use table_acs_properties by philg on 10/31/99)

-- this is used for when people want to comment on a news article
-- or calendar posting or other tables that are yet to be 
-- built; we don't mix in the page comments or the discussion
-- forums here, though perhaps we should...

create sequence general_comment_id_sequence;

create table general_comments (
	comment_id		integer primary key,
	on_what_id		integer not null,
	on_which_table		varchar(50),
	-- a description of what we're commenting on 
	one_line_item_desc	varchar(200) not null,
	user_id			integer not null references users,
        scope			varchar(20) default 'public' not null,
	constraint general_comments_scope_check check (scope in ('public', 'group')),
	-- group_id of the group for which this general comment was submitted
	group_id		integer references user_groups,
	comment_date		datetime not null,
	ip_address		varchar(50) not null,
	modified_date		datetime,
	one_line		varchar(200),
	content			text,
	-- is the content in HTML or plain text (the default)
	html_p			char(1) default 'f' check(html_p in ('t','f')),
	approved_p		char(1) default 't' check(approved_p in ('t','f')),
        lob			integer references lobs,
	-- file name including extension but not path
	client_file_name	varchar(500),
	file_type		varchar(100),	-- this is a MIME type (e.g., image/jpeg)
	file_extension		varchar(50), 	-- e.g., "jpg"
	-- fields that only make sense if this is an image
	caption			varchar(4000),
	original_width		integer,
	original_height		integer
);

create trigger gc_lob_trig before delete or update or insert
on general_comments for each row execute procedure on_lob_ref();

create function trig_general_comments_modified() returns opaque
as '
declare
begin
 NEW.modified_date := current_timestamp;
 return NEW;
end;
' language 'plpgsql';

create trigger general_comments_modified
before insert or update on general_comments
for each row execute procedure trig_general_comments_modified();

-- an index useful when printing out content to the public 

create index general_comments_cidx on general_comments(on_which_table, on_what_id);

-- an index useful when printing out a user history

create index general_comments_uidx on general_comments(user_id);


-- store pre-modification content
-- these are all pre-modification values

-- no integrity constraints because we don't want to interfere with a
-- comment being deleted

create table general_comments_audit (
	comment_id		integer,
	-- who did the modification and from where
	user_id			integer not null,
	ip_address		varchar(50) not null,
	audit_entry_time	datetime,
	-- the old modified date that goes with this content
	modified_date		datetime,
	content			text,
	one_line		varchar(200)
);

create function init_general_comments() returns boolean
as '
declare
 n_news_rows		integer;
 n_calendar_rows	integer;
 n_classified_rows	integer;
 n_neighbor_rows	integer;
begin
 select count(*) into n_news_rows from table_acs_properties where table_name = ''news'';
 if n_news_rows = 0 then 
   insert into table_acs_properties
    (table_name, module_key, section_name, user_url_stub, admin_url_stub)
    values
    (''news_items'',''news'',''News'',''/news/item.tcl?news_item_id='',''/admin/news/item.tcl?news_item_id='');
 end if;
 select count(*) into n_calendar_rows from table_acs_properties where table_name = ''calendar'';
 if n_calendar_rows = 0 then 
   insert into table_acs_properties
    (table_name, module_key, section_name, user_url_stub, admin_url_stub)
    values
    (''calendar'',''calendar'',''Calendar'',''/calendar/item.tcl?calendar_id='',''/admin/calendar/item.tcl?calendar_id='');
 end if;
 select count(*) into n_classified_rows from table_acs_properties where table_name = ''classified_ads'';
 if n_classified_rows = 0 then 
   insert into table_acs_properties
    (table_name, section_name, user_url_stub, admin_url_stub)
    values
    (''classified_ads'',''Classifieds'',''/gc/view-one.tcl?classified_ad_id='',''/admin/gc/edit-ad.tcl?classified_ad_id='');
 end if;
 select count(*) into n_neighbor_rows from table_acs_properties where table_name = ''neighbor_to_neighbor'';
 if n_neighbor_rows = 0 then 
   insert into table_acs_properties
    (table_name, section_name, user_url_stub, admin_url_stub)
    values
    (''neighbor_to_neighbor'',''Neighbor to Neighbor'',''/neighbor/view-one.tcl?neighbor_to_neighbor_id='',''/admin/neighbor/view-one.tcl?neighbor_to_neighbor_id='');
 end if;
 return ''t'';
end;
' language 'plpgsql';

select init_general_comments();

create function trig_news_gc_delete() returns opaque
as '
declare 
BEGIN
  DELETE FROM general_comments
    WHERE on_which_table = ''news_items''
    AND on_what_id = OLD.news_item_id;

    return OLD;
END;
' language 'plpgsql';

CREATE trigger news_gc_delete
  after DELETE
  ON news_items
  FOR each row execute procedure trig_news_gc_delete();

CREATE function trig_calendar_gc_delete() returns opaque
as '
DECLARE
BEGIN
  DELETE FROM general_comments
    WHERE on_which_table = ''calendar''
    AND on_what_id = OLD.calendar_id;
END;
' language 'plpgsql';

CREATE trigger calendar_gc_delete
  after DELETE
  ON calendar
  FOR each row execute procedure trig_calendar_gc_delete();

CREATE function trig_classified_ads_gc_delete() returns opaque
as '
DECLARE
BEGIN
  DELETE FROM general_comments
    WHERE on_which_table = ''classified_ads''
    AND on_what_id = old.classified_ad_id;
  RETURN old;
END;
' language 'plpgsql';

CREATE trigger classified_ads_gc_delete
  after DELETE
  ON classified_ads
  FOR each row execute procedure trig_classified_ads_gc_delete();

create function trig_n_to_n_gc_delete() returns opaque
as '
DECLARE
BEGIN
  DELETE FROM general_comments
    WHERE on_which_table = ''neighbor_to_neighbor''
    AND on_what_id = OLD.neighbor_to_neighbor_id;
END;
' language 'plpgsql';

CREATE trigger n_to_n_gc_delete
  after DELETE
  ON neighbor_to_neighbor
  FOR each row execute procedure trig_n_to_n_gc_delete();