package uk.ac.ox.zoo.seeg.abraid.mp.common.dao;

import uk.ac.ox.zoo.seeg.abraid.mp.common.domain.DiseaseExtentClass;
import uk.ac.ox.zoo.seeg.abraid.mp.common.domain.ModelRunStatus;

/**
 * Contains constants (e.g. queries, table/column names) for use with native SQL routines.
 *
 * Copyright (c) 2014 University of Oxford
 */
public final class NativeSQLConstants {
    private NativeSQLConstants() {
    }

    /** Query: Finds the first admin unit that contains the specified point. We use ST_INTERSECTS rather than
        ST_CONTAINS because the former returns true if the point is on the geometry border. */
    public static final String ADMIN_UNIT_CONTAINS_POINT_QUERY =
            "SELECT MIN(gaul_code) FROM admin_unit_%s WHERE ST_Intersects(geom, :point)";
    /** Filter for above query. */
    public static final String ADMIN_UNIT_CONTAINS_POINT_LEVEL_FILTER = " AND level = :adminLevel";

    private static final String GDAL_OPTIONS = "'COMPRESS=DEFLATE', 'ZLEVEL=9'";

    /** Query: Loads a model run's output raster. Converts to the specified GDAL raster format. */
    public static final String LOAD_RASTER_QUERY =
            "SELECT ST_AsGDALRaster(%s, :gdalFormat, ARRAY[" + GDAL_OPTIONS + "]) FROM model_run WHERE id = :id";
    /** Query: Saves a model run's output raster. Converts from any supported GDAL raster format. */
    public static final String UPDATE_RASTER_QUERY =
            "UPDATE model_run SET %s = ST_FromGDALRaster(:gdalRaster, :srid) WHERE id = :id";

    /** Query: Deletes a disease extent. */
    public static final String DELETE_DISEASE_EXTENT_QUERY =
            "DELETE FROM disease_extent WHERE disease_group_id = :diseaseGroupId";

    /** Clause for selecting relevant disease extent rows in the queries below. */
    private static final String DISEASE_EXTENT_CLAUSE =
            "FROM admin_unit_disease_extent_class c " +
            "JOIN admin_unit_%1$s a ON c.%1$s_gaul_code = a.gaul_code " +
            "WHERE c.disease_group_id = :diseaseGroupId " +
            "AND c.disease_extent_class IN ('" + DiseaseExtentClass.POSSIBLE_PRESENCE + "', '" +
                                                 DiseaseExtentClass.PRESENCE + "')";

    /** Query: Inserts a disease extent by aggregating the geometries in admin_unit_global/tropical, based on
     relevant rows in admin_unit_disease_extent_class. The geometries are aggregated by applying ST_DUMP (split
     multipolygons into polygons) then ST_COLLECT (concatenate polygons into a multipolygon); this is much quicker
     than ST_UNION which dissolves boundaries between polygons. */
    public static final String INSERT_DISEASE_EXTENT_QUERY =
            "INSERT INTO disease_extent (disease_group_id, geom) " +
            "SELECT :diseaseGroupId, ST_COLLECT(x.geom) " +
            "FROM (SELECT (ST_DUMP(a.geom)).geom " + DISEASE_EXTENT_CLAUSE + ") x";

    /** Query: Finds the environmental suitability for a disease group to exist at a point. This is taken from the
        mean prediction raster of the latest successful model run for the disease group. */
    public static final String ENV_SUITABILITY_QUERY =
            "SELECT DISTINCT ON (disease_group_id) ST_Value(mean_prediction_raster, :geom) " +
            "FROM model_run " +
            "WHERE disease_group_id = :diseaseGroupId " +
            "AND status = '" + ModelRunStatus.COMPLETED + "' " +
            "AND mean_prediction_raster IS NOT NULL " +
            "ORDER BY disease_group_id, response_date DESC";

    /** Query: Calculates the distance between the specified point and the disease extent of the specified disease
               group, as follows:
               1. ST_ClosestPoint: Find the closest point on the disease extent to the specified point. If the point
                  is within the disease extent, this returns the specified point itself (giving a distance of 0).
               2. ST_Distance: Find the orthodromic (surface) distance between the closest point and the specified
                  point. In theory this can operate without the use of ST_ClosestPoint, but it is much slower.
               3. Return the value in kilometres by dividing by 1000. */
    public static final String DISTANCE_OUTSIDE_DISEASE_EXTENT =
            "SELECT ST_Distance(GEOGRAPHY(ST_ClosestPoint(geom, :geom)), GEOGRAPHY(:geom)) / 1000 " +
            "FROM disease_extent " +
            "WHERE disease_group_id = :diseaseGroupId";

    /** Query: Finds the nominal distance to be used for a point that is within a disease extent, based on the
               disease extent class of the containing geometry. */
    public static final String DISTANCE_WITHIN_DISEASE_EXTENT =
            "SELECT distance_if_within_extent " +
            "FROM disease_extent_class " +
            "WHERE name IN" +
            "    (SELECT c.disease_extent_class " + DISEASE_EXTENT_CLAUSE +
            "     AND ST_Intersects(a.geom, :geom))";

    /** Column name: model_run.mean_prediction_raster. */
    public static final String MEAN_PREDICTION_RASTER_COLUMN_NAME = "mean_prediction_raster";
    /** Column name: model_run.prediction_uncertainty_raster. */
    public static final String PREDICTION_UNCERTAINTY_RASTER_COLUMN_NAME = "prediction_uncertainty_raster";

    /** Other: Global. */
    public static final String GLOBAL = "global";
    /** Other: Tropical. */
    public static final String TROPICAL = "tropical";
    /** Other: PostGIS's name for the GeoTiff raster format. */
    public static final String GDAL_RASTER_FORMAT = "GTiff";
}

@@ -3160,39 +3160,8 @@
 LECT
- DISTINCT ON (disease_group_id)
  ST_
@@ -3244,32 +3244,161 @@
          %22WHERE 
+id IN%22 +%0A            %22    (SELECT DISTINCT ON (disease_group_id) id%22 +%0A            %22    FROM model_run%22 +%0A            %22    WHERE 
 disease_group_id
@@ -3407,33 +3407,32 @@
  :diseaseGroupId
- 
 %22 +%0A            
@@ -3428,24 +3428,28 @@
            %22
+    
 AND status =
@@ -3483,17 +3483,16 @@
 TED + %22'
- 
 %22 +%0A    
@@ -3496,24 +3496,28 @@
            %22
+    
 AND mean_pre
@@ -3542,17 +3542,16 @@
 NOT NULL
- 
 %22 +%0A    
@@ -3559,16 +3559,20 @@
        %22
+    
 ORDER BY
@@ -3608,16 +3608,17 @@
 ate DESC
+)
 %22;%0A%0A    

Optimise query