/*
    * 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.server.persistence;
  
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  import java.sql.Timestamp;
  import java.util.ArrayList;
  import java.util.List;
  
  import triptracker.core.Coordinate;
  import triptracker.core.Route;
  import triptracker.core.User;
  import triptracker.core.Utils;
  
  /***
   * Implementation of the persistence layer in the form of a MySQL database.
   */
  public class MySqlDB extends PersistenceAdapter {
  	public static final String server = Properties.getString("MySqlDB.server");
  	public static final String database
  			= Properties.getString("MySqlDB.database");
  	
  	/***
  	 * Connect to the MySQL database.
  	 * 
  	 * @return database Connection object
  	 */
  	private Connection connect() {
  		Connection link = null;
  		
  		try {
  			// Logging on to the MySQL database server.
  			Class.forName(Properties.getString("MySqlDB.driver"));
  			String url = "jdbc:mysql://" + server + "/" + database;
  			link = DriverManager.getConnection(url,
  					Properties.getString("MySqlDB.username"),
  					Properties.getString("MySqlDB.password"));
  		} catch (ClassNotFoundException e) {
  			e.printStackTrace();
  		} catch (SQLException e) {
  			e.printStackTrace();
  		}
  		return link;
  	}
  
  	/***
  	 * {@inheritDoc}
  	 */
  	@Override
  	public int getUserId(String username){
  		Connection link = connect();
  		int userId = -1;
  		try {
  			String query = "SELECT uId FROM trip_user "
  				+ "WHERE user = '" + username + "'";
  			
  			Statement stmt = link.createStatement();
  			ResultSet rs = stmt.executeQuery(query);
  	
  			if (rs.next()) {
  				userId = rs.getInt(1);
  			}
  			
  			dbLog("UserId for user: " + username + " = " + userId );
  			stmt.close();
  			link.close();
  			return userId;
  		} catch (SQLException e) {
  			dbLog("SQL exception at getUserId: " + e);
  			e.printStackTrace();
  			return userId;
  		}
  	}
  	
  	/***
  	 * {@inheritDoc}
  	 */
  	@Override
 	public int authUser(String user, String pass) {
 		Connection link = connect();
 		String dbPass = "";
 		int userId = -1;
 		
 		try {
 			String query = "SELECT pass, uId FROM trip_user "
 					+ "WHERE user = '" + user + "'";
 
 			Statement stmt = link.createStatement();
 			ResultSet rs = stmt.executeQuery(query);
 
 			if (rs.next()) {
 				dbPass = rs.getString(1);
 				if (pass.equals(dbPass)) {
 					userId = rs.getInt(2);
 				}
 			}
 			dbLog("Checked user: " + user + ", userId: " + userId);
 			stmt.close();
 			link.close();
 		} catch (SQLException e) {
 			dbLog("SQL exception " + e + "\n");
 			e.printStackTrace();
 		}
 		return userId;
 	}
 
 	/***
 	 * {@inheritDoc}
 	 */
 	@Override
 	public void storeCoordinate(int routeID, Coordinate coord) {
 		Connection link = connect();
 
 		try {
 			String query = "INSERT INTO trip_coordinate "
 				+ " (rId, latitude, longitude, date) "
 				+ "VALUES('" + routeID  + "', '"
 				+ coord.getY() + "', '"
 				+ coord.getX() + "','"
 				+ coord.getDateString() + "') ";
 
 			Statement stmt = link.createStatement();
 			stmt.executeUpdate(query);
 			
 			// TODO Provide some kind of feedback to client if save failed.
 			
 			dbLog("Saved: Latitude: " + coord.getY() + 
 					" Longitude: " + coord.getX() + " Tidspunkt: "
 					+ coord.getDateString());
 			stmt.close();
 			link.close();
 		} catch (SQLException e) {
 			dbLog("SQL exception at storeCoordinate: " + e);
 			e.printStackTrace();
 		}
 	}
 
 	/***
 	 * {@inheritDoc}
 	 */
 	@Override
 	public void storeCoordinate(int routeID, List<Coordinate> coords) {
 		Connection link = connect();	
 		try {
 			Statement stmt = link.createStatement();
 			for(Coordinate coord : coords){			
 					String query = "INSERT INTO trip_coordinate "
 						+ " (rId, latitude, longitude, date) "
 						+ "VALUES('" + routeID  + "', '"
 						+ coord.getY() + "', '"
 						+ coord.getX() + "','"
 						+ coord.getDateString() + "') ";
 
 					stmt.executeUpdate(query);
 					
 					// TODO Provide some kind of feedback to client if save failed.
 			}
 			dbLog("Saved " + coords.size() + " buffered coordnates to route nr: " + routeID);
 			stmt.close();
 			link.close();
 		} catch (SQLException e) {
 			dbLog("SQL exception at storeCoordinate: " + e);
 			e.printStackTrace();
 		}
 	}
 	
 	/***
 	 * {@inheritDoc}
 	 */
 	@Override
 	public List<User> getUserList() {
 		Connection link = connect();
 		int userId;
 		String username;
 		List<User> users = new ArrayList<User>();
 		
 		try {
 			String query = "SELECT uId, user, pass FROM trip_user ";
 			Statement stmt = link.createStatement();
 			ResultSet rs = stmt.executeQuery(query);
 			
 			while (rs.next()) {
 				userId = rs.getInt(1);
 				username = rs.getString(2);
 				users.add(new User(userId, username, null));
 			}
 			dbLog("Sent list of users, with userId and username. ");
 			stmt.close();
 			link.close();
 		} catch (SQLException e) {
 			dbLog("SQL exception " + e + "\n");
 			e.printStackTrace();
 		}
 		return users;
 	}
 
 	/***
 	 * Returns user data for the specified user.
 	 * 
 	 * @param userId ID of user
 	 * @return User with given ID
 	 */
 	@Override
 	public User getUser(int userId) {
 		Connection link = connect();
 
 		User user = null;
 		
 		try {
 			String query = "SELECT user FROM trip_user WHERE uId = '"
 				+ userId + "'";
 			Statement stmt = link.createStatement();
 			ResultSet rs = stmt.executeQuery(query);
 			
 			while (rs.next()){
 				String username = rs.getString(1);
 				user = new User(userId, username, null);
 			}
 			dbLog("Sent user, with userId and username. ");
 			stmt.close();
 			link.close();
 		} catch (SQLException e) {
 			dbLog("SQL exception " + e + "\n");
 			e.printStackTrace();
 		}
 		return user;
 	}
 	
 	/***
 	 * {@inheritDoc}
 	 */
 	@Override
 	public Route makeRoute(int userId, String description,
 			boolean publicRoute) {
 		Route route = null;// = new Route(-1,0,null,true,null);
 
 		Connection link = connect();
 
 		try {			
 			String insertQuery = "INSERT INTO trip_route "
 				+ "(uId, description, active) "
 				+ "VALUES('" + userId  + "', '" + description + "', '1') ";
 			
 			Statement stmt = link.createStatement();
 			stmt.executeUpdate(insertQuery);
 			
 			String lastRouteQuery = "SELECT rId FROM trip_route "
 				+ "WHERE uId = '" + userId + "' ORDER BY rId DESC LIMIT 1";
 
 			ResultSet rs = stmt.executeQuery(lastRouteQuery);
 			
 			if (rs.next()) {
 				route = new Route(rs.getInt(1), userId, description);
 			}
 			
 			dbLog("Made route " + route.getRouteId() + " for user " + userId
 					+ " with description " + description);
 			
 			stmt.close();
 			link.close();
 		} catch (SQLException e) {
 			dbLog("SQL exception at makeroute: " + e + "\n");
 			e.printStackTrace();
 		}
 		return route;
 	}
 	
 	/***
 	 * {@inheritDoc}
 	 */
 	@Override
 	public List<Route> getRoutes(String username) {
 		List<Route> routes = new ArrayList<Route>();
 		Connection link = connect();
 		int userId = getUserId(username);
 		
 		try {
 			String query = "SELECT R.rId, R.uId, R.active, R.description, "
 				+ "R.public "
 				+ "FROM trip_route R LEFT OUTER JOIN trip_user U "
 				+ "ON R.uId = U.uId WHERE U.uId = '" + userId + "' "
 				+ "AND (U.uId = '" + getUserId() + "' OR R.public)"
 				+ "ORDER BY R.description";
 
 			Statement stmt = link.createStatement();
 			ResultSet rs = stmt.executeQuery(query);
 
 			while (rs.next()) {
 				int routeId = rs.getInt(1);
 				int uId = rs.getInt(2);
 				boolean active = rs.getBoolean(3);
 				String description = rs.getString(4);
 				boolean visible = rs.getBoolean(5);
 				String rQuery = "SELECT date " +
 					"FROM trip_coordinate " +
 					"WHERE  '" + routeId + "' = rId " +
 					"ORDER BY rId DESC " +
 					"LIMIT 1";
 
 				Statement rStmt = link.createStatement();
 				ResultSet routeSet = rStmt.executeQuery(rQuery);
 				Timestamp updated = null;
 				
 				if (routeSet.next()) {
 					updated = routeSet.getTimestamp(1);
 				}
 				
 				routes.add(new Route(routeId, uId, description, active, visible,
 						updated));
 			}
 			dbLog("Got routes for " + username);
 			stmt.close();
 			link.close();
 		} catch (SQLException e) {
 			dbLog("SQL exception at getRoutes: " + e);
 			e.printStackTrace();
 		}
 		return routes;
 	}
 
 	/***
 	 * {@inheritDoc}
 	 */
 	@Override
 	public List<Coordinate> getCoordinates(int routeID) {
 		List<Coordinate> coords = new ArrayList<Coordinate>();
 		Connection link = connect();
 
 		try {
 			// XXX tried adding order by
 			String query = "SELECT latitude, longitude, date "
 				+ "FROM trip_coordinate "
 				+ "WHERE rId = '" + routeID + "' ORDER BY date";
 			
 			Statement stmt = link.createStatement();
 			ResultSet rs = stmt.executeQuery(query);
 		
 			while (rs.next()) {
 				coords.add(new Coordinate(rs.getDouble(1), rs.getDouble(2),
 						Utils.parseDate(rs.getString(3))));
 			}
 			
 			stmt.close();
 			link.close();
 			dbLog("Sent coordinates for route: " + routeID);
 		} catch (SQLException e) {
 			dbLog("SQL exception at getCoordinates " + e);
 			e.printStackTrace();
 		}
 		return coords;
 	}
 
 	/***
 	 * {@inheritDoc}
 	 */
 	@Override
 	public void setVisible(int routeID, boolean visible) {
 		Connection link = connect();
 		
 		try {
 			String query = "UPDATE trip_route "
 				+ "SET public = " + (visible ? 1 : 0) + " "
 				+ "WHERE rId = '" + routeID + "'";
 		
 			Statement stmt = link.createStatement();
 			stmt.executeUpdate(query);
 			
 			stmt.close();
 			link.close();
 			dbLog("Changed visibility on route " + routeID);
 		} catch (SQLException e) {
 			dbLog("SQL exception at setVisible: " + e + "\n");
 			e.printStackTrace();
 		}
 	}
 
 	/***
 	 * {@inheritDoc}
 	 */
 	@Override
 	public void lockRoute(int routeID, boolean lock) {
 		Connection link = connect();
 		
 		try {
 			String query = "UPDATE trip_route "
 				+ "SET active = " + (lock ? 0 : 1) + " "
 				+ "WHERE rId = '" + routeID + "'";
 		
 			Statement stmt = link.createStatement();
 			stmt.executeUpdate(query);
 			
 			stmt.close();
 			link.close();
 			dbLog("Changed lock state on route " + routeID);
 		} catch (SQLException e) {
 			dbLog("SQL exception at lockRoute: " + e + "\n");
 			e.printStackTrace();
 		}
 	}
 
 	private void dbLog(String message){
 	//	System.out.println( message);
 		log(message);
 	}
 
 	/***
 	 * Returns spesific route with routeId
 	 * @param routeId
 	 * @return Route with given Id
 	 */
 	@Override
 	public Route getRoute(int routeId) {
 		Route route = null;
 		Timestamp updated = null;
 		Connection link = connect();
 		
 		try {
 			String query = "SELECT uId, active, description, public "
 				+ "FROM trip_route "
 				+ "WHERE rId = '" + routeId
 				+ "' AND (uId = '" + getUserId() + "' OR public)";
 			
 			Statement stmt = link.createStatement();
 			ResultSet rs = stmt.executeQuery(query);
 
 			while (rs.next()) {
 				int uId = rs.getInt(1);
 				Boolean active = rs.getBoolean(2);
 				String description = rs.getString(3);
 				Boolean visible = rs.getBoolean(4);
 				// Get date for last added coordinate.
 				String rQuery = "SELECT date "
 					+ "FROM trip_coordinate "
 					+ "WHERE rId = '" + routeId + "' "
 					+ "ORDER BY rId DESC "
 					+ "LIMIT 1"; 
 				
 				Statement rStmt = link.createStatement();
 				ResultSet routeSet = rStmt.executeQuery(rQuery);
 				
 				if (routeSet.next()) {
 					updated = routeSet.getTimestamp(1);
 				}
 				
 				route = new Route(routeId, uId, description, active, visible,
 						updated);
 				
 			}
 			dbLog("Got route: " + routeId + " updated: " + updated);
 			
 			stmt.close();
 			link.close();
 		} catch (SQLException e) {
 			dbLog("SQL exception at getRoutes: " + e);
 			e.printStackTrace();
 		}
 		return route;
 	}
 }