# Louie Ford Coursework Task 1

# 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="Album Searcher",layout="grid", bg="grey")

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

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

# this funciton will connect to the database and fetch all of the genres in the genre table and display them into the ListBox
def populateGenreList():
    myconnection = sqlite3.connect("chinook.db")
    mycursor = myconnection.cursor()
    mycursor.execute("Select genres.Name from genres order by genres.Name ASC")

    allGenre = mycursor.fetchall()
    for aGenre in allGenre:
        # adding the first value from the tuple into the ListBox
        # in this case, it is the genre name
        listboxgenre.append(aGenre[0])
    mycursor.close()
    myconnection.close()

# calling the function so the ListBox is filled and user can select an option
populateGenreList()

# this function is using an sql query to link the 'genres' and 'albums' tables from the database
def commit(genrename):
    mycursor.execute("""
    SELECT DISTINCT albums.Title, genres.Name
    FROM albums
    INNER JOIN tracks  ON tracks.AlbumId = albums.AlbumId
    INNER JOIN genres ON genres.GenreId = tracks.GenreId
    WHERE genres.Name = ? """,
        (genrename.value,))
    # this list box will show all of the albums that are part of the chosen genre
    albumlist = 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 Tracks!", command=commit, args=[listboxgenre],grid=[5,5])

# 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


#display the app on the screen for the user to use
app.display()