package code.api.util.migration

import code.api.util.APIUtil.{getPropsAsBoolValue, getPropsValue}
import code.api.util.{APIUtil, ApiPropsWithAlias}
import code.api.v4_0_0.DatabaseInfoJson
import code.consumer.Consumers
import code.context.MappedUserAuthContextUpdate
import code.customer.CustomerX
import code.migration.MigrationScriptLogProvider
import code.util.Helper.MdcLoggable
import com.github.dwickern.macros.NameOf.nameOf
import net.liftweb.mapper.Schemifier.getDefaultSchemaName
import net.liftweb.mapper.{BaseMetaMapper, DB}

import java.sql.{ResultSet, SQLException}
import java.text.SimpleDateFormat
import java.util.Date
import scala.collection.immutable
import scala.collection.mutable.HashMap

object Migration extends MdcLoggable {
  private val migrationScriptsEnabled = ApiPropsWithAlias.migrationScriptsEnabled
  private val executeAll = getPropsAsBoolValue("migration_scripts.execute_all", false)
  private val scriptsToExecute: immutable.Seq[String] = getPropsValue("list_of_migration_scripts_to_execute").toList.map(_.split(",")).flatten

  private def executeScript(blockOfCode: => Boolean): Boolean = {
    if(migrationScriptsEnabled) blockOfCode else migrationScriptsEnabled
  }
  
  private def runOnce(name: String)(blockOfCode: => Boolean): Boolean = {
    val toExecute: Boolean = executeAll || scriptsToExecute.contains(name)
    val isExecuted = MigrationScriptLogProvider.migrationScriptLogProvider.vend.isExecuted(name)
    (toExecute, isExecuted) match {
      case (true, false) => 
        logger.warn(s"Migration.database.$name is started at this instance.")
        blockOfCode
      case _ => true
    }
  }
  
  def saveLog(name: String, commitId: String, isSuccessful: Boolean, startDate: Long, endDate: Long, comment: String) = {
    var remark = comment
    if(comment.length() > 1024) {
      val traceUUID = APIUtil.generateUUID()
      val traceText = " Trace UUID: "  + traceUUID
      remark = remark.substring(0,970) + traceText
      logger.info(traceText)
      logger.info(comment)
    }
    MigrationScriptLogProvider.migrationScriptLogProvider.vend.saveLog(name, commitId, isSuccessful, startDate, endDate, remark) match {
      case true =>
        logger.warn(s"Migration.database.$name is executed at this instance.")
      case false =>
        logger.warn(s"Migration.database.$name is executed at this instance but the corresponding log is not saved!!!!!!")
    }
  }
  
  object database {
    
    def executeScripts(startedBeforeSchemifier: Boolean): Boolean = executeScript {
      dummyScript()
      addAccountAccessConsumerId()
//      populateMigrationOfViewDefinitionPermissions(startedBeforeSchemifier)
      generateAndPopulateMissingCustomerUUIDs(startedBeforeSchemifier)
      generateAndPopulateMissingConsumersUUIDs(startedBeforeSchemifier)
      populateTableRateLimiting()
      updateTableViewDefinition()
      bankAccountHoldersAndOwnerViewAccessInfo(startedBeforeSchemifier)
      alterTableMappedConsent()
      alterColumnChallengeAtTableMappedConsent()
      alterTableOpenIDConnectToken()
      alterTableMappedUserAuthContext(startedBeforeSchemifier)
      alterTableMappedUserAuthContextUpdate()
      populateNameAndAppTypeFieldsAtConsumerTable()
      populateAzpAndSubFieldsAtConsumerTable()
      populateTableBankAccountRouting()
      populateSettlementBankAccounts()
      alterColumnStatusAtTableMappedConsent()
      alterColumnDetailsAtTableTransactionRequest()
      deleteDuplicatedRowsInTheTableUserAuthContext(startedBeforeSchemifier)
      populateTheFieldDeletedAtResourceUser(startedBeforeSchemifier)
      populateTheFieldIsActiveAtProductAttribute(startedBeforeSchemifier)
      alterColumnUsernameProviderFirstnameAndLastnameAtAuthUser(startedBeforeSchemifier)
      populateMissingProviderAtAuthUser(startedBeforeSchemifier)
      alterColumnEmailAtResourceUser(startedBeforeSchemifier)
      alterColumnNameAtProductFee(startedBeforeSchemifier)
      addFastFirehoseAccountsView(startedBeforeSchemifier)
      addFastFirehoseAccountsMaterializedView(startedBeforeSchemifier)
      alterUserAuthContextColumnKeyAndValueLength(startedBeforeSchemifier)
      alterMappedTransactionRequestFieldsLengthMigration(startedBeforeSchemifier)
      dropIndexAtColumnUsernameAtTableAuthUser(startedBeforeSchemifier)
      dropIndexAtUserAuthContext()
      alterWebhookColumnUrlLength()
      dropConsentAuthContextDropIndex()
      alterMappedExpectedChallengeAnswerChallengeTypeLength()
      alterTransactionRequestChallengeChallengeTypeLength()
      alterUserAttributeNameLength()
      alterMappedCustomerAttribute(startedBeforeSchemifier)
      dropMappedBadLoginAttemptIndex()
      alterMetricColumnUrlLength()
//      populateViewDefinitionCanAddTransactionRequestToBeneficiary()
//      populateViewDefinitionCanSeeTransactionStatus()
      alterCounterpartyLimitFieldType()
      populateMigrationOfViewPermissions(startedBeforeSchemifier)
      changeTypeOfAudFieldAtConsumerTable()
      renameCustomerRoleNames()
      addUniqueIndexOnResourceUserUserId()
      addIndexOnMappedMetricUserId()
      alterRoleNameLength()
    }
    
