import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;


public class GameLogger {

	Connection fConnection;

	public static void main(String[] args)
	{
		new GameLogger();
	}

	public GameLogger()
	{
		fConnection = getConnection();
		printTable("User");
		changePassword("Samat", "a");
		printTable("User");
		try {
			fConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Returns a connection to the database named LogBook.db
	 * @return
	 */
	private Connection getConnection()
	{
		Connection con;

		//Find database Classes
		try {
			Class.forName("org.sqlite.JDBC");
		} catch (ClassNotFoundException e) {
			System.out.println("Unable to find database");
			System.exit(2);
		}
		//Connect to database
		try {
			con = DriverManager.getConnection("jdbc:sqlite:LogBook.db");
			return con;
		} catch (SQLException e) {
			System.out.println("Unable to find database");
			System.exit(2);
		}

		return null;
	}

	/**
	 * Adds a new user to the database
	 * @param aUser Username
	 * @param aPass Password
	 * @param aName Firstname
	 * @param aSurname Surname
	 * @param aEmail Email
	 */
	private void createUser(String aUser, String aPass, String aName, String aSurname, String aEmail)
	{
		try {
			Statement lStatement = fConnection.createStatement();
			String statement = "INSERT INTO USER VALUES(\"" + aUser + "\",\""
			+ aPass + "\",\""
			+ aName + "\",\""
			+ aSurname + "\",\""
			+ aEmail + "\","
			+ "0);";
			lStatement.executeUpdate(statement);
		} catch (SQLException e) {
			System.out.println("Unable to add new user");
		}
	}

	/**
	 * Adds a new developer to the database
	 * @param aName Name of developer
	 */
	private void createDeveloper(String aName)
	{
		try
		{
			Statement lStatement = fConnection.createStatement();
			String statement = "INSERT INTO Developer VALUES(\"" + aName +"\")";
			lStatement.executeUpdate(statement);
		} catch(SQLException e)
		{
			System.out.println("Unable to add new Developer");
		}
	}

	/**
	 * Adds a new platform to the database
	 * @param aName platform name
	 * @param aDesc platform description
	 */
	private void createPlatform(String aName, String aDesc)
	{
		try
		{
			Statement lStatement = fConnection.createStatement();
			String statement = "INSERT INTO Platform VALUES(\""
			+ aName + "\",\""
			+ aDesc + "\");";
			lStatement.executeUpdate(statement);
		} catch(SQLException e)
		{
			System.out.println("Unable to add new Developer");
		}
	}

	/**
	 * Adds a new page comment
	 * @param aMadeBy Person who made comment
	 * @param aMadeTo Person who the comment is aimed at
	 * @param aTime	Time it was made
	 * @param aComment The comment
	 */
	private void createPageComment(String aMadeBy, String aMadeTo, long aTime, String aComment)
	{
		try
		{
			long time = new Date().getTime();
			Statement lStatement = fConnection.createStatement();
			String statement = "INSERT INTO Platform VALUES(\"" + aMadeBy + "\",\""
			+ aMadeTo + "\","
			+ time + ",\""
			+ aComment + "\",);";
			lStatement.executeUpdate(statement);
		} catch(SQLException e)
		{
			System.out.println("Unable to add new Developer");
		}
	}

	/**
	 * Adds a new game to the database with the default rating of 0
	 * @param aGame GameName
	 * @param aDesc Description of game
	 * @param aDev Developer
	 * @param aPlat Platform
	 */
	private void createGame(String aGame, String aDesc, String aDev, String aPlat)
	{
		try
		{
			Statement lStatement = fConnection.createStatement();
			String statement = "INSERT INTO Game VALUES(\"" + aGame + "\",\""
			+ aDesc + "\","
			+ 0 + ","
			+ 0 + ",\""
			+ aDev + "\",\""
			+ aPlat + "\");";
			lStatement.executeUpdate(statement);
		} catch(SQLException e)
		{
			System.out.println("Unable to add new Developer");
		}
	}

	/**
	 * TODO: Use gamename to find platform and developername also get time? Also implement this method!
	 * @param aComment
	 * @param aGameName
	 */
	private void createGameComment(String aComment, String aGameName)
	{
		long time = new Date().getTime();]
		try
		{
			Statement lStatement = fConnection.createStatement();
			String statement = "DELETE FROM GameComment WHERE CommentText = " +
			"\"" + aComment + "\" " +
			"AND Username = \"" + aUser + "\"" +
			"AND Time = " + aTime +
			";";
			lStatement.executeUpdate(statement);
		} catch (SQLException e)
		{

		}
	}

	/**
	 * TODO: Implement this method, grab the time and get developer + platform from game table
	 * @param aStatus Status of completeion
	 * @param aComment Comment on game
 	 * @param aUser Username
	 * @param aGameName Game name
	 */
	private void createOwnedGame(String aStatus, String aComment, String aUser, String aGameName)
	{
	}

	private void deleteGameComment(long aTime, String aUser, String aComment)
	{
		try
		{
			Statement lStatement = fConnection.createStatement();
			String statement = "DELETE FROM GameComment WHERE CommentText = " +
			"\"" + aComment + "\" " +
			"AND Username = \"" + aUser + "\"" +
			"AND Time = " + aTime +
			";";
			lStatement.executeUpdate(statement);
		} catch(SQLException e)
		{
			System.out.println("Unable to add new Developer");
		}
	}

	/**
	 * Prints out the contents of a table in the console
	 * @param aTableName
	 */
	private void printTable(String aTableName)
	{
		try
		{
			Statement lStatement = fConnection.createStatement();
			String statement = "SELECT * FROM " + aTableName + ";";
			ResultSet result = lStatement.executeQuery(statement);
			ResultSetMetaData resultMeta = result.getMetaData();

			int columnCount = resultMeta.getColumnCount();

			//Print column names
			for(int i = 1; i <columnCount; i++)
			{
		        if (i > 1)
		        	System.out.print(",  ");
		        String columnName = resultMeta.getColumnName(i);
		        System.out.print(columnName);
			}

			System.out.println();

			//Print row data
			while(result.next())
			{
		        for (int i = 1; i <= columnCount; i++) {
		            if (i > 1) System.out.print(",  ");
		            String columnValue = result.getString(i);
		            System.out.print(columnValue);
		        }
		        System.out.println();
			}

		} catch(SQLException e)
		{
			System.out.println("Unable to print table " + aTableName);
		}
	}

	/**
	 * Changes the password of the passed in username
	 * @param aUser Username to change password of
	 * @param aPass Password to change to
	 */
	private void changePassword(String aUser, String aPass)
	{
		try
		{
			Statement lStatement = fConnection.createStatement();
			String statement = "UPDATE user SET Password= \"" + aPass
			+ "\" WHERE username = \""
			+ aUser + "\";";
			lStatement.executeUpdate(statement);
		} catch (SQLException e)
		{
			System.out.println("Unable to change password");
		}
	}
}