/*
 *  Copyright (C) 2006  Takashi Kasuya <kasuya@sfc.keio.ac.jp>
 *
 * This library is free software; you can redistribute it and/or
 *　modify it under the terms of the GNU Lesser General Public
 *　License as published by the Free Software Foundation; either
 *　version 2.1 of the License, or (at your option) any later version.
 *　This library is distributed in the hope that it will be useful,
 *　but WITHOUT ANY WARRANTY; without even the implied warranty of
 *　MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 *　Lesser General Public License for more details.
 *
 *　You should have received a copy of the GNU Lesser General Public
 *　License along with this library; if not, write to the Free Software
 *　Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 */

package jp.ac.naka.ec.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.Map;
import java.util.logging.Logger;

import javax.sip.address.SipURI;

import jp.ac.naka.ec.Location;
import jp.ac.naka.ec.entity.Entity;
import jp.ac.naka.ec.entity.EntityType;
import jp.ac.naka.ec.serial.MoxaSensorData;
import jp.ac.naka.ec.serial.SensorData;

/**
 * データベースの管理クラス。
 * 
 * @author Takashi Kasuya
 * 
 */
public class DatabaseConnector {
	public static String driver_name = "org.h2.Driver";
	public static String db_location = "jdbc:h2:tcp://localhost:9092/";
	public static String catalog = "test";
	public static String user = "sa";
	public static String pass = "";
	public static String top_table = "data";

	public static String type_table = "type", data_table = "sensor",
			entity_table = "entity", location_table = "location";

	private Connection con;
	private Map types;

	private static Logger logger = Logger.getLogger("ECLog");
	private static DatabaseConnector instance = new DatabaseConnector();

	private DatabaseConnector() {
	}

	/**
	 * Retrieve an Instance.
	 * @return
	 * @throws SQLException
	 */
	public static DatabaseConnector getInstance() throws SQLException {
		try {
			initialize();
		} catch (ClassNotFoundException e) {
			throw new SQLException(e.getMessage());
		}
		return instance;
	}

	/**
	 * Initiazie JDBC parameter.
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static void initialize() throws ClassNotFoundException, SQLException {
		Class.forName(driver_name);
		instance.con = DriverManager.getConnection(db_location + catalog, user,
				pass);
	}

	/**
	 * テーブルが作られているか？
	 * 
	 * @return
	 * @throws SQLException
	 */
	public boolean hasTables() throws SQLException {
		DatabaseMetaData meta = con.getMetaData();
		// テーブル情報の取得
		ResultSet rs = meta.getTables(null, null, null,
				new String[] { "TABLE" });
		while (rs.next()) {
			// テーブル名の取得
			String table_name = rs.getString(3);
			if (table_name.equalsIgnoreCase(top_table)) {
				return true;
			}
		}
		return false;
	}

	/**
	 * 新しいテーブルの作成
	 * 
	 * @return　テーブルが作成されたらtrue
	 * @throws SQLException
	 */
	public boolean createNewTable() throws SQLException {
		// トップのテーブル作成
		String query = "CREATE TABLE IF NOT EXISTS " + top_table + "("
				+ "ID INT AUTO_INCREMENT PRIMARY KEY," + "TYPE INT NOT NULL,"
				+ "SENSOR_DATA INT," + "OTHER_DATA VARCHAR(255),"
				+ "SOURCE INT," + "TARGET INT," + "LOCATION INT, "+"TIME TIMESTAMP NOT NULL)";
		Statement stmt = con.createStatement();

		stmt.execute(query);
		// センサデータ用のテーブル作成
		query = "CREATE TABLE IF NOT EXISTS " + data_table + "("
				+ "ID INT AUTO_INCREMENT PRIMARY KEY," + " TYPE INT NOT NULL,"
				+ " VALUE REAL NOT NULL," + "SEQ INT," + "SRC_ADDR INT,"
				+ "SRC_PAN_ID INT," + "RSSI INT)";
		stmt.execute(query);

		// Type用テーブル作成
		query = "CREATE TABLE IF NOT EXISTS " + type_table + "("
				+ "ID INT AUTO_INCREMENT PRIMARY KEY,"
				+ "TYPE VARCHAR(20) NOT NULL)";
		stmt.execute(query);
		// TODO ホントはテーブルがあるかきちんと確認
		for (EntityType type : EntityType.values()) {
			query = "INSERT INTO " + type_table + " (TYPE) VALUES " + "('"
					+ type.toString() + "')";
			stmt.execute(query);
		}
		// Entity用テーブル
		query = "CREATE TABLE IF NOT EXISTS " + entity_table + "("
				+ "ID INT AUTO_INCREMENT PRIMARY KEY,"
				+ "NAME VARCHAR(256) NOT NULL," + "URI VARCHAR (256) NOT NULL,"
				+ "TYPE INT NOT NULL," + "USER VARCHAR(256)," + "LOCATION INT)";
		stmt.execute(query);

		// Location用テーブル
		query = "CREATE TABLE IF NOT EXISTS " + location_table + "("
				+ "ID INT AUTO_INCREMENT PRIMARY KEY,"
				+ "NAME VARCHAR(256) NOT NULL," + "LATITUDE VARCHAR (256),"
				+ "LONGITUDE VARCHAR (256))";
		stmt.execute(query);
		logger.info(" - Make database tables successfully.");
		return true;
	}

