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.createArrayOrProxyArrayOf
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.Person
import com.ustadmobile.lib.db.entities.PersonPicture
import com.ustadmobile.lib.db.entities.UserSession
import com.ustadmobile.lib.db.entities.UserSessionAndPerson
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 UserSessionDao_JdbcImpl(
  public val _db: RoomDatabase,
) : UserSessionDao() {
  public val _insertAdapterUserSession_abort: EntityInsertionAdapter<UserSession> = object :
      EntityInsertionAdapter<UserSession>(_db) {
    override fun makeSql(returnsId: Boolean): String = when(dbType) {
      DoorDbType.SQLITE -> {
        "INSERT INTO UserSession (usUid, usPcsn, usLcsn, usLcb, usLct, usPersonUid, usClientNodeId, usStartTime, usEndTime, usStatus, usReason, usAuth, usSessionType) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
      }
      DoorDbType.POSTGRES ->  {
        "INSERT INTO UserSession (usUid, usPcsn, usLcsn, usLcb, usLct, usPersonUid, usClientNodeId, usStartTime, usEndTime, usStatus, usReason, usAuth, usSessionType) VALUES(COALESCE(?,nextval('UserSession_usUid_seq')), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" + if(returnsId) { " RETURNING usUid" } else "" 
      }
      else -> {
        throw IllegalArgumentException("Unsupported db type")
      }
    }

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement, entity: UserSession) {
      if(entity.usUid == 0L) {
        stmt.setObject(1, null)
      } else {
        stmt.setLong(1, entity.usUid)
      }
      stmt.setLong(2, entity.usPcsn)
      stmt.setLong(3, entity.usLcsn)
      stmt.setLong(4, entity.usLcb)
      stmt.setLong(5, entity.usLct)
      stmt.setLong(6, entity.usPersonUid)
      stmt.setLong(7, entity.usClientNodeId)
      stmt.setLong(8, entity.usStartTime)
      stmt.setLong(9, entity.usEndTime)
      stmt.setInt(10, entity.usStatus)
      stmt.setInt(11, entity.usReason)
      stmt.setString(12, entity.usAuth)
      stmt.setInt(13, entity.usSessionType)
    }
  }

  override suspend fun insertSession(session: UserSession): Long {
    val _retVal = _insertAdapterUserSession_abort.insertAndReturnIdAsync(session)
    return _retVal
  }

  override suspend fun findSessionsByPerson(personUid: Long): List<UserSession> =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT UserSession.*
    |          FROM UserSession
    |         WHERE usPersonUid = ? 
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,personUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_usUid = _result.getLong("usUid")
        val _tmp_usPcsn = _result.getLong("usPcsn")
        val _tmp_usLcsn = _result.getLong("usLcsn")
        val _tmp_usLcb = _result.getLong("usLcb")
        val _tmp_usLct = _result.getLong("usLct")
        val _tmp_usPersonUid = _result.getLong("usPersonUid")
        val _tmp_usClientNodeId = _result.getLong("usClientNodeId")
        val _tmp_usStartTime = _result.getLong("usStartTime")
        val _tmp_usEndTime = _result.getLong("usEndTime")
        val _tmp_usStatus = _result.getInt("usStatus")
        val _tmp_usReason = _result.getInt("usReason")
        val _tmp_usAuth = _result.getString("usAuth")
        val _tmp_usSessionType = _result.getInt("usSessionType")
        UserSession().apply {
          this.usUid = _tmp_usUid
          this.usPcsn = _tmp_usPcsn
          this.usLcsn = _tmp_usLcsn
          this.usLcb = _tmp_usLcb
          this.usLct = _tmp_usLct
          this.usPersonUid = _tmp_usPersonUid
          this.usClientNodeId = _tmp_usClientNodeId
          this.usStartTime = _tmp_usStartTime
          this.usEndTime = _tmp_usEndTime
          this.usStatus = _tmp_usStatus
          this.usReason = _tmp_usReason
          this.usAuth = _tmp_usAuth
          this.usSessionType = _tmp_usSessionType
        }
      }
    }
  }

  override fun findAllLocalSessionsLive(): Flow<List<UserSessionAndPerson>> =
      _db.doorFlow(arrayOf("UserSession", "Person", "PersonPicture", "SyncNode")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |            SELECT UserSession.*, Person.*, PersonPicture.*
      |              FROM UserSession
      |                   JOIN Person 
      |                        ON Person.personUid = UserSession.usPersonUid
      |                   LEFT JOIN PersonPicture
      |                        ON PersonPicture.personPictureUid = UserSession.usPersonUid
      |             WHERE UserSession.usClientNodeId = (
      |                   SELECT COALESCE(
      |                          (SELECT nodeClientId 
      |                            FROM SyncNode
      |                           LIMIT 1), 0))
      |               AND UserSession.usStatus = 1        
      |               AND (UserSession.usSessionType & 8) != 8
      |            
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          var _tmp_Person_nullCount = 0
          val _tmp_personUid = _result.getLong("personUid")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_username = _result.getString("username")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_firstNames = _result.getString("firstNames")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_lastName = _result.getString("lastName")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_emailAddr = _result.getString("emailAddr")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_phoneNum = _result.getString("phoneNum")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_gender = _result.getInt("gender")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_active = _result.getBoolean("active")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_isPersonalAccount = _result.getBoolean("isPersonalAccount")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_dateOfBirth = _result.getLong("dateOfBirth")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personAddress = _result.getString("personAddress")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personOrgId = _result.getString("personOrgId")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personGroupUid = _result.getLong("personGroupUid")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personLct = _result.getLong("personLct")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personCountry = _result.getString("personCountry")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personType = _result.getInt("personType")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personMasterChangeSeqNum = _result.getLong("personMasterChangeSeqNum")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personLocalChangeSeqNum = _result.getLong("personLocalChangeSeqNum")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personLastChangedBy = _result.getInt("personLastChangedBy")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_admin = _result.getBoolean("admin")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personNotes = _result.getString("personNotes")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_fatherName = _result.getString("fatherName")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_fatherNumber = _result.getString("fatherNumber")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_motherName = _result.getString("motherName")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_motherNum = _result.getString("motherNum")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_Person_isAllNull = _tmp_Person_nullCount == 25
          var _tmp_PersonPicture_nullCount = 0
          val _tmp_personPictureUid = _result.getLong("personPictureUid")
          if(_result.wasNull()) _tmp_PersonPicture_nullCount++
          val _tmp_personPictureLct = _result.getLong("personPictureLct")
          if(_result.wasNull()) _tmp_PersonPicture_nullCount++
          val _tmp_personPictureUri = _result.getString("personPictureUri")
          if(_result.wasNull()) _tmp_PersonPicture_nullCount++
          val _tmp_personPictureThumbnailUri = _result.getString("personPictureThumbnailUri")
          if(_result.wasNull()) _tmp_PersonPicture_nullCount++
          val _tmp_fileSize = _result.getInt("fileSize")
          if(_result.wasNull()) _tmp_PersonPicture_nullCount++
          val _tmp_personPictureActive = _result.getBoolean("personPictureActive")
          if(_result.wasNull()) _tmp_PersonPicture_nullCount++
          val _tmp_PersonPicture_isAllNull = _tmp_PersonPicture_nullCount == 6
          var _tmp_UserSession_nullCount = 0
          val _tmp_usUid = _result.getLong("usUid")
          if(_result.wasNull()) _tmp_UserSession_nullCount++
          val _tmp_usPcsn = _result.getLong("usPcsn")
          if(_result.wasNull()) _tmp_UserSession_nullCount++
          val _tmp_usLcsn = _result.getLong("usLcsn")
          if(_result.wasNull()) _tmp_UserSession_nullCount++
          val _tmp_usLcb = _result.getLong("usLcb")
          if(_result.wasNull()) _tmp_UserSession_nullCount++
          val _tmp_usLct = _result.getLong("usLct")
          if(_result.wasNull()) _tmp_UserSession_nullCount++
          val _tmp_usPersonUid = _result.getLong("usPersonUid")
          if(_result.wasNull()) _tmp_UserSession_nullCount++
          val _tmp_usClientNodeId = _result.getLong("usClientNodeId")
          if(_result.wasNull()) _tmp_UserSession_nullCount++
          val _tmp_usStartTime = _result.getLong("usStartTime")
          if(_result.wasNull()) _tmp_UserSession_nullCount++
          val _tmp_usEndTime = _result.getLong("usEndTime")
          if(_result.wasNull()) _tmp_UserSession_nullCount++
          val _tmp_usStatus = _result.getInt("usStatus")
          if(_result.wasNull()) _tmp_UserSession_nullCount++
          val _tmp_usReason = _result.getInt("usReason")
          if(_result.wasNull()) _tmp_UserSession_nullCount++
          val _tmp_usAuth = _result.getString("usAuth")
          if(_result.wasNull()) _tmp_UserSession_nullCount++
          val _tmp_usSessionType = _result.getInt("usSessionType")
          if(_result.wasNull()) _tmp_UserSession_nullCount++
          val _tmp_UserSession_isAllNull = _tmp_UserSession_nullCount == 13
          UserSessionAndPerson().apply {
            if(!_tmp_Person_isAllNull) {
              this.person = Person().apply {
                this.personUid = _tmp_personUid
                this.username = _tmp_username
                this.firstNames = _tmp_firstNames
                this.lastName = _tmp_lastName
                this.emailAddr = _tmp_emailAddr
                this.phoneNum = _tmp_phoneNum
                this.gender = _tmp_gender
                this.active = _tmp_active
                this.isPersonalAccount = _tmp_isPersonalAccount
                this.dateOfBirth = _tmp_dateOfBirth
                this.personAddress = _tmp_personAddress
                this.personOrgId = _tmp_personOrgId
                this.personGroupUid = _tmp_personGroupUid
                this.personLct = _tmp_personLct
                this.personCountry = _tmp_personCountry
                this.personType = _tmp_personType
                this.personMasterChangeSeqNum = _tmp_personMasterChangeSeqNum
                this.personLocalChangeSeqNum = _tmp_personLocalChangeSeqNum
                this.personLastChangedBy = _tmp_personLastChangedBy
                this.admin = _tmp_admin
                this.personNotes = _tmp_personNotes
                this.fatherName = _tmp_fatherName
                this.fatherNumber = _tmp_fatherNumber
                this.motherName = _tmp_motherName
                this.motherNum = _tmp_motherNum
              }
            }
            if(!_tmp_PersonPicture_isAllNull) {
              this.personPicture = PersonPicture().apply {
                this.personPictureUid = _tmp_personPictureUid
                this.personPictureLct = _tmp_personPictureLct
                this.personPictureUri = _tmp_personPictureUri
                this.personPictureThumbnailUri = _tmp_personPictureThumbnailUri
                this.fileSize = _tmp_fileSize
                this.personPictureActive = _tmp_personPictureActive
              }
            }
            if(!_tmp_UserSession_isAllNull) {
              this.userSession = UserSession().apply {
                this.usUid = _tmp_usUid
                this.usPcsn = _tmp_usPcsn
                this.usLcsn = _tmp_usLcsn
                this.usLcb = _tmp_usLcb
                this.usLct = _tmp_usLct
                this.usPersonUid = _tmp_usPersonUid
                this.usClientNodeId = _tmp_usClientNodeId
                this.usStartTime = _tmp_usStartTime
                this.usEndTime = _tmp_usEndTime
                this.usStatus = _tmp_usStatus
                this.usReason = _tmp_usReason
                this.usAuth = _tmp_usAuth
                this.usSessionType = _tmp_usSessionType
              }
            }
          }
        }
      }
    }
  }

  override suspend fun findAllLocalSessionsAsync(): List<UserSessionAndPerson> =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |            SELECT UserSession.*, Person.*, PersonPicture.*
    |              FROM UserSession
    |                   JOIN Person 
    |                        ON Person.personUid = UserSession.usPersonUid
    |                   LEFT JOIN PersonPicture
    |                        ON PersonPicture.personPictureUid = UserSession.usPersonUid
    |             WHERE UserSession.usClientNodeId = (
    |                   SELECT COALESCE(
    |                          (SELECT nodeClientId 
    |                            FROM SyncNode
    |                           LIMIT 1), 0))
    |               AND UserSession.usStatus = 1        
    |               AND (UserSession.usSessionType & 8) != 8
    |            
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        var _tmp_Person_nullCount = 0
        val _tmp_personUid = _result.getLong("personUid")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_username = _result.getString("username")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_firstNames = _result.getString("firstNames")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_lastName = _result.getString("lastName")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_emailAddr = _result.getString("emailAddr")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_phoneNum = _result.getString("phoneNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_gender = _result.getInt("gender")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_active = _result.getBoolean("active")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_isPersonalAccount = _result.getBoolean("isPersonalAccount")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_dateOfBirth = _result.getLong("dateOfBirth")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personAddress = _result.getString("personAddress")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personOrgId = _result.getString("personOrgId")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personGroupUid = _result.getLong("personGroupUid")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personLct = _result.getLong("personLct")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personCountry = _result.getString("personCountry")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personType = _result.getInt("personType")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personMasterChangeSeqNum = _result.getLong("personMasterChangeSeqNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personLocalChangeSeqNum = _result.getLong("personLocalChangeSeqNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personLastChangedBy = _result.getInt("personLastChangedBy")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_admin = _result.getBoolean("admin")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personNotes = _result.getString("personNotes")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_fatherName = _result.getString("fatherName")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_fatherNumber = _result.getString("fatherNumber")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_motherName = _result.getString("motherName")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_motherNum = _result.getString("motherNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_Person_isAllNull = _tmp_Person_nullCount == 25
        var _tmp_PersonPicture_nullCount = 0
        val _tmp_personPictureUid = _result.getLong("personPictureUid")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_personPictureLct = _result.getLong("personPictureLct")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_personPictureUri = _result.getString("personPictureUri")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_personPictureThumbnailUri = _result.getString("personPictureThumbnailUri")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_fileSize = _result.getInt("fileSize")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_personPictureActive = _result.getBoolean("personPictureActive")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_PersonPicture_isAllNull = _tmp_PersonPicture_nullCount == 6
        var _tmp_UserSession_nullCount = 0
        val _tmp_usUid = _result.getLong("usUid")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_usPcsn = _result.getLong("usPcsn")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_usLcsn = _result.getLong("usLcsn")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_usLcb = _result.getLong("usLcb")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_usLct = _result.getLong("usLct")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_usPersonUid = _result.getLong("usPersonUid")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_usClientNodeId = _result.getLong("usClientNodeId")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_usStartTime = _result.getLong("usStartTime")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_usEndTime = _result.getLong("usEndTime")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_usStatus = _result.getInt("usStatus")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_usReason = _result.getInt("usReason")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_usAuth = _result.getString("usAuth")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_usSessionType = _result.getInt("usSessionType")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_UserSession_isAllNull = _tmp_UserSession_nullCount == 13
        UserSessionAndPerson().apply {
          if(!_tmp_Person_isAllNull) {
            this.person = Person().apply {
              this.personUid = _tmp_personUid
              this.username = _tmp_username
              this.firstNames = _tmp_firstNames
              this.lastName = _tmp_lastName
              this.emailAddr = _tmp_emailAddr
              this.phoneNum = _tmp_phoneNum
              this.gender = _tmp_gender
              this.active = _tmp_active
              this.isPersonalAccount = _tmp_isPersonalAccount
              this.dateOfBirth = _tmp_dateOfBirth
              this.personAddress = _tmp_personAddress
              this.personOrgId = _tmp_personOrgId
              this.personGroupUid = _tmp_personGroupUid
              this.personLct = _tmp_personLct
              this.personCountry = _tmp_personCountry
              this.personType = _tmp_personType
              this.personMasterChangeSeqNum = _tmp_personMasterChangeSeqNum
              this.personLocalChangeSeqNum = _tmp_personLocalChangeSeqNum
              this.personLastChangedBy = _tmp_personLastChangedBy
              this.admin = _tmp_admin
              this.personNotes = _tmp_personNotes
              this.fatherName = _tmp_fatherName
              this.fatherNumber = _tmp_fatherNumber
              this.motherName = _tmp_motherName
              this.motherNum = _tmp_motherNum
            }
          }
          if(!_tmp_PersonPicture_isAllNull) {
            this.personPicture = PersonPicture().apply {
              this.personPictureUid = _tmp_personPictureUid
              this.personPictureLct = _tmp_personPictureLct
              this.personPictureUri = _tmp_personPictureUri
              this.personPictureThumbnailUri = _tmp_personPictureThumbnailUri
              this.fileSize = _tmp_fileSize
              this.personPictureActive = _tmp_personPictureActive
            }
          }
          if(!_tmp_UserSession_isAllNull) {
            this.userSession = UserSession().apply {
              this.usUid = _tmp_usUid
              this.usPcsn = _tmp_usPcsn
              this.usLcsn = _tmp_usLcsn
              this.usLcb = _tmp_usLcb
              this.usLct = _tmp_usLct
              this.usPersonUid = _tmp_usPersonUid
              this.usClientNodeId = _tmp_usClientNodeId
              this.usStartTime = _tmp_usStartTime
              this.usEndTime = _tmp_usEndTime
              this.usStatus = _tmp_usStatus
              this.usReason = _tmp_usReason
              this.usAuth = _tmp_usAuth
              this.usSessionType = _tmp_usSessionType
            }
          }
        }
      }
    }
  }

  override suspend fun findLocalSessionByUsername(username: String?): UserSessionAndPerson? =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |            SELECT UserSession.*, Person.*
    |              FROM UserSession
    |                   JOIN Person ON UserSession.usPersonUid = Person.personUid
    |             WHERE Person.username = ?
    |               AND UserSession.usClientNodeId = (
    |                   SELECT COALESCE(
    |                          (SELECT nodeClientId 
    |                            FROM SyncNode
    |                           LIMIT 1), 0))
    |               AND UserSession.usStatus = 1        
    |            
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setString(1,username)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(null) {
        var _tmp_Person_nullCount = 0
        val _tmp_personUid = _result.getLong("personUid")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_username = _result.getString("username")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_firstNames = _result.getString("firstNames")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_lastName = _result.getString("lastName")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_emailAddr = _result.getString("emailAddr")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_phoneNum = _result.getString("phoneNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_gender = _result.getInt("gender")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_active = _result.getBoolean("active")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_isPersonalAccount = _result.getBoolean("isPersonalAccount")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_dateOfBirth = _result.getLong("dateOfBirth")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personAddress = _result.getString("personAddress")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personOrgId = _result.getString("personOrgId")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personGroupUid = _result.getLong("personGroupUid")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personLct = _result.getLong("personLct")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personCountry = _result.getString("personCountry")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personType = _result.getInt("personType")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personMasterChangeSeqNum = _result.getLong("personMasterChangeSeqNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personLocalChangeSeqNum = _result.getLong("personLocalChangeSeqNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personLastChangedBy = _result.getInt("personLastChangedBy")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_admin = _result.getBoolean("admin")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personNotes = _result.getString("personNotes")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_fatherName = _result.getString("fatherName")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_fatherNumber = _result.getString("fatherNumber")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_motherName = _result.getString("motherName")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_motherNum = _result.getString("motherNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_Person_isAllNull = _tmp_Person_nullCount == 25
        var _tmp_PersonPicture_nullCount = 0
        val _tmp_personPictureUid = _result.getLong("personPictureUid")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_personPictureLct = _result.getLong("personPictureLct")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_personPictureUri = _result.getString("personPictureUri")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_personPictureThumbnailUri = _result.getString("personPictureThumbnailUri")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_fileSize = _result.getInt("fileSize")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_personPictureActive = _result.getBoolean("personPictureActive")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_PersonPicture_isAllNull = _tmp_PersonPicture_nullCount == 6
        var _tmp_UserSession_nullCount = 0
        val _tmp_usUid = _result.getLong("usUid")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_usPcsn = _result.getLong("usPcsn")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_usLcsn = _result.getLong("usLcsn")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_usLcb = _result.getLong("usLcb")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_usLct = _result.getLong("usLct")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_usPersonUid = _result.getLong("usPersonUid")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_usClientNodeId = _result.getLong("usClientNodeId")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_usStartTime = _result.getLong("usStartTime")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_usEndTime = _result.getLong("usEndTime")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_usStatus = _result.getInt("usStatus")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_usReason = _result.getInt("usReason")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_usAuth = _result.getString("usAuth")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_usSessionType = _result.getInt("usSessionType")
        if(_result.wasNull()) _tmp_UserSession_nullCount++
        val _tmp_UserSession_isAllNull = _tmp_UserSession_nullCount == 13
        UserSessionAndPerson().apply {
          if(!_tmp_Person_isAllNull) {
            this.person = Person().apply {
              this.personUid = _tmp_personUid
              this.username = _tmp_username
              this.firstNames = _tmp_firstNames
              this.lastName = _tmp_lastName
              this.emailAddr = _tmp_emailAddr
              this.phoneNum = _tmp_phoneNum
              this.gender = _tmp_gender
              this.active = _tmp_active
              this.isPersonalAccount = _tmp_isPersonalAccount
              this.dateOfBirth = _tmp_dateOfBirth
              this.personAddress = _tmp_personAddress
              this.personOrgId = _tmp_personOrgId
              this.personGroupUid = _tmp_personGroupUid
              this.personLct = _tmp_personLct
              this.personCountry = _tmp_personCountry
              this.personType = _tmp_personType
              this.personMasterChangeSeqNum = _tmp_personMasterChangeSeqNum
              this.personLocalChangeSeqNum = _tmp_personLocalChangeSeqNum
              this.personLastChangedBy = _tmp_personLastChangedBy
              this.admin = _tmp_admin
              this.personNotes = _tmp_personNotes
              this.fatherName = _tmp_fatherName
              this.fatherNumber = _tmp_fatherNumber
              this.motherName = _tmp_motherName
              this.motherNum = _tmp_motherNum
            }
          }
          if(!_tmp_PersonPicture_isAllNull) {
            this.personPicture = PersonPicture().apply {
              this.personPictureUid = _tmp_personPictureUid
              this.personPictureLct = _tmp_personPictureLct
              this.personPictureUri = _tmp_personPictureUri
              this.personPictureThumbnailUri = _tmp_personPictureThumbnailUri
              this.fileSize = _tmp_fileSize
              this.personPictureActive = _tmp_personPictureActive
            }
          }
          if(!_tmp_UserSession_isAllNull) {
            this.userSession = UserSession().apply {
              this.usUid = _tmp_usUid
              this.usPcsn = _tmp_usPcsn
              this.usLcsn = _tmp_usLcsn
              this.usLcb = _tmp_usLcb
              this.usLct = _tmp_usLct
              this.usPersonUid = _tmp_usPersonUid
              this.usClientNodeId = _tmp_usClientNodeId
              this.usStartTime = _tmp_usStartTime
              this.usEndTime = _tmp_usEndTime
              this.usStatus = _tmp_usStatus
              this.usReason = _tmp_usReason
              this.usAuth = _tmp_usAuth
              this.usSessionType = _tmp_usSessionType
            }
          }
        }
      }
    }
  }

  override suspend fun countAllLocalSessionsAsync(maxDateOfBirth: Long): Int =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT COUNT(*)
    |          FROM UserSession
    |               JOIN Person 
    |                    ON UserSession.usPersonUid = Person.personUid
    |         WHERE UserSession.usClientNodeId = (
    |                   SELECT COALESCE(
    |                          (SELECT nodeClientId 
    |                            FROM SyncNode
    |                           LIMIT 1), 0))
    |           AND UserSession.usStatus = 1                
    |           AND (? = 0 OR Person.dateOfBirth < ?)
    |           AND (UserSession.usSessionType & 8) != 8            
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,maxDateOfBirth)
    _stmt.setLong(2,maxDateOfBirth)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(0) {
        _result.getInt(1)
      }
    }
  }

  override suspend fun endSession(
    sessionUid: Long,
    newStatus: Int,
    reason: Int,
    endTime: Long,
  ) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        UPDATE UserSession
      |           SET usAuth = null,
      |               usStatus = ?,
      |               usReason = ?,
      |               usEndTime = ?,
      |               usLct = ?
      |         WHERE UserSession.usUid = ?
      |    
      """.trimMargin(),
      readOnly = false,)
    ) { _stmt -> 
      _stmt.setInt(1,newStatus)
      _stmt.setInt(2,reason)
      _stmt.setLong(3,endTime)
      _stmt.setLong(4,endTime)
      _stmt.setLong(5,sessionUid)
      _stmt.executeUpdateAsyncKmp()
    }
  }

  override fun findByUidLive(sessionUid: Long): Flow<UserSession?> =
      _db.doorFlow(arrayOf("UserSession")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT UserSession.*
      |          FROM UserSession
      |         WHERE UserSession.usUid = ?
      |         LIMIT 1
      |    
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,sessionUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(null) {
          val _tmp_usUid = _result.getLong("usUid")
          val _tmp_usPcsn = _result.getLong("usPcsn")
          val _tmp_usLcsn = _result.getLong("usLcsn")
          val _tmp_usLcb = _result.getLong("usLcb")
          val _tmp_usLct = _result.getLong("usLct")
          val _tmp_usPersonUid = _result.getLong("usPersonUid")
          val _tmp_usClientNodeId = _result.getLong("usClientNodeId")
          val _tmp_usStartTime = _result.getLong("usStartTime")
          val _tmp_usEndTime = _result.getLong("usEndTime")
          val _tmp_usStatus = _result.getInt("usStatus")
          val _tmp_usReason = _result.getInt("usReason")
          val _tmp_usAuth = _result.getString("usAuth")
          val _tmp_usSessionType = _result.getInt("usSessionType")
          UserSession().apply {
            this.usUid = _tmp_usUid
            this.usPcsn = _tmp_usPcsn
            this.usLcsn = _tmp_usLcsn
            this.usLcb = _tmp_usLcb
            this.usLct = _tmp_usLct
            this.usPersonUid = _tmp_usPersonUid
            this.usClientNodeId = _tmp_usClientNodeId
            this.usStartTime = _tmp_usStartTime
            this.usEndTime = _tmp_usEndTime
            this.usStatus = _tmp_usStatus
            this.usReason = _tmp_usReason
            this.usAuth = _tmp_usAuth
            this.usSessionType = _tmp_usSessionType
          }
        }
      }
    }
  }

  override suspend fun endOtherSessions(
    personUid: Long,
    exemptNodeId: Long,
    newStatus: Int,
    reason: Int,
    changeTime: Long,
  ) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        UPDATE UserSession
      |           SET usAuth = null,
      |               usStatus = ?,
      |               usReason = ?,
      |               usLct = ?
      |         WHERE usPersonUid = ?
      |           AND usClientNodeId != ?
      |           AND usStatus != ?                     
      |    
      """.trimMargin(),
      readOnly = false,)
    ) { _stmt -> 
      _stmt.setInt(1,newStatus)
      _stmt.setInt(2,reason)
      _stmt.setLong(3,changeTime)
      _stmt.setLong(4,personUid)
      _stmt.setLong(5,exemptNodeId)
      _stmt.setInt(6,newStatus)
      _stmt.executeUpdateAsyncKmp()
    }
  }

  override suspend fun findActiveNodeIdsByPersonUids(personUids: List<Long>): List<Long> =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT DISTINCT UserSession.usClientNodeId
    |          FROM UserSession
    |         WHERE UserSession.usPersonUid IN (?)
    |           AND UserSession.usStatus = 1
    |    
    """.trimMargin(),
    hasListParams = true,
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setArray(1, _stmt.getConnection().createArrayOrProxyArrayOf("BIGINT",
        personUids.toTypedArray()))
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        _result.getLong(1)
      }
    }
  }

  override suspend fun findActiveNodesIdsByGroupUids(groupUids: List<Long>): List<Long> =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT DISTINCT UserSession.usClientNodeId
    |          FROM UserSession
    |               JOIN PersonGroupMember 
    |                    ON PersonGroupMember.groupMemberPersonUid = UserSession.usPersonUid
    |         WHERE PersonGroupMember.groupMemberGroupUid IN (?)            
    |    
    """.trimMargin(),
    hasListParams = true,
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setArray(1, _stmt.getConnection().createArrayOrProxyArrayOf("BIGINT",
        groupUids.toTypedArray()))
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        _result.getLong(1)
      }
    }
  }

  override suspend fun countActiveSessionsForUserAndNode(personUid: Long, nodeId: Long): Int =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT COUNT(*)
    |          FROM UserSession
    |         WHERE UserSession.usPersonUid = ?
    |           AND UserSession.usStatus = 1
    |           AND UserSession.usClientNodeId = ?
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,personUid)
    _stmt.setLong(2,nodeId)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(0) {
        _result.getInt(1)
      }
    }
  }
}
