import java.io.*;
import java.net.MalformedURLException;
import java.net.URL;

import java.net.URLConnection;
import java.util.*;
import java.sql.*;
import uk.ac.roe.wfau.StringToRADec;
import javax.servlet.*;
import javax.servlet.http.*;

import uk.ac.roe.wfau.DatabaseURL;
import uk.ac.roe.wfau.WSAHTMLSchema;
import uk.ac.roe.wfau.WSASchema;
import uk.ac.roe.wfau.WSASession;

import com.oreilly.servlet.*;
import com.oreilly.servlet.multipart.*;
import net.mar.DefaultSelect;
import uk.ac.roe.wfau.ImageSelect;
import net.mar.SQLRadial;
import net.alanmaxwell.html.HTMLStrings;
import net.alanmaxwell.sql.SQLServerInfoJDBC;
import net.alanmaxwell.sql.SixSQLRetrieverThread;
import net.alanmaxwell.sql.SQLRetrieverThread6dF;
import org.apache.log4j.Logger;
import org.astrogrid.registry.client.admin.UpdateRegistry;
public class MultiGetImage extends HttpServlet {
    
    /**
     * Log4j logger for record keeping
     */
     private static Logger logger;
     
  
    private int serviceCurrent = 0;

    private int serviceTotal = 0;

    private static final int MAX_LINES = 501;
    private static final int MAX_AREA = 2004; //sq arcmin

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

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

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

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

    private boolean shuttingDown = false;

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

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

    // insert from com Demo

  
    private static String BASESQL_SELECT;
    private static String BASESQL_FROM;
    private static String BASESQL_WHERE;
    private static String WSACGIDIRURL;
    private static String LISTCGIDIRURL;
    private static String defaultUKIDSSDatabase;
    private static String defaultWorldDatabase;
    private static String server;
    private static String webURL;
    private static String tmpSpace;
    private static String listWebURL;
    private static String listTmpSpace;

   

  

