// 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, or (at your option)
// any later version.<P>
// 
// 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.<P>
// 
// To obtain a copy of the GNU General Public License write to the Free
// Software Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. <P>


package rob.servlets.addressbook ;

import java.io.* ;
import java.util.* ;

// This is the database class library
import java.sql.* ;

import javax.servlet.* ;
import javax.servlet.http.* ;

// This is supplied with the postgresql java class libraries.
// It duplicates the functionality of the unix crypt fuinction.
// I've used it here for encrypting passwords.
import org.postgresql.util.UnixCrypt ;

/**
 * <code>AddressBookServlet</code> just tests out the capabilities of the servlet
 * engine and the JDBC interface.  It implements a simple address book that allows
 * you to add, edit, delete and search for entries.  <P>
 *
 * The SQL to create the necessary tables is at the end of the file.<P>
 *
 * Created: Wed Aug 16 14:36:00 2000
 *
 * @author <a href="mailto:rob@corwin.the-judds.org">Robert Judd</a>
 * @version 1.0
 * @since 1.0
 * @see HttpServlet
 */
public class AddressBookServlet extends HttpServlet {

    /**
     * Loading of the database driver class is a bit of a kludge.  We need this
     * field to test if the class really was loaded.  If a request is made and this
     * is false, then an error page will be returned.
     */
    protected boolean dbClassLoaded = false ;

    /**
     * The URL to access the database - needs the database type, "postgresql",
     * the hostname, "random", the port (5432 is the default) and the
     * database name "addressbook".
     */
    protected String JDBC_URL = "jdbc:postgresql://random:5432/addressbook" ;

    /**
     * The name of a user who is allowed to read and write to the database
     */
    protected String DB_USER = "rob" ;

    /**
     * The password to access the database.
     */
    protected String DB_PASSWORD = "" ;

    /**
     * The name of the JDBC class that implements java.sql.Driver.  This must
     * be in the classpath of the servlet engine.
     */
    protected String DB_CLASSNAME = "org.postgresql.Driver" ;

    /**
     * Initialize the servlet; this is called by the server before any of the
     * other methods.  The default values for the fields above may be altered
     * by setting servlet parameters in the <code>zone.properties</code> file. <P>
     *
     * Once the parameters have been read the servlet attempts to load the 
     * database driver class.  If this fails (i.e. the class cannot be found)
     * then any requests to the servlet will return an error. <P>
     *
     * @param config the <code>ServletConfig</code> object passed to the servlet
     *               by the server.
     */
    public void init(ServletConfig config) throws ServletException {
	
	// Always call super.init(config) first
	super.init(config);


	// Read the initial parameters
	String param = null ;
	if ((param = config.getInitParameter("JDBC_URL")) != null 
	    && !param.equals(""))
	    JDBC_URL = param ;

	if ((param = config.getInitParameter("DB_USER")) != null 
	    && !param.equals(""))
            DB_USER = param ;

	if ((param = config.getInitParameter("DB_CLASSNAME")) != null 
	    && !param.equals(""))
            DB_CLASSNAME = param ;
            
	if ((param = config.getInitParameter("DB_PASSWORD")) != null)
	    DB_PASSWORD = param ;

	// Initialize the database
	try {
	    Class.forName(DB_CLASSNAME) ;
	    dbClassLoaded = true ;
	} catch (ClassNotFoundException cnfe) {
	    dbClassLoaded = false ;
	}
	    
	

    }
    
