package org.greenstone.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import org.greenstone.client.Place;
import java.util.HashMap;

public class PlaceInformation
{
	static Statement _database = null;
	static HashMap<String, ArrayList<Place>> _knownPlaces = new HashMap<String, ArrayList<Place>>();

	/**
	 * Default constructor Creates a connection to the database
	 */
	public static void init()
	{
		try
		{
			System.out.print("Loading postgresql driver... ");
			Class.forName("org.postgresql.Driver");
			Connection c = DriverManager.getConnection("jdbc:postgresql://localhost/GS3MapDB", "postgres", "admin");//DriverManager.getConnection("jdbc:postgresql://localhost/test", "sjm84", "password");
			System.out.println("Done!");
			_database = c.createStatement();
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
		}
	}

	/**
	 * Searches the database for a specific place by using the place name and
	 * the parent's place names
	 * 
	 * @param id
	 *            is the id of the place to search for in the database
	 * @return the list of places found (in case there is more than one match)
	 */
	public static ArrayList<Place> getSpecificPlace(Long id)
	{
		try
		{
			ResultSet results = _database.executeQuery("SELECT * FROM places WHERE id = " + id);

			// Go through the results and create a list of places
			ArrayList<Place> placeList = new ArrayList<Place>();

			while (results.next())
			{
				Place p = Place.createPlaceFromInformation(results.getLong("id"), results.getString("placename"), results.getString("subregion"), results.getString("region"), results.getString("country"), results.getString("placetype"), (Float) (results.getObject("longitude")), (Float) (results.getObject("latitude")), results.getLong("population"));

				if (p != null)
				{
					placeList.add(p);
				}
			}

			if (placeList.size() == 0)
			{
				placeList = null;
			}

			results.close();

			// Return the list of places
			return placeList;
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
			return null;
		}
	}

	/**
	 * Searches the database for a specific place by using the place name and
	 * the parent's place names
	 * 
	 * @param placeName
	 *            is the name of the place to find
	 * @param parentPlaceName
	 *            is the name of the place's parents (seperated by ", ")
	 * @return the list of places found (in case there is more than one match)
	 */
	public static ArrayList<Place> getSpecificPlace(String placeName, String parentPlaceName)
	{
		// Make sure the place name to find is not null
		if (placeName == null)
		{
			return null;
		}

		ResultSet results = null;

		String firstParent = null;
		String secondParent = null;
		String thirdParent = null;

		// Separate the parents
		if (parentPlaceName != null)
		{
			String[] parents = parentPlaceName.split(", ");

			if (parents.length == 1)
			{
				firstParent = parents[0];
			}
			else if (parents.length == 2)
			{
				firstParent = parents[0];
				secondParent = parents[1];
			}
			else
			{
				firstParent = parents[0];
				secondParent = parents[1];
				thirdParent = parents[2];
			}
		}

		try
		{
			// If the place does not have a parent
			if (parentPlaceName == null)
			{
				results = _database.executeQuery("SELECT * FROM places WHERE placename = '" + placeName.replaceAll("'", "\\\\'") + "' " + "and subregion is null " + "and region is null " + "and country is null;");
			}
			// If the place has one parent
			else if (secondParent == null)
			{
				results = _database.executeQuery("SELECT * FROM places WHERE placename = '" + placeName.replaceAll("'", "\\\\'") + "' " + "and subregion is null " + "and region is null " + "and country = '" + firstParent.replaceAll("'", "\\\\'") + "';");
			}
			// If the place has two parents
			else if (thirdParent == null)
			{
				results = _database.executeQuery("SELECT * FROM places WHERE placename = '" + placeName.replaceAll("'", "\\\\'") + "' " + "and subregion is null " + "and region = '" + firstParent.replaceAll("'", "\\\\'") + "' " + "and country = '" + secondParent.replaceAll("'", "\\\\'") + "';");
			}
			// If the place has three parents
			else
			{
				results = _database.executeQuery("SELECT * FROM places WHERE placename = '" + placeName.replaceAll("'", "\\\\'") + "' " + "and subregion = '" + firstParent.replaceAll("'", "\\\\'") + "' " + "and region = '" + secondParent.replaceAll("'", "\\\\'") + "' " + "and country = '" + thirdParent.replaceAll("'", "\\\\'") + "';");
			}

			// Go through the results and create a list of places
			ArrayList<Place> placeList = new ArrayList<Place>();

			while (results.next())
			{
				Place p = Place.createPlaceFromInformation(results.getLong("id"), results.getString("placename"), results.getString("subregion"), results.getString("region"), results.getString("country"), results.getString("placetype"), (Float) (results.getObject("longitude")), (Float) (results.getObject("latitude")), results.getLong("population"));

				if (p != null)
				{
					placeList.add(p);
				}
			}

			if (placeList.size() == 0)
			{
				placeList = null;
			}

			results.close();

			// Return the list of places
			return placeList;
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
			return null;
		}
	}

	/**
	 * Gets all of the place with the given name
	 * 
	 * @param placeName
	 * @return
	 */
	public static ArrayList<Place> getPlaces(String placeName)
	{
		ArrayList<Place> placeList = new ArrayList<Place>();

		ArrayList<Place> places = null;
		if((places = _knownPlaces.get(placeName)) != null)
		{
			return places;
		}
		
		try
		{
			if (_database == null)
			{
				System.out.println("***?***");
			}
			// Do a database query with the given place name
			ResultSet results = _database.executeQuery("SELECT * FROM places WHERE placename = \'" + placeName.replaceAll("'", "\\\\'") + "\'");

			// Go through the results and add them to the list of found places
			while (results.next())
			{
				Place p = Place.createPlaceFromInformation(results.getLong("id"), results.getString("placename"), results.getString("subregion"), results.getString("region"), results.getString("country"), results.getString("placetype"), (Float) (results.getObject("longitude")), (Float) (results.getObject("latitude")), results.getLong("population"));

				if (p != null)
				{
					placeList.add(p);
				}
			}
			results.close();

			// Find if any places have this place name as a alternative name
			ResultSet altNames = _database.executeQuery("SELECT * FROM alternatenames WHERE alternatename = \'" + placeName.replaceAll("'", "\\\\'") + "\'");

			ArrayList<Long> alternateIndexes = new ArrayList<Long>();

			while (altNames.next())
			{
				alternateIndexes.add(altNames.getLong("placeindex"));
			}
			altNames.close();

			for (Long index : alternateIndexes)
			{
				results = _database.executeQuery("SELECT * FROM places WHERE id = " + index);

				while (results.next())
				{
					Place p = Place.createPlaceFromInformation(results.getLong("id"), results.getString("placename"), results.getString("subregion"), results.getString("region"), results.getString("country"), results.getString("placetype"), (Float) (results.getObject("longitude")), (Float) (results.getObject("latitude")), results.getLong("population"));

					if (p != null)
					{
						placeList.add(p);
					}
				}
			}
			results.close();
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
		}

		_knownPlaces.put(placeName, placeList);
		
		return placeList;
	}
}