/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package eu.dnetlib.espas.spatial.utils;

import eu.dnetlib.espas.gui.shared.User;
import eu.dnetlib.espas.spatial.QShape;
import eu.dnetlib.espas.spatial.QueryCRS;
import eu.dnetlib.espas.spatial.shared.SpatialQueryStatus;
import eu.dnetlib.espas.spatial.TimePeriodConstraint;
import eu.dnetlib.espas.util.MetadataHandler;
import java.io.File;
import java.io.FileFilter;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.LinkOption;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.LinkedList;
import java.util.List;
import java.util.concurrent.TimeUnit;
import java.util.logging.Level;
import javax.sql.DataSource;
import org.apache.log4j.Logger;
import org.joda.time.DateTime;
import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.transaction.annotation.Transactional;

/**
 *
 * @author gathanas
 */
@Transactional(readOnly = false)
public class QueryDBUtils {
/**
 *
select observationid, st_asewkt(spatial_query.sq_179100002.location)as GSE, st_asewkt(public.geovertextent.geometryextent) as GEO, 
public.geovertextent.geometryextent && st_3DMakeBox(st_MakePoint(20.0, 37.0, 6371000000), st_MakePoint( 25.0, 39.0, 6371000000)) as GEOOver,
spatial_query.sq_179100002.location::geometry && st_3DMakeBox(st_SetSRID(st_MakePoint(-2.4917535619590526E9, 4.608861767171387E8, 5.845362409523932E9),94978), st_SetSRID(st_MakePoint(-2.4917613887732525E9, 4.60887621468693E8, 5.845380757649216E9),94978))::geometry as GSEOver 
from spatial_query.sq_179100002 join public.observation_extent on (spatial_query.sq_179100002.observationid = public.observation_extent.observation) join public.geovertextent on (public.observation_extent.geovertextent=public.geovertextent.id);

 */
   private final static Logger _logger = Logger.getLogger(QueryDBUtils.class);
   private final static int LIST_BATCH = 1000;

   private static final String PERIODIC_STATIC_INSTRUMENT_OBSERVATION_Q = 
           "select distinct (views.observation.id) , views.observation.startdate , views.observation.enddate , views.observation.temporalresolution, ST_AsGML(3,views.location.location) as instrumentlocation, views.location.srsname as instrumentsrsname,ST_AsGML(3,public.geovertextent.geometryextent) as observationlocation,public.geovertextent.srsname as observationsrsname"
      + " from views.observation left join views.observation_location on (views.observation.id=views.observation_location.observation) "
      + " left outer join public.observation_extent on (public.observation_extent.observation=views.observation.id) left join views.location on (views.observation_location.location=views.location.id) "
      + " join public.geovertextent on (public.observation_extent.geovertextent=public.geovertextent.id) "            
      + " where not exists (select observation_context.observation from observation_context where views.observation.id = observation_context.observation "
      + " and observation_context.role like '%satellite%') "
      + " and views.observation.temporalresolution is NOT NULL "
      + " and (views.observation.startdate, views.observation.enddate) OVERLAPS (? ::TIMESTAMP WITH TIME ZONE, ? ::TIMESTAMP WITH TIME ZONE )";

   private static final String NON_PERIODIC_STATIC_INSTRUMENT_OBSERVATION_Q = "select distinct (views.observation.id) , views.observation.startdate , views.observation.enddate , views.observation.temporalresolution, ST_AsGML(3,views.location.location) as instrumentlocation, views.location.srsname as instrumentsrsname,ST_AsGML(3,public.geovertextent.geometryextent) as observationlocation,public.geovertextent.srsname as observationsrsname" 
      + " from views.observation left join views.observation_location on (views.observation.id=views.observation_location.observation) " 
      + " left outer join public.observation_extent on (public.observation_extent.observation=views.observation.id) left join views.location on (views.observation_location.location=views.location.id) "
      + " join public.geovertextent on (public.observation_extent.geovertextent=public.geovertextent.id) "            
      + " where not exists (select observation_context.observation from observation_context where views.observation.id = observation_context.observation "
      + " and observation_context.role like '%satellite%') "
      + " and views.observation.temporalresolution is NULL "
      + " and (views.observation.startdate, views.observation.enddate) OVERLAPS (?::TIMESTAMP WITH TIME ZONE , ?::TIMESTAMP WITH TIME ZONE )";

    private static final String MOVING_INSTRUMENT_OBSERVATION_Q = "select views.observation.id, views.observation.startdate, views.observation.enddate, views.platform.title, views.platform.shortlabel" 
      + " from views.observation join views.observation_platform on (views.observation.id=views.observation_platform.observation) join views.platform on (views.observation_platform.platform=views.platform.id) " 
      + " where views.platform.type like '%Satellite%'"
      + " and (views.observation.startdate, views.observation.enddate) OVERLAPS (?::TIMESTAMP WITH TIME ZONE , ?::TIMESTAMP WITH TIME ZONE )";

    private static final String UNIQUE_MOVING_INSTRUMENT_Q = "select distinct views.platform.title, views.platform.shortlabel" 
      + " from views.observation join views.observation_platform on (views.observation.id=views.observation_platform.observation) join views.platform on (views.observation_platform.platform=views.platform.id) " 
      + " where views.platform.type like '%Satellite%'"
      + " and (views.observation.startdate, views.observation.enddate) OVERLAPS (?::TIMESTAMP WITH TIME ZONE , ?::TIMESTAMP WITH TIME ZONE )";

   private static final String SPATIAL_QUERY_STATUS = "select status, statusreport, lastupdatedon, expirationdate from spatial_query.squery where queryid=? and userid=?";
   private static final String SPATIAL_QUERY_EXPIRED_REQUESTS="select queryid from spatial_query.squery where expirationdate<=current_timestamp and status!='EXPIRED'";

   private static final String SPATIAL_QUERY_I = "insert into spatial_query.squery(queryid, userid, crs, squery, timeconstraint,status,statusreport,expirationdate,creationdate,lastupdatedon) values(?, ?, ?, ?, ?,?,?,?,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)";