    /**
     * Process a <code>GET</code> request - this checks to see if the database
     * driver class was loaded, returns an error if not, and then reads the
     * action parameter to see what to do next.  If there is no action parameter,
     * then the login page is returned; if the action is recognised it is performed,
     * otherwise an error is returned.
     *
     * @param req the <code>HttpServletRequest</code> object passed in by the
     *            server
     * @param res the <code>HttpServletResponse</code> object passed in by the
     *            server
     *
     * @exception <code>ServletException</code> if anything goes wrong with the
     *            servlet/server communication
     * @exception <code>IOException</code> if anything goes wrong with the io
     *            streams.
     */
    public void doGet(HttpServletRequest req, HttpServletResponse res)
    	throws ServletException, IOException {
	
	res.setContentType("text/html") ;
	PrintWriter out = res.getWriter() ;

	// Check that the driver class was loaded
	if (!dbClassLoaded) {
	    error(out, "Unable to load database driver.") ;
	    return ;
	}

	// Delegate reading the request parameters to a separate method - this
	// is to collect all that stuff in one place.
	Properties p = getParameters(req) ;

	String action = p.getProperty("action") ;

	if (action == null || action.equals(""))
	    login(out) ;
	else if (action.equals("Open"))
	    open(out, p) ;
	else if (action.equals("Add"))
	    add(out, p) ;
	else if (action.equals("Edit"))
	    edit(out, p) ;
	else if (action.equals("Save"))
	    save(out, p) ;
	else if (action.equals("Delete"))
	    delete(out, p) ;
	else if (action.equals("Log Out"))
	    logout(out, p) ;
	else if (action.equals("Search"))
	    search(out, p) ;
	else 
	    error(out, "Your command: "+action+" was not understood.") ;
	

    }  

    /**
     * Pass the request back to <code>doGet</code>.
     *
     * @param req The servlet request
     * @param res The servlet response
     * @exception ServletException if an error occurs
     * @exception IOException if an error occurs
     */
    public void doPost(HttpServletRequest req, HttpServletResponse res)
    	throws ServletException, IOException {
	
	doGet(req, res) ;
    }
    
    /**
     * This may be called by <code>doGet</code> or <code>doPost</code> to 
     * generate a <code>Properties</code> object with a list of name/value pairs
     * from the parameters passed in with the <code>ServletRequest</code> object.
     * The <code>ServletRequest</code> is used to get a list of parameter names,
     * then their values put into the <code>Properties</code> object.  <P>
     *
     * One of the reasons to put this in a separate method is so that if a 
     * parameter has multiple values, then the values may be strung togther
     * easily.  <P>
     *
     * Once the parameters are saved a session is obtained (created if 
     * necessary) and also stored with the properties.  Note the a 
     * <code>Properties</code> object is intended for <code>name=value</code> pairs
     * where <code>name</code> and <code>value</code> are both <code>String</code>s
     * but it extends <code>Hashtable</code> so arbitrary pairs of 
     * <code>Object</code>s may in fact be stored there. <P>
     * 
     * @param req the <code>HttpServletRequest</code> object passed in by the
     *            server
     *
     * @return a <code>Properties</code> object representing the name/value pairs
     *         passed in as parameters
     */
    protected Properties getParameters(HttpServletRequest req) {

	Enumeration enum = req.getParameterNames() ;
	Properties props = new Properties() ;
	while (enum.hasMoreElements()) {
	    // This is the parameter name
	    String name = (String) enum.nextElement() ;

	    // These are the values for the parameter - there may be more than one
	    String[] values = req.getParameterValues(name) ;
	    StringBuffer value = new StringBuffer() ;
	    String sep = "" ;

	    // Store the values separated by commas - this is bad if any of the 
	    // values contains a comma, be warned.
	    for (int i=0; i<values.length; ++i) {
		if (values[i] != null && !values[i].equals("")) {
		    value.append(sep).append(values[i]) ;
		    sep = "," ;
		}
	    }
	    props.setProperty(name, value.toString()) ;
	}	
	
	// Get the session of the user, create if it doesn't exist
	HttpSession session = req.getSession(true);
	props.put("HttpSession", session) ;

	return props ;
    }


    // ----------- The methods to handle the different requests -------------
    //
    // To keep the file size small, all of the static HTML text that will be 
    // returned is kept in separate classes.  These require some dynamic data,
    // and an output stream to which to write.  They then construct the page
    // and return it to the stream.