	/**
	 * 
	 * @param type
	 * @param data
	 * @param source
	 * @return
	 * @throws SQLException
	 */
	public int store(EntityType type, SensorData data, Entity source) throws SQLException {
		String query = "";
		int data_num = 0;
		Statement stmt = con.createStatement();
		if (data != null) {
			// とりあえずのREAL型（float）
			query = "INSERT INTO " + data_table
					+ " (TYPE, VALUE, SEQ, SRC_ADDR, SRC_PAN_ID, RSSI)"
					+ " VALUES (" + type.toString() + ", "
					+ data.getSensorValue() + ", "
					+ data.getSeqNumber() + ", " + data.getSrcAddr()
					+ ", " + data.getSrcPanId() + ", " + data.getRssi()
					+ ")";
			stmt.execute(query);

			// 挿入したデータのIDを取得
			query = "SELECT MAX(ID) FROM DATA" + data_table;
			data_num = getMaxID(data_table);
		}
		return data_num;
	}
	
	
	/**
	 * SQLクエリを自動的発行し、オートコミットしてデータを保存する。失敗するとfalseを返す。
	 * 
	 * @param data
	 * @return
	 * @throws SQLException
	 */
	public boolean store(DataObject data) throws SQLException {
		if (con.getAutoCommit())
			con.setAutoCommit(false);
		con.commit();

		try {
			// まずはセンサデータの保存
			SensorData sensor = data.getSensorData();
			String query = "";
			int data_num = 0;
			Statement stmt = con.createStatement();
			if (sensor != null) {
				data_num = sensor.getId();
			}
			if (types == null) {
				types = getDataTypeTable();
			}

			// SourceEntityの保存
			int source_num, target_num = 0;
			Entity source = data.getEventSource();
			source_num = updataEntity(source);
			int type = (Integer) types.get(source.getEntityType());
			int location = 0;
			// Locationの取得
			if (data.getLocation() != null) {
				location = prepareLocation(data.getLocation());
			}
			
			// TargetEntityの保存
			Entity target = data.getEventTarget();
			if (target != null) {
				target_num = updataEntity(target);
			}
			if (data_num != 0) {
				query = "INSERT INTO " + top_table
						+ " (TYPE, SENSOR_DATA, SOURCE, TARGET, LOCATION, TIME) VALUES ("
						+ type + ", " + data_num + ", " + source_num + ", "
						+ target_num + ", " + location + ", CURRENT_TIMESTAMP())";
			}
			
			// もしOTHER_DATAがあったら
			else if (data.getOtherData() != null) {
				query = "INSERT INTO " + top_table
						+ " (TYPE, OTHER_DATA, SOURCE, TARGET, LOCATION, TIME) VALUES ("
						+ type + ", '" + data.getOtherData() + "', "
						+ source_num + ", " + target_num + ", " + location + ", "
						+ "CURRENT_TIMESTAMP())";
			} else {
				throw new SQLException("There is no data.");
			}
			stmt.execute(query);

			con.commit();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			return false;
		}
		return true;
	}

	/**
	 * 
	 * @return
	 * @throws SQLException
	 */
	private Map getDataTypeTable() throws SQLException {
		Map<EntityType, Integer> map = new HashMap<EntityType, Integer>();
		String query = "SELECT * FROM TYPE";
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(query);
		while (rs.next()) {
			Integer num = rs.getInt(1);
			String type = rs.getString(2);
			map.put(EntityType.valueOf(type), num);
		}
		stmt.close();
		return map;
	}

	/**
	 * 
	 * @param table
	 * @return
	 * @throws SQLException
	 */
	private int getMaxID(String table) throws SQLException {
		String query = "SELECT MAX(ID) FROM " + table;
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(query);
		int data_num = 0;
		if (rs.next()) {
			data_num = rs.getInt(1);
		}
		stmt.close();
		return data_num;
	}

