# The betting game.
# We are going to assume that we are given inputs that either propose
# a bet, take part of a bet, process a result.
# Determine winners and losers.

import sqlite3 as lite
import sys

games = (
	(123, 20150801, 2000, 'Madison Square Garden', 'Knicks', 'Cavaliers', '', 'basketball'),
	(123, 20150801, 2000, 'Madison Square Garden', 'Cavaliers', 'Knicks',  '', 'basketball'),
	(456, 20150802, 2000, 'Madison Square Garden', 'Heat', 'Knicks', '', 'basketball'),
	(456, 20150802, 2000, 'Madison Square Garden', 'Knicks', 'Heat', '', 'basketball')
) 

results = (
	(123, 'LeBron', 'points', 43),
	(123, 'Stroudemayer', 'points', 20),
	(456, 'Stroudemayer', 'points', 30),
	(456, 'DWade', 'points', 14)
)

betdescriptions = (
	(13, 1, 123, 'LeBron points > 30', 1, 0, 1000, 0, 200, -1, -1),
	(14, 1, 123, 'LeBron points > 30; Stroudemayer points < 10', 2, 0, 1000, 0, 300, -1, -1)
)

def printresult(cur):
	rows = cur.fetchall()
	for r in rows:
		print r

try:
	con = lite.connect('bet.db')
	cur = con.cursor()
	cur.execute("DROP TABLE IF EXISTS team")
	cur.execute("CREATE TABLE team(team TEXT, player TEXT)")

	cur.execute("DROP TABLE IF EXISTS game")
	cur.execute("CREATE TABLE game(gameid INT, startdate INT, starttime INT, location TEXT, team1 TEXT, team2 TEXT, otherteams TEXT, gametype TEXT)")
	cur.executemany("INSERT INTO game VALUES(?, ?, ?, ?, ?, ?, ?, ?)",games)

	cur.execute("DROP TABLE IF EXISTS gametype")
	cur.execute("CREATE TABLE gametype(typeid TEXT, statallowed TEXT)")

	# minnumpereds is the minimum number of these semi-colon delimited
	# predicates that must be satisfied
	cur.execute("DROP TABLE IF EXISTS betdescription")
	cur.execute("CREATE TABLE betdescription(betid INT, proposerid INT, gameid INT, predicatestring TEXT, minnumpreds INT, allornothingflag INT, maximumamount INT, committed INT, moneyline TEXT, starttimewithingame INT, endtimewithingame INT)")
	cur.executemany("INSERT INTO betdescription VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",betdescriptions)


	cur.execute("DROP TABLE IF EXISTS result")
	cur.execute("CREATE TABLE result(gameid INT, player TEXT, stattype TEXT, numvalue REAL)")
	cur.executemany("INSERT INTO result VALUES(?, ?, ?, ?)",results)

	cur.execute("DROP TABLE IF EXISTS team")
	cur.execute("CREATE TABLE team(teamid INT, player TEXT)")
	print "Select sum(numvalue) from result" 
	cur.execute( "Select sum(numvalue) from result" )
	printresult(cur)
	print "Select sum(numvalue) from result where player = 'LeBron'" 
	cur.execute( "Select sum(numvalue) from result where player = 'LeBron'" )
	printresult(cur)
	print "Select sum(numvalue) from result group by player " 
	cur.execute( "Select sum(numvalue) from result group by  player " )
	printresult(cur)

	con.commit()

except lite.Error, e:

    if con:
        con.rollback()

    print "Error %s:" % e.args[0]
    sys.exit(1)

finally:

    if con:
        con.close()
