/*
 * This file is part of Nuts Framework.
 * Copyright (C) 2009 Nuts Develop Team.
 *
 * Nuts Framework is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License any later version.
 * 
 * Nuts Framework is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.	 See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with Nuts Framework. If not, see <http://www.gnu.org/licenses/>.
 */
package nuts.exts.struts2.actions;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.TreeSet;

import nuts.core.io.CsvReader;
import nuts.core.io.IOUtils;
import nuts.core.lang.StringUtils;
import nuts.core.orm.dao.DataAccessSession;
import nuts.core.orm.dao.ModelDAO;
import nuts.core.orm.dao.ModelMetaData;
import nuts.exts.fileupload.UploadFile;

import org.apache.commons.lang.exception.ExceptionUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;


/**
 */
@SuppressWarnings({ "unchecked", "serial" })
public class DataImportAction extends CommonDataAccessAction {
	protected static class DataType {
		String type;
		String format;
		
		public DataType(String type) {
			int i = type.indexOf(':');
			if (i >= 0) {
				this.type = type.substring(0, i);
				this.format = type.substring(i + 1);
			}
			else {
				this.type = type;
			}
		}
	}
	
	protected boolean propertyHeader = false;
	protected boolean deleteAll = false;
	
	protected Set<String> targetSet;
	protected String target;
	protected String encoding = "UTF-8";
	protected UploadFile uploadFile = new UploadFile();
	protected List uploadDataList = new ArrayList();
	protected int commitSize = 1000;
	
	/**
	 * @return the propertyHeader
	 */
	public boolean isPropertyHeader() {
		return propertyHeader;
	}

	/**
	 * @param propertyHeader the propertyHeader to set
	 */
	public void setPropertyHeader(boolean propertyHeader) {
		this.propertyHeader = propertyHeader;
	}

	/**
	 * @return the deleteAll
	 */
	public boolean isDeleteAll() {
		return deleteAll;
	}

	/**
	 * @param deleteAll the deleteAll to set
	 */
	public void setDeleteAll(boolean deleteAll) {
		this.deleteAll = deleteAll;
	}

	/**
	 * @return the uploadDataList
	 */
	public List getUploadDataList() {
		return uploadDataList;
	}

	/**
	 * @return the uploadCsv
	 */
	public UploadFile getUploadFile() {
		return uploadFile;
	}

	/**
	 * @return the target
	 */
	public String getTarget() {
		return target;
	}

	/**
	 * @param target the target to set
	 */
	public void setTarget(String target) {
		this.target = target;
	}

	/**
	 * @return the targetSet
	 */
	public Set<String> getTargetSet() {
		if (targetSet == null) {
			targetSet = new TreeSet();
			targetSet.addAll(getDataAccessSession().getMetaDataMap().keySet());
		}
		return targetSet;
	}

	/**
	 * @return the encoding
	 */
	public String getEncoding() {
		return encoding;
	}

	/**
	 * @param encoding the encoding to set
	 */
	public void setEncoding(String encoding) {
		this.encoding = encoding;
	}

	/**
	 * @return the commitSize
	 */
	public int getCommitSize() {
		return commitSize;
	}

	/**
	 * @param commitSize the commitSize to set
	 */
	public void setCommitSize(int commitSize) {
		this.commitSize = commitSize;
	}

	protected static SimpleDateFormat timestampFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
	protected static SimpleDateFormat datetimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	protected static SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
	
	protected Date parseDate(String str, String format) throws Exception {
		if ("now".equalsIgnoreCase(str)) {
			return Calendar.getInstance().getTime();
		}
		if (StringUtils.isNotBlank(format)) {
			SimpleDateFormat df = new SimpleDateFormat(format);
			return df.parse(str);
		}
		try {
			return timestampFormat.parse(str);
		}
		catch (ParseException e) {
			try {
				return datetimeFormat.parse(str);
			}
			catch (ParseException e2) {
				return dateFormat.parse(str);
			}
		}
	}

