import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.*;
import javax.servlet.http.*;

/**
 *
 * @author lonsdalp
 * @version
 */
public class WorkingFlightsServlet extends HttpServlet {
    
    
    boolean OriginExists=false;
    boolean DestinationExists=false;
    boolean deptExists=false;
    boolean arrivalTimeExists=false;
    String[] stringy = new String[0];
    /** Handles the HTTP GET method.
     * @param request servlet request
     * @param response servlet response
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        
        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
//creating html form
        out.println("<P>");
// in the following line, we use the escape character \ before the " because the " is actually part of the HTML code, not the Java code
// using the escape character tells the compiler to treat the following " as a literal string character, not part of the code
        out.print("<form action= \"WorkingFlightsServlet\" ");
        out.println("method=POST>");
        out.println("<head>");
        out.println("<title>Maybe</title>");
        out.println("</head>");
        out.println("<body bgcolor=#CCFFFF>");
        out.println("<h1> MAYBE CRAZY </h1>");
        out.println("<table>"+"<tr>");
        out.println("<td><b>Enter Origin:</b></td>");
        out.println("<td><input type=text size=20 name=Origin></td>");
        out.println("</tr>"+"<br>");
        out.println("<tr>");
        out.println("<td><b>Enter Destination:</td>");
        out.println("<td><input type=text size=20 name=Destination></td>");
        out.println("</tr>");
        out.println("<br>");
        out.println("<tr>");
        out.println("<td><b>Enter Departure Time:</td>");
        out.println("<td><input type=text size=20 name=DeptTime></td>");
        out.println("</tr>");
        out.println("<br>");
        out.println("<tr>");
        out.println("<td><b>Enter Arrival Time:</td>");
        out.println("<td><input type=text size=20 name=ArriTime></td>");
        out.println("</tr>");
        out.println("<br>");
         out.println("<tr>");
        out.println("<td><b>Enter Maximum Distance:</td>");
        out.println("<td><input type=text size=20 name=MaxDist></td>");
        out.println("</tr>");
        out.println("<br>");
         out.println("<tr>");
        out.println("<td><b>Enter Maimum Changes:</td>");
        out.println("<td><input type=text size=20 name=MaxCha></td>");
        out.println("</tr>");
        out.println("<br>");
        out.println("</table>");
        out.println("<br>");
        out.println("<b>Group by <BR>");
        out.println("<br>");
        out.println("<INPUT TYPE=RADIO NAME=groupby VALUE=Origin>checked=checked Origin<BR>");
        out.println("<br>");
        out.println("<INPUT TYPE=RADIO NAME=groupby VALUE=Destination>Destination<BR>");
        out.println("<br>");
        out.println("<b><input type=submit>");
        out.println("</body>");
        out.println("</form>");
        
    }
    
    /** Handles the HTTP POST method.
     * @param request servlet request
     * @param response servlet response
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        
        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
        float dept=-1;
        float arrivalTime;
        
        String Origin = request.getParameter("Origin");
        out.println(Origin);
        String Destination = request.getParameter("Destination");
        out.println(Destination);
        String dept1 = request.getParameter("depttime");
        if(dept1!=null&& dept1.length()>0){
            dept = Float.parseFloat(dept1);
            deptExists=true;
        }
        String arrivalTime1 =request.getParameter("arrivalTime");
        if(arrivalTime1!=null && arrivalTime1.length()>0){
            arrivalTime = Float.parseFloat(arrivalTime1);
            //String cookieNamearrivalTime1 = arrivalTime1;
            arrivalTimeExists=true;
        }
        if(Origin!=null&& Origin.length()>1){
            OriginExists=true;
            //String cookieNameOrigin = Origin;
        }
        if(Destination!=null&& Destination.length()>1){
            //String cookieNameDestination = Destination;
            DestinationExists=true;
        }
        
        try {
            if(request.getParameter("groupby")!=null){
                stringy= request.getParameterValues("groupby");
            }
            Class.forName("org.postgresql.Driver");
            Connection con;
            con = DriverManager.getConnection("jdbc:postgresql://dbteach/ug74evd", "ug74evd", "rotofroh");
            Statement stamt = con.createStatement();
            ResultSet rs = stamt.executeQuery("SELECT * FROM FLIGHTS WHERE ORIGIN='TTT';");
            //if groupby is set to destination
            if(stringy[0].equals("Destination")){
                out.println("groupby destination");
                if(!OriginExists){
                    if(!DestinationExists){
                        if(!deptExists){
                            if(!arrivalTimeExists){
                                out.println("Please enter a value");
                            }else{//only arrival time exists
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE  arrival_time ="+arrivalTime1+" ORDER by destination;");
                            }
                        }else{//if dept_time does exist but destinaion and origin don't
                            if(!arrivalTimeExists){//only dept_time exists
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE dept_time =\'"+dept1+"\'ORDER by destination;");
                            }else{//dept_time and arrival time exist
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE dept_time =\'"+dept1+ "\' and arrival_time =\'"+arrivalTime1+"\'ORDER by destination;");
                            }
                        }
                    }else{if(!deptExists){//if destination exists
                        if(!arrivalTimeExists){
                            out.println("we have a destination");
                            rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE destination =\'"+Destination+"\'ORDER by destination;");
                        }else{//if destination and arrival time exist
                            rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE arrival_time =\'"+arrivalTime1+ "\' and destination =\'"+Destination+"\'ORDER by destination;");
                        }
                    }else{//if destination and dept time exist
                        if(!arrivalTimeExists){
                            rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE dept_time =\'"+dept1+ "\' and destination =\'"+Destination+"\'ORDER by destination;");
                        }else{//if destination, dept time and arrival time exist
                            rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE dept_time =\'"+dept1+ "\' and destination =\'"+Destination+"\' and arrival_time=\'"+arrivalTime1+"\'ORDER by destination;");
                        }
                    }
                    }
                }else{//origin exists
                    if(!DestinationExists){
                        if(!deptExists){
                            if(!arrivalTimeExists){//only origin exists
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\'ORDER by destination;");
                                out.println("query done");
                            }else{//arrival time and origin exist
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\'and arrival_time=\'"+arrivalTime1+"\'ORDER by destination;");
                            }
                        }else{
                            if(!arrivalTimeExists){// dept time and origin exist
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\'and dept_time=\'"+dept1+"\'ORDER by destination;");
                            }else{// dept time, arrival time and origin exist
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\' and arrival_time=\'"+arrivalTime1+"\' and dept_time=\'"+dept1+"\'ORDER by destination;");
                            }
                        }
                    }else{
                        if(!deptExists){
                            if(!arrivalTimeExists){//destination and origin exist
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\' and destination=\'"+Destination+"\'ORDER by destination;");
                            }else{//destination, origin and arrival time exists
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\'and destination=\'"+Destination+"\'and arrival_time=\'"+arrivalTime1+"\'ORDER by destination;");
                            }
                        }else{
                            if(!arrivalTimeExists){
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\' and destination=\'"+Destination+"\' and dept_time=\'"+dept1+"\'ORDER by destination;");
                            }
                            //departure time, destination,origina and arrival time exists
                            rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\' and destination=\'"+Destination+"\' and arrival_time=\'"+arrivalTime1+"\'and dept_time=\'"+dept1+"\'ORDER by destination;");
                        }
                    }
                }
            }
            if(stringy[0].toString().equals("Origin")){
                out.println("groupby origin");
                if(!OriginExists){
                    if(!DestinationExists){
                        if(!deptExists){
                            if(!arrivalTimeExists){
                                out.println("Please enter a value");
                            }else{//only arrival time exists
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE  arrival_time ="+arrivalTime1+" ORDER by Origin;");
                                
                            }
                        }else{//if dept_time does exist but destinaion and origin don't
                            if(!arrivalTimeExists){//only dept_time exists
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE dept_time =\'"+dept1+"\' ORDER by Origin;");
                            }else{//dept_time and arrival time exist
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE dept_time =\'"+dept1+ "\' and arrival_time =\'"+arrivalTime1+"\' ORDER by Origin;");
                            }
                        }
                    }else{if(!deptExists){//if destination exists
                        if(!arrivalTimeExists){
                            rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE destination =\'"+Destination+"\' ORDER by Origin;");
                        }else{//if destination and arrival time exist
                            rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE arrival_time =\'"+arrivalTime1+ "\' and destination =\'"+Destination+"\' ORDER by Origin;");
                        }
                        
                        
                    }else{//if destination and dept time exist
                        if(!arrivalTimeExists){
                            rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE dept_time =\'"+dept1+ "\' and destination =\'"+Destination+"\' ORDER by Origin;");
                        }else{//if destination, dept time and arrival time exist
                            rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE dept_time =\'"+dept1+ "\' and destination =\'"+Destination+"\' and arrival_time=\'"+arrivalTime1+"\' ORDER by Origin;");
                        }
                    }
                    }
                    
                }else{//origin exists
                    if(!DestinationExists){
                        if(!deptExists){
                            if(!arrivalTimeExists){//only origin exists
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\' ORDER by Origin;");
                                out.println("query done");
                            }else{//arrival time and origin exist
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\'and arrival_time=\'"+arrivalTime1+"\' ORDER by Origin;");
                            }
                            
                        }else{
                            if(!arrivalTimeExists){// dept time and origin exist
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\'and dept_time=\'"+dept1+"\' ORDER by Origin;");
                            }else{// dept time, arrival time and origin exist
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\' and arrival_time=\'"+arrivalTime1+"\' and dept_time=\'"+dept1+"\'ORDER by Origin;");
                            }
                        }
                        
                    }else{
                        if(!deptExists){
                            if(!arrivalTimeExists){//destination and origin exist
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\' and destination=\'"+Destination+"\' ORDER by Origin;");
                            }else{//destination, origin and arrival time exists
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\'and destination=\'"+Destination+"\'and arrival_time=\'"+arrivalTime1+"\'ORDER by Origin;");
                            }
                            
                        }else{
                            //departure time, destination,origina and arrival time exists
                            rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\' and destination=\'"+Destination+"\' and arrival_time=\'"+arrivalTime1+"\'and dept_time=\'"+dept1+"\' ORDER by Origin;");
                        }
                        
                    }
                }}
           // rs.next();
            //need to print out result set neatly!
            out.println();
            
         
           
            String format;
            ResultSetMetaData metadata = rs.getMetaData();
            int colcount = metadata.getColumnCount();
            out.println("<table BORDER=3 bgcolor=#CCFFFF>");
            out.println("<tr>");
            for (int i = 1; i <= colcount; i++) {
                format = metadata.getColumnDisplaySize(i) + "s ";
                //   out.println("<tr>");
                // out.println("<td>"+ format+"</td>");
                out.println("<td>"+metadata.getColumnLabel(i)+"</td>");
                // out.println("</tr>");
                
            }
            out.println("</tr>");
            while(rs.next()) {
                out.println("<tr>");
                for (int i = 1; i <= colcount; i++) {
                    format = metadata.getColumnDisplaySize(i) + "s ";
                    // out.println(format);
                    out.println("<td>"+rs.getString(i)+"</td>");
                   // out.println("</tr>");
                }
                out.println("</tr>");
        
    }
       
           
            rs.close();
            
            //cookies
            Cookie[] cookies = request.getCookies();
            if ((cookies != null) && (cookies.length > 0)) {
                out.println("Cookies:" + "<br>");
                // we can iterate through each Cookie and get its Name and Value
                for (int i = 0; i < cookies.length; i++) {
                    Cookie cookie = cookies[i];
                    if(cookies[i].getName()=="groupby")
                        out.print("Cookie Name: " + (cookie.getName())
                        + "<br>");
                    out.println(" Cookie Value: "  + (cookie.getValue()) + "<br><br>");
                }
            } else {//if there are no cookies
                out.println("no cookies set<br><br>");
            }
            // set a cookie, if this request has parameters
            String cookieName = "groupby";
            // stringy[0] contains the value of the selected groupby radio button.
            String cookieValue1 = stringy[0];
         
            if (cookieName != null && cookieValue1 != null) {
                Cookie cookie = new Cookie(cookieName, cookieValue1);
                response.addCookie(cookie);
                
                out.println("<P>just set the cookie "+cookieName+"="+cookieValue1+"<br><br>");
            }  
            
            
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (ClassNotFoundException cnfe) {
            cnfe.printStackTrace();
        }
        
    }

}