# Example contact manager with a text based GUI that
# uses an SQL database to hold the data.
# Uses the pymysql DB connection module
# Copyright Jay Summet

import pymysql              #DB engine


#This function creates our people table.
#Note that sqlite uses "AUTOINCREMENT" and not "AUTO_INCREMENT" as
#MySQL does.
def createTable(db):
    cursor = db.cursor()
    cursor.execute("""CREATE TABLE people
                        (id INTEGER PRIMARY KEY AUTO_INCREMENT UNIQUE NOT NULL,
                        name TEXT NOT NULL,
                        email TEXT,
                        phone TEXT)""" )
    
    
    
#This function will connect to the database (if it exists!)
#We assume the table has already been created! (If not, call createTable() above)
def connectMySQL(hostname, username, password):

    db = pymysql.connect( host = hostname, passwd = password,
                            user = username, db='cs1803_ie')

    return(db)


    
    
    

#Show the main user interface:
def printMenu():
    menu = """+-----------------------+
|   1. Enter a Person   |
|   2. Lookup a Person  |
|   3. Update Person    |
|   q. Quit.            |
+-----------------------+"""
    print(menu)

#Adds a person to the database. Note that sqlite3 uses the 'qmark'
# format style, so we use question marks to indicate where we want
# data to be replaced by our parameters.
def addPerson(db, N, E, P):
    sql = "INSERT INTO people (name, email, phone) VALUES (%s,%s,%s)"
    cursor = db.cursor()
    cursor.execute( sql, (N,E,P) )
    db.commit()
    
    
#Prompts the user for information about a person to add to the
#database, then calls the addPerson function
def addMenu(db):
    name = input("Enter the person's full name:")
    email = input("Enter email, or <enter> for none:")
    phone = input("Enter phone number, or <enter> for none:")

    addPerson(db, name, email, phone)
    
#This function will search for a term in the database and
#return a list of people who match the search term.
def personLookup(db, term):
    st = "%" + term + "%"
    cursor = db.cursor()
    cursor.execute("SELECT * FROM people WHERE name LIKE %s OR phone LIKE %s OR email LIKE %s", (st,st,st) )
    myData = []
    for item in cursor:
        myData.append(item)
    return(myData)

    
#GUI to ask for the search term and print the results. Calls
# personLookup function for the actual search.
def menuLookup(db):
    term = input("Enter your search term:")
    pData = personLookup(db, term)
    for i in pData:
        print(i)

#This function will count the total number of records in the
#database, and return it as an integer.
def countPeople(myDb):

    myDude = myDb.cursor()
    myDude.execute("SELECT COUNT(id) FROM people" )
    myData = myDude.fetchone()
    number = myData[0]
    return(number)
    
    
    
def showAll(db):
    count = countPeople(db)
    print("The database has {} people listed, as follows".format( count) )

    sql = "SELECT * FROM people"

    cursor = db.cursor()
    cursor.execute(sql)

    for i in cursor:
        print(i)
    
def updatePerson(db):
    showAll(db)
    idStr = input("Enter the ID of the person you want to update:")
    idNum = int(idStr)

    changeWhat  = input("What do you want to change? Name/Phone/Email (N/P/E):")

    myC = db.cursor()

    if changeWhat == "N":
        name = input("Enter the new name:")
        sql = "UPDATE people SET name=%s WHERE id=%s"
        myC.execute(sql, (name, idNum)  )

    elif changeWhat == "P":
        phone = input("Enter the new phone:")
        sql = "UPDATE people SET phone=%s WHERE id=%s"
        myC.execute(sql, (phone, idNum) )
    elif changeWhat == "E":
        email = input("Enter the new Email:")
        sql = "UPDATE people SET email=%s WHERE id =%s"
        myC.execute(sql, (email,idNum) )

    else: #Unknown entry!
        print("Didn't understand what to update!")
        return

    myC.close() #Semi-optional...
    db.commit() # Important!

    


        
    
#The main loop of the text based GUI:
def mainLoop(db):

    
    printMenu()

    action = input("Enter the number that you want to do:")
    while( action != "q"):
        if action == "1":
            addMenu(db)
        elif action == "2":
            menuLookup(db)
        elif action == "3":
            updatePerson(db)
        else:
            print ("unknown command! Try again!")
        printMenu()
        action = input("Enter the number that you want to do:")

#The main program. Connect to the database, then run the main loop.
db = connectMySQL("academic-mysql.cc.gatech.edu", "cs1803_ie", "SECRETPASSWRD")
#call createTable here if the database does not have a table in it!
mainLoop(db)