

import datetime
import sqlite3 as lite
import sys
import web, random

# APPLICATION-SPECIFIC




urls = ( '/(.*)', 'index')

# relates after the url /xxx to the server that should be called.

# What does this template.render thing. 
render = web.template.render('templates')


class Constants:
	x = 10
	users = {}
	colors = ['red','blue','green']
	bet_messages = []
	winnertake = 0.99 # amount the winner gets of nominal bet
	houseaccount = -1 # id of house account

count = 0
lastx = ""

# call with /?name=Joe
class index:
    def GET(self,name):
	# split name on equal
	namefields = name.split("=")
	print "self is", self
	print "name is", name
	postquestion = web.input(name=None)
	print "postquestion is", postquestion
	print "postquestion[function]: ", postquestion['function']
	print "postquestion[argument]: ", postquestion['argument']
	print "postquestion[longquery]: ", postquestion['longquery']
	if 2 > len(namefields):
	  namefields = [postquestion['function'], postquestion['argument']]
	# if the first string is payout  then call payout (still need to 
	#  remove bet in order to prevent two calls to payout for the same bet)
	#  e.g. http://0.0.0.0:8080/payout=13
	if namefields[0] == "payout":
        	return render.index(payout(int(namefields[1])))
	# else if first string is findbet then take conditions as a string
	#   and search each string for any of the comma separated entities
	#   return them ordered by number of conditions that match, most first
	#  e.g. http://0.0.0.0:8080/findbet=team1='Cavaliers',moneyline>210
	elif namefields[0] == "findbet":
        	return render.index(findbet('='.join(namefields[1:])))
	# else if first string is proposebet then take all the information (comma-separated)
	#   make sure that the user id has enough in his account to do this and if so
	#   add the amount that can be lost to the committed
	#  e.g. http://0.0.0.0:8080/proposebet=1,123,'LeBron points>33;Stroudemayer points<10',2,0,300,0,-250,-1,-1
	elif namefields[0] == "proposebet":
        	return render.index(proposebet(namefields[1]))
	# else if first string is takebet then take all the information (comma-separated)
	#   make sure that the user id has enough in his account to do this and if so
	#   add the amount that can be lost to the committed
	#  e.g. http://0.0.0.0:8080/takebet=15,3,500
	elif namefields[0] == "takebet":
        	return render.index(takebet(namefields[1]))
	# else if first string is killbet then take conditions as a string
	# reduce the maximummount to committed; if 0 then delete
	#  e.g. http://0.0.0.0:8080/killbet=15
	elif namefields[0] == "killbet":
        	return render.index(killbet(int(namefields[1])))
	else:
		return 'Unrecognized command'
	

# APPLICATION-SPECIFIC

