import sqlite3
from tkinter import *
import tkinter.ttk as ttk
import tkinter.messagebox as tkMessageBox
from datetime import datetime

def Database():
    global conn, cursor
    conn = sqlite3.connect("compromisso.db")
    cursor = conn.cursor()
    cursor.execute(
        "CREATE TABLE IF NOT EXISTS REGISTRATION "
        "(RID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, DESCRICAO TEXT, DATA TEXT, ESTADO TEXT, HORA TEXT)")
    conn.commit()

def validar_data(data):
    try:
        datetime.strptime(data, "%Y-%m-%d")
        return True
    except ValueError:
        return False

def validar_hora(hora):
    try:
        datetime.strptime(hora, "%H:%M")
        return True
    except ValueError:
        return False

def register():
    Database()
    descricao_val = descricao.get()
    data_val = data.get()
    estado_val = estado.get()
    hora_val = hora.get()

    if descricao_val == "" or data_val == "" or estado_val == "Selecionar Estado" or hora_val == "":
        tkMessageBox.showwarning("Aviso", "Por favor, preencha todos os campos.")
    elif not validar_data(data_val):
        tkMessageBox.showwarning("Aviso", "Data inválida. Por favor, use o formato AAAA-MM-DD.")
    elif not validar_hora(hora_val):
        tkMessageBox.showwarning("Aviso", "Hora inválida. Por favor, use o formato HH:MM.")
    else:
        cursor.execute("INSERT INTO REGISTRATION (DESCRICAO, DATA, ESTADO, HORA) VALUES(?, ?, ?, ?)",
                       (descricao_val, data_val, estado_val, hora_val))
        conn.commit()
        tkMessageBox.showinfo("Sucesso", "Compromisso registrado com sucesso!")
        DisplayData()
        limpar()

def limpar():
    descricao.set("")
    data.set("")
    estado.set("Selecionar Estado")
    hora.set("")

def apagar():
    Database()
    if not tree.selection():
        tkMessageBox.showwarning("Aviso", "Por favor, selecione um compromisso para apagar.")
    else:
        result = tkMessageBox.askquestion('Confirmar', 'Você tem certeza que deseja apagar este compromisso?', icon="warning")
        if result == 'yes':
            curItem = tree.focus()
            contents = (tree.item(curItem))
            selecteditem = contents['values']
            tree.delete(curItem)
            cursor.execute("DELETE FROM REGISTRATION WHERE RID = ?", (selecteditem[0],))
            conn.commit()
            tkMessageBox.showinfo("Sucesso", "Compromisso apagado com sucesso.")
            DisplayData()

def actualizacao():
    Database()
    descricao_val = descricao.get()
    data_val = data.get()
    estado_val = estado.get()
    hora_val = hora.get()

    if descricao_val == "" or data_val == "" or estado_val == "Selecionar Estado" or hora_val == "":
        tkMessageBox.showwarning("Aviso", "Por favor, preencha todos os campos.")
    elif not validar_data(data_val):
        tkMessageBox.showwarning("Aviso", "Data inválida. Por favor, use o formato AAAA-MM-DD.")
    elif not validar_hora(hora_val):
        tkMessageBox.showwarning("Aviso", "Hora inválida. Por favor, use o formato HH:MM.")
    else:
        curItem = tree.focus()
        contents = (tree.item(curItem))
        selecteditem = contents['values']
        cursor.execute(
            "UPDATE REGISTRATION SET DESCRICAO = ?, DATA = ?, ESTADO = ?, HORA = ? WHERE RID = ?",
            (descricao_val, data_val, estado_val, hora_val, selecteditem[0]))
        conn.commit()
        tkMessageBox.showinfo("Sucesso", "Compromisso atualizado com sucesso!")
        DisplayData()
        limpar()

