package eu.dnetlib.espas.gui.server;

import java.text.SimpleDateFormat;
import java.util.*;

import eu.dnetlib.espas.gui.shared.*;
import org.apache.log4j.Logger;

public class QueryUtils {
	
	private static Logger logger = Logger.getLogger(QueryUtils.class);
	
	public static String createSQLQueryString(QueryOptions queryOptions, DateRange selectedDateRange) {
		
		List<String> values = new ArrayList<String>();
		String queryString = "SELECT distinct id, obs.startdate, obs.enddate, temporalresolution, " +
                "EXTRACT(EPOCH FROM temporalresolution) as resolution, resulttypes, results, seconds, " +
                "array_agg(observedproperty) as observedproperties from views.observation as obs";
		
		if(!queryOptions.getPlatforms().isEmpty()) {
			queryString += " join views.observation_platform as op on obs.id=op.observation";
		}
		if(!queryOptions.getRegionsOfSpace().isEmpty()) {
			queryString += " join views.observation_regionofspace as ors on obs.id=ors.observation";
		}
		if(!queryOptions.getDimensionalityTimelines().isEmpty()) {
			queryString += " join views.observation_dimensionalitytimeline as odt on obs.id=odt.observation";
		}
		if(!queryOptions.getDimensionalityInstances().isEmpty()) {
			queryString += " join views.observation_dimensionalityinstance as odi on obs.id=odi.observation";
		}
		if(!queryOptions.getProjects().isEmpty()) {
			queryString += " join views.observation_project as opr on obs.id=opr.observation";
		}
		if(!queryOptions.getYears().isEmpty()) {
			queryString += " join views.observation_year as oy on obs.id=oy.observation";
		}
		
		queryString += " WHERE";
		
		if(!queryOptions.getObservationCollections().isEmpty()) {

            for(Vocabulary vocabulary : queryOptions.getObservationCollections())
                values.add(vocabulary.getId());
			
			String observationCollectionValues = "";
			for(int i=0; i<values.size(); i++) {
				if(i!=0) 
					observationCollectionValues += " OR";
				observationCollectionValues += " obs.observationcollection=\'" + values.get(i) + "\'";
			}
			
			queryString += " ( " + observationCollectionValues + " ) AND"; 
			values.clear();
		}

        if(!queryOptions.getProcedures().isEmpty()) {

            for(Vocabulary vocabulary : queryOptions.getProcedures())
                values.add(vocabulary.getId());

            String procedureValues = "";

            for(int i=0; i<values.size(); i++) {
                if(i!=0)
                    procedureValues += " OR";
                procedureValues += " obs.procedure=\'" + values.get(i) + "\'";
            }

            queryString += " ( " + procedureValues + " ) AND";
            values.clear();
        }
		
		if(!queryOptions.getObservedProperties().isEmpty()) {

            for(Vocabulary vocabulary : queryOptions.getObservedProperties())
                values.add(vocabulary.getId());

			String observedPropertyValues = "";
			for(int i=0; i<values.size(); i++) {
				if(i!=0) 
					observedPropertyValues += " OR";
				observedPropertyValues += " obs.observedproperty=\'" + values.get(i) + "\'";
			}
			
			queryString += " ( " + observedPropertyValues + " ) AND";
			values.clear();
		}
		
		if(!queryOptions.getPlatforms().isEmpty()) {

            for(Vocabulary vocabulary : queryOptions.getPlatforms())
                values.add(vocabulary.getId());

			String platformValues = "";
			for(int i=0; i<values.size(); i++) {
				if(i!=0) 
					platformValues += " OR";
				platformValues += " op.platform=\'" + values.get(i) + "\'";
			}
			
			queryString += " ( " + platformValues + " ) AND";
			values.clear();
		}

		if(!queryOptions.getRegionsOfSpace().isEmpty()) {

            for(Vocabulary vocabulary : queryOptions.getRegionsOfSpace())
                values.add(vocabulary.getId());

			String regionOfSpaceValues = "";
			for(int i=0; i<values.size(); i++) {
				if(i!=0) 
					regionOfSpaceValues += " OR";
				regionOfSpaceValues += " ors.regionofspace=\'" + values.get(i) + "\'";
			}
			
			queryString += " ( " + regionOfSpaceValues + " ) AND";
			values.clear();
		}

		if(!queryOptions.getDimensionalityTimelines().isEmpty()) {

            for(Vocabulary vocabulary : queryOptions.getDimensionalityTimelines())
                values.add(vocabulary.getId());

			String dimensionalityTimelineValues = "";
			for(int i=0; i<values.size(); i++) {
				if(i!=0) 
					dimensionalityTimelineValues += " OR";
				dimensionalityTimelineValues += " odt.dimensionalitytimeline=\'" + values.get(i) + "\'";
			}
			
			queryString += " ( " + dimensionalityTimelineValues + " ) AND";
			values.clear();
		}
		
		if(!queryOptions.getDimensionalityInstances().isEmpty()) {

            for(Vocabulary vocabulary : queryOptions.getDimensionalityInstances())
                values.add(vocabulary.getId());

			String dimensionalityInstanceValues = "";
			for(int i=0; i<values.size(); i++) {
				if(i!=0) 
					dimensionalityInstanceValues += " OR";
				dimensionalityInstanceValues += " odi.dimensionalityinstance=\'" + values.get(i) + "\'";
			}
			
			queryString += " ( " + dimensionalityInstanceValues + " ) AND";
			values.clear();
		}
		
		if(!queryOptions.getProjects().isEmpty()) {

            for(Vocabulary vocabulary : queryOptions.getProjects())
                values.add(vocabulary.getId());

			String projectValues = "";
			for(int i=0; i<values.size(); i++) {
				if(i!=0) 
					projectValues += " OR";
				projectValues += " opr.project=\'" + values.get(i) + "\'";
			}
			
			queryString += " ( " + projectValues + " ) AND";
			values.clear();
		}
		
		if(!queryOptions.getYears().isEmpty()) {

            for(Vocabulary vocabulary : queryOptions.getYears())
                values.add(vocabulary.getId());

			String yearValues = "";
			for(int i=0; i<values.size(); i++) {
				if(i!=0) 
					yearValues += " OR";
				yearValues += " oy.year=\'" + values.get(i) + "\'";
			}
			
			queryString += " ( " + yearValues + " ) AND";
			values.clear();
		}
		
		if(!queryOptions.getTimePeriods().isEmpty()) {
			
			queryString += " (";
			for(int i=0; i< queryOptions.getTimePeriods().size(); i++) {
				if(i!=0)
					queryString += " OR";
				queryString += " (" ;
				queryString += " (to_timestamp('" + queryOptions.getTimePeriods().get(i).getStartDate();
				queryString += "', 'YYYY-MM-DD HH24:MI:SS'), to_timestamp('" + queryOptions.getTimePeriods().get(i).getEndDate() + "', 'YYYY-MM-DD HH24:MI:SS')) " +
						"overlaps ((case when obs.startdate is not null then obs.startdate else '1900-01-01 00:00:00' end), " +
						"(case when obs.enddate is not null then obs.enddate else now() end))";
				queryString += ")";
			}
			queryString += " ) AND";
		}

        if(selectedDateRange!=null) {

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            sdf.setTimeZone(TimeZone.getTimeZone("UTC"));

            final long SECONDS = 1000;
            final long MINUTES = 60*SECONDS;
            final long HOURS = 60*MINUTES;

            Date newMaxDate = (Date) selectedDateRange.getMaxDate().clone();
            newMaxDate.setTime(newMaxDate.getTime() + 23*HOURS + 59*MINUTES + 59*SECONDS);

            queryString += " (";
            queryString += " (to_timestamp('" + sdf.format(selectedDateRange.getMinDate());
            queryString += "', 'YYYY-MM-DD HH24:MI:SS'), to_timestamp('" + sdf.format(newMaxDate) + "', 'YYYY-MM-DD HH24:MI:SS')) " +
                        "overlaps ((case when obs.startdate is not null then obs.startdate else '1900-01-01 00:00:00' end), " +
                        "(case when obs.enddate is not null then obs.enddate else now() end))";
            queryString += " ) AND";
        }
		
		if(queryString.endsWith("AND"))
			queryString = queryString.substring(0, queryString.length()-3);
		
		if(queryString.endsWith("WHERE")) {
			queryString = queryString.substring(0, queryString.length()-5);
		}

        queryString += "group by id, obs.startdate, obs.enddate, temporalresolution, resolution, resulttypes, results, seconds";

		logger.debug("query = " + queryString);
        return queryString;
	}
	