# Split on comma and then search each condition
def findbet(myfields):
 print "myfields is: ", myfields
 myfieldsarr = myfields.split(",")
 print "myfieldsarr is: ", myfieldsarr
 con = lite.connect('bet.db')
 with con:    
   cur = con.cursor()    
   cur.execute("DROP TABLE IF EXISTS tmp")
   cur.execute("CREATE TABLE tmp(startdate INT, starttime INT, location TEXT, team1 TEXT, team2 TEXT, betid INT,  predicatestring TEXT, maximumamount INT, committed INT, moneyline TEXT)")
   if len(myfieldsarr) == 1:
     x = "SELECT game.startdate, game.starttime, game.location, game.team1, game.team2, betdescription.betid, betdescription.predicatestring, betdescription.maximumamount, betdescription.committed, betdescription.moneyline FROM betdescription, game WHERE game.gameid = betdescription.gameid and " + myfieldsarr[0]
     print "len 1 x", x
     cur.execute(x)
     rows = cur.fetchall()
     print "One field. Rows are: ", rows
   else:
     x = "INSERT INTO tmp SELECT game.startdate, game.starttime, game.location, game.team1, game.team2, betdescription.betid, betdescription.predicatestring, betdescription.maximumamount, betdescription.committed, betdescription.moneyline FROM betdescription, game WHERE game.gameid = betdescription.gameid and " + myfieldsarr[0]
     print "within else, creating tmp, here is x: ", x
     cur.execute(x)
     cur.execute("DROP TABLE IF EXISTS tmp2")
     cur.execute("CREATE TABLE tmp2(betid INT)")
     for cond in myfieldsarr[1:]:
   	x = "INSERT INTO tmp2 SELECT tmp.betid FROM tmp where " + cond
   	# Would like a WHERE clause that searches for strings
	print "within myfieldsarr[1:], here is x: ", x
   	cur.execute(x)
     cur.execute("DROP TABLE IF EXISTS tmp3")
     cur.execute("CREATE TABLE tmp3(betid INT, mycount INT)")
     x = "INSERT INTO tmp3 SELECT betid, count(*) FROM tmp2 group by betid "
     cur.execute(x)
     x = "SELECT tmp.startdate, tmp.starttime, tmp.location, tmp.team1, tmp.team2, tmp.betid, tmp.predicatestring, tmp.maximumamount, tmp.committed, tmp.moneyline, tmp3.mycount FROM tmp, tmp3 WHERE tmp.betid = tmp3.betid ORDER BY mycount"
     # x = "SELECT tmp.startdate, tmp.location, tmp.team1, tmp.team2, tmp.betid, tmp.predicatestring, tmp.maximumamount, tmp.committed, tmp.moneyline FROM tmp"
     cur.execute(x)
     rows = cur.fetchall()
     print "rows after join: ", rows
   out = ''
   for row in rows:
          out+= ' startdate: '+ str(row[0])+ ' starttime: ' + str(row[1]) + ' location: '+ str(row[2]) + ' team1 : '+ str(row[3]) + ' Team 2: ' + str(row[4]) + ' betid: ' + str(row[5])  + '<br>'
   return out

# Proposebet tries to establish a new bet for a user where the user
# asserts the associated predicate.
# We have to check that the user has enough in his account and we have to
# adjust committed for that user. Then we create a new id for the bet description
def proposebet(myfields):
 print "myfields is: ", myfields
 myfieldsarr = myfields.split(",")
 print "myfieldsarr is: ", myfieldsarr
 con = lite.connect('bet.db')
 amttobet = int(myfieldsarr[5])
 moneyline = int(myfieldsarr[7])
 if moneyline > 0:
	newcommitted = (0.0 + (amttobet * 100)) / moneyline
 if moneyline < 0:
	newcommitted = (0.0 + (amttobet * abs(moneyline))) / 100
 myuserid = int(myfieldsarr[0])
 with con:
   cur = con.cursor()
   totaltoproposer = 0
   x = "SELECT userid, balance, committed FROM account  WHERE userid = " + str(myuserid)
   cur.execute(x)
   rows = (cur.fetchall())
   if 0 == len(rows):
	return 'User id: ' + str(myuserid) + ' does not exist. Sorry.'
   row = rows[0]
   if (row[1] - row[2]) < newcommitted:
	return 'User id: ' + str(myuserid) + ' does not have a sufficient net balance to commit to this bet. He/she would need to commit: ' + str(newcommitted) + ' but only has a balance of ' + str(row[1]) + ' and has already committed: ' + str(row[2])
   x = "UPDATE account SET committed = committed + " + str(newcommitted) + " WHERE userid = " + str(myuserid)
   cur.execute(x)
   x = "SELECT max(betid) FROM betdescription"
   cur.execute(x)
   rows = (cur.fetchall())
   if 0 == len(rows):
	nextid = 10
   else:
	nextid = rows[0][0]+1
   x = "INSERT INTO betdescription VALUES( " + str(nextid) + ", " + str(myfieldsarr[0]) + ", " + str(myfieldsarr[1])+ ", " +  myfieldsarr[2]+ ", " +  str(myfieldsarr[3])+ ", " +  str(myfieldsarr[4])+ ", " +  str(myfieldsarr[5])+ ", " +  str(myfieldsarr[6])+ ", " +  str(myfieldsarr[7])+ ", " +  str(myfieldsarr[8])+ ", " +  str(myfieldsarr[9]) + ")"
   print "insert statement: ", x
   cur.execute(x)
   return 'User id: ' + str(myuserid) + ' has successfully proposed a bet with commitment ' + str(newcommitted) + ' on a bet of size ' + str(amttobet) 
  
  
