package hiro.yoshioka.sql.engine;

import hiro.yoshioka.sdh.DatabaseType;
import hiro.yoshioka.sql.DataBaseFactory;
import hiro.yoshioka.sql.HSQL;
import hiro.yoshioka.sql.IConnectSQL;
import hiro.yoshioka.sql.IRequestListener;
import hiro.yoshioka.sql.ITransactionSQL;
import hiro.yoshioka.sql.SqlBasicListener;
import hiro.yoshioka.sql.SqlTransactionListener;
import hiro.yoshioka.sql.params.ConnectionProperties;
import hiro.yoshioka.sql.params.ConnectionSettingBean;
import hiro.yoshioka.sql.params.IConnectionPropertiesChangeListener;
import hiro.yoshioka.sql.params.PropetiesChangeType;
import hiro.yoshioka.sql.params.SSHProperties;
import hiro.yoshioka.sql.resource.DBRoot;
import hiro.yoshioka.sql.util.SQLHistroyManager;
import hiro.yoshioka.sql.util.SQLUtil2;
import hiro.yoshioka.util.FileUtil;
import hiro.yoshioka.util.StringUtil;
import hiro.yoshioka.util.ref.ReferenceTracker;

import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;

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

import ch.ethz.ssh2.Connection;
import ch.ethz.ssh2.LocalPortForwarder;

public class SQLServerThread extends Thread implements IRequestListener {
	protected Log fLogger = LogFactory.getLog(getClass());
	private static final SQLServerThread fSingle = new SQLServerThread(
			"SQLServer");

	private RequestQueue requestQueue;

	private boolean fExit;

	private boolean fInitialized;

	private File configFileDir;
	private File connectionSetFile;
	private File connectionSetXmlFile;
	private ConnectionSettingBean fConnectionSettingBean;
	private int requestCount, endOfRequestCount;

	Thread fThread;

	private Map<ConnectionProperties, ITransactionSQL> sqlMap = new LinkedHashMap<ConnectionProperties, ITransactionSQL>();

	private Set<IConnectionPropertiesChangeListener> fConPropeteisUpdateList;
	private Set<SqlBasicListener> fConnectionList = new HashSet<SqlBasicListener>();
	private Set<SqlTransactionListener> fTransactionList = new HashSet<SqlTransactionListener>();
	private Set<IRequestListener> fRequestListenerList = new HashSet<IRequestListener>();
	private ScheduledExecutorService fScheduledExecutorService = Executors
			.newSingleThreadScheduledExecutor();

	public static SQLServerThread getSQLServer() {
		return fSingle;
	}

	public int getRequestCount() {
		return requestCount;
	}

	public int getEndOfRequestCount() {
		return endOfRequestCount;
	}

	private SQLServerThread(String name) {
		super(name);
		SQLHistroyManager.getInstance();
		KeepAliveCheckTask command = new KeepAliveCheckTask();
		fScheduledExecutorService.scheduleWithFixedDelay(command, 30, 30,
				TimeUnit.SECONDS);
	}

	class KeepAliveCheckTask implements Runnable {
		KeepAliveCheckTask() {
		}

		@Override
		public void run() {
			boolean ret = false;
			for (ConnectionProperties p : sqlMap.keySet()) {
				if (p == null) {
					continue;
				}
				if (checkAlive(p)) {
					ITransactionSQL sql = sqlMap.get(p);
					try {
						ret = sql.isAlive(p);
						fLogger.trace("ret=" + ret);
					} catch (Throwable e) {
						fLogger.warn(StringUtil.EMPTY_STRING, e);
					}
					fLogger.info("keep alive [" + ret + "]");
				}
			}
		}
	}

	private boolean checkAlive(ConnectionProperties p) {
		try {
			fLogger.trace("ConnectionProperties:" + p);
			DatabaseType type = p.getDatabaseType();
			int interval = p.getKeepAliveInterval();
			if (p.isConnected() && type.enableKeepAlive() && interval > 0) {
				ITransactionSQL sql = sqlMap.get(p);
				Date latestTime = sql.getLatestRequestTime();
				if (latestTime == null) {
					return true;
				}
				long div = (System.currentTimeMillis() - latestTime.getTime()) / 1000;
				fLogger.trace("div/interval " + div + "/" + interval);
				return div >= interval;
			}
			return false;
		} catch (Exception e) {
			fLogger.fatal(StringUtil.EMPTY_STRING, e);
		}
		return false;
	}

