--
-- /doc/sql/general-links.sql
--
-- by dh@arsdigita.com, <original creation date>
--
-- This is used in a similar way to general-comments to add links to a page.
-- In addition, users can view a "Hot Link" page of categorized links.

create sequence general_link_id_sequence start 1;

create table general_links (
	link_id                 integer primary key,
	url                     varchar(300) not null,
	link_title              varchar(100) not null,
	link_description        varchar(4000),
	-- meta tags defined by HTML at the URL
	meta_description        varchar(4000),
	meta_keywords           varchar(4000),
	n_ratings               integer,
        avg_rating              numeric,
	-- when was this submitted?
	creation_time	    	    datetime default current_timestamp not null,
	creation_user		    integer not null references users(user_id),
	creation_ip_address	    varchar(20) not null,
	last_modified		    datetime,
	last_modifying_user	    integer references users(user_id),
	-- last time this got checked
	last_checked_date       datetime,
	last_live_date          datetime,
        last_approval_change               datetime,
	-- has the link been approved? ( note that this is different from
	-- the approved_p in the table wite_wide_link_map ) 
	approved_p              char(1) check(approved_p in ('t','f')),
	approval_change_by     integer references users
);

-- Index on searchable fields

create index general_links_title_idx on general_links (link_title);

create sequence general_link_map_id start 1;

-- This table associates urls with any item in the database

create table site_wide_link_map (
	map_id          integer primary key,
	link_id                 integer not null references general_links,
	-- the table is this url associated with 
	on_which_table          varchar(30) not null,
	-- the row in *on_which_table* the url is associated with
	on_what_id              integer not null,
	-- a description of what the url is associated with
	one_line_item_desc      varchar(200) not null,
	-- who made the association
	creation_time	    	    datetime default current_timestamp not null,
	creation_user		    integer not null references users(user_id),
	creation_ip_address	    varchar(20) not null,
	last_modified		    datetime,
	last_modifying_user	    integer references users(user_id),
	-- has the link association  been approved ?
	approved_p              char(1) check(approved_p in ('t','f')),
	approval_change_by     integer references users
);

create index swlm_which_table_what_id_idx on site_wide_link_map (on_which_table, on_what_id);

-- We want users to be able to rate links
-- These ratings could be used in the display of the links
-- eg, ordering within category by rating, or displaying 
-- fav. links for people in a given group..

create table general_link_user_ratings (
	user_id         integer not null references users,
 	link_id         integer not null references general_links,
	-- a user may give a url a rating between 0 and 10
	rating          integer not null check(rating between 0 and 10 ),
	-- require that the user/url rating is unique
	primary key(link_id, user_id) 
);


insert into table_acs_properties (table_name, section_name, user_url_stub, admin_url_stub)
values ('general_links', 'General Links', '/general-links/view-one.tcl?link_id=', '/admin/general-links/edit-link.tcl?link_id=');

-- trigger for user ratings
-- PGsql doesn't have statement level triggers, so we move this to a row-level trigger.
-- That's fine because the reason this was a statement-level trigger to begin
-- with is once again Oracle's limitation of triggers reading from the table that triggered them.
-- (BMA)

create function trig_general_links_rating_update() returns opaque
as '
DECLARE
	v_n_ratings	integer;
	v_avg_rating	numeric;
BEGIN
	select count(*), avg(rating) into v_n_ratings, v_avg_rating from general_link_user_ratings
	where link_id= NEW.link_id;

	update general_links
	set n_ratings= v_n_ratings,
	avg_rating= v_avg_rating
	where link_id= NEW.link_id;

	return NEW;
END;
' language 'plpgsql';

create trigger general_links_rating_update
after insert or update on general_link_user_ratings
for each row
execute procedure trig_general_links_rating_update();

--
-- Postgres stuff (BMA)
--