/*
 * Copyright (C) The Apache Software Foundation. All rights reserved.
 *
 * This software is published under the terms of the Apache Software License
 * version 1.1, a copy of which has been included with this distribution in
 * the LICENSE file.
 */
package org.apache.james.transport.mailets;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.Collection;
import java.util.Iterator;
import java.util.Vector;

import javax.mail.MessagingException;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
import javax.mail.internet.ParseException;

import org.apache.avalon.cornerstone.services.datasource.DataSourceSelector;
import org.apache.avalon.excalibur.datasource.DataSourceComponent;
import org.apache.avalon.framework.component.ComponentManager;

import org.apache.james.Constants;

import org.apache.mailet.GenericMailet;
import org.apache.mailet.Mail;
import org.apache.mailet.MailAddress;
import org.apache.mailet.MailetException;


/**
 * Rewrites recipient addresses based on a database table.
 * Based on JBDCListserv this list server mailet takes both the list members and the list details from a couple of database tables
 * The first table is the list data table, which contains the names of the lists, and their parameters (prefix, reply_to_list etc)
 * The second table contains mappings of subscriber addresses and list addresses
 *
 * This works with the InJDBCListserv matcher
 * EG:
 *
 * &lt;mailet match="InJDBCListsListserv=db://maildb/lists" class="JDBCListsListserv"><br>
 * &lt;data_source>maildb&lt;/data_source><br>
 * &lt;listserv_table>lists&lt;/listserv_table><br>
 * &lt;members_table>listmembers&lt;/members_table><br>
 * &lt;/mailet><br>
 *
 * @see org.apache.james.transport.matchers.InJDBCListsListserv
 *
 * @author  Danny Angus <danny@apache.org>
 * This is $Revision: 1.22 $
 * Committed on $Date: 2002/03/11 21:46:32 $ by: $Author: serge $
 */
public class JDBCListsListserv extends GenericMailet {
    protected boolean attachmentsAllowed     = true;
    protected boolean cacheSettings          = true;
    protected DataSourceComponent datasource = null;
    protected MailAddress listservAddress    = null;
    protected String listservID              = null;
    //Queries to DB
    protected String listservQuery           = null;
    protected String listservTable           = null;
    //Settings for this listserv
    protected Collection members             = null;
    protected boolean membersOnly            = true;
    protected String membersQuery            = null;
    protected String membersTable            = null;
    protected String peopleTable             = null;
    protected boolean replyToList            = true;
    protected String subjectPrefix           = null;

    /**
     * Returns whether this listserv allow attachments
     */
    public boolean isAttachmentsAllowed() throws MessagingException {
        return attachmentsAllowed;
    }

    /**
     * The email address that this listserv processes on.  If returns null, will use the
     * recipient of the message, which hopefully will be the correct email address assuming
     * the matcher was properly specified.
     */
    public MailAddress getListservAddress() throws MessagingException {
        return listservAddress;
    }

    /**
     * Add Description
     *
     * @return Document return!
     */
    public String getMailetInfo() {
        return "JDBC Lists Listserver mailet";
    }

    /**
     * Returns a Collection of MailAddress objects of members to receive this email
     */
    public Collection getMembers() throws MessagingException {
        loadSettings();
        return members;
    }

    /**
     * Returns whether this list should restrict to senders only
     */
    public boolean isMembersOnly() throws MessagingException {
        return membersOnly;
    }

    /**
     * Returns whether listserv should add reply-to header
     */
    public boolean isReplyToList() throws MessagingException {
        return replyToList;
    }

    /**
     * An optional subject prefix which will be surrounded by [].
     */
    public String getSubjectPrefix() throws MessagingException {
        return subjectPrefix;
    }

