package org.apache.poi.ss.formula;

import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.hssf.util.CellReference;

import org.apache.poi.ss.SpreadsheetVersion;

import org.apache.poi.ss.formula.FormulaParseException;

import org.apache.poi.ss.formula.FormulaParser;

import org.apache.poi.ss.formula.FormulaRenderer;

import org.apache.poi.ss.formula.FormulaShifter;

import org.apache.poi.ss.formula.FormulaType;

import org.apache.poi.ss.formula.ptg.Area3DPtg;

import org.apache.poi.ss.formula.ptg.Area3DPxg;

import org.apache.poi.ss.formula.ptg.AreaPtg;

import org.apache.poi.ss.formula.ptg.Ptg;

import org.apache.poi.ss.formula.ptg.Ref3DPtg;

import org.apache.poi.ss.formula.ptg.Ref3DPxg;

import org.apache.poi.ss.formula.ptg.RefPtg;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellType;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/***

 * Wrapper around FormulaShifter, with added functionality. Takes formula string as input (instead of Ptg[]), so it makes better automation.

 * Works with both rows and columns.

 * @author Dragan Jovanović (zmau3012 @ skype)

 *

 */

public class CompleteFormulaShifter {

	private FormulaShifter formulaShifter;

	private SpreadsheetVersion spreadSheetVersion;

	private Sheet shiftingSheet;

	private boolean rowModeElseColumn; 

	public static CompleteFormulaShifter createForItemShift(Sheet sheet, boolean rowModeElseColumn, int firstShiftItemIndex, int lastShiftItemIndex, int shiftStep){

		CompleteFormulaShifter instance = new CompleteFormulaShifter(sheet, rowModeElseColumn);

		instance.configureForItemShifting(firstShiftItemIndex, lastShiftItemIndex, shiftStep);

		return instance; 

	}

	public static CompleteFormulaShifter createForItemCopy(Sheet sheet, boolean rowModeElseColumn, int firstShiftItemIndex, int lastShiftItemIndex, int shiftStep){

		CompleteFormulaShifter instance = new CompleteFormulaShifter(sheet, rowModeElseColumn);

		instance.configureForItemCopiing(firstShiftItemIndex, lastShiftItemIndex, shiftStep);

		return instance; 

	}

	public CompleteFormulaShifter(Sheet shiftingSheet, boolean rowModeElseColumn){

		this.shiftingSheet = shiftingSheet;

		this.spreadSheetVersion = getSpreadsheetVersion(shiftingSheet);

		this.rowModeElseColumn = rowModeElseColumn;

	}

	public void configureForItemShifting(int firstShiftItemIndex, int lastShiftItemIndex, int shiftStep){

		formulaShifter = FormulaShifter.createForRowShift(shiftingSheet.getWorkbook().getSheetIndex(shiftingSheet), shiftingSheet.getSheetName(), 

	    		firstShiftItemIndex, lastShiftItemIndex, shiftStep, spreadSheetVersion);

	}

	public void configureForItemCopiing(int firstShiftRowIndex, int lastShiftItemIndex, int shiftStep){

		formulaShifter = FormulaShifter.createForRowCopy(shiftingSheet.getWorkbook().getSheetIndex(shiftingSheet), shiftingSheet.getSheetName(), 

	    		firstShiftRowIndex, lastShiftItemIndex, shiftStep, spreadSheetVersion);

	}

	public void processFormulasOnOtherSheets(){

		Workbook wb = shiftingSheet.getWorkbook();

		int sheetIndex = 0;

		while(sheetIndex < wb.getNumberOfSheets()){

			Sheet aSheet = wb.getSheetAt(sheetIndex++);

			if(!aSheet.getSheetName().equals(shiftingSheet.getSheetName())){

				shiftAllFormulas(aSheet);

			}

		}

	}

