/*
 * Copyright (c) 2022 Airbyte, Inc., all rights reserved.
 */

package io.airbyte.db.jdbc;

import com.fasterxml.jackson.databind.JsonNode;
import com.google.errorprone.annotations.MustBeClosed;
import io.airbyte.commons.functional.CheckedConsumer;
import io.airbyte.commons.functional.CheckedFunction;
import io.airbyte.db.JdbcCompatibleSourceOperations;
import io.airbyte.db.SqlDatabase;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.List;
import java.util.Spliterator;
import java.util.Spliterators;
import java.util.function.Consumer;
import java.util.stream.Stream;
import java.util.stream.StreamSupport;

/**
 * Database object for interacting with a JDBC connection.
 */
public abstract class JdbcDatabase extends SqlDatabase {

  protected final JdbcCompatibleSourceOperations<?> sourceOperations;

  public JdbcDatabase(final JdbcCompatibleSourceOperations<?> sourceOperations) {
    this.sourceOperations = sourceOperations;
  }

  /**
   * Execute a database query.
   *
   * @param query the query to execute against the database.
   * @throws SQLException SQL related exceptions.
   */
  public abstract void execute(CheckedConsumer<Connection, SQLException> query) throws SQLException;

  @Override
  public void execute(final String sql) throws SQLException {
    execute(connection -> connection.createStatement().execute(sql));
  }

  public void executeWithinTransaction(final List<String> queries) throws SQLException {
    execute(connection -> {
      connection.setAutoCommit(false);
      for (final String s : queries) {
        connection.createStatement().execute(s);
      }
      connection.commit();
      connection.setAutoCommit(true);
    });
  }

  /**
   * Map records returned in a result set. It is an "unsafe" stream because the stream must be
   * manually closed. Otherwise, there will be a database connection leak.
   *
   * @param resultSet the result set
   * @param mapper function to make each record of the result set
   * @param <T> type that each record will be mapped to
   * @return stream of records that the result set is mapped to.
   */
  @MustBeClosed
  protected static <T> Stream<T> toUnsafeStream(final ResultSet resultSet, final CheckedFunction<ResultSet, T, SQLException> mapper) {
    return StreamSupport.stream(new Spliterators.AbstractSpliterator<>(Long.MAX_VALUE, Spliterator.ORDERED) {

      @Override
      public boolean tryAdvance(final Consumer<? super T> action) {
        try {
          if (!resultSet.next()) {
            resultSet.close();
            return false;
          }
          action.accept(mapper.apply(resultSet));
          return true;
        } catch (final SQLException e) {
          throw new RuntimeException(e);
        }
      }

    }, false);
  }

  /**
   * Use a connection to create a {@link ResultSet} and map it into a list. The entire
   * {@link ResultSet} will be buffered in memory before the list is returned. The caller does not
   * need to worry about closing any database resources.
   *
   * @param query execute a query using a {@link Connection} to get a {@link ResultSet}.
   * @param recordTransform transform each record of that result set into the desired type. do NOT
   *        just pass the {@link ResultSet} through. it is a stateful object will not be accessible if
   *        returned from recordTransform.
   * @param <T> type that each record will be mapped to.
   * @return Result of the query mapped to a list.
   * @throws SQLException SQL related exceptions.
   */
  public abstract <T> List<T> bufferedResultSetQuery(CheckedFunction<Connection, ResultSet, SQLException> query,
                                                     CheckedFunction<ResultSet, T, SQLException> recordTransform)
      throws SQLException;

  /**
   * Use a connection to create a {@link ResultSet} and map it into a stream. You CANNOT assume that
   * data will be returned from this method before the entire {@link ResultSet} is buffered in memory.
   * Review the implementation of the database's JDBC driver or use the StreamingJdbcDriver if you
   * need this guarantee. It is "unsafe" because the caller should close the returned stream to
   * release the database connection. Otherwise, there will be a connection leak.
   *
   * @param query execute a query using a {@link Connection} to get a {@link ResultSet}.
   * @param recordTransform transform each record of that result set into the desired type. do NOT
   *        just pass the {@link ResultSet} through. it is a stateful object will not be accessible if
   *        returned from recordTransform.
   * @param <T> type that each record will be mapped to.
   * @return Result of the query mapped to a stream.
   * @throws SQLException SQL related exceptions.
   */
  @MustBeClosed
  public abstract <T> Stream<T> unsafeResultSetQuery(CheckedFunction<Connection, ResultSet, SQLException> query,
                                                     CheckedFunction<ResultSet, T, SQLException> recordTransform)
      throws SQLException;

