import java.io.FileInputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;

import javax.activation.FileTypeMap;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.log4j.Logger;

import uk.ac.roe.wfau.SQLMethods;
import uk.ac.roe.wfau.WSASchema;
import uk.ac.roe.wfau.stack2string;
import uk.ac.roe.wfau.Archive;
import javax.mail.*;

import javax.mail.internet.*;

/**
 * Login servlet to verfiy valid users of the WSA
 * Initially did this via a simple text file, but is now
 * verifying against SQL server. Eventually will likely verify
 * against astrogrid communities.
 * 
 * Once verified session attributes are set which can be picke up by other
 * servlets/jsps
 * 
 * @author MAR
 *
 * @version 1.1 woking version using text file
 * 
 */
public class DBLogin extends HttpServlet {
    /**
     * Log4j logger for record keeping
     */
    static Logger logger;
    /**
     * database connection
     */
    private Connection con=null;
    /**
     * database connection
     */
    static String server;
    /**
     * database name
     */
    static String database;
    /**
     * database login
     */
    static String dbuser;
    /**
     * database login password
     */
    static String dbpasswd;
    /**
     * pre-release username
     */
    static String preReleaseUser;
    /**
     * pre-release passwd
     */
    static String preReleasePasswd;
    /**
     * Properties from file (used to load up parametsr and
     * hold simple text file of usernames/passwds
     */
    Properties prop;
    
    static HashMap userMap=new HashMap();
    
    /** 
     * Loads initialization parameters
     *
     * @see javax.servlet.GenericServlet#init()
     */
    
    String [] releasedDBs;
    
    
    //String archive=null;
    
    
    public void init() throws ServletException {
        // not needed anymore
        //super.init(conf);
        logger=Logger.getLogger("wsa.simple");
        logger.info("initializing DBLogin servlet");
        // read in global properties
        prop = new Properties();
        String path = getServletContext().getRealPath("/");
        try {
            prop.load(new FileInputStream(path
                    + "WEB-INF/WSA.properties"));
                    //+ "WEB-INF/DBLogin.properties"));
            
        } catch (IOException e) {
            logger.error("DBLogin,Can't find properties file ");
            return;
        }
        // assign properties to database connection
        server = prop.getProperty("loginServer");
        dbuser = prop.getProperty("DBLoginUser");
        dbpasswd = prop.getProperty("DBLoginPasswd");
        database = prop.getProperty("DBLoginDatabase"); 
        preReleaseUser=prop.getProperty("preReleaseUser"); 
        preReleasePasswd=prop.getProperty("preReleasePasswd"); 
    }

    /**
     * Load SQL server driver
     */
    static {
        try {
           // Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // A class not found error means the SQL driver class could not be
            // found.
            // Which means that this driver would not be available until this is
            // remedied. This flag variable will signal this to the class!
            logger
                    .error("DBLogin, SQLRetrieverThread::static(): Could not load SQLServerDriver class! "
                            + stack2string.getString(e));
        }
    }