	private void startKeepAliveTimer(ITransactionSQL sql, int intervalSec) {
		if (intervalSec <= 0) {
			return;
		}
	}

	private void stopKeepAliveTimer() {
		fScheduledExecutorService.shutdownNow();
	}

	public boolean isClosable(ConnectionProperties properties) {
		ITransactionSQL sql = getTransactionSQL(properties);
		if (sql == null) {
			// fLogger.info("sql=null ! properties:" + properties);
			return false;
		}
		return sql.canDoOperation(SQLOperationType.CLOSE);
	}

	public ITransactionSQL getTransactionSQL(DBRoot root) {
		for (ConnectionProperties key : sqlMap.keySet()) {
			if (key.getDBRootResource() == root) {
				return getTransactionSQL(key);
			}
		}
		return null;
	}

	public HSQL createHSQLLocalServerTransactionSQL() {
		ConnectionProperties hsqlProp = fConnectionSettingBean
				.getLocalHsqlServerConnection();
		ITransactionSQL sql = sqlMap.get(hsqlProp);
		if (sql == null || !hsqlProp.isConnected()) {
			sql = createTransactionSql(hsqlProp, null);
		}
		return (HSQL) sql;
	}

	public ITransactionSQL getTransactionSQL(ConnectionProperties properties) {
		if (fLogger.isInfoEnabled()) {
			if (properties == null) {
				fLogger.fatal("ConnectionProperties is null");
				// } else {
				// fLogger.info("input key:" + properties.getCreated() +
				// " DISP:"
				// + properties.getDisplayString());
				// }
				// for (ConnectionProperties key : sqlMap.keySet()) {
				// fLogger.info("now contain key:" + key.getCreated() + " DISP:"
				// + key.getDisplayString());
			}
		}
		return sqlMap.get(properties);
	}

	public void addConnectionPropertiesChangedListener(
			IConnectionPropertiesChangeListener listener) {
		if (fConPropeteisUpdateList == null) {
			fConPropeteisUpdateList = new HashSet<IConnectionPropertiesChangeListener>();
			;
		}
		fConPropeteisUpdateList.add(listener);
	}

	public void addConnectionListner(SqlBasicListener listener) {
		fConnectionList.add(listener);
		for (ITransactionSQL sql : sqlMap.values()) {
			sql.addConnectionListner(listener);
		}
	}

	public void removeConnectionListener(SqlBasicListener listener) {
		fConnectionList.remove(listener);
		for (ITransactionSQL sql : sqlMap.values()) {
			sql.removeConnetionListener(listener);
		}
	}

	public void addTracsactionListner(SqlTransactionListener listener) {
		fTransactionList.add(listener);
		for (ITransactionSQL sql : sqlMap.values()) {
			sql.addTracsactionListner(listener);
		}
	}

	public void removeTracsactionListner(SqlTransactionListener listener) {
		fTransactionList.remove(listener);
		for (ITransactionSQL sql : sqlMap.values()) {
			sql.removeTracsactionListner(listener);
		}
	}

	public void addRequestListner(IRequestListener listener) {
		fRequestListenerList.add(listener);
	}

	public void removeRequestListner(IRequestListener listener) {
		fRequestListenerList.remove(listener);
	}

	public boolean wasInitialized() {
		fLogger.info("fInitialized:" + fInitialized);
		return fInitialized;
	}

	public static boolean runNowThisRequest(Request request) {
		return getSQLServer().runNow(request);
	}

	public static int putRequest(Request request) {
		getSQLServer().requestQueue.putRequest(request);
		return getSQLServer().requestQueue.getSize();
	}

	public File getConfigFileDir() {
		return configFileDir;
	}

	public boolean init(File configFileDir) {
		this.configFileDir = configFileDir;
		if (!this.configFileDir.exists()) {
			if (!this.configFileDir.mkdir()) {
				return false;
			}
		}
		this.connectionSetFile = new File(configFileDir, "connections.def");
		this.connectionSetXmlFile = new File(configFileDir,
				"connections.def.xml");
		if (fThread != null) {
			dispose();
		}
		loadConnectionProperties();
		fConnectionSettingBean.initConnectionStatus();

		fExit = false;
		this.requestQueue = new RequestQueue();

		fThread = new Thread(this);
		fThread.setName("I'm Daemon For acceptting SQL Request ");
		fThread.setDaemon(true);
		fThread.start();
		ReferenceTracker.weakTrace(fThread);
		fInitialized = true;

		return true;
	}

