# Example contact manager with a text based GUI that
# uses an SQL database to hold the data.
# sqlite3 contact manager
# Copyright Jay Summet

import sqlite3              #DB engine
import os                   #For detecting if the DB file exists yet.

#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 contacts
                        (id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
                        name TEXT NOT NULL,
                        email TEXT,
                        phone TEXT)""" )
    
    
    
#This function will connect to the database (if it exists!)
#If it doesn't exit, it will create it, and add our table.
def connectOrCreate(filename):
    if os.path.exists(filename):
       db = sqlite3.connect(filename)
       return(db)
    else:
        db = sqlite3.connect(filename)
        createTable(db)
        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 contacts (name, email, phone) VALUES (?,?,?)"
    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 contacts WHERE name LIKE ? OR phone LIKE ? OR email LIKE ?", (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 contacts" )
    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 contacts"

    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 contacts SET name=? WHERE id=?"
        myC.execute(sql, (name, idNum)  )

    elif changeWhat == "P":
        phone = input("Enter the new phone:")
        sql = "UPDATE contacts SET phone=? WHERE id=?"
        myC.execute(sql, (phone, idNum) )
    elif changeWhat == "E":
        email = input("Enter the new Email:")
        sql = "UPDATE contacts SET email=? WHERE id =?"
        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 = connectOrCreate("contacts.sqlite3")
mainLoop(db)