import java.io.FileInputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Calendar;
import java.util.Properties;

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

import net.alanmaxwell.html.HTMLStrings;
import net.alanmaxwell.sql.GenericSQLRetrieverThread;
import net.alanmaxwell.sql.SQLServerInfoJDBC;

public class ServletSQL extends HttpServlet {  // 1
  private int serviceCurrent = 0;
  private int serviceTotal = 0;

  // Access methods for service counter...
  protected synchronized void enteringServiceMethod() { //2
    serviceCurrent++;
    serviceTotal++;
  };

  protected synchronized void leavingServiceMethod() { //3
    serviceCurrent--;
  };

  protected synchronized int getServiceCurrent() { //4
    return serviceCurrent;
  };

  protected synchronized int getServiceTotal() { //5
    return serviceTotal;
  };

  private boolean shuttingDown = false;

  // Access methods for the shuttingDown flag...
  protected synchronized void setShuttingDown(boolean flag) { //6
    shuttingDown = flag;
  };

  protected synchronized boolean isShuttingDown() { //7
    return shuttingDown;
  };


  private String dirName;
  private String tmpWeb;
  private String server;
  private String user;
  private String passwd;
  private String database;
  private String resultsTitle="";
  private String baseURL="";  

  public void init(ServletConfig config) throws ServletException {

	  super.init(config);
      String path=getServletContext().getRealPath("/");
      Properties properties = new Properties();
      try {
		  properties.load(new FileInputStream(path+"WEB-INF/ServletSQL.properties"));
	  }
	  catch (IOException e) {
		  System.out.println("ERROR: properties file not found");
		  return;
	  }

	  server = properties.getProperty("server");
	  database = properties.getProperty("database");
	  user = properties.getProperty("user");
	  passwd = properties.getProperty("passwd");  
      dirName = properties.getProperty("tmpDir");
      tmpWeb=properties.getProperty("tmpWeb");
      resultsTitle=properties.getProperty("resultsTitle");
      baseURL=properties.getProperty("baseURL");
  }

  // Override the service method to update the serviceCounter...
  protected void service(HttpServletRequest req,
                         HttpServletResponse resp)
                         throws ServletException,IOException {

    enteringServiceMethod();

    try
    {
      super.service(req, resp);
    }
    finally
    {
      leavingServiceMethod();
    } ;
  }; // end of service

 


