package com.ustadmobile.core.db.dao

import androidx.paging.PagingSource
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.paging.DoorLimitOffsetPagingSource
import com.ustadmobile.door.room.RoomDatabase
import com.ustadmobile.lib.db.composites.EnrolmentRequestAndCoursePic
import com.ustadmobile.lib.db.composites.EnrolmentRequestAndPersonDetails
import com.ustadmobile.lib.db.entities.CoursePicture
import com.ustadmobile.lib.db.entities.EnrolmentRequest
import com.ustadmobile.lib.db.entities.Person
import com.ustadmobile.lib.db.entities.PersonPicture
import kotlin.Boolean
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.collections.List
import kotlinx.coroutines.flow.Flow

public class EnrolmentRequestDao_JdbcImpl(
  public val _db: RoomDatabase,
) : EnrolmentRequestDao() {
  public val _insertAdapterEnrolmentRequest_abort: EntityInsertionAdapter<EnrolmentRequest> = object
      : EntityInsertionAdapter<EnrolmentRequest>(_db) {
    override fun makeSql(returnsId: Boolean): String =
        "INSERT INTO EnrolmentRequest (erUid, erClazzUid, erClazzName, erPersonUid, erPersonFullname, erPersonPictureUri, erPersonUsername, erRole, erRequestTime, erStatus, erStatusSetByPersonUid, erDeleted, erStatusSetAuth, erLastModified) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement, entity: EnrolmentRequest) {
      if(entity.erUid == 0L) {
        stmt.setObject(1, null)
      } else {
        stmt.setLong(1, entity.erUid)
      }
      stmt.setLong(2, entity.erClazzUid)
      stmt.setString(3, entity.erClazzName)
      stmt.setLong(4, entity.erPersonUid)
      stmt.setString(5, entity.erPersonFullname)
      stmt.setString(6, entity.erPersonPictureUri)
      stmt.setString(7, entity.erPersonUsername)
      stmt.setInt(8, entity.erRole)
      stmt.setLong(9, entity.erRequestTime)
      stmt.setInt(10, entity.erStatus)
      stmt.setLong(11, entity.erStatusSetByPersonUid)
      stmt.setBoolean(12, entity.erDeleted)
      stmt.setString(13, entity.erStatusSetAuth)
      stmt.setLong(14, entity.erLastModified)
    }
  }

  override suspend fun insert(enrolmentRequest: EnrolmentRequest) {
    _insertAdapterEnrolmentRequest_abort.insertAsync(enrolmentRequest)
  }

  override suspend fun findByClazzAndPerson(
    personUid: Long,
    clazzUid: Long,
    statusFilter: Int,
  ): List<EnrolmentRequest> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT EnrolmentRequest.*
    |          FROM EnrolmentRequest
    |         WHERE EnrolmentRequest.erPersonUid = CAST(? AS BIGINT)
    |           AND EnrolmentRequest.erClazzUid = CAST(? AS BIGINT)
    |           AND (? = 0 OR EnrolmentRequest.erStatus = ?)
    |    
    """.trimMargin(),
    postgreSql = """
    |
    |        SELECT EnrolmentRequest.*
    |          FROM EnrolmentRequest
    |         WHERE EnrolmentRequest.erPersonUid = ?
    |           AND EnrolmentRequest.erClazzUid = ?
    |           AND (? = 0 OR EnrolmentRequest.erStatus = ?)
    |    
    |""".trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,personUid)
    _stmt.setLong(2,clazzUid)
    _stmt.setInt(3,statusFilter)
    _stmt.setInt(4,statusFilter)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_erUid = _result.getLong("erUid")
        val _tmp_erClazzUid = _result.getLong("erClazzUid")
        val _tmp_erClazzName = _result.getString("erClazzName")
        val _tmp_erPersonUid = _result.getLong("erPersonUid")
        val _tmp_erPersonFullname = _result.getString("erPersonFullname")
        val _tmp_erPersonPictureUri = _result.getString("erPersonPictureUri")
        val _tmp_erPersonUsername = _result.getString("erPersonUsername")
        val _tmp_erRole = _result.getInt("erRole")
        val _tmp_erRequestTime = _result.getLong("erRequestTime")
        val _tmp_erStatus = _result.getInt("erStatus")
        val _tmp_erStatusSetByPersonUid = _result.getLong("erStatusSetByPersonUid")
        val _tmp_erDeleted = _result.getBoolean("erDeleted")
        val _tmp_erStatusSetAuth = _result.getString("erStatusSetAuth")
        val _tmp_erLastModified = _result.getLong("erLastModified")
        EnrolmentRequest().apply {
          this.erUid = _tmp_erUid
          this.erClazzUid = _tmp_erClazzUid
          this.erClazzName = _tmp_erClazzName
          this.erPersonUid = _tmp_erPersonUid
          this.erPersonFullname = _tmp_erPersonFullname
          this.erPersonPictureUri = _tmp_erPersonPictureUri
          this.erPersonUsername = _tmp_erPersonUsername
          this.erRole = _tmp_erRole
          this.erRequestTime = _tmp_erRequestTime
          this.erStatus = _tmp_erStatus
          this.erStatusSetByPersonUid = _tmp_erStatusSetByPersonUid
          this.erDeleted = _tmp_erDeleted
          this.erStatusSetAuth = _tmp_erStatusSetAuth
          this.erLastModified = _tmp_erLastModified
        }
      }
    }
  }

  override suspend fun hasPendingRequests(personUid: Long, clazzUid: Long): Boolean =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT EXISTS(
    |               SELECT EnrolmentRequest.erUid
    |                 FROM EnrolmentRequest
    |                WHERE EnrolmentRequest.erPersonUid = CAST(? AS BIGINT)
    |                  AND EnrolmentRequest.erClazzUid = CAST(? AS BIGINT)
    |                  AND EnrolmentRequest.erStatus = 1)
    |    
    """.trimMargin(),
    postgreSql = """
    |
    |        SELECT EXISTS(
    |               SELECT EnrolmentRequest.erUid
    |                 FROM EnrolmentRequest
    |                WHERE EnrolmentRequest.erPersonUid = ?
    |                  AND EnrolmentRequest.erClazzUid = ?
    |                  AND EnrolmentRequest.erStatus = 1)
    |    
    |""".trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,personUid)
    _stmt.setLong(2,clazzUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(false) {
        _result.getBoolean(1)
      }
    }
  }

  override fun findRequestsForUserAsFlow(accountPersonUid: Long, statusFilter: Int):
      Flow<List<EnrolmentRequestAndCoursePic>> = _db.doorFlow(arrayOf("EnrolmentRequest",
      "CoursePicture")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT EnrolmentRequest.*, CoursePicture.*
      |          FROM EnrolmentRequest
      |               LEFT JOIN CoursePicture
      |                         ON CoursePicture.coursePictureUid = EnrolmentRequest.erClazzUid
      |         WHERE EnrolmentRequest.erPersonUid = CAST(? AS BIGINT) 
      |           AND (? = 0 OR EnrolmentRequest.erStatus = ?)
      |    
      """.trimMargin(),
      postgreSql = """
      |
      |        SELECT EnrolmentRequest.*, CoursePicture.*
      |          FROM EnrolmentRequest
      |               LEFT JOIN CoursePicture
      |                         ON CoursePicture.coursePictureUid = EnrolmentRequest.erClazzUid
      |         WHERE EnrolmentRequest.erPersonUid = ? 
      |           AND (? = 0 OR EnrolmentRequest.erStatus = ?)
      |    
      |""".trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,accountPersonUid)
      _stmt.setInt(2,statusFilter)
      _stmt.setInt(3,statusFilter)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          var _tmp_EnrolmentRequest_nullCount = 0
          val _tmp_erUid = _result.getLong("erUid")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erClazzUid = _result.getLong("erClazzUid")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erClazzName = _result.getString("erClazzName")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erPersonUid = _result.getLong("erPersonUid")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erPersonFullname = _result.getString("erPersonFullname")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erPersonPictureUri = _result.getString("erPersonPictureUri")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erPersonUsername = _result.getString("erPersonUsername")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erRole = _result.getInt("erRole")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erRequestTime = _result.getLong("erRequestTime")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erStatus = _result.getInt("erStatus")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erStatusSetByPersonUid = _result.getLong("erStatusSetByPersonUid")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erDeleted = _result.getBoolean("erDeleted")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erStatusSetAuth = _result.getString("erStatusSetAuth")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erLastModified = _result.getLong("erLastModified")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_EnrolmentRequest_isAllNull = _tmp_EnrolmentRequest_nullCount == 14
          var _tmp_CoursePicture_nullCount = 0
          val _tmp_coursePictureUid = _result.getLong("coursePictureUid")
          if(_result.wasNull()) _tmp_CoursePicture_nullCount++
          val _tmp_coursePictureLct = _result.getLong("coursePictureLct")
          if(_result.wasNull()) _tmp_CoursePicture_nullCount++
          val _tmp_coursePictureUri = _result.getString("coursePictureUri")
          if(_result.wasNull()) _tmp_CoursePicture_nullCount++
          val _tmp_coursePictureThumbnailUri = _result.getString("coursePictureThumbnailUri")
          if(_result.wasNull()) _tmp_CoursePicture_nullCount++
          val _tmp_coursePictureActive = _result.getBoolean("coursePictureActive")
          if(_result.wasNull()) _tmp_CoursePicture_nullCount++
          val _tmp_CoursePicture_isAllNull = _tmp_CoursePicture_nullCount == 5
          EnrolmentRequestAndCoursePic().apply {
            if(!_tmp_EnrolmentRequest_isAllNull) {
              this.enrolmentRequest = EnrolmentRequest().apply {
                this.erUid = _tmp_erUid
                this.erClazzUid = _tmp_erClazzUid
                this.erClazzName = _tmp_erClazzName
                this.erPersonUid = _tmp_erPersonUid
                this.erPersonFullname = _tmp_erPersonFullname
                this.erPersonPictureUri = _tmp_erPersonPictureUri
                this.erPersonUsername = _tmp_erPersonUsername
                this.erRole = _tmp_erRole
                this.erRequestTime = _tmp_erRequestTime
                this.erStatus = _tmp_erStatus
                this.erStatusSetByPersonUid = _tmp_erStatusSetByPersonUid
                this.erDeleted = _tmp_erDeleted
                this.erStatusSetAuth = _tmp_erStatusSetAuth
                this.erLastModified = _tmp_erLastModified
              }
            }
            if(!_tmp_CoursePicture_isAllNull) {
              this.coursePicture = CoursePicture().apply {
                this.coursePictureUid = _tmp_coursePictureUid
                this.coursePictureLct = _tmp_coursePictureLct
                this.coursePictureUri = _tmp_coursePictureUri
                this.coursePictureThumbnailUri = _tmp_coursePictureThumbnailUri
                this.coursePictureActive = _tmp_coursePictureActive
              }
            }
          }
        }
      }
    }
  }

  override suspend fun updateStatus(
    uid: Long,
    status: Int,
    updateTime: Long,
  ) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        UPDATE EnrolmentRequest
      |           SET erStatus = ?,
      |               erLastModified = CAST(? AS BIGINT)
      |         WHERE erUid = CAST(? AS BIGINT)      
      |    
      """.trimMargin(),
      postgreSql = """
      |
      |        UPDATE EnrolmentRequest
      |           SET erStatus = ?,
      |               erLastModified = ?
      |         WHERE erUid = ?      
      |    
      |""".trimMargin(),
      readOnly = false,)
    ) { _stmt -> 
      _stmt.setInt(1,status)
      _stmt.setLong(2,updateTime)
      _stmt.setLong(3,uid)
      _stmt.executeUpdateAsyncKmp()
    }
  }

  override fun findPendingEnrolmentsForCourse(
    clazzUid: Long,
    includeDeleted: Boolean,
    statusFilter: Int,
    searchText: String,
    sortOrder: Int,
  ): PagingSource<Int, EnrolmentRequestAndPersonDetails> = object :
      DoorLimitOffsetPagingSource<EnrolmentRequestAndPersonDetails>(db = _db
  , tableNames = arrayOf("EnrolmentRequest", "Person", "PersonPicture")
  ) {
    override suspend fun loadRows(_limit: Int, _offset: Int): List<EnrolmentRequestAndPersonDetails>
        = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT * FROM (
      |        SELECT EnrolmentRequest.*, PersonPicture.*, Person.*
      |          FROM EnrolmentRequest
      |               JOIN Person
      |                    ON Person.personUid = EnrolmentRequest.erPersonUid
      |               LEFT JOIN PersonPicture
      |                         ON PersonPicture.personPictureUid = EnrolmentRequest.erPersonUid
      |         WHERE EnrolmentRequest.erClazzUid = CAST(? AS BIGINT)
      |           AND (? = 0 OR EnrolmentRequest.erStatus = ?)
      |           AND (CAST(? AS INTEGER) = 1 OR NOT EnrolmentRequest.erDeleted)
      |           AND (? = '%' OR EnrolmentRequest.erPersonFullname LIKE ?)
      |      ORDER BY CASE(?)
      |                WHEN 1 THEN EnrolmentRequest.erPersonFullname
      |                WHEN 3 THEN EnrolmentRequest.erPersonFullname
      |                ELSE ''
      |                END ASC,
      |                CASE(?)
      |                WHEN 2 THEN EnrolmentRequest.erPersonFullname
      |                WHEN 4 THEN EnrolmentRequest.erPersonFullname
      |                ELSE ''
      |            END DESC,
      |            CASE(?)
      |                WHEN 7 THEN EnrolmentRequest.erRequestTime
      |                ELSE 0
      |            END ASC,
      |            CASE(?)
      |                WHEN 8 THEN EnrolmentRequest.erRequestTime
      |                ELSE 0
      |            END DESC     
      |    ) AS _PagingData LIMIT ? OFFSET ?
      """.trimMargin(),
      postgreSql = """
      |SELECT * FROM (
      |        SELECT EnrolmentRequest.*, PersonPicture.*, Person.*
      |          FROM EnrolmentRequest
      |               JOIN Person
      |                    ON Person.personUid = EnrolmentRequest.erPersonUid
      |               LEFT JOIN PersonPicture
      |                         ON PersonPicture.personPictureUid = EnrolmentRequest.erPersonUid
      |         WHERE EnrolmentRequest.erClazzUid = ?
      |           AND (? = 0 OR EnrolmentRequest.erStatus = ?)
      |           AND (CAST(? AS INTEGER) = 1 OR NOT EnrolmentRequest.erDeleted)
      |           AND (? = '%' OR EnrolmentRequest.erPersonFullname LIKE ?)
      |      ORDER BY CASE(?)
      |                WHEN 1 THEN EnrolmentRequest.erPersonFullname
      |                WHEN 3 THEN EnrolmentRequest.erPersonFullname
      |                ELSE ''
      |                END ASC,
      |                CASE(?)
      |                WHEN 2 THEN EnrolmentRequest.erPersonFullname
      |                WHEN 4 THEN EnrolmentRequest.erPersonFullname
      |                ELSE ''
      |            END DESC,
      |            CASE(?)
      |                WHEN 7 THEN EnrolmentRequest.erRequestTime
      |                ELSE 0
      |            END ASC,
      |            CASE(?)
      |                WHEN 8 THEN EnrolmentRequest.erRequestTime
      |                ELSE 0
      |            END DESC     
      |    ) AS _PagingData LIMIT ? OFFSET ?
      |""".trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,clazzUid)
      _stmt.setInt(2,statusFilter)
      _stmt.setInt(3,statusFilter)
      _stmt.setBoolean(4,includeDeleted)
      _stmt.setString(5,searchText)
      _stmt.setString(6,searchText)
      _stmt.setInt(7,sortOrder)
      _stmt.setInt(8,sortOrder)
      _stmt.setInt(9,sortOrder)
      _stmt.setInt(10,sortOrder)
      _stmt.setInt(11,_limit)
      _stmt.setInt(12,_offset)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          var _tmp_EnrolmentRequest_nullCount = 0
          val _tmp_erUid = _result.getLong("erUid")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erClazzUid = _result.getLong("erClazzUid")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erClazzName = _result.getString("erClazzName")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erPersonUid = _result.getLong("erPersonUid")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erPersonFullname = _result.getString("erPersonFullname")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erPersonPictureUri = _result.getString("erPersonPictureUri")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erPersonUsername = _result.getString("erPersonUsername")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erRole = _result.getInt("erRole")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erRequestTime = _result.getLong("erRequestTime")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erStatus = _result.getInt("erStatus")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erStatusSetByPersonUid = _result.getLong("erStatusSetByPersonUid")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erDeleted = _result.getBoolean("erDeleted")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erStatusSetAuth = _result.getString("erStatusSetAuth")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_erLastModified = _result.getLong("erLastModified")
          if(_result.wasNull()) _tmp_EnrolmentRequest_nullCount++
          val _tmp_EnrolmentRequest_isAllNull = _tmp_EnrolmentRequest_nullCount == 14
          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_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
          EnrolmentRequestAndPersonDetails().apply {
            if(!_tmp_EnrolmentRequest_isAllNull) {
              this.enrolmentRequest = EnrolmentRequest().apply {
                this.erUid = _tmp_erUid
                this.erClazzUid = _tmp_erClazzUid
                this.erClazzName = _tmp_erClazzName
                this.erPersonUid = _tmp_erPersonUid
                this.erPersonFullname = _tmp_erPersonFullname
                this.erPersonPictureUri = _tmp_erPersonPictureUri
                this.erPersonUsername = _tmp_erPersonUsername
                this.erRole = _tmp_erRole
                this.erRequestTime = _tmp_erRequestTime
                this.erStatus = _tmp_erStatus
                this.erStatusSetByPersonUid = _tmp_erStatusSetByPersonUid
                this.erDeleted = _tmp_erDeleted
                this.erStatusSetAuth = _tmp_erStatusSetAuth
                this.erLastModified = _tmp_erLastModified
              }
            }
            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_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
              }
            }
          }
        }
      }
    }

    override suspend fun countRows(): Int = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT COUNT(*) FROM (
      |        SELECT EnrolmentRequest.*, PersonPicture.*, Person.*
      |          FROM EnrolmentRequest
      |               JOIN Person
      |                    ON Person.personUid = EnrolmentRequest.erPersonUid
      |               LEFT JOIN PersonPicture
      |                         ON PersonPicture.personPictureUid = EnrolmentRequest.erPersonUid
      |         WHERE EnrolmentRequest.erClazzUid = CAST(? AS BIGINT)
      |           AND (? = 0 OR EnrolmentRequest.erStatus = ?)
      |           AND (CAST(? AS INTEGER) = 1 OR NOT EnrolmentRequest.erDeleted)
      |           AND (? = '%' OR EnrolmentRequest.erPersonFullname LIKE ?)
      |      ORDER BY CASE(?)
      |                WHEN 1 THEN EnrolmentRequest.erPersonFullname
      |                WHEN 3 THEN EnrolmentRequest.erPersonFullname
      |                ELSE ''
      |                END ASC,
      |                CASE(?)
      |                WHEN 2 THEN EnrolmentRequest.erPersonFullname
      |                WHEN 4 THEN EnrolmentRequest.erPersonFullname
      |                ELSE ''
      |            END DESC,
      |            CASE(?)
      |                WHEN 7 THEN EnrolmentRequest.erRequestTime
      |                ELSE 0
      |            END ASC,
      |            CASE(?)
      |                WHEN 8 THEN EnrolmentRequest.erRequestTime
      |                ELSE 0
      |            END DESC     
      |    ) AS _PagingCount
      """.trimMargin(),
      postgreSql = """
      |SELECT COUNT(*) FROM (
      |        SELECT EnrolmentRequest.*, PersonPicture.*, Person.*
      |          FROM EnrolmentRequest
      |               JOIN Person
      |                    ON Person.personUid = EnrolmentRequest.erPersonUid
      |               LEFT JOIN PersonPicture
      |                         ON PersonPicture.personPictureUid = EnrolmentRequest.erPersonUid
      |         WHERE EnrolmentRequest.erClazzUid = ?
      |           AND (? = 0 OR EnrolmentRequest.erStatus = ?)
      |           AND (CAST(? AS INTEGER) = 1 OR NOT EnrolmentRequest.erDeleted)
      |           AND (? = '%' OR EnrolmentRequest.erPersonFullname LIKE ?)
      |      ORDER BY CASE(?)
      |                WHEN 1 THEN EnrolmentRequest.erPersonFullname
      |                WHEN 3 THEN EnrolmentRequest.erPersonFullname
      |                ELSE ''
      |                END ASC,
      |                CASE(?)
      |                WHEN 2 THEN EnrolmentRequest.erPersonFullname
      |                WHEN 4 THEN EnrolmentRequest.erPersonFullname
      |                ELSE ''
      |            END DESC,
      |            CASE(?)
      |                WHEN 7 THEN EnrolmentRequest.erRequestTime
      |                ELSE 0
      |            END ASC,
      |            CASE(?)
      |                WHEN 8 THEN EnrolmentRequest.erRequestTime
      |                ELSE 0
      |            END DESC     
      |    ) AS _PagingCount
      |""".trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,clazzUid)
      _stmt.setInt(2,statusFilter)
      _stmt.setInt(3,statusFilter)
      _stmt.setBoolean(4,includeDeleted)
      _stmt.setString(5,searchText)
      _stmt.setString(6,searchText)
      _stmt.setInt(7,sortOrder)
      _stmt.setInt(8,sortOrder)
      _stmt.setInt(9,sortOrder)
      _stmt.setInt(10,sortOrder)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(0) {
          _result.getInt(1)
        }
      }
    }
  }
}
