package org.ngbw.utils;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.TreeSet;

import javax.mail.Message;
import javax.mail.MessagingException;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;
import org.ngbw.sdk.Workbench;

import org.ngbw.sdk.database.ConnectionManager;
import org.ngbw.sdk.database.DriverConnectionSource;
import org.ngbw.sdk.database.User;


public class EmailOnCertainDay
{
    public static final String EMAIL_HOST = "smtp.ucsd.edu";
    public static final String EMAIL_SENDER2 = "cipresadmin@sdsc.edu";
    
    private static final String days = "days";
    private static final String months = "months";
    private static final String sus = "sus";
    private static final String expired = "expired";
    private static final String expired7 = expired + 7;
    private static final String expired30 = expired + 30;
    
    private Map<String, String> mailTitles = createTitleMap();
    private Map<String, String> mailBodys = createBodyMap();
    private Map<Integer, String> firstStatements = createFirstStatementMap();
    
    private static final String openingWords = "Dear CIPRES User ";
    private static final String body = "With 15 years of experience supporting users, CIPRES remains the only public resource that offers:<br>" +
                                       "<ul><li>Access to important community codes including BEAST, BEAST2, MrBayes, and RAxML</li>" +
                                       "<li>Automatic run configuration to ensure highly efficient use of precious compute time.</li>" +
                                       "<li>Robust, resumable transfer of multi-GB files via Globus.f</li>"  + 
                                       "<li>Indefinite storage of up to 150 GB.</li>" +
                                       "<li>Timely user support</li></ul><br>" +
                                       "What our users say:<br>" + 
                                       "<i>\"The computations took 6 minutes for my input file, while trying to run this on my PC it " +
                                       "collapsed after 2 weeks! User friendly server … which greatly facilitates research!\"<br>" +
                                       "\"Thanks.  I’m in the jungles of Panama for fieldwork, can't tell you how helpful it is to have this resource.\"<br>" +
                                       "\"The wall time for these data sets are 30 min (2000 Bootstrap reps) [on CIPRES], but locally it would take 24 hours for each run. So, instead of 10 days to complete all the analyses with a few local computers, I have publication quality trees overnight.\"</i><br><br>";
                            
            
            
    
    
    private static Map<String, String> createTitleMap() 
    {
        Map<String,String> myMap = new HashMap<String,String>();
        myMap.put(days, "Need help with creating and submitting a CIPRES job?");
        myMap.put(months, "Act Now: Your CIPRES trial is about to end!");
        myMap.put(sus, "Act Now: Your CIPRES trial is about to end!");  
        myMap.put(expired, "We want you back!");
        return myMap;
    }
  
