package com.ustadmobile.core.db.dao

import com.ustadmobile.door.DoorDbType
import com.ustadmobile.door.EntityInsertionAdapter
import com.ustadmobile.door.PreparedStatementConfig
import com.ustadmobile.door.ext.prepareAndUseStatementAsync
import com.ustadmobile.door.flow.doorFlow
import com.ustadmobile.door.jdbc.PreparedStatement
import com.ustadmobile.door.jdbc.ext.executeQueryAsyncKmp
import com.ustadmobile.door.jdbc.ext.executeUpdateAsyncKmp
import com.ustadmobile.door.jdbc.ext.mapNextRow
import com.ustadmobile.door.jdbc.ext.mapRows
import com.ustadmobile.door.jdbc.ext.useResults
import com.ustadmobile.door.room.RoomDatabase
import com.ustadmobile.lib.db.entities.Language
import com.ustadmobile.lib.db.entities.SiteTerms
import com.ustadmobile.lib.db.entities.SiteTermsWithLanguage
import kotlin.Boolean
import kotlin.IllegalArgumentException
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.collections.List
import kotlinx.coroutines.flow.Flow

public class SiteTermsDao_JdbcImpl(
  public val _db: RoomDatabase,
) : SiteTermsDao() {
  public val _insertAdapterSiteTerms_abort: EntityInsertionAdapter<SiteTerms> = object :
      EntityInsertionAdapter<SiteTerms>(_db) {
    override fun makeSql(returnsId: Boolean): String = when(dbType) {
      DoorDbType.SQLITE -> {
        "INSERT INTO SiteTerms (sTermsUid, termsHtml, sTermsLang, sTermsLangUid, sTermsActive, sTermsLastChangedBy, sTermsPrimaryCsn, sTermsLocalCsn, sTermsLct) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)"
      }
      DoorDbType.POSTGRES ->  {
        "INSERT INTO SiteTerms (sTermsUid, termsHtml, sTermsLang, sTermsLangUid, sTermsActive, sTermsLastChangedBy, sTermsPrimaryCsn, sTermsLocalCsn, sTermsLct) VALUES(COALESCE(?,nextval('SiteTerms_sTermsUid_seq')), ?, ?, ?, ?, ?, ?, ?, ?)" + if(returnsId) { " RETURNING sTermsUid" } else "" 
      }
      else -> {
        throw IllegalArgumentException("Unsupported db type")
      }
    }

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement, entity: SiteTerms) {
      if(entity.sTermsUid == 0L) {
        stmt.setObject(1, null)
      } else {
        stmt.setLong(1, entity.sTermsUid)
      }
      stmt.setString(2, entity.termsHtml)
      stmt.setString(3, entity.sTermsLang)
      stmt.setLong(4, entity.sTermsLangUid)
      stmt.setBoolean(5, entity.sTermsActive)
      stmt.setInt(6, entity.sTermsLastChangedBy)
      stmt.setLong(7, entity.sTermsPrimaryCsn)
      stmt.setLong(8, entity.sTermsLocalCsn)
      stmt.setLong(9, entity.sTermsLct)
    }
  }

  public val _insertAdapterSiteTerms_upsert: EntityInsertionAdapter<SiteTerms> = object :
      EntityInsertionAdapter<SiteTerms>(_db) {
    override fun makeSql(returnsId: Boolean): String = when(dbType) {
      DoorDbType.SQLITE -> {
        "INSERT OR REPLACE INTO SiteTerms (sTermsUid, termsHtml, sTermsLang, sTermsLangUid, sTermsActive, sTermsLastChangedBy, sTermsPrimaryCsn, sTermsLocalCsn, sTermsLct) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)"
      }
      DoorDbType.POSTGRES ->  {
        "INSERT INTO SiteTerms (sTermsUid, termsHtml, sTermsLang, sTermsLangUid, sTermsActive, sTermsLastChangedBy, sTermsPrimaryCsn, sTermsLocalCsn, sTermsLct) VALUES(COALESCE(?,nextval('SiteTerms_sTermsUid_seq')), ?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT (sTermsUid) DO UPDATE SET termsHtml = excluded.termsHtml,sTermsLang = excluded.sTermsLang,sTermsLangUid = excluded.sTermsLangUid,sTermsActive = excluded.sTermsActive,sTermsLastChangedBy = excluded.sTermsLastChangedBy,sTermsPrimaryCsn = excluded.sTermsPrimaryCsn,sTermsLocalCsn = excluded.sTermsLocalCsn,sTermsLct = excluded.sTermsLct" + if(returnsId) { " RETURNING sTermsUid" } else "" 
      }
      else -> {
        throw IllegalArgumentException("Unsupported db type")
      }
    }

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement, entity: SiteTerms) {
      if(entity.sTermsUid == 0L) {
        stmt.setObject(1, null)
      } else {
        stmt.setLong(1, entity.sTermsUid)
      }
      stmt.setString(2, entity.termsHtml)
      stmt.setString(3, entity.sTermsLang)
      stmt.setLong(4, entity.sTermsLangUid)
      stmt.setBoolean(5, entity.sTermsActive)
      stmt.setInt(6, entity.sTermsLastChangedBy)
      stmt.setLong(7, entity.sTermsPrimaryCsn)
      stmt.setLong(8, entity.sTermsLocalCsn)
      stmt.setLong(9, entity.sTermsLct)
    }
  }

  override suspend fun insertAsync(siteTerms: SiteTerms): Long {
    val _retVal = _insertAdapterSiteTerms_abort.insertAndReturnIdAsync(siteTerms)
    return _retVal
  }

  override suspend fun upsertList(termList: List<SiteTerms>) {
    _insertAdapterSiteTerms_upsert.insertListAsync(termList)
  }

  public override suspend fun insertListAsync(entityList: List<SiteTerms>) {
    _insertAdapterSiteTerms_abort.insertListAsync(entityList)
  }

  public override suspend fun updateListAsync(entityList: List<SiteTerms>) {
    val _sql =
        "UPDATE SiteTerms SET termsHtml = ?, sTermsLang = ?, sTermsLangUid = ?, sTermsActive = ?, sTermsLastChangedBy = ?, sTermsPrimaryCsn = ?, sTermsLocalCsn = ?, sTermsLct = ? WHERE sTermsUid = ?"
    _db.prepareAndUseStatementAsync(_sql) {
       _stmt ->
      _stmt.getConnection().setAutoCommit(false)
      for(_entity in entityList) {
        _stmt.setString(1, _entity.termsHtml)
        _stmt.setString(2, _entity.sTermsLang)
        _stmt.setLong(3, _entity.sTermsLangUid)
        _stmt.setBoolean(4, _entity.sTermsActive)
        _stmt.setInt(5, _entity.sTermsLastChangedBy)
        _stmt.setLong(6, _entity.sTermsPrimaryCsn)
        _stmt.setLong(7, _entity.sTermsLocalCsn)
        _stmt.setLong(8, _entity.sTermsLct)
        _stmt.setLong(9, _entity.sTermsUid)
        _stmt.executeUpdateAsyncKmp()
      }
      _stmt.getConnection().commit()
    }
  }

  override suspend fun findSiteTerms(langCode: String): SiteTerms? =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT * FROM SiteTerms WHERE sTermsUid = coalesce(
    |            (SELECT sTermsUid FROM SiteTerms st_int WHERE st_int.sTermsLang = ? LIMIT 1),
    |            (SELECT sTermsUid FROM SiteTerms st_int WHERE st_int.sTermsLang = 'en' LIMIT 1),
    |            0)
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setString(1,langCode)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(null) {
        val _tmp_sTermsUid = _result.getLong("sTermsUid")
        val _tmp_termsHtml = _result.getString("termsHtml")
        val _tmp_sTermsLang = _result.getString("sTermsLang")
        val _tmp_sTermsLangUid = _result.getLong("sTermsLangUid")
        val _tmp_sTermsActive = _result.getBoolean("sTermsActive")
        val _tmp_sTermsLastChangedBy = _result.getInt("sTermsLastChangedBy")
        val _tmp_sTermsPrimaryCsn = _result.getLong("sTermsPrimaryCsn")
        val _tmp_sTermsLocalCsn = _result.getLong("sTermsLocalCsn")
        val _tmp_sTermsLct = _result.getLong("sTermsLct")
        SiteTerms().apply {
          this.sTermsUid = _tmp_sTermsUid
          this.termsHtml = _tmp_termsHtml
          this.sTermsLang = _tmp_sTermsLang
          this.sTermsLangUid = _tmp_sTermsLangUid
          this.sTermsActive = _tmp_sTermsActive
          this.sTermsLastChangedBy = _tmp_sTermsLastChangedBy
          this.sTermsPrimaryCsn = _tmp_sTermsPrimaryCsn
          this.sTermsLocalCsn = _tmp_sTermsLocalCsn
          this.sTermsLct = _tmp_sTermsLct
        }
      }
    }
  }

  override suspend fun findByUidAsync(uid: Long): SiteTerms? =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = "SELECT * FROM SiteTerms WHERE sTermsUid = ?",
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,uid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(null) {
        val _tmp_sTermsUid = _result.getLong("sTermsUid")
        val _tmp_termsHtml = _result.getString("termsHtml")
        val _tmp_sTermsLang = _result.getString("sTermsLang")
        val _tmp_sTermsLangUid = _result.getLong("sTermsLangUid")
        val _tmp_sTermsActive = _result.getBoolean("sTermsActive")
        val _tmp_sTermsLastChangedBy = _result.getInt("sTermsLastChangedBy")
        val _tmp_sTermsPrimaryCsn = _result.getLong("sTermsPrimaryCsn")
        val _tmp_sTermsLocalCsn = _result.getLong("sTermsLocalCsn")
        val _tmp_sTermsLct = _result.getLong("sTermsLct")
        SiteTerms().apply {
          this.sTermsUid = _tmp_sTermsUid
          this.termsHtml = _tmp_termsHtml
          this.sTermsLang = _tmp_sTermsLang
          this.sTermsLangUid = _tmp_sTermsLangUid
          this.sTermsActive = _tmp_sTermsActive
          this.sTermsLastChangedBy = _tmp_sTermsLastChangedBy
          this.sTermsPrimaryCsn = _tmp_sTermsPrimaryCsn
          this.sTermsLocalCsn = _tmp_sTermsLocalCsn
          this.sTermsLct = _tmp_sTermsLct
        }
      }
    }
  }

  override fun findAllTermsAsListFlow(activeOnly: Int): Flow<List<SiteTerms>> =
      _db.doorFlow(arrayOf("SiteTerms")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT SiteTerms.*
      |          FROM SiteTerms
      |         WHERE ? = 0 
      |            OR CAST(sTermsActive AS INTEGER) = 1
      |    
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setInt(1,activeOnly)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          val _tmp_sTermsUid = _result.getLong("sTermsUid")
          val _tmp_termsHtml = _result.getString("termsHtml")
          val _tmp_sTermsLang = _result.getString("sTermsLang")
          val _tmp_sTermsLangUid = _result.getLong("sTermsLangUid")
          val _tmp_sTermsActive = _result.getBoolean("sTermsActive")
          val _tmp_sTermsLastChangedBy = _result.getInt("sTermsLastChangedBy")
          val _tmp_sTermsPrimaryCsn = _result.getLong("sTermsPrimaryCsn")
          val _tmp_sTermsLocalCsn = _result.getLong("sTermsLocalCsn")
          val _tmp_sTermsLct = _result.getLong("sTermsLct")
          SiteTerms().apply {
            this.sTermsUid = _tmp_sTermsUid
            this.termsHtml = _tmp_termsHtml
            this.sTermsLang = _tmp_sTermsLang
            this.sTermsLangUid = _tmp_sTermsLangUid
            this.sTermsActive = _tmp_sTermsActive
            this.sTermsLastChangedBy = _tmp_sTermsLastChangedBy
            this.sTermsPrimaryCsn = _tmp_sTermsPrimaryCsn
            this.sTermsLocalCsn = _tmp_sTermsLocalCsn
            this.sTermsLct = _tmp_sTermsLct
          }
        }
      }
    }
  }

  override suspend fun findAllTerms(activeOnly: Int): List<SiteTerms> =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT SiteTerms.*
    |          FROM SiteTerms
    |         WHERE ? = 0 
    |            OR CAST(sTermsActive AS INTEGER) = 1
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setInt(1,activeOnly)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_sTermsUid = _result.getLong("sTermsUid")
        val _tmp_termsHtml = _result.getString("termsHtml")
        val _tmp_sTermsLang = _result.getString("sTermsLang")
        val _tmp_sTermsLangUid = _result.getLong("sTermsLangUid")
        val _tmp_sTermsActive = _result.getBoolean("sTermsActive")
        val _tmp_sTermsLastChangedBy = _result.getInt("sTermsLastChangedBy")
        val _tmp_sTermsPrimaryCsn = _result.getLong("sTermsPrimaryCsn")
        val _tmp_sTermsLocalCsn = _result.getLong("sTermsLocalCsn")
        val _tmp_sTermsLct = _result.getLong("sTermsLct")
        SiteTerms().apply {
          this.sTermsUid = _tmp_sTermsUid
          this.termsHtml = _tmp_termsHtml
          this.sTermsLang = _tmp_sTermsLang
          this.sTermsLangUid = _tmp_sTermsLangUid
          this.sTermsActive = _tmp_sTermsActive
          this.sTermsLastChangedBy = _tmp_sTermsLastChangedBy
          this.sTermsPrimaryCsn = _tmp_sTermsPrimaryCsn
          this.sTermsLocalCsn = _tmp_sTermsLocalCsn
          this.sTermsLct = _tmp_sTermsLct
        }
      }
    }
  }

  override suspend fun findAllWithLanguageAsList(): List<SiteTermsWithLanguage> =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |SELECT SiteTerms.*, Language.*
    |        FROM SiteTerms
    |        LEFT JOIN Language ON SiteTerms.sTermsLangUid = Language.langUid
    |        WHERE CAST(sTermsActive AS INTEGER) = 1
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_sTermsUid = _result.getLong("sTermsUid")
        val _tmp_termsHtml = _result.getString("termsHtml")
        val _tmp_sTermsLang = _result.getString("sTermsLang")
        val _tmp_sTermsLangUid = _result.getLong("sTermsLangUid")
        val _tmp_sTermsActive = _result.getBoolean("sTermsActive")
        val _tmp_sTermsLastChangedBy = _result.getInt("sTermsLastChangedBy")
        val _tmp_sTermsPrimaryCsn = _result.getLong("sTermsPrimaryCsn")
        val _tmp_sTermsLocalCsn = _result.getLong("sTermsLocalCsn")
        val _tmp_sTermsLct = _result.getLong("sTermsLct")
        var _tmp_Language_nullCount = 0
        val _tmp_langUid = _result.getLong("langUid")
        if(_result.wasNull()) _tmp_Language_nullCount++
        val _tmp_name = _result.getString("name")
        if(_result.wasNull()) _tmp_Language_nullCount++
        val _tmp_iso_639_1_standard = _result.getString("iso_639_1_standard")
        if(_result.wasNull()) _tmp_Language_nullCount++
        val _tmp_iso_639_2_standard = _result.getString("iso_639_2_standard")
        if(_result.wasNull()) _tmp_Language_nullCount++
        val _tmp_iso_639_3_standard = _result.getString("iso_639_3_standard")
        if(_result.wasNull()) _tmp_Language_nullCount++
        val _tmp_Language_Type = _result.getString("Language_Type")
        if(_result.wasNull()) _tmp_Language_nullCount++
        val _tmp_languageActive = _result.getBoolean("languageActive")
        if(_result.wasNull()) _tmp_Language_nullCount++
        val _tmp_langLocalChangeSeqNum = _result.getLong("langLocalChangeSeqNum")
        if(_result.wasNull()) _tmp_Language_nullCount++
        val _tmp_langMasterChangeSeqNum = _result.getLong("langMasterChangeSeqNum")
        if(_result.wasNull()) _tmp_Language_nullCount++
        val _tmp_langLastChangedBy = _result.getInt("langLastChangedBy")
        if(_result.wasNull()) _tmp_Language_nullCount++
        val _tmp_langLct = _result.getLong("langLct")
        if(_result.wasNull()) _tmp_Language_nullCount++
        val _tmp_Language_isAllNull = _tmp_Language_nullCount == 11
        SiteTermsWithLanguage().apply {
          this.sTermsUid = _tmp_sTermsUid
          this.termsHtml = _tmp_termsHtml
          this.sTermsLang = _tmp_sTermsLang
          this.sTermsLangUid = _tmp_sTermsLangUid
          this.sTermsActive = _tmp_sTermsActive
          this.sTermsLastChangedBy = _tmp_sTermsLastChangedBy
          this.sTermsPrimaryCsn = _tmp_sTermsPrimaryCsn
          this.sTermsLocalCsn = _tmp_sTermsLocalCsn
          this.sTermsLct = _tmp_sTermsLct
          if(!_tmp_Language_isAllNull) {
            this.stLanguage = Language().apply {
              this.langUid = _tmp_langUid
              this.name = _tmp_name
              this.iso_639_1_standard = _tmp_iso_639_1_standard
              this.iso_639_2_standard = _tmp_iso_639_2_standard
              this.iso_639_3_standard = _tmp_iso_639_3_standard
              this.Language_Type = _tmp_Language_Type
              this.languageActive = _tmp_languageActive
              this.langLocalChangeSeqNum = _tmp_langLocalChangeSeqNum
              this.langMasterChangeSeqNum = _tmp_langMasterChangeSeqNum
              this.langLastChangedBy = _tmp_langLastChangedBy
              this.langLct = _tmp_langLct
            }
          }
        }
      }
    }
  }

  override suspend fun updateActiveByUid(
    sTermsUid: Long,
    active: Boolean,
    changeTime: Long,
  ) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        UPDATE SiteTerms 
      |           SET sTermsActive = ?,
      |               sTermsLct = ?
      |         WHERE sTermsUid = ?
      |        
      """.trimMargin(),
      readOnly = false,)
    ) { _stmt -> 
      _stmt.setBoolean(1,active)
      _stmt.setLong(2,changeTime)
      _stmt.setLong(3,sTermsUid)
      _stmt.executeUpdateAsyncKmp()
    }
  }

  override suspend fun findAvailableSiteTermLanguages(active: Int): List<String?> =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT SiteTerms.sTermsLang
    |          FROM SiteTerms
    |         WHERE CAST(SiteTerms.sTermsActive AS INTEGER) = ? 
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setInt(1,active)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        _result.getString(1)
      }
    }
  }

  override suspend fun findLatestByLanguage(lang: String): SiteTerms? =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT SiteTerms.*
    |          FROM SiteTerms
    |         WHERE SiteTerms.sTermsLang = ?
    |           AND CAST(SiteTerms.sTermsActive AS INTEGER) = 1
    |      ORDER BY SiteTerms.sTermsLct DESC
    |         LIMIT 1     
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setString(1,lang)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(null) {
        val _tmp_sTermsUid = _result.getLong("sTermsUid")
        val _tmp_termsHtml = _result.getString("termsHtml")
        val _tmp_sTermsLang = _result.getString("sTermsLang")
        val _tmp_sTermsLangUid = _result.getLong("sTermsLangUid")
        val _tmp_sTermsActive = _result.getBoolean("sTermsActive")
        val _tmp_sTermsLastChangedBy = _result.getInt("sTermsLastChangedBy")
        val _tmp_sTermsPrimaryCsn = _result.getLong("sTermsPrimaryCsn")
        val _tmp_sTermsLocalCsn = _result.getLong("sTermsLocalCsn")
        val _tmp_sTermsLct = _result.getLong("sTermsLct")
        SiteTerms().apply {
          this.sTermsUid = _tmp_sTermsUid
          this.termsHtml = _tmp_termsHtml
          this.sTermsLang = _tmp_sTermsLang
          this.sTermsLangUid = _tmp_sTermsLangUid
          this.sTermsActive = _tmp_sTermsActive
          this.sTermsLastChangedBy = _tmp_sTermsLastChangedBy
          this.sTermsPrimaryCsn = _tmp_sTermsPrimaryCsn
          this.sTermsLocalCsn = _tmp_sTermsLocalCsn
          this.sTermsLct = _tmp_sTermsLct
        }
      }
    }
  }
}
