import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
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 uk.ac.roe.wfau.StringToRADec;

import com.oreilly.servlet.multipart.FilePart;
import com.oreilly.servlet.multipart.MultipartParser;
import com.oreilly.servlet.multipart.ParamPart;
import com.oreilly.servlet.multipart.Part;

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

public class MPSQL 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 filename=null;
      String rows=null;
      String rowtotal=null;
      String compressParam=null;
      String formatParam=null;
      String dataType=null;
      File savedUploadFile=null;
      long size=0;
      String resultsPageHeading = resultsTitle;
      boolean errorFlag=false;
      StringBuffer errorMess=new StringBuffer();
          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;
      PrintWriter out = null;
        out = res.getWriter();
      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
      {
          try {
              MultipartParser mp = new MultipartParser(req, 10 * 1024 * 1024); // 10MB
              Part part;
            //  out.println("<p>Parsing input .....");
              while ((part = mp.readNextPart()) != null) {
                  String name = part.getName();
                  if (part.isParam()) {
                      ParamPart paramPart = (ParamPart) part;
                      String value = paramPart.getStringValue();
    
      	  		  	 if (name.equalsIgnoreCase("action")){
       				   action=value;
       	  		  	 }
      	  		  	 
     	  		  	 if (name.equalsIgnoreCase("sqlstmt")){
         				   SQLQueryString=value;
     	  		  	 }
    	  		  	 if (name.equalsIgnoreCase("emailAddress")){
       				   emailAddress=value;
   	  		  	 }
       	  		  	 if (name.equalsIgnoreCase("rows")){
         				   rows=value;
     	  		  	 }
      	  		  	 if (name.equalsIgnoreCase("rowtotal")){
       				   rowtotal=value;
   	  		  	 }
     	  		  	 if (name.equalsIgnoreCase("compress")){
         				   compressParam=value;
     	  		  	 }
    	  		  	 if (name.equalsIgnoreCase("format")){
       				   formatParam=value;
   	  		  	 }
       	  		  	 if (name.equalsIgnoreCase("dataType")){
         				   dataType=value;
     	  		  	 }
  
                  } else if (part.isFile()) {

                      // it's a file part
          
                      FilePart filePart = (FilePart) part;

                      filename = filePart.getFileName();
                     
                      if (filename == null) {
                          
                      }
                      
                      if (filename != null) {
                          String tmpUp=dirName+"/tmp_sql";
                          File dir = new File(tmpUp);
                          
  
                          try {
                          savedUploadFile = File.createTempFile("upload_sql", "tmp",
                                  dir);
                          
                          }
                          catch (Exception e) {
                              out.print(e);
                          }
                          // out.println("<br><br/>"+savedUploadFile.getAbsolutePath());
                          //out.println("<br><br/>"+savedUploadFile.getName());
                          size = filePart.writeTo(savedUploadFile);
                   
                          if (size == 0) {
                              errorFlag=true;
                              errorMess.append("<p><b>Upload file contain zero bytes, check filename</b>");
                          }
                         

                          
  
                          

                      }
                      
                  } // file part                 
                  } // while
             
          } // try
              catch (IOException IOEx) {
              out.println(IOEx);
          }                


        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 (emailAddress != null && !emailAddress.equalsIgnoreCase(""))
	      {
             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 (rows != null)
          {
            try
            {
            	rowsHTML = Integer.parseInt(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 (rowtotal != null)
          {
            if ( rowtotal.equalsIgnoreCase("N") ||
                 rowtotal.equalsIgnoreCase("NO") )
            {
              doRowTotal = false;
            }
          }

          // What about compression?
          if (compressParam != null)
          {
            
            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 (formatParam != null)
          {

            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;
            };
          };

          int numberOfLines=0;
          if (!errorFlag && savedUploadFile != null) {
              BufferedReader br;
              
              String line;
              br = new BufferedReader(new InputStreamReader(
                      new FileInputStream(savedUploadFile)));
              
              line="";
              long ll;
              double dd;
              while ((line=br.readLine()) !=null && numberOfLines < 10000) {
                  numberOfLines++;
          
                  try {
                 
                   if (dataType.equalsIgnoreCase("integer")) {
                       
                       ll=Long.parseLong(line.trim());
                   }
                   if (dataType.equalsIgnoreCase("float")) {
                       
                       dd=Double.parseDouble(line.trim());
                   }
                  }
                  catch (NumberFormatException nfe) {

                      errorFlag=true;
                      errorMess.append("<br /> error reading IDs at line "+numberOfLines);
                      
                  }

              }
              
              br.close();
   
              }
              

          // 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");
           if (errorFlag) {
               out.println("<p><b>Errors with input:</b><p>"+errorMess);
               return;
           }
          
          GenericSQLRetrieverThread retrieverThread = null;
          Thread runner = null;

          try
          {
            

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

            out.println(
              "    <h1>" + resultsPageHeading + "</h1> \n"
            );
            
            
            if (!errorFlag && savedUploadFile != null) {
                
                out.println("<p>"+filename+" uploaded file size: " + size
                        + " bytes, " + numberOfLines + " rows</p>");
                out.flush();
                
            }
            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");
            if (savedUploadFile != null && size > 0) {
            retrieverThread.setUploadFile(savedUploadFile.getAbsolutePath(),dataType);
            }
            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()

 
};