package it.cnr.isti.energia.view;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;

import javax.annotation.Resource;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.sql.DataSource;

import org.apache.shiro.SecurityUtils;
import org.apache.shiro.subject.Subject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author <a href="mailto:antonino.crivello@isti.cnr.it">Antonino Crivello</a>
 *
 */
@ManagedBean(name = "roomdetail")
@SessionScoped
public class RoomDetail {

	// Resource Injection
	@Resource(name = "java:jboss/MySqlDS/weboffice")
	private DataSource db;

	private String[] rooms;
	private String[] areas;
	private String currentArea = "";

	Logger log = LoggerFactory.getLogger(RoomDetail.class);

	public void findAreasRooms() {
		//log.info("currentArea 1 : {}", this.currentArea);
		try {
			this.getAreaRoomByUser();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public String[] getRooms() {
		//log.info("currentArea 2 : {}", this.currentArea);
		try {
			if (currentArea.equals(""))
				getRoomsbyArea(areas[0]);
			else
				getRoomsbyArea(currentArea);
			Arrays.sort(rooms);
		} catch (Exception e) {
			e.printStackTrace();
			findAreasRooms();
		}
		return rooms;
	}

	public String[] getAreas() {
		//log.info("currentArea 3 : {}", this.currentArea);
		try {
			findAreasRooms();
			Arrays.sort(areas);
		} catch (Exception e) {
			e.printStackTrace();
			findAreasRooms();
		}
		return areas;
	}

	public String[] getAreasandsub() {
		//log.info("currentArea 4 : {}", this.currentArea);
		String[] app = null;
		try {
			if (currentArea.equals(""))
				app = getAreaAndSubArea(areas[0]);
			else
				app = getAreaAndSubArea(currentArea);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return app;
	}

	public String getFirstRoom() {
		//log.info("currentArea 5 : {}", this.currentArea);

		try {
			Arrays.sort(rooms);
		} catch (Exception e) {
			e.printStackTrace();
			rooms[0] = "";
		}
		return rooms[0];
	}

	public void setArea(String current) {
		log.info("currentArea 6 : {}", this.currentArea);
		currentArea = current;
	}

	public String getFirstArea() {
		//log.info("currentArea 7 : {}", this.currentArea);

		try {
			Arrays.sort(areas);
		} catch (Exception e) {
			e.printStackTrace();
			areas[0] = "";
		}
		return areas[0];
	}

	// Viene restituita una Array di String contenente gli id dei sensori.
	// vengono recuperati da Mysql
	public String[] getSensorIdbyRoom(String room) throws SQLException {
		//log.info("currentArea 8 : {}", this.currentArea);
		if (db == null)
			throw new SQLException("Can't get data source");

		// get database connection
		Connection con = db.getConnection();

		if (con == null)
			throw new SQLException("Can't get database connection");

		int index = 0;
		String Sensor = "SELECT CollectionN,MacAddress,SensorType FROM sensors WHERE IdR IN "
				+ "(SELECT IdRoom FROM rooms WHERE Name IN (\"" + room + "\"))";

		PreparedStatement psSensor = con.prepareStatement(Sensor);
		ResultSet resultSensor = psSensor.executeQuery();

		resultSensor.last();
		String[] id = new String[resultSensor.getRow()];

		if (resultSensor.first()) {
			id[index] = resultSensor.getString("MacAddress");
			while (resultSensor.next()) {
				index++;
				// store all data into a List
				id[index] = resultSensor.getString("MacAddress");
			}
		}
		resultSensor.close();
		psSensor.close();
		con.close();
		return id;
	}

	// Viene restituito un Array di String contenente i nomi delle stanze che
	// compongono
	// l'area più il nome dell'area stessa
	public String[] getAreaAndSubArea(String area) throws SQLException {
		//log.info("currentArea 9 : {}", this.currentArea);
		if (db == null)
			throw new SQLException("Can't get data source");

		// get database connection
		Connection con = db.getConnection();

		if (con == null)
			throw new SQLException("Can't get database connection");

		int index = 0;
		String Label = "SELECT IdRoom, Name FROM rooms WHERE IdRoom IN "
				+ "(SELECT IdRoom FROM multiplearearoom WHERE IdArea="
				+ "(SELECT IdArea FROM areas WHERE Description IN (\"" + area
				+ "\")))";

		PreparedStatement psLabel = con.prepareStatement(Label);
		ResultSet resultLabel = psLabel.executeQuery();

		resultLabel.last();
		String[] label = new String[resultLabel.getRow() + 1];

		label[0] = area;

		if (resultLabel.getRow() > 1) {
			if (resultLabel.first()) {
				label[++index] = resultLabel.getString("Name");
				while (resultLabel.next()) {
					++index;
					// store all data into a List
					label[index] = resultLabel.getString("Name");
				}
			}
		} else {
			String[] app = new String[1];
			app[0] = area;
			resultLabel.close();
			psLabel.close();
			con.close();
			return app;
		}
		resultLabel.close();
		psLabel.close();
		con.close();
		return label;
	}

	// Vengono settati su membri della classe gli array rooms e areas
	// su cui l'utente ha diritti di lettura/vista
	public void getAreaRoomByUser() throws SQLException {
		//log.info("currentArea 10 : {}", this.currentArea);
		if (db == null)
			throw new SQLException("Can't get data source");

		// get database connection
		Connection con = db.getConnection();

		if (con == null)
			throw new SQLException("Can't get database connection");

		Subject currentUser = SecurityUtils.getSubject();
		String user = currentUser.getPrincipal().toString();

		String Name = "SELECT * FROM users WHERE Name=\"" + user + "\"";
		PreparedStatement psName = con.prepareStatement(Name);
		ResultSet resultName = psName.executeQuery();

		if (resultName.first()) {
			// GET User
			// String UserLog = resultName.getString("Name");

			String Area = "SELECT IdArea, Description FROM areas WHERE IdArea IN"
					+ "(SELECT FkArea FROM privileges WHERE idPrivilege IN"
					+ "(SELECT FkPrivilege FROM users WHERE Name='"
					+ resultName.getString("Name") + "'))";
			PreparedStatement psArea = con.prepareStatement(Area);
			ResultSet resultArea = psArea.executeQuery();

			resultArea.last();

			areas = new String[resultArea.getRow()];

			if (resultArea.first()) {
				areas[0] = resultArea.getString("Description");

				int i = 1;
				while (resultArea.next()) {
					areas[i] = resultArea.getString("Description");
					i++;
				}
			}

			String Room = "SELECT Name FROM rooms WHERE IdRoom IN "
					+ "(SELECT IdRoom FROM multiplearearoom WHERE IdArea IN"
					+ "(SELECT IdArea FROM areas WHERE IdArea IN"
					+ "(SELECT FkArea FROM privileges WHERE idPrivilege IN"
					+ "(SELECT FkPrivilege FROM users WHERE Name='"
					+ resultName.getString("Name") + "'))))";
			PreparedStatement psRoom = con.prepareStatement(Room);
			// log.info("ROOMS PRE");
			ResultSet resultRoom = psRoom.executeQuery();
			// log.info("ROOMS POST");
			resultRoom.last();
			rooms = new String[resultRoom.getRow()];

			if (resultRoom.first()) {
				rooms[0] = resultRoom.getString("Name");
				// log.info("ROOMS 0 {}", rooms[0]);
				int i = 1;
				while (resultRoom.next()) {
					rooms[i] = resultRoom.getString("Name");
					// log.info("ROOMS "+i+" {}", rooms[i]);
					i++;
				}
			}
			resultArea.close();
			resultRoom.close();
			psArea.close();
			psRoom.close();

		}
		resultName.close();
		psName.close();
		con.close();
	}

	private void getRoomsbyArea(String current) throws SQLException {
		//log.info("currentArea 11 : {}", this.currentArea);
		if (db == null)
			throw new SQLException("Can't get data source");

		// get database connection
		Connection con = db.getConnection();

		if (con == null)
			throw new SQLException("Can't get database connection");

		String Room = "SELECT Name FROM rooms WHERE IdRoom IN "
				+ "(SELECT IdRoom FROM multiplearearoom WHERE IdArea IN"
				+ "(SELECT IdArea FROM areas WHERE Description = '" + current
				+ "'))";
		PreparedStatement psRoom = con.prepareStatement(Room);
		// log.info("ROOMS PRE");
		ResultSet resultRoom = psRoom.executeQuery();
		// log.info("ROOMS POST");
		resultRoom.last();
		rooms = new String[resultRoom.getRow()];

		if (resultRoom.first()) {
			rooms[0] = resultRoom.getString("Name");
			// log.info("ROOMS 0 {}", rooms[0]);
			int i = 1;
			while (resultRoom.next()) {
				rooms[i] = resultRoom.getString("Name");
				// log.info("ROOMS "+i+" {}", rooms[i]);
				i++;
			}
		}

		resultRoom.close();
		psRoom.close();
		con.close();
	}
}