/**
 *   Copyright 2007 Y.Murakamin
 *
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 */
package net.murakamin.sticker.commands;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Arrays;

import net.murakamin.csv.CSVParser;
import net.murakamin.sticker.CommandRunner;
import net.murakamin.sticker.ConnectionPool;
import net.murakamin.sticker.Sticker;
import net.murakamin.sticker.StickerContext;
import net.murakamin.sticker.commands.enums.TargetType;
import net.murakamin.sticker.commands.exception.CommandExecutionException;
import net.murakamin.sticker.commands.util.StickerContextUtil;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.SystemUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.supercsv.cellprocessor.ift.CellProcessor;
import org.supercsv.cellprocessor.ift.StringCellProcessor;
import org.supercsv.io.CsvListWriter;
import org.supercsv.prefs.CsvPreference;
import org.supercsv.util.CSVContext;

/**
 * All data is exported from the remote data base to the CSV file. And all data
 * is exported from a remote data base to a local data base(hsqldb).
 * 
 * @author Y.Murakamin
 * @see net.murakamin.sticker.commands.ImportCommand
 * @see net.murakamin.sticker.commands.enums.TargetType
 * 
 */
public class ExportCommand implements Command, StickerCSVDataHandler {

	private final Log log = LogFactory.getLog(Command.class);

	private ConnectionPool conPool;
	private String destination;
	private String fileCharset = SystemUtils.FILE_ENCODING;

	private boolean header = true;
	private boolean overwrite = true;
	private char quote = CSVParser.QUOTE_NONE;
	private boolean forceQuote = false;
	private char separator = ',';
	private String sql = StringUtils.EMPTY;
	private TargetType target = TargetType.remote;

	private CsvListWriter writer;
	private StickerContext context;
	private CellProcessor stringCellProcesser;

	/**
	 * export csv data.
	 * 
	 * @see net.murakamin.csv.CSVDataHandler#addCSVData(String[])
	 */
	public void addCSVData(final String[] currentDatas) throws Exception {
		String destination = StickerContextUtil.getReplacementString(
				this.context, this.getDestination());

		if (!this.conPool.existsLocalTable(destination)) {
			CellProcessor[] cellProcessers = new CellProcessor[currentDatas.length];

			for (int c = 0; c < currentDatas.length; c++) {
				if (currentDatas[c] == null) {
					currentDatas[c] = StringUtils.EMPTY;
				}
				// replace end of line symbol for superCSV writer.
				currentDatas[c] = currentDatas[c].replaceAll("(\r\n)|(\r)",
						"\n");
				cellProcessers[c] = this.stringCellProcesser;

			}

			// remote to file
			this.writer.write(Arrays.asList(currentDatas), cellProcessers);

		} else {
			// remote to local

			String sql = this.createInsertPrepareStatement(destination,
					currentDatas);

			// sql = sql.replaceAll("\n", " ");
			sql = sql.replaceAll("\t", " ");
			if (sql.charAt(sql.length() - 1) == ';') {
				sql = sql.substring(0, sql.length() - 1);
			}

			Connection destConnection = this.conPool.getLocalConnection();

			PreparedStatement stmt = destConnection.prepareStatement(sql);
			int parameterIndex = 1;
			for (Object field : currentDatas) {
				stmt.setObject(parameterIndex, field);
				parameterIndex++;
			}

			if (Sticker.isDebug()) {
				StringBuffer buffer = new StringBuffer();
				for (String param : currentDatas) {
					buffer.append('[');
					buffer.append(param);
					buffer.append(']');
				}
				this.log.debug(stmt + ": parameters=" + buffer.toString());
			}
			try {
				stmt.execute();
			} catch (Exception e) {
				StringBuffer buffer = new StringBuffer();
				for (String param : currentDatas) {
					buffer.append('[');
					buffer.append(param);
					buffer.append(']');
				}

				this.log.error(buffer.toString());
				throw e;
			} finally {
				if (stmt != null) {
					stmt.close();
				}
			}
		}

	}

	/**
	 * The SQL insert statement is made based on the specified argument.
	 * 
	 * @param targetTable
	 *            table name
	 * @param values
	 *            insert values
	 * @return insert statement(SQL statement)
	 * @throws UnsupportedEncodingException
	 */
	private String createInsertPrepareStatement(final String targetTable,
			final String[] values) throws UnsupportedEncodingException {
		StringBuffer sql = new StringBuffer("insert into ");

		sql.append(targetTable);
		sql.append(" values (");
		for (String field : values) {
			sql.append('?');
			sql.append(',');
		}
		sql.replace(sql.length() - 1, sql.length(), ")");

		return sql.toString();
	}

