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.ClazzEnrolmentAndPerson
import com.ustadmobile.lib.db.composites.ClazzEnrolmentAndPersonDetailDetails
import com.ustadmobile.lib.db.composites.CourseNameAndPersonName
import com.ustadmobile.lib.db.composites.PersonAndClazzMemberListDetails
import com.ustadmobile.lib.db.entities.ClazzEnrolment
import com.ustadmobile.lib.db.entities.ClazzEnrolmentWithClazz
import com.ustadmobile.lib.db.entities.ClazzEnrolmentWithLeavingReason
import com.ustadmobile.lib.db.entities.ClazzEnrolmentWithPerson
import com.ustadmobile.lib.db.entities.xapi.ActorEntity
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 ClazzEnrolmentDao : BaseDao<ClazzEnrolment> {
  @Insert(
    entity = Any::class,
    onConflict = 3,
  )
  public actual abstract fun insertListAsync(entityList: List<ClazzEnrolment>)

  @Query(`value` =
      "\n        SELECT ClazzEnrolment.*, LeavingReason.*, \n               COALESCE(Clazz.clazzTimeZone, 'UTC') as timeZone\n          FROM ClazzEnrolment \n               LEFT JOIN LeavingReason \n                         ON LeavingReason.leavingReasonUid = ClazzEnrolment.clazzEnrolmentLeavingReasonUid\n               LEFT JOIN Clazz \n                         ON Clazz.clazzUid = ClazzEnrolment.clazzEnrolmentClazzUid\n         WHERE clazzEnrolmentPersonUid = :personUid \n           AND ClazzEnrolment.clazzEnrolmentActive \n           AND clazzEnrolmentClazzUid = :clazzUid \n      ORDER BY clazzEnrolmentDateLeft DESC\n           ")
  public actual abstract fun findAllEnrolmentsByPersonAndClazzUid(personUid: Long, clazzUid: Long):
      Flow<List<ClazzEnrolmentWithLeavingReason>>

  @Query(`value` =
      "\n            SELECT ClazzEnrolment.*, \n                   LeavingReason.*,\n                   COALESCE(Clazz.clazzTimeZone, 'UTC') AS timeZone\n              FROM ClazzEnrolment \n                   LEFT JOIN LeavingReason \n                             ON LeavingReason.leavingReasonUid = ClazzEnrolment.clazzEnrolmentLeavingReasonUid\n                   LEFT JOIN Clazz \n                             ON Clazz.clazzUid = ClazzEnrolment.clazzEnrolmentClazzUid\n             WHERE ClazzEnrolment.clazzEnrolmentUid = :enrolmentUid\n             ")
  public actual abstract suspend fun findEnrolmentWithLeavingReason(enrolmentUid: Long):
      ClazzEnrolmentWithLeavingReason?

  @Query(`value` =
      "\n        UPDATE ClazzEnrolment \n          SET clazzEnrolmentDateLeft = :endDate,\n              clazzEnrolmentLct = :updateTime\n        WHERE clazzEnrolmentUid = :clazzEnrolmentUid")
  public actual abstract suspend fun updateDateLeftByUid(
    clazzEnrolmentUid: Long,
    endDate: Long,
    updateTime: Long,
  )

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

  @Query(`value` =
      "\n               /* List of all CoursePermissions that are granted to the person as per accountPersonUid */\n          WITH CoursePermissionsForAccountPerson AS (\n               \n        /* Get CoursePermissions given to the active user based on their enrolment role*/\n        SELECT CoursePermission.*\n          FROM ClazzEnrolment ClazzEnrolment_ActiveUser\n               JOIN CoursePermission \n                    ON CoursePermission.cpClazzUid = ClazzEnrolment_ActiveUser.clazzEnrolmentClazzUid\n                   AND CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ActiveUser.clazzEnrolmentRole\n         WHERE ClazzEnrolment_ActiveUser.clazzEnrolmentPersonUid = :accountPersonUid \n         UNION\n        /* Get ClazzUids where the active user can view members based a grant directly to them */\n        SELECT CoursePermission.*\n          FROM CoursePermission\n         WHERE CoursePermission.cpToPersonUid  = :accountPersonUid\n    ),\n               /* Check if CoursePermission for accountPersonUid grants view permission */\n               CanViewPersonUidViaCoursePermission(personUid) AS (\n                    SELECT ClazzEnrolment.clazzEnrolmentPersonUid\n                      FROM CoursePermissionsForAccountPerson\n                           JOIN ClazzEnrolment \n                                ON (CoursePermissionsForAccountPerson.cpPermissionsFlag & 8192) > 0\n                               AND ClazzEnrolment.clazzEnrolmentClazzUid = CoursePermissionsForAccountPerson.cpClazzUid  \n                     WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :otherPersonUid         \n               )     \n        SELECT ClazzEnrolment.*,\n               Clazz.*,\n               CourseTerminology.*\n          FROM ClazzEnrolment\n               JOIN Clazz \n                    ON Clazz.clazzUid = ClazzEnrolment.clazzEnrolmentClazzUid\n               LEFT JOIN CourseTerminology\n                    ON CourseTerminology.ctUid = Clazz.clazzTerminologyUid\n         WHERE (:accountPersonUid != 0 AND :otherPersonUid != 0)\n           AND ClazzEnrolment.clazzEnrolmentPersonUid = :otherPersonUid\n               /* Check that accountPersonUid has permission to see otherPerson */\n           AND (    (SELECT :accountPersonUid = :otherPersonUid)\n                 OR (SELECT \n        EXISTS(SELECT 1\n                 FROM SystemPermission\n                WHERE :accountPersonUid != 0 \n                  AND SystemPermission.spToPersonUid = :accountPersonUid\n                  AND (SystemPermission.spPermissionsFlag &\n     \n                            8192\n                            \n        ) > 0\n                  AND NOT SystemPermission.spIsDeleted)\n    )\n                 OR (SELECT :otherPersonUid IN \n                             (SELECT CanViewPersonUidViaCoursePermission.personUid\n                                 FROM CanViewPersonUidViaCoursePermission))           \n               ) \n              /* Check that accountPersonUid has permission to see related Clazz */\n          AND (     (SELECT :accountPersonUid = :otherPersonUid)\n                 OR (SELECT \n        EXISTS(SELECT 1\n                 FROM SystemPermission\n                WHERE :accountPersonUid != 0 \n                  AND SystemPermission.spToPersonUid = :accountPersonUid\n                  AND (SystemPermission.spPermissionsFlag &\n     \n                            1\n                            \n        ) > 0\n                  AND NOT SystemPermission.spIsDeleted)\n    )\n                 OR (EXISTS(SELECT 1\n                              FROM CoursePermissionsForAccountPerson\n                             WHERE CoursePermissionsForAccountPerson.cpClazzUid = ClazzEnrolment.clazzEnrolmentClazzUid\n                               AND (CoursePermissionsForAccountPerson.cpPermissionsFlag & 1) > 0)) \n               )\n    ")
  public actual abstract fun findAllClazzesByPersonWithClazz(accountPersonUid: Long,
      otherPersonUid: Long): Flow<List<ClazzEnrolmentAndPersonDetailDetails>>

  @Query(`value` =
      "\n        SELECT ClazzEnrolment.*\n          FROM ClazzEnrolment\n         WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :personUid \n    ")
  public actual abstract fun findAllByPersonUid(personUid: Long): Flow<List<ClazzEnrolment>>

  @Query(`value` =
      "SELECT ClazzEnrolment.*, Clazz.* \n        FROM ClazzEnrolment \n        LEFT JOIN Clazz ON ClazzEnrolment.clazzEnrolmentClazzUid = Clazz.clazzUid \n        WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :personUid \n        AND ClazzEnrolment.clazzEnrolmentActive\n        ORDER BY ClazzEnrolment.clazzEnrolmentDateLeft DESC\n    ")
  public actual abstract suspend fun findAllClazzesByPersonWithClazzAsListAsync(personUid: Long):
      List<ClazzEnrolmentWithClazz>

  @Query(`value` =
      "\n        SELECT ClazzEnrolment.*, Person.*\n          FROM ClazzEnrolment\n                LEFT JOIN Person \n                          ON ClazzEnrolment.clazzEnrolmentPersonUid = Person.personUid\n        WHERE ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid\n              AND :date BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined \n              AND ClazzEnrolment.clazzEnrolmentDateLeft\n              AND CAST(clazzEnrolmentActive AS INTEGER) = 1\n              AND (:roleFilter = 0 OR ClazzEnrolment.clazzEnrolmentRole = :roleFilter)\n              AND (:personUidFilter = 0 OR ClazzEnrolment.clazzEnrolmentPersonUid = :personUidFilter)\n    ")
  public actual abstract suspend fun getAllClazzEnrolledAtTimeAsync(
    clazzUid: Long,
    date: Long,
    roleFilter: Int,
    personUidFilter: Long,
  ): List<ClazzEnrolmentWithPerson>

  @Query(`value` =
      "\n        SELECT ClazzEnrolment.*\n          FROM ClazzEnrolment\n         WHERE ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid\n           AND ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid\n           AND :time BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined \n                         AND ClazzEnrolment.clazzEnrolmentDateLeft\n           AND ClazzEnrolment.clazzEnrolmentActive              \n    ")
  public actual abstract suspend fun getAllEnrolmentsAtTimeByClazzAndPerson(
    clazzUid: Long,
    accountPersonUid: Long,
    time: Long,
  ): List<ClazzEnrolment>

  @Query(`value` = "SELECT * FROM ClazzEnrolment WHERE clazzEnrolmentUid = :uid")
  public actual abstract suspend fun findByUid(uid: Long): ClazzEnrolment?

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

  @Query(`value` =
      "\n        SELECT * \n          FROM (SELECT Person.*, PersonPicture.*,\n                       (SELECT MIN(ClazzEnrolment.clazzEnrolmentDateJoined) \n                          FROM ClazzEnrolment \n                         WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid) AS earliestJoinDate, \n        \n                       (SELECT MAX(ClazzEnrolment.clazzEnrolmentDateLeft) \n                          FROM ClazzEnrolment \n                         WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid) AS latestDateLeft, \n        \n                       (SELECT ClazzEnrolment.clazzEnrolmentRole \n                          FROM ClazzEnrolment \n                         WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid \n                           AND ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid \n                           AND ClazzEnrolment.clazzEnrolmentActive\n                      ORDER BY ClazzEnrolment.clazzEnrolmentDateLeft DESC\n                         LIMIT 1) AS enrolmentRole\n                  FROM Person\n                       LEFT JOIN PersonPicture\n                                 ON PersonPicture.personPictureUid = Person.personUid\n                 WHERE Person.personUid IN \n                       (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid \n                          FROM ClazzEnrolment \n                         WHERE ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid \n                           AND ClazzEnrolment.clazzEnrolmentActive \n                           AND ClazzEnrolment.clazzEnrolmentRole = :roleId \n                           AND (:filter != 1 \n                                 OR (:currentTime \n                                      BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined \n                                      AND ClazzEnrolment.clazzEnrolmentDateLeft))) \n                   /* Begin permission check */\n                   AND (\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         :permission\n         \n        ) > 0)\n              OR EXISTS(SELECT SystemPermission.spUid\n                          FROM SystemPermission\n                         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n                           AND (SystemPermission.spPermissionsFlag & \n     :permission\n         \n        ) > 0)\n               )\n    \n    )\n                        OR Person.personUid = :accountPersonUid\n                       )  \n                   /* End permission check */                   \n                   AND Person.firstNames || ' ' || Person.lastName LIKE :searchText\n               GROUP BY Person.personUid, PersonPicture.personPictureUid) AS CourseMember\n      ORDER BY CASE(:sortOrder)\n                WHEN 1 THEN CourseMember.firstNames\n                WHEN 3 THEN CourseMember.lastName\n                ELSE ''\n            END ASC,\n            CASE(:sortOrder)\n                WHEN 2 THEN CourseMember.firstNames\n                WHEN 4 THEN CourseMember.lastName\n                ELSE ''\n            END DESC,\n            CASE(:sortOrder)\n                WHEN 7 THEN CourseMember.earliestJoinDate\n                WHEN 9 THEN CourseMember.latestDateLeft\n                ELSE 0\n            END ASC,\n            CASE(:sortOrder)\n                WHEN 8 THEN CourseMember.earliestJoinDate\n                WHEN 10 THEN CourseMember.latestDateLeft\n                ELSE 0\n            END DESC\n    ")
  public actual abstract fun findByClazzUidAndRole(
    clazzUid: Long,
    roleId: Int,
    sortOrder: Int,
    searchText: String?,
    filter: Int,
    accountPersonUid: Long,
    currentTime: Long,
    permission: Long,
  ): PagingSource<Int, PersonAndClazzMemberListDetails>

  @Query(`value` =
      "\n        SELECT * \n          FROM (SELECT Person.*, PersonPicture.*,\n                       (SELECT MIN(ClazzEnrolment.clazzEnrolmentDateJoined) \n                          FROM ClazzEnrolment \n                         WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid) AS earliestJoinDate, \n        \n                       (SELECT MAX(ClazzEnrolment.clazzEnrolmentDateLeft) \n                          FROM ClazzEnrolment \n                         WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid) AS latestDateLeft, \n        \n                       (SELECT ClazzEnrolment.clazzEnrolmentRole \n                          FROM ClazzEnrolment \n                         WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid \n                           AND ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid \n                           AND ClazzEnrolment.clazzEnrolmentActive\n                      ORDER BY ClazzEnrolment.clazzEnrolmentDateLeft DESC\n                         LIMIT 1) AS enrolmentRole\n                  FROM Person\n                       LEFT JOIN PersonPicture\n                                 ON PersonPicture.personPictureUid = Person.personUid\n                       --Dummy join so that invalidations of the StatementEntity table will trigger\n                       -- PagingSource invalidation of ClazzGradebookPagingSource\n                       LEFT JOIN StatementEntity\n                                 ON StatementEntity.statementIdHi = 0 \n                                    AND StatementEntity.statementIdLo = 0\n                       LEFT JOIN ActorEntity\n                                 ON ActorEntity.actorUid = 0\n                 WHERE Person.personUid IN \n                       (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid \n                          FROM ClazzEnrolment \n                         WHERE ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid \n                           AND ClazzEnrolment.clazzEnrolmentActive \n                           AND ClazzEnrolment.clazzEnrolmentRole = :roleId \n                           AND (:filter != 1 \n                                 OR (:currentTime \n                                      BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined \n                                      AND ClazzEnrolment.clazzEnrolmentDateLeft))) \n                   /* Begin permission check */\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         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                        OR Person.personUid = :accountPersonUid\n                       )  \n                   /* End permission check */                   \n                   AND Person.firstNames || ' ' || Person.lastName LIKE :searchText\n               GROUP BY Person.personUid, PersonPicture.personPictureUid) AS CourseMember\n      ORDER BY CASE(:sortOrder)\n                WHEN 1 THEN CourseMember.firstNames\n                WHEN 3 THEN CourseMember.lastName\n                ELSE ''\n            END ASC,\n            CASE(:sortOrder)\n                WHEN 2 THEN CourseMember.firstNames\n                WHEN 4 THEN CourseMember.lastName\n                ELSE ''\n            END DESC,\n            CASE(:sortOrder)\n                WHEN 7 THEN CourseMember.earliestJoinDate\n                WHEN 9 THEN CourseMember.latestDateLeft\n                ELSE 0\n            END ASC,\n            CASE(:sortOrder)\n                WHEN 8 THEN CourseMember.earliestJoinDate\n                WHEN 10 THEN CourseMember.latestDateLeft\n                ELSE 0\n            END DESC\n    ")
  public actual abstract fun findByClazzUidAndRoleForGradebook(
    clazzUid: Long,
    roleId: Int,
    sortOrder: Int,
    searchText: String?,
    filter: Int,
    accountPersonUid: Long,
    currentTime: Long,
  ): PagingSource<Int, PersonAndClazzMemberListDetails>

  @Query(`value` =
      "\n          WITH \n        PersonUids(personUid) AS (\n            SELECT CourseMember.personUid \n              FROM (SELECT Person.*,\n                           (SELECT MIN(ClazzEnrolment.clazzEnrolmentDateJoined) \n                              FROM ClazzEnrolment \n                             WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid) AS earliestJoinDate, \n            \n                           (SELECT MAX(ClazzEnrolment.clazzEnrolmentDateLeft) \n                              FROM ClazzEnrolment \n                             WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid) AS latestDateLeft, \n            \n                           (SELECT ClazzEnrolment.clazzEnrolmentRole \n                              FROM ClazzEnrolment \n                             WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid \n                               AND ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid \n                               AND ClazzEnrolment.clazzEnrolmentActive\n                          ORDER BY ClazzEnrolment.clazzEnrolmentDateLeft DESC\n                             LIMIT 1) AS enrolmentRole\n                      FROM Person\n                     WHERE Person.personUid IN \n                           (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid \n                              FROM ClazzEnrolment \n                             WHERE ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid \n                               AND ClazzEnrolment.clazzEnrolmentActive \n                               AND ClazzEnrolment.clazzEnrolmentRole = :roleId \n                               AND (:filter != 1 \n                                     OR (:currentTime \n                                          BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined \n                                          AND ClazzEnrolment.clazzEnrolmentDateLeft))) \n                       /* Begin permission check */\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         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                            OR Person.personUid = :accountPersonUid\n                           )  \n                       /* End permission check */                   \n                       AND Person.firstNames || ' ' || Person.lastName LIKE :searchText\n                   GROUP BY Person.personUid) AS CourseMember\n          ORDER BY CASE(:sortOrder)\n                    WHEN 1 THEN CourseMember.firstNames\n                    WHEN 3 THEN CourseMember.lastName\n                    ELSE ''\n                END ASC,\n                CASE(:sortOrder)\n                    WHEN 2 THEN CourseMember.firstNames\n                    WHEN 4 THEN CourseMember.lastName\n                    ELSE ''\n                END DESC,\n                CASE(:sortOrder)\n                    WHEN 7 THEN CourseMember.earliestJoinDate\n                    WHEN 9 THEN CourseMember.latestDateLeft\n                    ELSE 0\n                END ASC,\n                CASE(:sortOrder)\n                    WHEN 8 THEN CourseMember.earliestJoinDate\n                    WHEN 10 THEN CourseMember.latestDateLeft\n                    ELSE 0\n                END DESC\n             LIMIT :studentsLimit\n            OFFSET :studentsOffset   \n         )\n    \n          \n        \n        SELECT ActorEntity.*\n          FROM ActorEntity\n         WHERE ActorEntity.actorPersonUid IN \n               (SELECT PersonUids.personUid\n                  FROM PersonUids)\n    ")
  public actual abstract suspend fun findActorEntitiesForGradebook(
    clazzUid: Long,
    roleId: Int,
    sortOrder: Int,
    searchText: String?,
    filter: Int,
    accountPersonUid: Long,
    currentTime: Long,
    studentsLimit: Int,
    studentsOffset: Int,
  ): List<ActorEntity>

  @Query(`value` =
      "\n       SELECT ClazzEnrolment.*,\n              Person.*,\n              PersonPicture.*\n         FROM ClazzEnrolment\n              JOIN Person\n                   ON Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid\n              LEFT JOIN PersonPicture\n                   ON PersonPicture.personPictureUid = ClazzEnrolment.clazzEnrolmentPersonUid\n                   \n        WHERE ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid\n              /* Begin permission check*/\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        \n                    8192\n                    \n        ) > 0)\n              OR EXISTS(SELECT SystemPermission.spUid\n                          FROM SystemPermission\n                         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n                           AND (SystemPermission.spPermissionsFlag & \n    \n                    8192\n                    \n        ) > 0)\n               )\n    )\n              )  \n              /* End permission check */\n    ")
  public actual abstract suspend
      fun findEnrolmentsAndPersonByClazzUidWithPermissionCheck(clazzUid: Long,
      accountPersonUid: Long): List<ClazzEnrolmentAndPerson>

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

  @Query(`value` =
      "\n       SELECT ClazzEnrolment.*\n         FROM ClazzEnrolment\n        WHERE ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid\n          AND ClazzEnrolment.clazzEnrolmentRole = :roleId\n              /* Begin permission check*/\n          AND (\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         :permission\n         \n        ) > 0)\n              OR EXISTS(SELECT SystemPermission.spUid\n                          FROM SystemPermission\n                         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n                           AND (SystemPermission.spPermissionsFlag & \n     :permission\n         \n        ) > 0)\n               )\n    \n    )\n                OR ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid\n              )  \n              /* End permission check */\n    ")
  public actual abstract suspend fun findEnrolmentsByClazzUidAndRole(
    clazzUid: Long,
    accountPersonUid: Long,
    roleId: Int,
    permission: Long,
  ): List<ClazzEnrolment>

  @Query(`value` =
      "\n         WITH \n        PersonUids(personUid) AS (\n            SELECT CourseMember.personUid \n              FROM (SELECT Person.*,\n                           (SELECT MIN(ClazzEnrolment.clazzEnrolmentDateJoined) \n                              FROM ClazzEnrolment \n                             WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid) AS earliestJoinDate, \n            \n                           (SELECT MAX(ClazzEnrolment.clazzEnrolmentDateLeft) \n                              FROM ClazzEnrolment \n                             WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid) AS latestDateLeft, \n            \n                           (SELECT ClazzEnrolment.clazzEnrolmentRole \n                              FROM ClazzEnrolment \n                             WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid \n                               AND ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid \n                               AND ClazzEnrolment.clazzEnrolmentActive\n                          ORDER BY ClazzEnrolment.clazzEnrolmentDateLeft DESC\n                             LIMIT 1) AS enrolmentRole\n                      FROM Person\n                     WHERE Person.personUid IN \n                           (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid \n                              FROM ClazzEnrolment \n                             WHERE ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid \n                               AND ClazzEnrolment.clazzEnrolmentActive \n                               AND ClazzEnrolment.clazzEnrolmentRole = :roleId \n                               AND (:filter != 1 \n                                     OR (:currentTime \n                                          BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined \n                                          AND ClazzEnrolment.clazzEnrolmentDateLeft))) \n                       /* Begin permission check */\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         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                            OR Person.personUid = :accountPersonUid\n                           )  \n                       /* End permission check */                   \n                       AND Person.firstNames || ' ' || Person.lastName LIKE :searchText\n                   GROUP BY Person.personUid) AS CourseMember\n          ORDER BY CASE(:sortOrder)\n                    WHEN 1 THEN CourseMember.firstNames\n                    WHEN 3 THEN CourseMember.lastName\n                    ELSE ''\n                END ASC,\n                CASE(:sortOrder)\n                    WHEN 2 THEN CourseMember.firstNames\n                    WHEN 4 THEN CourseMember.lastName\n                    ELSE ''\n                END DESC,\n                CASE(:sortOrder)\n                    WHEN 7 THEN CourseMember.earliestJoinDate\n                    WHEN 9 THEN CourseMember.latestDateLeft\n                    ELSE 0\n                END ASC,\n                CASE(:sortOrder)\n                    WHEN 8 THEN CourseMember.earliestJoinDate\n                    WHEN 10 THEN CourseMember.latestDateLeft\n                    ELSE 0\n                END DESC\n             LIMIT :studentsLimit\n            OFFSET :studentsOffset   \n         )\n    \n        \n       SELECT ClazzEnrolment.*\n         FROM ClazzEnrolment\n        WHERE ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid\n          AND ClazzEnrolment.clazzEnrolmentRole = :roleId\n          AND ClazzEnrolment.clazzEnrolmentPersonUid IN (\n              SELECT PersonUids.personUid\n                FROM PersonUids)\n              /* Begin permission check*/\n          AND (\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         :permission\n         \n        ) > 0)\n              OR EXISTS(SELECT SystemPermission.spUid\n                          FROM SystemPermission\n                         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n                           AND (SystemPermission.spPermissionsFlag & \n     :permission\n         \n        ) > 0)\n               )\n    \n    )\n                OR ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid\n              )  \n    ")
  public actual abstract suspend fun findEnrolmentsByClazzUidAndRolePaged(
    clazzUid: Long,
    roleId: Int,
    sortOrder: Int,
    searchText: String?,
    filter: Int,
    accountPersonUid: Long,
    currentTime: Long,
    permission: Long,
    studentsLimit: Int,
    studentsOffset: Int,
  ): List<ClazzEnrolment>

  @Query(`value` =
      "\n        SELECT ClazzEnrolment.*\n          FROM ClazzEnrolment\n         WHERE ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid\n           AND ClazzEnrolment.clazzEnrolmentRole = :roleId\n    ")
  public actual abstract suspend fun findAllEnrolmentsByClazzUidAndRole(clazzUid: Long,
      roleId: Int): List<ClazzEnrolment>

  @Query(`value` =
      "\n        UPDATE ClazzEnrolment \n          SET clazzEnrolmentActive = :enrolled,\n              clazzEnrolmentLct = :timeChanged\n        WHERE clazzEnrolmentUid = :clazzEnrolmentUid")
  public actual abstract fun updateClazzEnrolmentActiveForClazzEnrolment(
    clazzEnrolmentUid: Long,
    enrolled: Boolean,
    timeChanged: Long,
  ): Int

  @Query(`value` =
      "\n            UPDATE ClazzEnrolment \n               SET clazzEnrolmentRole = :newRole,\n                   clazzEnrolmentLct = :updateTime      \n             -- Avoid potential for duplicate approvals if user was previously refused      \n             WHERE clazzEnrolmentUid = COALESCE( \n                    (SELECT clazzEnrolmentUid\n                       FROM ClazzEnrolment\n                      WHERE clazzEnrolmentPersonUid = :personUid \n                            AND clazzEnrolmentClazzUid = :clazzUid\n                            AND clazzEnrolmentRole = :oldRole\n                            AND CAST(clazzEnrolmentActive AS INTEGER) = 1\n                      LIMIT 1), 0)")
  public actual abstract suspend fun updateClazzEnrolmentRole(
    personUid: Long,
    clazzUid: Long,
    newRole: Int,
    oldRole: Int,
    updateTime: Long,
  ): Int

  @Query(`value` =
      "\n        SELECT Person.firstNames, Person.lastName, Clazz.clazzName\n          FROM Person\n               LEFT JOIN Clazz\n                         ON Clazz.clazzUid = :clazzUid\n        WHERE Person.personUid = :personUid                 \n    ")
  public actual abstract suspend fun getClazzNameAndPersonName(personUid: Long, clazzUid: Long):
      CourseNameAndPersonName?

  @Query(`value` =
      "\n          WITH CanViewMembersClazzesViaCoursePermission(clazzUid) AS\n               /* Get clazzuids where active user can view members based on their own enrolment role */\n               (SELECT CoursePermission.cpClazzUid\n                  FROM ClazzEnrolment ClazzEnrolment_ActiveUser\n                       JOIN CoursePermission \n                            ON CoursePermission.cpClazzUid = ClazzEnrolment_ActiveUser.clazzEnrolmentClazzUid\n                           AND CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ActiveUser.clazzEnrolmentRole\n                 WHERE ClazzEnrolment_ActiveUser.clazzEnrolmentPersonUid = :accountPersonUid \n                   AND (CoursePermission.cpPermissionsFlag & 8192) > 0 \n                UNION\n                /* Get ClazzUids where the active user can view members based a grant directly to them */\n                SELECT CoursePermission.cpClazzUid\n                  FROM CoursePermission\n                 WHERE CoursePermission.cpToPersonUid  = :accountPersonUid\n                   AND (CoursePermission.cpPermissionsFlag & 8192) > 0\n               )\n        SELECT ClazzEnrolment.*\n          FROM ClazzEnrolment\n         WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid\n            OR (    ClazzEnrolment.clazzEnrolmentClazzUid IN \n                        (SELECT CanViewMembersClazzesViaCoursePermission.clazzUid\n                           FROM CanViewMembersClazzesViaCoursePermission)\n                AND (:otherPersonUid = 0 OR ClazzEnrolment.clazzEnrolmentPersonUid = :otherPersonUid)   \n                )\n    ")
  public actual abstract suspend
      fun findClazzEnrolmentEntitiesForPersonViewPermissionCheck(accountPersonUid: Long,
      otherPersonUid: Long): List<ClazzEnrolment>
}