    /**
     * Main method that does the verification and sets session attributes
     * 
     * @see javax.servlet.http.HttpServlet#doGet(javax.servlet.http.HttpServletRequest,
     *      javax.servlet.http.HttpServletResponse)
     */
    public void doGet(HttpServletRequest req, HttpServletResponse res)
            throws ServletException, IOException {
        HttpSession session = req.getSession();
        res.setContentType("text/html");
        PrintWriter out = res.getWriter();
        String emailAddress=null;
        String archive="WSA";
        // insert html page into output
        RequestDispatcher dispatcher = req.getRequestDispatcher("top.html");
        dispatcher.include(req, res);
        // add some spacing
        out.println("<h3>&nbsp;</h3>");
        // read in user supplied parameters
        if (req.getParameter("archive")!= null) {
            archive=req.getParameter("archive");
        }
        if (req.getParameter("emailAddress") == null) {
            
        
        
        String user = null;
        String passwd = null;
        String community = null;
        String community2 = "";
        if (req.getParameter("community2") != null) {
            community2 = req.getParameter("community2").trim();
            if (community2.indexOf("=") > 0 || community2.indexOf(" ") >0 ){
                community2="";
            }
        }
        try {
       
        user = req.getParameter("user").trim();
        
        passwd = req.getParameter("passwd").trim();
        
        
        if (req.getParameter("community") != null) {
        community = req.getParameter("community").trim();
        if (community.indexOf("=") > 0 || community.indexOf(" ") >0 ){
            community="";
        }
        }
        else {
            community="";
        }
        
        
        if (!community2.equals("") && community2.length() > 0 ) {
            community=community2;
        }
        }
        catch (NullPointerException npe) {
            out.println("<p><b>Error null value(s) supplied for user and/or password and/or community");
            return;
        }
 
 
        // pass parameters to verfiy method, put in try/catch in
        // case of null parameters
        logger.info("DBLogin,"+server+" "+user+","+"******"+","+community+","+req.getRemoteAddr());
        try {
            boolean valid=false;
            if (!community.equalsIgnoreCase("preRelease")) {
                valid=VerfiyUserSQL(user, passwd, community,session,archive);
            }
            else {
                if (user.equalsIgnoreCase(preReleaseUser) && passwd.equalsIgnoreCase(preReleasePasswd)) {
                    valid=true;
                }
            }
            session.setAttribute("archive",archive);
            
            if (valid) {
                //VerfiyUserSQL(user, passwd, community,session)
                out.println("Logged in as <p> Username: " + user
                        + " <p>Community: " + community + "<p>");
                // set session parameters 
                if (archive.equalsIgnoreCase("vsa")) {
                    session.setAttribute("vsalogin", Boolean.TRUE); 
                }
                else {
                session.setAttribute("login", Boolean.TRUE);
                }
                session.setAttribute("user", user.toLowerCase().trim());
                session.setAttribute("community", community.toLowerCase().trim());
             
                if (community.equalsIgnoreCase("nonsurvey")){
                    session.setAttribute("nonsurvey", Boolean.TRUE);
                    
                }
                else {
                    session.setAttribute("nonsurvey", Boolean.FALSE);
                    session.setAttribute("programmeID","-999");
                    session.setAttribute("source", Boolean.TRUE);
                    
                }
                
            } else {
                session.setAttribute("login", Boolean.FALSE);
                session.setAttribute("vsalogin", Boolean.FALSE);
                session.setAttribute("user", null);
                session.setAttribute("community", null);
                session.setAttribute("source", Boolean.FALSE);
                out.println("FAILED to log in.<p> Please go BACK and check your have entered your correct username, password <b>AND</b> community.<p>");
            }
            session.setMaxInactiveInterval(14400);
        } catch (Exception e) {
            out.println("Problem verfiying <p>"+e+"<p> please try again.<br>"
                            + "If the problem persists send an email to wsa-support@roe.ac.uk");
        }
        finally {
            try {
                if (con != null)
                    con.close();
            } catch (SQLException ignore) {
                // do nothing
            }
        // you never know it might do something
 

        }
        }
        else {
            try {
                String category=req.getParameter("category");
                emailAddress=req.getParameter("emailAddress");
                out.print(category+" "+emailAddress+" "+archive);
                String sqlStmt=SQLMethods.getLoginDetailsSQL(category,emailAddress,archive);
            out.print("<p>Checking "+category+" category for email address: "+emailAddress);
            con=getCon(database,dbuser,dbpasswd);
            
            out.print("<p>"+sqlStmt);
            Statement stmt=con.createStatement();
            ResultSet rs=stmt.executeQuery(sqlStmt);
            StringBuffer emailMessage=new StringBuffer("The "+Archive.getArchive(archive)+" "+category+
                    " login details matching "+emailAddress+" are:\n\n");
            int rows=0;
            while (rs.next()) {
                rows++;
                emailMessage.append("\n username:  "+rs.getString("username"));
                emailMessage.append("\n password:  "+rs.getString("password"));
                emailMessage.append("\n community: "+rs.getString("community"));
            }
            out.print("<p>"+emailMessage);
            if (rows == 0) {
                out.print("<p>No records found for "+emailAddress);
                out.print("<p>This might be because you are not registered or the registration is incorrect, please contact "+
                        Archive.getArchiveEmail(archive));
            }
            else {
                //String sender,String recipient,String subject,String contents,String cc, String bcc)
                Archive.sendEmail(Archive.getArchiveEmail(archive),"mar@roe.ac.uk","login details",emailMessage.toString(),null,null);
            }
            }
         catch (Exception e) {
            out.println("Problem verfiying email<p>"+e+"<p> please try again.<br>"
                            + "If the problem persists send an email to "+Archive.getArchiveEmail(archive));
        }
        finally {
            try {
                if (con != null)
                    con.close();
            } catch (SQLException ignore) {
                // do nothing
            }
        }
        
        }
        System.gc();

        dispatcher = req.getRequestDispatcher("bottom_end.html");
        dispatcher.include(req, res);
    }
  
