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


def Database():
    global conn, cursor
    conn = sqlite3.connect("Tripulação1.db")
    cursor = conn.cursor()
    cursor.execute(
        "CREATE TABLE IF NOT EXISTS REGISTRATION (RID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, NOME TEXT, "
        "DATANASCIMENTO TEXT, SEXO TEXT, CARGO TEXT, NACIONALIDADE TEXT)")


def DisplayForm():
    root = Tk()
    root.geometry("1000x500")
    root.title("Gestão de Tripulação")
    global tree
    global SEARCH
    global nome, datanascimento, sexo, cargo, nacionalidade
    SEARCH = StringVar()
    nome = StringVar()
    datanascimento = StringVar()
    sexo = StringVar()
    cargo = StringVar()
    nacionalidade = 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="Gestão de Tripulação", font=('verdana', 18), width=600, bg="cyan")
    lbl_text.pack(fill=X)

    Label(LFrom, text="Nome  ", font=("Arial", 12), bg="#15244C", fg="white").pack(side=TOP)
    Entry(LFrom, font=("Arial", 10, "bold"), textvariable=nome).pack(side=TOP, padx=10, fill=X)
    Label(LFrom, text="Data de Nascimento (YYYY-MM-DD)", font=("Arial", 12), bg="#15244C", fg="white").pack(side=TOP)
    Entry(LFrom, font=("Arial", 10, "bold"), textvariable=datanascimento).pack(side=TOP, padx=10, fill=X)
    Label(LFrom, text="Sexo ", font=("Arial", 12), bg="#15244C", fg="white").pack(side=TOP)
    sexo.set("Selecionar Sexo")
    content = {'Masculino', 'Feminino'}
    OptionMenu(LFrom, sexo, *content).pack(side=TOP, padx=10, fill=X)
    Label(LFrom, text="Cargo ", font=("Arial", 12), bg="#15244C", fg="white").pack(side=TOP)
    Entry(LFrom, font=("Arial", 10, "bold"), textvariable=cargo).pack(side=TOP, padx=10, fill=X)
    Label(LFrom, text="Nacionalidade ", font=("Arial", 12), bg="#15244C", fg="white").pack(side=TOP)
    Entry(LFrom, font=("Arial", 10, "bold"), textvariable=nacionalidade).pack(side=TOP, padx=10, fill=X)

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

    lbl_txtsearch = Label(LeftViewForm, text="Digite o Nome", 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=("Student Id", "Nome", "Datanascimento", "Idade", "Sexo", "Cargo", "Nacionalidade"),
                        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('Student Id', text="Id", anchor=W)
    tree.heading('Nome', text="Nome", anchor=W)
    tree.heading('Datanascimento', text="Data nascimento", anchor=W)
    tree.heading('Idade', text="Idade", anchor=W)
    tree.heading('Sexo', text="Sexo", anchor=W)
    tree.heading('Cargo', text="Cargo", anchor=W)
    tree.heading('Nacionalidade', text="Nacionalidade", anchor=W)

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

    tree.pack()
    DisplayData()
    root.mainloop()


def calculate_age(dob):
    dob = datetime.strptime(dob, "%Y-%m-%d")
    today = datetime.today()
    age = today.year - dob.year - ((today.month, today.day) < (dob.month, dob.day))
    return age


def validate_date(date_text):
    try:
        datetime.strptime(date_text, '%Y-%m-%d')
    except ValueError:
        tkMessageBox.showerror("Erro", "Data de Nascimento inválida. Use o formato YYYY-MM-DD.")
        return False
    return True


def SearchRecord():
    Database()
    if SEARCH.get() != "":
        tree.delete(*tree.get_children())
        cursor = conn.execute("SELECT * FROM REGISTRATION WHERE NOME LIKE ?", ('%' + str(SEARCH.get()) + '%',))
        fetch = cursor.fetchall()
        for data in fetch:
            age = calculate_age(data[2])
            tree.insert('', 'end', values=(data[0], data[1], data[2], age, data[3], data[4], data[5]))
        cursor.close()
        conn.close()


def register():
    Database()
    vnome = nome.get()
    vdatanascimento = datanascimento.get()
    vsexo = sexo.get()
    vcargo = cargo.get()
    vnacionalidade = nacionalidade.get()

    if vnome == '' or vdatanascimento == '' or vsexo == '' or vcargo == '' or vnacionalidade == '':
        tkMessageBox.showinfo("Atenção", "Preencha os espaços vazios!!!")
    elif not validate_date(vdatanascimento):
        return
    else:
        conn.execute(
            'INSERT INTO REGISTRATION (NOME, DATANASCIMENTO, SEXO, CARGO, NACIONALIDADE) VALUES (?, ?, ?, ?, ?)',
            (vnome, vdatanascimento, vsexo, vcargo, vnacionalidade))
        conn.commit()

        tkMessageBox.showinfo("Sucesso", "Tripulante adicionado com Sucesso")
        DisplayData()
        conn.close()


def actualizacao():
    Database()
    vnome = nome.get()
    vdatanascimento = datanascimento.get()
    vsexo = sexo.get()
    vcargo = cargo.get()
    vnacionalidade = nacionalidade.get()

    if vnome == '' or vdatanascimento == '' or vsexo == '' or vcargo == '' or vnacionalidade == '':
        tkMessageBox.showinfo("Atenção", "Preencha todos os espaços!!!")
    elif not validate_date(vdatanascimento):
        return
    else:
        curItem = tree.focus()
        contents = (tree.item(curItem))
        selecteditem = contents['values']
        conn.execute('UPDATE REGISTRATION SET NOME=?, DATANASCIMENTO=?, SEXO=?, CARGO=?, NACIONALIDADE=? WHERE RID = ?',
                     (vnome, vdatanascimento, vsexo, vcargo, vnacionalidade, selecteditem[0]))
        conn.commit()
        tkMessageBox.showinfo("Mensagem", "Actualização feita com sucesso")
        limpar()
        DisplayData()
        conn.close()


def apagar():
    Database()
    if not tree.selection():
        tkMessageBox.showwarning("Atenção", "Selecione o tripulante a eliminar ")
    else:
        result = tkMessageBox.askquestion('Confirmar', 'Deseja apagar o registo do tripulante?', icon="warning")
        if result == 'yes':
            curItem = tree.focus()
            contents = (tree.item(curItem))
            selecteditem = contents['values']
            tree.delete(curItem)
            cursor = conn.execute("DELETE FROM REGISTRATION WHERE RID = ?", (selecteditem[0],))
            conn.commit()
            cursor.close()
            conn.close()


def limpar():
    tree.delete(*tree.get_children())
    DisplayData()
    SEARCH.set("")
    nome.set("")
    datanascimento.set("")
    sexo.set("")
    cargo.set("")
    nacionalidade.set("")


def DisplayData():
    Database()
    tree.delete(*tree.get_children())
    cursor = conn.execute("SELECT * FROM REGISTRATION")
    fetch = cursor.fetchall()
    for data in fetch:
        age = calculate_age(data[2])
        tree.insert('', 'end', values=(data[0], data[1], data[2], age, data[3], data[4], data[5]))
        tree.bind("<Double-1>", OnDoubleClick)
    cursor.close()
    conn.close()


def OnDoubleClick(event):
    curItem = tree.focus()
    contents = (tree.item(curItem))
    selecteditem = contents['values']
    nome.set(selecteditem[1])
    datanascimento.set(selecteditem[2])
    sexo.set(selecteditem[4])
    cargo.set(selecteditem[5])
    nacionalidade.set(selecteditem[6])


if __name__ == '__main__':
    DisplayForm()