import mysql.connector
from tkinter import *
from tkinter import ttk
from tkinter import messagebox
from datetime import datetime

host_name = "localhost"
user = "root"
password = "root"
database = "miniProject"

purchase_db_location = ""

# ------------------------- Database ---------------------------#

mydb = mysql.connector.connect(
  host= host_name,
  user= user,
  password= password,
  database = database
)
mycursor = mydb.cursor()

##mycursor.execute("CREATE DATABASE purchase")

#mycursor.execute("CREATE TABLE purchase (id VARCHAR(20) PRIMARY KEY, item VARCHAR(255), price DECIMAL(20,2))")
#mycursor.execute("CREATE TABLE stock (item VARCHAR(255), stock varchar(25), price DECIMAL(20,2))")

#item1 = "Vitamin C"
#stock = "100"
#rice = 150
#sql = "INSERT INTO stock (item,stock,price) VALUES(%s,%s,%s)"
#val = (item1,stock,price)
#mycursor.execute(sql,val)
#mydb.commit()


def insertDb():
    global id, item
    tprice = 0
    productList=[]

    mydb = mysql.connector.connect(
        host= host_name,
        user= user,
        password= password,
        database = database
    )

    mycursor = mydb.cursor()

    last_value = MenuList.index("end")

    print(last_value)

    last_value = MenuList.index("end")

    for x in range(1,last_value):
        getValue = MenuList.get(x)
        print(getValue,"here")
        tprice += int(getValue[4])
        productD = getValue[0]+" = " + getValue[2] + ","
        productList.insert(x,productD)

        product = menu.get()
        quantity = getValue[2]

        print(product,quantity)

        val = "Select stock from stock where item ='" + product + "';"
        mycursor.execute(val)
        myresult = mycursor.fetchone()

        print(myresult)
        listToStrStock = ' '.join([str(elem) for elem in myresult])
        newStock = str(int(listToStrStock) - int(quantity))

        sql = "UPDATE stock set stock='"+newStock+"' Where item ='" + product + "';"
        mycursor.execute(sql)

        mydb.commit()



    print(productList)
    print(tprice)

    listToStr = ' '.join([str(elem) for elem in productList])
    resit_id = datetime.now().strftime("%d%m%Y%H%M%S")

    sql = "INSERT INTO purchase (id,item,price) VALUES(%s,%s,%s)"
    val = (resit_id,listToStr,tprice)

    mycursor.execute(sql,val)

    mydb.commit()

    print("Sucecs")

    MenuList.delete(0,END)
    txtQuantity.delete(0,END)
    ayat = "productName" , ":" , "quantity"  , ":" , 0 ,"totalPrice"
    MenuList.insert(0, ayat)

    messagebox.showinfo("showinfo", "Succesfull Purchase")

    ##---------------------------------------------------- INSERT INTO DB




def insertStockDb():
    mydb = mysql.connector.connect(
        host= host_name,
        user= user,
        password= password,
        database = database
    )

    mycursor = mydb.cursor()

    product = menu1.get()
    quantity = txtQuantity1.get()
    val = "Select stock from stock where item ='" + product + "';"
    mycursor.execute(val)
    myresult = mycursor.fetchone()

    print(myresult)
    listToStrStock = ' '.join([str(elem) for elem in myresult])
    newStock = str(int(listToStrStock) + int(quantity))

    sql = "UPDATE stock set stock='"+newStock+"' Where item ='" + product + "';"
    mycursor.execute(sql)

    mydb.commit()

    messagebox.showinfo("showinfo", "Succesfull Add Stock")

# ------------------------- Function ---------------------------#
def view_purchase():
    root = Tk()
    root.title("View Purchase - KEDAI RUNCIT PAK ALI")
    root.geometry("800x420")

    mydb = mysql.connector.connect(
        host= host_name,
        user= user,
        password= password,
        database = database
    )

    mycursor = mydb.cursor()

    string_view = ""

    val = "Select * from purchase;"
    mycursor.execute(val)
    myresult = mycursor.fetchall()

    for row in myresult:
            string_view = string_view + ("-ID : {} -ITEM : {} -Price: RM{} \n".format(row[0],row[1],row[2]))

    print("Total rows are:  ", len(myresult))

    #----------------------------------------------------- TKINTER HANDLER

    T = Text(root, height = 50, width = 100, bg = "light yellow")
    T.insert(1.0, string_view)
    T.pack()

    btnCancel = Button(root, text ="Close",width=10, bg='brown', command=root.destroy)
    btnCancel.place(x=700,y=380)

    root.mainloop()

def add_stock():
    global menu1
    global txtQuantity1

    root = Tk()
    root.title("Add Stock - KEDAI RUNCIT PAK ALI")
    root.geometry("400x150")
    menu1 = ttk.Combobox(root, values=["Fish Cake", "Printer Ink","Eraser","Herbs","Water Bottle", "Vitamin C"])
    menu1.current(0)
    menu1.place(x=40 , y=40)
    label1 = Label(root, text = "Quantity")
    label1.place(x = 200,y = 20)
    txtQuantity1 = Entry(root, textvariable="txtQuantity")
    txtQuantity1.place(x=200,y=40)
    btnAdd1 = Button(root, text ="Submit",width=10, bg='brown', command=insertStockDb)
    btnAdd1.place(x=150,y=80)
    root.mainloop()

def add_stock_to_database():
    pass

