package MeterBenchmark;
//import MeterBenchmark.Client;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import java.text.SimpleDateFormat;
import java.util.Date;

class MySQLClient extends Client {
	
	private Connection connection;
	//
	private static String serverName = "griddb1"; //
	private static String port = "3306";
	private static String username = "testuser1"; //
	private static String password = "testpw";
	private static String db = "testdb";
	private static String url1 = "jdbc:mysql://" + serverName + ":" + port + "/";
	private static String timezoneStuff = "?useUnicode&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";
	private static String fullURL1 = url1 + db + timezoneStuff;
	//
	
	public MySQLClient() {
		try {
			try {
				Class.forName("com.mysql.cj.jdbc.Driver");
			} catch (ClassNotFoundException e) {
				System.out.println("Can't find Driver");
				e.printStackTrace();
				System.exit(0);
			}
			
			connection = DriverManager.getConnection(fullURL1, username, password);
			System.out.println("Successfully Connected to MySQL server");
		} catch (SQLException e) {
			System.out.println("Failed Connection");
			e.printStackTrace();
			System.exit(0);
		}
	}
	private boolean writeMeterRead(String queryMeterReads, Date cal, MeterRead meterRead, int meter_id) {
		try {
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			PreparedStatement prepMeterReads = connection.prepareStatement(queryMeterReads);
			try {
				int count = 0;
				prepMeterReads.setLong(++count, meter_id);
				prepMeterReads.setTimestamp(++count, java.sql.Timestamp.valueOf(format.format(cal.getTime())));
				prepMeterReads.setDouble(++count, meterRead.usage_since_read);
				prepMeterReads.setDouble(++count, meterRead.usage_this_hour);
				prepMeterReads.setDouble(++count, meterRead.usage_this_day);
				prepMeterReads.setDouble(++count, meterRead.usage_this_week);
				prepMeterReads.setDouble(++count, meterRead.usage_this_month);
				prepMeterReads.setDouble(++count, meterRead.usage_this_year);
				prepMeterReads.setDouble(++count, meterRead.all_time_usage);
				prepMeterReads.setLong(++count, meterRead.error_code_1);
				prepMeterReads.setLong(++count, meterRead.error_code_2);
				
				
				prepMeterReads.execute();
				prepMeterReads.close();
				return true;
			} finally {
				if(prepMeterReads != null) try {prepMeterReads.close(); } catch (SQLException ignore) {}
			}
		} catch (SQLException e3) {
			System.out.println("Failed to write MeterRead");
			e3.printStackTrace();
			return false;
		}
	}
	public void writeUsage(Date timestamp, double usage_since_read, double usage_this_hour, double usage_this_day, double usage_this_week, double usage_this_month, double usage_this_year, double all_time_usage, int meter_id) {
		Meter m = new Meter();
		MeterRead mr = new MeterRead();
		mr.timestamp = timestamp;
		mr.usage_since_read = usage_since_read;
		mr.usage_this_hour = usage_this_hour;
		mr.usage_this_day = usage_this_day;
		mr.usage_this_week = usage_this_week;
		mr.usage_this_month = usage_this_month;
		mr.usage_this_year = usage_this_year;
		mr.all_time_usage = all_time_usage;
		m.id = meter_id;
		m.last_reading = timestamp;
		// Made up data
		m.address = "123 street";
		m.city = "fakecity";
		m.contact_name = "John Smith";
		m.description = "Some description";
		m.email = "fakeEmail@fake.com";
		m.latitude = (float) 1.253;
		m.longitude = (float) 5.234;
		m.phone_number = "1-123-456-7890";
		m.state_province = "CA";
		mr.error_code_1 = 12345;
		mr.error_code_2 = 67890;
		//
		
		String queryMeterReads = " insert into METER_READS (meter_id, timestamp, usage_since_read, "
				+ "usage_this_hour, usage_this_day, usage_this_week, usage_this_month, usage_this_year, "
				+ "all_time_usage, error_code_1, error_code_2) "
				+ "values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
		String queryMeterInsert = " INSERT INTO METERS (id, contact_name, email, number, "
				+ "description, address, city, state_province, last_reading, "
				+ "latitude, longitude) "
				+ "values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		String queryMeterUpdate = " UPDATE METERS SET id=?, contact_name=?, email=?, number=?, "
				+ "description=?, address=?, city=?, state_province=?, last_reading=?, "
				+ "latitude=?, longitude=? WHERE "
				+ "id=?";
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date cal1 = new Date();
		cal1 = new Date(timestamp.getTime());
		boolean b = true;
		boolean b1 = true;
		try {
			PreparedStatement prepMeter = connection.prepareStatement(queryMeterInsert);
			try {
				prepMeter.setLong(1, meter_id);
				prepMeter.setString(2, m.contact_name);
				prepMeter.setString(3, m.email);
				prepMeter.setString(4, m.phone_number);
				prepMeter.setString(5, m.description);
				prepMeter.setString(6, m.address);
				prepMeter.setString(7, m.city);
				prepMeter.setFloat(10, m.latitude);
				prepMeter.setString(8, m.state_province);
				prepMeter.setTimestamp(9, java.sql.Timestamp.valueOf(format.format(cal1.getTime())));
				prepMeter.setFloat(11, m.longitude);
				//prepMeter.setLong(12, meter_id);
				
				prepMeter.execute();
				prepMeter.close();
			}
			catch(SQLException e1) {
				b = false;
				if(e1 instanceof SQLIntegrityConstraintViolationException) {
					PreparedStatement prepMeter1 = connection.prepareStatement(queryMeterUpdate);
					try {
						prepMeter1.setLong(1, meter_id);
						prepMeter1.setString(2, m.contact_name);
						prepMeter1.setString(3, m.email);
						prepMeter1.setString(4, m.phone_number);
						prepMeter1.setString(5, m.description);
						prepMeter1.setString(6, m.address);
						prepMeter1.setString(7, m.city);
						prepMeter1.setString(8, m.state_province);
						prepMeter1.setTimestamp(9, java.sql.Timestamp.valueOf(format.format(cal1.getTime())));
						prepMeter1.setFloat(10, m.latitude);
						prepMeter1.setFloat(11, m.longitude);
						prepMeter1.setLong(12, meter_id);
						
						prepMeter1.execute();
						prepMeter1.close();
						b1 = true;
					}
					catch(SQLException e2) {
						b1 = false;
						e2.printStackTrace();
					}
				}
			}
		} catch (Exception e) {
			b = false;
			b1 = false;
			e.printStackTrace();
		}
		
		if(b == true || b1 == true) {
			this.writeMeterRead(queryMeterReads, cal1, mr, meter_id);
		} else {
			System.out.println("Failed to write to MeterRead, failed to write to Meter");
		}
	}
	public double getUsage(int id, Date start, Date end) {
		double result = 0.0;
		String getUsageSum = "SELECT timestamp, SUM(usage_since_read)"
				+ " AS reg_sum FROM METER_READS"
				+ " WHERE meter_id = ?"
				+ " AND timestamp >= ?"
				+ " AND timestamp <= ?";
		System.out.println(getUsageSum);
		try {
			//SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			//Date calStart = new Date();
			//Date calEnd = new Date();
			//calStart = start;
			//calEnd = end;
			PreparedStatement prepMeter = connection.prepareStatement(getUsageSum);
			prepMeter.setLong(1, id);
			prepMeter.setObject(2, new java.sql.Timestamp(start.getTime()));
			prepMeter.setObject(3, new java.sql.Timestamp(end.getTime()));
			
			ResultSet rs = prepMeter.executeQuery();
			rs.next();
			result = rs.getFloat(2);
			return result;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return result;
	}
	public float monthlyUsage(long id, int year, int month) {
		float result = 0;
		
		String getUsageSum = "SELECT timestamp, SUM(usage_since_read)"
				+ " AS reg_sum FROM METER_READS WHERE MONTH(timestamp) = ?"
				+ " AND YEAR(timestamp) = ?"
				+ " AND meter_id = ?";
		//String getUsageMax = "SELECT timestamp, MAX(usage_since_read)"
			//	+ " AS reg_sum FROM METER_READS WHERE MONTH(timestamp) = ?"
			//	+ " AND YEAR(timestamp) = ?"
			//	+ " AND meter_id = ?";
		try {
			PreparedStatement prepMeter = connection.prepareStatement(getUsageSum);
			prepMeter.setInt(1, month);
			prepMeter.setInt(2, year);
			prepMeter.setLong(3, id);
			
			ResultSet rs = prepMeter.executeQuery();
			rs.next();
			result = rs.getFloat(2);
			//
			rs.close();
			prepMeter.close();
			//
			return result;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return result;
	}

	@Override
	public void finalize() {
		try {
			connection.close();
			System.out.println("Closed connection successfully");
		} catch (SQLException e) {
			System.out.println("Failed to close connection");
			e.printStackTrace();
		}
	}
}
