package de.ivu.elect.business.system.control;

import de.ivu.elect.business.CurrentCustomer;
import de.ivu.elect.business.system.entity.DbInformation;
import de.ivu.elect.business.system.entity.DbScript;
import de.ivu.elect.business.system.entity.DbType;
import de.ivu.elect.business.version.boundary.AbstractVersion;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.annotation.Nullable;
import javax.inject.Inject;
import javax.sql.DataSource;
import java.io.File;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;

public class ElectDbInitializerSqlRunner {

  @Inject
  private transient CurrentCustomer currentCustomer;

  @Inject
  private transient AbstractVersion version;

  private static final Logger LOG = LoggerFactory.getLogger(ElectDbInitializerSqlRunner.class);

  public static final String DB_UPDATE_TABLE_NAME = "DB_UPDATE";

  public static final String SCRIPT_COLUMN_NAME = "script";

  public static final String SCRIPT_ZEITPUNKT_ERFOLGREICH_COLUMN_NAME = "zeitpunkt_erfolgreich";

  public static final String TIMESTAMP_COLUMN_NAME = "zeitpunkt";

  public DbInformation getDbVersion(DataSource dataSource) throws SQLException {
    List<DbScript> scripts = new ArrayList<>();
    String productName;
    boolean schemaVorhanden;
    try (var con = dataSource.getConnection()) {
      productName = con.getMetaData().getDatabaseProductName();
      try (var ps = con.prepareStatement("SELECT " + SCRIPT_COLUMN_NAME + " FROM " + DB_UPDATE_TABLE_NAME)) {
        try (var rs = ps.executeQuery()) {
          schemaVorhanden = true;
          while (rs.next()) {
            scripts.add(new DbScript(rs.getString(1)));
          }
        }
      } catch (SQLException e) {
        LOG.warn(getPrefix() + "Fehler beim Zugriff auf Tabelle " + DB_UPDATE_TABLE_NAME + " - Schema nicht vorhanden " + e.getMessage());
        schemaVorhanden = false;
      }
    }
    return new DbInformation(schemaVorhanden, bestimmeVersion(scripts), scripts, productName);
  }

  public List<DbScript> getSkripteNichtErfolgreich(DataSource dataSource) throws SQLException {
    List<DbScript> result = new ArrayList<>();
    try (var con = dataSource.getConnection()) {
      try (var ps =
               con.prepareStatement(
                   "SELECT " + SCRIPT_COLUMN_NAME + " FROM " + DB_UPDATE_TABLE_NAME + " WHERE " + SCRIPT_ZEITPUNKT_ERFOLGREICH_COLUMN_NAME + " IS NULL")) {
        try (var rs = ps.executeQuery()) {
          while (rs.next()) {
            result.add(new DbScript(rs.getString(1)));
          }
        }
      }
    }
    return result;
  }

  @Nullable
  private Long bestimmeVersion(List<DbScript> scripts) {

    if (scripts.isEmpty()) {
      return null;
    }

    var versionNach = scripts.stream().map(DbScript::getNachVersion).filter(Objects::nonNull).max(Long::compareTo).orElse(null);
    var versionVon = scripts.stream().map(DbScript::getVonVersion).filter(Objects::nonNull).max(Long::compareTo).orElse(null);

    if (Objects.equals(versionNach, versionVon)) {
      return versionNach;
    }

    if (versionVon == null) {
      return versionNach;
    }
    if (versionNach == null) {
      return versionVon;
    }

    if (versionVon > versionNach) {
      return versionVon;
    }
    return versionNach;
  }

  public void schreibeCreateScriptLockBeiFehler(DbType dbType, String skriptName, List<String> strings, DataSource dataSource) throws SQLException {
    schreibeScript(dbType, skriptName, strings, dataSource, true, true);
  }

  public void schreibeUpdateScriptKeinLockBeiFehler(DbType dbType, String skriptName, List<String> strings, DataSource dataSource) throws SQLException {
    schreibeScript(dbType, skriptName, strings, dataSource, false, false);
  }

