package com.adobe.dao;

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

import com.adobe.dataobjects.Customer;
import com.adobe.dataobjects.User;
import com.adobe.expcetions.UnCheckedException;
import com.adobe.utils.ApplicationData;
import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;

public class ManagedCustomerDAO {

	private ConnectionHandler connectionHandler;
	public ManagedCustomerDAO()
	{
		connectionHandler = ConnectionHandlerFactory.getConnectionHandler();
	}
	
	public int addCustomer(Customer customer)
	{
		int result = 0;
		Connection connection = null;

		if(customer == null)
		{
			throw new IllegalArgumentException("Customer object is invalid");
		}
		String customerName = customer.getCustomerName();
		String customerAddress = customer.getCustomerAddress();
		String customerType = customer.getCustomerType();
		
		if(customerName == null ||
				customerAddress == null ||
				customerType == null)
		{
			throw new IllegalArgumentException("Customer object is invalid");
		}
		try
		{
			connection = getConnection();
			PreparedStatement statement = 
				connection.prepareStatement("INSERT INTO CUSTOMERS (" +
						"CUSTOMER_NAME, CUSTOMER_ADDRESS, CUSTOMER_TYPE, " +
						"ENTRY_CREATED_USER, ENTRY_EDITED_USER, " +
						"ENTRY_MODIFIED_DATE ) VALUES(?,?,?,?,?,?)");
			
			statement.setString(1, customerName);
			statement.setString(2, customerAddress);
			statement.setString(3, customerType);
			statement.setInt(4, 2);
			statement.setInt(5, 2);
			statement.setTimestamp(6, new java.sql.Timestamp(new java.util.Date().getTime()));

			statement.executeUpdate();
			ResultSet rs = statement.getGeneratedKeys();

			if(rs != null && rs.next())
			{
				result = rs.getInt(1);
			}
		}
		catch(MySQLIntegrityConstraintViolationException multipleEntryException)
		{
			//duplicate entry found
			result = 0;
			throw new UnCheckedException("Customer entry already exists");
		}
		catch(Exception e)
		{
			throw new UnCheckedException(e);
		}		
		finally
		{
			try
			{
				if(connection != null)
				{
					connection.close();	
				}
			}catch(SQLException sqe)
			{
				throw new UnCheckedException(sqe);
			}			
		}		
		return result;
	}
	
	public void updateCustomer(Customer customer)
	{
		//boolean result = false;
		Connection connection = null;

		if(customer == null)
		{
			throw new IllegalArgumentException("Customer object is invalid");
		}
		String customerName = customer.getCustomerName();
		String customerAddress = customer.getCustomerAddress();
		String customerType = customer.getCustomerType();
		int customerId = customer.getCustomerId();
		
		if(customerName == null ||
				customerAddress == null ||
				customerId <= 0 ||
				customerType == null)
		{
			throw new IllegalArgumentException("Customer object is invalid");
		}
		try
		{
			connection = getConnection();
			PreparedStatement statement = 
				connection.prepareStatement("UPDATE CUSTOMERS SET CUSTOMER_NAME = ?," +
						"CUSTOMER_ADDRESS = ?, CUSTOMER_TYPE = ?," +
						"ENTRY_EDITED_USER = ?, ENTRY_MODIFIED_DATE = ?" +
						"WHERE CUSTOMER_ID = ?");
			
			statement.setString(1, customerName);
			statement.setString(2, customerAddress);
			statement.setString(3, customerType);
			statement.setInt(4, 2);
			statement.setTimestamp(5, new java.sql.Timestamp(new java.util.Date().getTime()));
			statement.setInt(6, customerId);
			int rowCount = statement.executeUpdate();
			if(rowCount == 1)
			{
				//result = true;
			}
		}
		catch(MySQLIntegrityConstraintViolationException multipleEntryException)
		{
			//duplicate entry found
			//result = false;
			throw new UnCheckedException("Customer entry already exists");
		}
		catch(Exception e)
		{
			throw new UnCheckedException(e);
		}		
		finally
		{
			try
			{
				if(connection != null)
				{
					connection.close();	
				}
			}catch(SQLException sqe)
			{
				throw new UnCheckedException(sqe);
			}			
		}				
		//return result;
	}
	