    private def dummyScript(): Boolean = {
      val name = nameOf(dummyScript)
      runOnce(name) {
        val startDate = System.currentTimeMillis()
        val commitId: String = APIUtil.gitCommit
        val comment: String = "dummy comment"
        val isSuccessful = true
        val endDate = System.currentTimeMillis()
        saveLog(name, commitId, isSuccessful, startDate, endDate, comment)
        isSuccessful
      }
    }
    
//    private def populateViewDefinitionCanAddTransactionRequestToBeneficiary(): Boolean = {
//      val name = nameOf(populateViewDefinitionCanAddTransactionRequestToBeneficiary)
//      runOnce(name) {
//        MigrationOfViewDefinitionCanAddTransactionRequestToBeneficiary.populateTheField(name)
//      }
//    }  

//    private def populateViewDefinitionCanSeeTransactionStatus(): Boolean = {
//      val name = nameOf(populateViewDefinitionCanSeeTransactionStatus)
//      runOnce(name) {
//        MigrationOfViewDefinitionCanSeeTransactionStatus.populateTheField(name)
//      }
//    }  
    

//    private def populateMigrationOfViewDefinitionPermissions(startedBeforeSchemifier: Boolean): Boolean = {
//      if (startedBeforeSchemifier == true) {
//        logger.warn(s"Migration.database.populateMigrationOfViewDefinitionPermissions(true) cannot be run before Schemifier.")
//        true
//      } else {
//        val name = nameOf(populateMigrationOfViewDefinitionPermissions(startedBeforeSchemifier))
//        runOnce(name) {
//          MigrationOfViewDefinitionPermissions.populate(name)
//        }
//      }
//    }  
//
    private def populateMigrationOfViewPermissions(startedBeforeSchemifier: Boolean): Boolean = {
      if (startedBeforeSchemifier == true) {
        logger.warn(s"Migration.database.populateMigrationOfViewPermissions(true) cannot be run before Schemifier.")
        true
      } else {
        val name = nameOf(populateMigrationOfViewPermissions(startedBeforeSchemifier))
        runOnce(name) {
          MigrationOfViewPermissions.populate(name)
        }
      }
    }  
    
    private def generateAndPopulateMissingCustomerUUIDs(startedBeforeSchemifier: Boolean): Boolean = {
      if(startedBeforeSchemifier == true) {
        logger.warn(s"Migration.database.generateAndPopulateMissingCustomerUUIDs(true) cannot be run before Schemifier.")
        true
      } else {
        val name = nameOf(generateAndPopulateMissingCustomerUUIDs(startedBeforeSchemifier))
        runOnce(name) {
          val startDate = System.currentTimeMillis()
          val commitId: String = APIUtil.gitCommit
          val isSuccessful = CustomerX.customerProvider.vend.populateMissingUUIDs()
          val endDate = System.currentTimeMillis()

          val comment: String =
            s"""Execute `generateAndPopulateMissingCustomerUUIDs` 
               |Duration: ${endDate - startDate} ms;
             """.stripMargin
          saveLog(name, commitId, isSuccessful, startDate, endDate, comment)
          isSuccessful
        }
      }
      
    }

