/*
 * This file is part of Nuts Framework.
 * Copyright(C) 2009-2012 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.tools.sql;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import nuts.core.lang.StringUtils;
import nuts.core.orm.sql.SqlExecutor;
import nuts.core.orm.sql.engine.SimpleSqlExecutor;

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;

/**
 * Import data from xls to database
 */
public class XlsDataImportor extends AbstractDataImportor {
	/**
	 * Main class
	 */
	public static class Main extends AbstractDataImportor.Main {
		/**
		 * @param args arguments
		 */
		public static void main(String[] args) {
			Main cgm = new Main();
			
			Object cg = new XlsDataImportor();

			cgm.execute(cg, args);
		}
	}
	
	/**
	 * Constructor
	 */
	public XlsDataImportor() {
		includes = new String[] { "**/*.xls" };
	}

	@Override
	protected void importFile(FileInputStream fis) throws Exception {
		HSSFWorkbook wb = new HSSFWorkbook(fis);
		if (truncate) {
			for (int i = wb.getNumberOfSheets() - 1; i >= 0; i--) {
				HSSFSheet sheet = wb.getSheetAt(i);
				String tableName = sheet.getSheetName(); 
				println2("Truncating table: " + tableName);
				truncateTable(tableName);
			}
		}
		for (int i = 0; i < wb.getNumberOfSheets(); i++) {
			HSSFSheet sheet = wb.getSheetAt(i);
			impXlsSheetData(sheet);
		}
	}
	
	private void impXlsSheetData(HSSFSheet sheet) throws Exception {
		String tableName = sheet.getSheetName();
		
		List<String> columns = getHeadValues(sheet, 0);
		if (columns.isEmpty()) {
			throw new Exception("[" + tableName + "] - the table column is empty!");
		}
		
		List<String> row2 = getHeadValues(sheet, 1);
		if (row2.size() != columns.size()) {
			throw new Exception("[" + tableName + "] - the column types is incorrect!");
		}
		
		List<DataType> types = new ArrayList<DataType>();
		for (String v : row2) {
			types.add(new DataType(v));
		}
		
		println2("Importing table: " + tableName);

		String insertSql = getInsertSql(tableName, columns, types);

		int line = 2;
		Map<String, Object> values = null;
		try {
			SqlExecutor executor = new SimpleSqlExecutor(connection); 

			int cnt = 0;
			for (; ; line++) {
				values = getRowValues(sheet, line, columns, types);
				if (values == null) {
					break;
				}
				cntRecord += executor.update(insertSql, values);
				cnt++;
				if (commit > 0 && cnt >= commit) {
					connection.commit();
				}
			}

			if (cnt > 0) {
				connection.commit();
			}
		}
		catch (Exception e) {
			rollback();
			throw new Exception("Failed to import sheet [" + tableName + "]:" 
				+ line + " - " + values, e);
		}
	}	
	
	private 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;
	}
	
	private Map<String, Object> getRowValues(HSSFSheet sheet, int r, List<String> columns, List<DataType> types) throws Exception {
		HSSFRow row = sheet.getRow(r);
		if (row == null) {
			return null;
		}

		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;
				}

				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) {
				throw new Exception("[" + sheet.getSheetName()
						+ "] - cell value is incorrect: (" 
						+ (r + 1) + "," + (c + 1)
						+ ") - " + v, e);
			}
		}
		
		return empty ? null : values;
	}
}
