/*
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.apache.sis.referencing.factory.sql;

import java.util.Set;
import java.util.List;
import java.util.Collections;
import java.util.LinkedHashSet;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.opengis.util.FactoryException;
import org.opengis.util.GenericName;
import org.opengis.metadata.Identifier;
import org.opengis.referencing.IdentifiedObject;
import org.opengis.referencing.crs.CoordinateReferenceSystem;
import org.opengis.referencing.crs.SingleCRS;
import org.opengis.referencing.crs.CompoundCRS;
import org.opengis.referencing.crs.GeodeticCRS;
import org.opengis.referencing.crs.TemporalCRS;
import org.opengis.referencing.crs.VerticalCRS;
import org.opengis.referencing.crs.GeneralDerivedCRS;
import org.opengis.referencing.cs.CoordinateSystem;
import org.opengis.referencing.datum.Datum;
import org.opengis.referencing.datum.Ellipsoid;
import org.opengis.referencing.datum.GeodeticDatum;
import org.opengis.referencing.datum.TemporalDatum;
import org.opengis.referencing.datum.VerticalDatum;
import org.opengis.referencing.datum.VerticalDatumType;
import org.apache.sis.internal.metadata.ReferencingServices;
import org.apache.sis.internal.metadata.sql.SQLUtilities;
import org.apache.sis.internal.referencing.Formulas;
import org.apache.sis.internal.system.Loggers;
import org.apache.sis.internal.util.CollectionsExt;
import org.apache.sis.metadata.iso.citation.Citations;
import org.apache.sis.referencing.IdentifiedObjects;
import org.apache.sis.referencing.factory.IdentifiedObjectFinder;
import org.apache.sis.referencing.factory.ConcurrentAuthorityFactory;
import org.apache.sis.util.ArraysExt;
import org.apache.sis.util.CharSequences;
import org.apache.sis.util.collection.Containers;
import org.apache.sis.util.logging.Logging;

import static org.apache.sis.internal.metadata.NameToIdentifier.Simplifier.ESRI_DATUM_PREFIX;


/**
 * An implementation of {@link IdentifiedObjectFinder} which scans over a smaller set of authority codes.
 * This is used for finding the EPSG code of a given Coordinate Reference System or other geodetic object.
 *
 * @author  Martin Desruisseaux (IRD, Geomatys)
 * @version 1.0
 * @since   0.7
 * @module
 */
final class EPSGCodeFinder extends IdentifiedObjectFinder {
    /**
     * The data access object to use for searching EPSG codes.
     */
    private final EPSGDataAccess dao;

    /**
     * The type of object to search, or {@code null} for using {@code object.getClass()}.
     * This is set to a non-null value when searching for dependencies, in order to avoid
     * confusion in an implementation class implements more than one GeoAPI interfaces.
     *
     * @see #isInstance(Class, IdentifiedObject)
     */
    private Class<? extends IdentifiedObject> declaredType;

    /**
     * Creates a new finder for the given data access object.
     */
    EPSGCodeFinder(final EPSGDataAccess dao) {
        super(dao.owner);
        this.dao = dao;
    }

    /**
     * Lookups objects which are approximately equal to the specified object.
     * This method temporarily disables warnings about deprecated objects.
     */
    @Override
    public Set<IdentifiedObject> find(final IdentifiedObject object) throws FactoryException {
        final boolean old = dao.quiet;
        dao.quiet = true;
        try {
            return super.find(object);
        } finally {
            dao.quiet = old;
        }
    }

