/*
 * Created on 18-Oct-2005
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */

package uk.ac.roe.wfau;
import java.util.regex.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;



import net.alanmaxwell.sql.WSASQLRetrieverThread;

/**
 * @author mar
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */



public class SQLMethods {
    static String doubleQuotes="\"";
    private static int MAXLENGTH=20000;
    private static int WEBQUERIESLENGTH=4096;
    static String [] COLOURS ={"R","G","B"};
    
    /**
     * @param raD degrees
     * @param decD degrees
     * @param rD arcminutes
     * @return
     */
    
    public static String getGCDistanceSQL(double raD, double decD, double rD) {
    	StringBuffer GCDistanceSQL=new StringBuffer("60.0*2.0*degrees(asin(sqrt(square(sin(radians(0.5*("+decD+"-dec))))" +
    			"+ cos(radians("+decD+"))*cos(radians(dec))" +
    					"*square(sin(radians(0.5*("+raD+"-ra)))))))");
    	/*
    	 *  60.0*2.0*degrees(asin(sqrt(square(sin(radians(0.5*(@dec1-@dec2)))) + cos(radians(@dec1))*cos(radians(@dec2))* 
square(sin(radians(0.5*(@ra1-@ra2)))))))
    	 */
    	
    	return GCDistanceSQL.toString();
    }
    public static String getRadialSQL(double raD, double decD, double rD) {
    	double minDec=Math.max(-90.0,decD-rD/60.0);
    	double maxDec=Math.min(90.0,decD+rD/60.0);
    	StringBuffer radialSQL=new StringBuffer("( dec between "+minDec+" and "+maxDec+")");
    	radialSQL.append(" and ("+getGCDistanceSQL(raD, decD, rD)+" <= "+rD);
    	return radialSQL.toString();
    }
    
    public static String getRequiredFiltersSQL(){
        return "select filterid,npass from requiredfilters";   
       }
    public static StringBuffer getFramesetSQL(ResultSet rset,String mergelogTable,String multiframeTable){
        return getFramesetSQL(rset, mergelogTable, multiframeTable, VDFSSchema.WSAARCHIVEID);
    }
    public static StringBuffer getFramesetSQL(ResultSet rset,String mergelogTable,String multiframeTable, int archiveID){
        StringBuffer sbSQL=new StringBuffer("");
        String mfidName=null;
        String extName=null;
        int npass;
        int filterid;
        int row=0;
        boolean first=true;
       
        try {
        while (rset.next()){
            row++;
           filterid=rset.getInt("filterid");
           npass=rset.getInt("npass");
           if (npass==1) {
               mfidName=VDFSSchema.getFilterName(filterid,archiveID)+"mfid";
               extName=VDFSSchema.getFilterName(filterid,archiveID)+"enum";
               if (!first) {
                   sbSQL.append(" or ");
               }
               first=false;
               sbSQL.append("("+mergelogTable+"."+mfidName+"="+multiframeTable+".multiframeid and "+
            		   mergelogTable+"."+extName+"=mfd.extnum) ");
   
           }
           else if (npass > 1){
               for (int i=0; i < npass; i++){
                   mfidName=VDFSSchema.getFilterName(filterid,archiveID)+"_"+(i+1)+"mfid";
                   extName=VDFSSchema.getFilterName(filterid,archiveID)+"_"+(i+1)+"enum";
                   if (!first) {
                       sbSQL.append(" or ");
                   }
                   first=false;
                   sbSQL.append("("+mergelogTable+"."+mfidName+"="+multiframeTable+".multiframeid and "+
                   mergelogTable+"."+extName+"=mfd.extnum) ");  
               }
           }
        }
        if ( row > 0 ){
        return sbSQL;
        }
        else {
            return null;
        }
        }
        catch (SQLException se){
            return null;
        }
        
    }
   
