from tkinter import *
import sqlite3
import tkinter as tk
from reportlab.pdfgen import canvas
import webbrowser as wb

root = Tk()
root.title('Laptop magazine')


# root.geometry('400x400')
# Database

# create a database or connect to one
conn = sqlite3.connect('plik_bazy.db')

# Create cursor
c = conn.cursor()

# create table
c.execute("""CREATE TABLE IF NOT EXISTS laptopy (
            brand text,
            model text,
            cpu text,
            ram text,
            screen text
        )""")

# LISTBOX


def listbox():
    list_window = Tk()
    list_window.title('Laptops list')
    # list_window.geometry("300x300")

    inf_label = Label(
        list_window, text='ID | Brand  Model  CPU  RAM  Screen', font=("Arial", 15))
    inf_label.grid(row=0, column=0)

    # create listbox
    my_listbox = Listbox(list_window, width=80, height=18)
    my_listbox.grid(row=1, column=0)

    # create scrollbar
    scrollbar = tk.Scrollbar(
        list_window, orient='vertical', command=my_listbox.yview)
    scrollbar.grid(row=1, column=1, sticky='ns')

    my_listbox.config(yscrollcommand=scrollbar.set, font=("Arial", 12))

    # add item to listbox

    conn = sqlite3.connect('plik_bazy.db')
    c = conn.cursor()

    # query database
    c.execute("SELECT *, oid FROM laptopy")
    records = c.fetchall()

    # loop results
    print_records = ''
    for record in records:
        print_records = str(record[5]) + " | " + str(record[0]) + "  " + str(
            record[1]) + "  " + str(record[2])+"  " + str(record[3])+"  " + str(record[4])
        my_listbox.insert(END, print_records)

    def delete_selected_in_listbox():
        get_oid_only = my_listbox.get(ANCHOR).split()[0]

        conn = sqlite3.connect('plik_bazy.db')
        c = conn.cursor()
        # delete a record
        c.execute("DELETE from laptopy WHERE oid= " + get_oid_only)
        conn.commit()
        conn.close()

        list_window.destroy()

    # create edit function

    def update():
        conn = sqlite3.connect('plik_bazy.db')
        c = conn.cursor()

        get_oid_only = my_listbox.get(ANCHOR).split()[0]

        c.execute("""UPDATE laptopy SET
            brand = :brand,
            model = :model,
            cpu = :cpu,
            ram = :ram,
            screen = :screen

            WHERE oid = :oid""",
                  {
                      'brand': brand_box_editor.get(),
                      'model': model_box_editor.get(),
                      'cpu': cpu_box_editor.get(),
                      'ram': ram_box_editor.get(),
                      'screen': screen_box_editor.get(),
                      'oid': get_oid_only
                  }
                  )

        conn.commit()
        conn.close()

        edit_window.destroy()
        list_window.destroy()

    def edit():
        global edit_window
        edit_window = Tk()
        edit_window.title('Edit Window')

        conn = sqlite3.connect('plik_bazy.db')
        c = conn.cursor()

        get_oid_only = my_listbox.get(ANCHOR).split()[0]
        # query database
        c.execute("SELECT * FROM laptopy WHERE oid = " + get_oid_only)
        records = c.fetchall()

        # create global variables for text box names
        global brand_box_editor
        global model_box_editor
        global cpu_box_editor
        global ram_box_editor
        global screen_box_editor

        for record in records:
            selected_info = Label(
                edit_window, text='Selected id: ' + get_oid_only,  font=("Arial", 12))
            selected_info.grid(row=0, column=1)

        # create text boxes
        brand_box_editor = Entry(edit_window, width=50)
        brand_box_editor.grid(row=1, column=1, padx=10, pady=5, ipady=5)
        model_box_editor = Entry(edit_window, width=50)
        model_box_editor.grid(row=2, column=1, padx=10, pady=5, ipady=5)
        cpu_box_editor = Entry(edit_window, width=50)
        cpu_box_editor.grid(row=3, column=1, padx=10, pady=5, ipady=5)
        ram_box_editor = Entry(edit_window, width=50)
        ram_box_editor.grid(row=4, column=1, padx=10, pady=5, ipady=5)
        screen_box_editor = Entry(edit_window, width=50)
        screen_box_editor.grid(row=5, column=1, padx=10, pady=5, ipady=5)

        # Create text box label
        brand_label_editor = Label(
            edit_window, text='Brand:', font=("Arial", 20))
        brand_label_editor.grid(row=1, column=0)
        model_label_editor = Label(
            edit_window, text='Model:', font=("Arial", 20))
        model_label_editor.grid(row=2, column=0)
        cpu_label_editor = Label(edit_window, text='CPU:', font=("Arial", 20))
        cpu_label_editor.grid(row=3, column=0)
        ram_label_editor = Label(edit_window, text='RAM:', font=("Arial", 20))
        ram_label_editor.grid(row=4, column=0)
        screen_label_editor = Label(
            edit_window, text='Screen:', font=("Arial", 20))
        screen_label_editor.grid(row=5, column=0)

        # Loop thru results
        for record in records:
            brand_box_editor.insert(0, record[0])
            model_box_editor.insert(0, record[1])
            cpu_box_editor.insert(0, record[2])
            ram_box_editor.insert(0, record[3])
            screen_box_editor.insert(0, record[4])

        # create save edited record button
        save_button = Button(edit_window, text="Save Record",
                             font=("Arial", 15), command=update)
        save_button.grid(row=6, column=0, columnspan=2,
                         pady=10, padx=10, ipadx=113)

    def generate_label():

        # Content

        conn = sqlite3.connect('plik_bazy.db')
        c = conn.cursor()

        get_oid_only = my_listbox.get(ANCHOR).split()[0]
        # query database
        c.execute("SELECT * FROM laptopy WHERE oid = " + get_oid_only)
        records = c.fetchall()

        for record in records:
            title = record[0] + '  ' + record[1]
            subTitle = record[2] + '  ' + record[3] + '  ' + record[4]

        pdf = canvas.Canvas('MyLabel.pdf')
        pdf.setTitle('Laptop label')
        pdf.setPageSize((300, 150))
        pdf.setFont('Helvetica-Bold', 20)
        pdf.drawString(15, 110, title)
        pdf.setFont('Helvetica', 15)
        pdf.drawString(15, 60, subTitle)

        pdf.save()

        wb.open_new('MyLabel.pdf')

    list_edit_item = Button(
        list_window, text='Edit', font=("Arial", 15), command=edit)
    list_edit_item.grid(row=2, column=0, columnspan=2,
                        pady=10, padx=10, ipadx=80)

    list_delete_item = Button(
        list_window, text='Delete', font=("Arial", 15), command=delete_selected_in_listbox)
    list_delete_item.grid(row=3, column=0, columnspan=2,
                          pady=10, padx=10, ipadx=69)

    list_delete_item = Button(
        list_window, text='Generate Label', font=("Arial", 15), command=generate_label)
    list_delete_item.grid(row=4, column=0, columnspan=2,
                          pady=10, padx=10, ipadx=29)