    /**
     * Write an error page.
     *
     * @param out a <code>PrintWriter</code> value
     * @param msg a <code>String</code> value
     */
    public void error(PrintWriter out, String msg) {

	new ErrorPage(msg).write(out) ;

    }

    /**
     * Return the login page - this is completely static.
     *
     * @param out a <code>PrintWriter</code> value
     */
    public void login(PrintWriter out) {

	// Nothing to do yet.  Just return the page
	new LoginPage().write(out) ;
	if (out.checkError()) 
	    error(out,  "There was an error returning the login page.") ;

    }

    /**
     * Try to access the database with the user's id and password.  Return the
     * main address book page if success, otherwise return the error page.  If 
     * we accessed the database successfully, then a cookie is created to note that
     * we are logged in, and save the userid - this avoids writing it into the
     * HTML page each time.
     *
     * All of the calls to the database are wrapped inside the methods 
     * <code>executeQuery</code> and <code>executeUpdate</code> for clarity.
     *
     * @param out a <code>PrintWriter</code> value
     * @param params a <code>Properties</code> value containing the parameters
     *               from the servlet request in case the class needs them to help
     *               construct the HTML page.
     */
    public void open(PrintWriter out, Properties params) {

	// Get the userid (param "userid") and password (param "password")
	// Look the user up in the DB
	// if success, then return the initial page,
	// else return and error message

	// There must be a userid and password.
	String userid = params.getProperty("userid") ;
	String password = params.getProperty("password") ;

	if (userid == null || userid.equals(""))
	    error(out,  "Please specify a userid.") ;
	else if (password == null)
	    error(out,  "Please specify a password.") ;
	else {

	    try {
		// Send the SQL 'SELECT password FROM Users WHERE userid = userid'
		// This will return nothing if the userid couldn't be found,
		// and the encrypted password if the userid was valid
		// 
		// The password from the request is checked against the encrypted 
		// one using the UnixCrypt class that comes with postgresql.
		// There are many other ways to do encryption...
		//
		String[][] response = executeQuery
		    ("SELECT password FROM Users WHERE userid = '"+userid+"'");
		if (response == null || response.length == 0)
		    error(out,  "No such user id.") ;
		else if (!UnixCrypt.matches(response[0][0], password))
		    error(out,  "Invalid user id/password.") ;
		else { // We're in!!!

		    // Get the cookie and save the userid
		    HttpSession session = (HttpSession) params.get("HttpSession") ;
		    session.putValue("IsLoggedIn", Boolean.TRUE) ;
		    session.putValue("userid", userid) ;
		    new MainPage(userid, null).write(out) ;

		}
	    } catch (SQLException sqle) {
		// This means that there was some error accessing the database
		error(out,  sqle.getMessage()) ;
	    }
	}

    }

    /**
     * This is a list of the fields in the database so that we may refer to them 
     * later.
     */
    public static final String[] fields = new String[] {
	"name", "email", "address1", "address2", "city",
	"state", "zipcode", "telephone"
    } ;

    /**
     * A helper class that returns the userid from the cookie with the request.
     *
     * @param params a <code>Properties</code> value
     * @return the userid, or null if it couldn't be found
     */
    public String validate(Properties params) {
	HttpSession session = (HttpSession) params.get("HttpSession") ;
	String userid = null ;
	if (session == null || 
	    !Boolean.TRUE.equals(session.getValue("IsLoggedIn")) ||
	    (userid = (String) session.getValue("userid")) == null)
	    return null ;
	else 
	    return userid ;
	
    }

