package com.adobe.services;

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

import com.adobe.objects.SimpleCustomer;
import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;

public class SimpleCustomerService 
{
	
	public SimpleCustomerService()
	{
	}
	
	public int addCustomer(SimpleCustomer customer)
	{
		int createdCustomerId= 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_MODIFIED_DATE ) VALUES(?,?,?,?)");
			
			statement.setString(1, customerName);
			statement.setString(2, customerAddress);
			statement.setString(3, customerType);
			statement.setTimestamp(4, new java.sql.Timestamp(new java.util.Date().getTime()));			
			
			int rowCount = statement.executeUpdate();

			if(rowCount == 1)
			{
				ResultSet rs = statement.getGeneratedKeys();
				if(rs.next())
				{
					createdCustomerId = rs.getInt(1);
				}
			}
		}
		catch(MySQLIntegrityConstraintViolationException multipleEntryException)
		{
			//duplicate entry found
			throw new RuntimeException("Customer entry already exists");
		}
		catch(Exception e)
		{
			throw new RuntimeException(e);
		}		
		finally
		{
			try
			{
				if(connection != null)
				{
					connection.close();	
				}
			}catch(SQLException sqe)
			{
				throw new RuntimeException(sqe);
			}			
		}		
		return createdCustomerId;
	}
	
	public void updateCustomer(SimpleCustomer customer)
	{
		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");
		}
		
		//update customer address
		
		try
		{
			
			connection = getConnection();
			PreparedStatement statement = 
				connection.prepareStatement("UPDATE CUSTOMERS SET CUSTOMER_NAME = ?," +
						"CUSTOMER_ADDRESS = ?, CUSTOMER_TYPE = ?," +
						"ENTRY_MODIFIED_DATE = ?" +
						"WHERE CUSTOMER_ID = ?");
			
			statement.setString(1, customerName);
			statement.setString(2, customerAddress);
			statement.setString(3, customerType);
			statement.setTimestamp(4, new java.sql.Timestamp(new java.util.Date().getTime()));
			statement.setInt(5, customerId);
			
			statement.executeUpdate();

		}
		catch(MySQLIntegrityConstraintViolationException multipleEntryException)
		{
			//duplicate entry found
			throw new RuntimeException("Customer entry already exists");
		}
		catch(Exception e)
		{
			throw new RuntimeException(e);
		}		
		finally
		{
			try
			{
				if(connection != null)
				{
					connection.close();	
				}
			}catch(SQLException sqe)
			{
				throw new RuntimeException(sqe);
			}			
		}				
	}
	
	public boolean deleteCustomer(SimpleCustomer customer)
	{
		boolean result = false;
		Connection connection = null;
		
		if(customer == null)
		{
			throw new IllegalArgumentException("Customer instance  is invalid");
		}
		
		int customerId = customer.getCustomerId();

		if(customerId <= 0)
		{
			throw new IllegalArgumentException("Customer id is invalid");
		}

		try
		{
			connection = getConnection();
			PreparedStatement customerStatement;

			customerStatement = connection.prepareStatement("DELETE FROM CUSTOMERS WHERE CUSTOMER_ID = ?");
			customerStatement.setInt(1, customerId);
			int rowCount = customerStatement.executeUpdate();
			if(rowCount == 1)
			{
				result = true;
			}	
		}
		catch(Exception e)
		{
			throw new RuntimeException(e);
		}		
		finally
		{
			try
			{
				if(connection != null)
				{
					connection.close();	
				}
			}catch(SQLException sqe)
			{
				throw new RuntimeException(sqe);
			}			
		}		
		return result;
	}
	
	public SimpleCustomer getCustomer(int customerId)
	{
		SimpleCustomer result = null;
		Connection connection = null;

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

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

	public ArrayList<SimpleCustomer> getCustomersPaged(int startIndex, int numberOfRows)
	{
		ArrayList<SimpleCustomer> customers = new ArrayList<SimpleCustomer>();
		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 RuntimeException(e);
		}		
		finally
		{
			try
			{
				if(connection != null)
				{
					connection.close();	
				}
			}catch(SQLException sqe)
			{
				throw new RuntimeException(sqe);
			}			
		}				
		return customers;
	}

	public ArrayList<SimpleCustomer> getAllCustomers()
	{
		ArrayList<SimpleCustomer> customers = new ArrayList<SimpleCustomer>();
		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 RuntimeException(e);
		}		
		finally
		{
			try
			{
				if(connection != null)
				{
					connection.close();	
				}
			}catch(SQLException sqe)
			{
				throw new RuntimeException(sqe);
			}			
		}				
		return customers;
	}

	private ArrayList<SimpleCustomer> createCustomersFromRS(ResultSet rs)throws Exception
	{
		ArrayList<SimpleCustomer> customers = new ArrayList<SimpleCustomer>();
		SimpleCustomer customer;
		while(rs.next())
		{
			customer = new SimpleCustomer();
			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.setEntryModifiedDate(rs.getTimestamp("ENTRY_MODIFIED_DATE"));
			
			customers.add(customer);
		}		
		return customers;
	}
	
	
	private Connection getConnection()throws Exception
	{
		Connection connection = null;
	     try 
	        {
	            Class.forName("org.gjt.mm.mysql.Driver").newInstance();
	        }
	        catch (Exception e) 
	        {
	        	throw e;
	        }

	        try 
	        {
	            connection = DriverManager.getConnection(
	              "jdbc:mysql://localhost/" + "DatabaseName", "UserName","Password");
	        }
	        catch (SQLException sqlException) 
	        {
	        	throw sqlException;
	        }		
		return connection;		
	}
	
}