package com.ustadmobile.core.db.dao

import androidx.room.Dao
import androidx.room.Insert
import androidx.room.Query
import com.ustadmobile.lib.db.composites.CourseAssignmentSubmissionFileAndTransferJob
import com.ustadmobile.lib.db.entities.CourseAssignmentSubmissionFile
import kotlin.Any
import kotlin.Boolean
import kotlin.Long
import kotlin.String
import kotlin.collections.List
import kotlinx.coroutines.flow.Flow

@Dao
public actual abstract class CourseAssignmentSubmissionFileDao :
    BaseDao<CourseAssignmentSubmissionFile> {
  @Insert(
    entity = Any::class,
    onConflict = 3,
  )
  public actual abstract suspend
      fun insertListAsync(entityList: List<CourseAssignmentSubmissionFile>)

  @Query(`value` =
      "\n        SELECT CourseAssignmentSubmissionFile.*, TransferJobItem.*\n          FROM CourseAssignmentSubmissionFile\n               LEFT JOIN TransferJobItem\n                         ON TransferJobItem.tjiEntityUid = CourseAssignmentSubmissionFile.casaUid\n                            AND TransferJobItem.tjiTableId = 90\n         WHERE CourseAssignmentSubmissionFile.casaSubmissionUid = :submissionUid\n           AND NOT CourseAssignmentSubmissionFile.casaDeleted\n    ")
  public actual abstract fun getBySubmissionUid(submissionUid: Long):
      Flow<List<CourseAssignmentSubmissionFileAndTransferJob>>

  @Query(`value` =
      "\n        SELECT CourseAssignmentSubmissionFile.*, TransferJobItem.*\n          FROM CourseAssignmentSubmissionFile\n               LEFT JOIN TransferJobItem\n                         ON TransferJobItem.tjiEntityUid = CourseAssignmentSubmissionFile.casaUid\n                            AND TransferJobItem.tjiTableId = 90\n         WHERE CourseAssignmentSubmissionFile.casaSubmissionUid IN\n               (SELECT CourseAssignmentSubmission.casUid\n                  FROM CourseAssignmentSubmission\n                 WHERE CourseAssignmentSubmission.casAssignmentUid = :assignmentUid\n                   AND CourseAssignmentSubmission.casSubmitterUid = \n                       (\n        SELECT CASE\n                    -- When assignment is individual then the submitter uid is the personuid if they are enrolled in the course otherwise zero \n                    WHEN (SELECT caGroupUid\n                            FROM ClazzAssignment\n                           WHERE caUid = :assignmentUid) = 0\n                         THEN (\n        SELECT COALESCE(\n                (SELECT ClazzEnrolment.clazzEnrolmentPersonUid\n                   FROM ClazzEnrolment\n                  WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid\n                    AND ClazzEnrolment.clazzEnrolmentRole = 1000\n                    AND ClazzEnrolment.clazzEnrolmentClazzUid = \n                        (SELECT ClazzAssignment.caClazzUid\n                           FROM ClazzAssignment\n                          WHERE ClazzAssignment.caUid = :assignmentUid)\n                  LIMIT 1), 0)\n    )\n                    -- When assignment is by groups but the active user is not an enrolled student then the submitter uid is zero     \n                    WHEN (SELECT caGroupUid\n                            FROM ClazzAssignment\n                           WHERE caUid = :assignmentUid) != 0\n                          AND (\n        SELECT COALESCE(\n                (SELECT ClazzEnrolment.clazzEnrolmentPersonUid\n                   FROM ClazzEnrolment\n                  WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid\n                    AND ClazzEnrolment.clazzEnrolmentRole = 1000\n                    AND ClazzEnrolment.clazzEnrolmentClazzUid = \n                        (SELECT ClazzAssignment.caClazzUid\n                           FROM ClazzAssignment\n                          WHERE ClazzAssignment.caUid = :assignmentUid)\n                  LIMIT 1), 0)\n    ) = 0\n                          THEN 0\n                    -- When assignment is by groups and the person is an enrolled student the submitter uid is the \n                    -- group that they are assigned to. If they are not assigned to a group but are enrolled\n                    -- then we submitter uid = SUBMITTER_ENROLLED_BUT_NOT_IN_GROUP\n                    ELSE COALESCE(\n                          (SELECT CourseGroupMember.cgmGroupNumber\n                             FROM CourseGroupMember\n                            WHERE (\n        SELECT COALESCE(\n                (SELECT ClazzEnrolment.clazzEnrolmentPersonUid\n                   FROM ClazzEnrolment\n                  WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid\n                    AND ClazzEnrolment.clazzEnrolmentRole = 1000\n                    AND ClazzEnrolment.clazzEnrolmentClazzUid = \n                        (SELECT ClazzAssignment.caClazzUid\n                           FROM ClazzAssignment\n                          WHERE ClazzAssignment.caUid = :assignmentUid)\n                  LIMIT 1), 0)\n    ) > 0\n                              AND CourseGroupMember.cgmSetUid = \n                                  (SELECT caGroupUid\n                                     FROM ClazzAssignment\n                                    WHERE caUid = :assignmentUid)\n                              AND CourseGroupMember.cgmPersonUid = :accountPersonUid\n                            LIMIT 1), -1)\n                    END\n    ))\n           AND NOT CourseAssignmentSubmissionFile.casaDeleted        \n    ")
  public actual abstract fun getByAssignmentUidAndPersonUid(accountPersonUid: Long,
      assignmentUid: Long): Flow<List<CourseAssignmentSubmissionFileAndTransferJob>>

  @Query(`value` =
      "\n        SELECT CourseAssignmentSubmissionFile.*, TransferJobItem.*\n          FROM CourseAssignmentSubmissionFile\n               LEFT JOIN TransferJobItem\n                         ON TransferJobItem.tjiEntityUid = CourseAssignmentSubmissionFile.casaUid\n                            AND TransferJobItem.tjiTableId = 90\n         WHERE CourseAssignmentSubmissionFile.casaSubmitterUid = :submitterUid\n           AND CourseAssignmentSubmissionFile.casaCaUid = :assignmentUid\n           AND NOT CourseAssignmentSubmissionFile.casaDeleted\n    ")
  public actual abstract fun getAllSubmissionFilesFromSubmitterAsFlow(submitterUid: Long,
      assignmentUid: Long): Flow<List<CourseAssignmentSubmissionFileAndTransferJob>>

  @Query(`value` =
      "\n        UPDATE CourseAssignmentSubmissionFile\n           SET casaUri = :uri,\n               casaTimestamp = :updateTime\n         WHERE casaUid = :casaUid\n    ")
  public actual abstract suspend fun updateUri(
    casaUid: Long,
    uri: String,
    updateTime: Long,
  )

  @Query(`value` =
      "\n        UPDATE CourseAssignmentSubmissionFile\n           SET casaDeleted = :deleted,\n               casaTimestamp = :updateTime\n         WHERE casaUid = :casaUid\n    ")
  public actual abstract suspend fun setDeleted(
    casaUid: Long,
    deleted: Boolean,
    updateTime: Long,
  )
}
