#Personal Contact manager, connects to MySQL database using pymysql.

import pymysql

def connect():
    db = pymysql.connect(
        host="academic-mysql.cc.gatech.edu",
        user="USERNAME",   passwd = "SECRET",
        db="DATABASENAME")
    return db

def createTable(db):
    sql = """ CREATE TABLE people
               (id INTEGER PRIMARY KEY AUTO_INCREMENT UNIQUE NOT NULL,
                name TEXT NOT NULL,
                email TEXT,
                age INTEGER)"""
    c = db.cursor()
    c.execute(sql)
    c.close()
    
    
def disconnect(db):
    db.close()
    

    
def addPerson(db, name, email, age):
    c = db.cursor()

    sql = """INSERT INTO people (name, email, age)
                 VALUES (%s, %s, %s)"""

    num = c.execute(sql, (name, email, age) )
    print("num affected:", num)

    c.close()
    db.commit()
    


def addMenu(db):
    name = input("Enter a name:")
    email= input("Enter an email:")
    age = input("Enter a age:")

    addPerson(db, name, email, age)


def lookupPerson(db):
    c = db.cursor()
    key = input("Enter search string:")
    fullKey =   "%" + key + "%"
    sql = """SELECT * FROM people
             WHERE name LIKE %s
                OR email LIKE %s"""

   
    num = c.execute(sql, (fullKey,fullKey ) )
    print("num is:", num)

    data = c.fetchall()
    for item in data:
        print(item[0], item[1], "- Age:",
              item[3], ":", item[2] )
    
    c.close()

def updatePerson(db):
    pId = input("Enter the ID of the person to update:")
    #pID = int(pId)
    changeWhat = input("What do you want to change? Name/Email/Age [N/E/A]:")
    c = db.cursor()

    if changeWhat == "N":
        name = input("Enter new name:")
        sql = "UPDATE people SET name=%s WHERE id=%s"
        c.execute(sql, (name, pId) )
    elif changeWhat == "E":
        phone = input("Enter new email:")
        sql = "UPDATE people SET email=%s WHERE id=%s"
        c.execute(sql, (phone, pId) )
    elif changeWhat == "A":
        age = input("Enter new age:")
        sql = "UPDATE people SET age=%s WHERE id=%s"
        c.execute(sql, (age, pId) )
        
        
    else:
        print("Option not recognized, must enter N, E or A2")
        
    
def printMenu():
    menu = """
    +--------------------+
    | 1. Add a Person    |
    | 2. Lookup a Person |
    | 3. Update a Person |
    | q. Quit            |
    +--------------------+"""
    print(menu)
    

    
db = connect()
#createTable(db) #We only need to do this once.

printMenu()
action = input("Enter what you want to do:")
while action != "q":
    if action == "1":
        addMenu(db)
    elif action == "2":
        lookupPerson(db)
    elif action == "3":
        updatePerson(db)

        
    printMenu()
    action = input("Enter what you want to do:")


disconnect(db)