	protected Object getCellValue(String v, int c, List<DataType> types) throws Exception {
		Object cv = null;
		if (StringUtils.isNotBlank(v)) {
			String type = types.get(c).type;
			String format = types.get(c).format;
			if ("boolean".equalsIgnoreCase(type)) {
				if ("true".equalsIgnoreCase(v) || "1".equals(v) || "yes".equalsIgnoreCase(v)) {
					cv = true;
				}
				else if ("false".equalsIgnoreCase(v) || "0".equals(v) || "no".equalsIgnoreCase(v)) {
					cv = false;
				}
			}
			else if ("char".equalsIgnoreCase(type) 
					|| "character".equalsIgnoreCase(type)) {
				cv = v.length() > 0 ? v.charAt(0) : '\0';
			}
			else if ("number".equalsIgnoreCase(type) 
					|| "numeric".equalsIgnoreCase(type)
					|| "decimal".equalsIgnoreCase(type)) {
				cv = NumberFormat.getInstance().parse(v);
			}
			else if ("byte".equalsIgnoreCase(type)) {
				cv = NumberFormat.getInstance().parse(v).byteValue();
			}
			else if ("short".equalsIgnoreCase(type)) {
				cv = NumberFormat.getInstance().parse(v).shortValue();
			}
			else if ("int".equalsIgnoreCase(type)
					|| "integer".equalsIgnoreCase(type)) {
				cv = NumberFormat.getInstance().parse(v).intValue();
			}
			else if ("long".equalsIgnoreCase(type)) {
				cv = NumberFormat.getInstance().parse(v).longValue();
			}
			else if ("float".equalsIgnoreCase(type)) {
				cv = NumberFormat.getInstance().parse(v).floatValue();
			}
			else if ("double".equalsIgnoreCase(type)) {
				cv = NumberFormat.getInstance().parse(v).doubleValue();
			}
			else if ("bigint".equalsIgnoreCase(type)
					|| "BigInteger".equalsIgnoreCase(type)) {
				cv = new BigInteger(v);
			}
			else if ("bigdec".equalsIgnoreCase(type)
					|| "BigDecimal".equalsIgnoreCase(type)) {
				cv = new BigDecimal(v);
			}
			else if ("date".equalsIgnoreCase(type)) {
				cv = parseDate(v, format);
			}
			else {
				cv = v;
			}
		}
		return cv;
	}

	/**
	 * @return INPUT
	 * @throws Exception if an error occurs
	 */
	public String execute() throws Exception {
		try {
			String fext = IOUtils.getFileNameExtension(uploadFile.getFileName());
			if ("xls".equalsIgnoreCase(fext)) {
				byte[] data = uploadFile.getData();
				if (data != null) {
					impXls(data);
				}
			}
			else if ("csv".equalsIgnoreCase(fext)) {
				if (StringUtils.isEmpty(target)) {
					addFieldError("target", getText("validation-required"));
				}
				else {
					byte[] data = uploadFile.getData();
					if (data != null) {
						impCsv(data);
					}
				}
			}
			else if (StringUtils.isNotEmpty(uploadFile.getFileName())) {
				addFieldError("uploadFile", getText("error-file-format"));
			}
		}
		finally {
			if (uploadFile.getFile() != null) {
				uploadFile.getFile().delete();
			}
		}
		return INPUT;
	}

	/**
	 * XlsImport
	 * @param data data
	 */
	protected void impXls(byte[] data) {
		try {
			InputStream is = new ByteArrayInputStream(data);
			HSSFWorkbook wb = new HSSFWorkbook(is);
			for (int i = 0; i < wb.getNumberOfSheets(); i++) {
				HSSFSheet sheet = wb.getSheetAt(i);
				impXlsSheetData(sheet);
			}
		}
		catch (Exception e) {
			log.warn("XlsImport", e);
			String s = ExceptionUtils.getStackTrace(e);
			addActionError(e.getMessage() + "\n"
					+ (s.length() > 500 ? s.substring(0, 500) + "..." : s));
		}
	}

	private String getPropertyName(ModelMetaData mmd, String c) {
		return propertyHeader ? c : mmd.getPropertyName(c);
	}
	
	private Map<String, Object> getPropertyValues(ModelMetaData mmd, Map<String, Object> values) {
		if (!propertyHeader) {
			Map<String, Object> pvs = new HashMap<String, Object>();
			for (Entry<String, Object> en : values.entrySet()) {
				String pn = mmd.getPropertyName(en.getKey());
				pvs.put(pn, en.getValue());
			}
			values = pvs;
		}
		return values;
	}
	
