package RDG;

import TS.FlightDijkstraFinder;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import javax.sql.DataSource;
import org.postgresql.ds.PGSimpleDataSource;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.PriorityQueue;

/**
 *
 * @author Marcel
 */
public class City {
    Integer city_id;
    String city_name;

    public Integer getCity_id() {
        return city_id;
    }

    public void setCity_id(Integer city_id) {
        this.city_id = city_id;
    }

    public String getCity_name() {
        return city_name;
    }

    public void setCity_name(String city_name) {
        this.city_name = city_name;
    }

    @Override
    public String toString() {
        return "City{" + "city_id=" + city_id + ", city_name=" + city_name + '}';
    }
    
    /**
    * return direct flights from some city, time and class
    * @param from departure_city_id
    * @param cas earliest departure time
    * @param class_id class, what should find.
    * @throws SQLException
    * @return List of Zaznams, all possible options
    */
    static public List<FlightDijkstraFinder.Zaznam> DirectCities(Integer from, Timestamp cas, Integer class_id) throws SQLException, Exception{
        if(!City.exists(from))
            throw new Exception("Flight w/ this id don't exists");
        List<FlightDijkstraFinder.Zaznam> res = new ArrayList<>();
        PreparedStatement s = DbContext.getConnection().prepareStatement("SELECT arrival_time, a.city_id as arrival_city, flight_id "
                + "FROM flights INNER JOIN airports as d ON flights.departure_airport_id=d.airport_id INNER JOIN airports as a ON flights.arrival_airport_id=a.airport_id "
                + "WHERE d.city_id=? and departure_time>? "
                + "and (select \"count\" from seat_count_in_class where seat_count_in_class.aircraft_type_id=flights.aircraft_type_id and class_id=?) > "
                + "(SELECT count(*) from bought_tickets where bought_tickets.flight_id=flights.flight_id and class_id=?)");
        s.setInt(1, from);
        s.setTimestamp(2, cas);
        s.setInt(3, class_id);
        s.setInt(4, class_id);
        ResultSet rs = s.executeQuery();
        while(rs.next()){
            res.add(new FlightDijkstraFinder.Zaznam(rs.getInt("arrival_city"), rs.getTimestamp("arrival_time"), new ArrayList<>(), rs.getInt("flight_id")));
        }
        return res;
    }
    /**
     * check if city w/ this id exists
     * @param city_id  
     * @throws SQLException
     * @return boolean if city w/ this id exists
     */
    public static boolean exists(int city_id) throws SQLException {
        PreparedStatement s = DbContext.getConnection().prepareStatement("SELECT * from cities WHERE city_id=?");
        s.setInt(1, city_id);
        return s.executeQuery().next();
    }
}