	public static String refineSQLQuery(Query query, List<RefineOption> refineOptions, DateRange selectedDateRange) {
		
		QueryOptions queryOptionsCopy = new QueryOptions(query.getQueryOptions());
		
		for (RefineOption ro:refineOptions) {
			
			List<Vocabulary> options = new ArrayList<Vocabulary>();
            Vocabulary option = new Vocabulary();
            option.setId(ro.getOptionId());
            option.setName(ro.getOptionValue());
			options.add(option);

            if(ro.getCategoryName().equals("Instrument")) {
                queryOptionsCopy.setInstruments(options);
                queryOptionsCopy.setProcedures(options);
            } else if(ro.getCategoryName().equals("Model")) {
                queryOptionsCopy.setComputations(options);
                queryOptionsCopy.setProcedures(options);
            } else if(ro.getCategoryName().equals("Observation Collection")) {
                queryOptionsCopy.setObservationCollections(options);
            } else if(ro.getCategoryName().equals("Platform")) {
                queryOptionsCopy.setPlatforms(options);
            } else if(ro.getCategoryName().equals("Region of Space")) {
                queryOptionsCopy.setRegionsOfSpace(options);
            } else if(ro.getCategoryName().equals("Dimensionality Timeline")) {
                queryOptionsCopy.setDimensionalityTimelines(options);
            } else if(ro.getCategoryName().equals("Dimensionality Instance")) {
                queryOptionsCopy.setDimensionalityInstances(options);
            } else if(ro.getCategoryName().equals("Project")) {
                queryOptionsCopy.setProjects(options);
            } else if(ro.getCategoryName().equals("Observation Year")) {
                queryOptionsCopy.setYears(options);
            }
		}
		
		return createSQLQueryString(queryOptionsCopy, selectedDateRange);
	}

