""" apswDemo.py
    Demonstrate connecting to SQLite
    with python using apsw
"""

import apsw
connection = apsw.Connection("dvd.db")
cursor = connection.cursor()

def buildData():
    """ creates data. Called only once """
    cursor.execute("DROP TABLE IF EXISTS movie")
    cursor.execute("DROP TABLE IF EXISTS rating")
    cursor.execute("DROP VIEW IF EXISTS vidRating")
    
    cursor.execute("CREATE TABLE movie (movieID integer primary key,  name varchar(20),  ratingID integer);")
    cursor.execute("INSERT INTO movie VALUES (null, 'Star Wars', 2)")
    cursor.execute("INSERT INTO movie VALUES (null, 'Empire Strikes Back', 2)")

    cursor.execute("CREATE TABLE rating (ratingID integer primary key, name varchar(10))")
    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')")

    cursor.execute("CREATE VIEW vidRating AS SELECT movie.name as 'video', rating.name as 'rating' FROM  movie, rating WHERE movie.ratingID = rating.ratingID")
           

def showMovies():
    """ display results of movies table """
    print "=== movies ==="
    result = cursor.execute("SELECT * FROM movie")
    for (id, name, rating) in result:
        print id, name, rating

    print

def showRatings():
    """ display results of ratings table """
    print "=== ratings ==="
    result = cursor.execute("SELECT * FROM rating")
    for (id, name) in result:
        print id, name

    print

def showVidRatings():
    """ display results of ratings table """
    print "=== videos and ratings ==="
    result = cursor.execute("SELECT * FROM vidRating")
    for (name, rating) in result:
        print name, 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))

def getRating():
    """allows the user to choose only from the valid ratings"""
    result = cursor.execute("SELECT * FROM rating")
    
    #create an empty list of legal values
    legalVals = []
    for (id, name) in result:
        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 * FROM vidRating WHERE video LIKE '%" + searchVal + "%'"

    result = cursor.execute(sql)

    for (movie, rating) in result:
        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":
            showVidRatings()
        elif choice == "2":
            searchMovie()
        elif choice == "3":
            addMovie()
        elif choice == "4":
            keepGoing = False
            
mainMenu()