package com.ferienclub.custadmin;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.ferienclub.custadmin.beans.Address;
import com.ferienclub.custadmin.beans.Appointment;
import com.ferienclub.custadmin.beans.CustomerPreference;
import com.ferienclub.custadmin.beans.CustomerPreferences;
import com.ferienclub.custadmin.beans.HistoryEntry;
import com.ferienclub.custadmin.beans.Place;
import com.ferienclub.custadmin.beans.User;
import com.ferienclub.leisure.esb.messages.BookingQuery;
import com.ferienclub.leisure.esb.messages.PrefQuery;
import com.ferienclub.util.DriverManager;
import com.ferienclub.util.IDGenerator;

public class CustomerAdministrationImpl implements CustomerAdministration {

	static Logger log = Logger.getLogger(CustomerAdministrationImpl.class);
	
	/* ********************************************************************************* */
	/*                                  PRIVATE METHODS									 */
	/* ********************************************************************************* */
	
	
	/* ********************************************************************************* */
	/*                                  PUBLIC METHODS									 */
	/* ********************************************************************************* */
	
	public User[] getUsers(String dummy){
		log.info(this.getClass() + " : Entering method getUsers()");
		try {
			Connection con = com.ferienclub.util.DriverManager.getConnection();
			Statement stmt = con.createStatement();
			String select = "SELECT u.USERID, u.NAME, u.FIRSTNAME, u.PASSWORD, " +
							"a.ADDRESSID, a.COUNTRY, a.PROVINCE, a.ZIPCODE, a.STREET, a.EMAIL, a.PHONE " +
							"FROM PTT_USER u, PTT_ADDRESS a " +
							"WHERE u.ADDRESSID=a.ADDRESSID";
			log.debug("Statement = " + select);
			ResultSet rs = stmt.executeQuery(select);
			List users = new ArrayList();
			log.debug("Processing result of query");
			while (rs.next()){
				User u = new User();
				Address a = new Address();
				u.setUserId(rs.getString(1));
				u.setName(rs.getString(2));
				u.setFirstName(rs.getString(3));
				u.setPassword(rs.getString(4));
				a.setAddressId(rs.getString(5));
				a.setCountry(rs.getString(6));
				a.setProvince(rs.getString(7));
				a.setZipCode(rs.getString(8));
				a.setStreet(rs.getString(9));
				a.setEmail(rs.getString(10));
				a.setPhone(rs.getString(11));
				u.setAddress(a);
				users.add(u);
			}
			User[] returnValue = new User[users.size()];
			for (int i = 0; i < users.size(); i++){
				User u = (User)users.get(i);
				returnValue[i] = u;
			}
			rs.close();
			stmt.close();
			DriverManager.closeConnection(con);
			return returnValue;
		} catch (SQLException e){
			e.printStackTrace();
		}
		return null;
	}
	
	public int enterNewUser(User u){
		log.info(this.getClass() + " : Entering method enterNewUser()");
		try {
			Connection con = com.ferienclub.util.DriverManager.getConnection();
			PreparedStatement pstmt = null;
			String addressId = u.getAddress().getAddressId();
			
			// Save the address
    		if (addressId == null){
	    		String insertAddress = "INSERT INTO PTT_ADDRESS VALUES (?,?,?,?,?,?,?)";
	    		Address a = u.getAddress();
	    		addressId = IDGenerator.fetchNewId();
	    		pstmt = con.prepareStatement(insertAddress);
	    		pstmt.setString(1,addressId);
	    		pstmt.setString(2,a.getCountry());
	    		pstmt.setString(3,a.getProvince());
	    		pstmt.setString(4,a.getZipCode());
	    		pstmt.setString(5,a.getStreet());
	    		pstmt.setString(6,a.getEmail());
	    		pstmt.setString(7,a.getPhone());
	    		pstmt.execute();
	    		log.debug("Inserted new Address");
	    		pstmt.close();
    		}
    		
    		// Save the user
    		String insertUser = "INSERT INTO PTT_USER VALUES (?,?,?,?,?,?,?)";
    		pstmt = con.prepareStatement(insertUser);
    		String userId = IDGenerator.fetchNewId();
    		pstmt.setString(1,userId);
    		pstmt.setString(2,"");
    		Timestamp ts = new Timestamp(new Date().getTime());
    		pstmt.setTimestamp(3,ts);
    		pstmt.setString(4,u.getName());
    		pstmt.setString(5,u.getFirstName());
    		pstmt.setString(6,u.getPassword());
    		pstmt.setString(7,addressId);
    		
    		pstmt.execute();
    		log.info(this.getClass() + " : Inserted new Customer");
    		pstmt.close();
    		
    		DriverManager.closeConnection(con);
		} catch (SQLException e){
			e.printStackTrace();
			return -1;
		}
		return 1;
	}

