package RDG;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Marcel
 */
public class FlightsFinder {

    /**
     * select all flights w/ departure Timestamp between Timestamp from and Timestamp to
     * @param from
     * @param to
     * @return List of Flight
     */
    public static List<Flight> SelectAll(Timestamp from, Timestamp to) throws SQLException {
        PreparedStatement s = DbContext.getConnection().prepareStatement("SELECT * from flights WHERE departure_time>? AND arrival_time<? ORDER BY flight_id LIMIT 1000");
        s.setTimestamp(1, from);
        s.setTimestamp(2, to);
        //System.out.println(s);
        ResultSet rs = s.executeQuery();
        List<Flight> res = new ArrayList<>();
        while (rs.next()) {
            Flight tmp = new Flight();
            tmp.setFlight_id(rs.getInt("flight_id"));
            tmp.setName(rs.getString("flight_name"));
            tmp.setDeparture_time(rs.getTimestamp("departure_time"));
            tmp.setArrival_time(rs.getTimestamp("arrival_time"));
            tmp.setDistance(rs.getInt("distance"));
            tmp.setAircraft_id(rs.getInt("aircraft_type_id"));
            tmp.setDeparture_airport_id(rs.getInt("departure_airport_id"));
            tmp.setArrival_airport_id(rs.getInt("arrival_airport_id"));
            tmp.setCompany_id(rs.getInt("company_id"));
            tmp.setFlight_cost_id(rs.getInt("flight_cost_id"));
            res.add(tmp);
        }
        return res;
    }

    /**
     * select flight, price and free tickets for that flight. Select by id, class and passenger type.
     * @param id flight_id
     * @param class_id in what class should find flight
     * @param passenger_type for counting price
     * @throws SQLException
     * @throws Exception
     * @return List of Maps where keys are column names related to flight
     */
    public static List findByID(int id, int class_id, int passenger_type) throws SQLException, Exception {
        if (!Flight.exists(id)) {
            throw new Exception("Flight w/ this id don't exists");
        }
        if (!Class.exists(class_id)) {
            throw new Exception("Class w/ this id don't exists");
        }
        if (!PassengerType.exists(passenger_type)) {
            throw new Exception("Passenger type w/ this id don't exists");
        }
        Float cena = 0.0F;
        PreparedStatement s = DbContext.getConnection().prepareStatement("SELECT *  from flights \n" 
                + "inner join flight_costs on flights.flight_cost_id=flight_costs.flight_cost_id \n" 
                + "CROSS JOIN  (SELECT (SELECT \"count\" from seat_count_in_class INNER JOIN flights ON flights.aircraft_type_id=seat_count_in_class.aircraft_type_id \n" 
                + "where flights.flight_id=? and seat_count_in_class.aircraft_type_id=flights.aircraft_type_id and seat_count_in_class.class_id=?) -\n" 
                + "(SELECT count(*) from bought_tickets where canceled=false and class_id=? and flight_id=?) as free) as free_seats \n" 
                + "where flights.flight_id=? and free>0");
        s.setInt(1, id);
        s.setInt(2, class_id);
        s.setInt(3, class_id);
        s.setInt(4, id);
        s.setInt(5, id);
        ResultSet rs = s.executeQuery();
        List ret = new ArrayList<>();
        while (rs.next()) {
            Flight tmp = new Flight();
            cena = rs.getFloat("price_per_km") * rs.getInt("distance");
            PreparedStatement ps = DbContext.getConnection().prepareStatement("SELECT * from classes inner join flight_costs on flight_costs.flight_cost_id=classes.flight_cost_id and class_id=?");
            ps.setInt(1, class_id);
            ResultSet res = ps.executeQuery();
            while (res.next()) {
                cena += res.getFloat("price_per_km") * rs.getInt("distance");
            }
            ps = DbContext.getConnection().prepareStatement("SELECT * from passenger_types inner join flight_costs on flight_costs.flight_cost_id=passenger_types.flight_cost_id and passenger_type_id=?");
            ps.setInt(1, passenger_type);
            res = ps.executeQuery();
            while (res.next()) {
                cena += res.getFloat("price_per_km") * rs.getInt("distance");
            }
            tmp.setFlight_id(rs.getInt("flight_id"));
            tmp.setFlight_cost_id(rs.getInt("flight_cost_id"));
            tmp.setName(rs.getString("flight_name"));
            tmp.setDeparture_time(rs.getTimestamp("departure_time"));
            tmp.setArrival_time(rs.getTimestamp("arrival_time"));
            tmp.setDistance(rs.getInt("distance"));
            tmp.setAircraft_id(rs.getInt("aircraft_type_id"));
            tmp.setDeparture_airport_id(rs.getInt("departure_airport_id"));
            tmp.setArrival_airport_id(rs.getInt("arrival_airport_id"));
            tmp.setCompany_id(rs.getInt("company_id"));
            ret.add(tmp);
            ret.add(cena);
            ret.add(rs.getInt("free"));
        }
        return ret;
    }
}