    /**
     * Returns a description of the condition to put in a {@code WHERE} clause for an object having
     * the given dependency.
     *
     * <div class="section"><b>Implementation note:</b>
     * The {@code super.find(…)} method performs a check (not documented in public API) for detecting
     * when it is invoked recursively, which is the case here. Consequently the {@code super.find(…)}
     * behavior below is slightly different than usual: since invoked recursively, {@code super.find(…)}
     * checks the cache of the {@link ConcurrentAuthorityFactory} wrapper. If found, the dependency will
     * also be stored in the cache. This is desirable because this method may be invoked (indirectly) in
     * a loop for many CRS objects sharing the same {@link CoordinateSystem} or {@link Datum} dependencies.
     * </div>
     *
     * @param  column      column in the SQL query containing EPSG codes of dependency.
     * @param  type        GeoAPI interface implemented by the dependency to search.
     * @param  dependency  the dependency for which to search EPSG codes, or {@code null}.
     * @param  ignoreAxes  whether to force ignoring axes. Useful for base CRS of projected CRS.
     * @return EPSG codes of given dependency, or {@code null} if no dependency were found.
     */
    private <T extends IdentifiedObject> Condition dependencies(final String column,
            final Class<T> type, final T dependency, final boolean ignoreAxes) throws FactoryException
    {
        if (dependency != null) {
            final Class<? extends IdentifiedObject> pt = declaredType;
            final boolean previous = isIgnoringAxes();
            final Set<IdentifiedObject> find;
            try {
                setIgnoringAxes(ignoreAxes | previous);
                declaredType = type;
                find = find(dependency);
            } finally {
                declaredType = pt;
                setIgnoringAxes(previous);
            }
            final Set<Number> filters = new LinkedHashSet<>(Containers.hashMapCapacity(find.size()));
            for (final IdentifiedObject dep : find) {
                Identifier id = IdentifiedObjects.getIdentifier(dep, Citations.EPSG);
                if (id != null) try {                                                   // Should never be null, but let be safe.
                    filters.add(Integer.parseInt(id.getCode()));
                } catch (NumberFormatException e) {
                    Logging.recoverableException(Logging.getLogger(Loggers.CRS_FACTORY), EPSGCodeFinder.class, "getCodeCandidates", e);
                }
            }
            if (!filters.isEmpty()) {
                return new Condition(column, filters);
            }
        }
        return null;
    }

    /**
     * A condition to put in a SQL {@code WHERE} clause. SQL query will be one of the forms shown below,
     * where {@code <column>} and {@code <values>} are {@link #column} and {@link #values} respectively.
     *
     * {@preformat sql
     *     SELECT <codeColumn> FROM <table> WHERE <column> IN (<values>)
     *     SELECT <codeColumn> FROM <table> WHERE <column> >= <value - ε> AND <column> <= <value + ε>
     * }
     *
     * The later form is used if {@code <filters>} is a floating point value.
     * Otherwise, {@code <filters>} are typically EPSG codes of dependencies.
     */
    private static class Condition {
        /** A sentinel value for filtering by name. */
        static final Condition NAME = new Condition("NAME", Collections.emptySet());

        /** The column on which the condition apply. */
        final String column;

        /** The values of the conditions. */
        final Set<Number> values;

        /** Creates a new condition. */
        Condition(final String column, final Set<Number> values) {
            this.column = column;
            this.values = values;
        }

        /**
         * Appends this condition into the given buffer. If {@code isNext} is {@code true}, then an {@code "AND"}
         * keyword is appended before the condition. Otherwise this method presumes that the given buffer already
         * ends with {@code "WHERE "} or {@code "AND "} keyword. This method does <strong>not</strong> append a
         * new {@code "AND"} keyword after the condition.
         *
         * @param  isNext  whether to append a {@code "AND"} keyword before the condition.
         * @param  buffer  where to append the SQL fragment.
         * @return whether a SQL fragment has been appended.
         */
        boolean appendToWhere(final StringBuilder buffer, final boolean isNext) {
            if (values.isEmpty()) return false;
            if (isNext) buffer.append(" AND ");
            buffer.append(column);
            if (values.size() == 1) {
                buffer.append('=').append(CollectionsExt.first(values));
            } else {
                buffer.append(" IN (");
                for (final Number code : values) {
                    buffer.append(code).append(',');
                }
                buffer.setCharAt(buffer.length() - 1, ')');
            }
            return true;
        }

        /**
         * Appends an ordering criterion. The buffer shall ends with {@code "ORDER BY"} keyword before
         * this method is invoked. If this method appends a criterion, then a comma will be appended
         * after that criterion for allowing chaining.
         *
         * @param  buffer  where to append the SQL fragment.
         */
        void appendToOrderBy(final StringBuilder buffer) {
        }

        /**
         * Returns a string representation of this condition for debugging purpose.
         */
        @Override
        public final String toString() {
            final StringBuilder buffer = new StringBuilder(50);
            appendToWhere(buffer, false);
            return buffer.toString();
        }
    }

    /**
     * A condition for a floating point value with a tolerance.
     */
    private static final class FloatCondition extends Condition {
        /** Creates a new condition for the given value. */
        FloatCondition(final String column, final double value) {
            super(column, Collections.singleton(value));
        }