	/**
	 * Entityの位置情報のアップデート
	 * @param source
	 * @return
	 * @throws SQLException
	 */
	private int updataEntity(Entity source) throws SQLException {
		Statement stmt = con.createStatement();
		String user = "";
		int num;
		int type = (Integer) types.get(source.getEntityType());
		if (source.getURI().isSipURI()) {
			SipURI sipuri = (SipURI) source.getURI();
			user = sipuri.getUser();
		}
		// 同じNAMEがないかチェック、あったら更新
		String query = "SELECT ID FROM " + entity_table + " WHERE URI='"
				+ source.getURI().toString() + "'";
		ResultSet rs = stmt.executeQuery(query);
		if (rs.next()) {
			num = rs.getInt(1);
			// 位置情報の更新
			if (source.getLocation() != null) {
				int location_num = prepareLocation(source.getLocation());
				query = "UPDATE " + entity_table + " SET " + "URI='"
						+ source.getURI().toString() + "', TYPE=" + type
						+ ", USER='" + user + "', LOCATION='" + location_num
						+ "' WHERE ID=" + num;
			} else {
				query = "UPDATE " + entity_table + " SET " + "URI='"
						+ source.getURI().toString() + "', TYPE=" + type
						+ ", USER='" + user + "'  WHERE ID=" + num;
			}
			stmt.execute(query);
		} else {
			query = "INSERT INTO " + entity_table
					+ " (NAME, URI, TYPE, USER) VALUES ('"
					+ source.getFullyQualifiedName() + "', '"
					+ source.getURI().toString() + "', " + type + ", '" + user
					+ "')";
			stmt.execute(query);
			num = getMaxID(entity_table);
		}
		stmt.close();
		return num;
	}

	/**
	 * 位置情報の追加、更新
	 * 
	 * @param location
	 * @return
	 * @throws SQLException
	 */
	private int prepareLocation(Location location)
			throws SQLException {
		Statement stmt = con.createStatement();
		String query = "SELECT * FROM " + location_table + " WHERE NAME = '"
				+ location.getName() + "'";
		ResultSet rs = stmt.executeQuery(query);
		int num = 0;
		if (rs.next()) {
			// 更新
			num = rs.getInt(1);
			query = "UPDATE " + location_table + " SET LATITUDE='"
					+ location.getLatitude() + "', LONGITUDE='"
					+ location.getLongitude() + "' WHERE ID=" + num;
			stmt.execute(query);
		} else {
			// 追加
			query = "INSERT INTO " + location_table
					+ "(NAME, LATITUDE, LONGITUDE) VALUES ('"
					+ location.getName() + "', '" + location.getLatitude()
					+ "', '" + location.getLongitude() + "')";
			stmt.execute(query);
			query = "SELECT * FROM " + location_table + " WHERE NAME = '"
					+ location.getName() + "'";
			rs = stmt.executeQuery(query);
			if (rs.next()) {
				num = rs.getInt(1);
			}
		}
		return num;
	}

	public static int MAX_COLUMN_REQUEST = 100;

	/**
	 * Retireve specified data of selected uri
	 * 
	 * @param uri
	 * @param num
	 * @return
	 * @throws SQLException
	 */
	public EventData[] getData(String uri, int num) throws SQLException {
		String query = "SELECT * FROM ENTITY WHERE URI = \'" + uri + "\'";
		int id = 0, count = 0, location_num =0;
		String name = "";
		Location location = null;
		
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(query);
		
		while (rs.next()) {
			id = rs.getInt(1);
			name = rs.getString(2);
		}
		
		query = "SELECT COUNT(*) from " +top_table +" WHERE SOURCE =\'" + id + "\'";
		rs = stmt.executeQuery(query);
		while (rs.next()) {
			count = rs.getInt(1);
		}
		count = (count > MAX_COLUMN_REQUEST) ? MAX_COLUMN_REQUEST : count;
		count = (count > num) ? num : count;
		EventData[] ret = new EventData[count];
		query = "SELECT * from " +top_table+" WHERE SOURCE =\'" + id + "\' ORDER BY id DESC";
		rs = stmt.executeQuery(query);
		int temp = 0, prev = 0;
		while (rs.next()) {
			// Sensor
			String sensor = rs.getString(3);
			location_num = rs.getInt(7);
			if (location_num != 0 && (prev != location_num)) {
				location = getLocation(location_num);
				prev = location_num;
			}
			Timestamp time = rs.getTimestamp(8);
			EventData data = new EventData();
			data.name = name;
			data.date = time.toString();
			if (sensor == null) {
				sensor = rs.getString(4);
			}
			data.value = sensor;
			
			if (location != null) {
				data.location = location;
			}
			ret[temp++] = data;
			if (temp == count) {
				break;
			}
		}
		return ret;
	}
	
	/**
	 * 
	 * @param num
	 * @return
	 * @throws SQLException
	 */
	private Location getLocation (int num) throws SQLException {
		Statement stmt = con.createStatement();
		String query = "SELECT * FROM LOCATION WHERE ID=" + num;
		ResultSet rs2 = stmt.executeQuery(query);
		Location location = null;
		if (rs2.next()) {
			String location_name = rs2.getString(2);
			String latitude = rs2.getString(3);
			String longitude = rs2.getString(4);
			location = new Location(location_name, latitude, longitude);
		}
		return location;
	}


}