	/**
	 * execute the csv data export. If the name specified with destination
	 * exists in local DB, it exports to local DB. It exports to the file of the
	 * name specified with destination besides.
	 * 
	 * @see net.murakamin.sticker.commands.execute(CommandRunner runner)
	 */
	public void execute(final CommandRunner runner) throws Exception {

		this.conPool = runner.getConnectionPool();
		this.context = runner.getStickerContext();

		if (StringUtils.isEmpty(this.getSql().trim())) {
			throw new CommandExecutionException(this,
					"SQL of the export is not defined.");
		}

		if (this.forceQuote == true && this.quote == CSVParser.QUOTE_NONE) {
			throw new CommandExecutionException(
					this,
					Messages.getString("net.murakamin.sticker.commands.ExportCommand.invalidForceQuote"));
		}

		
		// The quote character is given to both ends of the character by using
		// cellprocesser
		if (this.forceQuote == true) {
			// if the forceQuote attribute is true.
			this.stringCellProcesser = new StringCellProcessor() {
				@Override
				public Object execute(Object arg0, CSVContext arg1) {

					final String quoteRegex = new StringBuffer("(").append(quote).append(")").toString(); 
					final String quoteReplacement = new StringBuffer().append(quote).append(quote).toString(); 
					
					String target = (String)arg0;
					if ( forceQuote == true ){
						target = target.replaceAll(quoteRegex, quoteReplacement);
					}
					return new StringBuffer(target).insert(0, quote).append(quote).toString();
				}
			};
		} else {
			// quote char none
			this.stringCellProcesser = new StringCellProcessor() {
				@Override
				public Object execute(Object arg0, CSVContext arg1) {
					return (String) arg0;
				}
			};
		}

		ResultSet rs = null;
		PreparedStatement stmt = null;

		Connection sourceConnection = conPool.getRemoteConnection();
		try {
			// record select
			if (target == TargetType.local) {
				sourceConnection = conPool.getLocalConnection();
			}

			String sql = StickerContextUtil.applyContextVariable(this.context,
					this.getSql());
			String executableSql = StickerContextUtil.getReplacementString(
					this.context, sql);
			String destination = StickerContextUtil.getReplacementString(
					this.context, this.getDestination());

			stmt = sourceConnection.prepareStatement(executableSql);
			rs = stmt.executeQuery();

			// The header information is previously exported by using
			// ResultSetMetaData when the name specified with destination exists
			// in local DB.
			if (!conPool.existsLocalTable(destination)) {
				CsvPreference pref = new CsvPreference(
						(this.forceQuote) ? CSVParser.QUOTE_NONE : this.getQuote(), 
								this.getSeparator(), "\n");

				File destFile = new File(destination);
				this.writer = new CsvListWriter(new OutputStreamWriter(
						new FileOutputStream(destFile, !this.isOverwrite()),
						this.getFileCharset()), pref);

				if (this.isHeader()) {
					ResultSetMetaData rsMetaData = rs.getMetaData();
					String[] header = new String[rsMetaData.getColumnCount()];
					for (int c = 1; c < rsMetaData.getColumnCount() + 1; c++) {
						header[c - 1] = rsMetaData.getColumnName(c);
					}
					if (this.isOverwrite()
							|| !(new File(this.destination).exists())) {
						this.writer.writeHeader(header);
					}
				}
			}

			new CSVParser(this).doParse(rs);
			runner.run(this);

		} catch (Exception e) {
			throw new CommandExecutionException(this, e);
		} finally {
			String destination = StickerContextUtil.getReplacementString(
					this.context, this.getDestination());
			if (!conPool.existsLocalTable(destination)) {
				if (this.writer != null) {
					this.writer.close();
				}
			}

			if (rs != null) {
				rs.close();
			}
			if (stmt != null) {
				stmt.close();
			}
		}
	}

	/**
	 * get the export the CSV data ahead. Local data base table name or CSV file
	 * name
	 * 
	 * @return The export the CSV data ahead.
	 */
	public String getDestination() {
		return destination;
	}

	/**
	 * get the name of csv file encoding
	 * 
	 * @return the csv file encoding
	 * @see net.murakamin.csv.CSVDataHandler#getFileCharset()
	 */
	public String getFileCharset() {
		return this.fileCharset;
	}

	/**
	 * get the csv quote charactor. default value is '"'.
	 * 
	 * @return the csv quote charactor
	 * @see net.murakamin.csv.CSVDataHandler#getQuote()
	 */
	public char getQuote() {
		return this.quote;
	}

	public boolean isForceQuote() {
		return forceQuote;
	}

