-- sql/actions/lists.sql
-- This file is part of Decimail; see http://decimail.org
-- (C) 2007 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 lists cascade;
drop view lists_mailboxes cascade;
drop trigger lists_changed on lists cascade;
delete from actions where mailbox_pattern='Lists/%';

begin;

create table lists (
    username     text  not null,
    list_name    text  not null,
    submit_addr  text,
    custom_addr  text,
    primary key (username, list_name)
);

-- To make it possible to create new mailboxes under Lists/ 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 lists (username, list_name, submit_addr)
--   values ('your-username-here', 'Example', 'example-list@example.com'); 


create view lists_mailboxes as select
    username,
    ('Lists/' || list_name) as mailbox_name,
    (case when submit_addr is null then 'select 0 as msg_id where false'
     else 'select msg_id from u_recipients where addr=\'' || submit_addr || '\''
     end) as query
    from lists;


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


insert into actions (username, mailbox_pattern, create_action, delete_action,
                     rename_action, copy_action)
values ('phil',
        'Lists/%',
        'insert into lists (username,list_name,custom_addr) values (''$U'',replace(''$MB'',''Lists/'',''''),''spam_from_''||replace(''$MB'',''Lists/'','''')||''@chezphil.org'')',
        'delete from lists where username=''$U'' and list_name=replace(''$MB'',''Lists/'','''')',
        'update lists set list_name=replace(''$NMB'',''Lists/'','''') where username=''$U'' and list_name=replace(''$OMB'',''Lists/'','''')',
        'update lists set submit_addr=(select addr from u_recipients where msg_id=$MSG_ID) where username=''$U'' and list_name=replace(''$MB'',''Lists/'','''')'
       );

end;
