/******************************************************
  Used with the CreateAccount servlet.
  The servlet uses this class to establish a database connection, determine if the new
  account information is valid, insert the new account into the database, and get the 
  confirmation number to be sent in the email.

  @author Anthony Furst
  @version CISC474 A10
  
*/

package com.example.model;

import java.util.*;
import java.lang.*;
import java.sql.*;

public class Checker {
    private Connection myCon;
    private Statement myStmt;

    /** Takes the account information for the database and establishes a connection and a
	SQL statement, both of which will be used later to make queries.
	@param username The username of the database being used.
	@param password The password for the database being used.
	@param url The url of the database being used.
    */
    public void makeConnection(String username, String password, String url) {
	try {
	    Class.forName("org.gjt.mm.mysql.Driver").newInstance();
	}
	catch (Exception e) {
	    e.printStackTrace();
	}
	
	try {
	    myCon = DriverManager.getConnection(url, username, password);
	}
	catch (SQLException e) {
	    e.printStackTrace();
	}

	try {
	    myStmt = myCon.createStatement();
	}
	catch (Exception e) {
	    e.printStackTrace();
	}
    }

    /** Takes a username, password, and email for a newly created account.  Determines
	if the username is already in use.  If not, a new entry is created in the database with
	the username, password, and email specified by the user, along with a verification status,
	random confirmation number, and expiration date for the verification.
	@param username The user's username.
	@param password The user's password.
	@param email The user's email.
	@return True if the username does not already exist, false otherwise.
    */
    public boolean performQuery(String username, String password, String email) {	
	String selectQuery = "SELECT username FROM Account where username='" + username + "'";
	String insertQuery = "INSERT into Account values ('" + username + "', '" + password + 
	    "', '" + email;
	ResultSet results;
	boolean validName = true;
	Random random = new Random();
	
	//Get current date and time down to the second, add 3 dayds for expiration date
	Calendar calendar = new GregorianCalendar();
	int day = calendar.get(Calendar.DAY_OF_MONTH) + 3;
	int month = calendar.get(Calendar.MONTH) + 1;      //compensate for 0 indexed months
	int year = calendar.get(Calendar.YEAR);
	int hour = calendar.get(Calendar.HOUR_OF_DAY);
	int minute = calendar.get(Calendar.MINUTE) + 10;   
        int second = calendar.get(Calendar.SECOND);

	//Compensate for if adding 10 pushes the minutes over 60
	if (minute > 59) {
	    minute = minute % 60;
	    hour = hour + 1;
	}
	
	try {
	    results = myStmt.executeQuery(selectQuery);
	
	    //If a result exists, the username is already in use, return false
	    while (results.next()) {
		validName = false;
	    }
	
	    //Else add new account to the database
	    if (validName) {
		insertQuery += "', 'Pending', '" + random.nextInt();
		insertQuery += "', '" + year + "-" + month + "-" + day + " " + hour + ":" + minute + ":" + second + "')";
		myStmt.executeUpdate(insertQuery); 
	    }
	}
	catch (Exception e) {
	    e.printStackTrace();
	}
	finally {
	    return validName;
	}
    }

    /** Returns the confirmation number for an account with a specified username
	@param username The username of the account for which the confirmation is returned.
	@return The confirmation number represented as a String
    */
    public String getNumber(String username) {
	String query = "Select V_Number FROM Account where username='" + username + "'";
	ResultSet results;
	String number = "";
	
	try {
	    results = myStmt.executeQuery(query);

	    while (results.next()) {
		number = results.getString("V_Number");
	    }
	}
	catch (Exception e) {
	    e.printStackTrace();
	}
	finally {
	    return number;
	}
    }
    
    /** Closes the connection and the statement */
    public void closer() {
	try {
	    myCon.close();
	    myStmt.close();
	}
	catch (Exception e) {
	    e.printStackTrace();
	}
    }
}