package rdms;

import rdms.Ingest;
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.SQLIntegrityConstraintViolationException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Random;

public class MySQLIngest extends Ingest {
	private Connection connection;
	//
	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;
	//
	
	public MySQLIngest() {
		super();
	}
	public MySQLIngest(Meter m, MeterRead mr) {
		super(m,mr);
	}
	
	public MySQLIngest(double d, double w, double m, double y, double a) {
		super();
	}
	
	private Timestamp convertDateToTimestamp(Date d) {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		GregorianCalendar calendar = new GregorianCalendar();
		
		calendar.setTime(d);
		return java.sql.Timestamp.valueOf(format.format(calendar.getTime()));
	}
	private Date convertTimestampToDate(Timestamp t) {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		GregorianCalendar calendar = new GregorianCalendar();
		try {
			calendar.setTime(format.parse(t.toString()));
		} catch (ParseException e) {
			System.out.println("Error parsing date");
			e.printStackTrace();
		}
		return calendar.getTime();
	}
	
	public void setMeter(Meter m) {
		super.meter = m;
	}
	
	public void setMeterRead(MeterRead mr) {
		super.meterRead = mr;
		
	}
	private boolean writeMeterRead(String queryMeterReads, Calendar cal) {
		try {
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			PreparedStatement prepMeterReads = connection.prepareStatement(queryMeterReads);
			try {
				int count = 0;
				prepMeterReads.setLong(++count, super.meter.id);
				prepMeterReads.setTimestamp(++count, java.sql.Timestamp.valueOf(format.format(cal.getTime())));
				prepMeterReads.setDouble(++count, super.meterRead.usage_since_read);
				prepMeterReads.setDouble(++count, super.meterRead.usage_this_hour);
				prepMeterReads.setDouble(++count, super.meterRead.usage_this_day);
				prepMeterReads.setDouble(++count, super.meterRead.usage_this_week);
				prepMeterReads.setDouble(++count, super.meterRead.usage_this_month);
				prepMeterReads.setDouble(++count, super.meterRead.usage_this_year);
				prepMeterReads.setDouble(++count, super.meterRead.all_time_usage);
				prepMeterReads.setLong(++count, super.meterRead.error_code_1);
				prepMeterReads.setLong(++count, super.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;
		}
	}
	private boolean writeMeter(String queryMeter2, Calendar cal1) {
		try {
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			PreparedStatement prepMeter1 = connection.prepareStatement(queryMeter2);
			try {
				int count = 0;
				prepMeter1.setLong(++count, super.meter.id);
				prepMeter1.setString(++count, super.meter.contact_name);
				prepMeter1.setString(++count, super.meter.email);
				prepMeter1.setString(++count, super.meter.phone_number);
				prepMeter1.setString(++count, super.meter.description);
				prepMeter1.setString(++count, super.meter.address);
				prepMeter1.setString(++count, super.meter.city);
				prepMeter1.setString(++count, super.meter.state_province);
				prepMeter1.setTimestamp(++count, java.sql.Timestamp.valueOf(format.format(cal1.getTime())));
				prepMeter1.setFloat(++count, super.meter.latitude);
				prepMeter1.setFloat(++count, super.meter.longitude);
				prepMeter1.setLong(++count, super.meter.id);
				
				prepMeter1.executeUpdate();
				//
				prepMeter1.close();
				return true;
			} finally {
				if(prepMeter1 != null) try {prepMeter1.close(); } catch (SQLException ignore) {}
			}
			//
		} catch (SQLException e) {
			System.out.println("Failed to update Meter");
			return false;
		}
	}
	
	@Override
	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();
		}
	}
	
	@Override
	public void connectToServer(Connection s) {
		super.isSet = true;
		connection = s;
	}
	@Override
	public void closeServer() {
		try {
			connection.close();
			System.out.println("Closed connection successfully");
		} catch (SQLException e) {
			System.out.println("Failed to close connection");
			e.printStackTrace();
		}
	}
	
