package ModelClass;

import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.annotation.PostConstruct;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;

@ManagedBean(name = "Plan")
@SessionScoped
public class Plan {

    private int EMERGENCY_ID;
    private int SHOPPING_ID;
    private int INTRA_REGION_TRANSPORT_ID;
    private int INTER_REGION_TRANSPORT_ID;
    private int RESTAURANT_ID;
    private int HOTEL_ID;
    private int LANDMARK_ID;
    private int USER_ID;
    private int PLAN_ID;
    private int BUDGET;
    private int DAYS;
    private String EMERGENCY_NAME;
    private String SHOPPING_NAME;
    private String INTRA_REGION_NAME;
    private String INTER_REGION_NAME;
    private String RESTAURANT_NAME;
    private String HOTEL_NAME;
    private String LANDMARK_NAME;
    private String DESTINATION;
    private String LOCATION;
    private final DBConnection dbconnection = new DBConnection();
    private Map<String, String> regions;
    private List<String> landmarks = new ArrayList<>();
    private List<String> selectedlandmarks = new ArrayList<>();
    private List<String> hotels = new ArrayList<>();
    private String selectedhotel ;
    private List<String> restaurants = new ArrayList<>();
    private String selectedrestaurant ;
    private List<String> intratransport = new ArrayList<>();
    private String selectedintratransport ;
    private List<String> intertransport = new ArrayList<>();
    private String selectedintertransport;


    public int getBUDGET() {
        return BUDGET;
    }

    public void setBUDGET(int BUDGET) {
        this.BUDGET = BUDGET;
    }
    public String Demo() {
       System.out.println(getBUDGET() + " working? " + getDAYS());
       findPlan();
       return "okay";
   }

    public List<String> getIntertransport() {
        return intertransport;
    }

    public void setIntertransport(List<String> intertransport) {
        this.intertransport = intertransport;
    }

    public String getSelectedintertransport() {
        return selectedintertransport;
    }

    public void setSelectedintertransport(String selectedintertransport) {
        this.selectedintertransport = selectedintertransport;
    }

    public List<String> getHotels() {
        return hotels;
    }

    public void setHotels(List<String> hotels) {
        this.hotels = hotels;
    }

    public String getSelectedhotel() {
        return selectedhotel;
    }

    public void setSelectedhotel(String selectedhotel) {
        this.selectedhotel = selectedhotel;
    }

    public List<String> getRestaurants() {
        return restaurants;
    }

    public void setRestaurants(List<String> restaurants) {
        this.restaurants = restaurants;
    }

    public String getSelectedrestaurant() {
        return selectedrestaurant;
    }

    public void setSelectedrestaurant(String selectedrestaurant) {
        this.selectedrestaurant = selectedrestaurant;
    }

    public List<String> getIntratransport() {
        return intratransport;
    }

    public void setIntratransport(List<String> intratransport) {
        this.intratransport = intratransport;
    }

    public String getSelectedintratransport() {
        return selectedintratransport;
    }

    public void setSelectedintratransport(String selectedintratransport) {
        this.selectedintratransport = selectedintratransport;
    }




    public int getDAYS() {
        return DAYS;
    }

    public void setDAYS(int DAYS) {
        this.DAYS = DAYS;
    }

    public List<String> getLandmarks() {
        return landmarks;
    }

    public void setLandmarks(List<String> landmarks) {
        this.landmarks = landmarks;
    }

    public List<String> getSelectedlandmarks() {
        return selectedlandmarks;
    }

    public void setSelectedlandmarks(List<String> selectedlandmarks) {
        this.selectedlandmarks = selectedlandmarks;
    }

    public Map<String, String> getRegions() {
        return regions;
    }

    public void setRegions(Map<String, String> regions) {
        this.regions = regions;
    }

    public String getDESTINATION() {
        return DESTINATION;
    }

    public void setDESTINATION(String DESTINATION) {
        this.DESTINATION = DESTINATION;
    }

    public String getLOCATION() {
        return LOCATION;
    }

    public void setLOCATION(String LOCATION) {
        this.LOCATION = LOCATION;
    }

    public String getEMERGENCY_NAME() {
        return EMERGENCY_NAME;
    }

    public void setEMERGENCY_NAME(String EMERGENCY_NAME) {
        this.EMERGENCY_NAME = EMERGENCY_NAME;
    }

    public String getSHOPPING_NAME() {
        return SHOPPING_NAME;
    }

    public void setSHOPPING_NAME(String SHOPPING_NAME) {
        this.SHOPPING_NAME = SHOPPING_NAME;
    }

