/**
 *
 */
package common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;

/**
 *
 * @author 無糖ブラック
 *
 */
public class DBAccessWrapper {

	private Environment m_env = null;
	private Connection m_conn = null;
	private Statement m_stmt = null;

	/**
	 *
	 * @param env
	 */
	public DBAccessWrapper(Environment env) {
		m_env = env;
	}

	/**
	 * DB接続
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public void connect() throws ClassNotFoundException, SQLException {
		if (m_conn != null) {
			//接続済み
			return;
		}

		String driver = m_env.getParam("driver");
		String url = m_env.getParam("url");
		String user = m_env.getParam("user");
		String pass = m_env.getParam("pass");

		Class.forName(driver);
		if (m_conn == null) {
			m_conn = DriverManager.getConnection(url, user, pass);
		}
		if (m_stmt == null) {
			m_stmt = m_conn.createStatement();
		}
	}

	/**
	 * DB切断
	 * @throws SQLException
	 */
	public void close() throws SQLException {
		if (m_stmt != null) {
			m_stmt.close();
			m_stmt = null;
		}
		if (m_conn != null) {
			m_conn.close();
			m_conn = null;
		}
	}

	/**
	 * トランザクション開始
	 * @throws SQLException
	 */
	public void startTransaction() throws SQLException {
		m_conn.setAutoCommit(false);
	}

	/**
	 * コミット
	 * @throws SQLException
	 */
	public void commit() throws SQLException {
		m_conn.commit();
	}

	/**
	 * ロールバック
	 * @throws SQLException
	 */
	public void rollback() throws SQLException {
		m_conn.rollback();
	}

