package com.ustadmobile.core.db.dao.xapi

import androidx.paging.PagingSource
import androidx.room.Dao
import androidx.room.Insert
import androidx.room.Query
import androidx.room.RawQuery
import com.ustadmobile.door.DoorQuery
import com.ustadmobile.lib.db.composites.BlockStatus
import com.ustadmobile.lib.db.composites.PersonAndPictureAndNumAttempts
import com.ustadmobile.lib.db.composites.xapi.SessionTimeAndProgressInfo
import com.ustadmobile.lib.db.composites.xapi.StatementAndActivity
import com.ustadmobile.lib.db.composites.xapi.StatementEntityAndRelated
import com.ustadmobile.lib.db.composites.xapi.StatementEntityAndVerb
import com.ustadmobile.lib.db.composites.xapi.VerbEntityAndName
import com.ustadmobile.lib.db.entities.Person
import com.ustadmobile.lib.db.entities.StatementEntityAndDisplayDetails
import com.ustadmobile.lib.db.entities.StatementReportData
import com.ustadmobile.lib.db.entities.xapi.ActivityLangMapEntry
import com.ustadmobile.lib.db.entities.xapi.ActorEntity
import com.ustadmobile.lib.db.entities.xapi.StatementEntity
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 StatementDao {
  @Insert(
    onConflict = 5,
    entity = Any::class,
  )
  public actual abstract suspend fun insertOrIgnoreListAsync(entityList: List<StatementEntity>)

  @Query(`value` = "SELECT * From StatementEntity LIMIT 1")
  public actual abstract fun getOneStatement(): Flow<StatementEntity?>

  @RawQuery(observedEntities = arrayOf())
  public actual abstract suspend fun getResults(query: DoorQuery): List<StatementReportData>

  @RawQuery(observedEntities = arrayOf(StatementEntity::class, Person::class))
  public actual abstract fun getListResults(query: DoorQuery):
      PagingSource<Int, StatementEntityAndDisplayDetails>

  @Query(`value` = "SELECT * FROM PERSON LIMIT 1")
  public actual abstract fun getPerson(): Person?

  @Query(`value` =
      "\n        SELECT StatementEntity.*\n          FROM StatementEntity\n         WHERE (    (:statementIdHi = 0 AND :statementIdLo = 0) \n                 OR (statementIdHi = :statementIdHi AND statementIdLo = :statementIdLo))\n                  \n    ")
  public actual abstract suspend fun getStatements(statementIdHi: Long, statementIdLo: Long):
      List<StatementEntity>

  @Query(`value` =
      "\n        SELECT StatementEntity.*\n          FROM StatementEntity\n         WHERE statementIdHi = :statementIdHi \n           AND statementIdLo = :statementIdLo       \n    ")
  public actual abstract suspend fun findById(statementIdHi: Long, statementIdLo: Long):
      StatementEntity?

  @Query(`value` =
      "\n        SELECT StatementEntity.*\n               \n            \n          FROM StatementEntity\n         WHERE StatementEntity.statementActorPersonUid = :accountPersonUid\n           AND StatementEntity.statementContentEntryUid = :contentEntryUid\n           AND CAST(StatementEntity.completionOrProgress AS INTEGER) = 1\n           AND (:courseBlockUid = 0 OR StatementEntity.statementCbUid = :courseBlockUid)\n    \n        AND (    (CAST(StatementEntity.resultCompletion AS INTEGER) = 1)\n              OR (StatementEntity.extensionProgress IS NOT NULL))\n        \n        \n    \n    ")
  public actual abstract suspend fun findStatusStatementsByContentEntryUid(
    contentEntryUid: Long,
    courseBlockUid: Long,
    accountPersonUid: Long,
  ): List<StatementEntity>

  @Query(`value` =
      "\n        SELECT StatementEntity.*\n               \n        FROM StatementEntity\n       WHERE StatementEntity.statementActorPersonUid = :accountPersonUid\n         AND StatementEntity.statementContentEntryUid IN (\n             SELECT ContentEntryParentChildJoin.cepcjChildContentEntryUid\n               FROM ContentEntryParentChildJoin\n              WHERE ContentEntryParentChildJoin.cepcjParentContentEntryUid = :parentUid)\n         AND CAST(StatementEntity.completionOrProgress AS INTEGER) = 1\n         AND (    (CAST(StatementEntity.resultCompletion AS INTEGER) = 1)\n              OR (StatementEntity.extensionProgress IS NOT NULL))     \n    \n    ")
  public actual abstract suspend fun findStatusStatementByParentContentEntryUid(parentUid: Long,
      accountPersonUid: Long): List<StatementEntity>

  @Query(`value` =
      "\n        WITH PersonUids(personUid) AS (\n            SELECT :accountPersonUid AS personUid\n        ),\n        \n        \n        -- Get the ActorUids for the PersonUids See ActoryEntity doc for info on this join relationship\n        AgentActorUidsForPersonUid(actorUid, actorPersonUid) AS(\n             SELECT ActorEntity.actorUid AS actorUid, \n                    ActorEntity.actorPersonUid AS actorPersonUid\n               FROM ActorEntity\n              WHERE ActorEntity.actorPersonUid IN\n                    (SELECT PersonUids.personUid\n                       FROM PersonUids)           \n        ),\n        \n        -- Add in group actor uids\n        ActorUidsForPersonUid(actorUid, actorPersonUid) AS (\n             SELECT AgentActorUidsForPersonUid.actorUid AS actorUid,\n                    AgentActorUidsForPersonUid.actorPersonUid AS actorPersonUid\n               FROM AgentActorUidsForPersonUid     \n              UNION \n             SELECT GroupMemberActorJoin.gmajGroupActorUid AS actorUid,\n                    AgentActorUidsForPersonUid.actorPersonUid AS actorPersonUid\n               FROM AgentActorUidsForPersonUid\n                    JOIN GroupMemberActorJoin \n                         ON GroupMemberActorJoin.gmajMemberActorUid = AgentActorUidsForPersonUid.actorUid\n        )\n    \n        \n        SELECT StatementEntity.*\n               \n               FROM StatementEntity\n              WHERE (\n        (SELECT EXISTS(\n                SELECT 1\n                  FROM ClazzEnrolment\n                 WHERE ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid\n                   AND ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid\n                   AND ClazzEnrolment.clazzEnrolmentRole = 1000))\n    )\n                AND StatementEntity.statementActorUid IN (\n                    SELECT ActorUidsForPersonUid.actorUid\n                      FROM ActorUidsForPersonUid)\n                AND StatementEntity.statementClazzUid = :clazzUid\n                AND (    (CAST(StatementEntity.resultCompletion AS INTEGER) = 1)\n                      OR (StatementEntity.extensionProgress IS NOT NULL))\n    \n    ")
  public actual abstract suspend fun findStatusStatementsForStudentByClazzUid(clazzUid: Long,
      accountPersonUid: Long): List<StatementEntity>

  @Query(`value` =
      "\n        -- Get the PersonUids for those that are within the current page as per studentsLimit and \n        -- studentsOffset\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        -- Get the ActorUids for the PersonUids See ActoryEntity doc for info on this join relationship\n        AgentActorUidsForPersonUid(actorUid, actorPersonUid) AS(\n             SELECT ActorEntity.actorUid AS actorUid, \n                    ActorEntity.actorPersonUid AS actorPersonUid\n               FROM ActorEntity\n              WHERE ActorEntity.actorPersonUid IN\n                    (SELECT PersonUids.personUid\n                       FROM PersonUids)           \n        ),\n        \n        -- Add in group actor uids\n        ActorUidsForPersonUid(actorUid, actorPersonUid) AS (\n             SELECT AgentActorUidsForPersonUid.actorUid AS actorUid,\n                    AgentActorUidsForPersonUid.actorPersonUid AS actorPersonUid\n               FROM AgentActorUidsForPersonUid     \n              UNION \n             SELECT GroupMemberActorJoin.gmajGroupActorUid AS actorUid,\n                    AgentActorUidsForPersonUid.actorPersonUid AS actorPersonUid\n               FROM AgentActorUidsForPersonUid\n                    JOIN GroupMemberActorJoin \n                         ON GroupMemberActorJoin.gmajMemberActorUid = AgentActorUidsForPersonUid.actorUid\n        )\n    \n\n        \n        -- Fetch all statements that could be completion or progress for the Gradebook report\n        SELECT StatementEntity.*, ActorEntity.*, GroupMemberActorJoin.*\n          FROM StatementEntity\n               JOIN ActorEntity\n                    ON ActorEntity.actorUid = StatementEntity.statementActorUid\n               LEFT JOIN GroupMemberActorJoin\n                    ON ActorEntity.actorObjectType = 2\n                       AND GroupMemberActorJoin.gmajGroupActorUid = StatementEntity.statementActorUid\n                       AND GroupMemberActorJoin.gmajMemberActorUid IN (\n                           SELECT DISTINCT ActorUidsForPersonUid.actorUid\n                             FROM ActorUidsForPersonUid)\n         WHERE StatementEntity.statementClazzUid = :clazzUid\n           AND StatementEntity.completionOrProgress = :completionOrProgressTrueVal\n           AND StatementEntity.statementActorUid IN (\n               SELECT DISTINCT ActorUidsForPersonUid.actorUid\n                 FROM ActorUidsForPersonUid) \n           AND (      StatementEntity.resultScoreScaled IS NOT NULL\n                   OR StatementEntity.resultCompletion IS NOT NULL\n                   OR StatementEntity.resultSuccess IS NOT NULL\n                   OR StatementEntity.extensionProgress IS NOT NULL \n               )\n    \n    ")
  public actual abstract suspend fun findStatusForStudentsInClazzStatements(
    clazzUid: Long,
    roleId: Int,
    sortOrder: Int,
    searchText: String?,
    filter: Int,
    accountPersonUid: Long,
    currentTime: Long,
    studentsLimit: Int,
    studentsOffset: Int,
    completionOrProgressTrueVal: Boolean,
  ): List<StatementEntityAndRelated>

  @Query(`value` =
      "\n        WITH PersonUids(personUid) AS (\n            SELECT Person.personUid\n              FROM Person\n             WHERE Person.personUid IN (:studentPersonUids) \n        ),\n        \n        \n        -- Get the ActorUids for the PersonUids See ActoryEntity doc for info on this join relationship\n        AgentActorUidsForPersonUid(actorUid, actorPersonUid) AS(\n             SELECT ActorEntity.actorUid AS actorUid, \n                    ActorEntity.actorPersonUid AS actorPersonUid\n               FROM ActorEntity\n              WHERE ActorEntity.actorPersonUid IN\n                    (SELECT PersonUids.personUid\n                       FROM PersonUids)           \n        ),\n        \n        -- Add in group actor uids\n        ActorUidsForPersonUid(actorUid, actorPersonUid) AS (\n             SELECT AgentActorUidsForPersonUid.actorUid AS actorUid,\n                    AgentActorUidsForPersonUid.actorPersonUid AS actorPersonUid\n               FROM AgentActorUidsForPersonUid     \n              UNION \n             SELECT GroupMemberActorJoin.gmajGroupActorUid AS actorUid,\n                    AgentActorUidsForPersonUid.actorPersonUid AS actorPersonUid\n               FROM AgentActorUidsForPersonUid\n                    JOIN GroupMemberActorJoin \n                         ON GroupMemberActorJoin.gmajMemberActorUid = AgentActorUidsForPersonUid.actorUid\n        )\n    \n        \n        \n        -- Fetch all statements that could be completion or progress for the Gradebook report\n        SELECT StatementEntity.*, ActorEntity.*, GroupMemberActorJoin.*\n          FROM StatementEntity\n               JOIN ActorEntity\n                    ON ActorEntity.actorUid = StatementEntity.statementActorUid\n               LEFT JOIN GroupMemberActorJoin\n                    ON ActorEntity.actorObjectType = 2\n                       AND GroupMemberActorJoin.gmajGroupActorUid = StatementEntity.statementActorUid\n                       AND GroupMemberActorJoin.gmajMemberActorUid IN (\n                           SELECT DISTINCT ActorUidsForPersonUid.actorUid\n                             FROM ActorUidsForPersonUid)\n         WHERE StatementEntity.statementClazzUid = :clazzUid\n           AND StatementEntity.completionOrProgress = :completionOrProgressTrueVal\n           AND StatementEntity.statementActorUid IN (\n               SELECT DISTINCT ActorUidsForPersonUid.actorUid\n                 FROM ActorUidsForPersonUid) \n           AND (      StatementEntity.resultScoreScaled IS NOT NULL\n                   OR StatementEntity.resultCompletion IS NOT NULL\n                   OR StatementEntity.resultSuccess IS NOT NULL\n                   OR StatementEntity.extensionProgress IS NOT NULL \n               )\n    \n    ")
  public actual abstract suspend fun findStatusForStudentsInClazzByUidList(
    clazzUid: Long,
    studentPersonUids: List<Long>,
    completionOrProgressTrueVal: Boolean,
  ): List<StatementEntityAndRelated>

  @Query(`value` =
      "\n        WITH PersonUids(personUid) AS (\n            SELECT Person.personUid\n              FROM Person\n             WHERE Person.personUid IN (:studentPersonUids) \n        ),\n        \n        \n        -- Get the ActorUids for the PersonUids See ActoryEntity doc for info on this join relationship\n        AgentActorUidsForPersonUid(actorUid, actorPersonUid) AS(\n             SELECT ActorEntity.actorUid AS actorUid, \n                    ActorEntity.actorPersonUid AS actorPersonUid\n               FROM ActorEntity\n              WHERE ActorEntity.actorPersonUid IN\n                    (SELECT PersonUids.personUid\n                       FROM PersonUids)           \n        ),\n        \n        -- Add in group actor uids\n        ActorUidsForPersonUid(actorUid, actorPersonUid) AS (\n             SELECT AgentActorUidsForPersonUid.actorUid AS actorUid,\n                    AgentActorUidsForPersonUid.actorPersonUid AS actorPersonUid\n               FROM AgentActorUidsForPersonUid     \n              UNION \n             SELECT GroupMemberActorJoin.gmajGroupActorUid AS actorUid,\n                    AgentActorUidsForPersonUid.actorPersonUid AS actorPersonUid\n               FROM AgentActorUidsForPersonUid\n                    JOIN GroupMemberActorJoin \n                         ON GroupMemberActorJoin.gmajMemberActorUid = AgentActorUidsForPersonUid.actorUid\n        )\n    \n        \n        SELECT ActorEntity.*\n          FROM ActorEntity\n         WHERE ActorEntity.actorPersonUid IN \n               (SELECT PersonUids.personUid\n                  FROM PersonUids)\n           AND :clazzUid = :clazzUid\n           AND :accountPersonUid = :accountPersonUid\n    ")
  public actual abstract suspend fun findActorEntitiesForStudentInClazzByUidList(
    clazzUid: Long,
    studentPersonUids: List<Long>,
    accountPersonUid: Long,
  ): List<ActorEntity>

  @Query(`value` =
      "\n        WITH PersonUids(personUid) AS (\n             SELECT Person.personUid\n               FROM Person\n              WHERE Person.personUid IN (:studentPersonUids)\n        ),\n        \n        \n        -- Get the ActorUids for the PersonUids See ActoryEntity doc for info on this join relationship\n        AgentActorUidsForPersonUid(actorUid, actorPersonUid) AS(\n             SELECT ActorEntity.actorUid AS actorUid, \n                    ActorEntity.actorPersonUid AS actorPersonUid\n               FROM ActorEntity\n              WHERE ActorEntity.actorPersonUid IN\n                    (SELECT PersonUids.personUid\n                       FROM PersonUids)           \n        ),\n        \n        -- Add in group actor uids\n        ActorUidsForPersonUid(actorUid, actorPersonUid) AS (\n             SELECT AgentActorUidsForPersonUid.actorUid AS actorUid,\n                    AgentActorUidsForPersonUid.actorPersonUid AS actorPersonUid\n               FROM AgentActorUidsForPersonUid     \n              UNION \n             SELECT GroupMemberActorJoin.gmajGroupActorUid AS actorUid,\n                    AgentActorUidsForPersonUid.actorPersonUid AS actorPersonUid\n               FROM AgentActorUidsForPersonUid\n                    JOIN GroupMemberActorJoin \n                         ON GroupMemberActorJoin.gmajMemberActorUid = AgentActorUidsForPersonUid.actorUid\n        )\n    ,\n        \n        PersonUidsAndCourseBlocks(personUid, cbUid, cbType, caMarkingType) AS (\n             SELECT Person.personUid AS personUid,\n                    CourseBlock.cbUid AS cbUid,\n                    CourseBlock.cbType AS cbType,\n                    ClazzAssignment.caMarkingType AS caMarkingType\n               FROM Person\n                    JOIN CourseBlock\n                         ON CourseBlock.cbClazzUid = :clazzUid\n                    LEFT JOIN ClazzAssignment\n                         ON CourseBlock.cbType = 103\n                        AND ClazzAssignment.caUid = CourseBlock.cbEntityUid     \n              WHERE Person.personUid IN (:studentPersonUids)       \n        )\n        \n        SELECT PersonUidsAndCourseBlocks.personUid AS sPersonUid,\n               PersonUidsAndCourseBlocks.cbUid AS sCbUid,\n               (SELECT MAX(StatementEntity.extensionProgress)\n                  FROM StatementEntity\n                       \n       JOIN ActorEntity\n            ON ActorEntity.actorUid = StatementEntity.statementActorUid\n       LEFT JOIN GroupMemberActorJoin\n            ON ActorEntity.actorObjectType = 2\n               AND (GroupMemberActorJoin.gmajGroupActorUid, GroupMemberActorJoin.gmajMemberActorUid) IN (\n                   SELECT GroupMemberActorJoin.gmajGroupActorUid, \n                          GroupMemberActorJoin.gmajMemberActorUid\n                     FROM GroupMemberActorJoin\n                    WHERE GroupMemberActorJoin.gmajGroupActorUid = StatementEntity.statementActorUid\n                      AND GroupMemberActorJoin.gmajMemberActorUid IN (\n                          SELECT ActorUidsForPersonUid.actorUid\n                            FROM ActorUidsForPersonUid\n                           WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid))\n    \n                 WHERE \n            StatementEntity.statementCbUid = PersonUidsAndCourseBlocks.cbUid\n        AND StatementEntity.statementActorUid IN (\n            SELECT ActorUidsForPersonUid.actorUid\n              FROM ActorUidsForPersonUid\n             WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid)  \n                   \n     \n               ) AS sProgress,\n               (SELECT EXISTS(\n                       SELECT 1\n                         FROM StatementEntity\n                              \n       JOIN ActorEntity\n            ON ActorEntity.actorUid = StatementEntity.statementActorUid\n       LEFT JOIN GroupMemberActorJoin\n            ON ActorEntity.actorObjectType = 2\n               AND (GroupMemberActorJoin.gmajGroupActorUid, GroupMemberActorJoin.gmajMemberActorUid) IN (\n                   SELECT GroupMemberActorJoin.gmajGroupActorUid, \n                          GroupMemberActorJoin.gmajMemberActorUid\n                     FROM GroupMemberActorJoin\n                    WHERE GroupMemberActorJoin.gmajGroupActorUid = StatementEntity.statementActorUid\n                      AND GroupMemberActorJoin.gmajMemberActorUid IN (\n                          SELECT ActorUidsForPersonUid.actorUid\n                            FROM ActorUidsForPersonUid\n                           WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid))\n    \n                        WHERE (\n            StatementEntity.statementCbUid = PersonUidsAndCourseBlocks.cbUid\n        AND StatementEntity.statementActorUid IN (\n            SELECT ActorUidsForPersonUid.actorUid\n              FROM ActorUidsForPersonUid\n             WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid)  \n                   \n    )\n                          AND CAST(StatementEntity.resultCompletion AS INTEGER) = 1\n               )) AS sIsCompleted,\n               (SELECT CASE\n                       /*If there is a statement marked as success, then count as successful even if\n                        *there were subsequent failed attempts\n                        */\n                       WHEN (\n                            SELECT EXISTS(\n                                    SELECT 1\n                                      FROM StatementEntity\n                                           \n       JOIN ActorEntity\n            ON ActorEntity.actorUid = StatementEntity.statementActorUid\n       LEFT JOIN GroupMemberActorJoin\n            ON ActorEntity.actorObjectType = 2\n               AND (GroupMemberActorJoin.gmajGroupActorUid, GroupMemberActorJoin.gmajMemberActorUid) IN (\n                   SELECT GroupMemberActorJoin.gmajGroupActorUid, \n                          GroupMemberActorJoin.gmajMemberActorUid\n                     FROM GroupMemberActorJoin\n                    WHERE GroupMemberActorJoin.gmajGroupActorUid = StatementEntity.statementActorUid\n                      AND GroupMemberActorJoin.gmajMemberActorUid IN (\n                          SELECT ActorUidsForPersonUid.actorUid\n                            FROM ActorUidsForPersonUid\n                           WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid))\n    \n                                    WHERE (\n            StatementEntity.statementCbUid = PersonUidsAndCourseBlocks.cbUid\n        AND StatementEntity.statementActorUid IN (\n            SELECT ActorUidsForPersonUid.actorUid\n              FROM ActorUidsForPersonUid\n             WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid)  \n                   \n    )\n                                      AND CAST(StatementEntity.resultSuccess AS INTEGER) = 1\n                                   )                           \n                       ) THEN 1\n                       /*If there are no statements marked as success, however there are statements marekd as fail,\n                        *then count as fail \n                        */\n                       WHEN (\n                            SELECT EXISTS(\n                                    SELECT 1\n                                      FROM StatementEntity\n                                           \n       JOIN ActorEntity\n            ON ActorEntity.actorUid = StatementEntity.statementActorUid\n       LEFT JOIN GroupMemberActorJoin\n            ON ActorEntity.actorObjectType = 2\n               AND (GroupMemberActorJoin.gmajGroupActorUid, GroupMemberActorJoin.gmajMemberActorUid) IN (\n                   SELECT GroupMemberActorJoin.gmajGroupActorUid, \n                          GroupMemberActorJoin.gmajMemberActorUid\n                     FROM GroupMemberActorJoin\n                    WHERE GroupMemberActorJoin.gmajGroupActorUid = StatementEntity.statementActorUid\n                      AND GroupMemberActorJoin.gmajMemberActorUid IN (\n                          SELECT ActorUidsForPersonUid.actorUid\n                            FROM ActorUidsForPersonUid\n                           WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid))\n    \n                                    WHERE (\n            StatementEntity.statementCbUid = PersonUidsAndCourseBlocks.cbUid\n        AND StatementEntity.statementActorUid IN (\n            SELECT ActorUidsForPersonUid.actorUid\n              FROM ActorUidsForPersonUid\n             WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid)  \n                   \n    )\n                                      AND CAST(StatementEntity.resultSuccess AS INTEGER) = 0\n                                   )                           \n                       ) THEN 0\n                       /* Else there is no known success/fail result*/\n                       ELSE NULL\n                       END\n               ) AS sIsSuccess,\n               -- See ClazzGradebookScreen for info on which score is selected\n               (SELECT CASE\n                       -- When there is a peer marked assignment, take the average of the latest distinct ...\n                       WHEN (     PersonUidsAndCourseBlocks.cbType = 103\n                              AND PersonUidsAndCourseBlocks.caMarkingType = 2\n                            ) \n                            THEN (SELECT AVG(StatementEntity.resultScoreScaled)\n                                    FROM StatementEntity\n                                         \n       JOIN ActorEntity\n            ON ActorEntity.actorUid = StatementEntity.statementActorUid\n       LEFT JOIN GroupMemberActorJoin\n            ON ActorEntity.actorObjectType = 2\n               AND (GroupMemberActorJoin.gmajGroupActorUid, GroupMemberActorJoin.gmajMemberActorUid) IN (\n                   SELECT GroupMemberActorJoin.gmajGroupActorUid, \n                          GroupMemberActorJoin.gmajMemberActorUid\n                     FROM GroupMemberActorJoin\n                    WHERE GroupMemberActorJoin.gmajGroupActorUid = StatementEntity.statementActorUid\n                      AND GroupMemberActorJoin.gmajMemberActorUid IN (\n                          SELECT ActorUidsForPersonUid.actorUid\n                            FROM ActorUidsForPersonUid\n                           WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid))\n    \n                                   WHERE (\n            StatementEntity.statementCbUid = PersonUidsAndCourseBlocks.cbUid\n        AND StatementEntity.statementActorUid IN (\n            SELECT ActorUidsForPersonUid.actorUid\n              FROM ActorUidsForPersonUid\n             WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid)  \n                   \n    )\n                                     AND StatementEntity.timestamp = (\n                                         SELECT MAX(StatementEntity_Inner.timestamp)\n                                           FROM StatementEntity StatementEntity_Inner\n                                                \n       JOIN ActorEntity ActorEntity_Inner\n            ON ActorEntity_Inner.actorUid = StatementEntity_Inner.statementActorUid\n       LEFT JOIN GroupMemberActorJoin GroupMemberActorJoin_Inner\n            ON ActorEntity_Inner.actorObjectType = 2\n               AND (GroupMemberActorJoin_Inner.gmajGroupActorUid, GroupMemberActorJoin_Inner.gmajMemberActorUid) IN (\n                   SELECT GroupMemberActorJoin.gmajGroupActorUid, \n                          GroupMemberActorJoin.gmajMemberActorUid\n                     FROM GroupMemberActorJoin\n                    WHERE GroupMemberActorJoin.gmajGroupActorUid = StatementEntity.statementActorUid\n                      AND GroupMemberActorJoin.gmajMemberActorUid IN (\n                          SELECT ActorUidsForPersonUid.actorUid\n                            FROM ActorUidsForPersonUid\n                           WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid))\n    \n                                          WHERE (\n            StatementEntity_Inner.statementCbUid = PersonUidsAndCourseBlocks.cbUid\n        AND StatementEntity_Inner.statementActorUid IN (\n            SELECT ActorUidsForPersonUid.actorUid\n              FROM ActorUidsForPersonUid\n             WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid)  \n                   \n    )\n                                            AND StatementEntity_Inner.contextInstructorActorUid = StatementEntity.contextInstructorActorUid)\n                                   LIMIT 1)\n                       -- When an assignment, but not peer marked, then the latest score     \n                       WHEN PersonUidsAndCourseBlocks.cbType = 103\n                            THEN (SELECT StatementEntity.resultScoreScaled\n                                    FROM StatementEntity\n                                         \n       JOIN ActorEntity\n            ON ActorEntity.actorUid = StatementEntity.statementActorUid\n       LEFT JOIN GroupMemberActorJoin\n            ON ActorEntity.actorObjectType = 2\n               AND (GroupMemberActorJoin.gmajGroupActorUid, GroupMemberActorJoin.gmajMemberActorUid) IN (\n                   SELECT GroupMemberActorJoin.gmajGroupActorUid, \n                          GroupMemberActorJoin.gmajMemberActorUid\n                     FROM GroupMemberActorJoin\n                    WHERE GroupMemberActorJoin.gmajGroupActorUid = StatementEntity.statementActorUid\n                      AND GroupMemberActorJoin.gmajMemberActorUid IN (\n                          SELECT ActorUidsForPersonUid.actorUid\n                            FROM ActorUidsForPersonUid\n                           WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid))\n    \n                                   WHERE (\n            StatementEntity.statementCbUid = PersonUidsAndCourseBlocks.cbUid\n        AND StatementEntity.statementActorUid IN (\n            SELECT ActorUidsForPersonUid.actorUid\n              FROM ActorUidsForPersonUid\n             WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid)  \n                   \n    )\n                                ORDER BY StatementEntity.timestamp DESC\n                                   LIMIT 1)\n                       -- else the best score accomplished so far            \n                       ELSE (SELECT MAX(StatementEntity.resultScoreScaled) \n                               FROM StatementEntity\n                                    \n       JOIN ActorEntity\n            ON ActorEntity.actorUid = StatementEntity.statementActorUid\n       LEFT JOIN GroupMemberActorJoin\n            ON ActorEntity.actorObjectType = 2\n               AND (GroupMemberActorJoin.gmajGroupActorUid, GroupMemberActorJoin.gmajMemberActorUid) IN (\n                   SELECT GroupMemberActorJoin.gmajGroupActorUid, \n                          GroupMemberActorJoin.gmajMemberActorUid\n                     FROM GroupMemberActorJoin\n                    WHERE GroupMemberActorJoin.gmajGroupActorUid = StatementEntity.statementActorUid\n                      AND GroupMemberActorJoin.gmajMemberActorUid IN (\n                          SELECT ActorUidsForPersonUid.actorUid\n                            FROM ActorUidsForPersonUid\n                           WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid))\n    \n                              WHERE (\n            StatementEntity.statementCbUid = PersonUidsAndCourseBlocks.cbUid\n        AND StatementEntity.statementActorUid IN (\n            SELECT ActorUidsForPersonUid.actorUid\n              FROM ActorUidsForPersonUid\n             WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid)  \n                   \n    ))            \n                       END\n               ) AS sScoreScaled\n          FROM PersonUidsAndCourseBlocks\n         WHERE :accountPersonUid = :accountPersonUid \n    ")
  public actual abstract suspend fun findStatusForStudentsInClazz(
    clazzUid: Long,
    studentPersonUids: List<Long>,
    accountPersonUid: Long,
  ): List<BlockStatus>

  @Query(`value` =
      "\n        WITH PersonUids(personUid) AS (\n             SELECT Person.personUid\n               FROM Person\n              WHERE Person.personUid IN (:studentPersonUids)\n        ),\n        \n        \n        -- Get the ActorUids for the PersonUids See ActoryEntity doc for info on this join relationship\n        AgentActorUidsForPersonUid(actorUid, actorPersonUid) AS(\n             SELECT ActorEntity.actorUid AS actorUid, \n                    ActorEntity.actorPersonUid AS actorPersonUid\n               FROM ActorEntity\n              WHERE ActorEntity.actorPersonUid IN\n                    (SELECT PersonUids.personUid\n                       FROM PersonUids)           \n        ),\n        \n        -- Add in group actor uids\n        ActorUidsForPersonUid(actorUid, actorPersonUid) AS (\n             SELECT AgentActorUidsForPersonUid.actorUid AS actorUid,\n                    AgentActorUidsForPersonUid.actorPersonUid AS actorPersonUid\n               FROM AgentActorUidsForPersonUid     \n              UNION \n             SELECT GroupMemberActorJoin.gmajGroupActorUid AS actorUid,\n                    AgentActorUidsForPersonUid.actorPersonUid AS actorPersonUid\n               FROM AgentActorUidsForPersonUid\n                    JOIN GroupMemberActorJoin \n                         ON GroupMemberActorJoin.gmajMemberActorUid = AgentActorUidsForPersonUid.actorUid\n        )\n    ,\n        \n        PersonUidsAndCourseBlocks(personUid, cbUid, cbType, caMarkingType) AS (\n             SELECT Person.personUid AS personUid,\n                    CourseBlock.cbUid AS cbUid,\n                    CourseBlock.cbType AS cbType,\n                    ClazzAssignment.caMarkingType AS caMarkingType\n               FROM Person\n                    JOIN CourseBlock\n                         ON CourseBlock.cbClazzUid = :clazzUid\n                    LEFT JOIN ClazzAssignment\n                         ON CourseBlock.cbType = 103\n                        AND ClazzAssignment.caUid = CourseBlock.cbEntityUid     \n              WHERE Person.personUid IN (:studentPersonUids)       \n        )\n        \n        SELECT PersonUidsAndCourseBlocks.personUid AS sPersonUid,\n               PersonUidsAndCourseBlocks.cbUid AS sCbUid,\n               (SELECT MAX(StatementEntity.extensionProgress)\n                  FROM StatementEntity\n                       \n       JOIN ActorEntity\n            ON ActorEntity.actorUid = StatementEntity.statementActorUid\n       LEFT JOIN GroupMemberActorJoin\n            ON ActorEntity.actorObjectType = 2\n               AND (GroupMemberActorJoin.gmajGroupActorUid, GroupMemberActorJoin.gmajMemberActorUid) IN (\n                   SELECT GroupMemberActorJoin.gmajGroupActorUid, \n                          GroupMemberActorJoin.gmajMemberActorUid\n                     FROM GroupMemberActorJoin\n                    WHERE GroupMemberActorJoin.gmajGroupActorUid = StatementEntity.statementActorUid\n                      AND GroupMemberActorJoin.gmajMemberActorUid IN (\n                          SELECT ActorUidsForPersonUid.actorUid\n                            FROM ActorUidsForPersonUid\n                           WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid))\n    \n                 WHERE \n            StatementEntity.statementCbUid = PersonUidsAndCourseBlocks.cbUid\n        AND StatementEntity.statementActorUid IN (\n            SELECT ActorUidsForPersonUid.actorUid\n              FROM ActorUidsForPersonUid\n             WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid)  \n                   \n     \n               ) AS sProgress,\n               (SELECT EXISTS(\n                       SELECT 1\n                         FROM StatementEntity\n                              \n       JOIN ActorEntity\n            ON ActorEntity.actorUid = StatementEntity.statementActorUid\n       LEFT JOIN GroupMemberActorJoin\n            ON ActorEntity.actorObjectType = 2\n               AND (GroupMemberActorJoin.gmajGroupActorUid, GroupMemberActorJoin.gmajMemberActorUid) IN (\n                   SELECT GroupMemberActorJoin.gmajGroupActorUid, \n                          GroupMemberActorJoin.gmajMemberActorUid\n                     FROM GroupMemberActorJoin\n                    WHERE GroupMemberActorJoin.gmajGroupActorUid = StatementEntity.statementActorUid\n                      AND GroupMemberActorJoin.gmajMemberActorUid IN (\n                          SELECT ActorUidsForPersonUid.actorUid\n                            FROM ActorUidsForPersonUid\n                           WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid))\n    \n                        WHERE (\n            StatementEntity.statementCbUid = PersonUidsAndCourseBlocks.cbUid\n        AND StatementEntity.statementActorUid IN (\n            SELECT ActorUidsForPersonUid.actorUid\n              FROM ActorUidsForPersonUid\n             WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid)  \n                   \n    )\n                          AND CAST(StatementEntity.resultCompletion AS INTEGER) = 1\n               )) AS sIsCompleted,\n               (SELECT CASE\n                       /*If there is a statement marked as success, then count as successful even if\n                        *there were subsequent failed attempts\n                        */\n                       WHEN (\n                            SELECT EXISTS(\n                                    SELECT 1\n                                      FROM StatementEntity\n                                           \n       JOIN ActorEntity\n            ON ActorEntity.actorUid = StatementEntity.statementActorUid\n       LEFT JOIN GroupMemberActorJoin\n            ON ActorEntity.actorObjectType = 2\n               AND (GroupMemberActorJoin.gmajGroupActorUid, GroupMemberActorJoin.gmajMemberActorUid) IN (\n                   SELECT GroupMemberActorJoin.gmajGroupActorUid, \n                          GroupMemberActorJoin.gmajMemberActorUid\n                     FROM GroupMemberActorJoin\n                    WHERE GroupMemberActorJoin.gmajGroupActorUid = StatementEntity.statementActorUid\n                      AND GroupMemberActorJoin.gmajMemberActorUid IN (\n                          SELECT ActorUidsForPersonUid.actorUid\n                            FROM ActorUidsForPersonUid\n                           WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid))\n    \n                                    WHERE (\n            StatementEntity.statementCbUid = PersonUidsAndCourseBlocks.cbUid\n        AND StatementEntity.statementActorUid IN (\n            SELECT ActorUidsForPersonUid.actorUid\n              FROM ActorUidsForPersonUid\n             WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid)  \n                   \n    )\n                                      AND CAST(StatementEntity.resultSuccess AS INTEGER) = 1\n                                   )                           \n                       ) THEN 1\n                       /*If there are no statements marked as success, however there are statements marekd as fail,\n                        *then count as fail \n                        */\n                       WHEN (\n                            SELECT EXISTS(\n                                    SELECT 1\n                                      FROM StatementEntity\n                                           \n       JOIN ActorEntity\n            ON ActorEntity.actorUid = StatementEntity.statementActorUid\n       LEFT JOIN GroupMemberActorJoin\n            ON ActorEntity.actorObjectType = 2\n               AND (GroupMemberActorJoin.gmajGroupActorUid, GroupMemberActorJoin.gmajMemberActorUid) IN (\n                   SELECT GroupMemberActorJoin.gmajGroupActorUid, \n                          GroupMemberActorJoin.gmajMemberActorUid\n                     FROM GroupMemberActorJoin\n                    WHERE GroupMemberActorJoin.gmajGroupActorUid = StatementEntity.statementActorUid\n                      AND GroupMemberActorJoin.gmajMemberActorUid IN (\n                          SELECT ActorUidsForPersonUid.actorUid\n                            FROM ActorUidsForPersonUid\n                           WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid))\n    \n                                    WHERE (\n            StatementEntity.statementCbUid = PersonUidsAndCourseBlocks.cbUid\n        AND StatementEntity.statementActorUid IN (\n            SELECT ActorUidsForPersonUid.actorUid\n              FROM ActorUidsForPersonUid\n             WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid)  \n                   \n    )\n                                      AND CAST(StatementEntity.resultSuccess AS INTEGER) = 0\n                                   )                           \n                       ) THEN 0\n                       /* Else there is no known success/fail result*/\n                       ELSE NULL\n                       END\n               ) AS sIsSuccess,\n               -- See ClazzGradebookScreen for info on which score is selected\n               (SELECT CASE\n                       -- When there is a peer marked assignment, take the average of the latest distinct ...\n                       WHEN (     PersonUidsAndCourseBlocks.cbType = 103\n                              AND PersonUidsAndCourseBlocks.caMarkingType = 2\n                            ) \n                            THEN (SELECT AVG(StatementEntity.resultScoreScaled)\n                                    FROM StatementEntity\n                                         \n       JOIN ActorEntity\n            ON ActorEntity.actorUid = StatementEntity.statementActorUid\n       LEFT JOIN GroupMemberActorJoin\n            ON ActorEntity.actorObjectType = 2\n               AND (GroupMemberActorJoin.gmajGroupActorUid, GroupMemberActorJoin.gmajMemberActorUid) IN (\n                   SELECT GroupMemberActorJoin.gmajGroupActorUid, \n                          GroupMemberActorJoin.gmajMemberActorUid\n                     FROM GroupMemberActorJoin\n                    WHERE GroupMemberActorJoin.gmajGroupActorUid = StatementEntity.statementActorUid\n                      AND GroupMemberActorJoin.gmajMemberActorUid IN (\n                          SELECT ActorUidsForPersonUid.actorUid\n                            FROM ActorUidsForPersonUid\n                           WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid))\n    \n                                   WHERE (\n            StatementEntity.statementCbUid = PersonUidsAndCourseBlocks.cbUid\n        AND StatementEntity.statementActorUid IN (\n            SELECT ActorUidsForPersonUid.actorUid\n              FROM ActorUidsForPersonUid\n             WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid)  \n                   \n    )\n                                     AND StatementEntity.timestamp = (\n                                         SELECT MAX(StatementEntity_Inner.timestamp)\n                                           FROM StatementEntity StatementEntity_Inner\n                                                \n       JOIN ActorEntity ActorEntity_Inner\n            ON ActorEntity_Inner.actorUid = StatementEntity_Inner.statementActorUid\n       LEFT JOIN GroupMemberActorJoin GroupMemberActorJoin_Inner\n            ON ActorEntity_Inner.actorObjectType = 2\n               AND (GroupMemberActorJoin_Inner.gmajGroupActorUid, GroupMemberActorJoin_Inner.gmajMemberActorUid) IN (\n                   SELECT GroupMemberActorJoin.gmajGroupActorUid, \n                          GroupMemberActorJoin.gmajMemberActorUid\n                     FROM GroupMemberActorJoin\n                    WHERE GroupMemberActorJoin.gmajGroupActorUid = StatementEntity.statementActorUid\n                      AND GroupMemberActorJoin.gmajMemberActorUid IN (\n                          SELECT ActorUidsForPersonUid.actorUid\n                            FROM ActorUidsForPersonUid\n                           WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid))\n    \n                                          WHERE (\n            StatementEntity_Inner.statementCbUid = PersonUidsAndCourseBlocks.cbUid\n        AND StatementEntity_Inner.statementActorUid IN (\n            SELECT ActorUidsForPersonUid.actorUid\n              FROM ActorUidsForPersonUid\n             WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid)  \n                   \n    )\n                                            AND StatementEntity_Inner.contextInstructorActorUid = StatementEntity.contextInstructorActorUid)\n                                   LIMIT 1)\n                       -- When an assignment, but not peer marked, then the latest score     \n                       WHEN PersonUidsAndCourseBlocks.cbType = 103\n                            THEN (SELECT StatementEntity.resultScoreScaled\n                                    FROM StatementEntity\n                                         \n       JOIN ActorEntity\n            ON ActorEntity.actorUid = StatementEntity.statementActorUid\n       LEFT JOIN GroupMemberActorJoin\n            ON ActorEntity.actorObjectType = 2\n               AND (GroupMemberActorJoin.gmajGroupActorUid, GroupMemberActorJoin.gmajMemberActorUid) IN (\n                   SELECT GroupMemberActorJoin.gmajGroupActorUid, \n                          GroupMemberActorJoin.gmajMemberActorUid\n                     FROM GroupMemberActorJoin\n                    WHERE GroupMemberActorJoin.gmajGroupActorUid = StatementEntity.statementActorUid\n                      AND GroupMemberActorJoin.gmajMemberActorUid IN (\n                          SELECT ActorUidsForPersonUid.actorUid\n                            FROM ActorUidsForPersonUid\n                           WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid))\n    \n                                   WHERE (\n            StatementEntity.statementCbUid = PersonUidsAndCourseBlocks.cbUid\n        AND StatementEntity.statementActorUid IN (\n            SELECT ActorUidsForPersonUid.actorUid\n              FROM ActorUidsForPersonUid\n             WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid)  \n                   \n    )\n                                ORDER BY StatementEntity.timestamp DESC\n                                   LIMIT 1)\n                       -- else the best score accomplished so far            \n                       ELSE (SELECT MAX(StatementEntity.resultScoreScaled) \n                               FROM StatementEntity\n                                    \n       JOIN ActorEntity\n            ON ActorEntity.actorUid = StatementEntity.statementActorUid\n       LEFT JOIN GroupMemberActorJoin\n            ON ActorEntity.actorObjectType = 2\n               AND (GroupMemberActorJoin.gmajGroupActorUid, GroupMemberActorJoin.gmajMemberActorUid) IN (\n                   SELECT GroupMemberActorJoin.gmajGroupActorUid, \n                          GroupMemberActorJoin.gmajMemberActorUid\n                     FROM GroupMemberActorJoin\n                    WHERE GroupMemberActorJoin.gmajGroupActorUid = StatementEntity.statementActorUid\n                      AND GroupMemberActorJoin.gmajMemberActorUid IN (\n                          SELECT ActorUidsForPersonUid.actorUid\n                            FROM ActorUidsForPersonUid\n                           WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid))\n    \n                              WHERE (\n            StatementEntity.statementCbUid = PersonUidsAndCourseBlocks.cbUid\n        AND StatementEntity.statementActorUid IN (\n            SELECT ActorUidsForPersonUid.actorUid\n              FROM ActorUidsForPersonUid\n             WHERE ActorUidsForPersonUid.actorPersonUid = PersonUidsAndCourseBlocks.personUid)  \n                   \n    ))            \n                       END\n               ) AS sScoreScaled\n          FROM PersonUidsAndCourseBlocks\n         WHERE :accountPersonUid = :accountPersonUid \n    ")
  public actual abstract fun findStatusForStudentsInClazzAsFlow(
    clazzUid: Long,
    studentPersonUids: List<Long>,
    accountPersonUid: Long,
  ): Flow<List<BlockStatus>>

  @Query(`value` =
      "\n        WITH MostRecentRegistration(statementIdHi, statementIdLo, contextRegistrationHi, contextRegistrationLo) AS (\n             SELECT StatementEntity.statementIdHi, StatementEntity.contextRegistrationLo,\n                    StatementEntity.contextRegistrationHi, StatementEntity.contextRegistrationLo\n               FROM StatementEntity\n              WHERE StatementEntity.statementActorUid = :actorUid\n                AND StatementEntity.statementObjectUid1 = :activityUid\n                AND StatementEntity.contextRegistrationHi != 0\n           ORDER BY StatementEntity.timestamp DESC\n              LIMIT 1\n        )\n        \n        SELECT StatementEntity.*\n          FROM StatementEntity\n         WHERE StatementEntity.statementIdHi = (SELECT statementIdHi FROM MostRecentRegistration)\n           AND StatementEntity.statementIdLo = (SELECT statementIdLo FROM MostRecentRegistration)\n           AND NOT EXISTS(\n                   SELECT 1\n                     FROM StatementEntity StatementEntity_Inner\n                    WHERE StatementEntity_Inner.statementActorUid = :actorUid\n                      AND StatementEntity_Inner.statementObjectUid1 = :activityUid\n                      AND StatementEntity_Inner.contextRegistrationHi = (SELECT contextRegistrationHi FROM MostRecentRegistration)\n                      AND StatementEntity_Inner.contextRegistrationLo = (SELECT contextRegistrationLo FROM MostRecentRegistration)\n                      AND CAST(StatementEntity_Inner.completionOrProgress AS INTEGER) = 1\n                      AND (     StatementEntity_Inner.resultCompletion IS NOT NULL \n                            AND CAST(StatementEntity_Inner.resultCompletion AS INTEGER) = 1)\n                     )\n           AND :accountPersonUid IN \n               (SELECT ActorEntity.actorPersonUid\n                  FROM ActorEntity\n                 WHERE ActorEntity.actorUid = :actorUid)          \n                     \n    ")
  public actual abstract suspend fun findResumableRegistration(
    activityUid: Long,
    accountPersonUid: Long,
    actorUid: Long,
  ): StatementEntity?

  @Query(`value` =
      "\n        SELECT StatementEntity.*\n          FROM Person\n               JOIN StatementEntity\n                    ON (StatementEntity.statementIdHi, StatementEntity.statementIdLo) IN\n                            (SELECT StatementEntity.statementIdHi, StatementEntity.statementIdLo\n                               FROM StatementEntity\n                              WHERE StatementEntity.statementContentEntryUid = :contentEntryUid\n                                AND StatementEntity.statementActorPersonUid = Person.personUid\n                                AND CAST(StatementEntity.completionOrProgress AS INTEGER) = 1\n                           ORDER BY StatementEntity.extensionProgress DESC\n                              LIMIT 1)\n                    OR (StatementEntity.statementIdHi, StatementEntity.statementIdLo) IN    \n                          (SELECT StatementEntity.statementIdHi, StatementEntity.statementIdLo\n                           FROM StatementEntity\n                          WHERE StatementEntity.statementContentEntryUid = :contentEntryUid\n                            AND StatementEntity.statementActorPersonUid = Person.personUid\n                            AND CAST(StatementEntity.completionOrProgress AS INTEGER) = 1\n                       ORDER BY StatementEntity.resultScoreScaled DESC\n                       LIMIT 1)\n                    OR (StatementEntity.statementIdHi, StatementEntity.statementIdLo) IN    \n                          (SELECT StatementEntity.statementIdHi, StatementEntity.statementIdLo\n                           FROM StatementEntity\n                          WHERE StatementEntity.statementContentEntryUid = :contentEntryUid\n                            AND StatementEntity.statementActorPersonUid = Person.personUid\n                       ORDER BY StatementEntity.timestamp DESC\n                       LIMIT 1)    \n         WHERE Person.personUid IN\n            (SELECT DISTINCT StatementEntity.statementActorPersonUid\n                    \n        FROM StatementEntity\n                    LEFT JOIN ClazzEnrolment \n                         ON ClazzEnrolment.clazzEnrolmentUid =\n                           COALESCE(\n                            (SELECT ClazzEnrolment.clazzEnrolmentUid \n                               FROM ClazzEnrolment\n                              WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid\n                                AND ClazzEnrolment.clazzEnrolmentActive\n                                AND ClazzEnrolment.clazzEnrolmentClazzUid = StatementEntity.statementClazzUid \n                           ORDER BY ClazzEnrolment.clazzEnrolmentDateLeft DESC   \n                              LIMIT 1), 0)\n              WHERE StatementEntity.statementContentEntryUid = :contentEntryUid\n                /* permission check */\n                AND (    StatementEntity.statementActorPersonUid = :accountPersonUid\n                      OR EXISTS(SELECT CoursePermission.cpUid\n                                  FROM CoursePermission\n                                 WHERE CoursePermission.cpClazzUid = StatementEntity.statementClazzUid\n                                   AND (   CoursePermission.cpToPersonUid = :accountPersonUid \n                                        OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment.clazzEnrolmentRole )\n                                   AND (CoursePermission.cpPermissionsFlag & 128) > 0 \n                                   AND NOT CoursePermission.cpIsDeleted)\n                      OR (\n        EXISTS(SELECT 1\n                 FROM SystemPermission\n                WHERE :accountPersonUid != 0 \n                  AND SystemPermission.spToPersonUid = :accountPersonUid\n                  AND (SystemPermission.spPermissionsFlag &\n    \n                          128\n                          \n        ) > 0\n                  AND NOT SystemPermission.spIsDeleted)\n    ))\n    )      \n            AND (   :searchText = '%' \n                 OR Person.firstNames || ' ' || Person.lastName LIKE :searchText) \n                          \n    ")
  public actual abstract suspend fun findPersonsWithAttemptsStatements(
    contentEntryUid: Long,
    accountPersonUid: Long,
    searchText: String?,
  ): List<StatementEntity>

  @Query(`value` =
      "\n     SELECT Person.*, PersonPicture.*,\n            (SELECT COUNT(*)\n               FROM (SELECT DISTINCT StatementEntity.contextRegistrationHi, StatementEntity.contextRegistrationLo\n                       FROM StatementEntity\n                      WHERE StatementEntity.statementContentEntryUid = :contentEntryUid\n                        AND StatementEntity.statementActorPersonUid = Person.personUid\n                    ) AS DistinctRegistrations) AS numAttempts,\n            (SELECT EXISTS(\n                    SELECT 1\n                      FROM StatementEntity\n                     WHERE StatementEntity.statementContentEntryUid = :contentEntryUid\n                       AND StatementEntity.statementActorPersonUid = Person.personUid\n                       AND CAST(StatementEntity.completionOrProgress AS INTEGER) = 1\n                       AND CAST(StatementEntity.resultCompletion AS INTEGER) = 1)) AS isCompleted,\n            (SELECT CASE\n                    WHEN EXISTS(\n                         SELECT 1\n                           FROM StatementEntity\n                          WHERE StatementEntity.statementContentEntryUid = :contentEntryUid\n                            AND StatementEntity.statementActorPersonUid = Person.personUid\n                            AND CAST(StatementEntity.completionOrProgress AS INTEGER) = 1\n                            AND CAST(StatementEntity.resultSuccess AS INTEGER) = 1) THEN 1\n                    WHEN EXISTS(\n                         SELECT 1\n                           FROM StatementEntity\n                          WHERE StatementEntity.statementContentEntryUid = :contentEntryUid\n                            AND StatementEntity.statementActorPersonUid = Person.personUid\n                            AND CAST(StatementEntity.completionOrProgress AS INTEGER) = 1\n                            AND StatementEntity.resultSuccess IS NOT NULL\n                            AND CAST(StatementEntity.resultSuccess AS INTEGER) = 1) THEN 0\n                    ELSE NULL\n                    END) AS isSuccessful,\n            (\n             SELECT MAX(StatementEntity.extensionProgress)\n               FROM StatementEntity\n              WHERE StatementEntity.statementContentEntryUid = :contentEntryUid\n                AND StatementEntity.statementActorPersonUid = Person.personUid\n                AND CAST(StatementEntity.completionOrProgress AS INTEGER) = 1\n    ) AS maxProgress,\n            (\n             SELECT MAX(StatementEntity.resultScoreScaled)\n               FROM StatementEntity\n              WHERE StatementEntity.statementContentEntryUid = :contentEntryUid\n                AND StatementEntity.statementActorPersonUid = Person.personUid\n                AND CAST(StatementEntity.completionOrProgress AS INTEGER) = 1\n    ) AS maxScore,\n            \n            (\n        SELECT MAX(StatementEntity.timestamp)\n               FROM StatementEntity\n              WHERE StatementEntity.statementContentEntryUid = :contentEntryUid\n                AND StatementEntity.statementActorPersonUid = Person.personUid\n    ) AS mostRecentAttemptTime    \n       FROM Person\n            LEFT JOIN PersonPicture\n                 ON PersonPicture.personPictureUid = Person.personUid\n      WHERE Person.personUid IN\n            (SELECT DISTINCT StatementEntity.statementActorPersonUid\n                    \n        FROM StatementEntity\n                    LEFT JOIN ClazzEnrolment \n                         ON ClazzEnrolment.clazzEnrolmentUid =\n                           COALESCE(\n                            (SELECT ClazzEnrolment.clazzEnrolmentUid \n                               FROM ClazzEnrolment\n                              WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid\n                                AND ClazzEnrolment.clazzEnrolmentActive\n                                AND ClazzEnrolment.clazzEnrolmentClazzUid = StatementEntity.statementClazzUid \n                           ORDER BY ClazzEnrolment.clazzEnrolmentDateLeft DESC   \n                              LIMIT 1), 0)\n              WHERE StatementEntity.statementContentEntryUid = :contentEntryUid\n                /* permission check */\n                AND (    StatementEntity.statementActorPersonUid = :accountPersonUid\n                      OR EXISTS(SELECT CoursePermission.cpUid\n                                  FROM CoursePermission\n                                 WHERE CoursePermission.cpClazzUid = StatementEntity.statementClazzUid\n                                   AND (   CoursePermission.cpToPersonUid = :accountPersonUid \n                                        OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment.clazzEnrolmentRole )\n                                   AND (CoursePermission.cpPermissionsFlag & 128) > 0 \n                                   AND NOT CoursePermission.cpIsDeleted)\n                      OR (\n        EXISTS(SELECT 1\n                 FROM SystemPermission\n                WHERE :accountPersonUid != 0 \n                  AND SystemPermission.spToPersonUid = :accountPersonUid\n                  AND (SystemPermission.spPermissionsFlag &\n    \n                          128\n                          \n        ) > 0\n                  AND NOT SystemPermission.spIsDeleted)\n    ))\n    )      \n            AND (   :searchText = '%' \n                 OR Person.firstNames || ' ' || Person.lastName LIKE :searchText)\n     ORDER BY \n    CASE \n        WHEN :sortOrder = 5 THEN (\n             SELECT MAX(StatementEntity.resultScoreScaled)\n               FROM StatementEntity\n              WHERE StatementEntity.statementContentEntryUid = :contentEntryUid\n                AND StatementEntity.statementActorPersonUid = Person.personUid\n                AND CAST(StatementEntity.completionOrProgress AS INTEGER) = 1\n    )\n        WHEN :sortOrder = 7 THEN (\n             SELECT MAX(StatementEntity.extensionProgress)\n               FROM StatementEntity\n              WHERE StatementEntity.statementContentEntryUid = :contentEntryUid\n                AND StatementEntity.statementActorPersonUid = Person.personUid\n                AND CAST(StatementEntity.completionOrProgress AS INTEGER) = 1\n    )\n        WHEN :sortOrder = 9 THEN (\n        SELECT MAX(StatementEntity.timestamp)\n               FROM StatementEntity\n              WHERE StatementEntity.statementContentEntryUid = :contentEntryUid\n                AND StatementEntity.statementActorPersonUid = Person.personUid\n    )\n        ELSE 0\n    END ASC,\n    CASE \n        WHEN :sortOrder = 1 THEN Person.firstNames\n        WHEN :sortOrder = 3 THEN Person.lastName\n        ELSE ''\n    END ASC,    \n    CASE \n        WHEN :sortOrder = 6 THEN (\n             SELECT MAX(StatementEntity.resultScoreScaled)\n               FROM StatementEntity\n              WHERE StatementEntity.statementContentEntryUid = :contentEntryUid\n                AND StatementEntity.statementActorPersonUid = Person.personUid\n                AND CAST(StatementEntity.completionOrProgress AS INTEGER) = 1\n    )\n        WHEN :sortOrder = 8 THEN (\n             SELECT MAX(StatementEntity.extensionProgress)\n               FROM StatementEntity\n              WHERE StatementEntity.statementContentEntryUid = :contentEntryUid\n                AND StatementEntity.statementActorPersonUid = Person.personUid\n                AND CAST(StatementEntity.completionOrProgress AS INTEGER) = 1\n    )\n        WHEN :sortOrder = 10 THEN (\n        SELECT MAX(StatementEntity.timestamp)\n               FROM StatementEntity\n              WHERE StatementEntity.statementContentEntryUid = :contentEntryUid\n                AND StatementEntity.statementActorPersonUid = Person.personUid\n    )\n        ELSE 0\n    END DESC,\n    CASE\n        WHEN :sortOrder = 2 THEN Person.firstNames\n        WHEN :sortOrder = 4 THEN Person.lastName\n    END DESC    \n")
  public actual abstract fun findPersonsWithAttempts(
    contentEntryUid: Long,
    accountPersonUid: Long,
    searchText: String?,
    sortOrder: Int,
  ): PagingSource<Int, PersonAndPictureAndNumAttempts>

  @Query(`value` =
      "\n        WITH \n             DistinctRegistrationUids(contextRegistrationHi, contextRegistrationLo, statementClazzUid) AS (\n      SELECT DISTINCT StatementEntity.contextRegistrationHi, \n                     StatementEntity.contextRegistrationLo,\n                     StatementEntity.statementClazzUid\n                 FROM StatementEntity\n                WHERE StatementEntity.statementContentEntryUid = :contentEntryUid\n                  AND StatementEntity.statementActorPersonUid = :personUid)\n    \n        SELECT StatementEntity.*\n          FROM DistinctRegistrationUids\n               JOIN StatementEntity \n                    ON (StatementEntity.statementIdHi, StatementEntity.statementIdLo) IN \n                        -- Statement for time started\n                        (SELECT StatementEntity.statementIdHi, StatementEntity.statementIdLo \n                           FROM StatementEntity\n                          WHERE StatementEntity.contextRegistrationHi = DistinctRegistrationUids.contextRegistrationHi\n                            AND StatementEntity.contextRegistrationLo = DistinctRegistrationUids.contextRegistrationLo\n                       ORDER BY StatementEntity.timestamp ASC\n                          LIMIT 1)\n                          \n                       --statement for max progress   \n                    OR (StatementEntity.statementIdHi, StatementEntity.statementIdLo) IN \n                       (SELECT StatementEntity.statementIdHi, StatementEntity.statementIdLo \n                          FROM StatementEntity\n                         WHERE StatementEntity.contextRegistrationHi = DistinctRegistrationUids.contextRegistrationHi\n                           AND StatementEntity.contextRegistrationLo = DistinctRegistrationUids.contextRegistrationLo\n                           AND CAST(StatementEntity.completionOrProgress AS INTEGER) = 1\n                      ORDER BY StatementEntity.extensionProgress DESC\n                         LIMIT 1) \n                         \n                       --statement for max score  \n                    OR (StatementEntity.statementIdHi, StatementEntity.statementIdLo) IN \n                       (SELECT StatementEntity.statementIdHi, StatementEntity.statementIdLo \n                          FROM StatementEntity\n                         WHERE StatementEntity.contextRegistrationHi = DistinctRegistrationUids.contextRegistrationHi\n                           AND StatementEntity.contextRegistrationLo = DistinctRegistrationUids.contextRegistrationLo\n                           AND CAST(StatementEntity.completionOrProgress AS INTEGER) = 1\n                      ORDER BY StatementEntity.resultScoreScaled DESC\n                         LIMIT 1)\n                         \n                        --statement with completion  \n                    OR (StatementEntity.statementIdHi, StatementEntity.statementIdLo) IN \n                       (SELECT StatementEntity.statementIdHi, StatementEntity.statementIdLo \n                          FROM StatementEntity\n                         WHERE StatementEntity.contextRegistrationHi = DistinctRegistrationUids.contextRegistrationHi\n                           AND StatementEntity.contextRegistrationLo = DistinctRegistrationUids.contextRegistrationLo\n                           AND CAST(StatementEntity.completionOrProgress AS INTEGER) = 1\n                           AND CAST(StatementEntity.resultCompletion AS INTEGER) = 1\n                         LIMIT 1)\n         WHERE (\n            :personUid = :accountPersonUid \n                OR EXISTS(\n                    SELECT CoursePermission.cpUid\n                      FROM CoursePermission\n                           LEFT JOIN ClazzEnrolment \n                                ON ClazzEnrolment.clazzEnrolmentUid =\n                                  COALESCE(\n                                   (SELECT ClazzEnrolment.clazzEnrolmentUid \n                                      FROM ClazzEnrolment\n                                     WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid\n                                       AND ClazzEnrolment.clazzEnrolmentActive\n                                       AND ClazzEnrolment.clazzEnrolmentClazzUid = DistinctRegistrationUids.statementClazzUid \n                                  ORDER BY ClazzEnrolment.clazzEnrolmentDateLeft DESC   \n                                     LIMIT 1), 0)\n                     WHERE CoursePermission.cpClazzUid = DistinctRegistrationUids.statementClazzUid\n                       AND (   CoursePermission.cpToPersonUid = :accountPersonUid \n                            OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment.clazzEnrolmentRole )\n                       AND (CoursePermission.cpPermissionsFlag & 128) > 0 \n                       AND NOT CoursePermission.cpIsDeleted)\n                OR (\n        EXISTS(SELECT 1\n                 FROM SystemPermission\n                WHERE :accountPersonUid != 0 \n                  AND SystemPermission.spToPersonUid = :accountPersonUid\n                  AND (SystemPermission.spPermissionsFlag &\n    \n                    128\n                    \n        ) > 0\n                  AND NOT SystemPermission.spIsDeleted)\n    )\n    )\n    ")
  public actual abstract suspend fun findSessionsByPersonAndContentStatements(
    contentEntryUid: Long,
    personUid: Long,
    accountPersonUid: Long,
  ): List<StatementEntity>

  @Query(`value` =
      "\n WITH \n             DistinctRegistrationUids(contextRegistrationHi, contextRegistrationLo, statementClazzUid) AS (\n      SELECT DISTINCT StatementEntity.contextRegistrationHi, \n                     StatementEntity.contextRegistrationLo,\n                     StatementEntity.statementClazzUid\n                 FROM StatementEntity\n                WHERE StatementEntity.statementContentEntryUid = :contentEntryUid\n                  AND StatementEntity.statementActorPersonUid = :personUid)\n    ,\n      SessionsByPerson(contextRegistrationHi, contextRegistrationLo, timeStarted, maxProgress, maxScore, isCompleted, isSuccessful, resultDuration) AS (\n       SELECT DistinctRegistrationUids.contextRegistrationHi AS contextRegistrationHi,\n              DistinctRegistrationUids.contextRegistrationLo AS contextRegistrationLo,\n              (SELECT MIN(StatementEntity.timestamp)\n                 FROM StatementEntity\n                WHERE StatementEntity.contextRegistrationHi = DistinctRegistrationUids.contextRegistrationHi\n                  AND StatementEntity.contextRegistrationLo = DistinctRegistrationUids.contextRegistrationLo\n                  AND StatementEntity.statementActorPersonUid = :personUid\n                  AND StatementEntity.statementContentEntryUid = :contentEntryUid\n              ) AS timeStarted,\n              \n              (SELECT MAX(StatementEntity.extensionProgress)\n                 FROM StatementEntity\n                WHERE StatementEntity.contextRegistrationHi = DistinctRegistrationUids.contextRegistrationHi\n                  AND StatementEntity.contextRegistrationLo = DistinctRegistrationUids.contextRegistrationLo\n                  AND StatementEntity.statementActorPersonUid = :personUid\n                  AND StatementEntity.statementContentEntryUid = :contentEntryUid\n                  AND CAST(StatementEntity.completionOrProgress AS INTEGER) = 1\n               ) AS maxProgress,\n               \n              (SELECT MAX(StatementEntity.resultScoreScaled)\n                 FROM StatementEntity\n                WHERE StatementEntity.contextRegistrationHi = DistinctRegistrationUids.contextRegistrationHi\n                  AND StatementEntity.contextRegistrationLo = DistinctRegistrationUids.contextRegistrationLo\n                  AND StatementEntity.statementActorPersonUid = :personUid\n                  AND StatementEntity.statementContentEntryUid = :contentEntryUid\n                  AND CAST(StatementEntity.completionOrProgress AS INTEGER) = 1\n               ) AS maxScore,\n               \n              (SELECT EXISTS(\n                      SELECT 1 \n                        FROM StatementEntity\n                       WHERE StatementEntity.contextRegistrationHi = DistinctRegistrationUids.contextRegistrationHi\n                         AND StatementEntity.contextRegistrationLo = DistinctRegistrationUids.contextRegistrationLo\n                         AND StatementEntity.statementActorPersonUid = :personUid\n                         AND StatementEntity.statementContentEntryUid = :contentEntryUid\n                         AND CAST(StatementEntity.completionOrProgress AS INTEGER) = 1\n                         AND CAST(StatementEntity.resultCompletion AS INTEGER) = 1\n              )) AS isCompleted,\n              (SELECT CASE \n                      WHEN EXISTS(\n                           SELECT 1 \n                             FROM StatementEntity\n                            WHERE StatementEntity.contextRegistrationHi = DistinctRegistrationUids.contextRegistrationHi\n                              AND StatementEntity.contextRegistrationLo = DistinctRegistrationUids.contextRegistrationLo\n                              AND StatementEntity.statementActorPersonUid = :personUid\n                              AND StatementEntity.statementContentEntryUid = :contentEntryUid\n                              AND CAST(StatementEntity.completionOrProgress AS INTEGER) = 1\n                              AND CAST(StatementEntity.resultSuccess AS INTEGER) = 1) THEN 1\n                      WHEN EXISTS(\n                           SELECT 1 \n                             FROM StatementEntity\n                            WHERE StatementEntity.contextRegistrationHi = DistinctRegistrationUids.contextRegistrationHi\n                              AND StatementEntity.contextRegistrationLo = DistinctRegistrationUids.contextRegistrationLo\n                              AND StatementEntity.statementActorPersonUid = :personUid\n                              AND StatementEntity.statementContentEntryUid = :contentEntryUid\n                              AND CAST(StatementEntity.completionOrProgress AS INTEGER) = 1\n                              AND StatementEntity.resultSuccess IS NOT NULL\n                              AND CAST(StatementEntity.resultSuccess AS INTEGER) = 0) THEN 0\n                      ELSE NULL\n                      END) AS isSuccessful,\n                      (SELECT MAX(StatementEntity.resultDuration)\n                         FROM StatementEntity\n                        WHERE StatementEntity.contextRegistrationHi = DistinctRegistrationUids.contextRegistrationHi\n                          AND StatementEntity.contextRegistrationLo = DistinctRegistrationUids.contextRegistrationLo\n                          AND StatementEntity.statementActorPersonUid = :personUid\n                          AND StatementEntity.statementContentEntryUid = :contentEntryUid) AS resultDuration\n         FROM DistinctRegistrationUids\n         WHERE (\n            :personUid = :accountPersonUid \n                OR EXISTS(\n                    SELECT CoursePermission.cpUid\n                      FROM CoursePermission\n                           LEFT JOIN ClazzEnrolment \n                                ON ClazzEnrolment.clazzEnrolmentUid =\n                                  COALESCE(\n                                   (SELECT ClazzEnrolment.clazzEnrolmentUid \n                                      FROM ClazzEnrolment\n                                     WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid\n                                       AND ClazzEnrolment.clazzEnrolmentActive\n                                       AND ClazzEnrolment.clazzEnrolmentClazzUid = DistinctRegistrationUids.statementClazzUid \n                                  ORDER BY ClazzEnrolment.clazzEnrolmentDateLeft DESC   \n                                     LIMIT 1), 0)\n                     WHERE CoursePermission.cpClazzUid = DistinctRegistrationUids.statementClazzUid\n                       AND (   CoursePermission.cpToPersonUid = :accountPersonUid \n                            OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment.clazzEnrolmentRole )\n                       AND (CoursePermission.cpPermissionsFlag & 128) > 0 \n                       AND NOT CoursePermission.cpIsDeleted)\n                OR (\n        EXISTS(SELECT 1\n                 FROM SystemPermission\n                WHERE :accountPersonUid != 0 \n                  AND SystemPermission.spToPersonUid = :accountPersonUid\n                  AND (SystemPermission.spPermissionsFlag &\n    \n                    128\n                    \n        ) > 0\n                  AND NOT SystemPermission.spIsDeleted)\n    )\n    )) \n      SELECT SessionsByPerson.*\n        FROM SessionsByPerson\n    ORDER BY  \n        CASE :sortOrder\n            WHEN 1 THEN timeStarted\n            ELSE NULL\n        END DESC,\n        CASE :sortOrder\n            WHEN 2 THEN timeStarted\n            ELSE NULL\n        END ASC,\n        CASE :sortOrder\n            WHEN 4 THEN maxScore\n            ELSE NULL\n        END DESC,\n        CASE :sortOrder\n            WHEN 3 THEN maxScore\n            ELSE NULL\n        END ASC,\n        CASE :sortOrder\n            WHEN 6 THEN maxProgress\n            ELSE NULL\n        END DESC,\n        CASE :sortOrder\n            WHEN 5 THEN maxProgress\n            ELSE NULL\n        END ASC\n   ")
  public actual abstract fun findSessionsByPersonAndContent(
    contentEntryUid: Long,
    personUid: Long,
    accountPersonUid: Long,
    sortOrder: Int,
  ): PagingSource<Int, SessionTimeAndProgressInfo>

  @Query(`value` =
      "\n        SELECT ActivityLangMapEntry.*\n          FROM ActivityLangMapEntry\n         WHERE ActivityLangMapEntry.almeActivityUid IN (\n               SELECT DISTINCT StatementEntity.statementObjectUid1\n                 FROM StatementEntity\n                WHERE StatementEntity.contextRegistrationHi = :registrationHi\n                  AND StatementEntity.contextRegistrationLo = :registrationLo)\n    ")
  public actual abstract suspend
      fun findActivityEntryLangMapsForStatementsBySession(registrationHi: Long,
      registrationLo: Long): List<ActivityLangMapEntry>

  @Query(`value` =
      "\n    SELECT StatementEntity.*, VerbEntity.*, VerbLangMapEntry.*, ActivityEntity.*,\n           ActivityLangMapEntry.*,\n           ActivityLangMapDescription.almeValue AS statementActivityDescription\n    FROM StatementEntity\n    LEFT JOIN VerbEntity\n        ON StatementEntity.statementVerbUid = VerbEntity.verbUid\n    LEFT JOIN VerbLangMapEntry \n        ON (VerbLangMapEntry.vlmeVerbUid, VerbLangMapEntry.vlmeLangHash) = \n            (SELECT VerbLangMapEntry.vlmeVerbUid, VerbLangMapEntry.vlmeLangHash\n            FROM VerbLangMapEntry\n            WHERE VerbLangMapEntry.vlmeVerbUid = VerbEntity.verbUid\n            ORDER BY VerbLangMapEntry.vlmeLastModified DESC\n            LIMIT 1)\n    LEFT JOIN ActivityEntity\n              ON ActivityEntity.actUid = StatementEntity.statementObjectUid1\n    LEFT JOIN ActivityLangMapEntry\n              ON (ActivityLangMapEntry.almeActivityUid, ActivityLangMapEntry.almeHash) = \n                 (SELECT ActivityLangMapEntry.almeActivityUid, ActivityLangMapEntry.almeHash\n                    FROM ActivityLangMapEntry\n                   WHERE ActivityLangMapEntry.almeActivityUid = StatementEntity.statementObjectUid1\n                     AND ActivityLangMapEntry.almePropName = 'name'\n                   LIMIT 1)\n    LEFT JOIN ActivityLangMapEntry ActivityLangMapDescription\n              ON (ActivityLangMapDescription.almeActivityUid, ActivityLangMapDescription.almeHash) = \n                 (SELECT ActivityLangMapDescription.almeActivityUid, ActivityLangMapDescription.almeHash\n                    FROM ActivityLangMapEntry ActivityLangMapDescription\n                   WHERE ActivityLangMapDescription.almeActivityUid = StatementEntity.statementObjectUid1\n                     AND ActivityLangMapDescription.almePropName = 'description'\n                   LIMIT 1)\n    LEFT JOIN ClazzEnrolment \n        ON ClazzEnrolment.clazzEnrolmentUid =\n            COALESCE(\n                (SELECT ClazzEnrolment.clazzEnrolmentUid \n                FROM ClazzEnrolment\n                WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid\n                    AND ClazzEnrolment.clazzEnrolmentActive\n                    AND ClazzEnrolment.clazzEnrolmentClazzUid = StatementEntity.statementClazzUid \n                ORDER BY ClazzEnrolment.clazzEnrolmentDateLeft DESC   \n                LIMIT 1), 0)\n    WHERE StatementEntity.contextRegistrationHi = :registrationHi\n    AND StatementEntity.contextRegistrationLo = :registrationLo  \n    AND StatementEntity.statementActorPersonUid = :selectedPersonUid\n    AND StatementEntity.statementContentEntryUid = :contentEntryUid\n    AND (:searchText = '%' OR VerbEntity.verbUrlId LIKE :searchText)\n    AND StatementEntity.statementVerbUid NOT IN (:deSelectedVerbUids)\n    /* Permission check */\n    AND (\n        :accountPersonUid = :selectedPersonUid \n        OR EXISTS(\n            SELECT CoursePermission.cpUid\n            FROM CoursePermission\n            WHERE CoursePermission.cpClazzUid = StatementEntity.statementClazzUid\n            AND (\n                CoursePermission.cpToPersonUid = :accountPersonUid \n                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment.clazzEnrolmentRole\n            )\n            AND (CoursePermission.cpPermissionsFlag & 128) > 0 \n            AND NOT CoursePermission.cpIsDeleted\n        )\n        OR (\n            \n        EXISTS(SELECT 1\n                 FROM SystemPermission\n                WHERE :accountPersonUid != 0 \n                  AND SystemPermission.spToPersonUid = :accountPersonUid\n                  AND (SystemPermission.spPermissionsFlag &\n    \n            128\n            \n        ) > 0\n                  AND NOT SystemPermission.spIsDeleted)\n    \n        )\n    )\n    ORDER BY \n    CASE :sortOrder\n        WHEN 1 THEN StatementEntity.timestamp\n        ELSE NULL\n    END DESC,\n    CASE :sortOrder\n        WHEN 2 THEN StatementEntity.timestamp\n        ELSE NULL\n    END ASC,\n    CASE :sortOrder\n        WHEN 3 THEN StatementEntity.resultScoreRaw\n        ELSE NULL\n    END DESC,\n    CASE :sortOrder\n        WHEN 4 THEN StatementEntity.resultScoreRaw\n        ELSE NULL\n    END ASC\n")
  public actual abstract fun findStatementsBySession(
    registrationHi: Long,
    registrationLo: Long,
    accountPersonUid: Long,
    selectedPersonUid: Long,
    contentEntryUid: Long,
    searchText: String,
    sortOrder: Int,
    deSelectedVerbUids: List<Long>,
  ): PagingSource<Int, StatementEntityAndVerb>

  @Query(`value` =
      "\n    WITH DistinctVerbUrls(statementVerbUid) AS (\n        SELECT DISTINCT StatementEntity.statementVerbUid\n        FROM StatementEntity\n        WHERE StatementEntity.contextRegistrationHi = :registrationHi\n            AND StatementEntity.contextRegistrationLo = :registrationLo\n            AND StatementEntity.statementActorPersonUid = :selectedPersonUid\n            AND StatementEntity.statementContentEntryUid = :contentEntryUid\n    )\n    \n    SELECT VerbEntity.*,\n           VerbLangMapEntry.*\n    FROM DistinctVerbUrls\n         JOIN VerbEntity \n              ON VerbEntity.verbUid = DistinctVerbUrls.statementVerbUid\n         LEFT JOIN VerbLangMapEntry\n                  ON (VerbLangMapEntry.vlmeVerbUid, VerbLangMapEntry.vlmeLangHash) = \n                     (SELECT VerbLangMapEntry.vlmeVerbUid, VerbLangMapEntry.vlmeLangHash\n                      FROM VerbLangMapEntry\n                      WHERE VerbLangMapEntry.vlmeVerbUid = DistinctVerbUrls.statementVerbUid\n                      ORDER BY VerbLangMapEntry.vlmeLastModified DESC\n                      LIMIT 1)\n")
  public actual abstract fun getUniqueVerbsForSession(
    registrationHi: Long,
    registrationLo: Long,
    selectedPersonUid: Long,
    contentEntryUid: Long,
  ): Flow<List<VerbEntityAndName>>

  @Query(`value` =
      "\n        SELECT * \n          FROM (SELECT StatementEntity.*\n                 \n        FROM StatementEntity\n                    LEFT JOIN ClazzEnrolment \n                         ON ClazzEnrolment.clazzEnrolmentUid =\n                           COALESCE(\n                            (SELECT ClazzEnrolment.clazzEnrolmentUid \n                               FROM ClazzEnrolment\n                              WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid\n                                AND ClazzEnrolment.clazzEnrolmentActive\n                                AND ClazzEnrolment.clazzEnrolmentClazzUid = StatementEntity.statementClazzUid \n                           ORDER BY ClazzEnrolment.clazzEnrolmentDateLeft DESC   \n                              LIMIT 1), 0)\n              WHERE StatementEntity.statementContentEntryUid = :contentEntryUid\n                /* permission check */\n                AND (    StatementEntity.statementActorPersonUid = :accountPersonUid\n                      OR EXISTS(SELECT CoursePermission.cpUid\n                                  FROM CoursePermission\n                                 WHERE CoursePermission.cpClazzUid = StatementEntity.statementClazzUid\n                                   AND (   CoursePermission.cpToPersonUid = :accountPersonUid \n                                        OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment.clazzEnrolmentRole )\n                                   AND (CoursePermission.cpPermissionsFlag & 128) > 0 \n                                   AND NOT CoursePermission.cpIsDeleted)\n                      OR (\n        EXISTS(SELECT 1\n                 FROM SystemPermission\n                WHERE :accountPersonUid != 0 \n                  AND SystemPermission.spToPersonUid = :accountPersonUid\n                  AND (SystemPermission.spPermissionsFlag &\n    \n                          128\n                          \n        ) > 0\n                  AND NOT SystemPermission.spIsDeleted)\n    ))\n    \n                   AND (     StatementEntity.extensionProgress IS NOT NULL\n                         AND CAST(StatementEntity.completionOrProgress AS INTEGER) = 1)\n                 LIMIT 1) AS ProgressStatements\n        UNION\n        SELECT * \n          FROM (SELECT StatementEntity.*\n                 \n        FROM StatementEntity\n                    LEFT JOIN ClazzEnrolment \n                         ON ClazzEnrolment.clazzEnrolmentUid =\n                           COALESCE(\n                            (SELECT ClazzEnrolment.clazzEnrolmentUid \n                               FROM ClazzEnrolment\n                              WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid\n                                AND ClazzEnrolment.clazzEnrolmentActive\n                                AND ClazzEnrolment.clazzEnrolmentClazzUid = StatementEntity.statementClazzUid \n                           ORDER BY ClazzEnrolment.clazzEnrolmentDateLeft DESC   \n                              LIMIT 1), 0)\n              WHERE StatementEntity.statementContentEntryUid = :contentEntryUid\n                /* permission check */\n                AND (    StatementEntity.statementActorPersonUid = :accountPersonUid\n                      OR EXISTS(SELECT CoursePermission.cpUid\n                                  FROM CoursePermission\n                                 WHERE CoursePermission.cpClazzUid = StatementEntity.statementClazzUid\n                                   AND (   CoursePermission.cpToPersonUid = :accountPersonUid \n                                        OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment.clazzEnrolmentRole )\n                                   AND (CoursePermission.cpPermissionsFlag & 128) > 0 \n                                   AND NOT CoursePermission.cpIsDeleted)\n                      OR (\n        EXISTS(SELECT 1\n                 FROM SystemPermission\n                WHERE :accountPersonUid != 0 \n                  AND SystemPermission.spToPersonUid = :accountPersonUid\n                  AND (SystemPermission.spPermissionsFlag &\n    \n                          128\n                          \n        ) > 0\n                  AND NOT SystemPermission.spIsDeleted)\n    ))\n    \n                   AND (     StatementEntity.resultScoreScaled IS NOT NULL\n                         AND CAST(StatementEntity.completionOrProgress AS INTEGER) = 1)\n                 LIMIT 1) AS ScoreStatements\n    ")
  public actual abstract suspend fun scoreOrProgressDataExistsForContent(contentEntryUid: Long,
      accountPersonUid: Long): List<StatementEntity>

  @Query(`value` =
      "\n        SELECT StatementEntity.*, ActivityEntity.*\n          FROM StatementEntity\n               LEFT JOIN ActivityEntity\n                         ON ActivityEntity.actUid = StatementEntity.statementObjectUid1\n         WHERE StatementEntity.statementIdHi = :statementIdHi\n           AND StatementEntity.statementIdLo = :statementIdLo\n    ")
  public actual abstract suspend fun findByUidWithActivityAsync(statementIdHi: Long,
      statementIdLo: Long): StatementAndActivity?
}
