package dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Properties;

import time.Entry;








/**
 * @author Anders Njøs Slinde
 *
 */
public class TimeDAO {

	private String driver = "org.apache.derby.jdbc.EmbeddedDriver";
	private String protocol = "jdbc:derby:";
	private Statement statement;
	private Connection connection;
	private String dbName = "timeDB"; // the name of the database

	//ID and name of the user logged in.
	private int userID;
	private String userName;
	private double userWage = 0;

	/**
	 * Constructor is private to make creation of class illegal except by the singleton.
	 * connect 'jdbc:derby://localhost:1527/timeDB';
	 */
	private TimeDAO() {
		try {
			Class.forName(driver).newInstance();
			Properties props = new Properties();
			connection = DriverManager.getConnection(protocol + dbName + ";create=true", props);
			statement = connection.createStatement();

			//createTimeDB();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * Method to create new database
	 */
	private void createTimeDB() {
		String query1 =
			"CREATE TABLE entries (" +
			"id INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
			"userID INT NOT NULL," +
			"date DATE NOT NULL," +
			"start TIME NOT NULL," +
			"finish TIME NOT NULL," +
			"hours INT NOT NULL," +
			"factor INT NOT NULL," +
			"comment VARCHAR(500)," +
			"type VARCHAR(100)" +
			")";

		String query2 =
			"CREATE TABLE users (" +
			"id INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
			"name VARCHAR(100) NOT NULL," +
			"wage DOUBLE NOT NULL" +
			")";

		try {
			statement.execute(query1);
			statement.execute(query2);


		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Method to insert a User record into the users table
	 * @param record
	 * @throws SQLException
	 * @return true if user inserted successfully
	 */
	public boolean insertUser(String name, double wage) {
		name = name.toLowerCase();
		String query = "insert into users values" +
		"(DEFAULT, '" +
		name + "' ," +
		wage +
		")";

		try {
		statement.execute(query);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}


	/**
	 * @param user name of the user
	 * @return the id of the user
	 */
	public int checkUser(String user) {
		user = user.toLowerCase();
		String query = "select * from users where NAME='" + user + "'";
		try {
			ResultSet rs = statement.executeQuery(query);

			if(rs.next()) {
				int id = rs.getInt(1);
				return id;
			} else {
				return -1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
	}



	/**
	 * @param id
	 * @return users name
	 */
	public double getWageById(int id) {
		String query = "select wage from users where ID=" + id + "";
		try {
			ResultSet rs = statement.executeQuery(query);

			if(rs.next()) {
				double wage = rs.getDouble(1);
				return wage;
			} else {
				return -1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
	}

	/**
	 * Method to update a users wage
	 * @param wage what wage does the user want
	 * @param userID what user wants his wage changed
	 * @return true if it was a success
	 */
	public boolean changeWage(double wage, int userID) {
		String query = "UPDATE users SET wage=" + wage + " where id=" + userID;
		try {
			statement.execute(query);
			return true;
		} catch (SQLException e) {
			//e.printStackTrace();
			return false;
		}


	}

	/**
	 * Helper method used to create a SQL time object.
	 * @param hour
	 * @param min
	 * @return {@link Time} timeOject
	 */
	public Time createTime(int hour, int min) {
		Calendar cal = Calendar.getInstance();
		// set Date portion to January 1, 1970
		cal.set(Calendar.YEAR, 1970 );
		cal.set(Calendar.MONTH, Calendar.JANUARY );
		cal.set(Calendar.DATE, 1 );
		cal.set(Calendar.HOUR_OF_DAY, hour);
		cal.set(Calendar.MINUTE, min);
		return new Time(cal.getTimeInMillis());
	}

	/**
	 * Helper method used to create a SQL date object.
	 * @param year
	 * @param month
	 * @param day
	 * @return {@link Date} dateOject
	 */
	public Date createDate(int year, int month, int day) {
		Calendar cal = Calendar.getInstance();
		// set Date portion to January 1, 1970
		cal.set(Calendar.YEAR, year );
		cal.set(Calendar.MONTH, month-1);
		cal.set(Calendar.DATE, day );
		cal.set(Calendar.HOUR_OF_DAY, 0);
		cal.set(Calendar.MINUTE, 0);
		return new Date(cal.getTimeInMillis());
	}

	/**
	 * Inserts a new entry into the database
	 * @param entry to be inserted
	 * @return true if the entry was inserted successfully
	 */
	public boolean insertEntry(Entry entry) {
		PreparedStatement psInsert;
		try {
			psInsert = connection.prepareStatement("insert into entries values " +
					"(DEFAULT," + 	// primary key is generated
					" ?," + 		// userid
					" ?," + 		// date
					" ?," +			// start
					" ?," +			//finish
					" ?," +			//hours
					" ?," +			//factor
					" ?," +			//comment
					" ?" +			// type
					")");
			psInsert.setInt(1, entry.getUserId());
			psInsert.setDate(2, entry.getDate());
			psInsert.setTime(3, entry.getStart());
			psInsert.setTime(4, entry.getFinish());
			psInsert.setInt(5, entry.getHours());
			psInsert.setInt(6, entry.getFactor());
			psInsert.setString(7, entry.getComment());
			psInsert.setString(8, entry.getType());
			psInsert.executeUpdate();
			//System.out.println("HALLO???");
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * Method to update an entry
	 * @param entry What entry to be updated
	 * @param entryID id for the entry to be updated
	 * @return true if entry was updated without mishaps
	 */
	public boolean updateEntry(Entry entry, int entryID) {
		PreparedStatement psInsert;
		try {
			psInsert = connection.prepareStatement("UPDATE entries SET " +
					" userid=?," + 		// userid
					" date=?," + 		// date
					" start=?," +			// start
					" finish=?," +			//finish
					" hours=?," +			//hours
					" factor=?," +			//factor
					" comment=?," +			//comment
					" type=?" +			// type
					"WHERE id=?" +
					"");
			psInsert.setInt(1, entry.getUserId());
			psInsert.setDate(2, entry.getDate());
			psInsert.setTime(3, entry.getStart());
			psInsert.setTime(4, entry.getFinish());
			psInsert.setInt(5, entry.getHours());
			psInsert.setInt(6, entry.getFactor());
			psInsert.setString(7, entry.getComment());
			psInsert.setString(8, entry.getType());
			psInsert.setInt(9, entryID);
			psInsert.executeUpdate();
			//System.out.println("HALLO???");
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * Method to retrieve all the entries from the entries table
	 * @return {@link ArrayList} an ArrayList containing all the entries
	 */
	public ArrayList<Entry> retrieveAllEntries() {
		ArrayList<Entry> entries = new ArrayList<Entry>();

		try {
		ResultSet rs = statement.executeQuery("select * from entries inner join users on entries.userid=users.id");
		while (rs.next()) {
			int entryId = rs.getInt(1);
			int userId = rs.getInt(2);
			Date date = rs.getDate(3);
			Time start = rs.getTime(4);
			Time finish = rs.getTime(5);
			int hours = rs.getInt(6);
			int factor = rs.getInt(7);
			String comment = rs.getString(8);
			String type = rs.getString(9);

			String username = rs.getString(11);

			Entry entry = new Entry(entryId, start, finish, date, hours, userId, username, comment, factor, type);
			entries.add(entry);
		}
		} catch (SQLException e){
			//e.printStackTrace();
		}
		return entries;
	}

	/**
	 * Method to retrieve the entries from a specified month
	 * @return {@link ArrayList} an ArrayList containing all the entries
	 */
	public ArrayList<Entry> retrieveEntriesByMonth(int month) {
		ArrayList<Entry> entries = new ArrayList<Entry>();
		month = month - 1;
		try {
		ResultSet rs = statement.executeQuery("select * from entries inner join users on entries.userid=users.id");
		while (rs.next()) {
			int entryId = rs.getInt(1);
			int userId = rs.getInt(2);
			Date date = rs.getDate(3);
			Time start = rs.getTime(4);
			Time finish = rs.getTime(5);
			int hours = rs.getInt(6);
			int factor = rs.getInt(7);
			String comment = rs.getString(8);
			String type = rs.getString(9);

			String username = rs.getString(11);
			if(date.getMonth() == month) {
			Entry entry = new Entry(entryId, start, finish, date, hours, userId, username, comment, factor, type);
			entries.add(entry);
			}
		}
		} catch (SQLException e){
			//e.printStackTrace();
		}
		return entries;
	}

	/**
	 * Method to retrieve the entries from a specified month
	 * @return {@link ArrayList} an ArrayList containing all the entries
	 */
	public ArrayList<Entry> retrieveEntriesByYear(int year) {
		ArrayList<Entry> entries = new ArrayList<Entry>();

		try {
		ResultSet rs = statement.executeQuery("select * from entries inner join users on entries.userid=users.id");
		while (rs.next()) {
			int entryId = rs.getInt(1);
			int userId = rs.getInt(2);
			Date date = rs.getDate(3);
			Time start = rs.getTime(4);
			Time finish = rs.getTime(5);
			int hours = rs.getInt(6);
			int factor = rs.getInt(7);
			String comment = rs.getString(8);
			String type = rs.getString(9);

			String username = rs.getString(11);
			if(date.getYear() == year) {
			Entry entry = new Entry(entryId, start, finish, date, hours, userId, username, comment, factor, type);
			entries.add(entry);
			}
		}
		} catch (SQLException e){
			//e.printStackTrace();
		}
		return entries;
	}

	/**
	 * Method to retrieve the entries from a specified type
	 * @return {@link ArrayList} an ArrayList containing all the entries
	 */
	public ArrayList<Entry> retrieveEntriesByType(String type1) {
		ArrayList<Entry> entries = new ArrayList<Entry>();

		try {
		ResultSet rs = statement.executeQuery("select * from entries inner join users on entries.userid=users.id");
		while (rs.next()) {
			int entryId = rs.getInt(1);
			int userId = rs.getInt(2);
			Date date = rs.getDate(3);
			Time start = rs.getTime(4);
			Time finish = rs.getTime(5);
			int hours = rs.getInt(6);
			int factor = rs.getInt(7);
			String comment = rs.getString(8);
			String type = rs.getString(9);

			String username = rs.getString(11);
			if(type.compareTo(type1) == 0) {
			Entry entry = new Entry(entryId, start, finish, date, hours, userId, username, comment, factor, type);
			entries.add(entry);
			}
		}
		} catch (SQLException e){
			//e.printStackTrace();
		}
		return entries;
	}


	/**
	 * Helper method to create new entry from user input
	 * @param start
	 * @param finish
	 * @param date
	 * @param hours
	 * @param userName
	 * @param comment
	 * @param factor
	 * @param type
	 * @return an EntryObject
	 */
	public Entry createEntry(String start, String finish, String dateString, String hourString,
			int userID, String userName, String comment, String factorString, String type) throws NumberFormatException {

		int startHour = 0;
		int startMinute = 0;
		int finishHour = 0;
		int finishMinute = 0;
		int dateDay = 0;
		int dateMonth = 0;
		int dateYear = 0;
		try {
			startHour = java.lang.Integer.parseInt(start.substring(0, 2));
			startMinute = java.lang.Integer.parseInt(start.substring(3, 5));

			finishHour = java.lang.Integer.parseInt(finish.substring(0, 2));
			finishMinute = java.lang.Integer.parseInt(finish.substring(3, 5));

			dateDay = java.lang.Integer.parseInt(dateString.substring(0, 4));
			dateMonth = java.lang.Integer.parseInt(dateString.substring(5, 7));
			dateYear = java.lang.Integer.parseInt(dateString.substring(8, 10));
		} catch (Exception e) {
			return null;
		}

		Time finishTime = createTime(finishHour, finishMinute);
		Time startTime = createTime(startHour, startMinute);
		Date date = createDate(dateYear, dateMonth, dateDay);

		int hours = java.lang.Integer.parseInt(hourString);

		int factor = java.lang.Integer.parseInt(factorString);

		Entry returnEntry = new Entry(startTime, finishTime, date, hours, userID, userName, comment, factor, type);

		return returnEntry;

	}

	/**
	 * Deletes an entry after entry id
	 * @param entryID
	 * @return true if entry was removed
	 */
	public boolean removeEntry(int entryID) {

		String query = "delete from entries where id=" + entryID;

		try {
			statement.execute(query);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * Logges the user in, can be expanded to include a password
	 * @param name
	 * @return true if the user was logged in
	 */
	public boolean logInn(String name) {
		name = name.toLowerCase();
		int id = checkUser(name);
		double wage = getWageById(id);
		if(id > 0) {
			this.setUserID(id);
			this.setUserName(name);
			this.setUserWage(wage);
			return true;
		}
		return false;
	}

	/**
	 * @return wage
	 */
	public double getUserWage() {
		return userWage;
	}

	/**
	 * Saves the logged in users name
	 * @param userWage
	 */
	public void setUserWage(double userWage) {
		this.userWage = userWage;
	}

	/**
	 * sets the logged in users Id
	 * @param id
	 */
	private void setUserID(int id) {
		this.userID = id;

	}
	/**
	 * @return the userID
	 */
	public int getUserID() {
		return userID;
	}

	/**
	 * @param userName
	 */
	public void setUserName(String userName) {
		this.userName = userName;
	}

	/**
	 * @return the userName
	 */
	public String getUserName() {
		return userName;
	}

	/**
	 * Singleton class to instantiate only one object of the database connector.
	 */
	private static class SingletonHolder {
		private static final TimeDAO INSTANCE = new TimeDAO();
	}

	public static TimeDAO getInstance() {
		return SingletonHolder.INSTANCE;
	}






}