def DisplayForm():
    root = Tk()
    root.geometry("900x500")
    root.title("Registar os Compromissos")
    global tree
    global SEARCH
    global descricao, data, hora, estado
    SEARCH = StringVar()
    descricao = StringVar()
    data = StringVar()
    hora = StringVar()
    estado = StringVar()

    TopViewForm = Frame(root, width=600, bd=1, relief=SOLID)
    TopViewForm.pack(side=TOP, fill=X)

    LFrom = Frame(root, width="350", bg="#15244C")
    LFrom.pack(side=LEFT, fill=Y)

    LeftViewForm = Frame(root, width=500, bg="#0B4670")
    LeftViewForm.pack(side=LEFT, fill=Y)

    MidViewForm = Frame(root, width=600)
    MidViewForm.pack(side=RIGHT)

    lbl_text = Label(TopViewForm, text="Registar os Compromissos", font=('verdana', 18), width=600, bg="cyan")
    lbl_text.pack(fill=X)

    Label(LFrom, text="Descrição ", font=("Arial", 12), bg="#15244C", fg="white").pack(side=TOP)
    Entry(LFrom, font=("Arial", 10, "bold"), textvariable=descricao).pack(side=TOP, padx=10, fill=X)

    Label(LFrom, text="Data  (AAAA-MM-DD) ", font=("Arial", 12), bg="#15244C", fg="white").pack(side=TOP)
    Entry(LFrom, font=("Arial", 10, "bold"), textvariable=data).pack(side=TOP, padx=10, fill=X)

    Label(LFrom, text="Estado ", font=("Arial", 12), bg="#15244C", fg="white").pack(side=TOP)
    estado.set("Selecionar Estado")
    content = {'A Iniciar', 'A Decorrer', "Encerrado"}
    OptionMenu(LFrom, estado, *content).pack(side=TOP, padx=10, fill=X)

    Label(LFrom, text="Hora (HH:MM) ", font=("Arial", 12), bg="#15244C", fg="white").pack(side=TOP)
    Entry(LFrom, font=("Arial", 10, "bold"), textvariable=hora).pack(side=TOP, padx=10, fill=X)

    Button(LFrom, text="Submeter", font=("Arial", 10, "bold"), bg="cyan", command=register).pack(side=TOP, padx=10, pady=5, fill=X)

    lbl_txtsearch = Label(LeftViewForm, text="Digite a Descrição", font=('verdana', 10), bg="#0B4670")
    lbl_txtsearch.pack()
    search = Entry(LeftViewForm, textvariable=SEARCH, font=('verdana', 15), width=10)
    search.pack(side=TOP, padx=10, fill=X)

    btn_search = Button(LeftViewForm, text="Pesquisa", bg="cyan", command=SearchRecord)
    btn_search.pack(side=TOP, padx=10, pady=10, fill=X)
    btn_view = Button(LeftViewForm, text="Ver tudo", bg="cyan", command=DisplayData)
    btn_view.pack(side=TOP, padx=10, pady=10, fill=X)

    btn_Limpar = Button(LeftViewForm, text="Limpar", bg="cyan", command=limpar)
    btn_Limpar.pack(side=TOP, padx=10, pady=10, fill=X)
    btn_apagar = Button(LeftViewForm, text="Apagar", bg="cyan", command=apagar)
    btn_apagar.pack(side=TOP, padx=10, pady=10, fill=X)
    btn_actualizar = Button(LeftViewForm, text="Actualizar", bg="cyan", command=actualizacao)
    btn_actualizar.pack(side=TOP, padx=10, pady=10, fill=X)

    scrollbarx = Scrollbar(MidViewForm, orient=HORIZONTAL)
    scrollbary = Scrollbar(MidViewForm, orient=VERTICAL)

    tree = ttk.Treeview(MidViewForm, columns=("RID", "Descrição", "Data", "Estado", "Hora"),
                        selectmode="extended", height=100, yscrollcommand=scrollbary.set, xscrollcommand=scrollbarx.set)

    scrollbary.config(command=tree.yview)
    scrollbary.pack(side=RIGHT, fill=Y)

    scrollbarx.config(command=tree.xview)
    scrollbarx.pack(side=BOTTOM, fill=X)

    tree.heading('RID', text="ID", anchor=W)
    tree.heading('Descrição', text="Descrição", anchor=W)
    tree.heading('Data', text="Data", anchor=W)
    tree.heading('Estado', text="Estado", anchor=W)
    tree.heading('Hora', text="Hora", anchor=W)

    tree.column('#0', stretch=NO, minwidth=0, width=0)
    tree.column('#1', stretch=NO, minwidth=0, width=50)
    tree.column('#2', stretch=NO, minwidth=0, width=150)
    tree.column('#3', stretch=NO, minwidth=0, width=150)
    tree.column('#4', stretch=NO, minwidth=0, width=100)

    tree.bind("<Double-1>", OnDoubleClick)
    tree.pack()
    DisplayData()
    root.mainloop()

def SearchRecord():
    Database()
    if SEARCH.get() != "":
        tree.delete(*tree.get_children())
        cursor = conn.execute("SELECT * FROM REGISTRATION WHERE DESCRICAO LIKE ?", ('%' + str(SEARCH.get()) + '%',))
        fetch = cursor.fetchall()
        for data in fetch:
            tree.insert('', 'end', values=(data))
        cursor.close()
        conn.close()

def DisplayData():
    Database()
    tree.delete(*tree.get_children())
    cursor = conn.execute("SELECT * FROM REGISTRATION")
    fetch = cursor.fetchall()
    for data in fetch:
        tree.insert('', 'end', values=(data))
    cursor.close()
    conn.close()

def OnDoubleClick(event):
    curItem = tree.focus()
    contents = (tree.item(curItem))
    selecteditem = contents['values']
    descricao.set(selecteditem[1])
    data.set(selecteditem[2])
    estado.set(selecteditem[3])
    hora.set(selecteditem[4])

DisplayForm()
if __name__ == '__main__':
    mainloop()