    private def generateAndPopulateMissingConsumersUUIDs(startedBeforeSchemifier: Boolean): Boolean = {
      if(startedBeforeSchemifier == true) {
        logger.warn(s"Migration.database.generateAndPopulateMissingConsumersUUIDs(true) cannot be run before Schemifier.")
        true
      } else {
        val name = nameOf(generateAndPopulateMissingConsumersUUIDs(startedBeforeSchemifier))
        runOnce(name) {
          val startDate = System.currentTimeMillis()
          val commitId: String = APIUtil.gitCommit
          val isSuccessful = Consumers.consumers.vend.populateMissingUUIDs()
          val endDate = System.currentTimeMillis()
          val comment: String =
            s"""Execute `generateAndPopulateMissingConsumersUUIDs` 
               |Duration: ${endDate - startDate} ms;
             """.stripMargin
          saveLog(name, commitId, isSuccessful, startDate, endDate, comment)
          isSuccessful
        }
      }
    }

    private def populateTableRateLimiting(): Boolean = {
      val name = nameOf(populateTableRateLimiting)
      runOnce(name) {
        TableRateLmiting.populate(name)
      }
    }
    
    private def updateTableViewDefinition(): Boolean = {
      val name = nameOf(updateTableViewDefinition)
      runOnce(name) {
        UpdateTableViewDefinition.populate(name)
      }
    }

