package io.delphi.qa.auto.awsmfrw.managers.helpers.db;

import com.google.common.collect.Lists;
import io.delphi.qa.auto.awsmfrw.common_baby.EnSnQueryExecutionStatusItems;
import io.delphi.qa.auto.awsmfrw.common_baby.IsaCommonMethods;
import io.delphi.qa.auto.awsmfrw.common_baby.IsaLogging;
import io.delphi.qa.auto.awsmfrw.models.db.BsSqlTable;
import io.qameta.allure.Step;
import lombok.SneakyThrows;
import net.snowflake.client.jdbc.SnowflakeSQLException;
import org.apache.commons.dbutils.DbUtils;

import java.sql.*;
import java.util.*;

public class BsSnowflake extends BsDbSql implements IsaCommonMethods {

  private final Connection connection;
  public Properties PROPERTIES = new Properties();

  public Properties getProperties() {
    return PROPERTIES;
  }

  public BsSnowflake(Map<String, String> secretMap) {
    PROPERTIES.put("user", secretMap.get("user"));
    PROPERTIES.put("password", secretMap.get("password"));
    PROPERTIES.put("db", secretMap.get("database"));
    PROPERTIES.put("warehouse", secretMap.get("warehouse"));
    PROPERTIES.put("role", secretMap.get("role"));
    PROPERTIES.put("url", String.format("jdbc:snowflake://%s", secretMap.get("url")));
    connection = getConnection();
  }

  protected Connection getConnection() {
    Connection connection = null;
    try {
      connection = DriverManager.getConnection(PROPERTIES.getProperty("url"), PROPERTIES);
    } catch (SQLException e) {
      throw new RuntimeException("\n-- sql connection exception:" + e);
    }
    return connection;
  }

  public String getConnectionRole() {
    return PROPERTIES.getProperty("role");
  }

  @Override
  protected void finalize() throws Throwable {
    DbUtils.closeQuietly(connection);
    super.finalize();
  }

  @Step("Db::Postgres: execute query")
  public <T extends BsSqlTable<T>> List<T> fetchQueriedEntities(String queryText, T sourceObj) {
    StringBuilder msg = new StringBuilder();
    Statement st = null;
    ResultSet rs = null;
    List<T> result = new ArrayList<>();
    try {
      st = connection.createStatement();
      msg.append("\n-- role:").append(getProperties().getProperty("role"));
      msg.append("\n-- query text:\n").append(queryText);
      rs = Objects.requireNonNull(st).executeQuery(queryText);
      while (true) {
        try {
          if (rs.next()) {
            T obj = (T) sourceObj.builder(rs);
            msg.append("\n-- result:\n").append(gson.toJson(obj));
            result.add(obj);
          } else {
            break;
          }
        } catch (SQLException e) {
          msg.append("\n-- sql exception: ").append(e);
        }
      }
    } catch (SQLException e) {
      msg.append("\n-- sql exception: ").append(e);
      throw new RuntimeException(e);
    } finally {
      msg.append(String.format("\n-- processed records=%s", result.size()));
      log().info(String.valueOf(msg));
      DbUtils.closeQuietly(rs);
      DbUtils.closeQuietly(st);
      //      DbUtils.closeQuietly(connection);
    }
    return result;
  }

  @Step("Db::Postgres: execute update")
  protected int fetchUpdatedRowsAmount(String queryText) {
    StringBuilder msg = new StringBuilder();
    int affectedRows = 0;
    Statement st = null;
    try {
      st = connection.createStatement();
      msg.append("\n-- role:").append(getProperties().getProperty("role"));
      msg.append("\n-- query text:\n").append(queryText);
      affectedRows = st.executeUpdate(queryText);
    } catch (SQLException e) {
      msg.append("\n-- sql exception:\n").append(e);
      throw new RuntimeException(e);
    } finally {
      msg.append(String.format("\n-- processed records=%d", affectedRows));
      log().info(String.valueOf(msg));
      DbUtils.closeQuietly(st);
      //      DbUtils.closeQuietly(connection);
    }
    return affectedRows;
  }

  @SneakyThrows
  @Step("Db::Postgres: is execute success")
  protected String fetchQueryStatus(String queryText) {
    StringBuilder msg = new StringBuilder();
    String result = EnSnQueryExecutionStatusItems.UNKNOWN.name();
    Statement st = null;
    ResultSet rs = null;
    try {
      st = connection.createStatement();
      msg.append("\n-- role:").append(getProperties().getProperty("role"));
      msg.append("\n-- query text:\n").append(queryText);
      rs = st.executeQuery(queryText);
      if (rs.next()) {
        result = rs.getString(1);
      }
    } catch (SnowflakeSQLException e) {
      msg.append("\n-- sql exception:\n").append(e);
      throw e;
    } finally {
      msg.append(String.format("\n-- processed records status=%s", result));
      log().info(String.valueOf(msg));
      DbUtils.closeQuietly(rs);
      DbUtils.closeQuietly(st);
      //      DbUtils.closeQuietly(connection);
    }
    return result;
  }

  @SneakyThrows
  @Step("Db::Snowflake: execute custom query")
  public List<String> fetchQueriedItems(String queryText) {
    String pattern = guessEnv(queryText);
    queryText =
        IsaCommonMethods.resolvePattern(
            pattern,
            new HashMap<String, String>() {
              {
                put("env", envConfig.env().toUpperCase(Locale.ROOT));
              }
            });
    StringBuilder msg = new StringBuilder();
    List<String> result = new ArrayList<>();
    Statement st = null;
    ResultSet rs = null;
    try {
      st = connection.createStatement();
      msg.append("\n-- role:").append(getProperties().getProperty("role"));
      msg.append("\n-- query text:\n").append(queryText);
      rs = Objects.requireNonNull(st).executeQuery(queryText);
      while (true) {
        try {
          if (rs.next()) {
            result.addAll(Collections.singleton(rs.getString(1)));
          } else {
            break;
          }
        } catch (SQLException e) {
          msg.append("\n-- sql exception: ").append(e);
        }
      }
    } catch (SQLException e) {
      msg.append("\n-- sql exception: ").append(e);
      throw e;
    } finally {
      msg.append(String.format("\n-- processed records=%s", result.size()));
      IsaLogging.log(this.getClass(), String.valueOf(msg));
      DbUtils.closeQuietly(rs);
      DbUtils.closeQuietly(st);
      //      DbUtils.closeQuietly(connection);
    }
    return result;
  }

  private String guessEnv(String queryText) {
    final String[] query = {queryText};
    Lists.newArrayList(" dev_", " qa_", " stage_")
        .forEach(
            env -> {
              query[0] =
                  query[0].replace(env, " {env}_").replace(env.toUpperCase(Locale.ROOT), " {env}_");
            });
    return query[0];
  }
}