#!/usr/bin/python

import datetime, getpass
from ConfigParser import ConfigParser
import MySQLdb as mysql

qual = "qualification"
final = "elimination"
pract = "practice"

setupDBQuery = """SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE TABLE IF NOT EXISTS `matchdata` (
  `matchid` int(11) NOT NULL,
  `ball_laps_red` tinyint(4) NOT NULL,
  `ball_laps_blue` tinyint(4) NOT NULL,
  PRIMARY KEY  (`matchid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `matches` (
  `matchid` int(10) unsigned NOT NULL auto_increment,
  `datetime` datetime NOT NULL,
  `type` enum('practice','qualification','elimination') NOT NULL default 'practice',
  `redscore` int(11) default NULL,
  `redpenalty` int(11) default NULL,
  `bluescore` int(11) default NULL,
  `bluepenalty` int(11) default NULL,
  `notes` text NOT NULL,
  PRIMARY KEY  (`matchid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `robots` (
  `teamnum` int(11) NOT NULL,
  `botname` varchar(64) NOT NULL,
  `auto` int(11) NOT NULL,
  `speed` int(11) NOT NULL,
  `herd` int(11) NOT NULL,
  `hurdle` int(11) NOT NULL,
  `kickoff` int(11) NOT NULL,
  PRIMARY KEY  (`teamnum`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `ruleset` (
  `0` int(11) NOT NULL,
  PRIMARY KEY  (`0`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `ruleset` (`0`) VALUES
(2008);

CREATE TABLE IF NOT EXISTS `settings` (
  `id` tinyint(1) NOT NULL default '1',
  `ruleset` varchar(6) NOT NULL,
  `sqlfile` varchar(50) NOT NULL,
  `locked` tinyint(1) NOT NULL,
  `notes` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `settings` (`id`, `ruleset`, `sqlfile`, `locked`, `notes`) VALUES
(1, '2008', 'Preset-Empty', 1, '');

CREATE TABLE IF NOT EXISTS `teams` (
  `teamnum` int(11) NOT NULL,
  `name_official` varchar(500) NOT NULL,
  `name_short` varchar(30) NOT NULL,
  `nickname` varchar(50) NOT NULL,
  `strength` float NOT NULL default '0',
  `won` int(11) NOT NULL,
  `lost` int(11) NOT NULL,
  `tie` int(11) NOT NULL,
  `qualify` int(11) NOT NULL,
  `ranking` int(11) NOT NULL,
  `matches` int(11) NOT NULL,
  `location` varchar(60) NOT NULL,
  `notes` mediumtext NOT NULL,
  PRIMARY KEY  (`teamnum`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `team_match` (
  `teamnum` int(11) NOT NULL,
  `matchid` int(11) NOT NULL,
  `surrogate` tinyint(1) NOT NULL,
  `disqualified` tinyint(1) NOT NULL,
  `alliance` enum('red','blue') default NULL,
  `penalties` int(11) NOT NULL,
  `notes` text NOT NULL,
  PRIMARY KEY  (`teamnum`,`matchid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `team_matchdata` (
  `teamnum` int(11) NOT NULL,
  `matchid` int(11) NOT NULL,
  `auto_ball` tinyint(1) NOT NULL,
  `auto_markers` tinyint(1) NOT NULL,
  `ball_herd` tinyint(4) NOT NULL,
  `ball_over` tinyint(4) NOT NULL,
  `laps` tinyint(4) NOT NULL,
  `ball_top` tinyint(4) NOT NULL,
  `robot_tip` tinyint(1) NOT NULL,
  PRIMARY KEY  (`teamnum`,`matchid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `team_notes` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `teamnum` int(10) unsigned NOT NULL,
  `user` varchar(60) NOT NULL,
  `created` datetime NOT NULL,
  `note` tinytext NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `teamnum` (`teamnum`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
"""

#UTILITY FUNCTIONS-----------------------------
def isIni(obj):
	return hasattr(obj, 'sections') and hasattr(obj, 'getint') and hasattr(obj, 'getboolean') \
			and hasattr(obj, 'has_option') and hasattr(obj, 'get')

class Team(object):
	__teams = {}
	teamNumber = 0
	matches = []
	def __new__(cls, teamNum):
		teamNum = int(teamNum)
		if cls.__teams.has_key(teamNum): return cls.__teams[teamNum]
		else:
			self = super(Team, cls).__new__(cls)
			cls.__teams[teamNum] = self
			self.teamNumber = teamNum
			return self
	
	def __str__(self):
		return 'Team %s' % self.teamNumber
	
	"""def __repr__(self):
		return 'Team(%r)' % self.teamNumber"""
	
	def __hash__(self):
		return self.teamNumber.__hash__()

