/*
    * Trip Tracker, a real-time position tracking system for the Internet.
    * Copyright (C) 2006  Team Trip Tracker
    *
    * This program is free software; you can redistribute it and/or modify it
    * under the terms of the GNU General Public License as published by the
    * Free Software Foundation; either version 2 of the License, or (at your
    * option) any later version.
    *
   * This program is distributed in the hope that it will be useful, but
   * WITHOUT ANY WARRANTY; without even the implied warranty of
   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
   * General Public License for more details.
   *
   * You should have received a copy of the GNU General Public License along
   * with this program; if not, write to the Free Software Foundation, Inc.,
   * 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
   */
  
  package triptracker.testing;
  
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  import java.util.ArrayList;
  import java.util.List;
  
  import triptracker.core.Coordinate;
  
  /***
   * Converts from wierd sexagesimal coordinate format to decimal format and fixes
   * the old faulty conversion from NMEA sexagesimal to decimal.
   */
  public class DBConversion {
  	public static void main(String[] args) {
  //		convNmeaToDec();
  		convertNewF(55);
  		markNewF(55);
  	}
  
  	/***
  	 * Connect to the MySQL database.
  	 * 
  	 * @return database Connection object
  	 */
  	private static Connection connect() {
  		Connection link = null;
  		
  		try {
  			// Logging on to the MySQL database server.
  			Class.forName("com.mysql.jdbc.Driver");
  			String url = "jdbc:mysql://127.0.0.1:3306/trip";
  			link = DriverManager.getConnection(url, "trip", "track");
  		} catch (ClassNotFoundException e) {
  			e.printStackTrace();
  		} catch (SQLException e) {
  			e.printStackTrace();
  		}
  		return link;
  	}
  	
  
  	/***
  	 * Simply mark all coordinates of a given route with the 'newf' marker.
  	 */
  	private static void markNewF(final int routeId) {
  		Connection link = connect();
  
  		long time = System.currentTimeMillis();
  		
  		try {
  			Statement stmt = link.createStatement();
  			
  			// Update coordinates with 'newf' = 1.
  			String setQuery = "UPDATE trip_coordinate2 "
  				+ "SET newf = '1' "
  				+ "WHERE rId = '" + routeId + "'";
  			
  			int rows = stmt.executeUpdate(setQuery);
  			System.out.println("Update affected " + rows + " row(s).");
  			
  			stmt.close();
  			link.close();
  		} catch (SQLException e) {
  			e.printStackTrace();
  		}
  		
  		long curTime = System.currentTimeMillis();
  		System.out.println("Duration: " + (curTime - time) + " ms");
  	}
  
  	/***
  	 * Converts coordinates for a given route from old NMEA sexagesimal format
  	 * to new decimal format.
  	 */
  	public static void convertNewF(final int routeId) {
  		Connection link = connect();
 		int cId;
 		double lat, lng;
 		List<Coordinate> coords = new ArrayList<Coordinate>();
 
 		long time = System.currentTimeMillis();
 		
 		try {
 			Statement stmt = link.createStatement();
 
 			String getQuery = "SELECT cId, latitude, longitude "
 				+ "FROM trip_coordinate2 "
 				+ "WHERE rId = '" + routeId + "' AND newf = '0'";
 			
 			ResultSet rs = stmt.executeQuery(getQuery);
 			
 			// Add all coords we want to change to list.
 			while (rs.next()) {
 				cId = rs.getInt(1);
 				lat = rs.getDouble(2);
 				lng = rs.getDouble(3);
 				
 				// FIXME Create conversion from old erroneous decimal to new
 				// correct decimal format.
 				
 				// Convert to new decimal format.
 				lat = Coordinate.nmeaToDec(lat);
 				lng = Coordinate.nmeaToDec(lng);
 				
 				// Add the coord to the conversion queue.
 				coords.add(new Coordinate(cId, lat, lng));
 			}
 			
 			System.out.println("Updating " + coords.size() + " records...");
 			
 			// Run update on the queue.
 			for (Coordinate coord : coords) {
 				String setQuery = "UPDATE trip_coordinate2 "
 					+ "SET latitude = '" + coord.getY()
 					+ "', longitude = '" + coord.getX()
 					+ "', newf = '1' "
 					+ "WHERE cId = '" + coord.getCoordId() + "'";
 				
 				stmt.executeUpdate(setQuery);
 			}
 			
 			stmt.close();
 			link.close();
 		} catch (SQLException e) {
 			e.printStackTrace();
 		}
 		
 		long curTime = System.currentTimeMillis();
 		System.out.println("Duration: " + (curTime - time) + " ms");
 	}
 
 	/***
 	 * Converts from old NMEA sexagesimal format to new decimal format.
 	 */
 	public static void convNmeaToDec() {
 		Connection link = connect();
 		int cId;
 		double lat, lng;
 		boolean convert = false;
 		List<Coordinate> coords = new ArrayList<Coordinate>();
 
 		long time = System.currentTimeMillis();
 		
 		try {
 			Statement stmt = link.createStatement();
 
 			String getQuery = "SELECT cId, latitude, longitude "
 				+ "FROM trip_coordinate";
 			
 			ResultSet rs = stmt.executeQuery(getQuery);
 			
 			// Add all coords we want to change to list.
 			while (rs.next()) {
 				convert = false;
 				cId = rs.getInt(1);
 				lat = rs.getDouble(2);
 				lng = rs.getDouble(3);
 				
 				// Reverse direction on old coords.
 				if (lat < -5000) {
 					convert = true;
 					lat = -lat;
 				}
 				
 				if (lng < -500) { 
 					convert = true;
 					lng = -lng;
 				}
 				
 				// Convert to new decimal format.
 				if (lat > 5000) {
 					convert = true;
 					lat = Coordinate.nmeaToDec(lat);
 				}
 				
 				if (lng > 500) {
 					convert = true;
 					lng = Coordinate.nmeaToDec(lng);
 				}
 				
 				// Add the coord to the conversion queue.
 				if (convert) {
 					coords.add(new Coordinate(cId, lat, lng));
 				}
 			}
 			
 			System.out.println("Updating " + coords.size() + " records...");
 			
 			// Run update on the queue.
 			for (Coordinate coord : coords) {
 				String setQuery = "UPDATE trip_coordinate2 "
 					+ "SET latitude = '" + coord.getY()
 					+ "', longitude = '" + coord.getX()
 					+ "', newf = '1' "
 					+ "WHERE cId = '" + coord.getCoordId() + "'";
 				
 				stmt.executeUpdate(setQuery);
 			}
 			
 			stmt.close();
 			link.close();
 		} catch (SQLException e) {
 			e.printStackTrace();
 		}
 		
 		long curTime = System.currentTimeMillis();
 		System.out.println("Duration: " + (curTime - time) + " ms");
 	}
 
 }