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.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.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.CourseAssignmentMarkAndMarkerName
import com.ustadmobile.lib.db.composites.PersonAndPicture
import com.ustadmobile.lib.db.entities.AverageCourseAssignmentMark
import com.ustadmobile.lib.db.entities.CourseAssignmentMark
import com.ustadmobile.lib.db.entities.CourseAssignmentMarkWithPersonMarker
import com.ustadmobile.lib.db.entities.Person
import com.ustadmobile.lib.db.entities.PersonPicture
import kotlin.Boolean
import kotlin.IllegalArgumentException
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.collections.List
import kotlinx.coroutines.flow.Flow

public class CourseAssignmentMarkDao_JdbcImpl(
  public val _db: RoomDatabase,
) : CourseAssignmentMarkDao() {
  public val _insertAdapterCourseAssignmentMark_abort: EntityInsertionAdapter<CourseAssignmentMark>
      = object : EntityInsertionAdapter<CourseAssignmentMark>(_db) {
    override fun makeSql(returnsId: Boolean): String = when(dbType) {
      DoorDbType.SQLITE -> {
        "INSERT INTO CourseAssignmentMark (camUid, camAssignmentUid, camSubmitterUid, camMarkerSubmitterUid, camMarkerPersonUid, camMarkerComment, camMark, camMaxMark, camPenalty, camLct, camClazzUid) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
      }
      DoorDbType.POSTGRES ->  {
        "INSERT INTO CourseAssignmentMark (camUid, camAssignmentUid, camSubmitterUid, camMarkerSubmitterUid, camMarkerPersonUid, camMarkerComment, camMark, camMaxMark, camPenalty, camLct, camClazzUid) VALUES(COALESCE(?,nextval('CourseAssignmentMark_camUid_seq')), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" + if(returnsId) { " RETURNING camUid" } else "" 
      }
      else -> {
        throw IllegalArgumentException("Unsupported db type")
      }
    }

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement, entity: CourseAssignmentMark) {
      if(entity.camUid == 0L) {
        stmt.setObject(1, null)
      } else {
        stmt.setLong(1, entity.camUid)
      }
      stmt.setLong(2, entity.camAssignmentUid)
      stmt.setLong(3, entity.camSubmitterUid)
      stmt.setLong(4, entity.camMarkerSubmitterUid)
      stmt.setLong(5, entity.camMarkerPersonUid)
      stmt.setString(6, entity.camMarkerComment)
      stmt.setFloat(7, entity.camMark)
      stmt.setFloat(8, entity.camMaxMark)
      stmt.setFloat(9, entity.camPenalty)
      stmt.setLong(10, entity.camLct)
      stmt.setLong(11, entity.camClazzUid)
    }
  }

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

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

  public override fun insertList(entityList: List<CourseAssignmentMark>) {
    _insertAdapterCourseAssignmentMark_abort.insertList(entityList)
  }

  public override fun update(entity: CourseAssignmentMark) {
    val _sql =
        "UPDATE CourseAssignmentMark SET camAssignmentUid = ?, camSubmitterUid = ?, camMarkerSubmitterUid = ?, camMarkerPersonUid = ?, camMarkerComment = ?, camMark = ?, camMaxMark = ?, camPenalty = ?, camLct = ?, camClazzUid = ? WHERE camUid = ?"
    _db.prepareAndUseStatement(_sql) {
       _stmt ->
      _stmt.setLong(1, entity.camAssignmentUid)
      _stmt.setLong(2, entity.camSubmitterUid)
      _stmt.setLong(3, entity.camMarkerSubmitterUid)
      _stmt.setLong(4, entity.camMarkerPersonUid)
      _stmt.setString(5, entity.camMarkerComment)
      _stmt.setFloat(6, entity.camMark)
      _stmt.setFloat(7, entity.camMaxMark)
      _stmt.setFloat(8, entity.camPenalty)
      _stmt.setLong(9, entity.camLct)
      _stmt.setLong(10, entity.camClazzUid)
      _stmt.setLong(11, entity.camUid)
      _stmt.executeUpdate()
    }
  }

  override fun checkNoSubmissionsMarked(assignmentUid: Long): Flow<Boolean> =
      _db.doorFlow(arrayOf("CourseAssignmentMark")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT NOT EXISTS(SELECT 1
      |                        FROM CourseAssignmentMark
      |                       WHERE CourseAssignmentMark.camAssignmentUid = ?
      |                       LIMIT 1)
      |    
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,assignmentUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(false) {
          _result.getBoolean(1)
        }
      }
    }
  }

  override fun getMarkOfAssignmentForSubmitterLiveData(assignmentUid: Long, submitterUid: Long):
      Flow<AverageCourseAssignmentMark?> = _db.doorFlow(arrayOf("ClazzAssignment",
      "CourseAssignmentMark", "courseAssignmentMark")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |         WITH ScoreByMarker (averageScore, averagePenalty) AS (
      |                 SELECT AVG(camMark), AVG(camPenalty)
      |                   FROM courseAssignmentMark
      |                        JOIN ClazzAssignment
      |                        ON caUid = courseAssignmentMark.camAssignmentUid         
      |                    AND camAssignmentUid = ?
      |                    AND camSubmitterUid = ?
      |                  WHERE camLct = (SELECT MAX(mark.camLct) 
      |                                    FROM CourseAssignmentMark As mark
      |                                    WHERE mark.camAssignmentUid = ?
      |                                     AND mark.camSubmitterUid = ?
      |                                     AND (caMarkingType = 1
      |                                       OR mark.camMarkerSubmitterUid = courseAssignmentMark.camMarkerSubmitterUid))
      |                )                       
      |
      |         SELECT COALESCE(averageScore, -1) AS averageScore, COALESCE(averagePenalty, -1) AS averagePenalty
      |           FROM ScoreByMarker
      |    
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,assignmentUid)
      _stmt.setLong(2,submitterUid)
      _stmt.setLong(3,assignmentUid)
      _stmt.setLong(4,submitterUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(null) {
          val _tmp_averageScore = _result.getFloat("averageScore")
          val _tmp_averagePenalty = _result.getInt("averagePenalty")
          AverageCourseAssignmentMark().apply {
            this.averageScore = _tmp_averageScore
            this.averagePenalty = _tmp_averagePenalty
          }
        }
      }
    }
  }

  override fun getAllMarksForUserAsFlow(accountPersonUid: Long, assignmentUid: Long):
      Flow<List<CourseAssignmentMarkAndMarkerName>> = _db.doorFlow(arrayOf("CourseAssignmentMark",
      "Person", "ClazzAssignment", "CourseGroupMember", "ClazzEnrolment", "PersonPicture")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT CourseAssignmentMark.*,
      |               Person.firstNames AS markerFirstNames,
      |               Person.lastName AS markerLastName,
      |               PersonPicture.personPictureThumbnailUri AS markerPictureUri
      |          FROM CourseAssignmentMark
      |               LEFT JOIN Person
      |                         ON Person.personUid = CourseAssignmentMark.camMarkerPersonUid
      |               LEFT JOIN PersonPicture
      |                         ON PersonPicture.personPictureUid = CourseAssignmentMark.camMarkerPersonUid
      |         WHERE (
      |        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
      |    ) > 0
      |           AND CourseAssignmentMark.camAssignmentUid = ?
      |           AND CourseAssignmentMark.camSubmitterUid = (
      |        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
      |    )
      |      ORDER BY CourseAssignmentMark.camLct DESC    
      |    
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,assignmentUid)
      _stmt.setLong(2,accountPersonUid)
      _stmt.setLong(3,assignmentUid)
      _stmt.setLong(4,assignmentUid)
      _stmt.setLong(5,accountPersonUid)
      _stmt.setLong(6,assignmentUid)
      _stmt.setLong(7,accountPersonUid)
      _stmt.setLong(8,assignmentUid)
      _stmt.setLong(9,assignmentUid)
      _stmt.setLong(10,accountPersonUid)
      _stmt.setLong(11,assignmentUid)
      _stmt.setLong(12,assignmentUid)
      _stmt.setLong(13,accountPersonUid)
      _stmt.setLong(14,assignmentUid)
      _stmt.setLong(15,assignmentUid)
      _stmt.setLong(16,accountPersonUid)
      _stmt.setLong(17,assignmentUid)
      _stmt.setLong(18,accountPersonUid)
      _stmt.setLong(19,assignmentUid)
      _stmt.setLong(20,assignmentUid)
      _stmt.setLong(21,accountPersonUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          val _tmp_markerFirstNames = _result.getString("markerFirstNames")
          val _tmp_markerLastName = _result.getString("markerLastName")
          val _tmp_markerPictureUri = _result.getString("markerPictureUri")
          var _tmp_CourseAssignmentMark_nullCount = 0
          val _tmp_camUid = _result.getLong("camUid")
          if(_result.wasNull()) _tmp_CourseAssignmentMark_nullCount++
          val _tmp_camAssignmentUid = _result.getLong("camAssignmentUid")
          if(_result.wasNull()) _tmp_CourseAssignmentMark_nullCount++
          val _tmp_camSubmitterUid = _result.getLong("camSubmitterUid")
          if(_result.wasNull()) _tmp_CourseAssignmentMark_nullCount++
          val _tmp_camMarkerSubmitterUid = _result.getLong("camMarkerSubmitterUid")
          if(_result.wasNull()) _tmp_CourseAssignmentMark_nullCount++
          val _tmp_camMarkerPersonUid = _result.getLong("camMarkerPersonUid")
          if(_result.wasNull()) _tmp_CourseAssignmentMark_nullCount++
          val _tmp_camMarkerComment = _result.getString("camMarkerComment")
          if(_result.wasNull()) _tmp_CourseAssignmentMark_nullCount++
          val _tmp_camMark = _result.getFloat("camMark")
          if(_result.wasNull()) _tmp_CourseAssignmentMark_nullCount++
          val _tmp_camMaxMark = _result.getFloat("camMaxMark")
          if(_result.wasNull()) _tmp_CourseAssignmentMark_nullCount++
          val _tmp_camPenalty = _result.getFloat("camPenalty")
          if(_result.wasNull()) _tmp_CourseAssignmentMark_nullCount++
          val _tmp_camLct = _result.getLong("camLct")
          if(_result.wasNull()) _tmp_CourseAssignmentMark_nullCount++
          val _tmp_camClazzUid = _result.getLong("camClazzUid")
          if(_result.wasNull()) _tmp_CourseAssignmentMark_nullCount++
          val _tmp_CourseAssignmentMark_isAllNull = _tmp_CourseAssignmentMark_nullCount == 11
          CourseAssignmentMarkAndMarkerName().apply {
            this.markerFirstNames = _tmp_markerFirstNames
            this.markerLastName = _tmp_markerLastName
            this.markerPictureUri = _tmp_markerPictureUri
            if(!_tmp_CourseAssignmentMark_isAllNull) {
              this.courseAssignmentMark = 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 fun getAllMarksForSubmitterAsFlow(submitterUid: Long, assignmentUid: Long):
      Flow<List<CourseAssignmentMarkAndMarkerName>> = _db.doorFlow(arrayOf("CourseAssignmentMark",
      "Person", "PersonPicture")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT CourseAssignmentMark.*,
      |               Person.firstNames AS markerFirstNames,
      |               Person.lastName AS markerLastName,
      |               PersonPicture.personPictureThumbnailUri AS markerPictureUri
      |          FROM CourseAssignmentMark
      |               LEFT JOIN Person
      |                         ON Person.personUid = CourseAssignmentMark.camMarkerPersonUid
      |               LEFT JOIN PersonPicture
      |                         ON PersonPicture.personPictureUid = CourseAssignmentMark.camMarkerPersonUid
      |         WHERE CourseAssignmentMark.camAssignmentUid = ?
      |           AND CourseAssignmentMark.camSubmitterUid = ?
      |      ORDER BY CourseAssignmentMark.camLct DESC                             
      |    
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,assignmentUid)
      _stmt.setLong(2,submitterUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          val _tmp_markerFirstNames = _result.getString("markerFirstNames")
          val _tmp_markerLastName = _result.getString("markerLastName")
          val _tmp_markerPictureUri = _result.getString("markerPictureUri")
          var _tmp_CourseAssignmentMark_nullCount = 0
          val _tmp_camUid = _result.getLong("camUid")
          if(_result.wasNull()) _tmp_CourseAssignmentMark_nullCount++
          val _tmp_camAssignmentUid = _result.getLong("camAssignmentUid")
          if(_result.wasNull()) _tmp_CourseAssignmentMark_nullCount++
          val _tmp_camSubmitterUid = _result.getLong("camSubmitterUid")
          if(_result.wasNull()) _tmp_CourseAssignmentMark_nullCount++
          val _tmp_camMarkerSubmitterUid = _result.getLong("camMarkerSubmitterUid")
          if(_result.wasNull()) _tmp_CourseAssignmentMark_nullCount++
          val _tmp_camMarkerPersonUid = _result.getLong("camMarkerPersonUid")
          if(_result.wasNull()) _tmp_CourseAssignmentMark_nullCount++
          val _tmp_camMarkerComment = _result.getString("camMarkerComment")
          if(_result.wasNull()) _tmp_CourseAssignmentMark_nullCount++
          val _tmp_camMark = _result.getFloat("camMark")
          if(_result.wasNull()) _tmp_CourseAssignmentMark_nullCount++
          val _tmp_camMaxMark = _result.getFloat("camMaxMark")
          if(_result.wasNull()) _tmp_CourseAssignmentMark_nullCount++
          val _tmp_camPenalty = _result.getFloat("camPenalty")
          if(_result.wasNull()) _tmp_CourseAssignmentMark_nullCount++
          val _tmp_camLct = _result.getLong("camLct")
          if(_result.wasNull()) _tmp_CourseAssignmentMark_nullCount++
          val _tmp_camClazzUid = _result.getLong("camClazzUid")
          if(_result.wasNull()) _tmp_CourseAssignmentMark_nullCount++
          val _tmp_CourseAssignmentMark_isAllNull = _tmp_CourseAssignmentMark_nullCount == 11
          CourseAssignmentMarkAndMarkerName().apply {
            this.markerFirstNames = _tmp_markerFirstNames
            this.markerLastName = _tmp_markerLastName
            this.markerPictureUri = _tmp_markerPictureUri
            if(!_tmp_CourseAssignmentMark_isAllNull) {
              this.courseAssignmentMark = 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 getAllMarksForSubmitterAsFlowMarkerPersons(submitterUid: Long,
      assignmentUid: Long): List<PersonAndPicture> =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT Person.*, PersonPicture.*
    |          FROM Person
    |               LEFT JOIN PersonPicture
    |                         ON PersonPicture.personPictureUid = Person.personUid
    |         WHERE PersonUid IN
    |               (SELECT CourseAssignmentMark.camMarkerPersonUid
    |                  FROM CourseAssignmentMark
    |                 WHERE CourseAssignmentMark.camAssignmentUid = ?
    |                   AND CourseAssignmentMark.camSubmitterUid = ?)
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,assignmentUid)
    _stmt.setLong(2,submitterUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        var _tmp_Person_nullCount = 0
        val _tmp_personUid = _result.getLong("personUid")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_username = _result.getString("username")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_firstNames = _result.getString("firstNames")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_lastName = _result.getString("lastName")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_emailAddr = _result.getString("emailAddr")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_phoneNum = _result.getString("phoneNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_gender = _result.getInt("gender")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_active = _result.getBoolean("active")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_isPersonalAccount = _result.getBoolean("isPersonalAccount")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_dateOfBirth = _result.getLong("dateOfBirth")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personAddress = _result.getString("personAddress")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personOrgId = _result.getString("personOrgId")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personGroupUid = _result.getLong("personGroupUid")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personLct = _result.getLong("personLct")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personCountry = _result.getString("personCountry")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personType = _result.getInt("personType")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personMasterChangeSeqNum = _result.getLong("personMasterChangeSeqNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personLocalChangeSeqNum = _result.getLong("personLocalChangeSeqNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personLastChangedBy = _result.getInt("personLastChangedBy")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_admin = _result.getBoolean("admin")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personNotes = _result.getString("personNotes")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_fatherName = _result.getString("fatherName")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_fatherNumber = _result.getString("fatherNumber")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_motherName = _result.getString("motherName")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_motherNum = _result.getString("motherNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_Person_isAllNull = _tmp_Person_nullCount == 25
        var _tmp_PersonPicture_nullCount = 0
        val _tmp_personPictureUid = _result.getLong("personPictureUid")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_personPictureLct = _result.getLong("personPictureLct")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_personPictureUri = _result.getString("personPictureUri")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_personPictureThumbnailUri = _result.getString("personPictureThumbnailUri")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_fileSize = _result.getInt("fileSize")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_personPictureActive = _result.getBoolean("personPictureActive")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_PersonPicture_isAllNull = _tmp_PersonPicture_nullCount == 6
        PersonAndPicture().apply {
          if(!_tmp_Person_isAllNull) {
            this.person = Person().apply {
              this.personUid = _tmp_personUid
              this.username = _tmp_username
              this.firstNames = _tmp_firstNames
              this.lastName = _tmp_lastName
              this.emailAddr = _tmp_emailAddr
              this.phoneNum = _tmp_phoneNum
              this.gender = _tmp_gender
              this.active = _tmp_active
              this.isPersonalAccount = _tmp_isPersonalAccount
              this.dateOfBirth = _tmp_dateOfBirth
              this.personAddress = _tmp_personAddress
              this.personOrgId = _tmp_personOrgId
              this.personGroupUid = _tmp_personGroupUid
              this.personLct = _tmp_personLct
              this.personCountry = _tmp_personCountry
              this.personType = _tmp_personType
              this.personMasterChangeSeqNum = _tmp_personMasterChangeSeqNum
              this.personLocalChangeSeqNum = _tmp_personLocalChangeSeqNum
              this.personLastChangedBy = _tmp_personLastChangedBy
              this.admin = _tmp_admin
              this.personNotes = _tmp_personNotes
              this.fatherName = _tmp_fatherName
              this.fatherNumber = _tmp_fatherNumber
              this.motherName = _tmp_motherName
              this.motherNum = _tmp_motherNum
            }
          }
          if(!_tmp_PersonPicture_isAllNull) {
            this.picture = PersonPicture().apply {
              this.personPictureUid = _tmp_personPictureUid
              this.personPictureLct = _tmp_personPictureLct
              this.personPictureUri = _tmp_personPictureUri
              this.personPictureThumbnailUri = _tmp_personPictureThumbnailUri
              this.fileSize = _tmp_fileSize
              this.personPictureActive = _tmp_personPictureActive
            }
          }
        }
      }
    }
  }

  override fun getAllMarksOfAssignmentForSubmitter(
    assignmentUid: Long,
    submitterUid: Long,
    filter: Int,
  ): PagingSource<Int, CourseAssignmentMarkWithPersonMarker> = object :
      DoorLimitOffsetPagingSource<CourseAssignmentMarkWithPersonMarker>(db = _db
  , tableNames = arrayOf("courseAssignmentMark", "ClazzAssignment")
  ) {
    override suspend fun loadRows(_limit: Int, _offset: Int):
        List<CourseAssignmentMarkWithPersonMarker> =
        _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT * FROM (
      |          WITH ScoreByMarker AS (
      |                 SELECT *
      |                   FROM courseAssignmentMark    
      |                  WHERE camAssignmentUid = ?
      |                    AND camSubmitterUid = ?
      |                    AND (? = 0 OR camLct = (SELECT MAX(mark.camLct) 
      |                                    FROM CourseAssignmentMark As mark
      |                                    WHERE mark.camAssignmentUid = ?
      |                                      AND mark.camSubmitterUid = ?
      |                                      AND mark.camMarkerSubmitterUid = courseAssignmentMark.camMarkerSubmitterUid
      |                                      ))                 
      |                ORDER BY camLct DESC)    
      |                
      |          SELECT marker.*, ScoreByMarker.*, (ClazzAssignment.caGroupUid != 0) AS isGroup
      |            FROM ScoreByMarker
      |                 JOIN Person As marker
      |                 ON Marker.personUid = ScoreByMarker.camMarkerPersonUid
      |                 JOIN ClazzAssignment
      |                 ON ClazzAssignment.caUid = ?
      |    ) AS _PagingData LIMIT ? OFFSET ?
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,assignmentUid)
      _stmt.setLong(2,submitterUid)
      _stmt.setInt(3,filter)
      _stmt.setLong(4,assignmentUid)
      _stmt.setLong(5,submitterUid)
      _stmt.setLong(6,assignmentUid)
      _stmt.setInt(7,_limit)
      _stmt.setInt(8,_offset)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          val _tmp_isGroup = _result.getBoolean("isGroup")
          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")
          var _tmp_Person_nullCount = 0
          val _tmp_personUid = _result.getLong("personUid")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_username = _result.getString("username")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_firstNames = _result.getString("firstNames")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_lastName = _result.getString("lastName")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_emailAddr = _result.getString("emailAddr")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_phoneNum = _result.getString("phoneNum")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_gender = _result.getInt("gender")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_active = _result.getBoolean("active")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_isPersonalAccount = _result.getBoolean("isPersonalAccount")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_dateOfBirth = _result.getLong("dateOfBirth")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personAddress = _result.getString("personAddress")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personOrgId = _result.getString("personOrgId")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personGroupUid = _result.getLong("personGroupUid")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personLct = _result.getLong("personLct")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personCountry = _result.getString("personCountry")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personType = _result.getInt("personType")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personMasterChangeSeqNum = _result.getLong("personMasterChangeSeqNum")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personLocalChangeSeqNum = _result.getLong("personLocalChangeSeqNum")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personLastChangedBy = _result.getInt("personLastChangedBy")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_admin = _result.getBoolean("admin")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personNotes = _result.getString("personNotes")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_fatherName = _result.getString("fatherName")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_fatherNumber = _result.getString("fatherNumber")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_motherName = _result.getString("motherName")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_motherNum = _result.getString("motherNum")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_Person_isAllNull = _tmp_Person_nullCount == 25
          CourseAssignmentMarkWithPersonMarker().apply {
            this.isGroup = _tmp_isGroup
            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
            if(!_tmp_Person_isAllNull) {
              this.marker = Person().apply {
                this.personUid = _tmp_personUid
                this.username = _tmp_username
                this.firstNames = _tmp_firstNames
                this.lastName = _tmp_lastName
                this.emailAddr = _tmp_emailAddr
                this.phoneNum = _tmp_phoneNum
                this.gender = _tmp_gender
                this.active = _tmp_active
                this.isPersonalAccount = _tmp_isPersonalAccount
                this.dateOfBirth = _tmp_dateOfBirth
                this.personAddress = _tmp_personAddress
                this.personOrgId = _tmp_personOrgId
                this.personGroupUid = _tmp_personGroupUid
                this.personLct = _tmp_personLct
                this.personCountry = _tmp_personCountry
                this.personType = _tmp_personType
                this.personMasterChangeSeqNum = _tmp_personMasterChangeSeqNum
                this.personLocalChangeSeqNum = _tmp_personLocalChangeSeqNum
                this.personLastChangedBy = _tmp_personLastChangedBy
                this.admin = _tmp_admin
                this.personNotes = _tmp_personNotes
                this.fatherName = _tmp_fatherName
                this.fatherNumber = _tmp_fatherNumber
                this.motherName = _tmp_motherName
                this.motherNum = _tmp_motherNum
              }
            }
          }
        }
      }
    }

    override suspend fun countRows(): Int = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT COUNT(*) FROM (
      |          WITH ScoreByMarker AS (
      |                 SELECT *
      |                   FROM courseAssignmentMark    
      |                  WHERE camAssignmentUid = ?
      |                    AND camSubmitterUid = ?
      |                    AND (? = 0 OR camLct = (SELECT MAX(mark.camLct) 
      |                                    FROM CourseAssignmentMark As mark
      |                                    WHERE mark.camAssignmentUid = ?
      |                                      AND mark.camSubmitterUid = ?
      |                                      AND mark.camMarkerSubmitterUid = courseAssignmentMark.camMarkerSubmitterUid
      |                                      ))                 
      |                ORDER BY camLct DESC)    
      |                
      |          SELECT marker.*, ScoreByMarker.*, (ClazzAssignment.caGroupUid != 0) AS isGroup
      |            FROM ScoreByMarker
      |                 JOIN Person As marker
      |                 ON Marker.personUid = ScoreByMarker.camMarkerPersonUid
      |                 JOIN ClazzAssignment
      |                 ON ClazzAssignment.caUid = ?
      |    ) AS _PagingCount
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,assignmentUid)
      _stmt.setLong(2,submitterUid)
      _stmt.setInt(3,filter)
      _stmt.setLong(4,assignmentUid)
      _stmt.setLong(5,submitterUid)
      _stmt.setLong(6,assignmentUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(0) {
          _result.getInt(1)
        }
      }
    }
  }

  override fun getMarkOfAssignmentForStudent(assignmentUid: Long, submitterUid: Long):
      CourseAssignmentMark? = _db.prepareAndUseStatement(PreparedStatementConfig(
    sql = """
    |
    |        SELECT * 
    |          FROM CourseAssignmentMark
    |         WHERE camAssignmentUid = ?
    |           AND camSubmitterUid = ?
    |      ORDER BY camLct DESC
    |         LIMIT 1
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,assignmentUid)
    _stmt.setLong(2,submitterUid)
    _stmt.executeQuery().useResults{ _result -> 
      _result.mapNextRow(null) {
        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 findNextSubmitterToMarkForAssignment(
    assignmentUid: Long,
    submitterUid: Long,
    markerUid: Long,
  ): Long = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |         SELECT COALESCE((
    |            SELECT casSubmitterUid
    |              FROM CourseAssignmentSubmission
    |              
    |                   JOIN ClazzAssignment
    |                   ON ClazzAssignment.caUid = CourseAssignmentSubmission.casAssignmentUid
    |              
    |                   LEFT JOIN CourseAssignmentMark
    |                   ON CourseAssignmentMark.camSubmitterUid = CourseAssignmentSubmission.casSubmitterUid
    |                   AND CourseAssignmentMark.camAssignmentUid = ClazzAssignment.caUid
    |                   
    |                   LEFT JOIN PeerReviewerAllocation
    |                   ON praAssignmentUid = ClazzAssignment.caUid
    |                   AND praToMarkerSubmitterUid = ?
    |                   
    |             WHERE CourseAssignmentSubmission.casSubmitterUid != ?
    |               AND CourseAssignmentSubmission.casSubmitterUid != ?
    |               AND CourseAssignmentSubmission.casAssignmentUid = ?
    |               AND CourseAssignmentMark.camUid IS NULL
    |               AND (ClazzAssignment.caMarkingType = 1 
    |                    OR PeerReviewerAllocation.praMarkerSubmitterUid = ?)
    |          GROUP BY casSubmitterUid
    |         LIMIT 1),0)
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,submitterUid)
    _stmt.setLong(2,submitterUid)
    _stmt.setLong(3,markerUid)
    _stmt.setLong(4,assignmentUid)
    _stmt.setLong(5,markerUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(0L) {
        _result.getLong(1)
      }
    }
  }
}
