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.createArrayOrProxyArrayOf
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.getFloatNullable
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.AssignmentSubmitterUidAndName
import com.ustadmobile.lib.db.composites.ClazzAssignmentAndBlock
import com.ustadmobile.lib.db.entities.AssignmentProgressSummary
import com.ustadmobile.lib.db.entities.AssignmentSubmitterSummary
import com.ustadmobile.lib.db.entities.ClazzAssignment
import com.ustadmobile.lib.db.entities.ClazzAssignmentCourseBlockAndSubmitterUid
import com.ustadmobile.lib.db.entities.ClazzEnrolment
import com.ustadmobile.lib.db.entities.CourseAssignmentMark
import com.ustadmobile.lib.db.entities.CourseAssignmentSubmission
import com.ustadmobile.lib.db.entities.CourseBlock
import com.ustadmobile.lib.db.entities.CourseBlockPicture
import com.ustadmobile.lib.db.entities.CourseGroupMember
import com.ustadmobile.lib.db.entities.CourseGroupSet
import com.ustadmobile.lib.db.entities.PeerReviewerAllocation
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 ClazzAssignmentDao_JdbcImpl(
  public val _db: RoomDatabase,
) : ClazzAssignmentDao() {
  public val _insertAdapterClazzAssignment_upsert: EntityInsertionAdapter<ClazzAssignment> = object
      : EntityInsertionAdapter<ClazzAssignment>(_db) {
    override fun makeSql(returnsId: Boolean): String = when(dbType) {
      DoorDbType.SQLITE -> {
        "INSERT OR REPLACE INTO ClazzAssignment (caUid, caTitle, caDescription, caGroupUid, caActive, caClassCommentEnabled, caPrivateCommentsEnabled, caCompletionCriteria, caRequireFileSubmission, caFileType, caSizeLimit, caNumberOfFiles, caSubmissionPolicy, caMarkingType, caRequireTextSubmission, caTextLimitType, caTextLimit, caXObjectUid, caClazzUid, caPeerReviewerCount, caLocalChangeSeqNum, caMasterChangeSeqNum, caLastChangedBy, caLct) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
      }
      DoorDbType.POSTGRES ->  {
        "INSERT INTO ClazzAssignment (caUid, caTitle, caDescription, caGroupUid, caActive, caClassCommentEnabled, caPrivateCommentsEnabled, caCompletionCriteria, caRequireFileSubmission, caFileType, caSizeLimit, caNumberOfFiles, caSubmissionPolicy, caMarkingType, caRequireTextSubmission, caTextLimitType, caTextLimit, caXObjectUid, caClazzUid, caPeerReviewerCount, caLocalChangeSeqNum, caMasterChangeSeqNum, caLastChangedBy, caLct) VALUES(COALESCE(?,nextval('ClazzAssignment_caUid_seq')), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT (caUid) DO UPDATE SET caTitle = excluded.caTitle,caDescription = excluded.caDescription,caGroupUid = excluded.caGroupUid,caActive = excluded.caActive,caClassCommentEnabled = excluded.caClassCommentEnabled,caPrivateCommentsEnabled = excluded.caPrivateCommentsEnabled,caCompletionCriteria = excluded.caCompletionCriteria,caRequireFileSubmission = excluded.caRequireFileSubmission,caFileType = excluded.caFileType,caSizeLimit = excluded.caSizeLimit,caNumberOfFiles = excluded.caNumberOfFiles,caSubmissionPolicy = excluded.caSubmissionPolicy,caMarkingType = excluded.caMarkingType,caRequireTextSubmission = excluded.caRequireTextSubmission,caTextLimitType = excluded.caTextLimitType,caTextLimit = excluded.caTextLimit,caXObjectUid = excluded.caXObjectUid,caClazzUid = excluded.caClazzUid,caPeerReviewerCount = excluded.caPeerReviewerCount,caLocalChangeSeqNum = excluded.caLocalChangeSeqNum,caMasterChangeSeqNum = excluded.caMasterChangeSeqNum,caLastChangedBy = excluded.caLastChangedBy,caLct = excluded.caLct" + if(returnsId) { " RETURNING caUid" } else "" 
      }
      else -> {
        throw IllegalArgumentException("Unsupported db type")
      }
    }

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement, entity: ClazzAssignment) {
      if(entity.caUid == 0L) {
        stmt.setObject(1, null)
      } else {
        stmt.setLong(1, entity.caUid)
      }
      stmt.setString(2, entity.caTitle)
      stmt.setString(3, entity.caDescription)
      stmt.setLong(4, entity.caGroupUid)
      stmt.setBoolean(5, entity.caActive)
      stmt.setBoolean(6, entity.caClassCommentEnabled)
      stmt.setBoolean(7, entity.caPrivateCommentsEnabled)
      stmt.setInt(8, entity.caCompletionCriteria)
      stmt.setBoolean(9, entity.caRequireFileSubmission)
      stmt.setInt(10, entity.caFileType)
      stmt.setInt(11, entity.caSizeLimit)
      stmt.setInt(12, entity.caNumberOfFiles)
      stmt.setInt(13, entity.caSubmissionPolicy)
      stmt.setInt(14, entity.caMarkingType)
      stmt.setBoolean(15, entity.caRequireTextSubmission)
      stmt.setInt(16, entity.caTextLimitType)
      stmt.setInt(17, entity.caTextLimit)
      stmt.setLong(18, entity.caXObjectUid)
      stmt.setLong(19, entity.caClazzUid)
      stmt.setInt(20, entity.caPeerReviewerCount)
      stmt.setLong(21, entity.caLocalChangeSeqNum)
      stmt.setLong(22, entity.caMasterChangeSeqNum)
      stmt.setInt(23, entity.caLastChangedBy)
      stmt.setLong(24, entity.caLct)
    }
  }

  public val _insertAdapterClazzAssignment_abort: EntityInsertionAdapter<ClazzAssignment> = object :
      EntityInsertionAdapter<ClazzAssignment>(_db) {
    override fun makeSql(returnsId: Boolean): String = when(dbType) {
      DoorDbType.SQLITE -> {
        "INSERT INTO ClazzAssignment (caUid, caTitle, caDescription, caGroupUid, caActive, caClassCommentEnabled, caPrivateCommentsEnabled, caCompletionCriteria, caRequireFileSubmission, caFileType, caSizeLimit, caNumberOfFiles, caSubmissionPolicy, caMarkingType, caRequireTextSubmission, caTextLimitType, caTextLimit, caXObjectUid, caClazzUid, caPeerReviewerCount, caLocalChangeSeqNum, caMasterChangeSeqNum, caLastChangedBy, caLct) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
      }
      DoorDbType.POSTGRES ->  {
        "INSERT INTO ClazzAssignment (caUid, caTitle, caDescription, caGroupUid, caActive, caClassCommentEnabled, caPrivateCommentsEnabled, caCompletionCriteria, caRequireFileSubmission, caFileType, caSizeLimit, caNumberOfFiles, caSubmissionPolicy, caMarkingType, caRequireTextSubmission, caTextLimitType, caTextLimit, caXObjectUid, caClazzUid, caPeerReviewerCount, caLocalChangeSeqNum, caMasterChangeSeqNum, caLastChangedBy, caLct) VALUES(COALESCE(?,nextval('ClazzAssignment_caUid_seq')), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" + if(returnsId) { " RETURNING caUid" } else "" 
      }
      else -> {
        throw IllegalArgumentException("Unsupported db type")
      }
    }

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement, entity: ClazzAssignment) {
      if(entity.caUid == 0L) {
        stmt.setObject(1, null)
      } else {
        stmt.setLong(1, entity.caUid)
      }
      stmt.setString(2, entity.caTitle)
      stmt.setString(3, entity.caDescription)
      stmt.setLong(4, entity.caGroupUid)
      stmt.setBoolean(5, entity.caActive)
      stmt.setBoolean(6, entity.caClassCommentEnabled)
      stmt.setBoolean(7, entity.caPrivateCommentsEnabled)
      stmt.setInt(8, entity.caCompletionCriteria)
      stmt.setBoolean(9, entity.caRequireFileSubmission)
      stmt.setInt(10, entity.caFileType)
      stmt.setInt(11, entity.caSizeLimit)
      stmt.setInt(12, entity.caNumberOfFiles)
      stmt.setInt(13, entity.caSubmissionPolicy)
      stmt.setInt(14, entity.caMarkingType)
      stmt.setBoolean(15, entity.caRequireTextSubmission)
      stmt.setInt(16, entity.caTextLimitType)
      stmt.setInt(17, entity.caTextLimit)
      stmt.setLong(18, entity.caXObjectUid)
      stmt.setLong(19, entity.caClazzUid)
      stmt.setInt(20, entity.caPeerReviewerCount)
      stmt.setLong(21, entity.caLocalChangeSeqNum)
      stmt.setLong(22, entity.caMasterChangeSeqNum)
      stmt.setInt(23, entity.caLastChangedBy)
      stmt.setLong(24, entity.caLct)
    }
  }

  override suspend fun upsertListAsync(entities: List<ClazzAssignment>) {
    _insertAdapterClazzAssignment_upsert.insertListAsync(entities)
  }

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

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

  public override fun insertList(entityList: List<ClazzAssignment>) {
    _insertAdapterClazzAssignment_abort.insertList(entityList)
  }

  public override suspend fun insertListAsync(entityList: List<ClazzAssignment>) {
    _insertAdapterClazzAssignment_abort.insertListAsync(entityList)
  }

  override suspend fun updateAsync(clazzAssignment: ClazzAssignment) {
    val _sql =
        "UPDATE ClazzAssignment SET caTitle = ?, caDescription = ?, caGroupUid = ?, caActive = ?, caClassCommentEnabled = ?, caPrivateCommentsEnabled = ?, caCompletionCriteria = ?, caRequireFileSubmission = ?, caFileType = ?, caSizeLimit = ?, caNumberOfFiles = ?, caSubmissionPolicy = ?, caMarkingType = ?, caRequireTextSubmission = ?, caTextLimitType = ?, caTextLimit = ?, caXObjectUid = ?, caClazzUid = ?, caPeerReviewerCount = ?, caLocalChangeSeqNum = ?, caMasterChangeSeqNum = ?, caLastChangedBy = ?, caLct = ? WHERE caUid = ?"
    _db.prepareAndUseStatementAsync(_sql) {
       _stmt ->
      _stmt.setString(1, clazzAssignment.caTitle)
      _stmt.setString(2, clazzAssignment.caDescription)
      _stmt.setLong(3, clazzAssignment.caGroupUid)
      _stmt.setBoolean(4, clazzAssignment.caActive)
      _stmt.setBoolean(5, clazzAssignment.caClassCommentEnabled)
      _stmt.setBoolean(6, clazzAssignment.caPrivateCommentsEnabled)
      _stmt.setInt(7, clazzAssignment.caCompletionCriteria)
      _stmt.setBoolean(8, clazzAssignment.caRequireFileSubmission)
      _stmt.setInt(9, clazzAssignment.caFileType)
      _stmt.setInt(10, clazzAssignment.caSizeLimit)
      _stmt.setInt(11, clazzAssignment.caNumberOfFiles)
      _stmt.setInt(12, clazzAssignment.caSubmissionPolicy)
      _stmt.setInt(13, clazzAssignment.caMarkingType)
      _stmt.setBoolean(14, clazzAssignment.caRequireTextSubmission)
      _stmt.setInt(15, clazzAssignment.caTextLimitType)
      _stmt.setInt(16, clazzAssignment.caTextLimit)
      _stmt.setLong(17, clazzAssignment.caXObjectUid)
      _stmt.setLong(18, clazzAssignment.caClazzUid)
      _stmt.setInt(19, clazzAssignment.caPeerReviewerCount)
      _stmt.setLong(20, clazzAssignment.caLocalChangeSeqNum)
      _stmt.setLong(21, clazzAssignment.caMasterChangeSeqNum)
      _stmt.setInt(22, clazzAssignment.caLastChangedBy)
      _stmt.setLong(23, clazzAssignment.caLct)
      _stmt.setLong(24, clazzAssignment.caUid)
      _stmt.executeUpdateAsyncKmp()
    }
  }

  public override fun update(entity: ClazzAssignment) {
    val _sql =
        "UPDATE ClazzAssignment SET caTitle = ?, caDescription = ?, caGroupUid = ?, caActive = ?, caClassCommentEnabled = ?, caPrivateCommentsEnabled = ?, caCompletionCriteria = ?, caRequireFileSubmission = ?, caFileType = ?, caSizeLimit = ?, caNumberOfFiles = ?, caSubmissionPolicy = ?, caMarkingType = ?, caRequireTextSubmission = ?, caTextLimitType = ?, caTextLimit = ?, caXObjectUid = ?, caClazzUid = ?, caPeerReviewerCount = ?, caLocalChangeSeqNum = ?, caMasterChangeSeqNum = ?, caLastChangedBy = ?, caLct = ? WHERE caUid = ?"
    _db.prepareAndUseStatement(_sql) {
       _stmt ->
      _stmt.setString(1, entity.caTitle)
      _stmt.setString(2, entity.caDescription)
      _stmt.setLong(3, entity.caGroupUid)
      _stmt.setBoolean(4, entity.caActive)
      _stmt.setBoolean(5, entity.caClassCommentEnabled)
      _stmt.setBoolean(6, entity.caPrivateCommentsEnabled)
      _stmt.setInt(7, entity.caCompletionCriteria)
      _stmt.setBoolean(8, entity.caRequireFileSubmission)
      _stmt.setInt(9, entity.caFileType)
      _stmt.setInt(10, entity.caSizeLimit)
      _stmt.setInt(11, entity.caNumberOfFiles)
      _stmt.setInt(12, entity.caSubmissionPolicy)
      _stmt.setInt(13, entity.caMarkingType)
      _stmt.setBoolean(14, entity.caRequireTextSubmission)
      _stmt.setInt(15, entity.caTextLimitType)
      _stmt.setInt(16, entity.caTextLimit)
      _stmt.setLong(17, entity.caXObjectUid)
      _stmt.setLong(18, entity.caClazzUid)
      _stmt.setInt(19, entity.caPeerReviewerCount)
      _stmt.setLong(20, entity.caLocalChangeSeqNum)
      _stmt.setLong(21, entity.caMasterChangeSeqNum)
      _stmt.setInt(22, entity.caLastChangedBy)
      _stmt.setLong(23, entity.caLct)
      _stmt.setLong(24, entity.caUid)
      _stmt.executeUpdate()
    }
  }

  public override suspend fun updateListAsync(entityList: List<ClazzAssignment>) {
    val _sql =
        "UPDATE ClazzAssignment SET caTitle = ?, caDescription = ?, caGroupUid = ?, caActive = ?, caClassCommentEnabled = ?, caPrivateCommentsEnabled = ?, caCompletionCriteria = ?, caRequireFileSubmission = ?, caFileType = ?, caSizeLimit = ?, caNumberOfFiles = ?, caSubmissionPolicy = ?, caMarkingType = ?, caRequireTextSubmission = ?, caTextLimitType = ?, caTextLimit = ?, caXObjectUid = ?, caClazzUid = ?, caPeerReviewerCount = ?, caLocalChangeSeqNum = ?, caMasterChangeSeqNum = ?, caLastChangedBy = ?, caLct = ? WHERE caUid = ?"
    _db.prepareAndUseStatementAsync(_sql) {
       _stmt ->
      _stmt.getConnection().setAutoCommit(false)
      for(_entity in entityList) {
        _stmt.setString(1, _entity.caTitle)
        _stmt.setString(2, _entity.caDescription)
        _stmt.setLong(3, _entity.caGroupUid)
        _stmt.setBoolean(4, _entity.caActive)
        _stmt.setBoolean(5, _entity.caClassCommentEnabled)
        _stmt.setBoolean(6, _entity.caPrivateCommentsEnabled)
        _stmt.setInt(7, _entity.caCompletionCriteria)
        _stmt.setBoolean(8, _entity.caRequireFileSubmission)
        _stmt.setInt(9, _entity.caFileType)
        _stmt.setInt(10, _entity.caSizeLimit)
        _stmt.setInt(11, _entity.caNumberOfFiles)
        _stmt.setInt(12, _entity.caSubmissionPolicy)
        _stmt.setInt(13, _entity.caMarkingType)
        _stmt.setBoolean(14, _entity.caRequireTextSubmission)
        _stmt.setInt(15, _entity.caTextLimitType)
        _stmt.setInt(16, _entity.caTextLimit)
        _stmt.setLong(17, _entity.caXObjectUid)
        _stmt.setLong(18, _entity.caClazzUid)
        _stmt.setInt(19, _entity.caPeerReviewerCount)
        _stmt.setLong(20, _entity.caLocalChangeSeqNum)
        _stmt.setLong(21, _entity.caMasterChangeSeqNum)
        _stmt.setInt(22, _entity.caLastChangedBy)
        _stmt.setLong(23, _entity.caLct)
        _stmt.setLong(24, _entity.caUid)
        _stmt.executeUpdateAsyncKmp()
      }
      _stmt.getConnection().commit()
    }
  }

  override suspend fun getAllAssignmentsByClazzUidAsync(clazzUid: Long): List<ClazzAssignment> =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT * 
    |          FROM ClazzAssignment
    |         WHERE ClazzAssignment.caClazzUid = ?
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,clazzUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_caUid = _result.getLong("caUid")
        val _tmp_caTitle = _result.getString("caTitle")
        val _tmp_caDescription = _result.getString("caDescription")
        val _tmp_caGroupUid = _result.getLong("caGroupUid")
        val _tmp_caActive = _result.getBoolean("caActive")
        val _tmp_caClassCommentEnabled = _result.getBoolean("caClassCommentEnabled")
        val _tmp_caPrivateCommentsEnabled = _result.getBoolean("caPrivateCommentsEnabled")
        val _tmp_caCompletionCriteria = _result.getInt("caCompletionCriteria")
        val _tmp_caRequireFileSubmission = _result.getBoolean("caRequireFileSubmission")
        val _tmp_caFileType = _result.getInt("caFileType")
        val _tmp_caSizeLimit = _result.getInt("caSizeLimit")
        val _tmp_caNumberOfFiles = _result.getInt("caNumberOfFiles")
        val _tmp_caSubmissionPolicy = _result.getInt("caSubmissionPolicy")
        val _tmp_caMarkingType = _result.getInt("caMarkingType")
        val _tmp_caRequireTextSubmission = _result.getBoolean("caRequireTextSubmission")
        val _tmp_caTextLimitType = _result.getInt("caTextLimitType")
        val _tmp_caTextLimit = _result.getInt("caTextLimit")
        val _tmp_caXObjectUid = _result.getLong("caXObjectUid")
        val _tmp_caClazzUid = _result.getLong("caClazzUid")
        val _tmp_caPeerReviewerCount = _result.getInt("caPeerReviewerCount")
        val _tmp_caLocalChangeSeqNum = _result.getLong("caLocalChangeSeqNum")
        val _tmp_caMasterChangeSeqNum = _result.getLong("caMasterChangeSeqNum")
        val _tmp_caLastChangedBy = _result.getInt("caLastChangedBy")
        val _tmp_caLct = _result.getLong("caLct")
        ClazzAssignment().apply {
          this.caUid = _tmp_caUid
          this.caTitle = _tmp_caTitle
          this.caDescription = _tmp_caDescription
          this.caGroupUid = _tmp_caGroupUid
          this.caActive = _tmp_caActive
          this.caClassCommentEnabled = _tmp_caClassCommentEnabled
          this.caPrivateCommentsEnabled = _tmp_caPrivateCommentsEnabled
          this.caCompletionCriteria = _tmp_caCompletionCriteria
          this.caRequireFileSubmission = _tmp_caRequireFileSubmission
          this.caFileType = _tmp_caFileType
          this.caSizeLimit = _tmp_caSizeLimit
          this.caNumberOfFiles = _tmp_caNumberOfFiles
          this.caSubmissionPolicy = _tmp_caSubmissionPolicy
          this.caMarkingType = _tmp_caMarkingType
          this.caRequireTextSubmission = _tmp_caRequireTextSubmission
          this.caTextLimitType = _tmp_caTextLimitType
          this.caTextLimit = _tmp_caTextLimit
          this.caXObjectUid = _tmp_caXObjectUid
          this.caClazzUid = _tmp_caClazzUid
          this.caPeerReviewerCount = _tmp_caPeerReviewerCount
          this.caLocalChangeSeqNum = _tmp_caLocalChangeSeqNum
          this.caMasterChangeSeqNum = _tmp_caMasterChangeSeqNum
          this.caLastChangedBy = _tmp_caLastChangedBy
          this.caLct = _tmp_caLct
        }
      }
    }
  }

  override suspend fun updateActiveByUid(
    cbUid: Long,
    active: Boolean,
    changeTime: Long,
  ) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        UPDATE ClazzAssignment 
      |           SET caActive = ?, 
      |               caLct = ?
      |         WHERE caUid = ?
      """.trimMargin(),
      readOnly = false,)
    ) { _stmt -> 
      _stmt.setBoolean(1,active)
      _stmt.setLong(2,changeTime)
      _stmt.setLong(3,cbUid)
      _stmt.executeUpdateAsyncKmp()
    }
  }

  override fun getProgressSummaryForAssignment(
    assignmentUid: Long,
    clazzUid: Long,
    accountPersonUid: Long,
    group: String,
  ): Flow<AssignmentProgressSummary?> = _db.doorFlow(arrayOf("SystemPermission", "CoursePermission",
      "ClazzAssignment", "ClazzEnrolment", "PeerReviewerAllocation", "Person", "CourseGroupMember",
      "CourseAssignmentSubmission", "CourseAssignmentMark", "Comments", "PersonPicture")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        WITH 
      |            HasLearningRecordSelectPermission (hasPermission) AS
      |            (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 = ?), 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)
      |               )
      |    ))
      |        ,
      |        
      |        AssignmentClazzUid(clazzUid) AS
      |        (SELECT ClazzAssignment.caClazzUid
      |           FROM ClazzAssignment
      |          WHERE ClazzAssignment.caUid = ?)  
      |    ,
      |        
      |        SubmitterList(submitterId, name) AS 
      |        -- List of submitter uids and names if individual assignment eg caGroupUid = 0
      |        (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid AS submitterId, 
      |                Person.firstNames || ' ' || Person.lastName AS name
      |           FROM ClazzEnrolment
      |                JOIN Person 
      |                     ON Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid
      |          WHERE (
      |        SELECT ClazzAssignment.caGroupUid
      |                   FROM ClazzAssignment
      |                  WHERE ClazzAssignment.caUid = ?
      |    ) = 0
      |            AND ClazzEnrolment.clazzEnrolmentClazzUid = ?
      |            AND ClazzEnrolment.clazzEnrolmentRole = 1000
      |            -- either the active user has learnign record select permission on class or is an assigned reviewer for submitter
      |            AND (
      |                (SELECT hasPermission 
      |                   FROM HasLearningRecordSelectPermission)
      |                OR  
      |                 -- check if the active person eg accountpersonuid is assigned to mark this peer
      |                 (
      |        ((SELECT ClazzAssignment.caMarkingType
      |           FROM ClazzAssignment
      |          WHERE ClazzAssignment.caUid = ?) = 2)
      |    
      |                  AND
      |                  EXISTS(SELECT PeerReviewerAllocation.praUid
      |                           FROM PeerReviewerAllocation
      |                          WHERE PeerReviewerAllocation.praAssignmentUid = ?
      |                            AND PeerReviewerAllocation.praToMarkerSubmitterUid = ClazzEnrolment.clazzEnrolmentPersonUid
      |                            AND PeerReviewerAllocation.praMarkerSubmitterUid = ?))
      |                 )
      |         UNION
      |         -- List of submitter uids and names if the assignment is submitted by groups e.g. caGroupUid != 0
      |         SELECT DISTINCT CourseGroupMember.cgmGroupNumber AS submitterId,
      |                ? || ' ' || CourseGroupMember.cgmGroupNumber AS name
      |           FROM CourseGroupMember
      |          WHERE (
      |        SELECT ClazzAssignment.caGroupUid
      |                   FROM ClazzAssignment
      |                  WHERE ClazzAssignment.caUid = ?
      |    ) != 0
      |            AND CourseGroupMember.cgmSetUid = (
      |        SELECT ClazzAssignment.caGroupUid
      |                   FROM ClazzAssignment
      |                  WHERE ClazzAssignment.caUid = ?
      |    )
      |            -- either the active user has learning record select permission on class or is an assigned reviewer for submitter
      |            AND (
      |                (SELECT hasPermission 
      |                   FROM HasLearningRecordSelectPermission)
      |                OR 
      |                --check if the active user is in a group that was allocated to do a peer review of the given submitter uid
      |                (
      |        ((SELECT ClazzAssignment.caMarkingType
      |           FROM ClazzAssignment
      |          WHERE ClazzAssignment.caUid = ?) = 2)
      |    
      |                 AND
      |                 EXISTS(SELECT PeerReviewerAllocation.praUid
      |                          FROM PeerReviewerAllocation
      |                         WHERE PeerReviewerAllocation.praAssignmentUid = ?
      |                           AND PeerReviewerAllocation.praToMarkerSubmitterUid = CourseGroupMember.cgmGroupNumber
      |                           AND PeerReviewerAllocation.praMarkerSubmitterUid = 
      |                               (SELECT CourseGroupMemberInner.cgmGroupNumber
      |                                  FROM CourseGroupMember CourseGroupMemberInner
      |                                 WHERE CourseGroupMemberInner.cgmSetUid = (
      |        SELECT ClazzAssignment.caGroupUid
      |                   FROM ClazzAssignment
      |                  WHERE ClazzAssignment.caUid = ?
      |    )
      |                                   AND CourseGroupMemberInner.cgmPersonUid = ?
      |                                 LIMIT 1)
      |                        ))
      |            )
      |        )
      |        
      |    
      |        
      |        SELECT 
      |              -- whether or not the active user has permission to view learner records 
      |              (SELECT hasPermission
      |                 FROM HasLearningRecordSelectPermission) AS activeUserHasViewLearnerRecordsPermission,
      |        
      |              (SELECT COUNT(*)
      |                 FROM SubmitterList) AS totalStudents,
      |              
      |              -- Total marked students
      |              (SELECT COUNT(*)
      |                 FROM SubmitterList
      |                WHERE EXISTS(
      |                      SELECT CourseAssignmentMark.camUid
      |                        FROM CourseAssignmentMark
      |                       WHERE CourseAssignmentMark.camAssignmentUid = ?
      |                         AND CourseAssignmentMark.camSubmitterUid = SubmitterList.submitterId) 
      |                ) AS markedStudents,
      |              
      |              -- Total who have submitted  
      |              (SELECT COUNT(*)
      |                 FROM SubmitterList
      |                WHERE EXISTS(
      |                      SELECT CourseAssignmentSubmission.casUid
      |                        FROM CourseAssignmentSubmission
      |                       WHERE CourseAssignmentSubmission.casAssignmentUid = ?
      |                         AND CourseAssignmentSubmission.casSubmitterUid = SubmitterList.submitterId)
      |                ) AS submittedStudents,
      |              
      |              (SELECT (ClazzAssignment.caGroupUid != 0)
      |                 FROM ClazzAssignment
      |                WHERE ClazzAssignment.caUid = ?) AS isGroupAssignment
      |    
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,clazzUid)
      _stmt.setLong(2,accountPersonUid)
      _stmt.setLong(3,accountPersonUid)
      _stmt.setLong(4,clazzUid)
      _stmt.setLong(5,accountPersonUid)
      _stmt.setLong(6,accountPersonUid)
      _stmt.setLong(7,assignmentUid)
      _stmt.setLong(8,assignmentUid)
      _stmt.setLong(9,clazzUid)
      _stmt.setLong(10,assignmentUid)
      _stmt.setLong(11,assignmentUid)
      _stmt.setLong(12,accountPersonUid)
      _stmt.setString(13,group)
      _stmt.setLong(14,assignmentUid)
      _stmt.setLong(15,assignmentUid)
      _stmt.setLong(16,assignmentUid)
      _stmt.setLong(17,assignmentUid)
      _stmt.setLong(18,assignmentUid)
      _stmt.setLong(19,accountPersonUid)
      _stmt.setLong(20,assignmentUid)
      _stmt.setLong(21,assignmentUid)
      _stmt.setLong(22,assignmentUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(null) {
          val _tmp_submittedStudents = _result.getInt("submittedStudents")
          val _tmp_totalStudents = _result.getInt("totalStudents")
          val _tmp_markedStudents = _result.getInt("markedStudents")
          val _tmp_activeUserHasViewLearnerRecordsPermission =
              _result.getBoolean("activeUserHasViewLearnerRecordsPermission")
          val _tmp_isGroupAssignment = _result.getBoolean("isGroupAssignment")
          AssignmentProgressSummary().apply {
            this.submittedStudents = _tmp_submittedStudents
            this.totalStudents = _tmp_totalStudents
            this.markedStudents = _tmp_markedStudents
            this.activeUserHasViewLearnerRecordsPermission =
                _tmp_activeUserHasViewLearnerRecordsPermission
            this.isGroupAssignment = _tmp_isGroupAssignment
          }
        }
      }
    }
  }

  override fun getAssignmentSubmitterSummaryListForAssignment(
    assignmentUid: Long,
    clazzUid: Long,
    accountPersonUid: Long,
    group: String,
    searchText: String,
    sortOption: Int,
  ): PagingSource<Int, AssignmentSubmitterSummary> = object :
      DoorLimitOffsetPagingSource<AssignmentSubmitterSummary>(db = _db
  , tableNames = arrayOf("SystemPermission", "CoursePermission", "ClazzAssignment",
      "ClazzEnrolment", "PeerReviewerAllocation", "Person", "CourseGroupMember",
      "CourseAssignmentSubmission", "CourseAssignmentMark", "Comments", "PersonPicture")
  ) {
    override suspend fun loadRows(_limit: Int, _offset: Int): List<AssignmentSubmitterSummary> =
        _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT * FROM (
      |        WITH 
      |            HasLearningRecordSelectPermission (hasPermission) AS
      |            (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 = ?), 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)
      |               )
      |    ))
      |        ,
      |             
      |        AssignmentClazzUid(clazzUid) AS
      |        (SELECT ClazzAssignment.caClazzUid
      |           FROM ClazzAssignment
      |          WHERE ClazzAssignment.caUid = ?)  
      |    ,
      |             
      |        SubmitterList(submitterId, name) AS 
      |        -- List of submitter uids and names if individual assignment eg caGroupUid = 0
      |        (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid AS submitterId, 
      |                Person.firstNames || ' ' || Person.lastName AS name
      |           FROM ClazzEnrolment
      |                JOIN Person 
      |                     ON Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid
      |          WHERE (
      |        SELECT ClazzAssignment.caGroupUid
      |                   FROM ClazzAssignment
      |                  WHERE ClazzAssignment.caUid = ?
      |    ) = 0
      |            AND ClazzEnrolment.clazzEnrolmentClazzUid = ?
      |            AND ClazzEnrolment.clazzEnrolmentRole = 1000
      |            -- either the active user has learnign record select permission on class or is an assigned reviewer for submitter
      |            AND (
      |                (SELECT hasPermission 
      |                   FROM HasLearningRecordSelectPermission)
      |                OR  
      |                 -- check if the active person eg accountpersonuid is assigned to mark this peer
      |                 (
      |        ((SELECT ClazzAssignment.caMarkingType
      |           FROM ClazzAssignment
      |          WHERE ClazzAssignment.caUid = ?) = 2)
      |    
      |                  AND
      |                  EXISTS(SELECT PeerReviewerAllocation.praUid
      |                           FROM PeerReviewerAllocation
      |                          WHERE PeerReviewerAllocation.praAssignmentUid = ?
      |                            AND PeerReviewerAllocation.praToMarkerSubmitterUid = ClazzEnrolment.clazzEnrolmentPersonUid
      |                            AND PeerReviewerAllocation.praMarkerSubmitterUid = ?))
      |                 )
      |         UNION
      |         -- List of submitter uids and names if the assignment is submitted by groups e.g. caGroupUid != 0
      |         SELECT DISTINCT CourseGroupMember.cgmGroupNumber AS submitterId,
      |                ? || ' ' || CourseGroupMember.cgmGroupNumber AS name
      |           FROM CourseGroupMember
      |          WHERE (
      |        SELECT ClazzAssignment.caGroupUid
      |                   FROM ClazzAssignment
      |                  WHERE ClazzAssignment.caUid = ?
      |    ) != 0
      |            AND CourseGroupMember.cgmSetUid = (
      |        SELECT ClazzAssignment.caGroupUid
      |                   FROM ClazzAssignment
      |                  WHERE ClazzAssignment.caUid = ?
      |    )
      |            -- either the active user has learning record select permission on class or is an assigned reviewer for submitter
      |            AND (
      |                (SELECT hasPermission 
      |                   FROM HasLearningRecordSelectPermission)
      |                OR 
      |                --check if the active user is in a group that was allocated to do a peer review of the given submitter uid
      |                (
      |        ((SELECT ClazzAssignment.caMarkingType
      |           FROM ClazzAssignment
      |          WHERE ClazzAssignment.caUid = ?) = 2)
      |    
      |                 AND
      |                 EXISTS(SELECT PeerReviewerAllocation.praUid
      |                          FROM PeerReviewerAllocation
      |                         WHERE PeerReviewerAllocation.praAssignmentUid = ?
      |                           AND PeerReviewerAllocation.praToMarkerSubmitterUid = CourseGroupMember.cgmGroupNumber
      |                           AND PeerReviewerAllocation.praMarkerSubmitterUid = 
      |                               (SELECT CourseGroupMemberInner.cgmGroupNumber
      |                                  FROM CourseGroupMember CourseGroupMemberInner
      |                                 WHERE CourseGroupMemberInner.cgmSetUid = (
      |        SELECT ClazzAssignment.caGroupUid
      |                   FROM ClazzAssignment
      |                  WHERE ClazzAssignment.caUid = ?
      |    )
      |                                   AND CourseGroupMemberInner.cgmPersonUid = ?
      |                                 LIMIT 1)
      |                        ))
      |            )
      |        )
      |        
      |    
      |        
      |        SELECT SubmitterList.name AS name,
      |               SubmitterList.submitterId AS submitterUid,
      |               Comments.commentsText AS latestPrivateComment,
      |               CASE 
      |               WHEN SubmitterList.submitterId >= 10000 THEN
      |                    (SELECT PersonPicture.personPictureThumbnailUri
      |                       FROM PersonPicture
      |                      WHERE PersonPicture.personPictureUid =  SubmitterList.submitterId)
      |               ELSE NULL
      |               END AS pictureUri,       
      |               -- Determine submission status - marked, submitted, or not yet submitted
      |               CASE 
      |               WHEN CourseAssignmentMark.camUid IS NOT NULL THEN 2
      |               WHEN CourseAssignmentSubmission.casUid IS NOT NULL THEN 1
      |               ELSE 0 
      |               END AS fileSubmissionStatus
      |               
      |          FROM SubmitterList
      |               LEFT JOIN Comments 
      |                         ON Comments.commentsUid = 
      |                            (SELECT Comments.commentsUid 
      |                               FROM Comments
      |                              WHERE Comments.commentsEntityUid = ?
      |                                AND Comments.commentsForSubmitterUid = SubmitterList.submitterId
      |                                AND NOT Comments.commentsDeleted
      |                           ORDER BY Comments.commentsDateTimeAdded DESC     
      |                              LIMIT 1) 
      |               LEFT JOIN CourseAssignmentMark
      |                         ON CourseAssignmentMark.camUid = 
      |                            (SELECT camUid
      |                               FROM CourseAssignmentMark
      |                              WHERE CourseAssignmentMark.camAssignmentUid = ?
      |                                AND CourseAssignmentMark.camSubmitterUid = SubmitterList.submitterId
      |                              LIMIT 1)
      |               LEFT JOIN CourseAssignmentSubmission
      |                         ON CourseAssignmentSubmission.casUid = 
      |                            (SELECT casUid
      |                               FROM CourseAssignmentSubmission
      |                              WHERE CourseAssignmentSubmission.casAssignmentUid = ?
      |                                AND CourseAssignmentSubmission.casSubmitterUid = SubmitterList.submitterId 
      |                              LIMIT 1)
      |         WHERE (? = '%' OR SubmitterList.name LIKE ?)
      |      ORDER BY CASE(?)
      |               WHEN 5 THEN SubmitterList.name
      |               ELSE '' END ASC,
      |               CASE(?)
      |               WHEN 6 THEN SubmitterList.name
      |               ELSE '' END DESC
      |    ) AS _PagingData LIMIT ? OFFSET ?
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,clazzUid)
      _stmt.setLong(2,accountPersonUid)
      _stmt.setLong(3,accountPersonUid)
      _stmt.setLong(4,clazzUid)
      _stmt.setLong(5,accountPersonUid)
      _stmt.setLong(6,accountPersonUid)
      _stmt.setLong(7,assignmentUid)
      _stmt.setLong(8,assignmentUid)
      _stmt.setLong(9,clazzUid)
      _stmt.setLong(10,assignmentUid)
      _stmt.setLong(11,assignmentUid)
      _stmt.setLong(12,accountPersonUid)
      _stmt.setString(13,group)
      _stmt.setLong(14,assignmentUid)
      _stmt.setLong(15,assignmentUid)
      _stmt.setLong(16,assignmentUid)
      _stmt.setLong(17,assignmentUid)
      _stmt.setLong(18,assignmentUid)
      _stmt.setLong(19,accountPersonUid)
      _stmt.setLong(20,assignmentUid)
      _stmt.setLong(21,assignmentUid)
      _stmt.setLong(22,assignmentUid)
      _stmt.setString(23,searchText)
      _stmt.setString(24,searchText)
      _stmt.setInt(25,sortOption)
      _stmt.setInt(26,sortOption)
      _stmt.setInt(27,_limit)
      _stmt.setInt(28,_offset)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          val _tmp_submitterUid = _result.getLong("submitterUid")
          val _tmp_name = _result.getString("name")
          val _tmp_pictureUri = _result.getString("pictureUri")
          val _tmp_latestPrivateComment = _result.getString("latestPrivateComment")
          val _tmp_fileSubmissionStatus = _result.getInt("fileSubmissionStatus")
          AssignmentSubmitterSummary().apply {
            this.submitterUid = _tmp_submitterUid
            this.name = _tmp_name
            this.pictureUri = _tmp_pictureUri
            this.latestPrivateComment = _tmp_latestPrivateComment
            this.fileSubmissionStatus = _tmp_fileSubmissionStatus
          }
        }
      }
    }

    override suspend fun countRows(): Int = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT COUNT(*) FROM (
      |        WITH 
      |            HasLearningRecordSelectPermission (hasPermission) AS
      |            (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 = ?), 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)
      |               )
      |    ))
      |        ,
      |             
      |        AssignmentClazzUid(clazzUid) AS
      |        (SELECT ClazzAssignment.caClazzUid
      |           FROM ClazzAssignment
      |          WHERE ClazzAssignment.caUid = ?)  
      |    ,
      |             
      |        SubmitterList(submitterId, name) AS 
      |        -- List of submitter uids and names if individual assignment eg caGroupUid = 0
      |        (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid AS submitterId, 
      |                Person.firstNames || ' ' || Person.lastName AS name
      |           FROM ClazzEnrolment
      |                JOIN Person 
      |                     ON Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid
      |          WHERE (
      |        SELECT ClazzAssignment.caGroupUid
      |                   FROM ClazzAssignment
      |                  WHERE ClazzAssignment.caUid = ?
      |    ) = 0
      |            AND ClazzEnrolment.clazzEnrolmentClazzUid = ?
      |            AND ClazzEnrolment.clazzEnrolmentRole = 1000
      |            -- either the active user has learnign record select permission on class or is an assigned reviewer for submitter
      |            AND (
      |                (SELECT hasPermission 
      |                   FROM HasLearningRecordSelectPermission)
      |                OR  
      |                 -- check if the active person eg accountpersonuid is assigned to mark this peer
      |                 (
      |        ((SELECT ClazzAssignment.caMarkingType
      |           FROM ClazzAssignment
      |          WHERE ClazzAssignment.caUid = ?) = 2)
      |    
      |                  AND
      |                  EXISTS(SELECT PeerReviewerAllocation.praUid
      |                           FROM PeerReviewerAllocation
      |                          WHERE PeerReviewerAllocation.praAssignmentUid = ?
      |                            AND PeerReviewerAllocation.praToMarkerSubmitterUid = ClazzEnrolment.clazzEnrolmentPersonUid
      |                            AND PeerReviewerAllocation.praMarkerSubmitterUid = ?))
      |                 )
      |         UNION
      |         -- List of submitter uids and names if the assignment is submitted by groups e.g. caGroupUid != 0
      |         SELECT DISTINCT CourseGroupMember.cgmGroupNumber AS submitterId,
      |                ? || ' ' || CourseGroupMember.cgmGroupNumber AS name
      |           FROM CourseGroupMember
      |          WHERE (
      |        SELECT ClazzAssignment.caGroupUid
      |                   FROM ClazzAssignment
      |                  WHERE ClazzAssignment.caUid = ?
      |    ) != 0
      |            AND CourseGroupMember.cgmSetUid = (
      |        SELECT ClazzAssignment.caGroupUid
      |                   FROM ClazzAssignment
      |                  WHERE ClazzAssignment.caUid = ?
      |    )
      |            -- either the active user has learning record select permission on class or is an assigned reviewer for submitter
      |            AND (
      |                (SELECT hasPermission 
      |                   FROM HasLearningRecordSelectPermission)
      |                OR 
      |                --check if the active user is in a group that was allocated to do a peer review of the given submitter uid
      |                (
      |        ((SELECT ClazzAssignment.caMarkingType
      |           FROM ClazzAssignment
      |          WHERE ClazzAssignment.caUid = ?) = 2)
      |    
      |                 AND
      |                 EXISTS(SELECT PeerReviewerAllocation.praUid
      |                          FROM PeerReviewerAllocation
      |                         WHERE PeerReviewerAllocation.praAssignmentUid = ?
      |                           AND PeerReviewerAllocation.praToMarkerSubmitterUid = CourseGroupMember.cgmGroupNumber
      |                           AND PeerReviewerAllocation.praMarkerSubmitterUid = 
      |                               (SELECT CourseGroupMemberInner.cgmGroupNumber
      |                                  FROM CourseGroupMember CourseGroupMemberInner
      |                                 WHERE CourseGroupMemberInner.cgmSetUid = (
      |        SELECT ClazzAssignment.caGroupUid
      |                   FROM ClazzAssignment
      |                  WHERE ClazzAssignment.caUid = ?
      |    )
      |                                   AND CourseGroupMemberInner.cgmPersonUid = ?
      |                                 LIMIT 1)
      |                        ))
      |            )
      |        )
      |        
      |    
      |        
      |        SELECT SubmitterList.name AS name,
      |               SubmitterList.submitterId AS submitterUid,
      |               Comments.commentsText AS latestPrivateComment,
      |               CASE 
      |               WHEN SubmitterList.submitterId >= 10000 THEN
      |                    (SELECT PersonPicture.personPictureThumbnailUri
      |                       FROM PersonPicture
      |                      WHERE PersonPicture.personPictureUid =  SubmitterList.submitterId)
      |               ELSE NULL
      |               END AS pictureUri,       
      |               -- Determine submission status - marked, submitted, or not yet submitted
      |               CASE 
      |               WHEN CourseAssignmentMark.camUid IS NOT NULL THEN 2
      |               WHEN CourseAssignmentSubmission.casUid IS NOT NULL THEN 1
      |               ELSE 0 
      |               END AS fileSubmissionStatus
      |               
      |          FROM SubmitterList
      |               LEFT JOIN Comments 
      |                         ON Comments.commentsUid = 
      |                            (SELECT Comments.commentsUid 
      |                               FROM Comments
      |                              WHERE Comments.commentsEntityUid = ?
      |                                AND Comments.commentsForSubmitterUid = SubmitterList.submitterId
      |                                AND NOT Comments.commentsDeleted
      |                           ORDER BY Comments.commentsDateTimeAdded DESC     
      |                              LIMIT 1) 
      |               LEFT JOIN CourseAssignmentMark
      |                         ON CourseAssignmentMark.camUid = 
      |                            (SELECT camUid
      |                               FROM CourseAssignmentMark
      |                              WHERE CourseAssignmentMark.camAssignmentUid = ?
      |                                AND CourseAssignmentMark.camSubmitterUid = SubmitterList.submitterId
      |                              LIMIT 1)
      |               LEFT JOIN CourseAssignmentSubmission
      |                         ON CourseAssignmentSubmission.casUid = 
      |                            (SELECT casUid
      |                               FROM CourseAssignmentSubmission
      |                              WHERE CourseAssignmentSubmission.casAssignmentUid = ?
      |                                AND CourseAssignmentSubmission.casSubmitterUid = SubmitterList.submitterId 
      |                              LIMIT 1)
      |         WHERE (? = '%' OR SubmitterList.name LIKE ?)
      |      ORDER BY CASE(?)
      |               WHEN 5 THEN SubmitterList.name
      |               ELSE '' END ASC,
      |               CASE(?)
      |               WHEN 6 THEN SubmitterList.name
      |               ELSE '' END DESC
      |    ) AS _PagingCount
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,clazzUid)
      _stmt.setLong(2,accountPersonUid)
      _stmt.setLong(3,accountPersonUid)
      _stmt.setLong(4,clazzUid)
      _stmt.setLong(5,accountPersonUid)
      _stmt.setLong(6,accountPersonUid)
      _stmt.setLong(7,assignmentUid)
      _stmt.setLong(8,assignmentUid)
      _stmt.setLong(9,clazzUid)
      _stmt.setLong(10,assignmentUid)
      _stmt.setLong(11,assignmentUid)
      _stmt.setLong(12,accountPersonUid)
      _stmt.setString(13,group)
      _stmt.setLong(14,assignmentUid)
      _stmt.setLong(15,assignmentUid)
      _stmt.setLong(16,assignmentUid)
      _stmt.setLong(17,assignmentUid)
      _stmt.setLong(18,assignmentUid)
      _stmt.setLong(19,accountPersonUid)
      _stmt.setLong(20,assignmentUid)
      _stmt.setLong(21,assignmentUid)
      _stmt.setLong(22,assignmentUid)
      _stmt.setString(23,searchText)
      _stmt.setString(24,searchText)
      _stmt.setInt(25,sortOption)
      _stmt.setInt(26,sortOption)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(0) {
          _result.getInt(1)
        }
      }
    }
  }

  override suspend fun getCourseGroupMembersByAssignmentUid(assignmentUid: Long):
      List<CourseGroupMember> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT CourseGroupMember.*
    |          FROM CourseGroupMember
    |         WHERE CourseGroupMember.cgmSetUid = 
    |               (SELECT ClazzAssignment.caGroupUid
    |                  FROM ClazzAssignment
    |                 WHERE ClazzAssignment.caUid = ?) 
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,assignmentUid)
    _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 getAssignmentSubmissionsByAssignmentUid(
    assignmentUid: Long,
    clazzUid: Long,
    accountPersonUid: Long,
    group: String,
  ): List<CourseAssignmentSubmission> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        WITH 
    |            HasLearningRecordSelectPermission (hasPermission) AS
    |            (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 = ?), 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)
    |               )
    |    ))
    |        ,
    |             
    |        AssignmentClazzUid(clazzUid) AS
    |        (SELECT ClazzAssignment.caClazzUid
    |           FROM ClazzAssignment
    |          WHERE ClazzAssignment.caUid = ?)  
    |    ,
    |             
    |        SubmitterList(submitterId, name) AS 
    |        -- List of submitter uids and names if individual assignment eg caGroupUid = 0
    |        (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid AS submitterId, 
    |                Person.firstNames || ' ' || Person.lastName AS name
    |           FROM ClazzEnrolment
    |                JOIN Person 
    |                     ON Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid
    |          WHERE (
    |        SELECT ClazzAssignment.caGroupUid
    |                   FROM ClazzAssignment
    |                  WHERE ClazzAssignment.caUid = ?
    |    ) = 0
    |            AND ClazzEnrolment.clazzEnrolmentClazzUid = ?
    |            AND ClazzEnrolment.clazzEnrolmentRole = 1000
    |            -- either the active user has learnign record select permission on class or is an assigned reviewer for submitter
    |            AND (
    |                (SELECT hasPermission 
    |                   FROM HasLearningRecordSelectPermission)
    |                OR  
    |                 -- check if the active person eg accountpersonuid is assigned to mark this peer
    |                 (
    |        ((SELECT ClazzAssignment.caMarkingType
    |           FROM ClazzAssignment
    |          WHERE ClazzAssignment.caUid = ?) = 2)
    |    
    |                  AND
    |                  EXISTS(SELECT PeerReviewerAllocation.praUid
    |                           FROM PeerReviewerAllocation
    |                          WHERE PeerReviewerAllocation.praAssignmentUid = ?
    |                            AND PeerReviewerAllocation.praToMarkerSubmitterUid = ClazzEnrolment.clazzEnrolmentPersonUid
    |                            AND PeerReviewerAllocation.praMarkerSubmitterUid = ?))
    |                 )
    |         UNION
    |         -- List of submitter uids and names if the assignment is submitted by groups e.g. caGroupUid != 0
    |         SELECT DISTINCT CourseGroupMember.cgmGroupNumber AS submitterId,
    |                ? || ' ' || CourseGroupMember.cgmGroupNumber AS name
    |           FROM CourseGroupMember
    |          WHERE (
    |        SELECT ClazzAssignment.caGroupUid
    |                   FROM ClazzAssignment
    |                  WHERE ClazzAssignment.caUid = ?
    |    ) != 0
    |            AND CourseGroupMember.cgmSetUid = (
    |        SELECT ClazzAssignment.caGroupUid
    |                   FROM ClazzAssignment
    |                  WHERE ClazzAssignment.caUid = ?
    |    )
    |            -- either the active user has learning record select permission on class or is an assigned reviewer for submitter
    |            AND (
    |                (SELECT hasPermission 
    |                   FROM HasLearningRecordSelectPermission)
    |                OR 
    |                --check if the active user is in a group that was allocated to do a peer review of the given submitter uid
    |                (
    |        ((SELECT ClazzAssignment.caMarkingType
    |           FROM ClazzAssignment
    |          WHERE ClazzAssignment.caUid = ?) = 2)
    |    
    |                 AND
    |                 EXISTS(SELECT PeerReviewerAllocation.praUid
    |                          FROM PeerReviewerAllocation
    |                         WHERE PeerReviewerAllocation.praAssignmentUid = ?
    |                           AND PeerReviewerAllocation.praToMarkerSubmitterUid = CourseGroupMember.cgmGroupNumber
    |                           AND PeerReviewerAllocation.praMarkerSubmitterUid = 
    |                               (SELECT CourseGroupMemberInner.cgmGroupNumber
    |                                  FROM CourseGroupMember CourseGroupMemberInner
    |                                 WHERE CourseGroupMemberInner.cgmSetUid = (
    |        SELECT ClazzAssignment.caGroupUid
    |                   FROM ClazzAssignment
    |                  WHERE ClazzAssignment.caUid = ?
    |    )
    |                                   AND CourseGroupMemberInner.cgmPersonUid = ?
    |                                 LIMIT 1)
    |                        ))
    |            )
    |        )
    |        
    |    
    |      SELECT CourseAssignmentSubmission.*
    |        FROM CourseAssignmentSubmission 
    |       WHERE CourseAssignmentSubmission.casAssignmentUid = ?
    |         AND CourseAssignmentSubmission.casClazzUid = ?
    |         AND CourseAssignmentSubmission.casSubmitterUid IN 
    |             (SELECT SubmitterList.submitterId
    |                FROM SubmitterList) 
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,clazzUid)
    _stmt.setLong(2,accountPersonUid)
    _stmt.setLong(3,accountPersonUid)
    _stmt.setLong(4,clazzUid)
    _stmt.setLong(5,accountPersonUid)
    _stmt.setLong(6,accountPersonUid)
    _stmt.setLong(7,assignmentUid)
    _stmt.setLong(8,assignmentUid)
    _stmt.setLong(9,clazzUid)
    _stmt.setLong(10,assignmentUid)
    _stmt.setLong(11,assignmentUid)
    _stmt.setLong(12,accountPersonUid)
    _stmt.setString(13,group)
    _stmt.setLong(14,assignmentUid)
    _stmt.setLong(15,assignmentUid)
    _stmt.setLong(16,assignmentUid)
    _stmt.setLong(17,assignmentUid)
    _stmt.setLong(18,assignmentUid)
    _stmt.setLong(19,accountPersonUid)
    _stmt.setLong(20,assignmentUid)
    _stmt.setLong(21,clazzUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_casUid = _result.getLong("casUid")
        val _tmp_casAssignmentUid = _result.getLong("casAssignmentUid")
        val _tmp_casSubmitterUid = _result.getLong("casSubmitterUid")
        val _tmp_casSubmitterPersonUid = _result.getLong("casSubmitterPersonUid")
        val _tmp_casText = _result.getString("casText")
        val _tmp_casType = _result.getInt("casType")
        val _tmp_casTimestamp = _result.getLong("casTimestamp")
        val _tmp_casClazzUid = _result.getLong("casClazzUid")
        CourseAssignmentSubmission().apply {
          this.casUid = _tmp_casUid
          this.casAssignmentUid = _tmp_casAssignmentUid
          this.casSubmitterUid = _tmp_casSubmitterUid
          this.casSubmitterPersonUid = _tmp_casSubmitterPersonUid
          this.casText = _tmp_casText
          this.casType = _tmp_casType
          this.casTimestamp = _tmp_casTimestamp
          this.casClazzUid = _tmp_casClazzUid
        }
      }
    }
  }

  override suspend fun getAssignmentMarksByAssignmentUid(
    assignmentUid: Long,
    clazzUid: Long,
    accountPersonUid: Long,
    group: String,
  ): List<CourseAssignmentMark> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        WITH 
    |            HasLearningRecordSelectPermission (hasPermission) AS
    |            (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 = ?), 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)
    |               )
    |    ))
    |        ,
    |             
    |        AssignmentClazzUid(clazzUid) AS
    |        (SELECT ClazzAssignment.caClazzUid
    |           FROM ClazzAssignment
    |          WHERE ClazzAssignment.caUid = ?)  
    |    ,
    |             
    |        SubmitterList(submitterId, name) AS 
    |        -- List of submitter uids and names if individual assignment eg caGroupUid = 0
    |        (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid AS submitterId, 
    |                Person.firstNames || ' ' || Person.lastName AS name
    |           FROM ClazzEnrolment
    |                JOIN Person 
    |                     ON Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid
    |          WHERE (
    |        SELECT ClazzAssignment.caGroupUid
    |                   FROM ClazzAssignment
    |                  WHERE ClazzAssignment.caUid = ?
    |    ) = 0
    |            AND ClazzEnrolment.clazzEnrolmentClazzUid = ?
    |            AND ClazzEnrolment.clazzEnrolmentRole = 1000
    |            -- either the active user has learnign record select permission on class or is an assigned reviewer for submitter
    |            AND (
    |                (SELECT hasPermission 
    |                   FROM HasLearningRecordSelectPermission)
    |                OR  
    |                 -- check if the active person eg accountpersonuid is assigned to mark this peer
    |                 (
    |        ((SELECT ClazzAssignment.caMarkingType
    |           FROM ClazzAssignment
    |          WHERE ClazzAssignment.caUid = ?) = 2)
    |    
    |                  AND
    |                  EXISTS(SELECT PeerReviewerAllocation.praUid
    |                           FROM PeerReviewerAllocation
    |                          WHERE PeerReviewerAllocation.praAssignmentUid = ?
    |                            AND PeerReviewerAllocation.praToMarkerSubmitterUid = ClazzEnrolment.clazzEnrolmentPersonUid
    |                            AND PeerReviewerAllocation.praMarkerSubmitterUid = ?))
    |                 )
    |         UNION
    |         -- List of submitter uids and names if the assignment is submitted by groups e.g. caGroupUid != 0
    |         SELECT DISTINCT CourseGroupMember.cgmGroupNumber AS submitterId,
    |                ? || ' ' || CourseGroupMember.cgmGroupNumber AS name
    |           FROM CourseGroupMember
    |          WHERE (
    |        SELECT ClazzAssignment.caGroupUid
    |                   FROM ClazzAssignment
    |                  WHERE ClazzAssignment.caUid = ?
    |    ) != 0
    |            AND CourseGroupMember.cgmSetUid = (
    |        SELECT ClazzAssignment.caGroupUid
    |                   FROM ClazzAssignment
    |                  WHERE ClazzAssignment.caUid = ?
    |    )
    |            -- either the active user has learning record select permission on class or is an assigned reviewer for submitter
    |            AND (
    |                (SELECT hasPermission 
    |                   FROM HasLearningRecordSelectPermission)
    |                OR 
    |                --check if the active user is in a group that was allocated to do a peer review of the given submitter uid
    |                (
    |        ((SELECT ClazzAssignment.caMarkingType
    |           FROM ClazzAssignment
    |          WHERE ClazzAssignment.caUid = ?) = 2)
    |    
    |                 AND
    |                 EXISTS(SELECT PeerReviewerAllocation.praUid
    |                          FROM PeerReviewerAllocation
    |                         WHERE PeerReviewerAllocation.praAssignmentUid = ?
    |                           AND PeerReviewerAllocation.praToMarkerSubmitterUid = CourseGroupMember.cgmGroupNumber
    |                           AND PeerReviewerAllocation.praMarkerSubmitterUid = 
    |                               (SELECT CourseGroupMemberInner.cgmGroupNumber
    |                                  FROM CourseGroupMember CourseGroupMemberInner
    |                                 WHERE CourseGroupMemberInner.cgmSetUid = (
    |        SELECT ClazzAssignment.caGroupUid
    |                   FROM ClazzAssignment
    |                  WHERE ClazzAssignment.caUid = ?
    |    )
    |                                   AND CourseGroupMemberInner.cgmPersonUid = ?
    |                                 LIMIT 1)
    |                        ))
    |            )
    |        )
    |        
    |    
    |      SELECT CourseAssignmentMark.*
    |        FROM CourseAssignmentMark 
    |       WHERE CourseAssignmentMark.camAssignmentUid = ?
    |         AND CourseAssignmentMark.camClazzUid = ?
    |         AND CourseAssignmentMark.camSubmitterUid IN 
    |             (SELECT SubmitterList.submitterId
    |                FROM SubmitterList)
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,clazzUid)
    _stmt.setLong(2,accountPersonUid)
    _stmt.setLong(3,accountPersonUid)
    _stmt.setLong(4,clazzUid)
    _stmt.setLong(5,accountPersonUid)
    _stmt.setLong(6,accountPersonUid)
    _stmt.setLong(7,assignmentUid)
    _stmt.setLong(8,assignmentUid)
    _stmt.setLong(9,clazzUid)
    _stmt.setLong(10,assignmentUid)
    _stmt.setLong(11,assignmentUid)
    _stmt.setLong(12,accountPersonUid)
    _stmt.setString(13,group)
    _stmt.setLong(14,assignmentUid)
    _stmt.setLong(15,assignmentUid)
    _stmt.setLong(16,assignmentUid)
    _stmt.setLong(17,assignmentUid)
    _stmt.setLong(18,assignmentUid)
    _stmt.setLong(19,accountPersonUid)
    _stmt.setLong(20,assignmentUid)
    _stmt.setLong(21,clazzUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_camUid = _result.getLong("camUid")
        val _tmp_camAssignmentUid = _result.getLong("camAssignmentUid")
        val _tmp_camSubmitterUid = _result.getLong("camSubmitterUid")
        val _tmp_camMarkerSubmitterUid = _result.getLong("camMarkerSubmitterUid")
        val _tmp_camMarkerPersonUid = _result.getLong("camMarkerPersonUid")
        val _tmp_camMarkerComment = _result.getString("camMarkerComment")
        val _tmp_camMark = _result.getFloat("camMark")
        val _tmp_camMaxMark = _result.getFloat("camMaxMark")
        val _tmp_camPenalty = _result.getFloat("camPenalty")
        val _tmp_camLct = _result.getLong("camLct")
        val _tmp_camClazzUid = _result.getLong("camClazzUid")
        CourseAssignmentMark().apply {
          this.camUid = _tmp_camUid
          this.camAssignmentUid = _tmp_camAssignmentUid
          this.camSubmitterUid = _tmp_camSubmitterUid
          this.camMarkerSubmitterUid = _tmp_camMarkerSubmitterUid
          this.camMarkerPersonUid = _tmp_camMarkerPersonUid
          this.camMarkerComment = _tmp_camMarkerComment
          this.camMark = _tmp_camMark
          this.camMaxMark = _tmp_camMaxMark
          this.camPenalty = _tmp_camPenalty
          this.camLct = _tmp_camLct
          this.camClazzUid = _tmp_camClazzUid
        }
      }
    }
  }

  override suspend fun getPeerReviewerAllocationsByAssignmentUid(
    assignmentUid: Long,
    clazzUid: Long,
    accountPersonUid: Long,
  ): List<PeerReviewerAllocation> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |          WITH 
    |            HasLearningRecordSelectPermission (hasPermission) AS
    |            (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 = ?), 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)
    |               )
    |    ))
    |        
    |        SELECT PeerReviewerAllocation.*
    |          FROM PeerReviewerAllocation
    |         WHERE 
    |        ((SELECT ClazzAssignment.caMarkingType
    |           FROM ClazzAssignment
    |          WHERE ClazzAssignment.caUid = ?) = 2)
    |    
    |          AND NOT 
    |              (SELECT hasPermission 
    |                 FROM HasLearningRecordSelectPermission) 
    |           AND PeerReviewerAllocation.praAssignmentUid = ?
    |           AND (   PeerReviewerAllocation.praMarkerSubmitterUid = ?
    |                OR PeerReviewerAllocation.praMarkerSubmitterUid IN 
    |                   (SELECT CourseGroupMember.cgmGroupNumber
    |                      FROM CourseGroupMember
    |                     WHERE CourseGroupMember.cgmSetUid = 
    |                           (SELECT ClazzAssignment.caGroupUid
    |                              FROM ClazzAssignment
    |                             WHERE ClazzAssignment.caUid = ?)))
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,clazzUid)
    _stmt.setLong(2,accountPersonUid)
    _stmt.setLong(3,accountPersonUid)
    _stmt.setLong(4,clazzUid)
    _stmt.setLong(5,accountPersonUid)
    _stmt.setLong(6,accountPersonUid)
    _stmt.setLong(7,assignmentUid)
    _stmt.setLong(8,assignmentUid)
    _stmt.setLong(9,accountPersonUid)
    _stmt.setLong(10,assignmentUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_praUid = _result.getLong("praUid")
        val _tmp_praMarkerSubmitterUid = _result.getLong("praMarkerSubmitterUid")
        val _tmp_praToMarkerSubmitterUid = _result.getLong("praToMarkerSubmitterUid")
        val _tmp_praAssignmentUid = _result.getLong("praAssignmentUid")
        val _tmp_praActive = _result.getBoolean("praActive")
        val _tmp_praLct = _result.getLong("praLct")
        PeerReviewerAllocation().apply {
          this.praUid = _tmp_praUid
          this.praMarkerSubmitterUid = _tmp_praMarkerSubmitterUid
          this.praToMarkerSubmitterUid = _tmp_praToMarkerSubmitterUid
          this.praAssignmentUid = _tmp_praAssignmentUid
          this.praActive = _tmp_praActive
          this.praLct = _tmp_praLct
        }
      }
    }
  }

  override suspend fun getPeerReviewAllocationsForPerson(assignmentUid: Long,
      accountPersonUid: Long): List<PeerReviewerAllocation> =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT PeerReviewerAllocation.*
    |          FROM PeerReviewerAllocation
    |         WHERE PeerReviewerAllocation.praAssignmentUid = ?
    |           AND (PeerReviewerAllocation.praToMarkerSubmitterUid = ?
    |                OR
    |                PeerReviewerAllocation.praToMarkerSubmitterUid IN 
    |                (SELECT CourseGroupMember.cgmSetUid 
    |                   FROM CourseGroupMember
    |                  WHERE CourseGroupMember.cgmSetUid = 
    |                        (SELECT ClazzAssignment.caGroupUid
    |                           FROM ClazzAssignment
    |                          WHERE ClazzAssignment.caUid = ?)
    |                    AND CourseGroupMember.cgmPersonUid = ?))                 
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,assignmentUid)
    _stmt.setLong(2,accountPersonUid)
    _stmt.setLong(3,assignmentUid)
    _stmt.setLong(4,accountPersonUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_praUid = _result.getLong("praUid")
        val _tmp_praMarkerSubmitterUid = _result.getLong("praMarkerSubmitterUid")
        val _tmp_praToMarkerSubmitterUid = _result.getLong("praToMarkerSubmitterUid")
        val _tmp_praAssignmentUid = _result.getLong("praAssignmentUid")
        val _tmp_praActive = _result.getBoolean("praActive")
        val _tmp_praLct = _result.getLong("praLct")
        PeerReviewerAllocation().apply {
          this.praUid = _tmp_praUid
          this.praMarkerSubmitterUid = _tmp_praMarkerSubmitterUid
          this.praToMarkerSubmitterUid = _tmp_praToMarkerSubmitterUid
          this.praAssignmentUid = _tmp_praAssignmentUid
          this.praActive = _tmp_praActive
          this.praLct = _tmp_praLct
        }
      }
    }
  }

  override suspend fun getSubmitterUidsByClazzOrGroupSetUid(
    clazzUid: Long,
    groupSetUid: Long,
    time: Long,
  ): List<Long> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |         -- Submitter UIDs for individual assignment the list of personuids enrolled in the course
    |         SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid AS submitterUid
    |           FROM ClazzEnrolment
    |          WHERE (? = 0)
    |            AND ClazzEnrolment.clazzEnrolmentClazzUid = ?
    |            AND ClazzEnrolment.clazzEnrolmentRole = 1000
    |            AND ? BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined AND ClazzEnrolment.clazzEnrolmentDateLeft
    |          
    |         UNION
    |         
    |        SELECT DISTINCT CourseGroupMember.cgmGroupNumber AS submitterUid
    |          FROM CourseGroupMember
    |         WHERE ? != 0
    |           AND CourseGroupMember.cgmSetUid = ?         
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,groupSetUid)
    _stmt.setLong(2,clazzUid)
    _stmt.setLong(3,time)
    _stmt.setLong(4,groupSetUid)
    _stmt.setLong(5,groupSetUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        _result.getLong(1)
      }
    }
  }

  override suspend fun getSubmitterUidsAndNameByClazzOrGroupSetUid(
    clazzUid: Long,
    groupSetUid: Long,
    date: Long,
    groupStr: String,
  ): List<AssignmentSubmitterUidAndName> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        WITH SubmitterUids(submitterUid) AS (
    |            SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid AS submitterUid
    |               FROM ClazzEnrolment
    |              WHERE (? = 0)
    |                AND ClazzEnrolment.clazzEnrolmentClazzUid = ?
    |                AND ClazzEnrolment.clazzEnrolmentRole = 1000
    |                AND ? BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined AND ClazzEnrolment.clazzEnrolmentDateLeft
    |              
    |             UNION
    |             
    |            SELECT DISTINCT CourseGroupMember.cgmGroupNumber AS submitterUid
    |              FROM CourseGroupMember
    |             WHERE ? != 0
    |               AND CourseGroupMember.cgmSetUid = ?    
    |        )
    |        
    |        SELECT SubmitterUids.submitterUid AS submitterUid,
    |               CASE ?
    |               WHEN 0 THEN
    |                      (SELECT Person.firstNames || ' ' || Person.lastName
    |                         FROM Person
    |                        WHERE Person.personUid = SubmitterUids.submitterUid)
    |               ELSE (? || ' ' || SubmitterUids.submitterUid)   
    |               END AS name
    |          FROM SubmitterUids                  
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,groupSetUid)
    _stmt.setLong(2,clazzUid)
    _stmt.setLong(3,date)
    _stmt.setLong(4,groupSetUid)
    _stmt.setLong(5,groupSetUid)
    _stmt.setLong(6,groupSetUid)
    _stmt.setString(7,groupStr)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_name = _result.getString("name")
        val _tmp_submitterUid = _result.getLong("submitterUid")
        AssignmentSubmitterUidAndName().apply {
          this.name = _tmp_name
          this.submitterUid = _tmp_submitterUid
        }
      }
    }
  }

  override suspend fun getSubmitterCountFromAssignment(
    groupUid: Long,
    clazzUid: Long,
    group: String,
  ): Int = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |         
    |             WITH SubmitterList (submitterId, name)
    |            AS (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid AS submitterId, 
    |                       Person.firstNames || ' ' || Person.lastName AS name
    |                  FROM ClazzEnrolment
    |                  
    |                       JOIN Person 
    |                       ON Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid
    |                       
    |                 WHERE ? = 0 
    |                   AND clazzEnrolmentClazzUid = ?
    |                   AND clazzEnrolmentActive
    |                   AND clazzEnrolmentRole = 1000
    |              GROUP BY submitterId, name
    |            UNION                 
    |             SELECT DISTINCT CourseGroupMember.cgmGroupNumber AS submitterId,
    |                    ? || ' ' || CourseGroupMember.cgmGroupNumber AS name  
    |               FROM CourseGroupMember
    |                    JOIN CourseGroupSet
    |                    ON CourseGroupSet.cgsUid = ?
    |              WHERE CourseGroupMember.cgmSetUid = CourseGroupSet.cgsUid
    |                AND CourseGroupMember.cgmGroupNumber != 0
    |           GROUP BY submitterId, name
    |            )
    |        
    |        
    |         SELECT COUNT(*) 
    |          FROM SubmitterList
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,groupUid)
    _stmt.setLong(2,clazzUid)
    _stmt.setString(3,group)
    _stmt.setLong(4,groupUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(0) {
        _result.getInt(1)
      }
    }
  }

  override suspend fun getSubmitterUid(
    assignmentUid: Long,
    clazzUid: Long,
    accountPersonUid: Long,
  ): Long = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        WITH ClazzUidDummy(clazzUid) AS
    |             (SELECT ?)
    |             
    |        
    |        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
    |    
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,clazzUid)
    _stmt.setLong(2,assignmentUid)
    _stmt.setLong(3,accountPersonUid)
    _stmt.setLong(4,assignmentUid)
    _stmt.setLong(5,assignmentUid)
    _stmt.setLong(6,accountPersonUid)
    _stmt.setLong(7,assignmentUid)
    _stmt.setLong(8,accountPersonUid)
    _stmt.setLong(9,assignmentUid)
    _stmt.setLong(10,assignmentUid)
    _stmt.setLong(11,accountPersonUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(0L) {
        _result.getLong(1)
      }
    }
  }

  override suspend fun findByUidAsync(uid: Long): ClazzAssignment? =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT * 
    |          FROM ClazzAssignment 
    |         WHERE caUid = ?
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,uid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(null) {
        val _tmp_caUid = _result.getLong("caUid")
        val _tmp_caTitle = _result.getString("caTitle")
        val _tmp_caDescription = _result.getString("caDescription")
        val _tmp_caGroupUid = _result.getLong("caGroupUid")
        val _tmp_caActive = _result.getBoolean("caActive")
        val _tmp_caClassCommentEnabled = _result.getBoolean("caClassCommentEnabled")
        val _tmp_caPrivateCommentsEnabled = _result.getBoolean("caPrivateCommentsEnabled")
        val _tmp_caCompletionCriteria = _result.getInt("caCompletionCriteria")
        val _tmp_caRequireFileSubmission = _result.getBoolean("caRequireFileSubmission")
        val _tmp_caFileType = _result.getInt("caFileType")
        val _tmp_caSizeLimit = _result.getInt("caSizeLimit")
        val _tmp_caNumberOfFiles = _result.getInt("caNumberOfFiles")
        val _tmp_caSubmissionPolicy = _result.getInt("caSubmissionPolicy")
        val _tmp_caMarkingType = _result.getInt("caMarkingType")
        val _tmp_caRequireTextSubmission = _result.getBoolean("caRequireTextSubmission")
        val _tmp_caTextLimitType = _result.getInt("caTextLimitType")
        val _tmp_caTextLimit = _result.getInt("caTextLimit")
        val _tmp_caXObjectUid = _result.getLong("caXObjectUid")
        val _tmp_caClazzUid = _result.getLong("caClazzUid")
        val _tmp_caPeerReviewerCount = _result.getInt("caPeerReviewerCount")
        val _tmp_caLocalChangeSeqNum = _result.getLong("caLocalChangeSeqNum")
        val _tmp_caMasterChangeSeqNum = _result.getLong("caMasterChangeSeqNum")
        val _tmp_caLastChangedBy = _result.getInt("caLastChangedBy")
        val _tmp_caLct = _result.getLong("caLct")
        ClazzAssignment().apply {
          this.caUid = _tmp_caUid
          this.caTitle = _tmp_caTitle
          this.caDescription = _tmp_caDescription
          this.caGroupUid = _tmp_caGroupUid
          this.caActive = _tmp_caActive
          this.caClassCommentEnabled = _tmp_caClassCommentEnabled
          this.caPrivateCommentsEnabled = _tmp_caPrivateCommentsEnabled
          this.caCompletionCriteria = _tmp_caCompletionCriteria
          this.caRequireFileSubmission = _tmp_caRequireFileSubmission
          this.caFileType = _tmp_caFileType
          this.caSizeLimit = _tmp_caSizeLimit
          this.caNumberOfFiles = _tmp_caNumberOfFiles
          this.caSubmissionPolicy = _tmp_caSubmissionPolicy
          this.caMarkingType = _tmp_caMarkingType
          this.caRequireTextSubmission = _tmp_caRequireTextSubmission
          this.caTextLimitType = _tmp_caTextLimitType
          this.caTextLimit = _tmp_caTextLimit
          this.caXObjectUid = _tmp_caXObjectUid
          this.caClazzUid = _tmp_caClazzUid
          this.caPeerReviewerCount = _tmp_caPeerReviewerCount
          this.caLocalChangeSeqNum = _tmp_caLocalChangeSeqNum
          this.caMasterChangeSeqNum = _tmp_caMasterChangeSeqNum
          this.caLastChangedBy = _tmp_caLastChangedBy
          this.caLct = _tmp_caLct
        }
      }
    }
  }

  override fun findByUidAndClazzUidAsFlow(uid: Long, clazzUid: Long): Flow<ClazzAssignment?> =
      _db.doorFlow(arrayOf("ClazzAssignment")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT * 
      |          FROM ClazzAssignment 
      |         WHERE caUid = ?
      |           AND caClazzUid = ?
      |    
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,uid)
      _stmt.setLong(2,clazzUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(null) {
          val _tmp_caUid = _result.getLong("caUid")
          val _tmp_caTitle = _result.getString("caTitle")
          val _tmp_caDescription = _result.getString("caDescription")
          val _tmp_caGroupUid = _result.getLong("caGroupUid")
          val _tmp_caActive = _result.getBoolean("caActive")
          val _tmp_caClassCommentEnabled = _result.getBoolean("caClassCommentEnabled")
          val _tmp_caPrivateCommentsEnabled = _result.getBoolean("caPrivateCommentsEnabled")
          val _tmp_caCompletionCriteria = _result.getInt("caCompletionCriteria")
          val _tmp_caRequireFileSubmission = _result.getBoolean("caRequireFileSubmission")
          val _tmp_caFileType = _result.getInt("caFileType")
          val _tmp_caSizeLimit = _result.getInt("caSizeLimit")
          val _tmp_caNumberOfFiles = _result.getInt("caNumberOfFiles")
          val _tmp_caSubmissionPolicy = _result.getInt("caSubmissionPolicy")
          val _tmp_caMarkingType = _result.getInt("caMarkingType")
          val _tmp_caRequireTextSubmission = _result.getBoolean("caRequireTextSubmission")
          val _tmp_caTextLimitType = _result.getInt("caTextLimitType")
          val _tmp_caTextLimit = _result.getInt("caTextLimit")
          val _tmp_caXObjectUid = _result.getLong("caXObjectUid")
          val _tmp_caClazzUid = _result.getLong("caClazzUid")
          val _tmp_caPeerReviewerCount = _result.getInt("caPeerReviewerCount")
          val _tmp_caLocalChangeSeqNum = _result.getLong("caLocalChangeSeqNum")
          val _tmp_caMasterChangeSeqNum = _result.getLong("caMasterChangeSeqNum")
          val _tmp_caLastChangedBy = _result.getInt("caLastChangedBy")
          val _tmp_caLct = _result.getLong("caLct")
          ClazzAssignment().apply {
            this.caUid = _tmp_caUid
            this.caTitle = _tmp_caTitle
            this.caDescription = _tmp_caDescription
            this.caGroupUid = _tmp_caGroupUid
            this.caActive = _tmp_caActive
            this.caClassCommentEnabled = _tmp_caClassCommentEnabled
            this.caPrivateCommentsEnabled = _tmp_caPrivateCommentsEnabled
            this.caCompletionCriteria = _tmp_caCompletionCriteria
            this.caRequireFileSubmission = _tmp_caRequireFileSubmission
            this.caFileType = _tmp_caFileType
            this.caSizeLimit = _tmp_caSizeLimit
            this.caNumberOfFiles = _tmp_caNumberOfFiles
            this.caSubmissionPolicy = _tmp_caSubmissionPolicy
            this.caMarkingType = _tmp_caMarkingType
            this.caRequireTextSubmission = _tmp_caRequireTextSubmission
            this.caTextLimitType = _tmp_caTextLimitType
            this.caTextLimit = _tmp_caTextLimit
            this.caXObjectUid = _tmp_caXObjectUid
            this.caClazzUid = _tmp_caClazzUid
            this.caPeerReviewerCount = _tmp_caPeerReviewerCount
            this.caLocalChangeSeqNum = _tmp_caLocalChangeSeqNum
            this.caMasterChangeSeqNum = _tmp_caMasterChangeSeqNum
            this.caLastChangedBy = _tmp_caLastChangedBy
            this.caLct = _tmp_caLct
          }
        }
      }
    }
  }

  override suspend fun findByUidAndClazzUidAsync(assignmentUid: Long, clazzUid: Long):
      ClazzAssignment? = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT ClazzAssignment.* 
    |          FROM ClazzAssignment 
    |         WHERE ClazzAssignment.caUid = ?
    |           AND ClazzAssignment.caClazzUid = ?
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,assignmentUid)
    _stmt.setLong(2,clazzUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(null) {
        val _tmp_caUid = _result.getLong("caUid")
        val _tmp_caTitle = _result.getString("caTitle")
        val _tmp_caDescription = _result.getString("caDescription")
        val _tmp_caGroupUid = _result.getLong("caGroupUid")
        val _tmp_caActive = _result.getBoolean("caActive")
        val _tmp_caClassCommentEnabled = _result.getBoolean("caClassCommentEnabled")
        val _tmp_caPrivateCommentsEnabled = _result.getBoolean("caPrivateCommentsEnabled")
        val _tmp_caCompletionCriteria = _result.getInt("caCompletionCriteria")
        val _tmp_caRequireFileSubmission = _result.getBoolean("caRequireFileSubmission")
        val _tmp_caFileType = _result.getInt("caFileType")
        val _tmp_caSizeLimit = _result.getInt("caSizeLimit")
        val _tmp_caNumberOfFiles = _result.getInt("caNumberOfFiles")
        val _tmp_caSubmissionPolicy = _result.getInt("caSubmissionPolicy")
        val _tmp_caMarkingType = _result.getInt("caMarkingType")
        val _tmp_caRequireTextSubmission = _result.getBoolean("caRequireTextSubmission")
        val _tmp_caTextLimitType = _result.getInt("caTextLimitType")
        val _tmp_caTextLimit = _result.getInt("caTextLimit")
        val _tmp_caXObjectUid = _result.getLong("caXObjectUid")
        val _tmp_caClazzUid = _result.getLong("caClazzUid")
        val _tmp_caPeerReviewerCount = _result.getInt("caPeerReviewerCount")
        val _tmp_caLocalChangeSeqNum = _result.getLong("caLocalChangeSeqNum")
        val _tmp_caMasterChangeSeqNum = _result.getLong("caMasterChangeSeqNum")
        val _tmp_caLastChangedBy = _result.getInt("caLastChangedBy")
        val _tmp_caLct = _result.getLong("caLct")
        ClazzAssignment().apply {
          this.caUid = _tmp_caUid
          this.caTitle = _tmp_caTitle
          this.caDescription = _tmp_caDescription
          this.caGroupUid = _tmp_caGroupUid
          this.caActive = _tmp_caActive
          this.caClassCommentEnabled = _tmp_caClassCommentEnabled
          this.caPrivateCommentsEnabled = _tmp_caPrivateCommentsEnabled
          this.caCompletionCriteria = _tmp_caCompletionCriteria
          this.caRequireFileSubmission = _tmp_caRequireFileSubmission
          this.caFileType = _tmp_caFileType
          this.caSizeLimit = _tmp_caSizeLimit
          this.caNumberOfFiles = _tmp_caNumberOfFiles
          this.caSubmissionPolicy = _tmp_caSubmissionPolicy
          this.caMarkingType = _tmp_caMarkingType
          this.caRequireTextSubmission = _tmp_caRequireTextSubmission
          this.caTextLimitType = _tmp_caTextLimitType
          this.caTextLimit = _tmp_caTextLimit
          this.caXObjectUid = _tmp_caXObjectUid
          this.caClazzUid = _tmp_caClazzUid
          this.caPeerReviewerCount = _tmp_caPeerReviewerCount
          this.caLocalChangeSeqNum = _tmp_caLocalChangeSeqNum
          this.caMasterChangeSeqNum = _tmp_caMasterChangeSeqNum
          this.caLastChangedBy = _tmp_caLastChangedBy
          this.caLct = _tmp_caLct
        }
      }
    }
  }

  override suspend fun getGroupUidFromAssignment(uid: Long): Long =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |          SELECT COALESCE((
    |           SELECT caGroupUid
    |           FROM ClazzAssignment
    |          WHERE caUid = ?),-1)
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,uid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(0L) {
        _result.getLong(1)
      }
    }
  }

  override suspend fun getMarkingTypeFromAssignment(uid: Long): Int =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |          SELECT COALESCE((
    |           SELECT caMarkingType
    |           FROM ClazzAssignment
    |          WHERE caUid = ?),-1)
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,uid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(0) {
        _result.getInt(1)
      }
    }
  }

  override suspend fun findByUidWithBlockAsync(uid: Long): ClazzAssignmentAndBlock? =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT * 
    |          FROM ClazzAssignment
    |               LEFT JOIN CourseBlock
    |               ON CourseBlock.cbEntityUid = ClazzAssignment.caUid
    |               AND CourseBlock.cbType = 103
    |         WHERE caUid = ?
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,uid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(null) {
        var _tmp_ClazzAssignment_nullCount = 0
        val _tmp_caUid = _result.getLong("caUid")
        if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
        val _tmp_caTitle = _result.getString("caTitle")
        if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
        val _tmp_caDescription = _result.getString("caDescription")
        if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
        val _tmp_caGroupUid = _result.getLong("caGroupUid")
        if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
        val _tmp_caActive = _result.getBoolean("caActive")
        if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
        val _tmp_caClassCommentEnabled = _result.getBoolean("caClassCommentEnabled")
        if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
        val _tmp_caPrivateCommentsEnabled = _result.getBoolean("caPrivateCommentsEnabled")
        if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
        val _tmp_caCompletionCriteria = _result.getInt("caCompletionCriteria")
        if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
        val _tmp_caRequireFileSubmission = _result.getBoolean("caRequireFileSubmission")
        if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
        val _tmp_caFileType = _result.getInt("caFileType")
        if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
        val _tmp_caSizeLimit = _result.getInt("caSizeLimit")
        if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
        val _tmp_caNumberOfFiles = _result.getInt("caNumberOfFiles")
        if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
        val _tmp_caSubmissionPolicy = _result.getInt("caSubmissionPolicy")
        if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
        val _tmp_caMarkingType = _result.getInt("caMarkingType")
        if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
        val _tmp_caRequireTextSubmission = _result.getBoolean("caRequireTextSubmission")
        if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
        val _tmp_caTextLimitType = _result.getInt("caTextLimitType")
        if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
        val _tmp_caTextLimit = _result.getInt("caTextLimit")
        if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
        val _tmp_caXObjectUid = _result.getLong("caXObjectUid")
        if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
        val _tmp_caClazzUid = _result.getLong("caClazzUid")
        if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
        val _tmp_caPeerReviewerCount = _result.getInt("caPeerReviewerCount")
        if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
        val _tmp_caLocalChangeSeqNum = _result.getLong("caLocalChangeSeqNum")
        if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
        val _tmp_caMasterChangeSeqNum = _result.getLong("caMasterChangeSeqNum")
        if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
        val _tmp_caLastChangedBy = _result.getInt("caLastChangedBy")
        if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
        val _tmp_caLct = _result.getLong("caLct")
        if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
        val _tmp_ClazzAssignment_isAllNull = _tmp_ClazzAssignment_nullCount == 24
        var _tmp_CourseBlock_nullCount = 0
        val _tmp_cbUid = _result.getLong("cbUid")
        if(_result.wasNull()) _tmp_CourseBlock_nullCount++
        val _tmp_cbType = _result.getInt("cbType")
        if(_result.wasNull()) _tmp_CourseBlock_nullCount++
        val _tmp_cbIndentLevel = _result.getInt("cbIndentLevel")
        if(_result.wasNull()) _tmp_CourseBlock_nullCount++
        val _tmp_cbModuleParentBlockUid = _result.getLong("cbModuleParentBlockUid")
        if(_result.wasNull()) _tmp_CourseBlock_nullCount++
        val _tmp_cbTitle = _result.getString("cbTitle")
        if(_result.wasNull()) _tmp_CourseBlock_nullCount++
        val _tmp_cbDescription = _result.getString("cbDescription")
        if(_result.wasNull()) _tmp_CourseBlock_nullCount++
        val _tmp_cbCompletionCriteria = _result.getInt("cbCompletionCriteria")
        if(_result.wasNull()) _tmp_CourseBlock_nullCount++
        val _tmp_cbHideUntilDate = _result.getLong("cbHideUntilDate")
        if(_result.wasNull()) _tmp_CourseBlock_nullCount++
        val _tmp_cbDeadlineDate = _result.getLong("cbDeadlineDate")
        if(_result.wasNull()) _tmp_CourseBlock_nullCount++
        val _tmp_cbLateSubmissionPenalty = _result.getInt("cbLateSubmissionPenalty")
        if(_result.wasNull()) _tmp_CourseBlock_nullCount++
        val _tmp_cbGracePeriodDate = _result.getLong("cbGracePeriodDate")
        if(_result.wasNull()) _tmp_CourseBlock_nullCount++
        val _tmp_cbMaxPoints = _result.getFloatNullable("cbMaxPoints")
        if(_result.wasNull()) _tmp_CourseBlock_nullCount++
        val _tmp_cbMinPoints = _result.getFloatNullable("cbMinPoints")
        if(_result.wasNull()) _tmp_CourseBlock_nullCount++
        val _tmp_cbIndex = _result.getInt("cbIndex")
        if(_result.wasNull()) _tmp_CourseBlock_nullCount++
        val _tmp_cbClazzUid = _result.getLong("cbClazzUid")
        if(_result.wasNull()) _tmp_CourseBlock_nullCount++
        val _tmp_cbClazzSourcedId = _result.getString("cbClazzSourcedId")
        if(_result.wasNull()) _tmp_CourseBlock_nullCount++
        val _tmp_cbActive = _result.getBoolean("cbActive")
        if(_result.wasNull()) _tmp_CourseBlock_nullCount++
        val _tmp_cbHidden = _result.getBoolean("cbHidden")
        if(_result.wasNull()) _tmp_CourseBlock_nullCount++
        val _tmp_cbEntityUid = _result.getLong("cbEntityUid")
        if(_result.wasNull()) _tmp_CourseBlock_nullCount++
        val _tmp_cbLct = _result.getLong("cbLct")
        if(_result.wasNull()) _tmp_CourseBlock_nullCount++
        val _tmp_cbSourcedId = _result.getString("cbSourcedId")
        if(_result.wasNull()) _tmp_CourseBlock_nullCount++
        val _tmp_cbMetadata = _result.getString("cbMetadata")
        if(_result.wasNull()) _tmp_CourseBlock_nullCount++
        val _tmp_cbCreatedByAppId = _result.getString("cbCreatedByAppId")
        if(_result.wasNull()) _tmp_CourseBlock_nullCount++
        val _tmp_CourseBlock_isAllNull = _tmp_CourseBlock_nullCount == 23
        ClazzAssignmentAndBlock().apply {
          if(!_tmp_ClazzAssignment_isAllNull) {
            this.assignment = ClazzAssignment().apply {
              this.caUid = _tmp_caUid
              this.caTitle = _tmp_caTitle
              this.caDescription = _tmp_caDescription
              this.caGroupUid = _tmp_caGroupUid
              this.caActive = _tmp_caActive
              this.caClassCommentEnabled = _tmp_caClassCommentEnabled
              this.caPrivateCommentsEnabled = _tmp_caPrivateCommentsEnabled
              this.caCompletionCriteria = _tmp_caCompletionCriteria
              this.caRequireFileSubmission = _tmp_caRequireFileSubmission
              this.caFileType = _tmp_caFileType
              this.caSizeLimit = _tmp_caSizeLimit
              this.caNumberOfFiles = _tmp_caNumberOfFiles
              this.caSubmissionPolicy = _tmp_caSubmissionPolicy
              this.caMarkingType = _tmp_caMarkingType
              this.caRequireTextSubmission = _tmp_caRequireTextSubmission
              this.caTextLimitType = _tmp_caTextLimitType
              this.caTextLimit = _tmp_caTextLimit
              this.caXObjectUid = _tmp_caXObjectUid
              this.caClazzUid = _tmp_caClazzUid
              this.caPeerReviewerCount = _tmp_caPeerReviewerCount
              this.caLocalChangeSeqNum = _tmp_caLocalChangeSeqNum
              this.caMasterChangeSeqNum = _tmp_caMasterChangeSeqNum
              this.caLastChangedBy = _tmp_caLastChangedBy
              this.caLct = _tmp_caLct
            }
          }
          if(!_tmp_CourseBlock_isAllNull) {
            this.block = CourseBlock().apply {
              this.cbUid = _tmp_cbUid
              this.cbType = _tmp_cbType
              this.cbIndentLevel = _tmp_cbIndentLevel
              this.cbModuleParentBlockUid = _tmp_cbModuleParentBlockUid
              this.cbTitle = _tmp_cbTitle
              this.cbDescription = _tmp_cbDescription
              this.cbCompletionCriteria = _tmp_cbCompletionCriteria
              this.cbHideUntilDate = _tmp_cbHideUntilDate
              this.cbDeadlineDate = _tmp_cbDeadlineDate
              this.cbLateSubmissionPenalty = _tmp_cbLateSubmissionPenalty
              this.cbGracePeriodDate = _tmp_cbGracePeriodDate
              this.cbMaxPoints = _tmp_cbMaxPoints
              this.cbMinPoints = _tmp_cbMinPoints
              this.cbIndex = _tmp_cbIndex
              this.cbClazzUid = _tmp_cbClazzUid
              this.cbClazzSourcedId = _tmp_cbClazzSourcedId
              this.cbActive = _tmp_cbActive
              this.cbHidden = _tmp_cbHidden
              this.cbEntityUid = _tmp_cbEntityUid
              this.cbLct = _tmp_cbLct
              this.cbSourcedId = _tmp_cbSourcedId
              this.cbMetadata = _tmp_cbMetadata
              this.cbCreatedByAppId = _tmp_cbCreatedByAppId
            }
          }
        }
      }
    }
  }

  override fun findClazzAssignment(): ClazzAssignment? =
      _db.prepareAndUseStatement(PreparedStatementConfig(
    sql = """
    |
    |        SELECT * 
    |          FROM ClazzAssignment LIMIT 1
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.executeQuery().useResults{ _result -> 
      _result.mapNextRow(null) {
        val _tmp_caUid = _result.getLong("caUid")
        val _tmp_caTitle = _result.getString("caTitle")
        val _tmp_caDescription = _result.getString("caDescription")
        val _tmp_caGroupUid = _result.getLong("caGroupUid")
        val _tmp_caActive = _result.getBoolean("caActive")
        val _tmp_caClassCommentEnabled = _result.getBoolean("caClassCommentEnabled")
        val _tmp_caPrivateCommentsEnabled = _result.getBoolean("caPrivateCommentsEnabled")
        val _tmp_caCompletionCriteria = _result.getInt("caCompletionCriteria")
        val _tmp_caRequireFileSubmission = _result.getBoolean("caRequireFileSubmission")
        val _tmp_caFileType = _result.getInt("caFileType")
        val _tmp_caSizeLimit = _result.getInt("caSizeLimit")
        val _tmp_caNumberOfFiles = _result.getInt("caNumberOfFiles")
        val _tmp_caSubmissionPolicy = _result.getInt("caSubmissionPolicy")
        val _tmp_caMarkingType = _result.getInt("caMarkingType")
        val _tmp_caRequireTextSubmission = _result.getBoolean("caRequireTextSubmission")
        val _tmp_caTextLimitType = _result.getInt("caTextLimitType")
        val _tmp_caTextLimit = _result.getInt("caTextLimit")
        val _tmp_caXObjectUid = _result.getLong("caXObjectUid")
        val _tmp_caClazzUid = _result.getLong("caClazzUid")
        val _tmp_caPeerReviewerCount = _result.getInt("caPeerReviewerCount")
        val _tmp_caLocalChangeSeqNum = _result.getLong("caLocalChangeSeqNum")
        val _tmp_caMasterChangeSeqNum = _result.getLong("caMasterChangeSeqNum")
        val _tmp_caLastChangedBy = _result.getInt("caLastChangedBy")
        val _tmp_caLct = _result.getLong("caLct")
        ClazzAssignment().apply {
          this.caUid = _tmp_caUid
          this.caTitle = _tmp_caTitle
          this.caDescription = _tmp_caDescription
          this.caGroupUid = _tmp_caGroupUid
          this.caActive = _tmp_caActive
          this.caClassCommentEnabled = _tmp_caClassCommentEnabled
          this.caPrivateCommentsEnabled = _tmp_caPrivateCommentsEnabled
          this.caCompletionCriteria = _tmp_caCompletionCriteria
          this.caRequireFileSubmission = _tmp_caRequireFileSubmission
          this.caFileType = _tmp_caFileType
          this.caSizeLimit = _tmp_caSizeLimit
          this.caNumberOfFiles = _tmp_caNumberOfFiles
          this.caSubmissionPolicy = _tmp_caSubmissionPolicy
          this.caMarkingType = _tmp_caMarkingType
          this.caRequireTextSubmission = _tmp_caRequireTextSubmission
          this.caTextLimitType = _tmp_caTextLimitType
          this.caTextLimit = _tmp_caTextLimit
          this.caXObjectUid = _tmp_caXObjectUid
          this.caClazzUid = _tmp_caClazzUid
          this.caPeerReviewerCount = _tmp_caPeerReviewerCount
          this.caLocalChangeSeqNum = _tmp_caLocalChangeSeqNum
          this.caMasterChangeSeqNum = _tmp_caMasterChangeSeqNum
          this.caLastChangedBy = _tmp_caLastChangedBy
          this.caLct = _tmp_caLct
        }
      }
    }
  }

  override fun findByUidLive(uid: Long): Flow<ClazzAssignment?> =
      _db.doorFlow(arrayOf("ClazzAssignment")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT * 
      |                      FROM ClazzAssignment 
      |                     WHERE caUid = ?
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,uid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(null) {
          val _tmp_caUid = _result.getLong("caUid")
          val _tmp_caTitle = _result.getString("caTitle")
          val _tmp_caDescription = _result.getString("caDescription")
          val _tmp_caGroupUid = _result.getLong("caGroupUid")
          val _tmp_caActive = _result.getBoolean("caActive")
          val _tmp_caClassCommentEnabled = _result.getBoolean("caClassCommentEnabled")
          val _tmp_caPrivateCommentsEnabled = _result.getBoolean("caPrivateCommentsEnabled")
          val _tmp_caCompletionCriteria = _result.getInt("caCompletionCriteria")
          val _tmp_caRequireFileSubmission = _result.getBoolean("caRequireFileSubmission")
          val _tmp_caFileType = _result.getInt("caFileType")
          val _tmp_caSizeLimit = _result.getInt("caSizeLimit")
          val _tmp_caNumberOfFiles = _result.getInt("caNumberOfFiles")
          val _tmp_caSubmissionPolicy = _result.getInt("caSubmissionPolicy")
          val _tmp_caMarkingType = _result.getInt("caMarkingType")
          val _tmp_caRequireTextSubmission = _result.getBoolean("caRequireTextSubmission")
          val _tmp_caTextLimitType = _result.getInt("caTextLimitType")
          val _tmp_caTextLimit = _result.getInt("caTextLimit")
          val _tmp_caXObjectUid = _result.getLong("caXObjectUid")
          val _tmp_caClazzUid = _result.getLong("caClazzUid")
          val _tmp_caPeerReviewerCount = _result.getInt("caPeerReviewerCount")
          val _tmp_caLocalChangeSeqNum = _result.getLong("caLocalChangeSeqNum")
          val _tmp_caMasterChangeSeqNum = _result.getLong("caMasterChangeSeqNum")
          val _tmp_caLastChangedBy = _result.getInt("caLastChangedBy")
          val _tmp_caLct = _result.getLong("caLct")
          ClazzAssignment().apply {
            this.caUid = _tmp_caUid
            this.caTitle = _tmp_caTitle
            this.caDescription = _tmp_caDescription
            this.caGroupUid = _tmp_caGroupUid
            this.caActive = _tmp_caActive
            this.caClassCommentEnabled = _tmp_caClassCommentEnabled
            this.caPrivateCommentsEnabled = _tmp_caPrivateCommentsEnabled
            this.caCompletionCriteria = _tmp_caCompletionCriteria
            this.caRequireFileSubmission = _tmp_caRequireFileSubmission
            this.caFileType = _tmp_caFileType
            this.caSizeLimit = _tmp_caSizeLimit
            this.caNumberOfFiles = _tmp_caNumberOfFiles
            this.caSubmissionPolicy = _tmp_caSubmissionPolicy
            this.caMarkingType = _tmp_caMarkingType
            this.caRequireTextSubmission = _tmp_caRequireTextSubmission
            this.caTextLimitType = _tmp_caTextLimitType
            this.caTextLimit = _tmp_caTextLimit
            this.caXObjectUid = _tmp_caXObjectUid
            this.caClazzUid = _tmp_caClazzUid
            this.caPeerReviewerCount = _tmp_caPeerReviewerCount
            this.caLocalChangeSeqNum = _tmp_caLocalChangeSeqNum
            this.caMasterChangeSeqNum = _tmp_caMasterChangeSeqNum
            this.caLastChangedBy = _tmp_caLastChangedBy
            this.caLct = _tmp_caLct
          }
        }
      }
    }
  }

  override suspend fun updateActiveByList(
    uidList: List<Long>,
    active: Boolean,
    changeTime: Long,
  ) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        UPDATE ClazzAssignment
      |           SET caActive = ?,
      |               caLct = ?
      |         WHERE caUid IN (?)   
      |    
      """.trimMargin(),
      hasListParams = true,
      readOnly = false,)
    ) { _stmt -> 
      _stmt.setBoolean(1,active)
      _stmt.setLong(2,changeTime)
      _stmt.setArray(3, _stmt.getConnection().createArrayOrProxyArrayOf("BIGINT",
          uidList.toTypedArray()))
      _stmt.executeUpdateAsyncKmp()
    }
  }

  override fun findAssignmentCourseBlockAndSubmitterUidAsFlow(
    assignmentUid: Long,
    clazzUid: Long,
    accountPersonUid: Long,
  ): Flow<ClazzAssignmentCourseBlockAndSubmitterUid?> = _db.doorFlow(arrayOf("Person",
      "ClazzAssignment", "CourseBlock", "CourseGroupMember", "ClazzEnrolment", "CoursePermission",
      "SystemPermission", "CourseBlockPicture")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        WITH PersonIsStudent(isStudent)
      |             AS (SELECT EXISTS(
      |                        SELECT ClazzEnrolment.clazzEnrolmentPersonUid
      |                           FROM ClazzEnrolment
      |                          WHERE ClazzEnrolment.clazzEnrolmentPersonUid = ?
      |                            AND ClazzEnrolment.clazzEnrolmentClazzUid = ?
      |                            AND ClazzEnrolment.clazzEnrolmentRole = 1000))
      |                        
      |        SELECT ClazzAssignment.*,
      |               CourseBlock.*,
      |               CourseBlockPicture.*,
      |               CourseGroupSet.*,
      |               (
      |        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
      |    ) AS submitterUid,
      |               
      |               ((
      |             /* 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 hasModeratePermission
      |                   
      |          FROM ClazzAssignment
      |               JOIN CourseBlock
      |                    ON CourseBlock.cbEntityUid = ClazzAssignment.caUid
      |               LEFT JOIN CourseBlockPicture
      |                    ON CourseBlockPicture.cbpUid = CourseBlock.cbUid
      |               LEFT JOIN CourseGroupSet
      |                    ON CourseGroupSet.cgsUid = ClazzAssignment.caGroupUid
      |         WHERE ClazzAssignment.caUid = ?
      |           AND ClazzAssignment.caClazzUid = ?
      |           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 & 
      |         1
      |                
      |        ) > 0)
      |              OR EXISTS(SELECT SystemPermission.spUid
      |                          FROM SystemPermission
      |                         WHERE SystemPermission.spToPersonUid = ?
      |                           AND (SystemPermission.spPermissionsFlag & 
      |     1
      |                
      |        ) > 0)
      |               )
      |    )
      |    
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,accountPersonUid)
      _stmt.setLong(2,clazzUid)
      _stmt.setLong(3,assignmentUid)
      _stmt.setLong(4,accountPersonUid)
      _stmt.setLong(5,assignmentUid)
      _stmt.setLong(6,assignmentUid)
      _stmt.setLong(7,accountPersonUid)
      _stmt.setLong(8,assignmentUid)
      _stmt.setLong(9,accountPersonUid)
      _stmt.setLong(10,assignmentUid)
      _stmt.setLong(11,assignmentUid)
      _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,accountPersonUid)
      _stmt.setLong(19,assignmentUid)
      _stmt.setLong(20,clazzUid)
      _stmt.setLong(21,clazzUid)
      _stmt.setLong(22,accountPersonUid)
      _stmt.setLong(23,accountPersonUid)
      _stmt.setLong(24,clazzUid)
      _stmt.setLong(25,accountPersonUid)
      _stmt.setLong(26,accountPersonUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(null) {
          val _tmp_submitterUid = _result.getLong("submitterUid")
          val _tmp_hasModeratePermission = _result.getBoolean("hasModeratePermission")
          var _tmp_ClazzAssignment_nullCount = 0
          val _tmp_caUid = _result.getLong("caUid")
          if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
          val _tmp_caTitle = _result.getString("caTitle")
          if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
          val _tmp_caDescription = _result.getString("caDescription")
          if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
          val _tmp_caGroupUid = _result.getLong("caGroupUid")
          if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
          val _tmp_caActive = _result.getBoolean("caActive")
          if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
          val _tmp_caClassCommentEnabled = _result.getBoolean("caClassCommentEnabled")
          if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
          val _tmp_caPrivateCommentsEnabled = _result.getBoolean("caPrivateCommentsEnabled")
          if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
          val _tmp_caCompletionCriteria = _result.getInt("caCompletionCriteria")
          if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
          val _tmp_caRequireFileSubmission = _result.getBoolean("caRequireFileSubmission")
          if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
          val _tmp_caFileType = _result.getInt("caFileType")
          if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
          val _tmp_caSizeLimit = _result.getInt("caSizeLimit")
          if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
          val _tmp_caNumberOfFiles = _result.getInt("caNumberOfFiles")
          if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
          val _tmp_caSubmissionPolicy = _result.getInt("caSubmissionPolicy")
          if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
          val _tmp_caMarkingType = _result.getInt("caMarkingType")
          if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
          val _tmp_caRequireTextSubmission = _result.getBoolean("caRequireTextSubmission")
          if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
          val _tmp_caTextLimitType = _result.getInt("caTextLimitType")
          if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
          val _tmp_caTextLimit = _result.getInt("caTextLimit")
          if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
          val _tmp_caXObjectUid = _result.getLong("caXObjectUid")
          if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
          val _tmp_caClazzUid = _result.getLong("caClazzUid")
          if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
          val _tmp_caPeerReviewerCount = _result.getInt("caPeerReviewerCount")
          if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
          val _tmp_caLocalChangeSeqNum = _result.getLong("caLocalChangeSeqNum")
          if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
          val _tmp_caMasterChangeSeqNum = _result.getLong("caMasterChangeSeqNum")
          if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
          val _tmp_caLastChangedBy = _result.getInt("caLastChangedBy")
          if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
          val _tmp_caLct = _result.getLong("caLct")
          if(_result.wasNull()) _tmp_ClazzAssignment_nullCount++
          val _tmp_ClazzAssignment_isAllNull = _tmp_ClazzAssignment_nullCount == 24
          var _tmp_CourseBlock_nullCount = 0
          val _tmp_cbUid = _result.getLong("cbUid")
          if(_result.wasNull()) _tmp_CourseBlock_nullCount++
          val _tmp_cbType = _result.getInt("cbType")
          if(_result.wasNull()) _tmp_CourseBlock_nullCount++
          val _tmp_cbIndentLevel = _result.getInt("cbIndentLevel")
          if(_result.wasNull()) _tmp_CourseBlock_nullCount++
          val _tmp_cbModuleParentBlockUid = _result.getLong("cbModuleParentBlockUid")
          if(_result.wasNull()) _tmp_CourseBlock_nullCount++
          val _tmp_cbTitle = _result.getString("cbTitle")
          if(_result.wasNull()) _tmp_CourseBlock_nullCount++
          val _tmp_cbDescription = _result.getString("cbDescription")
          if(_result.wasNull()) _tmp_CourseBlock_nullCount++
          val _tmp_cbCompletionCriteria = _result.getInt("cbCompletionCriteria")
          if(_result.wasNull()) _tmp_CourseBlock_nullCount++
          val _tmp_cbHideUntilDate = _result.getLong("cbHideUntilDate")
          if(_result.wasNull()) _tmp_CourseBlock_nullCount++
          val _tmp_cbDeadlineDate = _result.getLong("cbDeadlineDate")
          if(_result.wasNull()) _tmp_CourseBlock_nullCount++
          val _tmp_cbLateSubmissionPenalty = _result.getInt("cbLateSubmissionPenalty")
          if(_result.wasNull()) _tmp_CourseBlock_nullCount++
          val _tmp_cbGracePeriodDate = _result.getLong("cbGracePeriodDate")
          if(_result.wasNull()) _tmp_CourseBlock_nullCount++
          val _tmp_cbMaxPoints = _result.getFloatNullable("cbMaxPoints")
          if(_result.wasNull()) _tmp_CourseBlock_nullCount++
          val _tmp_cbMinPoints = _result.getFloatNullable("cbMinPoints")
          if(_result.wasNull()) _tmp_CourseBlock_nullCount++
          val _tmp_cbIndex = _result.getInt("cbIndex")
          if(_result.wasNull()) _tmp_CourseBlock_nullCount++
          val _tmp_cbClazzUid = _result.getLong("cbClazzUid")
          if(_result.wasNull()) _tmp_CourseBlock_nullCount++
          val _tmp_cbClazzSourcedId = _result.getString("cbClazzSourcedId")
          if(_result.wasNull()) _tmp_CourseBlock_nullCount++
          val _tmp_cbActive = _result.getBoolean("cbActive")
          if(_result.wasNull()) _tmp_CourseBlock_nullCount++
          val _tmp_cbHidden = _result.getBoolean("cbHidden")
          if(_result.wasNull()) _tmp_CourseBlock_nullCount++
          val _tmp_cbEntityUid = _result.getLong("cbEntityUid")
          if(_result.wasNull()) _tmp_CourseBlock_nullCount++
          val _tmp_cbLct = _result.getLong("cbLct")
          if(_result.wasNull()) _tmp_CourseBlock_nullCount++
          val _tmp_cbSourcedId = _result.getString("cbSourcedId")
          if(_result.wasNull()) _tmp_CourseBlock_nullCount++
          val _tmp_cbMetadata = _result.getString("cbMetadata")
          if(_result.wasNull()) _tmp_CourseBlock_nullCount++
          val _tmp_cbCreatedByAppId = _result.getString("cbCreatedByAppId")
          if(_result.wasNull()) _tmp_CourseBlock_nullCount++
          val _tmp_CourseBlock_isAllNull = _tmp_CourseBlock_nullCount == 23
          var _tmp_CourseBlockPicture_nullCount = 0
          val _tmp_cbpUid = _result.getLong("cbpUid")
          if(_result.wasNull()) _tmp_CourseBlockPicture_nullCount++
          val _tmp_cbpLct = _result.getLong("cbpLct")
          if(_result.wasNull()) _tmp_CourseBlockPicture_nullCount++
          val _tmp_cbpPictureUri = _result.getString("cbpPictureUri")
          if(_result.wasNull()) _tmp_CourseBlockPicture_nullCount++
          val _tmp_cbpThumbnailUri = _result.getString("cbpThumbnailUri")
          if(_result.wasNull()) _tmp_CourseBlockPicture_nullCount++
          val _tmp_CourseBlockPicture_isAllNull = _tmp_CourseBlockPicture_nullCount == 4
          var _tmp_CourseGroupSet_nullCount = 0
          val _tmp_cgsUid = _result.getLong("cgsUid")
          if(_result.wasNull()) _tmp_CourseGroupSet_nullCount++
          val _tmp_cgsName = _result.getString("cgsName")
          if(_result.wasNull()) _tmp_CourseGroupSet_nullCount++
          val _tmp_cgsTotalGroups = _result.getInt("cgsTotalGroups")
          if(_result.wasNull()) _tmp_CourseGroupSet_nullCount++
          val _tmp_cgsActive = _result.getBoolean("cgsActive")
          if(_result.wasNull()) _tmp_CourseGroupSet_nullCount++
          val _tmp_cgsClazzUid = _result.getLong("cgsClazzUid")
          if(_result.wasNull()) _tmp_CourseGroupSet_nullCount++
          val _tmp_cgsLct = _result.getLong("cgsLct")
          if(_result.wasNull()) _tmp_CourseGroupSet_nullCount++
          val _tmp_CourseGroupSet_isAllNull = _tmp_CourseGroupSet_nullCount == 6
          ClazzAssignmentCourseBlockAndSubmitterUid().apply {
            this.submitterUid = _tmp_submitterUid
            this.hasModeratePermission = _tmp_hasModeratePermission
            if(!_tmp_ClazzAssignment_isAllNull) {
              this.clazzAssignment = ClazzAssignment().apply {
                this.caUid = _tmp_caUid
                this.caTitle = _tmp_caTitle
                this.caDescription = _tmp_caDescription
                this.caGroupUid = _tmp_caGroupUid
                this.caActive = _tmp_caActive
                this.caClassCommentEnabled = _tmp_caClassCommentEnabled
                this.caPrivateCommentsEnabled = _tmp_caPrivateCommentsEnabled
                this.caCompletionCriteria = _tmp_caCompletionCriteria
                this.caRequireFileSubmission = _tmp_caRequireFileSubmission
                this.caFileType = _tmp_caFileType
                this.caSizeLimit = _tmp_caSizeLimit
                this.caNumberOfFiles = _tmp_caNumberOfFiles
                this.caSubmissionPolicy = _tmp_caSubmissionPolicy
                this.caMarkingType = _tmp_caMarkingType
                this.caRequireTextSubmission = _tmp_caRequireTextSubmission
                this.caTextLimitType = _tmp_caTextLimitType
                this.caTextLimit = _tmp_caTextLimit
                this.caXObjectUid = _tmp_caXObjectUid
                this.caClazzUid = _tmp_caClazzUid
                this.caPeerReviewerCount = _tmp_caPeerReviewerCount
                this.caLocalChangeSeqNum = _tmp_caLocalChangeSeqNum
                this.caMasterChangeSeqNum = _tmp_caMasterChangeSeqNum
                this.caLastChangedBy = _tmp_caLastChangedBy
                this.caLct = _tmp_caLct
              }
            }
            if(!_tmp_CourseBlock_isAllNull) {
              this.courseBlock = CourseBlock().apply {
                this.cbUid = _tmp_cbUid
                this.cbType = _tmp_cbType
                this.cbIndentLevel = _tmp_cbIndentLevel
                this.cbModuleParentBlockUid = _tmp_cbModuleParentBlockUid
                this.cbTitle = _tmp_cbTitle
                this.cbDescription = _tmp_cbDescription
                this.cbCompletionCriteria = _tmp_cbCompletionCriteria
                this.cbHideUntilDate = _tmp_cbHideUntilDate
                this.cbDeadlineDate = _tmp_cbDeadlineDate
                this.cbLateSubmissionPenalty = _tmp_cbLateSubmissionPenalty
                this.cbGracePeriodDate = _tmp_cbGracePeriodDate
                this.cbMaxPoints = _tmp_cbMaxPoints
                this.cbMinPoints = _tmp_cbMinPoints
                this.cbIndex = _tmp_cbIndex
                this.cbClazzUid = _tmp_cbClazzUid
                this.cbClazzSourcedId = _tmp_cbClazzSourcedId
                this.cbActive = _tmp_cbActive
                this.cbHidden = _tmp_cbHidden
                this.cbEntityUid = _tmp_cbEntityUid
                this.cbLct = _tmp_cbLct
                this.cbSourcedId = _tmp_cbSourcedId
                this.cbMetadata = _tmp_cbMetadata
                this.cbCreatedByAppId = _tmp_cbCreatedByAppId
              }
            }
            if(!_tmp_CourseBlockPicture_isAllNull) {
              this.courseBlockPicture = CourseBlockPicture().apply {
                this.cbpUid = _tmp_cbpUid
                this.cbpLct = _tmp_cbpLct
                this.cbpPictureUri = _tmp_cbpPictureUri
                this.cbpThumbnailUri = _tmp_cbpThumbnailUri
              }
            }
            if(!_tmp_CourseGroupSet_isAllNull) {
              this.courseGroupSet = CourseGroupSet().apply {
                this.cgsUid = _tmp_cgsUid
                this.cgsName = _tmp_cgsName
                this.cgsTotalGroups = _tmp_cgsTotalGroups
                this.cgsActive = _tmp_cgsActive
                this.cgsClazzUid = _tmp_cgsClazzUid
                this.cgsLct = _tmp_cgsLct
              }
            }
          }
        }
      }
    }
  }

  override suspend fun findEnrolmentsByPersonUidAndAssignmentUid(assignmentUid: Long,
      accountPersonUid: Long): List<ClazzEnrolment> =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT ClazzEnrolment.*
    |          FROM ClazzEnrolment
    |         WHERE ClazzEnrolment.clazzEnrolmentClazzUid = 
    |               (SELECT ClazzAssignment.caClazzUid
    |                  FROM ClazzAssignment
    |                 WHERE ClazzAssignment.caUid = ?)
    |           AND ClazzEnrolment.clazzEnrolmentPersonUid = ? 
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,assignmentUid)
    _stmt.setLong(2,accountPersonUid)
    _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 findCourseGroupMembersByPersonUidAndAssignmentUid(assignmentUid: Long,
      accountPersonUid: Long): List<CourseGroupMember> =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT CourseGroupMember.*
    |          FROM CourseGroupMember
    |         WHERE CourseGroupMember.cgmSetUid = 
    |               (SELECT ClazzAssignment.caGroupUid 
    |                  FROM ClazzAssignment
    |                 WHERE ClazzAssignment.caUid = ?)
    |           AND CourseGroupMember.cgmPersonUid = ?
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,assignmentUid)
    _stmt.setLong(2,accountPersonUid)
    _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 findPeerReviewerAllocationsByPersonUidAndAssignmentUid(assignmentUid: Long,
      accountPersonUid: Long): List<PeerReviewerAllocation> =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT PeerReviewerAllocation.*
    |          FROM PeerReviewerAllocation
    |         WHERE PeerReviewerAllocation.praAssignmentUid = ?
    |           AND (
    |                    PeerReviewerAllocation.praMarkerSubmitterUid = ?
    |                 OR PeerReviewerAllocation.praToMarkerSubmitterUid = ?
    |                 OR PeerReviewerAllocation.praMarkerSubmitterUid IN
    |                    (SELECT CourseGroupMember.cgmGroupNumber
    |                       FROM CourseGroupMember
    |                      WHERE CourseGroupMember.cgmSetUid = 
    |                            (SELECT ClazzAssignment.caGroupUid
    |                               FROM ClazzAssignment
    |                              WHERE ClazzAssignment.caUid = ?)
    |                        AND CourseGroupMember.cgmPersonUid = ?)
    |                 OR PeerReviewerAllocation.praToMarkerSubmitterUid IN
    |                    (SELECT CourseGroupMember.cgmGroupNumber
    |                       FROM CourseGroupMember
    |                      WHERE CourseGroupMember.cgmSetUid = 
    |                            (SELECT ClazzAssignment.caGroupUid
    |                               FROM ClazzAssignment
    |                              WHERE ClazzAssignment.caUid = ?)
    |                                AND CourseGroupMember.cgmPersonUid = ?))
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,assignmentUid)
    _stmt.setLong(2,accountPersonUid)
    _stmt.setLong(3,accountPersonUid)
    _stmt.setLong(4,assignmentUid)
    _stmt.setLong(5,accountPersonUid)
    _stmt.setLong(6,assignmentUid)
    _stmt.setLong(7,accountPersonUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_praUid = _result.getLong("praUid")
        val _tmp_praMarkerSubmitterUid = _result.getLong("praMarkerSubmitterUid")
        val _tmp_praToMarkerSubmitterUid = _result.getLong("praToMarkerSubmitterUid")
        val _tmp_praAssignmentUid = _result.getLong("praAssignmentUid")
        val _tmp_praActive = _result.getBoolean("praActive")
        val _tmp_praLct = _result.getLong("praLct")
        PeerReviewerAllocation().apply {
          this.praUid = _tmp_praUid
          this.praMarkerSubmitterUid = _tmp_praMarkerSubmitterUid
          this.praToMarkerSubmitterUid = _tmp_praToMarkerSubmitterUid
          this.praAssignmentUid = _tmp_praAssignmentUid
          this.praActive = _tmp_praActive
          this.praLct = _tmp_praLct
        }
      }
    }
  }

  override suspend fun getLatestSubmissionTimeAllowed(assignmentUid: Long): Long =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        WITH CourseBlockDeadlines(deadline, gracePeriod) AS
    |             (SELECT CourseBlock.cbDeadlineDate AS deadline,
    |                     CourseBlock.cbGracePeriodDate AS gracePeriod
    |                FROM CourseBlock
    |               WHERE CourseBlock.cbEntityUid = ?
    |                 AND CourseBlock.cbType = 103
    |               LIMIT 1)
    |        SELECT CASE
    |               WHEN (SELECT gracePeriod 
    |                       FROM CourseBlockDeadlines)
    |                    BETWEEN 1 AND 7258118400000 THEN (SELECT gracePeriod FROM CourseBlockDeadlines)
    |               ELSE (SELECT deadline FROM CourseBlockDeadlines)
    |               END AS latestSubmissionTimeAllowed
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,assignmentUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(0L) {
        _result.getLong(1)
      }
    }
  }
}
