package gw.exportimport

uses gw.api.locale.DisplayKey
uses gw.entity.ITypeList
uses java.io.ByteArrayOutputStream

uses org.apache.poi.ss.util.CellAddress
uses org.apache.poi.xssf.usermodel.XSSFWorkbook
uses org.apache.poi.xssf.usermodel.XSSFDataValidation
uses org.apache.poi.xssf.usermodel.XSSFDataValidationHelper
uses org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint
uses org.apache.poi.xssf.usermodel.XSSFSheet
uses org.apache.poi.xssf.usermodel.XSSFCellStyle
uses org.apache.poi.xssf.usermodel.XSSFRow
uses org.apache.poi.ss.util.CellRangeAddressList
uses java.util.ArrayList
uses gw.api.util.LocaleUtil
uses gw.pl.util.ArgCheck
uses java.lang.IllegalArgumentException
uses org.apache.poi.ss.util.WorkbookUtil
uses java.util.List
uses gw.api.extension.ExtensionInterfaces

@ReadOnly
abstract class ExcelExporterBase {

  abstract property get LockedDataStyle() : XSSFCellStyle
  abstract property get UnlockedDataStyle() : XSSFCellStyle
  abstract property get ColumnHeaderStyle() : XSSFCellStyle
  abstract property get SpreadSheetPassword() : String
  abstract function initStyles(workbook : XSSFWorkbook)
  abstract function sheetName(type : Type) : String
  abstract function validateBeforeExport(template : boolean)
  
  var _data : ExportData< KeyableBean >
  var _listener : RowProcessedListener

  /**
   * @param data The ExportData
   * @param listener An optional RowProcessedListener
   */
  protected construct(exportData : ExportData< KeyableBean >, listener : RowProcessedListener) {
    _data = exportData
    _listener = listener
    ArgCheck.nonNull(exportData, "data", "ExcelExporter: Requires ExportData.")
  }

  /**
   * If the ExportData's header information is null, then we cannot perform the
   * export. Otherwise, create the excel workbook and save it.
   */
  function export(template : boolean) {
    validateBeforeExport(template)
    ExtensionInterfaces.IExcelFileWriterExtensions.saveExcelFile(exportWorkbookInMemory(template), _data, template)
  }

  internal function exportWorkbookInMemory(template : boolean) : byte[] {
    return renderWorkbookInMemory(createWorkbook(template))
  }

  /**
   * Creates a write-protected excel workbook by creating write-protected spreadsheets
   * and populating these sheets with the data contained in _data. Also, performs
   * general formatting to make the spreadsheet more visually appealing.
   * @return XFFSWorkbook The excel workbook
   */
  function createWorkbook(template : boolean) : XSSFWorkbook {
    var language = _data.Language
    if (language == null) {
      throw new IllegalArgumentException("Language is required.")      
    }
    var workbook : XSSFWorkbook
    LocaleUtil.runAsCurrentLocale(LocaleUtil.toLanguage(language), \ -> {
      workbook = createWorkbookInternal(template)
    })
    return workbook
  }

  private function createWorkbookInternal(template : boolean) : XSSFWorkbook{
    if (_listener != null) {
      _listener.maxRowsCalculated(calculateMaxRows())
    }

    var workbook = new XSSFWorkbook()
    initStyles(workbook)
    
    _data.SpreadSheetVariables.eachKeyAndValue(\ name, value -> {
      createMetaDataVariable(workbook, name, value)
    })
    createMetaDataVariable(workbook, ExportImportConstants.LANGUAGE_EXCEL_VARIABLE, _data.Language.Code)    
    createMetaDataVariable(workbook, ExportImportConstants.ENTITY_EXCEL_VARIABLE, _data.EntityType.Name)
    createMetaDataVariable(workbook, ExportImportConstants.TEMPLATE_EXCEL_VARIABLE, template as String)

    var entitySheet = workbook.createSheet(sheetName(_data.EntityType))
    var validationSheet = workbook.createSheet(DisplayKey.get("Export.DataValidation"))
    var additionalDataSheet : XSSFSheet
    if ( _data.HasAdditionalData ) {
      var exportData = _data.getAdditionalData(_data.AdditionalDataKeys.first())
      var dataType = typeof(exportData.first())
      var additionalSheetName = DisplayKey.get("Export.ScratchPad", ExportLocaleUtil.lookupEntityTypeDisplayName(dataType.Name))
      additionalDataSheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(additionalSheetName))
    }