  protected void doGet(HttpServletRequest req, HttpServletResponse res)
                 throws ServletException, IOException {
 
    try {
       if (server == null){
		   res.sendError(
	           res.SC_BAD_REQUEST,
	            "INVALID SQL Server. Check properties file."
	            );
			}

      // FIRST THING: Extract and verify all the provided parameters...
      SQLServerInfoJDBC SQLServer = null;
      String SQLQueryString = "";
      String ra=null;
      String dec=null;
      String ra2000=null;
      String dec2000=null;
      String radius="";
      String name=null;
      String sys=null;
      String select=null;
      String action=null;
      String where=null;
      String from=null;
      int tableid=0;
      
      String resultsPageHeading = resultsTitle;

      int rowsHTML = GenericSQLRetrieverThread.SQLROWS_DEFAULT;
      boolean doRowTotal = true;

      int OutputFormat = GenericSQLRetrieverThread.OUTPUT_NONE;
      int OutputCompression = GenericSQLRetrieverThread.COMPRESS_NONE;

      boolean do6df = false;
      boolean useHMS = true;
      boolean doTAR = false;
      String emailTAR = null;
      String emailAddress=null;
      boolean fg=true;
      
      SQLServer = new SQLServerInfoJDBC(
	          server, database, user, passwd,true
			            );
      // If we haven't been given a server, there's no point continuing...
      if (SQLServer == null)
      {
        res.sendError(
          res.SC_BAD_REQUEST,
          "You must provide the name of a VALID SQL Server as parameter 'server'."
        );
        return; // Finish this method immediately...
      }
      else
      {


      if (req.getParameter("action") != null)
	      {
	        action = req.getParameter("action");
          };



      if (action.equalsIgnoreCase("freeform"))
      {
		        if (req.getParameter("sqlstmt") != null)
		  	      {
		  	        SQLQueryString=req.getParameter("sqlstmt");
		  	       
          };
	  }


        if (SQLQueryString == null)
        {
          res.sendError(
            res.SC_BAD_REQUEST,
            "No SQL query was provided as parameter 'sql'."
          );
          return; // Finish this method immediately...
        }
        else if (SQLQueryString.length() > 4096)
        {
          res.sendError(
            res.SC_BAD_REQUEST,
            "The SQL query parameter 'sql' cannot exceed a length of 4096 characters."
          );
          return;
        }
        else
        {
          // Now we can extract the final OPTIONAL parameters...
 

	      if (req.getParameter("emailAddress") != null && !req.getParameter("emailAddress").equals(""))
	      {
	        emailAddress = req.getParameter("emailAddress");
             if (
                   ( emailAddress.length() < 3) ||
                   (emailAddress.indexOf("@") == -1)
                 )
              {
                res.sendError(
                  res.SC_BAD_REQUEST,
                  "You must also include a VALID e-mail address  " +
                  "(your emailAdddress= parameter seems to be empty or " +
                  "incorrect) "
                );
                return;
			}
		  }
		  else
		  {
			  emailAddress=null;
		  }


          // Do they want to return a special number of ROWS?
          if (req.getParameter("rows") != null)
          {
            try
            {
            	rowsHTML = Integer.parseInt(req.getParameter("rows"));
            }
            catch (NumberFormatException e)
            {
              rowsHTML = GenericSQLRetrieverThread.SQLROWS_DEFAULT;
            };

            if (rowsHTML < 0)
            {
              rowsHTML = 0;
            };
          };

          // When more rows are returned than displayed in summary,
          // does the user want to know the total rows returned?
          // Default, yes, takes slightly more time to run...
          if (req.getParameter("rowtotal") != null)
          {
            if ( req.getParameter("rowtotal").equalsIgnoreCase("N") ||
                 req.getParameter("rowtotal").equalsIgnoreCase("NO") )
            {
              doRowTotal = false;
            }
          }

          // What about compression?
          if (req.getParameter("compress") != null)
          {
            String compressParam = req.getParameter("compress");

            if (compressParam.equalsIgnoreCase("ZIP"))
            {
              OutputCompression = GenericSQLRetrieverThread.COMPRESS_ZIP;
            }
            else if (compressParam.equalsIgnoreCase("GZIP"))
            {
              OutputCompression = GenericSQLRetrieverThread.COMPRESS_GZIP;
            }
            else
            {
              OutputCompression = GenericSQLRetrieverThread.COMPRESS_NONE;
            };
          };

          // Any special format (in addition to HTML) requested?
          if (req.getParameter("format") != null)
          {
            String formatParam = req.getParameter("format");

            if (formatParam.equalsIgnoreCase("CSV"))
            {
              OutputFormat = GenericSQLRetrieverThread.OUTPUT_CSV;
            }
            else

            if (formatParam.equalsIgnoreCase("FITS"))
            {
			  OutputFormat = GenericSQLRetrieverThread.OUTPUT_FITS;
		    }
            else
            if (formatParam.equalsIgnoreCase("VOT"))
			            {
						  OutputFormat = GenericSQLRetrieverThread.OUTPUT_VOT;
		    }
		    else
            {
              OutputFormat = GenericSQLRetrieverThread.OUTPUT_NONE;
            };
          };

  
          

          // Now we should have everything we need, attempt to get the data...

          // Connect to the database and attempt to execute the provided SQL
          // Set the content type and then formulate the normal response...

          res.setContentType("text/html");

          PrintWriter out = null;
          GenericSQLRetrieverThread retrieverThread = null;
          Thread runner = null;

          try
          {
            out = res.getWriter();

            // Prepare the heading of the HTML document:
            out.println(
              HTMLStrings.getHTMLHeadWithScript(
                resultsPageHeading,
                baseURL + "configurestyles.js"
              )
            );

            out.println(
              "    <h1>" + resultsPageHeading + "</h1> \n"
            );
            
            if (OutputFormat != GenericSQLRetrieverThread.OUTPUT_NONE)
            {
              out.println(
                "    <p> \n" +
                "      Data file generating queries can take a bit longer to execute \n" +
                "      as they write to a file ALL rows returned by the query. \n" +
                "    </p> \n"
              );

              out.println(
                "    <p> \n" +
                "      A web link to your generated output file will appear at the \n" +
                "      bottom of this page. \n" +
                "    </p> \n"
              );
            };

            out.println(
              "    <p>"
            );
                
            out.print(
              "      <b>QUERY STARTED:</b> " +
                Calendar.getInstance().getTime() +
                "&nbsp;&nbsp;&nbsp;[" + getServiceCurrent() + " active, " +
                getServiceTotal() + " total] \n"
            );

            out.println(
              "    </p> \n"
            );

            out.println(
              "    <p> \n" +
              "      Please keep this browser window open and wait for your results to appear below... \n" +
              "    </p> \n"
            );

            res.flushBuffer();

            // THIS SECTION WILL DO THE QUERY:


              retrieverThread =
                new GenericSQLRetrieverThread(SQLServer); // Server we setup earlier...

             retrieverThread.setOutputBaseDIR(
//                "/home/6df/public_html/"
				dirName
              );
              retrieverThread.setOutputBaseURL(
//                "http://www-wfau.roe.ac.uk/6dFGS/"
                   tmpWeb
              );
              retrieverThread.setOutputAppendage(
                "tmp_sql/"
              );



            // Pass through any parameters to the retriever...



//            retrieverThread.setSQLQuery("EXEC dbo.spWebExecuteSQL '"+SQLQueryString+" '");
            retrieverThread.setSQLQuery(SQLQueryString);
            //retrieverThread.setSQLQuery(SQLQueryString);
            retrieverThread.setRowsHTML(rowsHTML);
            retrieverThread.setDoRowTotal(doRowTotal);
            retrieverThread.setOutputFormat(OutputFormat);
            retrieverThread.setOutputCompression(OutputCompression);
            retrieverThread.setFromAddress("mar@roe.ac.uk");
            retrieverThread.setSubject(database+" database query results");
            if ( emailAddress != null)
            {
				retrieverThread.setMightBeBackground(true);
			}









            // Start a separate thread running to retrieve the SQL...
            runner = new Thread(retrieverThread);
            runner.start();

    // Calls the the resolving method with the command line argument


          if (action.equalsIgnoreCase("region")) {
		        out.println("RA/Long.: " + ra2000 + " &nbsp;DEC/Lat: " + dec2000 +
		        " &nbsp; Coord. Sys (<b>B</b>1950,<b>J</b>2000 or <b>G</b>alactic): "+ sys);
			}

            out.println("    <p>");

            int dotCount = 0;

            while (
                    (!isShuttingDown()) &&
                    (runner.isAlive()) &&
                    (retrieverThread.isQueryProcessing() && fg == true)
                  )
            {
	             if ( emailAddress != null && dotCount >= 27 )
	             {
					 retrieverThread.setEmailAddress(emailAddress);
					 fg = false;
				  }
              try
              {
                Thread.sleep(3000);
              }
              catch (InterruptedException ie)
              {
                // Do nothing when interrupted...
              }

              out.print(
                "&#8226;&#8226;&#8226;<!--                                         " +
                "                                                           -->"
              );

              res.flushBuffer();

              //dotCount++;
                dotCount=dotCount+3;
              if ((dotCount % 30) == 0)
              {
                out.println(" "+
                   retrieverThread.getRowsReturned() + " rows returned so far <br>"
                );

                //dotCount = 0;
              }
            }


			if (fg || !retrieverThread.isQueryProcessing()) {

            fg=true;
			out.println("      OK");
            out.println("    </p> \n");

            retrieverThread.setQueryTerminated(true);
            retrieverThread.setMightBeBackground(false);
            out.println(retrieverThread.getResultsHTML());

            out.println(
              "    <p> \n" +
              "      <b>QUERY FINISHED:</b> " + Calendar.getInstance().getTime() + " \n" +
              "    </p> \n"
            );

            out.println(
              "    <p> \n" +
              "      Click your browsers 'BACK' button to try another query...\n" +
              "    </p> \n"
            );

            if (action.equalsIgnoreCase("radial")) {
				out.println(
					"<form action=\"pixel.jsp?ra="+ra2000+"&dec="+dec2000+
					"&radius="+radius+"&sys="+sys+"\" method=\"post\">" +
					"<input class=\"FontSans\" type=\"submit\" value=\"getAreaImage\" /> </form> "+
					"Click the getAreaImage button to retrieve an image of the searched area"
					);
				}


            out.println(
              HTMLStrings.getHTMLTail(baseURL + "browserversion.js")
            );

			}
			else
			{
				out.println("<p> Query moved to background. "+
								"On completion you will be informed where to retrieve your results by email to <b>"+
				emailAddress+"</b><p> Click your browsers 'BACK' button to return to the form page. </body></html>");

			}




            // QUERY DONE!
          }
          finally
          {
            // Free up resources...
//            System.out.println("here");
//            File fooFile = new File("/home/6df/tada.txt");
//            fooFile.createNewFile();
//				File fooFile = new File("/home/avo/mar/tada.txt");
 //           fooFile.createNewFile();

            if (fg == true) {

            	if (retrieverThread != null)
            	{
					/*
					if (retrieverThread.isQueryProcessing()){
						System.out.println("resorting to close");
						retrieverThread.setDBConnectionClosed();
					}
                    */

            	  retrieverThread.setQueryTerminated(true);
            	  //System.out.println("stopped");
            	};

			}

				runner = null;
        	    retrieverThread = null;

            if (out != null)
            {
              out.close();
            };

            out = null;
          }; // try..finally
        }; // end of valid SQL string
      }; // end of valid server if (SQLServer == null) else...
    } // end of initail try or doGet
    catch (Exception e)
    {
       
      res.sendError(
        res.SC_INTERNAL_SERVER_ERROR,
        e + ""
      );
      
      return;
    };
  }; // END of doGet()

  protected void doPost(HttpServletRequest req, HttpServletResponse res)
    throws ServletException, IOException
  {
    // Data received by an HTTP POST is handled the same way
    // as data sent with a HTTP GET request... simply pass on to above...
    doGet(req, res);
  }; // END of doPost()

 
};