    private static Map<String, String> createBodyMap() 
    {
        Map<String,String> myMap = new HashMap<String,String>();
        myMap.put(days, "Thank you for creating a new CIPRES account. We noticed that you have not submitted a job yet.<br><br>"
                         + "If you need help getting started, please visit our <a href=\"https://www.phylo.org/index.php/help/\">help</a> page. "
                         + "Each code has an information page with example data sets and results files, so you can try out individual codes. "
                         + "We also have a <a href=\"https://groups.google.com/g/cipres-science-gateway-users\">Google Group</a> for timely help and advice. You may also contact me directly at mmiller@ucsd.edu. "
                         + "Please let us know if you encounter any issues in using your account.<br><br>"
                         + "Sincerely,<br><br>"
                         + "Mark Miller
<br>"
                         + "CIPRES Project Lead<br><br><img src=\"https://www.phylo.org/images/logo_new.jpg\" alt=\"\" height=\"100\" width=\"255\" />");        
        myMap.put(months, "There are only about 30 days left in your CIPRES free trial. Don’t lose access to all 20+ supported community codes in the fully supported CIPRES workbench. " +
                          "By purchasing a subscription for <a href=\"https://www.phylo.org/index.php/help/subscriptions.html\">as little as $30</a> (US), you will get priority queuing and retain access to CIPRES features for a full year.<br><br>"
                         + body
                         + "Sincerely,<br><br>"
                         + "Mark Miller
<br>"
                         + "CIPRES Project Lead<br><br><img src=\"https://www.phylo.org/images/logo_new.jpg\" alt=\"\" height=\"100\" width=\"255\" />");
        myMap.put(sus, "You have nearly reached (or already reached) the limit of 1,000 hours in your free trial. Get the convenience of submitting jobs to the priority queue on CIPRES for a full year by purchasing a subscription <a href=\"https://www.phylo.org/index.php/help/subscriptions.html\">for as little as $30 US</a>. <br><br>"
                         + body
                         + "Sincerely,<br><br>"
                         + "Mark Miller
<br>"
                         + "CIPRES Project Lead<br><br><img src=\"https://www.phylo.org/images/logo_new.jpg\" alt=\"\" height=\"100\" width=\"255\" />"); 
       myMap.put(expired7, "Your CIPRES free trial has now ended. You can regain the convenience of submitting jobs and get priority queuing " +
                          "by purchasing a subscription for <a href=\"https://www.phylo.org/index.php/help/subscriptions.html\">as little as $30</a> (US).<br><br>"
                         + body
                         + "Sincerely,<br><br>"
                         + "Mark Miller
<br>"
                         + "CIPRES Project Lead<br><br><img src=\"https://www.phylo.org/images/logo_new.jpg\" alt=\"\" height=\"100\" width=\"255\" />");        
      myMap.put(expired30, "Your CIPRES free trial has ended. You can regain the convenience of submitting jobs CIPRES, and get priority queuing " +
                          "by purchasing a subscription for <a href=\"https://www.phylo.org/index.php/help/subscriptions.html\">as little as $30</a> (US).<br><br>"
                         + body
                         + "Sincerely,<br><br>"
                         + "Mark Miller
<br>"
                         + "CIPRES Project Lead<br><br><img src=\"https://www.phylo.org/images/logo_new.jpg\" alt=\"\" height=\"100\" width=\"255\" />");         
       return myMap;
    } 
    
    private static Map<Integer, String> createFirstStatementMap() 
    {
        Map<Integer,String> myMap = new HashMap<Integer,String>();
        myMap.put(new Integer(1), "Your CIPRES subscription will end in %d months. ");
        myMap.put(new Integer(5), "Your CIPRES subscription will end in 30 days. ");
        myMap.put(new Integer(6), "Your free trial at CIPRES has now ended. ");        
        return myMap;
    }
      

    // public methods
    public static void main ( String[] args )
    {
        int numOfDays = 0;
        int numOfMonths = 0;
        int numOfSus = 0;
        int numOfExpired = 0;
        boolean exceptionHappened =false;
        try{
            String susStr = System.getProperty(sus);
            if (susStr != null && !susStr.isEmpty())
                numOfSus = Integer.parseInt(susStr);
            else
            {
                String expiredStr = System.getProperty(expired);
                if (expiredStr != null && !expiredStr.isEmpty())
                    numOfExpired = Integer.parseInt(expiredStr);
                else
                {
                    String numOfDaysStr = System.getProperty(days);
                    String numOfMonthsStr = System.getProperty(months);
                    if (numOfDaysStr != null && !numOfDaysStr.isEmpty())
                        numOfDays = Integer.parseInt(numOfDaysStr);
                    if (numOfMonthsStr != null && !numOfMonthsStr.isEmpty())
                        numOfMonths = Integer.parseInt(numOfMonthsStr);
                }
            }
        }
        catch (Exception err)
        {
            exceptionHappened = true;
            System.out.print(err);
        }
        if (exceptionHappened || (numOfDays == 0 && numOfMonths == 0 && numOfSus == 0 && numOfExpired == 0))
        {
            System.out.println("Usage: java --cp <class path> -Dlog4j.configuration=sdk_log4j.xml org.ngbw.utils.EmailOnCertainDay -Ddays=<integer number> -Dmonths=<integer number> -Dsus=<integer number> -Dexpired=<integer number>");
            System.out.println("You need to specify at least one of the four parameters");
            System.exit(1);            
        }
        else if (numOfDays < 0 || numOfMonths < 0 || numOfSus < 0 || numOfExpired < 0)
        {
            System.out.println("Usage: java --cp <class path> -Dlog4j.configuration=sdk_log4j.xml org.ngbw.utils.EmailOnCertainDay -Ddays=<integer number> -Dmonths=<integer number> -Dsus=<integer number> -Dexpired=<integer number>");
            System.out.println("Negative number is not acceptable");
            System.exit(1);             
        }
        
        try
        {
            
            ConnectionManager.setConnectionSource(new DriverConnectionSource());

            Connection dbConn = ConnectionManager.getConnectionSource().getConnection();

            try
            {
                //dbConn.setAutoCommit(false);
                new EmailOnCertainDay().getCertainUsersAndSendEmail(dbConn, numOfDays, numOfMonths, numOfSus, numOfExpired);

                System.out.println("Done!");
            }
            finally
            {
                dbConn.close();
            }
        }
        catch ( Exception err )
        {
            err.printStackTrace(System.err);

            System.exit(1);
        }
    }

