/*
 * 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.epsg;

import java.util.Map;
import java.util.Set;
import java.util.List;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Objects;
import java.util.regex.Pattern;
import java.util.regex.Matcher;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.IOException;
import java.io.PrintStream;
import java.nio.file.Path;
import java.nio.file.Files;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.sis.util.Workaround;
import org.apache.sis.util.CharSequences;
import org.apache.sis.util.StringBuilders;
import org.apache.sis.util.internal.shared.URLs;
import org.apache.sis.metadata.sql.internal.shared.SQLBuilder;
import org.apache.sis.metadata.sql.internal.shared.ScriptRunner;


/**
 * Rewrites the {@code INSERT TO ...} statements of a <abbr>SQL</abbr> script in a more compact form.
 * This class is used only for updating the <abbr>SQL</abbr> scripts used by Apache <abbr>SIS</abbr>
 * for the <abbr>EPSG</abbr> dataset when a newer release of that dataset is available.
 * The steps to follow are documented in the {@code README.md} file.
 *
 * @author  Martin Desruisseaux (IRD, Geomatys)
 */
final class DataScriptFormatter extends ScriptRunner {
    /**
     * The {@value} keywords.
     */
    private static final String INSERT_INTO = "INSERT INTO";

    /**
     * The {@value} keywords.
     */
    private static final String VALUES = "VALUES";

    /**
     * Mapping from the table names in <abbr>SQL</abbr> scripts to the names used in {@code EPSGDataAccess}.
     * Those names are derived from the names used in the dataset distributed in MS-Access format.
     * We use those original names because they are easier to read than the names in SQL scripts.
     */
    private final Map<String,String> toOriginalTableNames;

    /**
     * Texts to replace for spelling reasons. The main changes that we are applying is the addition of accents.
     * The <abbr>EPSG</abbr> geodetic dataset tends to restrict itself to the <abbr>ASCII</abbr> character set,
     * but this is not a restriction mandated by <abbr>OGC</abbr> standards except in <abbr>WKT</abbr> strings
     * (in the latter case, <abbr>SIS</abbr> removes the accents on-the-fly).
     *
     * <p>Since the content of this map is arbitrary and has no incidence on the validity of the <abbr>SQL</abbr>
     * script generated by {@code DataScriptFormatter}, its content should be provided by the caller.</p>
     *
     * @see #addSpellingChange(String, String, String, String)
     * @see #printSpellingChangeCount(PrintStream)
     */
    private final List<TextChange> spellingChanges;

    /**
     * Names of the columns to search for computing {@link TableValues#booleanColumnIndices}.
     */
    private final Set<String> booleanColumns;

    /**
     * Names of the columns to search for computing {@link TableValues#doubleColumnIndices}.
     * We do not reformat the east/west/north/south bounds, the {@code greenwich_longitude},
     * the {@code semi_major_axis}, the {@code semi_minor_axis} and the {@code inv_flattening}
     * columns because their values are close to integers,
     */
    private final Set<String> doubleColumns;

    /**
     * Names of the columns to exclude. Some are columns deprecated since <abbr>EPSG</abbr> version 10+,
     * but still present for compatibility reasons with only null values in every rows. Other columns are
     * <abbr>EPSG</abbr> metadata not used by <abbr>SIS</abbr> (e.g. change records).
     *
     * <p>The following columns are replaced by the {@code "Usage"} intersection table,
     * but <abbr>EPSG</abbr> keeps the old columns filled with null values.</p>
     * <ul>
     *   <li>{@code area_of_use_code}: appeared in {@code "Coordinate Reference System"}, {@code "Coordinate_Operation"} and {@code "Datum"}.</li>
     *   <li>{@code crs_scope}: appeared in {@code "Coordinate Reference System"}.</li>
     *   <li>{@code coord_op_scope}: appeared in {@code "Coordinate_Operation"}.</li>
     *   <li>{@code datum_scope}: appeared in {@code "Datum"}.</li>
     * </ul>
     *
     * <p>The following columns are deprecated for other reasons:</p>
     * <ul>
     *   <li>{@code realization_epoch}: replaced by {@code publication_date}.</li>
     *   <li>{@code area_polygon_file_ref}: replaced by the {@code "Area Polygon"} table.
     *       The latter is not included in the downloaded files.</li>
     * </ul>
     *
     * <p>The following columns contains <abbr>EPSG</abbr> metadata not used by Apache<abbr>SIS</abbr>.
     * They are lineage and could be included in a future version if we can map them to ISO 19115.</p>
     * <ul>
     *   <li>{@code information_source}, {@code data_source} and {@code revision_date}: appeared in almost all tables.</li>
     *   <li>{@code change_id}: appeared in almost all tables. Required by the removal of the {@code "Change"} table.</li>
     *   <li>{@code example}: appeared in {@code "Coordinate_Operation Method"}.</li>
     * </ul>
     */
    private final Set<String> excludedColumns;