# Takebet tries to take part of a proposed bet.
# We have to check that the user has enough in his account and we have to
# adjust committed for that user. The we insert into bettaken 
#  e.g. http://0.0.0.0:8080/takebet=14,3,500
def takebet(myfields):
 print "myfields is: ", myfields
 myfieldsarr = myfields.split(",")
 print "myfieldsarr is: ", myfieldsarr
 con = lite.connect('bet.db')
 betid = int(myfieldsarr[0])
 myuserid = int(myfieldsarr[1])
 amttobet = int(myfieldsarr[2])
 with con:
   cur = con.cursor()
   totaltoproposer = 0
   x = "SELECT userid, balance, committed FROM account  WHERE userid = " + str(myuserid)
   cur.execute(x)
   rows = (cur.fetchall())
   if 0 == len(rows):
	return 'User id: ' + str(myuserid) + ' does not exist. Sorry.'
   row = rows[0]
   x = "SELECT maximumamount, committed, moneyline FROM betdescription  WHERE betid = " + str(betid)
   cur.execute(x)
   rows = (cur.fetchall())
   if 0 == len(rows):
	return 'Bet id: ' + str(betid) + ' does not exist. Sorry.'
   # We don't need this moneyline logic for the taker, but we do have to 
   # understand the effect on the commitment
   betdescrow = rows[0]
   # ??? Take care of betdescription
   if (row[1] - row[2]) < amttobet:
	return 'User id: ' + str(myuserid) + ' does not have a sufficient net balance to commit to this bet. He/she would need to commit: ' + str(amttobet) + ' but only has a balance of ' + str(row[1]) + ' and has already committed: ' + str(row[2])
   if (betdescrow[0] - betdescrow[1]) < amttobet:
	return 'Bet id: ' + str(betid) + ' cannot allow this bet of size ' + str(amttobet) + ' but only of size ' + str(betdescrow[0] - betdescrow[1])
   x = "UPDATE account SET committed = committed + " + str(amttobet) + " WHERE userid = " + str(myuserid)
   cur.execute(x)
   x = "UPDATE betdescription SET committed = committed + " + str(amttobet) + " WHERE betid = " + str(betid)
   cur.execute(x)
   x = "INSERT INTO bettaken VALUES( " + str(betid) + ", " + str(myuserid) + ", " + str(amttobet) + ")"
   print "insert statement: ", x
   cur.execute(x)
   return 'User id: ' + str(myuserid) + ' has successfully taken bet ' + str(betid) + ' on a bet of size ' + str(amttobet) 
  
  
   

# Payout determines whom to pay and how much
# If true, then proposer is paid whatever  the taker bets and taker loses that
# amount. If false, then taker gets what taker bets/(moneyline/100).
def payout(betid):
 con = lite.connect('bet.db')
 with con:    
  cur = con.cursor()    
  # x = "BEGIN TRANSACTION"
  # cur.execute(x)
  flag_moneyline_user = evalpred(cur, betid)
  if 'No such bet' == flag_moneyline_user:
	return 'Bet ' + str(betid) + ' no longer exists. Probably it has already been taken care of. '
  totaltoproposer = 0
  x = "SELECT userid, amount FROM bettaken  WHERE betid = '" + str(betid)  + "'"
  cur.execute(x)
  rows = cur.fetchall()
  # It would be better to do this as one massive join, but can't do this
  # in sqlite. We would join bettaken and account to update account.balance
  # based on all the users..
  # Something like an update that joins with bettaken
  # 
  # This is how how the r[1] field (the moneyline) works:
  # If the person taking the bet, bets say $100 and the moneyline is 300,
  # then the taker gets $100/3 as a winning.
  # If the moneyline is say -400, then the taker gets $400 as a winning.
  # Whatever proposer pays, the proposer pays an additional rake to the house.
  # If the taker loses, then taker loses $100 + rake * $100.
  for r in rows:
	if flag_moneyline_user[0]:
		amt = int(r[1])
		totaltoproposer+= amt
		x = "UPDATE account SET balance= balance - " + str(amt) + ", committed= committed - " + str(amt) + " WHERE userid = " + str(r[0])
		cur.execute(x)
	else:
		print "r[1] is: ", r[1]
		print "flag_moneyline_user[1] is: ", flag_moneyline_user[1]
		amt = int(r[1])
		if amt > 100:
		  amt = (0.0 + (amt * 100))/float(flag_moneyline_user[1])
		  # proposer pays out less by this factor.
		elif amt < -100:
		  amt = (0.0 + (amt * abs(float(flag_moneyline_user[1]))))/100
		  # proposer pays out more by the absolute value of this
		totaltoproposer-= amt
		x = "UPDATE account SET balance= balance + " + str(Constants.winnertake * amt) + ", committed= committed - " + str(amt)  + " WHERE userid = " + str(r[0])
		print "x is: ", x
		cur.execute(x)
  # pay off or take money from proposer
  if totaltoproposer > 0:
  	x = "UPDATE account SET balance= balance + " + str(Constants.winnertake * totaltoproposer) + ", committed= committed - " + str(totaltoproposer)+ " WHERE userid = " + str(flag_moneyline_user[2])
  	cur.execute(x)
  else:
  	x = "UPDATE account SET balance= balance + " + str(totaltoproposer) + ", committed= committed - " + str(totaltoproposer) + " WHERE userid = " + str(flag_moneyline_user[2])
  	cur.execute(x)
  x = "UPDATE account SET balance= balance + " + str((1.0-Constants.winnertake) * abs(totaltoproposer)) + " WHERE userid = " + str(Constants.houseaccount)
  print "updating house account: ", x
  cur.execute(x)
  # x = "END TRANSACTION"
  # cur.execute(x)
  x = "DELETE FROM betdescription WHERE betid = " + str(betid)
  cur.execute(x)
  x = "DELETE FROM bettaken WHERE betid = " + str(betid)
  cur.execute(x)
  return generatefromaccount(cur)
  
  
   