    public static String createCQLQueryString(QueryOptions queryOptions, DateRange selectedDateRange) {

        List<String> values = new ArrayList<String>();
        String queryString = "";

        if(!queryOptions.getObservationCollections().isEmpty()) {

            for(Vocabulary vocabulary : queryOptions.getObservationCollections())
                values.add(vocabulary.getId());

            String observationCollectionValues = "";
            for(int i=0; i<values.size(); i++) {
                if(i!=0)
                    observationCollectionValues += " OR";
                observationCollectionValues += " observationcollectionid exact \"" + values.get(i) + "\"";
            }

            queryString += " ( " + observationCollectionValues + " ) AND";
            values.clear();
        }

        if(!queryOptions.getProcedures().isEmpty()) {

            for(Vocabulary vocabulary : queryOptions.getProcedures())
                values.add(vocabulary.getId());

            String procedureValues = "";

            for(int i=0; i<values.size(); i++) {
                if(i!=0)
                    procedureValues += " OR";
                procedureValues += " procedureid exact \"" + values.get(i) + "\"";
            }

            queryString += " ( " + procedureValues + " ) AND";
            values.clear();
        }

        if(!queryOptions.getObservedProperties().isEmpty()) {

            for(Vocabulary vocabulary : queryOptions.getObservedProperties())
                values.add(vocabulary.getId());

            String observedPropertyValues = "";
            for(int i=0; i<values.size(); i++) {
                if(i!=0)
                    observedPropertyValues += " OR";
                observedPropertyValues += " observedpropertyid exact \"" + values.get(i) + "\"";
            }

            queryString += " ( " + observedPropertyValues + " ) AND";
            values.clear();
        }

        if(!queryOptions.getPlatforms().isEmpty()) {

            for(Vocabulary vocabulary : queryOptions.getPlatforms())
                values.add(vocabulary.getId());

            String platformValues = "";
            for(int i=0; i<values.size(); i++) {
                if(i!=0)
                    platformValues += " OR";
                platformValues += " platformid exact \"" + values.get(i) + "\"";
            }

            queryString += " ( " + platformValues + " ) AND";
            values.clear();
        }

        if(!queryOptions.getRegionsOfSpace().isEmpty()) {

            for(Vocabulary vocabulary : queryOptions.getRegionsOfSpace())
                values.add(vocabulary.getId());

            String regionOfSpaceValues = "";
            for(int i=0; i<values.size(); i++) {
                if(i!=0)
                    regionOfSpaceValues += " OR";
                regionOfSpaceValues += " regionofspaceid exact \"" + values.get(i) + "\"";
            }

            queryString += " ( " + regionOfSpaceValues + " ) AND";
            values.clear();
        }

        if(!queryOptions.getDimensionalityTimelines().isEmpty()) {

            for(Vocabulary vocabulary : queryOptions.getDimensionalityTimelines())
                values.add(vocabulary.getId());

            String dimensionalityTimelineValues = "";
            for(int i=0; i<values.size(); i++) {
                if(i!=0)
                    dimensionalityTimelineValues += " OR";
                dimensionalityTimelineValues += " dimensionalitytimelineid exact \"" + values.get(i) + "\"";
            }

            queryString += " ( " + dimensionalityTimelineValues + " ) AND";
            values.clear();
        }

        if(!queryOptions.getDimensionalityInstances().isEmpty()) {

            for(Vocabulary vocabulary : queryOptions.getDimensionalityInstances())
                values.add(vocabulary.getId());

            String dimensionalityInstanceValues = "";
            for(int i=0; i<values.size(); i++) {
                if(i!=0)
                    dimensionalityInstanceValues += " OR";
                dimensionalityInstanceValues += " dimensionalityinstanceid exact \"" + values.get(i) + "\"";
            }

            queryString += " ( " + dimensionalityInstanceValues + " ) AND";
            values.clear();
        }

        if(!queryOptions.getProjects().isEmpty()) {

            for(Vocabulary vocabulary : queryOptions.getProjects())
                values.add(vocabulary.getId());

            String projectValues = "";
            for(int i=0; i<values.size(); i++) {
                if(i!=0)
                    projectValues += " OR";
                projectValues += " projectid exact \"" + values.get(i) + "\"";
            }

            queryString += " ( " + projectValues + " ) AND";
            values.clear();
        }

        if(!queryOptions.getYears().isEmpty()) {

            for(Vocabulary vocabulary : queryOptions.getYears())
                values.add(vocabulary.getId());

            String yearValues = "";
            for(int i=0; i<values.size(); i++) {
                if(i!=0)
                    yearValues += " OR";
                yearValues += " year exact \"" + values.get(i) + "\"";
            }

            queryString += " ( " + yearValues + " ) AND";
            values.clear();
        }

        if(!queryOptions.getTimePeriods().isEmpty()) {

            queryString += " (";
            for(int i=0; i< queryOptions.getTimePeriods().size(); i++) {
                if(i!=0)
                    queryString += " OR";
                queryString += " (" ;
                queryString += " enddateseconds >= " + Long.toString(queryOptions.getTimePeriods().get(i).getStartDateInSeconds()) +
                        " and startdateseconds <= " + Long.toString(queryOptions.getTimePeriods().get(i).getEndDateInSeconds());
                queryString += ")";
            }
            queryString += " ) AND";
        }

        //TODO uncomment and correct to reinstate the date double slider
//        if(selectedDateRange!=null) {
//
//            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//            sdf.setTimeZone(TimeZone.getTimeZone("UTC"));
//
//            final long SECONDS = 1000;
//            final long MINUTES = 60*SECONDS;
//            final long HOURS = 60*MINUTES;
//
//            Date newMaxDate = (Date) selectedDateRange.getMaxDate().clone();
//            newMaxDate.setTime(newMaxDate.getTime() + 23*HOURS + 59*MINUTES + 59*SECONDS);
//
//            queryString += " (";
//            queryString += " (to_timestamp('" + sdf.format(selectedDateRange.getMinDate());
//            queryString += "', 'YYYY-MM-DD HH24:MI:SS'), to_timestamp('" + sdf.format(newMaxDate) + "', 'YYYY-MM-DD HH24:MI:SS')) " +
//                    "overlaps ((case when obs.startdate is not null then obs.startdate else '1900-01-01 00:00:00' end), " +
//                    "(case when obs.enddate is not null then obs.enddate else now() end))";
//            queryString += " ) AND";
//        }

        if(queryString.endsWith("AND"))
            queryString = queryString.substring(0, queryString.length()-3);

        logger.debug("query = " + queryString);
        return  queryString;
    }