def view_stock():
    root = Tk()
    root.title("View Stock - KEDAI RUNCIT PAK ALI")
    root.geometry("800x420")

    mydb = mysql.connector.connect(
        host= host_name,
        user= user,
        password= password,
        database = database
    )

    mycursor = mydb.cursor()

    string_view = ""

    val = "Select * from stock;"
    mycursor.execute(val)
    myresult = mycursor.fetchall()

    for row in myresult:
            string_view = string_view + ("-ITEM : {} -STOCK : {}Pcs -Price: RM{} \n".format(row[0],row[1],row[2]))

    print("Total rows are:  ", len(myresult))

    #----------------------------------------------------- TKINTER HANDLER

    T = Text(root, height = 50, width = 100, bg = "light yellow")
    T.insert(1.0, string_view)
    T.pack()

    btnCancel = Button(root, text ="Close",width=10, bg='brown', command=root.destroy)
    btnCancel.place(x=700,y=380)

    root.mainloop()

def priceList(productName,quantity):
    tprice = 0

    i = 0

    if (productName == "Fish Cake"):
        price = 15
        totalPrice = price * int(quantity)
        ayat = productName , ":" , quantity  , ":" ,  totalPrice
        MenuList.insert(1 , ayat)


    elif (productName == "Printer Ink"):
        price = 150
        totalPrice = price * int(quantity)
        ayat = productName , ":" , quantity  , ":" ,  totalPrice
        MenuList.insert(1, ayat)

    elif (productName == "Eraser"):
        price = 150
        totalPrice = price * int(quantity)
        ayat = productName , ":" , quantity  , ":" ,  totalPrice
        MenuList.insert(1, ayat)

    elif (productName == "Herbs"):
        price = 150
        totalPrice = price * int(quantity)
        ayat = productName , ":" , quantity  , ":" ,  totalPrice
        MenuList.insert(1, ayat)

    elif (productName == "Water Bottle"):
        price = 150
        totalPrice = price * int(quantity)
        ayat = productName , ":" , quantity  , ":" ,  totalPrice
        MenuList.insert(1, ayat)

    elif (productName == "Vitamin C"):
        price = 150
        totalPrice = price * int(quantity)
        ayat = productName , ":" , quantity  , ":" ,  totalPrice
        MenuList.insert(1, ayat)

    last_value = MenuList.index("end")

    for x in range(0,last_value):
        getValue = MenuList.get(x)
        print(getValue,"here")
        tprice += int(getValue[4])

    txttotalP.config(text=tprice)
    txtQuantity.delete(0, END)

def enter():

    productName = menu.get()
    quantity = txtQuantity.get()

    if( not quantity.isnumeric() or quantity==""):
        messagebox.showwarning("showwarning", "Please insert number")
    else:
        priceList(productName,quantity)

def deleteSelected():
    try:
        selection = MenuList.curselection()
        MenuList.delete(selection[0])
    except:
        messagebox.showwarning("showwarning", "Please select a list to cancel")

def newFile():
    MenuList.delete(0,END)
    txtQuantity.delete(0,END)
    menu.delete(0,END)
    ayat = "productName" , ":" , "quantity"  , ":" , 0 ,"totalPrice"
    MenuList.insert(0, ayat)

# ------------------------------- GUI --------------------------------#

root = Tk()
root.title('Main Menu')
root.title("KEDAI RUNCIT PAK ALI")

# ------------------------- Menu ---------------------------#
menubar = Menu(root)
root.config(menu=menubar)

file_menu = Menu(menubar,tearoff=0)
file_menu.add_command(label='New',command=newFile)
file_menu.add_command(label='Exit',command=root.destroy)
menubar.add_cascade(label="File",menu=file_menu)

file_menu_edit = Menu(menubar,tearoff=0)
file_menu_edit.add_command(label='Add Stock',command=add_stock)
menubar.add_cascade(label="Edit",menu=file_menu_edit)

file_menu_view = Menu(menubar,tearoff=0)
file_menu_view.add_command(label='View Stock',command=view_stock)
file_menu_view.add_command(label='View Purchase',command=view_purchase)
menubar.add_cascade(label="View",menu=file_menu_view)

# ----------------------- Content ---------------------------#

MenuList = Listbox(root,width=44, height=12,)

ayat = "productName" , ":" , "quantity"  , ":" , 0 ,"totalPrice"
MenuList.insert(0, ayat)
#MenuList.grid(row=0,columnspan=3)
MenuList.place(x = 115, y=20)

menu = ttk.Combobox(root, values=["Fish Cake", "Printer Ink","Eraser","Herbs","Water Bottle", "Vitamin C"])
#print(dict(menu))
menu.current(0)
menu.place(x=115 , y=240)

lbltotal = Label(root, text = "Total Price :")
lbltotal.place(x = 400,y = 100)

txttotalP = Label(root, text = "RM0")
txttotalP.place(x = 400,y = 130)

lbl1 = Label(root, text = "Quantity")
lbl1.place(x = 270,y = 215)

txtQuantity = Entry(textvariable="quantity")
txtQuantity.place(x=270,y=240)

btnConfirm = Button(root, text ="Confirm",width=10,fg='brown', bg='light green', command=insertDb)
btnConfirm.place(x=115,y=270)

btnEnter = Button(root, text ="Enter",width=10,fg='purple', bg='light green',command = enter)
btnEnter.place(x=218,y=270)

btnCancel = Button(root, text ="Cancel",width=10,fg='yellow', bg='red', command=deleteSelected)
btnCancel.place(x=315,y=270)

root.geometry("500x350")

root.mainloop()