	public int addPlace(Place p) {
		Connection con = DriverManager.getConnection();
		try {
			Statement stmt = con.createStatement();
			String sql = "INSERT INTO PTT_PLACE VALUES (newId(),'" + p.getName() + "'," + p.getLongitude() + "," + p.getLatitude() + ", " + p.getValidRange() + ")";
			log.info(this.getClass() + " : Executing sql: \"" + sql + "\"");
			stmt.execute(sql);
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
		DriverManager.closeConnection(con);
		return 1;
	}

	public int deletePlace(String placeId) {
		return 1;
	}

	public Place[] getPlaces(String dummy) {
		Connection con = DriverManager.getConnection();
		try {
			Statement stmt = con.createStatement();
			String sql = "SELECT PLACEID, NAME, LONGITUDE, LATITUDE, VALIDRANGE FROM PTT_PLACE";
			ResultSet rs = stmt.executeQuery(sql);
			List l = new ArrayList();
			while (rs.next())
			{
				Place p = new Place();
				p.setPlaceId(rs.getString(1));
				p.setName(rs.getString(2));
				p.setLongitude(rs.getFloat(3));
				p.setLatitude(rs.getFloat(4));
				p.setValidRange(rs.getFloat(5));
				l.add(p);
			}
			Place[] places = new Place[l.size()];
			for (int i = 0; i < places.length; i++)
			{
				places[i] = (Place)l.get(i);
			}
			rs.close();
			stmt.close();
			DriverManager.closeConnection(con);
			return places;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public int deleteAppointment(String appId) {
		Connection con = DriverManager.getConnection();
		try {
			Statement stmt = con.createStatement();
			String sql = "DELETE FROM PTT_APPOINTMENT_USER WHERE APPOINTMENTID='" + appId + "'";
			log.info(this.getClass() + " : Executing sql: " + sql);
			stmt.execute(sql);
			sql = "DELETE FROM PTT_APPOINTMENT WHERE APPOINTMENTID='" + appId + "'";
			log.info(this.getClass() + " : Executing sql: " + sql);
			stmt.execute(sql);
			stmt.close();
			DriverManager.closeConnection(con);
		} catch (SQLException e){
			e.printStackTrace();
			return -1;
		}
		return 1;
	}

	public int insertAppointment(Appointment app) {
		Connection con = DriverManager.getConnection();
		try {
			// First insert the appointment entry
			String sql = "INSERT INTO PTT_APPOINTMENT VALUES (?,?,?,?,?,?,?)";
			PreparedStatement pstmt = con.prepareStatement(sql);
			String newId = "";
			if (app.getAppointmentId() == null || app.getAppointmentId().equals(""))
				 newId = IDGenerator.fetchNewId();
			else newId = app.getAppointmentId();
				 
			pstmt.setString(1,newId);
			pstmt.setString(2,app.getSubject());
			pstmt.setTimestamp(3,new Timestamp(app.getDatetime().getTime()));
			pstmt.setInt(4,app.getDuration());
			pstmt.setString(5,app.getCategory());
			pstmt.setString(6,app.getPlace().getPlaceId());
			pstmt.setString(7,"OK");
			log.info(this.getClass() + " : Executing SQL " + pstmt.toString());
			pstmt.execute();
			
			// Now insert the references
			// First the leading user
			sql = "INSERT INTO PTT_APPOINTMENT_USER VALUES (?,?,?,?)";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1,app.getLeadingUser().getUserId());
			pstmt.setString(2,newId);
			pstmt.setString(3,"Y");
			pstmt.setString(4,"O");
			log.info(this.getClass() + " : Executing SQL " + pstmt.toString());
			pstmt.execute();
			
			// Next all the other users partaking in the appointment
			Statement stmt = null;
			for (Iterator itor = app.getUsers().iterator(); itor.hasNext();)
			{
				User u = (User)itor.next();
				String userId = u.getUserId();
				sql = "INSERT INTO PTT_APPOINTMENT_USER VALUES ('" + userId + "','" + newId + "','N','O')";
				stmt = con.createStatement();
				log.info(this.getClass() + " : Executing SQL " + sql);
				stmt.execute(sql);
			}
			pstmt.close();
			if (stmt != null) stmt.close();
			DriverManager.closeConnection(con);
		} catch (SQLException e){
			e.printStackTrace();
			return -1;
		}
		return 1;
	}
	
	public HistoryEntry[] getHistory(String dummy){
		HistoryEntry[] entries = null;
		Connection con = DriverManager.getConnection();
		try {
			Statement stmt = con.createStatement();
			String query = "SELECT h.ENTRYID, h.USERID, h.ENTRYTEXT, h.ENTRYDATE, t.ENTRYTYPEDESC FROM " +
					"PTT_HISTORY h, PTT_HISTORYENTRY_TYPE t " +
					"WHERE h.ENTRYTYPEID = t.ENTRYTYPEID";
			ResultSet rs = stmt.executeQuery(query);
			List temp = new ArrayList();
			while (rs.next()){
				HistoryEntry entry = new HistoryEntry();
				entry.setEntryId(rs.getString(1));
				entry.setUserId(rs.getString(2));
				entry.setEntryText(rs.getString(3));
				entry.setEntryDate(rs.getDate(4));
				entry.setEntryTypeDesc(rs.getString(5));
				temp.add(entry);
			}
			
			entries = new HistoryEntry[temp.size()];
			for (int i = 0; i < entries.length; i++){
				entries[i] = (HistoryEntry)temp.get(i);
			}
			rs.close();
			stmt.close();
			DriverManager.closeConnection(con);
			return entries;
		} catch (SQLException e){
			log.info(this.getClass() + " : SQLException in method addHistoryEntry");
			e.printStackTrace();
			return null;
		}
	}
	
	public int addHistoryEntry(HistoryEntry entry){
		Connection con = DriverManager.getConnection();
		try {
			// First we have to find out if there already exists a type for this entry
			String query = "SELECT ENTRYTYPEID FROM PTT_HISTORYENTRY_TYPE WHERE ENTRYTYPEDESC = '" + entry.getEntryTypeDesc() + "'";
			Statement stmt = con.createStatement();
			log.info(this.getClass() + " : Executing: " + query);
			ResultSet rs = stmt.executeQuery(query);
			String typeId = null;
			if (!rs.next()){ // No type detected! Create a new one
				typeId = IDGenerator.fetchNewId();
				String insert = "INSERT INTO PTT_HISTORYENTRY_TYPE VALUES ('" + typeId + "','" + entry.getEntryTypeDesc() + "')";
				log.info(this.getClass() + " : Executing: " + insert);
				stmt.execute(insert);
			} else {
				typeId = rs.getString(1);
			}
			// Now we can insert the new history-entry
			String insert = "INSERT INTO PTT_HISTORY VALUES (?,?,?,?,?)";
			PreparedStatement pstmt = con.prepareStatement(insert);
			pstmt.setString(1,IDGenerator.fetchNewId());
			pstmt.setString(2,entry.getUserId());
			pstmt.setString(3,typeId);
			pstmt.setString(4,entry.getEntryText());
			pstmt.setTimestamp(5, new Timestamp(entry.getEntryDate().getTime()));
			log.info(this.getClass() + " : Executing prepared Statement " + insert);
			pstmt.execute();
			rs.close();
			stmt.close();
			pstmt.close();
			DriverManager.closeConnection(con);
		} catch (SQLException e){
			log.info(this.getClass() + " : SQLException in method addHistoryEntry");
			e.printStackTrace();
			return -1;
		}
		return 1;
	}
	
	public Appointment[] getAppointments(String dummy) {
		// First cache all users and places
		User[] users = getUsers("");
		Place[] places = getPlaces("");
		Map userCache = new HashMap();
		Map placesCache = new HashMap();
		for (int i = 0; i < users.length; i++) userCache.put(users[i].getUserId(), users[i]);
		for (int i = 0; i < places.length; i++) placesCache.put(places[i].getPlaceId(), places[i]);
		// Everything is cached!
		// Now read out all entries for the appointments
		String sql = "SELECT APPOINTMENTID, SUBJECT, DATETIME, DURATION, CATEGORY, PLACEID FROM PTT_APPOINTMENT";
		Connection con = DriverManager.getConnection();
		try 
		{
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			List apps = new ArrayList();
			while (rs.next())
			{
				Appointment a = new Appointment();
				a.setAppointmentId(rs.getString(1));
				a.setSubject(rs.getString(2));
				a.setDatetime(rs.getDate(3));
				a.setDuration(rs.getInt(4));
				a.setCategory(rs.getString(5));
				a.setPlace((Place)placesCache.get(rs.getString(6)));
				apps.add(a);
			}
			
			// Now all Appointments are inside the ArrayList "apps" but without users
			// Get all users for a appointment and add them ...
			
			for (Iterator itor = apps.iterator(); itor.hasNext();)
			{
				Appointment a = (Appointment)itor.next();
				sql = "SELECT USERID, LEAD FROM PTT_APPOINTMENT_USER WHERE APPOINTMENTID='" + a.getAppointmentId() + "'";
				rs = stmt.executeQuery(sql);
				List participants = new ArrayList();
				while (rs.next())
				{
					String userId = rs.getString(1);
					String lead = rs.getString(2);
					if (lead.equals("Y")){
						// In case of the leading user
						a.setLeadingUser((User)userCache.get(userId));
						continue;
					}
					// In case of no leading user
					participants.add((User)userCache.get(userId));
				}
				a.setUsers(participants);
			}
			Appointment[] appointments = new Appointment[apps.size()];
			// Convert all the shit to an array and get away from here
			for (int i = 0; i < appointments.length; i++)
				appointments[i] = (Appointment)apps.get(i);
			rs.close();
			stmt.close();
			DriverManager.closeConnection(con);
			return appointments;
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
			return null;
		} 
	}

	public int setCustomerPreferences(CustomerPreferences pref) {
		log.info("Entering setCustomerPreferences");
		log.info("Deleting old preferences");
		deleteCustomerPreferences(pref.getUserId());
		Connection con = DriverManager.getConnection();
		try {
			String insert = "INSERT INTO PTT_USER_PREFERENCES VALUES (?,?,?)";
			PreparedStatement pstmt = con.prepareStatement(insert);
			for (Iterator itor = pref.iterator(); itor.hasNext();){
				CustomerPreference cp = (CustomerPreference)itor.next();
				pstmt.setString(1,pref.getUserId());
				pstmt.setString(2,cp.getName());
				pstmt.setInt(3,cp.getValue());
				log.info(this.getClass() + " : Executing: INSERT INTO PTT_USER_PREFERENCES VALUES ('" + pref.getUserId() + "'" +
						",'" + cp.getName() + "','" + cp.getValue() + " ')");
				pstmt.execute();
			}
			pstmt.close();
			DriverManager.closeConnection(con);
		} catch (SQLException e){
			log.info(this.getClass() + " : Error in method setCustomerPreferences.");
			e.printStackTrace();
			return -1;
		}
		return 1;
	}

	public CustomerPreferences[] getCustomerPreferences(PrefQuery dummy) {
		log.info(this.getClass() + " : Entering method getCustomerPreferences");
		Map cache = new HashMap();
		Connection con = DriverManager.getConnection();
		try {
			Statement stmt = con.createStatement();
			String query = "SELECT * FROM PTT_USER_PREFERENCES";
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()){
				String userId = rs.getString(1);
				String name = rs.getString(2);
				int value = rs.getInt(3);
				CustomerPreference p = new CustomerPreference(name, value);
				List l = (List)cache.get(userId);
				if (l != null) l.add(p);
				else {
					List temp = new ArrayList();
					temp.add(p);
					cache.put(userId,temp);
				}
			}
			List prefs = new ArrayList();
			for (Iterator itor = cache.entrySet().iterator(); itor.hasNext();){
				Map.Entry entry = (Map.Entry)itor.next();
				String name = (String)entry.getKey();
				List preferences = (List)entry.getValue();
				prefs.add(new CustomerPreferences(preferences,name));
			}
			CustomerPreferences[] rv = new CustomerPreferences[prefs.size()];
			for (int i = 0; i < rv.length; i++){
				rv[i] = (CustomerPreferences)prefs.get(i);
			}
			rs.close();
			stmt.close();
			DriverManager.closeConnection(con);
			return rv;
		} catch (SQLException e){
			log.info(this.getClass() + " : Error in method getCustomerPreferences.");
			e.printStackTrace();
			return null;
		}
	}

	public int deleteCustomerPreferences(String userId) {
		Connection con = DriverManager.getConnection();
		try {
			String delete = "DELETE FROM PTT_USER_PREFERENCES WHERE USERID='" + userId + "'";
			Statement stmt = con.createStatement();
			if (stmt.execute(delete))
			    return 1;
			else
				return -1;
		} catch (SQLException e){
			log.info(this.getClass() + " : Error in method deleteCustomerPreferences(\"" + userId + "\").");
			e.printStackTrace();
			return -1;
		}
	}

	public CustomerPreferences getCustomerPreferences(String userId) {
		Connection con = DriverManager.getConnection();
		try {
			Statement stmt = con.createStatement();
			String query = "SELECT * FROM PTT_USER_PREFERENCES WHERE USERID='" + userId + "'";
			ResultSet rs = stmt.executeQuery(query);
			List prefs = new ArrayList();
			String uid = null;
			while (rs.next())
			{
				uid   = rs.getString(1);
				String prefName = rs.getString(2);
				int prefValue = rs.getInt(3);
				CustomerPreference p = new CustomerPreference(prefName, prefValue);
				prefs.add(p);
			}
			CustomerPreferences userPrefs = new CustomerPreferences(prefs, uid);
			rs.close();
			stmt.close();
			DriverManager.closeConnection(con);
			return userPrefs;
		} catch (SQLException e){
			log.info("Error in method getCustomerPreferences");
		}
		return null;
	}

	public String[] getPreferenceNames(String dummy) {
		Connection con = DriverManager.getConnection();
		try {
			Statement stmt = con.createStatement();
			String query = "SELECT DISTINCT PREFERENCENAME FROM PTT_USER_PREFERENCES";
			ResultSet rs = stmt.executeQuery(query);
			List names = new ArrayList();
			while (rs.next())
			{
				names.add(rs.getString(1));
			}
			String[] names2 = new String[names.size()];
			for (int i = 0; i < names2.length; i++)
				names2[i] = (String)names.get(i);
			rs.close();
			stmt.close();
			DriverManager.closeConnection(con);
			return names2;
		} catch (SQLException e){
			log.info("Error in method getCustomerPreferences");
		}
		return null;
	}

}



































