import java.sql.*;
import java.util.Scanner;


public class CollisionAvoider {

	static final String JDBC_DRIVER = "org.mariadb.jdbc.Driver";
	static final String DB_URL = "jdbc:mariadb://localhost/mining";

	//  Database credentials
	static final String USER = "root";
	static final String PASS = "123456";
	private static Scanner reader;
	public static Connection conn;

	private double distance(double lat1, double lon1, double lat2, double lon2, String unit) {
		double theta = lon1 - lon2;
		double dist = Math.sin(deg2rad(lat1)) * Math.sin(deg2rad(lat2)) + Math.cos(deg2rad(lat1)) * Math.cos(deg2rad(lat2)) * Math.cos(deg2rad(theta));
		dist = Math.acos(dist);
		dist = rad2deg(dist);
		dist = dist * 60 * 1.1515;
		if (unit == "K") {
			dist = dist * 1.609344;
		} else if (unit == "N") {
			dist = dist * 0.8684;
		}

		return (dist);
	}

	/*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
	/*::	This function converts decimal degrees to radians						 :*/
	/*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
	private double deg2rad(double deg) {
		return (deg * Math.PI / 180.0);
	}

	/*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
	/*::	This function converts radians to decimal degrees						 :*/
	/*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
	private double rad2deg(double rad) {
		return (rad * 180 / Math.PI);
	}

	public void makeConnection() throws ClassNotFoundException, SQLException
	{
        Class.forName(JDBC_DRIVER);
        System.out.println("Connecting to a selected database...");
        conn = DriverManager.getConnection(
        		DB_URL, USER, PASS);
        System.out.println("Connected database successfully...");
	}

	public double fahrenheitToCelcius(double fahrenheit)
	{
		//(°F  -  32)  x  5/9 = °C
		return (fahrenheit - 32) * 5.0/9.0;
	}

	public boolean checkBadWeather(double latitude, double longitude, String day, String month, String year) throws SQLException
	{
		String query = "SELECT * FROM gsod g, stations s WHERE (g.temp < 32  OR g.fog = 1 OR g.rain_drizzle = 1 OR g.snow_ice_pellets = 1 OR g.hail =1 OR g.thunder = 1 OR g.tornado_funnel_cloud = 1) AND g.year = '" + year + "' AND g.mo = '" + month + "' AND g.da = '" +
						day + "' AND s.usaf = g.stn AND s.wban = g.wban";

		// create the java statement
		Statement st = conn.createStatement();

		// execute the query, and get a java resultset
		ResultSet rs = st.executeQuery(query);
		// iterate through the java resultset
		while (rs.next())
		{
			double distanceBetween = distance(rs.getFloat("s.lat"), rs.getFloat("s.lon"), latitude, longitude, "K");
			if (distanceBetween < 10)
			{
				return true;
			}
		}
		return false;
	}

	public boolean checkBadWeather(String street, String day, String month, String year) throws SQLException
	{
		String query = "SELECT * FROM gsod g, stations s, nypd_mv_collisions m WHERE (g.temp < 32  OR g.fog = 1 OR g.rain_drizzle = 1 OR g.snow_ice_pellets = 1 OR g.hail = 1 OR g.thunder = 1 OR g.tornado_funnel_cloud = 1) AND g.year = '" + year + "' AND g.mo = '" + month + "' AND g.da = '" +
						day + "' AND s.usaf = g.stn AND s.wban = g.wban AND (m.on_street_name LIKE '" + street + "' OR m.off_street_name LIKE '" + street + "' OR m.cross_street_name LIKE '" + street + "')";

		// create the java statement
		Statement st = conn.createStatement();

		// execute the query, and get a java resultset
		ResultSet rs = st.executeQuery(query);
		// iterate through the java resultset
		while (rs.next())
		{
			double distanceBetween = distance(rs.getFloat("s.lat"), rs.getFloat("s.lon"), rs.getFloat("m.latitude"), rs.getFloat("m.longitude"), "K");
	    	if (distanceBetween < 10)
			{
				return true;
			}
		}
		return false;
	}

	public double averageCrashesPerDay(String street) throws SQLException
	{
		String query = "SELECT (count(*) / 365) as crash_count FROM nypd_mv_collisions m WHERE (m.on_street_name LIKE '" + street + "' OR m.off_street_name LIKE '" + street + "' OR m.cross_street_name LIKE '" + street + "')";

		// create the java statement
		Statement st = conn.createStatement();

		// execute the query, and get a java resultset
		ResultSet rs = st.executeQuery(query);
		rs.first();
		return rs.getDouble("crash_count");
	}

	public int numberOfCrashes(String street, String date) throws SQLException
	{
		String query = "SELECT count(*) as crash_count FROM nypd_mv_collisions m WHERE (m.on_street_name LIKE '" + street + "' OR m.off_street_name LIKE '" + street + "' OR m.cross_street_name LIKE '" + street + "') AND DATE(m.timestamp)  = STR_TO_DATE('" + date + "', '%Y-%m-%d')";

		// create the java statement
		Statement st = conn.createStatement();

		// execute the query, and get a java resultset
		ResultSet rs = st.executeQuery(query);
		rs.first();
		return rs.getInt("crash_count");
	}

	public double increase(double first, double second)
	{
		double ratio = first / second;
		return ratio * 100.0;
	}

	public double increaseInRisk(double riskIncrease1, double riskIncrease2, double crashIncreaseCount1, double crashIncreaseCount2)
	{
		if (riskIncrease1 > riskIncrease2 && crashIncreaseCount1 > crashIncreaseCount2)
		{
			return increase(riskIncrease1, riskIncrease2);
		}
		else
		{
			return 0;
		}
	}

	public double compareTwoStreets(String street1, String street2, String date) throws SQLException
	{
		double average1 = averageCrashesPerDay(street1);
		double average2 = averageCrashesPerDay(street2);
		double number1 = numberOfCrashes(street1, date);
		double number2 = numberOfCrashes(street2, date);
		double riskInrease1 = increase(number1, average1);
		double riskInrease2 = increase(number2, average2);
		return increaseInRisk(riskInrease1, riskInrease2, number1 - average1, number2 - average2);
	}
}