# see whether the proposer of the bet won or lost
def evalpred(cur, betid):
       x = "SELECT gameid, predicatestring, minnumpreds, moneyline, proposerid FROM betdescription  WHERE betid = '" + str(betid)  + "'"
       cur.execute(x)
       rows = cur.fetchall()
       if 0 == len(rows):
	  return 'No such bet'
       r = rows[0] # Should be only one row
       preds = r[1].split(";")
       mycount = 0
       for p in preds:
	  predparts = (p.strip()).split(" ")
	  x = "SELECT player, stattype, numvalue FROM result WHERE gameid = " +  str(r[0]) + " and player = '" + predparts[0] + "' and stattype = '" + predparts[1] + "'"
	  print x
	  cur.execute(x)
       	  pr = (cur.fetchall())[0] # Should be only one row
	  if (predparts[2] == "=")  and (int(pr[2]) == int(predparts[3])):
		mycount+= 1
	  if (predparts[2] == ">")  and (int(pr[2]) > int(predparts[3])):
		mycount+= 1
	  if (predparts[2] == "<")  and (int(pr[2]) < int(predparts[3])):
		mycount+= 1
	  print "mycount is ", mycount
     
       if  mycount >= int(str(r[2])):
          return [True, r[3], r[4]]
       else:
          return [False, r[3], r[4]]

         

# change balances for a betid based on who wins and the odds and amount bet
def adjustbalance(betid):
	return betid


def generatefromaccount(cur):
       out = 'generating an output for all accounts '
       x = "SELECT * FROM account " 
       print x
       cur.execute(x)
       rows = cur.fetchall()
       for row in rows[:3]:
          out+= ' ID: '+ str(row[0])+ ' balance: '+ str(row[1]) + ' Committed: ' + str(row[2]) + ' First name: '+ str(row[3]) + ' Second name: ' + row[4]  + '<br>'
       return out

def generatefromdb(name):
   global count
   out = 'generating an output for ' + name + ' with count: ' + str(count)
   count+= 1
   con = lite.connect('bet.db')
   with con:    
       cur = con.cursor()    
       x = "SELECT * FROM game WHERE team1 = '" + name + "' OR team2 = '" + name + "'"
       print x
       cur.execute(x)
   
       rows = cur.fetchall()
   
       for row in rows[:3]:
          out+= ' Game: '+ str(row[0])+ ' start date: '+ str(row[1]) + ' start time: '+ str(row[2]) + ' location: ' + row[3]  + ' team: ' + row[4] + ' other team: ' + row[5] + ' game type: ' + row[7] + '<br>'
   return out


if __name__ == '__main__':
    app = web.application(urls, globals())
    app.run()