    public static StringBuffer getFramesetSQL(ResultSet rset,String mergelogTable,String multiframeTable, int archiveID, String framesetID){
        StringBuffer sbSQL=new StringBuffer("");
        String mfidName=null;
        String extName=null;
        int npass;
        int filterid;
        int row=0;
        boolean first=true;
       
        try {
        while (rset.next()){
            row++;
           filterid=rset.getInt("filterid");
           npass=rset.getInt("npass");
           if (npass==1) {
               mfidName=VDFSSchema.getFilterName(filterid,archiveID)+"mfid";
               extName=VDFSSchema.getFilterName(filterid,archiveID)+"enum";
               if (!first) {
                   sbSQL.append(" union ");
               }
               first=false;
               sbSQL.append("select "+mfidName+" as mfid,"+extName+" as eNum from "+ mergelogTable+" where framesetID= "+framesetID);
           }
           else if (npass > 1){
               for (int i=0; i < npass; i++){
                   mfidName=VDFSSchema.getFilterName(filterid,archiveID)+"_"+(i+1)+"mfid";
                   extName=VDFSSchema.getFilterName(filterid,archiveID)+"_"+(i+1)+"enum";
                   if (!first) {
                       sbSQL.append(" union ");
                   }
                   first=false;
                   sbSQL.append("select "+mfidName+" as mfid,"+extName+" as eNum from "+ mergelogTable+" where framesetID= "+framesetID);    
               }
           }
        }
        if ( row > 0 ){
        return sbSQL;
        }
        else {
            return null;
        }
        }
        catch (SQLException se){
            return null;
        }
        
    }
   
    
    public static String getLoginDetailsSQL(String category,String emailAddress,String archive){
        if (archive !=null && archive.equalsIgnoreCase(Archive.VSA)) {
            return null ;
        }
        else {
            if (category != null && category.equalsIgnoreCase("ukidss")) {
                return "select username,password,community from wsa_users where email like '"+emailAddress.trim()+"'" ;
            }
            else if (category != null && category.equalsIgnoreCase("nonsurvey")) {
                return "select username,password,community from wsa_nonSurveyUsers where email like '"+emailAddress.trim()+"'" ;
            }
            return null;    
            
        }
    }
    
    public static String getNSDBs (String user, int archiveID) {
        if (archiveID==VDFSSchema.VSAARCHIVEID) {
            return getProjectDBs (user);
        }
        else {
        return getNSDBs (user);
        }
    }
    
    public static String getProjectDBs (String user) {
    	if (!user.equalsIgnoreCase("vvv")) {
        return "select name from master.dbo.sysdatabases where name like '"+user+"v2%'  order by name desc";
    }
        else {
        	return "select name from master.dbo.sysdatabases where name like '"+user+"%'  order by crdate desc";
        }
        	
    }
    public static String getNSDBs (String user, int archiveID, String community) {
    	if (archiveID==VDFSSchema.GESARCHIVEID) {
    	if (community.toLowerCase().indexOf("beta") >= 0) {
        return "select name from master.dbo.sysdatabases where name like '"+user+"%' or name like'"+user+"' order by name desc";
    	}
    	else {
    		return "select name from master.dbo.sysdatabases where (name like '"+user+"%' or name like'"+user+"') and name not like '%beta%' order by crdate desc";	
    	}
    	}
    	else {
    		if (community.toLowerCase().indexOf("beta") >= 0) {
    	        return "select name from master.dbo.sysdatabases where name like '"+user+"v%' or name like'"+user+"' order by name desc";
    	    	}
    	    	else {
    	    		return "select name from master.dbo.sysdatabases where (name like '"+user+"v%' or name like'"+user+"') and name not like '%beta%' order by name desc";	
    	    	}
    	}
    }
    public static String getNSDBs (String user) {
        return "select name from master.dbo.sysdatabases where name like '"+user+"v2%' or name like'"+user+"' order by name desc";
    }
  /*  public static String getNSFilterIDSQL (String database, String progID) {
        return "select filterID,npass from "+database+"..programme as p,"+database+"..requiredfilters as rf " +
        		"where p.programmeid=rf.programmeid and p.programmeid = "+progID;
    }
    */
    public static String getNSFilterIDSQL (String database, String user) {
        return "select filterID,npass from "+database+"..programme as p,"+database+"..requiredfilters as rf " +
        		"where p.programmeid=rf.programmeid and detectionTable = '"+user+"Detection'";
    }
    
