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.prepareAndUseStatement
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.composites.CourseGroupMemberAndPerson
import com.ustadmobile.lib.db.composites.PersonAndPicture
import com.ustadmobile.lib.db.entities.ClazzEnrolment
import com.ustadmobile.lib.db.entities.CourseGroupMember
import com.ustadmobile.lib.db.entities.CourseGroupMemberAndName
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 CourseGroupMemberDao_JdbcImpl(
  public val _db: RoomDatabase,
) : CourseGroupMemberDao() {
  public val _insertAdapterCourseGroupMember_abort: EntityInsertionAdapter<CourseGroupMember> =
      object : EntityInsertionAdapter<CourseGroupMember>(_db) {
    override fun makeSql(returnsId: Boolean): String =
        "INSERT INTO CourseGroupMember (cgmUid, cgmSetUid, cgmGroupNumber, cgmPersonUid, cgmLct) VALUES(?, ?, ?, ?, ?)"

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement, entity: CourseGroupMember) {
      if(entity.cgmUid == 0L) {
        stmt.setObject(1, null)
      } else {
        stmt.setLong(1, entity.cgmUid)
      }
      stmt.setLong(2, entity.cgmSetUid)
      stmt.setInt(3, entity.cgmGroupNumber)
      stmt.setLong(4, entity.cgmPersonUid)
      stmt.setLong(5, entity.cgmLct)
    }
  }

  public val _insertAdapterCourseGroupMember_upsert: EntityInsertionAdapter<CourseGroupMember> =
      object : EntityInsertionAdapter<CourseGroupMember>(_db) {
    override fun makeSql(returnsId: Boolean): String =
        "INSERT OR REPLACE INTO CourseGroupMember (cgmUid, cgmSetUid, cgmGroupNumber, cgmPersonUid, cgmLct) VALUES(?, ?, ?, ?, ?)"

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement, entity: CourseGroupMember) {
      if(entity.cgmUid == 0L) {
        stmt.setObject(1, null)
      } else {
        stmt.setLong(1, entity.cgmUid)
      }
      stmt.setLong(2, entity.cgmSetUid)
      stmt.setInt(3, entity.cgmGroupNumber)
      stmt.setLong(4, entity.cgmPersonUid)
      stmt.setLong(5, entity.cgmLct)
    }
  }

  override suspend fun insertListAsync(entityList: List<CourseGroupMember>) {
    _insertAdapterCourseGroupMember_abort.insertListAsync(entityList)
  }

  override suspend fun upsertListAsync(list: List<CourseGroupMember>) {
    _insertAdapterCourseGroupMember_upsert.insertListAsync(list)
  }

  public override fun insert(entity: CourseGroupMember): Long {
    val _retVal = _insertAdapterCourseGroupMember_abort.insertAndReturnId(entity)
    return _retVal
  }

  public override suspend fun insertAsync(entity: CourseGroupMember): Long {
    val _retVal = _insertAdapterCourseGroupMember_abort.insertAndReturnIdAsync(entity)
    return _retVal
  }

  public override fun insertList(entityList: List<CourseGroupMember>) {
    _insertAdapterCourseGroupMember_abort.insertList(entityList)
  }

  override suspend fun updateListAsync(entityList: List<CourseGroupMember>) {
    val _sql =
        "UPDATE CourseGroupMember SET cgmSetUid = ?, cgmGroupNumber = ?, cgmPersonUid = ?, cgmLct = ? WHERE cgmUid = ?"
    _db.prepareAndUseStatementAsync(_sql) {
       _stmt ->
      _stmt.getConnection().setAutoCommit(false)
      for(_entity in entityList) {
        _stmt.setLong(1, _entity.cgmSetUid)
        _stmt.setInt(2, _entity.cgmGroupNumber)
        _stmt.setLong(3, _entity.cgmPersonUid)
        _stmt.setLong(4, _entity.cgmLct)
        _stmt.setLong(5, _entity.cgmUid)
        _stmt.executeUpdateAsyncKmp()
      }
      _stmt.getConnection().commit()
    }
  }

  public override fun update(entity: CourseGroupMember) {
    val _sql =
        "UPDATE CourseGroupMember SET cgmSetUid = ?, cgmGroupNumber = ?, cgmPersonUid = ?, cgmLct = ? WHERE cgmUid = ?"
    _db.prepareAndUseStatement(_sql) {
       _stmt ->
      _stmt.setLong(1, entity.cgmSetUid)
      _stmt.setInt(2, entity.cgmGroupNumber)
      _stmt.setLong(3, entity.cgmPersonUid)
      _stmt.setLong(4, entity.cgmLct)
      _stmt.setLong(5, entity.cgmUid)
      _stmt.executeUpdate()
    }
  }

  override suspend fun findByGroupSetUidAsync(groupSetUid: Long): List<CourseGroupMember> =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT CourseGroupMember.*
    |          FROM CourseGroupMember
    |         WHERE cgmSetUid = CAST(? AS BIGINT) 
    |    
    """.trimMargin(),
    postgreSql = """
    |
    |        SELECT CourseGroupMember.*
    |          FROM CourseGroupMember
    |         WHERE cgmSetUid = ? 
    |    
    |""".trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,groupSetUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_cgmUid = _result.getLong("cgmUid")
        val _tmp_cgmSetUid = _result.getLong("cgmSetUid")
        val _tmp_cgmGroupNumber = _result.getInt("cgmGroupNumber")
        val _tmp_cgmPersonUid = _result.getLong("cgmPersonUid")
        val _tmp_cgmLct = _result.getLong("cgmLct")
        CourseGroupMember().apply {
          this.cgmUid = _tmp_cgmUid
          this.cgmSetUid = _tmp_cgmSetUid
          this.cgmGroupNumber = _tmp_cgmGroupNumber
          this.cgmPersonUid = _tmp_cgmPersonUid
          this.cgmLct = _tmp_cgmLct
        }
      }
    }
  }

  override suspend fun findByPersonUid(groupSetUid: Long, studentUid: Long): CourseGroupMember? =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT * 
    |          FROM CourseGroupMember
    |         WHERE cgmPersonUid = CAST(? AS BIGINT) 
    |          AND cgmSetUid = CAST(? AS BIGINT)
    |         LIMIT 1
    |    
    """.trimMargin(),
    postgreSql = """
    |
    |        SELECT * 
    |          FROM CourseGroupMember
    |         WHERE cgmPersonUid = ? 
    |          AND cgmSetUid = ?
    |         LIMIT 1
    |    
    |""".trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,studentUid)
    _stmt.setLong(2,groupSetUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(null) {
        val _tmp_cgmUid = _result.getLong("cgmUid")
        val _tmp_cgmSetUid = _result.getLong("cgmSetUid")
        val _tmp_cgmGroupNumber = _result.getInt("cgmGroupNumber")
        val _tmp_cgmPersonUid = _result.getLong("cgmPersonUid")
        val _tmp_cgmLct = _result.getLong("cgmLct")
        CourseGroupMember().apply {
          this.cgmUid = _tmp_cgmUid
          this.cgmSetUid = _tmp_cgmSetUid
          this.cgmGroupNumber = _tmp_cgmGroupNumber
          this.cgmPersonUid = _tmp_cgmPersonUid
          this.cgmLct = _tmp_cgmLct
        }
      }
    }
  }

  override suspend fun findByCourseGroupSetAndClazz(
    cgsUid: Long,
    clazzUid: Long,
    time: Long,
    activeFilter: Int,
    accountPersonUid: Long,
  ): List<CourseGroupMemberAndName> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        --First get a list of all enrolments - this may contains duplicates for students who leave and re-enrol
    |        WITH AllEnrollmentsAndActiveStatus(enrolledPersonUid, isActive) AS 
    |             (SELECT ClazzEnrolment.clazzEnrolmentPersonUid AS enrolledPersonUid,
    |                     (CAST(? AS BIGINT) BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined AND ClazzEnrolment.clazzEnrolmentDateLeft) AS isActive
    |                FROM ClazzEnrolment
    |               WHERE ClazzEnrolment.clazzEnrolmentClazzUid = 
    |        CASE(CAST(? AS BIGINT))
    |                         WHEN 0 THEN 
    |                                (SELECT CourseGroupSet.cgsClazzUid
    |                                   FROM CourseGroupSet
    |                                  WHERE CourseGroupSet.cgsUid = CAST(? AS BIGINT))
    |                         ELSE CAST(? AS BIGINT)
    |                     END
    |    
    |                 AND ClazzEnrolment.clazzEnrolmentRole = 1000),
    |        --Consolidate and removes any duplicates
    |             EnrolledStudentPersonUids(enrolledPersonUid, isActive) AS
    |             (SELECT DISTINCT AllEnrollmentsAndActiveStatus.enrolledPersonUid,
    |                     (SELECT CAST(AllEnrollmentsInner.isActive AS INTEGER)
    |                        FROM AllEnrollmentsAndActiveStatus AllEnrollmentsInner
    |                       WHERE AllEnrollmentsInner.enrolledPersonUid = AllEnrollmentsAndActiveStatus.enrolledPersonUid
    |                    ORDER BY AllEnrollmentsInner.isActive DESC
    |                       LIMIT 1) AS isActive
    |                FROM AllEnrollmentsAndActiveStatus)
    |        
    |        -- Now create a list with each students name, the coursegroupmember object if any and active status        
    |        SELECT (Person.firstNames || ' ' || Person.lastName) AS name,
    |               Person.personUid,
    |               CourseGroupMember.*,
    |               PersonPicture.*,
    |               EnrolledStudentPersonUids.isActive AS enrolmentIsActive,
    |               PersonPicture.personPictureThumbnailUri AS pictureUri
    |          FROM EnrolledStudentPersonUids
    |               JOIN Person
    |                    ON Person.personUid = EnrolledStudentPersonUids.enrolledPersonUid 
    |               LEFT JOIN PersonPicture
    |                         ON PersonPicture.personPictureUid = Person.personUid  
    |               -- LEFT JOIN will use the most recent member in case of duplicate assignments eg if      
    |               LEFT JOIN CourseGroupMember
    |                         ON CourseGroupMember.cgmUid = 
    |                            (SELECT CourseGroupMember.cgmUid
    |                               FROM CourseGroupMember
    |                              WHERE CourseGroupMember.cgmPersonUid = EnrolledStudentPersonUids.enrolledPersonUid
    |                                AND CourseGroupMember.cgmSetUid = CAST(? AS BIGINT) 
    |                           ORDER BY CourseGroupMember.cgmLct DESC        
    |                              LIMIT 1)
    |         WHERE (? = 0 OR ? = EnrolledStudentPersonUids.isActive)  
    |               /* 
    |                * Begin permission check -  must have course view members permission, or active 
    |                * user must be in the same group 
    |                */ 
    |            AND (
    |                    ((
    |             /* If the accountPersonUid is the owner of the course, all permissions are granted */
    |             (COALESCE(
    |                          (SELECT _Clazz_Permission.clazzOwnerPersonUid 
    |                             FROM Clazz _Clazz_Permission
    |                            WHERE _Clazz_Permission.clazzUid = CAST(? AS BIGINT)), 0) = CAST(? AS BIGINT))
    |              /* 
    |              If there is a CoursePermission entity that is for the course as per the clazzUid
    |              parameter that is granted to the person directly or to the enrolmentRole that the 
    |              person has in the course, then permission is granted.
    |              */              
    |              OR EXISTS(SELECT CoursePermission.cpUid
    |                          FROM CoursePermission
    |                               
    |        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson 
    |                        ON CoursePermission.cpToEnrolmentRole != 0
    |                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = 
    |                           (SELECT COALESCE(
    |                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid 
    |                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner
    |                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid
    |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = CAST(? AS BIGINT)
    |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive
    |                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   
    |                                     LIMIT 1), 0))
    |    
    |                         WHERE CoursePermission.cpClazzUid = CAST(? AS BIGINT)
    |                           AND (CoursePermission.cpToPersonUid = CAST(? AS BIGINT) 
    |                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)
    |                           AND (CoursePermission.cpPermissionsFlag & 
    |         8192
    |                     
    |        ) > 0)
    |              OR EXISTS(SELECT SystemPermission.spUid
    |                          FROM SystemPermission
    |                         WHERE SystemPermission.spToPersonUid = CAST(? AS BIGINT)
    |                           AND (SystemPermission.spPermissionsFlag & 
    |     8192
    |                     
    |        ) > 0)
    |               )
    |    )
    |                  OR EXISTS(
    |                     SELECT 1
    |                       FROM CourseGroupMember _CourseGroupMemberForActivePerson
    |                      WHERE _CourseGroupMemberForActivePerson.cgmPersonUid = CAST(? AS BIGINT)
    |                        AND _CourseGroupMemberForActivePerson.cgmGroupNumber = CourseGroupMember.cgmGroupNumber)     
    |                 )
    |      ORDER BY Person.firstNames, Person.lastName ASC
    |    
    """.trimMargin(),
    postgreSql = """
    |
    |        --First get a list of all enrolments - this may contains duplicates for students who leave and re-enrol
    |        WITH AllEnrollmentsAndActiveStatus(enrolledPersonUid, isActive) AS 
    |             (SELECT ClazzEnrolment.clazzEnrolmentPersonUid AS enrolledPersonUid,
    |                     (? BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined AND ClazzEnrolment.clazzEnrolmentDateLeft) AS isActive
    |                FROM ClazzEnrolment
    |               WHERE ClazzEnrolment.clazzEnrolmentClazzUid = 
    |        CASE(?)
    |                         WHEN 0 THEN 
    |                                (SELECT CourseGroupSet.cgsClazzUid
    |                                   FROM CourseGroupSet
    |                                  WHERE CourseGroupSet.cgsUid = ?)
    |                         ELSE ?
    |                     END
    |    
    |                 AND ClazzEnrolment.clazzEnrolmentRole = 1000),
    |        --Consolidate and removes any duplicates
    |             EnrolledStudentPersonUids(enrolledPersonUid, isActive) AS
    |             (SELECT DISTINCT AllEnrollmentsAndActiveStatus.enrolledPersonUid,
    |                     (SELECT CAST(AllEnrollmentsInner.isActive AS INTEGER)
    |                        FROM AllEnrollmentsAndActiveStatus AllEnrollmentsInner
    |                       WHERE AllEnrollmentsInner.enrolledPersonUid = AllEnrollmentsAndActiveStatus.enrolledPersonUid
    |                    ORDER BY AllEnrollmentsInner.isActive DESC
    |                       LIMIT 1) AS isActive
    |                FROM AllEnrollmentsAndActiveStatus)
    |        
    |        -- Now create a list with each students name, the coursegroupmember object if any and active status        
    |        SELECT (Person.firstNames || ' ' || Person.lastName) AS name,
    |               Person.personUid,
    |               CourseGroupMember.*,
    |               PersonPicture.*,
    |               EnrolledStudentPersonUids.isActive AS enrolmentIsActive,
    |               PersonPicture.personPictureThumbnailUri AS pictureUri
    |          FROM EnrolledStudentPersonUids
    |               JOIN Person
    |                    ON Person.personUid = EnrolledStudentPersonUids.enrolledPersonUid 
    |               LEFT JOIN PersonPicture
    |                         ON PersonPicture.personPictureUid = Person.personUid  
    |               -- LEFT JOIN will use the most recent member in case of duplicate assignments eg if      
    |               LEFT JOIN CourseGroupMember
    |                         ON CourseGroupMember.cgmUid = 
    |                            (SELECT CourseGroupMember.cgmUid
    |                               FROM CourseGroupMember
    |                              WHERE CourseGroupMember.cgmPersonUid = EnrolledStudentPersonUids.enrolledPersonUid
    |                                AND CourseGroupMember.cgmSetUid = ? 
    |                           ORDER BY CourseGroupMember.cgmLct DESC        
    |                              LIMIT 1)
    |         WHERE (? = 0 OR ? = EnrolledStudentPersonUids.isActive)  
    |               /* 
    |                * Begin permission check -  must have course view members permission, or active 
    |                * user must be in the same group 
    |                */ 
    |            AND (
    |                    ((
    |             /* If the accountPersonUid is the owner of the course, all permissions are granted */
    |             (COALESCE(
    |                          (SELECT _Clazz_Permission.clazzOwnerPersonUid 
    |                             FROM Clazz _Clazz_Permission
    |                            WHERE _Clazz_Permission.clazzUid = ?), 0) = ?)
    |              /* 
    |              If there is a CoursePermission entity that is for the course as per the clazzUid
    |              parameter that is granted to the person directly or to the enrolmentRole that the 
    |              person has in the course, then permission is granted.
    |              */              
    |              OR EXISTS(SELECT CoursePermission.cpUid
    |                          FROM CoursePermission
    |                               
    |        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson 
    |                        ON CoursePermission.cpToEnrolmentRole != 0
    |                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = 
    |                           (SELECT COALESCE(
    |                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid 
    |                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner
    |                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid
    |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = ?
    |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive
    |                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   
    |                                     LIMIT 1), 0))
    |    
    |                         WHERE CoursePermission.cpClazzUid = ?
    |                           AND (CoursePermission.cpToPersonUid = ? 
    |                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)
    |                           AND (CoursePermission.cpPermissionsFlag & 
    |         8192
    |                     
    |        ) > 0)
    |              OR EXISTS(SELECT SystemPermission.spUid
    |                          FROM SystemPermission
    |                         WHERE SystemPermission.spToPersonUid = ?
    |                           AND (SystemPermission.spPermissionsFlag & 
    |     8192
    |                     
    |        ) > 0)
    |               )
    |    )
    |                  OR EXISTS(
    |                     SELECT 1
    |                       FROM CourseGroupMember _CourseGroupMemberForActivePerson
    |                      WHERE _CourseGroupMemberForActivePerson.cgmPersonUid = ?
    |                        AND _CourseGroupMemberForActivePerson.cgmGroupNumber = CourseGroupMember.cgmGroupNumber)     
    |                 )
    |      ORDER BY Person.firstNames, Person.lastName ASC
    |    
    |""".trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,time)
    _stmt.setLong(2,clazzUid)
    _stmt.setLong(3,cgsUid)
    _stmt.setLong(4,clazzUid)
    _stmt.setLong(5,cgsUid)
    _stmt.setInt(6,activeFilter)
    _stmt.setInt(7,activeFilter)
    _stmt.setLong(8,clazzUid)
    _stmt.setLong(9,accountPersonUid)
    _stmt.setLong(10,accountPersonUid)
    _stmt.setLong(11,clazzUid)
    _stmt.setLong(12,accountPersonUid)
    _stmt.setLong(13,accountPersonUid)
    _stmt.setLong(14,accountPersonUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_name = _result.getString("name")
        val _tmp_personUid = _result.getLong("personUid")
        val _tmp_enrolmentIsActive = _result.getBoolean("enrolmentIsActive")
        val _tmp_pictureUri = _result.getString("pictureUri")
        var _tmp_CourseGroupMember_nullCount = 0
        val _tmp_cgmUid = _result.getLong("cgmUid")
        if(_result.wasNull()) _tmp_CourseGroupMember_nullCount++
        val _tmp_cgmSetUid = _result.getLong("cgmSetUid")
        if(_result.wasNull()) _tmp_CourseGroupMember_nullCount++
        val _tmp_cgmGroupNumber = _result.getInt("cgmGroupNumber")
        if(_result.wasNull()) _tmp_CourseGroupMember_nullCount++
        val _tmp_cgmPersonUid = _result.getLong("cgmPersonUid")
        if(_result.wasNull()) _tmp_CourseGroupMember_nullCount++
        val _tmp_cgmLct = _result.getLong("cgmLct")
        if(_result.wasNull()) _tmp_CourseGroupMember_nullCount++
        val _tmp_CourseGroupMember_isAllNull = _tmp_CourseGroupMember_nullCount == 5
        CourseGroupMemberAndName().apply {
          this.name = _tmp_name
          this.personUid = _tmp_personUid
          this.enrolmentIsActive = _tmp_enrolmentIsActive
          this.pictureUri = _tmp_pictureUri
          if(!_tmp_CourseGroupMember_isAllNull) {
            this.cgm = CourseGroupMember().apply {
              this.cgmUid = _tmp_cgmUid
              this.cgmSetUid = _tmp_cgmSetUid
              this.cgmGroupNumber = _tmp_cgmGroupNumber
              this.cgmPersonUid = _tmp_cgmPersonUid
              this.cgmLct = _tmp_cgmLct
            }
          }
        }
      }
    }
  }

  override fun findByCourseGroupSetAndClazzAsFlow(
    cgsUid: Long,
    clazzUid: Long,
    time: Long,
    activeFilter: Int,
    accountPersonUid: Long,
  ): Flow<List<CourseGroupMemberAndName>> = _db.doorFlow(arrayOf("ClazzEnrolment", "Person",
      "PersonPicture", "CourseGroupMember", "CourseGroupSet")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        --First get a list of all enrolments - this may contains duplicates for students who leave and re-enrol
      |        WITH AllEnrollmentsAndActiveStatus(enrolledPersonUid, isActive) AS 
      |             (SELECT ClazzEnrolment.clazzEnrolmentPersonUid AS enrolledPersonUid,
      |                     (CAST(? AS BIGINT) BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined AND ClazzEnrolment.clazzEnrolmentDateLeft) AS isActive
      |                FROM ClazzEnrolment
      |               WHERE ClazzEnrolment.clazzEnrolmentClazzUid = 
      |        CASE(CAST(? AS BIGINT))
      |                         WHEN 0 THEN 
      |                                (SELECT CourseGroupSet.cgsClazzUid
      |                                   FROM CourseGroupSet
      |                                  WHERE CourseGroupSet.cgsUid = CAST(? AS BIGINT))
      |                         ELSE CAST(? AS BIGINT)
      |                     END
      |    
      |                 AND ClazzEnrolment.clazzEnrolmentRole = 1000),
      |        --Consolidate and removes any duplicates
      |             EnrolledStudentPersonUids(enrolledPersonUid, isActive) AS
      |             (SELECT DISTINCT AllEnrollmentsAndActiveStatus.enrolledPersonUid,
      |                     (SELECT CAST(AllEnrollmentsInner.isActive AS INTEGER)
      |                        FROM AllEnrollmentsAndActiveStatus AllEnrollmentsInner
      |                       WHERE AllEnrollmentsInner.enrolledPersonUid = AllEnrollmentsAndActiveStatus.enrolledPersonUid
      |                    ORDER BY AllEnrollmentsInner.isActive DESC
      |                       LIMIT 1) AS isActive
      |                FROM AllEnrollmentsAndActiveStatus)
      |        
      |        -- Now create a list with each students name, the coursegroupmember object if any and active status        
      |        SELECT (Person.firstNames || ' ' || Person.lastName) AS name,
      |               Person.personUid,
      |               CourseGroupMember.*,
      |               PersonPicture.*,
      |               EnrolledStudentPersonUids.isActive AS enrolmentIsActive,
      |               PersonPicture.personPictureThumbnailUri AS pictureUri
      |          FROM EnrolledStudentPersonUids
      |               JOIN Person
      |                    ON Person.personUid = EnrolledStudentPersonUids.enrolledPersonUid 
      |               LEFT JOIN PersonPicture
      |                         ON PersonPicture.personPictureUid = Person.personUid  
      |               -- LEFT JOIN will use the most recent member in case of duplicate assignments eg if      
      |               LEFT JOIN CourseGroupMember
      |                         ON CourseGroupMember.cgmUid = 
      |                            (SELECT CourseGroupMember.cgmUid
      |                               FROM CourseGroupMember
      |                              WHERE CourseGroupMember.cgmPersonUid = EnrolledStudentPersonUids.enrolledPersonUid
      |                                AND CourseGroupMember.cgmSetUid = CAST(? AS BIGINT) 
      |                           ORDER BY CourseGroupMember.cgmLct DESC        
      |                              LIMIT 1)
      |         WHERE (? = 0 OR ? = EnrolledStudentPersonUids.isActive)  
      |               /* 
      |                * Begin permission check -  must have course view members permission, or active 
      |                * user must be in the same group 
      |                */ 
      |            AND (
      |                    ((
      |             /* If the accountPersonUid is the owner of the course, all permissions are granted */
      |             (COALESCE(
      |                          (SELECT _Clazz_Permission.clazzOwnerPersonUid 
      |                             FROM Clazz _Clazz_Permission
      |                            WHERE _Clazz_Permission.clazzUid = CAST(? AS BIGINT)), 0) = CAST(? AS BIGINT))
      |              /* 
      |              If there is a CoursePermission entity that is for the course as per the clazzUid
      |              parameter that is granted to the person directly or to the enrolmentRole that the 
      |              person has in the course, then permission is granted.
      |              */              
      |              OR EXISTS(SELECT CoursePermission.cpUid
      |                          FROM CoursePermission
      |                               
      |        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson 
      |                        ON CoursePermission.cpToEnrolmentRole != 0
      |                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = 
      |                           (SELECT COALESCE(
      |                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid 
      |                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner
      |                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid
      |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = CAST(? AS BIGINT)
      |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive
      |                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   
      |                                     LIMIT 1), 0))
      |    
      |                         WHERE CoursePermission.cpClazzUid = CAST(? AS BIGINT)
      |                           AND (CoursePermission.cpToPersonUid = CAST(? AS BIGINT) 
      |                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)
      |                           AND (CoursePermission.cpPermissionsFlag & 
      |         8192
      |                     
      |        ) > 0)
      |              OR EXISTS(SELECT SystemPermission.spUid
      |                          FROM SystemPermission
      |                         WHERE SystemPermission.spToPersonUid = CAST(? AS BIGINT)
      |                           AND (SystemPermission.spPermissionsFlag & 
      |     8192
      |                     
      |        ) > 0)
      |               )
      |    )
      |                  OR EXISTS(
      |                     SELECT 1
      |                       FROM CourseGroupMember _CourseGroupMemberForActivePerson
      |                      WHERE _CourseGroupMemberForActivePerson.cgmPersonUid = CAST(? AS BIGINT)
      |                        AND _CourseGroupMemberForActivePerson.cgmGroupNumber = CourseGroupMember.cgmGroupNumber)     
      |                 )
      |      ORDER BY Person.firstNames, Person.lastName ASC
      |    
      """.trimMargin(),
      postgreSql = """
      |
      |        --First get a list of all enrolments - this may contains duplicates for students who leave and re-enrol
      |        WITH AllEnrollmentsAndActiveStatus(enrolledPersonUid, isActive) AS 
      |             (SELECT ClazzEnrolment.clazzEnrolmentPersonUid AS enrolledPersonUid,
      |                     (? BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined AND ClazzEnrolment.clazzEnrolmentDateLeft) AS isActive
      |                FROM ClazzEnrolment
      |               WHERE ClazzEnrolment.clazzEnrolmentClazzUid = 
      |        CASE(?)
      |                         WHEN 0 THEN 
      |                                (SELECT CourseGroupSet.cgsClazzUid
      |                                   FROM CourseGroupSet
      |                                  WHERE CourseGroupSet.cgsUid = ?)
      |                         ELSE ?
      |                     END
      |    
      |                 AND ClazzEnrolment.clazzEnrolmentRole = 1000),
      |        --Consolidate and removes any duplicates
      |             EnrolledStudentPersonUids(enrolledPersonUid, isActive) AS
      |             (SELECT DISTINCT AllEnrollmentsAndActiveStatus.enrolledPersonUid,
      |                     (SELECT CAST(AllEnrollmentsInner.isActive AS INTEGER)
      |                        FROM AllEnrollmentsAndActiveStatus AllEnrollmentsInner
      |                       WHERE AllEnrollmentsInner.enrolledPersonUid = AllEnrollmentsAndActiveStatus.enrolledPersonUid
      |                    ORDER BY AllEnrollmentsInner.isActive DESC
      |                       LIMIT 1) AS isActive
      |                FROM AllEnrollmentsAndActiveStatus)
      |        
      |        -- Now create a list with each students name, the coursegroupmember object if any and active status        
      |        SELECT (Person.firstNames || ' ' || Person.lastName) AS name,
      |               Person.personUid,
      |               CourseGroupMember.*,
      |               PersonPicture.*,
      |               EnrolledStudentPersonUids.isActive AS enrolmentIsActive,
      |               PersonPicture.personPictureThumbnailUri AS pictureUri
      |          FROM EnrolledStudentPersonUids
      |               JOIN Person
      |                    ON Person.personUid = EnrolledStudentPersonUids.enrolledPersonUid 
      |               LEFT JOIN PersonPicture
      |                         ON PersonPicture.personPictureUid = Person.personUid  
      |               -- LEFT JOIN will use the most recent member in case of duplicate assignments eg if      
      |               LEFT JOIN CourseGroupMember
      |                         ON CourseGroupMember.cgmUid = 
      |                            (SELECT CourseGroupMember.cgmUid
      |                               FROM CourseGroupMember
      |                              WHERE CourseGroupMember.cgmPersonUid = EnrolledStudentPersonUids.enrolledPersonUid
      |                                AND CourseGroupMember.cgmSetUid = ? 
      |                           ORDER BY CourseGroupMember.cgmLct DESC        
      |                              LIMIT 1)
      |         WHERE (? = 0 OR ? = EnrolledStudentPersonUids.isActive)  
      |               /* 
      |                * Begin permission check -  must have course view members permission, or active 
      |                * user must be in the same group 
      |                */ 
      |            AND (
      |                    ((
      |             /* If the accountPersonUid is the owner of the course, all permissions are granted */
      |             (COALESCE(
      |                          (SELECT _Clazz_Permission.clazzOwnerPersonUid 
      |                             FROM Clazz _Clazz_Permission
      |                            WHERE _Clazz_Permission.clazzUid = ?), 0) = ?)
      |              /* 
      |              If there is a CoursePermission entity that is for the course as per the clazzUid
      |              parameter that is granted to the person directly or to the enrolmentRole that the 
      |              person has in the course, then permission is granted.
      |              */              
      |              OR EXISTS(SELECT CoursePermission.cpUid
      |                          FROM CoursePermission
      |                               
      |        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson 
      |                        ON CoursePermission.cpToEnrolmentRole != 0
      |                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = 
      |                           (SELECT COALESCE(
      |                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid 
      |                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner
      |                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid
      |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = ?
      |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive
      |                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   
      |                                     LIMIT 1), 0))
      |    
      |                         WHERE CoursePermission.cpClazzUid = ?
      |                           AND (CoursePermission.cpToPersonUid = ? 
      |                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)
      |                           AND (CoursePermission.cpPermissionsFlag & 
      |         8192
      |                     
      |        ) > 0)
      |              OR EXISTS(SELECT SystemPermission.spUid
      |                          FROM SystemPermission
      |                         WHERE SystemPermission.spToPersonUid = ?
      |                           AND (SystemPermission.spPermissionsFlag & 
      |     8192
      |                     
      |        ) > 0)
      |               )
      |    )
      |                  OR EXISTS(
      |                     SELECT 1
      |                       FROM CourseGroupMember _CourseGroupMemberForActivePerson
      |                      WHERE _CourseGroupMemberForActivePerson.cgmPersonUid = ?
      |                        AND _CourseGroupMemberForActivePerson.cgmGroupNumber = CourseGroupMember.cgmGroupNumber)     
      |                 )
      |      ORDER BY Person.firstNames, Person.lastName ASC
      |    
      |""".trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,time)
      _stmt.setLong(2,clazzUid)
      _stmt.setLong(3,cgsUid)
      _stmt.setLong(4,clazzUid)
      _stmt.setLong(5,cgsUid)
      _stmt.setInt(6,activeFilter)
      _stmt.setInt(7,activeFilter)
      _stmt.setLong(8,clazzUid)
      _stmt.setLong(9,accountPersonUid)
      _stmt.setLong(10,accountPersonUid)
      _stmt.setLong(11,clazzUid)
      _stmt.setLong(12,accountPersonUid)
      _stmt.setLong(13,accountPersonUid)
      _stmt.setLong(14,accountPersonUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          val _tmp_name = _result.getString("name")
          val _tmp_personUid = _result.getLong("personUid")
          val _tmp_enrolmentIsActive = _result.getBoolean("enrolmentIsActive")
          val _tmp_pictureUri = _result.getString("pictureUri")
          var _tmp_CourseGroupMember_nullCount = 0
          val _tmp_cgmUid = _result.getLong("cgmUid")
          if(_result.wasNull()) _tmp_CourseGroupMember_nullCount++
          val _tmp_cgmSetUid = _result.getLong("cgmSetUid")
          if(_result.wasNull()) _tmp_CourseGroupMember_nullCount++
          val _tmp_cgmGroupNumber = _result.getInt("cgmGroupNumber")
          if(_result.wasNull()) _tmp_CourseGroupMember_nullCount++
          val _tmp_cgmPersonUid = _result.getLong("cgmPersonUid")
          if(_result.wasNull()) _tmp_CourseGroupMember_nullCount++
          val _tmp_cgmLct = _result.getLong("cgmLct")
          if(_result.wasNull()) _tmp_CourseGroupMember_nullCount++
          val _tmp_CourseGroupMember_isAllNull = _tmp_CourseGroupMember_nullCount == 5
          CourseGroupMemberAndName().apply {
            this.name = _tmp_name
            this.personUid = _tmp_personUid
            this.enrolmentIsActive = _tmp_enrolmentIsActive
            this.pictureUri = _tmp_pictureUri
            if(!_tmp_CourseGroupMember_isAllNull) {
              this.cgm = CourseGroupMember().apply {
                this.cgmUid = _tmp_cgmUid
                this.cgmSetUid = _tmp_cgmSetUid
                this.cgmGroupNumber = _tmp_cgmGroupNumber
                this.cgmPersonUid = _tmp_cgmPersonUid
                this.cgmLct = _tmp_cgmLct
              }
            }
          }
        }
      }
    }
  }

  override suspend fun findByCourseGroupSetAndClazzAsFlowPersons(clazzUid: Long, cgsUid: Long):
      List<PersonAndPicture> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT Person.*, PersonPicture.*
    |          FROM Person
    |               LEFT JOIN PersonPicture
    |                         ON PersonPicture.personPictureUid = Person.personUid
    |         WHERE Person.personUid IN
    |               (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid
    |                  FROM ClazzEnrolment
    |                 WHERE ClazzEnrolment.clazzEnrolmentClazzUid = 
    |        CASE(CAST(? AS BIGINT))
    |                         WHEN 0 THEN 
    |                                (SELECT CourseGroupSet.cgsClazzUid
    |                                   FROM CourseGroupSet
    |                                  WHERE CourseGroupSet.cgsUid = CAST(? AS BIGINT))
    |                         ELSE CAST(? AS BIGINT)
    |                     END
    |    )
    |    
    """.trimMargin(),
    postgreSql = """
    |
    |        SELECT Person.*, PersonPicture.*
    |          FROM Person
    |               LEFT JOIN PersonPicture
    |                         ON PersonPicture.personPictureUid = Person.personUid
    |         WHERE Person.personUid IN
    |               (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid
    |                  FROM ClazzEnrolment
    |                 WHERE ClazzEnrolment.clazzEnrolmentClazzUid = 
    |        CASE(?)
    |                         WHEN 0 THEN 
    |                                (SELECT CourseGroupSet.cgsClazzUid
    |                                   FROM CourseGroupSet
    |                                  WHERE CourseGroupSet.cgsUid = ?)
    |                         ELSE ?
    |                     END
    |    )
    |    
    |""".trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,clazzUid)
    _stmt.setLong(2,cgsUid)
    _stmt.setLong(3,clazzUid)
    _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
        PersonAndPicture().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.picture = 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
            }
          }
        }
      }
    }
  }

  override suspend fun findByCourseGroupSetAndClazzAsFlowEnrolments(clazzUid: Long, cgsUid: Long):
      List<ClazzEnrolment> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT ClazzEnrolment.*
    |          FROM ClazzEnrolment
    |         WHERE ClazzEnrolment.clazzEnrolmentClazzUid = 
    |        CASE(CAST(? AS BIGINT))
    |                         WHEN 0 THEN 
    |                                (SELECT CourseGroupSet.cgsClazzUid
    |                                   FROM CourseGroupSet
    |                                  WHERE CourseGroupSet.cgsUid = CAST(? AS BIGINT))
    |                         ELSE CAST(? AS BIGINT)
    |                     END
    |    
    |    
    """.trimMargin(),
    postgreSql = """
    |
    |        SELECT ClazzEnrolment.*
    |          FROM ClazzEnrolment
    |         WHERE ClazzEnrolment.clazzEnrolmentClazzUid = 
    |        CASE(?)
    |                         WHEN 0 THEN 
    |                                (SELECT CourseGroupSet.cgsClazzUid
    |                                   FROM CourseGroupSet
    |                                  WHERE CourseGroupSet.cgsUid = ?)
    |                         ELSE ?
    |                     END
    |    
    |    
    |""".trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,clazzUid)
    _stmt.setLong(2,cgsUid)
    _stmt.setLong(3,clazzUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_clazzEnrolmentUid = _result.getLong("clazzEnrolmentUid")
        val _tmp_clazzEnrolmentPersonUid = _result.getLong("clazzEnrolmentPersonUid")
        val _tmp_clazzEnrolmentClazzUid = _result.getLong("clazzEnrolmentClazzUid")
        val _tmp_clazzEnrolmentDateJoined = _result.getLong("clazzEnrolmentDateJoined")
        val _tmp_clazzEnrolmentDateLeft = _result.getLong("clazzEnrolmentDateLeft")
        val _tmp_clazzEnrolmentRole = _result.getInt("clazzEnrolmentRole")
        val _tmp_clazzEnrolmentAttendancePercentage =
            _result.getFloat("clazzEnrolmentAttendancePercentage")
        val _tmp_clazzEnrolmentActive = _result.getBoolean("clazzEnrolmentActive")
        val _tmp_clazzEnrolmentLeavingReasonUid = _result.getLong("clazzEnrolmentLeavingReasonUid")
        val _tmp_clazzEnrolmentOutcome = _result.getInt("clazzEnrolmentOutcome")
        val _tmp_clazzEnrolmentLocalChangeSeqNum =
            _result.getLong("clazzEnrolmentLocalChangeSeqNum")
        val _tmp_clazzEnrolmentMasterChangeSeqNum =
            _result.getLong("clazzEnrolmentMasterChangeSeqNum")
        val _tmp_clazzEnrolmentLastChangedBy = _result.getInt("clazzEnrolmentLastChangedBy")
        val _tmp_clazzEnrolmentLct = _result.getLong("clazzEnrolmentLct")
        val _tmp_clazzEnrolmentInviteUid = _result.getLong("clazzEnrolmentInviteUid")
        ClazzEnrolment().apply {
          this.clazzEnrolmentUid = _tmp_clazzEnrolmentUid
          this.clazzEnrolmentPersonUid = _tmp_clazzEnrolmentPersonUid
          this.clazzEnrolmentClazzUid = _tmp_clazzEnrolmentClazzUid
          this.clazzEnrolmentDateJoined = _tmp_clazzEnrolmentDateJoined
          this.clazzEnrolmentDateLeft = _tmp_clazzEnrolmentDateLeft
          this.clazzEnrolmentRole = _tmp_clazzEnrolmentRole
          this.clazzEnrolmentAttendancePercentage = _tmp_clazzEnrolmentAttendancePercentage
          this.clazzEnrolmentActive = _tmp_clazzEnrolmentActive
          this.clazzEnrolmentLeavingReasonUid = _tmp_clazzEnrolmentLeavingReasonUid
          this.clazzEnrolmentOutcome = _tmp_clazzEnrolmentOutcome
          this.clazzEnrolmentLocalChangeSeqNum = _tmp_clazzEnrolmentLocalChangeSeqNum
          this.clazzEnrolmentMasterChangeSeqNum = _tmp_clazzEnrolmentMasterChangeSeqNum
          this.clazzEnrolmentLastChangedBy = _tmp_clazzEnrolmentLastChangedBy
          this.clazzEnrolmentLct = _tmp_clazzEnrolmentLct
          this.clazzEnrolmentInviteUid = _tmp_clazzEnrolmentInviteUid
        }
      }
    }
  }

  override suspend fun findByCourseGroupSetAndGroupNumAsync(
    courseGroupSetUid: Long,
    groupNum: Int,
    clazzUid: Long,
    assignmentUid: Long,
    accountPersonUid: Long,
  ): List<CourseGroupMemberAndPerson> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT CourseGroupMember.*, Person.*
    |          FROM CourseGroupMember
    |               JOIN Person 
    |                    ON Person.personUid = CourseGroupMember.cgmPersonUid
    |         WHERE (    CourseGroupMember.cgmSetUid = CAST(? AS BIGINT)
    |                AND CourseGroupMember.cgmGroupNumber = ?)
    |           AND (    /* Grant permission where the active person is in the group */ 
    |                    EXISTS(SELECT 1
    |                             FROM CourseGroupMember CourseGroupMemberInternal
    |                            WHERE CourseGroupMemberInternal.cgmSetUid = CAST(? AS BIGINT)
    |                              AND CourseGroupMemberInternal.cgmPersonUid = CAST(? AS BIGINT))
    |                    /* Grant permission where the activepersonuid is in a group assigned to mark this group */
    |                 OR EXISTS(SELECT 1
    |                             FROM PeerReviewerAllocation
    |                            WHERE PeerReviewerAllocation.praAssignmentUid = CAST(? AS BIGINT)
    |                              AND PeerReviewerAllocation.praMarkerSubmitterUid = ?
    |                              AND EXISTS(SELECT 1
    |                                           FROM CourseGroupMember CourseGroupMemberInternal
    |                                          WHERE CourseGroupMemberInternal.cgmSetUid = PeerReviewerAllocation.praMarkerSubmitterUid
    |                                            AND CourseGroupMemberInternal.cgmPersonUid = CAST(? AS BIGINT))) 
    |                    /* Grant permission where the active person has the select person permission for the class */                        
    |                 OR ((
    |             /* If the accountPersonUid is the owner of the course, all permissions are granted */
    |             (COALESCE(
    |                          (SELECT _Clazz_Permission.clazzOwnerPersonUid 
    |                             FROM Clazz _Clazz_Permission
    |                            WHERE _Clazz_Permission.clazzUid = CAST(? AS BIGINT)), 0) = CAST(? AS BIGINT))
    |              /* 
    |              If there is a CoursePermission entity that is for the course as per the clazzUid
    |              parameter that is granted to the person directly or to the enrolmentRole that the 
    |              person has in the course, then permission is granted.
    |              */              
    |              OR EXISTS(SELECT CoursePermission.cpUid
    |                          FROM CoursePermission
    |                               
    |        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson 
    |                        ON CoursePermission.cpToEnrolmentRole != 0
    |                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = 
    |                           (SELECT COALESCE(
    |                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid 
    |                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner
    |                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid
    |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = CAST(? AS BIGINT)
    |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive
    |                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   
    |                                     LIMIT 1), 0))
    |    
    |                         WHERE CoursePermission.cpClazzUid = CAST(? AS BIGINT)
    |                           AND (CoursePermission.cpToPersonUid = CAST(? AS BIGINT) 
    |                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)
    |                           AND (CoursePermission.cpPermissionsFlag & 
    |         8192
    |                     
    |        ) > 0)
    |              OR EXISTS(SELECT SystemPermission.spUid
    |                          FROM SystemPermission
    |                         WHERE SystemPermission.spToPersonUid = CAST(? AS BIGINT)
    |                           AND (SystemPermission.spPermissionsFlag & 
    |     8192
    |                     
    |        ) > 0)
    |               )
    |    )    
    |               )
    |               
    |    
    """.trimMargin(),
    postgreSql = """
    |
    |        SELECT CourseGroupMember.*, Person.*
    |          FROM CourseGroupMember
    |               JOIN Person 
    |                    ON Person.personUid = CourseGroupMember.cgmPersonUid
    |         WHERE (    CourseGroupMember.cgmSetUid = ?
    |                AND CourseGroupMember.cgmGroupNumber = ?)
    |           AND (    /* Grant permission where the active person is in the group */ 
    |                    EXISTS(SELECT 1
    |                             FROM CourseGroupMember CourseGroupMemberInternal
    |                            WHERE CourseGroupMemberInternal.cgmSetUid = ?
    |                              AND CourseGroupMemberInternal.cgmPersonUid = ?)
    |                    /* Grant permission where the activepersonuid is in a group assigned to mark this group */
    |                 OR EXISTS(SELECT 1
    |                             FROM PeerReviewerAllocation
    |                            WHERE PeerReviewerAllocation.praAssignmentUid = ?
    |                              AND PeerReviewerAllocation.praMarkerSubmitterUid = ?
    |                              AND EXISTS(SELECT 1
    |                                           FROM CourseGroupMember CourseGroupMemberInternal
    |                                          WHERE CourseGroupMemberInternal.cgmSetUid = PeerReviewerAllocation.praMarkerSubmitterUid
    |                                            AND CourseGroupMemberInternal.cgmPersonUid = ?)) 
    |                    /* Grant permission where the active person has the select person permission for the class */                        
    |                 OR ((
    |             /* If the accountPersonUid is the owner of the course, all permissions are granted */
    |             (COALESCE(
    |                          (SELECT _Clazz_Permission.clazzOwnerPersonUid 
    |                             FROM Clazz _Clazz_Permission
    |                            WHERE _Clazz_Permission.clazzUid = ?), 0) = ?)
    |              /* 
    |              If there is a CoursePermission entity that is for the course as per the clazzUid
    |              parameter that is granted to the person directly or to the enrolmentRole that the 
    |              person has in the course, then permission is granted.
    |              */              
    |              OR EXISTS(SELECT CoursePermission.cpUid
    |                          FROM CoursePermission
    |                               
    |        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson 
    |                        ON CoursePermission.cpToEnrolmentRole != 0
    |                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = 
    |                           (SELECT COALESCE(
    |                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid 
    |                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner
    |                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid
    |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = ?
    |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive
    |                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   
    |                                     LIMIT 1), 0))
    |    
    |                         WHERE CoursePermission.cpClazzUid = ?
    |                           AND (CoursePermission.cpToPersonUid = ? 
    |                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)
    |                           AND (CoursePermission.cpPermissionsFlag & 
    |         8192
    |                     
    |        ) > 0)
    |              OR EXISTS(SELECT SystemPermission.spUid
    |                          FROM SystemPermission
    |                         WHERE SystemPermission.spToPersonUid = ?
    |                           AND (SystemPermission.spPermissionsFlag & 
    |     8192
    |                     
    |        ) > 0)
    |               )
    |    )    
    |               )
    |               
    |    
    |""".trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,courseGroupSetUid)
    _stmt.setInt(2,groupNum)
    _stmt.setLong(3,courseGroupSetUid)
    _stmt.setLong(4,accountPersonUid)
    _stmt.setLong(5,assignmentUid)
    _stmt.setInt(6,groupNum)
    _stmt.setLong(7,accountPersonUid)
    _stmt.setLong(8,clazzUid)
    _stmt.setLong(9,accountPersonUid)
    _stmt.setLong(10,accountPersonUid)
    _stmt.setLong(11,clazzUid)
    _stmt.setLong(12,accountPersonUid)
    _stmt.setLong(13,accountPersonUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        var _tmp_CourseGroupMember_nullCount = 0
        val _tmp_cgmUid = _result.getLong("cgmUid")
        if(_result.wasNull()) _tmp_CourseGroupMember_nullCount++
        val _tmp_cgmSetUid = _result.getLong("cgmSetUid")
        if(_result.wasNull()) _tmp_CourseGroupMember_nullCount++
        val _tmp_cgmGroupNumber = _result.getInt("cgmGroupNumber")
        if(_result.wasNull()) _tmp_CourseGroupMember_nullCount++
        val _tmp_cgmPersonUid = _result.getLong("cgmPersonUid")
        if(_result.wasNull()) _tmp_CourseGroupMember_nullCount++
        val _tmp_cgmLct = _result.getLong("cgmLct")
        if(_result.wasNull()) _tmp_CourseGroupMember_nullCount++
        val _tmp_CourseGroupMember_isAllNull = _tmp_CourseGroupMember_nullCount == 5
        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
        CourseGroupMemberAndPerson().apply {
          if(!_tmp_CourseGroupMember_isAllNull) {
            this.courseGroupMember = CourseGroupMember().apply {
              this.cgmUid = _tmp_cgmUid
              this.cgmSetUid = _tmp_cgmSetUid
              this.cgmGroupNumber = _tmp_cgmGroupNumber
              this.cgmPersonUid = _tmp_cgmPersonUid
              this.cgmLct = _tmp_cgmLct
            }
          }
          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
            }
          }
        }
      }
    }
  }
}