    populateEntitySpreadsheet(entitySheet, template)
    var validations : List<XSSFDataValidation> = populateValidations(validationSheet, workbook, template)

    if ( _data.HasAdditionalData ) {
      populateAdditionalDataSpreadsheet(additionalDataSheet)
    }

    createSheetLayout(entitySheet, ExportImportConstants.HEADER_INDEX, ExportImportConstants.FIRST_DATA_ROW_INDEX, 0, false, template)
    createSheetLayout(validationSheet, -1, 0, ExportImportConstants.VALIDATION_FIRST_COLUMN_INDEX, true, false)
    if ( _data.HasAdditionalData ) {
      createSheetLayout(additionalDataSheet, ExportImportConstants.HEADER_INDEX, ExportImportConstants.FIRST_DATA_ROW_INDEX, 0, true, false)
    }

    entitySheet.setActiveCell(new CellAddress(ExportImportHelper.convertCellLocationToExcelFormat(1, 0, false)))

    registerValidationsWithEntitySheet(entitySheet, validations)

    workbook.lockStructure()
    workbook.setSheetHidden(ExportImportConstants.VALIDATION_SHEET, true)

    return workbook
  }
  
  internal function createMetaDataVariable(workbook : XSSFWorkbook, variableName : String, variableValue : String) {
    var variable = workbook.createName()
    variable.setNameName(variableName)
    variable.setRefersToFormula("\"${variableValue}\"")
  }

  private function registerValidationsWithEntitySheet(entitySheet : XSSFSheet, validations : List<XSSFDataValidation>) {
    for (validation in validations) {
      entitySheet.addValidationData(validation)
    }
  }

  /**
   * Create each column's header by extracting information from the ColumnInfo's
   *
   * @param sheet Header information is added to this spreadsheet.
   * @param columnInfos The ColumnInfo's that specify each column.
   */
  private function createHeader(sheet : XSSFSheet, columnInfos : List<ColumnInfo>) {
    var headerRow = sheet.createRow(ExportImportConstants.HEADER_INDEX)
    createColumns(headerRow, columnInfos, null)
    headerRow.each(\ cell -> {cell.CellStyle = ColumnHeaderStyle})
  }

  /**
   * Populates the spreadsheet with header and body information for each column.
   *
   * @param sheet Information is added to this spreadsheet.
   */
  internal function populateEntitySpreadsheet(sheet : XSSFSheet, template : boolean) {
    var columnInfos = _data.getEntityColumnInfos(template)
    createHeader(sheet, columnInfos)
    if (not template) {
      createRows(sheet, columnInfos, _data.Rows)
    }
  }

  /**
   * Populates the validation sheet with validation typekey information. For
   * each column output for the entity, check if the column supports data
   * validation. If so, load the typekeys, create a new row in the validation
   * sheet, and then create a new validation based off of that row.
   */
  private function populateValidations(sheet : XSSFSheet, workbook : XSSFWorkbook, template : boolean) : List<XSSFDataValidation> {
    var validations : List<XSSFDataValidation> = {}
    var columns = _data.getEntityColumnInfos(template)
    for (columnInfo in columns index entityColumnIndex) {
      var validValues = new ArrayList<String>()
      var rowIndex = validations.size()
      if (columnInfo.ColumnType typeis ITypeList) {        
        var keys = getTypeKeys(columnInfo)
        keys.each(\ k -> validValues.add(k.toString()))
      } else if (columnInfo.FlagsAction) {
        var strategy = ExtensionInterfaces.IImportStrategyRegistryExtensions.getStrategy(_data.EntityType)
        if (strategy.AllowCreate) {
          validValues.add(DisplayKey.get("Export.Action.Add"))
        }
        if (not template and strategy.AllowDelete) {
          validValues.add(DisplayKey.get("Export.Action.Delete"))
        }
      }

      if (not validValues.Empty) {
        createNewValidationRow(sheet, rowIndex, validValues, columnInfo.Header)    
        var validation = createValidationFromRange(validValues.Count, rowIndex, entityColumnIndex, workbook, workbook.getSheetAt(0))
        validations.add(validation)
      }
    }
    return validations
  }

  private function getTypeKeys(columnInfo : ColumnInfo) : List<gw.entity.TypeKey> {
    var realType = columnInfo.ColumnType as ITypeList
    var keys = realType.getTypeKeys(false)
    return keys
  }

  /**
   * Create a new row in the validation sheet, create an id cell containing the same column 
   * header as is displayed on the entity sheet, load the list of valid values, and create a cell 
   * for each key in the list.
   */
  private function createNewValidationRow(validationSheet : XSSFSheet, rowIndex : int, keys : java.util.List<String>, header : String){
    var row = validationSheet.createRow(rowIndex)
    var cellID = row.createCell(ExportImportConstants.VALIDATION_HEADER_COLUMN_INDEX)
    cellID.setCellValue(getDisplayKeyValue(header))
    cellID.setCellStyle(LockedDataStyle)
    for (key in keys index currentKeyIndex) {
      var cell = row.createCell(currentKeyIndex + ExportImportConstants.VALIDATION_FIRST_COLUMN_INDEX)
      cell.setCellValue(key)
      cell.setCellStyle(LockedDataStyle)
    }
  }

  /**
   * Creates a data validation based off of a named list pointing to a range of validation cells
   * and for the column entityColumnIndex on the entity sheet.
   */
  private function createValidationFromRange(numTypeKeys : int, validationRowIndex : int, entityColumnIndex : int, workbook : XSSFWorkbook, sheet : XSSFSheet) : XSSFDataValidation {
    var range = new CellRangeAddressList(ExportImportConstants.FIRST_DATA_ROW_INDEX,
                  ExtensionInterfaces.IExportImportConfigurableConstantsExtensions.MAX_VALIDATED_ROWS + ExportImportConstants.FIRST_DATA_ROW_INDEX - 1,
                  entityColumnIndex, entityColumnIndex)
    var listName = createNamedList(numTypeKeys, validationRowIndex, workbook)
    var validationHelper = sheet.DataValidationHelper as XSSFDataValidationHelper
    var constraint = validationHelper.createFormulaListConstraint(listName) as XSSFDataValidationConstraint
    var validation = validationHelper.createValidation(constraint, range) as  XSSFDataValidation
    validation.ShowErrorBox = true

    return validation
  }

  /**
   * Creates a named list that is used in creating a new data validation
   */
  private function createNamedList(numTypeKeys : int, validationRowIndex : int, workbook : XSSFWorkbook) : String {
    var firstCellInRange = ExportImportHelper.convertCellLocationToExcelFormat(validationRowIndex, ExportImportConstants.VALIDATION_FIRST_COLUMN_INDEX, true)
    var lastCellInRange = ExportImportHelper.convertCellLocationToExcelFormat(validationRowIndex, numTypeKeys, true)
    var listName = "${DisplayKey.get("Export.ValidationList")}${validationRowIndex}"
    var namedRange = workbook.createName()
    namedRange.setNameName(listName)
    namedRange.setRefersToFormula("${DisplayKey.get("Export.DataValidation")}!${firstCellInRange}:${lastCellInRange}")
    return listName
  }

  /**
   * Populates a spreadsheet with header and body information for each list of
   *    additional data specified in the export data.
   *
   * @param sheet Information is added to this spreadsheet.
   */
  private function populateAdditionalDataSpreadsheet(sheet : XSSFSheet) {
    for ( key in _data.AdditionalDataKeys ) {
      var columnInfos = _data.getAdditionalDataColumnInfos(key)
      populateAdditionalDataSpreadsheet(sheet, columnInfos, _data.getAdditionalData(key))
    }
  }

  private function populateAdditionalDataSpreadsheet(sheet : XSSFSheet,
      columnInfos : List<ColumnInfo>, additionalData : List<KeyableBean>) {
    createHeader(sheet, columnInfos)
    createRows(sheet, columnInfos, additionalData)
  }

  /**
   * Iterates through all KeyableBeans stored in data and appends information
   * to the body of the column.
   *
   * @param sheet Header information is added to this spreadsheet.
   * @param offset specifies that we do not overwrite, or insert
   * rows above, the header row.
   */
  private function createRows(sheet : XSSFSheet, columnInfos : List<ColumnInfo>, rows : List<KeyableBean>) {
    for ( row in rows index line ) {
      var newRow = sheet.createRow(line + ExportImportConstants.FIRST_DATA_ROW_INDEX)
      createColumns(newRow, columnInfos, row)
      _listener?.rowProcessed()
    }
  }

  /**
   * Creates a new cell in each column and assigns a value to it.
   *
   * @param row the row that the cells are being added to
   * @param columnInfos The metadata needed to assign a value to the cell
   * @param entityRow if this is null, then we are creating columns for a header row
   */
  private function createColumns(row : XSSFRow, columnInfos : List<ColumnInfo>, entityRow : KeyableBean) {
    var isHeader = entityRow == null
    for (column in columnInfos index cellNumber) {
      var cell = row.createCell(cellNumber)
      var value = (isHeader)
          ? getDisplayKeyValue(column.Header)
          : column.Resolver.resolveCellValue(entityRow)
      cell.setCellStyle((column.Locked or isHeader) ? LockedDataStyle : UnlockedDataStyle)
      if (value != null) {
        cell.setCellValue(value.toString())
      }
    }
  }

  private function renderWorkbookInMemory(workbook : XSSFWorkbook) : byte[] {
    var outputStream = new ByteArrayOutputStream()
    workbook.write(outputStream)
    return outputStream.toByteArray()
  }

  private function calculateMaxRows() : int {
    var maxRows = _data.Rows.Count
    if (_data.HasAdditionalData) {
      _data.AdditionalDataKeys.each(\ a -> {
        maxRows += _data.getAdditionalData(a).Count
      })
    }
    return maxRows
  }

  /**
   * Autosize columns, create a freezepane, and then protect the sheet.
   */
  function createSheetLayout(sheet : XSSFSheet, rowIndex : int, rowFreezeFrame : int, columnFreezeFrame : int, locked : boolean, template : boolean) {
    var maxColumns = calculateMaxColumns(sheet, rowIndex)
    var isEntitySheet = rowIndex != -1
    setCellLockDefaultForSheet(sheet, maxColumns, locked, isEntitySheet)
    setColumnsToTextFormat(sheet, maxColumns)
    autoSizeColumns(sheet, maxColumns, isEntitySheet)
    sheet.createFreezePane(columnFreezeFrame, rowFreezeFrame)
    if (not template) {
      sheet.protectSheet(SpreadSheetPassword)
    }
  }

  private function setColumnsToTextFormat(sheet : XSSFSheet, dataColumns : int) {
    //magic @ symbol to set default format of column to text
    var format = sheet.Workbook.createDataFormat().getFormat("@")
    var style = sheet.Workbook.createCellStyle()
    style.setDataFormat(format)
    style.Locked = false
    
    dataColumns += + ExtensionInterfaces.IExportImportConfigurableConstantsExtensions.ADDITIONAL_UNLOCKED_COLUMNS
    
    for (column in 0 .. dataColumns) {
      sheet.setDefaultColumnStyle(column, style)
    } 
  }

  private function setCellLockDefaultForSheet(sheet : XSSFSheet, dataColumns : int, locked : boolean, isEntitySheet : boolean) {
    var maxColumns = (isEntitySheet ? ExtensionInterfaces.IExportImportConfigurableConstantsExtensions.ADDITIONAL_UNLOCKED_COLUMNS : 0) + dataColumns
    for (column in 0..|maxColumns) {
      sheet.setDefaultColumnStyle(column, (locked) ? LockedDataStyle : UnlockedDataStyle)
    }
  }

  /**
   * Autosizes each column
   */
  private function autoSizeColumns(sheet : XSSFSheet, dataColumns : int, isEntitySheet : boolean) {
    for (column in 0..|(dataColumns)) {
      sheet.autoSizeColumn(column)
    }
    if (isEntitySheet) {
      var maxColumns = dataColumns + ExtensionInterfaces.IExportImportConfigurableConstantsExtensions.ADDITIONAL_UNLOCKED_COLUMNS
      for (columnIndex in dataColumns..|(maxColumns)) {
        sheet.setColumnWidth(columnIndex, 10*256)
      }
    }
  }

  /**
   * Calculates the maximum number of columns in the sheet. If row index is not null, then use
   * that row to calculate the maximum number of columns
   */
  private function calculateMaxColumns(sheet : XSSFSheet, rowIndex : int) : int {
    var maxColumns : int
    if (rowIndex == -1) {
      for (row in sheet) {
        var currentColumns = row.Count
        maxColumns = (currentColumns > maxColumns) ? currentColumns : maxColumns
      }
    } else {
      maxColumns = sheet.getRow(rowIndex).Count
    }
    return maxColumns
  }

  protected property get Data() : ExportData< KeyableBean > {
    return _data
  }
  
  static private function getDisplayKeyValue(displayKey : String) : String {
    return gw.api.locale.DisplayKey.get(displayKey)
  }
 
}