/* XSSFPivotCache.java

{{IS_NOTE
	Purpose:
		
	Description:
		
	History:
		Apr 25, 2012 6:44:57 PM , Created by Sam
}}IS_NOTE

Copyright (C) 2012 Potix Corporation. All Rights Reserved.

{{IS_RIGHT
}}IS_RIGHT
*/
package org.zkoss.poi.xssf.usermodel;

import java.io.IOException;
import java.io.OutputStream;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;

import javax.xml.namespace.QName;

import org.apache.xmlbeans.XmlCalendar;
import org.apache.xmlbeans.XmlException;
import org.apache.xmlbeans.XmlOptions;
import org.openxmlformats.schemas.officeDocument.x2006.relationships.STRelationshipId;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCacheField;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCacheFields;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCacheSource;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDateTime;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTNumber;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotCacheDefinition;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRecord;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSharedItems;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTString;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheetSource;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.PivotCacheDefinitionDocument;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STSourceType;
import org.zkoss.poi.POIXMLDocument;
import org.zkoss.poi.POIXMLDocumentPart;
import org.zkoss.poi.POIXMLException;
import org.zkoss.poi.openxml4j.exceptions.InvalidFormatException;
import org.zkoss.poi.openxml4j.opc.PackagePart;
import org.zkoss.poi.openxml4j.opc.PackageRelationship;
import org.zkoss.poi.openxml4j.opc.internal.MemoryPackagePart;
import org.zkoss.poi.ss.usermodel.Cell;
import org.zkoss.poi.ss.usermodel.CellStyle;
import org.zkoss.poi.ss.usermodel.DateUtil;
import org.zkoss.poi.ss.usermodel.PivotCache;
import org.zkoss.poi.ss.usermodel.Row;
import org.zkoss.poi.ss.usermodel.Sheet;
import org.zkoss.poi.ss.usermodel.Workbook;
import org.zkoss.poi.ss.util.AreaReference;

public class XSSFPivotCache extends POIXMLDocumentPart implements PivotCache, Serializable {
	private static final long serialVersionUID = 3352036355710415414L;
	
	private long _cacheId;
	private CTPivotCacheDefinition _pivotCacheDefinition;
	private XSSFPivotCacheRecords _pivotCacheRecords;

	public XSSFPivotCache() {
		super();
		onDocumentCreate();
	}
	
	@Override
	protected void onDocumentCreate() {
		_pivotCacheDefinition = CTPivotCacheDefinition.Factory.newInstance();
		_pivotCacheDefinition.addNewCacheSource();
		
		//default value
		short ver = (short)3;
		_pivotCacheDefinition.setCreatedVersion(ver);
		_pivotCacheDefinition.setRefreshedVersion(ver);
		_pivotCacheDefinition.setMinRefreshableVersion(ver);
	}
	
	public XSSFPivotCache(PackagePart part, PackageRelationship rel) {
		super(part, rel);
	}
	
	public XSSFPivotCache(long cacheId, POIXMLDocumentPart parent, PackagePart part, PackageRelationship rel) throws IOException {
		super(parent, part, rel);
		
		_cacheId = cacheId;
		onDocumentRead();
	}
	
	@Override
	protected void onDocumentRead() throws IOException {
		try {
			PackagePart part = getPackagePart();
			PackageRelationship rel = getPackageRelationship();
			_pivotCacheDefinition = PivotCacheDefinitionDocument.Factory.parse(part.getInputStream()).getPivotCacheDefinition();
			
			PackageRelationship relationship = part.getRelationshipsByType(XSSFRelation.PIVOT_CACHE_RECORDS.getRelation()).getRelationshipByID(rel.getId());
			_pivotCacheRecords = new XSSFPivotCacheRecords(part.getRelatedPart(relationship), relationship);
		} catch (IOException e){
            throw new POIXMLException(e);
        } catch (XmlException e) {
        	throw new POIXMLException(e);
		} catch (InvalidFormatException e) {
			throw new POIXMLException(e);
		}
	}

	@Override
	protected void commit() throws IOException {
		
		XmlOptions xmlOptions = new XmlOptions(DEFAULT_XML_OPTIONS);
		xmlOptions.setSaveSyntheticDocumentElement(new QName(CTPivotCacheDefinition.type.getName().getNamespaceURI(), "pivotCacheDefinition"));
        Map<String, String> map = new HashMap<String, String>();
        map.put(STRelationshipId.type.getName().getNamespaceURI(), "r");
        xmlOptions.setSaveSuggestedPrefixes(map);
        
        PackagePart part = getPackagePart();
        clearMemoryPackagePart(part);
        
        _pivotCacheDefinition.setId(_pivotCacheRecords.getPackageRelationship().getId());
        
        OutputStream out = part.getOutputStream();
        _pivotCacheDefinition.save(out, xmlOptions);
        out.close();
	}

