# https://www.youtube.com/watch?v=YXPyB4XeYLA
from tkinter import Tk,Entry,Label,messagebox, Button,Toplevel,Frame,LabelFrame
import os
from PIL import ImageTk,Image
import sqlite3
#from tkinter import messagebox

# get the paths for images, os independent! (I think)
dirname = os.path.dirname(__file__)
imagesFolder = os.path.join(dirname, "images")

root = Tk()
root.title("whats a database?")
root.iconbitmap(os.path.join(imagesFolder,"icon.ico"))
# Set the original window size
#root.geometry("400x400")

# Databases

# Create a database or connect to one
conn = sqlite3.connect("Address_Book.db")

# create cursor
cur = conn.cursor()

# Create table
cur.execute("""CREATE TABLE IF NOT EXISTS addresses (
    first_name text,
    last_name text,
    address text,
    city text,
    state text,
    zipcode integer
    )""")

# Editing an existing record.
def edit( oid ):
    editor = Toplevel()
    editor.title("Editing Record: " + str(oid))
    editor.iconbitmap(os.path.join(imagesFolder,"icon.ico"))

# Create Function to Delete a Record
def delete( oid ):
    global frame

    # Display a confirmation box
    response = messagebox.askyesno("Confirmation", "Are you sure you want to delete record ID: " + str(oid))
    if response == 1:

        # Create a database connection or connect to one
        conn = sqlite3.connect("Address_Book.db")

        # create cursor
        cur = conn.cursor()

        cur.execute("DELETE FROM addresses  WHERE oid=:oid", { 'oid':oid })

        # Commit Changes
        conn.commit()

        # Close Connection
        conn.close()

        frame.grid_forget()
        query()

# Create submit function
def submit():
    # Create a database connection or connect to one
    conn = sqlite3.connect("Address_Book.db")

    # create cursor
    cur = conn.cursor()

    # Insert into table
    cur.execute("INSERT INTO addresses VALUES (:f_name, :l_name, :address, :city, :state, :zipcode)",
        {
            'f_name':f_name.get(),
            'l_name':l_name.get(),
            'address':address.get(),
            'city':city.get(),
            'state':state.get(),
            'zipcode':zipcode.get()
        })

    # Commit Changes
    conn.commit()

    # Close Connection
    conn.close()

    # Clear the text boxes
    f_name.delete(0,END)
    l_name.delete(0,END)
    address.delete(0,END)
    city.delete(0,END)
    state.delete(0,END)
    zipcode.delete(0,END)

# Create a query function

def query():
    global frame
    # Create a Frame to hold all the records.
    frame = LabelFrame( root, text="Results", padx=5, pady=5) # Optional: text="This is my frame..."
    frame.grid(row=8, column=0, columnspan=2)

    # Create a database connection or connect to one
    conn = sqlite3.connect("Address_Book.db")

    # create cursor
    cur = conn.cursor()

    # Select from table
    cur.execute("SELECT *, oid FROM addresses")
    records = cur.fetchall()
    #print(records) # Show in the terminal

    # Declare the variable to print out
    print_records = ""
    counter = 0
    # Loop through results
    for record in records:
        global record_id
        #print_records += str(record) + "\n" # Prints the whole record
        record_id = record[6]
        print_records += str(record[0]) + " " +  str(record[1]) + " " + "\t ID: " + str(record_id) + "\t"
        queryLabel = Label(frame, text=print_records)

        # Edit Button
        edit_btn = Button(frame, text="Edit",command=lambda: edit(record_id))

        # Adding some space here
        spacer = Label(frame, text="\t")

        # Delete Button
        del_btn = Button(frame, text="DELETE",command=lambda: delete(record_id))

        queryLabel.grid(row=counter, column=0)
        edit_btn.grid(row=counter, column=1)
        spacer.grid(row=counter, column=2)
        del_btn.grid(row=counter, column=3)
        # Incremnet the counter to put next record on a new line
        counter += 1
        # Clear out print_records for the next one
        print_records = ""


    # Commit Changes
    conn.commit()

    # Close Connection
    conn.close()

    return

# Text boxes that correspond to the database
f_name = Entry(root, width=30)
f_name.grid(row=0, column=1, padx=20, pady=(10,0))

l_name = Entry(root, width=30)
l_name.grid(row=1, column=1)

address = Entry(root, width=30)
address.grid(row=2, column=1)

city = Entry(root, width=30)
city.grid(row=3, column=1)

state = Entry(root, width=30)
state.grid(row=4, column=1)

zipcode = Entry(root, width=30)
zipcode.grid(row=5, column=1)

# Create text box labels
f_name_label = Label(root, text="First Name: ")
f_name_label.grid(row=0, column=0, pady=(10,0))

l_name_label = Label(root, text="Last Name: ")
l_name_label.grid(row=1, column=0)

address_label = Label(root, text="Address: ")
address_label.grid(row=2, column=0)

city_label = Label(root, text="City: ")
city_label.grid(row=3, column=0)

state_label = Label(root, text="State: ")
state_label.grid(row=4, column=0)

zipcode_label = Label(root, text="Zipcode: ")
zipcode_label.grid(row=5, column=0)

# Create submit button
submit_btn = Button(root, text="Add Record to Database", command=submit)
submit_btn.grid(row=6, column=0, columnspan=2, pady=10, padx=10, ipadx=100)

# Create a Query Button
query_btn = Button(root, text="Show Records", command=query)
query_btn.grid(row=7, column=0, columnspan=2, pady=10, padx=10,ipadx=137)

# Commit Changes
conn.commit()

# Close Connection
conn.close()

# and as always... the mainloop
root.mainloop()