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.entities.CourseAssignmentSubmission
import kotlin.Boolean
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.collections.List
import kotlinx.coroutines.flow.Flow

public class CourseAssignmentSubmissionDao_JdbcImpl(
  public val _db: RoomDatabase,
) : CourseAssignmentSubmissionDao() {
  public val _insertAdapterCourseAssignmentSubmission_abort:
      EntityInsertionAdapter<CourseAssignmentSubmission> = object :
      EntityInsertionAdapter<CourseAssignmentSubmission>(_db) {
    override fun makeSql(returnsId: Boolean): String =
        "INSERT INTO CourseAssignmentSubmission (casUid, casAssignmentUid, casSubmitterUid, casSubmitterPersonUid, casText, casType, casTimestamp, casClazzUid) VALUES(?, ?, ?, ?, ?, ?, ?, ?)"

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement,
        entity: CourseAssignmentSubmission) {
      if(entity.casUid == 0L) {
        stmt.setObject(1, null)
      } else {
        stmt.setLong(1, entity.casUid)
      }
      stmt.setLong(2, entity.casAssignmentUid)
      stmt.setLong(3, entity.casSubmitterUid)
      stmt.setLong(4, entity.casSubmitterPersonUid)
      stmt.setString(5, entity.casText)
      stmt.setInt(6, entity.casType)
      stmt.setLong(7, entity.casTimestamp)
      stmt.setLong(8, entity.casClazzUid)
    }
  }

  override suspend fun insertListAsync(entityList: List<CourseAssignmentSubmission>) {
    _insertAdapterCourseAssignmentSubmission_abort.insertListAsync(entityList)
  }

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

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

  public override fun insertList(entityList: List<CourseAssignmentSubmission>) {
    _insertAdapterCourseAssignmentSubmission_abort.insertList(entityList)
  }

  public override fun update(entity: CourseAssignmentSubmission) {
    val _sql =
        "UPDATE CourseAssignmentSubmission SET casAssignmentUid = ?, casSubmitterUid = ?, casSubmitterPersonUid = ?, casText = ?, casType = ?, casTimestamp = ?, casClazzUid = ? WHERE casUid = ?"
    _db.prepareAndUseStatement(_sql) {
       _stmt ->
      _stmt.setLong(1, entity.casAssignmentUid)
      _stmt.setLong(2, entity.casSubmitterUid)
      _stmt.setLong(3, entity.casSubmitterPersonUid)
      _stmt.setString(4, entity.casText)
      _stmt.setInt(5, entity.casType)
      _stmt.setLong(6, entity.casTimestamp)
      _stmt.setLong(7, entity.casClazzUid)
      _stmt.setLong(8, entity.casUid)
      _stmt.executeUpdate()
    }
  }

  override fun getAllSubmissionsFromSubmitter(assignmentUid: Long, submitterUid: Long):
      PagingSource<Int, CourseAssignmentSubmission> = object :
      DoorLimitOffsetPagingSource<CourseAssignmentSubmission>(db = _db
  , tableNames = arrayOf("CourseAssignmentSubmission")
  ) {
    override suspend fun loadRows(_limit: Int, _offset: Int): List<CourseAssignmentSubmission> =
        _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT * FROM (
      |        SELECT * 
      |          FROM CourseAssignmentSubmission
      |         WHERE casAssignmentUid = CAST(? AS BIGINT)
      |           AND casSubmitterUid = CAST(? AS BIGINT)
      |      ORDER BY casTimestamp DESC
      |    ) AS _PagingData LIMIT ? OFFSET ?
      """.trimMargin(),
      postgreSql = """
      |SELECT * FROM (
      |        SELECT * 
      |          FROM CourseAssignmentSubmission
      |         WHERE casAssignmentUid = ?
      |           AND casSubmitterUid = ?
      |      ORDER BY casTimestamp DESC
      |    ) AS _PagingData LIMIT ? OFFSET ?
      |""".trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,assignmentUid)
      _stmt.setLong(2,submitterUid)
      _stmt.setInt(3,_limit)
      _stmt.setInt(4,_offset)
      _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 countRows(): Int = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT COUNT(*) FROM (
      |        SELECT * 
      |          FROM CourseAssignmentSubmission
      |         WHERE casAssignmentUid = CAST(? AS BIGINT)
      |           AND casSubmitterUid = CAST(? AS BIGINT)
      |      ORDER BY casTimestamp DESC
      |    ) AS _PagingCount
      """.trimMargin(),
      postgreSql = """
      |SELECT COUNT(*) FROM (
      |        SELECT * 
      |          FROM CourseAssignmentSubmission
      |         WHERE casAssignmentUid = ?
      |           AND casSubmitterUid = ?
      |      ORDER BY casTimestamp DESC
      |    ) AS _PagingCount
      |""".trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,assignmentUid)
      _stmt.setLong(2,submitterUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(0) {
          _result.getInt(1)
        }
      }
    }
  }

  override fun getAllSubmissionsForUser(accountPersonUid: Long, assignmentUid: Long):
      Flow<List<CourseAssignmentSubmission>> = _db.doorFlow(arrayOf("CourseAssignmentSubmission",
      "ClazzAssignment", "CourseGroupMember", "ClazzEnrolment")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |         SELECT CourseAssignmentSubmission.*
      |          FROM CourseAssignmentSubmission
      |         WHERE casSubmitterUid = (
      |        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 = CAST(? AS BIGINT)) = 0
      |                         THEN (
      |        SELECT COALESCE(
      |                (SELECT ClazzEnrolment.clazzEnrolmentPersonUid
      |                   FROM ClazzEnrolment
      |                  WHERE ClazzEnrolment.clazzEnrolmentPersonUid = CAST(? AS BIGINT)
      |                    AND ClazzEnrolment.clazzEnrolmentRole = 1000
      |                    AND ClazzEnrolment.clazzEnrolmentClazzUid = 
      |                        (SELECT ClazzAssignment.caClazzUid
      |                           FROM ClazzAssignment
      |                          WHERE ClazzAssignment.caUid = CAST(? AS BIGINT))
      |                  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 = CAST(? AS BIGINT)) != 0
      |                          AND (
      |        SELECT COALESCE(
      |                (SELECT ClazzEnrolment.clazzEnrolmentPersonUid
      |                   FROM ClazzEnrolment
      |                  WHERE ClazzEnrolment.clazzEnrolmentPersonUid = CAST(? AS BIGINT)
      |                    AND ClazzEnrolment.clazzEnrolmentRole = 1000
      |                    AND ClazzEnrolment.clazzEnrolmentClazzUid = 
      |                        (SELECT ClazzAssignment.caClazzUid
      |                           FROM ClazzAssignment
      |                          WHERE ClazzAssignment.caUid = CAST(? AS BIGINT))
      |                  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 = CAST(? AS BIGINT)
      |                    AND ClazzEnrolment.clazzEnrolmentRole = 1000
      |                    AND ClazzEnrolment.clazzEnrolmentClazzUid = 
      |                        (SELECT ClazzAssignment.caClazzUid
      |                           FROM ClazzAssignment
      |                          WHERE ClazzAssignment.caUid = CAST(? AS BIGINT))
      |                  LIMIT 1), 0)
      |    ) > 0
      |                              AND CourseGroupMember.cgmSetUid = 
      |                                  (SELECT caGroupUid
      |                                     FROM ClazzAssignment
      |                                    WHERE caUid = CAST(? AS BIGINT))
      |                              AND CourseGroupMember.cgmPersonUid = CAST(? AS BIGINT)
      |                            LIMIT 1), -1)
      |                    END
      |    )
      |    
      """.trimMargin(),
      postgreSql = """
      |
      |         SELECT CourseAssignmentSubmission.*
      |          FROM CourseAssignmentSubmission
      |         WHERE casSubmitterUid = (
      |        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,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.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 fun getAllSubmissionsFromSubmitterAsFlow(submitterUid: Long, assignmentUid: Long):
      Flow<List<CourseAssignmentSubmission>> = _db.doorFlow(arrayOf("CourseAssignmentSubmission")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT CourseAssignmentSubmission.*
      |          FROM CourseAssignmentSubmission
      |         WHERE CourseAssignmentSubmission.casAssignmentUid = CAST(? AS BIGINT)
      |           AND CourseAssignmentSubmission.casSubmitterUid = CAST(? AS BIGINT)
      |      ORDER BY CourseAssignmentSubmission.casTimestamp DESC      
      |    
      """.trimMargin(),
      postgreSql = """
      |
      |        SELECT CourseAssignmentSubmission.*
      |          FROM CourseAssignmentSubmission
      |         WHERE CourseAssignmentSubmission.casAssignmentUid = ?
      |           AND CourseAssignmentSubmission.casSubmitterUid = ?
      |      ORDER BY CourseAssignmentSubmission.casTimestamp DESC      
      |    
      |""".trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,assignmentUid)
      _stmt.setLong(2,submitterUid)
      _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 getLatestSubmissionForUserAsync(accountPersonUid: Long, assignmentUid: Long):
      CourseAssignmentSubmission? = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT CourseAssignmentSubmission.*
    |          FROM CourseAssignmentSubmission
    |         WHERE casSubmitterUid = (
    |        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 = CAST(? AS BIGINT)) = 0
    |                         THEN (
    |        SELECT COALESCE(
    |                (SELECT ClazzEnrolment.clazzEnrolmentPersonUid
    |                   FROM ClazzEnrolment
    |                  WHERE ClazzEnrolment.clazzEnrolmentPersonUid = CAST(? AS BIGINT)
    |                    AND ClazzEnrolment.clazzEnrolmentRole = 1000
    |                    AND ClazzEnrolment.clazzEnrolmentClazzUid = 
    |                        (SELECT ClazzAssignment.caClazzUid
    |                           FROM ClazzAssignment
    |                          WHERE ClazzAssignment.caUid = CAST(? AS BIGINT))
    |                  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 = CAST(? AS BIGINT)) != 0
    |                          AND (
    |        SELECT COALESCE(
    |                (SELECT ClazzEnrolment.clazzEnrolmentPersonUid
    |                   FROM ClazzEnrolment
    |                  WHERE ClazzEnrolment.clazzEnrolmentPersonUid = CAST(? AS BIGINT)
    |                    AND ClazzEnrolment.clazzEnrolmentRole = 1000
    |                    AND ClazzEnrolment.clazzEnrolmentClazzUid = 
    |                        (SELECT ClazzAssignment.caClazzUid
    |                           FROM ClazzAssignment
    |                          WHERE ClazzAssignment.caUid = CAST(? AS BIGINT))
    |                  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 = CAST(? AS BIGINT)
    |                    AND ClazzEnrolment.clazzEnrolmentRole = 1000
    |                    AND ClazzEnrolment.clazzEnrolmentClazzUid = 
    |                        (SELECT ClazzAssignment.caClazzUid
    |                           FROM ClazzAssignment
    |                          WHERE ClazzAssignment.caUid = CAST(? AS BIGINT))
    |                  LIMIT 1), 0)
    |    ) > 0
    |                              AND CourseGroupMember.cgmSetUid = 
    |                                  (SELECT caGroupUid
    |                                     FROM ClazzAssignment
    |                                    WHERE caUid = CAST(? AS BIGINT))
    |                              AND CourseGroupMember.cgmPersonUid = CAST(? AS BIGINT)
    |                            LIMIT 1), -1)
    |                    END
    |    )
    |           AND CourseAssignmentSubmission.casAssignmentUid = CAST(? AS BIGINT)
    |      ORDER BY casTimestamp DESC
    |         LIMIT 1
    |    
    """.trimMargin(),
    postgreSql = """
    |
    |        SELECT CourseAssignmentSubmission.*
    |          FROM CourseAssignmentSubmission
    |         WHERE casSubmitterUid = (
    |        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
    |    )
    |           AND CourseAssignmentSubmission.casAssignmentUid = ?
    |      ORDER BY casTimestamp DESC
    |         LIMIT 1
    |    
    |""".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.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(null) {
        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 doesUserHaveSubmissions(accountPersonUid: Long, assignmentUid: Long): Boolean
      = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT EXISTS
    |               (SELECT CourseAssignmentSubmission.casUid
    |                  FROM CourseAssignmentSubmission
    |                 WHERE CourseAssignmentSubmission.casSubmitterUid = (
    |        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 = CAST(? AS BIGINT)) = 0
    |                         THEN (
    |        SELECT COALESCE(
    |                (SELECT ClazzEnrolment.clazzEnrolmentPersonUid
    |                   FROM ClazzEnrolment
    |                  WHERE ClazzEnrolment.clazzEnrolmentPersonUid = CAST(? AS BIGINT)
    |                    AND ClazzEnrolment.clazzEnrolmentRole = 1000
    |                    AND ClazzEnrolment.clazzEnrolmentClazzUid = 
    |                        (SELECT ClazzAssignment.caClazzUid
    |                           FROM ClazzAssignment
    |                          WHERE ClazzAssignment.caUid = CAST(? AS BIGINT))
    |                  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 = CAST(? AS BIGINT)) != 0
    |                          AND (
    |        SELECT COALESCE(
    |                (SELECT ClazzEnrolment.clazzEnrolmentPersonUid
    |                   FROM ClazzEnrolment
    |                  WHERE ClazzEnrolment.clazzEnrolmentPersonUid = CAST(? AS BIGINT)
    |                    AND ClazzEnrolment.clazzEnrolmentRole = 1000
    |                    AND ClazzEnrolment.clazzEnrolmentClazzUid = 
    |                        (SELECT ClazzAssignment.caClazzUid
    |                           FROM ClazzAssignment
    |                          WHERE ClazzAssignment.caUid = CAST(? AS BIGINT))
    |                  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 = CAST(? AS BIGINT)
    |                    AND ClazzEnrolment.clazzEnrolmentRole = 1000
    |                    AND ClazzEnrolment.clazzEnrolmentClazzUid = 
    |                        (SELECT ClazzAssignment.caClazzUid
    |                           FROM ClazzAssignment
    |                          WHERE ClazzAssignment.caUid = CAST(? AS BIGINT))
    |                  LIMIT 1), 0)
    |    ) > 0
    |                              AND CourseGroupMember.cgmSetUid = 
    |                                  (SELECT caGroupUid
    |                                     FROM ClazzAssignment
    |                                    WHERE caUid = CAST(? AS BIGINT))
    |                              AND CourseGroupMember.cgmPersonUid = CAST(? AS BIGINT)
    |                            LIMIT 1), -1)
    |                    END
    |    )
    |                   AND CourseAssignmentSubmission.casAssignmentUid = CAST(? AS BIGINT))
    |    
    """.trimMargin(),
    postgreSql = """
    |
    |        SELECT EXISTS
    |               (SELECT CourseAssignmentSubmission.casUid
    |                  FROM CourseAssignmentSubmission
    |                 WHERE CourseAssignmentSubmission.casSubmitterUid = (
    |        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
    |    )
    |                   AND CourseAssignmentSubmission.casAssignmentUid = ?)
    |    
    |""".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.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(false) {
        _result.getBoolean(1)
      }
    }
  }

  override suspend fun countFileSubmissionFromStudent(assignmentUid: Long, submitterUid: Long): Int
      = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT Count(casUid)
    |          FROM CourseAssignmentSubmission
    |         WHERE casAssignmentUid = CAST(? AS BIGINT)
    |           AND casSubmitterUid = CAST(? AS BIGINT)
    |           AND casType = 2
    |    
    """.trimMargin(),
    postgreSql = """
    |
    |        SELECT Count(casUid)
    |          FROM CourseAssignmentSubmission
    |         WHERE casAssignmentUid = ?
    |           AND casSubmitterUid = ?
    |           AND casType = 2
    |    
    |""".trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,assignmentUid)
    _stmt.setLong(2,submitterUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(0) {
        _result.getInt(1)
      }
    }
  }

  override suspend fun countSubmissionsFromSubmitter(assignmentUid: Long, submitterUid: Long): Int =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT Count(casUid)
    |          FROM CourseAssignmentSubmission
    |         WHERE casAssignmentUid = CAST(? AS BIGINT)
    |           AND casSubmitterUid = CAST(? AS BIGINT)
    |    
    """.trimMargin(),
    postgreSql = """
    |
    |        SELECT Count(casUid)
    |          FROM CourseAssignmentSubmission
    |         WHERE casAssignmentUid = ?
    |           AND casSubmitterUid = ?
    |    
    |""".trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,assignmentUid)
    _stmt.setLong(2,submitterUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(0) {
        _result.getInt(1)
      }
    }
  }

  override fun getStatusOfAssignmentForSubmitter(assignmentUid: Long, submitterUid: Long): Flow<Int>
      = _db.doorFlow(arrayOf("CourseAssignmentSubmission", "CourseAssignmentMark")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |           SELECT COALESCE((
      |                SELECT (CASE WHEN CourseAssignmentMark.camAssignmentUid IS NOT NULL 
      |                             THEN 2
      |                             ELSE 1 
      |                             END) AS status
      |                  FROM CourseAssignmentSubmission
      |                       
      |                       LEFT JOIN CourseAssignmentMark
      |                       ON CourseAssignmentMark.camAssignmentUid = CAST(? AS BIGINT)
      |                       AND CourseAssignmentMark.camSubmitterUid = CAST(? AS BIGINT)
      |                       
      |                 WHERE CourseAssignmentSubmission.casAssignmentUid = CAST(? AS BIGINT)
      |                   AND CourseAssignmentSubmission.casSubmitterUid = CAST(? AS BIGINT)
      |                 LIMIT 1
      |           ),0) AS Status
      |    
      """.trimMargin(),
      postgreSql = """
      |
      |           SELECT COALESCE((
      |                SELECT (CASE WHEN CourseAssignmentMark.camAssignmentUid IS NOT NULL 
      |                             THEN 2
      |                             ELSE 1 
      |                             END) AS status
      |                  FROM CourseAssignmentSubmission
      |                       
      |                       LEFT JOIN CourseAssignmentMark
      |                       ON CourseAssignmentMark.camAssignmentUid = ?
      |                       AND CourseAssignmentMark.camSubmitterUid = ?
      |                       
      |                 WHERE CourseAssignmentSubmission.casAssignmentUid = ?
      |                   AND CourseAssignmentSubmission.casSubmitterUid = ?
      |                 LIMIT 1
      |           ),0) AS Status
      |    
      |""".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(0) {
          _result.getInt(1)
        }
      }
    }
  }

  override suspend fun findLastSubmissionFromStudent(submitterUid: Long, assignmentUid: Long):
      CourseAssignmentSubmission? = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT * 
    |          FROM CourseAssignmentSubmission
    |         WHERE CourseAssignmentSubmission.casAssignmentUid = CAST(? AS BIGINT)
    |           AND CourseAssignmentSubmission.casSubmitterUid = CAST(? AS BIGINT)
    |      ORDER BY casTimestamp DESC
    |         LIMIT 1
    |    
    """.trimMargin(),
    postgreSql = """
    |
    |        SELECT * 
    |          FROM CourseAssignmentSubmission
    |         WHERE CourseAssignmentSubmission.casAssignmentUid = ?
    |           AND CourseAssignmentSubmission.casSubmitterUid = ?
    |      ORDER BY casTimestamp DESC
    |         LIMIT 1
    |    
    |""".trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,assignmentUid)
    _stmt.setLong(2,submitterUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(null) {
        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 fun checkNoSubmissionsMade(assignmentUid: Long): Flow<Boolean> =
      _db.doorFlow(arrayOf("CourseAssignmentSubmission")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |         SELECT NOT EXISTS(SELECT 1
      |                        FROM CourseAssignmentSubmission
      |                       WHERE CourseAssignmentSubmission.casAssignmentUid = CAST(? AS BIGINT)
      |                       LIMIT 1)
      |    
      """.trimMargin(),
      postgreSql = """
      |
      |         SELECT NOT EXISTS(SELECT 1
      |                        FROM CourseAssignmentSubmission
      |                       WHERE CourseAssignmentSubmission.casAssignmentUid = ?
      |                       LIMIT 1)
      |    
      |""".trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,assignmentUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(false) {
          _result.getBoolean(1)
        }
      }
    }
  }

  override suspend fun checkNoSubmissionsMadeAsync(assignmentUid: Long): Boolean =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |         SELECT NOT EXISTS(SELECT 1
    |                        FROM CourseAssignmentSubmission
    |                       WHERE CourseAssignmentSubmission.casAssignmentUid = CAST(? AS BIGINT)
    |                       LIMIT 1)
    |    
    """.trimMargin(),
    postgreSql = """
    |
    |         SELECT NOT EXISTS(SELECT 1
    |                        FROM CourseAssignmentSubmission
    |                       WHERE CourseAssignmentSubmission.casAssignmentUid = ?
    |                       LIMIT 1)
    |    
    |""".trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,assignmentUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(false) {
        _result.getBoolean(1)
      }
    }
  }

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

  override fun findByUidAsFlow(submissionUid: Long): Flow<CourseAssignmentSubmission?> =
      _db.doorFlow(arrayOf("CourseAssignmentSubmission")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT CourseAssignmentSubmission.*
      |          FROM CourseAssignmentSubmission
      |         WHERE casUid = CAST(? AS BIGINT)
      |    
      """.trimMargin(),
      postgreSql = """
      |
      |        SELECT CourseAssignmentSubmission.*
      |          FROM CourseAssignmentSubmission
      |         WHERE casUid = ?
      |    
      |""".trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,submissionUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(null) {
          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 fun findByAssignmentUidAndAccountPersonUid(accountPersonUid: Long, assignmentUid: Long):
      Flow<List<CourseAssignmentSubmission>> = _db.doorFlow(arrayOf("CourseAssignmentSubmission",
      "ClazzAssignment", "CourseGroupMember", "ClazzEnrolment")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT CourseAssignmentSubmission.*
      |          FROM CourseAssignmentSubmission
      |         WHERE CourseAssignmentSubmission.casAssignmentUid = CAST(? AS BIGINT)
      |           AND CourseAssignmentSubmission.casSubmitterUid = 
      |               (
      |        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 = CAST(? AS BIGINT)) = 0
      |                         THEN (
      |        SELECT COALESCE(
      |                (SELECT ClazzEnrolment.clazzEnrolmentPersonUid
      |                   FROM ClazzEnrolment
      |                  WHERE ClazzEnrolment.clazzEnrolmentPersonUid = CAST(? AS BIGINT)
      |                    AND ClazzEnrolment.clazzEnrolmentRole = 1000
      |                    AND ClazzEnrolment.clazzEnrolmentClazzUid = 
      |                        (SELECT ClazzAssignment.caClazzUid
      |                           FROM ClazzAssignment
      |                          WHERE ClazzAssignment.caUid = CAST(? AS BIGINT))
      |                  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 = CAST(? AS BIGINT)) != 0
      |                          AND (
      |        SELECT COALESCE(
      |                (SELECT ClazzEnrolment.clazzEnrolmentPersonUid
      |                   FROM ClazzEnrolment
      |                  WHERE ClazzEnrolment.clazzEnrolmentPersonUid = CAST(? AS BIGINT)
      |                    AND ClazzEnrolment.clazzEnrolmentRole = 1000
      |                    AND ClazzEnrolment.clazzEnrolmentClazzUid = 
      |                        (SELECT ClazzAssignment.caClazzUid
      |                           FROM ClazzAssignment
      |                          WHERE ClazzAssignment.caUid = CAST(? AS BIGINT))
      |                  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 = CAST(? AS BIGINT)
      |                    AND ClazzEnrolment.clazzEnrolmentRole = 1000
      |                    AND ClazzEnrolment.clazzEnrolmentClazzUid = 
      |                        (SELECT ClazzAssignment.caClazzUid
      |                           FROM ClazzAssignment
      |                          WHERE ClazzAssignment.caUid = CAST(? AS BIGINT))
      |                  LIMIT 1), 0)
      |    ) > 0
      |                              AND CourseGroupMember.cgmSetUid = 
      |                                  (SELECT caGroupUid
      |                                     FROM ClazzAssignment
      |                                    WHERE caUid = CAST(? AS BIGINT))
      |                              AND CourseGroupMember.cgmPersonUid = CAST(? AS BIGINT)
      |                            LIMIT 1), -1)
      |                    END
      |    )
      |      ORDER BY CourseAssignmentSubmission.casTimestamp DESC
      |    
      """.trimMargin(),
      postgreSql = """
      |
      |        SELECT CourseAssignmentSubmission.*
      |          FROM CourseAssignmentSubmission
      |         WHERE CourseAssignmentSubmission.casAssignmentUid = ?
      |           AND CourseAssignmentSubmission.casSubmitterUid = 
      |               (
      |        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 CourseAssignmentSubmission.casTimestamp DESC
      |    
      |""".trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,assignmentUid)
      _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.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
          }
        }
      }
    }
  }
}