	public Workbook getWorkbook() {
		return (Workbook) getParent();
	}
	
	public void setCacheId(long cacheId) {
		_cacheId = cacheId;
	}
	
	public long getCacheId() {
		return _cacheId;
	}

	public List<CacheField> getFields() {
		CTCacheFields cacheFields = _pivotCacheDefinition.getCacheFields();
		if (cacheFields == null) {
			return Collections.emptyList();
		}
		
		ArrayList<CacheField> list = new ArrayList<CacheField>();
		for (CTCacheField e : cacheFields.getCacheFieldList()) {
			list.add(new XSSFCacheField(e));
		}
		
		return list;
	}

	public List<CacheRecord> getRecords() {
		List<CTRecord> records = _pivotCacheRecords.getRows();
		if (records == null) {
			return Collections.emptyList();
		}
		
		CTCacheFields cacheFields = _pivotCacheDefinition.getCacheFields();
		List<CacheRecord> list = new ArrayList<CacheRecord>();
		for (CTRecord record : records) {
			list.add(new XSSFCacheRecord(record, cacheFields.getCacheFieldList()));
		}
		return list;
	}

	/**
	 * Sets sheet source
	 * 
	 * @return string sheet name
	 */
	private static String setSheetSource(AreaReference areaRef, CTCacheSource cacheSource) {
		CTWorksheetSource sheetSource = null;
		
		STSourceType.Enum type = cacheSource.getType();
		if (type != STSourceType.WORKSHEET) {
			cacheSource.setType(STSourceType.WORKSHEET);
			sheetSource = cacheSource.addNewWorksheetSource();
		} else {
			sheetSource = cacheSource.getWorksheetSource();
		}
		
		String sheetName = areaRef.getLastCell().getSheetName();
		String ref = areaRef.formatAsString();
		ref = ref.substring(sheetName.length() + 1);
		
		sheetSource.setSheet(sheetName);
		sheetSource.setRef(ref);
		
		return sheetName;
	}
	
	private static Cell getCell(int row, int col, Sheet sheet) {
		Row r = sheet.getRow(row);
		return r != null ? r.getCell(col) : null;
	}
	
	private static String getCellText(Cell cell) {
		if (cell == null) {
			return null;
		}
		
		int type = cell.getCellType();
		if (type == Cell.CELL_TYPE_STRING) {
			return cell.getStringCellValue();
		} else if (type == Cell.CELL_TYPE_FORMULA && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_STRING) {
			return cell.getRichStringCellValue().getString();
		}
		return null;
	}
	
	private static ArrayList<FieldInfo> prepareFields(int row, int lCol, int rCol, Sheet sheet) {
		ArrayList<FieldInfo> fields = new ArrayList<FieldInfo>();
		for (int i = lCol; i <= rCol; i++) {
			String field = getCellText(getCell(row, i, sheet));
			if (field == null || field.length() == 0) {
				throw new IllegalArgumentException("PivotTable field name should not be empty");
			}
			fields.add(new FieldInfo(field));
		}
		
		return fields;
	}
	