    public void init(ServletConfig config) throws ServletException {
        super.init(config);
        logger = Logger.getLogger("wsa.simple");
        logger.debug("initializing MultiGetImage servlet");
        String path = getServletContext().getRealPath("/");
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream(path
                    + "WEB-INF/WSA.properties"));
        } catch (IOException e) {
            logger.error("MultiGetImage, properties file not found");
            return;
           
        }

        
        server = properties.getProperty("server");
        
        tmpSpace = properties.getProperty("MGItmpSpace");
        webURL = properties.getProperty("MGIwebURL");
        listTmpSpace=properties.getProperty("listTmpSpace");
        listWebURL = properties.getProperty("listWebURL");
            
        BASESQL_SELECT = properties.getProperty("MGIBASESQL_SELECT");
        BASESQL_FROM = properties.getProperty("MGIBASESQL_FROM");
        BASESQL_WHERE = properties.getProperty("MGIBASESQL_WHERE");
        defaultUKIDSSDatabase = properties.getProperty("defaultUKIDSSDatabase");
        defaultWorldDatabase = properties.getProperty("defaultWorldDatabase");
        WSACGIDIRURL=properties.getProperty("WSACGIDIRURL");
        LISTCGIDIRURL=properties.getProperty("LISTCGIDIRURL");
        if (WSACGIDIRURL == null) {
            WSACGIDIRURL=WSAHTMLSchema.baseCGIURL;
        }
        try {
           // Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
        } catch (ClassNotFoundException e) {

            System.err
                    .println("SQLRetrieverThread::static(): Could not load SQLServerDriver class!");
        }
    }

    protected void doPost(HttpServletRequest req, HttpServletResponse res)
            throws ServletException, IOException {
        String fileName = null;
        BufferedReader br;

        File savedUploadFile=null;

        PrintWriter out=null;

        Connection con = null;
        
        String email=null;
        String email1=null;
        String database=null;
        String resFile=null;
        String community=null;
        String user=null;
        
        int noRows=0;
        int noBands=0;
        String [] bandArray=new String [10];

        try {
            HttpSession session = req.getSession();
            community=WSASession.getCommunity(session);
            user=WSASession.getUser(session);
            StringBuffer select=null;
            StringBuffer from=null;
            StringBuffer where=null;
            String IPAddress=req.getRemoteAddr();
            String programmeID=null;
            int intProgrammeID=0;
            String band=null;
            String idPresent=null;
            boolean IDPresent=false;
            String crossHair="y";
            String userX=null;
            double dUserX=0.0;
            String action = "";
            String subAction = "";
            String mode="norm";
            String lupPar=null;
            res.setContentType("text/html");
            out = res.getWriter();
            String line=null;
            double [] coords = new double[2];
            long idCol;
            String objName;
            Object [] objs ;
            int numberOfLines = 0;
            boolean errorFlag=false;
            StringBuffer errorMess= new StringBuffer("");
            out.println("<html><head><title>MultiGetImage</title></head><body>");
            int extraLines=0;
   
            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()) {
                        
                        // it's a parameter part
                        ParamPart paramPart = (ParamPart) part;
                        String value = paramPart.getStringValue();

                        if (name.equalsIgnoreCase("database")) {
                            if (value != null) {
                            database = value.trim();
                            }
                            out.println("<p>Database: "+database);
                        }
                        
                        if (name.equalsIgnoreCase("mode")) {
                            if (value != null) {
                            mode = value.trim();
                            }
                            
                        }
                        if (name.equalsIgnoreCase("luptitudePar")) {
                            if (value != null) {
                            lupPar = value.trim();
                            }
                            
                        }
                        if (name.equalsIgnoreCase("programmeID")) {
                            out.println("<p>Programme: ");
                            programmeID = value.trim();
                            try {
                                intProgrammeID=Integer.valueOf(programmeID).intValue();
                                out.println(WSASchema.getSurveyName(intProgrammeID));
                            }
                            catch (Exception e) {
                                errorFlag=true;
                                errorMess.append("<p><b>Invalid survey/progammeID<b>");
                                
                            }
                            
                        }
  
                        if (name.equalsIgnoreCase("band")) {
                            band = value.trim();
                            bandArray[noBands]=band;
                            noBands++;
                            out.println("<p>Waveband: "+band);
                        }
                        if (name.equalsIgnoreCase("idPresent")) {
                            idPresent = value.trim();
                            if (idPresent.equalsIgnoreCase("mfID") || idPresent.equalsIgnoreCase("fsID") ) {
                                IDPresent=true;
                            }
                            if (mode.equalsIgnoreCase("norm")) {
                            out.println("<p>ID column? : "+idPresent);
                            }
                        }
                        if (name.equalsIgnoreCase("userX")) {
                            userX = value.trim();
                            if (mode.equalsIgnoreCase("norm")) {
                            out.println("<p>Size: "+userX);
                            }
                            try {
                            dUserX=Double.valueOf(userX).doubleValue();
                        }
                        catch (Exception e) {
                            errorFlag=true;
                            errorMess.append("<p><b>Invalid size (not a number?)<b>");
                            
                        }
                        }
                        if (name.equalsIgnoreCase("email")) {
                            email = value.trim();
                            out.println("<p>Email: "+email);
                        }
                        if (name.equalsIgnoreCase("email1")) {
                            email1 = value.trim();
                        }
                        if (name.equalsIgnoreCase("crossHair")) {
                            crossHair = value.trim();
                            if (mode.equalsIgnoreCase("norm")) {
                            out.println("<p>Cross hair? : "+crossHair);
                            }
                        }

                        //  out.println("param: name=" + name + "; value=" +
                        // value);
                    } else if (part.isFile()) {

                        // it's a file part

                        FilePart filePart = (FilePart) part;

                        fileName = filePart.getFileName();

                        if (fileName == null) {
                            errorFlag=true;
                            errorMess.append("<p><b>No file name supplied</b>");
                        }

 
                        out.println("<p>Parsing uploaded file ...");
                        out.println("<!--                                                                                      -->");
                        out.println("<!--                                                                                      -->");
                        out.println("<!--                                                                                      -->");
                        out.println("<!--                                                                                      -->");
                        out.println("<!--                                                                                      -->");
                        out.println("<!--                                                                                      -->");
                        out.println("<!--                                                                                      -->");
                        out.println("<!--                                                                                      -->");
                        
                        out.flush();
                        res.flushBuffer();
                        
                        if (fileName != null) {
                            File dir;
                            if (mode.equalsIgnoreCase("list")) {
                            dir = new File(listTmpSpace);
                            extraLines=4500;
                            }
                            else {
                             dir = new File(tmpSpace);
                            }
                            savedUploadFile = File.createTempFile("upload", "tmp",
                                    dir);
                            // out.println("<br>"+savedUploadFile.getAbsolutePath());
                            //out.println("<br>"+savedUploadFile.getName());
                            long size = filePart.writeTo(savedUploadFile);
                     
                            if (size == 0) {
                                errorFlag=true;
                                errorMess.append("<p><b>Upload file contain zero bytes, check filename</b>");
                            }
                           
                            if (!errorFlag) {
                            br = new BufferedReader(new InputStreamReader(
                                    new FileInputStream(savedUploadFile)));
                            //out.println("heres");
                            line="";
                            while ((line=br.readLine()) !=null && numberOfLines < MAX_LINES+extraLines+1) {
                                numberOfLines++;
                        
                                try {
                                StringToRADec.getRADec(line.trim(),IDPresent);
                                
                                }
                                catch (NumberFormatException nfe) {
      
                                    errorFlag=true;
                                    errorMess.append("<br> error reading coords or ID column at line "+numberOfLines);
                                    
                                }
 
                            }
                            
                            br.close();
                            }
                            
                            
                            out.println("<p>"+fileName+" uploaded file size: " + size
                                    + " bytes, " + numberOfLines + " rows");
                            out.flush();
                            
                            if (numberOfLines > MAX_LINES + extraLines) {
                                errorFlag=true;
                                errorMess.append("<p><b>File contained more than "+(MAX_LINES+extraLines)+ " lines</b>");
                            }
                        } 
                    }
                    
                }
                
                out.flush();
                if (noBands < 1 || noBands > 9) {
                    errorFlag=true;
                    errorMess.append("<p><b>Please select 1 or more wavebands: number wavebands selected =</b> "+noBands);  
                }
                if (numberOfLines*dUserX*dUserX > MAX_AREA* 1.0) {
                    errorFlag=true;
                    errorMess.append("<p><b>Total area requested: number objects x size &gt; "+MAX_AREA+" sq arcmin</b>");
                }
                
                
                if (dUserX < 0.05 || dUserX > 12) {
                    errorFlag=true;
                    errorMess.append("<p><b>Size should be &gt; 0.05 arcmin and &lt; 12 arcmin</b>");
                }
                if (email != null && email1 != null) {
                    if (email.length() < 3 || email.indexOf('@') < 0) {
                        errorFlag=true;
                        errorMess.append("<p><b>Invalid email supplied</b>"); 
                    }
                    if (!email.equals(email1)){
                        errorFlag=true;
                        errorMess.append("<p><b>Supplied emails do no match</b>");
                    }
                    
                } else {
                    errorFlag=true;
                    errorMess.append("<p>Null email(s) supplied");
                }
                
                if (errorFlag) {
                    out.print(errorMess+"<p>Use the browser's back button to return to the form");
                    return;
                } 
            
                
                
            } catch (IOException IOEx) {
                out.println(IOEx);
            }
            out.flush();
            try {
                
                String UKIDSSDatabase=defaultUKIDSSDatabase.toString();
                String worldDatabase=defaultWorldDatabase.toString();                
                if (database != null) {
                    UKIDSSDatabase = database;
                    worldDatabase = database;
                }
               
                String durl=DatabaseURL.getDatabaseURL(session,true,server,UKIDSSDatabase,worldDatabase);
                out.println("<p>Connecting to "+DatabaseURL.getDatabaseNameFromURL(durl,true)+" database<p>");
                //out.println(durl);
       
                con = DriverManager.getConnection(durl);
              if (con == null) {
                  out.println("Can't get connection");
                  return;
              }
              
              Statement stmt = con.createStatement();
              out.flush();  
               
                stmt.setQueryTimeout(600);
                boolean somethingChanged = false;
                con.setAutoCommit(false);
                ImageSelect ImgSel = new ImageSelect();
               stmt.executeUpdate("create table #upMultiGetImage (objnum int,userRA float,userDec float,idNum bigInt)");

               br = new BufferedReader(new InputStreamReader(
                       new FileInputStream(savedUploadFile)));

  
               int lineNumber=0;
               double [] RAArray= new double [numberOfLines];
               double [] DecArray= new double [numberOfLines];
               String [] nameArray= new String [numberOfLines];
               out.flush();
               while ((line=br.readLine()) !=null) {
                   
                   try {
                   objs=StringToRADec.getRADec(line.trim(),IDPresent);
                   coords[0]=((Double)objs[0]).doubleValue();
                   coords[1]=((Double)objs[1]).doubleValue();
                   idCol=((Long)objs[2]).longValue();
                   objName=(String)objs[3];
                   if (coords[0] >= 0.0 && coords[0] <= 360.0 && coords[1] >= -90.0 && coords[1] <= 90.0) {
                    //   out.println(objName+":<br>");
                   stmt.addBatch("insert into #upMultiGetImage values ("+
   			            (lineNumber) +","+coords[0]+","+coords[1]+","+idCol+")");
                   
                   RAArray[lineNumber]=coords[0];
                   DecArray[lineNumber]=coords[1];
                   nameArray[lineNumber]=objName;
                   lineNumber++;
                   }
                   }
                   catch (NumberFormatException nfe) {
                       
                   }

               }              
               br.close();
               int [] updateCount=stmt.executeBatch();
               con.commit();
               con.setAutoCommit(true);
               out.println("<p>"+updateCount.length+ " rows uploaded to database" );
               out.flush();
               // out.println("<br> closed "+updateCount.length);
               out.println("<p>Number of wavebands requested: "+noBands );
               if (!mode.equalsIgnoreCase("norm")) {
               out.println("<p>Mode: "+mode);
               }
               String [][][] allStrArray = new String [noBands][][];
               String [][] strArray;
               for (int j=0; j<noBands;j++) {
                   out.println("<p>Querying waveband "+bandArray[j] );
                   out.flush();
               select = new StringBuffer(BASESQL_SELECT);
               if (mode.equalsIgnoreCase("list")) {
                   select.append(",illumfile,catname");
               }
               from = new StringBuffer(BASESQL_FROM);
               where= new StringBuffer(BASESQL_WHERE);
               if (programmeID != null){
                   from.append(", programmeframe as pf ");
                   where.append(" and pf.programmeId="+programmeID+" and mf.multiframeid=pf.multiframeid ");
               }
               if (band != null) {
                   if (community.equalsIgnoreCase("nonsurvey")) {
                       from.append(","+user.trim()+"mergeLog as ml ");  
                   }
                   else {
                   from.append(","+WSASchema.getMergeTableName(intProgrammeID)+" as ml ");
                   }
                   where.append(" and mf.multiframeId=ml."+bandArray[j]+"mfid and ca.extNum="+bandArray[j]+"enum ");
               }
               
               if (idPresent.equals("mfID")){
                   where.append(" and mf.multiframeId=#upMultiGetImage.idNum ");
               }
               if (idPresent.equals("fsID")){
                   where.append(" and ml.framesetId=#upMultiGetImage.idNum ");
               }
               where.append(" ");
               ResultSet rs=stmt.executeQuery("select "+select+" from " + from +" where "+ where +" order by objnum");
               logger.info("MULTIGETIMAGE "+"query ran");
               ResultSetMetaData md = rs.getMetaData();
               boolean check = ImgSel.setRSMD(md);
               
               
               allStrArray[j]=ImgSel.setRS(rs,0.0,0.0,dUserX,dUserX,updateCount.length);
               //allStrArray[j]=strArray;
               }
               
               resFile=savedUploadFile.getAbsolutePath()+".res";
               resFile=resFile.trim();
               BufferedWriter resultsFile = new BufferedWriter(new FileWriter(resFile));
               int offset=2;
               if (mode.equalsIgnoreCase("list")) {
                   offset=0;
               }
                 for (int i=0; i<allStrArray[0].length;i++){
                     for (int j=0; j<noBands;j++) {
                    resultsFile.write(RAArray[i]+","+DecArray[i]);
                     for (int k=0; k < allStrArray[j][i].length-offset;k++){
                         resultsFile.write(","+allStrArray[j][i][k]);
                     }
                     resultsFile.write(",band="+bandArray[j]+",objName="+nameArray[i]+",upID="+(i+1)+"\n");
                     }
                 }
                 
                 
                 resultsFile.close();
                res.flushBuffer();
                noRows=updateCount.length;
                try {
                    URL url=null;
                    if (mode.equalsIgnoreCase("list")) {
                        out.print("<p>Initiating list driven photometry ....<p>");
                        url = new URL (LISTCGIDIRURL+"submit_listDrivenPhotometry.jsp?file="+resFile+"&email="+email+
                                "&noRows="+updateCount.length+"&filename="+fileName+"&progID="+programmeID+"&luptitudePar="+lupPar);
                        logger.info("MultiGetImage: listurl "+url.getFile());
                        
                    }
                    else {
                    out.print("<p>Initiating image extraction ....<p>");
                    StringBuffer bandStrBuff=new StringBuffer("&band=");
                    for (int j=0; j<noBands;j++) {
                        bandStrBuff.append(bandArray[j]+":");
                    }
                url = new URL (WSACGIDIRURL+"MultiGetImageParent.cgi?file="+resFile+"&email="+email+"&idPresent="+idPresent+
                        "&noRows="+updateCount.length+"&size="+userX+bandStrBuff.toString()+"&crossHair="+crossHair+"&survey="+WSASchema.getSurveyName(intProgrammeID).trim().replace(' ','_')+
                        "&filename="+fileName);
               // out.print(url);
                logger.info("MultiGetImage: cgiurl "+url.getFile());
                    }
                URLConnection uc = url.openConnection();
                InputStream content = (InputStream)uc.getInputStream();
                BufferedReader in   = 
                  new BufferedReader (new InputStreamReader (content));
                String wLine;
                while ((wLine = in.readLine()) != null) {
                  out.println (wLine);
                }
              } catch (MalformedURLException e) {
                out.println ("Invalid URL");
              } catch (IOException e) {
                out.println ("<b>Error reading URL</b>");
              }


                ImgSel=null;
                allStrArray=null;
                strArray=null;
                
                stmt.close();
                con.close();
               // out.println("<p>Committing");
                
                if (somethingChanged) {
                    out.println("<p>Updates committed");
                }
            }

            catch (Exception e) {
                    
                    if (e instanceof SQLException) {
                        out.print("<p>Database error " + e);
                    }
                    logger.error("MULTIGETIMAGE "+e);
            }
            if (resFile != null) {
                String dirname=resFile.substring(resFile.indexOf("/upload")+1,resFile.length());
                dirname=dirname.replaceAll(".res","")+"_"+noRows+"dir";
                out.println("<p><b>If no errors were reported above, on completetion you will be emailed the URL of the results.</b>");
                
                if (mode.equalsIgnoreCase("norm")) {
                    out.println("<p>(If accessing your email is not convenient the following link can be used to check the status of your request <a href=\""+WSACGIDIRURL+
                        "MultiGetImageCheck.cgi?path="+dirname+"\">MultiGetImage Status Check</a>)");
                }
            
            }
            out.println("<p>Use the browser's back button to return to the form</body></html>");
        } catch (Exception e) {
            out.println(e);
            logger.error("MULTIGETIMAGE "+e);
            e.printStackTrace();
            res.sendError(res.SC_INTERNAL_SERVER_ERROR, e + "");
            return;
        }
        
        finally {
            logger.info("MULTIGETIMAGE finally");
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException sqlEx) {
                }
                System.gc();
            }
        }
    }; // END of doGet()

    protected void doGet(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...
        doPost(req, res);
    }; // END of doPost()

    protected boolean verifyString(String str,int maxSize){
        if (str != null && str.length() <= maxSize && !str.equals("")){
            return true;
        }
        return false;
    }
};