   private static final String SPATIAL_QUERY_CREATE_TABLE = "create table  IF NOT EXISTS -1- (id SERIAL, queryid TEXT NOT NULL,observationid TEXT NOT NULL, type TEXT NOT NULL,starttimepoint TIMESTAMP WITH TIME ZONE NOT NULL, endtimepoint TIMESTAMP WITH TIME ZONE NOT NULL,locationtxt TEXT NOT NULL, location GEOMETRY, constraint -2-_pm_key PRIMARY KEY (id))";
   private static final String SPATIAL_QUERY_MASS_INSERT = "insert into -tname- (id, queryid, observationid, type, starttimepoint, endtimepoint, location, locationtxt) VALUES ";
   private static final String SPATIAL_QUERY_MASS_ELIMINATE_DOUBLES= "delete FROM -tname- as sp_original USING -tname- as sp_table WHERE sp_original.queryid = sp_table.queryid " +
                                                                     " AND sp_original.observationid = sp_table.observationid " +
                                                                     " AND sp_original.type = sp_table.type " +
                                                                     " AND sp_original.starttimepoint = sp_table.starttimepoint " +
                                                                     " AND sp_original.endtimepoint = sp_table.endtimepoint " +
                                                                     " AND sp_original.locationtxt = sp_table.locationtxt " +
                                                                     " AND sp_original.id < sp_table.id";

   private static final String SPATIAL_QUERY_MASS_UPDATE= "update -tname- set location=ST_GeomFromGML(locationtxt) where locationtxt not like '%NaN%' and locationtxt like '%srsName=\"EPSG%'";
   private static final String SPATIAL_QUERY_MASS_INSERT_RESULT = "insert into -tname- (queryid, observationid, type, starttimepoint, endtimepoint, location) "
                                                                  + "select distinct queryid, observationid, type, starttimepoint, endtimepoint, location from -qtname- where ";
   private static final String SPATIAL_QUERY_CREATE_RESULT_TABLE = "create table IF NOT EXISTS -tname- (queryid TEXT NOT NULL,observationid TEXT NOT NULL, type TEXT NOT NULL,starttimepoint TIMESTAMP WITH TIME ZONE NOT NULL, endtimepoint TIMESTAMP WITH TIME ZONE NOT NULL, location GEOMETRY NOT NULL, constraint -tname2-_pm_key PRIMARY KEY (queryid,observationid,type,starttimepoint,endtimepoint,location))";
   private static final String SPATIAL_COPY_MASS_INSERT = "COPY -tname- (queryid, observationid, type, starttimepoint, endtimepoint, locationtxt) from stdin with delimiter ';'";
   
   private static final String UPDATE_SPATIAL_QUERY_STATUS ="update spatial_query.squery set status=? ,statusreport=? ,lastupdatedon=CURRENT_TIMESTAMP where queryid=?";
   private static final String CLEANUP_SPATIAL_QUERIES="select spatial_query.cleanupexpiredquery(?)";

    private static final String UPDATE_SPATIAL_QUERY_NOTIFICATION = "update spatial_query.squery set emailnotified=? where queryid=?";
    private static final String SPATIAL_QUERY_GET_UNOTIFIED = "select queryid, userid, statusreport, status, expirationdate from spatial_query.squery where userid not like 'guest' and emailnotified='FALSE' and (status='COMPLETED' OR status='FAILED')";
    private static final String SPATIAL_QUERY_REQUEST_USER ="select espasuser.email, espasuser.name from espasuser where espasuser.email =?";

   private DataSource spDBSource;
   private int expirationDateOffset=15;
   private String databaseURL="jdbc:postgresql://espas-srv.stp.rl.ac.uk:5432/dnet_espas";
   private String databaseUsername="dnet";
   private String databasePassword="dnetPwd";
   
   private String tempDataFileStore="/tmp/espas/data/";

   private static QueryDBUtils queryDBUtil;
   
   public static QueryDBUtils getQueryDBInstance(){
       return queryDBUtil;
   }
   
   public QueryDBUtils() {
   }

   ///////////////////////////////////////////
   //       Spring initialization code
   
   public void init(){
       queryDBUtil = this;
   }
   
   public DataSource getSpDBSource() {
      return spDBSource;
   }

   public void setSpDBSource(DataSource spDBSource) {
      this.spDBSource = spDBSource;
   }

   public String getTempDataFileStore() {
      return tempDataFileStore;
   }

   public void setTempDataFileStore(String tempDataFileStore) {
      this.tempDataFileStore = tempDataFileStore;
   }

   public int getExpirationDateOffset() {
      return expirationDateOffset;
   }

   public void setExpirationDateOffset(int expirationDateOffset) {
      this.expirationDateOffset = expirationDateOffset;
   }

    public String getDatabaseURL() {
        return databaseURL;
    }

    public void setDatabaseURL(String databaseURL) {
        this.databaseURL = databaseURL;
    }

    public String getDatabaseUsername() {
        return databaseUsername;
    }

    public void setDatabaseUsername(String databaseUsername) {
        this.databaseUsername = databaseUsername;
    }

    public String getDatabasePassword() {
        return databasePassword;
    }