        /**
         * Appends a condition with a numeric value assumed a linear distance in metres.
         * The tolerance threshold is 1 centimetre for a planet of the size of Earth.
         */
        @Override
        boolean appendToWhere(final StringBuilder buffer, final boolean isNext) {
            if (isNext) buffer.append(" AND ");
            final double value = values.iterator().next().doubleValue();
            final double tolerance = Math.abs((Formulas.LINEAR_TOLERANCE / ReferencingServices.AUTHALIC_RADIUS) * value);
            buffer.append(column).append(">=").append(value - tolerance).append(" AND ")
                  .append(column).append("<=").append(value + tolerance);
            return true;
        }

        /**
         * Appends an ordering condition.
         */
        @Override
        void appendToOrderBy(final StringBuilder buffer) {
            final double value = values.iterator().next().doubleValue();
            buffer.append("ABS(").append(column).append('-').append(value).append("), ");
        }
    }

    /**
     * Returns {@code true} if the given object implements the given interface, ignoring interfaces
     * that are not subtypes of {@link #declaredType}. This method is used for avoiding confusion if
     * the given object implements more than one GeoAPI interfaces. Test order matter: CRS should be
     * tested first in order to have precedence over datum types.
     */
    private boolean isInstance(final Class<? extends IdentifiedObject> type, final IdentifiedObject object) {
        return (declaredType == null || type.isAssignableFrom(declaredType)) && type.isInstance(object);
    }

