package dmo.fs.db.dodex.h2;

import com.fasterxml.jackson.databind.JsonNode;
import dmo.fs.db.dodex.CreateDatabaseImpl;
import dmo.fs.db.dodex.hib.MutinySessionFactory;
import dmo.fs.db.dodex.utils.DodexUtil;
import io.smallrye.mutiny.Uni;
import io.vertx.jdbcclient.JDBCConnectOptions;
import io.vertx.mutiny.core.Promise;
import io.vertx.mutiny.jdbcclient.JDBCPool;
import io.vertx.mutiny.sqlclient.Pool;
import io.vertx.mutiny.sqlclient.Row;
import io.vertx.mutiny.sqlclient.RowIterator;
import io.vertx.reactivex.core.Vertx;
import io.vertx.sqlclient.PoolOptions;
import jakarta.enterprise.inject.spi.CDI;
import jakarta.persistence.metamodel.ManagedType;
import org.hibernate.reactive.mutiny.Mutiny;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.time.Duration;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

import static io.vertx.mutiny.jdbcclient.JDBCPool.pool;

public class HandicapDatabaseH2 extends DbH2 {
    protected final static Logger logger =
      LoggerFactory.getLogger(HandicapDatabaseH2.class.getName());
    protected Properties dbProperties;
    protected Map<String, String> dbOverrideMap = new ConcurrentHashMap<>();
    protected Map<String, String> dbMap;
    protected JsonNode defaultNode;
    protected String webEnv = DodexUtil.getEnv();
    protected DodexUtil dodexUtil = new DodexUtil();
    protected JDBCPool pool4;
    protected Boolean isCreateTables = false;
    protected Promise<String> returnPromise = Promise.promise();
    protected Promise<String> finalPromise = Promise.promise();
    protected JDBCConnectOptions connectOptions;
    protected PoolOptions poolOptions;

    public HandicapDatabaseH2(Map<String, String> dbOverrideMap, Properties dbOverrideProps)
      throws IOException {
        super();

        defaultNode = dodexUtil.getDefaultNode();
        dbMap = dodexUtil.jsonNodeToMap(defaultNode, webEnv);
        dbProperties = dodexUtil.mapToProperties(dbMap);

        if (dbOverrideProps != null && !dbOverrideProps.isEmpty()) {
            this.dbProperties = dbOverrideProps;
        }
        if (dbOverrideMap != null) {
            this.dbOverrideMap = dbOverrideMap;
        }

        dbProperties.setProperty("foreign_keys", "true");

        assert dbOverrideMap != null;
        CreateDatabaseImpl.mapMerge(dbMap, dbOverrideMap);
        databaseSetup();
    }

    public HandicapDatabaseH2() throws IOException {
        super();
        defaultNode = dodexUtil.getDefaultNode();
        dbMap = dodexUtil.jsonNodeToMap(defaultNode, webEnv);
        dbProperties = dodexUtil.mapToProperties(dbMap);

        dbProperties.setProperty("foreign_keys", "true");
        databaseSetup();
    }

    public HandicapDatabaseH2(Boolean isCreateTables)
      throws IOException {
        super();
        defaultNode = dodexUtil.getDefaultNode();

        dbMap = dodexUtil.jsonNodeToMap(defaultNode, webEnv);
        dbProperties = dodexUtil.mapToProperties(dbMap);

        dbProperties.setProperty("foreign_keys", "true");
        this.isCreateTables = isCreateTables;
    }

    @Override
    public Uni<String> checkOnTables() {
        return returnPromise.future();
    }