    /**
     * Clean up
     * 
     * @see javax.servlet.GenericServlet#destroy()
     */
    public void destroy() {
        try {
            if (con != null)
                con.close();
            con=null;
        } catch (SQLException ignore) {
            // do nothing
        }
        userMap=null;
    }

  protected void doPost(HttpServletRequest req, HttpServletResponse res)
    throws ServletException, IOException
  {
    // Data received by an HTTP POST is handled the same way
    // as data sent with a HTTP GET request... simply pass on to above...
    doGet(req, res);
  }; // END of doPost()
  
  /**
   * Simple user verication against user/passwds/communities held in
   * the  properties file
   * 
   * @param user
   * username
   * @param passwd
   * password
   * @param community
   * community
   * @return
   * true is user has been verified
   * @throws Exception
   */
  private synchronized boolean VerfiyUserSimple(String user,String passwd,String community) throws Exception{
      if (user != null && passwd != null && community != null){ 
          // read in user from properties
          if (prop.getProperty(user)!=null){
              String passCommunity=prop.getProperty(user);
              String passwdBit=
                  passCommunity.substring(0,passCommunity.lastIndexOf(":"));
              String communityBit=
                  passCommunity.substring(1+passCommunity.lastIndexOf(":"));
              System.out.println(passwdBit);
              System.out.println(communityBit);
              if (passwdBit.equals(passwd) && communityBit.equals(community)){
                  return true;
              }
              else {
                  return false;
              }
          }
          if(prop.getProperty(user) == null){
              return false;
          }
      }   
      return false;
  }
  
  
  private Connection getCon(String database,String dbuser,String dbpasswd) throws SQLException {
      return DriverManager.getConnection("jdbc:jtds:sqlserver://"
      + server + ":1433/" + database,dbuser, dbpasswd);
   
  }
  /**
   * Placeholder method to do verification against SQL server
   * 
   * @param user
   * username
   * @param passwd
   * password
   * @param community
   * community
   * @param session
   * session object
   * @return
   * true is user has been verified
   * @throws Exception
   */
public synchronized boolean VerfiyUserSQL(String user,String passwd,String community,HttpSession session,String archive) throws Exception{
      boolean valid=false;
      if (user != null && passwd != null && community != null){ 
          logger.debug(database+" "+dbuser+" "+dbpasswd);
      if (con == null || con.isClosed()){
          //logger.info("null");
          try {
              con=getCon(database,dbuser, dbpasswd);
              /*
              con = DriverManager.getConnection("jdbc:jtds:sqlserver://"
                      + server + ":1433/" + database,dbuser, dbpasswd);
                      */
              
                     // + server + ":1433/" + database+";wsid=111",dbuser, dbpasswd);
              
          /*con=DriverManager.getConnection("jdbc:microsoft:sqlserver://"+
                  server+":1433;DatabaseName="+database, dbuser, dbpasswd);
                  */
          }
          catch(Exception e){
              logger.error("DBLogin, "+e);
              throw(e);             
          }
      }
      ResultSet rs=null;
      Statement stmt=null;
      String SQLQuery=null;
      
      try {
      stmt = con.createStatement();
      
      stmt.setQueryTimeout(90);
      
      if (archive != null && archive.equalsIgnoreCase("vsa")) {
          SQLQuery="select * from vsa_users where username='"+
          user+"' and password='"+passwd+"'and community='"+community+"'";
      }
      else {
      if (!community.trim().equalsIgnoreCase("nonSurvey")){
          SQLQuery="select * from wsa_users where username='"+
          user+"' and password='"+passwd+"'and community='"+community+"'";
      }
      else {
          SQLQuery="select sourcetable,programmeid,numFilterID1,numFilterID2,numFilterID3,numFilterID4,numFilterID5," +
          		"numFilterID6,numFilterID7,numFilterID8,schemaVersion,databasename from wsa_nonSurveyUsers where username='"+
          user+"' and password='"+passwd+"'and community='"+community+"'"; 
      }
      }
      rs = stmt.executeQuery(SQLQuery);
      }
      catch (SQLException se){
      ;
      }
      String sourceTable=null;
      if (rs.next()){
            
            valid=true;
            if (session != null) {
            if (community.trim().equalsIgnoreCase("nonSurvey")){
                session.removeAttribute("releasedDBs");
                session.removeAttribute("dbName");
                sourceTable=rs.getString(1);
                String progID=rs.getString(2);
                String schemaVersion=rs.getString(11);
                String dbName=rs.getString("databasename");
                if (sourceTable == null) {
                    sourceTable="n";
                }
                if (progID == null) {
                    progID="-999";
                }
                // place holder
                int [] iArray={rs.getShort(3),rs.getShort(4),rs.getShort(5),rs.getShort(6),rs.getShort(7),rs.getShort(8),rs.getShort(9),rs.getShort(10)};
                session.setAttribute("reqFilters",iArray);
                session.setAttribute("programmeID",progID);
                session.setAttribute("schemaVersion",schemaVersion);
                session.setAttribute("dbName",dbName);
                if (sourceTable.equalsIgnoreCase("y")){
                    session.setAttribute("source", Boolean.TRUE);
                }
                else {
                    session.setAttribute("source", Boolean.FALSE);
                }
               
                
                rs = stmt.executeQuery(SQLMethods.getNSDBs(user));
                ArrayList listDB = new ArrayList();
                while (rs.next()){
                    listDB.add(rs.getString(1));
                }
                if (!listDB.isEmpty()) {
                    
                releasedDBs=((String[]) listDB.toArray(new String[listDB.size()]));
                session.setAttribute("releasedDBs",releasedDBs);
                
                String version=releasedDBs[0].toLowerCase().replaceAll(user.toLowerCase(),"");
                if (version.indexOf("v2") >= 0 ){
                session.setAttribute("source", Boolean.TRUE);
                }
                listDB=null;
                if (releasedDBs[0] != null ){
                    session.setAttribute("dbName",releasedDBs[0]);
                rs=stmt.executeQuery(SQLMethods.getNSFilterIDSQL(releasedDBs[0],user));
                
                int [] filterIDs = new int [WSASchema.ALLFILTERID.length];
                //System.out.println("here");
                while (rs.next()){
                    filterIDs[rs.getInt("filterID")]=rs.getInt("npass");
                    //System.out.println(rs.getInt("filterID")+" "+rs.getInt("npass"));
                }
                session.setAttribute("filterIDs",filterIDs);
                session.setAttribute("reqFilters",filterIDs);
                }
                } // list empty
                
            }
            else {
                session.setAttribute("reqFilters",null);
                session.setAttribute("programmeID",null);
                session.setAttribute("schemaVersion",null);
                session.setAttribute("source", Boolean.TRUE);
            }
            }
            else {
                userMap.put(user+passwd+community,new Long(System.currentTimeMillis()));
                List keys=new ArrayList(userMap.keySet());
                Iterator it = keys.iterator();
                long timeDiff=0;
                Object key;
                while (it.hasNext() ) {
                    key=it.next();
                    timeDiff=System.currentTimeMillis()-((Long)userMap.get(key)).longValue();
                    logger.debug("tdiff:"+timeDiff);
                    if (timeDiff > 5000) {
                        logger.debug("removing"+userMap.get(key)+(String)key);
                        userMap.remove(key);
                        
                    }
                    }
                }
      }
      
      if (community.trim().equalsIgnoreCase("nonSurvey")){
 
      }
     
      
      try {
      rs.close();
      stmt.close();
      con.close();
      con=null;
      }
      catch (Exception e){
      logger.error("DBLogin,"+e);
      throw(e);
      }
      }
      return valid;
  }

   
}