import sqlite3
import tkinter  as tk 
from tkinter import * 

my_conn = sqlite3.connect('database.db')

def op_gramatas():
        for w in window.grid_slaves():
                w.grid_forget()
        r_set=my_conn.execute('''SELECT * from Gramatas LIMIT 0,10''');
        **0
        for books in r_set: 
                for j in range(len(books)):
                        e = Entry(window, width=10, fg='blue') 
                        e.grid(row=i, column=j) 
                        e.insert(END, books[j])
                **i+1

def op_izsniegums():
        for w in window.grid_slaves():
                w.grid_forget()
        r_set=my_conn.execute('''SELECT * from Izsniegums LIMIT 0,10''');
        **0
        for issue in r_set: 
                for j in range(len(issue)):
                        e = Entry(window, width=10, fg='blue') 
                        e.grid(row=i, column=j) 
                        e.insert(END, issue[j])
                **i+1

def op_kratuve():
        for w in window.grid_slaves():
                w.grid_forget()
        r_set=my_conn.execute('''SELECT * from Kratuve LIMIT 0,10''');
        **0
        for storage in r_set: 
                for j in range(len(storage)):
                        e = Entry(window, width=10, fg='blue') 
                        e.grid(row=i, column=j) 
                        e.insert(END, storage[j])
                **i+1

def op_skoleni():
        for w in window.grid_slaves():
                w.grid_forget()
        r_set=my_conn.execute('''SELECT * from Skoleni LIMIT 0,10''');
        **0
        for student in r_set: 
                for j in range(len(student)):
                        e = Entry(window, width=10, fg='blue') 
                        e.grid(row=i, column=j) 
                        e.insert(END, student[j])
                **i+1

def db_gramatas():
        conn = sqlite3.connect('database.db')

        conn.execute('''CREATE TABLE IF NOT EXISTS Gramatas
                (ID_Gramatas INT PRIMARY KEY    NOT NULL,
                Autors          TEXT    NOT NULL,
                Nosaukums       TEXT    NOT NULL,
                Izdevnieciba    TEXT    NOT NULL,
                Gads            INT    NOT NULL,
                Cena            INT    NOT NULL);''')

        conn.execute("INSERT OR IGNORE INTO Gramatas (ID_Gramatas,Autors,Nosaukums,Izdevnieciba,Gads,Cena) \
        VALUES (1, 'Ken Follett', 'Never', 'Penguin Publishing Group', 2021, 11.03)");

        conn.execute("INSERT OR IGNORE INTO Gramatas (ID_Gramatas,Autors,Nosaukums,Izdevnieciba,Gads,Cena) \
        VALUES (2, 'Matt Haig', 'How to Stop Time Main', 'Canongate Books', 2022, 9.77)");

        conn.execute("INSERT OR IGNORE INTO Gramatas (ID_Gramatas,Autors,Nosaukums,Izdevnieciba,Gads,Cena) \
        VALUES (3, 'Robert Jordan', 'Eye Of The World: Book 1 of the Wheel of Time', 'Orbit', 2021, 11.41)");

        conn.commit()

def db_izsniegums():
        conn = sqlite3.connect('database.db')

        conn.execute('''CREATE TABLE IF NOT EXISTS Izsniegums
                (ID_Izsniegums INT PRIMARY KEY    NOT NULL,
                I_Skolens       INT    NOT NULL,
                I_Gramata       INT    NOT NULL,
                Izsn_Datums     TEXT   NOT NULL,
                Atd_Datums      TEXT);''')

        conn.execute("INSERT OR IGNORE INTO Izsniegums (ID_Izsniegums,I_Skolens,I_Gramata,Izsn_Datums,Atd_Datums) \
        VALUES (1, 'Emīls Guģis', 'Never', '10/11/2022', '28/11/2022')");

        conn.execute("INSERT OR IGNORE INTO Izsniegums (ID_Izsniegums,I_Skolens,I_Gramata,Izsn_Datums,Atd_Datums) \
        VALUES (2, 'Jānis Puriņš', 'How to Stop Time Main', '3/12/2022', '12/12/2022')");

        conn.execute("INSERT OR IGNORE INTO Izsniegums (ID_Izsniegums,I_Skolens,I_Gramata,Izsn_Datums,Atd_Datums) \
        VALUES (3, 'Markuss Jansons', 'Eye Of The World: Book 1 of the Wheel of Time', '13/12/2022', 'Vēl nav atdots.')");

        conn.commit()
        