	public void deleteCustomer(int customerId)
	{
//		boolean result = false;
		Connection connection = null;
		
		if(customerId <= 0)
		{
			throw new IllegalArgumentException("Customer id is invalid");
		}
		try
		{
			connection = getConnection();
			PreparedStatement statement;
			PreparedStatement deptStatement;
			//delete all the departments and its mappings belonging to
			//this customer
			statement = connection.prepareStatement("SELECT DEPARTMENT_ID FROM " +
					"CUSTOMER_DEPARTMENTS WHERE CUSTOMER_ID = ?");
			statement.setInt(1, customerId);
			ResultSet departmentsRS = statement.executeQuery();
			int departmentId = 0;
			while(departmentsRS.next())
			{
				departmentId = departmentsRS.getInt("DEPARTMENT_ID");
				deptStatement = connection.prepareStatement("DELETE FROM REQUESTS_DEPARTMENTS " +
						"WHERE DEPARTMENT_ID = ?");
				deptStatement.setInt(1, departmentId);
				deptStatement.executeUpdate();
				
				deptStatement = connection.prepareStatement("DELETE FROM DEPARTMENT_CONTACTS WHERE DEPARTMENT_ID = ?");
				deptStatement.setInt(1, departmentId);
				deptStatement.executeUpdate();
			}
			
			//delete the department
			statement = connection.prepareStatement("DELETE FROM CUSTOMER_DEPARTMENTS " +
					"WHERE CUSTOMER_ID = ?");
			statement.setInt(1, customerId);
			statement.executeUpdate();
			
			//delete the customer
			statement = connection.prepareStatement("DELETE FROM CUSTOMERS WHERE CUSTOMER_ID = ?");
			statement.setInt(1, customerId);
			int rowCount = statement.executeUpdate();
			if(rowCount == 1)
			{
//				result = true;
			}
		}
		catch(Exception e)
		{
			throw new UnCheckedException(e);
		}		
		finally
		{
			try
			{
				if(connection != null)
				{
					connection.close();	
				}
			}catch(SQLException sqe)
			{
				throw new UnCheckedException(sqe);
			}			
		}		
//		return result;
	}
	
	public Customer getCustomer(int customerId)
	{
		Customer result = null;
		Connection connection = null;

		try
		{
			connection = getConnection();
			PreparedStatement statement = 
				connection.prepareStatement("SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID = ?");
			statement.setInt(1, customerId);
			ResultSet rs = statement.executeQuery();
			ArrayList<Customer> customers = createCustomersFromRS(rs);
			if(customers != null && customers.size() > 0 )
			{
				result = customers.get(0);
			}
		}
		catch(Exception e)
		{
			throw new UnCheckedException(e);
		}		
		finally
		{
			try
			{
				if(connection != null)
				{
					connection.close();	
				}
			}catch(SQLException sqe)
			{
				throw new UnCheckedException(sqe);
			}			
		}				

		return result;
	}
	
	public int getCustomersCount()
	{
		int result = 0;
		ArrayList<Customer> customers = getAllCustomers();
		if(customers != null)
		{
			result = customers.size();
		}
		return result;
	}

	public ArrayList<Customer> getCustomersPaged(int startIndex, int numberOfRows)
	{
		ArrayList<Customer> customers = new ArrayList<Customer>();
		Connection connection = null;

		try
		{
			connection = getConnection();
			PreparedStatement statement = 
				connection.prepareStatement("SELECT * FROM CUSTOMERS LIMIT ?, ?");
			statement.setInt(1, startIndex);
			statement.setInt(2, numberOfRows);
			ResultSet rs = statement.executeQuery();
			customers = createCustomersFromRS(rs);
		}
		catch(Exception e)
		{
			throw new UnCheckedException(e);
		}		
		finally
		{
			try
			{
				if(connection != null)
				{
					connection.close();	
				}
			}catch(SQLException sqe)
			{
				throw new UnCheckedException(sqe);
			}			
		}				
		return customers;
	}