    public void setDatabasePassword(String databasePassword) {
        this.databasePassword = databasePassword;
    }

   
   /////////////////////////////////////
//   db fields to insert : queryid, userid, crs, squery, timeconstraint,status,statusreport
   public void registerSpatialQuery(String uniqQueryId, QShape querySpace, QueryCRS crs, TimePeriodConstraint timeConstraint, String userid) {
      Connection downloadDBConnection = null;
      PreparedStatement prStatement = null;
      PreparedStatement createTableStatement = null;
      
      Calendar expiresOnCalendar = new GregorianCalendar();
      expiresOnCalendar.add(Calendar.DAY_OF_MONTH, this.getExpirationDateOffset());

      try {
         downloadDBConnection = DataSourceUtils.getConnection(spDBSource);
         prStatement = downloadDBConnection.prepareStatement(SPATIAL_QUERY_I);
         prStatement.clearParameters();
         prStatement.setString(1, uniqQueryId);
         prStatement.setString(2, userid);
         prStatement.setString(3, crs.name());
         prStatement.setString(4, querySpace.getQueryString());
         prStatement.setString(5, timeConstraint.toString());
         prStatement.setString(6, SpatialQueryStatus.QueryStatus.SUBMITTED.name());
         prStatement.setString(7, "");
         prStatement.setTimestamp(8, new Timestamp(expiresOnCalendar.getTimeInMillis()));
         prStatement.execute();
         prStatement.close();

         createTableStatement = downloadDBConnection.prepareStatement(SPATIAL_QUERY_CREATE_TABLE.replace("-1-", "spatial_query."+uniqQueryId).replace("-2-", uniqQueryId));
         createTableStatement.execute();
         createTableStatement.close();

         DataSourceUtils.releaseConnection(downloadDBConnection,spDBSource);
      } catch (Exception ex) {

         if (prStatement != null)
            try {
               prStatement.close();
            } catch (SQLException ex1) {
               _logger.error(null, ex1);
            }
         
         if (createTableStatement != null)
            try {
               createTableStatement.close();
            } catch (SQLException ex1) {
               _logger.error(null, ex1);
            }

         if (downloadDBConnection != null)
               DataSourceUtils.releaseConnection(downloadDBConnection,spDBSource);

         _logger.error("Exception while inserting result download request in db", ex);
      }
   }

   public List<Object[]> getPeriodicStaticInstrumentObs(Date qstartTime, Date qendTime) {
      List<Object[]> response = new LinkedList<Object[]>();
      Connection downloadDBConnection = null;
      PreparedStatement prStatement = null;
      ResultSet result = null;
      try {
         downloadDBConnection = DataSourceUtils.getConnection(spDBSource);
         prStatement = downloadDBConnection.prepareStatement(PERIODIC_STATIC_INSTRUMENT_OBSERVATION_Q);
         prStatement.clearParameters();
         prStatement.setTimestamp(1, new Timestamp(qstartTime.getTime()));
         prStatement.setTimestamp(2, new Timestamp(qendTime.getTime()));
//       response row content is : views.observation.id, views.observation.startdate, views.observation.enddate, views.observation.temporalresolution, views.location.location,views.location.srsname
         result = prStatement.executeQuery();

         while(result!=null && result.next()){
            String gmlXMLRes =result.getString(5);
            String instrumentGmlXML = (gmlXMLRes!=null ? gmlXMLRes.trim().split("\\s|>|/", 2)[0]+" xmlns:gml=\""+MetadataHandler.GML32_NAMESPACE+"\""+
              gmlXMLRes.trim().substring(gmlXMLRes.trim().indexOf(gmlXMLRes.trim().split("\\s|>|/", 2)[1])-1):"");
            instrumentGmlXML= instrumentGmlXML.replaceAll("srsName=\"[.[^>/\\s]]*\"", "srsName=\""+result.getString(6)+"\"");
            instrumentGmlXML= (instrumentGmlXML.contains("srsName=")?instrumentGmlXML:
                    instrumentGmlXML.replaceFirst("xmlns:gml=\""+MetadataHandler.GML32_NAMESPACE+"\"", "xmlns:gml=\""+MetadataHandler.GML32_NAMESPACE+"\" "+
                    "srsName=\""+result.getString(6)+"\""));

//            String gmlXML2Res =result.getString(7);
//            String gmlXML2 = (gmlXML2Res!=null ? gmlXML2Res.trim().split("\\s|>|/", 2)[0]+" xmlns:gml=\""+MetadataHandler.GML32_NAMESPACE+"\""+
//              gmlXML2Res.trim().substring(gmlXML2Res.trim().indexOf(gmlXML2Res.trim().split("\\s|>|/", 2)[1])-1):"");
//            gmlXML2= gmlXML2.replaceAll("srsName=\"[.[^>/\\s]]*\"", "srsName=\""+result.getString(8)+"\"");
//            gmlXML2= (gmlXML2.contains("srsName=")?gmlXML2:
//                    gmlXML2.replaceFirst("xmlns:gml=\""+MetadataHandler.GML32_NAMESPACE+"\"", "xmlns:gml=\""+MetadataHandler.GML32_NAMESPACE+"\" "+
//                    "srsName=\""+result.getString(8)+"\""));
            
            Object[] rowObjects = new Object[]{result.getString(1), 
               result.getTimestamp(2)!=null?new Date(result.getTimestamp(2).getTime()):null, 
               result.getTimestamp(3)!=null?new Date(result.getTimestamp(3).getTime()):null,
               result.getObject(4), 
               instrumentGmlXML,
               result.getString(6)
//               gmlXML2Res!=null?gmlXML2:(gmlXMLRes!=null?gmlXML:gmlXML2Res),
//               result.getString(8)!=null?result.getString(8):result.getString(6)
            };

            response.add(rowObjects);
         }
         
         result.close();
         prStatement.close();
         DataSourceUtils.releaseConnection(downloadDBConnection,spDBSource);
      } catch (Exception ex) {

         if (result != null)
            try {
               result.close();
            } catch (SQLException ex1) {
               _logger.error(null, ex1);
            }

         if (prStatement != null)
            try {
               prStatement.close();
            } catch (SQLException ex1) {
               _logger.error(null, ex1);
            }

         if (downloadDBConnection != null)
               DataSourceUtils.releaseConnection(downloadDBConnection,spDBSource);

         _logger.error("Exception while inserting result download request in db", ex);
      }
      finally {
         return response;
      }
   }
   
