-- sql/mailboxes.sql
-- This file is part of Decimail; see http://decimail.org
-- (C) 2004-7 Philip Endecott
 
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 2 of the License, or
-- any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
 

drop table years cascade;
drop view year_timeranges cascade;
drop table months cascade;
drop view month_timeranges cascade;
drop view all_timeranges cascade;
drop view timerange_common_mailboxes cascade;
drop table recent_common_mailboxes cascade;
drop table misc_common_mailboxes cascade;
drop view by_from_common_mailboxes cascade;
drop view all_role_common_mailboxes cascade;
--drop view www_common_mailboxes cascade;
drop view common_mailboxes cascade;
drop view common_mailboxes_per_user cascade;
drop view mailboxes_view cascade;
drop trigger users_changed on users cascade;



begin;

create table years (
	year	integer	not null
);

insert into years values (1995);
insert into years values (1996);
insert into years values (1997);
insert into years values (1998);
insert into years values (1999);
insert into years values (2000);
insert into years values (2001);
insert into years values (2002);
insert into years values (2003);
insert into years values (2004);
insert into years values (2005);
insert into years values (2006);
insert into years values (2007);

create view year_timeranges as select
	('Chronological/' || year || '/All Messages' ) as mailbox_name,
	(year || '-01-01 00:00:00')::timestamp with time zone as start_time,
	(year+1 || '-01-01 00:00:00')::timestamp with time zone as end_time
	from years;

create table months (
	monthnum	integer not null,
	monthname	text
);

insert into months values (1, '01-January');
insert into months values (2, '02-February');
insert into months values (3, '03-March');
insert into months values (4, '04-April');
insert into months values (5, '05-May');
insert into months values (6, '06-June');
insert into months values (7, '07-July');
insert into months values (8, '08-August');
insert into months values (9, '09-September');
insert into months values (10, '10-October');
insert into months values (11, '11-November');
insert into months values (12, '12-December');

create view month_timeranges as select
	('Chronological/' || year || '/' || monthname) as mailbox_name,
	(year || '-' || monthnum || '-01 00:00:00')::timestamp with time zone
		as start_time,
	(year || '-' || monthnum || '-01 00:00:00')::timestamp with time zone
		+ interval '1 month' as end_time
	from years, months;


create view all_timeranges as select
	* from year_timeranges
	union select * from month_timeranges;


create view timerange_common_mailboxes as select
	mailbox_name,
	('select msg_id from u_messages where '
	 'msgdate between timestamp with time zone \'' || start_time || '\''
	 ' and timestamp with time zone \'' || end_time || '\'') as query
	from all_timeranges
	order by start_time;


create table recent_common_mailboxes (
	mailbox_name	text	not null,
	query		text	not null
);

insert into recent_common_mailboxes
	values ('This Month',
	'select msg_id from u_messages where msgdate > now()::timestamp with time zone - \'1 month\'::interval');
insert into recent_common_mailboxes
	values ('This Week',
	'select msg_id from u_messages where msgdate > now()::timestamp with time zone - \'1 week\'::interval');
insert into recent_common_mailboxes
	values ('Today',
	'select msg_id from u_messages where msgdate > now()::timestamp with time zone - \'1 day\'::interval');


create table misc_common_mailboxes (
	mailbox_name	text	not null,
	query		text	not null
);



--insert into misc_common_mailboxes values ('Lists/All Today',
--'select msg_id from to_addrs ta join mailing_lists ml on (ta.addr = ml.submit_addr) join messages m using(msg_id) where m.msgdate > now()::timestamp with time zone - \'1 day\'::interval');

--insert into misc_common_mailboxes values ('Today not lists',
--'select msg_id from  messages m where m.msgdate > now()::timestamp with time zone - \'1 day\'::interval  and not exists (select 1 from to_addrs ta join mailing_lists ml on (ta.addr = ml.submit_addr) where ta.msg_id = m.msg_id)');

insert into misc_common_mailboxes values ('All Messages',
					  'select msg_id from u_messages');

insert into misc_common_mailboxes values ('INBOX', 'select 0 as msg_id where false');



create view common_mailboxes as
	      select * from timerange_common_mailboxes
	union select * from recent_common_mailboxes
--	union select * from www_common_mailboxes
	union select * from misc_common_mailboxes;


create view common_mailboxes_per_user as select
	username,
	mailbox_name,
	query
	from users cross join common_mailboxes;

create view mailboxes_view as select
	* from common_mailboxes_per_user
	union select * from people_mailboxes
        union select * from domain_mailboxes
        union select * from subject_mailboxes
        union select * from label_mailboxes
	union select * from spam_mailboxes
	union select * from lists_mailboxes
        union select * from keyword_mailboxes;


create trigger users_changed
	after insert or update or delete on users
	for each statement
	execute procedure update_mailboxes();


truncate mailboxes;
insert into mailboxes (username, mailbox_name, query)
	select username, mailbox_name, query from mailboxes_view;



end;