  private void schreibeScript(DbType dbType, String skriptName, List<String> strings, DataSource dataSource, boolean lockDatenbankBeiFehler,
                              boolean createScript) throws SQLException {
    try (var con = dataSource.getConnection()) {
      LOG.info("createScript: " + createScript);
      LOG.info("dbType.isCommitAfterCraeteStatement(): " + dbType.isCommitAfterCraeteStatement());
      var autoCommit = !createScript || !dbType.isCommitAfterCraeteStatement();
      LOG.info("autoCommit: " + autoCommit);
      con.setAutoCommit(autoCommit);
      for (var i = 0; i < strings.size(); i++) {
        var statement = strings.get(i);
        if (isStatementAusfuehrbar(statement, dbType)) {
          try (var stmt = con.createStatement()) {
            LOG.info(getPrefix() + "Execute: " + statement);
            var filteredStatement = filter(statement);
            try {
              if (stmt.execute(filteredStatement)) {
                LOG.info("Kein Update");
              } else {
                LOG.info("Update count: " + stmt.getUpdateCount());
              }
            } catch (SQLException sex) {

              if (dbType.isRetry(filteredStatement, sex)) {
                LOG.warn(getPrefix() + "Fehler beim Ausführen von Statment: " + statement + ", Meldung: " + sex.getMessage());
                try {
                  var retryStatement = dbType.filterRetry(filteredStatement, sex);
                  LOG.warn(getPrefix() + "Wiederhole Ausführung mit Statement: " + retryStatement);
                  stmt.execute(retryStatement);
                } catch (SQLException sex2) {
                  handleSqlException(dbType, skriptName, strings, i, sex2, lockDatenbankBeiFehler);
                }
              } else {
                handleSqlException(dbType, skriptName, strings, i, sex, lockDatenbankBeiFehler);
                return;
              }
            }
          }
          if (!autoCommit /*ELECT-13149*/) {
            try {
              con.commit();
            } catch (SQLException sex3) {
              handleSqlException(dbType, skriptName, strings, i, sex3, lockDatenbankBeiFehler);
            }
          }
        } else {
          if (StringUtils.isNotBlank(statement)) {
            LOG.info(getPrefix() + "Skip: " + statement);
          }
        }
      }
    }
  }

  private void handleSqlException(DbType dbType, String skriptName, List<String> strings, int i, SQLException sex, boolean lockDatenbank) {
    var sb = new StringBuilder();
    sb.append("Fehler beim Ausführen von SQL-Statements\n");
    try {
      // ELECT-15336
      sb.append("Anwendungsversion: ").append(version.getNumber()).append(", ").append(version.getTimestamp()).append("\n");
      sb.append("Zeitpunkt: ").append(new Date()).append("\n");
    } catch (Exception e) {
      LOG.warn(e.getMessage(), e);
    }
    sb.append("Skript: ");
    sb.append(skriptName);
    sb.append("\nFehlermeldung: ");
    sb.append(sex.getMessage());
    sb.append("\nError-Code: ");
    sb.append(sex.getErrorCode());
    sb.append("\nSQL-State: ");
    sb.append(sex.getSQLState());
    sb.append("\n");
    if (lockDatenbank) {
      sb.append("******************************************************************\n");
      sb.append("************* Anwendung/Server NICHT erneut starten! *************\n");
      sb.append("************* Anwendung/Server NICHT erneut starten! *************\n");
      sb.append("************* Anwendung/Server NICHT erneut starten! *************\n");
      sb.append("******************************************************************\n");
      sb.append("******* Folgende Ausgabe für eine weitere Analyse sichern! *******\n");
      sb.append("******************************************************************\n");
    }
    sb.append("******************************************************************\n");
    sb.append("Folgende Statements wurden FEHLERFREI ausgeführt:\n");
    sb.append("******************************************************************\n");
    for (var j = 0; j < i; j++) {
      var str = strings.get(j);
      if (isStatementAusfuehrbar(str, dbType)) {
        sb.append(str);
        sb.append("\n");
      }
    }
    sb.append("******************************************************************\n");
    sb.append("******************************************************************\n");
    sb.append("Folgende Statements wurden FEHLERHAFT oder GAR NICHT ausgeführt:\n");
    sb.append("******************************************************************\n");
    for (var j = i; j < strings.size(); j++) {
      var str = strings.get(j);
      if (isStatementAusfuehrbar(str, dbType)) {
        sb.append(str);
        sb.append("\n");
      }
    }
    if (lockDatenbank) {
      sb.append("******************************************************************\n");
      sb.append("******************************************************************\n");
      sb.append("************* Anwendung/Server NICHT erneut starten! *************\n");
      sb.append("************* Anwendung/Server NICHT erneut starten! *************\n");
      sb.append("************* Anwendung/Server NICHT erneut starten! *************\n");
      sb.append("******************************************************************\n");
      sb.append("*********** Ausgabe für eine weitere Analyse sichern! ************\n");
      sb.append("******************************************************************\n");
      sb.append("******************* Anwendung ist GESPERRT ***********************\n");
      sb.append("******************************************************************\n");
      sb.append("DB-Lockfile: ");
      sb.append(getDbLockFile().getAbsolutePath());
    }
    var msg = getPrefix() + sb;
    if (lockDatenbank) {
      lockDb(msg);
    }
    throw new RuntimeException(msg, sex);
  }

