--
-- A data model for keeping notification information for users
-- 
-- ben@mit.edu
--

create sequence notification_class_id_sequence;

create table user_notification_classes (
       notification_class_id	       integer not null primary key,
       notification_class	       varchar(200),
       -- The item URL is the URL to get to one item that someone was notified about
       item_url			       varchar(300),
       class_url		       varchar(300),
       -- The varname is the variable name to replace "item_id"
       item_pretty_name		       varchar(100),
       item_varname		       varchar(100)
);

create table user_notification_prefs (
       user_id			     integer not null references users,
       notification_class_id	     integer not null references user_notification_classes,
       primary key (user_id, notification_class_id),
       notification_pref	     varchar(50) default 'none' check (notification_pref in ('none','instant', 'hourly','daily','weekly'))
);

create table user_notifications (
       user_id			     integer not null,
       notification_class_id	     integer not null,
       primary key (user_id, notification_class_id),
       foreign key (user_id, notification_class_id) references user_notification_prefs,
       notification_content	     lztext
);

create function trig_notif_addrow() returns opaque
as '
DECLARE
	v_check integer;
BEGIN
	select count(*) into v_check
	from user_notification_prefs where user_id= NEW.user_id and notification_class_id= NEW.notification_class_id;

	IF v_check = 0
	THEN
	insert into user_notification_prefs (user_id, notification_class_id, notification_pref)
	values (NEW.user_id, NEW.notification_class_id, ''instant'');
	END IF;

	RETURN NEW;
END;
' language 'plpgsql';

create trigger notif_add_row
before insert on user_notifications
for each row
execute procedure trig_notif_addrow();

create table user_notification_interest_map (
       user_id				    integer not null references users,
       notification_class_id		    integer not null references user_notification_classes,
       item_id				    integer not null,
       primary key (user_id, notification_class_id, item_id)
);
       

create function trig_notification_add_row() returns opaque
as '
DECLARE
	v_check integer;
BEGIN
	select count(*) into v_check
	from user_notifications where user_id= NEW.user_id and notification_class_id= NEW.notification_class_id;

	IF v_check = 0
	THEN
	insert into user_notifications (user_id, notification_class_id, notification_content)
	values (NEW.user_id, NEW.notification_class_id, '''');
	END IF;

	RETURN NEW;
END;
' language 'plpgsql';

create trigger notification_add_row
before insert on user_notification_interest_map
for each row
execute procedure trig_notification_add_row();

-- PL/SQL to do notifications
create function user_add_notification(integer, integer, varchar) returns integer
as '
DECLARE
	v_class_id alias for $1;
	v_item_id alias for $2;
	v_message alias for $3;
	v_one_class	user_notification_classes%ROWTYPE;
BEGIN
	select * into v_one_class from user_notification_classes where notification_class_id= v_class_id;

	update user_notifications set
	notification_content= coalesce(notification_content,'''') || to_char(sysdate(),''YYYY-MM-DD'') || '' '' || 
			      v_one_class.item_pretty_name || '' #'' || v_item_id ||
			      '': '' || v_message || ''\n'' || v_one_class.item_url || ''?'' || 
			      v_one_class.item_varname || ''='' || v_item_id || ''\n\n''
	where user_id in (select user_id from user_notification_interest_map where notification_class_id= v_class_id and item_id= v_item_id)
	and notification_class_id= v_class_id;

	return 1;
END;
' language 'plpgsql';


create function notification_get_pref(integer,integer) returns char
as '
DECLARE
	v_user_id alias for $1;
	v_class_id alias for $2;
BEGIN
	return notification_pref from user_notification_prefs where user_id= v_user_id and notification_class_id= v_class_id;
END;
' language 'plpgsql';