package db;
import java.io.IOException;
import java.io.OutputStream;
import java.net.InetSocketAddress;

import com.sun.net.httpserver.HttpExchange;
import com.sun.net.httpserver.HttpHandler;
import com.sun.net.httpserver.HttpServer;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;

import org.json.*;
/*
 * a simple static http server
*/
public class TDPM_AP {

  public static void main(String[] args) throws Exception {

    HttpServer server = HttpServer.create(new InetSocketAddress(8000), 0);
    server.createContext("/GetTrainsNumbersInDay", new MyHandler());
    server.setExecutor(null); // creates a default executor
    server.start();
    JSONEncode();

  }

  static class MyHandler implements HttpHandler {
    public void handle(HttpExchange t) throws IOException {
      String response = "Welcome Real's HowTo test page";
      t.sendResponseHeaders(200, response.length());
      OutputStream os = t.getResponseBody();
      os.write(response.getBytes());
      os.close();
    }
  }

  public static void JSONEncode() throws JSONException {

		System.out.println("-------- PostgreSQL "
				+ "JDBC Connection Testing ------------");

		try {

			Class.forName("org.postgresql.Driver");

		} catch (ClassNotFoundException e) {

			System.out.println("Where is your PostgreSQL JDBC Driver? "
					+ "Include in your library path!");
			e.printStackTrace();
			return;

		}

		System.out.println("PostgreSQL JDBC Driver Registered!");

		Connection connection = null;
		ResultSet resultSet = null;

		try {


			connection = DriverManager.getConnection(
					"jdbc:postgresql://192.168.60.210/sandbox_timetable", "sandbox",
					"sandbox");

			Statement select = connection.createStatement();
          resultSet = select.executeQuery("SELECT * FROM trains WHERE id='230'");

          JSONArray jArray = new JSONArray();


            while (resultSet.next())
            {
          	  Integer json_id = resultSet.getInt("id");
  		      String json_number = resultSet.getString("number");
  		      String json_carrier = resultSet.getString("carrier");
  		      String json_category = resultSet.getString("category");
  		      String json_name = resultSet.getString("name");
                //System.out.println(json_id);

  		      JSONObject jObj = new JSONObject();
  		      jObj.put("id", json_id);
  		      jObj.put("number", json_number);
  		      jObj.put("carrier", json_carrier);
  		      jObj.put("category", json_category);
  		      jObj.put("name", json_name);

  		      jArray.put(jObj);

            }

            System.out.println(jArray);


		} catch (SQLException e) {

			System.out.println("Connection Failed! Check output console");
			e.printStackTrace();
			return;

		}

		if (connection != null) {
			System.out.println("You made it, take control your database now!");
		} else {
			System.out.println("Failed to make connection!");
		}
	}
}