	public void setSheetSource(AreaReference sourceRef) {
		if (_pivotCacheRecords == null) {
			int pivotCacheRecordNumber = 1;//PivotCache has one PivotCacheRecords
			_pivotCacheRecords = (XSSFPivotCacheRecords) createRelationship(XSSFRelation.PIVOT_CACHE_RECORDS, XSSFFactory.getInstance(), pivotCacheRecordNumber);	
		}
		
		CTCacheFields cacheFields = _pivotCacheDefinition.getCacheFields();
		if (cacheFields == null) {
			cacheFields = _pivotCacheDefinition.addNewCacheFields();
		}
		
		String refreshedBy = ((POIXMLDocument)getWorkbook()).getProperties().getCoreProperties().getCreator();
		if (refreshedBy != null && !"".equals(refreshedBy)) {
			_pivotCacheDefinition.setRefreshedBy(refreshedBy);
		}
		_pivotCacheDefinition.setRefreshedDate(DateUtil.getExcelDate(Calendar.getInstance().getTime()));
		
		String sheetName = setSheetSource(sourceRef, _pivotCacheDefinition.getCacheSource());
		if (sheetName == null || sheetName.length() == 0) {
			throw new IllegalArgumentException("AreaReference shall has source sheet");
		}
		Sheet sheet = getWorkbook().getSheet(sheetName);
		
		int tRow = sourceRef.getFirstCell().getRow();
		int lCol = sourceRef.getFirstCell().getCol();
		int bRow = sourceRef.getLastCell().getRow();
		int rCol = sourceRef.getLastCell().getCol();
		if ((bRow - tRow + 1) < 2) {
			throw new IllegalArgumentException("PivotTable requires at least two rows of source data.");
		}
		
		List<FieldInfo> fields = prepareFields(tRow, lCol, rCol, sheet);
		//the first data row is (tRow + 1)
		for (int r = tRow + 1; r <= bRow; r++) {
			
			Iterator<FieldInfo> iter = fields.iterator();
			for (int col = lCol; col <= rCol; col++) {
				Cell cell = getCell(r, col, sheet);
				FieldInfo field = iter.next();
				field.add(cell);
			}
		}
		
		createCacheRecords(fields, createSharedItems(fields, cacheFields), _pivotCacheRecords);
		cacheFields.setCount(fields.size());
		_pivotCacheDefinition.setRecordCount(fields.get(0).size());
	}
		
	private static void newCell(Object src, CTRecord record) {
		 if (src instanceof Calendar) {
			 record.addNewD().setV(((Calendar)src));
		 } else if (src instanceof Number) {
			 record.addNewN().setV(((Number)src).doubleValue());
		 } else if (src instanceof String) {
			 record.addNewS().setV((String)src);
		 } else if (src == null) {
			 record.addNewM();
		 }
	}
	
	/**
	 * Create records
	 * 
	 * @param stringValues
	 * @param cacheRecords
	 */
	private static void createCacheRecords(List<FieldInfo> fields, HashMap<Integer, CTSharedItems> shareItems, XSSFPivotCacheRecords cacheRecords) {
		
		IndexMapper indexMapper = new IndexMapper(shareItems);
		
		int rowSize = fields.get(0).size();
		cacheRecords.setCount(rowSize);
		for (int i = 0; i < rowSize; i++) {
			
			CTRecord r = cacheRecords.addNewRow();
			for (int j = 0; j < fields.size(); j++) {
				FieldInfo field = fields.get(j);
				Object obj = field.getValue(i);
				
				int idx = indexMapper.getShareItemIndex(j, obj);
				if (idx >= 0) {
					r.addNewX().setV(idx);
				} else {
					newCell(obj, r);
				}
			}
		}
	}
	
	private static void setupSharedItemsProperties(FieldInfo field, CTSharedItems sharedItems) {
		if (field.containsBlank) {
			sharedItems.setContainsBlank(true);
		}
		
		if (field.containsDate) {
			if (!field.isContainsSemiMixedTypes()){
				sharedItems.setContainsSemiMixedTypes(false);
			}
			if (!field.containsNonDate) {
				sharedItems.setContainsNonDate(false);
			}
			sharedItems.setContainsDate(true);
			if (!field.containsString) {
				sharedItems.setContainsString(false);
			}
			if (field.isContainsMixedTypes()) {
				sharedItems.setContainsMixedTypes(true);
			}
			sharedItems.setMinDate(field.minDate);
			sharedItems.setMaxDate(field.maxDate);
			
		} else if (field.containsNumber) {
			sharedItems.setContainsString(field.containsString);
			sharedItems.setContainsNumber(true);
			if (field.isContainsMixedTypes()) {
				sharedItems.setContainsMixedTypes(true);
			} else if (!field.isContainsSemiMixedTypes()) {
				sharedItems.setContainsSemiMixedTypes(false);
			}
			sharedItems.setMinValue(field.minNumber);
			sharedItems.setMaxValue(field.maxNumber);
			if (field.containsInteger) {
				sharedItems.setContainsInteger(true);
			}
		} else if (field.isMissing()) {
			sharedItems.setContainsNonDate(false);
			sharedItems.setContainsString(false);
			sharedItems.setContainsBlank(true);
		}
	}
	
