package org.zkoss.zssex.api;

import java.util.ArrayList;

import org.zkoss.poi.ss.formula.eval.NotImplementedException;
import org.zkoss.poi.ss.usermodel.Cell;
import org.zkoss.poi.ss.usermodel.Row;
import org.zkoss.poi.ss.usermodel.charts.CategoryData;
import org.zkoss.poi.ss.usermodel.charts.ChartDataSource;
import org.zkoss.poi.ss.usermodel.charts.ChartTextSource;
import org.zkoss.poi.ss.usermodel.charts.DataSources;
import org.zkoss.poi.ss.usermodel.charts.XYData;
import org.zkoss.poi.ss.util.CellRangeAddress;
import org.zkoss.poi.xssf.usermodel.charts.XSSFArea3DChartData;
import org.zkoss.poi.xssf.usermodel.charts.XSSFAreaChartData;
import org.zkoss.poi.xssf.usermodel.charts.XSSFBar3DChartData;
import org.zkoss.poi.xssf.usermodel.charts.XSSFBarChartData;
import org.zkoss.poi.xssf.usermodel.charts.XSSFColumn3DChartData;
import org.zkoss.poi.xssf.usermodel.charts.XSSFColumnChartData;
import org.zkoss.poi.xssf.usermodel.charts.XSSFDoughnutChartData;
import org.zkoss.poi.xssf.usermodel.charts.XSSFLine3DChartData;
import org.zkoss.poi.xssf.usermodel.charts.XSSFLineChartData;
import org.zkoss.poi.xssf.usermodel.charts.XSSFPie3DChartData;
import org.zkoss.poi.xssf.usermodel.charts.XSSFPieChartData;
import org.zkoss.poi.xssf.usermodel.charts.XSSFScatChartData;
import org.zkoss.zss.api.AreaRef;
import org.zkoss.zss.api.model.ChartData;
import org.zkoss.zss.api.model.Sheet;
import org.zkoss.zss.api.model.Chart.Type;
import org.zkoss.zss.api.model.impl.ChartDataImpl;
import org.zkoss.zss.api.model.impl.SheetImpl;
import org.zkoss.zss.api.model.impl.SimpleRef;

/**
 * Utility to create simple ChartData from a sheet selection.
 * It guesses the series from the selection. 
 * @author dennis
 * @deprecated ported to org.zkoss.zss.ngapi.impl.ChartDataHelper
 */