class Match(object):
	redAlliance = None
	blueAlliance = None
	redAllianceData = None
	blueAllianceData = None
	number = 0
	matchType = qual
	time = datetime.datetime.today()
	netScore = {'red':0, 'blue':0}
	penalties = {'red':0, 'blue':0}
	finalScore = {'red':0, 'blue':0}
	notes=""
	
	def __init__(self, *args):
		print "init-ing..."
		if len(args) >= 1 and isIni(args[0]):
				self.initFromIni(args[0])
	
	def initFromIni(self, iniData):
		"""Takes a ConfigParser object, and makes a Match object from it, if possible"""
		sections = iniData.sections()
		if 'blue' in sections and 'red' in sections and 'match' in sections:
			#Make our object, start with the basics
			self.redAlliance  = [iniData.getint('red',  t) for t in ('team1', 'team2', 'team3')]
			self.blueAlliance = [iniData.getint('blue', t) for t in ('team1', 'team2', 'team3')]
			self.redAlliance  = [Team(n) for n in self.redAlliance[:]]
			self.blueAlliance = [Team(n) for n in self.blueAlliance[:]]
			self.redAllianceData  = dict(zip( self.redAlliance,  [TeamMatchData(iniData, n, self) for n in self.redAlliance[:]]  ))
			self.blueAllianceData = dict(zip( self.blueAlliance, [TeamMatchData(iniData, n, self) for n in self.blueAlliance[:]] ))
			self.number = iniData.getint('match', 'number')
			self.matchType = iniData.get('match', 'type')
			time = iniData.get('match', 'time').split(':')
			self.time = datetime.time( int(time[0]), int(time[1]) )
			self.notes = iniData.get('match', 'notes')
			
			#Scores
			self.finalScore['red'] = iniData.getint('red', 'score')
			self.finalScore['blue'] = iniData.getint('blue', 'score')
			self.penalties['red'] = iniData.getint('red', 'penalties')
			self.penalties['blue'] = iniData.getint('blue', 'penalties')
			self.netScore['red'] = self.finalScore['red'] + self.penalties['red']
			self.netScore['blue'] = self.finalScore['blue'] + self.penalties['blue']
		
	def writeToDB(self, cursor):
		#Write our data to the DB
		cursor.execute(''' UPDATE matches SET redscore=%s, redpenalty=%s, bluescore=%s, bluepenalty=%s, notes=%s
		WHERE matchid=%s AND type=%s ''', (self.netScore['red'], self.penalties['red'], 
										   self.netScore['blue'], self.penalties['blue'],
										   self.notes, self.number, self.matchType))
		#Tell everyone else to write to the DB
		for k, td in self.redAllianceData.iteritems():
			td.writeToDB(cursor, self)
		for k, td in self.blueAllianceData.iteritems():
			td.writeToDB(cursor, self)
	
class TeamMatchData(object):
	'''surrogate = False
	disqualified = False
	notes = ""
	autonPulled = False
	autonCrossed = False
	toHerded = 0
	toHurdled = 0
	toLaps = 0
	pgPlaced = False
	pgTipped = False'''
	
	def __init__(self, iniData, team, match):
		self.initfromIni(iniData, team, match)
	
	def initfromIni(self, iniData, team, matchObject):
		if iniData.has_section(str(team.teamNumber)):
			#construct our object
			self.team = team
			teamNum = str(team.teamNumber)
			self.match = matchObject
			self.surrogate = iniData.getboolean(teamNum, 'surrogate')
			self.disqualified = iniData.getboolean(teamNum, 'disqualified')
			self.notes = iniData.get(teamNum, 'notes')
			self.autonPulled = iniData.getboolean(teamNum, 'autonBallPulled')
			self.autonCrossed = iniData.getint(teamNum, 'autonMarkersCrossed')
			self.toHerded = iniData.getint(teamNum, 'teleopBallsHerded')
			self.toHurdled = iniData.getint(teamNum, 'teleopBallsHurdled')
			self.toLaps = iniData.getint(teamNum, 'teleopLaps')
			self.pgPlaced = iniData.getboolean(teamNum, 'postgamePlacedBall')
			self.pgTipped = iniData.getboolean(teamNum, 'postgameRobotTipped')
	
	def writeToDB(self, cursor, match):
		cursor.execute('''UPDATE team_match SET surrogate=%s, disqualified=%s, alliance=%s, notes=%s WHERE teamnum=%s AND matchid=%s''', 
					   (self.surrogate, self.disqualified, ('red' if self in match.redAllianceData else 'blue'), 
					   self.notes, self.team.teamNumber, match.number))
		
		cursor.execute('''UPDATE team_matchdata SET auto_ball=%s, auto_markers=%s, ball_herd=%s, ball_over=%s, laps=%s, ball_top=%s, robot_tip=%s WHERE teamnum=%s AND matchid=%s''', 
					   (self.autonPulled, self.autonCrossed, self.toHerded, self.toHurdled, self.toLaps, self.pgPlaced, self.pgTipped, self.team.teamNumber, match.number))

if __name__ == '__main__':
	import sys, os, os.path
	if len(sys.argv) >=2 and os.path.exists(sys.argv[1]) and sys.argv[1][-4:] == '.ini':
		data = ConfigParser()
		data.readfp(open(sys.argv[1]))
		matchdata = Match(data)
		#Do the database whatnot
		tourn = ConfigParser()
		tourn.readfp(open('tournament.ini'))
		tName = tourn.get('Tournament', 'Name')
		tNameMap = {'Wisconsin Regional':'frc_%s_wisconsin', 'West Michigan Regional':'frc_%s_westmichigan'}
		db = tNameMap[tName] % str(datetime.datetime.today().year)
		user = raw_input("Username: ")
		passwd = getpass.getpass()
		conn = mysql.connect(user=user, passwd=passwd, db=db)
		curs = conn.cursor()
		matchdata.writeToDB(curs)
		curs.close()
		conn.commit()