import sqlite3

def menu():
    print()
    print()

    print("\t\t Employee Database")
    print()
    print("a. View All Employees: ")
    print()
    print("b. Add a New Employee: ")
    print()
    print("c. Update an Employee: ")
    print()
    print("d. Delete an Employee: ")
    print()
    print("q. Quit")
    print()
    choice = input("Enter your choice: ")
    return choice[0].lower()

def DB_Connect():
    try:
        global conn
        global cur
        conn = sqlite3.connect("company.db")
        cur = conn.cursor()
        # cur.execute("drop table if exists employee")
        cur.execute("CREATE TABLE if NOT exists employee \
        (empID INTEGER PRIMARY KEY AUTOINCREMENT,\
        empName VARCHAR (25) NOT NULL,\
        empJob VARCHAR (25),\
        empSal DECIMAL (7, 2) NOT NULL, \
        empImgLoc VARCHAR (25),\
        empStatus tinyint(1) NOT NULL DEFAULT 1)")
        conn.commit()
        # conn.close()
    except Error as e:
            print(e)



def Add(name, job, sal, imageloc):
    cur.execute("insert into employee\
    values (null,?,?,?,?,?)",\
    (name, job, sal, imageloc,
    1))
    conn.commit()
    cur.execute("SELECT name FROM sqlite_master WHERE type ='table'")
    print(cur.fetchall())
    View()


def View():
    conn = sqlite3.connect("company.db")
    cur = conn.cursor()
    cur.execute('SELECT * FROM employee')
    rows = cur.fetchall()
    conn.close()

def update(id,name,job,sal,image,status):
    print(id,name,job,sal,image,status)
    conn = sqlite3.connect("emp.db")
    cur=conn.cursor()
    cur.execute("UPDATE employee set empName=?, empJob =?,\
    empSal =?, empImgLoc = ?, empStatus =? \
    WHERE empID=?", (name,job,sal,image,status,id))
    conn.commit()

##def Update(id, name, job, sal, image, status):
##    print(id, name, job, sal, image, status)
##    conn = sqlite3.connect("emp.db")
##    cur = conn.cursor()
##    cur.execute("UPDATE employee set empName = ?, empJob = ?, empSal = ?, empImgLoc = ?, empStatus = ? \
##                WHERE empID = ?", (name, job, sal, image, status, id))
##    conn.commit()


def delete(id):
    cur.execute("delete from employee where empID=?", (id,))
    conn.commit()

def Search(id="", name=""):
    conn = sqlite3.connect("company.db")
    cur = conn.cursor()
    cur.execute("SELECT * FROM employee WHERE empID= ? or empName = ?", (id, name))
    conn.commit()
    conn.close()

##def insert(name,job,sal,imageloc):
##  conn = sqlite3.connect("emp.db")# no need to execute dbconn() - why?
##  cur=conn.cursor()
##  cur.execute("insert into employee values
##  (null,?,?,?,?,?)",(name,job,sal,imageloc,1))
##  conn.commit()
##  conn.close()
##
def main():
    DB_Connect()
    cur.execute("SELECT name FROM sqlite_master WHERE type = 'table'")
    print(cur.fetchall())


    while True:
        choice = menu()

        if choice == 'a':
            print()
            print("View All Selected")
            print()
            View()


        elif choice == 'b':
            print()
            print("Add a New Employee Selected")
            Add('test', 'tester', 12344, 'img/test1.png')
        elif choice == 'c':
            print()
            print("Update an Employee Selected")
            print()
            update(id,name,job,sal,image,status)

        elif choice == 'd':
            print()
            print("Delete an Employee Selected")
            print()
            Delete(id)

        elif choice == 's':
            print()
            print("Search an Employee Selected")
            print()
            Search(id="", name="")

        elif(choice == 'q'):
            print()
            print("you quit, bye")
            print()
            break

        else:
            print()
            print("invalid choice - try again")
            print()
            conn.close()
main()