package golf.handicap.hibernate.db

import dmo.fs.entities.PersistedRatings
import dmo.fs.entities.PersistedScores
import dmo.fs.utils.Constants
import dmo.fs.utils.Err
import golf.handicap.Golfer
import golf.handicap.Score
import io.smallrye.mutiny.Uni
import io.vertx.core.json.JsonArray
import io.vertx.core.json.JsonObject
import io.vertx.mutiny.core.Promise
import jakarta.persistence.criteria.CriteriaBuilder
import jakarta.persistence.criteria.CriteriaDelete
import jakarta.persistence.criteria.Root
import org.hibernate.reactive.mutiny.Mutiny
import org.slf4j.LoggerFactory
import java.math.BigDecimal
import java.math.RoundingMode
import java.sql.SQLException
import java.text.ParsePosition
import java.text.SimpleDateFormat
import java.time.LocalDateTime
import java.time.Year
import java.time.ZoneId
import java.time.format.DateTimeFormatter
import java.util.*


class PopulateGolferScores : SqlConstants(), IPopulateGolferScores {
  private val teeDate = SimpleDateFormat("yyyy-MM-dd")
  private val teeYear = SimpleDateFormat("yyyy")
  private var beginDate: String? = null
  private var endDate: String? = null
  private var maxRows = 20
  private var gettingData = false
  private var beginGolfDate: Date? = null
  private var endGolfDate: Date? = null
  private var overlapYears = false

  /*

   */

  companion object {
    private val LOGGER = LoggerFactory.getLogger(PopulateGolferScores::class.java.name)

    enum class RatingKey(val entry: String) {
      PIN("PIN"),
      RATING("TEE_RATING"),
      SLOPE("TEE_SLOPE"),
      ADJUSTED("ADJUSTED_SCORE"),
      TIME("TEE_TIME"),
      SEQ("COURSE_SEQ"),
      PAR("TEE_PAR"),
      NAME("COURSE_NAME"),
      GROSS("GROSS_SCORE"),
      NET("NET_SCORE"),
      HANDICAP("HANDICAP"),
      TEES("COURSE_TEES"),
      USED("USED")
    }

    enum class Parms(val entry: String) {
      USED("used"),
      SEQ("seq"),
      TIME("time"),
      PIN("pin"),
      BEGIN("begin"),
      END("end"),
      FIRST("first"),
      LAST("last"),
      HANDICAP("handicap"),
      NET("net"),
      GROSS("gross"),
      ADJUSTED("adjusted"),
      TEE("tee"),
      COURSE("course"),
    }
  }

  private fun setPeriod(year: String) {
    beginDate = "$year-01-01"
    endDate = (year.toInt() + 1).toString() + "-01-01"
    beginGolfDate = beginDate?.let { teeDate.parse(it, ParsePosition(0)) }
    endGolfDate = endDate?.let { teeDate.parse(it, ParsePosition(0)) }
  }

  fun useCurrentYearOnly(thisYearOnly: Boolean) {
    overlapYears = thisYearOnly
  }

  override fun getGolferScores(
    golfer: Golfer,
    rows: Int,
    sessionFactory: Mutiny.SessionFactory
  ): Uni<Map<String, Any?>> {
    val scorePromise = Promise.promise<Map<String, Any?>>()
    val oldRows = this.maxRows
    this.maxRows = rows
    gettingData = true

    getVertxContext().runOnContext { _ ->
      sessionFactory.openSession().onItemOrFailure().invoke { session, err ->
        if (err != null) {
          LOGGER.error(Err.displayErr(75), err.message)
        }
        getGolferScores(golfer, session).onItemOrFailure().invoke { map, err ->
          if (err != null) {
            LOGGER.error(Err.displayErr(76), err.message)
          }
          gettingData = false
          this.maxRows = oldRows

          session.close().onItemOrFailure().invoke { _, err ->
            if (err != null) {
              LOGGER.error(Err.displayErr(77), err.message)
            }
            scorePromise.complete(map)
          }.subscribeAsCompletionStage()
        }.subscribeAsCompletionStage()
      }.subscribeAsCompletionStage()
    }

    return scorePromise.future()
  }