	/**
	 *
	 * @param selectParams
	 * @param tableName
	 * @param condition
	 * @param conditionTypes
	 * @param conditionValues
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public ArrayList<ArrayList<String>> select(ArrayList<String> selectParams, String tableName, String condition, ArrayList<String> conditionTypes, ArrayList<Object> conditionValues) throws SQLException, ClassNotFoundException {
		ArrayList<String> sortParams = new ArrayList<String>();
		ArrayList<String> groupParams = new ArrayList<String>();
		return selectWithGroupByAndOrderBy(selectParams, tableName, condition, conditionTypes, conditionValues, groupParams, sortParams);
	}

	/**
	 *
	 * @param selectParams
	 * @param tableName
	 * @param condition
	 * @param conditionTypes
	 * @param conditionValues
	 * @param sortParams
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public ArrayList<ArrayList<String>> selectWithOrderBy(ArrayList<String> selectParams, String tableName, String condition, ArrayList<String> conditionTypes, ArrayList<Object> conditionValues, ArrayList<String> sortParams) throws SQLException, ClassNotFoundException {
		ArrayList<String> groupParams = new ArrayList<String>();
		return selectWithGroupByAndOrderBy(selectParams, tableName, condition, conditionTypes, conditionValues, groupParams, sortParams);
	}

	/**
	 *
	 * @param selectParams
	 * @param tableName
	 * @param condition
	 * @param conditionTypes
	 * @param conditionValues
	 * @param groupParams
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public ArrayList<ArrayList<String>> selectWithGroupBy(ArrayList<String> selectParams, String tableName, String condition, ArrayList<String> conditionTypes, ArrayList<Object> conditionValues, ArrayList<String> groupParams) throws SQLException, ClassNotFoundException {
		ArrayList<String> sortParams = new ArrayList<String>();
		return selectWithGroupByAndOrderBy(selectParams, tableName, condition, conditionTypes, conditionValues, groupParams, sortParams);
	}

	/**
	 *
	 * @param selectParams
	 * @param tableName
	 * @param condition
	 * @param conditionTypes
	 * @param conditionValues
	 * @param groupParams
	 * @param sortParams
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public ArrayList<ArrayList<String>> selectWithGroupByAndOrderBy(ArrayList<String> selectParams, String tableName, String condition, ArrayList<String> conditionTypes, ArrayList<Object> conditionValues, ArrayList<String> groupParams, ArrayList<String> sortParams) throws SQLException, ClassNotFoundException {

		//SQL構築
		String sql = buildSqlForSelect(selectParams, tableName, condition, groupParams, sortParams);

		//プリペアードステートメント生成
		PreparedStatement stmt = m_conn.prepareStatement(sql);

		//パラメータ設定
		setPreparedStatementParams(stmt, conditionTypes, conditionValues);

		//SQL実行
		ResultSet resultSet = stmt.executeQuery();

		//検索結果を配列に格納
		ArrayList<ArrayList<String>> resultArrayList = getResultSet(resultSet, selectParams);

		//解放処理
		if (resultSet != null) {
			resultSet.close();
		}
		if (stmt != null) {
			stmt.close();
		}

		return resultArrayList;
	}

	/**
	 *
	 * @param targetTableName
	 * @param insertParams
	 * @param insertTypes
	 * @param insertValues
	 * @param selectParams
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public ArrayList<ArrayList<String>> insert(String targetTableName, ArrayList<String> insertParams, ArrayList<String> insertTypes, ArrayList<Object> insertValues, ArrayList<String> selectParams) throws SQLException, ClassNotFoundException {

		//SQL構築
		String sql = buildSqlForInsert(insertParams, targetTableName, selectParams);

		//プリペアードステートメント生成
		PreparedStatement stmt = m_conn.prepareStatement(sql);

		//パラメータ設定
		setPreparedStatementParams(stmt, insertTypes, insertValues);

		//SQL実行
		ResultSet resultSet = stmt.executeQuery();

		//検索結果を配列に格納
		ArrayList<ArrayList<String>> resultArrayList = getResultSet(resultSet, selectParams);

		//解放処理
		if (resultSet != null) {
			resultSet.close();
		}
		if (stmt != null) {
			stmt.close();
		}

		return resultArrayList;
	}

	/**
	 *
	 * @param updateParams
	 * @param updateTypes
	 * @param updateValues
	 * @param targetTableName
	 * @param updateCondition
	 * @return
	 * @throws SQLException
	 */
	public int update(ArrayList<String> updateParams, ArrayList<String> updateTypes, ArrayList<Object> updateValues, String targetTableName, String updateCondition, ArrayList<String> conditionTypes, ArrayList<Object> conditionValues) throws SQLException {

		//SQL構築
		String sql = buildSqlForUpdate(updateParams, targetTableName, updateCondition);

		//プリペアードステートメント生成
		PreparedStatement stmt = m_conn.prepareStatement(sql);

		//パラメータ設定

		ArrayList<String> allTypes = new ArrayList<String>();
		ArrayList<Object> allValues = new ArrayList<Object>();
		allTypes.addAll(updateTypes);
		allTypes.addAll(conditionTypes);
		allValues.addAll(updateValues);
		allValues.addAll(conditionValues);

		setPreparedStatementParams(stmt, allTypes, allValues);

		//SQL実行
		int result = stmt.executeUpdate();

		//解放処理
		if (stmt != null) {
			stmt.close();
		}

		return result;
	}

	/**
	 *
	 * @param targetTableName
	 * @param deleteCondition
	 * @param conditionTypes
	 * @param conditionValues
	 * @return
	 * @throws SQLException
	 */
	public int delete(String targetTableName, String deleteCondition, ArrayList<String> conditionTypes, ArrayList<Object> conditionValues) throws SQLException {

		//SQL構築
		String sql = buildSqlForDelete(targetTableName, deleteCondition);

		//プリペアードステートメント生成
		PreparedStatement stmt = m_conn.prepareStatement(sql);

		//パラメータ設定
		setPreparedStatementParams(stmt, conditionTypes, conditionValues);

		//SQL実行
		int result = stmt.executeUpdate();

		//解放処理
		if (stmt != null) {
			stmt.close();
		}

		return result;
	}

	/**
	 *
	 * @param targetTableName
	 * @return
	 * @throws SQLException
	 */
	public int truncate(String targetTableName) throws SQLException {
		int result = 0;
		StringBuilder sql = new StringBuilder();
		sql.append("TRUNCATE TABLE ");
		sql.append(targetTableName);
		sql.append(" RESTART IDENTITY CASCADE");
		result = m_stmt.executeUpdate(sql.toString());
		return result;
	}