    /**
     * The values for each table. The array length is the maximal number of tables that we expect in an EPSG schema.
     * An {@link ArrayIndexOutOfBoundsException} when reading this array would mean that the schema has changed.
     */
    private final TableValues[] valuesPerTable;

    /**
     * Number of valid values in {@link #valuesPerTable}.
     */
    private int tableCount;

    /**
     * Statements other than {@code INSERT INTO}.
     */
    private final List<String> otherStatements;

    /**
     * Creates a new instance.
     *
     * @param  c  a dummy connection. Will be used for fetching metadata.
     * @throws SQLException if an error occurred while fetching metadata.
     */
    public DataScriptFormatter(final Connection c) throws SQLException {
        super(c, null, Integer.MAX_VALUE);
        booleanColumns  = Set.of("deprecated", "show_crs", "show_operation", "reverse_op", "param_sign_reversal", "ellipsoid_shape");
        doubleColumns   = Set.of("parameter_value");
        excludedColumns = Set.of("area_of_use_code", "crs_scope", "coord_op_scope", "datum_scope",    // Replaced by "Usage" table.
                                 "information_source", "data_source", "revision_date", "change_id",   // Unused EPSG metadata.
                                 "realization_epoch", "example", "area_polygon_file_ref");

        toOriginalTableNames = Map.ofEntries(
                Map.entry("epsg_alias",                      "Alias"),
                Map.entry("epsg_area",                       "Area"),      // Deprecated (removed in EPSG 10)
                Map.entry("epsg_change",                     "Change"),
                Map.entry("epsg_conventionalrs",             "Conventional RS"),
                Map.entry("epsg_coordinateaxis",             "Coordinate Axis"),
                Map.entry("epsg_coordinateaxisname",         "Coordinate Axis Name"),
                Map.entry("epsg_coordoperation",             "Coordinate_Operation"),
                Map.entry("epsg_coordoperationmethod",       "Coordinate_Operation Method"),
                Map.entry("epsg_coordoperationparam",        "Coordinate_Operation Parameter"),
                Map.entry("epsg_coordoperationparamtype",    "Coordinate_Operation Parameter Type"),    // Only in the online registry.
                Map.entry("epsg_coordoperationparamusage",   "Coordinate_Operation Parameter Usage"),
                Map.entry("epsg_coordoperationparamvalue",   "Coordinate_Operation Parameter Value"),
                Map.entry("epsg_coordoperationpath",         "Coordinate_Operation Path"),
                Map.entry("epsg_coordinatereferencesystem",  "Coordinate Reference System"),
                Map.entry("epsg_coordinatesystem",           "Coordinate System"),
                Map.entry("epsg_datum",                      "Datum"),
                Map.entry("epsg_datumensemble",              "Datum Ensemble"),
                Map.entry("epsg_datumensemblemember",        "Datum Ensemble Member"),
                Map.entry("epsg_datumrealizationmethod",     "Datum Realization Method"),
                Map.entry("epsg_definingoperation",          "Defining Operation"),
                Map.entry("epsg_deprecation",                "Deprecation"),
                Map.entry("epsg_ellipsoid",                  "Ellipsoid"),
                Map.entry("epsg_extent",                     "Extent"),
                Map.entry("epsg_namingsystem",               "Naming System"),
                Map.entry("epsg_primemeridian",              "Prime Meridian"),
                Map.entry("epsg_scope",                      "Scope"),
                Map.entry("epsg_supersession",               "Supersession"),
                Map.entry("epsg_unitofmeasure",              "Unit of Measure"),
                Map.entry("epsg_usage",                      "Usage"),
                Map.entry("epsg_versionhistory",             "Version History"));

        toOriginalTableNames.forEach((oldTable, newTable) -> addReplacement(oldTable, '"' + newTable + '"'));
        valuesPerTable  = new TableValues[toOriginalTableNames.size()];
        otherStatements = new ArrayList<>();
        spellingChanges = new ArrayList<>();
    }