  @Throws(Exception::class)
  fun getGolferScores(golfer: Golfer, session: Mutiny.Session): Uni<Map<String, Any?>> {
    val scoresPromise = Promise.promise<Map<String, Any?>>()
    val tableMap: MutableMap<String, Any> = HashMap()
    val golferPin = golfer.pin
    val previousYear = Year.now().value - 1
    var isCalcHandicap = false
    overlapYears = golfer.overlap
    beginDate = if (overlapYears) "01-01-$previousYear" else beginDate

    val query = if (gettingData) {
      if (golferPin.isNullOrEmpty()) {
        session.createQuery(
          GOLFER_SCORES.replace(
            ":R", Constants.SCORES_WITH_NAME
          ),
          PersistedScores::class.java
        )
          .setMaxResults(maxRows)
          .setParameter(Parms.FIRST.entry, golfer.firstName)
          .setParameter(Parms.LAST.entry, golfer.lastName)
      } else {
        session.createQuery(
          GOLFER_SCORES.replace(":R", Constants.SCORES_WITH_PIN), PersistedScores::class.java
        )
          .setMaxResults(maxRows)
          .setParameter(Parms.PIN.entry, golfer.pin)
      }
    }
    /* Used to calculate golfer's handicap */
    else {
      isCalcHandicap = true

      session.createQuery(
        HANDICAP_DATA, PersistedScores::class.java
      ).setMaxResults(maxRows)
        .setParameter(Parms.PIN.entry, golfer.pin)
    }

    val tableArray = JsonArray()
    query
      .setParameter(Parms.BEGIN.entry, dateToLocalDateTime(beginGolfDate!!))
      .setParameter(Parms.END.entry, dateToLocalDateTime(endGolfDate!!))
      .resultList.onItemOrFailure().invoke { scores, err ->

        if (err != null) {
          LOGGER.error(Err.displayErr(78), err.message)
          scoresPromise.complete(tableMap)
          throw RuntimeException(err.message)
        }

        scores.forEach { score ->
          val tableObject = JsonObject()
          var persistedRating = PersistedRatings()

          if (isCalcHandicap) {
            try {
              val ratingSet: Set<PersistedRatings> = score.course?.ratings!!

              for (rating in ratingSet) {
                if (score.courseTees == rating.tee) {
                  persistedRating = rating
                }
              }
            } catch (e: Exception) {
              e.printStackTrace()
            }
          }

          if (isCalcHandicap) {
            tableObject.put(RatingKey.PIN.entry, score.pin)
              .put(RatingKey.RATING.entry, persistedRating.teeRating.toString())
              .put(RatingKey.SLOPE.entry, persistedRating.teeSlope.toString())
              .put(RatingKey.ADJUSTED.entry, score.adjustedScore.toString())
              .put(RatingKey.TIME.entry, score.teeTime.toString())
              .put(RatingKey.SEQ.entry, persistedRating.course_seq.toString())
              .put(RatingKey.PAR.entry, persistedRating.teePar.toString())
          } else {
            tableObject.put(RatingKey.NAME.entry, score.course?.courseName)
              .put(RatingKey.GROSS.entry, score.grossScore.toString())
              .put(
                RatingKey.NET.entry,
                BigDecimal(score.netScore.toString()).setScale(1, RoundingMode.UP)
              )
              .put(RatingKey.ADJUSTED.entry, score.adjustedScore.toString())
              .put(RatingKey.HANDICAP.entry, BigDecimal(score.handicap.toString()).setScale(1, RoundingMode.UP))
              .put(RatingKey.TEES.entry, score.courseTees.toString())
              .put(
                RatingKey.TIME.entry,
                if (gettingData) score.teeTime.toString()
                  .substring(0, 10) else score.teeTime.toString()
              )
              .put(RatingKey.USED.entry, if (score.used == null) "" else score.used.toString())
          }
          tableArray.add(tableObject)
        }

        tableMap["array"] = tableArray
        scoresPromise.complete(tableMap)
      }.subscribeAsCompletionStage()

    return scoresPromise.future()
  }

  override fun removeLastScore(golferPIN: String?, session: Mutiny.Session): Uni<String> {
    val usedPromise = Promise.promise<String>()
    var used: String = "N"

    session.createQuery(LAST_SCORE, List::class.java)
      .setParameter(Parms.PIN.entry, golferPIN)
      .singleResult.onItemOrFailure().invoke { list, err ->
        if (err != null && !err.message?.startsWith(Err.displayErr(66))!!) {
          LOGGER.error(Err.displayErr(79), err.message)
          usedPromise.complete(used)
        }

        if ((list != null && list.isEmpty()) || list == null) {
          usedPromise.complete(null)
        } else {
          used = list[0].toString()

          if (used != "Y") {
            used = "N"
          }
          val cb: CriteriaBuilder = session.criteriaBuilder
          val cd: CriteriaDelete<PersistedScores> = cb.createCriteriaDelete(PersistedScores::class.java)
          val scores: Root<PersistedScores> = cd.from(PersistedScores::class.java)
          cd.where(
            cb.equal(scores.get<String>(Parms.PIN.entry), golferPIN),
            cb.and(cb.equal(scores.get<LocalDateTime>("teeTime"), list[2]))
          )
          session.withTransaction { tx ->
            session.createQuery(cd).executeUpdate().onItemOrFailure()
              .invoke { _, throwable ->
                if (throwable != null) {
                  LOGGER.error(Err.displayErr(80), throwable.message)
                  usedPromise.complete(used)
                  tx.markForRollback()
                  throw RuntimeException(throwable.message)
                }
                session.flush().onItemOrFailure().invoke { _, throwable ->
                  if (throwable != null) {
                    tx.markForRollback()
                    throwable.printStackTrace()
                    usedPromise.complete(used)
                    throw RuntimeException(throwable.message)
                  }
                  session.clear()
                  usedPromise.complete(used)
                }.subscribeAsCompletionStage()
              }.subscribeAsCompletionStage()
            Uni.createFrom().item(tx)
          }.subscribeAsCompletionStage()
        }
      }.subscribeAsCompletionStage()

    return usedPromise.future()
  }

