package com.javahonk.dao;

import java.sql.Types;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.javahonk.di.bean.Person;

public class PersonDAO implements IPersonDAO {

    private JdbcTemplate jdbcTemplate;
    private PlatformTransactionManager platformTransactionManager;

    @Override
    public int insertUser(Person person) throws Exception {
	//Create TransactionDefinition
	DefaultTransactionDefinition defaultTransactionDefinition = 
		new DefaultTransactionDefinition();
	//Set name of your transaction
	defaultTransactionDefinition.setName("deletePersonTransaction");
	//Set transaction properties. Here we are setting 
	//PROPAGATION_REQUIRED - (Support current transaction. Ceate 
	//new one if not exists. This is analogous to EJB transaction
	//attribute with the same name. This one is default setting of
	//transaction definition and typically defines transaction
	//synchronization scope.
	defaultTransactionDefinition.setPropagationBehavior(
		TransactionDefinition.PROPAGATION_REQUIRED);
	//Retrieve transaction status information and to programmatically 
	//request roll-back
	TransactionStatus status = platformTransactionManager.
		getTransaction(defaultTransactionDefinition);
	int value;
	try {
	    String inserQuery = "INSERT INTO person(First_Name, "
		    + "Last_Name, Street_Name, City, State, Country) "
		    + "VALUES(?, ?, ?, ?, ?, ?)";
	    Object[] params = new Object[] { person.getFirstName(),
		    person.getLastName(),
		    person.getStreet(), person.getCity(), 
		    person.getState(), person.getCountry() };
	    int[] types = new int[] { Types.VARCHAR, Types.VARCHAR,
		    Types.VARCHAR, Types.VARCHAR,
		    Types.VARCHAR, Types.VARCHAR };
	    value = jdbcTemplate.update(inserQuery, params, types);
	    //commit transaction
	    platformTransactionManager.commit(status);
	    System.out.println("\nPerson inserted to the table");
	    
	} catch (Exception e) {
	    e.printStackTrace();
	    platformTransactionManager.rollback(status);
	    throw e;
	}
	return value;
    }

    @Override
    public void deletePerson(int personID) throws Exception {
	
	//Create TransactionDefinition
	DefaultTransactionDefinition defaultTransactionDefinition = 
		new DefaultTransactionDefinition();
	//Set name of your transaction
	defaultTransactionDefinition.setName("deletePersonTransaction");
	//Set transaction properties. Here we are setting 
	//PROPAGATION_REQUIRED - (Support current transaction. Ceate 
	//new one if not exists. This is analogous to EJB transaction
	//attribute with the same name. This one is default setting of
	//transaction definition and typically defines transaction
	//synchronization scope.
	defaultTransactionDefinition.setPropagationBehavior(
		TransactionDefinition.PROPAGATION_REQUIRED);
	//Retrieve transaction status information and to programmatically 
	//request roll-back
	TransactionStatus status = platformTransactionManager.
		getTransaction(defaultTransactionDefinition);
	
	try {
	    String deletePerson = "DELETE FROM person WHERE id =?";
	    Object[] params = new Object[] { personID };
	    int[] types = new int[] { Types.VARCHAR };
	    jdbcTemplate.update(deletePerson, params, types);
	    //commit transaction
	    platformTransactionManager.commit(status);
	    System.out.println("\nPerson with id 1 deleted from " + "the table\n");
	    
	} catch (Exception e) {
	    e.printStackTrace();
	    platformTransactionManager.rollback(status);
	    throw e;	   
	}

    }

    @Override
    //Important: For select statement we don't need any transaction
    //because we are not changing anything into the database
    //only we are setting transaction definition
    public void selectAllPerson() throws Exception {
	
	//Create TransactionDefinition
    	DefaultTransactionDefinition defaultTransactionDefinition = 
    		new DefaultTransactionDefinition();
    	//Set name of your transaction
    	defaultTransactionDefinition.setName("selectAllPersonTransaction");
    	//Set transaction properties. Here we are setting 
    	//ISOLATION_REPEATABLE_READ - It means dirty reads and 
    	//non-repeatable reads will be prevented but phantom reads
    	//can occur. It prohibits transaction from reading the row 
    	//with uncommitted changes and also prohibits situation where 
    	//one transaction reads row and second transaction alters row
    	//and first transaction rereads same row getting different values 
    	//second time ("non-repeatable read").
    	defaultTransactionDefinition.setPropagationBehavior(
    		TransactionDefinition.ISOLATION_REPEATABLE_READ);    	
	try {
	    System.out.println("\nList of person in the table\n");
	    String selectAllPerson = "SELECT * FROM person";
	    List<Map<String, Object>> listOfPerson = jdbcTemplate.
		    queryForList(selectAllPerson);
	    for (Iterator<Map<String, Object>> iterator = listOfPerson.
		    iterator(); iterator.hasNext();) {
		Map<String, Object> map = (Map<String, Object>) 
			iterator.next();
		System.out.println(map);		
	    }
	    System.out.println();
	    
	} catch (Exception e) {
	    e.printStackTrace();
	    throw e; 
	}

    }
    
    @Override 
    //Important: For select statement we don't need any transaction
    //because we are not changing anything into the database
    //only we are setting transaction definition
    public void selectPersonByName() throws Exception {
	//Create TransactionDefinition
    	DefaultTransactionDefinition defaultTransactionDefinition = 
    		new DefaultTransactionDefinition();
    	//Set name of your transaction
    	defaultTransactionDefinition.setName("deletePersonTransaction");
    	//Set transaction properties. Here we are setting 
    	//ISOLATION_REPEATABLE_READ - It means dirty reads and 
    	//non-repeatable reads will be prevented but phantom reads
    	//can occur. It prohibits transaction from reading the row 
    	//with uncommitted changes and also prohibits situation where 
    	//one transaction reads row and second transaction alters row
    	//and first transaction rereads same row getting different values 
    	//second time ("non-repeatable read").
    	defaultTransactionDefinition.setPropagationBehavior(
    		TransactionDefinition.ISOLATION_REPEATABLE_READ);
	
    	try {
	    System.out.println("\nList of person name Java "
		    	+ "in the table\n");
	    String selectAllPerson = "SELECT * FROM person where "
	    	+ "First_Name ='Java'";
	    List<Map<String, Object>> listOfPerson = jdbcTemplate.
		    queryForList(selectAllPerson);
	    for (Iterator<Map<String, Object>> iterator = listOfPerson.
		    iterator(); iterator.hasNext();) {
		Map<String, Object> map = (Map<String, Object>) 
			iterator.next();
		System.out.println(map);
	    }
	    System.out.println();
	    
	} catch (Exception e) {
	    e.printStackTrace();
	    throw e; 
	}
	
    }

    
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
	this.jdbcTemplate = jdbcTemplate;
    }

    public void setPlatformTransactionManager(PlatformTransactionManager 
	    platformTransactionManager) {
        this.platformTransactionManager = platformTransactionManager;
    }
    

}
