from os import getcwd as pwd
import tkinter as tk
import sqlite3


def sql_07():
    """Create a program that displays the following screen:
                    +---------------------------------------+
                    |                        _____________  |
                    | Enter student's name  |_____________| |
                    |                        _____________  |
                    | Enter student's grade |_____________| |
                    |        _________     _________        |
                    |       |___Add___|   |__Clear__|       |
                    |_______________________________________|
        It should save the data to an SQL database called TestScores when the
        Add button is clicked. The Clear button should clear the window."""
    db_path = f"{pwd()}/sqlite/db/NotasAlumnos.db"
    
    with sqlite3.connect(db_path) as db:
        cursor = db.cursor()
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS notas (
                id INTEGER PRIMARY KEY,
                alumno TEXT NOT NULL,
                nota FLOAT NOT NULL
            )""")

    window = tk.Tk()
    window.title("Notas Estudiantes")
    window.geometry("400x200")
    common_bg = "dodger blue"
    window["bg"] = common_bg

    def save():
        with sqlite3.connect(db_path) as db:
            cursor = db.cursor()
            alumno = in_name.get()
            nota = in_grade.get()
            query = f"INSERT INTO notas(alumno, nota) VALUES(?,?)"
            cursor.execute(query, [alumno, nota])
        btn_clear.focus()

    def clear():
        in_name.delete(0, 'end')
        in_grade.delete(0, 'end')
        in_name.focus()

    lbl_name = tk.Label(text="Nombre", font="Verdana 18")
    lbl_name.place(x=30, y=20, width=150, height=40)
    lbl_name["bg"] = common_bg
    lbl_grade = tk.Label(text="Nota", font="Verdana 18")
    lbl_grade.place(x=30, y=70, width=150, height=40)
    lbl_grade["bg"] = common_bg

    in_name = tk.Entry(font="Verdana 14")
    in_name.place(x=180, y=20, width=190, height=40)
    in_grade = tk.Entry(font="Verdana 14")
    in_grade.place(x=180, y=70, width=190, height=40)

    btn_save = tk.Button(text="Guardar", command=save)
    btn_save["font"] = "Verdana 16"
    btn_save.place(x=30, y=140, width=150, height=45)
    btn_clear = tk.Button(text="Limpiar", command=clear)
    btn_clear["font"] = "Verdana 16"
    btn_clear.place(x=220, y=140, width=150, height=45)
    
    in_name.focus()

    window.mainloop()