import json
import openpyxl

coallitions= ['allied', 'axis']

# These need to be manually populated in case any plane has been renamed or added
sturmoviks = ['il2m41', 'il2m42', 'il2m43', 'ju87d3', 'hs129b2mk103', 'me410a1', 'p38j25', 'typhoonmk1bsabre']
bombers = ['pe2s35', 'pe2s87', 'a20b', 'he111h6', 'he111h16', 'ju88a4', 'ju88c6', 'ar234b2']
paras = ['alliedparas_li2', 'axisparas_ju523mg4e', 'alliedparas_c47a', 'cg4a']
planesWithMods = ['hurricanemkii', 'la5s8', 'p47d22', 'p38j25', 'p51b5', 'p51d15', 'p47d28', 'spitfiremkixe', 'spitfiremkixc', 'spitfiremkxiv', 'mosquitofbmkvis2', 'tempestmkvs2', 'bf109f2', 'bf109g6late', 'bf109k4', 'hs129b2'] # These list contains the root strings for any planes which also have modifications. Extra string after the root will be put into the table cell
ignoredPlanes = ['bf110e2sc1000', 'bf110g2sc1000']

def processRPS(rpsRaw, coallition, sturmoviks, bombers, paras):
# Function that takes the raw json data and converts it to a planesets dictionary, with plane name as key and a list of planeset indices as a value
  planesets = dict()
  planesets_fighters = dict()
  planesets_sturmoviks = dict()
  planesets_bombers = dict()
  planesets_paras = dict()
  index = []
  # Loop through and put raw json rps into dictionaries
  for i in range(len(rpsRaw)):
      for plane in list(rpsRaw[i][coallition].keys()):
          if  not(rpsRaw[i][coallition][plane]['bomberAirstart'] or rpsRaw[i][coallition][plane]['supplyAirstart']) and not(plane in bombers) and not(plane in sturmoviks) and not(plane in paras):
             #rpsRaw[i][coallition][plane]['frontField']
              planesets_fighters.setdefault(plane, []).append(rpsRaw[i]["index"]+1)
          if plane in sturmoviks:
              planesets_sturmoviks.setdefault(plane, []).append(rpsRaw[i]["index"]+1)
          if plane in bombers:
              planesets_bombers.setdefault(plane, []).append(rpsRaw[i]["index"]+1)
          if plane in paras:
              planesets_paras.setdefault(plane, []).append(rpsRaw[i]["index"]+1)

  # Sort the rps indices from smallest to largezt
  for plane in planesets_fighters:
      planesets_fighters[plane].sort()
  
  for plane in planesets_sturmoviks:
      planesets_sturmoviks[plane].sort()
  
  for plane in planesets_bombers:
      planesets_bombers[plane].sort()
  
  # Sort the planesets so that earliest appearing planes come first in the table
  planesets_fighters=dict(sorted(planesets_fighters.items(), key = lambda index: (index[1], index[-1])))
  planesets_sturmoviks = dict(sorted(planesets_sturmoviks.items(), key = lambda index: (index[1], index[-1])))
  planesets_bombers = dict(sorted(planesets_bombers.items(), key = lambda index: (index[1], index[-1])))
  planesets = planesets_fighters | planesets_sturmoviks | planesets_bombers
  return planesets
  
def fillTable(rpsRaw, ws, planesets, coallition, starting_row, moddedPlanes, ignoredPlanes):
# Function that generates the excel table. Takes planesets as inout, generates excel table and returns tne last row of the table
  row = 2 + starting_row
  # Create column header
  for columnheader in range(1,len(rpsRaw)+1):
    ws.cell(row=1, column=columnheader+1, value="Planeset " + str(columnheader))
  # Styles for cell
  if coallition == 'allied':
    cellFill = openpyxl.styles.PatternFill(start_color='FFFF0000', end_color='FFFF0000', fill_type='solid')
  else:
    cellFill = openpyxl.styles.PatternFill(start_color='FF0000FF', end_color='FF0000FF', fill_type='solid')
  whiteFont = openpyxl.styles.Font(color='FFFFFFFF')
  thin=openpyxl.styles.Side(border_style="thin", color="FF000000")
  borderGrid = openpyxl.styles.Border(top=thin, bottom=thin, left=thin, right=thin)
  
  # List of full names of modded planes
  moddedPlanesFullName = []
  for plane in planesets:
    for moddedPlane in moddedPlanes:
      if plane[:len(moddedPlane)] == moddedPlane:
        moddedPlanesFullName.append(plane)
  
  moddedPlaneRows = dict()
  for plane in planesets:
    notModded=False
    for index in planesets[plane]:
      for moddedPlane in moddedPlanes:
        if plane[:len(moddedPlane)] == moddedPlane and not(plane in ignoredPlanes):
          mod = plane[len(moddedPlane):]
          if not(moddedPlane in moddedPlaneRows.keys()):
            ws.cell(row=row, column=1, value=plane[:len(moddedPlane)])
            ws.cell(row=row, column=len(rpsRaw)+2, value=plane[:len(moddedPlane)])
            ws.cell(row=row, column=index+1, value=mod).fill = cellFill
            ws.cell(row=row, column=index+1, value=mod).font = whiteFont
            moddedPlaneRows.setdefault(moddedPlane, row)
            row=row+1
          else:
            ws.cell(row=moddedPlaneRows[moddedPlane], column=index+1, value=mod).fill = cellFill
            ws.cell(row=moddedPlaneRows[moddedPlane], column=index+1, value=mod).font = whiteFont
        elif not(plane in moddedPlanesFullName) and not(plane in ignoredPlanes):
          ws.cell(row=row, column=1, value=plane)
          ws.cell(row=row, column=len(rpsRaw)+2, value=plane)
          ws.cell(row=row, column=index+1).fill = cellFill
          notModded = True
    if notModded:
      row = row +1
  
  # Generate gridlines for the table
  for tablerow in range(1,row):
    for tablecolumn in range(1,len(rpsRaw)+3):
      ws.cell(row=tablerow, column=tablecolumn).border = borderGrid
  return row

theatres = ['Eastern', 'Western']

# Create the excel workbook
wb = openpyxl.Workbook()

# Write tables to each different sheet for Eastern and Western fromts
for theatre in theatres:
  # Load json file
  with open("rps" + theatre  + "Front.json", 'r') as f:
    rpsRaw = json.load(f)
  # Create worksheet
  ws = wb.create_sheet(theatre)

  i=0
  for coallition in coallitions:
    planesets = dict()
    planesets = processRPS(rpsRaw, coallition, sturmoviks, bombers, paras)
    print(planesets)
    if i==0:
      # Add rps table for allies
      lastRow = fillTable(rpsRaw, ws, planesets, coallition, 0, planesWithMods, ignoredPlanes)
    else:
      # Add rps table for axis right after the allied table
      fillTable(rpsRaw, ws, planesets, coallition, lastRow-2, planesWithMods, ignoredPlanes)
    i = i+1

# Generate the excel file
wb.save('FVP_RPS.xlsx')