    public static String refineCQLQuery(Query query, List<RefineOption> refineOptions, DateRange selectedDateRange) {

        QueryOptions queryOptionsCopy = new QueryOptions(query.getQueryOptions());

        for (RefineOption ro:refineOptions) {

            List<Vocabulary> options = new ArrayList<Vocabulary>();
            Vocabulary option = new Vocabulary();
            option.setId(ro.getOptionId());
            option.setName(ro.getOptionValue());
            options.add(option);

            if(ro.getCategoryName().equals("Instrument")) {
                queryOptionsCopy.setInstruments(options);
                queryOptionsCopy.setProcedures(options);
            } else if(ro.getCategoryName().equals("Model")) {
                queryOptionsCopy.setComputations(options);
                queryOptionsCopy.setProcedures(options);
            } else if(ro.getCategoryName().equals("Observation Collection")) {
                queryOptionsCopy.setObservationCollections(options);
            } else if(ro.getCategoryName().equals("Platform")) {
                queryOptionsCopy.setPlatforms(options);
            } else if(ro.getCategoryName().equals("Region of Space")) {
                queryOptionsCopy.setRegionsOfSpace(options);
            } else if(ro.getCategoryName().equals("Dimensionality Timeline")) {
                queryOptionsCopy.setDimensionalityTimelines(options);
            } else if(ro.getCategoryName().equals("Dimensionality Instance")) {
                queryOptionsCopy.setDimensionalityInstances(options);
            } else if(ro.getCategoryName().equals("Project")) {
                queryOptionsCopy.setProjects(options);
//            } else if(ro.getCategoryName().equals("Observation Year")) {
//                queryOptionsCopy.setYears(options);
            }
        }

        return createCQLQueryString(queryOptionsCopy, selectedDateRange);
    }