   public synchronized List<Object[]> getNonPeriodicStaticInstrumentObs(Date qstartTime, Date qendTime) {
      List<Object[]> response = new LinkedList<Object[]>();
      Connection downloadDBConnection = null;
      PreparedStatement prStatement = null;
      ResultSet result = null;
      try {
         downloadDBConnection = DataSourceUtils.getConnection(spDBSource);
         prStatement = downloadDBConnection.prepareStatement(NON_PERIODIC_STATIC_INSTRUMENT_OBSERVATION_Q);
         prStatement.clearParameters();
         prStatement.setTimestamp(1, new Timestamp(qstartTime.getTime()));
         prStatement.setTimestamp(2, new Timestamp(qendTime.getTime()));
//       response row content is : views.observation.id, views.observation.startdate, views.observation.enddate, views.observation.temporalresolution, views.location.location,views.location.srsname
         result = prStatement.executeQuery();

         while(result!=null && result.next()){
            String gmlXMLRes =result.getString(5);
            String instrumentGmlXML = (gmlXMLRes!=null ? gmlXMLRes.trim().split("\\s|>|/", 2)[0]+" xmlns:gml=\""+MetadataHandler.GML32_NAMESPACE+"\""+
                  gmlXMLRes.trim().substring(gmlXMLRes.trim().indexOf(gmlXMLRes.trim().split("\\s|>|/", 2)[1])-1):"");
                  instrumentGmlXML= instrumentGmlXML.replaceAll("srsName=\"[.[^>/\\s]]*\"", "srsName=\""+result.getString(6)+"\"");
                  instrumentGmlXML= (instrumentGmlXML.contains("srsName=")?instrumentGmlXML:
                  instrumentGmlXML.replaceFirst("xmlns:gml=\""+MetadataHandler.GML32_NAMESPACE+"\"", "xmlns:gml=\""+MetadataHandler.GML32_NAMESPACE+"\" "+
                    "srsName=\""+result.getString(6)+"\""));

//            String gmlXML2Res =result.getString(7);
//            String gmlXML2 = (gmlXML2Res!=null ? gmlXML2Res.trim().split("\\s|>|/", 2)[0]+" xmlns:gml=\""+MetadataHandler.GML32_NAMESPACE+"\""+
//                  gmlXML2Res.trim().substring(gmlXML2Res.trim().indexOf(gmlXML2Res.trim().split("\\s|>|/", 2)[1])-1):"");
//                  gmlXML2= gmlXML2.replaceAll("srsName=\"[.[^>/\\s]]*\"", "srsName=\""+result.getString(8)+"\"");
//                  gmlXML2= (gmlXML2.contains("srsName=")?gmlXML2:
//                  gmlXML2.replaceFirst("xmlns:gml=\""+MetadataHandler.GML32_NAMESPACE+"\"", "xmlns:gml=\""+MetadataHandler.GML32_NAMESPACE+"\" "+
//                    "srsName=\""+result.getString(8)+"\""));
            
            Object[] rowObjects = new Object[]{result.getString(1), 
               result.getTimestamp(2)!=null?new Date(result.getTimestamp(2).getTime()):null, 
               result.getTimestamp(3)!=null?new Date(result.getTimestamp(3).getTime()):null,
               result.getObject(4), 
               instrumentGmlXML,
               result.getString(6)
//               gmlXML2Res!=null?gmlXML2:(gmlXMLRes!=null?instrumentGmlXML:gmlXML2Res),
//               result.getString(8)!=null?result.getString(8):result.getString(6)
            };

            response.add(rowObjects);
         }
         
         result.close();
         prStatement.close();
         DataSourceUtils.releaseConnection(downloadDBConnection,spDBSource);
      } catch (Exception ex) {

         if (result != null)
            try {
               result.close();
            } catch (SQLException ex1) {
               _logger.error(null, ex1);
            }

         if (prStatement != null)
            try {
               prStatement.close();
            } catch (SQLException ex1) {
               _logger.error(null, ex1);
            }

         if (downloadDBConnection != null)
               DataSourceUtils.releaseConnection(downloadDBConnection,spDBSource);

         _logger.error("Exception while inserting result download request in db", ex);
      }
      finally {
         return response;
      }
   }

     public synchronized void registerFilteredObservations(String queryId, String type, File tempContetFile) {
      Connection downloadDBConnection = null;
      PreparedStatement prStatement = null;
      PreparedStatement mInsertStatemtn = null;
      PreparedStatement massUpdateStatementStmnt=null;
      PreparedStatement massEliminateStatementStmnt=null;
      
      try {
         downloadDBConnection = DataSourceUtils.getConnection(spDBSource);
         prStatement = downloadDBConnection.prepareStatement(SPATIAL_QUERY_CREATE_TABLE.replace("-1-", "spatial_query."+queryId).replace("-2-", queryId));
         prStatement.execute();
         prStatement.close();

         
         Connection dbConnection = null;
         try{
         dbConnection = getDbConnection();
//         code for mass copy import through file
            CopyManager cpManager = new CopyManager((BaseConnection) dbConnection);
            String massCopyQuery = SPATIAL_COPY_MASS_INSERT.replaceAll("-tname-", "spatial_query." + queryId);
            cpManager.copyIn(massCopyQuery, new FileInputStream(tempContetFile));
            dbConnection.close();
            tempContetFile.delete();
         }
         catch(SQLException sqEx){
            _logger.error("Exception while preparing for mass insert.", sqEx);
            if(dbConnection!=null)
               dbConnection.close();
         }
         
        String massEliminateDoubleStatement = SPATIAL_QUERY_MASS_ELIMINATE_DOUBLES.replaceAll("-tname-", "spatial_query."+queryId);
        massEliminateStatementStmnt = downloadDBConnection.prepareStatement(massEliminateDoubleStatement);
        massEliminateStatementStmnt.execute();
        massEliminateStatementStmnt.close();
        
        String massUpdateStatement = SPATIAL_QUERY_MASS_UPDATE.replace("-tname-", "spatial_query."+queryId);
        massUpdateStatementStmnt = downloadDBConnection.prepareStatement(massUpdateStatement);
        massUpdateStatementStmnt.execute();
        massUpdateStatementStmnt.close();
         
        DataSourceUtils.releaseConnection(downloadDBConnection,spDBSource);         
      } catch (Exception ex) {

          if (mInsertStatemtn != null)
            try {
               mInsertStatemtn.close();
            } catch (SQLException ex1) {
               _logger.error("Failed to close mass insert statement", ex1);
            }

          
          if (massEliminateStatementStmnt != null)
            try {
               massEliminateStatementStmnt.close();
            } catch (SQLException ex1) {
               _logger.error("Failed to close mass eliminate doubles statement", ex1);
            }

          if (massUpdateStatementStmnt != null)
            try {
               massUpdateStatementStmnt.close();
            } catch (SQLException ex1) {
               _logger.error("Failed to close mass update statement", ex1);
            }

          if (prStatement != null)
            try {
               prStatement.close();
            } catch (SQLException ex1) {
               _logger.error("Failed to close mass insert statement", ex1);
            }
         if (downloadDBConnection != null)
               DataSourceUtils.releaseConnection(downloadDBConnection,spDBSource);

         _logger.error("Exception while inserting result download request in db", ex);
      }
      
         
     }
 
