package hiro.yoshioka.util;

import hiro.yoshioka.sdh.BindObject;
import hiro.yoshioka.sdh.DatabaseType;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Reader;
import java.io.StringReader;
import java.io.StringWriter;
import java.io.Writer;
import java.math.BigDecimal;
import java.nio.CharBuffer;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

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

public class SQLUtil {
	private static final Log fLogger = LogFactory.getLog(SQLUtil.class
			.getClass());
	public static final String VARCHAR_4000 = "VARCHAR(4000)";
	public static final String VARCHAR2_4000 = "VARCHAR2(4000)";
	public static final String FORMAT_YYYY_MM_DD_HH_MM_SS = "yyyy-MM-dd HH:mm:ss";

	public static final Object BIGINT_TO_NUMBER = "NUMBER(19)";

	// 1 [2006]
	// 2 [06]
	// 3 [08]
	public static final Pattern FORMAT_DATE = Pattern
			.compile("(\\d\\d\\d\\d).(\\d\\d).(\\d\\d)");

	// 1 [2006]
	// 2 [06]
	// 3 [08]
	// 4 [ 10:12:30.123456]
	// 5 [10:12:30]
	// 6 [10]
	// 7 [12]
	// 8 [30]
	// 9 [.123456]
	// 10 [123456]
	public static final Pattern FORMAT_TIMESTAMP = Pattern
			.compile("(\\d\\d\\d\\d).(\\d\\d).(\\d\\d)( +((\\d?\\d):(\\d?\\d):(\\d?\\d))([.](\\d+))?)?( [A-Z]+[0-9])?");

	public static final Pattern FORMAT_TIME = Pattern
			.compile("(\\d?\\d):(\\d?\\d):(\\d?\\d)");

	public static Time getTime(String value) {
		Matcher m = FORMAT_TIME.matcher(value);
		if (m.matches()) {
			return Time.valueOf(value);
		} else if ("SYSDATE".equalsIgnoreCase(value)
				|| "NOW".equalsIgnoreCase(value)) {
			return new Time(System.currentTimeMillis());
		}
		return null;
	}

	public static Boolean getBoolean(String value) {
		if (StringUtil.isEmpty(value)) {
			return null;
		}
		char c = value.charAt(0);
		if (c == '0' || c == 'f' || c == 'F' || c == '\u00d7' || c == '-') {
			return Boolean.FALSE;
		}
		return Boolean.TRUE;
	}

	public static String getStringUsingType(CallableStatement st, int idx,
			SQLDataType type) throws SQLException {
		switch (type) {
		case TIMESTAMP:
			Timestamp t = st.getTimestamp(idx);
			if (t == null) {
				return null;
			}
			java.util.Date dd = new java.util.Date(t.getTime());
			return String.format("%tF %<tT", dd);
		case DATE:
			Timestamp tt = st.getTimestamp(idx);
			if (tt == null) {
				return null;
			}
			return String.format("%tF", new java.util.Date(tt.getTime()));
		case DECIMAL:
		case NUMERIC:
			BigDecimal decimal = st.getBigDecimal(idx);
			if (decimal == null) {
				return StringUtil.EMPTY_STRING;
			} else {
				return decimal.toPlainString();
			}
		default:
			return st.getString(idx);
		}
	}

	public static Integer getInteger(Object value) {
		if (fLogger.isTraceEnabled()) {
			fLogger.trace("value[" + value + "]");
		}
		if (value == null) {
			return null;
		}
		if (value instanceof String) {
			String vstr = (String) value;
			if (vstr.length() == 0) {
				return null;
			}
			return new Integer(vstr);
		}
		if (value instanceof Number) {
			Number num = (Number) value;
			return num.intValue();
		}
		return null;
	}