//NOTE this class have to locate in EE
public class ChartDataUtil {
/*
	public static ChartData getChartData(Sheet sheet, AreaRef selection,
			Type type) {
		org.zkoss.poi.ss.usermodel.charts.ChartData data = newChartData(sheet, selection, type);
		return toChartData(data);
	}
	
	public static ChartData toChartData(org.zkoss.poi.ss.usermodel.charts.ChartData data){
		return new ChartDataImpl(new SimpleRef<org.zkoss.poi.ss.usermodel.charts.ChartData>(data));
	}

	// following code refers from SSWorkbookCtrl
	private static org.zkoss.poi.ss.usermodel.charts.ChartData newChartData(Sheet sheet, AreaRef selection,
			Type type) {
		org.zkoss.poi.ss.usermodel.charts.ChartData data = null;

		switch (type) {
		case AREA_3D:
			data = fillCategoryData(new XSSFArea3DChartData(), sheet, selection);
			break;
		case AREA:
			data = fillCategoryData(new XSSFAreaChartData(), sheet, selection);
			break;
		case BAR_3D:
			data = fillCategoryData(new XSSFBar3DChartData(), sheet, selection);
			// ((XSSFBar3DChartData) data).setGrouping(ChartGrouping.STANDARD);
			break;
		case COLUMN_3D:
			data = fillCategoryData(new XSSFColumn3DChartData(), sheet, selection);
			// ((XSSFBar3DChartData) data).setGrouping(ChartGrouping.STANDARD);
			break;
		case BAR:
			data = fillCategoryData(new XSSFBarChartData(), sheet, selection);
			// ((XSSFBarChartData) data).setGrouping(ChartGrouping.STANDARD);
			break;
		case COLUMN:
			data = fillCategoryData(new XSSFColumnChartData(), sheet, selection);
			// ((XSSFBarChartData) data).setGrouping(ChartGrouping.STANDARD);
			break;
		case BUBBLE:
			throw new UnsupportedOperationException();
		case DOUGHNUT:
			data = fillCategoryData(new XSSFDoughnutChartData(), sheet, selection);
			break;
		case LINE_3D:
			data = fillCategoryData(new XSSFLine3DChartData(), sheet, selection);
			break;
		case LINE:
			data = fillCategoryData(new XSSFLineChartData(), sheet, selection);
			break;
		case PIE_3D:
			data = fillCategoryData(new XSSFPie3DChartData(), sheet, selection);
			break;
		case OF_PIE:
			// break;
			throw new UnsupportedOperationException();
		case PIE:
			data = fillCategoryData(new XSSFPieChartData(), sheet, selection);
			break;
		case RADAR:
			throw new NotImplementedException("Radar data not impl");
		case SCATTER:
			data = fillXYData(new XSSFScatChartData(), sheet, selection);
			break;
		case STOCK:
			// data = fillCategoryData(new XSSFStockChartData());
			// break;
			throw new UnsupportedOperationException();
		case SURFACE_3D:
			// break;
			throw new UnsupportedOperationException();
		case SURFACE:
			// break;
			throw new UnsupportedOperationException();
		}
		return data;
	}
	
	private static CellRangeAddress toCellRangeAddress(int row,int column,int lastRow,int lastColumn){
		return new CellRangeAddress(row,lastRow,column,lastColumn);
	}
	
	private static XSheet toPoiSheet(Sheet sheet){
		return ((SheetImpl)sheet).getNative();
	}
	
	private static org.zkoss.poi.ss.usermodel.charts.ChartData fillXYData(XYData data,Sheet sheet, AreaRef selection) {
		
		AreaRef rect = getChartDataRange(sheet,selection);
		int colIdx = rect.getColumn();
		int rowIdx = rect.getRow();
		
		ChartDataSource<Number> horValues = null;
		ArrayList<ChartTextSource> titles = new ArrayList<ChartTextSource>();
		ArrayList<ChartDataSource<Number>> values = new ArrayList<ChartDataSource<Number>>();
		
		int colWidth = selection.getLastColumn() - colIdx;
		int rowHeight = selection.getLastRow() - rowIdx;
		if (rowHeight > colWidth) {
			//find horizontal value, at least 1 column
			if (colIdx < selection.getLastColumn()) {
				int lCol = selection.getColumn();
				int rCol = lCol;
				if (rCol < colIdx) {
					rCol = colIdx - 1;
				} else {
					colIdx += 1;
				}
//				String startCell = spreadsheet.getColumntitle(lCol) + spreadsheet.getRowtitle(rowIdx);
//				String endCell = spreadsheet.getColumntitle(rCol) + spreadsheet.getRowtitle(selection.getBottom());
				CellRangeAddress cra = toCellRangeAddress(rowIdx,lCol,selection.getLastRow(),rCol);
				horValues = DataSources.fromNumericCellRange(((SheetImpl)sheet).getNative(), cra);
			}
			//find values
//			int i = 1;
			for (int c = colIdx; c <= selection.getLastColumn(); c++) {
				//find title
				String title = null;
				int row = rowIdx - 1;
				if (row >= selection.getRow()) {
					title = XRanges.range(toPoiSheet(sheet), selection.getRow(), c, row, c).getText().toString();
				}
				titles.add(title == null ? null : DataSources.fromString(title));
				
//				String startCell = spreadsheet.getColumntitle(c) + spreadsheet.getRowtitle(rowIdx);
//				String endCell = spreadsheet.getColumntitle(c) + spreadsheet.getRowtitle(selection.getBottom());
				CellRangeAddress cra = toCellRangeAddress(rowIdx,c,selection.getLastRow(),c);
				values.add(DataSources.fromNumericCellRange(toPoiSheet(sheet), cra));
			}
		} else {
			//find horizontal value, at least 1 row
			if (rowIdx < selection.getLastRow()) {
				int tRow = selection.getRow();
				int bRow = tRow;
				if (bRow < rowIdx) {
					bRow = rowIdx - 1;
				} else {
					rowIdx += 1;
				}
//				String startCell = spreadsheet.getColumntitle(colIdx) + spreadsheet.getRowtitle(tRow);
//				String endCell = spreadsheet.getColumntitle(selection.getRight()) + spreadsheet.getRowtitle(tRow);
				CellRangeAddress cra = toCellRangeAddress(tRow,colIdx,tRow,selection.getLastColumn());
				horValues = DataSources.fromNumericCellRange(toPoiSheet(sheet), cra);
			}
			//find values
//			int i = 1;
			for (int r = rowIdx; r <= selection.getLastRow(); r++) {
				//find title
				String title = null;
				int col = colIdx - 1;
				if (col >= selection.getColumn()) {
					title = XRanges.range(toPoiSheet(sheet), r, selection.getColumn(), r, col).getText().toString();
				}
				titles.add(title == null ? null : DataSources.fromString(title));
				
//				String startCell = spreadsheet.getColumntitle(colIdx) + spreadsheet.getRowtitle(r);
//				String endCell = spreadsheet.getColumntitle(selection.getRight()) + spreadsheet.getRowtitle(r);
				CellRangeAddress cra = toCellRangeAddress(r,colIdx,r,selection.getLastColumn());
				values.add(DataSources.fromNumericCellRange(toPoiSheet(sheet), cra));
			}
		}
		
		for (int i = 0; i < values.size(); i++) {
			data.addSerie(titles.get(i), horValues, values.get(i));
		}
		return data;
	}

	private static CategoryData fillCategoryData(CategoryData data,
			Sheet sheet, AreaRef selection) {
		AreaRef rect = getChartDataRange(sheet, selection);
		int colIdx = rect.getColumn();
		int rowIdx = rect.getRow();

		ChartDataSource<String> cats = null;
		ArrayList<ChartTextSource> titles = new ArrayList<ChartTextSource>();
		ArrayList<ChartDataSource<Number>> vals = new ArrayList<ChartDataSource<Number>>();

		int colWidth = selection.getLastColumn() - colIdx;
		int rowHeight = selection.getLastRow() - rowIdx;
		if (rowHeight > colWidth) { // catalog by row, value by column
			// find catalog
			int col = colIdx - 1;
			if (col >= selection.getColumn()) {
//				String startCell = spreadsheet.getColumntitle(selection.getLeft()) + spreadsheet.getRowtitle(rowIdx);
//				String endCell = spreadsheet.getColumntitle(col)+ spreadsheet.getRowtitle(selection.getBottom());
				CellRangeAddress cra = toCellRangeAddress(rowIdx,selection.getColumn(),selection.getLastRow(),col);
				cats = DataSources.fromStringCellRange(toPoiSheet(sheet), cra);
			}
			// find value, by column
//			int i = 1;
			for (int c = colIdx; c <= selection.getLastColumn(); c++) {
				// find title
				String title = null;
				int row = rowIdx - 1;
				if (row >= selection.getRow()) {
					title = XRanges.range(toPoiSheet(sheet), selection.getRow(), c, row, c).getText().toString();
				}
				titles.add(title == null ? null : DataSources.fromString(title));

//				String startCell = spreadsheet.getColumntitle(c)+ spreadsheet.getRowtitle(rowIdx);
//				String endCell = spreadsheet.getColumntitle(c)+ spreadsheet.getRowtitle(selection.getBottom());
				CellRangeAddress cra = toCellRangeAddress(rowIdx,c,selection.getLastRow(),c);
				ChartDataSource<Number> val = DataSources.fromNumericCellRange(toPoiSheet(sheet),cra);
				vals.add(val);
			}
		} else { // catalog by column, value by row
			// find catalog
			int row = rowIdx - 1;
			if (row >= selection.getRow()) {
//				String startCell = spreadsheet.getColumntitle(colIdx)+ spreadsheet.getRowtitle(row);
//				String endCell = spreadsheet.getColumntitle(selection.getRight()) + spreadsheet.getRowtitle(row);
				CellRangeAddress cra = toCellRangeAddress(row,colIdx,row,selection.getLastColumn());
				cats = DataSources.fromStringCellRange(toPoiSheet(sheet),cra);
			}

			// find value
//			int i = 1;
			for (int r = rowIdx; r <= selection.getLastRow(); r++) {
				// find title
				String title = null;
				int col = colIdx - 1;
				if (col >= selection.getColumn()) {
					title = XRanges.range(toPoiSheet(sheet), r, selection.getColumn(), r,
									col).getText().toString();
				}
				titles.add(title == null ? null : DataSources.fromString(title));

//				String startCell = spreadsheet.getColumntitle(colIdx)+ spreadsheet.getRowtitle(r);
//				String endCell = spreadsheet.getColumntitle(selection.getRight()) + spreadsheet.getRowtitle(r);
				CellRangeAddress cra = toCellRangeAddress(r,colIdx,r,selection.getLastColumn());
				ChartDataSource<Number> val = DataSources.fromNumericCellRange(toPoiSheet(sheet),cra);
				vals.add(val);
			}
		}

		for (int i = 0; i < vals.size(); i++) {
			data.addSerie(titles.get(i), cats, vals.get(i));
		}
		return data;
	}

	private static AreaRef getChartDataRange(Sheet sheet,AreaRef selection) {
		// assume can't find number cell, use last cell as value
		int colIdx = selection.getColumn();
		int rowIdx = -1;
		XSheet ws = toPoiSheet(sheet);
		for (int r = selection.getLastRow(); r >= selection.getRow(); r--) {
			Row row = ws.getRow(r);
			if(row==null) continue;
			int rCol = colIdx;
			for (int c = selection.getLastColumn(); c >= rCol; c--) {
				if (isQualifiedCell(row.getCell(c))) {
					colIdx = c;
					rowIdx = r;
				} else {
					break;
				}
			}
		}
		if (rowIdx == -1) { // can not find number cell, use last cell as
							// chart's value
			rowIdx = selection.getLastRow();
			colIdx = selection.getLastColumn();
		}
		return new AreaRef(rowIdx, colIdx, selection.getLastRow(),
				selection.getLastColumn());
	}

	private static boolean isQualifiedCell(Cell cell) {
		if (cell == null)
			return true;
		int cellType = cell.getCellType();
		return cellType == Cell.CELL_TYPE_NUMERIC
				|| cellType == Cell.CELL_TYPE_FORMULA
				|| cellType == Cell.CELL_TYPE_BLANK;
	}
*/
}
