""" sqlite python demo"""

from pysqlite2 import dbapi2 as sqlite
connection = sqlite.connect('test.db')
cursor = connection.cursor()

def buildData():    
    
    cursor.execute("DROP TABLE movie")
    cursor.execute("DROP TABLE rating")
    
    cursor.execute("""
    CREATE TABLE movie (
      movieID integer primary key,
      name varchar(30),
      ratingID integer
    )""")

    cursor.execute("INSERT INTO movie VALUES (null, 'Cars', 1)")
    cursor.execute("INSERT INTO movie VALUES (null, 'Cars Gone Bad', 4)")

    cursor.execute("""
    CREATE TABLE rating (
      ratingID integer primary key,
      name varchar(30)
    )""")

    cursor.execute("INSERT INTO rating VALUES (null, 'G')")
    cursor.execute("INSERT INTO rating VALUES (null, 'PG')")
    cursor.execute("INSERT INTO rating VALUES (null, 'PG-13')")
    cursor.execute("INSERT INTO rating VALUES (null, 'R')")


    connection.commit()
  
def showMovies():
    #show movies
    cursor.execute("SELECT * FROM movie")

    recordset = cursor.fetchall()

    print "========== movies =================="

    for (id, name, rating) in recordset:
        print "================================"
        print "movieID: %s" % id
        print "name: %s" % name
        print "ratingID: %s" % rating
        print

def showRatings():    
    #show ratings
    cursor.execute("SELECT * FROM rating")
    recordset = cursor.fetchall()

    print "========== ratings ================="
    for (id, rating) in recordset:
        print "================================"
        print "ratingID: %s" % id
        print "rating: %s" % rating
        print

def showJoin():
    cursor.execute("""
    SELECT
      movie.name AS 'movie',
      rating.name AS 'rating'
    FROM
      movie, rating
    WHERE
      movie.ratingID = rating.ratingID
    """)

    recordset = cursor.fetchall()

    print "========== joined =================="
    for (name, rating) in recordset:
        print "================================"
        print "movie: %s" % name
        print "rating: %s" % rating
        print

def addMovie():
    """allows user to add a movie to the system"""
    name = raw_input("Movie name? ")
    #rating = raw_input("Rating? ")
    rating = getRating()

    cursor.execute("INSERT INTO movie VALUES (null, '%s', %s)" % (name, rating))
    connection.commit()

    #showMovies()

def getRating():
    """allows the user to choose only from the valid ratings"""
    cursor.execute("SELECT * FROM rating")
    recordset = cursor.fetchall()
    
    #create an empty list of legal values
    legalVals = []
    for (id, name) in recordset:
        print "%d) %s" % (id, name)
        legalVals.append(str(id))

    choice = 999
    while choice not in legalVals:
        choice = raw_input("rating: ")

    return choice

def searchMovie():
    """searches for a string in the movie title field"""
    searchVal = raw_input("search for? ")

    sql = """
    SELECT
      movie.name,
      rating.name
    FROM
      movie, rating
    WHERE
      movie.ratingID = rating.ratingID
    AND
      movie.name LIKE '%""" + searchVal + "%'"

    cursor.execute(sql)

    recordset = cursor.fetchall()
    for (movie, rating) in recordset:
        print "%s (%s)" % (movie, rating)

def mainMenu():
    keepGoing = True
    while keepGoing:
        print """
        1) Show all movies
        2) Search for a movie
        3) Add a movie       
        4) Quit """

        choice = 999
        while choice not in ("1", "2", "3", "4"):
            choice = raw_input("Please choose 1-4: ")

        if choice == "1":
            showJoin()
        elif choice == "2":
            searchMovie()
        elif choice == "3":
            addMovie()
        elif choice == "4":
            keepGoing = False
            
mainMenu()