   public synchronized void registerFilteredObservations(String queryId, String type, List<Object[]> filteredObservations) {
      Connection downloadDBConnection = null;
      PreparedStatement prStatement = null;
      PreparedStatement mInsertStatemtn = null;
      PreparedStatement massUpdateStatementStmnt=null;
      PreparedStatement massEliminateStatementStmnt=null;
      
      try {
         downloadDBConnection = DataSourceUtils.getConnection(spDBSource);
         prStatement = downloadDBConnection.prepareStatement(SPATIAL_QUERY_CREATE_TABLE.replace("-1-", "spatial_query."+queryId).replace("-2-", queryId));
         prStatement.execute();
         prStatement.close();
//         downloadDBConnection.commit();
         
//queryid ,observationid , timepoint , location ,
         SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ssz");
         
         _logger.info("Mass insert statement size is:"+filteredObservations.size());
         File tempF = createTempDataFile(queryId);
         FileWriter tempFWriter = new FileWriter(tempF);
         List<String> inserted = new LinkedList<String>();
         int indexSize = filteredObservations.size() / LIST_BATCH;
         
         for(int index=0;index<=indexSize; index++){
            String massInsertStatement = SPATIAL_QUERY_MASS_INSERT.replace("-tname-", "spatial_query."+queryId);
            int fromIndex = index*LIST_BATCH;
            int endIndex = (index+1)*LIST_BATCH>filteredObservations.size()?filteredObservations.size():((index+1)*LIST_BATCH)-1;
            String insertedRecord;

            if(fromIndex<endIndex)
            for(Object[] recordRow: filteredObservations.subList(fromIndex, endIndex))
               if(recordRow[4]!=null && !((String)recordRow[4]).isEmpty()){
                  insertedRecord = "('"+(String)recordRow[0]+"';'"+(String)recordRow[1]+"';'"+type+"'"
                          + ";to_timestamp('"+dateFormat.format((Date)recordRow[2])+"','YYYY-MM-DD HH24:MI:SS')"
                          + ";to_timestamp('"+dateFormat.format((Date)recordRow[3])+"','YYYY-MM-DD HH24:MI:SS')"
                          + ";'"+((String)recordRow[4]).trim()+"')";
                  _logger.trace("About to check record :"+insertedRecord);
                  
//                  if(!inserted.contains(insertedRecord)){
//                     massInsertStatement +="('"+(String)recordRow[0]+"','"+(String)recordRow[1]+"','"+type+"'"
//                             + ",to_timestamp('"+dateFormat.format((Date)recordRow[2])+"','YYYY-MM-DD HH24:MI:SS')"
//                             + ",to_timestamp('"+dateFormat.format((Date)recordRow[3])+"','YYYY-MM-DD HH24:MI:SS')"
//                             + ",st_geomfromgml('"+(String)recordRow[4]+"'),'"+(String)recordRow[4]+"'), ";                     
                     tempFWriter.append(""+((String)recordRow[0]).trim()+";"+((String)recordRow[1]).trim()+";"+type+""
                             + ";"+(new Timestamp(((Date)recordRow[2]).getTime())).toString().trim()
                             + ";"+(new Timestamp(((Date)recordRow[3]).getTime())).toString().trim()
                             + ";"+((String)recordRow[4]).replaceAll("\\s", " ").trim()+"\n");
//                     inserted.add(insertedRecord);
//                  }
               }
               else
                  _logger.trace("Record :"+(String)recordRow[1]+ ", "+dateFormat.format((Date)recordRow[2])+" has  null  or empty geometry");

            
//            massInsertStatement= massInsertStatement.substring(0, massInsertStatement.lastIndexOf("),")+1);
//            mInsertStatemtn = downloadDBConnection.prepareStatement(massInsertStatement);
//            mInsertStatemtn.execute();
//            mInsertStatemtn.close();
      }
         tempFWriter.flush();
         tempFWriter.close();
         
         Connection dbConnection = null;
         try{
         dbConnection = getDbConnection();
//         code for mass copy import through file
            CopyManager cpManager = new CopyManager((BaseConnection) dbConnection);
            String massCopyQuery = SPATIAL_COPY_MASS_INSERT.replaceAll("-tname-", "spatial_query." + queryId);
            cpManager.copyIn(massCopyQuery, new FileInputStream(tempF));
            dbConnection.close();
         }
         catch(SQLException sqEx){
            _logger.error("Exception while preparing for mass insert.", sqEx);
            if(dbConnection!=null)
               dbConnection.close();
         }
         
        String massEliminateDoubleStatement = SPATIAL_QUERY_MASS_ELIMINATE_DOUBLES.replaceAll("-tname-", "spatial_query."+queryId);
        massEliminateStatementStmnt = downloadDBConnection.prepareStatement(massEliminateDoubleStatement);
        massEliminateStatementStmnt.execute();
        massEliminateStatementStmnt.close();
        
        String massUpdateStatement = SPATIAL_QUERY_MASS_UPDATE.replace("-tname-", "spatial_query."+queryId);
        massUpdateStatementStmnt = downloadDBConnection.prepareStatement(massUpdateStatement);
        massUpdateStatementStmnt.execute();
        massUpdateStatementStmnt.close();
         
        DataSourceUtils.releaseConnection(downloadDBConnection,spDBSource);         
      } catch (Exception ex) {

          if (mInsertStatemtn != null)
            try {
               mInsertStatemtn.close();
            } catch (SQLException ex1) {
               _logger.error("Failed to close mass insert statement", ex1);
            }

          
          if (massEliminateStatementStmnt != null)
            try {
               massEliminateStatementStmnt.close();
            } catch (SQLException ex1) {
               _logger.error("Failed to close mass eliminate doubles statement", ex1);
            }

          if (massUpdateStatementStmnt != null)
            try {
               massUpdateStatementStmnt.close();
            } catch (SQLException ex1) {
               _logger.error("Failed to close mass update statement", ex1);
            }

          if (prStatement != null)
            try {
               prStatement.close();
            } catch (SQLException ex1) {
               _logger.error("Failed to close mass insert statement", ex1);
            }
         if (downloadDBConnection != null)
               DataSourceUtils.releaseConnection(downloadDBConnection,spDBSource);

         _logger.error("Exception while inserting result download request in db", ex);
      }
      
   }

