#!/usr/bin/python
# setupdb.py: initializes the database tables used by the rlg system.

import rlgalldb as rlgall
import psycopg2

webuser = "webserver"
allowquery = "GRANT SELECT ON {0} TO " + webuser + ";"

dbconn = psycopg2.connect("dbname=rlg")
dbcur = dbconn.cursor()

dbcur.execute("CREATE TABLE games ( gname varchar(20), offbytes int );")
dbcur.execute("CREATE TABLE players (pname varchar(20) PRIMARY KEY);")
dbconn.commit()

for game in rlgall.gamelist:
  dbcur.execute("INSERT INTO games VALUES (%s, %s);", (game.uname, 0))

  createquery = "CREATE TABLE " + game.uname + " ( "
  for i, field in enumerate(game.sqltypes.keys()):
    createquery += "{0} {1}".format(field, game.sqltypes[field])
    if field == "name":
      createquery += " REFERENCES players(pname)"
    if i == len(game.sqltypes) - 1:
      createquery += " )"
    else:
      createquery += ", "
  createquery += ";"
  #print createquery
  dbcur.execute(createquery)
  dbcur.execute(allowquery.format(game.uname))
  dbconn.commit()

dbcur.close()
dbconn.close()
exit()