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.jdbc.PreparedStatement
import com.ustadmobile.door.jdbc.ext.executeQueryAsyncKmp
import com.ustadmobile.door.jdbc.ext.executeUpdateAsyncKmp
import com.ustadmobile.door.jdbc.ext.mapNextRow
import com.ustadmobile.door.jdbc.ext.mapRows
import com.ustadmobile.door.jdbc.ext.useResults
import com.ustadmobile.door.paging.DoorLimitOffsetPagingSource
import com.ustadmobile.door.room.RoomDatabase
import com.ustadmobile.lib.db.composites.CommentsAndName
import com.ustadmobile.lib.db.composites.PersonAndPicture
import com.ustadmobile.lib.db.entities.Comments
import com.ustadmobile.lib.db.entities.Person
import com.ustadmobile.lib.db.entities.PersonPicture
import kotlin.Boolean
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.collections.List

public class CommentsDao_JdbcImpl(
  public val _db: RoomDatabase,
) : CommentsDao() {
  public val _insertAdapterComments_abort: EntityInsertionAdapter<Comments> = object :
      EntityInsertionAdapter<Comments>(_db) {
    override fun makeSql(returnsId: Boolean): String =
        "INSERT INTO Comments (commentsUid, commentsText, commentsEntityUid, commentsStatus, commentsFromPersonUid, commentsForSubmitterUid, commentsFromSubmitterUid, commentsFlagged, commentsDeleted, commentsDateTimeAdded, commentsLct) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement, entity: Comments) {
      if(entity.commentsUid == 0L) {
        stmt.setObject(1, null)
      } else {
        stmt.setLong(1, entity.commentsUid)
      }
      stmt.setString(2, entity.commentsText)
      stmt.setLong(3, entity.commentsEntityUid)
      stmt.setInt(4, entity.commentsStatus)
      stmt.setLong(5, entity.commentsFromPersonUid)
      stmt.setLong(6, entity.commentsForSubmitterUid)
      stmt.setLong(7, entity.commentsFromSubmitterUid)
      stmt.setBoolean(8, entity.commentsFlagged)
      stmt.setBoolean(9, entity.commentsDeleted)
      stmt.setLong(10, entity.commentsDateTimeAdded)
      stmt.setLong(11, entity.commentsLct)
    }
  }

  override suspend fun insertAsync(comments: Comments): Long {
    val _retVal = _insertAdapterComments_abort.insertAndReturnIdAsync(comments)
    return _retVal
  }

  override suspend fun updateDeletedByCommentUid(
    uid: Long,
    deleted: Boolean,
    changeTime: Long,
  ) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        UPDATE Comments 
      |           SET commentsDeleted = ?,
      |               commentsLct = CAST(? AS BIGINT)
      |         WHERE Comments.commentsUid = CAST(? AS BIGINT)
      |    
      """.trimMargin(),
      postgreSql = """
      |
      |        UPDATE Comments 
      |           SET commentsDeleted = ?,
      |               commentsLct = ?
      |         WHERE Comments.commentsUid = ?
      |    
      |""".trimMargin(),
      readOnly = false,)
    ) { _stmt -> 
      _stmt.setBoolean(1,deleted)
      _stmt.setLong(2,changeTime)
      _stmt.setLong(3,uid)
      _stmt.executeUpdateAsyncKmp()
    }
  }

  override fun findPrivateCommentsForUserByAssignmentUid(
    accountPersonUid: Long,
    assignmentUid: Long,
    includeDeleted: Boolean,
  ): PagingSource<Int, CommentsAndName> = object : DoorLimitOffsetPagingSource<CommentsAndName>(db =
      _db
  , tableNames = arrayOf("ClazzAssignment", "CourseGroupMember", "Comments", "ClazzEnrolment",
      "Person", "PersonPicture")
  ) {
    override suspend fun loadRows(_limit: Int, _offset: Int): List<CommentsAndName> =
        _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT * FROM (
      |        SELECT Comments.*,
      |               Person.firstNames AS firstNames, 
      |               Person.lastName AS lastName,
      |               PersonPicture.personPictureThumbnailUri AS pictureUri
      |          FROM Comments
      |               LEFT JOIN Person 
      |                    ON Person.personUid = Comments.commentsFromPersonUid
      |               LEFT JOIN PersonPicture
      |                    ON PersonPicture.personPictureUid = Comments.commentsFromPersonUid
      |         WHERE Comments.commentsForSubmitterUid = (
      |        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 Comments.commentsForSubmitterUid != 0
      |           AND Comments.commentsEntityUid = CAST(? AS BIGINT)
      |           AND (CAST(Comments.commentsDeleted AS INTEGER) = 0 OR CAST(? AS INTEGER) = 1) 
      |      ORDER BY Comments.commentsDateTimeAdded DESC     
      |    ) AS _PagingData LIMIT ? OFFSET ?
      """.trimMargin(),
      postgreSql = """
      |SELECT * FROM (
      |        SELECT Comments.*,
      |               Person.firstNames AS firstNames, 
      |               Person.lastName AS lastName,
      |               PersonPicture.personPictureThumbnailUri AS pictureUri
      |          FROM Comments
      |               LEFT JOIN Person 
      |                    ON Person.personUid = Comments.commentsFromPersonUid
      |               LEFT JOIN PersonPicture
      |                    ON PersonPicture.personPictureUid = Comments.commentsFromPersonUid
      |         WHERE Comments.commentsForSubmitterUid = (
      |        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 Comments.commentsForSubmitterUid != 0
      |           AND Comments.commentsEntityUid = ?
      |           AND (CAST(Comments.commentsDeleted AS INTEGER) = 0 OR CAST(? AS INTEGER) = 1) 
      |      ORDER BY Comments.commentsDateTimeAdded DESC     
      |    ) AS _PagingData LIMIT ? OFFSET ?
      |""".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.setBoolean(12,includeDeleted)
      _stmt.setInt(13,_limit)
      _stmt.setInt(14,_offset)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          val _tmp_firstNames = _result.getString("firstNames")
          val _tmp_lastName = _result.getString("lastName")
          val _tmp_pictureUri = _result.getString("pictureUri")
          var _tmp_Comments_nullCount = 0
          val _tmp_commentsUid = _result.getLong("commentsUid")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsText = _result.getString("commentsText")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsEntityUid = _result.getLong("commentsEntityUid")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsStatus = _result.getInt("commentsStatus")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsFromPersonUid = _result.getLong("commentsFromPersonUid")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsForSubmitterUid = _result.getLong("commentsForSubmitterUid")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsFromSubmitterUid = _result.getLong("commentsFromSubmitterUid")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsFlagged = _result.getBoolean("commentsFlagged")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsDeleted = _result.getBoolean("commentsDeleted")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsDateTimeAdded = _result.getLong("commentsDateTimeAdded")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsLct = _result.getLong("commentsLct")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_Comments_isAllNull = _tmp_Comments_nullCount == 11
          CommentsAndName().apply {
            this.firstNames = _tmp_firstNames
            this.lastName = _tmp_lastName
            this.pictureUri = _tmp_pictureUri
            if(!_tmp_Comments_isAllNull) {
              this.comment = Comments().apply {
                this.commentsUid = _tmp_commentsUid
                this.commentsText = _tmp_commentsText
                this.commentsEntityUid = _tmp_commentsEntityUid
                this.commentsStatus = _tmp_commentsStatus
                this.commentsFromPersonUid = _tmp_commentsFromPersonUid
                this.commentsForSubmitterUid = _tmp_commentsForSubmitterUid
                this.commentsFromSubmitterUid = _tmp_commentsFromSubmitterUid
                this.commentsFlagged = _tmp_commentsFlagged
                this.commentsDeleted = _tmp_commentsDeleted
                this.commentsDateTimeAdded = _tmp_commentsDateTimeAdded
                this.commentsLct = _tmp_commentsLct
              }
            }
          }
        }
      }
    }

    override suspend fun countRows(): Int = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT COUNT(*) FROM (
      |        SELECT Comments.*,
      |               Person.firstNames AS firstNames, 
      |               Person.lastName AS lastName,
      |               PersonPicture.personPictureThumbnailUri AS pictureUri
      |          FROM Comments
      |               LEFT JOIN Person 
      |                    ON Person.personUid = Comments.commentsFromPersonUid
      |               LEFT JOIN PersonPicture
      |                    ON PersonPicture.personPictureUid = Comments.commentsFromPersonUid
      |         WHERE Comments.commentsForSubmitterUid = (
      |        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 Comments.commentsForSubmitterUid != 0
      |           AND Comments.commentsEntityUid = CAST(? AS BIGINT)
      |           AND (CAST(Comments.commentsDeleted AS INTEGER) = 0 OR CAST(? AS INTEGER) = 1) 
      |      ORDER BY Comments.commentsDateTimeAdded DESC     
      |    ) AS _PagingCount
      """.trimMargin(),
      postgreSql = """
      |SELECT COUNT(*) FROM (
      |        SELECT Comments.*,
      |               Person.firstNames AS firstNames, 
      |               Person.lastName AS lastName,
      |               PersonPicture.personPictureThumbnailUri AS pictureUri
      |          FROM Comments
      |               LEFT JOIN Person 
      |                    ON Person.personUid = Comments.commentsFromPersonUid
      |               LEFT JOIN PersonPicture
      |                    ON PersonPicture.personPictureUid = Comments.commentsFromPersonUid
      |         WHERE Comments.commentsForSubmitterUid = (
      |        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 Comments.commentsForSubmitterUid != 0
      |           AND Comments.commentsEntityUid = ?
      |           AND (CAST(Comments.commentsDeleted AS INTEGER) = 0 OR CAST(? AS INTEGER) = 1) 
      |      ORDER BY Comments.commentsDateTimeAdded DESC     
      |    ) AS _PagingCount
      |""".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.setBoolean(12,includeDeleted)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(0) {
          _result.getInt(1)
        }
      }
    }
  }

  override suspend fun findPrivateCommentsForUserByAssignmentUidPersons(accountPersonUid: Long,
      assignmentUid: Long): List<PersonAndPicture> =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT Person.*, PersonPicture.*
    |          FROM Person
    |               LEFT JOIN PersonPicture
    |                         ON PersonPicture.personPictureUid = Person.personUid
    |         WHERE Person.personUid IN
    |               (SELECT DISTINCT Comments.commentsFromPersonUid
    |                  FROM Comments
    |                 WHERE Comments.commentsForSubmitterUid = (
    |        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 Comments.commentsForSubmitterUid != 0
    |                   AND Comments.commentsEntityUid = CAST(? AS BIGINT)
    |                   AND CAST(Comments.commentsDeleted AS INTEGER) = 0)
    |    
    """.trimMargin(),
    postgreSql = """
    |
    |        SELECT Person.*, PersonPicture.*
    |          FROM Person
    |               LEFT JOIN PersonPicture
    |                         ON PersonPicture.personPictureUid = Person.personUid
    |         WHERE Person.personUid IN
    |               (SELECT DISTINCT Comments.commentsFromPersonUid
    |                  FROM Comments
    |                 WHERE Comments.commentsForSubmitterUid = (
    |        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 Comments.commentsForSubmitterUid != 0
    |                   AND Comments.commentsEntityUid = ?
    |                   AND CAST(Comments.commentsDeleted AS INTEGER) = 0)
    |    
    |""".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.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_dateOfBirth = _result.getLong("dateOfBirth")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personAddress = _result.getString("personAddress")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personOrgId = _result.getString("personOrgId")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personGroupUid = _result.getLong("personGroupUid")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personLct = _result.getLong("personLct")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personCountry = _result.getString("personCountry")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personType = _result.getInt("personType")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personMasterChangeSeqNum = _result.getLong("personMasterChangeSeqNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personLocalChangeSeqNum = _result.getLong("personLocalChangeSeqNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personLastChangedBy = _result.getInt("personLastChangedBy")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_admin = _result.getBoolean("admin")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personNotes = _result.getString("personNotes")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_fatherName = _result.getString("fatherName")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_fatherNumber = _result.getString("fatherNumber")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_motherName = _result.getString("motherName")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_motherNum = _result.getString("motherNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_Person_isAllNull = _tmp_Person_nullCount == 24
        var _tmp_PersonPicture_nullCount = 0
        val _tmp_personPictureUid = _result.getLong("personPictureUid")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_personPictureLct = _result.getLong("personPictureLct")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_personPictureUri = _result.getString("personPictureUri")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_personPictureThumbnailUri = _result.getString("personPictureThumbnailUri")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_fileSize = _result.getInt("fileSize")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_personPictureActive = _result.getBoolean("personPictureActive")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_PersonPicture_isAllNull = _tmp_PersonPicture_nullCount == 6
        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.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 findPrivateCommentsForSubmitterByAssignmentUid(
    submitterUid: Long,
    assignmentUid: Long,
    includeDeleted: Boolean,
  ): PagingSource<Int, CommentsAndName> = object : DoorLimitOffsetPagingSource<CommentsAndName>(db =
      _db
  , tableNames = arrayOf("Comments", "Person", "PersonPicture")
  ) {
    override suspend fun loadRows(_limit: Int, _offset: Int): List<CommentsAndName> =
        _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT * FROM (
      |        SELECT Comments.*,
      |               Person.firstNames AS firstNames, 
      |               Person.lastName AS lastName,
      |               PersonPicture.personPictureThumbnailUri AS pictureUri
      |          FROM Comments
      |               LEFT JOIN Person 
      |                    ON Person.personUid = Comments.commentsFromPersonUid
      |               LEFT JOIN PersonPicture
      |                    ON PersonPicture.personPictureUid = Comments.commentsFromPersonUid
      |         WHERE Comments.commentsForSubmitterUid = CAST(? AS BIGINT)
      |           AND Comments.commentsEntityUid = CAST(? AS BIGINT)
      |           AND (NOT Comments.commentsDeleted OR CAST(? AS INTEGER) = 1)
      |      ORDER BY Comments.commentsDateTimeAdded DESC        
      |    ) AS _PagingData LIMIT ? OFFSET ?
      """.trimMargin(),
      postgreSql = """
      |SELECT * FROM (
      |        SELECT Comments.*,
      |               Person.firstNames AS firstNames, 
      |               Person.lastName AS lastName,
      |               PersonPicture.personPictureThumbnailUri AS pictureUri
      |          FROM Comments
      |               LEFT JOIN Person 
      |                    ON Person.personUid = Comments.commentsFromPersonUid
      |               LEFT JOIN PersonPicture
      |                    ON PersonPicture.personPictureUid = Comments.commentsFromPersonUid
      |         WHERE Comments.commentsForSubmitterUid = ?
      |           AND Comments.commentsEntityUid = ?
      |           AND (NOT Comments.commentsDeleted OR CAST(? AS INTEGER) = 1)
      |      ORDER BY Comments.commentsDateTimeAdded DESC        
      |    ) AS _PagingData LIMIT ? OFFSET ?
      |""".trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,submitterUid)
      _stmt.setLong(2,assignmentUid)
      _stmt.setBoolean(3,includeDeleted)
      _stmt.setInt(4,_limit)
      _stmt.setInt(5,_offset)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          val _tmp_firstNames = _result.getString("firstNames")
          val _tmp_lastName = _result.getString("lastName")
          val _tmp_pictureUri = _result.getString("pictureUri")
          var _tmp_Comments_nullCount = 0
          val _tmp_commentsUid = _result.getLong("commentsUid")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsText = _result.getString("commentsText")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsEntityUid = _result.getLong("commentsEntityUid")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsStatus = _result.getInt("commentsStatus")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsFromPersonUid = _result.getLong("commentsFromPersonUid")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsForSubmitterUid = _result.getLong("commentsForSubmitterUid")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsFromSubmitterUid = _result.getLong("commentsFromSubmitterUid")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsFlagged = _result.getBoolean("commentsFlagged")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsDeleted = _result.getBoolean("commentsDeleted")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsDateTimeAdded = _result.getLong("commentsDateTimeAdded")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsLct = _result.getLong("commentsLct")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_Comments_isAllNull = _tmp_Comments_nullCount == 11
          CommentsAndName().apply {
            this.firstNames = _tmp_firstNames
            this.lastName = _tmp_lastName
            this.pictureUri = _tmp_pictureUri
            if(!_tmp_Comments_isAllNull) {
              this.comment = Comments().apply {
                this.commentsUid = _tmp_commentsUid
                this.commentsText = _tmp_commentsText
                this.commentsEntityUid = _tmp_commentsEntityUid
                this.commentsStatus = _tmp_commentsStatus
                this.commentsFromPersonUid = _tmp_commentsFromPersonUid
                this.commentsForSubmitterUid = _tmp_commentsForSubmitterUid
                this.commentsFromSubmitterUid = _tmp_commentsFromSubmitterUid
                this.commentsFlagged = _tmp_commentsFlagged
                this.commentsDeleted = _tmp_commentsDeleted
                this.commentsDateTimeAdded = _tmp_commentsDateTimeAdded
                this.commentsLct = _tmp_commentsLct
              }
            }
          }
        }
      }
    }

    override suspend fun countRows(): Int = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT COUNT(*) FROM (
      |        SELECT Comments.*,
      |               Person.firstNames AS firstNames, 
      |               Person.lastName AS lastName,
      |               PersonPicture.personPictureThumbnailUri AS pictureUri
      |          FROM Comments
      |               LEFT JOIN Person 
      |                    ON Person.personUid = Comments.commentsFromPersonUid
      |               LEFT JOIN PersonPicture
      |                    ON PersonPicture.personPictureUid = Comments.commentsFromPersonUid
      |         WHERE Comments.commentsForSubmitterUid = CAST(? AS BIGINT)
      |           AND Comments.commentsEntityUid = CAST(? AS BIGINT)
      |           AND (NOT Comments.commentsDeleted OR CAST(? AS INTEGER) = 1)
      |      ORDER BY Comments.commentsDateTimeAdded DESC        
      |    ) AS _PagingCount
      """.trimMargin(),
      postgreSql = """
      |SELECT COUNT(*) FROM (
      |        SELECT Comments.*,
      |               Person.firstNames AS firstNames, 
      |               Person.lastName AS lastName,
      |               PersonPicture.personPictureThumbnailUri AS pictureUri
      |          FROM Comments
      |               LEFT JOIN Person 
      |                    ON Person.personUid = Comments.commentsFromPersonUid
      |               LEFT JOIN PersonPicture
      |                    ON PersonPicture.personPictureUid = Comments.commentsFromPersonUid
      |         WHERE Comments.commentsForSubmitterUid = ?
      |           AND Comments.commentsEntityUid = ?
      |           AND (NOT Comments.commentsDeleted OR CAST(? AS INTEGER) = 1)
      |      ORDER BY Comments.commentsDateTimeAdded DESC        
      |    ) AS _PagingCount
      |""".trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,submitterUid)
      _stmt.setLong(2,assignmentUid)
      _stmt.setBoolean(3,includeDeleted)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(0) {
          _result.getInt(1)
        }
      }
    }
  }

  override fun findPrivateCommentsForSubmitterByAssignmentUidPersons(submitterUid: Long,
      assignmentUid: Long): List<PersonAndPicture> =
      _db.prepareAndUseStatement(PreparedStatementConfig(
    sql = """
    |
    |        SELECT Person.*, PersonPicture.*
    |          FROM Person
    |               LEFT JOIN PersonPicture
    |                         ON PersonPicture.personPictureUid = Person.personUid 
    |         WHERE Person.personUid IN 
    |               (SELECT Comments.commentsFromPersonUid
    |                  FROM Comments
    |                 WHERE Comments.commentsForSubmitterUid = CAST(? AS BIGINT)
    |                   AND Comments.commentsEntityUid = CAST(? AS BIGINT)
    |                   AND NOT Comments.commentsDeleted) 
    |    
    """.trimMargin(),
    postgreSql = """
    |
    |        SELECT Person.*, PersonPicture.*
    |          FROM Person
    |               LEFT JOIN PersonPicture
    |                         ON PersonPicture.personPictureUid = Person.personUid 
    |         WHERE Person.personUid IN 
    |               (SELECT Comments.commentsFromPersonUid
    |                  FROM Comments
    |                 WHERE Comments.commentsForSubmitterUid = ?
    |                   AND Comments.commentsEntityUid = ?
    |                   AND NOT Comments.commentsDeleted) 
    |    
    |""".trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,submitterUid)
    _stmt.setLong(2,assignmentUid)
    _stmt.executeQuery().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_dateOfBirth = _result.getLong("dateOfBirth")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personAddress = _result.getString("personAddress")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personOrgId = _result.getString("personOrgId")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personGroupUid = _result.getLong("personGroupUid")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personLct = _result.getLong("personLct")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personCountry = _result.getString("personCountry")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personType = _result.getInt("personType")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personMasterChangeSeqNum = _result.getLong("personMasterChangeSeqNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personLocalChangeSeqNum = _result.getLong("personLocalChangeSeqNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personLastChangedBy = _result.getInt("personLastChangedBy")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_admin = _result.getBoolean("admin")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personNotes = _result.getString("personNotes")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_fatherName = _result.getString("fatherName")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_fatherNumber = _result.getString("fatherNumber")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_motherName = _result.getString("motherName")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_motherNum = _result.getString("motherNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_Person_isAllNull = _tmp_Person_nullCount == 24
        var _tmp_PersonPicture_nullCount = 0
        val _tmp_personPictureUid = _result.getLong("personPictureUid")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_personPictureLct = _result.getLong("personPictureLct")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_personPictureUri = _result.getString("personPictureUri")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_personPictureThumbnailUri = _result.getString("personPictureThumbnailUri")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_fileSize = _result.getInt("fileSize")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_personPictureActive = _result.getBoolean("personPictureActive")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_PersonPicture_isAllNull = _tmp_PersonPicture_nullCount == 6
        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.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 findCourseCommentsByAssignmentUid(assignmentUid: Long, includeDeleted: Boolean):
      PagingSource<Int, CommentsAndName> = object : DoorLimitOffsetPagingSource<CommentsAndName>(db
      = _db
  , tableNames = arrayOf("Comments", "Person", "PersonPicture")
  ) {
    override suspend fun loadRows(_limit: Int, _offset: Int): List<CommentsAndName> =
        _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT * FROM (
      |        SELECT Comments.*,
      |               Person.firstNames AS firstNames, 
      |               Person.lastName AS lastName,
      |               PersonPicture.personPictureThumbnailUri AS pictureUri
      |          FROM Comments
      |               LEFT JOIN Person 
      |                    ON Person.personUid = Comments.commentsFromPersonUid
      |               LEFT JOIN PersonPicture
      |                    ON PersonPicture.personPictureUid = Comments.commentsFromPersonUid
      |         WHERE Comments.commentsEntityUid = CAST(? AS BIGINT)
      |           AND Comments.commentsForSubmitterUid = 0
      |           AND (CAST(Comments.commentsDeleted AS INTEGER) = 0 OR CAST(? AS INTEGER) = 1)
      |      ORDER BY Comments.commentsDateTimeAdded DESC     
      |    ) AS _PagingData LIMIT ? OFFSET ?
      """.trimMargin(),
      postgreSql = """
      |SELECT * FROM (
      |        SELECT Comments.*,
      |               Person.firstNames AS firstNames, 
      |               Person.lastName AS lastName,
      |               PersonPicture.personPictureThumbnailUri AS pictureUri
      |          FROM Comments
      |               LEFT JOIN Person 
      |                    ON Person.personUid = Comments.commentsFromPersonUid
      |               LEFT JOIN PersonPicture
      |                    ON PersonPicture.personPictureUid = Comments.commentsFromPersonUid
      |         WHERE Comments.commentsEntityUid = ?
      |           AND Comments.commentsForSubmitterUid = 0
      |           AND (CAST(Comments.commentsDeleted AS INTEGER) = 0 OR CAST(? AS INTEGER) = 1)
      |      ORDER BY Comments.commentsDateTimeAdded DESC     
      |    ) AS _PagingData LIMIT ? OFFSET ?
      |""".trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,assignmentUid)
      _stmt.setBoolean(2,includeDeleted)
      _stmt.setInt(3,_limit)
      _stmt.setInt(4,_offset)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          val _tmp_firstNames = _result.getString("firstNames")
          val _tmp_lastName = _result.getString("lastName")
          val _tmp_pictureUri = _result.getString("pictureUri")
          var _tmp_Comments_nullCount = 0
          val _tmp_commentsUid = _result.getLong("commentsUid")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsText = _result.getString("commentsText")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsEntityUid = _result.getLong("commentsEntityUid")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsStatus = _result.getInt("commentsStatus")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsFromPersonUid = _result.getLong("commentsFromPersonUid")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsForSubmitterUid = _result.getLong("commentsForSubmitterUid")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsFromSubmitterUid = _result.getLong("commentsFromSubmitterUid")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsFlagged = _result.getBoolean("commentsFlagged")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsDeleted = _result.getBoolean("commentsDeleted")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsDateTimeAdded = _result.getLong("commentsDateTimeAdded")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_commentsLct = _result.getLong("commentsLct")
          if(_result.wasNull()) _tmp_Comments_nullCount++
          val _tmp_Comments_isAllNull = _tmp_Comments_nullCount == 11
          CommentsAndName().apply {
            this.firstNames = _tmp_firstNames
            this.lastName = _tmp_lastName
            this.pictureUri = _tmp_pictureUri
            if(!_tmp_Comments_isAllNull) {
              this.comment = Comments().apply {
                this.commentsUid = _tmp_commentsUid
                this.commentsText = _tmp_commentsText
                this.commentsEntityUid = _tmp_commentsEntityUid
                this.commentsStatus = _tmp_commentsStatus
                this.commentsFromPersonUid = _tmp_commentsFromPersonUid
                this.commentsForSubmitterUid = _tmp_commentsForSubmitterUid
                this.commentsFromSubmitterUid = _tmp_commentsFromSubmitterUid
                this.commentsFlagged = _tmp_commentsFlagged
                this.commentsDeleted = _tmp_commentsDeleted
                this.commentsDateTimeAdded = _tmp_commentsDateTimeAdded
                this.commentsLct = _tmp_commentsLct
              }
            }
          }
        }
      }
    }

    override suspend fun countRows(): Int = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT COUNT(*) FROM (
      |        SELECT Comments.*,
      |               Person.firstNames AS firstNames, 
      |               Person.lastName AS lastName,
      |               PersonPicture.personPictureThumbnailUri AS pictureUri
      |          FROM Comments
      |               LEFT JOIN Person 
      |                    ON Person.personUid = Comments.commentsFromPersonUid
      |               LEFT JOIN PersonPicture
      |                    ON PersonPicture.personPictureUid = Comments.commentsFromPersonUid
      |         WHERE Comments.commentsEntityUid = CAST(? AS BIGINT)
      |           AND Comments.commentsForSubmitterUid = 0
      |           AND (CAST(Comments.commentsDeleted AS INTEGER) = 0 OR CAST(? AS INTEGER) = 1)
      |      ORDER BY Comments.commentsDateTimeAdded DESC     
      |    ) AS _PagingCount
      """.trimMargin(),
      postgreSql = """
      |SELECT COUNT(*) FROM (
      |        SELECT Comments.*,
      |               Person.firstNames AS firstNames, 
      |               Person.lastName AS lastName,
      |               PersonPicture.personPictureThumbnailUri AS pictureUri
      |          FROM Comments
      |               LEFT JOIN Person 
      |                    ON Person.personUid = Comments.commentsFromPersonUid
      |               LEFT JOIN PersonPicture
      |                    ON PersonPicture.personPictureUid = Comments.commentsFromPersonUid
      |         WHERE Comments.commentsEntityUid = ?
      |           AND Comments.commentsForSubmitterUid = 0
      |           AND (CAST(Comments.commentsDeleted AS INTEGER) = 0 OR CAST(? AS INTEGER) = 1)
      |      ORDER BY Comments.commentsDateTimeAdded DESC     
      |    ) AS _PagingCount
      |""".trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,assignmentUid)
      _stmt.setBoolean(2,includeDeleted)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(0) {
          _result.getInt(1)
        }
      }
    }
  }

  override suspend fun findCourseCommentsByAssignmentUidPersons(assignmentUid: Long): List<Person> =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT Person.*, PersonPicture.*
    |          FROM Person
    |               LEFT JOIN PersonPicture
    |                         ON PersonPicture.personPictureUid = Person.personUid
    |         WHERE Person.personUid IN
    |               (SELECT DISTINCT Comments.commentsFromPersonUid
    |                  FROM Comments
    |                       LEFT JOIN Person 
    |                            ON Person.personUid = Comments.commentsFromPersonUid
    |                 WHERE Comments.commentsEntityUid = CAST(? AS BIGINT)
    |                   AND Comments.commentsForSubmitterUid = 0)
    |    
    """.trimMargin(),
    postgreSql = """
    |
    |        SELECT Person.*, PersonPicture.*
    |          FROM Person
    |               LEFT JOIN PersonPicture
    |                         ON PersonPicture.personPictureUid = Person.personUid
    |         WHERE Person.personUid IN
    |               (SELECT DISTINCT Comments.commentsFromPersonUid
    |                  FROM Comments
    |                       LEFT JOIN Person 
    |                            ON Person.personUid = Comments.commentsFromPersonUid
    |                 WHERE Comments.commentsEntityUid = ?
    |                   AND Comments.commentsForSubmitterUid = 0)
    |    
    |""".trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,assignmentUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_personUid = _result.getLong("personUid")
        val _tmp_username = _result.getString("username")
        val _tmp_firstNames = _result.getString("firstNames")
        val _tmp_lastName = _result.getString("lastName")
        val _tmp_emailAddr = _result.getString("emailAddr")
        val _tmp_phoneNum = _result.getString("phoneNum")
        val _tmp_gender = _result.getInt("gender")
        val _tmp_active = _result.getBoolean("active")
        val _tmp_dateOfBirth = _result.getLong("dateOfBirth")
        val _tmp_personAddress = _result.getString("personAddress")
        val _tmp_personOrgId = _result.getString("personOrgId")
        val _tmp_personGroupUid = _result.getLong("personGroupUid")
        val _tmp_personLct = _result.getLong("personLct")
        val _tmp_personCountry = _result.getString("personCountry")
        val _tmp_personType = _result.getInt("personType")
        val _tmp_personMasterChangeSeqNum = _result.getLong("personMasterChangeSeqNum")
        val _tmp_personLocalChangeSeqNum = _result.getLong("personLocalChangeSeqNum")
        val _tmp_personLastChangedBy = _result.getInt("personLastChangedBy")
        val _tmp_admin = _result.getBoolean("admin")
        val _tmp_personNotes = _result.getString("personNotes")
        val _tmp_fatherName = _result.getString("fatherName")
        val _tmp_fatherNumber = _result.getString("fatherNumber")
        val _tmp_motherName = _result.getString("motherName")
        val _tmp_motherNum = _result.getString("motherNum")
        Person().apply {
          this.personUid = _tmp_personUid
          this.username = _tmp_username
          this.firstNames = _tmp_firstNames
          this.lastName = _tmp_lastName
          this.emailAddr = _tmp_emailAddr
          this.phoneNum = _tmp_phoneNum
          this.gender = _tmp_gender
          this.active = _tmp_active
          this.dateOfBirth = _tmp_dateOfBirth
          this.personAddress = _tmp_personAddress
          this.personOrgId = _tmp_personOrgId
          this.personGroupUid = _tmp_personGroupUid
          this.personLct = _tmp_personLct
          this.personCountry = _tmp_personCountry
          this.personType = _tmp_personType
          this.personMasterChangeSeqNum = _tmp_personMasterChangeSeqNum
          this.personLocalChangeSeqNum = _tmp_personLocalChangeSeqNum
          this.personLastChangedBy = _tmp_personLastChangedBy
          this.admin = _tmp_admin
          this.personNotes = _tmp_personNotes
          this.fatherName = _tmp_fatherName
          this.fatherNumber = _tmp_fatherNumber
          this.motherName = _tmp_motherName
          this.motherNum = _tmp_motherNum
        }
      }
    }
  }
}
