package com.ferienclub.vacancymgm;

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.Calendar;
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.User;
import com.ferienclub.leisure.esb.messages.BookingQuery;
import com.ferienclub.util.DriverManager;
import com.ferienclub.util.IDGenerator;
import com.ferienclub.vacancymgm.beans.Booking;
import com.ferienclub.vacancymgm.beans.Room;
import com.ferienclub.vacancymgm.beans.RoomType;

public class VacancyManagementImpl implements VacancyManagement {
	
	Logger log = Logger.getLogger(VacancyManagementImpl.class);
	
	public Room[] getRooms(String dummy) {
		try {
			log.info(this.getClass() + " : Entering Method getRooms()");
	    	Connection con = DriverManager.getConnection();
	    	String sql = "SELECT r.ROOMID, r.ROOMNO, r.STATUS, r.FLOOR, rt.ROOMTYPEID, rt.ROOMCATEGORY, rt.ROOMTYPEDESC " +
	    			     "FROM VM_ROOM r, VM_ROOMTYPE rt " +
	    			     "WHERE r.TYPE=rt.ROOMTYPEID";
	    	Statement stmt = con.createStatement();
	    	ResultSet rs = stmt.executeQuery(sql);
	    	List rooms = new ArrayList();
	    	while (rs.next()){
	    		Room r = new Room();
	    		r.setRoomId(rs.getString(1));
	    		r.setRoomNo(rs.getInt(2));
	    		r.setStatus(rs.getString(3));
	    		r.setFloor(rs.getInt(4));
	    		RoomType rt = new RoomType();
	    		rt.setRoomTypeId(rs.getString(5));
	    		rt.setRoomCategory(rs.getString(6));
	    		rt.setRoomTypeDesc(rs.getString(7));
	    		r.setType(rt);
	    		rooms.add(r);
	    	}
	    	rs.close();
	    	stmt.close();
	    	DriverManager.closeConnection(con);
	    	Room[] returnValue = new Room[rooms.size()];
	    	for (int i = 0; i < rooms.size(); i++){
	    		Room r = (Room)rooms.get(i);
	    		System.out.println(r);
	    		returnValue[i] = r;
	    	}
	    	System.out.println("Returning ...");
	    	return returnValue;
    	} catch (SQLException e){
    		e.printStackTrace();
    	}
        return null;
	}
	