    /**
     * Insert an address into the database and return the main page with a 
     * message to say if we were successful or not.  Minimal checks are done by the
     * database to validate the address: the name must not be null, the email 
     * address must contain an '@' symbol and the userid(of the user adding the 
     * address)/email address combination must be unique.
     *
     * @param out a <code>PrintWriter</code> value
     * @param params a <code>Properties</code> value
     */
    public void add(PrintWriter out, Properties params) {

	String userid = validate(params) ;
	if (userid == null)
	    login(out) ;
	else {
	    //
	    // Construct the SQL statement
	    //
	    // INSERT INTO Addresses ( userid, name, email, address1, address2,
	    //	 city, state, zipcode, telephone) VALUES ('userid', 'name', 'email',
	    //   'address1', 'address2', 'city', 'state', 'zipcode', 'telephone')
	    //
	    StringBuffer sql = new StringBuffer("INSERT INTO Addresses ( ") ;
	    sql.append("userid") ;
	    for (int i=0, max=fields.length; i<max; ++i) 
		sql.append(", ").append(fields[i]) ;
	    sql.append(") VALUES ('").append(userid).append('\'') ;
	    
	    for (int i=0, max=fields.length; i<max; ++i) {
		sql.append(", '") ;
		String value = params.getProperty(fields[i]) ;
		if (value != null) sql.append(value) ;
		sql.append('\'') ;
	    }
	    sql.append(')') ;
	    String msg = "" ;
	    try {
		// Execute the SQL and count how many rows were affected
		int count = executeUpdate(sql.toString()) ;
		if (count >= 1) msg = "Address added." ;
		else msg = "Strange error: nothing added." ;
	    } catch (SQLException sqle) {
		msg = sqle.getMessage() ;
	    }
	    new MainPage(userid, null, msg).write(out) ;
	}
    }

    /**
     * Return the page where the user can edit an address - the address information
     * is contained in the request and passed into the response.
     *
     * @param out a <code>PrintWriter</code> value
     * @param params a <code>Properties</code> value
     */
    public void edit(PrintWriter out, Properties params) {

	String userid = validate(params) ;
	if (userid == null)
	    login(out) ;
	else {
	    // Get the address information from the request
	    String[] info = new String[fields.length+2] ;
	    info[info.length-1] = userid ;
	    for (int i=0, max=fields.length; i<max; ++i) 
		info[i] = params.getProperty(fields[i], "") ;
	    
	    info[info.length-2] = params.getProperty("addressid", "") ;
	    if (info[info.length-2].equals(""))
		error(out,  "No address given.") ;
	    else
		new EditPage(info).write(out) ;
	}
    }

    /**
     * Save an edited address in the database.  This is called from the edit page.
     * The addresses are tracked using an addressid field in the database table.
     * The addressid is managed by the database and guarenteed to be unique.
     *
     * @param out a <code>PrintWriter</code> value
     * @param params a <code>Properties</code> value
     */
    public void save(PrintWriter out, Properties params) {

	String userid = validate(params) ;
	String addressid = params.getProperty("addressid", "") ;
	if (userid == null)
	    login(out) ;
	else if (addressid.equals("")) 
	    error(out,  "No address given.") ;
	else {
	    // Construct the SQL
	    //
	    // UPDATE Addresses SET name = 'name', email = 'email', 
	    //   address1 = 'address1', address2 = 'address2', city = 'city', 
	    //   state = 'state', zipcode = 'zipcode', telephone = 'telephone'
	    //   WHERE userid = 'userid' AND addressid = 'addressid'
	    //
	    StringBuffer sql = new StringBuffer("UPDATE Addresses SET ") ;

	    String sep = "" ;
	    for (int i=0, max=fields.length; i<max; ++i) {
		sql.append(sep).append(fields[i]).append(" = '") ;
		String value = params.getProperty(fields[i], "") ;
		sql.append(value).append('\'') ;
		sep = ", " ;
	    }

	    sql.append(" WHERE userid = '").append(userid)
		.append("' AND addressid = '").append(addressid).append('\'') ;
	    String msg = "" ;
	    try {
		// Execute the SQL and count how many rows were affected
		int count = executeUpdate(sql.toString()) ;
		if (count >= 1) msg = "Address saved." ;
		else msg = "Strange error: nothing saved." ;
	    } catch (SQLException sqle) {
		msg = sqle.getMessage() ;
	    }
	    new MainPage(userid, null, msg).write(out) ;

	}
    }