	/**
	 *
	 * @param selectParams
	 * @param targetTableName
	 * @param selectCondition
	 * @param groupParams
	 * @param sortParams
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	private String buildSqlForSelect(ArrayList<String> selectParams, String targetTableName, String selectCondition, ArrayList<String> groupParams, ArrayList<String> sortParams) throws SQLException, ClassNotFoundException {
		String delimiter = "";
		Iterator<String> itr;
		StringBuilder sql = new StringBuilder();

		//SELECT句
		sql.append("SELECT ");

		delimiter = "";
		itr = selectParams.iterator();
		while (itr.hasNext()) {
			sql.append(delimiter);
			sql.append(itr.next());
			delimiter = ", ";
		}

		//FROM句
		sql.append(" FROM ");
		sql.append(targetTableName);

		//WHERE句
		if ( !selectCondition.isEmpty() ) {
			sql.append(" WHERE ");
			sql.append(selectCondition);
		}

		//GROUP BY句
		if ( groupParams.size() > 0 ) {
			sql.append(" GROUP BY ");

			delimiter = "";
			itr = groupParams.iterator();
			while (itr.hasNext()) {
				sql.append(delimiter);
				sql.append(itr.next());
				delimiter = ", ";
			}
		}

		//ORDER BY句
		if ( sortParams.size() > 0 ) {
			sql.append(" ORDER BY ");

			delimiter = "";
			itr = sortParams.iterator();
			while (itr.hasNext()) {
				sql.append(delimiter);
				sql.append(itr.next());
				delimiter = ", ";
			}
		}

		return sql.toString().toUpperCase();
	}

	/**
	 *
	 * @param insertParams
	 * @param targetTableName
	 * @param selectParams
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	private String buildSqlForInsert(ArrayList<String> insertParams, String targetTableName, ArrayList<String> selectParams) throws SQLException, ClassNotFoundException {
		String delimiter = "";
		Iterator<String> itr;
		StringBuilder sql = new StringBuilder();
		StringBuilder sql1 = new StringBuilder();
		StringBuilder sql2 = new StringBuilder();

		//INSERT句
		sql1.append("INSERT INTO ");
		sql1.append(targetTableName);
		sql1.append("( ");

		//VALUES句
		sql2.append("VALUES ( ");

		delimiter = "";
		itr = insertParams.iterator();
		while (itr.hasNext()) {
			//INSERT句
			sql1.append(delimiter);
			sql1.append(itr.next());

			//VALUES句
			sql2.append(delimiter);
			sql2.append("?");
			delimiter = ", ";
		}
		sql1.append(" ) ");
		sql2.append(" )");

		sql.append(sql1.toString());
		sql.append(sql2.toString());

		//RETURNING句
		if ( selectParams.size() > 0 ) {
			sql.append(" RETURNING ");
			delimiter = "";
			itr = selectParams.iterator();
			while (itr.hasNext()) {
				sql.append(delimiter);
				sql.append(itr.next());
				delimiter = ", ";
			}
		}

		return sql.toString().toUpperCase();
	}

	/**
	 *
	 * @param updateParams
	 * @param targetTableName
	 * @param updateCondition
	 * @return
	 * @throws SQLException
	 */
	public String buildSqlForUpdate(ArrayList<String> updateParams, String targetTableName, String updateCondition) throws SQLException {

		StringBuilder sql = new StringBuilder();

		//UPDATE句
		sql.append("UPDATE ");
		sql.append(targetTableName);

		//SET句
		sql.append(" SET ");
		String delimiter = "";
		for (int ii = 0; ii < updateParams.size(); ++ii) {
			String param = updateParams.get(ii);
			sql.append(delimiter);
			sql.append(param);
			sql.append(" = ?");
			delimiter = ", ";
		}

		//WHERE句
		if ( !updateCondition.isEmpty() ) {
			sql.append(" WHERE ");
			sql.append(updateCondition);
		}

		return sql.toString().toUpperCase();
	}