	private Booking[] getBookings(){
		log.info(this.getClass() + " : Entering method getBookings()");
		try {
			Connection con = DriverManager.getConnection();
	    	Statement stmt = con.createStatement();
	    	String sql = "SELECT BOOKINGID, BOOKINGUSER, FROMDATE, TODATE, STATUS FROM VM_BOOKING";
	    	List bookings = new ArrayList();
	    	ResultSet rs = stmt.executeQuery(sql);
	    	while (rs.next())
	    	{
	    		Booking b = new Booking();
	    		User bookingUser = new User();
	    		b.setBookingId(rs.getString(1));
	    		bookingUser.setUserId(rs.getString(2));
	    		b.setBookingUser(bookingUser);
	    		b.setFromDate(rs.getDate(3));
	    		b.setToDate(rs.getDate(4));
	    		b.setStatus(rs.getString(5));
	    		bookings.add(b);
	    	}
	    	
	    	// Now get all userIds for the users participating in this booking
	    	for (Iterator itor = bookings.iterator(); itor.hasNext();){
	    		Booking b = (Booking)itor.next();
	    		List users = new ArrayList();
	    		String query = "SELECT USERID FROM VM_BOOKING_USER WHERE BOOKINGID='" + b.getBookingId() + "'";
	    		rs = stmt.executeQuery(query);
	    		while (rs.next()){
	    			String userId = rs.getString(1);
	    			User u = new User();
	    			u.setUserId(userId);
	    			users.add(u);
	    		}
	    		b.setUsers(users);
	    	}
	    	
	    	Booking[] returnArray = new Booking[bookings.size()];
	    	for (int i = 0; i < returnArray.length; i++)
	    	{
	    		returnArray[i] = (Booking)bookings.get(i);
	    	}
	    	
	    	rs.close();
	    	stmt.close();
	    	DriverManager.closeConnection(con);
	    	return returnArray;
		} catch (Exception e){
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * Returns all Bookings from the database. 
	 * TODO attach the Rooms and Users
	 */
	public Booking[] getBookings(String dummy) {
		return getBookings();
	}
	
	/**
	 * Returns all Bookings from the database. 
	 * TODO attach the Rooms and Users
	 */
	public Booking[] getBookings(BookingQuery dummy) {
		return getBookings();
	}
	
	public Booking[] getBookings4Room(String roomId) {
		log.info(this.getClass() + " : Entering method getBookings4Room(\"" + roomId + "\")");
		try {
			Connection con = DriverManager.getConnection();
	    	Statement stmt = con.createStatement();
	    	String sql = "SELECT b.BOOKINGID, b.BOOKINGUSER, b.FROMDATE, b.TODATE, b.STATUS " +
	    			     "from VM_BOOKING b, VM_ROOM r, VM_ROOM_BOOKING rb " +
	    			     "WHERE b.BOOKINGID=rb.BOOKINGID AND rb.ROOMID=r.ROOMID AND " +
	    			     "r.ROOMID='" + roomId + "'";
	    	List bookings = new ArrayList();
	    	ResultSet rs = stmt.executeQuery(sql);
	    	while (rs.next()){
	    		Booking b = new Booking();
	    		b.setBookingId(rs.getString(1));
	    		User bookingUser = new User();
	    		bookingUser.setUserId(rs.getString(2));
	    		b.setBookingUser(bookingUser);
	    		b.setFromDate(rs.getDate(3));
	    		b.setToDate(rs.getDate(4));
	    		b.setStatus(rs.getString(5));
	    		bookings.add(b);
	    	}
	    	rs.close();
	    	stmt.close();
	    	DriverManager.closeConnection(con);
	    	Booking[] returnValue = new Booking[bookings.size()];
	    	for (int i = 0; i < bookings.size(); i++){
	    		Booking b = (Booking)bookings.get(i);
	    		returnValue[i] = b;
	    	}
	    	return returnValue;
    	} catch (SQLException e){
    		e.printStackTrace();
    	}
        return null;
	}

	public int makeBooking(Booking b) {
		log.info(this.getClass() + " : Entering method makeBooking(\"" + b.getBookingUser().getUserId() + "\")");
		try {
			Connection con = DriverManager.getConnection();
	    	Statement stmt = con.createStatement();
	    	
	    	/* ***  First enter the probably new users into the database ***/
	    	
	    	String sql = "SELECT USERID FROM VM_USER";
	    	ResultSet rs = stmt.executeQuery(sql);
	    	Map userCache = new HashMap();
	    	while (rs.next())
	    	{
	    		String userId = rs.getString(1);
	    		userCache.put(userId, "exists"); 
	    	}
	    	for (int i = 0; i < b.getUsers().size(); i++)
	    	{
	    		if (userCache.get(((User)b.getUsers().get(i)).getUserId()) == null){
		    		String insert = "INSERT INTO VM_USER VALUES ('" + ((User)b.getUsers().get(i)).getUserId() + "')";
		    		stmt.execute(insert);
		    		userCache.put(((User)b.getUsers().get(i)).getUserId(),"exists");
	    		}
	    	} 
	    	
	    	
	    	/* *********   Save the Booking ***********/
	    	
	    	// Save the main booking entry
	    	String bookingId = IDGenerator.fetchNewId();
	    	SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
	    	Timestamp stamp1 = new Timestamp(b.getFromDate().getTime());
	    	Timestamp stamp2 = new Timestamp(b.getToDate().getTime());
	    	String insert = "INSERT INTO VM_BOOKING VALUES (?,?,?,?,?)";
	    	PreparedStatement pstmt = con.prepareStatement(insert);
	    	pstmt.setString(1,bookingId);
	    	pstmt.setString(2,(b.getBookingUser()).getUserId());
	    	pstmt.setTimestamp(3,stamp1);
	    	pstmt.setTimestamp(4,stamp2);
	    	pstmt.setString(5,"OPEN");
	    	pstmt.execute();
	    	// Save the associations for the rooms
	    	for (int i = 0; i < b.getRoomsIds().size(); i++){
	    		insert = "INSERT INTO VM_ROOM_BOOKING VALUES ('" + bookingId + "','" + (String)b.getRoomsIds().get(i) + "')";
	    		System.out.println(insert);
	    		stmt.execute(insert);
	    	}
	    	// Save the associations for the users
	    	for (int i = 0; i < b.getUsers().size(); i++){
	    		insert = "INSERT INTO VM_BOOKING_USER VALUES ('" + bookingId + "','" + ((User)b.getUsers().get(i)).getUserId() + "')";
	    		System.out.println(insert);
	    		stmt.execute(insert);
	    	}
	    	rs.close();
	    	stmt.close();
	    	DriverManager.closeConnection(con);
    	} catch (SQLException e){
    		e.printStackTrace();
    		return -1;
    	}
        return 1;
	}

	/*
	 * Hole Buchung für den Benutzer und setze Status auf <to be defined>
	 * Setze die Stati der die Buchung betreffenden Räume auf OCCUPIED
	 */
	public int checkIn(User u) {
		ResultSet rs;
		Statement stmt;
		Connection con;
		try {
	    	con = DriverManager.getConnection();
	    	stmt = con.createStatement();
	    	// Get the booking
	    	log.info(this.getClass() + "Getting Booking Data");
	    	String getBookingsSQL = "SELECT BOOKINGID, FROMDATE, TODATE FROM VM_BOOKING " +
	    							"WHERE BOOKINGUSER='" + u.getUserId() + "' AND STATUS='OPEN'";
	    	log.info(this.getClass() + " : executing: \"" + getBookingsSQL + "\"");
	    	rs = stmt.executeQuery(getBookingsSQL);
	    	boolean firstRow = true;
	    	String bookingId = "";
	    	Date from = null, to = null, now = new Date();
	    	
	    	while (rs.next()){
	    		if (!firstRow) break; // We only take the first booking --> TODO add serious checkIn handling
	    		bookingId = rs.getString(1);
	    		from = rs.getDate(2);
	    		to = rs.getDate(3);
	    		firstRow = false;
	    	}
	    	
	    	if (firstRow) {
	    		log.info("No Booking could be retrieved for the user " + u.getUserId());
	    		rs.close();
	    		stmt.close();
	    		DriverManager.closeConnection(con);
	    		return -1;
	    	}
	    	
	    	// Check wheter guest is in the time interval
	    	long frommillis = from.getTime();
	    	long tomillis = to.getTime();
	    	long nowmillis = now.getTime();
	    	
	    	if (nowmillis < frommillis) {
	    		log.info("The guest arrives too early.");
	    		rs.close();
	    		stmt.close();
	    		DriverManager.closeConnection(con);
	    		return -1;
	    	}
	    	
	    	if (nowmillis > tomillis) {
	    		log.info("The guest arrives too late.");
	    		rs.close();
	    		stmt.close();
	    		DriverManager.closeConnection(con);
	    		return -1;
	    	}
	    	
	    	log.info(this.getClass() + " : User is inside the desired time interval");
	    	// OK, the guest is in the right time interval
	    	// Now update the booking
	    	log.info(this.getClass() + " : Update the booking");
	    	String updateBookingSQL = "UPDATE VM_BOOKING SET STATUS='ACTIVE' WHERE BOOKINGID='" + bookingId + "'";
	    	stmt.executeUpdate(updateBookingSQL);
	    	
	    	// Now get all RoomIds for this booking
	    	log.info(this.getClass() + " : Retrieve all rooms associated with the booking " + bookingId);
	    	String getRoomIdsSQL = "SELECT ROOMID FROM VM_ROOM_BOOKING WHERE BOOKINGID='" + bookingId + "'";
	    	rs = stmt.executeQuery(getRoomIdsSQL);
	    	List roomIds = new ArrayList();
	    	while (rs.next()) roomIds.add(rs.getString(1));
	    	
	    	// Now update all rooms with the new status OCCUPIED
	    	String updateRoomSQL = "";
	    	log.info("Update each room...");
	    	for (Iterator itor = roomIds.iterator(); itor.hasNext();)
	    	{
	    		updateRoomSQL = "UPDATE VM_ROOM SET STATUS='OCCUPIED' WHERE ROOMID='" + (String)itor.next() + "'";
	    		log.info(this.getClass() + " : " + updateRoomSQL);
	    		stmt.executeUpdate(updateRoomSQL);
	    	}
	    	
	    	rs.close();
	    	stmt.close();
	    	DriverManager.closeConnection(con);
	    	log.info(this.getClass() + " : Successfully checked in User " + u.getUserId());
    	} catch (SQLException e){
    		e.printStackTrace();
    		return -1;
    	}
		return 1;
	}
	
	/*
	 * Setze die Buchung auf closed
	 * Setze alle Räume auf VACANT
	 */
	public int checkOut(User u){
		try {
			Connection con = DriverManager.getConnection();
			Statement stmt = con.createStatement();
			// Get the booking
			log.info(this.getClass() + "Getting Booking Data");
	    	String getBookingsSQL = "SELECT BOOKINGID FROM VM_BOOKING " +
	    							"WHERE BOOKINGUSER='" + u.getUserId() + "' AND STATUS='ACTIVE'";
	    	log.info(this.getClass() + " : executing: \"" + getBookingsSQL + "\"");
	    	ResultSet rs = stmt.executeQuery(getBookingsSQL);
	    	String bookingId = null;
	    	if (rs.next()) bookingId = rs.getString(1);
	    	else {
	    		log.info(this.getClass() + " : ERROR, no booking found to checkOut");
	    		return -1;
	    	}
	    	rs.close();
	    	String updateBookingSQL = "UPDATE VM_BOOKING SET STATUS='CLOSED' WHERE BOOKINGUSER='" + u.getUserId() + "'";
	    	stmt.executeUpdate(updateBookingSQL);
	    	
	    	// Now update all Rooms
	    	log.info(this.getClass() + " : Retrieve all rooms associated with the booking " + bookingId);
	    	String getRoomIdsSQL = "SELECT ROOMID FROM VM_ROOM_BOOKING WHERE BOOKINGID='" + bookingId + "'";
	    	rs = stmt.executeQuery(getRoomIdsSQL);
	    	List roomIds = new ArrayList();
	    	while (rs.next()) roomIds.add(rs.getString(1));
	    	
	    	String updateRoomSQL = null;
	    	for (Iterator itor = roomIds.iterator(); itor.hasNext();)
	    	{
	    		updateRoomSQL = "UPDATE VM_ROOM SET STATUS='VACANT' WHERE ROOMID='" + (String)itor.next() + "'";
	    		log.info(this.getClass() + " : " + updateRoomSQL);
	    		stmt.executeUpdate(updateRoomSQL);
	    	}
	    	
	    	rs.close();
	    	stmt.close();
	    	DriverManager.closeConnection(con);
	    	log.info(this.getClass() + " : Successfully checked out User " + u.getUserId());
		} catch (Exception e){
			e.printStackTrace();
			return -1;
		}
		return 1;
	}

	
}


