    private def bankAccountHoldersAndOwnerViewAccessInfo(startedBeforeSchemifier: Boolean): Boolean = {
      if(startedBeforeSchemifier == true) {
        logger.warn(s"Migration.database.bankAccountHoldersAndOwnerViewAccessInfo(true) cannot be run before Schemifier.")
        true
      } else {
        val name = nameOf(bankAccountHoldersAndOwnerViewAccessInfo(startedBeforeSchemifier))
        runOnce(name) {
          BankAccountHoldersAndOwnerViewAccess.saveInfoBankAccountHoldersAndOwnerViewAccessInfo(name)
        }
      }
    }
    private def alterTableMappedConsent(): Boolean = {
      val name = nameOf(alterTableMappedConsent)
      runOnce(name) {
        MigrationOfMappedConsent.alterColumnJsonWebToken(name)
      }
    }
    private def alterColumnChallengeAtTableMappedConsent(): Boolean = {
      val name = nameOf(alterColumnChallengeAtTableMappedConsent)
      runOnce(name) {
        MigrationOfMappedConsent.alterColumnChallenge(name)
      }
    }
    private def alterTableOpenIDConnectToken(): Boolean = {
      val name = nameOf(alterTableOpenIDConnectToken)
      runOnce(name) {
        MigrationOfOpnIDConnectToken.alterColumnAccessToken(name)
        MigrationOfOpnIDConnectToken.alterColumnRefreshToken(name)
      }
    }
    private def populateNameAndAppTypeFieldsAtConsumerTable(): Boolean = {
      val name = nameOf(populateNameAndAppTypeFieldsAtConsumerTable)
      runOnce(name) {
        MigrationOfConsumer.populateNamAndAppType(name)
      }
    }
    private def populateAzpAndSubFieldsAtConsumerTable(): Boolean = {
      val name = nameOf(populateAzpAndSubFieldsAtConsumerTable)
      runOnce(name) {
        MigrationOfConsumer.populateAzpAndSub(name)
      }
    }
    private def changeTypeOfAudFieldAtConsumerTable(): Boolean = {
      val name = nameOf(changeTypeOfAudFieldAtConsumerTable)
      runOnce(name) {
        MigrationOfConsumer.alterTypeofAud(name)
      }
    }
    private def alterTableMappedUserAuthContext(startedBeforeSchemifier: Boolean): Boolean = {
      if(startedBeforeSchemifier == true) {
        logger.warn(s"Migration.database.alterTableMappedUserAuthContext(true) cannot be run before Schemifier.")
        true
      } else {
        val name = nameOf(alterTableMappedUserAuthContext(startedBeforeSchemifier))
        runOnce(name) {
          MigrationOfMappedUserAuthContext.dropUniqueIndex(name)
        }
      }
    }
    private def alterTableMappedUserAuthContextUpdate(): Boolean = {
      val name = nameOf(MappedUserAuthContextUpdate)
      runOnce(name) {
        MigrationOfMappedUserAuthContextUpdate.dropUniqueIndex(name)
      }
    }
    private def populateTableBankAccountRouting(): Boolean = {
      val name = nameOf(populateTableBankAccountRouting)
      runOnce(name) {
        MigrationOfAccountRoutings.populate(name)
      }
    }
    private def populateSettlementBankAccounts(): Boolean = {
      val name = nameOf(populateSettlementBankAccounts)
      runOnce(name) {
        MigrationOfSettlementAccounts.populate(name)
      }
    }
    private def alterColumnStatusAtTableMappedConsent(): Boolean = {
      val name = nameOf(alterColumnStatusAtTableMappedConsent)
      runOnce(name) {
        MigrationOfMappedConsent.alterColumnStatus(name)
      }
    }
    private def alterColumnDetailsAtTableTransactionRequest(): Boolean = {
      val name = nameOf(alterColumnDetailsAtTableTransactionRequest)
      runOnce(name) {
        MigrationOfTransactionRequerst.alterColumnDetails(name)
      }
    }
    private def deleteDuplicatedRowsInTheTableUserAuthContext(startedBeforeSchemifier: Boolean): Boolean = {
      if(startedBeforeSchemifier == true) {
        logger.warn(s"Migration.database.deleteDuplicatedRowsInTheTableUserAuthContext(true) cannot be run before Schemifier.")
        true
      } else {
        val name = nameOf(deleteDuplicatedRowsInTheTableUserAuthContext(startedBeforeSchemifier))
        runOnce(name) {
          MigrationOfUserAuthContext.removeDuplicates(name)
        }
      }
    }
    private def populateTheFieldDeletedAtResourceUser(startedBeforeSchemifier: Boolean): Boolean = {
      if(startedBeforeSchemifier == true) {
        logger.warn(s"Migration.database.populateTheFieldDeletedAtResourceUser(true) cannot be run before Schemifier.")
        true
      } else {
        val name = nameOf(populateTheFieldDeletedAtResourceUser(startedBeforeSchemifier))
        runOnce(name) {
          MigrationOfResourceUser.populateNewFieldIsDeleted(name)
        }
      }
    }
    private def populateTheFieldIsActiveAtProductAttribute(startedBeforeSchemifier: Boolean): Boolean = {
      if(startedBeforeSchemifier == true) {
        logger.warn(s"Migration.database.populateTheFieldIsActiveAtProductAttribute(true) cannot be run before Schemifier.")
        true
      } else {
        val name = nameOf(populateTheFieldIsActiveAtProductAttribute(startedBeforeSchemifier))
        runOnce(name) {
          MigrationOfProductAttribute.populateTheFieldIsActive(name)
        }
      }
    }
    private def alterColumnUsernameProviderFirstnameAndLastnameAtAuthUser(startedBeforeSchemifier: Boolean): Boolean = {
      if(startedBeforeSchemifier == true) {
        logger.warn(s"Migration.database.alterColumnUsernameProviderFirstnameAndLastnameAtAuthUser(true) cannot be run before Schemifier.")
        true
      } else {
        val name = nameOf(alterColumnUsernameProviderFirstnameAndLastnameAtAuthUser(startedBeforeSchemifier))
        runOnce(name) {
          MigrationOfAuthUser.alterColumnUsernameProviderEmailFirstnameAndLastname(name)
        }
      }
    }
    private def populateMissingProviderAtAuthUser(startedBeforeSchemifier: Boolean): Boolean = {
      if(startedBeforeSchemifier == true) {
        logger.warn(s"Migration.database.populateMissingProviderAtAuthUser(true) cannot be run before Schemifier.")
        true
      } else {
        val name = nameOf(populateMissingProviderAtAuthUser(startedBeforeSchemifier))
        runOnce(name) {
          MigrationOfAuthUser.populateMissingProviderWithLocalIdentity(name)
        }
      }
    }
    private def alterColumnEmailAtResourceUser(startedBeforeSchemifier: Boolean): Boolean = {
      if(startedBeforeSchemifier == true) {
        logger.warn(s"Migration.database.alterColumnEmailAtResourceUser(true) cannot be run before Schemifier.")
        true
      } else {
        val name = nameOf(alterColumnEmailAtResourceUser(startedBeforeSchemifier))
        runOnce(name) {
          MigrationOfResourceUser.alterColumnEmail(name)
        }
      }
    }
    private def alterColumnNameAtProductFee(startedBeforeSchemifier: Boolean): Boolean = {
      if(startedBeforeSchemifier == true) {
        logger.warn(s"Migration.database.alterColumnNameAtProductFee(true) cannot be run before Schemifier.")
        true
      } else {
        val name = nameOf(alterColumnNameAtProductFee(startedBeforeSchemifier))
        runOnce(name) {
          MigrationOfProductFee.alterColumnProductFeeName(name)
        }
      }
    }
    private def addFastFirehoseAccountsView(startedBeforeSchemifier: Boolean): Boolean = {
      if(startedBeforeSchemifier == true) {
        logger.warn(s"Migration.database.addfastFirehoseAccountsView(true) cannot be run before Schemifier.")
        true
      } else {
        val name = nameOf(addFastFirehoseAccountsView(startedBeforeSchemifier))
        runOnce(name) {
          MigrationOfFastFireHoseView.addFastFireHoseView(name)
        }
      }
    }
    
