#!/usr/bin/python
# import - Imports PGN to PostgreSQL.
# Copyright (c) Ari Makela 2003
# This program is licensed under GNU General Public License, version 2
# or, at your option, later.
#
# $Id: import.py,v 1.11 2003/12/07 08:38:34 hauva Exp $
#

import re
import sys
import chess
import psycopg
import getopt

# PGN is always subset of ISO-8859-1 so let's do ugly things...

reload(sys)
sys.setdefaultencoding('iso-8859-1')
del sys.setdefaultencoding

def saveGame(game):
	global gameCount, conn, curs

	sql = 'INSERT INTO games '
	fields = '''
	(event_id, site_id, year, round, white_id, black_id,
	result_id, nic_id, eco_id, movetext_id, plycount)
	'''
	values = '''
	(%d, %d, %d, %d, %d, %d,
	%d, %d, %d, %d, %d)
	'''
	results = { '1-0': 0, '1/2-1/2': 1, '0-1': 2, '*': 3 }
	gameCount += 1
  	print gameCount, game.white[0].surname, '-', \
		game.black[0].surname, game.date
	eventId = getEventId(game.event)
	siteId = getSiteId(game.site)
	movetextId = saveMovetext(conn, curs, toXml(game))
    	year, mon, mday = game.date.split('.')
	date = None

	if year.isdigit() and mon.isdigit() and mday.isdigit():
		date = psycopg.Date(int(year), int(mon), int(mday))

	if not year.isdigit():
		year = None

	whiteId = getPlayerId(game.white[0])
	blackId = getPlayerId(game.black[0])
	tournamentRound = None

	if game.tournamentRound.isdigit():
		tournamentRound = int(game.tournamentRound)

	ecoId = getEcoId(conn, curs, game.eco)
	nicId = getNicId(conn, curs, game.nic)
	resultId = results[game.result]
	sql += fields + 'VALUES' + values
	curs.execute(sql, [eventId, siteId, int(year), tournamentRound, \
		whiteId, blackId, resultId, nicId, ecoId, movetextId, \
		len(game.moves)])
	conn.commit()

def getNicId(conn, curs, nicCode):
	global nic

	if not nicCode:
		return None
	elif nic.has_key(nicCode):
		return nic[nicCode]
	else:
		sql = "SELECT nextval('nic_seq')"
		curs.execute(sql)
		id = int(curs.fetchone()[0])
		sql = 'INSERT INTO nic (id, nic) VALUES (%d, %s)'
		curs.execute(sql, [id, nicCode])
		conn.commit()
		nic[nicCode] = id

def getEcoId(conn, curs, ecoCode):
	global eco

	if not ecoCode:
		return None
	elif eco.has_key(ecoCode):
		return eco[ecoCode]
	else:
		sql = "SELECT nextval('eco_seq')"
		curs.execute(sql)
		id = int(curs.fetchone()[0])
		sql = 'INSERT INTO eco (id, eco) VALUES (%d, %s)'
		curs.execute(sql, [id, ecoCode])
		conn.commit()
		eco[ecoCode] = id

def saveMovetext(conn, curs, xml):
	sql = '''
	INSERT INTO movetexts
	(movetext)
	VALUES
	(%s)
	'''
	curs.execute(sql, [xml])
	conn.commit()

def getPlayerId(p):
	global player, curs, conn

	if p.firstname:
		key = p.surname + ', ' + p.firstname
	else:
		key = p.surname
	
	if player.has_key(key):
		return player[key]
	else:
		sql = "SELECT nextval('players_seq')"
		curs.execute(sql)
		tmp = curs.fetchone()
		id = int(tmp[0])
		sql = 'INSERT INTO players (id, surname, first_name) ' + \
			'VALUES (%d, %s, %s)'
		curs.execute(sql, [id, p.surname, p.firstname])
		conn.commit()
		player[key] = id

		return id

def getSiteId(name):
	global site, curs, conn

	if site.has_key(name):
		return site[name]
	else:
		sql = "SELECT nextval('sites_seq')"
		curs.execute(sql)
		tmp = curs.fetchone()
		id = int(tmp[0])
		sql = 'INSERT INTO sites (id, name) VALUES (%d, %s)'
		curs.execute(sql, [id, name])
		conn.commit()
		site[name] = id

		return id

def getEventId(name):
	global event, curs, conn

	if event.has_key(name):
		return event[name]
	else:
		sql = "SELECT nextval('events_seq')"
		curs.execute(sql)
		tmp = curs.fetchone()
		id = int(tmp[0])
		sql = 'INSERT INTO events (id, name) VALUES (%d, %s)'
		curs.execute(sql, [id, name])
		conn.commit()
		event[name] = id

		return id

