package airTeague;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class AirTeagueHelper {

	public Connection getConnection(){
		Connection con = null;
		try{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system","admin");
		}
		catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			System.out.println(e.getCause());
		}
		return con;
	}

	public int findDistance(String dep, String dest){
		int distance=0;
		try{
		Connection con = getConnection();
		Statement stmt = con.createStatement();
		String sql = "SELECT * FROM CITIES";
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next()){
			String city1 = rs.getString("CITY_1");
			String city2 = rs.getString("CITY_2");
			if(dep.equalsIgnoreCase(city1) || dep.equalsIgnoreCase(city2)){
				if(dest.equalsIgnoreCase(city1) || dest.equalsIgnoreCase(city2)){
					distance = rs.getInt("DISTANCE");
				}//end of inside loop
			}//end of total if
		}//end of while
		}//end of try
		catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			System.out.println(e.getCause());
		}//end of catch
		return distance;
	}

	public CityTempPair findDepartureInfo(String dep){
		CityTempPair departure = new CityTempPair();
		try{
			Connection con = getConnection();
			Statement stmt = con.createStatement();
			String sql = "SELECT * FROM CITIES";
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()){
				String city1 = rs.getString("CITY_1");
				String city2 = rs.getString("CITY_2");
				if(dep.equalsIgnoreCase(city1)){
					int temp = rs.getInt("WEATHER_CITY_1");
					departure.setName(dep);
					departure.setTemp(temp);
				}
				else if(dep.equalsIgnoreCase(city2)){
					int temp = rs.getInt("WEATHER_CITY_2");
					departure.setName(dep);
					departure.setTemp(temp);
				}
			}//end of while loop
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			System.out.println(e.getCause());
		}
		return departure;
	}

	public CityTempPair findDestinationInfo(String dest){
		CityTempPair destination = new CityTempPair();
		try{
			Connection con = getConnection();
			Statement stmt = con.createStatement();
			String sql = "SELECT * FROM CITIES";
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()){
				String city1 = rs.getString("CITY_1");
				String city2 = rs.getString("CITY_2");
				if(dest.equalsIgnoreCase(city1)){
					int temp = rs.getInt("WEATHER_CITY_1");
					destination.setName(dest);
					destination.setTemp(temp);
				}
				else if(dest.equalsIgnoreCase(city2)){
					int temp = rs.getInt("WEATHER_CITY_2");
					destination.setName(dest);
					destination.setTemp(temp);
				}
			}//end of while loop
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			System.out.println(e.getCause());
		}
		return destination;
	}

}