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.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 =
        "INSERT INTO UserSession (usUid, usPcsn, usLcsn, usLcb, usLct, usPersonUid, usClientNodeId, usStartTime, usEndTime, usStatus, usReason, usAuth, usSessionType) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

    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 = CAST(? AS BIGINT) 
    |    
    """.trimMargin(),
    postgreSql = """
    |
    |        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 (CAST(? AS BIGINT) = 0 OR Person.dateOfBirth < CAST(? AS BIGINT))
    |           AND (UserSession.usSessionType & 8) != 8            
    |    
    """.trimMargin(),
    postgreSql = """
    |
    |        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 = CAST(? AS BIGINT),
      |               usLct = CAST(? AS BIGINT)
      |         WHERE UserSession.usUid = CAST(? AS BIGINT)
      |    
      """.trimMargin(),
      postgreSql = """
      |
      |        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 = CAST(? AS BIGINT)
      |         LIMIT 1
      |    
      """.trimMargin(),
      postgreSql = """
      |
      |        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 = CAST(? AS BIGINT)
      |         WHERE usPersonUid = CAST(? AS BIGINT)
      |           AND usClientNodeId != CAST(? AS BIGINT)
      |           AND usStatus != ?                     
      |    
      """.trimMargin(),
      postgreSql = """
      |
      |        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 = CAST(? AS BIGINT)
    |           AND UserSession.usStatus = 1
    |           AND UserSession.usClientNodeId = CAST(? AS BIGINT)
    |    
    """.trimMargin(),
    postgreSql = """
    |
    |        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)
      }
    }
  }
}