    @Override
    public Promise<Pool> databaseSetup() {

        Promise<Pool> poolPromise = Promise.promise();
        if ("dev".equals(webEnv)) {
            CreateDatabaseImpl.configureTestDefaults(dbMap, dbProperties);
        } else {
            CreateDatabaseImpl.configureDefaults(dbMap, dbProperties);
        }

        poolOptions =
          new PoolOptions().setMaxSize(Runtime.getRuntime().availableProcessors() * 5);
        String fileName = dbMap.get("filename");
        connectOptions = new JDBCConnectOptions()
          .setJdbcUrl(dbMap.get("url") + dbMap.get("filename"))
          .setUser(dbProperties.getProperty("user"))
          .setPassword(dbProperties.getProperty("password"))
          .setIdleTimeout(1)
        // .setCachePreparedStatements(true)
        ;

        pool4 = pool(DodexUtil.getVertx(), connectOptions, poolOptions);

        pool4.getConnection().onItem().invoke(conn -> {
            conn.query(CHECKUSERSQL).execute().onItem().invoke(row -> {
                  RowIterator<Row> ri = row.iterator();
                  String val = null;
                  while (ri.hasNext()) {
                      val = ri.next().getString(0);
                  }

                  if (val == null) {
                      final String usersSql = getCreateTable("USERS");

                      conn.query(usersSql).execute().onFailure().invoke(err ->
                          logger.info("Users Table Error: {}", err.getMessage()))
                        .onItem().invoke(result -> logger.info("{} -- Users Table Added.", fileName))
                        .subscribeAsCompletionStage().isDone();
                  }
              }).onFailure().invoke(err ->
                logger.info("Connection Users Table Error: {}", err.getMessage()))
              .flatMap(result -> conn.query(CHECKMESSAGESSQL)
                .execute().onItem().invoke(row -> {
                    RowIterator<Row> ri = row.iterator();
                    String val = null;
                    while (ri.hasNext()) {
                        val = ri.next().getString(0);
                    }

                    if (val == null) {
                        final String sql = getCreateTable("MESSAGES");

                        conn.query(sql).execute().onFailure().invoke(err ->
                            logger.info("Messages Table Error: {}", err.getMessage()))
                          .onItem().invoke(row2 -> logger.info("{} -- Messages Table Added.", fileName))
                          .subscribeAsCompletionStage().isDone();
                    }
                }).onFailure().invoke(err ->
                  logger.info("Connection Messages Table Error: {}", err.getMessage())))
              .flatMap(result -> conn.query(CHECKUNDELIVEREDSQL).execute().onItem().invoke(row -> {
                  RowIterator<Row> ri = row.iterator();
                  String val = null;
                  while (ri.hasNext()) {
                      val = ri.next().getString(0);
                  }

                  if (val == null) {
                      final String sql = getCreateTable("UNDELIVERED");

                      conn.query(sql).execute().onFailure().invoke(err ->
                          logger.info("Undelivered Table Error: {}", err.getMessage()))
                        .onItem().invoke(row2 -> logger.info("{} -- Undelivered Table Added.", fileName))
                        .subscribeAsCompletionStage().isDone();
                  }
              }).onFailure().invoke(err ->
                logger.info("Undelivered Table Error2: {}", err.getMessage())))
              .flatMap(result -> conn.query(CHECKGROUPSSQL).execute().onItem().invoke(row -> {
                  RowIterator<Row> ri = row.iterator();
                  String val = null;
                  while (ri.hasNext()) {
                      val = ri.next().getString(0);
                  }
                  if (val == null) {
                      final String sql = getCreateTable("GROUPS");

                      conn.query(sql).execute().onFailure().invoke(err ->
                          logger.info("Groups Table Error: {}", err.getMessage()))
                        .onItem().invoke(row2 -> logger.info("{} -- Groups Table Added.", fileName))
                        .subscribeAsCompletionStage().isDone();
                  }
              }).onFailure().invoke(err ->
                logger.info("Groups Table Error2: {}", err.getMessage())))
              .flatMap(result -> conn.query(CHECKMEMBERSQL).execute().onItem().invoke(row -> {
                  RowIterator<Row> ri = row.iterator();
                  String val = null;
                  while (ri.hasNext()) {
                      val = ri.next().getString(0);
                  }
                  if (val == null) {
                      final String sql = getCreateTable("MEMBER");

                      conn.query(sql).execute().onFailure().invoke(err ->
                          logger.info("Member Table Error: {}", err.getMessage()))
                        .onItem().invoke(row2 -> logger.info("{} -- Member Table Added.", fileName))
                        .subscribeAsCompletionStage().isDone();
                  }
              }).onFailure().invoke(err ->
                logger.info("Member Table Error2: {}", err.getMessage())))
              .flatMap(result -> conn.query(CHECKHANDICAPSQL).execute().onFailure().invoke(err ->
                  logger.error(String.format("Golfer Table Error: %s", err.getMessage())))
                .onItem().invoke(rows -> {
                    Set<String> names = new HashSet<>();

                    for (Row row : rows) {
                        names.add(row.getString(0));
                    }

                    String sql = getCreateTable("GOLFER");
                    if (names.contains("GOLFER")) {
                        sql = SELECTONE;
                    }

                    conn.query(sql).execute().onFailure().invoke(err ->
                        logger.error("Golfer Table Error: {}", err.getMessage()))
                      .onItem().invoke(row1 -> {
                          if (!names.contains("GOLFER")) {
                              logger.warn("{} -- Golfer Table Added.", fileName);
                          }
                          String sql2 = getCreateTable("COURSE");

                          if (names.contains("COURSE")) {
                              sql2 = SELECTONE;
                          }
                          conn.query(sql2).execute().onFailure().invoke(err ->
                              logger.warn("Course Table Error: {}", err.getMessage()))
                            .onItem().invoke(row2 -> {
                                if (!names.contains("COURSE")) {
                                    logger.warn("{} -- Course Table Added.", fileName);
                                }
                                String sql3 = getCreateTable("RATINGS");
                                if (names.contains("RATINGS")) {
                                    sql3 = SELECTONE;
                                }
                                conn.query(sql3).execute().onFailure().invoke(err ->
                                    logger.warn("Ratings Table Error: {}", err.getMessage()))
                                  .onItem().invoke(row3 -> {
                                      if (!names.contains("RATINGS")) {
                                          logger.warn("{} -- Ratings Table Added.", fileName);
                                      }
                                      String sql4 = getCreateTable("SCORES");
                                      if (names.contains("SCORES")) {
                                          sql4 = SELECTONE;
                                      }
                                      conn.query(sql4).execute().onFailure().invoke(err ->
                                          logger.error("Scores Table Error: {}", err.getMessage()))
                                        .onItem().invoke(row4 -> {
                                            if (!names.contains("SCORES")) {
                                                logger.warn("{} -- Scores Table Added.", fileName);
                                            }
                                        }).onTermination().invoke(() -> {
                                            finalPromise.complete(isCreateTables.toString());
                                            returnPromise.complete(isCreateTables.toString());
                                            conn.close().subscribeAsCompletionStage().isDone();
                                        }).subscribeAsCompletionStage().isDone();
                                  }).subscribeAsCompletionStage().isDone();
                            }).subscribeAsCompletionStage().isDone();
                      }).subscribeAsCompletionStage().isDone();
                })).subscribeAsCompletionStage().isDone();

            finalPromise.future().onItem().invoke(isTablesCreated -> {
                poolPromise.complete(pool4);
            }).subscribeAsCompletionStage().isDone();

        }).onFailure().invoke(err -> {
            logger.info("Starting Create Tables Error: {}", err.getMessage());
            err.printStackTrace();
        }).subscribeAsCompletionStage().isDone();
        return poolPromise;
    }

    protected JDBCPool getJavaPool() {
        return pool(DodexUtil.getVertx(), connectOptions, poolOptions);
    }

    @Override
    @SuppressWarnings("unchecked")
    public <T> T getPool4() {
        return (T) pool4;
    }

    @Override
    public Vertx getVertxR() {
        return null;
    }

    @Override
    public void setVertxR(Vertx vertx) {
        //
    }

    @Override
    public io.vertx.mutiny.core.Vertx getVertx() {
        return null;
    }

    @Override
    public void setVertx(io.vertx.mutiny.core.Vertx vertx) {
        //
    }

    @Override
    @SuppressWarnings("unchecked")
    public <T> T getConnectOptions() {
        return (T) connectOptions;
    }
    @Override
    public PoolOptions getPoolOptions() {
        return poolOptions;
    }
}