    private def addFastFirehoseAccountsMaterializedView(startedBeforeSchemifier: Boolean): Boolean = {
      if(startedBeforeSchemifier == true) {
        logger.warn(s"Migration.database.addfastFirehoseAccountsMaterializedView(true) cannot be run before Schemifier.")
        true
      } else {
        val name = nameOf(addFastFirehoseAccountsMaterializedView(startedBeforeSchemifier))
        runOnce(name) {
          MigrationOfFastFireHoseMaterializedView.addFastFireHoseMaterializedView(name)
        }
      }
    }
    
    private def alterUserAuthContextColumnKeyAndValueLength(startedBeforeSchemifier: Boolean): Boolean = {
      if(startedBeforeSchemifier == true) {
        logger.warn(s"Migration.database.alterUserAuthContextColumnKeyAndValueLength(true) cannot be run before Schemifier.")
        true
      } else {
        val name = nameOf(alterUserAuthContextColumnKeyAndValueLength(startedBeforeSchemifier))
        runOnce(name) {
          MigrationOfUserAuthContextFieldLength.alterColumnKeyAndValueLength(name)
        }
      }
    }    
    
    private def alterMappedTransactionRequestFieldsLengthMigration(startedBeforeSchemifier: Boolean): Boolean = {
      if(startedBeforeSchemifier == true) {
        logger.warn(s"Migration.database.alterMappedTransactionRequestFieldsLengthMigration(true) cannot be run before Schemifier.")
        true
      } else {
        val name = nameOf(alterMappedTransactionRequestFieldsLengthMigration(startedBeforeSchemifier))
        runOnce(name) {
          MigrationOfMappedTransactionRequestFieldsLength.alterMappedTransactionRequestFieldsLength(name)
        }
      }
    }
    
    private def dropIndexAtColumnUsernameAtTableAuthUser(startedBeforeSchemifier: Boolean): Boolean = {
      if(startedBeforeSchemifier == true) {
        logger.warn(s"Migration.database.dropIndexAtColumnUsernameAtTableAuthUser(true) cannot be run before Schemifier.")
        true
      } else {
        val name = nameOf(dropIndexAtColumnUsernameAtTableAuthUser(startedBeforeSchemifier))
        runOnce(name) {
          MigrationOfAuthUser.dropIndexAtColumnUsername(name)
        }
      }
    }

    private def dropIndexAtUserAuthContext(): Boolean = {
      val name = nameOf(dropIndexAtUserAuthContext)
      runOnce(name) {
        MigrationOfMappedUserAuthContext.dropUniqueIndex(name)
      }
    }
    
    private def addAccountAccessConsumerId(): Boolean = {
      val name = nameOf(addAccountAccessConsumerId)
      runOnce(name) {
        MigrationOfAccountAccessAddedConsumerId.addAccountAccessConsumerId(name)
      }
    }