    private class UserInfoMatched
    {
        private long userId;
        private String userName;
        private String email;
        
        public UserInfoMatched(long userId, String userName, String email)
        {
            this.userId = userId;
            this.userName = userName;
            this.email = email;
        }
        
        public long getUserId()
        {
            return userId;
        }
        public String getUserName()
        {
            return userName;
        }
        public String getEmail()
        {
            return email;
        }
    }
    
    private void getCertainUsersAndSendEmail(Connection dbConn, int numOfDays, int numOfMonths, int numOfSus, int numOfExpired) throws SQLException, MessagingException, IOException
    {
        StringBuilder stmtBuilder = null;
        
        if (numOfExpired > 0)
        {
            stmtBuilder = new StringBuilder("SELECT U.USER_ID, U.USERNAME, U.EMAIL FROM users U WHERE USER_ID IN (SELECT USER_ID FROM user_su_transaction WHERE USER_ID IN (SELECT USER_ID FROM user_su_allocation WHERE SU_EXPIRE_TIME = DATE_SUB(CURDATE(), INTERVAL ? DAY)) " +
                                            "AND SU_TRANSACTION_TYPE_L = 3)");
        }
        else if (numOfSus > 0)
        {
            stmtBuilder = new StringBuilder("SELECT U.USER_ID, U.USERNAME, U.EMAIL FROM users U WHERE LAST_LOGIN >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR) " +
                                            "AND U.USER_ID NOT IN (SELECT USER_ID FROM user_preferences WHERE PREFERENCE = '900EmailSent' and VALUE = 'TRUE') " +
                                            "AND U.USER_ID NOT IN (SELECT DISTINCT(USER_ID) FROM user_su_transaction WHERE SU_TRANSACTION_TYPE_L = 3) " +
                                            "AND U.USER_ID IN (SELECT USER_ID FROM job_stats J WHERE J.USER_ID = U.USER_ID AND J.DATE_STARTED >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR) GROUP BY J.USER_ID HAVING SUM(SU_CHARGED) > 0)");
        }
        else if (numOfMonths > 0)
        {
            if (numOfMonths == 5)
                stmtBuilder = new StringBuilder("SELECT A.USER_ID, U.USERNAME, U.EMAIL FROM user_su_allocation A, users U WHERE A.SU_EXPIRE_TIME = DATE_ADD(CURDATE(), INTERVAL 30 DAY) AND A.USER_ID = U.USER_ID ");
            else if (numOfMonths == 6)
                stmtBuilder = new StringBuilder("SELECT A.USER_ID, U.USERNAME, U.EMAIL FROM user_su_allocation A, users U WHERE A.SU_EXPIRE_TIME = CURDATE() AND A.USER_ID = U.USER_ID ");
            else
                stmtBuilder = new StringBuilder("SELECT USER_ID, USERNAME, EMAIL FROM users WHERE DATE_CREATED >= DATE_SUB(CURDATE(), INTERVAL ? MONTH) and date_created < DATE_ADD(DATE_SUB(CURDATE(), INTERVAL ? MONTH), INTERVAL 1 DAY) ");
        }
        else
            stmtBuilder = new StringBuilder("SELECT USER_ID, USERNAME, EMAIL FROM users WHERE DATE_CREATED >= DATE_SUB(CURDATE(), INTERVAL ? DAY) and date_created < DATE_SUB(CURDATE(), INTERVAL ? DAY) ");
        
        //Connection dbConn = ConnectionManager.getConnectionSource().getConnection();
        PreparedStatement selectStmt = null;
        ResultSet usersInfos = null;

        try
        {
            selectStmt = dbConn.prepareStatement(stmtBuilder.toString());
            if (numOfExpired > 0)
            {
                selectStmt.setInt(1, numOfExpired);
            }
            else if (numOfSus <= 0)
            {
                if (numOfMonths > 0)
                {
                    if(numOfMonths != 5 && numOfMonths != 6)
                    {
                        selectStmt.setInt(1, numOfMonths);
                        selectStmt.setInt(2, numOfMonths);
                    }
                }
                else
                {
                    selectStmt.setInt(1, numOfDays);
                    selectStmt.setInt(2, numOfDays-1);
                }
            }
            //log.debug("Executing: " + stmtBuilder.toString().replaceFirst("\\?", "'" + submitter + "'").replaceFirst("\\?", "'" + status + "'"));
            usersInfos = selectStmt.executeQuery();
            List<UserInfoMatched> retval = new ArrayList<UserInfoMatched>();

            while (usersInfos.next())
            {
                retval.add(new EmailOnCertainDay.UserInfoMatched(usersInfos.getInt(1), usersInfos.getString(2), usersInfos.getString(3)));
            }
            
            if (numOfExpired > 0)
            {
                for (UserInfoMatched mim : retval)
                {
                  sendEmail(mim.getEmail(), mailTitles.get(expired), openingWords + mim.getUserName() + ",<br><br>" + mailBodys.get(expired+numOfExpired), EMAIL_SENDER2);                
                }                
            }
            else if (numOfSus> 0)
            {
                for (UserInfoMatched mim : retval)
                {  
                    User candidate = new User(dbConn, mim.getUserId());
                    long suBalance = Workbench.getInstance().getSuAllocationRemaining(candidate);
                    System.out.println("\n********************************\nsuBalance = " + suBalance);
                    if (suBalance <= (1000-numOfSus))
                    {
                        sendEmail(mim.getEmail(), mailTitles.get(sus), openingWords + mim.getUserName() + ",<br><br>" + mailBodys.get(sus), EMAIL_SENDER2);
                        int count = insert900EmailSent(dbConn, mim.userId);
                        if (count != 1)
                            System.out.println("Insertion of notification of 900 su used failed!");
                    }
                }             
            }
            else if (numOfMonths > 0)            
            {
                String firstStatement = "";
                /*
                if (numOfMonths >= 1 && numOfMonths <=4)
                {
                    firstStatement = String.format(firstStatements.get(new Integer(1)), 6-numOfMonths);
                }
                else if (numOfMonths == 5)
                    firstStatement = firstStatements.get(new Integer(5));
                else
                    firstStatement = firstStatements.get(new Integer(6));
                */
                for (UserInfoMatched mim : retval)
                {
                    if (!hasPurchasedSUs(dbConn, mim.userId))
                    {
                        sendEmail(mim.getEmail(), mailTitles.get(months), openingWords + mim.getUserName() + ",<br><br>" + firstStatement + mailBodys.get(months), EMAIL_SENDER2);
                    }
                }
            }
            else
            {
                Set<Long> jobOwners = getCertainJobOwners(dbConn, numOfDays);
                for (UserInfoMatched mim : retval)
                {
                    if (!jobOwners.contains(mim.userId))
                    {
                        sendEmail(mim.getEmail(), mailTitles.get(days), openingWords + mim.getUserName() + ",<br><br>" + mailBodys.get(days), EMAIL_SENDER2);
                    }
                }
            }
           
        }
        catch ( SQLException sqlErr )
        {           
            throw sqlErr;
        }
        catch ( MessagingException e )
        {
            throw e;
        }               
        finally
        {
            if (usersInfos  != null)
            {
                usersInfos .close();
            }

            if (selectStmt != null)
            {
                selectStmt.close();
            }

            //dbConn.close();
        }        
    }