	private void shiftAllFormulas(Sheet aSheet){

		for(int rowNo = 0; rowNo < aSheet.getPhysicalNumberOfRows(); rowNo++)

		{	

			Row row = aSheet.getRow(rowNo);

		    for (int i = 0; i <= row.getPhysicalNumberOfCells(); i++){

		    	org.apache.poi.ss.usermodel.Cell currentCell = row.getCell(i);

		    	if(currentCell != null){

		    		if(currentCell.getCellTypeEnum() == CellType.FORMULA)

		    			shiftFormula(currentCell); 

		    	}

		    }

		}

	}

	public void shiftFormula(Cell cell) {

		switch (spreadSheetVersion) {

			case EXCEL2007:

				int currentSheetIndex = getSheetIndex(cell.getSheet());

				String shiftedFormula = getShiftedFormulaForXSSF(cell.toString(), currentSheetIndex); 

				cell.setCellFormula(shiftedFormula);

			break;

			case EXCEL97:

				shiftFormulaForHSSF((HSSFCell)cell);

			break;

		}

	}

	// original code in org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter.shiftFormula()

	/** Recalculates formula, for EXCEL2007 spreadsheets 

	 * @param formula string representing formula

	 * */

    public String getShiftedFormulaForXSSF(String formula, int currentSheetIndex) {

    	boolean adjustSucceeded;

    	Workbook wb = shiftingSheet.getWorkbook();

    	XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb);

        try {

            Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, currentSheetIndex);

            String shiftedFmla = null;

            if(rowModeElseColumn)

            	adjustSucceeded = formulaShifter.adjustFormula(ptgs, currentSheetIndex);

            else {

	        	Ptg[] transponedPtgs = transpose(ptgs);

	        	adjustSucceeded = formulaShifter.adjustFormula(transponedPtgs, currentSheetIndex); 

	        	if (adjustSucceeded) 

	        		ptgs = transpose(transponedPtgs);

            }

            if(adjustSucceeded){

	            shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);

	                // dont use logger here, because of transfer to POI project

	                //System.out.println(String.format("original : %s; shifted : %s", formula, shiftedFmla));

