package hiro.yoshioka.sdh2;

import hiro.yoshioka.sdh.CSVRecordDataHolder.HeaderData;
import hiro.yoshioka.sdh.BindObject;
import hiro.yoshioka.sdh.HeaderType;
import hiro.yoshioka.sdh.ResultSetDataHolder;
import hiro.yoshioka.sdh.ResultSetMetaCopy;
import hiro.yoshioka.sdh.StringRecordData;
import hiro.yoshioka.util.SQLDataType;
import hiro.yoshioka.util.SQLUtil;
import hiro.yoshioka.util.StringUtil;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class ReflectionPreparedStatement {
	public static final String FORMAT_YYYY_MM_DD_HH_MM_SS = "yyyy-MM-dd HH:mm:ss";
	private Map<String, String> columnNameSubstitutionMap;
	ResultSetMetaCopy meta;
	StringRecordData[] newData, oldData;
	int[] primaryIdx;
	String tableName;
	String schemaName;
	boolean withOutSchemaAlias;
	ArrayList<BindObject> bindList = new ArrayList<BindObject>();

	protected transient Log fLogger = LogFactory.getLog(getClass());

	public ReflectionPreparedStatement(ResultSetMetaCopy meta,
			StringRecordData[] newData, StringRecordData[] oldData,
			int[] primaryIdx, String tableName, String schemaName,
			Map<String, String> columnNameSubstitutionMap,
			boolean withOutSchemaAlias) {
		this.meta = meta;
		this.newData = newData;
		this.oldData = oldData;
		this.primaryIdx = primaryIdx;
		this.tableName = tableName;
		this.schemaName = schemaName;
		this.columnNameSubstitutionMap = columnNameSubstitutionMap;
		this.withOutSchemaAlias = withOutSchemaAlias;
	}

	public ReflectionPreparedStatement(ResultSetMetaCopy meta, String[] datumn,
			String tableName, String schemaName) {
		this.meta = meta;
		this.tableName = tableName;
		this.schemaName = schemaName;
		newData = new StringRecordData[datumn.length + 1];
		newData[0] = new ResultSetDataHolder().new HeaderData(
				StringUtil.EMPTY_STRING, HeaderType.INSERT);
		for (int i = 1; i < newData.length; i++) {
			newData[i] = new StringRecordData(datumn[i - 1]);
		}
	}

	public void setBinds(PreparedStatement st) throws SQLException {
		for (int i = 0; i < bindList.size(); i++) {
			BindObject o = bindList.get(i);
			SQLUtil.setBinds(st, i + 1, o);
		}
	}

	public String getStatement() throws SQLException {
		bindList.clear();
		StringBuffer buff = new StringBuffer();
		if (newData == null) { // blobRef
			buff.append("SELECT ");
			buff.append(meta.getBlobOrBinary());
			buff.append(" FROM ");
			buff.append(getSchemaTableName(1));
			buff.append(createWhere(oldData));
			return buff.toString();
		}
		HeaderData header = (HeaderData) newData[0];
		if (header.update()) {
			for (int i = 1; i < newData.length; i++) {
				if (!newData[i].getString().equals(oldData[i].getString())) {
					if (buff.length() == 0) {
						buff.append("UPDATE ");
						buff.append(getSchemaTableName(i));
						buff.append(" SET ");
					}
					buff.append(getSubstitutionColumnName(meta.getColumnName(i))
							+ " = ?,");
					updateByType(SQLDataType.parse(meta.getColumnType(i)),
							newData[i].getString());
				}
			}
			buff.setLength(buff.length() - 1);
			buff.append(createWhere(oldData));
		} else if (header.insert()) {
			StringBuffer valueBuff = new StringBuffer();
			for (int i = 1; i < newData.length; i++) {
				if (meta.getColumnName(i).indexOf("$") >= 0) {
					if (fLogger.isTraceEnabled()) {
						fLogger.trace("this column [" + meta.getColumnName(i)
								+ "] is ignored.");
					}
					continue;
				}
				if (buff.length() == 0) {
					buff.append("INSERT INTO ");
					buff.append(getSchemaTableName(i));
					buff.append(" ( ");
				}
				buff.append(getSubstitutionColumnName(meta.getColumnName(i))
						+ " ,");
				valueBuff.append("?,");
				updateByType(SQLDataType.parse(meta.getColumnType(i)),
						newData[i].getString());
			}
			buff.setLength(buff.length() - 1);
			valueBuff.setLength(valueBuff.length() - 1);
			buff.append(") VALUES (").append(valueBuff).append(")");
		} else if (header.delete()) {
			buff.append("DELETE FROM ");
			buff.append(getSchemaTableName(1));
			buff.append(" ");
			buff.append(createWhere(newData));
		}
		return buff.toString();
	}

	private void updateByType(SQLDataType columnType, String value) {
		fLogger.trace("columnType[" + columnType + "]");
		if (value == null) {
			// bindList.add(null);
			bindList.add(new BindObject(null, columnType));
			return;
		}
		switch (columnType) {

		case BIT:
		case BOOLEAN:
			bindList.add(new BindObject(SQLUtil.getBoolean(value), columnType));
			return;

		case TIME:
			bindList.add(new BindObject(SQLUtil.getTime(value), columnType));
			return;
		case SMALLINT:
			bindList.add(new BindObject(SQLUtil.getShort(value), columnType));
			return;
		case INTEGER:
			bindList.add(new BindObject(SQLUtil.getInteger(value), columnType));
			return;
		case REAL:
		case FLOAT:
		case BIGINT:
		case NUMERIC:
		case DOUBLE:
		case DECIMAL:
			bindList.add(new BindObject(SQLUtil.getBigDecimal(value),
					columnType));
			return;
		case TIMESTAMP:
			bindList.add(new BindObject(SQLUtil.getTimeStamp(value), columnType));
			return;
		case DATE:
			bindList.add(new BindObject(SQLUtil.getDate(value), columnType));
			return;
		default:
			if (value.length() == 0) {
				value = null;
			}
			bindList.add(new BindObject(value, columnType));
		}
	}

	public String getTableName() {
		return tableName;
	}

	private String getSchemaTableName(int column) throws SQLException {
		String schema = meta.getSchemaName(column);
		String table = meta.getTableName(column);
		if (!StringUtil.isEmpty(schemaName)) {
			schema = schemaName;
		}
		if (schema == null || schema.length() == 0 || withOutSchemaAlias) {
			if (table == null || table.length() == 0) {
				return tableName;
			}
			return meta.getTableName(column);
		}
		return schema + "." + meta.getTableName(column);
	}

	private String createWhere(StringRecordData[] sr) throws SQLException {
		StringBuffer buff = new StringBuffer();
		buff.append(" WHERE ");
		for (int i = 0; i < primaryIdx.length; i++) {
			if (i > 0) {
				buff.append(" AND ");
			}
			int idx = primaryIdx[i] + 1;
			buff.append(getSubstitutionColumnName(meta.getColumnName(idx)))
					.append("=?");
			updateByType(SQLDataType.parse(meta.getColumnType(idx)),
					sr[idx].getString());
		}
		return buff.toString();
	}

	private String getSubstitutionColumnName(String sourceName) {
		if (this.columnNameSubstitutionMap == null) {
			return sourceName;
		}
		String name = this.columnNameSubstitutionMap.get(sourceName
				.toUpperCase());
		if (StringUtil.isEmpty(name)) {
			return sourceName;
		}
		return name;
	}
}