    private boolean 
    sendEmail ( String recipient, String subject, String body, String sender ) throws MessagingException
    {
        Properties properties = new Properties();
        properties.put("mail.smtp.host", EMAIL_HOST);
        Session session = Session.getDefaultInstance(properties, null);
        Message message = new MimeMessage(session);
        try
        {
            message.setFrom(new InternetAddress(sender));
            message.setRecipient(Message.RecipientType.TO, new InternetAddress(recipient));
            message.setSubject(subject);
            message.setContent(body, "text/html; charset=utf-8");
            //message.setText(body);
            message.setSentDate(new Date());
            Transport.send(message);
            System.out.println("Sending email to " + recipient + " ...");
            return true;
        }
        catch ( MessagingException e )
        {
            throw e;
        }
    }
    
    private Set<Long> getCertainJobOwners(Connection dbConn, int numOfDays) throws SQLException            
    {
        StringBuilder stmtBuilder = new StringBuilder("SELECT DISTINCT(USER_ID) FROM tasks WHERE CREATION_DATE >= DATE_SUB(CURDATE(), INTERVAL ? DAY)");
        

        //Connection dbConn = ConnectionManager.getConnectionSource().getConnection();
        PreparedStatement selectStmt = null;
        ResultSet usersInfos = null;

        try
        {
            selectStmt = dbConn.prepareStatement(stmtBuilder.toString());
            selectStmt.setInt(1, numOfDays);

            usersInfos = selectStmt.executeQuery();
            Set<Long> retval = new TreeSet<Long>();

            while (usersInfos.next())
            {
                retval.add(new Long(usersInfos.getInt(1)));
            }
            
            return retval;
           
        }
        catch ( SQLException sqlErr )
        {           
            throw sqlErr;
        }        
        finally
        {
            if (usersInfos  != null)
            {
                usersInfos .close();
            }

            if (selectStmt != null)
            {
                selectStmt.close();
            }
        }        
    }
    