    /**
     * Delete the address form the database.  The addressid is used to match the 
     * address.
     *
     * @param out a <code>PrintWriter</code> value
     * @param params a <code>Properties</code> value
     */
    public void delete(PrintWriter out, Properties params) {

	String userid = validate(params) ;
	String addressid = params.getProperty("addressid", "") ;
	String msg = null ;
	if (userid == null)
	    error(out,  "No userid.") ;//	    login(out, params) ;
	else if (addressid.equals("")) 
	    error(out,  "No address given.") ;
	else {
	    try {
		// Execute the SQL 
		//
		// DELETE FROM Addresses WHERE userid = 'userid' 
		//      AND addressid = 'addressid'
		//
		// and count how many rows were affected
		int count = executeUpdate
		    ("DELETE FROM Addresses WHERE userid = '"+userid+
		     "' AND addressid = '"+addressid+"' ") ;
		if (count >= 1) msg = "Address deleted." ;
		else msg = "No records match" ;
	    } catch (SQLException sqle) {
		msg = sqle.getMessage() ;
	    }
	    new MainPage(userid, null, msg).write(out) ;
	}

    }

    /**
     * Logs out the user from the servlet.  Since we don't store any database
     * information between requests all that needs be done is invalidate the cookie.
     *
     * @param out a <code>PrintWriter</code> value
     * @param params a <code>Properties</code> value
     */
    public void logout(PrintWriter out, Properties params) {

	HttpSession session = (HttpSession) params.get("HttpSession") ;
	if (session != null) session.invalidate() ;
	login(out) ;
    }

    /**
     * Search for a string in the addresses and return all those that match.
     * The field to search in, or all of them, may be specified.
     *
     * @param out a <code>PrintWriter</code> value
     * @param params a <code>Properties</code> value
     */
    public void search(PrintWriter out, Properties params) {

	String userid = validate(params) ;
	if (userid == null)
	    login(out) ;
	else {
	    //
	    // Construct the SQL statement
	    //
	    // SELECT name, email, address1, address2, city, state, zipcode,
	    //   telephone, addressid FROM Addresses WHERE userid = 'userid'
	    //   [ AND (name ~* 'search_str' OR email ~* 'search_str' OR
	    //          address1 ~* 'search_str' OR address2 ~* 'search_str' OR 
	    //          city ~* 'search_str' OR state ~* 'search_str' OR
	    //          zipcode ~* 'search_str' OR telephone ~* 'search_str') |
	    //     AND field ~* 'search_str' ]
	    //
	    StringBuffer sql = 
		new StringBuffer("SELECT ") ;
	    String sep = "" ;
	    for (int j=0, max=fields.length; j<max; ++j) {
		sql.append(sep).append(fields[j]) ;
		sep = ", " ;
	    }
	    sql.append(", addressid") ;
	    sql.append(" FROM Addresses WHERE userid = '") ;
	    sql.append(userid).append('\'') ;
	    
	    String search = params.getProperty("search") ;
	    String field = params.getProperty("field") ;
	    if (search != null && !search.equals("") && field != null) {
		if (field.equals("all")) {
		    sep = "" ;
		    sql.append(" AND ( ") ;
		    for (int j=0, max=fields.length; j<max; ++j) {
			sql.append(sep).append(fields[j]).append(" ~* '")
			    .append(search).append('\'') ;
			sep = " OR " ;
		    }
		    sql.append(')') ;
		} else 
		    sql.append(" AND ").append(field).append(" ~* '")
			.append(search).append('\'') ;
	    }

	    try {
		// Execute the query and return the responses
		String[][] response = executeQuery(sql.toString()) ;
		if (response == null) response = new String[0][0] ;
		new MainPage(userid, response).write(out) ;

	    } catch (SQLException sqle) {
		new MainPage(userid, null, sqle.getMessage()).write(out) ;
	    }
	}
    }

