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 javax.sql.DataSource;
import org.postgresql.ds.PGSimpleDataSource;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.HashMap;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Marcel
 */
/**
* data row gateway for seat stuff
*/
public class SeatCount {
    private int aircraft_type_id, count, class_id;

    public int getAircraft_type_id() {
        return aircraft_type_id;
    }

    public void setAircraft_type_id(int aircraft_type_id) {
        this.aircraft_type_id = aircraft_type_id;
    }

    public int getCount() {
        return count;
    }

    public void setCount(int count) {
        this.count = count;
    }

    public int getClass_id() {
        return class_id;
    }

    public void setClass_id(int class_id) {
        this.class_id = class_id;
    }
    
    public void delete() throws SQLException{
        PreparedStatement t = DbContext.getConnection().prepareStatement("DELETE FROM seat_count_in_class WHERE aircraft_type_id=?");
        t.setInt(1, aircraft_type_id);
        t.executeUpdate();
        
        t = DbContext.getConnection().prepareStatement("DELETE FROM seats WHERE aircraft_type_id=?");
        t.setInt(1, aircraft_type_id);
        t.executeUpdate();
    }
    
    public void insert() throws SQLException, Exception{
        PreparedStatement s = DbContext.getConnection().prepareStatement("INSERT INTO seat_count_in_class (\"count\", aircraft_type_id, class_id) VALUES (?, ?, ?);");
        s.setInt(1, count);
        s.setInt(2, aircraft_type_id);
        s.setInt(3, class_id);
        s.executeUpdate();
        
        for(int j=0;j<count;j++){
            PreparedStatement ps = DbContext.getConnection().prepareStatement("INSERT INTO seats (aircraft_type_id, class_id, seat_number) VALUES (?, ?, ?);");
            ps.setInt(1, aircraft_type_id);
            ps.setInt(2, class_id);
            ps.setInt(3, j);
            ps.executeUpdate();
        }
    }
    /**
    * check if for this ticket seatExists seat
    * @return true if there is seat for this ticket
    */
    public static boolean seatExists(Integer ticket_id){
        try (PreparedStatement s = DbContext.getConnection().prepareStatement("SELECT EXISTS(SELECT * FROM bought_tickets WHERE canceled=false AND bought_ticket_id=? AND seat_id IS NOT NULL) as b")) {
            s.setInt(1, ticket_id);
            ResultSet rs = s.executeQuery();
            while(rs.next()){
                return rs.getBoolean("b");
            }
        }catch(Exception e){
            System.out.println(e);
        }
        return false;
    }
    /**
    * selectAll free seats
    * @param class_id class where find free seats
    * @param aircraft_id aircraft where find free seats
    * @param flight_id flight where find free seats
    * @return List of Maps where keys are column names related to seats
    */
    public static Map<String, String> selectFree(Integer class_id, Integer aircraft_id, Integer flight_id) throws SQLException, Exception{
        Map<String, String> res=new HashMap<>();
        if(!Class.exists(class_id))
            throw new Exception("Class w/ this id don't exists");
        if(!AircraftType.exists(aircraft_id))
            throw new Exception("Aircraft w/ this id don't exists");
        if(!Flight.exists(class_id))
            throw new Exception("Class w/ this id don't exists");
        try (PreparedStatement s = DbContext.getConnection().prepareStatement("SELECT * FROM seats "
                + "WHERE NOT EXISTS(SELECT * FROM bought_tickets WHERE bought_tickets.seat_id=seats.seat_id AND bought_tickets.flight_id=? AND canceled=false)"
                + "AND class_id=? AND aircraft_type_id=?")) {
            s.setInt(1, flight_id);
            s.setInt(2, class_id);
            s.setInt(3, aircraft_id);
            ResultSet rs = s.executeQuery();
            while(rs.next()){
                res.put(rs.getString("seat_id"), rs.getString("seat_number"));
            }
        }catch(Exception e){
            System.out.println(e);
        }
        return res;
    }
    /**
    * count booked seats
    * @param class_id where find booked seats
    * @param aircraft_id where find booked seats
    * @param flight_id where find booked seats
    * @return count of booked seats
    */
    public static int countBooked(Integer class_id, Integer aircraft_id, Integer flight_id) throws SQLException, Exception{
        if(!Class.exists(class_id))
            throw new Exception("Class w/ this id don't exists");
        if(!AircraftType.exists(aircraft_id))
            throw new Exception("Aircraft w/ this id don't exists");
        if(!Flight.exists(class_id))
            throw new Exception("Class w/ this id don't exists");
        try (PreparedStatement s = DbContext.getConnection().prepareStatement("SELECT count(*) FROM bought_tickets WHERE flight_id=? AND class_id=? AND canceled=false")) {
            s.setInt(1, flight_id);
            s.setInt(2, class_id);
            ResultSet rs = s.executeQuery();
            while(rs.next()){
                return rs.getInt(1);
            }
        }catch(Exception e){
            System.out.println(e);
        }
        return 0;
    }
    /**
    * check if seat w/ this id exists
    * @param seat_id seat_id
    * @throws SQLException
    * @return boolean if seat exists
    */
    public static boolean exists(int seat_id) throws SQLException {
        PreparedStatement s = DbContext.getConnection().prepareStatement("SELECT * from seats WHERE seat_id=?");
        s.setInt(1, seat_id);
        return s.executeQuery().next();
    }
    /**
    * check if seat w/ this id is valid for this ticket 
    * (if it is in correct class, aircraft, and it is not reserved in that flight)
    * @param seat_id seat_id
    * @throws SQLException
    * @return boolean if seat is valid
    */
    public static boolean valid_seat(int seat_id, int ticket_id) throws SQLException, Exception {
        BoughtTicket b = new BoughtTicket();
        b.findById(ticket_id);
        PreparedStatement s = DbContext.getConnection().prepareStatement("SELECT * FROM seats "
                + "WHERE NOT EXISTS(SELECT * FROM bought_tickets WHERE bought_tickets.seat_id=seats.seat_id AND bought_tickets.flight_id=? AND canceled=false)"
                + "AND class_id=? AND aircraft_type_id=? AND seat_id=?");
        s.setInt(1, b.getFlight_id());
        s.setInt(2, b.getClass_id());
        s.setInt(3, b.getAircraft_type_id());
        s.setInt(4, seat_id);
        return s.executeQuery().next();
    }
    /**
    * rebook all seats for this flight to new aircraft
    * @param new_aircraft_id new aircraft type id
    * @param flight_id flight_id, where rebook seats
    * @throws SQLException
    * @return Boolean if seat is valid
    */
    public static void changeBookedSeats(int new_aircraft_id, int flight_id) throws SQLException {
        PreparedStatement s = DbContext.getConnection().prepareStatement("UPDATE bought_tickets SET "
                + "seat_id=random_nonbooked_seat_from_aircraft(?, ?, bought_ticket_id) WHERE flight_id=? AND seat_id IS NOT NULL");
        s.setInt(1, flight_id);
        s.setInt(2, new_aircraft_id);
        s.setInt(3, flight_id);
        s.executeUpdate();
    }
}