    /**
     * Returns a set of authority codes that <strong>may</strong> identify the same object than the specified one.
     * This implementation tries to get a smaller set than what {@link EPSGDataAccess#getAuthorityCodes(Class)}
     * would produce. Deprecated objects must be last in iteration order.
     */
    @Override
    protected Set<String> getCodeCandidates(final IdentifiedObject object) throws FactoryException {
        final TableInfo   table;                        // Contains `codeColumn` and `table` names.
        final Condition[] filters;                      // Conditions to put in the WHERE clause.
crs:    if (isInstance(CoordinateReferenceSystem.class, object)) {
            /*
             * For compound CRS, the SQL statement may be something like below
             *
             *   SELECT COORD_REF_SYS_CODE FROM "Coordinate Reference System"
             *     WHERE CAST(COORD_REF_SYS_KIND AS VARCHAR(80)) LIKE 'compound%'
             *       AND CMPD_HORIZCRS_CODE IN (?,…)
             *       AND CMPD_VERTCRS_CODE IN (?,…)
             */
            table = TableInfo.CRS;
            if (isInstance(CompoundCRS.class, object)) {
                final List<CoordinateReferenceSystem> components = ((CompoundCRS) object).getComponents();
                if (components != null) switch (components.size()) {        // Paranoiac check.
                    case 1: return getCodeCandidates(components.get(0));    // Should not happen.
                    case 2: {
                        filters = new Condition[2];
                        for (int i=0; i<=1; i++) {
                            if ((filters[i] = dependencies((i==0) ? "CMPD_HORIZCRS_CODE" : "CMPD_VERTCRS_CODE",
                                    CoordinateReferenceSystem.class, components.get(i), false)) == null)
                            {
                                return Collections.emptySet();
                            }
                        }
                        break crs;
                    }
                }
            }
            /*
             * For Coordinate Reference System, the SQL statement may be something like below
             * (with DATUM_CODE replaced by SOURCE_GEOGCRS_CODE in a projected CRS):
             *
             *   SELECT COORD_REF_SYS_CODE FROM "Coordinate Reference System"
             *     WHERE CAST(COORD_REF_SYS_KIND AS VARCHAR(80)) LIKE 'geographic%'
             *       AND DATUM_CODE IN (?,…) AND DEPRECATED=0
             *     ORDER BY COORD_REF_SYS_CODE
             */
            final Condition filter;
            if (object instanceof GeneralDerivedCRS) {              // No need to use isInstance(Class, Object) from here.
                filter = dependencies("SOURCE_GEOGCRS_CODE", CoordinateReferenceSystem.class, ((GeneralDerivedCRS) object).getBaseCRS(), true);
            } else if (object instanceof GeodeticCRS) {
                filter = dependencies("DATUM_CODE", GeodeticDatum.class, ((GeodeticCRS) object).getDatum(), true);
            } else if (object instanceof VerticalCRS) {
                filter = dependencies("DATUM_CODE", VerticalDatum.class, ((VerticalCRS) object).getDatum(), true);
            } else if (object instanceof TemporalCRS) {
                filter = dependencies("DATUM_CODE", TemporalDatum.class, ((TemporalCRS) object).getDatum(), true);
            } else if (object instanceof SingleCRS) {
                filter = dependencies("DATUM_CODE", Datum.class, ((SingleCRS) object).getDatum(), true);
            } else {
                return Collections.emptySet();
            }
            if (filter == null) {
                return Collections.emptySet();
            }
            filters = new Condition[] {filter};
        } else if (isInstance(Datum.class, object)) {
            /*
             * We currently have no better way to filter datum (or reference frames) than their names.
             * Filtering must be at least as tolerant as AbstractDatum.isHeuristicMatchForName(String).
             * The SQL statement will be something like below:
             *
             *   SELECT DATUM_CODE FROM "Datum"
             *    WHERE ELLIPSOID_CODE IN (?,…)
             *      AND (LOWER(DATUM_NAME) LIKE '?%')
             */
            table = TableInfo.DATUM;
            if (isInstance(GeodeticDatum.class, object)) {
                filters = new Condition[] {
                    dependencies("ELLIPSOID_CODE", Ellipsoid.class, ((GeodeticDatum) object).getEllipsoid(), true),
                    Condition.NAME
                };
                if (filters[0] == null) {
                    return Collections.emptySet();
                }
            } else {
                if (isInstance(VerticalDatum.class, object)) {
                    final VerticalDatumType type = ((VerticalDatum) object).getVerticalDatumType();
                    if (type != null && !type.equals(EPSGDataAccess.VERTICAL_DATUM_TYPE)) {
                        return Collections.emptySet();
                    }
                }
                filters = new Condition[] {
                    Condition.NAME
                };
            }
        } else if (isInstance(Ellipsoid.class, object)) {
            /*
             * The SQL query will be something like below:
             *
             *   SELECT ELLIPSOID_CODE FROM "Ellipsoid"
             *     WHERE SEMI_MAJOR_AXIS >= ?-ε AND SEMI_MAJOR_AXIS <= ?+ε
             *     ORDER BY ABS(SEMI_MAJOR_AXIS-?)
             */
            table   = TableInfo.ELLIPSOID;
            filters = new Condition[] {
                new FloatCondition("SEMI_MAJOR_AXIS", ((Ellipsoid) object).getSemiMajorAxis())
            };
        } else {
            // Not a supported type. Returns all codes.
            return super.getCodeCandidates(object);
        }
        /*
         * At this point we collected the information needed for creating the main SQL query.
         * If the filters include a filter by names, we will need to take aliases in account.
         * The following block prepares in advance the SQL query that we will need to execute,
         * but does not execute it now. Note that this block overwrites the `buffer` content,
         * so that buffer shall not contain valuable information yet.
         */
        final StringBuilder buffer = new StringBuilder(350);     // Temporary buffer for building SQL query.
        final Set<String> namePatterns;
        final String aliasSQL;
        if (ArraysExt.containsIdentity(filters, Condition.NAME)) {
            namePatterns = new LinkedHashSet<>();
            namePatterns.add(toDatumPattern(object.getName().getCode(), buffer));
            for (final GenericName id : object.getAlias()) {
                namePatterns.add(toDatumPattern(id.tip().toString(), buffer));
            }
            buffer.setLength(0);
            buffer.append("SELECT OBJECT_CODE FROM [Alias] WHERE OBJECT_TABLE_NAME='").append(table.unquoted()).append("' AND ");
            // PostgreSQL does not require explicit cast when the value is a literal instead than "?".
            appendFilterByName(namePatterns, "ALIAS", buffer);
            aliasSQL = dao.translator.apply(buffer.toString());
            buffer.setLength(0);
        } else {
            namePatterns = null;
            aliasSQL = null;
        }
        /*
         * Prepare the first part of SQL statement, which may be like below:
         *
         *    SELECT <codeColumn> FROM <table>
         *      WHERE CAST(<typeColumn> AS VARCHAR(80)) LIKE 'type%'
         *        AND <filter.column> IN (<filter.values>)
         *        AND (LOWER(<nameColumn>) LIKE '<name>%')
         *
         * The query is assembled in the `buffer`. The first WHERE condition specifies the desired type.
         * That condition may be absent. The next conditions specify desired values. It may be EPSG codes
         * of dependencies or parameter values as floating points. The last condition is on the object name.
         * It may be absent (typically, only datums or reference frames have that condition).
         */
        buffer.append("SELECT ").append(table.codeColumn).append(" FROM ").append(table.table);
        table.where(object.getClass(), buffer);         // Unconditionally append a "WHERE" clause.
        boolean isNext = false;
        for (final Condition filter : filters) {
            isNext |= filter.appendToWhere(buffer, isNext);
        }
        /*
         * We did not finished to build the SQL query, but the remaining part may require a JDBC connection.
         * We do not use PreparedStatement because the number of parameters varies, and we may need to use a
         * Statement two times for completely different queries.
         */
        try (Statement stmt = dao.connection.createStatement()) {
            if (namePatterns != null) {
                if (isNext) buffer.append(" AND ");
                isNext = false;
                appendFilterByName(namePatterns, table.nameColumn, buffer);
                try (ResultSet result = stmt.executeQuery(aliasSQL)) {
                    while (result.next()) {
                        final int code = result.getInt(1);
                        if (!result.wasNull()) {            // Should never be null but we are paranoiac.
                            if (!isNext) {
                                isNext = true;
                                buffer.append(" OR ").append(table.codeColumn).append(" IN (");
                            } else {
                                buffer.append(',');
                            }
                            buffer.append(code);
                        }
                    }
                }
                if (isNext) buffer.append(')');
            }
            buffer.append(getSearchDomain() == Domain.ALL_DATASET
                          ? " ORDER BY ABS(DEPRECATED), "
                          : " AND DEPRECATED=0 ORDER BY ");     // Do not put spaces around "=" - SQLTranslator searches for this exact match.
            for (final Condition filter : filters) {
                filter.appendToOrderBy(buffer);
            }
            buffer.append(table.codeColumn);          // Only for making order determinist.
            /*
             * At this point the SQL query is complete. Run it, preserving order.
             * Then sort the result by taking in account the supersession table.
             */
            final Set<String> result = new LinkedHashSet<>();       // We need to preserve order in this set.
            try (ResultSet r = stmt.executeQuery(dao.translator.apply(buffer.toString()))) {
                while (r.next()) {
                    result.add(r.getString(1));
                }
            }
            result.remove(null);                    // Should not have null element, but let be safe.
            if (result.size() > 1) {
                final Object[] id = result.toArray();
                if (dao.sort(table.unquoted(), id)) {
                    result.clear();
                    for (final Object c : id) {
                        result.add((String) c);
                    }
                }
            }
            return result;
        } catch (SQLException exception) {
            throw dao.databaseFailure(Identifier.class, String.valueOf(CollectionsExt.first(filters[0].values)), exception);
        }
    }