    public static String getNSFilterIDSQL (String database) {
        return "select filterID,npass from "+database+"..programme as p,"+database+"..requiredfilters as rf " +
        		"where p.programmeid=rf.programmeid and p.programmeid > 1000";
    }
    public static String getUpdateQStatusSQLURL(int qID, int qStatus, String resultsURL){
        return "exec userInterface..spUpdateQStatusAndURL "+qID+","+qStatus+",'"+resultsURL+"'";
    }
    public static String getUpdateQStatusSQL(int qID, int qStatus){
        return "exec userInterface..spUpdateQStatus "+qID+","+qStatus;
    }
    
    public static String checkQueryPlan(Statement stmt, String SQL) throws SQLException {
        StringBuffer warnings=new StringBuffer("");
        stmt.executeUpdate("SET SHOWPLAN_ALL ON");
        ResultSet rs=stmt.executeQuery(SQL);
        ResultSetMetaData rsmd=rs.getMetaData();
        int noCols =rsmd.getColumnCount();
        
        long maxRows=0;
        /*
        int er=0;
        for (int i=1; i<= noCols; i++) {
            if (rsmd.getColumnName(i).equalsIgnoreCase("estimaterows")) {
                er=i;
            }
                
        }
        */
        while (rs.next()) {
             if (rs.getLong("estimaterows") > maxRows) {
                 maxRows=rs.getLong("estimaterows");
             }
      //       NO STATS:([dxsMergeLog].[jmfID], [dxsMergeLog].[jeNum], [dxsMergeLog].[kmfID], [dxsMergeLog].[keNum])
           if (rs.getString("warnings")!=null ) {
               if (rs.getString("warnings").indexOf("NO STATS") >= 0) {
                   QueryRetrieverThread.logger.info("MISSING STATS: "+rs.getString("warnings"));
               }
               else {
                warnings.append(" Warning: [");
                warnings.append(rs.getString("warnings"));
                warnings.append("] \n");
               }
               }
            
           }
        stmt.executeUpdate("SET SHOWPLAN_ALL OFF");
        if ( (maxRows > 100000000 && !SQL.matches("(?i).*count.*") ) || maxRows > 10000000000L) {
            warnings.append(" Warning: [Part of query estimated to return ");
            warnings.append(maxRows);
            warnings.append(" rows, check you have joined the tables in your query?] ");
        }
        if (warnings.length() > 1) {
        return warnings.toString();
        }
        else {
            return null;
        }
    }
    