  /**
   * String query is a common use case for {@link JdbcDatabase#unsafeResultSetQuery}. So this method
   * is created as syntactic sugar.
   */
  public List<String> queryStrings(final CheckedFunction<Connection, ResultSet, SQLException> query,
                                   final CheckedFunction<ResultSet, String, SQLException> recordTransform)
      throws SQLException {
    try (final Stream<String> stream = unsafeResultSetQuery(query, recordTransform)) {
      return stream.toList();
    }
  }

  /**
   * Use a connection to create a {@link PreparedStatement} and map it into a stream. You CANNOT
   * assume that data will be returned from this method before the entire {@link ResultSet} is
   * buffered in memory. Review the implementation of the database's JDBC driver or use the
   * StreamingJdbcDriver if you need this guarantee. It is "unsafe" because the caller should close
   * the returned stream to release the database connection. Otherwise, there will be a connection
   * leak.
   *
   * @param statementCreator create a {@link PreparedStatement} from a {@link Connection}.
   * @param recordTransform transform each record of that result set into the desired type. do NOT
   *        just pass the {@link ResultSet} through. it is a stateful object will not be accessible if
   *        returned from recordTransform.
   * @param <T> type that each record will be mapped to.
   * @return Result of the query mapped to a stream.void execute(String sql)
   * @throws SQLException SQL related exceptions.
   */
  @MustBeClosed
  public abstract <T> Stream<T> unsafeQuery(CheckedFunction<Connection, PreparedStatement, SQLException> statementCreator,
                                            CheckedFunction<ResultSet, T, SQLException> recordTransform)
      throws SQLException;

  /**
   * Json query is a common use case for
   * {@link JdbcDatabase#unsafeQuery(CheckedFunction, CheckedFunction)}. So this method is created as
   * syntactic sugar.
   */
  public List<JsonNode> queryJsons(final CheckedFunction<Connection, PreparedStatement, SQLException> statementCreator,
                                   final CheckedFunction<ResultSet, JsonNode, SQLException> recordTransform)
      throws SQLException {
    try (final Stream<JsonNode> stream = unsafeQuery(statementCreator, recordTransform)) {
      return stream.toList();
    }
  }

  public int queryInt(final String sql, final String... params) throws SQLException {
    try (final Stream<Integer> stream = unsafeQuery(c -> {
      PreparedStatement statement = c.prepareStatement(sql);
      int i = 1;
      for (String param : params) {
        statement.setString(i, param);
        ++i;
      }
      return statement;
    }, rs -> rs.getInt(1))) {
      return stream.findFirst().get();
    }
  }

  /**
   * It is "unsafe" because the caller must manually close the returned stream. Otherwise, there will
   * be a database connection leak.
   */
  @MustBeClosed
  @Override
  public Stream<JsonNode> unsafeQuery(final String sql, final String... params) throws SQLException {
    return unsafeQuery(connection -> {
      final PreparedStatement statement = connection.prepareStatement(sql);
      int i = 1;
      for (final String param : params) {
        statement.setString(i, param);
        ++i;
      }
      return statement;
    }, sourceOperations::rowToJson);
  }

  /**
   * Json query is a common use case for {@link JdbcDatabase#unsafeQuery(String, String...)}. So this
   * method is created as syntactic sugar.
   */
  public List<JsonNode> queryJsons(final String sql, final String... params) throws SQLException {
    try (final Stream<JsonNode> stream = unsafeQuery(sql, params)) {
      return stream.toList();
    }
  }

  public ResultSetMetaData queryMetadata(final String sql, final String... params) throws SQLException {
    try (final Stream<ResultSetMetaData> q = unsafeQuery(c -> {
      PreparedStatement statement = c.prepareStatement(sql);
      int i = 1;
      for (String param : params) {
        statement.setString(i, param);
        ++i;
      }
      return statement;
    },
        ResultSet::getMetaData)) {
      return q.findFirst().orElse(null);
    }
  }

  public abstract DatabaseMetaData getMetaData() throws SQLException;

}