	private void loadConnectionProperties() {
		ConnectionSettingBean load = null;

		load = (ConnectionSettingBean) SQLUtil2.readObject(connectionSetFile);

		try {
			if (load == null) {
				System.err.println("load from xml file ");
				load = (ConnectionSettingBean) SQLUtil2
						.readObjectXML(connectionSetXmlFile);
			}
		} catch (Exception e2) {
			e2.printStackTrace();
		}

		if (load == null) {
			fConnectionSettingBean = new ConnectionSettingBean();
			fConnectionSettingBean.getLocalHsqlServerConnection();
			sqlMap = new LinkedHashMap<ConnectionProperties, ITransactionSQL>();
		} else {
			fConnectionSettingBean = load;
		}
	}

	public boolean changeConnectionProperties(
			PropetiesChangeType propetiesChangeType,
			Collection<ConnectionProperties> newProperties) throws IOException {
		boolean ret = false;
		if (fConnectionSettingBean.changeConnectionProperties(newProperties)) {
			ret = saveConnectionProperties();
		}
		updateConnection(propetiesChangeType);
		return ret;
	}

	public ConnectionSettingBean getConnectionSettingBean() {
		return fConnectionSettingBean;
	}

	public boolean saveConnectionProperties() throws IOException {
		System.out.println("save :: " + fConnectionSettingBean);
		return FileUtil.save(connectionSetFile, fConnectionSettingBean);
	}

	public boolean saveConnectionPropertiesToXml() throws IOException {
		return SQLUtil2.saveXML(connectionSetXmlFile, fConnectionSettingBean);
	}