    /**
     * Add Description
     *
     * @throws MessagingException Document throws!
     * @throws MailetException Document throws!
     */
    public void init() throws MessagingException {
        if(getInitParameter("data_source") == null) {
            throw new MailetException("data_source not specified for JDBCListserv");
        }
        if(getInitParameter("listserv_table") == null) {
            throw new MailetException("listserv_table not specified for JDBCListserv");
        }
        if(getInitParameter("members_table") == null) {
            throw new MailetException("members_table not specified for JDBCListserv");
        }
        if(getInitParameter("people_table") == null) {
            throw new MailetException("people_table not specified for JDBCListserv");
        }
        String datasourceName = getInitParameter("data_source");
        listservTable = getInitParameter("listserv_table");
        membersTable  = getInitParameter("members_table");
        peopleTable   = getInitParameter("people_table");
        Connection conn = null;
        try {
            ComponentManager componentManager = (ComponentManager)getMailetContext().getAttribute(
                                                        Constants.AVALON_COMPONENT_MANAGER);
            // Get the DataSourceSelector block
            DataSourceSelector datasources    = (DataSourceSelector)componentManager.lookup(
                                                        DataSourceSelector.ROLE);
            // Get the data-source required.
            datasource                        = (DataSourceComponent)datasources.select(
                                                        datasourceName);
            conn                              = datasource.getConnection();
            if(conn == null) {
                System.err.println("Cant get connection");
            }
            // Check if the required listserv table exists. If not, complain.
            DatabaseMetaData dbMetaData = conn.getMetaData();
            // Need to ask in the case that identifiers are stored, ask the DatabaseMetaInfo.
            // Try UPPER, lower, and MixedCase, to see if the table is there.
            if(!(tableExists(dbMetaData, listservTable) || tableExists(dbMetaData,
                                                                       listservTable.toUpperCase()) ||
                   tableExists(dbMetaData, listservTable.toLowerCase()))) {
                String create                     =
                        "CREATE TABLE " + listservTable + "(" +
                        "list_address VARCHAR (50) NOT NULL," + "members_only VARCHAR (10)," +
                        "attachments_allowed VARCHAR (10)," + "reply_to_list VARCHAR (10)," +
                        "subject_prefix VARCHAR (50)," + "PRIMARY KEY(list_address)" + ")";
                PreparedStatement createStatement = conn.prepareStatement(create);
                createStatement.execute();
                createStatement.close();
                log("created table " + listservTable);
            }
            // Check if the required members table exists. If not, complain.
            // Need to ask in the case that identifiers are stored, ask the DatabaseMetaInfo.
            // Try UPPER, lower, and MixedCase, to see if the table is there.
            if(!(tableExists(dbMetaData, membersTable) || tableExists(dbMetaData,
                                                                      membersTable.toUpperCase()) ||
                   tableExists(dbMetaData, membersTable.toLowerCase()))) {
                String create                     =
                        "CREATE TABLE " + membersTable + "(" +
                        "uid INTEGER NOT NULL AUTO_INCREMENT," + "list_address VARCHAR (50)," +
                        "member INTEGER," + "PRIMARY KEY(uid)," +
                        "FOREIGN KEY (list_address) REFERENCES lists (list_address)," +
                        "FOREIGN KEY (member) REFERENCES people (user_id)," +
                        "UNIQUE (list_address, member)" + ")";
                PreparedStatement createStatement = conn.prepareStatement(create);
                createStatement.execute();
                createStatement.close();
                log("created table " + membersTable);
            }
            if(!(tableExists(dbMetaData, peopleTable) || tableExists(dbMetaData,
                                                                     peopleTable.toUpperCase()) ||
                   tableExists(dbMetaData, peopleTable.toLowerCase()))) {
                String create                     =
                        "CREATE TABLE " + peopleTable + "(" +
                        "    user_id INTEGER NOT NULL AUTO_INCREMENT," +
                        "    email_address VARCHAR (50)," + " )";
                PreparedStatement createStatement = conn.prepareStatement(create);
                createStatement.execute();
                createStatement.close();
                log("created table " + peopleTable);
            }
            listservQuery = "SELECT members_only, attachments_allowed, reply_to_list, subject_prefix, list_address FROM " +
                            listservTable + " WHERE list_address = ?";
            membersQuery  = "SELECT  " + peopleTable + ".email_address as member FROM " +
                            membersTable + "," + peopleTable + " WHERE " + peopleTable +
                            ".user_id = " + membersTable + ".user_id and " + membersTable +
                            ".list_address = ?";
        } catch(Exception e) {
            throw new MessagingException("Error initializing JDBCAlias", e);
        } finally {
            try {
                if(conn != null) {
                    conn.close();
                }
            } catch(SQLException sqle) {
                sqle.printStackTrace();
            }
        }
    }

    /**
     * Add Description
     *
     * @param recipient Document parameter!
     * @return Document return!
     */
    public boolean matchRecipient(MailAddress recipient) {
        Connection conn = null;
        try {
            conn = datasource.getConnection();
            String query        = "Select count(*) from " + listservTable +
                                  " where list_address = '" + recipient.toString() + "'";
            PreparedStatement q = conn.prepareStatement(query);
            ResultSet rs        = q.executeQuery();
            rs.first();
            int count = rs.getInt(1);
            if(count > 0) {
                return true;
            }
            conn.close();
            log("closed conn");
        } catch(Exception e) {
            log("Cant connect to list to match recipients");
            e.printStackTrace();
        } finally {
            try {
                if(conn != null) {
                    conn.close();
                    log("closed conn");
                }
            } catch(SQLException sqle) {
                sqle.printStackTrace();
                log("Cant close conn");
            }
        }
        return false;
    }

