import tkinter as tk
import mysql.connector

# Connect to database
database = mysql.connector.connect(
  host="localhost",
  user="root",
  password="YaNa2203",
)

# Create Window
window = tk.Tk()
window.title("Service Finder")

# Window properties
window.resizable(width=False, height=False)

## Widgets


# Radio options
gpInt = IntVar()
radioGP = tk.Checkbutton(window, text="Doctors", state='disabled', variable=gpInt)
radioGP.grid(row=2, column=0, sticky=tk.W)

dentistInt = IntVar()
radioDentist = tk.Checkbutton(window, text="Dentist", state='disabled', variable=dentistInt)
radioDentist.grid(row=3, column=0, sticky=tk.W)

opticianInt = IntVar()
radioOptician = tk.Checkbutton(window, text="Optician", state='disabled', variable=opticianInt)
radioOptician.grid(row=4, column=0, sticky=tk.W)

schoolInt = IntVar()
radioSchool = tk.Checkbutton(window, text="School", state='disabled', variable=schoolInt)
radioSchool.grid(row=5, column=0, sticky=tk.W)

nurseryInt = IntVar()
radioNursery = tk.Checkbutton(window, text="Nursery", state='disabled', variable=nurseryInt)
radioNursery.grid(row=6, column=0, sticky=tk.W)

# Search
def loadDatabase():
    # Get script
    fd = open('Find Distance.sql', 'r')
    sqlFile = fd.read()
    fd.close()

    cursor = database.cursor(buffered=True)

    # Set the correct database
    database.cursor().execute("USE project;")

    text = postcodeField.get()
    text = text.replace(" ", "")
    text = text[:4] + " " + text[4:]
    print(text)
    # Replace the constants in the script
    sqlFile = sqlFile.replace('$POSTCODE$', text)
    sqlFile = sqlFile.replace('$CATEGORY1$', str(gpInt))
    sqlFile = sqlFile.replace('$CATEGORY2$', str(dentistInt))
    sqlFile = sqlFile.replace('$CATEGORY3$', str(opticianInt))
    sqlFile = sqlFile.replace('$CATEGORY4$', str(schoolInt))
    sqlFile = sqlFile.replace('$CATEGORY5$', str(nurseryInt))

    # Execute the script
    result = cursor.execute(sqlFile)

    # Clear list
    listbox1.delete(0,tk.END)
    listbox2.delete(0,tk.END)
    listbox3.delete(0,tk.END)
    listbox4.delete(0,tk.END)

    # Insert database into list
    for x in cursor:
        listbox1.insert(tk.END, x[0])
        listbox2.insert(tk.END, x[1])
        listbox3.insert(tk.END, x[2])
        listbox4.insert(tk.END, str(x[3]) + " miles")

noticeText = tk.StringVar()
noticeLabel = tk.Label(window, textvariable=noticeText)
noticeLabel.grid(row=7, column=0, sticky=tk.W)
noticeText.set("* required field")
# Search button
searchButton = tk.Button(text="Search", command=loadDatabase, state='disabled')
searchButton.grid(row=8, column =0, columnspan=3, sticky=tk.W)

# Postcode
postcodeText = tk.StringVar()
postcodeLabel = tk.Label(window, textvariable=postcodeText)
postcodeLabel.grid(row=0, column=0, sticky=tk.W)
postcodeText.set("Postcode *:")

# When the postcode text changes
def postChange(text):
    text = text.replace(" ", "")
    if len(text) != 7:
        searchButton.config(state='disabled')
    else:
        searchButton.config(state='normal')

# Postcode Button
postcodeFieldText = tk.StringVar()
postcodeFieldText.trace("w", lambda name, index, mode, postcodeFieldText=postcodeFieldText: postChange(postcodeFieldText.get()))
postcodeField = tk.Entry(window, bd = 5, textvariable=postcodeFieldText)
postcodeField.grid(row=0, column=1, sticky=tk.W)

# On age change
def ageChange(text):
    # Check the age, and enable the appropriate buttons
    if text != "":
        radioGP.config(state='normal')
        radioDentist.config(state='normal')
        radioOptician.config(state='normal')
        if int(text) < 4:
            radioNursery.config(state='normal')
            radioSchool.config(state='normal')
            radioSchool.config(state='disabled')
        elif int(text) <= 16:
            radioNursery.config(state='disabled')
            radioSchool.config(state='normal')
        else:
            radioSchool.config(state='disabled')
            radioNursery.config(state='disabled')
            var.set(99)
    else:
        radioSchool.config(state='disabled')
        radioNursery.config(state='disabled')
        radioGP.config(state='disabled')
        radioDentist.config(state='disabled')
        radioOptician.config(state='disabled')
        var.set(99)

ageText = tk.StringVar()
ageLabel = tk.Label(window, textvariable=ageText)
ageLabel.grid(row=1, column=0, sticky=tk.W)
ageText.set("Age *:")

ageFieldText = tk.StringVar()
ageFieldText.trace("w", lambda name, index, mode, ageFieldText=ageFieldText: ageChange(ageFieldText.get()))
ageField = tk.Entry(window, bd = 5, textvariable=ageFieldText)
ageField.grid(row=1, column=1, sticky=tk.W)

# Canvas for the listboxes
frame = tk.Frame(window, width=400, height=400)
frame.grid(row=0, column=3, rowspan=20)

canvas = tk.Canvas(frame, width=400, height=400, scrollregion=(0,0,500,500), confine=False)

canvas.pack(side=tk.LEFT,expand=True,fill=tk.BOTH)

# SQL table
listbox1 = tk.Listbox(canvas, height=32)
listbox1.grid(column=0, row = 0)
listbox2 = tk.Listbox(canvas, height=32)
listbox2.grid(column=1, row = 0)
listbox3 = tk.Listbox(canvas, height=32)
listbox3.grid(column=2, row = 0)
listbox4 = tk.Listbox(canvas, height=32)
listbox4.grid(column=3, row = 0)

# Select the corresponding box on click
def lb1Select(e):
    if len(listbox1.curselection()):
        listbox2.select_set(listbox1.curselection()[0])
listbox1.bind('<<ListboxSelect>>', lb1Select)

# Start window
window.mainloop()