    private boolean hasPurchasedSUs(Connection dbConn, long  userId) throws SQLException
    {
        StringBuilder stmtBuilder = new StringBuilder("SELECT COUNT(ID) FROM user_su_transaction WHERE USER_ID = ? and SU_TRANSACTION_TYPE_L = 3");
        

        //Connection dbConn = ConnectionManager.getConnectionSource().getConnection();
        PreparedStatement selectStmt = null;
        ResultSet usersInfos = null;

        try
        {
            selectStmt = dbConn.prepareStatement(stmtBuilder.toString());
            selectStmt.setLong(1, userId);

            usersInfos = selectStmt.executeQuery();

            while (usersInfos.next())
            {
                if (usersInfos.getInt(1) > 0)
                    return true;
            }
            
            return false;
           
        }
        catch ( SQLException sqlErr )
        {           
            throw sqlErr;
        }        
        finally
        {
            if (usersInfos  != null)
            {
                usersInfos .close();
            }

            if (selectStmt != null)
            {
                selectStmt.close();
            }

            //dbConn.close();
        }                
    }
    
    private int insert900EmailSent(Connection dbConn, long userId) throws SQLException
    {
        StringBuilder stmtBuilder0 = new StringBuilder("SELECT COUNT(*) FROM user_preferences WHERE USER_ID = ? AND PREFERENCE = '900EmailSent'");
        //StringBuilder stmtBuilder = new StringBuilder("insert into user_preferences values (?, 'TRUE', '900EmailSent')");
        

        //Connection dbConn = ConnectionManager.getConnectionSource().getConnection();
        PreparedStatement selectStmt0 = null;
        PreparedStatement selectStmt = null;
        ResultSet usersInfos = null;
        
        int count = 0;
        try
        {
            selectStmt0 = dbConn.prepareStatement(stmtBuilder0.toString());
            selectStmt0.setLong(1, userId);
            
            usersInfos = selectStmt0.executeQuery();
            boolean existing = false;
            while (usersInfos.next())
            {
                if (usersInfos.getInt(1) > 0)
                    existing = true;
            }
            StringBuilder stmtBuilder = existing? new StringBuilder("UPDATE user_preferences SET VALUE = 'TRUE' WHERE USER_ID = ? AND PREFERENCE = '900EmailSent' ") : new StringBuilder("INSERT INTO user_preferences VALUES (?, 'TRUE', '900EmailSent')");
            selectStmt = dbConn.prepareStatement(stmtBuilder.toString());
            selectStmt.setLong(1, userId);
            
            count = selectStmt.executeUpdate();
            
            return count;          
        }
        catch ( SQLException sqlErr )
        {           
            throw sqlErr;
        }        
        finally
        {
            if (usersInfos  != null)
            {
                usersInfos .close();
            }            
            if (selectStmt0 != null)
            {
                selectStmt0.close();
            }
            if (selectStmt != null)
            {
                selectStmt.close();
            }            
        }               
        
    }

}