    /**
     * Adds a pattern to replace by the given text. This method should be used mostly for minor spelling changes,
     * such as adding the missing accents on letters of texts in French. Replacement are tried in the order that
     * this method is invoked and stop at the first match.
     *
     * @param table        name of the table where to replace a value, or {@code null} for any.
     * @param before       string that must exist in the <abbr>SQL</abbr> before the text, or null if none.
     * @param regex        regular expression to search. Will be interpreted with an implicit word boundary.
     * @param replacement  the replacement for the given pattern.
     */
    public void addSpellingChange(final String table, final String before, final String regex, final String replacement) {
        spellingChanges.add(new TextChange(table, before, regex, replacement));
    }

    /**
     * Replaces an <abbr>ASCII</abbr> text by the same text with accents added on some characters.
     * The <abbr>ASCII</abbr> text is inferred from the given text with accented characters.
     *
     * @param  replacement  the replacement with accented characters.
     */
    public void addAccentedCharacters(final String replacement) {
        addSpellingChange(null, null, Pattern.quote(CharSequences.toASCII(replacement).toString()), replacement);
    }

    /**
     * Compacts the given file.
     *
     * @param  inputFile    the input file where to read the SQL statements to compact.
     * @param  outputFile   the output file where to write the compacted SQL statements.
     * @throws IOException  if an I/O operation failed.
     * @throws SQLException should never happen.
     */
    public final void run(final Path inputFile, final Path outputFile) throws SQLException, IOException {
        if (Files.isSameFile(inputFile, outputFile)) {
            throw new IllegalArgumentException("Input and output files are the same.");
        }
        try (BufferedReader in = Files.newBufferedReader(inputFile)) {
            run(inputFile.getFileName().toString(), in);
        }
        try (BufferedWriter out = Files.newBufferedWriter(outputFile)) {
            out.write("---\n" +
                      "---    Copyright International Association of Oil and Gas Producers (IOGP)\n" +
                      "---    See  " + URLs.EPSG_LICENSE + "  (a copy is in ./LICENSE.txt).\n" +
                      "---\n" +
                      "---    This file has been reformatted for the needs of Apache SIS project.\n" +
                      "---    See org.apache.sis.referencing.factory.sql.epsg.DataScriptFormatter.\n" +
                      "---\n" +
                      "\n");

            // Sort in order of table name for consistency with previous versions (smaller SVN diff).
            Arrays.sort(valuesPerTable, (t1, t2) -> t1.insertStatement.compareTo(t2.insertStatement));
            for (int i=0; i<tableCount; i++) {
                valuesPerTable[i].write(out);
            }
            for (String other : otherStatements) {
                out.write(other);
                out.write(";\n");       // Really want Unix EOL, not the platform-specific one.
            }
        }
    }

    /**
     * Description of a change to apply in the text.
     *
     * @see #spellingChanges
     * @see #addSpellingChange(String, String, String, String)
     */
    private static final class TextChange {
        /** Name of the table where to replace a value, or {@code null} for any. */
        private final String table;

        /** String that must exist in the <abbr>SQL</abbr> before the text, or {@code null} if none. */
        private final String before;

