package hiro.yoshioka.sql;

import hiro.yoshioka.classmanager.ClassManager;
import hiro.yoshioka.sdh.DatabaseType;
import hiro.yoshioka.sql.evernote.EverNoteSQL;
import hiro.yoshioka.sql.mongo.MongoSQL;
import hiro.yoshioka.sql.params.ConnectionProperties;
import hiro.yoshioka.sql.params.DBConnectionInitialItem;
import hiro.yoshioka.sql.twitter.TwitterSQL;
import hiro.yoshioka.util.SQLDataType;
import hiro.yoshioka.util.StringUtil;

import java.io.File;
import java.sql.Driver;
import java.util.HashMap;

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

public class DataBaseFactory {

	protected static Log fLogger = LogFactory.getLog(DataBaseFactory.class
			.getClass());

	static HashMap<String, Driver> driverMap = new HashMap<String, Driver>();

	public static HSQL createHSQL() {
		return new HSQL(new jdbcDriver());
	}

	public static final ConnectionProperties createHSQLInistialItem() {
		ConnectionProperties ret = new ConnectionProperties();
		ret.setAuthenticate(null, "sa", StringUtil.EMPTY_STRING);
		ret.setDisplayString("LocalHsqlDB");
		ret.setDriverName(DatabaseType.HSQL.getDriverName());
		return ret;
	}

	public static ITransactionSQL createSQL(ConnectionProperties prop) {
		return createSQL(prop.getDriverFilePath(), prop.getDriverName());
	}

	public static ITransactionSQL createSQL(DBConnectionInitialItem item) {
		return createSQL(item.fDriverFilePath, item.fDriverName);
	}

	public static ITransactionSQL createSQL(String jarfile, String driverName) {
		try {
			ClassManager class_manager = null;
			File jarF = new File(jarfile);
			fLogger.info("JARFILE=" + jarfile + " DriverName=" + driverName);
			AbsTransactionSQL sql = null;
			DatabaseType dbType = DatabaseType.parse(driverName);
			switch (dbType) {
			case ODBC:
				return new ODBCSQL(null);
			case MONGO:
				return new MongoSQL();
			case DOMINO:
				class_manager = new ClassManager();
				class_manager.add_lib(jarF);
				return new DominoSQL(class_manager);
			case EVERNOTE:
				return new EverNoteSQL();
			case TWITTER:
				return new TwitterSQL();
			case HSQL:
				return createHSQL();
			}

			Driver driver = driverMap.get(driverName);

			if (driver == null) {
				class_manager = new ClassManager();
				class_manager.add_lib(jarF);
				Class cls = class_manager.getClassForName(driverName);
				fLogger.info("cls=" + cls);
				if (cls != null) {
					driver = (Driver) cls.newInstance();
					fLogger.info("driver=" + driver);
				}
				if (driver != null) {
					driverMap.put(driverName, driver);
				}
			}

			switch (dbType) {
			case ORACLE:
				sql = new OracleSQL(driver, jarF);
				break;
			case MYSQL:
				sql = new MySQL(driver);
				break;
			case MS_SQLSERVER:
				sql = new SQLServerSQL(driver);
				break;
			case SYBASE:
				sql = new SybaseSQL(driver);
				break;
			case HSQL:
				sql = new HSQL(new jdbcDriver());
				break;
			case POSTGRES:
				sql = new PostgresSQL(driver);
				break;
			case SQLITE:
				sql = new SQLiteSQL(driver);
				break;
			case UNKNOWN:
				sql = new GeneralSQL(driver);
				break;
			}

			return sql;
		} catch (Exception e) {
			fLogger.fatal(StringUtil.EMPTY_STRING, e);
		}
		return null;
	}

	public static final String getStringFormatHH24MI(DatabaseType databaseType,
			String colname) {
		StringBuilder buf = new StringBuilder();
		switch (databaseType) {
		case ORACLE:
			return "TO_CHAR(" + colname + ",'HH24:MI')";
		case MYSQL:
			return "DATE_FORMAT(" + colname + ",'%H:%i')";
		case HSQL:
			buf.append("CASE WHEN HOUR(").append(colname).append(")<10 ");
			buf.append("THEN TO_CHAR(").append(colname).append(",'hh:mm') ");
			buf.append("ELSE TO_CHAR(").append(colname)
					.append(",'HH24:mm') END ");
			return buf.toString();
		}
		return "TO_CHAR(" + colname + ",'HH24:MI')";
	}

	public static final String getStringFormat(SQLDataType sqlType,
			DatabaseType databaseType, String colname) {
		StringBuilder buf = new StringBuilder();
		switch (databaseType) {
		case ORACLE:
			return "TO_CHAR(" + colname + ",'YYYY/MM/DD HH24:MI:SS')";
		case MYSQL:
			return "DATE_FORMAT(" + colname + ",'%Y/%m/%d %T')";
		case HSQL:
			if (SQLDataType.DATE == sqlType) {
				buf.append("TO_CHAR(").append(colname)
						.append(",'yyyy/MM/dd 00:00:00') ");
			} else {
				buf.append("CASE WHEN HOUR(").append(colname).append(")<10 ");
				buf.append("THEN TO_CHAR(").append(colname)
						.append(",'yyyy/MM/dd hh:mm:ss') ");
				buf.append("ELSE TO_CHAR(").append(colname)
						.append(",'yyyy/MM/dd HH24:mm:ss') END ");
			}
			return buf.toString();
		}
		return "TO_CHAR(" + colname + ",'YYYY/MM/DD HH24:MI:SS')";
	}

	public static final String getSuggestURL(DatabaseType type) {
		switch (type) {
		case ORACLE:
			return OracleSQL.getSuggestURL();
		case MYSQL:
			return MySQL.getSuggestURL();
		case MS_SQLSERVER:
			return SQLServerSQL.getSuggestURL();
		case HSQL:
			return HSQL.getSuggestURL();
		case POSTGRES:
			return PostgresSQL.getSuggestURL();
		case ODBC:
			return ODBCSQL.getSuggestURL();
		case SQLITE:
			return SQLiteSQL.getSuggestURL();
		case EVERNOTE:
			return EverNoteSQL.getHostString();
		}
		return StringUtil.EMPTY_STRING;
	}

	public static String getLimit(String driverName) {
		switch (DatabaseType.parse(driverName)) {
		case ORACLE:
			return OracleSQL.getLimitString();
		case MYSQL:
			return MySQL.getLimitString();
		case HSQL:
			// return HSQL.getLimitString();
		}
		return StringUtil.EMPTY_STRING;
	}

	public static String getToDateBindingFormat(String driverName) {
		switch (DatabaseType.parse(driverName)) {
		case ORACLE:
			return "TO_DATE(?,'YYYY-MM-DD HH24:MI:SS')";
		case HSQL:
			return "?";
		}
		return StringUtil.EMPTY_STRING;
	}

	public static boolean supportTtuncate(String driverName) {
		switch (DatabaseType.parse(driverName)) {
		case HSQL:
			return false;
		case SQLITE:
			return false;
		}
		return true;
	}

}
