package net.mar;
import javax.servlet.*;
import javax.servlet.http.HttpSession;

import uk.ac.roe.wfau.SesameClient;
import uk.ac.roe.wfau.TangentRectangle;
import uk.ac.roe.wfau.VDFSSchema;
import uk.ac.roe.wfau.VDFSSession;
import uk.ac.roe.wfau.WSASchema;
import uk.ac.roe.wfau.WSASession;
import net.mar.AnException;
import net.mar.DefaultSelect;
//import cds.Sesame;
import java.util.StringTokenizer;
import jsky.coords.*;
import java.awt.geom.*;
import java.io.PrintWriter;

public class SQLRegion
{
		String ra2000=null;
		String dec2000=null;
		String sys=null;
	    String ra=null;
	    String dec=null;
        String radius=null;
        String from=null;
        String xSize=null;
        String ySize=null;
        String boxAlignment=null;
        int regionType = 0; // 1= radial 2= box
 		String name="";
 		int archiveID=VDFSSchema.WSAARCHIVEID;
 
	public SQLRegion() {
			super();
		}

	public SQLRegion(int archiveID) {
			super();
			this.archiveID = archiveID;
		}

	public String setRequest(ServletRequest req,HttpSession sess, String databaseToUse) throws AnException
	{
        boolean errorsPresent=false;
        StringBuffer errorSB=new StringBuffer("");
        
		String sql="";
        String select=null;
     	String action=null;
      	String where=null;
      	
      	String prog="";
      	String database=null;
      	int tableid=0;
		      if (req.getParameter("radius") != null)
		          {
		            radius = req.getParameter("radius");
		          }
		      if (req.getParameter("xSize") != null)
	          {
	            xSize = req.getParameter("xSize");
	          }
		      if (req.getParameter("ySize") != null)
	          {
	            ySize = req.getParameter("ySize");
	          }
		      if (req.getParameter("boxAlignment") != null)
	          {
	            boxAlignment = req.getParameter("boxAlignment");
	          }
		      if (req.getParameter("ra") != null)
		          {
		            ra = req.getParameter("ra");
		          }
		      if (req.getParameter("dec") != null)
		          {
		            dec = req.getParameter("dec");
		          }
		      if (req.getParameter("name") != null)
			      {
			        name = req.getParameter("name");
		          }
		      if (req.getParameter("sys") != null)
			      {
			        sys = req.getParameter("sys");
          		  }
      if (req.getParameter("select") != null)
	      {
	        select = req.getParameter("select");
          };
      if (req.getParameter("from") != null)
	      {
	        from = req.getParameter("from");
          };
          if (req.getParameter("progID") != null)
	      {
	        prog = req.getParameter("progID");
          };
          if (req.getParameter("programmeID") != null)
	      {
	        prog = req.getParameter("programmeID");
          };
          if (req.getParameter("database") != null)
	      {
	        database = req.getParameter("database");
          };
          
          if (databaseToUse != null) {
              database=databaseToUse;
          }
      if (req.getParameter("where") != null)
	      {
	        where = req.getParameter("where");
          };
          
          if (prog.equalsIgnoreCase("null")) {
              errorsPresent = true;
      		 errorSB.append(" <br>Null survey chosen, please select from list.");
          }
          if (from == null || from.equalsIgnoreCase("null")) {
             errorsPresent = true;
     		 errorSB.append(" <br>Null table chosen, please select from list.");
          }

     if (ra.length() > 0 & dec.length() > 0 & name.length() > 0)
      {
         errorsPresent = true;
		 errorSB.append(" <br>Only supply coordinates OR a name.");
        }
     if (select.length() ==0 || select.equals("") || select==null)
       {
         errorsPresent = true;
		 errorSB.append(" <br>Please enter some parameters in Select box.");
        }

       if ((radius == null || radius.equals("")) && (xSize == null || xSize.equals(""))){
         errorsPresent = true;
  		 errorSB.append(" <br> No radius or x-size supplied.");
	        }
        if (radius.length() > 0) {
            regionType=1;
		try {
			  if (Float.parseFloat(radius) > 90 || Float.parseFloat(radius) < 0 )
			  {
			      
			      errorsPresent = true;
			  	  errorSB.append(" <br>Radius supplied should be greater than zero and less than 90 arcmin.");
			   }
		   }
	       catch (NumberFormatException e)
	            {
	           errorsPresent = true;
	    	   errorSB.append("<br>Radius supplied was not a number.");
            };
            
        }
        
        if (xSize.length() > 0 && ySize.length() <= 0) {
            errorsPresent = true;
		  	errorSB.append("<br> x-size supplied but not y-size"); 
        }
        if (ySize.length() > 0 && xSize.length() <= 0) {
            errorsPresent = true;
		  	errorSB.append("<br> y-size supplied but not x-size."); 
        }
        
        if (xSize.length() > 0 ) {
            regionType=2;
    		try {
    			  if (Float.parseFloat(xSize) > 180)
    			  {
    			      
    			      errorsPresent = true;
    			  	  errorSB.append(" <br>x-size supplied should be less than 180 arcmin.");
    			   }
    		   }
    	       catch (NumberFormatException e)
    	            {
    	           errorsPresent = true;
    	    	   errorSB.append("<br> x-size supplied was not a number.");
                };
            }
        if (ySize.length() > 0) {
    		try {
    			  if (Float.parseFloat(ySize) > 180)
    			  {
    			      
    			      errorsPresent = true;
    			  	  errorSB.append("<br> y-size supplied should be less than 180 arcmin.");
    			   }
    		   }
    	       catch (NumberFormatException e)
    	            {
    	           errorsPresent = true;
    	    	   errorSB.append("<br> y-size supplied was not a number.");
                };
            }

        if (radius.length() > 0 & (xSize.length() > 0 || ySize.length() > 0))
        {
           errorsPresent = true;
  		 errorSB.append(" <br>Only supply a radius OR box size.");
          }

        
       
       
       if (name.length() > 0){
			  sys="J";
			  String result = null;
			  try {
			      SesameClient sClient=new SesameClient(name);
			      ra=sClient.getRA();
			      dec=sClient.getDec();
			  
		  	  }
		  	  catch (Exception e){
		  	    errorsPresent = true;
		  		errorSB.append("<br> Unable to resolve supplied name into valid RA & Dec. Service unavailable.");
		  		result=null;
						  }
		  	if (result != null) {
		//      String sub = result.substring(result.indexOf("%J")+2,result.indexOf("(",result.indexOf("%J")));
		 //     sub=sub.trim();
		 //     StringTokenizer st = new StringTokenizer(sub);

		      double raValue=-100;
		      double decValue=-100;
		      try
		  	    {
		  	      raValue = Double.parseDouble(ra);
		  	      decValue = Double.parseDouble(dec);
		  	    }
		  	    catch (NumberFormatException nfe)
		  	    {
		  	    errorsPresent = true;
			  	errorSB.append(" Unable to resolve supplied name into valid RA & Dec. Please input coords directly.");
		      }

		      if (errorsPresent==false && (raValue > 360 || raValue < 0 || decValue < -90 || decValue > 90))
		      { 
		        errorsPresent = true;
			  	errorSB.append(" Unable to resolve supplied name into valid RA & Dec. Please input coords directly.");
		   		}
			}
       }	
       
       
         else {
            
			     if (ra == null || ra.equals("") || dec==null || dec.equals("")){  
			     errorsPresent = true;
		  		 errorSB.append("<br>  Check values of supplied coords (blank or null).");
			 }
			 }
     //  System.out.println(regionType);
	 WorldCoords wc =new WorldCoords();
	 
	 if (errorsPresent == false) {
try {
wc = new WorldCoords(Double.parseDouble(ra),Double.parseDouble(dec));
}
catch (Exception e){
	try {
		wc = new WorldCoords(ra,dec);
	}
	catch (Exception ane) {	   
	    errorsPresent = true;
 		errorSB.append(" Unable to parse supplied coords.");
			 }
		 }
	 }

	 if (regionType==0){
	     errorsPresent = true;
	     errorSB.append(" Unspecified region type.");
	 }

	 int progID=-999;
	 if (!prog.equals("")) {
	     try {
	     progID=Integer.valueOf(prog).intValue();
	     if (from.equalsIgnoreCase("source")) {
	     //from=WSASchema.getSourceTableName(progID);
	    	 from=VDFSSchema.getSourceTableName(progID, archiveID);
	     }
	     else if (from.equalsIgnoreCase("sourceView")) {
		    // from=WSASchema.getSourceViewName(progID);
	    	 from=VDFSSchema.getSourceViewName(progID,archiveID);
		     }
	     else if (from.equalsIgnoreCase("detection")) {
		    // from=WSASchema.getDetectionTableName(progID);
	    	 from=VDFSSchema.getDetectionTableName(progID,archiveID);
	     }
	     else {
		     errorsPresent = true;
		     errorSB.append("<br>Invalid table");
	     }
	     }
	     catch (NumberFormatException nfe) {
		     errorsPresent = true;
		     errorSB.append("<br>Invalid survey/programme.");
        }
	 }

	 if (wc.getRaDeg() < 0) {
	     errorsPresent = true;
	     errorSB.append("<br>Supplied RA/Longitude &lt; 0");
	 }
	 if (wc.getRaDeg() > 360) {
	     errorsPresent = true;
	     errorSB.append("<br>Supplied RA/Longitude &gt; 0");
	 }
	 
	 if (wc.getDecDeg() < -90) {
	     errorsPresent = true;
	     errorSB.append("<br>Supplied Dec/Latitude &lt; -90");
	 }
	 if (wc.getDecDeg() > 90) {
	     errorsPresent = true;
	     errorSB.append("<br>Supplied Dec/Latitude &gt; +90");
	 }

	 
	 if (errorsPresent) {
    throw new AnException(errorSB.toString());
}

//System.out.println("here");
Point2D.Double pointJ2000=new Point2D.Double(wc.getX(),wc.getY());
//System.out.println("here1");

//System.out.println("here2");
//System.out.println(regionType);
//System.out.println("here3");
if (sys.equals("G")){
//    System.out.println("here4");
wcscon.gal2fk5(pointJ2000);
}
if (sys.equals("B")){
//    System.out.println("here5");
wcscon.fk425(pointJ2000);
}
//System.out.println("here6");

ra2000=String.valueOf(pointJ2000.getX());
dec2000=String.valueOf(pointJ2000.getY());
//System.out.println("here7");
ra2000=ra2000.replace('+',' ');
dec2000=dec2000.replace('+',' ');
// System.out.println(wc.getX()+" yy "+wc.getY()+" "+ra2000+" :: "+dec2000);
DefaultSelect ds = new DefaultSelect();

//System.out.println(WSASession.getDefaultList(sess,from,database,progID));
select=SQLRegion.replaceall(select,"default",WSASession.getDefaultList(sess,from,database,progID)); // from+".*");

//System.out.println("here");
if (regionType==1){
//    System.out.println("here1");
double radiusD=Double.parseDouble(radius);
TangentRectangle tr= new TangentRectangle(pointJ2000.getX(),pointJ2000.getY(),
        radiusD);

double [] unitV = SLALIB.DCS2C(pointJ2000.getX()*Math.PI/(180.0),pointJ2000.getY()*Math.PI/(180.0));
String cX=String.valueOf(unitV[0]);
String cY=String.valueOf(unitV[1]);
String cZ=String.valueOf(unitV[2]);
String cosRadius=String.valueOf(Math.cos(radiusD*Math.PI/(180.0*60.)));
String minDec=String.valueOf(tr.getMinLatitude());
String maxDec=String.valueOf(tr.getMaxLatitude());
double minRA=tr.getMinLongitude();
String RASQL =tr.getRASQL();

String sqlDistance=
    "2*DEGREES(ASIN(sqrt(power("+cX+"-cx,2)+power("+
    cY+"-cy,2)+power("+cZ+"-cz,2))/2))*60";
       if (where != null && !where.equals("") && !where.equals(" "))
       {
       sql="SELECT  "+ select + ","+sqlDistance+" as distance "+
       " FROM " + from + 
       " WHERE dec > "+minDec+" and dec < "+maxDec +
       " and "+RASQL+
       " and ((cx * "+cX+" + cy * " + cY +" + cz * "+cZ+
       " ) >= "+cosRadius+") "
       + " and "+ where ;
   		}
   		else {
   	       sql="SELECT  "+ select + ","+sqlDistance+" as distance "+
   	       " FROM " + from + 
   	       " WHERE dec > "+minDec+" and dec < "+maxDec+
   	       " and "+RASQL+
   	       " and ((cx * "+cX+" + cy * " + cY +" + cz * "+cZ+
   	       " ) >= "+cosRadius+") ";	
		}
//EXEC dbo.spWebExecuteSQL ' + " '"
}

if (regionType ==2){
    //System.out.println("here2");
    double xSizeD=Double.parseDouble(xSize);
    double ySizeD=Double.parseDouble(ySize);
    double minLat;
    double minLong;
    double maxLat;
    double maxLong;
    String whereRegion=null;
    String latString=null;
    String longString=null;
    TangentRectangle tr=null;
    if (boxAlignment.equalsIgnoreCase("RADec")){
        latString="Dec";
        longString="RA";
        tr= new TangentRectangle(pointJ2000.getX(),pointJ2000.getY(),xSizeD,ySizeD);
    }
    else if (boxAlignment.equalsIgnoreCase("Galactic")){
        latString="b";
        longString="l";
        Point2D.Double pointG;
        pointG=wcscon.fk52gal(pointJ2000);
        tr= new TangentRectangle(pointG.getX(),pointG.getY(),xSizeD,ySizeD);
    }
    
        minLat=tr.getMinLatitude();
        maxLat=tr.getMaxLatitude();
        minLong=tr.getMinLongitude();
        maxLong=tr.getMaxLongitude();
        if (minLong <= maxLong) {
            whereRegion= "( "+longString+" >= " + minLong + " and " +
            longString+" <= " + maxLong + " and " +
            latString+" >= " + minLat + " and " +
            latString+" <= " + maxLat + " ) ";
            
        }
        else if (minLong > maxLong) {
            whereRegion= "( ("+longString+" >= " + minLong + " or " +
            longString+" <= " + maxLong + ") and " +
            latString+" >= " + minLat + " and " +
            latString+" <= " + maxLat+ " ) ";
            
        }
        if (where != null && !where.equals("") && !where.equals(" "))
        {
        sql="SELECT  "+ select + 
        " FROM " + from + 
        " WHERE "+whereRegion+
        " and "+ where ;
    		}
    		else {
    	       sql="SELECT  "+ select + 
    	       " FROM " + from + 
    	       " WHERE "+whereRegion;	
 		}
        
    
}
return sql;

	}

