import java.sql.Connection;
import java.sql.*;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Vector;

import javax.swing.table.DefaultTableModel;

import com.mysql.jdbc.PreparedStatement;

import net.proteanit.sql.DbUtils;

public class Model {

	private View view;
	private adminView adminView;
	private PassengerView passengerview;
	private driverView driverview;
	private updateAccount update;
	private bookRide book;
	private registerVehicle register;
	private addFavorite favorite;
	private addPayment payment;
	private String url = "jdbc:mysql://localhost:3306/uber?useSSL=false";
	private String user = "root";
	private String pass = "nobunaga";
	private Users loggedin = new Users();



	public void printText ( ) {
		try {
			// Get connection
			Connection myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/uber?useSSL=false", "root", "nobunaga");
			// create statement
			Statement myStmt = myConn.createStatement();
			// execute SQL query
			ResultSet myRs = myStmt.executeQuery("select * from user");
			// process
			while (myRs.next()) {
				System.out.println(myRs.getString("CelNo"));
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void logout () {
		this.setLoggedin(null);
	}

	public void insertData (String fName, String lName, String email, String CelNumber, String password, LocalDate localDate, int type, String license) {
		if (type == 0) {
		try {
			// Get connection
			Connection myConn = DriverManager.getConnection(url, user, pass);
			// create statement
			Statement myStmt = myConn.createStatement();
			// execute SQL query

			String sql = "insert into uber.user "
						+ "(LastName, FirstName, Email, CelNo, Password, DateRegistered)"
						+ " values ('" +lName +"','" +fName +"','" +email +"','" +CelNumber + "','"+password +"','" +localDate +"')";
			// process
			myStmt.executeUpdate(sql);

			String sql2 = "INSERT into uber.passenger (PCelno) values ('" + CelNumber +"')";
			myStmt.executeUpdate(sql2);

			System.out.println("Insertion complete");



		}
		catch (Exception e) {
			e.printStackTrace();
			}
		}
		else if (type == 1) {
			try {
				// Get connection
				Connection myConn = DriverManager.getConnection(url, user, pass);
				// create statement
				Statement myStmt = myConn.createStatement();
				// execute SQL query

				String sql = "INSERT INTO uber.user "
							+ "(LastName, FirstName, Email, CelNo, Password, DateRegistered)"
							+ " values ('" +lName +"','" +fName +"','" +email +"','" +CelNumber + "','"+password +"','" +localDate +"')";
				// process
				myStmt.executeUpdate(sql);

				String sql2 = "INSERT into uber.driver (DCellNo, LicenseNo) values ('" + CelNumber +"','" +license + "')";

				myStmt.executeUpdate(sql2);

				System.out.println("Insertion complete");



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

	public void registerVehicle (String license, String color, String date, String type) {
		try {
			// Get connection
			Connection myConn = DriverManager.getConnection(url, user, pass);
			// create statement
			Statement myStmt = myConn.createStatement();
			// execute SQL query

			String sql = "INSERT INTO uber.vehicle "
						+ "(License, Color, Type, DateBought)"
						+ " VALUES ('" +license +"','" +color +"','" +type +"','" +date + "')";
			// process
			myStmt.executeUpdate(sql);

			System.out.println("Insertion complete");


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

	public void addPayment (String CardNo, String ExpiryDate, String CVV, String CellNo) {
		try {
			// Get connection
			Connection myConn = DriverManager.getConnection(url, user, pass);
			// create statement
			Statement myStmt = myConn.createStatement();
			// execute SQL query

			String sql = "INSERT INTO uber.paymentmethod "
						+ "(CardNo, ExpiryDate, CVV, CellNo)"
						+ " VALUES ('" +CardNo +"','" +ExpiryDate +"','" +CVV +"','" +CellNo + "')";
			// process
			myStmt.executeUpdate(sql);

			String sql2 = "INSERT INTO uber.passenger"
					+ "(PaymentMethod)"
					+ "VALUES (Credit Card)";
			myStmt.executeUpdate(sql2);
			System.out.println("Insertion complete");


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

	public void bookRide (String Start, String End, String Type) {
		try {
			// Get connection
			Connection myConn = DriverManager.getConnection(url, user, pass);
			// create statement
			Statement myStmt = myConn.createStatement();
			// execute SQL query

			String sql1 = "SELECT Distance FROM uber.distances "
					+ "WHERE StartPoint = '" +Start + "'"
					+ "AND EndPoint = '" +End + "'";

			ResultSet rs = myStmt.executeQuery(sql1);
			float distance = rs.getFloat("Distance");

			String sql2 = "SELECT Multiplier FROM uber.vehicleclass"
					+ "WHERE Type = '" +Type + "'";
			ResultSet rs2 = myStmt.executeQuery(sql2);
			float multiplier = rs.getFloat("Multiplier");

			float fare = 100 * multiplier + distance *2;

			String sql = "INSERT INTO uber.trip "
						+ "(TotalFare, Type, DistanceCovered)"
						+ " VALUES ('"+fare +"','" +Type + "','" +distance +"')";
			// process
			myStmt.executeUpdate(sql);



			System.out.println("Insertion complete");


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

	public void addFavorite (String Unitno, String CelNo, String Street, String Village, String City) {
		try {
			// Get connection
			Connection myConn = DriverManager.getConnection(url, user, pass);
			// create statement
			Statement myStmt = myConn.createStatement();
			// execute SQL query

			String sql = "INSERT INTO uber.favorites "
						+ "(CellNo, UnitNo, StreetName, Village, City)"
						+ " VALUES ('" +CelNo +"','" +Unitno + "','" +Street +"','" +Village + "','" +City + "')";
			// process
			myStmt.executeUpdate(sql);

			System.out.println("Insertion complete");


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

	public void deleteData (String fName, String lName, String email, String CelNumber, String password, LocalDate localDate, int type, String license) {
		if (type == 0) {
			try {
				// Get connection
				Connection myConn = DriverManager.getConnection(url, user, pass);
				// create statement
				Statement myStmt = myConn.createStatement();
				// execute SQL query

				String sql = "DELETE FROM uber.user LastName, FirstName, Email, CelNo, Password, DateRegistered"
						+ "WHERE LastName = '" +lName + "' AND " +"FirstName = '" +fName + "' AND Email = '" +email + "' AND CelNo = '" +CelNumber + "' AND DateRegistered = '" +localDate +"'" ;

				// process
				myStmt.executeUpdate(sql);

				String sql2 = "DELETE FROM uber.driver PCellNo, LicenseNo WHERE PCellNo = '" + CelNumber +"' AND PaymentMethod = " + loggedin.getPaymentMethod();

				myStmt.executeUpdate(sql2);

				System.out.println("Insertion complete");

			}
			catch (Exception e) {
				e.printStackTrace();
				}
		}
		else if (type == 1) {
		try {
			// Get connection
			Connection myConn = DriverManager.getConnection(url, user, pass);
			// create statement
			Statement myStmt = myConn.createStatement();
			// execute SQL query

			String sql = "DELETE FROM uber.user LastName, FirstName, Email, CelNo, Password, DateRegistered"
					+ "WHERE LastName = '" +lName + "' AND " +"FirstName = '" +fName + "' AND Email = '" +email + "' AND CelNo = '" +CelNumber + "' AND DateRegistered = '" +localDate +"'" ;

			// process
			myStmt.executeUpdate(sql);

			String sql2 = "DELETE FROM uber.driver DCellNo, LicenseNo WHERE DCellNo = '" + CelNumber +"', LicenseNo = '" +license + "')";

			myStmt.executeUpdate(sql2);

			System.out.println("Insertion complete");

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

	public void deleteAccount (Users loggedin) {
		if (loggedin.getsType() == "Driver") {
		try {
				System.out.println("Type = Driver");
				System.out.println("Cell Number: " +loggedin.getsCellNo());
				System.out.println("Password: " +loggedin.getsPassword());
				// Get connection
				Connection myConn = DriverManager.getConnection(url, user, pass);
				// create statement
				Statement myStmt = myConn.createStatement();
				// execute SQL query

				String sql = "DELETE FROM uber.user WHERE CelNo = '" +loggedin.getsCellNo() + "'";

				// process
				myStmt.executeUpdate(sql);


				String sql2 = "DELETE FROM uber.driver WHERE DCellNo = '" + loggedin.getsCellNo() + "'";

				myStmt.executeUpdate(sql2);

				System.out.println("Delete complete");
				}

			catch (Exception e) {
				e.printStackTrace();
				}
		}
			else if (loggedin.getsType() == "Passenger") {
				try {
					System.out.println("Type = Passenger");
					System.out.println("Cell Number: " +loggedin.getsCellNo());
					System.out.println("Password: " +loggedin.getsPassword());
					// Get connection
					Connection myConn = DriverManager.getConnection(url, user, pass);
					// create statement
					Statement myStmt = myConn.createStatement();
					// execute SQL query

					String sql = "DELETE FROM uber.user WHERE CelNo = '" +loggedin.getsCellNo() +"'";

					// process
					myStmt.executeUpdate(sql);


					String sql2 = "DELETE FROM uber.passenger WHERE PCelNo = '" + loggedin.getsCellNo() + "'";

					myStmt.executeUpdate(sql2);

					System.out.println("Delete complete");
					}

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

			this.loggedin = null;
	}

	public void updateData (String fName, String lName, String email, String CelNumber, String password, LocalDate localDate, int type, String license) {
		if (type == 1) {
			try {
					String temp = loggedin.getsCellNo();
					System.out.println("Temp =" +temp);
					// Get connection
					Connection myConn = DriverManager.getConnection(url, user, pass);
					// create statement
					Statement myStmt = myConn.createStatement();
					// execute SQL query

					String sql = "UPDATE uber.user SET CelNo = '"+CelNumber +"', " + "FirstName = '" +fName +"', "+ "LastName = '" +lName + "', " + "Email = '"+email + "', "
							+ "Password = '" +password +"'" + "WHERE CelNo = '" +temp +"'";

					// process
					myStmt.executeUpdate(sql);


					String sql2 = "UPDATE uber.driver SET DCellNo = '"+CelNumber + "', LicenseNo = '" +license +"' WHERE DCellNo = '" + temp;

					myStmt.executeUpdate(sql2);

					System.out.println("Update complete");
					}

				catch (Exception e) {
					e.printStackTrace();
					}
			}
			else if (type == 0) {
					try {
						String temp = loggedin.getsCellNo();
						System.out.println("Temp =" +temp);
						// Get connection
						Connection myConn = DriverManager.getConnection(url, user, pass);
						// create statement
						Statement myStmt = myConn.createStatement();
						// execute SQL query

						String sql = "UPDATE uber.user SET CelNo = '"+CelNumber +"', " + "FirstName = '" +fName +"', "+ "LastName = '" +lName + "', " + "Email = '"+email + "', "
								+ "Password = '" +password +"'" + "WHERE CelNo = '" +temp +"'";

						// process
						myStmt.executeUpdate(sql);


						String sql2 = "UPDATE uber.driver SET PCelNo = '"+CelNumber + "' WHERE DCellNo = '" + temp + "'";

						myStmt.executeUpdate(sql2);

						System.out.println("Update complete");
						}

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

	public int checkData (String CelNo, char[] Password, int type) {
		int x = 0;
		try {
			System.out.println(CelNo);
			String passString = new String(Password);

			// Get connection
			Connection myConn = DriverManager.getConnection(url, user, pass);

			if (type == 1) {
					String sql = "SELECT * FROM uber.user u, uber.passenger p WHERE PCelNo = '" +CelNo + "' " +"AND Password = '" + passString +"' AND p.PCelNo = u.CelNo";
				java.sql.PreparedStatement ps = myConn.prepareStatement(sql);
				ResultSet rs = ps.executeQuery();
				if (rs.next()) {

					System.out.println("Welcome " +rs.getString("FirstName"));
					System.out.println("Passenger");
					x=1;



					loggedin.setsType("Passenger");
					loggedin.setsFirstName(rs.getString("FirstName"));
					loggedin.setsLastName(rs.getString("LastName"));
					loggedin.setsCellNo(rs.getString("CelNo"));
					loggedin.setsPassword(rs.getString("Password"));
					loggedin.setsEmail(rs.getString("Email"));


					System.out.println("First Name: "+loggedin.getsFirstName());
					System.out.println("Last Name: "+loggedin.getsLastName());
					System.out.println("Cell Number: " +loggedin.getsCellNo());
					System.out.println("Password: " +loggedin.getsPassword());



				}
				else
					return x;

			}
			else if (type == 2) {
				String sql = "SELECT * FROM uber.driver d, uber.user u WHERE CelNo = '" +CelNo + "' " +"AND Password = '" + passString +"' AND d.DCellNo = u.CelNo";
				java.sql.PreparedStatement ps = myConn.prepareStatement(sql);
				ResultSet rs = ps.executeQuery();
				if (rs.next()) {

					System.out.println("Welcome " +rs.getString("FirstName"));
					System.out.println("Driver");
					x=1;

					Users u1 = new Users();
					u1.setsCellNo(CelNo);
					u1.setsPassword(passString);
					u1.setsType("Driver");
					loggedin = u1;
				}
				else
					return x;

			}






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




		return x;
	}


	public DefaultTableModel buildTableModel(ResultSet rs)
	        throws SQLException {

	    ResultSetMetaData metaData = rs.getMetaData();

	    // names of columns
	    Vector<String> columnNames = new Vector<String>();
	    int columnCount = metaData.getColumnCount();
	    for (int column = 1; column <= columnCount; column++) {
	        columnNames.add(metaData.getColumnName(column));
	    }

	    // data of the table
	    Vector<Vector<Object>> data = new Vector<Vector<Object>>();
	    while (rs.next()) {
	        Vector<Object> vector = new Vector<Object>();
	        for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
	            vector.add(rs.getObject(columnIndex));
	        }
	        data.add(vector);
	    }

	    return new DefaultTableModel(data, columnNames);

	}

	public Users getLoggedin() {
		return loggedin;
	}

	public void setLoggedin(Users loggedin) {
		this.loggedin = loggedin;
	}




}