    public static void checkValidSQL(Statement stmt, String SQL) throws SQLException {
        stmt.executeUpdate("set noexec on");
        stmt.executeUpdate(SQL);
        stmt.executeUpdate("set noexec off");
        stmt=null;
    }
    public static void checkValidSQL(Connection conn, String SQL) throws SQLException {
        Statement	 stmt = conn.createStatement();
        stmt.executeUpdate("set noexec on");
        stmt.executeUpdate(SQL);
        stmt.executeUpdate("set noexec off");
        stmt=null;
        conn=null;
    }
    public static ResultSetMetaData browseSQL(Connection conn, String SQL) throws SQLException {
        try {
            int commentPos=SQL.indexOf("--");
            if (commentPos < 0) {
                commentPos=SQL.length();
            }
        String pSQL=SQL.substring(0,commentPos);
        if (!pSQL.matches("(?i).*amenhotep\\..*") &&
                !pSQL.matches("(?i).*thutmose\\..*") &&
                        !pSQL.matches("(?i).*sql6df\\..*" )) {
             pSQL=pSQL+" for browse" ;
                }
        PreparedStatement pstmt = conn.prepareStatement(pSQL);// +" for browse");
        conn=null;        
        return pstmt.getMetaData();
        }
        catch (Exception e) {
            WSASQLRetrieverThread.logger.error(e);
            return null;
        }
    }
    public static String parseSQL(String SQL) throws Exception {
        return parseSQL(SQL,MAXLENGTH);
    }
    public static String parseSQL(String SQL,int maxLength) throws Exception {
        boolean parsed=true;
        String parsedSQL=SQL.trim();
        parsedSQL=parsedSQL.replaceAll("\r?\n"," "); // replace line feeds & extra white space
        //parsedSQL=parsedSQL.replaceAll("\r?\n|\\s+"," "); // replace line feeds & extra white space
        //parsedSQL=parsedSQL.replaceAll("\\s+"," "); // extra white space
        parsedSQL=parsedSQL.replace('"','\''); // replace double quotes with singles as initial users have got used to using doubles
        StringBuffer sb=new StringBuffer("");
        

        parsedSQL=parsedSQL.replaceAll("(?i)\\sxp_|\\ssp_|\\sfn_|\\sms_|\\sdt_|\\.xp_|\\.sp_|\\.fn_|\\.ms_|\\.dt_"," as#"); // case insensitive replace 
        //parsedSQL=parsedSQL.replaceAll("(?i)\\.xp_|\\.sp_|\\.fn_|\\.ms_|\\.dt_"," as#"); // case insensitive replace
        
        //System.out.print(SQL);
        
        
        if (SQL.length() > maxLength) {
            sb.append("SQL query string length greater than "+maxLength+" characters.");
            parsed=false;
        }
        
        if (parsedSQL.toLowerCase().indexOf("with") != 0 && parsedSQL.toLowerCase().indexOf("select") != 0 && parsedSQL.toLowerCase().indexOf("exec userinterface..") !=0) {
            sb.append("Query must start with a select statement");
            parsed=false;
        }
        if (!parsed) {
            throw new Exception(sb.toString());
        }
        else {
        return parsedSQL;
        }
    }
    
    public static String getWebqueriesString(String SQLQuery,int row, long elapsedTime, String user, String ipAddress){
            return "exec dbo.spUpdateWebqueries  '"+SQLQuery.substring(0,Math.min(SQLQuery.length(),WEBQUERIESLENGTH)).replaceAll("\r?\n"," ").replaceAll("'","''")+" ',"+row+","+elapsedTime+",'"+
        user+"','"+ipAddress+"'";
    }

    public static String getWebqueriesString(String SQLQuery,int row, long elapsedTime, String user, String ipAddress, String dbName){
 return "exec userInterface..spUpdateWebqueries  '"+SQLQuery.substring(0,Math.min(SQLQuery.length(),WEBQUERIESLENGTH)).replaceAll("\r?\n"," ").replaceAll("'","''")+" ',"+row+","+elapsedTime+",'"+
        user+"','"+ipAddress+"','"+dbName+"'";
    }
    public static String getWebqueriesString(String SQLQuery,int row, long elapsedTime, String user, String ipAddress, String dbName, int row_size){
        return "exec userInterface..spUpdateWebqueries  '"+SQLQuery.substring(0,Math.min(SQLQuery.length(),WEBQUERIESLENGTH)).replaceAll("\r?\n"," ").replaceAll("'","''")+" ',"+row+","+elapsedTime+",'"+
               user+"','"+ipAddress+"','"+dbName+"',"+row_size;
           }
    
