from tkinter import *
from tkinter import ttk
import pandas as pd
import sqlalchemy


##########
global xtree
global x_df
global l_cho
global dtree

##########

# Create the engine to connect to the PostgreSQL database
engine = sqlalchemy.create_engine('mariadb+mariadbconnector://harold:KA0791te@localhost:3306/books?charset=utf8mb4')
# Read data from SQL table
df = pd.read_sql_table('book', engine)
aut_df = pd.read_sql_query('SELECT DISTINCT author FROM authors', engine)
pub_df = pd.read_sql_query('SELECT DISTINCT publisher FROM publishers', engine)
ser_df = pd.read_sql_query('SELECT DISTINCT series FROM series', engine)
tit_df = pd.read_sql_query('SELECT DISTINCT title FROM book', engine)
a_df = tit_df.join(aut_df, on=None, how='left', lsuffix='', rsuffix='').fillna('void')
b_df = a_df.join(pub_df, on=None, how='left', lsuffix='', rsuffix='').fillna('void')
c_df = b_df.join(ser_df, on=None, how='left', lsuffix='', rsuffix='').fillna('void')
column_names = ["isbn", "title", "series", "author", "publisher", "year", "location", "cover", "description"]
main_df = c_df.reindex(columns=column_names)


##########

window = Tk()
window.geometry("1200x800")
window.wm_title("My Book Library")

frm_1 = Frame(window)
frm_2 = Frame(window)
frm_3 = Frame(window)
frm_3.place(x=0, y=0, height=20, width=800)


def detail_tree(tree_item):
    clear_all()
    frm_2.place(x=10, y=20, height=100, width=800)
    var1 = tree_item[0]
    column_names = ["title", "series", "author", "year"]
    sql_s = main_df.loc[main_df[l_cho].str.contains(var1)]
    sql_s = sql_s.reindex(columns=column_names)
    sql_s_col = sql_s.columns.values
    dtree = ttk.Treeview(frm_2, show='headings')
    dtree.delete(*dtree.get_children())
    dtree.place(x=0, y=0)
    dtree.bind('<ButtonRelease-1>', detail_picked)
    # all the column name are generated dynamically.
    dtree["columns"] = sql_s_col
    # generating for loop to create columns and give heading to them through df_col var.
    for x in range(len(sql_s_col)):
        dtree.column(x, width=200)
        dtree.heading(x, text=sql_s_col[x])
    # generating for loop to print values of dataframe in treeview column.
    for index, row in sql_s.iterrows():
        dtree.insert('', 0, text=index, values=list(row))


def detail_picked():
    for selected_item in dtree.selection():
        item = dtree.item(selected_item)
        dtree.item = item['values']
        book_detail()


def book_detail():
    print("book detail")


def item_picked():
    # print ("item_picked")
    for selected_item in xtree.selection():
        item = xtree.item(selected_item)
        tree_item = item['values']
        detail_tree(tree_item)


def x_tree():
    # use of treeview
    global x_df, j
    clear_all()
    if l_cho == "author":
        x_df = aut_df
        j = 200
    elif l_cho == "series":
        x_df = ser_df
        j = 200
    elif l_cho == "title":
        x_df = tit_df
        j = 200
    elif l_cho == "all":
        x_df = main_df
        j = 120
    x_df_col = x_df.columns.values

    frm_1.place(x=10, y=20, height=300, width=1200)
    xtree = ttk.Treeview(frm_1, show='headings')
    xtree.place(x=20, y=0)
    xtree.delete(*xtree.get_children())
    xtree.bind('<ButtonRelease-1>', item_picked)
    # all the column name are generated dynamically.
    xtree["columns"] = x_df_col
    # generating for loop to create columns and give heading to them through df_col var.
    for x in range(len(x_df_col)):
        xtree.column(x, width=j)
        xtree.heading(x, text=x_df_col[x])
    # generating for loop to print values of dataframe in treeview column.
    for index, row in x_df.iterrows():
        xtree.insert('', 0, text=index, values=list(row))
        # add a scrollbar for treeview
    scrollbar = ttk.Scrollbar(frm_1, orient=VERTICAL, command=xtree.yview)
    scrollbar.place(x=0+5+2, y=0, height=200+20)
    xtree.configure(yscroll=scrollbar.set)


def aut_tree():
    global l_cho
    l_cho = "author"
    x_tree()


def ser_tree():
    global l_cho
    l_cho = "series"
    x_tree()


def tit_tree():
    global l_cho
    l_cho = "title"
    x_tree()


def all_tree():
    global l_cho
    l_cho = "all"
    x_tree()


def clear_all():
    frm_1.place(x=10, y=20, height=0, width=0)
    frm_2.place(x=10, y=20, height=0, width=0)


menubar = Menu(frm_3, background='#f1e8e4', foreground='black', activebackground='white', activeforeground='black')
file = Menu(menubar, tearoff=1, background='#f1e8e9', foreground='black')
file.add_command(label="View Authors", command=aut_tree)
file.add_command(label="View Series", command=ser_tree)
file.add_command(label="View Titles", command=tit_tree)
file.add_command(label="View All", command=all_tree)
file.add_command(label="Exit", command=window.quit)
menubar.add_cascade(label="File", menu=file)
search = Menu(menubar, tearoff=1, background='#f1e8e9', foreground='black')
search.add_command(label="Title", command=tit_tree)
search.add_command(label="Author", command=aut_tree)
search.add_command(label="Series", command=ser_tree)
menubar.add_cascade(label="Search", menu=search)
clear = Menu(menubar, tearoff=1, background='#f1e8e9', foreground='black')
clear.add_command(label="Clear", command=clear_all)
menubar.add_cascade(label="Clear", menu=clear)


window.config(menu=menubar)
window.mainloop()