package com.javahonk.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

import com.javahonk.domain.Person;

public class PersonDAOImpl implements IPersonDAO {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public int insertPerson(Person person) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int updatePerson(Person person) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public void deletePerson(int personID) {
		
		SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)

		.withProcedureName("SelectMultiResultSet");

		Map<String, Object> inParamMap = new HashMap<String, Object>();
		SqlParameterSource in = new MapSqlParameterSource(inParamMap);

		Map<String, Object> simpleJdbcCallResult = simpleJdbcCall.execute(in);
		System.out.println(simpleJdbcCallResult);
		
	}

	@Override
	public List<Map<String, Object>> selectAllPerson() {
		String sql = "SELECT * FROM person";
		List<Map<String, Object>> listOfPerson = jdbcTemplate.queryForList(sql);
		return listOfPerson;
	}

	@Override
	public Map<String, Object> returnMultipleResultSet() {
		
		// TO use SimpleJdbcCall please un-comment below
		//return useSimpleJdbcCall();
		// TO use CallableStatement please un-comment below
		//useCallableStatement();
		
		// TO use CallableStatementCreator below is the code
		SqlParameter departId = new SqlParameter(Types.VARCHAR);
		
		List<SqlParameter> paramList = new ArrayList<SqlParameter>();
		paramList.add(departId);	

		final String procedureCall = "{call SelectMultiResultSet(?)}";
		Map<String, Object> resultMap = jdbcTemplate.call(new CallableStatementCreator() {

					@Override
					public CallableStatement createCallableStatement(Connection connection) throws SQLException {

						CallableStatement callableStatement = connection.prepareCall(procedureCall);
						callableStatement.setString(1, "31");
						return callableStatement;

					}
				}, paramList);
		System.out.println(resultMap);
		return resultMap;
	}

	private void useCallableStatement() {
		final String procedureCall = "{call SelectMultiResultSet(?)}";
		Connection connection = null;
		Map<String, Object> result = new HashMap<String, Object>();
		try {

			// Get Connection from dataSource
			connection = jdbcTemplate.getDataSource().getConnection();
			CallableStatement callableSt = connection.prepareCall(procedureCall);
			callableSt.setString("departId", "31");
			
			// Call Stored Procedure
			boolean results = callableSt.execute();
			
			while (results) {
				ResultSet rs = callableSt.getResultSet();
				 ResultSetMetaData rsmd = rs.getMetaData();
				 String tableName = "";
				// Display the column name and type.
			      int cols = rsmd.getColumnCount();
			      for (int i = 1; i <= cols; i++) {
			         System.out.println("NAME: " + rsmd.getColumnName(i) + " " + "TYPE: " + rsmd.getColumnTypeName(i));	
			         tableName = rsmd.getTableName(i);
			         
			      }
			      while (rs.next()) {
					if (tableName.equals("person")) {
						System.out.println("Table name is: "+tableName);
					} else if (tableName.equals("employee")) {
						System.out.println("Table name is: "+tableName);
					}else {
						System.out.println("Table name is: "+tableName);
					}
					
			      }
			     rs.close();				

		        //Check for next result set
		        results = callableSt.getMoreResults();
			}			

		} catch (SQLException e) {

			e.printStackTrace();

		} finally {

			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}

	private Map<String, Object> useSimpleJdbcCall() {
		
		SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate).withProcedureName("SelectMultiResultSet");

		Map<String, Object> inParamMap = new HashMap<String, Object>();
		inParamMap.put("departId", "31");
		SqlParameterSource in = new MapSqlParameterSource(inParamMap);

		Map<String, Object> simpleJdbcCallResult = simpleJdbcCall.execute(in);
		
		
		 Iterator<Entry<String, Object>> it = simpleJdbcCallResult.entrySet().iterator();
		    while (it.hasNext()) {
		        Map.Entry<String, Object> entry = (Map.Entry<String, Object>) it.next();
		        String key = (String) entry.getKey();
		        Object value = (Object) entry.getValue();
		        System.out.println("Key: "+key);
		        System.out.println("Value: "+value);
		    }
		return simpleJdbcCallResult;
	}


}
