#!/usr/bin/python


#
# Converts the gamedb.json to a Excel XML spreadsheet
# It takes its input from reading a json and an optional filter text file and outputs to stdout.
#
# Example usage:
#
#       ./ConvertGameDBToExcel.py gamedb.json > GameDB.xml                      - Converts the whole json file to xml
#       ./ConvertGameDBToExcel.py gamedb.json filter.txt > GameDB.xml           - Only export the GED data
#       ./ConvertGameDBToExcel.py gamedb.json filter.txt --noged > GameDB.xml   - Only export the data not defined in the filter file
#


import sys, csv, json
import GameDBUtils


class GameDataBase:
  """ The GameDB """
  data = []
  def __init__(self):
    self.data = json.load(open(sys.argv[1], 'rb'))



class CsvSpreadSheet:
  """ A spreadsheet """
  def __init__(self):
    self.csvwriter = csv.writer(sys.stdout)

  def NewWorkSheet(self, Name):
    self.csvwriter.writerow([ Name ])

  def ColumnWidths(self, ColumnWidths):
    pass

  def RowHeader(self, RowHeaderData):
    self.csvwriter.writerow([''] + RowHeaderData)

  def RowData(self, RowData):
    self.csvwriter.writerow([''] + RowData)

class SchemaFilter:
  """The db schema file reader"""
  arrayKeys = []
  inited=False
  excludeGEDData=False
  #Parse the command line and extract the needed parameters
  def __init__(self):
    if len(sys.argv)>=3:
      with open(sys.argv[2], 'rb') as schemacsvfile:
        self.csvreader = csv.reader(schemacsvfile, delimiter='	')
        # Store every line that ends with a capital E
        fullLengthKeys = []  
        for row in self.csvreader:
          if ((len(row) != 0 ) and (str(row[1]) == 'E')):
            fullLengthKeys.append(row[0])
        # Create a list of array filters
        # Keep track of the dbtype name so we can filter the entries correctly
        # An array name could be in several dbtype but we might only want to see
        # it for a certain type
        for entry in fullLengthKeys:
          # Dbtype name is always the start of the line followed by a dot
          pos = entry.find('.')
          if (pos != -1):
            newStringArray = entry.split('.', 1);
            numDelmins = newStringArray[1].count('[')
            arrayKeyEntry = []
            arrayKeyEntry.append(newStringArray[0])
            if numDelmins!=0:
              while (newStringArray[1].find('[') != -1):
                stringPart = newStringArray[1].split('[', 1);
                newStringArray[1] = stringPart[1];
                arrayKeyEntry.append(stringPart[0] + '[')
            arrayKeyEntry.append(newStringArray[1])
            self.arrayKeys.append(arrayKeyEntry)
      self.inited=True
    # Are we exporting the xml excluding the GED data as defined by the filter file ?
    if len(sys.argv)>=4:
      param = str(sys.argv[3])
      if param=="--noged":
        self.excludeGEDData=True
      
class XmlSpreadSheet:
  """ A spreadsheet """
  haveWorkSheet = False
  def __init__(self):
    print('<?xml version="1.0"?>')
    print('<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">')
    print('  <ss:Styles>')
    print('    <ss:Style ss:ID="Heading">')
    print('      <ss:Font ss:Bold="1"/>')
    print('      <ss:Alignment ss:Horizontal="Left" ss:Rotate="60"/>')
    print('    </ss:Style>')
    print('    <ss:Style ss:ID="Float">')
    print('      <ss:NumberFormat ss:Format="Fixed"/>')
    print('    </ss:Style>')
    print('  </ss:Styles>')

  def __del__(self):
    if self.haveWorkSheet == True:
      print('    </ss:Table>')
      print('  </ss:Worksheet>')
    print('</ss:Workbook>')

  def NewWorkSheet(self, Name):
    if self.haveWorkSheet == True:
      print('    </ss:Table>')
      print('  </ss:Worksheet>')
    self.haveWorkSheet = True
    print('  <ss:Worksheet ss:Name="' + Name + '">')
    print('    <ss:Table>')

  def ColumnWidths(self, ColumnWidths):
    for w in ColumnWidths:
      print('        <ss:Column ss:Width="' + str(max(w * 8, 20)) + '"/>')

  def RowHeader(self, RowHeaderData):
    print('      <ss:Row ss:StyleID="Heading" ss:Height="200">')
    for item in RowHeaderData:
      print('        <ss:Cell>')
      print('          <ss:Data ss:Type="String">' + str(item) + '</ss:Data>')
      print('        </ss:Cell>')
    print('      </ss:Row>')

  def RowData(self, RowData):
    print('      <ss:Row>')
    for item in RowData:
      if isinstance( item, float ):
        print('        <ss:Cell ss:StyleID="Float">')
      else:
        print('        <ss:Cell>')
      if str(item) == 'True' and not item == 'True':
        print('          <ss:Data ss:Type="Boolean">1</ss:Data>')
      elif str(item) == 'False' and not item == 'False':
        print('          <ss:Data ss:Type="Boolean">0</ss:Data>')
      elif isinstance( item, (int, float, long)):
        print('          <ss:Data ss:Type="Number">' + str(item) + '</ss:Data>')
      else:
        print('          <ss:Data ss:Type="String">' + str(item) + '</ss:Data>')
      print('        </ss:Cell>')
    print('      </ss:Row>')