	public static Short getShort(Object value) {
		if (value == null) {
			return null;
		}
		if (value instanceof String) {
			String vstr = (String) value;
			if (vstr.length() == 0) {
				return null;
			}
			return new Short(vstr);
		}
		if (value instanceof Number) {
			Number num = (Number) value;
			return num.shortValue();
		}
		return null;
	}

	public static BigDecimal getBigDecimal(String value) {
		if (fLogger.isTraceEnabled()) {
			fLogger.trace("value[" + value + "]");
		}
		if (value.length() == 0) {
			return null;
		}
		return new BigDecimal(value);
	}

	public static java.util.Date getDateOfUtil(Object o) {
		if (o == null) {
			return null;
		}
		if (o instanceof String) {
			String os = (String) o;
			return new java.util.Date(getDate(os).getTime());
		} else if (o instanceof Long) {
			Long l = (Long) o;
			if (l == 0) {
				return null;
			}
			return new java.util.Date(l);
		} else if (o instanceof java.util.Date) {
			return (java.util.Date) o;
		}
		return null;
	}

	public static Date getDateOfSql(Object value) {
		if (value == null) {
			return null;
		}
		if (value instanceof String) {
			String os = (String) value;
			return getDate(os);
		} else if (value instanceof Long) {
			Long l = (Long) value;
			if (l == 0) {
				return null;
			}
			return new java.sql.Date(l);
		} else if (value instanceof java.util.Date) {
			return new java.sql.Date(((java.util.Date) value).getTime());
		}
		return null;
	}

	public static Date getDate(String value) {
		return getDate(value, true);
	}

	public static Date getDate(String value, boolean ignoreHHMMSS) {
		if (fLogger.isTraceEnabled()) {
			fLogger.trace("value[" + value + "]");
		}
		// 3/17/2005 18:40:21

		if (ignoreHHMMSS) {
			value = value.replaceAll("\\s*\\d+:\\d+:\\d+\\s*",
					StringUtil.EMPTY_STRING);
		} else {
			return new Date(getTimeStamp(value).getTime());
		}
		if (value.length() == 0) {
			if (fLogger.isTraceEnabled()) {
				fLogger.trace("value length=0 and return null..");
			}
			return null;
		} else if (value.equalsIgnoreCase("SYSDATE")
				|| "NOW".equalsIgnoreCase(value)) {
			if (fLogger.isTraceEnabled()) {
				fLogger.trace("value is SYSDATE and return currentTimeStamp");
			}
			return new Date(System.currentTimeMillis());
		}

		if (value.matches("\\d+[ -/]+\\d+")) {
			int year = GregorianCalendar.getInstance().get(Calendar.YEAR);
			String dateVal = value.replaceAll("(\\d+)[ -/]+(\\d+)", year
					+ "-$1-$2");
			if (fLogger.isTraceEnabled()) {
				fLogger.trace(dateVal);
			}
			return Date.valueOf(dateVal);
		} else if (value.matches("\\d+[ -/]+\\d+[ -/]+\\d+")) {
			String dateVal = value.replaceAll("(\\d+)[ -/]+(\\d+)[ -/]+(\\d+)",
					"$1-$2-$3");
			if (fLogger.isTraceEnabled()) {
				fLogger.trace(dateVal);
			}
			return Date.valueOf(dateVal);
		} else if (value.matches("(\\d{4})(\\d{2})(\\d{2})")) {
			String dateVal = value.replaceAll("(\\d{4})(\\d{2})(\\d{2})",
					"$1-$2-$3");
			if (fLogger.isTraceEnabled()) {
				fLogger.trace(dateVal);
			}
			return Date.valueOf(dateVal);
		} else {
			if (fLogger.isWarnEnabled()) {
				fLogger.warn("no matches Date format[" + value + "]");
			}
			return null;
		}
	}

