package dk.thoerup.traininfoservice;

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;

import dk.thoerup.traininfoservice.DBConnection;

/**
 * Servlet implementation class LocateStations
 */
public class LocateStations extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public LocateStations() {
        super();
        // TODO Auto-generated constructor stub
    }
    


	protected String getStations(Connection conn, double latitude, double longitude, String name) throws SQLException {
		String SQL = "";
		
		if (latitude >= 0.0 && longitude >= 0.0)
		{
		//inner select is workaround from not being able to use a calculated column directly in where clause
			SQL = "SELECT * FROM ( "+
						   "               SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, " + 
						   "                     earth_distance( ll_to_earth(latitude,longitude), ll_to_earth(?,?))::int AS calcdist " + 
						   "               FROM trainstations " +
						   "               WHERE latitude IS NOT NULL AND longitude IS NOT NULL " +
						   "       ) AS trainstations2 " +
					       "ORDER BY calcdist ASC " + 
					       "LIMIT 4 ";
		} else if (name != null) {
			SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,0.0 " + 
				  "FROM trainstations " + 
				  "WHERE name ILIKE ? AND latitude IS NOT NULL AND longitude IS NOT NULL " +
				  "ORDER BY name ";
		} else throw new SQLException("not enough parameters");
		
		
		PreparedStatement stmt = null;
		ResultSet res = null;
		
		StringBuffer buff = new StringBuffer();
		
		buff.append("<?xml version=\"1.0\" ?>\n");
		buff.append("<stations>\n");
		try
		{
			stmt = conn.prepareStatement(SQL);
			if (latitude >= 0 && longitude >= 0) {
				stmt.setDouble(1, latitude);
				stmt.setDouble(2, longitude);
			}
			if (name != null) {
				stmt.setString(1, "%" + name + "%");
			}
			
			res = stmt.executeQuery();
			
			while (res.next()) {
				buff.append("<station>\n");

				buff.append("<id>").append( res.getInt(1) ).append("</id>\n");
				buff.append("<name>").append( res.getString(2) ) .append("</name>\n");
				buff.append("<latitude>").append( res.getDouble(3) ) .append("</latitude>\n");
				buff.append("<longitude>").append( res.getDouble(4) ) .append("</longitude>\n");
				res.getString(5);
				buff.append("<fjerntog>").append( !res.wasNull() ) .append("</fjerntog>\n");
				res.getString(6);
				buff.append("<stog>").append( !res.wasNull() ) .append("</stog>\n");
				buff.append("<calcdist>").append( res.getInt(7) ) .append("</calcdist>\n");
				
				buff.append("</station>\n");				
			}
		} finally {
			if (res != null && !res.isClosed())
				res.close();
			if (stmt != null && !stmt.isClosed())
				stmt.close();
		}
		buff.append("</stations>\n");
		return buff.toString();
	}

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		double latitude = -1.0;
		if (request.getParameter("latitude") != null)
			latitude = Double.parseDouble( request.getParameter("latitude") );
		
		double longitude = -1.0;
		if (request.getParameter("latitude") != null)
			longitude = Double.parseDouble( request.getParameter("longitude") );
		
		String name = request.getParameter("name");
				
		Connection conn = null;
		try {
			conn = DBConnection.getConnection();
			
			String xml = getStations(conn, latitude, longitude, name);
			
			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) {}
		}
	}

}