	public ArrayList<Customer> getAllCustomers()
	{
		ArrayList<Customer> customers = new ArrayList<Customer>();
		Connection connection = null;

		try
		{
			connection = getConnection();
			PreparedStatement statement = 
				connection.prepareStatement("SELECT * FROM CUSTOMERS");
			
			ResultSet rs = statement.executeQuery();
			customers = createCustomersFromRS(rs);
		}
		catch(Exception e)
		{
			throw new UnCheckedException(e);
		}		
		finally
		{
			try
			{
				if(connection != null)
				{
					connection.close();	
				}
			}catch(SQLException sqe)
			{
				throw new UnCheckedException(sqe);
			}			
		}				
		return customers;
	}

	private ArrayList<Customer> createCustomersFromRS(ResultSet rs)throws Exception
	{
		ArrayList<Customer> customers = new ArrayList<Customer>();
		UserDAO userDao = new UserDAO();
		User user = null;
		Customer customer;
		while(rs.next())
		{
			customer = new Customer();
			customer.setCustomerId(rs.getInt("CUSTOMER_ID"));
			customer.setCustomerName(rs.getString("CUSTOMER_NAME"));
			customer.setCustomerType(rs.getString("CUSTOMER_TYPE"));
			customer.setCustomerAddress(rs.getString("CUSTOMER_ADDRESS"));
			customer.setEntryCreatedUser(rs.getInt("ENTRY_CREATED_USER"));
			customer.setEntryEditedUser(rs.getInt("ENTRY_EDITED_USER"));
			customer.setEntryModifiedDate(rs.getTimestamp("ENTRY_MODIFIED_DATE"));
			customer.setDepartmentNames(getDepartmentNamesOfCustomer(rs.getInt("CUSTOMER_ID")));
			user = userDao.getUserFromID(rs.getInt("ENTRY_CREATED_USER"));
			if(user != null)
			{
				customer.setEntryCreatedUserName(user.getUserName());				
			}
			user = null;
			user = userDao.getUserFromID(rs.getInt("ENTRY_EDITED_USER"));
			if(user != null)
			{
				customer.setEntryEditedUserName(user.getUserName());	
			}			
			user = null;
			customers.add(customer);
		}		
		return customers;
	}
	
	private ArrayList<String> getDepartmentNamesOfCustomer(int customerId)
	{
		ArrayList<String> departmentNames = new ArrayList<String>();
		Connection connection = null;
		try
		{
			connection = getConnection();
			PreparedStatement statement = 
				connection.prepareStatement("SELECT CD.DEPARTMENT_NAME FROM " +
						"CUSTOMER_DEPARTMENTS CD " +
						"WHERE CD.CUSTOMER_ID = ?");
			statement.setInt(1, customerId);
			ResultSet rs = statement.executeQuery();
			while(rs.next())
			{
				departmentNames.add(rs.getString("DEPARTMENT_NAME"));
			}
		}
		catch(Exception e)
		{
			throw new UnCheckedException(e);
		}		
		finally
		{
			try
			{
				if(connection != null)
				{
					connection.close();	
				}
			}catch(SQLException sqe)
			{
				throw new UnCheckedException(sqe);
			}			
		}						
		return departmentNames;
	}

	private Connection getConnection()throws Exception
	{
		Connection connection = connectionHandler.getConnection(
				ApplicationData.MYSQL_DB_NAME, 
				ApplicationData.MYSQL_DB_USER_NAME, 
				ApplicationData.MYSQL_DB_USER_PASSWORD);
		return connection;		
	}	
}