# create submit function for database


def submit():
    conn = sqlite3.connect('plik_bazy.db')
    c = conn.cursor()

    # insert into table
    c.execute("INSERT INTO laptopy VALUES (:brand, :model, :cpu, :ram, :screen)",
              {
                  'brand': brand_box.get(),
                  'model': model_box.get(),
                  'cpu': cpu_box.get(),
                  'ram': ram_box.get(),
                  'screen': screen_box.get()
              })

    conn.commit()
    conn.close()

    # clear the textboxes
    brand_box.delete(0, END)
    model_box.delete(0, END)
    cpu_box.delete(0, END)
    ram_box.delete(0, END)
    screen_box.delete(0, END)


# create text boxes
brand_box = Entry(root, width=50)
brand_box.grid(row=1, column=1, padx=10, pady=5, ipady=5)
model_box = Entry(root, width=50)
model_box.grid(row=2, column=1, padx=10, pady=5, ipady=5)
cpu_box = Entry(root, width=50)
cpu_box.grid(row=3, column=1, padx=10, pady=5, ipady=5)
ram_box = Entry(root, width=50)
ram_box.grid(row=4, column=1, padx=10, pady=5, ipady=5)
screen_box = Entry(root, width=50)
screen_box.grid(row=5, column=1, padx=10, pady=5, ipady=5)


# Create text box label
info_label = Label(root, text='Insert new laptop:', font=("Arial", 15))
info_label.grid(row=0, column=1)
brand_label = Label(root, text='Brand:', font=("Arial", 20))
brand_label.grid(row=1, column=0)
model_label = Label(root, text='Model:', font=("Arial", 20))
model_label.grid(row=2, column=0)
cpu_label = Label(root, text='CPU:', font=("Arial", 20))
cpu_label.grid(row=3, column=0)
ram_label = Label(root, text='RAM:', font=("Arial", 20))
ram_label.grid(row=4, column=0)
screen_label = Label(root, text='Screen:', font=("Arial", 20))
screen_label.grid(row=5, column=0)

# create submit button
submit_btn = Button(root, text="Add laptop to database",
                    font=("Arial", 20), command=submit)
submit_btn.grid(row=6, column=0, columnspan=2, pady=10, padx=10, ipadx=80)


# create list button
list_button = Button(root, text="Laptop list",
                     font=("Arial", 20), command=listbox)
list_button.grid(row=7, column=0, columnspan=2, pady=10, padx=10, ipadx=158)

# commit changes
conn.commit()

# close connection
conn.close()

root.mainloop()


# drop table after exit
# conn = sqlite3.connect('plik_bazy.db')
# c = conn.cursor()
# c.execute("DROP TABLE laptopy")
# conn.commit()
# conn.close()