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 java.util.logging.Level;
import java.util.logging.Logger;

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

/**
 * Servlet implementation class LocateStations
 */
public class LocateStations extends HttpServlet {
	private static final long serialVersionUID = 1L;

	Logger logger = Logger.getLogger( LocateStations.class.toString() );


	
	protected String transformToIntList(String input) {		
		String strings[] = input.split(",");
		
		StringBuffer sb = new StringBuffer();
		sb.append("(");
		for (int i = 0; i<strings.length; i++) {
			if (i>0) {
				sb.append(",");
			}
			sb.append( Integer.parseInt(strings[i])); //by doing the integer conversion we ensure that it really is a integer
		}
		sb.append(")");
		return sb.toString();		
	}


	protected PreparedStatement createStatement(Connection conn, HttpServletRequest req) throws SQLException {

		PreparedStatement stmt = null;
		String SQL;

		if (req.getParameter("latitude") != null && req.getParameter("latitude") != null) {
			//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 enabled = true " +
			"       ) AS trainstations2 " +
			"ORDER BY calcdist ASC " + 
			"LIMIT 4 ";
			double latitude = Double.parseDouble( req.getParameter("latitude") );
			double longitude = Double.parseDouble( req.getParameter("longitude") );
			stmt = conn.prepareStatement(SQL);
			stmt.setDouble(1, latitude);
			stmt.setDouble(2, longitude);
		} else if (req.getParameter("name") != null) {
			SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,0.0 " + 
			"FROM trainstations " + 
			"WHERE name ILIKE ? AND enabled = true " +
			"ORDER BY name ";

			String name = req.getParameter("name").trim();
			stmt = conn.prepareStatement(SQL);
			stmt.setString(1, name + "%");
		} else if (req.getParameter("list") != null) {
			String list = transformToIntList( req.getParameter("list"));
			SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,0.0 " + 
			"FROM trainstations " + 
			"WHERE id IN " + list + " AND enabled = true " +
			"ORDER BY name ";
			stmt = conn.prepareStatement(SQL);
		} 

		return stmt;
	}


	protected String formatResultset(PreparedStatement stmt) throws SQLException {
		ResultSet res = null;

		StringBuffer buff = new StringBuffer();

		buff.append("<?xml version=\"1.0\" ?>\n");
		buff.append("<stations>\n");	

		try
		{
			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();
	}

	@Override
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

		Connection conn = null;
		try {
			conn = DBConnection.getConnection();

			PreparedStatement stmt = createStatement(conn, request);
			if (stmt != null){
				String xml = formatResultset(stmt);

				response.setContentType("text/xml");
				response.getWriter().print(xml);
			} else {
				response.sendError(400, "not enough parameters");		
			}


		} catch (Exception e) {				
			logger.log(Level.SEVERE, "Exception while finding stations", e);
			response.sendError(500);						
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (Exception t) {}
		}
	}

}