package com.ustadmobile.core.db.dao

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.executeUpdateAsyncKmp
import com.ustadmobile.door.jdbc.ext.mapRows
import com.ustadmobile.door.jdbc.ext.useResults
import com.ustadmobile.door.room.RoomDatabase
import com.ustadmobile.lib.db.composites.CourseAssignmentSubmissionFileAndTransferJob
import com.ustadmobile.lib.db.entities.CourseAssignmentSubmissionFile
import com.ustadmobile.lib.db.entities.TransferJobItem
import kotlin.Boolean
import kotlin.Long
import kotlin.String
import kotlin.collections.List
import kotlinx.coroutines.flow.Flow

public class CourseAssignmentSubmissionFileDao_JdbcImpl(
  public val _db: RoomDatabase,
) : CourseAssignmentSubmissionFileDao() {
  public val _insertAdapterCourseAssignmentSubmissionFile_abort:
      EntityInsertionAdapter<CourseAssignmentSubmissionFile> = object :
      EntityInsertionAdapter<CourseAssignmentSubmissionFile>(_db) {
    override fun makeSql(returnsId: Boolean): String =
        "INSERT INTO CourseAssignmentSubmissionFile (casaUid, casaSubmissionUid, casaSubmitterUid, casaCaUid, casaClazzUid, casaMimeType, casaFileName, casaUri, casaSize, casaTimestamp, casaDeleted) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement,
        entity: CourseAssignmentSubmissionFile) {
      if(entity.casaUid == 0L) {
        stmt.setObject(1, null)
      } else {
        stmt.setLong(1, entity.casaUid)
      }
      stmt.setLong(2, entity.casaSubmissionUid)
      stmt.setLong(3, entity.casaSubmitterUid)
      stmt.setLong(4, entity.casaCaUid)
      stmt.setLong(5, entity.casaClazzUid)
      stmt.setString(6, entity.casaMimeType)
      stmt.setString(7, entity.casaFileName)
      stmt.setString(8, entity.casaUri)
      stmt.setInt(9, entity.casaSize)
      stmt.setLong(10, entity.casaTimestamp)
      stmt.setBoolean(11, entity.casaDeleted)
    }
  }

  override suspend fun insertListAsync(entityList: List<CourseAssignmentSubmissionFile>) {
    _insertAdapterCourseAssignmentSubmissionFile_abort.insertListAsync(entityList)
  }

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

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

  public override fun insertList(entityList: List<CourseAssignmentSubmissionFile>) {
    _insertAdapterCourseAssignmentSubmissionFile_abort.insertList(entityList)
  }

  public override fun update(entity: CourseAssignmentSubmissionFile) {
    val _sql =
        "UPDATE CourseAssignmentSubmissionFile SET casaSubmissionUid = ?, casaSubmitterUid = ?, casaCaUid = ?, casaClazzUid = ?, casaMimeType = ?, casaFileName = ?, casaUri = ?, casaSize = ?, casaTimestamp = ?, casaDeleted = ? WHERE casaUid = ?"
    _db.prepareAndUseStatement(_sql) {
       _stmt ->
      _stmt.setLong(1, entity.casaSubmissionUid)
      _stmt.setLong(2, entity.casaSubmitterUid)
      _stmt.setLong(3, entity.casaCaUid)
      _stmt.setLong(4, entity.casaClazzUid)
      _stmt.setString(5, entity.casaMimeType)
      _stmt.setString(6, entity.casaFileName)
      _stmt.setString(7, entity.casaUri)
      _stmt.setInt(8, entity.casaSize)
      _stmt.setLong(9, entity.casaTimestamp)
      _stmt.setBoolean(10, entity.casaDeleted)
      _stmt.setLong(11, entity.casaUid)
      _stmt.executeUpdate()
    }
  }

  override fun getBySubmissionUid(submissionUid: Long):
      Flow<List<CourseAssignmentSubmissionFileAndTransferJob>> =
      _db.doorFlow(arrayOf("TransferJobItem", "CourseAssignmentSubmissionFile")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT CourseAssignmentSubmissionFile.*, TransferJobItem.*
      |          FROM CourseAssignmentSubmissionFile
      |               LEFT JOIN TransferJobItem
      |                         ON TransferJobItem.tjiEntityUid = CourseAssignmentSubmissionFile.casaUid
      |                            AND TransferJobItem.tjiTableId = 90
      |         WHERE CourseAssignmentSubmissionFile.casaSubmissionUid = CAST(? AS BIGINT)
      |           AND NOT CourseAssignmentSubmissionFile.casaDeleted
      |    
      """.trimMargin(),
      postgreSql = """
      |
      |        SELECT CourseAssignmentSubmissionFile.*, TransferJobItem.*
      |          FROM CourseAssignmentSubmissionFile
      |               LEFT JOIN TransferJobItem
      |                         ON TransferJobItem.tjiEntityUid = CourseAssignmentSubmissionFile.casaUid
      |                            AND TransferJobItem.tjiTableId = 90
      |         WHERE CourseAssignmentSubmissionFile.casaSubmissionUid = ?
      |           AND NOT CourseAssignmentSubmissionFile.casaDeleted
      |    
      |""".trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,submissionUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          var _tmp_CourseAssignmentSubmissionFile_nullCount = 0
          val _tmp_casaUid = _result.getLong("casaUid")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaSubmissionUid = _result.getLong("casaSubmissionUid")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaSubmitterUid = _result.getLong("casaSubmitterUid")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaCaUid = _result.getLong("casaCaUid")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaClazzUid = _result.getLong("casaClazzUid")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaMimeType = _result.getString("casaMimeType")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaFileName = _result.getString("casaFileName")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaUri = _result.getString("casaUri")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaSize = _result.getInt("casaSize")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaTimestamp = _result.getLong("casaTimestamp")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaDeleted = _result.getBoolean("casaDeleted")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_CourseAssignmentSubmissionFile_isAllNull = _tmp_CourseAssignmentSubmissionFile_nullCount == 11
          var _tmp_TransferJobItem_nullCount = 0
          val _tmp_tjiUid = _result.getInt("tjiUid")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiTjUid = _result.getInt("tjiTjUid")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjTotalSize = _result.getLong("tjTotalSize")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjTransferred = _result.getLong("tjTransferred")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjAttemptCount = _result.getInt("tjAttemptCount")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiSrc = _result.getString("tjiSrc")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiDest = _result.getString("tjiDest")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiType = _result.getInt("tjiType")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiStatus = _result.getInt("tjiStatus")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiTableId = _result.getInt("tjiTableId")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiEntityUid = _result.getLong("tjiEntityUid")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiEntityEtag = _result.getLong("tjiEntityEtag")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiLockIdToRelease = _result.getLong("tjiLockIdToRelease")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiPartialTmpFile = _result.getString("tjiPartialTmpFile")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_TransferJobItem_isAllNull = _tmp_TransferJobItem_nullCount == 14
          CourseAssignmentSubmissionFileAndTransferJob().apply {
            if(!_tmp_CourseAssignmentSubmissionFile_isAllNull) {
              this.submissionFile = CourseAssignmentSubmissionFile().apply {
                this.casaUid = _tmp_casaUid
                this.casaSubmissionUid = _tmp_casaSubmissionUid
                this.casaSubmitterUid = _tmp_casaSubmitterUid
                this.casaCaUid = _tmp_casaCaUid
                this.casaClazzUid = _tmp_casaClazzUid
                this.casaMimeType = _tmp_casaMimeType
                this.casaFileName = _tmp_casaFileName
                this.casaUri = _tmp_casaUri
                this.casaSize = _tmp_casaSize
                this.casaTimestamp = _tmp_casaTimestamp
                this.casaDeleted = _tmp_casaDeleted
              }
            }
            if(!_tmp_TransferJobItem_isAllNull) {
              this.transferJobItem = TransferJobItem().apply {
                this.tjiUid = _tmp_tjiUid
                this.tjiTjUid = _tmp_tjiTjUid
                this.tjTotalSize = _tmp_tjTotalSize
                this.tjTransferred = _tmp_tjTransferred
                this.tjAttemptCount = _tmp_tjAttemptCount
                this.tjiSrc = _tmp_tjiSrc
                this.tjiDest = _tmp_tjiDest
                this.tjiType = _tmp_tjiType
                this.tjiStatus = _tmp_tjiStatus
                this.tjiTableId = _tmp_tjiTableId
                this.tjiEntityUid = _tmp_tjiEntityUid
                this.tjiEntityEtag = _tmp_tjiEntityEtag
                this.tjiLockIdToRelease = _tmp_tjiLockIdToRelease
                this.tjiPartialTmpFile = _tmp_tjiPartialTmpFile
              }
            }
          }
        }
      }
    }
  }

  override fun getByAssignmentUidAndPersonUid(accountPersonUid: Long, assignmentUid: Long):
      Flow<List<CourseAssignmentSubmissionFileAndTransferJob>> =
      _db.doorFlow(arrayOf("CourseAssignmentSubmission", "ClazzAssignment", "TransferJobItem",
      "CourseGroupMember", "CourseAssignmentSubmissionFile", "ClazzEnrolment")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT CourseAssignmentSubmissionFile.*, TransferJobItem.*
      |          FROM CourseAssignmentSubmissionFile
      |               LEFT JOIN TransferJobItem
      |                         ON TransferJobItem.tjiEntityUid = CourseAssignmentSubmissionFile.casaUid
      |                            AND TransferJobItem.tjiTableId = 90
      |         WHERE CourseAssignmentSubmissionFile.casaSubmissionUid IN
      |               (SELECT CourseAssignmentSubmission.casUid
      |                  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
      |    ))
      |           AND NOT CourseAssignmentSubmissionFile.casaDeleted        
      |    
      """.trimMargin(),
      postgreSql = """
      |
      |        SELECT CourseAssignmentSubmissionFile.*, TransferJobItem.*
      |          FROM CourseAssignmentSubmissionFile
      |               LEFT JOIN TransferJobItem
      |                         ON TransferJobItem.tjiEntityUid = CourseAssignmentSubmissionFile.casaUid
      |                            AND TransferJobItem.tjiTableId = 90
      |         WHERE CourseAssignmentSubmissionFile.casaSubmissionUid IN
      |               (SELECT CourseAssignmentSubmission.casUid
      |                  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
      |    ))
      |           AND NOT CourseAssignmentSubmissionFile.casaDeleted        
      |    
      |""".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 {
          var _tmp_CourseAssignmentSubmissionFile_nullCount = 0
          val _tmp_casaUid = _result.getLong("casaUid")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaSubmissionUid = _result.getLong("casaSubmissionUid")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaSubmitterUid = _result.getLong("casaSubmitterUid")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaCaUid = _result.getLong("casaCaUid")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaClazzUid = _result.getLong("casaClazzUid")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaMimeType = _result.getString("casaMimeType")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaFileName = _result.getString("casaFileName")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaUri = _result.getString("casaUri")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaSize = _result.getInt("casaSize")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaTimestamp = _result.getLong("casaTimestamp")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaDeleted = _result.getBoolean("casaDeleted")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_CourseAssignmentSubmissionFile_isAllNull = _tmp_CourseAssignmentSubmissionFile_nullCount == 11
          var _tmp_TransferJobItem_nullCount = 0
          val _tmp_tjiUid = _result.getInt("tjiUid")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiTjUid = _result.getInt("tjiTjUid")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjTotalSize = _result.getLong("tjTotalSize")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjTransferred = _result.getLong("tjTransferred")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjAttemptCount = _result.getInt("tjAttemptCount")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiSrc = _result.getString("tjiSrc")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiDest = _result.getString("tjiDest")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiType = _result.getInt("tjiType")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiStatus = _result.getInt("tjiStatus")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiTableId = _result.getInt("tjiTableId")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiEntityUid = _result.getLong("tjiEntityUid")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiEntityEtag = _result.getLong("tjiEntityEtag")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiLockIdToRelease = _result.getLong("tjiLockIdToRelease")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiPartialTmpFile = _result.getString("tjiPartialTmpFile")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_TransferJobItem_isAllNull = _tmp_TransferJobItem_nullCount == 14
          CourseAssignmentSubmissionFileAndTransferJob().apply {
            if(!_tmp_CourseAssignmentSubmissionFile_isAllNull) {
              this.submissionFile = CourseAssignmentSubmissionFile().apply {
                this.casaUid = _tmp_casaUid
                this.casaSubmissionUid = _tmp_casaSubmissionUid
                this.casaSubmitterUid = _tmp_casaSubmitterUid
                this.casaCaUid = _tmp_casaCaUid
                this.casaClazzUid = _tmp_casaClazzUid
                this.casaMimeType = _tmp_casaMimeType
                this.casaFileName = _tmp_casaFileName
                this.casaUri = _tmp_casaUri
                this.casaSize = _tmp_casaSize
                this.casaTimestamp = _tmp_casaTimestamp
                this.casaDeleted = _tmp_casaDeleted
              }
            }
            if(!_tmp_TransferJobItem_isAllNull) {
              this.transferJobItem = TransferJobItem().apply {
                this.tjiUid = _tmp_tjiUid
                this.tjiTjUid = _tmp_tjiTjUid
                this.tjTotalSize = _tmp_tjTotalSize
                this.tjTransferred = _tmp_tjTransferred
                this.tjAttemptCount = _tmp_tjAttemptCount
                this.tjiSrc = _tmp_tjiSrc
                this.tjiDest = _tmp_tjiDest
                this.tjiType = _tmp_tjiType
                this.tjiStatus = _tmp_tjiStatus
                this.tjiTableId = _tmp_tjiTableId
                this.tjiEntityUid = _tmp_tjiEntityUid
                this.tjiEntityEtag = _tmp_tjiEntityEtag
                this.tjiLockIdToRelease = _tmp_tjiLockIdToRelease
                this.tjiPartialTmpFile = _tmp_tjiPartialTmpFile
              }
            }
          }
        }
      }
    }
  }

  override fun getAllSubmissionFilesFromSubmitterAsFlow(submitterUid: Long, assignmentUid: Long):
      Flow<List<CourseAssignmentSubmissionFileAndTransferJob>> =
      _db.doorFlow(arrayOf("TransferJobItem", "CourseAssignmentSubmissionFile")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT CourseAssignmentSubmissionFile.*, TransferJobItem.*
      |          FROM CourseAssignmentSubmissionFile
      |               LEFT JOIN TransferJobItem
      |                         ON TransferJobItem.tjiEntityUid = CourseAssignmentSubmissionFile.casaUid
      |                            AND TransferJobItem.tjiTableId = 90
      |         WHERE CourseAssignmentSubmissionFile.casaSubmitterUid = CAST(? AS BIGINT)
      |           AND CourseAssignmentSubmissionFile.casaCaUid = CAST(? AS BIGINT)
      |           AND NOT CourseAssignmentSubmissionFile.casaDeleted
      |    
      """.trimMargin(),
      postgreSql = """
      |
      |        SELECT CourseAssignmentSubmissionFile.*, TransferJobItem.*
      |          FROM CourseAssignmentSubmissionFile
      |               LEFT JOIN TransferJobItem
      |                         ON TransferJobItem.tjiEntityUid = CourseAssignmentSubmissionFile.casaUid
      |                            AND TransferJobItem.tjiTableId = 90
      |         WHERE CourseAssignmentSubmissionFile.casaSubmitterUid = ?
      |           AND CourseAssignmentSubmissionFile.casaCaUid = ?
      |           AND NOT CourseAssignmentSubmissionFile.casaDeleted
      |    
      |""".trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,submitterUid)
      _stmt.setLong(2,assignmentUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          var _tmp_CourseAssignmentSubmissionFile_nullCount = 0
          val _tmp_casaUid = _result.getLong("casaUid")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaSubmissionUid = _result.getLong("casaSubmissionUid")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaSubmitterUid = _result.getLong("casaSubmitterUid")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaCaUid = _result.getLong("casaCaUid")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaClazzUid = _result.getLong("casaClazzUid")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaMimeType = _result.getString("casaMimeType")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaFileName = _result.getString("casaFileName")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaUri = _result.getString("casaUri")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaSize = _result.getInt("casaSize")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaTimestamp = _result.getLong("casaTimestamp")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_casaDeleted = _result.getBoolean("casaDeleted")
          if(_result.wasNull()) _tmp_CourseAssignmentSubmissionFile_nullCount++
          val _tmp_CourseAssignmentSubmissionFile_isAllNull = _tmp_CourseAssignmentSubmissionFile_nullCount == 11
          var _tmp_TransferJobItem_nullCount = 0
          val _tmp_tjiUid = _result.getInt("tjiUid")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiTjUid = _result.getInt("tjiTjUid")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjTotalSize = _result.getLong("tjTotalSize")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjTransferred = _result.getLong("tjTransferred")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjAttemptCount = _result.getInt("tjAttemptCount")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiSrc = _result.getString("tjiSrc")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiDest = _result.getString("tjiDest")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiType = _result.getInt("tjiType")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiStatus = _result.getInt("tjiStatus")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiTableId = _result.getInt("tjiTableId")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiEntityUid = _result.getLong("tjiEntityUid")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiEntityEtag = _result.getLong("tjiEntityEtag")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiLockIdToRelease = _result.getLong("tjiLockIdToRelease")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_tjiPartialTmpFile = _result.getString("tjiPartialTmpFile")
          if(_result.wasNull()) _tmp_TransferJobItem_nullCount++
          val _tmp_TransferJobItem_isAllNull = _tmp_TransferJobItem_nullCount == 14
          CourseAssignmentSubmissionFileAndTransferJob().apply {
            if(!_tmp_CourseAssignmentSubmissionFile_isAllNull) {
              this.submissionFile = CourseAssignmentSubmissionFile().apply {
                this.casaUid = _tmp_casaUid
                this.casaSubmissionUid = _tmp_casaSubmissionUid
                this.casaSubmitterUid = _tmp_casaSubmitterUid
                this.casaCaUid = _tmp_casaCaUid
                this.casaClazzUid = _tmp_casaClazzUid
                this.casaMimeType = _tmp_casaMimeType
                this.casaFileName = _tmp_casaFileName
                this.casaUri = _tmp_casaUri
                this.casaSize = _tmp_casaSize
                this.casaTimestamp = _tmp_casaTimestamp
                this.casaDeleted = _tmp_casaDeleted
              }
            }
            if(!_tmp_TransferJobItem_isAllNull) {
              this.transferJobItem = TransferJobItem().apply {
                this.tjiUid = _tmp_tjiUid
                this.tjiTjUid = _tmp_tjiTjUid
                this.tjTotalSize = _tmp_tjTotalSize
                this.tjTransferred = _tmp_tjTransferred
                this.tjAttemptCount = _tmp_tjAttemptCount
                this.tjiSrc = _tmp_tjiSrc
                this.tjiDest = _tmp_tjiDest
                this.tjiType = _tmp_tjiType
                this.tjiStatus = _tmp_tjiStatus
                this.tjiTableId = _tmp_tjiTableId
                this.tjiEntityUid = _tmp_tjiEntityUid
                this.tjiEntityEtag = _tmp_tjiEntityEtag
                this.tjiLockIdToRelease = _tmp_tjiLockIdToRelease
                this.tjiPartialTmpFile = _tmp_tjiPartialTmpFile
              }
            }
          }
        }
      }
    }
  }

  override suspend fun updateUri(
    casaUid: Long,
    uri: String,
    updateTime: Long,
  ) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        UPDATE CourseAssignmentSubmissionFile
      |           SET casaUri = ?,
      |               casaTimestamp = CAST(? AS BIGINT)
      |         WHERE casaUid = CAST(? AS BIGINT)
      |    
      """.trimMargin(),
      postgreSql = """
      |
      |        UPDATE CourseAssignmentSubmissionFile
      |           SET casaUri = ?,
      |               casaTimestamp = ?
      |         WHERE casaUid = ?
      |    
      |""".trimMargin(),
      readOnly = false,)
    ) { _stmt -> 
      _stmt.setString(1,uri)
      _stmt.setLong(2,updateTime)
      _stmt.setLong(3,casaUid)
      _stmt.executeUpdateAsyncKmp()
    }
  }

  override suspend fun setDeleted(
    casaUid: Long,
    deleted: Boolean,
    updateTime: Long,
  ) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        UPDATE CourseAssignmentSubmissionFile
      |           SET casaDeleted = ?,
      |               casaTimestamp = CAST(? AS BIGINT)
      |         WHERE casaUid = CAST(? AS BIGINT)
      |    
      """.trimMargin(),
      postgreSql = """
      |
      |        UPDATE CourseAssignmentSubmissionFile
      |           SET casaDeleted = ?,
      |               casaTimestamp = ?
      |         WHERE casaUid = ?
      |    
      |""".trimMargin(),
      readOnly = false,)
    ) { _stmt -> 
      _stmt.setBoolean(1,deleted)
      _stmt.setLong(2,updateTime)
      _stmt.setLong(3,casaUid)
      _stmt.executeUpdateAsyncKmp()
    }
  }
}