        /** Text to search. Will be interpreted with an implicit word boundary. */
        private final Matcher matcher;

        /** The replacement for the matched text. */
        private final String replacement;

        /** Number of times that a match has been found. */
        private int matchCount;

        /**
         * Creates a new description of a change to apply in the text.
         *
         * @param table        name of the table where to replace a value, or {@code null} for any.
         * @param before       string that must exist in the <abbr>SQL</abbr> before the text, or null if none.
         * @param regex        regular expression to search. Will be interpreted with an implicit word boundary.
         * @param replacement  the replacement for the given pattern.
         */
        TextChange(final String table, final String before, final String regex, final String replacement)  {
            this.table       = table;
            this.before      = before;
            this.matcher     = Pattern.compile(regex).matcher("");
            this.replacement = Objects.requireNonNull(replacement);
        }

        /**
         * Returns whether the given text matches the pattern.
         * This method does not verify the table in which the text occurs.
         */
        final boolean matches(final String text) {
            if (matcher.reset(text).lookingAt()) {
                for (int c, i = matcher.start(); i > 0; i -= Character.charCount(c)) {
                    c = text.codePointBefore(i);
                    if (Character.isWhitespace(c)) continue;
                    if (Character.isLetter(c)) return false;
                    break;
                }
                final int length = text.length();
                for (int c, i = matcher.end(); i < length; i += Character.charCount(c)) {
                    c = text.codePointAt(i);
                    if (Character.isWhitespace(c)) continue;
                    if (Character.isLetter(c)) return false;
                    break;
                }
                return true;
            }
            return false;
        }

        /**
         * Replaces the matched text in the given buffer. This method shall be invoked only if
         * {@link #matches(String)} returned {@code true}.
         *
         * @param  sql    the buffer where to do the replacement.
         * @param  lower  index of the first character of the region given to {@link #matches(String)}.
         * @return whether the text has been replaced.
         */
        final boolean replace(final StringBuilder sql, final int lower) {
            if (before != null) {
                final int i = sql.indexOf(before);
                if (i < 0 || i >= lower) {
                    return false;
                }
            }
            if (table != null) {
                final int s = CharSequences.skipLeadingWhitespaces(sql, 0, lower);
                if (!CharSequences.regionMatches(sql, s, SQLBuilder.INSERT + '"' + table + '"')) {
                    return false;
                }
            }
            sql.replace(lower + matcher.start(), lower + matcher.end(), replacement);
            matchCount++;
            return true;
        }

        /**
         * Formats the number of replacements done.
         */
        @Override
        public String toString() {
            return String.format("%3d replacements by \"%s\"", matchCount, replacement);
        }
    }

    /**
     * Replaces the content of a text such as {@code 'some text'}.
     * If the text content is a table name, the old table name is replaced by the new name.
     *
     * @param  sql    the whole SQL statement.
     * @param  lower  index of the opening quote character ({@code '}) of the text in {@code sql}.
     * @param  upper  index after the closing quote character ({@code '}) of the text in {@code sql}.
     */
    @Override
    @Workaround(library="EPSG", version="8.9")
    protected void editText(final StringBuilder sql, int lower, int upper) {
        final String text = sql.substring(++lower, --upper);
        String replacement = toOriginalTableNames.get(text);
        if (replacement != null) {
            sql.replace(lower, upper, replacement);
            return;
        }
        for (final TextChange entry : spellingChanges) {
            if (entry.matches(text) && entry.replace(sql, lower)) {
                return;     // Value of `upper` may be no longer valid.
            }
        }
        StringBuilders.trimWhitespaces(sql, lower, upper);
    }

    /**
     * Makes sure that {@link #execute(StringBuilder)} is invoked for every line. Whether the SQL statement
     * is supported or not is irrelevant for this method since we do not know yet what will be the database
     * engine. We just copy the <abbr>SQL</abbr> statements in a file without executing them.
     *
     * @return {@code true}.
     */
    @Override
    protected boolean isSupported(final CharSequence sql) {
        return true;
    }