    public static String refineLocationResultsQuery(String query, List<RefineOption> refineOptions, DateRange selectedDateRange) {

        String queryString = "select obs.id, obs.startdate, obs.enddate from (" + query  + ") as obs";

        for(RefineOption refineOption : refineOptions) {

            if(refineOption.getCategoryName().equals("Instrument")) {

                queryString += " join views.observation_instrument as oi on obs.id=oi.observation and oi.instrument=\'"
                        + refineOption.getOptionId() + "\'";

            } else if(refineOption.getCategoryName().equals("Model")) {

                queryString += " join views.observation_computation as oc on obs.id=oc.observation and oc.computation=\'"
                        + refineOption.getOptionId() + "\'";

            } else if(refineOption.getCategoryName().equals("Observation Collection")) {

                queryString += " join views.observation_observationcollection as ooc on obs.id=ooc.observation and " +
                        "ooc.observationcollection=\'" + refineOption.getOptionId() + "\'";

            } else if(refineOption.getCategoryName().equals("Platform")) {

                queryString += " join views.observation_platform as op on obs.id=op.observation and op.platform=\'"
                        + refineOption.getOptionId() + "\'";

            } else if(refineOption.getCategoryName().equals("Region of Space")) {

                queryString += " join views.observation_regionofspace as ors on obs.id=ors.observation and ors.regionofspace=\'"
                        + refineOption.getOptionId() + "\'";

            } else if(refineOption.getCategoryName().equals("Dimensionality Timeline")) {

                queryString += " join views.observation_dimensionalitytimeline as odt on obs.id=odt.observation and " +
                        "odt.dimensionalitytimeline=\'" + refineOption.getOptionId() + "\'";

            } else if(refineOption.getCategoryName().equals("Dimensionality Instance")) {

                queryString += " join views.observation_dimensionalityinstance as odi on obs.id=odi.observation and " +
                        "odi.dimensionalityinstance=\'" + refineOption.getOptionId() + "\'";

            } else if(refineOption.getCategoryName().equals("Project")) {

                queryString += " join views.observation_project as opr on obs.id=opr.observation and opr.project=\'"
                        + refineOption.getOptionId() + "\'";
            }
        }

        if(selectedDateRange!=null) {

            queryString += " WHERE";

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            sdf.setTimeZone(TimeZone.getTimeZone("UTC"));

            final long SECONDS = 1000;
            final long MINUTES = 60*SECONDS;
            final long HOURS = 60*MINUTES;

            Date newMaxDate = (Date) selectedDateRange.getMaxDate().clone();
            newMaxDate.setTime(newMaxDate.getTime() + 23*HOURS + 59*MINUTES + 59*SECONDS);

            queryString += " (to_timestamp('" + sdf.format(selectedDateRange.getMinDate());
            queryString += "', 'YYYY-MM-DD HH24:MI:SS'), to_timestamp('" + sdf.format(newMaxDate) + "', 'YYYY-MM-DD HH24:MI:SS')) " +
                    "overlaps ((case when obs.startdate is not null then obs.startdate else '1900-01-01 00:00:00' end), " +
                    "(case when obs.enddate is not null then obs.enddate else now() end))";
        }

        logger.debug("query = " + queryString);

        return queryString;
    }
}