package com.ustadmobile.core.db.dao

import androidx.paging.PagingSource
import androidx.room.Dao
import androidx.room.Insert
import androidx.room.Query
import com.ustadmobile.lib.db.composites.AssignmentPermissionAndActiveUserSubmitterUid
import com.ustadmobile.lib.db.composites.CoursePermissionAndEnrolment
import com.ustadmobile.lib.db.composites.CoursePermissionAndListDetail
import com.ustadmobile.lib.db.composites.PermissionPair
import com.ustadmobile.lib.db.composites.PermissionTriple
import com.ustadmobile.lib.db.entities.CoursePermission
import kotlin.Any
import kotlin.Boolean
import kotlin.Int
import kotlin.Long
import kotlin.collections.List
import kotlinx.coroutines.flow.Flow

@Dao
public actual abstract class CoursePermissionDao {
  @Query(`value` =
      "\n        SELECT CoursePermission.*, Person.*, PersonPicture.*\n          FROM CoursePermission\n               LEFT JOIN Person\n                         ON Person.personUid = CoursePermission.cpToPersonUid\n               LEFT JOIN PersonPicture\n                         ON PersonPicture.personPictureUid = Person.personUid\n         WHERE CoursePermission.cpClazzUid = :clazzUid \n           AND (CAST(:includeDeleted AS INTEGER) = 1 OR NOT CoursePermission.cpIsDeleted) \n    ")
  public actual abstract fun findByClazzUidAsPagingSource(clazzUid: Long, includeDeleted: Boolean):
      PagingSource<Int, CoursePermissionAndListDetail>

  @Query(`value` =
      "\n        SELECT CoursePermission.*\n          FROM CoursePermission\n         WHERE CoursePermission.cpUid = :uid\n           AND CoursePermission.cpClazzUid = :clazzUid\n    ")
  public actual abstract suspend fun findByUidAndClazzUid(uid: Long, clazzUid: Long):
      CoursePermission?

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

  @Insert(
    onConflict = 1,
    entity = Any::class,
  )
  public actual abstract suspend fun upsertAsync(coursePermission: CoursePermission)

  @Query(`value` =
      "\n        UPDATE CoursePermission\n           SET cpIsDeleted = :isDeleted,\n               cpLastModified = :updateTime\n         WHERE cpUid = :cpUid  \n    ")
  public actual abstract suspend fun setDeleted(
    cpUid: Long,
    isDeleted: Boolean,
    updateTime: Long,
  )

  @Query(`value` =
      "\n       SELECT CoursePermission.*, ClazzEnrolment_ForAccountPerson.*\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 = (\n        SELECT COALESCE(\n               (SELECT _ClazzEnrolment_ForClazzUid.clazzEnrolmentClazzUid\n                  FROM ClazzEnrolment _ClazzEnrolment_ForClazzUid\n                 WHERE _ClazzEnrolment_ForClazzUid.clazzEnrolmentUid = :clazzEnrolmentUid), 0)\n    )\n          AND (CoursePermission.cpToPersonUid = :accountPersonUid \n               OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)\n    ")
  public actual abstract suspend
      fun personHasPermissionWithClazzByEnrolmentUidEntities2(accountPersonUid: Long,
      clazzEnrolmentUid: Long): List<CoursePermissionAndEnrolment>

  @Query(`value` =
      "\n        SELECT CASE :clazzEnrolmentUid \n                WHEN 0 THEN (SELECT EXISTS(\n                         SELECT 1\n                           FROM SystemPermission\n                          WHERE :accountPersonUid != 0 \n                            AND SystemPermission.spToPersonUid = :accountPersonUid\n                            AND (SystemPermission.spPermissionsFlag & 2048) > 0\n                            AND NOT SystemPermission.spIsDeleted))\n                ELSE (\n                  SELECT (\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 = (\n        SELECT COALESCE(\n               (SELECT _ClazzEnrolment_ForClazzUid.clazzEnrolmentClazzUid\n                  FROM ClazzEnrolment _ClazzEnrolment_ForClazzUid\n                 WHERE _ClazzEnrolment_ForClazzUid.clazzEnrolmentUid = :clazzEnrolmentUid), 0)\n    )), 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 = (\n        SELECT COALESCE(\n               (SELECT _ClazzEnrolment_ForClazzUid.clazzEnrolmentClazzUid\n                  FROM ClazzEnrolment _ClazzEnrolment_ForClazzUid\n                 WHERE _ClazzEnrolment_ForClazzUid.clazzEnrolmentUid = :clazzEnrolmentUid), 0)\n    )\n                           AND (CoursePermission.cpToPersonUid = :accountPersonUid \n                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)\n                           AND (CoursePermission.cpPermissionsFlag & \n         \n                         (\n        CASE (\n        SELECT ClazzEnrolment.clazzEnrolmentRole\n          FROM ClazzEnrolment\n         WHERE ClazzEnrolment.clazzEnrolmentUid = :clazzEnrolmentUid         \n    )\n             WHEN 1000 THEN 8\n             ELSE 8\n        END     \n    )\n                         \n        ) > 0)\n              OR EXISTS(SELECT SystemPermission.spUid\n                          FROM SystemPermission\n                         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n                           AND (SystemPermission.spPermissionsFlag & \n     \n                         (\n        CASE (\n        SELECT ClazzEnrolment.clazzEnrolmentRole\n          FROM ClazzEnrolment\n         WHERE ClazzEnrolment.clazzEnrolmentUid = :clazzEnrolmentUid         \n    )\n             WHEN 1000 THEN 8\n             ELSE 8\n        END     \n    )\n                         \n        ) > 0)\n               )\n     \n                )\n               END \n    ")
  public actual abstract suspend fun userHasEnrolmentEditPermission(accountPersonUid: Long,
      clazzEnrolmentUid: Long): Boolean

  @Query(`value` =
      "\n       SELECT CoursePermission.*, ClazzEnrolment_ForAccountPerson.*\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 (:clazzUid = 0 OR CoursePermission.cpClazzUid = :clazzUid)\n          AND (CoursePermission.cpToPersonUid = :accountPersonUid \n               OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)\n    ")
  public actual abstract suspend fun personHasPermissionWithClazzEntities2(accountPersonUid: Long,
      clazzUid: Long): List<CoursePermissionAndEnrolment>

  @Query(`value` =
      "\n        SELECT (:clazzUid != 0 AND :accountPersonUid != 0)\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         :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    ")
  public actual abstract fun personHasPermissionWithClazzAsFlow2(
    accountPersonUid: Long,
    clazzUid: Long,
    permission: Long,
  ): Flow<Boolean>

  @Query(`value` =
      "\n        SELECT (    (:clazzUid != 0 AND :accountPersonUid != 0)\n                AND ((\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         :firstPermission\n                     \n        ) > 0)\n              OR EXISTS(SELECT SystemPermission.spUid\n                          FROM SystemPermission\n                         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n                           AND (SystemPermission.spPermissionsFlag & \n     :firstPermission\n                     \n        ) > 0)\n               )\n    )\n               ) AS firstPermission,\n               (    (:clazzUid != 0 AND :accountPersonUid != 0)\n                AND ((\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         :secondPermission\n                     \n        ) > 0)\n              OR EXISTS(SELECT SystemPermission.spUid\n                          FROM SystemPermission\n                         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n                           AND (SystemPermission.spPermissionsFlag & \n     :secondPermission\n                     \n        ) > 0)\n               )\n    )\n               ) AS secondPermission\n    ")
  public actual abstract fun personHasPermissionWithClazzPairAsFlow(
    accountPersonUid: Long,
    clazzUid: Long,
    firstPermission: Long,
    secondPermission: Long,
  ): Flow<PermissionPair>

  @Query(`value` =
      "\n        SELECT (    (:clazzUid != 0 AND :accountPersonUid != 0)\n                AND ((\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         :firstPermission\n                     \n        ) > 0)\n              OR EXISTS(SELECT SystemPermission.spUid\n                          FROM SystemPermission\n                         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n                           AND (SystemPermission.spPermissionsFlag & \n     :firstPermission\n                     \n        ) > 0)\n               )\n    )\n               ) AS firstPermission,\n               (    (:clazzUid != 0 AND :accountPersonUid != 0)\n                AND ((\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         :secondPermission\n                     \n        ) > 0)\n              OR EXISTS(SELECT SystemPermission.spUid\n                          FROM SystemPermission\n                         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n                           AND (SystemPermission.spPermissionsFlag & \n     :secondPermission\n                     \n        ) > 0)\n               )\n    )\n               ) AS secondPermission\n    ")
  public actual abstract suspend fun personHasPermissionWithClazzPairAsync(
    accountPersonUid: Long,
    clazzUid: Long,
    firstPermission: Long,
    secondPermission: Long,
  ): PermissionPair

  @Query(`value` =
      "\n        SELECT (    (:clazzUid != 0 AND :accountPersonUid != 0)\n                AND ((\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         :firstPermission\n                     \n        ) > 0)\n              OR EXISTS(SELECT SystemPermission.spUid\n                          FROM SystemPermission\n                         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n                           AND (SystemPermission.spPermissionsFlag & \n     :firstPermission\n                     \n        ) > 0)\n               )\n    )\n               ) AS firstPermission,\n               (    (:clazzUid != 0 AND :accountPersonUid != 0)\n                AND ((\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         :secondPermission\n                     \n        ) > 0)\n              OR EXISTS(SELECT SystemPermission.spUid\n                          FROM SystemPermission\n                         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n                           AND (SystemPermission.spPermissionsFlag & \n     :secondPermission\n                     \n        ) > 0)\n               )\n    )\n               ) AS secondPermission,\n               (    (:clazzUid != 0 AND :accountPersonUid != 0)\n                AND ((\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         :thirdPermission\n                     \n        ) > 0)\n              OR EXISTS(SELECT SystemPermission.spUid\n                          FROM SystemPermission\n                         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n                           AND (SystemPermission.spPermissionsFlag & \n     :thirdPermission\n                     \n        ) > 0)\n               )\n    )\n               ) AS thirdPermission\n    ")
  public actual abstract fun personHasPermissionWithClazzTripleAsFlow(
    accountPersonUid: Long,
    clazzUid: Long,
    firstPermission: Long,
    secondPermission: Long,
    thirdPermission: Long,
  ): Flow<PermissionTriple>

  @Query(`value` =
      "\n        SELECT (:clazzUid != 0 AND :accountPersonUid != 0)\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         :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    ")
  public actual abstract suspend fun personHasPermissionWithClazzAsync2(
    accountPersonUid: Long,
    clazzUid: Long,
    permission: Long,
  ): Boolean

  @Query(`value` =
      "\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    ")
  public actual abstract suspend
      fun findApplicableCoursePermissionEntitiesForAccountPerson(accountPersonUid: Long):
      List<CoursePermission>

  @Query(`value` =
      "\n        WITH \n        AccountSubmitterUid(accountSubmitterUid) AS \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    ,\n             CanMarkSubmitter(canMark) AS\n             (SELECT CASE (SELECT ClazzAssignment.caMarkingType\n                             FROM ClazzAssignment\n                            WHERE ClazzAssignment.caUid = :assignmentUid)\n                           WHEN 2 THEN \n                                EXISTS(SELECT 1\n                                         FROM PeerReviewerAllocation\n                                        WHERE PeerReviewerAllocation.praToMarkerSubmitterUid = :submitterUid\n                                          AND PeerReviewerAllocation.praMarkerSubmitterUid =\n                                              (SELECT AccountSubmitterUid.accountSubmitterUid\n                                                 FROM AccountSubmitterUid))\n                           ELSE ((\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         256\n                                 \n        ) > 0)\n              OR EXISTS(SELECT SystemPermission.spUid\n                          FROM SystemPermission\n                         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n                           AND (SystemPermission.spPermissionsFlag & \n     256\n                                 \n        ) > 0)\n               )\n    )\n                          END)\n\n             \n                    /* Can edit */\n             SELECT (     (:accountPersonUid != 0 AND :assignmentUid != 0 AND :clazzUid != 0 AND :submitterUid != 0)\n                      AND (SELECT CanMarkSubmitter.canMark \n                            FROM CanMarkSubmitter)) AS canMark,\n                    /* can view */   \n                    (     (:accountPersonUid != 0 AND :assignmentUid != 0 AND :clazzUid != 0 AND :submitterUid != 0)\n                      AND (     (SELECT CanMarkSubmitter.canMark\n                                   FROM CanMarkSubmitter)\n                             OR (SELECT :submitterUid = \n                                  (SELECT AccountSubmitterUid.accountSubmitterUid\n                                     FROM AccountSubmitterUid))\n                             OR ((\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                    ) AS canView,\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    \n                    ) AS canModerate,\n                    (SELECT accountSubmitterUid\n                       FROM AccountSubmitterUid) AS activeUserSubmitterUid\n             \n    ")
  public actual abstract fun userPermissionsForAssignmentSubmitterUid(
    accountPersonUid: Long,
    assignmentUid: Long,
    clazzUid: Long,
    submitterUid: Long,
  ): Flow<AssignmentPermissionAndActiveUserSubmitterUid>
}
