package Controler.IO;

import java.awt.geom.Point2D;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import Model.Acquisition;
import Model.IAcquisition;
import Model.IPoint;
import Model.IUser;
import Model.Point;
import Model.Signature;
import Model.User;

public class DB {

	private static DB instance;
	private Connection conn = null;

	public static DB getInstance() {
		if(instance == null) {
			instance = new DB();
			instance.initialize();
		}
		return instance;
	}


	public void initialize() {
		try {
			
//			String userName = "thibaud";
//			String password = "thibaud";
//			String url = "jdbc:mysql://178.170.114.59/biometrie";
			
			String userName = "root";
			String password = "";			
			String url = "jdbc:mysql://localhost/biometrie";
			
			Class.forName ("com.mysql.jdbc.Driver").newInstance ();
			DriverManager.setLoginTimeout(2);
			conn = DriverManager.getConnection (url, userName, password);      
		}
		catch (Exception e) {
			System.err.println ("Cannot connect to database server");
		}
	}

	public List<IUser> getAllUsers() {
		List<IUser> usersList = new ArrayList<IUser>();

		if(conn != null) {

			try {
				Statement s = conn.createStatement ();
				s.executeQuery ("SELECT userId, userLastName, userFirstName FROM user");
				ResultSet rs = s.getResultSet ();

				while (rs.next())
				{
					IUser tmp =	new User(rs.getInt ("userId"), 
							rs.getString ("userFirstName"), 
							rs.getString ("userLastName")
							);

					usersList.add(tmp);
				}

				rs.close ();
				s.close ();

			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return usersList;

	}
	
	public List<IAcquisition> getAllAcquisitions(Signature signature) {
		List<IAcquisition> acquisitionsList = new ArrayList<IAcquisition>();

		if(conn != null) {

			try {
				Statement s = conn.createStatement ();
				s.executeQuery ("SELECT acquisitionId, acquisitionIsLog FROM acquisition " +
						"WHERE userId="+signature.getUser().getUserId());
				ResultSet rs = s.getResultSet ();

				while (rs.next())
				{
					IAcquisition tmp =	new Acquisition(
							rs.getInt ("acquisitionId"), 
							rs.getInt ("acquisitionIsLog") == 1);

					acquisitionsList.add(tmp);
				}

				rs.close ();
				s.close ();

			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return acquisitionsList;

	}
	//TODO Remplacer le User par un IUser
	
	public User findUser(String firstName, String lastName) {

		if(conn != null) {

			try {
				Statement s = conn.createStatement ();
				s.executeQuery ("SELECT userId, userLastName, userFirstName FROM user " +
						"WHERE userFirstName='"+firstName+"' AND userLastName='"+lastName+"'");
				ResultSet rs = s.getResultSet ();
				if(rs.isLast()) {
					return null;
				}
				rs.next();
				User tmp =	new User(rs.getInt ("userId"), 
						rs.getString ("userFirstName"), 
						rs.getString ("userLastName")
						);

				rs.close ();
				s.close ();

				return tmp;
			} catch (Exception e) {
				//e.printStackTrace();
				return null;
			}
		}
		return null;
	}

	public List<IPoint> getAllAPoints(IAcquisition a) {
		List<IPoint> pointsList = new ArrayList<IPoint>();

		if(conn != null) {

			try {
				Statement s = conn.createStatement ();
				s.executeQuery ("SELECT pointId, pointX, pointY, pointPressure, pointTime FROM point " +
						"WHERE acquisitionId="+a.getAcquisitionId());
				ResultSet rs = s.getResultSet();

				while (rs.next())
				{
					Point2D.Double p = new Point2D.Double();
					p.x = rs.getDouble("pointX");
					p.y = rs.getDouble("pointY");
					IPoint tmp =	new Point(
							rs.getInt ("pointId"), 
							p,
							rs.getInt("pointPressure"),
							rs.getFloat("pointTime"));

					pointsList.add(tmp);
				}

				rs.close ();
				s.close ();

			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return pointsList;

	}
	/**
	 * Retourne le nombre total de logs
	 */
	public int getNumberOfLogs(){
		long numberOfLog = 0;
		if(conn != null) {

			try {
				Statement s = conn.createStatement ();
				s.executeQuery ("SELECT COUNT(idLog) AS nb FROM log");
				ResultSet rs = s.getResultSet();
				rs.first();// on se d�cale au premier resultat renvoye
				numberOfLog = rs.getLong("nb");// on retourne la valeur contenue dans la premiere colonne du resultat
				rs.close ();
				s.close ();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return (int)numberOfLog;
	}
	/**
	 * Retourne le nombre total d'entree pour lesquelles
	 * le champ isSignatureValid est true et le champ isUserValid est false.
	 * Conclusion : c'est le nombre total de fausses acceptations
	 * 
	 */
	public int getNumberOfFalseApprobation(){
		int numberOfLog = 0;
		if(conn != null) {

			try {
				Statement s = conn.createStatement ();
				s.executeQuery ("SELECT COUNT(idLog) FROM Log WHERE isSignatureValid > 0 AND isUserValid = 0");
				ResultSet rs = s.getResultSet();
				rs.first();
				numberOfLog = rs.getInt(1);// on retourne la valeur contenue dans la premiere colonne du resultat
				rs.close ();
				s.close ();

			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return numberOfLog;
	}
	/**
	 * Retourne le nombre total d'entree pour lesquelles
	 * le champ isSignatureValid est false et le champ isUserValid est true.
	 * Conclusion : c'est le nombre total de faux refus
	 */
	public int getNumberOfFalseRefusal(){
		int numberOfFR = 0;
		if(conn != null) {
			try {
				Statement s = conn.createStatement ();
				s.executeQuery ("SELECT COUNT(idLog) FROM Log WHERE isSignatureValid = 0 AND isUserValid = 0");
				ResultSet rs = s.getResultSet();
				rs.first();
				numberOfFR = rs.getInt(1);// on retourne la valeur contenue dans la premiere colonne du resultat
				rs.close ();
				s.close ();

			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return numberOfFR;
	}
	
	/**
	 * Return  the first acquisition of the given user
	 * @throws SQLException
	 */
	public IAcquisition getFirstAcquisition(IUser user){
		//on recupere l'id de l'utilisateur specifié
		int userId = user.getUserId();
		//on recup ensuite l'id de la premiere acquisition de cet utilisateur
		IAcquisition acquisition = null;
		if(conn != null) {
			try {
				Statement s = conn.createStatement ();
				s.executeQuery ("SELECT * FROM biometrie.acquisition WHERE userId=" + userId + " LIMIT 0,1;");
				ResultSet rs = s.getResultSet();
				rs.first();
				acquisition = new Acquisition(
						rs.getInt ("acquisitionId"), 
						rs.getInt ("acquisitionIsLog") == 1
					);
				rs.close ();
				s.close ();

			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return acquisition;
	}
	public int addUser(String firstName, String lastName) throws SQLException {
		Statement s = conn.createStatement ();
		s.executeUpdate ("INSERT INTO user(userFirstName, userLastName) " +
				" VALUES('"+firstName+"', '"+lastName+"')", Statement.RETURN_GENERATED_KEYS);
		ResultSet resultSet = s.getGeneratedKeys(); 
		if (resultSet != null && resultSet.next()) { 
			return resultSet.getInt(1);
		}
		return 0;
	}

	public int addAcquisition(User user, boolean isLog) throws SQLException {
		Statement s = conn.createStatement ();
		s.executeUpdate ("INSERT INTO acquisition(acquisitionIsLog, userId) " +
				" VALUES("+(isLog ? 1 : 0)+", "+user.getUserId()+")", Statement.RETURN_GENERATED_KEYS);
		ResultSet resultSet = s.getGeneratedKeys(); 
		if (resultSet != null && resultSet.next()) { 
			return resultSet.getInt(1);
		}
		return 0;
	}

	public int addPoint(Acquisition a, Point2D.Double point, int pressure, float time) throws SQLException {
		Statement s = conn.createStatement ();
		s.executeUpdate ("INSERT INTO point(acquisitionId, pointX, pointY, pointPressure, pointTime) " +
				" VALUES("+a.getAcquisitionId()+", "+point.x+", "+point.y+", "+pressure+", "+time+")", Statement.RETURN_GENERATED_KEYS);
		ResultSet resultSet = s.getGeneratedKeys(); 
		if ( resultSet != null && resultSet.next()) { 
			return resultSet.getInt(1);
		}
		return 0;
	}

	public void addPoints(Acquisition a, List<IPoint> points) throws SQLException {

		String req = "INSERT INTO point(acquisitionId, pointX, pointY, pointPressure, pointTime) VALUES";

		boolean foo = false;

		for(IPoint point : points) {

			if(foo) {
				req += ", ";
			} else {
				foo = true;
			}

			req += "("+a.getAcquisitionId()+", "+point.getPoint().x+", "+point.getPoint().y+", "+point.getPressure()+", "+point.getTime()+")";

		}


		if(points.size() != 0) {
			Statement s = conn.createStatement ();
			s.executeUpdate (req);
			s.close();
			System.out.println(req);
		} else {
			System.out.println("Pas de points");
		}
	}


	public Connection getConnection() {
		return conn;
	}


	public int addLog(int acquisitionId, boolean isSignatureId, boolean isUserValid) throws SQLException {
		Statement s = conn.createStatement ();
		s.executeUpdate ("INSERT INTO log(acquisitionId, isSignatureValid, isUserValid) " +
				" VALUES("+acquisitionId+","+(isSignatureId ? 1 : 0)+", "+(isUserValid ? 1 : 0)+")", Statement.RETURN_GENERATED_KEYS);
		ResultSet resultSet = s.getGeneratedKeys(); 
		if (resultSet != null && resultSet.next()) { 
			return resultSet.getInt(1);
		}
		return 0;
	}

}