	protected void impXlsSheetData(HSSFSheet sheet) throws Exception {
		List uploadData = new ArrayList();
		uploadDataList.add(uploadData);
		
		target = sheet.getSheetName();
		
		uploadData.add(target);

		ModelMetaData mmd = getDataAccessSession().getMetaData(target);
		if (mmd == null) {
			throw new Exception("Incorrent target: " + target);
		}

		List<String> columns = getHeadValues(sheet, 0);
		if (columns.isEmpty()) {
			throw new Exception("[" + uploadFile.getFileName() + "!" + target + "] - the table column is empty!");
		}
		uploadData.add(columns);
		
		for (String c : columns) {
			String p = getPropertyName(mmd, c);
			if (StringUtils.isEmpty(p)) {
				throw new Exception("[" + uploadFile.getFileName() + "!" + target + "] - the table column(" + c + ") is incorrect!");
			}
		}
		
		List<String> row2 = getHeadValues(sheet, 1);
		if (row2.size() != columns.size()) {
			throw new Exception("[" + uploadFile.getFileName() + "!" + target + "] - the column types is incorrect!");
		}
		uploadData.add(row2);
		
		List<DataType> types = new ArrayList<DataType>();
		for (String v : row2) {
			types.add(new DataType(v));
		}
		
		DataAccessSession das = getDataAccessSession();
		ModelDAO dao = das.getModelDAO(target);

		if (deleteAll) {
			dao.deleteAll();
		}

		int cnt = 0;
		for (int i = 2; ; i++) {
			Map<String, Object> values = getRowValues(sheet, i, columns, types, uploadData);
			if (values == null) {
				break;
			}

			values = getPropertyValues(mmd, values);
			
			Object bean = mmd.createObject();
			mmd.setDataProperties(bean, values);
			dao.save(bean);

			cnt++;
			if (cnt % commitSize == 0) {
				das.commit();
			}
		}
		if (cnt > 0 && cnt % commitSize != 0) {
			das.commit();
		}
		addActionMessage(getText("success-import", 
				new String[] { target, String.valueOf(cnt) }));
	}	
	
	protected List<String> getHeadValues(HSSFSheet sheet, int r) throws Exception {
		List<String> values = new ArrayList<String>();

		HSSFRow row = sheet.getRow(r);
		if (row != null) {
			for (int c = 0; ; c++) {
				HSSFCell cell = row.getCell(c);
				if (cell == null) {
					break;
				}
				String v = null;
				try {
					v = cell.getStringCellValue();
					if (StringUtils.isBlank(v)) {
						break;
					}
					values.add(v);
				}
				catch (Exception e) {
					throw new Exception("[" + sheet.getSheetName() + "] - head value is incorrect: (" + r + "," + c + ") - " + v, e);
				}
			}
		}
		
		return values;
	}
	
	protected Map<String, Object> getRowValues(HSSFSheet sheet, int r,
			List<String> columns, List<DataType> types, List uploadData)
			throws Exception {
		HSSFRow row = sheet.getRow(r);
		if (row == null) {
			return null;
		}

		String[] line = new String[columns.size()];
		
		boolean empty = true;
		Map<String, Object> values = new HashMap<String, Object>(columns.size());
		for (int c = 0; c < columns.size(); c++) {
			HSSFCell cell = row.getCell(c);
			if (cell == null) {
				continue;
			}

			String v = null;
			try {
				switch (cell.getCellType()) {
				case Cell.CELL_TYPE_NUMERIC:
					v = String.valueOf(cell.getNumericCellValue());
					if (StringUtils.contains(v, '.')) {
						v = StringUtils.stripEnd(v, "0");
						v = StringUtils.stripEnd(v, ".");
					}
					break;
				case Cell.CELL_TYPE_FORMULA:
					try {
						v = String.valueOf(cell.getNumericCellValue());
					}
					catch (Exception e) {
						v = cell.getStringCellValue();
					}
					break;
				default: 
					v = cell.getStringCellValue();
					break;
				}
				line[c] = v;

				Object cv;
				if (StringUtils.isBlank(v)) {
					cv = null;
				}
				else {
					empty = false;

					String type = types.get(c).type;
					String format = types.get(c).format;
					if ("date".equalsIgnoreCase(type)) {
						try {
							cv = cell.getDateCellValue();
						}
						catch (Exception e) {
							cv = parseDate(v, format);
						}
					}
					else {
						cv = getCellValue(v, c, types);
					}
				}
				values.put(columns.get(c), cv);
			}
			catch (Exception e) {
				String msg = getText("error-value", new String[] {
						sheet.getSheetName(), 
						String.valueOf(r + 1), String.valueOf(c + 1),
						String.valueOf(v), e.getMessage()
				});
				throw new Exception(msg);
			}
		}

		if (empty) {
			return null;
		}
		else {
			uploadData.add(line);
			return values;
		}
	}