  private boolean isStatementAusfuehrbar(String statement, DbType dbType) {
    return StringUtils.isNotBlank(statement) && !statement.trim().startsWith("--") && !dbType.isIgnoreSql(statement.trim());
  }

  private String filter(String statement) {
    if (statement.endsWith(";")) {
      return statement.substring(0, statement.length() - 1);
    }
    return statement;
  }

  private String getPrefix() {
    return currentCustomer.getPrefix();
  }

  public void lockDb(String message) {
    try {
      FileUtils.write(getDbLockFile(), message, StandardCharsets.UTF_8, false);
    } catch (Exception e) {
      LOG.error("DB-Lockfile kann nicht erstellt werden! " + e.getMessage());
    }
  }

  public void checkDblock() {
    var dbLockFile = getDbLockFile();
    LOG.info(getPrefix() + "Prüfe Existenz DB-Lockfile file: " + dbLockFile.getAbsolutePath());
    if (dbLockFile.isFile()) {
      if (isDbLockFileLoeschen()) {
        LOG.warn(getPrefix() + "DB Lock-File vorhanden, wird geloescht: " + dbLockFile.getAbsolutePath());
        try {
          FileUtils.forceDelete(dbLockFile);
          return;
        } catch (IOException e) {
          throw new RuntimeException("DB-Lockfile kann nicht glöscht werden!\nDatei: " + dbLockFile.getAbsolutePath());
        }
      }

      String content;
      try {
        content = FileUtils.readFileToString(dbLockFile, StandardCharsets.UTF_8);
      } catch (Exception e) {
        LOG.error("DB-Lockfile vorhanden, kann aber nicht gelesen werden");
        content = "UNKNOWN";
      }
      throw new RuntimeException("DB-Lockfile vorhanden!\nDatei: " + dbLockFile
          .getAbsolutePath() + "\nInhalt:\n" + content + "\nFehler analysieren und beheben, anschließend DB-Lockfile löschen.");
    }
    LOG.info(getPrefix() + "DB Lock file nicht vorhanden");

  }

  private boolean isDbLockFileLoeschen() {
    return Boolean.TRUE.toString().equalsIgnoreCase(System.getProperty("de.votegroup.elect.delete.db.lock"));
  }

  private File getDbLockFile() {
    return new File(System.getProperty("java.io.tmpdir") + File.separator + "elect_" + currentCustomer.getCustomer().name() + ".dblock");
  }
}