   public void updateQueryStatus(String uniqQueryId, SpatialQueryStatus.QueryStatus queryStatus, String statusReport) {
      Connection downloadDBConnection = null;
      PreparedStatement prStatement = null;
      try {
         downloadDBConnection = DataSourceUtils.getConnection(spDBSource);
         prStatement = downloadDBConnection.prepareStatement(UPDATE_SPATIAL_QUERY_STATUS);
         prStatement.setString(1, queryStatus.name());
         prStatement.setString(2, statusReport);
         prStatement.setString(3, uniqQueryId);
         prStatement.executeUpdate();
         prStatement.close();
         DataSourceUtils.releaseConnection(downloadDBConnection,spDBSource);
      } catch (Exception ex) {
         if (prStatement != null)
            try {
               prStatement.close();
            } catch (SQLException ex1) {
               _logger.error("Failed to close mass update statement", ex1);
            }
         if (downloadDBConnection != null)
               DataSourceUtils.releaseConnection(downloadDBConnection,spDBSource);
         _logger.error("Exception while updating request status in db", ex);
      }
      
   }

//   SPATIAL_QUERY_MASS_INSERT_RESULT = "insert into -tname- (queryid, observationid, timepoint, location) VALUES select from -qtname- where ";
//   SPATIAL_QUERY_CREATE_RESULT_TABLE = "create table -tname- (queryid TEXT NOT NULL,observationid TEXT NOT NULL, timepoint TIMESTAMP WITH TIME ZONE NOT NULL, location GEOMETRY(POINTZ) NOT NULL, constraint -2-_pm_key PRIMARY KEY (queryid,observationid,timepoint,location))";

   public void performLocationQuery(String queryId, QShape spaceConstraint) throws SQLException{
        
      Connection downloadDBConnection = null;
      PreparedStatement prStatement = null;
      PreparedStatement mInsertStatemtn = null;
      try {
       String tname = queryId+"_result";
       String tableCreateStm = SPATIAL_QUERY_CREATE_RESULT_TABLE.replace("-tname-", "spatial_query."+tname);
       tableCreateStm = tableCreateStm.replace("-tname2-", tname);
       
       String queryResultStm = SPATIAL_QUERY_MASS_INSERT_RESULT + spaceConstraint.getQueryString();
        queryResultStm = queryResultStm.replace("-tname-", "spatial_query."+tname);
        queryResultStm = queryResultStm.replace("-qtname-", "spatial_query."+queryId);
        _logger.info("Executing spatial result query :\n"+queryResultStm+"\n");

        downloadDBConnection = DataSourceUtils.getConnection(spDBSource);
        prStatement = downloadDBConnection.prepareStatement(tableCreateStm);
        prStatement.execute();
        prStatement.close();
         
            
        mInsertStatemtn = downloadDBConnection.prepareStatement(queryResultStm);
        mInsertStatemtn.execute();
        mInsertStatemtn.close();


        DataSourceUtils.releaseConnection(downloadDBConnection,spDBSource);
         
      } catch (Exception ex) {
         if (mInsertStatemtn != null)
            try {
               mInsertStatemtn.close();
            } catch (SQLException ex1) {
               _logger.error(null, ex1);
            }
         
         if (prStatement != null)
            try {
               prStatement.close();
            } catch (SQLException ex1) {
               _logger.error(null, ex1);
            }
         if (downloadDBConnection != null)
               DataSourceUtils.releaseConnection(downloadDBConnection,spDBSource);

         _logger.error("Exception while inserting spatial query ["+queryId+"] result in db.", ex);
      }
      
        
   }

   public File createTempDataFile(String queryId) throws IOException {
      File parentLocation = new File(this.tempDataFileStore);
      parentLocation.mkdirs();
      File tmpDataFile = File.createTempFile(queryId+"_data", ".txt", parentLocation);
      return tmpDataFile;
   }

   private void cleanupTempDataFile(final String queryId) throws IOException{
      File parentLocation = new File(this.tempDataFileStore);
      File[] toBeRemoved = parentLocation.listFiles(new FileFilter() {
      DateTime currentDate = new DateTime(); 
            
         @Override
         public boolean accept(File _file) {
            try {
                Date fileDate = new Date(Files.getLastModifiedTime(_file.toPath(), new LinkOption[]{LinkOption.NOFOLLOW_LINKS}).toMillis());
               if (fileDate.before(currentDate.minusDays(2).toDate()));
               return true;
            } catch (IOException ex) {
               _logger.warn(null, ex);
            }
            return false;
         }
      });

      for(File _f:toBeRemoved)
         _f.delete();
   }
   
