####################################
# SQLite/Py Database Manager       #
# ?                                #
####################################


import sqlite3, time, datetime, random
from tkinter import *




class Window(Frame):
    def __init__(self, master = None):
        Frame.__init__(self, master)

        self.master = master

        self.init_window()

    def init_window(self):

        self.master.title("Database Manager v. 0.0.1")


        self.myButton = Button(self.master, text = 'Click to enter text', command=self.print_message)
        self.myButton.grid(row=2, column=2)

        self.usertext = StringVar()
        self.myentry = Entry(self.master, textvariable=self.usertext)
        #make widget visible
        self.myentry.grid(row=1, column=0)


        menu = Menu(self.master)
        self.master.config(menu=menu)

        file = Menu(menu)

        file.add_command(label='Exit', command=self.client_exit)


        menu.add_cascade(label='File', menu=file)

        edit = Menu(menu)

        edit.add_command(label='Undo')
        menu.add_cascade(label='Edit', menu=edit)

    def print_message(self):
        print (self.usertext.get())

    def client_exit(self):
        exit()

window = Window

root = Tk()
root.geometry("400x300")

app = Window(root)
root.mainloop()




class DatabaseManager(object):
    def __init__(self, db):
        self.conn = sqlite3.connect(db)
        self.conn.execute('pragma foreign_keys = on')
        self.conn.commit()
        self.c = self.conn.cursor()

# Create a query if there are not paramaters to be passed
    def query_params(self, arg, params=None):
        self.c.execute(arg, params)
        self.conn.commit()
        return self.c
# Create a query if there are parameters that need tob e passed
    def query(self, arg):
        self.c.execute(arg)
        self.conn.commit()
        return self.c

#fetch info on description of execution
    def fetch(self):
        return self.c.fetchall()

    def __del__(self):
        self.conn.close()

conn = sqlite3.connect("connect.db")
c = conn.cursor()

dbmgr = DatabaseManager("connect.db")

#print(pygame.font.get_fonts())

while 1 > 0:
    print ("\n")
    #Creates a table in the sql db based on user input

    ### ADD PRIMARY KEY ###
    def create_table():
        name = input("Enter name of the table you want to create: ")
        values = []
        table = True

        while table:
            val = input("\nEnter values and value type e.g.: TELNUM INT, press return key for each value entered. Once all values entered, type 'done': ")

            if val != 'done':
                values.append(val)
                print (values)

            else:
                output = ""
                for i in values:
                    output += i + ","
                output = output[:-1]
                query = "CREATE TABLE " +name+ "("+output+")"
                dbmgr.query(query)
                table = False


#creates as any rows as there are names in john.txt

    def quantity():
        x = random.randint(1000000, 9999999)
        file = open("john.txt", "r")

        for line in file:
            query = "INSERT INTO test (playerNAME, playerID) VALUES (?, ?)"
            params = (line, x)
            dbmgr.query_params(query, params)

            x = random.randint(1000000, 9999999)


#deletes specified table from db

    def drop_table():
        i = 0
        tables = []
        dbmgr.query("SELECT name FROM sqlite_master WHERE type='table';")
        rows = c.fetchall()
        for row in rows:
            method = str(i)+". "+str(row[0])
            tables.append(method)
            i += 1

        print (tables)
        user_input = int(input("Select table number you would like to delete: "))
        drop = (tables[user_input][3:])
        #will result in error if there are more than 9 tables and one of the 10+ is selected
        dbmgr.query("DROP TABLE " + drop)

#Insert or change values in a table in the current database
    def modify_values():
        available_tables = []


        def insert_values():

            dbmgr.query("SELECT name FROM sqlite_master WHERE type='table';")
            i = 0
            rows = dbmgr.fetch()
            for row in rows:
                    method = str(i)+". "+str(row[0])
                    available_tables.append(method)
                    i += 1


            print (available_tables)

            try:
                user_input = int(input("Enter number of table to enter values into:\n~ "))

            except (ValueError, IndexError):
                print ("error")

            edit = available_tables[user_input][3:]
            dbmgr.query("SELECT * FROM " +edit)
            columns = dbmgr.c.description


            values = [x[0] for x in columns]
            values = str(values)
            values = values.replace("'", "")
            values = values.replace("[", "(")
            values = values.replace("]", ")")


            user = input("Enter values you would like to insert into the table you chose, e.g.\n 'EmployeeName', 5032346345, 'EmployeeName@gmail.com':\n~ ")
            user2 = input("Enter values you would like to insert into the table you chose, e.g.\n 'EmployeeName', 5032346345, 'EmployeeName@gmail.com':\n~ ")
            query = "INSERT INTO " +edit+" "+values+" VALUES (?, ?)"

            dbmgr.query_params(query, (user, user2))




        def change_values():
            tables = []
            available_tables = []

            dbmgr.query("SELECT name FROM sqlite_master WHERE type='table';")
            i = 0
            rows = c.fetchall()
            for row in rows:
                    method = str(i)+". "+str(row[0])
                    available_tables.append(method)
                    i += 1



            print (available_tables)
            user_input = int(input("Enter name of table to change values:\n~ "))
            dbmgr.query("SELECT * FROM "+available_tables[user_input][3:])
            rows = c.fetchall()
            for row in rows:
                tables.append(row)
            print (tables)



        print ("1. Change values~\n2. Insert values~")

        user = input("Select which number to change or insert values into a table:\n~ ")
        if user == '2':
            insert_values()
        elif user == '1':
            change_values()




    #Extract info from db and prints to python console
    def read_from_db():


        tables = []
        query = "SELECT name FROM sqlite_master WHERE type='table'"
        dbmgr.query(query)
        print (dbmgr.fetch())




    ##    c.execute('SELECT * FROM stuffToPlot')
    ##    rows = c.fetchall()
    ##    for row in rows:
    ##        print(row)

    #Attempt to see if the user has connected to the database
    ##def connected():
    ##    try:
    ##        c.execute('SELECT * FROM stuffToPlot')
    ##        myfont = pygame.font.SysFont("courier", 15)
    ##        connText = myfont.render("Connected to Database ["+db+"]", 1, (100,255, 0))
    ##        screen.blit(connText, (50, 100))
    ##    except sqlite3.OperationalError or sqlite3.ProgrammingError:
    ##        myfont = pygame.font.SysFont("courier", 15)
    ##        connText = myfont.render("Cannot Connect to Database ["+db+"]", 1, (100,255, 0))
    ##        screen.blit(connText, (50, 100))



    #######################################################
    #######################################################

    #asks the user how to alter db

    user_input = input("Enter either: \n1. View Table in Current Working DB~\n2. Create a table and enter values~\n3. Delete table~\n4. Insert/Change table values\n5. Show Power of SQL\n~ ")
    if user_input == '1':
        read_from_db()

    elif user_input == '2':
        create_table()

    elif user_input == '3':
        drop_table()

    elif user_input == '4':
        modify_values()

    elif user_input == '5':
        quantity()

conn.close()