import java.sql.*;
import java.util.ArrayList;
public class Tour_Monde {
	static ArrayList<String> list1 = new ArrayList<String>();
	static ArrayList<Integer> list2 = new ArrayList<Integer>();
	static String Trajet=" ";
	public static void main(String[] args)
	{
		FetchEscales("Paris",5);

	}
	public static void FetchEscales(String ville1, int heures){
		int heuresCalculé=0 ;
		String Ville=ville1;
		Boolean arret=false;
		int cpt=0;

		//int Heures;


		try
		{
			String myUrl = "jdbc:mysql://localhost/tp-vol";
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(myUrl, "root", "");
			String querytop = "select*from vol where Ville_depart='" + Ville +"' and ville_arrive in(select ville_escale from escales)";
			Statement sttop = conn.createStatement();
			ResultSet rstop = sttop.executeQuery(querytop);
			while(rstop.next()){
				arret=false;
				String Num = rstop.getString("NumVol");
				Trajet=Trajet+" | "+Num;
				String V_D = rstop .getString("Ville_depart");
				Trajet=Trajet+" | "+V_D;
				String H_depart=rstop.getString("Heure_depart");
				Trajet=Trajet+" | "+H_depart;
				String H_arrive=rstop.getString("Heure_arrive");
				Trajet=Trajet+" | "+H_arrive;
				String V_A = rstop .getString("Ville_arrive");
				Trajet=Trajet+" | "+V_A;
				// print the results
				//System.out.format("%s | %s | %s | %s | %s\n",Num,V_D,H_depart,H_arrive,V_A);
				Ville=V_A;
				//System.out.println("Voila  : "+Ville);
				heuresCalculé=calculHeures(Ville, heuresCalculé);
				cpt++;
				siRetourParisPossible(Ville,heuresCalculé);

				while(arret!=true){
					String query = "select*from vol where Ville_depart='" + Ville +"' and ville_arrive in(select ville_escale from escales)";
					Statement st = conn.createStatement();
					ResultSet rs = st.executeQuery(query);
					while(rs.next()){
						String Num2 = rs.getString("NumVol");
						Trajet=Trajet+" | "+Num2;
						String V_D2 = rs .getString("Ville_depart");
						Trajet=Trajet+" | "+V_D2;
						String H_depart2=rs.getString("Heure_depart");
						Trajet=Trajet+" | "+H_depart2;
						String H_arrive2=rs.getString("Heure_arrive");
						Trajet=Trajet+" | "+H_arrive2;
						String V_A2 = rs .getString("Ville_arrive");
						Trajet=Trajet+" | "+V_A2;

						// print the results

						//System.out.format("%s | %s | %s | %s | %s\n",Num2,V_D2,H_depart2,H_arrive2,V_A2);
						Ville=V_A2;
						heuresCalculé=calculHeures(Ville, heuresCalculé);
						cpt++;
						siRetourParisPossible(Ville,heuresCalculé);
					}

					//}
					//************
					if(cpt==0){
						String villeD = null;
						arret=true;
						String query2 = "select * from vol where Ville_depart='" + Ville +"'";
						Statement st2 = conn.createStatement();
						ResultSet rs2 = st2.executeQuery(query2);
						while(rs2.next()){
							String Num1 = rs2.getString("NumVol");
							Trajet=Trajet+" | "+Num1;
							String V_D1 = rs2.getString("Ville_depart");
							Trajet=Trajet+" | "+V_D1;
							String H_depart1=rs2.getString("Heure_depart");
							Trajet=Trajet+" | "+H_depart1;
							String H_arrive1=rs2.getString("Heure_arrive");
							Trajet=Trajet+" | "+H_arrive1;
							String V_A1 = rs2.getString("Ville_arrive");
							Trajet=Trajet+" | "+V_A1;
							//System.out.format("%s | %s | %s | %s | %s\n",Num1,V_D1,H_depart1,H_arrive1,V_A1);
							villeD=V_A1;
						}
						st2.close();
						rs2.close();
						String query4 = "select * from vol where Ville_depart='" + villeD +"'and Ville_arrive='Paris'";
						Statement st4 = conn.createStatement();
						ResultSet rs4 = st4.executeQuery(query4);
						while(rs4.next()){
							String Num4 = rs4.getString("NumVol");
							Trajet=Trajet+" | "+Num4;
							String V_D4 = rs4.getString("Ville_depart");
							Trajet=Trajet+" | "+V_D4;
							String H_depart4=rs4.getString("Heure_depart");
							Trajet=Trajet+" | "+H_depart4;
							String H_arrive4=rs4.getString("Heure_arrive");
							Trajet=Trajet+" | "+H_arrive4;
							String V_A4 = rs4.getString("Ville_arrive");
							Trajet=Trajet+" | "+V_A4;
							//System.out.format("%s | %s | %s | %s | %s\n",Num4,V_D4,H_depart4,H_arrive4,V_A4);
							//System.out.println("Heures escales : "+heuresCalculé);
							list1.add(Trajet);
							list2.add(heuresCalculé);
							//System.out.println("--------------------");
						}
						heuresCalculé=0;
						Trajet=" ";
						st4.close();
						rs4.close();

					}//************

					cpt=0;
					st.close();
					rs.close();
				}
			}
			sttop.close();
			rstop.close();
			conn.close();
			//------
			int cpt2=0;
			for(int i=0;i<list1.size();i++){
				if(list2.get(i)<=heures){
					System.out.println(list1.get(i).toString());
					System.out.println("  nombres heures escales : "+list2.get(i).toString());
					System.out.println("  -----------------------");
				}
				else{
					cpt++;

				}

			}
			if(cpt==list1.size()){
				System.out.println("aucun vol ne correspond au nombre heures escales que vous voulez");
			}

		}
		catch (Exception e)
		{
			System.err.println("Got an exception! ");
			System.err.println(e.getMessage());
		}

	}
	public static int calculHeures(String villeEscale,int heures){
		try {
			String myUrl = "jdbc:mysql://localhost/tp-vol";
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn;
			conn = DriverManager.getConnection(myUrl, "root", "");

			String query = "select Duree_escale from escales where Ville_escale='"+ villeEscale +"'";
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(query);
			while(rs.next()){
				int Num2 = rs.getInt("Duree_escale");
				heures=heures+Num2;

			}
		}
		catch (Exception e)
		{
			System.err.println("Got an exception! ");
			System.err.println(e.getMessage());
		}
		return heures;

	}
	public static void siRetourParisPossible(String ville,int heures){
		try {
			String myUrl = "jdbc:mysql://localhost/tp-vol";
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn;
			conn = DriverManager.getConnection(myUrl, "root", "");
			String Trajet2=Trajet;
			String query = "select * from vol where Ville_depart='"+ ville +"' and Ville_arrive='Paris'";
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(query);
			while(rs.next()){
				String Num4 = rs.getString("NumVol");
				Trajet2=Trajet2+" | "+Num4;
				String V_D4 = rs.getString("Ville_depart");
				Trajet2=Trajet2+" | "+V_D4;
				String H_depart4=rs.getString("Heure_depart");
				Trajet2=Trajet2+" | "+H_depart4;
				String H_arrive4=rs.getString("Heure_arrive");
				Trajet2=Trajet2+" | "+H_arrive4;
				String V_A4 = rs.getString("Ville_arrive");
				Trajet2=Trajet2+" | "+V_A4;
				//System.out.format("%s | %s | %s | %s | %s\n",Num4,V_D4,H_depart4,H_arrive4,V_A4);
				//System.out.println("Heures escales : "+heuresCalculé);
				list1.add(Trajet2);
				list2.add(heures);

			}
		}
		catch (Exception e)
		{
			System.err.println("Got an exception! ");
			System.err.println(e.getMessage());
		}
	}
}