	private static void createSharedItems(FieldInfo field, CTSharedItems sharedItems) {
		//missing -> date -> number -> string
		int size = 0;
		if (field.containsBlank) {
			sharedItems.addNewM();
			size++;
		}
		if (field.containsDate) {
			for (Calendar cal : field.getSharedDates()) {
				sharedItems.addNewD().setV(cal);
			}
			size += field.getSharedDates().size();
		}
		if (field.containsNumber) {
			for (Double num : field.getSharedNumbers()) {
				sharedItems.addNewN().setV(num);
			}
			size += field.getSharedNumbers().size();
		}
		if (field.containsString) {
			for (String s : field.getSharedStrings()) {
				sharedItems.addNewS().setV(s);
			}
			size += field.getSharedStrings().size();
		}
		sharedItems.setCount(size);
	}
	
	private static HashMap<Integer, CTSharedItems> createSharedItems(List<FieldInfo> fields, CTCacheFields cacheFields) {
		HashMap<Integer, CTSharedItems> shareItems = new HashMap<Integer, CTSharedItems>();
		
		int itemIndex = 0;
		for (FieldInfo fieldInfo : fields) {
			CTCacheField ctCacheField = cacheFields.addNewCacheField();
			ctCacheField.setName(fieldInfo.fieldName);
			ctCacheField.setNumFmtId(fieldInfo.formatId);
			
			CTSharedItems ctSharedItems = ctCacheField.addNewSharedItems();
			setupSharedItemsProperties(fieldInfo, ctSharedItems);
			createSharedItems(fieldInfo, ctSharedItems);
			
			shareItems.put(itemIndex++, ctSharedItems);
		}
		
		return shareItems;
	}
	
	public SheetSource getSheetSource() {
		CTCacheSource source = _pivotCacheDefinition.getCacheSource();
		STSourceType.Enum type = source.getType();
		if (type == STSourceType.WORKSHEET) {
			return new SheetSourceImpl(source.getWorksheetSource());
		}
		
		return null;
	}
	
	public short getRefreshedVersion() {
		return _pivotCacheDefinition.getRefreshedVersion();
	}

	public short getMinRefreshableVersion() {
		return _pivotCacheDefinition.getMinRefreshableVersion();
	}

	public short getCreatedVersion() {
		return _pivotCacheDefinition.getCreatedVersion();
	}
	
	private class SheetSourceImpl implements SheetSource {

		private final CTWorksheetSource _source;
		SheetSourceImpl(CTWorksheetSource source) {
			_source = source;
		}
		
		public String getName() {
			return _source.getSheet();
		}

		public String getRef() {
			return _source.getRef();
		}
	}
	
	private static class FieldInfo {
		
		String fieldName;
		boolean containsNonDate;
		boolean containsDate;
		boolean containsString;
		boolean containsNumber;
		boolean containsBlank;
		boolean containsInteger = true;
		
		Calendar minDate;
		Calendar maxDate;
		
		Double minNumber;
		Double maxNumber;
		
		int formatId;
		
		LinkedHashSet<Double> sharedNumbers;
		LinkedHashSet<String> sharedStrings;
		LinkedHashSet<Calendar> sharedDates;
		
		ArrayList<Cell> cells = new ArrayList<Cell>();
		ArrayList<Object> values = new ArrayList<Object>();
		
		FieldInfo (String name) {
			fieldName = name;
		}
		
		boolean isInteger(Double src) {
			return Math.floor(src) == src;
		}
		
		boolean isDateFormat(Cell cell) {
			if (cell == null) return false;
			
			CellStyle style = cell.getCellStyle();
			if (style == null) return false;
			return DateUtil.isADateFormat(style.getDataFormat(), style.getDataFormatString());
		}
		
		boolean isValidDate(Cell cell) {
			return DateUtil.isCellDateFormatted(cell);
		}
		
		boolean isNumber(Cell cell) {
			int cellType = cell.getCellType();
			return cellType == Cell.CELL_TYPE_NUMERIC 
				|| (cellType == Cell.CELL_TYPE_FORMULA && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC);
		}
		
		boolean isString(Cell cell) {
			int cellType = cell.getCellType();
			return cellType == Cell.CELL_TYPE_STRING
				|| (cellType == Cell.CELL_TYPE_FORMULA && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_STRING);
		}
		