	public static java.sql.Timestamp getTimeStamp(String target) {
		if (target == null || target.length() == 0) {
			return null;
		} else if (target.equalsIgnoreCase("SYSDATE")
				|| "NOW".equalsIgnoreCase(target)) {
			return new Timestamp(System.currentTimeMillis());
		}
		Calendar cal = GregorianCalendar.getInstance();
		int year = cal.get(Calendar.YEAR);
		int month = cal.get(Calendar.MONTH) + 1;
		int day = cal.get(Calendar.DATE);
		int hour = 0;
		int min = 0;
		int sec = 0;
		String nano = "0";

		if (target.matches("\\d+[ -/]+\\d+")) {
			String[] v = target.split("[ -/]+");
			month = Integer.parseInt(v[0]);
			day = Integer.parseInt(v[1]);
		} else if (target.matches("(\\d{4})(\\d{2})(\\d{2})")) {
			year = Integer.parseInt(target.substring(0, 4));
			month = Integer.parseInt(target.substring(5, 6));
			day = Integer.parseInt(target.substring(7, 8));
		} else {
			Matcher m = FORMAT_TIMESTAMP.matcher(target);
			if (m.matches()) {
				year = Integer.parseInt(m.group(1));
				month = Integer.parseInt(m.group(2));
				day = Integer.parseInt(m.group(3));
				if (m.group(5) != null) {
					hour = Integer.parseInt(m.group(6));
					min = Integer.parseInt(m.group(7));
					sec = Integer.parseInt(m.group(8));
				}
				if (m.group(10) != null) {
					nano = m.group(10);
				}
			} else {
				// maybe time on notes db.
				m = FORMAT_TIME.matcher(target);
				if (m.matches()) {
					hour = Integer.parseInt(m.group(1));
					min = Integer.parseInt(m.group(2));
					sec = Integer.parseInt(m.group(3));
				}

			}
		}
		String s = String.format("%d-%02d-%02d %02d:%02d:%02d.%-9s", year,
				month, day, hour, min, sec, nano);
		java.sql.Timestamp ret = java.sql.Timestamp.valueOf(s);
		return ret;
	}

	public static void setBinds(PreparedStatement st, int idx, BindObject o)
			throws SQLException {
		if (o.target == null) {
			if (fLogger.isTraceEnabled()) {
				fLogger.info((idx) + " : setNull[" + o + "]");
			}
			st.setNull(idx, o.type.getType());
			return;
		}
		switch (o.type) {
		case BIT:
		case BOOLEAN:
			if (fLogger.isTraceEnabled()) {
				fLogger.trace((idx) + " : setBoolean[" + o + "]");
			}
			st.setBoolean(idx, (Boolean) o.target);
			break;
		case TIME:
			if (fLogger.isTraceEnabled()) {
				fLogger.trace((idx) + " : setTime[" + o + "]");
			}
			st.setTime(idx, (Time) o.target);
			break;
		case TIMESTAMP:
			if (fLogger.isTraceEnabled()) {
				fLogger.trace((idx) + " : setTimeStamp[" + o + "]");
			}
			st.setTimestamp(idx, (Timestamp) o.target);
			break;
		case DATE:
			if (fLogger.isTraceEnabled()) {
				fLogger.trace((idx) + " : setDate[" + o + "]");
			}
			st.setDate(idx, (Date) o.target);
			break;
		case SMALLINT:
			if (fLogger.isTraceEnabled()) {
				fLogger.trace((idx) + " : setShort[" + o + "]");
			}
			st.setShort(idx, (Short) o.target);
			break;
		case INTEGER:
			if (fLogger.isTraceEnabled()) {
				fLogger.trace((idx) + " : setInt[" + o + "]");
			}
			st.setInt(idx, (Integer) o.target);
			break;
		case REAL:
		case FLOAT:
		case BIGINT:
		case NUMERIC:
		case DOUBLE:
		case DECIMAL:
			if (fLogger.isTraceEnabled()) {
				fLogger.trace((idx) + " : setBigDecimal[" + o + "]");
			}
			st.setBigDecimal(idx, (BigDecimal) o.target);
			break;
		case CHAR:
		case NCHAR:
		case VARCHAR:
		case NVARCHAR:
		case LONGVARCHAR:
		case LONGNVARCHAR:
			if (fLogger.isTraceEnabled()) {
				fLogger.trace((idx) + " : setString[" + o + "]");
			}
			st.setString(idx, (String) o.target);
			break;
		default:
			if (fLogger.isTraceEnabled()) {
				fLogger.trace((idx) + " : setString[" + o + "]");
			}
			st.setString(idx, (String) o.target);
			break;
		}
	}

