/***************************************************
  Used by the Verify servlet.
  The servlet uses this class to establish a connection to the database, determine if a 
  an account exists, whether it's window for verification has expired, and whether the
  confirmation number provided with it is valid.

  @author Anthony Furst
  @version CISC474 A10
*/

package com.example.model;

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

public class Verifier {
    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 and email and determines if an account with such a combination
	exists.
	@param username The username for the account.
	@param email The email for the account.
	@return True if such an account exists, false otherwise.
    */
    public boolean getIsValid(String username, String email) {
	boolean isValid = false;
	ResultSet results;

	try {
	    results = myStmt.executeQuery("Select * From Account where username='" + username +
					  "' and email='" + email + "'");

	    //If a result exists, then the account exists.
	    while (results.next()) {
		isValid = true;
	    }
	}
	catch (Exception e) {
	    e.printStackTrace();
	}
	finally {
	    return isValid;
	}
    }

    /** Takes a username and an email and determines if an account with such a combination
	has passed the expiration date for verifying the email.
	@param username The username for the account.
	@param email The email for the account.
	@return True if the verification window is expired, false otherwise
    */
    public boolean getIsExpired(String username, String email) {
	boolean isExpired = true;
	ResultSet results;
	Calendar calendar = new GregorianCalendar();
	String expDate;
	String curDate = "";

	//Get the current time and date to compare against the expiration time and date
	int day = calendar.get(Calendar.DAY_OF_MONTH);
	int month = calendar.get(Calendar.MONTH) + 1;
	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);

	if (minute > 59) {
	    minute = minute % 60;
	    hour = hour + 1;
	}

	//Convert the time and date to the correct format
	curDate += year + "-" + month + "-" + day + " " + hour + ":" + minute + ":" + second;

	try {
	    results = myStmt.executeQuery("Select Expiration from Account where username='" + username +
					  "' and email='" + email + "' and Expiration>='" + curDate + "'");
	    
	    //If a result exists, then the account is not expired
	    if (results.next()) {
		isExpired = false;
	    }
	    //Otherwise the user's account is expired and is deleted from the database
	    else {
		myStmt.executeUpdate("Delete from Account where username='" + username + "'");
	    }		
	}
	catch (Exception e) {
	    e.printStackTrace();
	}
	finally {
	    return isExpired;
	}
    }
    
    /** Takes a username, an email, and a confirmation number and determines if the number matches the
	number paired with the username and email in the database.
	@param username The username for an account.
	@param email The email for that account.
	@param number The confirmation number entered by the user.
	@return True if the confirmation number matches the account, false otherwise.
    */
    public boolean getIsVerified(String username, String email, String number) {	
	String getNumber = "Select * From Account where username='" + username + "' and email='" + email +
	    "' and V_Number='" + number + "'";
	ResultSet results;
	boolean verified = false;
	
	try {
	    results = myStmt.executeQuery(getNumber);
	
	    //If a result exists, the confirmation number matches and the users account is marked as Verified
	    if (results.next()) {
		myStmt.executeUpdate("Update Account Set Verified='Verified' Where username='" + username + "'");
		verified = true;
	    }
	    //Otherwise, the number is invalid and the users account is marked as Not Verified
	    else {
		myStmt.executeUpdate("Update Account Set Verified='Not Verified' Where username='" + username + "'");
	    }
	}
	catch (Exception e) {
	    e.printStackTrace();
	}
	finally {
	    return verified;
	}
    }    
    
    /** Closes the connection and the statement */
    public void closer() {
	try {
	    myCon.close();
	    myStmt.close();
	}
	catch (Exception e) {
	    e.printStackTrace();
	}
    }
}

