package com.points.lcp.samples.lpapi.servlets;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

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

import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;
import com.heroku.sdk.jdbc.DatabaseUrl;

public class MemberValidationServlet extends HttpServlet {
	
	private static Gson gson = new GsonBuilder().setPrettyPrinting().disableHtmlEscaping().create();

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		try {
			
			JsonObject mvRequest = new JsonParser().parse(req.getReader()).getAsJsonObject();
			Connection connection = DatabaseUrl.extract().getConnection();
			Statement stmt = connection.createStatement();
			String selectQuery = "
				SELECT
					memberId,
					firstName,
					lastName,
					balance,
					email,
					accountStatus,
					membershipLevel,
					accountCreationDate,
					countryCode,
					language
				FROM LPAPIUSERS
				WHERE memberId = '" + mvRequest.getAsJsonPrimitive("memberId").getAsString() + "'
				;
			";
			ResultSet rs = stmt.executeQuery(selectQuery);
			if (rs.next()) {
				JsonObject mvResponse = new JsonObject();
				mvResponse.addProperty("status", "success");				
				mvResponse.addProperty("statusMessage", "SUCCESS");
				mvResponse.addProperty("memberId", rs.getString(1));				
				mvResponse.addProperty("firstName", rs.getString(2));
				mvResponse.addProperty("lastName", rs.getString(3));
				mvResponse.addProperty("balance", rs.getInt(4));
				mvResponse.addProperty("email", rs.getString(5));
				mvResponse.addProperty("accountStatus", rs.getString(6));
				mvResponse.addProperty("membershipLevel", rs.getString(7));
				mvResponse.addProperty("accountCreationDate", rs.getString(8));
				mvResponse.addProperty("countryCode", rs.getString(9));
				mvResponse.addProperty("language", rs.getString(10));
				
				resp.getWriter().write(gson.toJson(mvResponse));
			} else {
				JsonObject unknownMember = new JsonObject();
				unknownMember.addProperty("status", "failure");
				unknownMember.addProperty("statusMessage", "UNKNOWN_MEMBER");
				resp.getWriter().write(gson.toJson(unknownMember));
			}	
		} catch (Exception e) {
			resp.sendError(400);
		} 		
	}
}

@@ -1028,25 +1028,24 @@
  = %22
-%0A%09%09%09%09
 SELECT
+ %22
 %0A%09%09%09%09%09
++%22
 memb
@@ -1049,22 +1049,26 @@
 emberId,
+ %22
 %0A%09%09%09%09%09
++%22
 firstNam
@@ -1069,22 +1069,26 @@
 rstName,
+ %22
 %0A%09%09%09%09%09
++%22
 lastName
@@ -1092,22 +1092,26 @@
 ame,
+ %22
 %0A%09%09%09%09%09
++%22
 balance,
 %0A%09%09%09
@@ -1110,26 +1110,34 @@
 nce,
+ %22
 %0A%09%09%09%09%09
++%22
 email,
+ %22
 %0A%09%09%09%09%09
++%22
 acco
@@ -1146,22 +1146,26 @@
 tStatus,
+ %22
 %0A%09%09%09%09%09
++%22
 membersh
@@ -1172,22 +1172,26 @@
 ipLevel,
+ %22
 %0A%09%09%09%09%09
++%22
 accountC
@@ -1202,22 +1202,26 @@
 ionDate,
+ %22
 %0A%09%09%09%09%09
++%22
 countryC
@@ -1228,22 +1228,26 @@
 ode,
+ %22
 %0A%09%09%09%09%09
++%22
 language
 %0A%09%09%09
@@ -1242,21 +1242,25 @@
 language
+ %22
 %0A%09%09%09%09
++%22
 FROM LPA
@@ -1266,21 +1266,25 @@
 APIUSERS
+ %22
 %0A%09%09%09%09
++%22
 WHERE me
@@ -1360,18 +1360,19 @@
 + %22'
+ %22
 %0A%09%09%09%09
-;%0A%09%09%09
++%22; 
 %22;%0A%09

Fix LPAPI sample queries