package org.openxdata.gis.server;

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

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

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

public class MappingServiceImpl extends RemoteServiceServlet implements MappingService {

	private Connection connection;
	private Statement statement;
	private ResultSet resultSet;

	private String connectionDriver = "";
	private String connectionURL = "";
	private String databaseName = "";
	private String userName = "";
	private String password = "";

	private String querySQL = "";
	private ResultSetMetaData rsmd;

	private Properties oxdProperties;

	public void getOXDProperties() {
		oxdProperties = new Properties();
		try {
			oxdProperties.load(new FileInputStream("OPENXDATA_SETTINGS.properties"));
			this.userName = oxdProperties.getProperty("hibernate.connection.username");
			this.password = oxdProperties.getProperty("hibernate.connection.password");
			this.connectionDriver = oxdProperties.getProperty("hibernate.connection.driver_class");
			this.connectionURL = oxdProperties.getProperty("hibernate.connection.url");

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public List<String> getReportsNames() {

		List<String> reportsNames = new ArrayList<String>();
		getOXDProperties();
		try {
			Class.forName(connectionDriver);
			connection = DriverManager.getConnection(connectionURL, userName, password);
			statement = connection.createStatement();

			String sql = "SELECT name FROM report";
			resultSet = statement.executeQuery(sql);

			while (resultSet.next()) {
				reportsNames.add(resultSet.getString("name"));
			}
			resultSet.close();
			statement.close();
			connection.close();
		} catch (Exception e) {
			MessageBox.alert("Error","Something went wrong! Error as follows: " + e, null);
		}
		return reportsNames;
	}

	public List<String> getSelectedReportFields(String reportName) {
		List<String> reportFields = new ArrayList<String>();
		getOXDProperties();
		try {
			Class.forName(connectionDriver);
			connection = DriverManager.getConnection(connectionURL, userName, password);
			statement = connection.createStatement();

			String sql = "SELECT * FROM report WHERE name='" + reportName + "'";
			resultSet = statement.executeQuery(sql);
			while (resultSet.next()) { //TODO what if there are many reports of the same name? nkv
				querySQL = resultSet.getString("query_sql");
			}
			if (querySQL.length() != 0) { //i.e. the string is not null nkv
				resultSet = statement.executeQuery(querySQL);
				rsmd = resultSet.getMetaData();
				int numberOfColumns = rsmd.getColumnCount();
				for (int i=1; i<=numberOfColumns; i++) {
					reportFields.add(rsmd.getColumnName(i));
				}
			}
		} catch (Exception e) {
			MessageBox.alert("Error","Something went wrong! Error as follows: " + e, null);
		}
		return reportFields;
	}

	public List<String> getReportDataRow(String reportName, String reportField, String reportFieldValue) {
		List<String> reportDataRow = new ArrayList<String>();
		getOXDProperties();
		try {
			Class.forName(connectionDriver);
			connection = DriverManager.getConnection(connectionURL, userName, password);
			statement = connection.createStatement();

			String sql = "SELECT * FROM report WHERE name='" + reportName + "'";
			resultSet = statement.executeQuery(sql);
			while (resultSet.next()) { //TODO what if there are many reports of the same name? nkv
				querySQL = resultSet.getString("query_sql");
			}
			if (querySQL.length() != 0) { //i.e. the string is not null nkv
				resultSet = statement.executeQuery(querySQL);
				rsmd = resultSet.getMetaData();
				System.out.println("this is the report field value from shape file: " + reportFieldValue);
				int numberOfColumns = rsmd.getColumnCount();
				System.out.println("these are report field values in the database: ");
				while (resultSet.next()) {
					System.out.println(resultSet.getObject(reportField).toString());
					if (resultSet.getObject(reportField).toString().equalsIgnoreCase(reportFieldValue)) {
						for (int i=1; i<=numberOfColumns; i++) {
							reportDataRow.add(rsmd.getColumnName(i));
							reportDataRow.add(resultSet.getObject(i).toString());
						}
					}
				}
			}
		} catch (Exception e) {
			MessageBox.alert("Error","Something went wrong! Error as follows: " + e, null);
		}
		return reportDataRow;
	}

}