package com.ustadmobile.core.db.dao

import androidx.paging.DataSource
import androidx.room.Dao
import androidx.room.Query
import com.ustadmobile.lib.db.entities.ClazzAssignmentContentJoin
import com.ustadmobile.lib.db.entities.ContentEntryWithParentChildJoinAndStatusAndMostRecentContainer
import kotlin.Int
import kotlin.Long
import kotlin.Unit
import kotlin.collections.List

@Dao
public actual abstract class ClazzAssignmentContentJoinDao : BaseDao<ClazzAssignmentContentJoin> {
  @Query(`value` =
      "\n     REPLACE INTO ClazzAssignmentContentJoinReplicate(cacjPk, cacjDestination)\n      SELECT DISTINCT ClazzAssignmentContentJoin.cacjUid AS cacjUid,\n             :newNodeId AS cacjDestination\n        FROM UserSession\n               JOIN PersonGroupMember \n                    ON UserSession.usPersonUid = PersonGroupMember.groupMemberPersonUid\n               \n               JOIN ScopedGrant\n                    ON ScopedGrant.sgGroupUid = PersonGroupMember.groupMemberGroupUid\n                        AND (ScopedGrant.sgPermissions & \n        \n                    8388608 \n                    \n                       ) > 0\n               JOIN Clazz \n                    ON \n            ((ScopedGrant.sgTableId = -2\n                                AND ScopedGrant.sgEntityUid = -2)\n                            OR (ScopedGrant.sgTableId = 6\n                                AND ScopedGrant.sgEntityUid = Clazz.clazzUid)\n                            OR (ScopedGrant.sgTableId = 164\n                                AND ScopedGrant.sgEntityUid = Clazz.clazzSchoolUid))\n        \n         \n               JOIN ClazzAssignment\n                    ON ClazzAssignment.caClazzUid = Clazz.clazzUid\n               JOIN ClazzAssignmentContentJoin\n                    ON ClazzAssignment.caUid = ClazzAssignmentContentJoin.cacjAssignmentUid     \n       WHERE UserSession.usClientNodeId = :newNodeId\n         AND UserSession.usStatus = 1             \n         AND ClazzAssignmentContentJoin.cacjLct != COALESCE(\n             (SELECT cacjVersionId\n                FROM ClazzAssignmentContentJoinReplicate\n               WHERE cacjPk = ClazzAssignmentContentJoin.cacjUid\n                 AND cacjDestination = :newNodeId), 0) \n      /*psql ON CONFLICT(cacjPk, cacjDestination) DO UPDATE\n             SET cacjPending = true\n      */       \n    ")
  public actual abstract suspend fun replicateOnNewNode(newNodeId: Long): Unit

  @Query(`value` =
      "\n REPLACE INTO ClazzAssignmentContentJoinReplicate(cacjPk, cacjDestination)\n  SELECT DISTINCT ClazzAssignmentContentJoin.cacjUid AS cacjUid,\n         UserSession.usClientNodeId AS cacjDestination\n    FROM ChangeLog\n         JOIN ClazzAssignmentContentJoin\n             ON ChangeLog.chTableId = 521\n                AND ChangeLog.chEntityPk = ClazzAssignmentContentJoin.cacjUid\n         JOIN ClazzAssignment\n              ON ClazzAssignment.caUid = ClazzAssignmentContentJoin.cacjAssignmentUid\n         JOIN Clazz \n              ON Clazz.clazzUid = ClazzAssignment.caClazzUid \n         \n            JOIN ScopedGrant\n                 ON \n            ((ScopedGrant.sgTableId = -2\n                                AND ScopedGrant.sgEntityUid = -2)\n                            OR (ScopedGrant.sgTableId = 6\n                                AND ScopedGrant.sgEntityUid = Clazz.clazzUid)\n                            OR (ScopedGrant.sgTableId = 164\n                                AND ScopedGrant.sgEntityUid = Clazz.clazzSchoolUid))\n        \n                    AND (ScopedGrant.sgPermissions & \n        \n              8388608\n              \n              \n                                                       ) > 0\n             JOIN PersonGroupMember AS PrsGrpMbr\n                   ON ScopedGrant.sgGroupUid = PrsGrpMbr.groupMemberGroupUid\n                                               \n              JOIN UserSession\n                   ON UserSession.usPersonUid = PrsGrpMbr.groupMemberPersonUid\n                      AND UserSession.usStatus = 1\n        \n   WHERE UserSession.usClientNodeId != (\n         SELECT nodeClientId \n           FROM SyncNode\n          LIMIT 1)\n     AND ClazzAssignmentContentJoin.cacjLct != COALESCE(\n         (SELECT cacjVersionId\n            FROM ClazzAssignmentContentJoinReplicate\n           WHERE cacjPk = ClazzAssignmentContentJoin.cacjUid\n             AND cacjDestination = UserSession.usClientNodeId), 0)\n /*psql ON CONFLICT(cacjPk, cacjDestination) DO UPDATE\n     SET cacjPending = true\n  */               \n ")
  public actual abstract suspend fun replicateOnChange(): Unit

  @Query(`value` =
      "\n                    SELECT ContentEntry.*, ContentEntryParentChildJoin.*, \n                            Container.*, \n                             COALESCE(ClazzAssignmentRollUp.cacheStudentScore,0) AS resultScore,\n                                           \n                             COALESCE(ClazzAssignmentRollUp.cacheMaxScore,0) AS resultMax,\n                                                         \n                             COALESCE(ClazzAssignmentRollUp.cacheProgress,0) AS progress,                            \n                            \n                             COALESCE(ClazzAssignmentRollUp.cacheContentComplete,'FALSE') AS contentComplete,\n                                 \n                             COALESCE(ClazzAssignmentRollUp.cacheSuccess,0) AS success,\n                             \n                             COALESCE(ClazzAssignmentRollUp.cachePenalty,0) AS penalty,\n                               \n                             COALESCE((CASE WHEN ClazzAssignmentRollUp.cacheContentComplete \n                                            THEN 1 ELSE 0 END),0) AS totalCompletedContent,\n                        \n                             0 as assignmentContentWeight,\n                             1 as totalContent\n                           \n                             \n                      FROM ClazzAssignmentContentJoin\n                            LEFT JOIN ContentEntry \n                            ON ContentEntry.contentEntryUid = cacjContentUid \n                            \n                            LEFT JOIN ContentEntryParentChildJoin \n                            ON ContentEntryParentChildJoin.cepcjChildContentEntryUid = ContentEntry.contentEntryUid \n                           \n                            LEFT JOIN ClazzAssignmentRollUp\n                            ON cacheContentEntryUid = ClazzAssignmentContentJoin.cacjContentUid\n                                AND cachePersonUid = :personUid\n                                AND cacheClazzAssignmentUid = :clazzAssignmentUid\n                                                        \n                            \n                            LEFT JOIN Container \n                            ON Container.containerUid = \n                                (SELECT containerUid \n                                   FROM Container \n                                  WHERE containerContentEntryUid =  ContentEntry.contentEntryUid \n                               ORDER BY cntLastModified DESC LIMIT 1)\n                               \n                    WHERE ClazzAssignmentContentJoin.cacjAssignmentUid = :clazzAssignmentUid\n                      AND ClazzAssignmentContentJoin.cacjActive\n                      AND NOT ContentEntry.ceInactive\n                      AND (ContentEntry.publik OR :personUid != 0)\n                      ORDER BY ContentEntry.title ASC , \n                               ContentEntryParentChildJoin.childIndex, ContentEntry.contentEntryUid\n                               ")
  public actual abstract suspend
      fun findAllContentByClazzAssignmentUidAsync(clazzAssignmentUid: Long, personUid: Long):
      List<ContentEntryWithParentChildJoinAndStatusAndMostRecentContainer>

  @Query(`value` =
      "\n                    SELECT ContentEntry.*, ContentEntryParentChildJoin.*, \n                            Container.*, \n                             COALESCE(ClazzAssignmentRollUp.cacheStudentScore,0) AS resultScore,\n                                           \n                             COALESCE(ClazzAssignmentRollUp.cacheMaxScore,0) AS resultMax,\n                                                         \n                             COALESCE(ClazzAssignmentRollUp.cacheProgress,0) AS progress,                            \n                            \n                             COALESCE(ClazzAssignmentRollUp.cacheContentComplete,'FALSE') AS contentComplete,\n                                 \n                             COALESCE(ClazzAssignmentRollUp.cacheSuccess,0) AS success,\n                             \n                             COALESCE(ClazzAssignmentRollUp.cachePenalty,0) AS penalty,\n                               \n                             COALESCE((CASE WHEN ClazzAssignmentRollUp.cacheContentComplete \n                                            THEN 1 ELSE 0 END),0) AS totalCompletedContent,\n                        \n                             0 as assignmentContentWeight,\n                             1 as totalContent\n                           \n                             \n                      FROM ClazzAssignmentContentJoin\n                            LEFT JOIN ContentEntry \n                            ON ContentEntry.contentEntryUid = cacjContentUid \n                            \n                            LEFT JOIN ContentEntryParentChildJoin \n                            ON ContentEntryParentChildJoin.cepcjChildContentEntryUid = ContentEntry.contentEntryUid \n                           \n                            LEFT JOIN ClazzAssignmentRollUp\n                            ON cacheContentEntryUid = ClazzAssignmentContentJoin.cacjContentUid\n                                AND cachePersonUid = :personUid\n                                AND cacheClazzAssignmentUid = :clazzAssignmentUid\n                                                        \n                            \n                            LEFT JOIN Container \n                            ON Container.containerUid = \n                                (SELECT containerUid \n                                   FROM Container \n                                  WHERE containerContentEntryUid =  ContentEntry.contentEntryUid \n                               ORDER BY cntLastModified DESC LIMIT 1)\n                               \n                    WHERE ClazzAssignmentContentJoin.cacjAssignmentUid = :clazzAssignmentUid\n                      AND ClazzAssignmentContentJoin.cacjActive\n                      AND NOT ContentEntry.ceInactive\n                      AND (ContentEntry.publik OR :personUid != 0)\n                      ORDER BY ContentEntry.title ASC , \n                               ContentEntryParentChildJoin.childIndex, ContentEntry.contentEntryUid\n                               ")
  public actual abstract fun findAllContentByClazzAssignmentUidDF(clazzAssignmentUid: Long,
      personUid: Long):
      DataSource.Factory<Int, ContentEntryWithParentChildJoinAndStatusAndMostRecentContainer>
}