	            return shiftedFmla;

            }

            else return formula; // better return some error msg ?

        } catch (FormulaParseException fpe) {

            // Log, but don't change, rather than breaking; uncomment this in  POI context

            //logger.log(POILogger.WARN, "Error shifting formula on row ", row.getRowNum(), fpe);

            return formula;

        }

    }

    // original code in org.apache.poi.hssf.record.aggregates.ValueRecordsAggregate.updateFormulasAfterRowShift()

	/** Recalculates formula, for EXCEL97 spreadsheets 

	 * @param formula string representing formula

	 * */

    void shiftFormulaForHSSF(HSSFCell cell) {

    	boolean adjustSucceeded;

        int sheetIndex = getSheetIndex(cell.getSheet());

        FormulaRecordAggregate fra = (FormulaRecordAggregate)cell.getCellValueRecord();

        Ptg[] ptgs = fra.getFormulaTokens(); 

        if(rowModeElseColumn)

        	adjustSucceeded = formulaShifter.adjustFormula(ptgs, sheetIndex);

        else {

        	Ptg[] transposedPtgs = transpose(ptgs);

        	adjustSucceeded = formulaShifter.adjustFormula(transposedPtgs, sheetIndex);

        	if (adjustSucceeded)

        		ptgs = transpose(transposedPtgs);

        }

        fra.setParsedExpression(ptgs);

    }

    private Ptg[] transpose(Ptg[] original){

        Ptg[] transposed = new Ptg[original.length];

        int index = 0;

        for (Ptg ptg : original) {

            String ptgType = ptg.getClass().getSimpleName();  

        	if(ptgType.equals("Ref3DPtg")){  //3D means (sheetNo, col, row) reference, for example Sheet1!B3; xls version  

            	int oldColumnIndex = ((Ref3DPtg) ptg).getColumn();

            	((Ref3DPtg) ptg).setColumn(((Ref3DPtg) ptg).getRow());

            	((Ref3DPtg) ptg).setRow(oldColumnIndex);

            	transposed[index] = ptg;

        	} else if(ptgType.equals("Ref3DPxg")){  //3D means (sheetNo, col, row) reference, for example Sheet1!B3; xlsx version  

    	        int oldColumnIndex = ((Ref3DPxg) ptg).getColumn();

            	((Ref3DPxg) ptg).setColumn(((Ref3DPxg) ptg).getRow());

            	((Ref3DPxg) ptg).setRow(oldColumnIndex);

            	transposed[index] = ptg;

        	} else if(ptgType.equals("AreaPtg")){  // region for aggregate function, for example A1:B3 or Sheet1!B3:Sheet1!C3

		        	int oldFirstColumnIndex = ((AreaPtg) ptg).getFirstColumn();

		        	((AreaPtg) ptg).setFirstColumn(((AreaPtg) ptg).getFirstRow());

		        	((AreaPtg) ptg).setFirstRow(oldFirstColumnIndex);

		        	int oldLastColumnIndex = ((AreaPtg) ptg).getLastColumn();

		        	((AreaPtg) ptg).setLastColumn(((AreaPtg) ptg).getLastRow());

		        	((AreaPtg) ptg).setLastRow(oldLastColumnIndex);

		        	transposed[index] = ptg;

        	}

        	 else if(ptgType.equals("Area3DPtg")){ //for example SUM(Sheet1!B3:C3); xls version 

		        	int oldFirstColumnIndex = ((Area3DPtg) ptg).getFirstColumn();

		        	((Area3DPtg) ptg).setFirstColumn(((Area3DPtg) ptg).getFirstRow());

		        	((Area3DPtg) ptg).setFirstRow(oldFirstColumnIndex);

		        	int oldLastColumnIndex = ((Area3DPtg) ptg).getLastColumn();

		        	((Area3DPtg) ptg).setLastColumn(((Area3DPtg) ptg).getLastRow());

		        	((Area3DPtg) ptg).setLastRow(oldLastColumnIndex);

		        	transposed[index] = ptg;

        	 }

        	 else if(ptgType.equals("Area3DPxg")){ //for example SUM(Sheet1!B3:C3); xlsx version 

        	         int oldFirstColumnIndex = ((Area3DPxg) ptg).getFirstColumn();

		        	((Area3DPxg) ptg).setFirstColumn(((Area3DPxg) ptg).getFirstRow());

		        	((Area3DPxg) ptg).setFirstRow(oldFirstColumnIndex);

		        	int oldLastColumnIndex = ((Area3DPxg) ptg).getLastColumn();

		        	((Area3DPxg) ptg).setLastColumn(((Area3DPxg) ptg).getLastRow());

		        	((Area3DPxg) ptg).setLastRow(oldLastColumnIndex);

		        	transposed[index] = ptg;

        	 }	else if(ptgType.equals("RefPtg")){  // common simple reference, like A2 

	                RefPtg transponedCellRefToken = new RefPtg(transpose(ptg.toFormulaString()));

	                transposed[index] = transponedCellRefToken;

        	 }

        	 else // operators like + or SUM, for example

	            	transposed[index] = ptg;

            index++;

        }

        return transposed;

    }

    public static String transpose(String cellreference){

    	CellReference original = new CellReference(cellreference);

    	// transpose, calling public CellReference(int *pRow*, int *pCol*) !!!!

    	CellReference transposed = new CellReference(original.getCol(), original.getRow(), original.isColAbsolute(), original.isRowAbsolute());  

    	return transposed.formatAsString();

    }

	private int getSheetIndex(Sheet sheet){

		return sheet.getWorkbook().getSheetIndex(sheet);

	}

	public static SpreadsheetVersion getSpreadsheetVersion(Sheet sheet){

		if(sheet.getWorkbook() instanceof XSSFWorkbook)

			return SpreadsheetVersion.EXCEL2007;

		else if(sheet.getWorkbook() instanceof HSSFWorkbook)

			return SpreadsheetVersion.EXCEL97;

		else return null;

	}

}