    /**
     * Returns a SQL pattern for the given datum name. The name is returned in all lower cases for allowing
     * case-insensitive searches. Punctuations are replaced by any sequence of characters ({@code '%'}) and
     * non-ASCII letters are replaced by any single character ({@code '_'}). The returned pattern should be
     * flexible enough for accepting all names considered equal in {@code DefaultGeodeticDatum} comparisons.
     * In case of doubt, it is okay to return a pattern accepting more names.
     *
     * @param  name    the datum name for which to return a SQL pattern.
     * @param  buffer  temporary buffer to use for creating the pattern.
     * @return the SQL pattern for the given name.
     *
     * @see org.apache.sis.referencing.datum.DefaultGeodeticDatum#isHeuristicMatchForName(String)
     */
    private static String toDatumPattern(final String name, final StringBuilder buffer) {
        int start = 0;
        if (name.startsWith(ESRI_DATUM_PREFIX)) {
            start = ESRI_DATUM_PREFIX.length();
        }
        int end = name.indexOf('(');        // Ignore "Paris" in "Nouvelle Triangulation Française (Paris)".
        if (end < 0) end = name.length();
        end = CharSequences.skipTrailingWhitespaces(name, start, end);
        buffer.setLength(0);
        SQLUtilities.toLikePattern(name, start, end, true, true, buffer);
        return buffer.toString();
    }

    /**
     * Appends to the given buffer the SQL statement for filtering datum names using a pattern created by
     * {@link #toDatumPattern(String, StringBuilder)}. This method append a SQL fragment like below:
     *
     * {@preformat sql
     *     (LOWER(<column>) LIKE '<pattern>' OR …)
     * }
     *
     * This method assumes that {@code namePatterns} contains at least one element.
     *
     * @param  namePatterns  the patterns created by {@link #toDatumPattern(String, StringBuilder)}.
     * @param  column        column where the search for the names.
     * @param  buffer        buffer where to add the SQL fragment.
     */
    private static void appendFilterByName(final Set<String> namePatterns, final String column, final StringBuilder buffer) {
        String separator = "(";
        for (final String pattern : namePatterns) {
            buffer.append(separator).append("LOWER(").append(column)
                  .append(") LIKE '").append(pattern).append('\'');
            separator = " OR ";
        }
        buffer.append(')');
    }
}