def getMoveTokens(line):
	stringList = line.split(None, sys.maxint)

	return stringList

def toXml(game):
	game.moves = getMoves(moveTokens) 
	s = game.simpleXml('utf-8')
	return s

def getMoves(a):
	moves = []

	if len(a) == 0:
		return moves
	else:	
		move = a.pop(0)

		while len(a) > 0:
			if move == '*':
				pass
			elif move.startswith('K') or move.startswith('Q') or \
				move.startswith('R') or \
				move.startswith('B') or \
				move.startswith('N') or \
				move.startswith('a') or \
				move.startswith('b') or \
				move.startswith('c') or \
				move.startswith('d') or \
				move.startswith('e') or \
				move.startswith('f') or \
				move.startswith('g') or \
				move.startswith('h') or \
				move == 'O-O-O' or move == 'O-O':

				try:
					moves += [game.move(move)]
					game.advance()
					fen = game.fen()
					nic, eco, name = game.classify(fen)

					if nic:
						game.nic = nic

					if eco:
						game.eco = eco

					if name:
						game.englishName = name
					
				except chess.IllegalMoveError:
					sys.stderr.write('Illegal move: ')
					sys.stderr.write('"' + move + '"\n')

					for line in game.show():
						sys.stderr.write(line + '\n')

			move = a.pop(0)

	return moves

def initMap(cursor, table, field):
	map = {}
	sql = 'SELECT id, ' + field + ' FROM ' + table
	cursor.execute(sql)
	
	while 1:
		try:
			id, name = curs.fetchone()
			map[name] = id
		except TypeError:
			break

	return map

def initPlayerMap(curs):
	map = {}
	sql = 'SELECT id, surname, first_name FROM players'
	curs.execute(sql)

	while 1:
		try:
			id, surname, first_name = curs.fetchone()
			key = surname + ', ' + first_name
			map[key] = id
		except TypeError:
			break

	return map

OTHER = 13
ROSTER = 42
DSN = 'dbname=chess'

#opts, args_proper = getopt(sys.argv[1:], 's')
conn = psycopg.connect(DSN)
curs = conn.cursor()
gameCount = 0
state = OTHER
game = None
moveTokens = []
player = initPlayerMap(curs)
event = initMap(curs, 'events', 'name')
site = initMap(curs, 'sites', 'name')
eco = initMap(curs, 'eco', 'eco')
nic = initMap(curs, 'nic', 'nic')
rosterRe = re.compile('^\[(\S+)\s+"([^"]+)')

while 1:
	line = sys.stdin.readline()

	if not line:
		break
	else:
		line = line.rstrip()

		if line.startswith('[', 0, sys.maxint):
			if state == OTHER:
				if game:
					++gameCount
					saveGame(game)

				game = chess.Game()
				moveTokens = []
			
			state = ROSTER
			m = rosterRe.match(line)
			key = m.group(1)
			value = m.group(2)
			value = value.encode('utf-8')

			if key == 'White':
				game.white.append(chess.Player(value))
			elif key == 'Black':
				game.black.append(chess.Player(value))
			elif key == 'Event':
				game.event = value
			elif key == 'Site':
				game.site = value
			elif key == 'Date':
				game.date = value
			elif key == 'Round':
				game.tournamentRound = value
			elif key == 'Result':
				game.result = value
			elif key == 'ECO':
				game.eco = value
			elif key == 'NIC':
				game.nic = value
		else:
			state = OTHER
			moveTokens += getMoveTokens(line)

saveGame(game)
curs.close()
conn.close()

#
# $Log: import.py,v $
# Revision 1.11  2003/12/07 08:38:34  hauva
# *** empty log message ***
#
# Revision 1.10  2003/12/06 14:39:13  hauva
# import testable
#
# Revision 1.9  2003/12/03 14:03:22  hauva
# *** empty log message ***
#
# Revision 1.8  2003/12/02 01:22:12  hauva
# Work on SXC
#
# Revision 1.7  2003/11/24 22:30:36  hauva
# FEN support, en passant is not yet implemented
#
# Revision 1.6  2003/11/24 20:33:22  hauva
# charset to UTF-8
#
# Revision 1.5  2003/11/23 18:17:08  hauva
# *** empty log message ***
#
# Revision 1.4  2003/11/23 15:57:12  hauva
# *** empty log message ***
#
# Revision 1.3  2003/11/23 11:55:13  hauva
# moved ChessGML to chess.py where it belongs
#
# Revision 1.2  2003/11/23 10:58:37  hauva
# chessgml now works
#
# Revision 1.1  2003/11/22 08:35:02  hauva
# *** empty log message ***
#
#