gameDB = GameDataBase()
output = XmlSpreadSheet()
gedSchemaFilter = SchemaFilter()

gamedbkeys = gameDB.data.keys()
gamedbkeys.sort()

# Iterate the tables of the GameDB
for row in gamedbkeys:

  arr = gameDB.data[row]
  if len(arr) > 0:

    # This is both the unique set of keys of all the items in the sheet
    # And also the max width of the values of that key as strings
    uniqueKeyWidths = {}
    for item in arr:
      itemFlat = GameDBUtils.FlattenJson(item, True)
      for k in itemFlat.keys():
        stringLength = len(str(itemFlat[k]))
        if k in uniqueKeyWidths:
          uniqueKeyWidths[k] = max(uniqueKeyWidths[k], stringLength)
        else:
          uniqueKeyWidths[k] = stringLength

	# Compile the list of keys that are present in uniqueKeyWidths.keys() and in gedSchemaFilter.arrayKeys for that row
    keys = []
    tempKeys = uniqueKeyWidths.keys()
    if 'uid' in tempKeys:
      tempKeys.remove('uid')

    if gedSchemaFilter.inited==False:
      #No filter file is applied
      tempKeys.sort()
      keys = tempKeys
    else:
      # a filter file is applied
      for entry in tempKeys:
        addToKeys=False
        # The entry excludes the dbtype name which is stored in 'row'
        # We need to add it back to make correct matches
        fullEntry = str(row) + "." + entry
        for keysInArray in gedSchemaFilter.arrayKeys:
          numCount = 0
          keyPartCounter = 0
          for keyPart in keysInArray:
            # matches have to be careful not to match against part of the work
            # so look for '.' + keypart unless it's an array declaration starting with '[' or ']'
            # in that case don't add the '.'
            keyPartToTest = keyPart
            arraySepPos = keyPart.find('[')
            if arraySepPos == -1 or arraySepPos != 0:
              arraySepPos = keyPart.find(']')
            if keyPartCounter != 0 and ( arraySepPos != 0 ):
              keyPartToTest = '.' + keyPart
            keyPartCounter += 1
            if fullEntry.find(keyPartToTest)==-1:
              break
            numCount += 1
          if numCount==len(keysInArray):
            addToKeys=True
            break
        if addToKeys==True and gedSchemaFilter.excludeGEDData==False:
          keys.append(entry)
        elif addToKeys==False and gedSchemaFilter.excludeGEDData==True:
          keys.append(entry)
           
    if (len(keys)==0):
      continue
    
    keys.sort()
    keys = ['uid'] + keys

    # Start a new worksheet (we output each table on a new page)
    output.NewWorkSheet(row)

    # Sort the widths in the new order of the columns
    widths = []
    for k in keys:
      widths.append(uniqueKeyWidths[k])

    # Output the column widths and the column titles
    output.ColumnWidths(widths)
    output.RowHeader(keys)

    # Output all the rows of the current table
    for item in arr:
      itemFlat = GameDBUtils.FlattenJson(item, True)
      vals = []
      for k in keys:
        # print(k)
        val = ''
        if k in itemFlat:
          val = itemFlat[k]
        vals.append(val)
      output.RowData(vals)