	public static Object cnv(String input, SQLDataType dataType) {
		switch (dataType) {
		case CHAR:
		case NCHAR:
		case VARCHAR:
		case NVARCHAR:
			return input;
		case DECIMAL:
		case NUMERIC:
		case INTEGER:
		case FLOAT:
			return getBigDecimal(input);
		case DATE:
			return getDate(input);
		case TIME:
			return getTime(input);
		case TIMESTAMP:
			return getTimeStamp(input);
		case BLOB:
			break;
		case BINARY:
			break;
		default:
		}
		return null;
	}

	// -----------------------------------------------------------------------
	// memo
	// SQLLite support type : NULL,INTEGER,REAL,TEXT,BLOB
	// -----------------------------------------------------------------------

	public static void setBindString(DatabaseType mappingFrom,
			DatabaseType mappingTo, PreparedStatement st, int idx,
			ResultSet all_rs, SQLDataType dataType) throws SQLException {
		Object o = null;
		switch (dataType) {
		case TINYINT:
		case SMALLINT:
		case INTEGER:
		case BIGINT:
		case NUMERIC:
		case DECIMAL:
			st.setObject(idx, all_rs.getObject(idx), dataType.getType());
			break;
		case FLOAT:
		case REAL:
		case DOUBLE:
			st.setObject(idx, all_rs.getObject(idx), dataType.getType());
			break;
		case CHAR:
		case VARCHAR:
		case LONGVARCHAR:
		case NCHAR:
		case NVARCHAR:
		case LONGNVARCHAR:
			st.setString(idx, all_rs.getString(idx));
			break;
		case DATE:
			st.setDate(idx, all_rs.getDate(idx));
			break;
		case TIME:
			st.setTime(idx, all_rs.getTime(idx));
			break;
		case TIMESTAMP:
			st.setTimestamp(idx, all_rs.getTimestamp(idx));
			break;
		case BIT:
		case BOOLEAN:
			st.setBoolean(idx, all_rs.getBoolean(idx));
			break;
		case CLOB:
		case NCLOB:
			// switch (mappingTo) {
			// case HSQL:
			// case MS_SQLSERVER:
			// case MYSQL:
			// case POSTGRES:
			Clob clob = all_rs.getClob(idx);
			if (clob == null) {
				st.setNull(idx, Types.VARCHAR); // hsql has nothing clob
			} else {
				// POSTGRE!!!!!!!!!!!
				// raise exception!!! I can't use stream
				// org.postgresql.jdbc4.Jdbc4PreparedStatement.setCharacterStream(int,
				// Reader)

				// st.setCharacterStream(idx, clob.getCharacterStream());

				// st.setString(idx, clob.getSubString(0, (int)
				// clob.length()));
				Reader reader = clob.getCharacterStream();
				CharBuffer buf = CharBuffer.allocate(1024);
				StringWriter out = new StringWriter();
				int n = 0;
				try {
					while ((n = reader.read(buf)) >= 0) {
						out.write(buf.array(), 0, n);
						buf.clear();
					}
				} catch (IOException e) {
					e.printStackTrace();
				}
				st.setString(idx, out.toString());
			}
			// break;
			// default:
			// st.setBytes(idx, all_rs.getBytes(idx));
			// break;
			// }
			break;
		case BLOB:
		case BINARY:
			o = all_rs.getObject(idx);
			if (o == null) {
				st.setNull(idx, Types.VARBINARY);
			} else {
				st.setBytes(idx, all_rs.getBytes(idx));
			}
			break;
		case VARBINARY:
		case LONGVARBINARY:
			o = all_rs.getObject(idx);
			if (o == null) {
				st.setNull(idx, Types.VARBINARY);
			} else {
				switch (mappingTo) {
				case ORACLE:
				case SQLITE:
				case MS_SQLSERVER:
					st.setBytes(idx, all_rs.getBytes(idx));
					break;
				default:
					st.setBinaryStream(idx, all_rs.getBinaryStream(idx));
					break;
				}
			}
			break;
		case NULL:
		case OTHER:
		case JAVA_OBJECT:
		case DISTINCT:
		case ARRAY:
		case REF:
		case DATALINK:
		case ROWID:
		case SQLXML:
			st.setObject(idx, all_rs.getObject(idx), dataType.getType());
			break;
		case STRUCT:
			st.setString(idx, all_rs.getString(idx));
			break;
		case BINARY_FLOAT: // BINARY_FLOAT
			o = all_rs.getObject(idx);
			if (o == null) {
				st.setNull(idx, Types.FLOAT);
			} else {
				st.setObject(idx, o, Types.FLOAT);
			}
			break;
		case BINARY_DOUBLE: // BINARY_DOUBLE
			o = all_rs.getObject(idx);
			if (o == null) {
				st.setNull(idx, Types.DOUBLE);
			} else {
				st.setObject(idx, o, Types.DOUBLE);
			}
			break;
		default:
			System.err.println("not seted[" + dataType + "]");
			break;
		}
	}

