"""
AIRLINE RESERVATION SYSTEM
DEVELOPERS
    Stephen Tafoya
    Ryan Hamilton

MAIN PROJECT FILE - RUNS ENTIRE PROGRAM
"""

import sqlite3  # ST Import database management


class AirDB(object):        # ST
    """
    class DB(db)
        documentation:
            class that handles main database management,
            will handle all database operations
    """
    # Initializer

    def __init__(self, db):     # ST
        con = self.connectDB(db)    # ST
        if con is not None:     # ST
            self.connection = con   # ST
            self.cursor = self.connection.cursor()  # ST
            self.setupDB()      # ST

    def connectDB(self, db):    # ST
        """
        connectDB
            -- create simplified function with error handling
            -- to be used externally
            -- returns a connection or None
        """
        try:
            conn = sqlite3.connect(db)  # ST connect to database specified
            return conn  # ST return connection
        except sqlite3.Error as e:  # ST catch all sqlite3 errors
            print(e)  # ST display error

        return None  # ST return None

    def setupDB(self):  # ST makes sure the db has correct table

        self.cursor.execute('''CREATE TABLE IF NOT EXISTS AIRPORTS (
            id integer PRIMARY KEY  AUTOINCREMENT,
            country text NOT NULL,
            state text NOT NULL,
            airport text NOT NULL
            )''')   # ST

    def insertAirport(self, query):     # ST
        """
            adds a new index to airports
            should be tuple of format
                (country, state, airport)
        """
        datastr = '''INSERT INTO AIRPORTS(country,state,airport) VALUES(?,?,?)'''

        try:
            self.cursor.execute(datastr, query)     # ST attempt to querry
        except sqlite3.Error as e:                  # ST catch error
            print(e, "failed")                      # ST display error

    def insertAirports(self, table):            # ST calls insertAirport with table
        for query in table:                     # ST for each element in table
            self.insertAirport(query)           # ST insert airport

    def loadCountries(self):  # RH loads all countries in database
        self.cursor.execute('SELECT country FROM AIRPORTS')     # RH
        all_countries = self.cursor.fetchall()      # RH
        countries = []                              # RH
        for i in range(0, len(all_countries)):      # RH
            temp_var = all_countries[i][0]          # RH
            countries.append(temp_var)              # RH
        return countries                            # RH

    def loadStates(self, country):  # RH
        # Loads all states in database that are in a given country
        self.cursor.execute('SELECT state FROM AIRPORTS WHERE country = {x}'.   # RH
                            format(x="'" + country + "'"))              # RH
        all_states = self.cursor.fetchall()                 # RH
        states = []
        for i in range(0, len(all_states)):                 # RH
            temp_var = all_states[i][0]                     # RH
            states.append(temp_var)                         # RH
        return states                                       # RH

    def loadAirports(self, state):  # RH
        # Loads all airports in database that are in a given state
        self.cursor.execute('SELECT airport FROM AIRPORTS WHERE state = {x}'.   # RH
                            format(x="'" + state + "'"))
        all_airports = self.cursor.fetchall()   # RH
        airports = []                           # RH
        for i in range(0, len(all_airports)):   # RH
            temp_var = all_airports[i][0]       # RH
            airports.append(temp_var)           # RH
        return airports                         # RH

    def findAirport(self, airport):  # ST
        self.cursor.execute("SELECT id FROM AIRPORTS WHERE airport = ?", (airport,))
        val = self.cursor.fetchall()  # ST
        if val[0][0] >= 0:  # ST
            return val[0][0]  # ST

        return -1  # ST

    def close(self):                    # ST
        self.connection.commit()        # ST sync all changes
        self.connection.close()         # ST close db


class FlightDB(object):         # RH
    # Initializer

    def __init__(self, db):     # RH
        con = self.connectDB(db)    # RH
        if con is not None:         # RH
            self.connection = con   # RH
            self.cursor = self.connection.cursor()  # RH
            self.setupDB()          # RH

    def connectDB(self, db):        # RH
        """
        connectDB
            -- create simplified function with error handling
            -- to be used externally
            -- returns a connection or None
        """
        try:
            conn = sqlite3.connect(db)  # RH connect to database specified
            return conn  # RH return connection
        except sqlite3.Error as e:  # RH catch all sqlite3 errors
            print(e)  # RH display error

        return None  # RH return None

    def setupDB(self):  # RH

        self.cursor.execute('''CREATE TABLE IF NOT EXISTS FLIGHTS (
            id integer PRIMARY KEY   AUTOINCREMENT,
            start int NOT NULL,
            stop int NOT NULL,
            flight_spec text NOT NULL,
            address text NOT NULL,
            billing_address text,
            cost real NOT NULL,
            payment_type int NOT NULL,
            payment_card text
            )''')   # ST

        self.cursor.execute('''CREATE TABLE IF NOT EXISTS PASSENGERS (
            id integer PRIMARY KEY  AUTOINCREMENT,
            flight_id integer NOT NULL,
            name text NOT NULL,
            bags integer NOT NULL,
            seating text NOT NULL
            )''')   # ST

    def nextIndex(self):
        self.cursor.execute("SELECT id FROM FLIGHTS")
        var = self.cursor.fetchall()
        return len(var)

    def insertFlight(self, query):  # RH
        # adds a new index to the flights table in the database.
        data = '''INSERT INTO FLIGHTS(start, stop, flight_spec, address, billing_address, cost, payment_type, payment_card) VALUES(?, ?, ?, ?, ?, ?, ?, ?)'''

        try:            # RH
            self.cursor.execute(data, query)    # RH
        except sqlite3.Error as e:              # RH
            print(e, "failed")                  # RH

    def insertFlights(self, table):             # RH
        for query in table:                     # RH
            self.insertFlight(query)            # RH

    def insertPassenger(self, query):           # RH
        # adds a new index to the passangers table in the database
        data = '''INSERT INTO PASSENGERS(flight_id, name, bags, seating) VALUES(?, ?, ?, ?)'''
        try:
            self.cursor.execute(data, query)    # RH
        except sqlite3.Error as e:              # RH
            print(e, "failed")                  # RH

    def insertPassangers(self, table):          # RH
        for query in table:                     # RH
            self.insertPassenger(query)         # RH

    def loadFlights(self):                      # RH
        # pulls flights table from database and prints it for display
        self.cursor.execute('SELECT FLIGHTS')   # RH
        all_flights = self.cursor.fetchall()    # RH
        print(all_flights)                      # RH

    def loadPassangers(self):                   # RH
        # pulls passangers table from database and prints it for display
        self.cursor.execute('SELECT PASSENGERS')    # RH
        all_passengers = self.cursor.fetchall()     # RH
        print(all_passengers)                   # RH

    def close(self):                            # RH
        self.connection.commit()                # RH
        self.connection.close()                 # RH


def main():
    pass


if __name__ == "__main__":
    main()