	public void dispose() {
		fExit = true;
		try {
			saveConnectionProperties();
			stopKeepAliveTimer();
		} catch (Exception e1) {
			e1.printStackTrace();
		}
		try {
			saveConnectionPropertiesToXml();
		} catch (IOException e1) {
			e1.printStackTrace();
		}
		fLogger.info("dispose... ");
		if (fInitialized) {
			requestQueue.putRequest(new NoMoreRequest(SQLOperationType.TEST));
		}
		fLogger.info("canncel");
		try {
			SQLHistroyManager.getInstance().save();
		} catch (Exception e) {
			e.printStackTrace();
		}
		for (ITransactionSQL sql : sqlMap.values()) {
			if (sql != null && sql instanceof IConnectSQL) {
				try {
					sql.cansel();
				} catch (SQLException e) {
				}
				try {
					IConnectSQL isql = (IConnectSQL) sql;
					isql.close();
				} catch (SQLException e) {
				}
			}
		}
		try {
			HsqlServerManager.dispose();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private void putAllRequestListener(Request request) {
		for (IRequestListener requestListener : fRequestListenerList) {
			request.addListener(requestListener);
		}
	}

	private boolean runNow(Request request) {
		request.addListener(this);
		this.putAllRequestListener(request);

		fLogger.info(Thread.currentThread().getName() + " handles  " + request);
		if (SQLOperationType.CONNECT == request.operation) {
			return (createTransactionSql(request.getConnectionProperties(),
					request) != null);
		} else {
			return request.execute();
		}
	}

	public void run() {
		for (int i = 0; i < 1000000; i++) {
			Request request = requestQueue.getRequest();
			request.addListener(this);
			this.putAllRequestListener(request);
			if (fExit) {
				fLogger.info(Thread.currentThread().getName()
						+ "Exit... and sutemasukara- handles  " + request);
				break;
			}
			fLogger.info(Thread.currentThread().getName() + " handles  "
					+ request);
			if (SQLOperationType.CONNECT == request.operation) {
				createTransactionSql(request.getConnectionProperties(), request);
			} else {
				request.start();
			}
			try {
				Thread.sleep(1000);
			} catch (InterruptedException e) {
			}
		}
	}

	private ITransactionSQL createTransactionSql(ConnectionProperties prop,
			Request request) {
		ITransactionSQL sql = sqlMap.get(prop);
		if (sql == null) {
			sql = DataBaseFactory.createSQL(prop);
			sqlMap.put(prop, sql);
			for (SqlBasicListener basic : fConnectionList) {
				sql.addConnectionListner(basic);
			}
			for (SqlTransactionListener transaction : fTransactionList) {
				sql.addTracsactionListner(transaction);
			}
		}
		if (sql != null && request != null) {
			if (request.execute()) {
				if (prop.getDatabaseType().enableKeepAlive()) {
					startKeepAliveTimer(sql, prop.getKeepAliveInterval());
				}
			} else {
				if (request.e instanceof SQLException) {
					SQLException se = (SQLException) request.e;
					// if (fSQL instanceof HSQL) {
					// if
					// (se.getMessage().equals("socket creation error")
					// || se.getErrorCode() == -80) {
					// // if (!HSQLAction.isRunning()) {
					// // Application
					// // .openDialog(
					// // "Info",
					// // "Please push HSQL Server button at tool bar",
					// // MessageDialog.INFORMATION);
					// // }
					// }
					// }
				}
			}
		}
		return sql;
	}

	public void removeConnectionPropertiesChangedListener(
			IConnectionPropertiesChangeListener listener) {
		if (fConPropeteisUpdateList != null) {
			fConPropeteisUpdateList.remove(listener);
		}
	}

	public void updateConnection(PropetiesChangeType type) {
		if (fConPropeteisUpdateList != null) {
			for (IConnectionPropertiesChangeListener obj : fConPropeteisUpdateList) {
				if (obj != null) {
					try {
						obj.changed(type, fConnectionSettingBean);
					} catch (Exception e) {
					}
				}
			}
		}
	}

	class NoMoreRequest extends Request {
		public NoMoreRequest(SQLOperationType operation) {
			super(operation);
		}
	}

	public void called_pre(Request request, SQLOperationType operation) {
		requestCount++;
	}

	public void called_done(Request request, SQLOperationType operation,
			ConnectionProperties properteis, Object o) {
		endOfRequestCount++;
		if (!request.hasException() && request instanceof TransactionRequest) {
			SQLHistroyManager.getInstance().addStatement(
					(TransactionRequest) request);
		}
	}

	public Collection<Request> listActiveRequest() {
		return requestQueue.list();
	}

	public void beginTask(String taskName, int row) {
	}

	public void subTask(String subTaskName) {
	}

	public void worked(int i) {
	}

	public Collection<ConnectionProperties> getConnectionSet() {
		return fConnectionSettingBean.getConnectionSet();
	}

	// ---------------------------------------------------------
	// jdbc over an SSH tunnel
	// ---------------------------------------------------------
	class SSHConnectionInfo {
		SSHProperties sshProperties;
		ch.ethz.ssh2.Connection conn;
		LocalPortForwarder lpf1;

		public SSHConnectionInfo(SSHProperties sshProperties) {
			this.sshProperties = sshProperties;
		}

		public void makeTunnel() throws IOException {
			/* Create a connection instance */
			this.conn = new ch.ethz.ssh2.Connection(sshProperties.hostname);

			/* Now connect */
			this.conn.connect();

			/* Authenticate */
			boolean isAuthenticated = conn.authenticateWithPassword(
					sshProperties.username, sshProperties.password);

			if (isAuthenticated == false) {
				throw new IOException("Authentication failed.");
			}
			// local_port
			// host_to_connect
			// port_to_connect
			this.lpf1 = conn.createLocalPortForwarder(sshProperties.local_port,
					sshProperties.host_to_connect,
					sshProperties.port_to_connect);

			this.conn.requestRemotePortForwarding(sshProperties.bindAddress,
					sshProperties.bindPort, sshProperties.targetAddress,
					sshProperties.targetPort);
		}

		public boolean closeTunnel() throws IOException {
			this.conn.cancelRemotePortForwarding(this.sshProperties.bindPort);

			/*
			 * Stop accepting connections on 127.0.0.1:8080 that are being
			 * forwarded to www.ethz.ch:80
			 */
			lpf1.close();

			/* Close the connection */
			conn.close();
			return true;
		}
	}

	private Map<SSHProperties, SSHConnectionInfo> sshConnectionMap = new HashMap<SSHProperties, SSHConnectionInfo>();

	private boolean makeTunnel(SSHProperties sshProperties) {
		try {
			SSHConnectionInfo info = sshConnectionMap.get(sshProperties);
			info.makeTunnel();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	private boolean closeTunnel(SSHProperties sshProperties) {
		try {
			SSHConnectionInfo info = sshConnectionMap.get(sshProperties);
			info.closeTunnel();
		} catch (Exception e) {
		}
		return true;
	}
}