    public String getINTRA_REGION_NAME() {
        return INTRA_REGION_NAME;
    }

    public void setINTRA_REGION_NAME(String INTRA_REGION_NAME) {
        this.INTRA_REGION_NAME = INTRA_REGION_NAME;
    }

    public String getINTER_REGION_NAME() {
        return INTER_REGION_NAME;
    }

    public void setINTER_REGION_NAME(String INTER_REGION_NAME) {
        this.INTER_REGION_NAME = INTER_REGION_NAME;
    }

    public String getRESTAURANT_NAME() {
        return RESTAURANT_NAME;
    }

    public void setRESTAURANT_NAME(String RESTAURANT_NAME) {
        this.RESTAURANT_NAME = RESTAURANT_NAME;
    }

    public String getHOTEL_NAME() {
        return HOTEL_NAME;
    }

    public void setHOTEL_NAME(String HOTEL_NAME) {
        this.HOTEL_NAME = HOTEL_NAME;
    }

    public String getLANDMARK_NAME() {
        return LANDMARK_NAME;
    }

    public void setLANDMARK_NAME(String LANDMARK_NAME) {
        this.LANDMARK_NAME = LANDMARK_NAME;
    }

    public int getEMERGENCY_ID() {
        return EMERGENCY_ID;
    }

    public void setEMERGENCY_ID(int EMERGENCY_ID) {
        this.EMERGENCY_ID = EMERGENCY_ID;
    }

    public int getSHOPPING_ID() {
        return SHOPPING_ID;
    }

    public void setSHOPPING_ID(int SHOPPING_ID) {
        this.SHOPPING_ID = SHOPPING_ID;
    }

    public int getINTRA_REGION_TRANSPORT_ID() {
        return INTRA_REGION_TRANSPORT_ID;
    }

    public void setINTRA_REGION_TRANSPORT_ID(int INTRA_REGION_TRANSPORT_ID) {
        this.INTRA_REGION_TRANSPORT_ID = INTRA_REGION_TRANSPORT_ID;
    }

    public int getINTER_REGION_TRANSPORT_ID() {
        return INTER_REGION_TRANSPORT_ID;
    }

    public void setINTER_REGION_TRANSPORT_ID(int INTER_REGION_TRANSPORT_ID) {
        this.INTER_REGION_TRANSPORT_ID = INTER_REGION_TRANSPORT_ID;
    }

    public int getRESTAURANT_ID() {
        return RESTAURANT_ID;
    }

    public void setRESTAURANT_ID(int RESTAURANT_ID) {
        this.RESTAURANT_ID = RESTAURANT_ID;
    }

    public int getHOTEL_ID() {
        return HOTEL_ID;
    }

    public void setHOTEL_ID(int HOTEL_ID) {
        this.HOTEL_ID = HOTEL_ID;
    }

    public int getLANDMARK_ID() {
        return LANDMARK_ID;
    }

    public void setLANDMARK_ID(int LANDMARK_ID) {
        this.LANDMARK_ID = LANDMARK_ID;
    }

    public int getUSER_ID() {
        return USER_ID;
    }

    public void setUSER_ID(int USER_ID) {
        this.USER_ID = USER_ID;
    }

    public int getPLAN_ID() {
        return PLAN_ID;
    }

    public void setPLAN_ID(int PLAN_ID) {
        this.PLAN_ID = PLAN_ID;
    }


    public void onLocationChange() {
        System.out.println(LOCATION);
    }

    public void onDestinationChange() throws SQLException {
        landmarks.clear();
        selectedlandmarks.clear();
        Statement stmt2 = null;
        Connection con = null;
        ResultSet rs1 = null;
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        String dbURL2 = "jdbc:oracle:thin:@localhost:1521:orcl";
        con = DriverManager.getConnection(dbURL2, dbconnection.user, dbconnection.password);
        if (con != null) {
            stmt2 = con.createStatement();
            try {
                String query = "SELECT REGION_ID FROM REGION WHERE REGION_NAME ='" + DESTINATION + "'";
                rs1 = stmt2.executeQuery(query);
                if (rs1.next()) {
                    int i = rs1.getInt(1);
                    rs1 = stmt2.executeQuery("SELECT LANDMARK_NAME FROM LANDMARKS WHERE REGION_ID = " + i);
                    while (rs1.next()) {
                        landmarks.add(rs1.getString(1));
                    }
                }
            } catch (Exception e) {
                System.out.println(e);
            } finally {
                try {
                    con.close();
                    stmt2.close();
                } catch (SQLException e) {
                    System.out.println(e);
                }
            }
        }
    }

