#!/usr/bin/python


#
# Converts a GameDB Excel XML spreadsheet to gamedb.json
# It takes its input from reading the xml file and outputs to stdout.
#
# Example usage:
#
#       ./ConvertExcelToGameDB.py gamedb.xml > gamedb.json
#       ./ConvertExcelToGameDB.py gamedb.xml --pp > gamedb.json     - output the json in prettyprint mode
#


import xml.etree.ElementTree
import sys
import json
import GameDBUtils

xmlstr = open(sys.argv[1], 'rb').read()
root = xml.etree.ElementTree.fromstring(xmlstr)

prettyprint = False
if len(sys.argv)>=3:
  param = str(sys.argv[2])
  if param=="--pp":
    prettyprint=True

#
# This helper function with create an empty list in the python object tree
# if a list with the given path doesn't exist in that object tree
# (This function assumes the object tree was already constructed with calls
# to UnflattenJson of the given paths, so it can assume various indexes are valid)
#
def AddEmptyArray( obj, path ):
  pathList = path.split('.')
  pathArray = pathList[0].split('[')
  if len(pathArray) == 2:
    # This is the case where it is an array, eg:  sim.resources[1].cost
    arrayName = pathArray[0]
    arrayIndex = int(pathArray[1][:-1])
    if not obj.has_key(arrayName):  # Here we assume the list is inside a dict
      obj[arrayName] = list()
      # obj[arrayName].append("blah")
    elif not len(pathList) == 1:
      if len(obj[arrayName]) > arrayIndex:
        AddEmptyArray( obj[arrayName][arrayIndex], path.split('.', 1)[1] )
  else:
    # Normal non-array item case
    #print 'here: '
    #print obj
    if not len(pathList) == 1:
      if pathList[0] in obj.keys():
        AddEmptyArray( obj[pathList[0]], path.split('.', 1)[1] )


# Turn a string in to a typed python object
def MakeTyped(str, typ):
  if typ == 'Number':
    if '.' in str:
      return float(str)
    # allow the int/float conversions to except if input malformed
    return int(str)
  elif typ == 'Boolean':
    if str == '1':
      return True
    elif str == '0':
      return False
    print 'Bad boolean value'
    exit(0)
  elif typ == 'String':
    return str
  print 'Unknown/unexpected type: ' + typ
  exit(0)


# This is the python object that will be populated with the python representation of the GameDb
pyGameDb = {}

namespaces = { 'ss': 'urn:schemas-microsoft-com:office:spreadsheet' }

for sheet in root.iterfind('ss:Worksheet', namespaces):
  tableName = sheet.attrib['{urn:schemas-microsoft-com:office:spreadsheet}Name']

  # print 'Adding table: ' + tableName
  newTable = []
  pyGameDb[tableName] = newTable

  for table in sheet.iterfind('ss:Table', namespaces):
    rowIndex = 0
    columnNames = []
    for row in table.iterfind('ss:Row', namespaces):
      if rowIndex == 0:
        for cell in row.iterfind('ss:Cell', namespaces):
          for data in cell.iterfind('ss:Data', namespaces):
            columnNames.append(data.text)
      else:
        rowData = {}

        columnIndex = 0
        rowName = 'empty name'
        ignoreList = []
        for cell in row.iterfind('ss:Cell', namespaces):
          data = cell.find('ss:Data', namespaces)
          if columnIndex < len(columnNames):
            col = columnNames[columnIndex]
            if data == None:
              if not "[" in col:
                if GameDBUtils.UnflattenJson( rowData, col, "" ) == False:
                  print 'UnflattenJson error on col: ' + col + ' with row: ' + str(rowData)
            else:
              dataType = data.attrib['{urn:schemas-microsoft-com:office:spreadsheet}Type']
              if data.text:
                if 0 == columnIndex:
                  rowName = data.text
                if GameDBUtils.UnflattenJson( rowData, col, MakeTyped(data.text, dataType) ) == False:
                  print 'UnflattenJson error on col: ' + col + ' and data: ' + data.text + ' with row: ' + str(rowData)
              elif not "[" in col and dataType == 'String':
                # Remove empty string for now because of CROM handling
                ignoreList.append(col)
          columnIndex += 1

        if (columnIndex != len(columnNames)):
          print 'Invalid row/cell ' + rowName + ' should have ' + str(len(columnNames)) + ' columns but has ' + str(columnIndex) + ' instead'
          exit(1)

        # Normalize the json so it still outputs an array object when it is empty
        for col in columnNames:
          if col not in ignoreList:
            AddEmptyArray( rowData, col )

        pyGameDb[tableName].append(rowData)

      rowIndex += 1


# Output the python representation of the GameDb as json
if prettyprint==True:
  print json.dumps(pyGameDb, indent=4, sort_keys=True)
else:
  print json.dumps(pyGameDb, sort_keys=True)