package com.ustadmobile.core.db.dao

import androidx.paging.PagingSource
import androidx.room.Dao
import androidx.room.Insert
import androidx.room.Query
import androidx.room.Update
import com.ustadmobile.lib.db.composites.AssignmentSubmitterUidAndName
import com.ustadmobile.lib.db.composites.ClazzAssignmentAndBlock
import com.ustadmobile.lib.db.entities.AssignmentProgressSummary
import com.ustadmobile.lib.db.entities.AssignmentSubmitterSummary
import com.ustadmobile.lib.db.entities.ClazzAssignment
import com.ustadmobile.lib.db.entities.ClazzAssignmentCourseBlockAndSubmitterUid
import com.ustadmobile.lib.db.entities.ClazzEnrolment
import com.ustadmobile.lib.db.entities.CourseAssignmentMark
import com.ustadmobile.lib.db.entities.CourseAssignmentSubmission
import com.ustadmobile.lib.db.entities.CourseGroupMember
import com.ustadmobile.lib.db.entities.PeerReviewerAllocation
import kotlin.Any
import kotlin.Boolean
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.collections.List
import kotlinx.coroutines.flow.Flow

@Dao
public actual abstract class ClazzAssignmentDao : BaseDao<ClazzAssignment>,
    OneToManyJoinDao<ClazzAssignment> {
  @Query(`value` =
      "\n        SELECT * \n          FROM ClazzAssignment\n         WHERE ClazzAssignment.caClazzUid = :clazzUid\n    ")
  public actual abstract suspend fun getAllAssignmentsByClazzUidAsync(clazzUid: Long):
      List<ClazzAssignment>

  @Query(`value` =
      "\n        UPDATE ClazzAssignment \n           SET caActive = :active, \n               caLct = :changeTime\n         WHERE caUid = :cbUid")
  public actual abstract suspend fun updateActiveByUid(
    cbUid: Long,
    active: Boolean,
    changeTime: Long,
  )

  @Query(`value` =
      "\n        WITH \n            HasLearningRecordSelectPermission (hasPermission) AS\n            (SELECT (\n                  (\n             /* If the accountPersonUid is the owner of the course, all permissions are granted */\n             (COALESCE(\n                          (SELECT _Clazz_Permission.clazzOwnerPersonUid \n                             FROM Clazz _Clazz_Permission\n                            WHERE _Clazz_Permission.clazzUid = :clazzUid), 0) = :accountPersonUid)\n              /* \n              If there is a CoursePermission entity that is for the course as per the clazzUid\n              parameter that is granted to the person directly or to the enrolmentRole that the \n              person has in the course, then permission is granted.\n              */              \n              OR EXISTS(SELECT CoursePermission.cpUid\n                          FROM CoursePermission\n                               \n        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson \n                        ON CoursePermission.cpToEnrolmentRole != 0\n                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = \n                           (SELECT COALESCE(\n                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid \n                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner\n                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid\n                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = :accountPersonUid\n                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive\n                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   \n                                     LIMIT 1), 0))\n    \n                         WHERE CoursePermission.cpClazzUid = :clazzUid\n                           AND (CoursePermission.cpToPersonUid = :accountPersonUid \n                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)\n                           AND (CoursePermission.cpPermissionsFlag & \n         128\n                  \n        ) > 0)\n              OR EXISTS(SELECT SystemPermission.spUid\n                          FROM SystemPermission\n                         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n                           AND (SystemPermission.spPermissionsFlag & \n     128\n                  \n        ) > 0)\n               )\n    ))\n        ,\n        \n        AssignmentClazzUid(clazzUid) AS\n        (SELECT ClazzAssignment.caClazzUid\n           FROM ClazzAssignment\n          WHERE ClazzAssignment.caUid = :assignmentUid)  \n    ,\n        \n        SubmitterList(submitterId, name) AS \n        -- List of submitter uids and names if individual assignment eg caGroupUid = 0\n        (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid AS submitterId, \n                Person.firstNames || ' ' || Person.lastName AS name\n           FROM ClazzEnrolment\n                JOIN Person \n                     ON Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid\n          WHERE (\n        SELECT ClazzAssignment.caGroupUid\n                   FROM ClazzAssignment\n                  WHERE ClazzAssignment.caUid = :assignmentUid\n    ) = 0\n            AND ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid\n            AND ClazzEnrolment.clazzEnrolmentRole = 1000\n            -- either the active user has learnign record select permission on class or is an assigned reviewer for submitter\n            AND (\n                (SELECT hasPermission \n                   FROM HasLearningRecordSelectPermission)\n                OR  \n                 -- check if the active person eg accountpersonuid is assigned to mark this peer\n                 (\n        ((SELECT ClazzAssignment.caMarkingType\n           FROM ClazzAssignment\n          WHERE ClazzAssignment.caUid = :assignmentUid) = 2)\n    \n                  AND\n                  EXISTS(SELECT PeerReviewerAllocation.praUid\n                           FROM PeerReviewerAllocation\n                          WHERE PeerReviewerAllocation.praAssignmentUid = :assignmentUid\n                            AND PeerReviewerAllocation.praToMarkerSubmitterUid = ClazzEnrolment.clazzEnrolmentPersonUid\n                            AND PeerReviewerAllocation.praMarkerSubmitterUid = :accountPersonUid))\n                 )\n         UNION\n         -- List of submitter uids and names if the assignment is submitted by groups e.g. caGroupUid != 0\n         SELECT DISTINCT CourseGroupMember.cgmGroupNumber AS submitterId,\n                :group || ' ' || CourseGroupMember.cgmGroupNumber AS name\n           FROM CourseGroupMember\n          WHERE (\n        SELECT ClazzAssignment.caGroupUid\n                   FROM ClazzAssignment\n                  WHERE ClazzAssignment.caUid = :assignmentUid\n    ) != 0\n            AND CourseGroupMember.cgmSetUid = (\n        SELECT ClazzAssignment.caGroupUid\n                   FROM ClazzAssignment\n                  WHERE ClazzAssignment.caUid = :assignmentUid\n    )\n            -- either the active user has learning record select permission on class or is an assigned reviewer for submitter\n            AND (\n                (SELECT hasPermission \n                   FROM HasLearningRecordSelectPermission)\n                OR \n                --check if the active user is in a group that was allocated to do a peer review of the given submitter uid\n                (\n        ((SELECT ClazzAssignment.caMarkingType\n           FROM ClazzAssignment\n          WHERE ClazzAssignment.caUid = :assignmentUid) = 2)\n    \n                 AND\n                 EXISTS(SELECT PeerReviewerAllocation.praUid\n                          FROM PeerReviewerAllocation\n                         WHERE PeerReviewerAllocation.praAssignmentUid = :assignmentUid\n                           AND PeerReviewerAllocation.praToMarkerSubmitterUid = CourseGroupMember.cgmGroupNumber\n                           AND PeerReviewerAllocation.praMarkerSubmitterUid = \n                               (SELECT CourseGroupMemberInner.cgmGroupNumber\n                                  FROM CourseGroupMember CourseGroupMemberInner\n                                 WHERE CourseGroupMemberInner.cgmSetUid = (\n        SELECT ClazzAssignment.caGroupUid\n                   FROM ClazzAssignment\n                  WHERE ClazzAssignment.caUid = :assignmentUid\n    )\n                                   AND CourseGroupMemberInner.cgmPersonUid = :accountPersonUid\n                                 LIMIT 1)\n                        ))\n            )\n        )\n        \n    \n        \n        SELECT \n              -- whether or not the active user has permission to view learner records \n              (SELECT hasPermission\n                 FROM HasLearningRecordSelectPermission) AS activeUserHasViewLearnerRecordsPermission,\n        \n              (SELECT COUNT(*)\n                 FROM SubmitterList) AS totalStudents,\n              \n              -- Total marked students\n              (SELECT COUNT(*)\n                 FROM SubmitterList\n                WHERE EXISTS(\n                      SELECT CourseAssignmentMark.camUid\n                        FROM CourseAssignmentMark\n                       WHERE CourseAssignmentMark.camAssignmentUid = :assignmentUid\n                         AND CourseAssignmentMark.camSubmitterUid = SubmitterList.submitterId) \n                ) AS markedStudents,\n              \n              -- Total who have submitted  \n              (SELECT COUNT(*)\n                 FROM SubmitterList\n                WHERE EXISTS(\n                      SELECT CourseAssignmentSubmission.casUid\n                        FROM CourseAssignmentSubmission\n                       WHERE CourseAssignmentSubmission.casAssignmentUid = :assignmentUid\n                         AND CourseAssignmentSubmission.casSubmitterUid = SubmitterList.submitterId)\n                ) AS submittedStudents,\n              \n              (SELECT (ClazzAssignment.caGroupUid != 0)\n                 FROM ClazzAssignment\n                WHERE ClazzAssignment.caUid = :assignmentUid) AS isGroupAssignment\n    ")
  public actual abstract fun getProgressSummaryForAssignment(
    assignmentUid: Long,
    clazzUid: Long,
    accountPersonUid: Long,
    group: String,
  ): Flow<AssignmentProgressSummary?>

  @Query(`value` =
      "\n        WITH \n            HasLearningRecordSelectPermission (hasPermission) AS\n            (SELECT (\n                  (\n             /* If the accountPersonUid is the owner of the course, all permissions are granted */\n             (COALESCE(\n                          (SELECT _Clazz_Permission.clazzOwnerPersonUid \n                             FROM Clazz _Clazz_Permission\n                            WHERE _Clazz_Permission.clazzUid = :clazzUid), 0) = :accountPersonUid)\n              /* \n              If there is a CoursePermission entity that is for the course as per the clazzUid\n              parameter that is granted to the person directly or to the enrolmentRole that the \n              person has in the course, then permission is granted.\n              */              \n              OR EXISTS(SELECT CoursePermission.cpUid\n                          FROM CoursePermission\n                               \n        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson \n                        ON CoursePermission.cpToEnrolmentRole != 0\n                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = \n                           (SELECT COALESCE(\n                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid \n                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner\n                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid\n                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = :accountPersonUid\n                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive\n                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   \n                                     LIMIT 1), 0))\n    \n                         WHERE CoursePermission.cpClazzUid = :clazzUid\n                           AND (CoursePermission.cpToPersonUid = :accountPersonUid \n                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)\n                           AND (CoursePermission.cpPermissionsFlag & \n         128\n                  \n        ) > 0)\n              OR EXISTS(SELECT SystemPermission.spUid\n                          FROM SystemPermission\n                         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n                           AND (SystemPermission.spPermissionsFlag & \n     128\n                  \n        ) > 0)\n               )\n    ))\n        ,\n             \n        AssignmentClazzUid(clazzUid) AS\n        (SELECT ClazzAssignment.caClazzUid\n           FROM ClazzAssignment\n          WHERE ClazzAssignment.caUid = :assignmentUid)  \n    ,\n             \n        SubmitterList(submitterId, name) AS \n        -- List of submitter uids and names if individual assignment eg caGroupUid = 0\n        (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid AS submitterId, \n                Person.firstNames || ' ' || Person.lastName AS name\n           FROM ClazzEnrolment\n                JOIN Person \n                     ON Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid\n          WHERE (\n        SELECT ClazzAssignment.caGroupUid\n                   FROM ClazzAssignment\n                  WHERE ClazzAssignment.caUid = :assignmentUid\n    ) = 0\n            AND ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid\n            AND ClazzEnrolment.clazzEnrolmentRole = 1000\n            -- either the active user has learnign record select permission on class or is an assigned reviewer for submitter\n            AND (\n                (SELECT hasPermission \n                   FROM HasLearningRecordSelectPermission)\n                OR  \n                 -- check if the active person eg accountpersonuid is assigned to mark this peer\n                 (\n        ((SELECT ClazzAssignment.caMarkingType\n           FROM ClazzAssignment\n          WHERE ClazzAssignment.caUid = :assignmentUid) = 2)\n    \n                  AND\n                  EXISTS(SELECT PeerReviewerAllocation.praUid\n                           FROM PeerReviewerAllocation\n                          WHERE PeerReviewerAllocation.praAssignmentUid = :assignmentUid\n                            AND PeerReviewerAllocation.praToMarkerSubmitterUid = ClazzEnrolment.clazzEnrolmentPersonUid\n                            AND PeerReviewerAllocation.praMarkerSubmitterUid = :accountPersonUid))\n                 )\n         UNION\n         -- List of submitter uids and names if the assignment is submitted by groups e.g. caGroupUid != 0\n         SELECT DISTINCT CourseGroupMember.cgmGroupNumber AS submitterId,\n                :group || ' ' || CourseGroupMember.cgmGroupNumber AS name\n           FROM CourseGroupMember\n          WHERE (\n        SELECT ClazzAssignment.caGroupUid\n                   FROM ClazzAssignment\n                  WHERE ClazzAssignment.caUid = :assignmentUid\n    ) != 0\n            AND CourseGroupMember.cgmSetUid = (\n        SELECT ClazzAssignment.caGroupUid\n                   FROM ClazzAssignment\n                  WHERE ClazzAssignment.caUid = :assignmentUid\n    )\n            -- either the active user has learning record select permission on class or is an assigned reviewer for submitter\n            AND (\n                (SELECT hasPermission \n                   FROM HasLearningRecordSelectPermission)\n                OR \n                --check if the active user is in a group that was allocated to do a peer review of the given submitter uid\n                (\n        ((SELECT ClazzAssignment.caMarkingType\n           FROM ClazzAssignment\n          WHERE ClazzAssignment.caUid = :assignmentUid) = 2)\n    \n                 AND\n                 EXISTS(SELECT PeerReviewerAllocation.praUid\n                          FROM PeerReviewerAllocation\n                         WHERE PeerReviewerAllocation.praAssignmentUid = :assignmentUid\n                           AND PeerReviewerAllocation.praToMarkerSubmitterUid = CourseGroupMember.cgmGroupNumber\n                           AND PeerReviewerAllocation.praMarkerSubmitterUid = \n                               (SELECT CourseGroupMemberInner.cgmGroupNumber\n                                  FROM CourseGroupMember CourseGroupMemberInner\n                                 WHERE CourseGroupMemberInner.cgmSetUid = (\n        SELECT ClazzAssignment.caGroupUid\n                   FROM ClazzAssignment\n                  WHERE ClazzAssignment.caUid = :assignmentUid\n    )\n                                   AND CourseGroupMemberInner.cgmPersonUid = :accountPersonUid\n                                 LIMIT 1)\n                        ))\n            )\n        )\n        \n    \n        \n        SELECT SubmitterList.name AS name,\n               SubmitterList.submitterId AS submitterUid,\n               Comments.commentsText AS latestPrivateComment,\n               CASE \n               WHEN SubmitterList.submitterId >= 10000 THEN\n                    (SELECT PersonPicture.personPictureThumbnailUri\n                       FROM PersonPicture\n                      WHERE PersonPicture.personPictureUid =  SubmitterList.submitterId)\n               ELSE NULL\n               END AS pictureUri,       \n               -- Determine submission status - marked, submitted, or not yet submitted\n               CASE \n               WHEN CourseAssignmentMark.camUid IS NOT NULL THEN 2\n               WHEN CourseAssignmentSubmission.casUid IS NOT NULL THEN 1\n               ELSE 0 \n               END AS fileSubmissionStatus\n               \n          FROM SubmitterList\n               LEFT JOIN Comments \n                         ON Comments.commentsUid = \n                            (SELECT Comments.commentsUid \n                               FROM Comments\n                              WHERE Comments.commentsEntityUid = :assignmentUid\n                                AND Comments.commentsForSubmitterUid = SubmitterList.submitterId\n                                AND NOT Comments.commentsDeleted\n                           ORDER BY Comments.commentsDateTimeAdded DESC     \n                              LIMIT 1) \n               LEFT JOIN CourseAssignmentMark\n                         ON CourseAssignmentMark.camUid = \n                            (SELECT camUid\n                               FROM CourseAssignmentMark\n                              WHERE CourseAssignmentMark.camAssignmentUid = :assignmentUid\n                                AND CourseAssignmentMark.camSubmitterUid = SubmitterList.submitterId\n                              LIMIT 1)\n               LEFT JOIN CourseAssignmentSubmission\n                         ON CourseAssignmentSubmission.casUid = \n                            (SELECT casUid\n                               FROM CourseAssignmentSubmission\n                              WHERE CourseAssignmentSubmission.casAssignmentUid = :assignmentUid\n                                AND CourseAssignmentSubmission.casSubmitterUid = SubmitterList.submitterId \n                              LIMIT 1)\n         WHERE (:searchText = '%' OR SubmitterList.name LIKE :searchText)\n      ORDER BY CASE(:sortOption)\n               WHEN 5 THEN SubmitterList.name\n               ELSE '' END ASC,\n               CASE(:sortOption)\n               WHEN 6 THEN SubmitterList.name\n               ELSE '' END DESC\n    ")
  public actual abstract fun getAssignmentSubmitterSummaryListForAssignment(
    assignmentUid: Long,
    clazzUid: Long,
    accountPersonUid: Long,
    group: String,
    searchText: String,
    sortOption: Int,
  ): PagingSource<Int, AssignmentSubmitterSummary>

  @Query(`value` =
      "\n        SELECT CourseGroupMember.*\n          FROM CourseGroupMember\n         WHERE CourseGroupMember.cgmSetUid = \n               (SELECT ClazzAssignment.caGroupUid\n                  FROM ClazzAssignment\n                 WHERE ClazzAssignment.caUid = :assignmentUid) \n    ")
  public actual abstract suspend fun getCourseGroupMembersByAssignmentUid(assignmentUid: Long):
      List<CourseGroupMember>

  @Query(`value` =
      "\n        WITH \n            HasLearningRecordSelectPermission (hasPermission) AS\n            (SELECT (\n                  (\n             /* If the accountPersonUid is the owner of the course, all permissions are granted */\n             (COALESCE(\n                          (SELECT _Clazz_Permission.clazzOwnerPersonUid \n                             FROM Clazz _Clazz_Permission\n                            WHERE _Clazz_Permission.clazzUid = :clazzUid), 0) = :accountPersonUid)\n              /* \n              If there is a CoursePermission entity that is for the course as per the clazzUid\n              parameter that is granted to the person directly or to the enrolmentRole that the \n              person has in the course, then permission is granted.\n              */              \n              OR EXISTS(SELECT CoursePermission.cpUid\n                          FROM CoursePermission\n                               \n        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson \n                        ON CoursePermission.cpToEnrolmentRole != 0\n                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = \n                           (SELECT COALESCE(\n                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid \n                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner\n                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid\n                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = :accountPersonUid\n                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive\n                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   \n                                     LIMIT 1), 0))\n    \n                         WHERE CoursePermission.cpClazzUid = :clazzUid\n                           AND (CoursePermission.cpToPersonUid = :accountPersonUid \n                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)\n                           AND (CoursePermission.cpPermissionsFlag & \n         128\n                  \n        ) > 0)\n              OR EXISTS(SELECT SystemPermission.spUid\n                          FROM SystemPermission\n                         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n                           AND (SystemPermission.spPermissionsFlag & \n     128\n                  \n        ) > 0)\n               )\n    ))\n        ,\n             \n        AssignmentClazzUid(clazzUid) AS\n        (SELECT ClazzAssignment.caClazzUid\n           FROM ClazzAssignment\n          WHERE ClazzAssignment.caUid = :assignmentUid)  \n    ,\n             \n        SubmitterList(submitterId, name) AS \n        -- List of submitter uids and names if individual assignment eg caGroupUid = 0\n        (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid AS submitterId, \n                Person.firstNames || ' ' || Person.lastName AS name\n           FROM ClazzEnrolment\n                JOIN Person \n                     ON Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid\n          WHERE (\n        SELECT ClazzAssignment.caGroupUid\n                   FROM ClazzAssignment\n                  WHERE ClazzAssignment.caUid = :assignmentUid\n    ) = 0\n            AND ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid\n            AND ClazzEnrolment.clazzEnrolmentRole = 1000\n            -- either the active user has learnign record select permission on class or is an assigned reviewer for submitter\n            AND (\n                (SELECT hasPermission \n                   FROM HasLearningRecordSelectPermission)\n                OR  \n                 -- check if the active person eg accountpersonuid is assigned to mark this peer\n                 (\n        ((SELECT ClazzAssignment.caMarkingType\n           FROM ClazzAssignment\n          WHERE ClazzAssignment.caUid = :assignmentUid) = 2)\n    \n                  AND\n                  EXISTS(SELECT PeerReviewerAllocation.praUid\n                           FROM PeerReviewerAllocation\n                          WHERE PeerReviewerAllocation.praAssignmentUid = :assignmentUid\n                            AND PeerReviewerAllocation.praToMarkerSubmitterUid = ClazzEnrolment.clazzEnrolmentPersonUid\n                            AND PeerReviewerAllocation.praMarkerSubmitterUid = :accountPersonUid))\n                 )\n         UNION\n         -- List of submitter uids and names if the assignment is submitted by groups e.g. caGroupUid != 0\n         SELECT DISTINCT CourseGroupMember.cgmGroupNumber AS submitterId,\n                :group || ' ' || CourseGroupMember.cgmGroupNumber AS name\n           FROM CourseGroupMember\n          WHERE (\n        SELECT ClazzAssignment.caGroupUid\n                   FROM ClazzAssignment\n                  WHERE ClazzAssignment.caUid = :assignmentUid\n    ) != 0\n            AND CourseGroupMember.cgmSetUid = (\n        SELECT ClazzAssignment.caGroupUid\n                   FROM ClazzAssignment\n                  WHERE ClazzAssignment.caUid = :assignmentUid\n    )\n            -- either the active user has learning record select permission on class or is an assigned reviewer for submitter\n            AND (\n                (SELECT hasPermission \n                   FROM HasLearningRecordSelectPermission)\n                OR \n                --check if the active user is in a group that was allocated to do a peer review of the given submitter uid\n                (\n        ((SELECT ClazzAssignment.caMarkingType\n           FROM ClazzAssignment\n          WHERE ClazzAssignment.caUid = :assignmentUid) = 2)\n    \n                 AND\n                 EXISTS(SELECT PeerReviewerAllocation.praUid\n                          FROM PeerReviewerAllocation\n                         WHERE PeerReviewerAllocation.praAssignmentUid = :assignmentUid\n                           AND PeerReviewerAllocation.praToMarkerSubmitterUid = CourseGroupMember.cgmGroupNumber\n                           AND PeerReviewerAllocation.praMarkerSubmitterUid = \n                               (SELECT CourseGroupMemberInner.cgmGroupNumber\n                                  FROM CourseGroupMember CourseGroupMemberInner\n                                 WHERE CourseGroupMemberInner.cgmSetUid = (\n        SELECT ClazzAssignment.caGroupUid\n                   FROM ClazzAssignment\n                  WHERE ClazzAssignment.caUid = :assignmentUid\n    )\n                                   AND CourseGroupMemberInner.cgmPersonUid = :accountPersonUid\n                                 LIMIT 1)\n                        ))\n            )\n        )\n        \n    \n      SELECT CourseAssignmentSubmission.*\n        FROM CourseAssignmentSubmission \n       WHERE CourseAssignmentSubmission.casAssignmentUid = :assignmentUid\n         AND CourseAssignmentSubmission.casClazzUid = :clazzUid\n         AND CourseAssignmentSubmission.casSubmitterUid IN \n             (SELECT SubmitterList.submitterId\n                FROM SubmitterList) \n    ")
  public actual abstract suspend fun getAssignmentSubmissionsByAssignmentUid(
    assignmentUid: Long,
    clazzUid: Long,
    accountPersonUid: Long,
    group: String,
  ): List<CourseAssignmentSubmission>

  @Query(`value` =
      "\n        WITH \n            HasLearningRecordSelectPermission (hasPermission) AS\n            (SELECT (\n                  (\n             /* If the accountPersonUid is the owner of the course, all permissions are granted */\n             (COALESCE(\n                          (SELECT _Clazz_Permission.clazzOwnerPersonUid \n                             FROM Clazz _Clazz_Permission\n                            WHERE _Clazz_Permission.clazzUid = :clazzUid), 0) = :accountPersonUid)\n              /* \n              If there is a CoursePermission entity that is for the course as per the clazzUid\n              parameter that is granted to the person directly or to the enrolmentRole that the \n              person has in the course, then permission is granted.\n              */              \n              OR EXISTS(SELECT CoursePermission.cpUid\n                          FROM CoursePermission\n                               \n        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson \n                        ON CoursePermission.cpToEnrolmentRole != 0\n                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = \n                           (SELECT COALESCE(\n                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid \n                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner\n                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid\n                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = :accountPersonUid\n                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive\n                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   \n                                     LIMIT 1), 0))\n    \n                         WHERE CoursePermission.cpClazzUid = :clazzUid\n                           AND (CoursePermission.cpToPersonUid = :accountPersonUid \n                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)\n                           AND (CoursePermission.cpPermissionsFlag & \n         128\n                  \n        ) > 0)\n              OR EXISTS(SELECT SystemPermission.spUid\n                          FROM SystemPermission\n                         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n                           AND (SystemPermission.spPermissionsFlag & \n     128\n                  \n        ) > 0)\n               )\n    ))\n        ,\n             \n        AssignmentClazzUid(clazzUid) AS\n        (SELECT ClazzAssignment.caClazzUid\n           FROM ClazzAssignment\n          WHERE ClazzAssignment.caUid = :assignmentUid)  \n    ,\n             \n        SubmitterList(submitterId, name) AS \n        -- List of submitter uids and names if individual assignment eg caGroupUid = 0\n        (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid AS submitterId, \n                Person.firstNames || ' ' || Person.lastName AS name\n           FROM ClazzEnrolment\n                JOIN Person \n                     ON Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid\n          WHERE (\n        SELECT ClazzAssignment.caGroupUid\n                   FROM ClazzAssignment\n                  WHERE ClazzAssignment.caUid = :assignmentUid\n    ) = 0\n            AND ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid\n            AND ClazzEnrolment.clazzEnrolmentRole = 1000\n            -- either the active user has learnign record select permission on class or is an assigned reviewer for submitter\n            AND (\n                (SELECT hasPermission \n                   FROM HasLearningRecordSelectPermission)\n                OR  \n                 -- check if the active person eg accountpersonuid is assigned to mark this peer\n                 (\n        ((SELECT ClazzAssignment.caMarkingType\n           FROM ClazzAssignment\n          WHERE ClazzAssignment.caUid = :assignmentUid) = 2)\n    \n                  AND\n                  EXISTS(SELECT PeerReviewerAllocation.praUid\n                           FROM PeerReviewerAllocation\n                          WHERE PeerReviewerAllocation.praAssignmentUid = :assignmentUid\n                            AND PeerReviewerAllocation.praToMarkerSubmitterUid = ClazzEnrolment.clazzEnrolmentPersonUid\n                            AND PeerReviewerAllocation.praMarkerSubmitterUid = :accountPersonUid))\n                 )\n         UNION\n         -- List of submitter uids and names if the assignment is submitted by groups e.g. caGroupUid != 0\n         SELECT DISTINCT CourseGroupMember.cgmGroupNumber AS submitterId,\n                :group || ' ' || CourseGroupMember.cgmGroupNumber AS name\n           FROM CourseGroupMember\n          WHERE (\n        SELECT ClazzAssignment.caGroupUid\n                   FROM ClazzAssignment\n                  WHERE ClazzAssignment.caUid = :assignmentUid\n    ) != 0\n            AND CourseGroupMember.cgmSetUid = (\n        SELECT ClazzAssignment.caGroupUid\n                   FROM ClazzAssignment\n                  WHERE ClazzAssignment.caUid = :assignmentUid\n    )\n            -- either the active user has learning record select permission on class or is an assigned reviewer for submitter\n            AND (\n                (SELECT hasPermission \n                   FROM HasLearningRecordSelectPermission)\n                OR \n                --check if the active user is in a group that was allocated to do a peer review of the given submitter uid\n                (\n        ((SELECT ClazzAssignment.caMarkingType\n           FROM ClazzAssignment\n          WHERE ClazzAssignment.caUid = :assignmentUid) = 2)\n    \n                 AND\n                 EXISTS(SELECT PeerReviewerAllocation.praUid\n                          FROM PeerReviewerAllocation\n                         WHERE PeerReviewerAllocation.praAssignmentUid = :assignmentUid\n                           AND PeerReviewerAllocation.praToMarkerSubmitterUid = CourseGroupMember.cgmGroupNumber\n                           AND PeerReviewerAllocation.praMarkerSubmitterUid = \n                               (SELECT CourseGroupMemberInner.cgmGroupNumber\n                                  FROM CourseGroupMember CourseGroupMemberInner\n                                 WHERE CourseGroupMemberInner.cgmSetUid = (\n        SELECT ClazzAssignment.caGroupUid\n                   FROM ClazzAssignment\n                  WHERE ClazzAssignment.caUid = :assignmentUid\n    )\n                                   AND CourseGroupMemberInner.cgmPersonUid = :accountPersonUid\n                                 LIMIT 1)\n                        ))\n            )\n        )\n        \n    \n      SELECT CourseAssignmentMark.*\n        FROM CourseAssignmentMark \n       WHERE CourseAssignmentMark.camAssignmentUid = :assignmentUid\n         AND CourseAssignmentMark.camClazzUid = :clazzUid\n         AND CourseAssignmentMark.camSubmitterUid IN \n             (SELECT SubmitterList.submitterId\n                FROM SubmitterList)\n    ")
  public actual abstract suspend fun getAssignmentMarksByAssignmentUid(
    assignmentUid: Long,
    clazzUid: Long,
    accountPersonUid: Long,
    group: String,
  ): List<CourseAssignmentMark>

  @Query(`value` =
      "\n          WITH \n            HasLearningRecordSelectPermission (hasPermission) AS\n            (SELECT (\n                  (\n             /* If the accountPersonUid is the owner of the course, all permissions are granted */\n             (COALESCE(\n                          (SELECT _Clazz_Permission.clazzOwnerPersonUid \n                             FROM Clazz _Clazz_Permission\n                            WHERE _Clazz_Permission.clazzUid = :clazzUid), 0) = :accountPersonUid)\n              /* \n              If there is a CoursePermission entity that is for the course as per the clazzUid\n              parameter that is granted to the person directly or to the enrolmentRole that the \n              person has in the course, then permission is granted.\n              */              \n              OR EXISTS(SELECT CoursePermission.cpUid\n                          FROM CoursePermission\n                               \n        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson \n                        ON CoursePermission.cpToEnrolmentRole != 0\n                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = \n                           (SELECT COALESCE(\n                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid \n                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner\n                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid\n                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = :accountPersonUid\n                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive\n                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   \n                                     LIMIT 1), 0))\n    \n                         WHERE CoursePermission.cpClazzUid = :clazzUid\n                           AND (CoursePermission.cpToPersonUid = :accountPersonUid \n                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)\n                           AND (CoursePermission.cpPermissionsFlag & \n         128\n                  \n        ) > 0)\n              OR EXISTS(SELECT SystemPermission.spUid\n                          FROM SystemPermission\n                         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n                           AND (SystemPermission.spPermissionsFlag & \n     128\n                  \n        ) > 0)\n               )\n    ))\n        \n        SELECT PeerReviewerAllocation.*\n          FROM PeerReviewerAllocation\n         WHERE \n        ((SELECT ClazzAssignment.caMarkingType\n           FROM ClazzAssignment\n          WHERE ClazzAssignment.caUid = :assignmentUid) = 2)\n    \n          AND NOT \n              (SELECT hasPermission \n                 FROM HasLearningRecordSelectPermission) \n           AND PeerReviewerAllocation.praAssignmentUid = :assignmentUid\n           AND (   PeerReviewerAllocation.praMarkerSubmitterUid = :accountPersonUid\n                OR PeerReviewerAllocation.praMarkerSubmitterUid IN \n                   (SELECT CourseGroupMember.cgmGroupNumber\n                      FROM CourseGroupMember\n                     WHERE CourseGroupMember.cgmSetUid = \n                           (SELECT ClazzAssignment.caGroupUid\n                              FROM ClazzAssignment\n                             WHERE ClazzAssignment.caUid = :assignmentUid)))\n    ")
  public actual abstract suspend fun getPeerReviewerAllocationsByAssignmentUid(
    assignmentUid: Long,
    clazzUid: Long,
    accountPersonUid: Long,
  ): List<PeerReviewerAllocation>

  @Query(`value` =
      "\n        SELECT PeerReviewerAllocation.*\n          FROM PeerReviewerAllocation\n         WHERE PeerReviewerAllocation.praAssignmentUid = :assignmentUid\n           AND (PeerReviewerAllocation.praToMarkerSubmitterUid = :accountPersonUid\n                OR\n                PeerReviewerAllocation.praToMarkerSubmitterUid IN \n                (SELECT CourseGroupMember.cgmSetUid \n                   FROM CourseGroupMember\n                  WHERE CourseGroupMember.cgmSetUid = \n                        (SELECT ClazzAssignment.caGroupUid\n                           FROM ClazzAssignment\n                          WHERE ClazzAssignment.caUid = :assignmentUid)\n                    AND CourseGroupMember.cgmPersonUid = :accountPersonUid))                 \n    ")
  public actual abstract suspend fun getPeerReviewAllocationsForPerson(assignmentUid: Long,
      accountPersonUid: Long): List<PeerReviewerAllocation>

  @Query(`value` =
      "\n         -- Submitter UIDs for individual assignment the list of personuids enrolled in the course\n         SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid AS submitterUid\n           FROM ClazzEnrolment\n          WHERE (:groupSetUid = 0)\n            AND ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid\n            AND ClazzEnrolment.clazzEnrolmentRole = 1000\n            AND :time BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined AND ClazzEnrolment.clazzEnrolmentDateLeft\n          \n         UNION\n         \n        SELECT DISTINCT CourseGroupMember.cgmGroupNumber AS submitterUid\n          FROM CourseGroupMember\n         WHERE :groupSetUid != 0\n           AND CourseGroupMember.cgmSetUid = :groupSetUid         \n    ")
  public actual abstract suspend fun getSubmitterUidsByClazzOrGroupSetUid(
    clazzUid: Long,
    groupSetUid: Long,
    time: Long,
  ): List<Long>

  @Query(`value` =
      "\n        WITH SubmitterUids(submitterUid) AS (\n            SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid AS submitterUid\n               FROM ClazzEnrolment\n              WHERE (:groupSetUid = 0)\n                AND ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid\n                AND ClazzEnrolment.clazzEnrolmentRole = 1000\n                AND :date BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined AND ClazzEnrolment.clazzEnrolmentDateLeft\n              \n             UNION\n             \n            SELECT DISTINCT CourseGroupMember.cgmGroupNumber AS submitterUid\n              FROM CourseGroupMember\n             WHERE :groupSetUid != 0\n               AND CourseGroupMember.cgmSetUid = :groupSetUid    \n        )\n        \n        SELECT SubmitterUids.submitterUid AS submitterUid,\n               CASE :groupSetUid\n               WHEN 0 THEN\n                      (SELECT Person.firstNames || ' ' || Person.lastName\n                         FROM Person\n                        WHERE Person.personUid = SubmitterUids.submitterUid)\n               ELSE (:groupStr || ' ' || SubmitterUids.submitterUid)   \n               END AS name\n          FROM SubmitterUids                  \n    ")
  public actual abstract suspend fun getSubmitterUidsAndNameByClazzOrGroupSetUid(
    clazzUid: Long,
    groupSetUid: Long,
    date: Long,
    groupStr: String,
  ): List<AssignmentSubmitterUidAndName>

  @Query(`value` =
      "\n         \n             WITH SubmitterList (submitterId, name)\n            AS (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid AS submitterId, \n                       Person.firstNames || ' ' || Person.lastName AS name\n                  FROM ClazzEnrolment\n                  \n                       JOIN Person \n                       ON Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid\n                       \n                 WHERE :groupUid = 0 \n                   AND clazzEnrolmentClazzUid = :clazzUid\n                   AND clazzEnrolmentActive\n                   AND clazzEnrolmentRole = 1000\n              GROUP BY submitterId, name\n            UNION                 \n             SELECT DISTINCT CourseGroupMember.cgmGroupNumber AS submitterId,\n                    :group || ' ' || CourseGroupMember.cgmGroupNumber AS name  \n               FROM CourseGroupMember\n                    JOIN CourseGroupSet\n                    ON CourseGroupSet.cgsUid = :groupUid\n              WHERE CourseGroupMember.cgmSetUid = CourseGroupSet.cgsUid\n                AND CourseGroupMember.cgmGroupNumber != 0\n           GROUP BY submitterId, name\n            )\n        \n        \n         SELECT COUNT(*) \n          FROM SubmitterList\n    ")
  public actual abstract suspend fun getSubmitterCountFromAssignment(
    groupUid: Long,
    clazzUid: Long,
    group: String,
  ): Int

  @Query(`value` =
      "\n        WITH ClazzUidDummy(clazzUid) AS\n             (SELECT :clazzUid)\n             \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    ")
  public actual abstract suspend fun getSubmitterUid(
    assignmentUid: Long,
    clazzUid: Long,
    accountPersonUid: Long,
  ): Long

  @Update(
    entity = Any::class,
    onConflict = 3,
  )
  public actual abstract suspend fun updateAsync(clazzAssignment: ClazzAssignment)

  @Query(`value` =
      "\n        SELECT * \n          FROM ClazzAssignment \n         WHERE caUid = :uid\n    ")
  public actual abstract suspend fun findByUidAsync(uid: Long): ClazzAssignment?

  @Query(`value` =
      "\n        SELECT * \n          FROM ClazzAssignment \n         WHERE caUid = :uid\n           AND caClazzUid = :clazzUid\n    ")
  public actual abstract fun findByUidAndClazzUidAsFlow(uid: Long, clazzUid: Long):
      Flow<ClazzAssignment?>

  @Query(`value` =
      "\n        SELECT ClazzAssignment.* \n          FROM ClazzAssignment \n         WHERE ClazzAssignment.caUid = :assignmentUid\n           AND ClazzAssignment.caClazzUid = :clazzUid\n    ")
  public actual abstract suspend fun findByUidAndClazzUidAsync(assignmentUid: Long, clazzUid: Long):
      ClazzAssignment?

  @Query(`value` =
      "\n          SELECT COALESCE((\n           SELECT caGroupUid\n           FROM ClazzAssignment\n          WHERE caUid = :uid),-1)\n    ")
  public actual abstract suspend fun getGroupUidFromAssignment(uid: Long): Long

  @Query(`value` =
      "\n          SELECT COALESCE((\n           SELECT caMarkingType\n           FROM ClazzAssignment\n          WHERE caUid = :uid),-1)\n    ")
  public actual abstract suspend fun getMarkingTypeFromAssignment(uid: Long): Int

  @Query(`value` =
      "\n        SELECT * \n          FROM ClazzAssignment\n               LEFT JOIN CourseBlock\n               ON CourseBlock.cbEntityUid = ClazzAssignment.caUid\n               AND CourseBlock.cbType = 103\n         WHERE caUid = :uid\n    ")
  public actual abstract suspend fun findByUidWithBlockAsync(uid: Long): ClazzAssignmentAndBlock?

  @Query(`value` = "\n        SELECT * \n          FROM ClazzAssignment LIMIT 1\n    ")
  public actual abstract fun findClazzAssignment(): ClazzAssignment?

  @Query(`value` =
      "SELECT * \n                      FROM ClazzAssignment \n                     WHERE caUid = :uid")
  public actual abstract fun findByUidLive(uid: Long): Flow<ClazzAssignment?>

  @Insert(
    onConflict = 1,
    entity = Any::class,
  )
  public actual abstract suspend fun upsertListAsync(entities: List<ClazzAssignment>)

  @Query(`value` =
      "\n        UPDATE ClazzAssignment\n           SET caActive = :active,\n               caLct = :changeTime\n         WHERE caUid IN (:uidList)   \n    ")
  public actual abstract suspend fun updateActiveByList(
    uidList: List<Long>,
    active: Boolean,
    changeTime: Long,
  )

  @Query(`value` =
      "\n        WITH PersonIsStudent(isStudent)\n             AS (SELECT EXISTS(\n                        SELECT ClazzEnrolment.clazzEnrolmentPersonUid\n                           FROM ClazzEnrolment\n                          WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid\n                            AND ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid\n                            AND ClazzEnrolment.clazzEnrolmentRole = 1000))\n                        \n        SELECT ClazzAssignment.*,\n               CourseBlock.*,\n               CourseBlockPicture.*,\n               CourseGroupSet.*,\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    ) AS submitterUid,\n               \n               ((\n             /* If the accountPersonUid is the owner of the course, all permissions are granted */\n             (COALESCE(\n                          (SELECT _Clazz_Permission.clazzOwnerPersonUid \n                             FROM Clazz _Clazz_Permission\n                            WHERE _Clazz_Permission.clazzUid = :clazzUid), 0) = :accountPersonUid)\n              /* \n              If there is a CoursePermission entity that is for the course as per the clazzUid\n              parameter that is granted to the person directly or to the enrolmentRole that the \n              person has in the course, then permission is granted.\n              */              \n              OR EXISTS(SELECT CoursePermission.cpUid\n                          FROM CoursePermission\n                               \n        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson \n                        ON CoursePermission.cpToEnrolmentRole != 0\n                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = \n                           (SELECT COALESCE(\n                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid \n                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner\n                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid\n                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = :accountPersonUid\n                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive\n                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   \n                                     LIMIT 1), 0))\n    \n                         WHERE CoursePermission.cpClazzUid = :clazzUid\n                           AND (CoursePermission.cpToPersonUid = :accountPersonUid \n                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)\n                           AND (CoursePermission.cpPermissionsFlag & \n         4\n                \n        ) > 0)\n              OR EXISTS(SELECT SystemPermission.spUid\n                          FROM SystemPermission\n                         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n                           AND (SystemPermission.spPermissionsFlag & \n     4\n                \n        ) > 0)\n               )\n    ) AS hasModeratePermission\n                   \n          FROM ClazzAssignment\n               JOIN CourseBlock\n                    ON CourseBlock.cbEntityUid = ClazzAssignment.caUid\n               LEFT JOIN CourseBlockPicture\n                    ON CourseBlockPicture.cbpUid = CourseBlock.cbUid\n               LEFT JOIN CourseGroupSet\n                    ON CourseGroupSet.cgsUid = ClazzAssignment.caGroupUid\n         WHERE ClazzAssignment.caUid = :assignmentUid\n           AND ClazzAssignment.caClazzUid = :clazzUid\n           AND (\n                (\n             /* If the accountPersonUid is the owner of the course, all permissions are granted */\n             (COALESCE(\n                          (SELECT _Clazz_Permission.clazzOwnerPersonUid \n                             FROM Clazz _Clazz_Permission\n                            WHERE _Clazz_Permission.clazzUid = :clazzUid), 0) = :accountPersonUid)\n              /* \n              If there is a CoursePermission entity that is for the course as per the clazzUid\n              parameter that is granted to the person directly or to the enrolmentRole that the \n              person has in the course, then permission is granted.\n              */              \n              OR EXISTS(SELECT CoursePermission.cpUid\n                          FROM CoursePermission\n                               \n        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson \n                        ON CoursePermission.cpToEnrolmentRole != 0\n                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = \n                           (SELECT COALESCE(\n                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid \n                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner\n                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid\n                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = :accountPersonUid\n                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive\n                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   \n                                     LIMIT 1), 0))\n    \n                         WHERE CoursePermission.cpClazzUid = :clazzUid\n                           AND (CoursePermission.cpToPersonUid = :accountPersonUid \n                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)\n                           AND (CoursePermission.cpPermissionsFlag & \n         1\n                \n        ) > 0)\n              OR EXISTS(SELECT SystemPermission.spUid\n                          FROM SystemPermission\n                         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n                           AND (SystemPermission.spPermissionsFlag & \n     1\n                \n        ) > 0)\n               )\n    )\n    ")
  public actual abstract fun findAssignmentCourseBlockAndSubmitterUidAsFlow(
    assignmentUid: Long,
    clazzUid: Long,
    accountPersonUid: Long,
  ): Flow<ClazzAssignmentCourseBlockAndSubmitterUid?>

  @Query(`value` =
      "\n        SELECT ClazzEnrolment.*\n          FROM ClazzEnrolment\n         WHERE ClazzEnrolment.clazzEnrolmentClazzUid = \n               (SELECT ClazzAssignment.caClazzUid\n                  FROM ClazzAssignment\n                 WHERE ClazzAssignment.caUid = :assignmentUid)\n           AND ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid \n    ")
  public actual abstract suspend fun findEnrolmentsByPersonUidAndAssignmentUid(assignmentUid: Long,
      accountPersonUid: Long): List<ClazzEnrolment>

  @Query(`value` =
      "\n        SELECT CourseGroupMember.*\n          FROM CourseGroupMember\n         WHERE CourseGroupMember.cgmSetUid = \n               (SELECT ClazzAssignment.caGroupUid \n                  FROM ClazzAssignment\n                 WHERE ClazzAssignment.caUid = :assignmentUid)\n           AND CourseGroupMember.cgmPersonUid = :accountPersonUid\n    ")
  public actual abstract suspend
      fun findCourseGroupMembersByPersonUidAndAssignmentUid(assignmentUid: Long,
      accountPersonUid: Long): List<CourseGroupMember>

  @Query(`value` =
      "\n        SELECT PeerReviewerAllocation.*\n          FROM PeerReviewerAllocation\n         WHERE PeerReviewerAllocation.praAssignmentUid = :assignmentUid\n           AND (\n                    PeerReviewerAllocation.praMarkerSubmitterUid = :accountPersonUid\n                 OR PeerReviewerAllocation.praToMarkerSubmitterUid = :accountPersonUid\n                 OR PeerReviewerAllocation.praMarkerSubmitterUid IN\n                    (SELECT CourseGroupMember.cgmGroupNumber\n                       FROM CourseGroupMember\n                      WHERE CourseGroupMember.cgmSetUid = \n                            (SELECT ClazzAssignment.caGroupUid\n                               FROM ClazzAssignment\n                              WHERE ClazzAssignment.caUid = :assignmentUid)\n                        AND CourseGroupMember.cgmPersonUid = :accountPersonUid)\n                 OR PeerReviewerAllocation.praToMarkerSubmitterUid IN\n                    (SELECT CourseGroupMember.cgmGroupNumber\n                       FROM CourseGroupMember\n                      WHERE CourseGroupMember.cgmSetUid = \n                            (SELECT ClazzAssignment.caGroupUid\n                               FROM ClazzAssignment\n                              WHERE ClazzAssignment.caUid = :assignmentUid)\n                                AND CourseGroupMember.cgmPersonUid = :accountPersonUid))\n    ")
  public actual abstract suspend
      fun findPeerReviewerAllocationsByPersonUidAndAssignmentUid(assignmentUid: Long,
      accountPersonUid: Long): List<PeerReviewerAllocation>

  @Query(`value` =
      "\n        WITH CourseBlockDeadlines(deadline, gracePeriod) AS\n             (SELECT CourseBlock.cbDeadlineDate AS deadline,\n                     CourseBlock.cbGracePeriodDate AS gracePeriod\n                FROM CourseBlock\n               WHERE CourseBlock.cbEntityUid = :assignmentUid\n                 AND CourseBlock.cbType = 103\n               LIMIT 1)\n        SELECT CASE\n               WHEN (SELECT gracePeriod \n                       FROM CourseBlockDeadlines)\n                    BETWEEN 1 AND 7258118400000 THEN (SELECT gracePeriod FROM CourseBlockDeadlines)\n               ELSE (SELECT deadline FROM CourseBlockDeadlines)\n               END AS latestSubmissionTimeAllowed\n    ")
  public actual abstract suspend fun getLatestSubmissionTimeAllowed(assignmentUid: Long): Long
}