	public void setForceQuote(boolean forceQuot) {
		this.forceQuote = forceQuot;
	}

	/**
	 * get the csv field separator charactor. default value is ','.
	 * 
	 * @return the csv field separator charactor
	 * @see net.murakamin.csv.CSVDataHandler#getSeparator()
	 */
	public char getSeparator() {
		return this.separator;
	}

	/**
	 * get the sql statmenet. Data is acquired by using this SQL statement, and
	 * it exports as CSV data.
	 * 
	 * @return the sql statement.
	 */
	public String getSql() {
		return sql;
	}

	/**
	 * The acquisition source of the CSV data is acquired.
	 * 
	 * @return the source of data acquisition
	 */
	public String getTarget() {
		return this.target.name();
	}

	/**
	 * @see net.murakamin.sticker.commands.ExecutableCommand#getVersionTerm()
	 */
	public VersionTerm getVersionTerm() {
		return VersionTerm.PERMANENT_COMMAND;
	}

	/**
	 * @see net.murakamin.sticker.commands.ExecutableCommand#isDebugPrint()
	 */
	public boolean isDebugPrint() {
		return true;
	}

	/**
	 * True when header is output to CSV file
	 * 
	 * @return True when header is output to CSV file
	 */
	public boolean isHeader() {
		return this.header;
	}

	/**
	 * It is true if it overwrites in the place where the CSV file is exported
	 * when the file already exists.
	 * 
	 * @return true when overwrite file
	 */
	public boolean isOverwrite() {
		return overwrite;
	}

	/**
	 * set the export the CSV data ahead. Local data base table name or CSV file
	 * name
	 * 
	 * @param destination
	 *            The export the CSV data ahead.
	 */
	public void setDestination(final String destination) {
		this.destination = destination;
	}

	/**
	 * get the name of csv file encoding
	 * 
	 * @param the
	 *            name of csv file encoding
	 */
	public void setFileCharset(final String fileCharset) {
		this.fileCharset = fileCharset;
	}

	/**
	 * True when header is output to CSV file
	 * 
	 * @param header
	 *            True when header is output to CSV file
	 */
	public void setHeader(final boolean header) {
		this.header = header;
	}

	/**
	 * It is true if it overwrites in the place where the CSV file is exported
	 * when the file already exists.
	 * 
	 * @param overwrite
	 *            true when overwrite file
	 */
	public void setOverwrite(final boolean overwrite) {
		this.overwrite = overwrite;
	}

	/**
	 * set the csv quote charactor. default value is '"'.
	 * 
	 * @param quote
	 *            the csv quote charactor
	 * @see net.murakamin.csv.CSVDataHandler#getQuote()
	 */
	public void setQuote(final char quot) {
		this.quote = quot;
	}

	/**
	 * set the csv field separator charactor. default value is ','.
	 * 
	 * @param separator
	 *            the csv field separator charactor
	 */
	public void setSeparator(final char separator) {
		this.separator = separator;
	}

	/**
	 * set the sql statmenet. Data is acquired by using this SQL statement, and
	 * it exports as CSV data.
	 * 
	 * @param sql
	 *            the sql statement.
	 */
	public void setSql(final String sql) {
		this.sql = sql;
	}

	/**
	 * The acquisition source of the CSV data is acquired.
	 * 
	 * @param target
	 *            the source of data acquisition
	 */
	public void setTarget(final String target) {
		this.target = TargetType.valueOf(target);
	}

	@Override
	public String toString() {
		StringBuffer buffer = new StringBuffer();

		buffer.append("<export> export execute:\n");

		buffer.append(" destination:");
		buffer.append(this.getDestination());
		buffer.append(SystemUtils.LINE_SEPARATOR);

		buffer.append(" target:");
		buffer.append(this.getTarget());
		buffer.append(SystemUtils.LINE_SEPARATOR);

		buffer.append(" header:");
		buffer.append(this.isHeader());
		buffer.append(SystemUtils.LINE_SEPARATOR);

		buffer.append(" separator:");
		buffer.append(this.getSeparator());
		buffer.append(SystemUtils.LINE_SEPARATOR);

		buffer.append(" quote:");
		buffer.append(this.getQuote() == CSVParser.QUOTE_NONE ? "None" : this
				.getQuote());
		buffer.append(SystemUtils.LINE_SEPARATOR);

		buffer.append(" charset:");
		buffer.append(this.getFileCharset());
		buffer.append(SystemUtils.LINE_SEPARATOR);

		buffer.append(" sql:");
		buffer.append(this.getSql());
		buffer.append(SystemUtils.LINE_SEPARATOR);

		return buffer.toString();
	}

}
