--
-- a data model for "to do lists"
--
-- by Ben Adida (ben@mit.edu)

create sequence todo_list_id_sequence;

create table todo_lists (
	list_id		integer not null primary key,
	user_id		integer not null references users,
	list_name	varchar(100),
	list_details	varchar(4000),
	creation_date	datetime,
	public_p	char(1) default 'f' check (public_p in ('t', 'f')),
	due_date	datetime
);

create index todo_lists_by_user_id on todo_lists(user_id);

--
-- A table that says which users are allowed to assign things
-- to another list for another user
--

create table todo_list_user_map (
	list_id			integer not null references todo_lists,
	user_id			integer not null references users,
	automatic_approval	char(1) default 't' check (automatic_approval in ('t','f')),
	primary key (list_id, user_id)
);

create table todo_list_user_group_map (
       list_id			integer not null references todo_lists,
       user_group_id		integer not null references user_groups,
       primary key (list_id, user_group_id)
);

create sequence todo_item_id_sequence;

create table todo_items (
	item_id		integer not null primary key,
	list_id		integer not null references todo_lists,
	-- properties
	priority	integer check (priority between 1 and 5),
	creation_date	datetime,
	due_date	datetime,
	completion_date	datetime,
	deletion_date	datetime,
	-- this can be assigned by someone else
	assigned_by	integer references users,
	approved_p	char(1) default 't' check (approved_p in ('t','f')),
	item_details	varchar(4000)
);


create view deleted_todo_items as select * from todo_items where deletion_date is not null;
create view viewable_todo_items as select * from todo_items where deletion_date is null;

create view open_todo_items as select * from viewable_todo_items where completion_date is NULL;
create view closed_todo_items as select * from viewable_todo_items where completion_date is NOT NULL;

create view approved_todo_items as select * from viewable_todo_items where approved_p='t';
create view unapproved_todo_items as select * from viewable_todo_items where approved_p='f';
create view pending_todo_items as select * from viewable_todo_items where approved_p is NULL;

-- user preferences
create table todo_user_preferences (
	user_id			integer not null primary key references users,
	-- a bunch of todo preferences
	-- an expiration for old items
	old_item_expire		integer default 7 not null,
	-- when sorting items, sort by
	sort_by			varchar(100),
	-- mail notification
	notification_interval	integer default 1 not null,
	-- last time user was notified
	last_notification	datetime,
	-- which time zone, and when does midnight start
	time_offset		integer,
	personal_midnight	integer,
	-- separate the completed items or not
	separate_completed_items_p	char(1) default 'f' check (separate_completed_items_p in ('t','f'))
);


-- A function to tell if two dates are on same day
create function same_day_p(datetime, datetime, integer) returns char
as '
DECLARE
	first_date alias for $1;
	second_date alias for $2;
	personal_midnight alias for $3;
	first_day	char(2);
	second_day	char(2);
	difference	numeric;
BEGIN
	IF first_date is NULL or second_date is NULL
	THEN return ''f'';
	END IF;
	
	first_day:= date_part (''day'', first_date - (personal_midnight || '' hours'')::timespan);
	second_day:= date_part (''day'', second_date - (personal_midnight || '' hours'')::timespan);

	difference:= date_num_days(second_date - first_date);
	
	IF abs(difference)>1
	THEN return(''f'');
	END IF;


	IF first_day != second_day
	THEN return(''f'');
	END IF;

	return(''t'');
END;
' language 'plpgsql';

	

-- a table for correspondance between relative dates in pretty
-- format, and what they're displayed as
create table relative_date_pretty_text (
	pretty_date		varchar(25),
	min_days		integer,
	max_days		integer,
	days			integer
);

insert into relative_date_pretty_text 
(pretty_date, min_days, max_days, days)
values
('in a month', 25, 35, 30);
insert into relative_date_pretty_text 
(pretty_date, min_days, max_days, days)
values
('in a week', 5, 9, 7);
insert into relative_date_pretty_text 
(pretty_date, min_days, max_days, days)
values
('in a few days', 2, 5, 3);
insert into relative_date_pretty_text 
(pretty_date, min_days, max_days, days)
values
('tomorrow', 1, 2, 1);
insert into relative_date_pretty_text 
(pretty_date, min_days, max_days, days)
values
('today', -1, 1, 0);



-- a function to display the date in a nice way
create function pretty_relative_date(datetime) returns varchar
as '
DECLARE
BEGIN
	return pretty_relative_date( $1,0);
END;
' language 'plpgsql';

create function pretty_relative_date(datetime, integer) returns varchar
as '
DECLARE
	v_date alias for $1;
	midnight_offset alias for $2;
	same_days		char(1);
	difference_in_days	numeric;
	v_pretty_date		varchar(25);
BEGIN
	IF v_date is NULL
	THEN return(NULL);
	END IF;

	difference_in_days:= date_num_days(v_date-sysdate());
	same_days:= same_day_p(sysdate(), v_date, midnight_offset);
	
	IF same_days=''f'' AND difference_in_days>= -1 AND difference_in_days<0
 	THEN difference_in_days:= -1.5;	
	END IF;

	IF same_days=''f'' AND difference_in_days>= 0 and difference_in_days<= 1
	THEN difference_in_days:= 1.5;
	END IF;

	select pretty_date into v_pretty_date from relative_date_pretty_text where (min_days is NULL or min_days<= difference_in_days) AND (max_days is NULL or max_days> difference_in_days) AND days is NOT NULL;
	
	return(v_pretty_date);	
END;
' language 'plpgsql';

--
-- a function to get the days field from a number of days from the pretty text
--
create function todo_days_from_pretty(varchar) returns numeric
as '
DECLARE
	pretty_text alias for $1;

	return_num  numeric;
BEGIN
	select days into return_num from relative_date_pretty_text where pretty_date= pretty_text;

	return(return_num);
END;
' language 'plpgsql';


-- A function to find the min in the interval
create function todo_interval_min(datetime, integer) returns integer
as '
DECLARE
	v_date alias for $1;
	midnight_offset alias for $2;
	v_return_num integer;
BEGIN
	return(todo_days_from_pretty(pretty_relative_date(v_date, midnight_offset)));
END;
' language 'plpgsql';

create function todo_interval_min(datetime) returns integer
as '
DECLARE
BEGIN
	return(todo_interval_min($1, 0));
END;
' language 'plpgsql';

-- A function to check access
create function user_can_access_list_p(integer, integer) returns char
as '
DECLARE
	v_user_id alias for $1;
	v_list_id alias for $2;
	v_count	  integer;
	v_public_p char(1);
BEGIN
	select public_p into v_public_p from todo_lists where list_id= v_list_id;
	IF v_public_p = ''t''
	then return(''t'');
	end if;

	select count(*) into v_count from todo_lists where user_id = v_user_id and list_id = v_list_id;

	IF (v_count>0)
	THEN return(''t'');
	END IF;

	select count(*) into v_count from todo_list_user_map where list_id= v_list_id and user_id= v_user_id;
	
	IF (v_count>0)
	THEN return(''t'');
	END IF;

	select count(*) into v_count from todo_list_user_group_map where list_id= v_list_id and 0<(select count(*) from user_group_map where group_id= todo_list_user_group_map.user_group_id and user_id= v_user_id);

	IF (v_count>0)
	THEN return(''t'');
	END IF;
	
	return(''f'');
END;
' language 'plpgsql';