package rdms;

import rdms.Extract;
import rdms.Meter;
import rdms.MeterRead;
//import rdms.ServerConnection;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

public class MySQLExtract extends Extract {
	
	private Connection connection;
	//private ServerConnection serverConnection;
	//
	private static String serverName = "griddb1";
	private static String port = "3306";
	private static String username = "testuser1";
	private static String password = "Admin1!!!";
	
	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;
	//
	
	private Meter setMeterResult(ResultSet result) {
		Meter tempMeter = new Meter();
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
			tempMeter.id = result.getLong(1);
			tempMeter.contact_name = result.getString(2);
			tempMeter.email = result.getString(3);
			tempMeter.phone_number = result.getString(4);
			tempMeter.description = result.getString(5);
			tempMeter.address = result.getString(6);
			tempMeter.city = result.getString(7);
			tempMeter.state_province = result.getString(8);
			try {
				tempMeter.last_reading = format.parse(result.getTimestamp(9).toString());
			} catch (ParseException e) {
				System.out.println("Failed to parse time");
				e.printStackTrace();
			}
			tempMeter.latitude = result.getFloat(10);
			tempMeter.longitude = result.getFloat(11);
			return tempMeter;
		} catch(SQLException e) {
			return tempMeter;
		}
	}
	private MeterRead setMeterReadResult(ResultSet result) {
		MeterRead tempMeterRead2 = new MeterRead();
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
			try {
				tempMeterRead2.timestamp = format.parse(result.getTimestamp(2).toString());
			} catch (ParseException e) {
				System.out.println("Failed to parse time");
				e.printStackTrace();
			}
			tempMeterRead2.usage_since_read = result.getDouble(3);
			tempMeterRead2.usage_this_day = result.getDouble(4);
			tempMeterRead2.usage_this_week = result.getDouble(5);
			tempMeterRead2.usage_this_month = result.getDouble(6);
			tempMeterRead2.usage_this_year = result.getDouble(7);
			tempMeterRead2.all_time_usage = result.getDouble(8);
			tempMeterRead2.error_code_1 = result.getLong(9);
			tempMeterRead2.error_code_2 = result.getLong(10);
			return tempMeterRead2;
		} catch(SQLException e) {
			return tempMeterRead2;
		}
	}
	
	@Override
	public void gatherData() {
		Meter met1;
		MeterRead metr1;
		Statement stmt;
		try {
			stmt = connection.createStatement();
			ResultSet result = stmt.executeQuery("SELECT * FROM METERS");
			
			while(result.next()) {
				met1 = new Meter();
				met1 = setMeterResult(result);
				super.meterList.add(met1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		Statement stmt1;
		try {
			stmt1 = connection.createStatement();
			ResultSet result1 = stmt1.executeQuery("SELECT * FROM METER_READS");
			HashSet<MeterRead> tempSet = new HashSet<MeterRead>();
			while(result1.next()) {
				metr1 = setMeterReadResult(result1);
				long tempID = result1.getLong(1);
				super.meterReadList.add(metr1);
				if(super.mappedList.containsKey(tempID) && !super.mappedList.get(tempID).isEmpty()) {
					tempSet = super.mappedList.get(tempID);
					tempSet.add(metr1);
				} else {
					tempSet = new HashSet<MeterRead>();
					tempSet.add(metr1);
				}
				super.mappedList.put(tempID, tempSet);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	public void prepareData() {
		
	}
	/*@Override
	public void printSummaryMonth(int month) {
		//
		//
		System.out.println("---Summary of Database Month " + month + "---");
		System.out.println("Max Usage of ALL database of Month " + month + ": " + super.maxUsageSpecificMonth[(month-1)]);
		System.out.println("Max Usage of Month " + month + " per ID: ");
		//Iterator it = super.usageMaxMonthMap.entrySet().iterator();
		Set<Long> keysID = super.usageMaxMonthMap.keySet();
		Iterator<Long> it2 = keysID.iterator();
		while(it2.hasNext()) {
			long key1 = it2.next();
			Float [] pairV = super.usageMaxMonthMap.get(key1);
			if(pairV[(month-1)] > 0) {
				System.out.println("         " + "Month Max of ID " + key1 + ": " + pairV[(month-1)]);
			}
		}
		
		while(it.hasNext()) {
			Map.Entry pair = (Map.Entry)it.next(); 
			Float [] pairV = (Float[]) pair.getValue();
			System.out.println("         " + "Month Max of ID " + pair.getKey() + ": " + pairV[(month-1)]);
			it.remove();
		}
		
		System.out.println("---End of Summary of Month " + month + "--");
	}*/
	
	@Override
	public float calculateUsageMonth(long id, int monthnum) {
		float result = 0;
		
		String getUsageSum = "SELECT timestamp, SUM(usage_since_read)"
				+ " AS reg_sum FROM METER_READS WHERE MONTH(timestamp) = ?"
				+ " AND meter_id = ?";
		String getUsageMax = "SELECT timestamp, MAX(usage_since_read)"
				+ " AS reg_sum FROM METER_READS WHERE MONTH(timestamp) = ?"
				+ " AND meter_id = ?";
		try {
			PreparedStatement prepMeter = connection.prepareStatement(getUsageSum);
			prepMeter.setInt(1, monthnum);
			prepMeter.setLong(2, 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 testFunction() {
		float test = calculateUsageMonth((long)4,7);
		System.out.println("Sum: " + test);
	}
	//
	//should improve performance on this, if possible
	/*@Override
	public void createSummary() {
		Iterator<Meter> it = super.meterList.iterator();
		//
		while(it.hasNext()) {
			Meter met = new Meter();
			met = it.next();
			HashSet<MeterRead> mrSetTemp = super.mappedList.get(met.id);
			if(super.mappedList.containsKey(met.id) && !super.mappedList.get(met.id).isEmpty()) {
				Iterator<MeterRead> it1 = mrSetTemp.iterator();
				MeterRead maxUsagesMeterRead = new MeterRead();
				
				Calendar cal1 = Calendar.getInstance();
				int curMonth = 0;
				Float[] curMonthMax = new Float[12];
				for(int i = 0; i < 12; i++) {
					curMonthMax[i] = Float.valueOf(((float)(-1.0)));
				}
				while(it1.hasNext()) {
					MeterRead metr1 = new MeterRead();
					metr1 = it1.next();
					if(maxUsagesMeterRead.all_time_usage < metr1.all_time_usage) {
						maxUsagesMeterRead.all_time_usage = metr1.all_time_usage;
					}
					if(maxUsagesMeterRead.usage_this_month < metr1.usage_this_month) {
						maxUsagesMeterRead.usage_this_month = metr1.usage_this_month;
					}
					if(maxUsagesMeterRead.usage_this_week < metr1.usage_this_week) {
						maxUsagesMeterRead.usage_this_week = metr1.usage_this_week;
					}
					if(maxUsagesMeterRead.usage_this_year < metr1.usage_this_year) {
						maxUsagesMeterRead.usage_this_year = metr1.usage_this_year;
					}
					if(maxUsagesMeterRead.usage_this_day < metr1.usage_this_day) {
						maxUsagesMeterRead.usage_this_day = metr1.usage_this_day;
					}
					cal1.setTime(metr1.timestamp);
					curMonth = cal1.get(Calendar.MONTH) - 1;
					//if(curMonth == month) {
						if((float) metr1.usage_this_month > (float) curMonthMax[curMonth]) {
							curMonthMax[curMonth] = Float.valueOf(((float) metr1.usage_this_month));
						}
					//}
				}
				for(int i = 0; i < 12; i++) {
					if(curMonthMax[i] > super.maxUsageSpecificMonth[i]) {
						super.maxUsageSpecificMonth[i] = curMonthMax[i];
					}
				}
				super.usageMaxMonthMap.put(met.id, curMonthMax);
				// add the max usage to the report
				maxUsagesMeterRead.timestamp = met.last_reading;
				super.summaryList.put(met.id, maxUsagesMeterRead);
			}
		}
	}*/
	
	public void connectToServer() {
		System.out.println("Attempting to connect...");
		try {
			try {
				Class.forName("com.mysql.cj.jdbc.Driver");
			} catch (ClassNotFoundException e) {
				System.out.println("Can't find Driver");
				e.printStackTrace();
			}
			
			connection = DriverManager.getConnection(fullURL1, username, password);
			System.out.println("Successfully Connected");
		} catch (SQLException e) {
			System.out.println("Failed Connection");
			e.printStackTrace();
		} 
	}
	public void closeServer() {
		try {
			connection.close();
			System.out.println("Closed connection successfully");
		} catch (SQLException e) {
			System.out.println("Failed to close connection");
			e.printStackTrace();
		}
	}
	public void printData() {
		ArrayList<Meter> meterList = new ArrayList<Meter>();
		ArrayList<MeterRead> meterReadList = new ArrayList<MeterRead>();
		try {
			Statement stmt = connection.createStatement();
			ResultSet result = stmt.executeQuery("SELECT * FROM METERS");
			while(result.next()) {
				Meter tempMeter2 = setMeterResult(result);
				
				meterList.add(tempMeter2);
				//
				printMeterInfo(tempMeter2);
			}
			Statement stmt2 = connection.createStatement();
			ResultSet result2 = stmt2.executeQuery("SELECT * FROM METER_READS");
			while(result2.next()) {
				MeterRead tempMeterRead2 = setMeterReadResult(result2);
				
				meterReadList.add(tempMeterRead2);
				//
				printMeterReadInfo(tempMeterRead2);
			}
		} catch(SQLException e) {
			e.printStackTrace();
		}
	}
}