# Louie Ford Coursework Task 2

# Importing everything needed to make the program
import sqlite3
from guizero import App, PushButton, ListBox, TextBox, Text
from typing import List

# creating a connection with the database and a cursor
myconnection = sqlite3.connect("chinook.db")
mycursor = myconnection.cursor()

# making an app for the program to go onto
app = App(title="Customer Invoice Searcher",layout="grid", bg="grey")

# Adding text to the top of the page giving instructions to the user
text = Text(app, "Select a Customer and a Date here : ", grid=[1,0])

# making a listbox so the user can chose from the variety of different genres
listboxcustomer = ListBox(app, items=[], grid=[1,1], scrollbar=True)

# creating a function that fetches all customers from the table 'Customers'
# DISTINCT will only pick unique names, so the same person doesn't appear twice

def populateCustomerList():
    myconnection = sqlite3.connect("chinook.db")
    mycursor = myconnection.cursor()
    mycursor.execute("""SELECT  DISTINCT customers.FirstName FROM customers""")

    allCustomers = mycursor.fetchall()
    for aCustomer in allCustomers:
        # [0] adds the first item in the tuple from each customer
        # in this case, it is the first name of the customer
        listboxcustomer.append(aCustomer[0])
    mycursor.close()
    myconnection.close()

# calling the function so the ListBox is filled with customer names
populateCustomerList()

# commit function that will join invoice and customer tables
# this is so we can get all of the invoices that are associated with the customer name
def commit(listboxcustomer):
    mycursor.execute("""
    SELECT invoices.total
    FROM customers
    INNER JOIN invoices ON invoices.CustomerId = customers.CustomerId
    WHERE FirstName = ? """,
        (listboxcustomer.value,))
    # this list box will show all of the albums that are part of the chosen genre
    invoicelist = ListBox(app, items=([x[0] for x in mycursor.fetchall()]),grid=[0,3], scrollbar=True)


# submit button so the user can confirm they want to chose that genre and then outputs all of the albums
submit = PushButton(app, text="Generate Invoices!", command=commit, args=[listboxcustomer,],grid=[4,4])

# highlight and lowlight functions added to the pushbuttons to make it clear to the user which button they are about to press
def highlight():
    quitApp.bg = "red"
def lowlight():
    quitApp.bg = "grey"

def highlight2():
    submit.bg = "green"
def lowlight2():
    submit.bg = "grey"

# pushbutton that closes the app when pressed
quitApp = PushButton(app, command=quit, text="Quit App",grid=[0,5])

# using events to determine if the pushbutton is highlighted or not
quitApp.when_mouse_enters = highlight
quitApp.when_mouse_leaves = lowlight
submit.when_mouse_enters = highlight2
submit.when_mouse_leaves = lowlight2

# calling the display function so the app runs on screen for the user to see
app.display()