    // ---------------- Database query methods ---------------------------------


    /**
     * Execute arbitrary SQL to query the database.
     *
     * @param sql the SQL to execute
     * @return the <code>ResultSet</code> returned by the call to the database
     * @exception <code>SQLException</code> if anything went wrong.
     */
    protected String[][] executeQuery(String sql) throws SQLException {

	// Create a connection
	Connection conn = DriverManager.getConnection(JDBC_URL,DB_USER,DB_PASSWORD);

	// Create a statement - this is a placeholder for the sql to execute
	Statement stmt = conn.createStatement() ;

	// Execute the sql and get the 'Result Set' that is returned
	ResultSet rows = stmt.executeQuery(sql) ;
	
	log("Executed "+sql) ;
	ArrayList response = new ArrayList() ;

	// Get the number of columns in the response
	int cols = rows.getMetaData().getColumnCount() ;
	log("Col count "+cols) ;

	// Loop through the rows saving the information
	while (rows.next()) {
	    String[] record = new String[cols] ;
	    for (int i=0, max=record.length; i<max; ++i) {
		Object o = rows.getObject(i+1) ;
		record[i] = ( o != null) ? o.toString() : "" ;
		//		log("record["+i+"]="+record[i]) ;
	    }
	    response.add(record) ;
	}
	rows.close() ;
	stmt.close() ;
	conn.close() ;
	return (String[][]) response.toArray(new String[0][0]) ;

    }

    /**
     * Execute arbitrary SQL to update the database.
     *
     * @param sql the SQL to execute
     * @return the <code>ResultSet</code> returned by the call to the database
     * @exception <code>SQLException</code> if anything went wrong.
     */
    protected int executeUpdate(String sql) throws SQLException {

	Connection conn = DriverManager.getConnection(JDBC_URL,DB_USER,DB_PASSWORD);
	Statement stmt = conn.createStatement() ;
	log("Executing "+sql) ;
	int count = stmt.executeUpdate(sql) ;
	stmt.close() ;
	conn.close() ;
	return count ;

    }

}

/*


 CREATE TABLE Users (
   userid       VARCHAR(8)    NOT NULL  CHECK (userid <> ''),
   password     VARCHAR(13)   NOT NULL,
   PRIMARY KEY (userid),
   UNIQUE (userid)
 );
  
INSERT INTO Users (userid, password) VALUES ('rob', '9CfYD85XoehjA' ) ; // pw = fred

 CREATE TABLE Addresses (
   addressid    SERIAL        NOT NULL,
   userid       VARCHAR(8)    NOT NULL  REFERENCES Users,
   name         TEXT          NOT NULL  CHECK (name <> ''),
   email        TEXT          NOT NULL  CHECK (email ~ '@'),
   address1     TEXT          DEFAULT '',
   address2     TEXT          DEFAULT '',
   city         TEXT          DEFAULT '',
   state        TEXT          DEFAULT '',
   zipcode      TEXT          DEFAULT '',
   telephone    TEXT          DEFAULT '',
   country      TEXT          DEFAULT '',
   PRIMARY KEY (email),
   UNIQUE (userid, email)
 );

INSERT INTO Addresses (userid, name, email, address1, address2, city, state, zipcode, telephone) VALUES ('rob', 'Robert Judd', 'rjudd@mlug.missouri.edu', '1024 Megabyte', '', 'Columbia', 'MO', '65201-1024', '573 555 1212') ;

UPDATE Addresses SET name = 'new name', email = 'new email', address1 = 'new address1', address2 = 'new address2', city = 'new city', state = 'new state', zipcode = 'new zipcode', telephone = 'new telephone' WHERE addressid = 'id' AND userid = 'userid' ;

*/