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.xapi.StatementEntityAndRelated
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.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?
}