    public static String getWSACrossIDSQL(String select, String tableName, String colID,String extraFrom, String extraWhere) {
        return "SELECT #upload.*,#proxtab.distance,"+select+" \nFROM  #upload left outer join #proxtab on #upload.upload_id=upid " +
		"left outer join "+tableName+" on "+tableName+"."+colID+"=archiveID "+extraFrom+" "+extraWhere+" \norder by upload_id";
    }
    public static String getPhotomSQL(String select, String ra, String dec, int progID) {
        StringBuffer sb=new StringBuffer(select);
        sb.append(",illumfile,catname");
        sb.append(" from Multiframe as mf, Multiframedetector as mfd, currentastrometry as ca, filter as f, ");
        sb.append(WSASchema.getMergeTableName(progID)+" as ml ");
        sb.append(" where mf.multiframeId=mfd.multiframeId and mf.multiframeId=ca.multiframeId and mfd.extNum=ca.extNum and f.filterid=mf.filterid ");
        sb.append(" and (("+ra+" >= minRA and "+ra +" <= maxRA)" +
            " or ("+ra+" + 360.0 >= minRA and "+ra+" + 360.0 <= maxRA))" +
            " and ( "+dec+" >= minDec and "+dec+" <= maxDec)");
        String [] bands = WSASchema.getBands(progID);
        sb.append(" and (");
        for (int i=0; i<bands.length; i++) {
            sb.append(" (ml."+bands[i]+"mfid=mfd.multiframeId and ml."+bands[i]+"enum=mfd.extnum) ");
            if (i < bands.length-1) {
                sb.append(" or ");
            }
        }
        sb.append(") order by mf.filterID");
        return sb.toString();
    
   
    }
    public static String getColourRegionSQL(String raL, String raH, String decL, String decH, String [] RGB, String mergelog) {
    	return getColourRegionSQL(raL,  raH,  decL,  decH,  RGB,  mergelog, VDFSSchema.WSAARCHIVEID);
    }
    public static String getColourRegionSQL(String raL, String raH, String decL, String decH, String [] RGB, String mergelog,int archiveID) {
        /*
         * dbcc dropcleanbuffers

select 
--
minra,maxra,mindec,maxdec,
filterid,filename,rabase,decbase,yenum,henum,kenum
from currentastrometry as ca 
--with (index=0)
,lasmergelog as ml,multiframe as mf  where
ymfid > 0 and hmfid > 0 and kmfid > 0 and
(
(ca.multiframeid=ml.ymfid and ca.extnum=ml.yenum) and
(mf.multiframeid=ml.ymfid or mf.multiframeid=ml.hmfid or mf.multiframeid=ml.kmfid)
)
and 
(
(minRA <= @RAL and maxRA >= @RAL)
or
(minRA >= @RAL and minRA <= @RAH)
or
(minRA <= @RAH and maxRA >= @RAH)
or
(minRA >= @RAL-360 and minRA <= @RAH-360)
or
(minRA <= @RAL+360 and maxRA >= @RAL+360)
)
and 
(
(minDec <= @DECL and maxDec >= @DecL)
or
(minDec >= @DecL and minDec <= @DecH)
)
order by framesetid,filterid

         */
        /*
        StringBuffer sb=new StringBuffer("select filterid,mf.multiframeID,filename,rabase,decbase ");
        for (int i=0; i<RGB.length;i++) {
            sb.append(","+RGB[i]+"MFID,"+RGB[i]+"enum");
        }
        sb.append(" from currentastrometry as ca, multiframe as mf, "+mergelog+ " as ml where ");
        for (int i=0; i<RGB.length;i++) {
            sb.append(RGB[i]+"mfid > 0 and ");
        }
        sb.append("( (ca.multiframeid=ml."+RGB[0]+"mfid and ca.extnum=ml."+RGB[0]+"enum) and");
        sb.append("(mf.multiframeid=ml."+RGB[0]+"mfid or mf.multiframeid=ml."+RGB[1]+"mfid or mf.multiframeid=ml."+RGB[2]+"mfid) ) and (");
        sb.append("(minRA <= "+raL+" and maxRA >= "+raL+") or (minRA >= "+raL+" and minRA <= "+raH+") or (minRA <= "+raH+" and maxRA >= "+raH+") or (minRA >= "+raL+"-360 and minRA <= "+raH+"-360) or (minRA <= "+raL+"+360 and maxRA >= "+raL+"+360) )");
        sb.append(" and ( (minDec <= "+decL+" and maxDec >= "+decL+") or (minDec >= "+decL+" and minDec <= "+decH+") ) order by framesetid,filterid");
        */
        StringBuffer sb=new StringBuffer("select ml.framesetid");
        for (int i=0; i<RGB.length;i++) {
            sb.append(", mf"+COLOURS[i]+".filename as "+COLOURS[i]+"file" );
            sb.append(", mfd"+COLOURS[i]+".extnum as "+COLOURS[i]+"extnum" );
            sb.append(", mf"+COLOURS[i]+".exptime as "+COLOURS[i]+"exptime" );
            if (archiveID != VDFSSchema.OSAARCHIVEID) {
            sb.append(", mf"+COLOURS[i]+".nustep as "+COLOURS[i]+"nustep" );
            }
            else {
            	sb.append(", 1  as "+COLOURS[i]+"nustep" );	
            }
            if (archiveID != VDFSSchema.OSAARCHIVEID) {
            sb.append(", mf"+COLOURS[i]+".njitter as "+COLOURS[i]+"njitter" );
            }
            else {
            	sb.append(", 1 as "+COLOURS[i]+"njitter" );	
            }
            sb.append(", mfd"+COLOURS[i]+".photzpcat as "+COLOURS[i]+"photzpcat " );
            sb.append(", mf"+COLOURS[i]+".filterID as "+COLOURS[i]+"filterID \n" );
            
                   
        }
        sb.append("from ( select framesetid from currentastrometry as ca, "+mergelog+ " as ml where ");
        for (int i=0; i<RGB.length;i++) {
            sb.append(RGB[i]+"mfid > 0 and ");
        }
        sb.append("( (ca.multiframeid=ml."+RGB[0]+"mfid and ca.extnum=ml."+RGB[0]+"enum) and ( \n");
        sb.append("(minRA <= "+raL+" and maxRA >= "+raL+") or (minRA >= "+raL+" and minRA <= "+raH+") or (minRA <= "+raH+" and maxRA >= "+raH+") or (minRA >= "+raL+"-360 and minRA <= "+raH+"-360) or (minRA <= "+raL+"+360 and maxRA >= "+raL+"+360) ) \n");
        sb.append(" and ( (minDec <= "+decL+" and maxDec >= "+decL+") or (minDec >= "+decL+" and minDec <= "+decH+") ) ) )as t , \n");
        sb.append(mergelog+" as ml ");
        for (int i=0; i<RGB.length;i++) {    
            sb.append(" ,multiframe as mf"+COLOURS[i]);
            sb.append(" ,multiframedetector as mfd"+COLOURS[i]);
        }
        sb.append(" where ml.framesetid=t.framesetid ");
        for (int i=0; i<RGB.length;i++) {
            sb.append("  and mf"+COLOURS[i]+".multiframeid=mfd"+COLOURS[i]+".multiframeid ");
            //(ml.kmfid = mfr.multiframeid and ml.kenum=mfdr.extnum)
            sb.append(" and (ml."+RGB[i]+"mfid = mf"+COLOURS[i]+".multiframeid and ml."+RGB[i]+"enum=mfd"+COLOURS[i]+".extnum)");
        }
        return sb.toString();
    }
    public static int getSizeOfRS(ResultSetMetaData rsmd) {
        int recordLength=0;
        
        try {
        	for (int i=0; i<rsmd.getColumnCount(); i += 1) {
        	    //System.out.println(rsmd.getColumnName(i+1)+" "+rsmd.getColumnType(i+1));
        	    
    			switch(rsmd.getColumnType(i+1)){
    				case Types.TINYINT :
    					recordLength+=1;
    				break;
    				case Types.SMALLINT :
    					recordLength+=2;
    				break;
    				case Types.REAL :
    					recordLength+=4;
    					break;
    				case Types.FLOAT :
    					recordLength+=8;
    					break;
    				case Types.DOUBLE :
    					recordLength+=8;
    					break;
    				case Types.INTEGER :
    					recordLength+=4;
    					break;
    				case Types.VARCHAR :
    				    recordLength+=rsmd.getPrecision(i+1);
    					break;
    				case Types.CHAR :
    				    recordLength+=rsmd.getPrecision(i+1);
    					break;
    				case Types.BIGINT :
    				    recordLength+=8;
    					break;
    				case Types.DATE :
    				    recordLength+=8;
    					break;
    				case Types.TIMESTAMP :
    				    recordLength+=8;
    					break;
    				case Types.NUMERIC :
    					recordLength+=8;
    					break;
    				case Types.DECIMAL :
    					recordLength+=8;
    					break;
    				default :
    				    recordLength+=4;
    					break;
    			}
        	}
        }
        catch (Exception e) {
            
        }
        rsmd=null;
        return recordLength;
    }

}