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

import java.io.File;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

import javax.annotation.Nullable;
import javax.inject.Inject;
import javax.sql.DataSource;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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;

public class ElectDbInitializerSqlRunner {

  @Inject
  private transient CurrentCustomer currentCustomer;

  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 TIMESTAMP_COLUMN_NAME = "zeitpunkt";

  public DbInformation getDbVersion(DataSource dataSource) throws SQLException {
    List<DbScript> scripts = new ArrayList<>();
    String productName;
    boolean schemaVorhanden;
    try (Connection con = dataSource.getConnection()) {
      productName = con.getMetaData().getDatabaseProductName();
      try (PreparedStatement ps = con.prepareStatement("SELECT " + SCRIPT_COLUMN_NAME + " FROM " + DB_UPDATE_TABLE_NAME)) {
        try (ResultSet 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);
  }

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


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

    Long versionNach = scripts.stream().map(DbScript::getNachVersion).filter(Objects::nonNull).max(Long::compareTo).orElse(null);
    Long 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 schreibeScriptLockBeiFehler(DbType dbType, String skriptName, List<String> strings, DataSource dataSource) throws SQLException {
    schreibeScript(dbType, skriptName, strings, dataSource, true);
  }

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

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

              if (dbType.isRetry(filteredStatement, sex)) {
                LOG.warn(getPrefix() + "Fehler beim Ausführen von Statment: " + statement + ", Meldung: " + sex.getMessage());
                try {
                  String 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 (dbType.isCommitAfterCraeteStatement()) {
            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) {
    StringBuilder sb = new StringBuilder();
    sb.append("Fehler beim Ausführen von SQL-Statements\n");
    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 (int j = 0; j < i; j++) {
      String 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 (int j = i; j < strings.size(); j++) {
      String 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());
    }
    String 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() {
    File dbLockFile = getDbLockFile();
    LOG.info(getPrefix() + "Prüfe Existenz DB-Lockfile file: " + dbLockFile.getAbsolutePath());
    if (dbLockFile.isFile()) {
      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 File getDbLockFile() {
    return new File(System.getProperty("java.io.tmpdir") + File.separator + "elect_" + currentCustomer.getCustomer().name() + ".dblock");
  }
}
