package com.arconsis.locationtolife.server.verticle;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;
import org.vertx.java.core.AsyncResult;
import org.vertx.java.core.AsyncResultHandler;
import org.vertx.java.core.Handler;
import org.vertx.java.core.eventbus.EventBus;
import org.vertx.java.core.eventbus.Message;
import org.vertx.java.core.json.JsonArray;
import org.vertx.java.core.json.JsonObject;
import org.vertx.java.deploy.Verticle;

import snaq.db.ConnectionPool;

public class DataAccessManager extends Verticle {

	private static Logger LOG = Logger.getLogger(DataAccessManager.class);
	private static JsonObject CONFIG = null;
	private static ConnectionPool POOL = null;

	private static final String QUERY_ALL_ITEMS = "SELECT * FROM item_detail;";
	private static final String QUERY_ITEM_DETAIL = "SELECT * FROM item_detail WHERE id = ?";

	private EventBus eventBus = null;

	@Override
	public void start() throws Exception {
		if (LOG.isDebugEnabled()) {
			LOG.debug(">> start");
		}
		eventBus = vertx.eventBus();
		CONFIG = container.getConfig();

		initializeDatabase();
		setupMessageHandler();
	}



	private void initializeDatabase() {
		try {
			Class<?> clazz = Class.forName(CONFIG.getString("driverClass"));
		        driver = (Driver)clazz.newInstance();
		        DriverManager.registerDriver(driver);

			// Configure pool of not already done
			if (POOL == null) {
				int minPool = CONFIG.getNumber("minPool").intValue();
				int maxPool = CONFIG.getNumber("maxPool").intValue();
				int maxSize = CONFIG.getNumber("maxSize").intValue();
				int idleTimeout = CONFIG.getNumber("idleTimeout").intValue();
				String url = CONFIG.getString("jdbcUrl");
				String username = CONFIG.getString("username");
				String password = CONFIG.getString("password");

				POOL = new ConnectionPool("LOCALPOOL", minPool, maxPool,
						maxSize, idleTimeout, url, username, password);
				LOG.debug("created new connection pool instance!");
			}

		} catch (Exception ex) {
			LOG.error("Could not load database driver!", ex);
		}
	}


	private void setupMessageHandler() {

		registerItems();
	}






	private void registerItems() {
		eventBus.registerHandler("query.items", new Handler<Message<JsonObject>>() {
					public void handle(Message<JsonObject> message) {
						if (LOG.isDebugEnabled()) {
							LOG.debug(">> query.items");
						}

						//LOG.debug(message.body.toString());

						final Connection conn = getConnection(1000);
						Statement stmt = null;
						ResultSet rs = null;
						try {

							if (conn != null) {

								stmt = conn.createStatement();
								rs = stmt.executeQuery(QUERY_ALL_ITEMS);

								JsonArray jsonArray = new JsonArray();
								while (rs.next()) {
									JsonObject jsonObject = new JsonObject();
									jsonObject.putNumber("id", rs.getLong("id"));
									jsonObject.putString("name", rs.getString("name"));
									jsonObject.putString("data", rs.getString("data"));
									jsonObject.putString("created_date", rs.getTimestamp("created_date").toString());
									jsonObject.putString("changed_date", rs.getTimestamp("changed_date").toString());

									String tags = rs.getString("tags");
									jsonObject.putString("tags", tags == null ? "" : tags);

									String category = rs.getString("category");
									jsonObject.putString("category", category == null ? "" : category);

									long group_id =  rs.getLong("group_id");
									jsonObject.putNumber("group_id", group_id);

									jsonArray.add(jsonObject);
								}

								JsonObject replyObject = new JsonObject();
								replyObject.putArray("items", jsonArray);

								message.reply(replyObject.putString("status", "ok"));
							} else {
								// timeout occurred!
								LOG.debug("TIMEOUT OCCURED");
								message.reply(message.body.putString("status", "error"));
							}


						} catch (Exception e) {
								LOG.error("Error ", e);
								message.reply(message.body.putString("status", "error"));
						} finally {
							try {
								if (rs != null) {rs.close();};
								if (stmt != null) {stmt.close();};
								if (conn!= null) {conn.close();};
							} catch(SQLException e) { /* .. */};
						}
					}
				}, new AsyncResultHandler<Void>() {
					public void handle(AsyncResult<Void> arg0) {
						if (LOG.isDebugEnabled()) {
							LOG.debug(">> query.items handler registered");
						}
					}
				});

		eventBus.registerHandler("query.items.detail", new Handler<Message<JsonObject>>() {
			public void handle(Message<JsonObject> message) {
				if (LOG.isDebugEnabled()) {
					LOG.debug(">> query.items.detail");
				}

				//LOG.debug(message.body.toString());
				final Integer item_detail_id = message.body.getNumber("item_detail_id").intValue();

				final Connection conn = getConnection(1000);
				PreparedStatement stmt = null;
				ResultSet rs = null;
				try {

					if (conn != null) {

						stmt = conn.prepareStatement(QUERY_ITEM_DETAIL);
						stmt.setInt(1, item_detail_id);
						rs = stmt.executeQuery();


						JsonObject jsonObject = null;
						while (rs.next()) {
							jsonObject = new JsonObject();
							jsonObject.putNumber("id", rs.getLong("id"));
							jsonObject.putString("name", rs.getString("name"));
							jsonObject.putString("data", rs.getString("data"));
							jsonObject.putString("created_date", rs.getTimestamp("created_date").toString());
							jsonObject.putString("changed_date", rs.getTimestamp("changed_date").toString());

							String tags = rs.getString("tags");
							jsonObject.putString("tags", tags == null ? "" : tags);

							String category = rs.getString("category");
							jsonObject.putString("category", category == null ? "" : category);

							long group_id =  rs.getLong("group_id");
							jsonObject.putNumber("group_id", group_id);
						}


						JsonObject replyObject = new JsonObject();

						String responseStatus = "ok";
						if (jsonObject != null) {
							replyObject.putObject("item", jsonObject);
						} else {
							responseStatus = "notFound";
						}

						message.reply(replyObject.putString("status", responseStatus));


					} else {
						// timeout occurred!
						LOG.debug("TIMEOUT OCCURED");
						message.reply(message.body.putString("status", "error"));
					}


				} catch (Exception e) {
						LOG.error("Error ", e);
						message.reply(message.body.putString("status", "error"));
				} finally {
					try {
						if (rs != null) {rs.close();};
						if (stmt != null) {stmt.close();};
						if (conn!= null) {conn.close();};
					} catch(SQLException e) { /* .. */};
				}
			}
		}, new AsyncResultHandler<Void>() {
			public void handle(AsyncResult<Void> arg0) {
				if (LOG.isDebugEnabled()) {
					LOG.debug(">> query.items.detail handler registered");
				}
			}
		});
	}




	private final Connection getConnection(long timeout) {

		Connection conn = null;
		try {

			conn = POOL.getConnection(timeout);
		} catch (Exception ex) {
		    LOG.error("DB Exception: " + ex.getMessage());
		}

		return conn;
	}


}