-- sql/actions/spam.sql
-- This file is part of Decimail; see http://decimail.org
-- (C) 2006 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 spamtypes cascade;
drop table spam_addresses cascade;
drop view spam_mailboxes cascade;
drop trigger spamtypes_changed on spamtypes cascade;
delete from actions where mailbox_pattern='Spam/%';

begin;

create table spamtypes (
    username  text  not null,
    spamtype  text  not null,
    primary key (username, spamtype)
);

-- To make it possible to create new mailboxes under Spam/ there
-- must be at least one mailbox already present, since empty folders are
-- not found by Decimail's LIST command.  So you'll need to run something
-- like this:

-- insert into spamtypes (username, spamtype) values ('your-username-here', 'Spam'); 

create table spam_addresses (
    username  text  not null,
    spamtype  text  not null,
    email     text  not null,
    primary key (username, spamtype, email),
    foreign key (username, spamtype) references spamtypes
        on delete cascade on update cascade
);

create index spam_addresses_by_email on spam_addresses(email);


create view spam_mailboxes as select
    username,
    ('Spam/' || spamtype) as mailbox_name,
    ('select 0 as msg_id where false'::text) as query
    from spamtypes;


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


insert into actions (username, mailbox_pattern, create_action, delete_action,
                     rename_action, copy_action)
values ('phil',
        'Spam/%',
        'insert into spamtypes (username,spamtype) values (''$U'',replace(''$MB'',''Spam/'',''''))',
        'delete from spamtypes where username=''$U'' and spamtype=replace(''$MB'',''Spam/'','''')',
        'update spamtypes set spamtype=replace(''$NMB'',''Spam/'','''') where username=''$U'' and spamtype=replace(''$OMB'',''Spam/'','''')',
        'insert into spam_addresses select ''$U'' as username, replace(''$MB'',''Spam/'','''') as spamtype, addr as email from u_recipients where msg_id=$MSG_ID'); 
end;