	//@Override
	public void setMeterReadFromDB(Timestamp key) {
		String sql = (" SELECT * FROM METER_READS WHERE timestamp=?");
		try {
			PreparedStatement st = connection.prepareStatement(sql);
			st.setTimestamp(1, key);
			ResultSet rs = st.executeQuery();
			
			Date tempDate = new Date();
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			GregorianCalendar calendar = new GregorianCalendar();
			int lnum = 0;
			if(rs.next() && lnum == 0) {
				try {
					try {
						calendar.setTime(format.parse(rs.getTimestamp(1).toString()));
					} catch (ParseException e) {
						System.out.println("Failed to parse date");
						e.printStackTrace();
					}
					tempDate = calendar.getTime();
					super.meterRead.timestamp = tempDate;
					super.meterRead.usage_since_read = rs.getDouble(2);
					super.meterRead.usage_this_day = rs.getDouble(3);
					super.meterRead.usage_this_week = rs.getDouble(4);
					super.meterRead.usage_this_month = rs.getDouble(5);
					super.meterRead.usage_this_year = rs.getDouble(6);
					super.meterRead.all_time_usage = rs.getDouble(7);
					super.meterRead.error_code_1 = rs.getLong(8);
					super.meterRead.error_code_2 = rs.getLong(9);
					lnum++;
					rs.close();
					st.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					if(rs != null) try {rs.close(); } catch (SQLException ignore) {}
					if(st != null) try {st.close(); } catch (SQLException ignore) {}
					System.out.println("Failed to extract MeterRead");
					e.printStackTrace();
				}
			}
		} catch (SQLException e) {
			System.out.println("ResultSet is empty");
			e.printStackTrace();
		}
	}
	
	//@Override
	public MeterRead getMeterReadFromDB(Timestamp key) {
		// Basically the same as setMeterReadFromDB, but it doesn't update meterRead, it returns a seperate MeterRead
		MeterRead meterRead1 = new MeterRead();
		String sql = (" SELECT * FROM METER_READS WHERE timestamp=?");
		try {
			PreparedStatement st = connection.prepareStatement(sql);
			st.setTimestamp(1, key);
			ResultSet rs = st.executeQuery();
			
			Date tempDate = new Date();
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			GregorianCalendar calendar = new GregorianCalendar();
			if(rs.next()) {
				int count = 1;
				try {
					try {
						calendar.setTime(format.parse(rs.getTimestamp(++count).toString()));
					} catch (ParseException e) {
						System.out.println("Failed to parse date");
						e.printStackTrace();
					}
					tempDate = calendar.getTime();
					meterRead1.timestamp = tempDate;
					meterRead1.usage_since_read = rs.getDouble(++count);
					meterRead1.usage_this_hour = rs.getDouble(++count);
					meterRead1.usage_this_day = rs.getDouble(++count);
					meterRead1.usage_this_week = rs.getDouble(++count);
					meterRead1.usage_this_month = rs.getDouble(++count);
					meterRead1.usage_this_year = rs.getDouble(++count);
					meterRead1.all_time_usage = rs.getDouble(++count);
					meterRead1.error_code_1 = rs.getLong(++count);
					meterRead1.error_code_2 = rs.getLong(++count);
					
					rs.close();
					st.close();
				} catch (SQLException e) {
					if(rs != null) try {rs.close(); } catch (SQLException ignore) {}
					if(st != null) try {st.close(); } catch (SQLException ignore) {}
					System.out.println("Failed to extract MeterRead");
					e.printStackTrace();
				}
			}
		} catch (SQLException e) {
			System.out.println("ResultSet is empty");
			e.printStackTrace();
		}
		return meterRead1;
	}
	
	@Override
	public Meter getMeterFromDB(long key) {
		Meter meter1 = new Meter();
		
		String sql = (" SELECT * FROM METERS WHERE id=?");
		try {
			PreparedStatement st = connection.prepareStatement(sql);
			st.setLong(1, key);
			ResultSet rs = st.executeQuery();
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			GregorianCalendar cal1 = new GregorianCalendar();
			if(rs.next()) {
				try {
					meter1.id = rs.getLong(1);
					meter1.contact_name = rs.getString(2);
					meter1.email = rs.getString(3);
					meter1.phone_number = rs.getString(4);
					meter1.description = rs.getString(5);
					meter1.address = rs.getString(6);
					meter1.city = rs.getString(7);
					meter1.state_province = rs.getString(8);
					try {
						cal1.setTime(format.parse(rs.getTimestamp(9).toString()));
					} catch (ParseException e) {
						System.out.println("Failed to parse date");
						e.printStackTrace();
					}
					meter1.last_reading = cal1.getTime();
					meter1.latitude = rs.getFloat(10);
					meter1.longitude = rs.getFloat(11);
					
					rs.close();
					st.close();
				} catch (SQLException e) {
					if(rs != null) try {rs.close(); } catch (SQLException ignore) {}
					if(st != null) try {st.close(); } catch (SQLException ignore) {}
					System.out.println("Failed to extract Meter");
					e.printStackTrace();
				}
			}
		} catch (SQLException e) {
			System.out.println("ResultSet is empty");
			e.printStackTrace();
		}
		return meter1;
	}
	