    private def alterWebhookColumnUrlLength(): Boolean = {
      val name = nameOf(alterWebhookColumnUrlLength)
      runOnce(name) {
        MigrationOfWebhookUrlFieldLength.alterColumnUrlLength(name)
      }
    }

    private def alterMetricColumnUrlLength(): Boolean = {
      val name = nameOf(alterMetricColumnUrlLength)
      runOnce(name) {
        MigrationOfMetricTable.alterColumnCorrelationidLength(name)
      }
    }

    private def dropConsentAuthContextDropIndex(): Boolean = {
      val name = nameOf(dropConsentAuthContextDropIndex)
      runOnce(name) {
        MigrationOfConsentAuthContextDropIndex.dropUniqueIndex(name)
      }
    }
  
    private def alterMappedExpectedChallengeAnswerChallengeTypeLength(): Boolean = {
      val name = nameOf(alterMappedExpectedChallengeAnswerChallengeTypeLength)
      runOnce(name) {
        MigrationOfMappedExpectedChallengeAnswerFieldLength.alterColumnLength(name)
      }
    }
  
    private def alterTransactionRequestChallengeChallengeTypeLength(): Boolean = {
      val name = nameOf(alterTransactionRequestChallengeChallengeTypeLength)
      runOnce(name) {
        MigrationOfTransactionRequestChallengeChallengeTypeLength.alterColumnChallengeChallengeTypeLength(name)
      }
    }  
  
    private def alterUserAttributeNameLength(): Boolean = {
      val name = nameOf(alterUserAttributeNameLength)
      runOnce(name) {
        MigrationOfUserAttributeNameFieldLength.alterNameLength(name)
      }
    }  
    private def alterMappedCustomerAttribute(startedBeforeSchemifier: Boolean): Boolean = {
      if(startedBeforeSchemifier == true) {
        logger.warn(s"Migration.database.alterMappedCustomerAttribute(true) cannot be run before Schemifier.")
        true
      } else {
        val name = nameOf(alterMappedCustomerAttribute(startedBeforeSchemifier))
        runOnce(name) {
          MigrationOfCustomerAttributes.alterColumnValue(name)
        }
      }
    }

    private def dropMappedBadLoginAttemptIndex(): Boolean = {
      val name = nameOf(dropMappedBadLoginAttemptIndex)
      runOnce(name) {
        MigrationOfMappedBadLoginAttemptDropIndex.dropUniqueIndex(name)
      }
    }

    private def alterCounterpartyLimitFieldType(): Boolean = {
      val name = nameOf(alterCounterpartyLimitFieldType)
      runOnce(name) {
        MigrationOfCounterpartyLimitFieldType.alterCounterpartyLimitFieldType(name)
      }
    }

    private def renameCustomerRoleNames(): Boolean = {
      val name = nameOf(renameCustomerRoleNames)
      runOnce(name) {
        MigrationOfCustomerRoleNames.renameCustomerRoles(name)
      }
    }

    private def addUniqueIndexOnResourceUserUserId(): Boolean = {
      val name = nameOf(addUniqueIndexOnResourceUserUserId)
      runOnce(name) {
        MigrationOfUserIdIndexes.addUniqueIndexOnResourceUserUserId(name)
      }
    }

    private def addIndexOnMappedMetricUserId(): Boolean = {
      val name = nameOf(addIndexOnMappedMetricUserId)
      runOnce(name) {
        MigrationOfUserIdIndexes.addIndexOnMappedMetricUserId(name)
      }
    }
    
    private def alterRoleNameLength(): Boolean = {
      val name = nameOf(alterRoleNameLength)
      runOnce(name) {
        MigrationOfRoleNameFieldLength.alterRoleNameLength(name)
      }
    }
  }

