package uver;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;


public class Uver {

    public static void main(String[] args) {
        String url = "jdbc:postgresql://bdd.inf.udec.cl:5432/isw1a";
        String usuario = "isw1a";
        String contraseña = "matiasmora18";
        try{
            Class.forName("org.postgresql.Driver");
            Connection conexion = DriverManager.getConnection(url,usuario,contraseña);
            java.sql.Statement st = conexion.createStatement();

            /////Pruebas/////
            String sql;
            String driver="";
            //sql=addNewUser(12121212, "Robert", 1996, 3, 16, 98767698, "Callese Viejo Lesbiano", "arbochanpion@cc.ru", "1234567", true, false, false, false);
            //sql=addNewRoute("Temuco", "Valdivia", 16, 45, 0);


            //Saca id de viajes para ir de ciudad origen a destino o intermedios y los guarda en idtravels//
            sql=searchTravel("Concepción", "Iquique");
            ResultSet result=st.executeQuery(sql);
            ArrayList<String> idtravels = new ArrayList<>();
            ArrayList<String> nseatss = new ArrayList<>();
            while(result.next()){
                String idtravel = result.getString("idtravel");
                String nseats = result.getString("seats");
                idtravels.add(idtravel);
                nseatss.add(nseats);
            }
            result.close();
            if(idtravels.isEmpty())System.out.println("No hay viajes que satisfagan su consulta");
            //Da la info de los viajes obtenidos
            while(!idtravels.isEmpty()){
                String aux=searchTravel4(idtravels.get(0));
                result=st.executeQuery(aux);
                while(result.next())driver=result.getString("fullname");
                aux=searchTravel2(idtravels.get(0));
                result=st.executeQuery(aux);
                while(result.next()){
                    String ocity=result.getString("ocity");
                    String dcity=result.getString("dcity");
                    System.out.println("El viaje con Conductor:"+driver+", Origen:"+ocity+" y Destino:"+dcity+ " dispone "+nseatss.get(0)+" asientos totales y tiene el siguiente itinerario:");
                }
                sql=DReservation(idtravels.get(0));
                result.close();
                result=st.executeQuery(sql);
                while(result.next()){
                    String ocity=result.getString("ocity");
                    String dcity=result.getString("dcity");
                    String dseats=result.getString("dseats");
                    String sum=result.getString("sum");
                    int idseats=Integer.parseInt(dseats);
                    int isum=Integer.parseInt(sum);
                    if(isum>0)System.out.println(ocity+ "-" +dcity+ " Asientos Reservados: "+isum);

                }
                nseatss.remove(0);
                result.close();

                //Da la info de las rutas seguidas por los viajes obtenidos
                aux=searchTravel3(idtravels.get(0));
                result=st.executeQuery(aux);
                while(result.next()){
                    String ocity=result.getString("ocity");
                    String dcity=result.getString("dcity");
                    String ohour=result.getString("ohour");
                    System.out.println(ocity+ "-" +dcity+ " -> " +ohour);
                }
                idtravels.remove(0);
            }



            ////Fin////

           /* String sql = "SELECT * FROM uver.login WHERE username = 'dantzig' and pass = 1234";
            ResultSet result = st.executeQuery(sql);
            while(result.next()){
                String id = result.getString("username");
                String pas= result.getString("pass");
                System.out.println("id: " + id + "   pass: " + pas);
            }
            result.close();*/

            st.close();
            conexion.close();
        } catch(Exception e){
            System.out.println("ERROR DE CONEXION" + e.getMessage());
        }


    }



        ////Métodos////


    public static String addNewUser(int rut, String name, int bornyear, int bornmonth, int bornday, int phone, String description, String mail, String password, boolean smoke, boolean pet, boolean talk, boolean music){
    String addNewUser = "INSERT INTO uver.person (rut, fullname, born, phone, description, mail, password, smoke, pet, talk, music) VALUES (" +rut+ ", '" +name+ "', '"  +bornyear+ "-" +bornmonth+ "-" +bornday+ "', " +phone+ ", '" +description+ "', '" +mail+ "', '" +password+ "', " +smoke+ ", " +pet+ ", " +talk+ ", " +music+ ")";
    return addNewUser;
    }

    public static String addNewRoute(String src, String dst, int hour, int minute, int second){
    String addNewRoute = "INSERT INTO uver.route (idroute, ocity, dcity, ohour) VALUES ( " +Math.random()*100+ ", '" +src+ "', '" +dst+ "', '" +hour+ ":" +minute+ ":" +second+ "')";
    return addNewRoute;
    }

    public static String searchTravel(String src, String dst){
    String searchTravel="SELECT DISTINCT uver.travel_route.idtravel, uver.travel.seats\n" +
                        "FROM uver.travel_route\n" +
                        "INNER JOIN uver.route \n" +
                        "ON uver.travel_route.idroute = uver.route.idroute\n" +
                        "INNER JOIN uver.travel \n" +
                        "ON uver.travel.idtravel = uver.travel_route.idtravel \n" +
                        "WHERE uver.travel.ocity='"+src+"' AND uver.travel.dcity='"+dst+"' OR uver.route.ocity='"+src+"' AND uver.route.dcity='"+dst+"'";
    return searchTravel;
    }

    public static String searchTravel2(String idtravel){
        String searchTravel2="SELECT uver.travel.ocity, uver.travel.dcity\n" +
                             "FROM uver.travel\n" +
                             "WHERE uver.travel.idtravel="+idtravel+";";
        return searchTravel2;
    }

    public static String searchTravel3(String idtravel){
        String searchTravel3="SELECT uver.route.ocity, uver.route.dcity, uver.route.ohour\n" +
                             "FROM uver.travel_route\n" +
                             "INNER JOIN uver.route\n" +
                             "ON uver.travel_route.idroute=uver.route.idroute\n" +
                             "WHERE uver.travel_route.idtravel="+idtravel+";";
        return searchTravel3;
    }

    public static String searchTravel4(String idtravel){
        String searchTravel4="SELECT uver.person.fullname\n" +
                             "FROM uver.person_driver\n" +
                             "INNER JOIN uver.driver_travel \n" +
                             "ON uver.person_driver.rut=uver.driver_travel.rut AND uver.driver_travel.idtravel="+idtravel+"\n" +
                             "INNER JOIN uver.person\n" +
                             "ON uver.person.rut=uver.person_driver.rut";
        return searchTravel4;
    }

    public static String DReservation(String idtravel){
        String DReservation="SELECT uver.route.dseats, SUM(uver.reservation.nseats), uver.route.idroute, uver.travel.idtravel, uver.route.ocity, uver.route.dcity\n" +
                            "FROM uver.travel\n" +
                            "INNER JOIN uver.travel_route\n" +
                            "ON uver.travel.idtravel=uver.travel_route.idtravel\n" +
                            "INNER JOIN uver.route\n" +
                            "ON uver.route.idroute=uver.travel_route.idroute\n" +
                            "INNER JOIN uver.reservation\n" +
                            "ON uver.reservation.idroute=uver.route.idroute\n" +
                            "WHERE uver.travel.idtravel="+idtravel+"\n" +
                            "GROUP BY uver.route.idroute, uver.travel.idtravel";
        return DReservation;
    }

}