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.AssignmentPermissionAndActiveUserSubmitterUid
import com.ustadmobile.lib.db.composites.CoursePermissionAndEnrolment
import com.ustadmobile.lib.db.composites.CoursePermissionAndListDetail
import com.ustadmobile.lib.db.composites.PermissionPair
import com.ustadmobile.lib.db.composites.PermissionTriple
import com.ustadmobile.lib.db.entities.ClazzEnrolment
import com.ustadmobile.lib.db.entities.CoursePermission
import com.ustadmobile.lib.db.entities.Person
import com.ustadmobile.lib.db.entities.PersonPicture
import kotlin.Boolean
import kotlin.IllegalArgumentException
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.collections.List
import kotlinx.coroutines.flow.Flow

public class CoursePermissionDao_JdbcImpl(
  public val _db: RoomDatabase,
) : CoursePermissionDao() {
  public val _insertAdapterCoursePermission_upsert: EntityInsertionAdapter<CoursePermission> =
      object : EntityInsertionAdapter<CoursePermission>(_db) {
    override fun makeSql(returnsId: Boolean): String = when(dbType) {
      DoorDbType.SQLITE -> {
        "INSERT OR REPLACE INTO CoursePermission (cpUid, cpLastModified, cpClazzUid, cpToEnrolmentRole, cpToPersonUid, cpToGroupUid, cpPermissionsFlag, cpIsDeleted) VALUES(?, ?, ?, ?, ?, ?, ?, ?)"
      }
      DoorDbType.POSTGRES ->  {
        "INSERT INTO CoursePermission (cpUid, cpLastModified, cpClazzUid, cpToEnrolmentRole, cpToPersonUid, cpToGroupUid, cpPermissionsFlag, cpIsDeleted) VALUES(COALESCE(?,nextval('CoursePermission_cpUid_seq')), ?, ?, ?, ?, ?, ?, ?) ON CONFLICT (cpUid) DO UPDATE SET cpLastModified = excluded.cpLastModified,cpClazzUid = excluded.cpClazzUid,cpToEnrolmentRole = excluded.cpToEnrolmentRole,cpToPersonUid = excluded.cpToPersonUid,cpToGroupUid = excluded.cpToGroupUid,cpPermissionsFlag = excluded.cpPermissionsFlag,cpIsDeleted = excluded.cpIsDeleted" + if(returnsId) { " RETURNING cpUid" } else "" 
      }
      else -> {
        throw IllegalArgumentException("Unsupported db type")
      }
    }

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement, entity: CoursePermission) {
      if(entity.cpUid == 0L) {
        stmt.setObject(1, null)
      } else {
        stmt.setLong(1, entity.cpUid)
      }
      stmt.setLong(2, entity.cpLastModified)
      stmt.setLong(3, entity.cpClazzUid)
      stmt.setInt(4, entity.cpToEnrolmentRole)
      stmt.setLong(5, entity.cpToPersonUid)
      stmt.setLong(6, entity.cpToGroupUid)
      stmt.setLong(7, entity.cpPermissionsFlag)
      stmt.setBoolean(8, entity.cpIsDeleted)
    }
  }

  override suspend fun upsertAsync(coursePermission: CoursePermission) {
    _insertAdapterCoursePermission_upsert.insertAsync(coursePermission)
  }

  override fun findByClazzUidAsPagingSource(clazzUid: Long, includeDeleted: Boolean):
      PagingSource<Int, CoursePermissionAndListDetail> = object :
      DoorLimitOffsetPagingSource<CoursePermissionAndListDetail>(db = _db
  , tableNames = arrayOf("Person", "PersonPicture", "CoursePermission")
  ) {
    override suspend fun loadRows(_limit: Int, _offset: Int): List<CoursePermissionAndListDetail> =
        _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT * FROM (
      |        SELECT CoursePermission.*, Person.*, PersonPicture.*
      |          FROM CoursePermission
      |               LEFT JOIN Person
      |                         ON Person.personUid = CoursePermission.cpToPersonUid
      |               LEFT JOIN PersonPicture
      |                         ON PersonPicture.personPictureUid = Person.personUid
      |         WHERE CoursePermission.cpClazzUid = ? 
      |           AND (CAST(? AS INTEGER) = 1 OR NOT CoursePermission.cpIsDeleted) 
      |    ) AS _PagingData LIMIT ? OFFSET ?
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,clazzUid)
      _stmt.setBoolean(2,includeDeleted)
      _stmt.setInt(3,_limit)
      _stmt.setInt(4,_offset)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          var _tmp_CoursePermission_nullCount = 0
          val _tmp_cpUid = _result.getLong("cpUid")
          if(_result.wasNull()) _tmp_CoursePermission_nullCount++
          val _tmp_cpLastModified = _result.getLong("cpLastModified")
          if(_result.wasNull()) _tmp_CoursePermission_nullCount++
          val _tmp_cpClazzUid = _result.getLong("cpClazzUid")
          if(_result.wasNull()) _tmp_CoursePermission_nullCount++
          val _tmp_cpToEnrolmentRole = _result.getInt("cpToEnrolmentRole")
          if(_result.wasNull()) _tmp_CoursePermission_nullCount++
          val _tmp_cpToPersonUid = _result.getLong("cpToPersonUid")
          if(_result.wasNull()) _tmp_CoursePermission_nullCount++
          val _tmp_cpToGroupUid = _result.getLong("cpToGroupUid")
          if(_result.wasNull()) _tmp_CoursePermission_nullCount++
          val _tmp_cpPermissionsFlag = _result.getLong("cpPermissionsFlag")
          if(_result.wasNull()) _tmp_CoursePermission_nullCount++
          val _tmp_cpIsDeleted = _result.getBoolean("cpIsDeleted")
          if(_result.wasNull()) _tmp_CoursePermission_nullCount++
          val _tmp_CoursePermission_isAllNull = _tmp_CoursePermission_nullCount == 8
          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_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 == 24
          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
          CoursePermissionAndListDetail().apply {
            if(!_tmp_CoursePermission_isAllNull) {
              this.coursePermission = CoursePermission().apply {
                this.cpUid = _tmp_cpUid
                this.cpLastModified = _tmp_cpLastModified
                this.cpClazzUid = _tmp_cpClazzUid
                this.cpToEnrolmentRole = _tmp_cpToEnrolmentRole
                this.cpToPersonUid = _tmp_cpToPersonUid
                this.cpToGroupUid = _tmp_cpToGroupUid
                this.cpPermissionsFlag = _tmp_cpPermissionsFlag
                this.cpIsDeleted = _tmp_cpIsDeleted
              }
            }
            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.dateOfBirth = _tmp_dateOfBirth
                this.personAddress = _tmp_personAddress
                this.personOrgId = _tmp_personOrgId
                this.personGroupUid = _tmp_personGroupUid
                this.personLct = _tmp_personLct
                this.personCountry = _tmp_personCountry
                this.personType = _tmp_personType
                this.personMasterChangeSeqNum = _tmp_personMasterChangeSeqNum
                this.personLocalChangeSeqNum = _tmp_personLocalChangeSeqNum
                this.personLastChangedBy = _tmp_personLastChangedBy
                this.admin = _tmp_admin
                this.personNotes = _tmp_personNotes
                this.fatherName = _tmp_fatherName
                this.fatherNumber = _tmp_fatherNumber
                this.motherName = _tmp_motherName
                this.motherNum = _tmp_motherNum
              }
            }
            if(!_tmp_PersonPicture_isAllNull) {
              this.personPicture = PersonPicture().apply {
                this.personPictureUid = _tmp_personPictureUid
                this.personPictureLct = _tmp_personPictureLct
                this.personPictureUri = _tmp_personPictureUri
                this.personPictureThumbnailUri = _tmp_personPictureThumbnailUri
                this.fileSize = _tmp_fileSize
                this.personPictureActive = _tmp_personPictureActive
              }
            }
          }
        }
      }
    }

    override suspend fun countRows(): Int = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT COUNT(*) FROM (
      |        SELECT CoursePermission.*, Person.*, PersonPicture.*
      |          FROM CoursePermission
      |               LEFT JOIN Person
      |                         ON Person.personUid = CoursePermission.cpToPersonUid
      |               LEFT JOIN PersonPicture
      |                         ON PersonPicture.personPictureUid = Person.personUid
      |         WHERE CoursePermission.cpClazzUid = ? 
      |           AND (CAST(? AS INTEGER) = 1 OR NOT CoursePermission.cpIsDeleted) 
      |    ) AS _PagingCount
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,clazzUid)
      _stmt.setBoolean(2,includeDeleted)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(0) {
          _result.getInt(1)
        }
      }
    }
  }

  override suspend fun findByUidAndClazzUid(uid: Long, clazzUid: Long): CoursePermission? =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT CoursePermission.*
    |          FROM CoursePermission
    |         WHERE CoursePermission.cpUid = ?
    |           AND CoursePermission.cpClazzUid = ?
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,uid)
    _stmt.setLong(2,clazzUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(null) {
        val _tmp_cpUid = _result.getLong("cpUid")
        val _tmp_cpLastModified = _result.getLong("cpLastModified")
        val _tmp_cpClazzUid = _result.getLong("cpClazzUid")
        val _tmp_cpToEnrolmentRole = _result.getInt("cpToEnrolmentRole")
        val _tmp_cpToPersonUid = _result.getLong("cpToPersonUid")
        val _tmp_cpToGroupUid = _result.getLong("cpToGroupUid")
        val _tmp_cpPermissionsFlag = _result.getLong("cpPermissionsFlag")
        val _tmp_cpIsDeleted = _result.getBoolean("cpIsDeleted")
        CoursePermission().apply {
          this.cpUid = _tmp_cpUid
          this.cpLastModified = _tmp_cpLastModified
          this.cpClazzUid = _tmp_cpClazzUid
          this.cpToEnrolmentRole = _tmp_cpToEnrolmentRole
          this.cpToPersonUid = _tmp_cpToPersonUid
          this.cpToGroupUid = _tmp_cpToGroupUid
          this.cpPermissionsFlag = _tmp_cpPermissionsFlag
          this.cpIsDeleted = _tmp_cpIsDeleted
        }
      }
    }
  }

  override fun findByUidAndClazzUidAsFlow(uid: Long, clazzUid: Long): Flow<CoursePermission?> =
      _db.doorFlow(arrayOf("CoursePermission")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT CoursePermission.*
      |          FROM CoursePermission
      |         WHERE CoursePermission.cpUid = ?
      |           AND CoursePermission.cpClazzUid = ? 
      |    
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,uid)
      _stmt.setLong(2,clazzUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(null) {
          val _tmp_cpUid = _result.getLong("cpUid")
          val _tmp_cpLastModified = _result.getLong("cpLastModified")
          val _tmp_cpClazzUid = _result.getLong("cpClazzUid")
          val _tmp_cpToEnrolmentRole = _result.getInt("cpToEnrolmentRole")
          val _tmp_cpToPersonUid = _result.getLong("cpToPersonUid")
          val _tmp_cpToGroupUid = _result.getLong("cpToGroupUid")
          val _tmp_cpPermissionsFlag = _result.getLong("cpPermissionsFlag")
          val _tmp_cpIsDeleted = _result.getBoolean("cpIsDeleted")
          CoursePermission().apply {
            this.cpUid = _tmp_cpUid
            this.cpLastModified = _tmp_cpLastModified
            this.cpClazzUid = _tmp_cpClazzUid
            this.cpToEnrolmentRole = _tmp_cpToEnrolmentRole
            this.cpToPersonUid = _tmp_cpToPersonUid
            this.cpToGroupUid = _tmp_cpToGroupUid
            this.cpPermissionsFlag = _tmp_cpPermissionsFlag
            this.cpIsDeleted = _tmp_cpIsDeleted
          }
        }
      }
    }
  }

  override suspend fun setDeleted(
    cpUid: Long,
    isDeleted: Boolean,
    updateTime: Long,
  ) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        UPDATE CoursePermission
      |           SET cpIsDeleted = ?,
      |               cpLastModified = ?
      |         WHERE cpUid = ?  
      |    
      """.trimMargin(),
      readOnly = false,)
    ) { _stmt -> 
      _stmt.setBoolean(1,isDeleted)
      _stmt.setLong(2,updateTime)
      _stmt.setLong(3,cpUid)
      _stmt.executeUpdateAsyncKmp()
    }
  }

  override suspend fun personHasPermissionWithClazzByEnrolmentUidEntities2(accountPersonUid: Long,
      clazzEnrolmentUid: Long): List<CoursePermissionAndEnrolment> =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |       SELECT CoursePermission.*, ClazzEnrolment_ForAccountPerson.*
    |         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 = (
    |        SELECT COALESCE(
    |               (SELECT _ClazzEnrolment_ForClazzUid.clazzEnrolmentClazzUid
    |                  FROM ClazzEnrolment _ClazzEnrolment_ForClazzUid
    |                 WHERE _ClazzEnrolment_ForClazzUid.clazzEnrolmentUid = ?), 0)
    |    )
    |          AND (CoursePermission.cpToPersonUid = ? 
    |               OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,accountPersonUid)
    _stmt.setLong(2,clazzEnrolmentUid)
    _stmt.setLong(3,accountPersonUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        var _tmp_CoursePermission_nullCount = 0
        val _tmp_cpUid = _result.getLong("cpUid")
        if(_result.wasNull()) _tmp_CoursePermission_nullCount++
        val _tmp_cpLastModified = _result.getLong("cpLastModified")
        if(_result.wasNull()) _tmp_CoursePermission_nullCount++
        val _tmp_cpClazzUid = _result.getLong("cpClazzUid")
        if(_result.wasNull()) _tmp_CoursePermission_nullCount++
        val _tmp_cpToEnrolmentRole = _result.getInt("cpToEnrolmentRole")
        if(_result.wasNull()) _tmp_CoursePermission_nullCount++
        val _tmp_cpToPersonUid = _result.getLong("cpToPersonUid")
        if(_result.wasNull()) _tmp_CoursePermission_nullCount++
        val _tmp_cpToGroupUid = _result.getLong("cpToGroupUid")
        if(_result.wasNull()) _tmp_CoursePermission_nullCount++
        val _tmp_cpPermissionsFlag = _result.getLong("cpPermissionsFlag")
        if(_result.wasNull()) _tmp_CoursePermission_nullCount++
        val _tmp_cpIsDeleted = _result.getBoolean("cpIsDeleted")
        if(_result.wasNull()) _tmp_CoursePermission_nullCount++
        val _tmp_CoursePermission_isAllNull = _tmp_CoursePermission_nullCount == 8
        var _tmp_ClazzEnrolment_nullCount = 0
        val _tmp_clazzEnrolmentUid = _result.getLong("clazzEnrolmentUid")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentPersonUid = _result.getLong("clazzEnrolmentPersonUid")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentClazzUid = _result.getLong("clazzEnrolmentClazzUid")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentDateJoined = _result.getLong("clazzEnrolmentDateJoined")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentDateLeft = _result.getLong("clazzEnrolmentDateLeft")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentRole = _result.getInt("clazzEnrolmentRole")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentAttendancePercentage =
            _result.getFloat("clazzEnrolmentAttendancePercentage")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentActive = _result.getBoolean("clazzEnrolmentActive")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentLeavingReasonUid = _result.getLong("clazzEnrolmentLeavingReasonUid")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentOutcome = _result.getInt("clazzEnrolmentOutcome")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentLocalChangeSeqNum =
            _result.getLong("clazzEnrolmentLocalChangeSeqNum")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentMasterChangeSeqNum =
            _result.getLong("clazzEnrolmentMasterChangeSeqNum")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentLastChangedBy = _result.getInt("clazzEnrolmentLastChangedBy")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentLct = _result.getLong("clazzEnrolmentLct")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_ClazzEnrolment_isAllNull = _tmp_ClazzEnrolment_nullCount == 14
        CoursePermissionAndEnrolment().apply {
          if(!_tmp_CoursePermission_isAllNull) {
            this.coursePermission = CoursePermission().apply {
              this.cpUid = _tmp_cpUid
              this.cpLastModified = _tmp_cpLastModified
              this.cpClazzUid = _tmp_cpClazzUid
              this.cpToEnrolmentRole = _tmp_cpToEnrolmentRole
              this.cpToPersonUid = _tmp_cpToPersonUid
              this.cpToGroupUid = _tmp_cpToGroupUid
              this.cpPermissionsFlag = _tmp_cpPermissionsFlag
              this.cpIsDeleted = _tmp_cpIsDeleted
            }
          }
          if(!_tmp_ClazzEnrolment_isAllNull) {
            this.clazzEnrolment = 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
            }
          }
        }
      }
    }
  }

  override suspend fun userHasEnrolmentEditPermission(accountPersonUid: Long,
      clazzEnrolmentUid: Long): Boolean = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT CASE ? 
    |                WHEN 0 THEN (SELECT EXISTS(
    |                         SELECT 1
    |                           FROM SystemPermission
    |                          WHERE ? != 0 
    |                            AND SystemPermission.spToPersonUid = ?
    |                            AND (SystemPermission.spPermissionsFlag & 2048) > 0
    |                            AND NOT SystemPermission.spIsDeleted))
    |                ELSE (
    |                  SELECT (
    |             /* 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 = (
    |        SELECT COALESCE(
    |               (SELECT _ClazzEnrolment_ForClazzUid.clazzEnrolmentClazzUid
    |                  FROM ClazzEnrolment _ClazzEnrolment_ForClazzUid
    |                 WHERE _ClazzEnrolment_ForClazzUid.clazzEnrolmentUid = ?), 0)
    |    )), 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 = (
    |        SELECT COALESCE(
    |               (SELECT _ClazzEnrolment_ForClazzUid.clazzEnrolmentClazzUid
    |                  FROM ClazzEnrolment _ClazzEnrolment_ForClazzUid
    |                 WHERE _ClazzEnrolment_ForClazzUid.clazzEnrolmentUid = ?), 0)
    |    )
    |                           AND (CoursePermission.cpToPersonUid = ? 
    |                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)
    |                           AND (CoursePermission.cpPermissionsFlag & 
    |         
    |                         (
    |        CASE (
    |        SELECT ClazzEnrolment.clazzEnrolmentRole
    |          FROM ClazzEnrolment
    |         WHERE ClazzEnrolment.clazzEnrolmentUid = ?         
    |    )
    |             WHEN 1000 THEN 8
    |             ELSE 8
    |        END     
    |    )
    |                         
    |        ) > 0)
    |              OR EXISTS(SELECT SystemPermission.spUid
    |                          FROM SystemPermission
    |                         WHERE SystemPermission.spToPersonUid = ?
    |                           AND (SystemPermission.spPermissionsFlag & 
    |     
    |                         (
    |        CASE (
    |        SELECT ClazzEnrolment.clazzEnrolmentRole
    |          FROM ClazzEnrolment
    |         WHERE ClazzEnrolment.clazzEnrolmentUid = ?         
    |    )
    |             WHEN 1000 THEN 8
    |             ELSE 8
    |        END     
    |    )
    |                         
    |        ) > 0)
    |               )
    |     
    |                )
    |               END 
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,clazzEnrolmentUid)
    _stmt.setLong(2,accountPersonUid)
    _stmt.setLong(3,accountPersonUid)
    _stmt.setLong(4,clazzEnrolmentUid)
    _stmt.setLong(5,accountPersonUid)
    _stmt.setLong(6,accountPersonUid)
    _stmt.setLong(7,clazzEnrolmentUid)
    _stmt.setLong(8,accountPersonUid)
    _stmt.setLong(9,clazzEnrolmentUid)
    _stmt.setLong(10,accountPersonUid)
    _stmt.setLong(11,clazzEnrolmentUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(false) {
        _result.getBoolean(1)
      }
    }
  }

  override suspend fun personHasPermissionWithClazzEntities2(accountPersonUid: Long,
      clazzUid: Long): List<CoursePermissionAndEnrolment> =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |       SELECT CoursePermission.*, ClazzEnrolment_ForAccountPerson.*
    |         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 (? = 0 OR CoursePermission.cpClazzUid = ?)
    |          AND (CoursePermission.cpToPersonUid = ? 
    |               OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,accountPersonUid)
    _stmt.setLong(2,clazzUid)
    _stmt.setLong(3,clazzUid)
    _stmt.setLong(4,accountPersonUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        var _tmp_CoursePermission_nullCount = 0
        val _tmp_cpUid = _result.getLong("cpUid")
        if(_result.wasNull()) _tmp_CoursePermission_nullCount++
        val _tmp_cpLastModified = _result.getLong("cpLastModified")
        if(_result.wasNull()) _tmp_CoursePermission_nullCount++
        val _tmp_cpClazzUid = _result.getLong("cpClazzUid")
        if(_result.wasNull()) _tmp_CoursePermission_nullCount++
        val _tmp_cpToEnrolmentRole = _result.getInt("cpToEnrolmentRole")
        if(_result.wasNull()) _tmp_CoursePermission_nullCount++
        val _tmp_cpToPersonUid = _result.getLong("cpToPersonUid")
        if(_result.wasNull()) _tmp_CoursePermission_nullCount++
        val _tmp_cpToGroupUid = _result.getLong("cpToGroupUid")
        if(_result.wasNull()) _tmp_CoursePermission_nullCount++
        val _tmp_cpPermissionsFlag = _result.getLong("cpPermissionsFlag")
        if(_result.wasNull()) _tmp_CoursePermission_nullCount++
        val _tmp_cpIsDeleted = _result.getBoolean("cpIsDeleted")
        if(_result.wasNull()) _tmp_CoursePermission_nullCount++
        val _tmp_CoursePermission_isAllNull = _tmp_CoursePermission_nullCount == 8
        var _tmp_ClazzEnrolment_nullCount = 0
        val _tmp_clazzEnrolmentUid = _result.getLong("clazzEnrolmentUid")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentPersonUid = _result.getLong("clazzEnrolmentPersonUid")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentClazzUid = _result.getLong("clazzEnrolmentClazzUid")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentDateJoined = _result.getLong("clazzEnrolmentDateJoined")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentDateLeft = _result.getLong("clazzEnrolmentDateLeft")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentRole = _result.getInt("clazzEnrolmentRole")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentAttendancePercentage =
            _result.getFloat("clazzEnrolmentAttendancePercentage")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentActive = _result.getBoolean("clazzEnrolmentActive")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentLeavingReasonUid = _result.getLong("clazzEnrolmentLeavingReasonUid")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentOutcome = _result.getInt("clazzEnrolmentOutcome")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentLocalChangeSeqNum =
            _result.getLong("clazzEnrolmentLocalChangeSeqNum")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentMasterChangeSeqNum =
            _result.getLong("clazzEnrolmentMasterChangeSeqNum")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentLastChangedBy = _result.getInt("clazzEnrolmentLastChangedBy")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentLct = _result.getLong("clazzEnrolmentLct")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_ClazzEnrolment_isAllNull = _tmp_ClazzEnrolment_nullCount == 14
        CoursePermissionAndEnrolment().apply {
          if(!_tmp_CoursePermission_isAllNull) {
            this.coursePermission = CoursePermission().apply {
              this.cpUid = _tmp_cpUid
              this.cpLastModified = _tmp_cpLastModified
              this.cpClazzUid = _tmp_cpClazzUid
              this.cpToEnrolmentRole = _tmp_cpToEnrolmentRole
              this.cpToPersonUid = _tmp_cpToPersonUid
              this.cpToGroupUid = _tmp_cpToGroupUid
              this.cpPermissionsFlag = _tmp_cpPermissionsFlag
              this.cpIsDeleted = _tmp_cpIsDeleted
            }
          }
          if(!_tmp_ClazzEnrolment_isAllNull) {
            this.clazzEnrolment = 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
            }
          }
        }
      }
    }
  }

  override fun personHasPermissionWithClazzAsFlow2(
    accountPersonUid: Long,
    clazzUid: Long,
    permission: Long,
  ): Flow<Boolean> = _db.doorFlow(arrayOf("Clazz", "CoursePermission", "ClazzEnrolment")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT (? != 0 AND ? != 0)
      |          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 & 
      |         ?
      |         
      |        ) > 0)
      |              OR EXISTS(SELECT SystemPermission.spUid
      |                          FROM SystemPermission
      |                         WHERE SystemPermission.spToPersonUid = ?
      |                           AND (SystemPermission.spPermissionsFlag & 
      |     ?
      |         
      |        ) > 0)
      |               )
      |    
      |    )            
      |    
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,clazzUid)
      _stmt.setLong(2,accountPersonUid)
      _stmt.setLong(3,clazzUid)
      _stmt.setLong(4,accountPersonUid)
      _stmt.setLong(5,accountPersonUid)
      _stmt.setLong(6,clazzUid)
      _stmt.setLong(7,accountPersonUid)
      _stmt.setLong(8,permission)
      _stmt.setLong(9,accountPersonUid)
      _stmt.setLong(10,permission)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(false) {
          _result.getBoolean(1)
        }
      }
    }
  }

  override fun personHasPermissionWithClazzPairAsFlow(
    accountPersonUid: Long,
    clazzUid: Long,
    firstPermission: Long,
    secondPermission: Long,
  ): Flow<PermissionPair> = _db.doorFlow(arrayOf("Clazz", "CoursePermission", "ClazzEnrolment",
      "SystemPermission")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT (    (? != 0 AND ? != 0)
      |                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 & 
      |         ?
      |                     
      |        ) > 0)
      |              OR EXISTS(SELECT SystemPermission.spUid
      |                          FROM SystemPermission
      |                         WHERE SystemPermission.spToPersonUid = ?
      |                           AND (SystemPermission.spPermissionsFlag & 
      |     ?
      |                     
      |        ) > 0)
      |               )
      |    )
      |               ) AS firstPermission,
      |               (    (? != 0 AND ? != 0)
      |                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 & 
      |         ?
      |                     
      |        ) > 0)
      |              OR EXISTS(SELECT SystemPermission.spUid
      |                          FROM SystemPermission
      |                         WHERE SystemPermission.spToPersonUid = ?
      |                           AND (SystemPermission.spPermissionsFlag & 
      |     ?
      |                     
      |        ) > 0)
      |               )
      |    )
      |               ) AS secondPermission
      |    
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,clazzUid)
      _stmt.setLong(2,accountPersonUid)
      _stmt.setLong(3,clazzUid)
      _stmt.setLong(4,accountPersonUid)
      _stmt.setLong(5,accountPersonUid)
      _stmt.setLong(6,clazzUid)
      _stmt.setLong(7,accountPersonUid)
      _stmt.setLong(8,firstPermission)
      _stmt.setLong(9,accountPersonUid)
      _stmt.setLong(10,firstPermission)
      _stmt.setLong(11,clazzUid)
      _stmt.setLong(12,accountPersonUid)
      _stmt.setLong(13,clazzUid)
      _stmt.setLong(14,accountPersonUid)
      _stmt.setLong(15,accountPersonUid)
      _stmt.setLong(16,clazzUid)
      _stmt.setLong(17,accountPersonUid)
      _stmt.setLong(18,secondPermission)
      _stmt.setLong(19,accountPersonUid)
      _stmt.setLong(20,secondPermission)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow() {
          val _tmp_firstPermission = _result.getBoolean("firstPermission")
          val _tmp_secondPermission = _result.getBoolean("secondPermission")
          PermissionPair().apply {
            this.firstPermission = _tmp_firstPermission
            this.secondPermission = _tmp_secondPermission
          }
        }
      }
    }
  }

  override suspend fun personHasPermissionWithClazzPairAsync(
    accountPersonUid: Long,
    clazzUid: Long,
    firstPermission: Long,
    secondPermission: Long,
  ): PermissionPair = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT (    (? != 0 AND ? != 0)
    |                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 & 
    |         ?
    |                     
    |        ) > 0)
    |              OR EXISTS(SELECT SystemPermission.spUid
    |                          FROM SystemPermission
    |                         WHERE SystemPermission.spToPersonUid = ?
    |                           AND (SystemPermission.spPermissionsFlag & 
    |     ?
    |                     
    |        ) > 0)
    |               )
    |    )
    |               ) AS firstPermission,
    |               (    (? != 0 AND ? != 0)
    |                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 & 
    |         ?
    |                     
    |        ) > 0)
    |              OR EXISTS(SELECT SystemPermission.spUid
    |                          FROM SystemPermission
    |                         WHERE SystemPermission.spToPersonUid = ?
    |                           AND (SystemPermission.spPermissionsFlag & 
    |     ?
    |                     
    |        ) > 0)
    |               )
    |    )
    |               ) AS secondPermission
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,clazzUid)
    _stmt.setLong(2,accountPersonUid)
    _stmt.setLong(3,clazzUid)
    _stmt.setLong(4,accountPersonUid)
    _stmt.setLong(5,accountPersonUid)
    _stmt.setLong(6,clazzUid)
    _stmt.setLong(7,accountPersonUid)
    _stmt.setLong(8,firstPermission)
    _stmt.setLong(9,accountPersonUid)
    _stmt.setLong(10,firstPermission)
    _stmt.setLong(11,clazzUid)
    _stmt.setLong(12,accountPersonUid)
    _stmt.setLong(13,clazzUid)
    _stmt.setLong(14,accountPersonUid)
    _stmt.setLong(15,accountPersonUid)
    _stmt.setLong(16,clazzUid)
    _stmt.setLong(17,accountPersonUid)
    _stmt.setLong(18,secondPermission)
    _stmt.setLong(19,accountPersonUid)
    _stmt.setLong(20,secondPermission)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow() {
        val _tmp_firstPermission = _result.getBoolean("firstPermission")
        val _tmp_secondPermission = _result.getBoolean("secondPermission")
        PermissionPair().apply {
          this.firstPermission = _tmp_firstPermission
          this.secondPermission = _tmp_secondPermission
        }
      }
    }
  }

  override fun personHasPermissionWithClazzTripleAsFlow(
    accountPersonUid: Long,
    clazzUid: Long,
    firstPermission: Long,
    secondPermission: Long,
    thirdPermission: Long,
  ): Flow<PermissionTriple> = _db.doorFlow(arrayOf("Clazz", "CoursePermission", "ClazzEnrolment",
      "SystemPermission")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT (    (? != 0 AND ? != 0)
      |                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 & 
      |         ?
      |                     
      |        ) > 0)
      |              OR EXISTS(SELECT SystemPermission.spUid
      |                          FROM SystemPermission
      |                         WHERE SystemPermission.spToPersonUid = ?
      |                           AND (SystemPermission.spPermissionsFlag & 
      |     ?
      |                     
      |        ) > 0)
      |               )
      |    )
      |               ) AS firstPermission,
      |               (    (? != 0 AND ? != 0)
      |                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 & 
      |         ?
      |                     
      |        ) > 0)
      |              OR EXISTS(SELECT SystemPermission.spUid
      |                          FROM SystemPermission
      |                         WHERE SystemPermission.spToPersonUid = ?
      |                           AND (SystemPermission.spPermissionsFlag & 
      |     ?
      |                     
      |        ) > 0)
      |               )
      |    )
      |               ) AS secondPermission,
      |               (    (? != 0 AND ? != 0)
      |                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 & 
      |         ?
      |                     
      |        ) > 0)
      |              OR EXISTS(SELECT SystemPermission.spUid
      |                          FROM SystemPermission
      |                         WHERE SystemPermission.spToPersonUid = ?
      |                           AND (SystemPermission.spPermissionsFlag & 
      |     ?
      |                     
      |        ) > 0)
      |               )
      |    )
      |               ) AS thirdPermission
      |    
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,clazzUid)
      _stmt.setLong(2,accountPersonUid)
      _stmt.setLong(3,clazzUid)
      _stmt.setLong(4,accountPersonUid)
      _stmt.setLong(5,accountPersonUid)
      _stmt.setLong(6,clazzUid)
      _stmt.setLong(7,accountPersonUid)
      _stmt.setLong(8,firstPermission)
      _stmt.setLong(9,accountPersonUid)
      _stmt.setLong(10,firstPermission)
      _stmt.setLong(11,clazzUid)
      _stmt.setLong(12,accountPersonUid)
      _stmt.setLong(13,clazzUid)
      _stmt.setLong(14,accountPersonUid)
      _stmt.setLong(15,accountPersonUid)
      _stmt.setLong(16,clazzUid)
      _stmt.setLong(17,accountPersonUid)
      _stmt.setLong(18,secondPermission)
      _stmt.setLong(19,accountPersonUid)
      _stmt.setLong(20,secondPermission)
      _stmt.setLong(21,clazzUid)
      _stmt.setLong(22,accountPersonUid)
      _stmt.setLong(23,clazzUid)
      _stmt.setLong(24,accountPersonUid)
      _stmt.setLong(25,accountPersonUid)
      _stmt.setLong(26,clazzUid)
      _stmt.setLong(27,accountPersonUid)
      _stmt.setLong(28,thirdPermission)
      _stmt.setLong(29,accountPersonUid)
      _stmt.setLong(30,thirdPermission)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow() {
          val _tmp_firstPermission = _result.getBoolean("firstPermission")
          val _tmp_secondPermission = _result.getBoolean("secondPermission")
          val _tmp_thirdPermission = _result.getBoolean("thirdPermission")
          PermissionTriple().apply {
            this.firstPermission = _tmp_firstPermission
            this.secondPermission = _tmp_secondPermission
            this.thirdPermission = _tmp_thirdPermission
          }
        }
      }
    }
  }

  override suspend fun personHasPermissionWithClazzAsync2(
    accountPersonUid: Long,
    clazzUid: Long,
    permission: Long,
  ): Boolean = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT (? != 0 AND ? != 0)
    |          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 & 
    |         ?
    |         
    |        ) > 0)
    |              OR EXISTS(SELECT SystemPermission.spUid
    |                          FROM SystemPermission
    |                         WHERE SystemPermission.spToPersonUid = ?
    |                           AND (SystemPermission.spPermissionsFlag & 
    |     ?
    |         
    |        ) > 0)
    |               )
    |    
    |    )            
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,clazzUid)
    _stmt.setLong(2,accountPersonUid)
    _stmt.setLong(3,clazzUid)
    _stmt.setLong(4,accountPersonUid)
    _stmt.setLong(5,accountPersonUid)
    _stmt.setLong(6,clazzUid)
    _stmt.setLong(7,accountPersonUid)
    _stmt.setLong(8,permission)
    _stmt.setLong(9,accountPersonUid)
    _stmt.setLong(10,permission)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(false) {
        _result.getBoolean(1)
      }
    }
  }

  override suspend
      fun findApplicableCoursePermissionEntitiesForAccountPerson(accountPersonUid: Long):
      List<CoursePermission> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        /* Get CoursePermissions given to the active user based on their enrolment role*/
    |        SELECT CoursePermission.*
    |          FROM ClazzEnrolment ClazzEnrolment_ActiveUser
    |               JOIN CoursePermission 
    |                    ON CoursePermission.cpClazzUid = ClazzEnrolment_ActiveUser.clazzEnrolmentClazzUid
    |                   AND CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ActiveUser.clazzEnrolmentRole
    |         WHERE ClazzEnrolment_ActiveUser.clazzEnrolmentPersonUid = ? 
    |         UNION
    |        /* Get ClazzUids where the active user can view members based a grant directly to them */
    |        SELECT CoursePermission.*
    |          FROM CoursePermission
    |         WHERE CoursePermission.cpToPersonUid  = ?
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,accountPersonUid)
    _stmt.setLong(2,accountPersonUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_cpUid = _result.getLong("cpUid")
        val _tmp_cpLastModified = _result.getLong("cpLastModified")
        val _tmp_cpClazzUid = _result.getLong("cpClazzUid")
        val _tmp_cpToEnrolmentRole = _result.getInt("cpToEnrolmentRole")
        val _tmp_cpToPersonUid = _result.getLong("cpToPersonUid")
        val _tmp_cpToGroupUid = _result.getLong("cpToGroupUid")
        val _tmp_cpPermissionsFlag = _result.getLong("cpPermissionsFlag")
        val _tmp_cpIsDeleted = _result.getBoolean("cpIsDeleted")
        CoursePermission().apply {
          this.cpUid = _tmp_cpUid
          this.cpLastModified = _tmp_cpLastModified
          this.cpClazzUid = _tmp_cpClazzUid
          this.cpToEnrolmentRole = _tmp_cpToEnrolmentRole
          this.cpToPersonUid = _tmp_cpToPersonUid
          this.cpToGroupUid = _tmp_cpToGroupUid
          this.cpPermissionsFlag = _tmp_cpPermissionsFlag
          this.cpIsDeleted = _tmp_cpIsDeleted
        }
      }
    }
  }

  override fun userPermissionsForAssignmentSubmitterUid(
    accountPersonUid: Long,
    assignmentUid: Long,
    clazzUid: Long,
    submitterUid: Long,
  ): Flow<AssignmentPermissionAndActiveUserSubmitterUid> = _db.doorFlow(arrayOf("ClazzAssignment",
      "ClazzEnrolment", "CourseGroupMember", "PeerReviewerAllocation", "CoursePermission",
      "SystemPermission")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        WITH 
      |        AccountSubmitterUid(accountSubmitterUid) AS 
      |        (
      |        SELECT CASE
      |                    -- When assignment is individual then the submitter uid is the personuid if they are enrolled in the course otherwise zero 
      |                    WHEN (SELECT caGroupUid
      |                            FROM ClazzAssignment
      |                           WHERE caUid = ?) = 0
      |                         THEN (
      |        SELECT COALESCE(
      |                (SELECT ClazzEnrolment.clazzEnrolmentPersonUid
      |                   FROM ClazzEnrolment
      |                  WHERE ClazzEnrolment.clazzEnrolmentPersonUid = ?
      |                    AND ClazzEnrolment.clazzEnrolmentRole = 1000
      |                    AND ClazzEnrolment.clazzEnrolmentClazzUid = 
      |                        (SELECT ClazzAssignment.caClazzUid
      |                           FROM ClazzAssignment
      |                          WHERE ClazzAssignment.caUid = ?)
      |                  LIMIT 1), 0)
      |    )
      |                    -- When assignment is by groups but the active user is not an enrolled student then the submitter uid is zero     
      |                    WHEN (SELECT caGroupUid
      |                            FROM ClazzAssignment
      |                           WHERE caUid = ?) != 0
      |                          AND (
      |        SELECT COALESCE(
      |                (SELECT ClazzEnrolment.clazzEnrolmentPersonUid
      |                   FROM ClazzEnrolment
      |                  WHERE ClazzEnrolment.clazzEnrolmentPersonUid = ?
      |                    AND ClazzEnrolment.clazzEnrolmentRole = 1000
      |                    AND ClazzEnrolment.clazzEnrolmentClazzUid = 
      |                        (SELECT ClazzAssignment.caClazzUid
      |                           FROM ClazzAssignment
      |                          WHERE ClazzAssignment.caUid = ?)
      |                  LIMIT 1), 0)
      |    ) = 0
      |                          THEN 0
      |                    -- When assignment is by groups and the person is an enrolled student the submitter uid is the 
      |                    -- group that they are assigned to. If they are not assigned to a group but are enrolled
      |                    -- then we submitter uid = SUBMITTER_ENROLLED_BUT_NOT_IN_GROUP
      |                    ELSE COALESCE(
      |                          (SELECT CourseGroupMember.cgmGroupNumber
      |                             FROM CourseGroupMember
      |                            WHERE (
      |        SELECT COALESCE(
      |                (SELECT ClazzEnrolment.clazzEnrolmentPersonUid
      |                   FROM ClazzEnrolment
      |                  WHERE ClazzEnrolment.clazzEnrolmentPersonUid = ?
      |                    AND ClazzEnrolment.clazzEnrolmentRole = 1000
      |                    AND ClazzEnrolment.clazzEnrolmentClazzUid = 
      |                        (SELECT ClazzAssignment.caClazzUid
      |                           FROM ClazzAssignment
      |                          WHERE ClazzAssignment.caUid = ?)
      |                  LIMIT 1), 0)
      |    ) > 0
      |                              AND CourseGroupMember.cgmSetUid = 
      |                                  (SELECT caGroupUid
      |                                     FROM ClazzAssignment
      |                                    WHERE caUid = ?)
      |                              AND CourseGroupMember.cgmPersonUid = ?
      |                            LIMIT 1), -1)
      |                    END
      |    )
      |    ,
      |             CanMarkSubmitter(canMark) AS
      |             (SELECT CASE (SELECT ClazzAssignment.caMarkingType
      |                             FROM ClazzAssignment
      |                            WHERE ClazzAssignment.caUid = ?)
      |                           WHEN 2 THEN 
      |                                EXISTS(SELECT 1
      |                                         FROM PeerReviewerAllocation
      |                                        WHERE PeerReviewerAllocation.praToMarkerSubmitterUid = ?
      |                                          AND PeerReviewerAllocation.praMarkerSubmitterUid =
      |                                              (SELECT AccountSubmitterUid.accountSubmitterUid
      |                                                 FROM AccountSubmitterUid))
      |                           ELSE ((
      |             /* 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 & 
      |         256
      |                                 
      |        ) > 0)
      |              OR EXISTS(SELECT SystemPermission.spUid
      |                          FROM SystemPermission
      |                         WHERE SystemPermission.spToPersonUid = ?
      |                           AND (SystemPermission.spPermissionsFlag & 
      |     256
      |                                 
      |        ) > 0)
      |               )
      |    )
      |                          END)
      |
      |             
      |                    /* Can edit */
      |             SELECT (     (? != 0 AND ? != 0 AND ? != 0 AND ? != 0)
      |                      AND (SELECT CanMarkSubmitter.canMark 
      |                            FROM CanMarkSubmitter)) AS canMark,
      |                    /* can view */   
      |                    (     (? != 0 AND ? != 0 AND ? != 0 AND ? != 0)
      |                      AND (     (SELECT CanMarkSubmitter.canMark
      |                                   FROM CanMarkSubmitter)
      |                             OR (SELECT ? = 
      |                                  (SELECT AccountSubmitterUid.accountSubmitterUid
      |                                     FROM AccountSubmitterUid))
      |                             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 & 
      |         128
      |                                 
      |        ) > 0)
      |              OR EXISTS(SELECT SystemPermission.spUid
      |                          FROM SystemPermission
      |                         WHERE SystemPermission.spToPersonUid = ?
      |                           AND (SystemPermission.spPermissionsFlag & 
      |     128
      |                                 
      |        ) > 0)
      |               )
      |    ))               
      |                    ) AS canView,
      |                    ((
      |             /* 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 & 
      |         4
      |                     
      |        ) > 0)
      |              OR EXISTS(SELECT SystemPermission.spUid
      |                          FROM SystemPermission
      |                         WHERE SystemPermission.spToPersonUid = ?
      |                           AND (SystemPermission.spPermissionsFlag & 
      |     4
      |                     
      |        ) > 0)
      |               )
      |    
      |                    ) AS canModerate,
      |                    (SELECT accountSubmitterUid
      |                       FROM AccountSubmitterUid) AS activeUserSubmitterUid
      |             
      |    
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,assignmentUid)
      _stmt.setLong(2,accountPersonUid)
      _stmt.setLong(3,assignmentUid)
      _stmt.setLong(4,assignmentUid)
      _stmt.setLong(5,accountPersonUid)
      _stmt.setLong(6,assignmentUid)
      _stmt.setLong(7,accountPersonUid)
      _stmt.setLong(8,assignmentUid)
      _stmt.setLong(9,assignmentUid)
      _stmt.setLong(10,accountPersonUid)
      _stmt.setLong(11,assignmentUid)
      _stmt.setLong(12,submitterUid)
      _stmt.setLong(13,clazzUid)
      _stmt.setLong(14,accountPersonUid)
      _stmt.setLong(15,accountPersonUid)
      _stmt.setLong(16,clazzUid)
      _stmt.setLong(17,accountPersonUid)
      _stmt.setLong(18,accountPersonUid)
      _stmt.setLong(19,accountPersonUid)
      _stmt.setLong(20,assignmentUid)
      _stmt.setLong(21,clazzUid)
      _stmt.setLong(22,submitterUid)
      _stmt.setLong(23,accountPersonUid)
      _stmt.setLong(24,assignmentUid)
      _stmt.setLong(25,clazzUid)
      _stmt.setLong(26,submitterUid)
      _stmt.setLong(27,submitterUid)
      _stmt.setLong(28,clazzUid)
      _stmt.setLong(29,accountPersonUid)
      _stmt.setLong(30,accountPersonUid)
      _stmt.setLong(31,clazzUid)
      _stmt.setLong(32,accountPersonUid)
      _stmt.setLong(33,accountPersonUid)
      _stmt.setLong(34,clazzUid)
      _stmt.setLong(35,accountPersonUid)
      _stmt.setLong(36,accountPersonUid)
      _stmt.setLong(37,clazzUid)
      _stmt.setLong(38,accountPersonUid)
      _stmt.setLong(39,accountPersonUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow() {
          val _tmp_canMark = _result.getBoolean("canMark")
          val _tmp_canView = _result.getBoolean("canView")
          val _tmp_canModerate = _result.getBoolean("canModerate")
          val _tmp_activeUserSubmitterUid = _result.getLong("activeUserSubmitterUid")
          AssignmentPermissionAndActiveUserSubmitterUid().apply {
            this.canMark = _tmp_canMark
            this.canView = _tmp_canView
            this.canModerate = _tmp_canModerate
            this.activeUserSubmitterUid = _tmp_activeUserSubmitterUid
          }
        }
      }
    }
  }
}
