package dk.thoerup.droidradarservice;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


public class LocationService extends HttpServlet {
	private static final long serialVersionUID = 1L;


	public LocationService() {
		super();
		// TODO Auto-generated constructor stub
	}
	
	
	protected void updateDb(Connection conn, long imei, double latitude, double longitude, String handle, String group) throws SQLException {
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement("UPDATE droidradar SET handle=?, groupname=?, latitude=?, longitude=?, updated=now()  WHERE  imei=?");
			stmt.setString(1, handle);
			stmt.setString(2, group);
			stmt.setDouble(3, latitude);
			stmt.setDouble(4, longitude);
			stmt.setLong(5, imei);
			
			int count = stmt.executeUpdate();
			
			
			System.out.println("count" + count);
			
			if (count < 1) {
				stmt.close();
				stmt = conn.prepareStatement("INSERT INTO droidradar (imei,handle,groupname,latitude,longitude,updated) VALUES (?,?,?,?,?, now() )" );
				stmt.setLong(1, imei);
				stmt.setString(2, handle);
				stmt.setString(3, group);
				stmt.setDouble(4, latitude);
				stmt.setDouble(5, longitude);
				stmt.execute();
			}
		} finally {
			if (stmt != null && !stmt.isClosed())
				stmt.close();
		}
	}
	
	protected String locateDroids(Connection conn, long imei, double latitude, double longitude) throws SQLException {
		//inner select is workaround from not being able to use a calculated column directly in where clause
		final String SQL = "SELECT * FROM ( "+
						   "               SELECT imei,handle,groupname,latitude,longitude, " +
						   "                     extract(epoch from now())::int - extract(epoch from updated)::int  AS lastupdate, " + 
						   "                     earth_distance( ll_to_earth(latitude,longitude), ll_to_earth(?,?))::int AS calcdist " + 
						   "               FROM droidradar " +
                           "               WHERE imei <> ? AND updated  > now() - interval '15 minutes'  " +
						   "       ) AS droidradar2 " +
                           "WHERE calcdist < 64000 " +
					       "ORDER BY calcdist ASC ";
		
		System.out.println(SQL);
		
		PreparedStatement stmt = null;
		ResultSet res = null;
		
		StringBuffer buff = new StringBuffer();
		
		buff.append("<?xml version=\"1.0\" ?>\n");
		buff.append("<droids>\n");
		try
		{
			stmt = conn.prepareStatement(SQL);
			stmt.setDouble(1, latitude);
			stmt.setDouble(2, longitude);
			stmt.setLong(3, imei);
			
			res = stmt.executeQuery();
			
			while (res.next()) {
				buff.append("<droid>\n");
				buff.append("<imei>").append(res.getLong(1) ) .append("</imei>\n");
				buff.append("<handle>").append(res.getString(2) ) .append("</handle>\n");
				buff.append("<group>").append(res.getString(3) ) .append("</group>\n");
				buff.append("<latitude>").append(res.getString(4) ) .append("</latitude>\n");
				buff.append("<longitude>").append(res.getString(5) ) .append("</longitude>\n");
				buff.append("<lastupdate>").append(res.getInt(6) ) .append("</lastupdate>\n");				
				
				buff.append("</droid>\n");				
			}
		} finally {
			if (res != null && !res.isClosed())
				res.close();
			if (stmt != null && !stmt.isClosed())
				stmt.close();
		}
		buff.append("</droids>\n");
		return buff.toString();
	}

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		long imei = Long.parseLong(request.getParameter("imei"));
		
		double latitude = Double.parseDouble( request.getParameter("latitude"));
		double longitude = Double.parseDouble( request.getParameter("longitude"));
		
		
		String handle = request.getParameter("handle");
		String group = request.getParameter("group");
		
		
		Connection conn = null;
		try {
			conn = DBConnection.getConnection();
			
			updateDb(conn, imei, latitude, longitude, handle, group);
			
			String xml = locateDroids(conn, imei, latitude, longitude);
			
			response.setContentType("text/xml");
			response.getWriter().print(xml);
			
			conn.close();
			conn = null;
			
			
		} catch (Exception e) {
			throw new ServletException(e);
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (Throwable t) {}
		}
		
	}

}