    @PostConstruct
    public void init() {
        regions = new HashMap<String, String>();
        Statement stmt = null;
        Connection con = null;
        ResultSet rs = null;
        String value;

        try {
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

            String dbURL2 = "jdbc:oracle:thin:@localhost:1521:orcl";
            con = DriverManager.getConnection(dbURL2, dbconnection.user, dbconnection.password);
            if (con != null) {
                stmt = con.createStatement();
                try {
                    rs = stmt.executeQuery("SELECT REGION_NAME FROM REGION");
                    while (rs.next()) {
                        value = rs.getString(1);
                        regions.put(value, value);
                    }
                } catch (SQLException e) {
                    System.out.println("No data found");
                }
            }
        } catch (SQLException e) {
            System.out.println(e.toString());
        } finally {
            try {
                con.close();
                stmt.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        /*restaurants.add("hagu");
        restaurants.add("mutu");

        hotels.add("billu");
        hotels.add("puppy");*/
    }

    public void findPlan()
    {
        System.out.println(LOCATION);
        intertransport.clear();
        String ret = "failed";
        System.out.println("budget: "+BUDGET);
        //regions = new HashMap<String, String>();
        Statement stmt = null,stmt1=null,stmt2=null;
        CallableStatement cstmt;
        Connection con = null;
        ResultSet rs = null,rs1=null,rs2=null;
        String sql;
        int currentBalance = BUDGET,temp = 0, id1=0, id2=0;;

        try {
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

            String dbURL2 = "jdbc:oracle:thin:@localhost:1521:orcl";
            con = DriverManager.getConnection(dbURL2, dbconnection.user, dbconnection.password);
            if (con != null) {
                stmt = con.createStatement();
                stmt1 = con.createStatement();
                stmt2 = con.createStatement();
                try {
                    //suggest inter region transport

                    rs = stmt.executeQuery("SELECT REGION_ID FROM REGION WHERE REGION_NAME='"+LOCATION+"'");
                    //System.out.print();
                    if(rs.next()) {
                        id1 = rs.getInt(1);
                        rs1 = stmt1.executeQuery("SELECT REGION_ID FROM REGION WHERE REGION_NAME='"+DESTINATION+"'");
                       if(rs1.next())
                        {
                            id2 = rs1.getInt(1);
                            String query = "SELECT VEHICLE_NAME||' ('||EXPENSE||'/-)' FROM INTER_REGION_TRANSPORT WHERE FROM_DIST="
                            +id1+" AND TO_DIST="+ id2 +" AND 2*EXPENSE<="+currentBalance;
                            System.out.print(query);
                            rs2 = stmt2.executeQuery(query);
                            while(rs2.next())
                            {
                                intertransport.add(rs2.getString(1));
                                System.out.print("inserted");
                                ret ="success";
                            }

                        }
                    }

                        //sql = "{? = CALL MINIMUM_SPENT_INTER(?,?,?)}";
                        cstmt = con.prepareCall("{? = call MINIMUM_SPENT_INTER(?,?,?)}");
                        cstmt.registerOutParameter(1, Types.INTEGER);
                        cstmt.setInt(2, id1);
                        cstmt.setInt(3, id2);
                        cstmt.setInt(4, currentBalance);
                        cstmt.execute();
                        temp = cstmt.getInt(1);
                        currentBalance -= temp;
                        System.out.println(temp+" current:"+currentBalance);

                        //suggest Hotel options
                        hotels.clear();
                        sql = "SELECT HOTEL_NAME||' ('||MINIMUM_COST||'/-)' FROM HOTELS_AND_RESORTS WHERE REGION_ID="
                                + id2 +" AND MINIMUM_COST*"+DAYS+"<="+currentBalance;
                        System.out.println(sql);
                        if(DAYS>0)
                        {
                                rs=stmt.executeQuery(sql);
                                while(rs.next())
                                {
                                    hotels.add(rs.getString(1));
                                    System.out.println(rs.getString(1)+" inserted");
                                }

                            cstmt = con.prepareCall("{? = call MINIMUM_SPENT_HOTEL(?,?,?)}");
                            cstmt.registerOutParameter(1, Types.INTEGER);
                            cstmt.setInt(2, id2);
                            cstmt.setInt(3, currentBalance);
                            cstmt.setInt(4, DAYS);
                            cstmt.execute();
                            temp = cstmt.getInt(1);
                            currentBalance -= temp;
                            System.out.println(temp+" current:"+currentBalance);
                        //suggest intra region transport options
                        }
                        intratransport.clear();
                        for(int i=0;i<landmarks.size();i++)
                        {
                            sql = "SELECT MODE_OF_TRANSPORT||' ('||EXPENSE||'/-)' FROM INTRA_REGION_TRANSPORT WHERE FROM_DEST ='"+DESTINATION+"' AND TO_DEST ='"+
                                    landmarks.get(i)+"' AND 2*EXPENSE<="+currentBalance;
                            System.out.println(sql);
                            rs = stmt.executeQuery(sql);
                            while(rs.next())
                            {
                                intratransport.add(rs.getString(1));
                                System.out.println(rs.getString(1) +" inserted");
                            }
                                cstmt = con.prepareCall("{? = call MINIMUM_SPENT_INTRA(?,?,?)}");
                                cstmt.registerOutParameter(1, Types.INTEGER);
                                cstmt.setString(2, LOCATION);
                                cstmt.setString(3, landmarks.get(i));
                                cstmt.setInt(4, currentBalance);
                                cstmt.execute();
                                temp = cstmt.getInt(1);
                                currentBalance -= temp;
                                System.out.println(temp+" current:"+currentBalance);
                        }

                        //suggest Restaurants options
                        restaurants.clear();
                        sql = "SELECT RESTAURANT_NAME||' ('||AVERAGE_COST||'/-)' FROM RESTAURANTS WHERE "
                                + "REGION_ID="+id2+" AND AVERAGE_COST*"+DAYS+"<="+currentBalance;
                        System.out.println(sql);
                        if(DAYS>0)
                        {
                            rs = stmt.executeQuery(sql);
                            while(rs.next())
                            {
                                restaurants.add(rs.getString(1));
                                System.out.println("inserted "+rs.getString(1));
                            }
                            cstmt = con.prepareCall("{? = call MINIMUM_SPENT_RESTAURANT(?,?,?)}");
                            cstmt.registerOutParameter(1, Types.INTEGER);
                            cstmt.setInt(2, id2);
                            cstmt.setInt(3, currentBalance);
                            cstmt.setInt(4, DAYS);
                            cstmt.execute();
                            temp = cstmt.getInt(1);
                            currentBalance -= temp;
                            System.out.println(temp+" current:"+currentBalance);

                        }
                } catch (SQLException e) {
                    System.out.println("No data found");
                }
            }
        } catch (SQLException e) {
            System.out.println(e.toString());
        } finally {
            try {
                con.close();
                stmt.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

    }

    public void storePlan()
    {

    }


    public void insert() throws SQLException {

        /*  Statement stmt = null;
    Connection con = null;
    ResultSet rs = null;
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    String dbURL2 = "jdbc:oracle:thin:@localhost:1521:orcl";
    con = DriverManager.getConnection(dbURL2, user, password);
    System.out.print("In insert function "+getRegionID()+getRegionName());
            if (con!= null) {
                System.out.println("Connected with connection #2");
                stmt = con.createStatement();
            try {
            rs = stmt.executeQuery("SELECT * FROM REGION WHERE REGION_NAME = '"+getRegionName()+"'");
                if(!rs.next())
                {
                stmt.executeUpdate("INSERT INTO REGION (REGION_ID, REGION_NAME)"+" VALUES(REGION_ID_VAL.NEXTVAL"+", "+"'"+getRegionName()+"')");
                System.out.print("In insert function "+getRegionID()+getRegionName());
                }
            }
            catch(SQLException e)
            {
                System.out.println("No data found while inserting");
            }
            finally
            {
                try
                {
                    con.close();
                    stmt.close();
                }
                catch(SQLException e)
                {
                           System.out.println(e);
                }
            }
            }  */
    }

    public void delete() throws SQLException {
        /*  Statement stmt = null;
    Connection con = null;
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    String dbURL2 = "jdbc:oracle:thin:@localhost:1521:orcl";
    con = DriverManager.getConnection(dbURL2, user, password);
    System.out.print("In delete function "+getRegionName());
    if (con!= null) {
            System.out.println("Connected with connection #2");
            stmt = con.createStatement();
            try {
            stmt.executeUpdate("DELETE FROM REGION WHERE REGION_NAME = '"+getDelRegionName()+"'");
            System.out.print("In delete function "+getRegionName());
            }
            catch(SQLException e)
            {
                System.out.println("No data found during deletion");
            }
            finally
            {
                try
                {
                    con.close();
                    stmt.close();
                }
                catch(SQLException e)
                {
                           System.out.println(e);
                }
            }
            }  */

    }

}