package dk.thoerup.traininfoservice;

import java.io.IOException;
import java.io.PrintWriter;
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 LoadStations extends HttpServlet {
	private static final long serialVersionUID = 1L;

	public LoadStations() {
		super();
	}

	protected void insertOrUpdate(Connection conn, String type, String code, String name)  throws SQLException{
		String sql = "SELECT id FROM trainstations WHERE stationcode=?";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setString(1, code);
		
		ResultSet rs = stmt.executeQuery();
		
		boolean stog = type.equalsIgnoreCase("S2");
		
		if (rs.next()) {
			int id = rs.getInt(1);
			stmt = conn.prepareStatement("UPDATE trainstations SET name=?, stog=?, updated=now() WHERE id=?");
			stmt.setString(1, name);
			stmt.setBoolean(2, stog);
			stmt.setInt(3, id);
			stmt.execute();
		} else {
			stmt = conn.prepareStatement("INSERT INTO trainstations (name,stationcode,stog,updated) VALUES (?,?,?,now())");
			stmt.setString(1, name);
			stmt.setString(2, code);
			stmt.setBoolean(3, stog);
			stmt.execute();
		}
		
	}

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		String url = "http://rafiki.t-hoerup.dk/tog/stations.php";

		String stationStr = DownloadUtil.getContentString(url, 5000, "ISO-8859-1");

		String stations[] = stationStr.split("\n");
		Connection conn = null; 
		try {
			conn = DBConnection.getConnection();		
			for (String stationLine : stations) {
				String fields[] = stationLine.split(";");
				
				
				insertOrUpdate(conn, fields[0], fields[1], fields[2]);
			}
		} catch (Exception e) {
			throw new ServletException(e);
		} finally {
			try {
				if (conn != null && !conn.isClosed())
					conn.close();
			} catch (SQLException e) {}
		}    			
		PrintWriter out = resp.getWriter();
		out.print("ok");
	}

}