package RDG;

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 java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.postgresql.ds.PGSimpleDataSource;
/**
 *
 * @author Marcel
 */

/**
* data row gateway for Flight
*/
public class Flight {
    private Integer flight_id;
    private String name;
    private Timestamp departure_time;
    private Timestamp arrival_time;
    private Integer distance;
    private Integer departure_airport_id;
    private Integer arrival_airport_id;
    private Integer company_id;
    private Integer aircraft_id;
    private Integer flight_cost_id;

    @Override
    public String toString() {
        return "Flights{" + "flight_id=" + flight_id + ", name=" + name + ", departure_time=" + departure_time + ", arrival_time=" + arrival_time + ", distance=" + distance + ", departure_airport_id=" + departure_airport_id + ", arrival_airport_id=" + arrival_airport_id + ", company_id=" + company_id + ", aircraft_id=" + aircraft_id + ", flight_cost__id=" + flight_cost_id + '}';
    }
    
    public Integer getFlight_cost_id() {
        return flight_cost_id;
    }

    public void setFlight_cost_id(Integer flight_cost_id) {
        this.flight_cost_id = flight_cost_id;
    }

    public Integer getFlight_id() {
        return flight_id;
    }

    public void setFlight_id(Integer flight_id) {
        this.flight_id = flight_id;
    }
    
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Timestamp getDeparture_time() {
        return departure_time;
    }

    public void setDeparture_time(Timestamp departure_time) {
        this.departure_time = departure_time;
    }

    public Timestamp getArrival_time() {
        return arrival_time;
    }

    public void setArrival_time(Timestamp arrival_time) {
        this.arrival_time = arrival_time;
    }

    public Integer getDistance() {
        return distance;
    }

    public void setDistance(Integer distance) {
        this.distance = distance;
    }

    public Integer getDeparture_airport_id() {
        return departure_airport_id;
    }

    public void setDeparture_airport_id(Integer departure_airport_id) {
        this.departure_airport_id = departure_airport_id;
    }

    public Integer getArrival_airport_id() {
        return arrival_airport_id;
    }

    public void setArrival_airport_id(Integer arrival_airport_id) {
        this.arrival_airport_id = arrival_airport_id;
    }

    public Integer getCompany_id() {
        return company_id;
    }

    public void setCompany_id(Integer company_id) {
        this.company_id = company_id;
    }

    public Integer getAircraft_id() {
        return aircraft_id;
    }

    public void setAircraft_id(Integer aircraft_id) {
        this.aircraft_id = aircraft_id;
    }
    
    public void insert() throws SQLException, Exception {
        try (PreparedStatement s = DbContext.getConnection().prepareStatement("INSERT INTO flights (flight_name, departure_time, arrival_time, distance, departure_airport_id, arrival_airport_id, company_id, aircraft_type_id, flight_cost_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS)) {
            s.setString(1, name);
            s.setTimestamp(2, departure_time);
            s.setTimestamp(3, arrival_time);
            s.setInt(4, distance);
            s.setInt(5, departure_airport_id);
            s.setInt(6, arrival_airport_id);
            s.setInt(7, company_id);
            s.setInt(8, aircraft_id);
            s.setInt(9, flight_cost_id);

            s.executeUpdate();

            try (ResultSet r = s.getGeneratedKeys()) {
                r.next();
                flight_id = r.getInt(1);
            }
        }
    }

    public void update() throws SQLException, Exception {
        if (flight_id == null) {
            throw new IllegalStateException("flight_id is not set");
        }
        try (PreparedStatement s = DbContext.getConnection().prepareStatement("UPDATE flights SET flight_name=?, departure_time=?, arrival_time=?, distance=?, departure_airport_id=?, arrival_airport_id=?, company_id=?, aircraft_type_id=?, flight_cost_id=?  WHERE flight_id=?")) {
            s.setString(1, name);
            s.setTimestamp(2, departure_time);
            s.setTimestamp(3, arrival_time);
            s.setInt(4, distance);
            s.setInt(5, departure_airport_id);
            s.setInt(6, arrival_airport_id);
            s.setInt(7, company_id);
            s.setInt(8, aircraft_id);
            s.setInt(9, flight_cost_id);
            
            s.setInt(10, flight_id);

            s.executeUpdate();
        }
    }
    /**
     * Update aircraft_type_id on flight w/ flight_id
     * @param flight_id 
     * @param new_aircraft_type_id 
     * @throws SQLException
     * @throws Exception
     */
    public static void updateAircraftType(int flight_id, int new_aircraft_type_id) throws SQLException, Exception {
        if(!Flight.exists(flight_id))
            throw new Exception("Flight w/ this id don't exists");
        try (PreparedStatement s = DbContext.getConnection().prepareStatement("UPDATE flights SET aircraft_type_id=? WHERE flight_id=?")) {
            s.setInt(1, new_aircraft_type_id);
            s.setInt(2, flight_id);

            s.executeUpdate();
        }
    }

    public void delete() throws SQLException, Exception {
        if (flight_id == null) {
            throw new IllegalStateException("flight_id is not set");
        }
        
        try (PreparedStatement s = DbContext.getConnection().prepareStatement("DELETE FROM flights WHERE flight_id = ?")) {
            s.setInt(1, flight_id);

            s.executeUpdate();
        }
    }
    /**
     * Check if flight w/ this id exists
     * @param flight_id 
     * @throws SQLException
     * @return boolean if flight exists
     */
    public static boolean exists(int flight_id) throws SQLException{
        PreparedStatement s = DbContext.getConnection().prepareStatement("SELECT * from flights WHERE flight_id=?");
        s.setInt(1, flight_id);
        return s.executeQuery().next();
    }
}