	public static int getMaxColumnNameBytes(Connection connection) {
		try {
			if (connection != null) {
				DatabaseMetaData meta = connection.getMetaData();
				if (meta != null) {
					return meta.getMaxColumnNameLength();
				}
			}
		} catch (SQLException e) {
			if (fLogger.isTraceEnabled()) {
				fLogger.trace(StringUtil.EMPTY_STRING, e);
			}
		}
		return 30;
	}

	public static Clob getClobOfSql(Object o) {
		if (o == null) {
			return null;
		}
		return new MyClob((String) o);
	}

	static class MyClob implements Clob {
		String clobString;

		public MyClob(String clobString) {
			this.clobString = clobString;
		}

		@Override
		public void truncate(long len) throws SQLException {
			// TODO Auto-generated method stub

		}

		@Override
		public int setString(long pos, String str, int offset, int len)
				throws SQLException {
			// TODO Auto-generated method stub
			return 0;
		}

		@Override
		public int setString(long pos, String str) throws SQLException {
			// TODO Auto-generated method stub
			return 0;
		}

		@Override
		public Writer setCharacterStream(long pos) throws SQLException {
			// TODO Auto-generated method stub
			return null;
		}

		@Override
		public OutputStream setAsciiStream(long pos) throws SQLException {
			// TODO Auto-generated method stub
			return null;
		}

		@Override
		public long position(Clob searchstr, long start) throws SQLException {
			// TODO Auto-generated method stub
			return 0;
		}

		@Override
		public long position(String searchstr, long start) throws SQLException {
			// TODO Auto-generated method stub
			return 0;
		}

		@Override
		public long length() throws SQLException {
			return this.clobString.length();
		}

		@Override
		public String getSubString(long pos, int length) throws SQLException {
			return this.clobString.substring((int) pos, (int) (pos + length));
		}

		@Override
		public Reader getCharacterStream(long pos, long length)
				throws SQLException {
			return new StringReader(this.getSubString(pos, (int) length));
		}

		@Override
		public Reader getCharacterStream() throws SQLException {
			return new StringReader(this.clobString);
		}

		@Override
		public InputStream getAsciiStream() throws SQLException {
			return null;
		}

		@Override
		public void free() throws SQLException {
			// TODO Auto-generated method stub

		}

	}
}