	/**
	 * CsvImport
	 * @param data data
	 */
	protected void impCsv(byte[] data) {
		try {
			ModelMetaData mmd = getDataAccessSession().getMetaData(target);

			List uploadData = new ArrayList();
			uploadDataList.add(uploadData);
			
			uploadData.add(target);

			CsvReader csv = getCsvReader(data);

			List<String> columns = csv.readNext();
			if (columns == null || columns.isEmpty()) {
				throw new Exception("[" + uploadFile.getFileName() + "] - the table column is empty!");
			}
			uploadData.add(columns);
			
			for (String c : columns) {
				String p = getPropertyName(mmd, c);
				if (StringUtils.isEmpty(p)) {
					throw new Exception("[" + uploadFile.getFileName() + "] - the table column(" + c + ") is incorrect!");
				}
			}
			
			List<String> row2 = csv.readNext();
			if (row2 == null || row2.size() != columns.size()) {
				throw new Exception("[" + uploadFile.getFileName() + "] - the column types is incorrect!");
			}
			uploadData.add(row2);
			
			List<DataType> types = new ArrayList<DataType>();
			for (String v : row2) {
				types.add(new DataType(v));
			}

			DataAccessSession das = getDataAccessSession();
			ModelDAO dao = das.getModelDAO(target);

			if (deleteAll) {
				dao.deleteAll();
			}
			
			int cnt = 0;
			for (int i = 3; ; i++) {
				Map<String, Object> values = getRowValues(csv, i, columns,
						types, uploadData);
				if (values == null) {
					break;
				}

				values = getPropertyValues(mmd, values);
				
				Object bean = mmd.createObject();
				mmd.setDataProperties(bean, values);
				dao.save(bean);

				cnt++;
				if (cnt % commitSize == 0) {
					das.commit();
				}
			}

			if (cnt > 0 && cnt % commitSize != 0) {
				das.commit();
			}
			addActionMessage(getText("success-import", 
					new String[] { target, String.valueOf(cnt) }));
		}
		catch (Exception e) {
			log.warn("CsvImport", e);

			String s = ExceptionUtils.getStackTrace(e);
			addActionError(e.getMessage() + "\n"
					+ (s.length() > 500 ? s.substring(0, 500) + "..." : s));
		}
	}

	protected Map<String, Object> getRowValues(CsvReader csv, int r,
			List<String> columns, List<DataType> types, List uploadData)
			throws Exception {
		List<String> row = csv.readNext();
		if (row == null) {
			return null;
		}
		uploadData.add(row);

		boolean empty = true;
		
		Map<String, Object> values = new HashMap<String, Object>(columns.size());
		for (int c = 0; c < columns.size(); c++) {
			String v = null;
			if (c < row.size()) {
				v = row.get(c);
			}
			
			try {
				Object cv = getCellValue(v, c, types);

				empty = (cv == null);

				values.put(columns.get(c), cv);
			}
			catch (Exception e) {
				String msg = getText("error-value", new String[] {
						target, String.valueOf(r), String.valueOf(c),
						String.valueOf(v), e.getMessage()
				});
				throw new Exception(msg);
			}
		}
		
		return empty ? null : values;
	}

	protected CsvReader getCsvReader(byte[] data) throws Exception {
		int offset = 0;
		int length = data.length;
		
		if (length > 3 && "UTF-8".equalsIgnoreCase(encoding)) {
			if (data[0] == -17 && data[1] == -69 && data[2] == -65) {
				offset = 3;
				length -= 3;
			}
		}
		return new CsvReader(new InputStreamReader(
				new ByteArrayInputStream(data, offset, length), encoding));
	}
}