    /**
     * Add Description
     *
     * @param mail Document parameter!
     * @throws MessagingException Document throws!
     * @throws MailetException Document throws!
     */
    public void service(Mail mail) throws MessagingException {
        listservAddress = getListAddress(mail);
        Collection members = new Vector();
        members.addAll(getMembers());
        //Check for members only flag....
        if(isMembersOnly() && !members.contains(mail.getSender())) {
            //Need to bounce the message to say they can't send to this list
            getMailetContext().bounce(mail,
                                      "Only members of this listserv are allowed to send a message to this address.");
            mail.setState(Mail.GHOST);
            return;
        }
        //Check for no attachments
        try {
            if(!isAttachmentsAllowed() &&
               mail.getMessage().getContent() instanceof MimeMultipart) {
                getMailetContext().bounce(mail, "You cannot send attachments to this listserv.");
                mail.setState(Mail.GHOST);
                return;
            }
        } catch(Exception ioe) {
            ioe.printStackTrace();
            throw new MailetException("Error creating listserv message", ioe);
        }
        MimeMessage message = mail.getMessage();
        //Set the subject if set
        if((getSubjectPrefix() != null) && (!getSubjectPrefix().equalsIgnoreCase(""))) {
            String prefix = "[" + getSubjectPrefix() + "]";
            String subj   = message.getSubject();
            if(subj == null) {
                subj = "";
            }
            //replace Re: with RE:
            String re ="Re:";
            int index = subj.indexOf(re);
            while(index > -1){
                subj = subj.substring(0, index) +"RE: "+ subj.substring(index + re.length());
                index = subj.indexOf(re);
            }
            //reduce them to one at the beginning
            re ="RE:";
            index = subj.indexOf(re,re.length());
             while(index > 0){
                subj = subj.substring(0, index) + subj.substring(index + re.length());
                index = subj.indexOf(re,1);
            }
            //If the "prefix" is in the subject line, remove it and everything before it
            index = subj.indexOf(prefix);
            if(index > -1) {
                if(index == 0) {
                    subj = prefix + ' ' + subj.substring(index + prefix.length());
                } else {
                    subj = prefix + ' ' + subj.substring(0, index) +
                           subj.substring(index + prefix.length() + 1);
                }
            } else {
                subj = prefix + ' ' + subj;
            }
            message.setSubject(subj);
        }
        if(listservAddress == null) {
            //Use the recipient
            listservAddress = (MailAddress)mail.getRecipients().iterator().next();
        }
        if(isReplyToList()) {
            message.setHeader("Reply-To", listservAddress.toString());
        }
        //Send the message to the list members
        getMailetContext().sendMail(listservAddress, members, message);
        //Kill the old message
        mail.setState(Mail.GHOST);
    }

    protected void loadSettings() throws MessagingException {
        Connection conn        = null;
        PreparedStatement stmt = null;
        ResultSet rs           = null;
        try {
            //Load members
            conn = datasource.getConnection();
            stmt = conn.prepareStatement(membersQuery);
            stmt.setString(1, listservAddress.toString());
            rs = stmt.executeQuery();
            Collection tmpMembers = new Vector();
            while(rs.next()) {
                String address = rs.getString(1);
                try {
                    MailAddress mailAddress = new MailAddress(address);
                    tmpMembers.add(mailAddress);
                } catch(ParseException pe) {
                    //don't stop... just log and continue
                    log("error parsing address '" + address + "' in listserv '" +
                        listservAddress + "'");
                }
            }
            members = tmpMembers;
            rs.close();
            stmt.close();
            stmt = conn.prepareStatement(listservQuery);
            stmt.setString(1, listservAddress.toString());
            rs = stmt.executeQuery();
            if(!rs.next()) {
                throw new MailetException("Could not find listserv record for '" +
                                          listservAddress + "'");
            }
            membersOnly        = rs.getBoolean("members_only");
            attachmentsAllowed = rs.getBoolean("attachments_allowed");
            replyToList        = rs.getBoolean("reply_to_list");
            subjectPrefix      = rs.getString("subject_prefix");
            String address     = rs.getString("list_address");
            if(address == null) {
                listservAddress = null;
            } else {
                try {
                    listservAddress = new MailAddress(address);
                } catch(ParseException pe) {
                    //log and ignore
                    log("invalid listserv address '" + listservAddress + "' for listserv '" +
                        listservAddress + "'");
                    listservAddress = null;
                }
            }
            rs.close();
            stmt.close();
        } catch(SQLException sqle) {
            sqle.printStackTrace();
            throw new MailetException("Problem loading settings", sqle);
        } finally {
            try {
                if(conn != null) {
                    conn.close();
                    log("cloed conn");
                }
            } catch(SQLException sqle) {
                throw new MailetException("Problem closing connection", sqle);
            }
        }
    }

    private MailAddress getListAddress(Mail mail) {
        for(Iterator i = mail.getRecipients().iterator(); i.hasNext();) {
            MailAddress rec = (MailAddress)i.next();
            if(matchRecipient(rec)) {
                //yes I know.. this will prevent cross postings, is that so bad? :-)
                return rec;
            }
        }
        return null;
    }

    /**
     * Checks database metadata to see if a table exists.
     */
    private boolean tableExists(DatabaseMetaData dbMetaData, String tableName)
                         throws SQLException {
        ResultSet rsTables = dbMetaData.getTables(null, null, tableName, null);
        boolean found      = rsTables.next();
        rsTables.close();
        return found;
    }
}