		void add(Cell cell) {
			cells.add(cell);
			
			if (cell != null) {
				if (isNumber(cell)) {
					formatId = cell.getCellStyle().getDataFormat();
					if (isDateFormat(cell)) {
						containsDate = true;
						if (!isValidDate(cell)) {
							containsNonDate = true;
							
							values.add(cell.getNumericCellValue());
						} else {
							XmlCalendar c = new XmlCalendar(DateUtil.getJavaDate(cell.getNumericCellValue()));
							c.clear(Calendar.ZONE_OFFSET);
							
							getSharedDates().add(c);
							values.add(c);
							
							if (minDate == null) {
								minDate = c;
							} else {
								minDate = getMinDate(c, minDate);
							}
							if (maxDate == null) {
								maxDate = c;
							} else {
								maxDate = getMaxDate(c, maxDate);
							}	
						}
					} else {//Number
						containsNumber = true;
						Double num = cell.getNumericCellValue();
						if (!isInteger(num)) {
							containsInteger = false;
						}
						
						getSharedNumbers().add(num);
						values.add(num);
						
						if (minNumber == null)
							minNumber = num;
						else
							minNumber = Math.min(num, minNumber);
						if (maxNumber == null)
							maxNumber = num;
						else
							maxNumber = Math.max(num, maxNumber);
					}
				} else if (isString(cell)) {
					containsString = true;
					String str = getCellText(cell);
					
					getSharedStrings().add(str);
					values.add(str);
				} else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
					values.add(null);
				}
			} else {
				containsBlank = true;
				values.add(null);
			}
		}
		
		private Calendar getMaxDate(Calendar c1, Calendar c2) {
			int cmp = c1.compareTo(c2);
			return cmp >= 0 ? c1 : c2;
		}

		private Calendar getMinDate(Calendar c1, Calendar c2) {
			int cmp = c1.compareTo(c2);
			return cmp <= 0 ? c1 : c2;
		}
		
		LinkedHashSet<Double> getSharedNumbers() {
			if (sharedNumbers == null) {
				sharedNumbers = new LinkedHashSet<Double>();
			}
			return sharedNumbers;
		}
		
		LinkedHashSet<Calendar> getSharedDates() {
			if (sharedDates == null) {
				sharedDates = new LinkedHashSet<Calendar>();
			}
			return sharedDates;
		}
		
		LinkedHashSet<String> getSharedStrings() {
			if (sharedStrings == null) {
				sharedStrings = new LinkedHashSet<String>();
			}
			return sharedStrings;
		}
		
		Object getValue(int index) {
			return values.get(index);
		}
		
		int size() {
			return cells.size();
		}
		
		boolean isMissing() {
			for (Object o : values) {
				if (o != null) {
					return false;
				}
			}
			return true;
		}
		
		boolean isContainsSemiMixedTypes() {
			int bTrue = 0;
			if (containsBlank) bTrue++;
			if (containsDate) bTrue++;
			if (containsString) bTrue++;
			if (containsNumber) bTrue++;
			return bTrue > 1;
		}
		
		boolean isContainsMixedTypes() {
			int bTrue = 0;
//			if (containsBlank) bTrue++;
			if (containsDate) {
				bTrue++;
				if (containsNonDate) bTrue++;
			}
			if (containsString) bTrue++;
			if (containsNumber) bTrue++;
			
			return bTrue > 1;
		}
	}
	
	private static class IndexMapper {
		List<HashMap<Object, Integer>> _mapper = new ArrayList<HashMap<Object, Integer>>();
		
		IndexMapper(HashMap<Integer, CTSharedItems> shareItems) {
			
			int itemSize = shareItems.size();
			for (int i = 0; i < itemSize; i++) {
				HashMap<Object, Integer> map = new HashMap<Object, Integer>();
				_mapper.add(map);
				
				CTSharedItems ctSharedItems = shareItems.get(i);
				
				int idx = 0;
				
				if (ctSharedItems.getContainsBlank()) {
					map.put(null, idx++);
				}
				
				if (ctSharedItems.getContainsDate()) {
					List<CTDateTime> dList = ctSharedItems.getDList();
					for (CTDateTime d : dList) {
						map.put(d.getV(), idx++);
					}
				}
				
				if (ctSharedItems.getContainsNumber()) {
					List<CTNumber> nList = ctSharedItems.getNList();
					for (CTNumber n : nList) {
						map.put(n.getV(), idx++);
					}
				}
				
				if (ctSharedItems.getContainsString()) {
					List<CTString> sList = ctSharedItems.getSList();
					for (CTString s : sList) {
						map.put(s.getV(), idx++);
					}
				}
			}
		}
		
		int getShareItemIndex(int colIndex, Object key) {
			HashMap<Object, Integer> map = _mapper.get(colIndex);
			
			Integer index = map.get(key);
			if (index != null) {
				return index;
			}
			
			return -1;
		} 
	}
}