	/**
	 *
	 * @param targetTableName
	 * @param deleteCondition
	 * @return
	 * @throws SQLException
	 */
	public String buildSqlForDelete(String targetTableName, String deleteCondition) throws SQLException {

		StringBuilder sql = new StringBuilder();

		//DELETE句
		sql.append("DELETE ");

		//FROM句
		sql.append("FROM ");
		sql.append(targetTableName);

		//WHERE句
		if ( !deleteCondition.isEmpty() ) {
			sql.append(" WHERE ");
			sql.append(deleteCondition);
		}

		return sql.toString().toUpperCase();
	}

	/**
	 *
	 * @param resultSet
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	private ArrayList<ArrayList<String>> getResultSet(ResultSet resultSet, ArrayList<String> params) throws SQLException  {

		ArrayList<ArrayList<String>> resultArrayList = new ArrayList<ArrayList<String>>();
		while (resultSet.next()) {
			ArrayList<String> result = new ArrayList<String>();
			for ( int ii = 0; ii < params.size(); ++ii ) {
				String param = params.get(ii);
				int pos = param.indexOf(" AS ");
				if ( pos >= 0) {
					param = param.substring(pos + 4, param.length());
				}
				String value = "";
				value = resultSet.getString(param);
				result.add(value);
			}
			resultArrayList.add(result);
		}

		return resultArrayList;
	}

	/**
	 *
	 * @param stmt
	 * @param types
	 * @param values
	 * @throws SQLException
	 */
	private void setPreparedStatementParams(PreparedStatement stmt, ArrayList<String> types, ArrayList<Object> values) throws SQLException {

		stmt.clearParameters();
		for (int ii = 0; ii < values.size(); ++ii) {

			String type = types.get(ii);
			Object objValue = values.get(ii);
			String strValue = objValue.toString();

			int index = ii+1;

			if (type == "string") {
				stmt.setString(index, strValue);
			}
			else if (type == "byte") {
				byte value = 0;
				if ( objValue != null && !strValue.isEmpty() ) {
					value = Byte.parseByte(strValue);
				}
				stmt.setByte(index, value);
			}
			else if (type == "short") {
				short value = 0;
				if ( objValue != null && !strValue.isEmpty() ) {
					value = Short.parseShort(strValue);
				}
				stmt.setShort(index, value);
			}
			else if (type == "int") {
				int value = 0;
				if ( objValue != null && !strValue.isEmpty() ) {
					value = Integer.parseInt(strValue);
				}
				stmt.setInt(index, value);
			}
			else if (type == "long") {
				long value = 0;
				if ( objValue != null && !strValue.isEmpty() ) {
					value = Long.parseLong(strValue);
				}
				stmt.setLong(index, value);
			}
			else if (type == "float") {
				float value = 0;
				if ( objValue != null && !strValue.isEmpty() ) {
					value = Float.parseFloat(strValue);
				}
				stmt.setFloat(index, value);
			}
			else if (type == "double") {
				double value = 0;
				if ( objValue != null && !strValue.isEmpty() ) {
					value = Double.parseDouble(strValue);
				}
				stmt.setDouble(index, value);
			}
			else if (type == "boolean") {
				boolean value = false;
				if ( objValue != null && !strValue.isEmpty() ) {
					value = Boolean.parseBoolean(strValue);
				}
				stmt.setBoolean(index, value);
			}
			else if (type == "date") {
				java.sql.Date value = null ;
				if ( objValue != null && !strValue.isEmpty() ) {
					value = java.sql.Date.valueOf(strValue);
				}
				stmt.setDate(index, value);
			}
			else if (type == "time") {
				java.sql.Time value = null ;
				if ( objValue != null && !strValue.isEmpty() ) {
					value = java.sql.Time.valueOf(strValue);
				}
				stmt.setTime(index, value);
			}
			else if (type == "timestamp") {
				java.sql.Timestamp value = null ;
				if ( objValue != null && !strValue.isEmpty() ) {
					value = java.sql.Timestamp.valueOf(strValue);
				}
				stmt.setTimestamp(index, value);
			}
		}
	}
}