	public String getRA()
	{
		return ra;
	}
	public String getDec()
	{
		return dec;
	}
	public String getSys()
	{
		return sys;
	}
	public String getRadius()
	{
		return radius;
	}
	public String getFrom()
	{
		return from;
	}

	   public static String replaceall (String orig, String from, String to)
	    {
	        int fromLength = from.length();

	        if (fromLength==0)
	            throw new IllegalArgumentException
	            ("String to be replaced must not be empty");

	        int start = orig.indexOf (from);
	        if (start==-1)
	            return orig;

	        boolean greaterLength = (to.length() >= fromLength);

	        StringBuffer buffer;
	        // If the "to" parameter is longer than (or
	        // as long as) "from", the final length will
	        // be at least as large
	        if (greaterLength)
	        {
	            if (from.equals (to))
	                return orig;
	            buffer = new StringBuffer(orig.length());
	        }
	        else
	        {
	            buffer = new StringBuffer();
	        }

	        char [] origChars = orig.toCharArray();

	        int copyFrom=0;
	        while (start != -1)
	        {
	            buffer.append (origChars, copyFrom, start-copyFrom);
	            buffer.append (to);
	            copyFrom=start+fromLength;
	            start = orig.indexOf (from, copyFrom);
	        }
	        buffer.append (origChars, copyFrom, origChars.length-copyFrom);

	        return buffer.toString();
	    }

}