    /**
     * "Executes" the given SQL statement. In the context of this {@code EPSGDataWriter} class,
     * executing a SQL statement means compacting it and writing it to the output file.
     *
     * @param  sql  the SQL statement to compact.
     * @return the number of rows added.
     * @throws IOException if an I/O operation failed.
     * @throws SQLException if a syntax error happens.
     */
    @Override
    protected int execute(final StringBuilder sql) throws IOException, SQLException {
        removeLF(sql);
        String line = CharSequences.trimWhitespaces(sql).toString();
        /*
         * Search for a table in reverse order on the assumption that the most recently added tables
         * are the most likely to be reused in the next statement.
         */
        for (int i = tableCount; --i >= 0;) {
            final TableValues values = valuesPerTable[i];
            if (line.startsWith(values.insertStatement)) {
                // The previous instruction was already an INSERT INTO the same table.
                values.add(line);
                return 1;
            }
        }
        /*
         * We are beginning insertions in a new table.
         */
        if (line.startsWith(INSERT_INTO)) {
            int valuesStart = line.indexOf(VALUES, INSERT_INTO.length());
            if (valuesStart < 0) {
                throw new SQLException("This simple program wants VALUES on the same line as INSERT INTO.");
            }
            valuesStart += VALUES.length();     // Move to the end of "VALUES".
            final CharSequence insertStatement = CharSequences.trimWhitespaces(line, 0, valuesStart);
            final var values = new TableValues(insertStatement.toString(), booleanColumns, doubleColumns, excludedColumns);
            valuesPerTable[tableCount++] = values;
            values.add(line);
            return 1;
        }
        /*
         * May be an {@code UPDATE} statement. They existed in older versions of EPSG database.
         * But with the most recent EPSG versions, the `otherStatements` list should stay empty.
         */
        otherStatements.add(line);
        return 0;
    }

    /**
     * Reformats a multi-line text as a single line text. For each occurrence of line feed
     * (the {@code '\n'} character) found in the given buffer, this method performs the following steps:
     *
     * <ol>
     *   <li>Remove the line feed character and the {@linkplain Character#isWhitespace(char) white spaces} around them.</li>
     *   <li>If the last character before the line feed and the first character after the line feed are both
     *       {@linkplain Character#isLetterOrDigit(char) letter or digit}, then a space is inserted between them.
     *       Otherwise they will be no space.</li>
     * </ol>
     *
     * This method is provided for {@link #execute(StringBuilder)} implementations, in order to "compress"
     * a multi-lines SQL statement on a single line before further processing by the caller.
     *
     * <p><b>Note:</b> current version does not use codepoint API
     * on the assumption that it is not needed for EPSG's SQL files.</p>
     *
     * @param  sql  the string in which to perform the removal.
     */
    static void removeLF(final StringBuilder sql) {
        int i = sql.length();
        while ((i = sql.lastIndexOf("\n", i)) >= 0) {
            final int length = sql.length();
            int nld = 0;
            int upper = i;
            while (++upper < length) {
                final char c = sql.charAt(upper);
                if (!Character.isWhitespace(c)) {
                    if (Character.isLetterOrDigit(c)) {
                        nld++;
                    }
                    break;
                }
            }
            while (i != 0) {
                final char c = sql.charAt(--i);
                if (!Character.isWhitespace(c)) {
                    if (Character.isLetterOrDigit(c)) {
                        nld++;
                    }
                    i++;
                    break;
                }
            }
            if (nld == 2) {
                upper--;
            }
            sql.delete(i, upper);
            if (nld == 2) {
                sql.setCharAt(i, ' ');
            }
        }
    }

    /**
     * Prints a summary of the number of replacements done for each case declared by {@code addSpellingChange(…)}.
     *
     * @param  out  where to print.
     *
     * @see #addSpellingChange(String, String, String, String)
     */
    public void printSpellingChangeCount(final PrintStream out) {
        spellingChanges.forEach(out::println);
    }
}
