--
-- Threads. A new way to organize your life.
-- by Ben Adida (ben@adida.net)
--

create sequence thread_id_sequence;

create table threads (
       thread_id     integer not null primary key,
       thread_name   varchar(200),
       start_date     datetime,
       end_date	     datetime,
       priority	     integer not null check (priority >= 1 AND priority <= 9),
       -- A thread can be: blocked if waiting input from someone else, suspended
       -- if not important for now, and active if being actively pursued.
       thread_state  varchar(100) not null check (thread_state in ('blocked', 'suspended', 'active')),
       description   varchar(4000)
);

-- Who can access what thread
create table thread_user_map (
       thread_id	     integer not null references threads,
       user_id		     integer not null references users,
       primary key (thread_id, user_id)
);

create function user_can_see_thread_p(integer, integer)
returns char as '
DECLARE
	v_user_id alias for $1;
	v_thread_id alias for $2;
	v_count integer;
BEGIN
	select count(*) into v_count from thread_user_map where thread_id= v_thread_id
	and user_id= v_user_id;
	if v_count > 0
	then return ''t'';
	else return ''f'';
	end if ;
END;
' language 'plpgsql';

create sequence note_id_sequence;

create table notes (
       note_id	   integer not null primary key,
       user_id	   integer not null references users,
       thread_id   integer not null references threads,
       note_date   datetime,
       content	   varchar(4000)
);

-- Some notes might link to a new thread
create table note_thread_map (
       note_id		     integer not null references notes,
       thread_id	     integer not null references threads,
       primary key (note_id, thread_id)
);