  @Throws(SQLException::class, InterruptedException::class)
  override fun setGolferHandicap(golfer: Golfer, session: Mutiny.Session): Uni<Int> {
    val updatedPromise = Promise.promise<Int>()

    session.createMutationQuery(UPDATE_HANDICAP)
      .setParameter(Parms.HANDICAP.entry, golfer.handicap)
      .setParameter(Parms.PIN.entry, golfer.pin).executeUpdate().onItemOrFailure()
      .invoke { rowsUpdated, throwable ->
        if (throwable != null) {
          session.close().onItemOrFailure().invoke { _, throwable ->
            updatedPromise.complete(0)
          }.subscribeAsCompletionStage()
          throw RuntimeException(throwable.message)
        }
        val score = golfer.score ?: run {
          Score()
        }

        session.createMutationQuery(UPDATE_SCORES)
          .setParameter(Parms.HANDICAP.entry, golfer.handicap)
          .setParameter(Parms.NET.entry, 0.0f)
          .setParameter(Parms.SEQ.entry, score.course?.course_key)
          .setParameter(Parms.PIN.entry, golfer.pin)
          .setParameter(Parms.TIME.entry, score.teeTime).executeUpdate().onItemOrFailure()
          .invoke { updated, throwable ->
            if (throwable != null) {
              LOGGER.error(Err.displayErr(81), throwable.message)
              session.close().onItemOrFailure().invoke { _, throwable ->
                updatedPromise.complete(0)
              }.subscribeAsCompletionStage()
              throw RuntimeException(throwable.message)
            }

            session.flush().onItemOrFailure().invoke { _, throwable ->
              if (throwable != null) {
                LOGGER.error(Err.displayErr(82), throwable.message)
                updatedPromise.complete(0)
                throw RuntimeException(throwable.message)
              }
              val totalUpdated = rowsUpdated + updated
              updatedPromise.complete(totalUpdated)
            }.subscribeAsCompletionStage()
          }.subscribeAsCompletionStage()
      }.subscribeAsCompletionStage()

    return updatedPromise.future()  //rowsUpdated
  }

  @Throws(Exception::class)
  fun setUsed(pin: String?, course: Int, teeTime: String, session: Mutiny.Session): Uni<Int> {
    val formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")
    val teeDate = LocalDateTime.parse("$teeTime:00".replace("T", " "), formatter)
    val usedPromise = Promise.promise<Int>()

    session.createMutationQuery(SET_USED)
      .setParameter(Parms.USED.entry, '*')
      .setParameter(Parms.SEQ.entry, course)
      .setParameter(Parms.TIME.entry, teeDate)
      .setParameter(Parms.PIN.entry, pin).executeUpdate().onItemOrFailure().invoke { used, err ->
        if (err != null) {
          LOGGER.error(Err.displayErr(83), err.message)
        }

        session.flush().onItem().invoke { _ ->
          usedPromise.complete(used)
        }.subscribeAsCompletionStage()
      }.subscribeAsCompletionStage()

    return usedPromise.future()
  }

  @Throws(Exception::class)
  fun clearUsed(pin: String?, session: Mutiny.Session): Uni<Int> {
    val usedPromise = Promise.promise<Int>()

    session.createMutationQuery(RESET_SCORES)
      .setParameter(Parms.USED.entry, Character.valueOf(' '))
      .setParameter(Parms.PIN.entry, pin).executeUpdate().onItemOrFailure().invoke { count, err ->
        if (err != null) {
          LOGGER.error(Err.displayErr(84), err.message)
        }
        session.flush().onItemOrFailure().invoke { _, throwable ->
          if (throwable != null) {
            LOGGER.error(Err.displayErr(84), throwable.message)
          }
          usedPromise.complete(count)
        }.subscribeAsCompletionStage()
      }.subscribeAsCompletionStage()

    return usedPromise.future()
  }

  init {
    setPeriod(teeYear.format(Date()))
  }

  private fun dateToLocalDateTime(date: Date): LocalDateTime {
    return LocalDateTime.ofInstant(date.toInstant(), ZoneId.systemDefault())
  }
}