/*
 * RiskScape™ Copyright New Zealand Institute for Earth Science Limited
 * (Earth Sciences New Zealand) is distributed for research purposes only
 * under the terms of AGPLv3.
 *
 * RiskScape™ Copyright 2026 New Zealand Institute for Earth Science
 * Limited (Earth Sciences New Zealand). All rights reserved. Source code
 * available under the AGPLv3.
 * 
 * This program is free software: you can redistribute it and/or modify it under
 *  the terms of the GNU Affero General Public License as published by the Free
 *  Software Foundation, either version 3 of the License, or (at your option) any
 *  later version.
 * 
 * This program is distributed for RESEARCH PURPOSES ONLY, in the hope that it will
 * be useful for research and education initiatives.
 * 
 * If you are not a researcher, or you are a researcher who wishes to use this
 * program on terms other than AGPLv3 (including those who wish to restrict the
 * distribution of any source code created using this program), please contact:
 * https://riskscape.org.nz
 * 
 * This program is distributed WITHOUT ANY WARRANTY; without even the implied
 * warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Affero General Public License for more details.  You should have received a copy
 * of the GNU Affero General Public License along with this program.  If not, see
 * <http://www.gnu.org/licenses/>.
 * 
 * By way of summary only, under the AGPLv3:
 *     • Permissions of this strongest copyleft license are conditioned
 *       on making available complete source code of licensed works and
 *       modifications, which include larger works using a licensed work,
 *       under the same license.
 *     • Copyright and license notices must be preserved.
 *     • Contributors provide an express grant of patent rights.
 *     • When a modified version is used to provide a service over a
 *       network, the complete source code of the modified version must be made
 *       available.
 */

package nz.org.riskscape.engine;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;
import java.util.stream.Collectors;


/**
 * Helps get data from a JDBC database for test assertions.
 */
public class JdbcHelper implements AutoCloseable {

  public static JdbcHelper fromGeoPackage(File geoPackageFile) {
    return fromJdbcUrl("jdbc:sqlite:" + geoPackageFile.getAbsolutePath());
  }

  public static JdbcHelper fromJdbcUrl(String jdbcUrl) {
    try {
    Connection conn = DriverManager.getConnection(jdbcUrl);
    return new JdbcHelper(conn);
    } catch (Exception e) {
      throw new RuntimeException(e);
    }
  }

  private final Connection conn;
  private final String quoteChar;

  public JdbcHelper(Connection conn) throws SQLException {
    this.conn = conn;
    this.quoteChar = conn.getMetaData().getIdentifierQuoteString();
  }



  /**
   * @param tableName the table to read from
   * @param columns   the columns to be read
   * @return a list containing an entry for each row, that contains entries for each column
   */
  public List<List<String>> readTable(String tableName, String... columns) {
    List<List<String>> results = new ArrayList<>();
    try (Statement stmt = conn.createStatement()) {
      String query = String.format("select %s from %s",
          Arrays.stream(columns)
              .map(column -> quoteIdentifier(column))
              .collect(Collectors.joining(", ")),
          quoteIdentifier(tableName));
      ResultSet rs = stmt.executeQuery(query);
      while (rs.next()) {
        List<String> row = new ArrayList<>();
        for (String c: columns) {
          row.add(Objects.toString(rs.getObject(c)));
        }
        results.add(row);
      }
    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
    return results;
  }

  public void dropTable(String tableName) {
    try (Statement stmt = conn.createStatement()) {
      stmt.executeUpdate("drop table if exists " + quoteIdentifier(tableName));
    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
  }

  private String quoteIdentifier(String identifier) {
    return String.format("%s%s%s", quoteChar, identifier, quoteChar);
  }

  @Override
  public void close() {
    try {
      conn.close();
    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
  }

}
