package org.greenstone.atlas.server;

import java.net.URLConnection;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import org.greenstone.atlas.client.FindPlaceService;
import org.greenstone.atlas.client.Place;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;

/**
 * The server side implementation of the RPC service.
 */
@SuppressWarnings("serial")
public class FindPlaceServiceImpl extends RemoteServiceServlet implements FindPlaceService 
{
	protected PageScanner _pageScanner = null;
	protected Statement _database = null;
	protected ArrayList<Place> _places = null;

	public Boolean loadGazetteer()
	{
		System.err.println("Loading Gazetteer... ");
		_pageScanner = new PageScanner(this.getServletContext().getRealPath(""));
		System.err.println("... Done!");
		return true;
	}
	
	public String getMarkedUpText()
	{
		return _pageScanner.getMarkedUpText();
	}
	
	public Boolean findPlacesInText(String text, String html)
	{
		_pageScanner.examineTextWithGate(text, html);	
		return true;
	}

	public ArrayList<Place> getPlaces(double maxScore, double minScore, long minPopulation, boolean locality, boolean region, boolean country, int numberOfPlacesToGet) 
	{
		return _pageScanner.getPlacesWithParams(minScore, maxScore, minPopulation, locality, region, country, numberOfPlacesToGet);
	}
	
	public Boolean adjustScoringParams(double penalty, double parentBonus, double indirectReferencePercent) 
	{
		_pageScanner.setScoringParams(penalty, parentBonus, indirectReferencePercent);
		return true;
	}
	
	public Boolean isGazetteerLoaded() 
	{
		return _pageScanner != null && _pageScanner.isGazetteerLoaded();
	}

	public ArrayList<ArrayList<Place>> findPlacesInMultipleTexts(String[] texts) 
	{
		return _pageScanner.examineMultipleTexts(texts);
	}

	public ArrayList<Place> updateGazetteer(int start, int end) 
	{
		if(_places == null)
		{
			if(_database == null)
			{
				try 
				{
					Class.forName("org.postgresql.Driver");
					Connection c = DriverManager.getConnection("jdbc:postgresql://localhost/GS3MapDB", "sam","password"); 
					_database = c.createStatement();
				}
				catch (Exception ex) 
				{
					ex.printStackTrace();
				}
				
			}
			
			System.out.println("Got connection");
	
			try
			{
				ResultSet results = _database.executeQuery("SELECT * FROM places WHERE latitude is null;");
				
				System.out.println("Got results");
				
				_places = new ArrayList<Place>();

				while(results.next())
				{
					_places.add(Place.createPlaceFromInformation(results.getLong("id"), results.getString("placename"), results.getString("subregion"), results.getString("region"), results.getString("country"), results.getString("placetype"), null, null, results.getLong("population")));	
				}
			}
			catch(Exception ex)
			{
				ex.printStackTrace();
			}
		}
		
		System.out.println("Making subset");
		
		ArrayList<Place> placeSubset = new ArrayList<Place>();
		for(int i = start; i <= end; i++)
		{
			if(i < _places.size())
			{
				System.out.println("Adding place to subset " + i);
				placeSubset.add(_places.get(i));
			}
		}
		
		System.out.println("Done making subset, places size = " + placeSubset.size());
		
		return placeSubset;
	}
	
	public Boolean addToDatabase(Place p, float lat, float lng)
	{
		if(_database == null)
		{
			try 
			{
				Class.forName("org.postgresql.Driver");
				Connection c = DriverManager.getConnection("jdbc:postgresql://localhost/GS3MapDB", "sam","password"); 
				_database = c.createStatement();
			}
			catch (Exception ex) 
			{
				ex.printStackTrace();
			}
		}
		
		String placeName = p.getName();
		String parentPlaceName = p.getParentPlaceName();
		
		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)
			{
				 _database.executeQuery("UPDATE places SET latitude = " + lat + ", longitude = " + lng  + " 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)
			{
				_database.executeQuery("UPDATE places SET latitude = " + lat + ", longitude = " + lng  + " 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)
			{
				_database.executeQuery("UPDATE places SET latitude = " + lat + ", longitude = " + lng  + " WHERE placename = \'" + placeName.replaceAll("\'", "\\\\\'") + "\' " +
						"and subregion is null " +
						"and region = \'" + firstParent.replaceAll("\'", "\\\\\'") + "\' " +
						"and country = \'" + secondParent.replaceAll("\'", "\\\\\'") + "\';");
			}
			//If the place has three parents
			else
			{
				_database.executeQuery("UPDATE places SET latitude = " + lat + ", longitude = " + lng  + " WHERE placename = \'" + placeName.replaceAll("\'", "\\\\\'") + "\' " +
						"and subregion = \'" + firstParent.replaceAll("\'", "\\\\\'") + "\' " +
						"and region = \'" + secondParent.replaceAll("\'", "\\\\\'") + "\' " +
						"and country = \'" + thirdParent.replaceAll("\'", "\\\\\'") + "\';");
			}
			_database.close();
			_database = null;
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		
		return true;
	}

	public ArrayList<String> spatialSearch(ArrayList<Float[]> points) 
	{
		try 
		{
			System.out.println("Connecting to database for spatial search");
			if(_database == null)
			{
				Class.forName("org.postgresql.Driver");
				Connection c = DriverManager.getConnection("jdbc:postgresql://localhost/GS3MapDB", "sam","password"); 
				_database = c.createStatement();
			}	
			System.out.println("Done connecting to database");
			
			StringBuilder polygonString = new StringBuilder("'POLYGON((");
			
			for(int j = 0; j < points.size(); j++)
			{
				Float[] current = points.get(j);
				if(j != 0)
				{
					polygonString.append(", " + current[0] + " " + current[1]);
				}
				else
				{
					polygonString.append(current[0] + " " + current[1]);
				}
			}
			
			polygonString.append(", " + points.get(0)[0] + " " + points.get(0)[1]);
			
			polygonString.append("))'");
			
			System.out.println("Polystring = " + polygonString);
			
			ResultSet results = _database.executeQuery("select placename from places where id in (select id from placepoints where ST_Within(point, " + polygonString + "));");
			
			ArrayList<String> placeNames = new ArrayList<String>();
			while(results.next())
			{
				placeNames.add(results.getString("placename"));
				System.out.println(results.getString("placename"));
			}
			
			_database.close();
			_database = null;
			
			return placeNames;
		}
		catch (Exception ex) 
		{
			ex.printStackTrace();
			return null;
		}
	}
	
	public Place getPlaceById(long id)
	{
		try
		{
			if(_database == null)
			{
				System.out.print("Loading postgresql driver... ");
				Class.forName("org.postgresql.Driver");
				Connection c = DriverManager.getConnection("jdbc:postgresql://localhost/GS3MapDB", "sam", "password");//DriverManager.getConnection("jdbc:postgresql://localhost/test", "sjm84", "password");
				System.out.println("Done!");
				_database = c.createStatement();
			}
			
			ResultSet results = _database.executeQuery("SELECT * FROM places WHERE id = " + id);
			if(!results.next())
			{
				return null;
			}
			
			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"));
			
			results.close();
			
			return p;
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
			return null;
		}
	}
}