    public SpatialQueryStatus getQueryStatus(String queryId, String userid) throws SQLException{
      SpatialQueryStatus response = new SpatialQueryStatus();
      Connection downloadDBConnection = null;
      PreparedStatement prStatement = null;
      ResultSet result = null;
      try {
         downloadDBConnection = DataSourceUtils.getConnection(spDBSource);
         prStatement = downloadDBConnection.prepareStatement(SPATIAL_QUERY_STATUS);
         prStatement.clearParameters();
         prStatement.setString(1, queryId);
         prStatement.setString(2, userid);

         result = prStatement.executeQuery();

         if(result.next()){
             response.setStatus(SpatialQueryStatus.QueryStatus.valueOf(result.getString(1)));
             response.setDescription(result.getString(2));
             response.setExpirationDate(result.getDate(4));
             response.setQueryId(queryId);
             response.setUserId(userid);
         }
         result.close();
         prStatement.close();
         DataSourceUtils.releaseConnection(downloadDBConnection,spDBSource);
      } catch (Exception ex) {

         if (result != null)
            try {
               result.close();
            } catch (SQLException ex1) {
               _logger.error(null, ex1);
            }

         if (prStatement != null)
            try {
               prStatement.close();
            } catch (SQLException ex1) {
               _logger.error(null, ex1);
            }

         if (downloadDBConnection != null)
               DataSourceUtils.releaseConnection(downloadDBConnection,spDBSource);

         _logger.error("Exception while inserting result download request in db", ex);
      }
      finally {
         return response;
      }
        
    }

    private Connection getDbConnection() throws SQLException {
      return DriverManager.getConnection(databaseURL, databaseUsername, databasePassword); 
    }

    public List<String> getExpiredRequests() {
      List<String> response = new LinkedList<String>();
      Connection downloadDBConnection = null;
      PreparedStatement prStatement = null;
      ResultSet result = null;
      try {
         downloadDBConnection = DataSourceUtils.getConnection(spDBSource);
         prStatement = downloadDBConnection.prepareStatement(SPATIAL_QUERY_EXPIRED_REQUESTS);
         result = prStatement.executeQuery();

         while(result.next()){
             response.add(result.getString(1));
         }
         result.close();
         prStatement.close();
         DataSourceUtils.releaseConnection(downloadDBConnection,spDBSource);
      } catch (Exception ex) {

         if (result != null)
            try {
               result.close();
            } catch (SQLException ex1) {
               _logger.error(null, ex1);
            }

         if (prStatement != null)
            try {
               prStatement.close();
            } catch (SQLException ex1) {
               _logger.error(null, ex1);
            }

         if (downloadDBConnection != null)
               DataSourceUtils.releaseConnection(downloadDBConnection,spDBSource);

         _logger.error("Exception while inserting result download request in db", ex);
      }
      finally {
         return response;
      }
        
   }

   public void cleanupExpiredRequest(String queryId) {
      try {
         this.cleanupTempDataFile(queryId);
      } catch (IOException ex) {
         _logger.error("Failed to cleanup temporary data files from "+this.getTempDataFileStore(), ex);
      }
      
      Connection downloadDBConnection = null;
      PreparedStatement prStatement = null;
      try {
         downloadDBConnection = DataSourceUtils.getConnection(spDBSource);
         prStatement = downloadDBConnection.prepareStatement(CLEANUP_SPATIAL_QUERIES);
         prStatement.clearParameters();
         prStatement.setString(1,  queryId);
         prStatement.executeQuery();
         prStatement.close();
         DataSourceUtils.releaseConnection(downloadDBConnection,spDBSource);
      } catch (Exception ex) {
         if (prStatement != null)
            try {
               prStatement.close();
            } catch (SQLException ex1) {
               _logger.error(null, ex1);
            }
         if (downloadDBConnection != null)
               DataSourceUtils.releaseConnection(downloadDBConnection,spDBSource);
         _logger.error("Exception while cleaning up db from expired requests", ex);
      }
      
   }

   public List<Object[]> getSatelliteObservations(TimePeriodConstraint timeConstraint) {
      List<Object[]> response = new LinkedList<Object[]>();
      Connection downloadDBConnection = null;
      PreparedStatement prStatement = null;
      ResultSet result = null;
      try {
         downloadDBConnection = DataSourceUtils.getConnection(spDBSource);
         prStatement = downloadDBConnection.prepareStatement(MOVING_INSTRUMENT_OBSERVATION_Q);
         prStatement.clearParameters();
         prStatement.setTimestamp(1, new Timestamp(timeConstraint.getFromDate().getTime()));
         prStatement.setTimestamp(2, new Timestamp(timeConstraint.getToDate().getTime()));
//       response row content is : views.observation.id, views.observation.startdate, views.observation.enddate, 
//         views.platform.title, views.platform.shortlabel
         result = prStatement.executeQuery();

         while(result!=null && result.next()){
            
            Object[] rowObjects = new Object[]{result.getString(1), 
               result.getTimestamp(2)!=null?new Date(result.getTimestamp(2).getTime()):null, 
               result.getTimestamp(3)!=null?new Date(result.getTimestamp(3).getTime()):null,
               result.getString(4), result.getString(5)};

            response.add(rowObjects);
         }
         
         result.close();
         prStatement.close();
         DataSourceUtils.releaseConnection(downloadDBConnection,spDBSource);
      } catch (Exception ex) {

         if (result != null)
            try {
               result.close();
            } catch (SQLException ex1) {
               _logger.error(null, ex1);
            }

         if (prStatement != null)
            try {
               prStatement.close();
            } catch (SQLException ex1) {
               _logger.error(null, ex1);
            }

         if (downloadDBConnection != null)
               DataSourceUtils.releaseConnection(downloadDBConnection,spDBSource);

         _logger.error("Exception while retrieving satellite observations from the  db", ex);
      }
      finally {
         return response;
      }
   }