	@Override
	public boolean checkMeterExistsDB(long key) {
		String sql = ("SELECT 1 FROM METERS WHERE id=?");
		try {
			PreparedStatement st = connection.prepareStatement(sql);
			try {
				st.setLong(1, key);
				ResultSet rs = st.executeQuery();
				
				try {	
					if(rs.getLong(1) == 1) {
						
						rs.close();
						st.close();
						return true;
					}
					boolean b = rs.next();
					rs.close();
					st.close();
					return b;
				} finally {
					if(rs != null) try {rs.close(); } catch (SQLException ignore) {}
				}
			} finally {
				if(st != null) try {st.close(); } catch (SQLException ignore) {}
			}
			//return true;
		} catch(SQLException e) {
			return false;
		} 
		//return true;
	}
	
	//@Override
	public boolean checkMeterReadExistsDB(Timestamp key) {
		String sql = (" SELECT * FROM METER_READS WHERE timestamp=?");
		try {
			PreparedStatement st = connection.prepareStatement(sql);
			try {
				st.setTimestamp(1, key);
				ResultSet rs = st.executeQuery();
				try {
					boolean b = rs.next();
					st.close();
					rs.close();
					return b;
				} finally {
					if(rs != null) try {rs.close(); } catch (SQLException ignore) {}
				}
			} finally {
				if(st != null) try {st.close(); } catch (SQLException ignore) {}
			}
		} catch(SQLException e) {
			//System.out.println("ResultSet is empty");
			//e.printStackTrace();
		}
		return false;
	}
	@Override
	public void writeToDatabase() {
		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 queryMeterReads2 = " insert into METER_READS (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, meter_id) "
				+ "values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) "
				+ "select id, ? "
				+ "from METERS "
				+ "where id = ?";
		
		String queryMeter = " INSERT INTO METERS (id, contact_name, email, number, "
				+ "description, address, city, state_province, last_reading, "
				+ "latitude, longitude) "
				+ "values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		
		String queryMeter2 = " UPDATE METERS SET id=?, contact_name=?, email=?, number=?, "
				+ "description=?, address=?, city=?, state_province=?, last_reading=?, "
				+ "latitude=?, longitude=? WHERE "
				+ "id=?";
		
		Calendar cal = new GregorianCalendar();
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		if(meterRead != null && meterRead.timestamp != null) {
			cal.setTime(meterRead.timestamp);
		}
		boolean b = false;
		Calendar cal1 = new GregorianCalendar();
		if(super.meter != null && super.meter.last_reading != null) {
			cal1.setTime(super.meter.last_reading);
		}
		boolean metEx = checkMeterExistsDB(super.meter.id);
		if(metEx || checkMeterExistsDB(super.meter.id)) {
			b = writeMeter(queryMeter2, cal);
		}
		boolean bs = false;
		boolean mExists = false;
		if(b == false && (!checkMeterExistsDB(super.meter.id)) && super.meter != null) {
			if(!metEx) {
				try {
					//System.out.println("MetEX: " + metEx  );
					PreparedStatement prepMeter = connection.prepareStatement(queryMeter);
					try {
						prepMeter.setLong(1, super.meter.id);
						prepMeter.setString(2, super.meter.contact_name);
						prepMeter.setString(3, super.meter.email);
						prepMeter.setString(4, super.meter.phone_number);
						prepMeter.setString(5, super.meter.description);
						prepMeter.setString(6, super.meter.address);
						prepMeter.setString(7, super.meter.city);
						prepMeter.setString(8, super.meter.state_province);
						prepMeter.setTimestamp(9, java.sql.Timestamp.valueOf(format.format(cal1.getTime())));
						prepMeter.setFloat(10, super.meter.latitude);
						prepMeter.setFloat(11, super.meter.longitude);
						
						if(!checkMeterExistsDB(super.meter.id)) {
							try {
							//System.out.println("Going in : " + (!checkMeterExistsDB(super.meter.id)));
								prepMeter.execute();
								prepMeter.close();
								mExists = true;
							} catch(SQLException e) {
								if(e instanceof SQLIntegrityConstraintViolationException) {
									b = true;
									b = writeMeter(queryMeter2, cal);
									mExists = true;
								} else {
									
								}
							}
						} else {
							prepMeter.close();
							b = true;
							b = writeMeter(queryMeter2,cal);
							mExists = true;
						}
					} finally {
						prepMeter.close();
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					System.out.println("Failed to write Meter");
					e.printStackTrace();
				}
			}
		}
		boolean b1 = false;
		//if(!bs && super.meterRead != null && (b || metEx || checkMeterExistsDB(super.meter.id))) {
		if(!bs && mExists && super.meterRead != null)
			b1 = writeMeterRead(queryMeterReads, cal);
		//}
		
		
		
	}
}