/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package air_hotel_project;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Locale;
import java.util.concurrent.TimeUnit;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.scene.control.DatePicker;

/**
 *
 * @author Shoumik
 */
public class Controller {
    static Connection con;
    static PreparedStatement pst;
    static ResultSet rs;
    public static ObservableList<Hotel> hotelData;
    public static ObservableList<Flight> flightData;
   // public static UI myUI;

    public Controller() {
        hotelData = FXCollections.observableArrayList();
        flightData = FXCollections.observableArrayList();
    }

    public void EstablishConnection(){
          try {
                con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:globaldb", "shoumik", "shoumik");
               // showData();
            } catch (SQLException e) {
            //    errorAlert("Invalid Username/Password", "Invalid Username/Password", null);
            }
    }
    public ObservableList<Hotel> getHotels(String location, DatePicker checkIn, DatePicker checkOut ){
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd-MMM-yyyy",Locale.US);
        String formattedValue = (checkIn.getValue()).format(formatter);
        LocalDate checkInDate = checkIn.getValue();
        Date date1 = java.sql.Date.valueOf(checkInDate);
        LocalDate localDateCurrent = LocalDate.now();
        Date date2 = java.sql.Date.valueOf(localDateCurrent);
        long diff = getDateDiff(date1, date2, TimeUnit.DAYS);

	//String formattedValue1 = (datePicker1.getValue()).format(formatter);
        String sql = "SELECT H.PICTURE, H.HOTEL_NAME, (H.HOTEL_CITY ||','|| H.HOTEL_COUNTRY), R.ROOM_TYPE,  (R.SINGLE_BED_COUNT ||','||R.DOUBLE_BED_COUNT) , ROOM.AIR_CONDITIONER, H.BREAKFAST, HR.HOTEL_ROOM_PRICE, H.BREAKFAST_PRICE, HR.INCREMENT_FOR_DATE  \n" +
"FROM HOTEL H \n" +
"JOIN HOTEL_ROOM HR\n" +
"ON (H.HOTEL_ID=HR.HOTEL_ID)\n" +
"JOIN ROOM R\n" +
"ON (R.ROOM_ID=HR.ROOM_ID)\n" +
"WHERE (H.HOTEL_CITY='"+location+"' OR H.HOTEL_COUNTRY='"+location+"') AND HR.FULL_BOOKED_DATE<TO_DATE('"+formattedValue+"');";
        try {
            con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:globaldb", "shoumik", "shoumik123");
            pst = con.prepareStatement(sql);
            rs = pst.executeQuery();
            hotelData.clear();
            while (rs.next()) {
                //hotelData.add(new Hotel(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4)));
               float exp=rs.getFloat(8);
               float var=(float) (1.00/(Math.pow(diff, exp)));
               int price = (int) (rs.getInt(6)*(1.00+var));
               hotelData.add(new Hotel(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), price, rs.getInt(9)));
                // System.out.println(rs.getInt("team_id") + " , " + rs.getString(2) + " , " + rs.getString(3) + " , " + rs.getString(4) + " , " + rs.getString(5) + " , " + rs.getString(6));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return hotelData;
    }
    public static void main(String[] args) {
        Controller controller = new Controller();
        controller.EstablishConnection();
//        controller.getHotels();
        UI.startInterface(args);
    }

    public static long getDateDiff(Date date1, Date date2, TimeUnit timeUnit) {
    long diffInMillies = date2.getTime() - date1.getTime();
    return timeUnit.convert(diffInMillies,TimeUnit.MILLISECONDS);
}
}