/*
 * $Id$
 *
 * This file is part of the Cloud Services Integration Platform (CSIP),
 * a Model-as-a-Service framework, API, and application suite.
 *
 * 2012-2017, OMSLab, Colorado State University.
 *
 * OMSLab licenses this file to you under the MIT license.
 * See the LICENSE file in the project root for more information.
 */
package svap.utils;

/**
 *
 * @author rumpal
 */
public class DBQueries {

    public static final double METERS_TO_MILES = 0.000621371;
    
    public static String SVAP1aQuery01(String geometry) {
        String query = "SELECT map_unit_s, map_unit_n FROM common.s_usa_ecomapsubsections "
                + "WHERE ogr_geometry.STIntersects(geometry::STGeomFromText('" + geometry + "', 4326))=1;";
        return query;
    }

    public static String SVAP1bQuery01(String geometry) {
        String query = "SELECT na_l1name, na_l2name, us_l3name, us_l4name, us_l4code "
                + "FROM common.us_eco_l4_no_st "
                + "WHERE ogr_geometry.STIntersects(geometry::STGeomFromText('" + geometry + "', 4326))=1;";
        return query;
    }

    public static String SVAP1cQuery01(String geometry) {
        String query = "SELECT mlra_id, mlra_name, mlrarsym, lrrsym, lrr_name "
                + "FROM common.mlra_v42 "
                + "WITH (index(idx_common_mlra42)) "
                + "WHERE ogr_geometry.STIntersects(geometry::STGeomFromText('" + geometry + "', 4326))=1;";
        return query;
    }

    public static String SVAP7aQuery01() {
        String query = "SELECT id FROM svap.d_reference_stream WHERE id = ?;";
        return query;
    }

    public static String SVAP7bQuery01(String wkt, double searchRadius) {
        String query = "SELECT refStream.id, name, score, "
                + "geography::STGeomFromText(location.STAsText(), 4326).MakeValid().STAsText() AS location, "
                + "location.STDistance(geometry::STGeomFromText('" + wkt + "', 4326)) * " + METERS_TO_MILES + " AS distance "
                + "FROM svap.d_reference_stream AS refStream "
                + "INNER JOIN svap.d_ref_stream_scores AS scores "
                + "ON refStream.id = scores.ref_stream_id "
                + (searchRadius != 0 ? "WHERE location.STDistance(geometry::STGeomFromText('" + wkt + "', 4326)) < (" + searchRadius + " / " + METERS_TO_MILES + ") "
                        : " WHERE location.STIntersects( geometry::STGeomFromText('" + wkt + "', 4326)) = 1 ")
                + " AND expired_date IS NULL;";
        return query;
    }


    public static String SVAP7bQuery03(String wkt) {
        String query = "SELECT geography::STGeomFromText(ogr_geometry.STAsText(), 4326).MakeValid().STAsText() AS location "
                + "FROM common.mlra_v42 "
                + "WITH (index(idx_common_mlra42)) "                
                + " WHERE ogr_geometry.STIntersects( geometry::STGeomFromText('" + wkt + "', 4326)) = 1;";
        return query;
    }

    public static String SVAP7cQuery01(int id) {
        String query = "SELECT name, geography::STGeomFromText(location.STAsText(), 4326).MakeValid().STAsText() AS location, "
                + "description, last_update, expired_date "
                + "FROM svap.d_reference_stream "
                + "WHERE id = " + id + ";";
        return query;
    }

    public static String SVAP7cQuery02(int id) {
        String query = "SELECT id, score, comment "
                + "FROM svap.d_ref_stream_scores "
                + "WHERE ref_stream_id = " + id + ";";
        return query;
    }

    public static String SVAP7cQuery03(int id) {
        String query = "SELECT id, name, description, photo_file "
                + "FROM svap.d_ref_stream_photos "
                + "WHERE ref_stream_id = " + id + ";";
        return query;
    }

    public static String SVAP10aQuery01() {
        String query = "SELECT * FROM svap.d_element ORDER BY id;";
        return query;
    }

    public static String SVAP10bQuery01(int id) {
        String query = "SELECT * FROM svap.d_element WHERE id = " + id + ";";
        return query;
    }

    public static String SVAP10bQuery02(int id) {
        String query = "SELECT * FROM svap.d_feature WHERE element_id = " + id + ";";
        return query;
    }

    public static String SVAP10bQuery03(int id) {
//        String query = "SELECT * FROM svap.d_scoring_choice AS choice JOIN svap.d_scoring_header AS header ON choice.scoring_header_id = header.id "
//                + "WHERE choice.element_id = " + id + ";";
        String query = "SELECT id, scoring_header_id, description, low_score, high_score, "
                + "(SELECT header FROM svap.d_scoring_header WHERE id = choice.scoring_header_id) AS header "
                + "FROM svap.d_scoring_choice AS choice WHERE element_id = " + id + ";";
        return query;
    }

    public static String SVAP11Query01(int id) {
        String query = "SELECT name FROM svap.d_element WHERE id = " + id + ";";
        return query;
    }
}