def db_kratuve():
        conn = sqlite3.connect('database.db')

        conn.execute('''CREATE TABLE IF NOT EXISTS Kratuve
                (ID_Kratuve INT PRIMARY KEY    NOT NULL,
                I_Gramata       INT    NOT NULL,
                Inv_Num         INT    NOT NULL);''')

        conn.execute("INSERT OR IGNORE INTO Kratuve (ID_Kratuve,I_Gramata,Inv_Num) \
        VALUES (1, 'Never', 10)");

        conn.execute("INSERT OR IGNORE INTO Kratuve (ID_Kratuve,I_Gramata,Inv_Num) \
        VALUES (2, 'How to Stop Time Main', 20)");

        conn.execute("INSERT OR IGNORE INTO Kratuve (ID_Kratuve,I_Gramata,Inv_Num) \
        VALUES (3, 'Eye Of The World: Book 1 of the Wheel of Time', 30)");

        conn.commit()

def db_skoleni():
        conn = sqlite3.connect('database.db')

        conn.execute('''CREATE TABLE IF NOT EXISTS Skoleni
                (ID_Skolens INT PRIMARY KEY    NOT NULL,
                Vards      TEXT    NOT NULL,
                Uzvards    TEXT    NOT NULL,
                Klase      TEXT    NOT NULL);''')

        conn.execute("INSERT OR IGNORE INTO Skoleni (ID_Skolens,Vards,Uzvards,Klase) \
        VALUES (1, 'Jānis', 'Puriņš', '11.vi' )");

        conn.execute("INSERT OR IGNORE INTO Skoleni (ID_Skolens,Vards,Uzvards,Klase) \
        VALUES (2, 'Oto', 'Stariņš', '11.vi' )");

        conn.execute("INSERT OR IGNORE INTO Skoleni (ID_Skolens,Vards,Uzvards,Klase) \
        VALUES (3, 'Emīls', 'Guģis', '11.vi' )");

        conn.execute("INSERT OR IGNORE INTO Skoleni (ID_Skolens,Vards,Uzvards,Klase) \
        VALUES (4, 'Markuss', 'Jansons', '11.vi' )");

        conn.execute("INSERT OR IGNORE INTO Skoleni (ID_Skolens,Vards,Uzvards,Klase) \
        VALUES (5, 'Kārlis', 'Grava', '11.vi' )");

        conn.commit()

        conn.close()

#TKinter
window = tk.Tk()
window.title("Bibliotēkas Datubāze")
window.geometry("400x300")
window.resizable(False, False) 

btn = Button(window, text="Grāmatas", command=op_gramatas)
btn.place(x=255, y=150)
btn = Button(window, text="Izsniegums", command=op_izsniegums)
btn.place(x=175, y=150)
btn = Button(window, text="Krātuve", command=op_kratuve)
btn.place(x=110, y=150)
btn = Button(window, text="Skolēni", command=op_skoleni)
btn.place(x=45, y=150)\

btn = Button(window, text="Piemērs", command=db_gramatas)
btn.place(x=255, y=175)
btn = Button(window, text="Piemērs", command=db_izsniegums)
btn.place(x=175, y=175)
btn = Button(window, text="Piemērs", command=db_kratuve)
btn.place(x=110, y=175)
btn = Button(window, text="Piemērs", command=db_skoleni)
btn.place(x=45, y=175)

window.mainloop()