package org.openxdata.gis.server;

import java.io.FileInputStream;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Properties;

import org.openxdata.gis.client.data.MarkerData;
import org.openxdata.gis.client.data.ReportData;
import org.openxdata.gis.client.service.ConnectionService;

import com.extjs.gxt.ui.client.widget.MessageBox;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;

/**
 * This class creates the connection to the database and runs a SQL query
 *
 * @author Hans Terje Møller
 */
@SuppressWarnings("serial")
public class ConnectionServiceImpl extends RemoteServiceServlet implements
		ConnectionService {

	// Class variables
	// These variables are for getting the different cells from the database
	private int reportId;
	private int formId;
	private String name;
	private String description;
	private String query;

	// This variable is the report query
	private String reportQuery;

	// These variables are for establishing the database connection
	private Connection connection;
	private Statement statement;
	private ResultSet resultSet;

	// These variables defines the settings for connecting to the database
	private String driver = "com.mysql.jdbc.Driver";
	private String databaseUrl = "jdbc:mysql://localhost:3306/";
	private String databaseName = "openxdata";
	private String username;
	private String password;

	// These variables are for setting and getting the different values
	private int intData;
	private String stringData;
	private double doubleData;
	private Date dateData;

	// This variable is for adding the converted object to the array
	private String valueData;

	// This variable converts the date from the database to this format
	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd");

	/**
	 * This method establishes a connection and runs the SQL query for
	 * getting the reports
	 */
	public ReportData[] getReportData() {
		// This array list stores the data retrieved from the SQL query
		ArrayList<ReportData> reportValues = new ArrayList<ReportData>();
		Properties oxdProperties = new Properties();

		try {
			// Loads the OpenXdata properties
			oxdProperties.load(new FileInputStream("OPENXDATA_SETTINGS.properties"));

			// Gets user name, password and database name from OpenXdata properties
			username = oxdProperties.getProperty("hibernate.connection.username");
			password = oxdProperties.getProperty("hibernate.connection.password");

			// Creates the database connection
			Class.forName(driver);
			connection = DriverManager.getConnection(databaseUrl + databaseName, username, password);
			statement = connection.createStatement();

			// This is the report SQL query
			String sql = "SELECT report_id, form_definition_version_id, name, description, query_sql FROM reports";
			resultSet = statement.executeQuery(sql);

			// This is a while-loop which runs to the database and retrieves all
			// the rows of the given columns
			while (resultSet.next()) {
				reportId = resultSet.getInt("report_id");
				formId = resultSet.getInt("form_definition_version_id");
				name = resultSet.getString("name");
				description = resultSet.getString("description");
				query = resultSet.getString("query_sql");

				reportValues.add(new ReportData(reportId, formId, name,
						description, query));
			}

			resultSet.close();
			statement.close();
			connection.close();

		} catch (Exception e) {
			MessageBox.alert("Error",
					"Something went wrong! Error as follows: " + e, null);
		}

		// Returns the values to the array
		return (ReportData[]) reportValues.toArray(new ReportData[reportValues
				.size()]);
	}

	/**
	 * This method establishes the connection and runs the SQL query for getting
	 * the data in the reports
	 */
	public MarkerData[] getMarkerData() {

		// This array list stores the data retrieved from the SQL query
		ArrayList<MarkerData> values = new ArrayList<MarkerData>();

		try {
			// Creates the database connection
			Class.forName(driver);
			connection = DriverManager.getConnection(
					databaseUrl + databaseName, username, password);
			statement = connection.createStatement();

			// This is the SQL query
			resultSet = statement.executeQuery(reportQuery);

			// Gets the meta data from the result set
			ResultSetMetaData rsMetaData = resultSet.getMetaData();
			// Gets the number of columns in the table
			int columnCount = rsMetaData.getColumnCount();

			while (resultSet.next()) {
				for (int i=1; i <= columnCount; i++) {
					// Gets the column names in the table
					String columnName = rsMetaData.getColumnName(i);
					// Gets the column types in the table
					String columnType = rsMetaData.getColumnTypeName(i);
					// Handles all of the values as objects
					Object value = resultSet.getObject(i);

					// Checks what type the object is and converts it accordingly
					// At the time being it supports INTEGER, VARCHAR, TEXT, DOUBLE and DATE.
					// TODO: Add support for all types
					if (value != null) {
						if (columnType.equals("INTEGER")) {
							String valueData = value.toString();
							setIntData(Integer.parseInt(valueData));
						} else if (columnType.equals("VARCHAR")) {
							String valueData = value.toString();
							setStringData(valueData);
						} else if (columnType.equals("TEXT")) {
							String valueData = value.toString();
							setStringData(valueData);
						} else if (columnType.equals("DOUBLE")) {
							String valueData = value.toString();
							setDoubleData(Double.parseDouble(valueData));
						} else if (columnType.equals("DATE")) {
							String valueData = value.toString();
							setDateData(sdf.parse(valueData));
						}
					}

					// Sets a 'null' value if the object doesn't contain any data
					if (value == null) {
						valueData = "";
					} else {
						valueData = value.toString();
					}

					values.add(new MarkerData(valueData, columnName, columnType, columnCount));
				}
			}

			resultSet.close();
			statement.close();
			connection.close();

		} catch (Exception e) {
			MessageBox.alert("Error",
					"Something went wrong! Error as follows: " + e, null);
		}

		return (MarkerData[]) values.toArray(new MarkerData[values.size()]);
	}

	/**
	 * Method for getting the database URL
	 */
	public String getDatabaseUrl(String databaseUrl) {
		return this.databaseUrl = databaseUrl;
	}

	/**
	 * Method for getting the database name
	 */
	public String getDatabaseName(String databaseName) {
		return this.databaseName = databaseName;
	}

	/**
	 * This method gets the report query
	 */
	public String getReportQuery(String reportQuery) {
		return this.reportQuery = reportQuery;
	}

	/**
	 * Method for setting intData
	 * @param intData
	 */
	public void setIntData(int intData) {
		this.intData = intData;
	}

	/**
	 * Method for getting intData
	 * @return intData
	 */
	public int getIntData() {
		return intData;
	}

	/**
	 * Method for setting stringData
	 * @param stringData
	 */
	public void setStringData(String stringData) {
		this.stringData = stringData;
	}

	/**
	 * Method for getting stringData
	 * @return stringData
	 */
	public String getStringData() {
		return stringData;
	}

	/**
	 * Method for setting doubleData
	 * @param doubleData
	 */
	public void setDoubleData(double doubleData) {
		this.doubleData = doubleData;
	}

	/**
	 * Method for getting doubleData
	 * @return doubleData
	 */
	public double getDoubleData() {
		return doubleData;
	}

	/**
	 * Method for setting dateData
	 * @param date
	 */
	public void setDateData(Date dateData) {
		this.dateData = dateData;
	}

	/**
	 * Method for getting dateData
	 * @return dateData
	 */
	public Date getDateData() {
		return dateData;
	}
}