  /**
    * In this object we put functions dedicated to common database tasks.
    */
  object DbFunction {
    /**
      * This function is copied from the module net.liftweb.mapper.Schemifier
      */
    private def using[RetType <: Any, VarType <: ResultSet](f: => VarType)(f2: VarType => RetType): RetType = {
      val theVar = f
      try {
        f2(theVar)
      } finally {
        theVar.close()
      }
    }
    /**
      * This function is copied from the module "net.liftweb.mapper.Schemifier".
      * The purpose is to provide answer does a table exist at a database instance.
      * For instance migration scripts needs to differentiate update of an instance from build a new one from scratch.
     *  note: 07.05.2024 now. we get the connection from HikariDatasource.ds instead of Liftweb.
      */
    def tableExists (table: BaseMetaMapper, actualTableNames: HashMap[String, String] = new HashMap[String, String]()): Boolean = {
      DB.use(net.liftweb.util.DefaultConnectionIdentifier) {
        conn =>
          val md = conn.getMetaData
          val schema =  getDefaultSchemaName(conn)
      
          using(md.getTables(null, schema, null, null)){ rs =>
            def hasTable(rs: ResultSet): Boolean =
              if (!rs.next) false
              else rs.getString(3) match {
                case s if s.toLowerCase == table._dbTableNameLC.toLowerCase => actualTableNames(table._dbTableNameLC) = s; true
                case _ => hasTable(rs)
              }
    
            hasTable(rs)
          }
      }
    }
    /**
      * The purpose is to provide answer does a procedure exist at a database instance.
      */
    def procedureExists(name: String): Boolean = {
      DB.use(net.liftweb.util.DefaultConnectionIdentifier) {
        conn =>
          val md = conn.getMetaData
          val schema = getDefaultSchemaName(conn)
          using(md.getProcedures(null, schema, null)){ rs =>
            def hasProcedure(rs: ResultSet): Boolean =
              if (!rs.next) false
              else rs.getString(3) match {
                case s if s.toLowerCase == name => true
                case _ => hasProcedure(rs)
              }
            hasProcedure(rs)
          }
      }
    }


    /**
      * The purpose is to provide info about the database in mapper mode.
      */
    def mapperDatabaseInfo: DatabaseInfoJson = {
      DB.use(net.liftweb.util.DefaultConnectionIdentifier) {
        conn =>
          val md = conn.getMetaData
          val productName = md.getDatabaseProductName()
          val productVersion = md.getDatabaseProductVersion()
          DatabaseInfoJson(product_name = productName, product_version = productVersion)
      }
    }

    /**
      * This function is copied from the module "net.liftweb.mapper.Schemifier".
      * 
      * Creates an SQL command and optionally executes it.
      *
      * @param performWrite Whether the SQL command should be executed.
      * @param logFunc Logger.
      * @param connection Database connection.
      * @param makeSql Factory for SQL command.
      *
      * @return SQL command.
      */
    def maybeWrite(performWrite: Boolean, logFunc: (=> AnyRef) => Unit) (makeSql: () => String) : String ={
      DB.use(net.liftweb.util.DefaultConnectionIdentifier) {
        conn =>
          val ct = makeSql()
          logger.trace("maybeWrite DDL: " + ct)
          if (performWrite) {
            logFunc(ct)
            val st = conn.createStatement
            st.execute(ct)
            st.close
          }
          ct
      }
    }

    /**
      * This function makes a copy on an table
      * @param table The table we want to back up
      * @return true in case of success or false otherwise
      */
    def makeBackUpOfTable(table: BaseMetaMapper): Boolean ={
      DB.use(net.liftweb.util.DefaultConnectionIdentifier) {
        conn =>
          try {
            val tableName = table.dbTableName
            val sdf = new SimpleDateFormat("yyyy_MM_dd_HH_mm_ss_SSS")
            val resultDate = new Date(System.currentTimeMillis())
            val dbDriver = APIUtil.getPropsValue("db.driver","org.h2.Driver")
            val sqlQuery = if (dbDriver.contains("com.microsoft.sqlserver.jdbc.SQLServerDriver")) {
              s"SELECT * INTO ${tableName}_backup_${sdf.format(resultDate)} FROM $tableName;"
            }else{
              s"CREATE TABLE ${tableName}_backup_${sdf.format(resultDate)} AS (SELECT * FROM $tableName);"
            }
            DB.prepareStatement(sqlQuery, conn){
              stmt => stmt.execute() //statement.executeQuery() expects a resultset and you don't get one.
              // Use statement.execute() for an ALTER-statement to avoid this issue.
            }
            true
          } catch {
            case e: SQLException => 
              logger.error(e)
              false
          }
      }
    }
  }
  
}