    public List<String[]> getUniqueSatellitesinTimeRange(TimePeriodConstraint timeConstraint) {
      List<String[]> response = new LinkedList<String[]>();
      Connection downloadDBConnection = null;
      PreparedStatement prStatement = null;
      ResultSet result = null;
      try {
         downloadDBConnection = DataSourceUtils.getConnection(spDBSource);
         prStatement = downloadDBConnection.prepareStatement(UNIQUE_MOVING_INSTRUMENT_Q);
         prStatement.clearParameters();
         prStatement.setTimestamp(1, new Timestamp(timeConstraint.getFromDate().getTime()));
         prStatement.setTimestamp(2, new Timestamp(timeConstraint.getToDate().getTime()));
//       response row content is : views.platform.title, views.platform.shortlabel
         
         result = prStatement.executeQuery();

         while(result!=null && result.next()){
            
            String[] rowObjects = new String[]{result.getString(1), result.getString(2)};

            response.add(rowObjects);
         }
         
         result.close();
         prStatement.close();
         DataSourceUtils.releaseConnection(downloadDBConnection,spDBSource);
      } catch (Exception ex) {

         if (result != null)
            try {
               result.close();
            } catch (SQLException ex1) {
               _logger.error(null, ex1);
            }

         if (prStatement != null)
            try {
               prStatement.close();
            } catch (SQLException ex1) {
               _logger.error(null, ex1);
            }

         if (downloadDBConnection != null)
               DataSourceUtils.releaseConnection(downloadDBConnection,spDBSource);

         _logger.error("Exception while retrieving satellite observations from the  db", ex);
      }
      finally {
         return response;
      }
    }

    public User getDownloadRequestUser(String queryId,String userId) {
        User user = new User();
       Connection downloadDBConnection = null;
       PreparedStatement prStatement = null;
       ResultSet resultSet=null; 
        try {            
            downloadDBConnection = DataSourceUtils.getConnection(spDBSource);
            prStatement = downloadDBConnection.prepareStatement(SPATIAL_QUERY_REQUEST_USER);
            prStatement.clearParameters();
            prStatement.setString(1, userId+"");
            resultSet = prStatement.executeQuery();
            if(resultSet.next()){
                user.setEmail(resultSet.getString(1));
                user.setName(resultSet.getString(2));
            }
            resultSet.close();
            prStatement.close();
             DataSourceUtils.releaseConnection(downloadDBConnection, spDBSource);
        } catch (SQLException ex) {
           
           if(resultSet!=null)
              try {
                 resultSet.close();
              } catch (SQLException ex1) {
                 _logger.error(null, ex1);
              }
           if (prStatement != null)
              try {
                 prStatement.close();
              } catch (SQLException ex1) {
                 _logger.error(null, ex1);
              }
           if (downloadDBConnection != null)
                  DataSourceUtils.releaseConnection(downloadDBConnection, spDBSource);
            _logger.error("Failed to retrieve user details for download job "+queryId, ex);
        }
        return user;
    }

    public void updateQueryNotification(String queryId, boolean notificationFlag) {
       Connection downloadDBConnection = null;
       PreparedStatement prStatement = null;
        try {
            downloadDBConnection = DataSourceUtils.getConnection(spDBSource);
            prStatement = downloadDBConnection.prepareStatement(UPDATE_SPATIAL_QUERY_NOTIFICATION);
            prStatement.clearParameters();
            prStatement.setBoolean(1, notificationFlag);
            prStatement.setString(2, queryId);
            prStatement.executeUpdate();
            prStatement.close();
             DataSourceUtils.releaseConnection(downloadDBConnection, spDBSource);
        } catch (SQLException ex) {
           
           if (prStatement != null)
              try {
                 prStatement.close();
              } catch (SQLException ex1) {
                 _logger.error(null, ex1);
              }
           if (downloadDBConnection != null)
                  DataSourceUtils.releaseConnection(downloadDBConnection, spDBSource);
            _logger.error("Failed to update email notification flag for job"+queryId, ex);
        }
    }

    public Collection<SpatialQueryStatus> getUpdatedUnnotifiedQueries() {
        Collection<SpatialQueryStatus> updatedQueries = new LinkedList<SpatialQueryStatus>();
       Connection downloadDBConnection = null;
       PreparedStatement prStatement = null;
       ResultSet result=null; 
        try {
            downloadDBConnection = DataSourceUtils.getConnection(spDBSource);
            prStatement = downloadDBConnection.prepareStatement(SPATIAL_QUERY_GET_UNOTIFIED);
            prStatement.clearParameters();
            result= prStatement.executeQuery();
            //select queryid, userid, statusreport, status, expirationdate 
            while(result.next()){
                SpatialQueryStatus qStatus = new SpatialQueryStatus();
                        qStatus.setQueryId(result.getString(1));
                        qStatus.setUserId(result.getString(2));
                        qStatus.setDescription(result.getString(3));
                        qStatus.setStatus(SpatialQueryStatus.QueryStatus.valueOf(result.getString(4)));
                        qStatus.setExpirationDate(result.getDate(5));
                updatedQueries.add(qStatus);
            }
            result.close();
            prStatement.close();
             DataSourceUtils.releaseConnection(downloadDBConnection, spDBSource);
        } catch (SQLException ex) {
           
           if(result!=null)
              try {
                 result.close();
              } catch (SQLException ex1) {
                 _logger.error(null, ex1);
              }
           if (prStatement != null)
              try {
                 prStatement.close();
              } catch (SQLException ex1) {
                 _logger.error(null, ex1);
              }
           if (downloadDBConnection != null)
                  DataSourceUtils.releaseConnection(downloadDBConnection, spDBSource);
            _logger.error( "Failed to retrieve unnotified completed or failed location queries ", ex);
        }
           
           return updatedQueries;
    }

   
}