# -*- coding: utf-8 -*-
#
#*********************************************************************
# Name:        FlightdelaySelect.py
# Created:     5/18/2016
# Modified:    6/19/2016
# Created By:  Peter McCutcheon
#
#---------------------------------------------------------------------
# Description:
#
#   This Python script reads the flight data database to obtain the
#   airports with the greatest flight delay minutes.  The program uses 
#   SQL to read the database and generates a bar graph of the 12 airports
#   with the most delay.
#
#**********************************************************************

import csv
import sqlite3
from matplotlib import pyplot as plt

#
#   Connect to the FlightData database and create a cursor for SQL operations.
#
conn = sqlite3.connect('../data/flightdata.db')
curs = conn.cursor()          

#
#   Set up a couple of lists to be passed to matplotlib for the bar graph.
#
airport = []
delay = []
ap_code = []
apinfo = []
tmpHold = []
#
#   Set up the SQL select statement to get the flight data from the database.
#   We are going group the data by airport then sum on the departure delay for
#   airport and to a descending sort on that sum. 
#

selStr = """select a.originairport, a.origin, b.airport_desc, sum(a.departdelay) as dd, count(
*) from flightdelay as a, airportid as b where a.originairport = b.airportid group by a.originairport order by dd desc limit 12;"""

#
#   Fire off the actual SQL select.
#
try:
    curs.execute(selStr)
except Exception as er:
    print("Sqlite Error " + er.message)
#
#   Read through the rows that were returned from the DB and set up
#   the lists to pass to matplotlib.
#

result = curs.fetchall()
for row in result:
    #record = list(row)
    airport.append(int(row[0]))
    ap_code.append(row[1])
    delay.append(int(row[3]))
    tmpHold = [row[1], row[2]]
    apinfo.append(tmpHold)    
    print(airport,delay,ap_code)        
     
#
#   Open a file to write Airport information (three letter code and description)
#
print(apinfo)
with open('../data/apinfofile.txt','wt') as APinfofile:
    csvout = csv.writer(APinfofile)
    csvout.writerows(apinfo)

#
#   Set up the objects for the bar graph.  On the bar method the arguments are:
#       1st: Left side of bar must a list of scalars (numbers) Here we are
#            counting the number of airports we return.
#       2nd: The height of the bars agian a scalar list (numbers).  Here we are
#            using the sum of the airport delay.
#       3rd: Width of the individual bars.
#       4th: Where to put the bar label.
#       5th: The color of the bars.
#
plt.bar([i for i, _ in enumerate(ap_code)], delay, width=0.4, align='center', color='blue')
plt.title("Airport Delay Totals for 01/2016")
#
#   These are the labels for the bars.  Called ticks in matplotlib.  Here we count
#   them and pass the list of labels generated by our DB SQL select.
#
plt.xticks([i for i, _ in enumerate(ap_code)], ap_code)
plt.ylabel('Minutes')

#
#   Time to show the actual bar graph.
#   Then clean everything up.
#
plt.savefig('../img/delays.png')
plt.show() 
curs.close()
conn.close()