package com.ustadmobile.core.db.dao

import androidx.paging.DataSource
import androidx.room.Dao
import androidx.room.Insert
import androidx.room.Query
import androidx.room.Update
import com.ustadmobile.lib.db.entities.CourseBlock
import com.ustadmobile.lib.db.entities.CourseBlockWithCompleteEntity
import com.ustadmobile.lib.db.entities.CourseBlockWithEntityDb
import kotlin.Boolean
import kotlin.Int
import kotlin.Long
import kotlin.Unit
import kotlin.collections.List

@Dao
public actual abstract class CourseBlockDao : BaseDao<CourseBlock>, OneToManyJoinDao<CourseBlock> {
  @Query(`value` =
      "\n    REPLACE INTO CourseBlockReplicate(cbPk, cbDestination)\n      SELECT DISTINCT CourseBlock.cbUid AS cbPk,\n             :newNodeId AS cbDestination\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                    2 \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 CourseBlock\n                    ON CourseBlock.cbClazzUid = Clazz.clazzUid                \n       WHERE UserSession.usClientNodeId = :newNodeId\n         AND UserSession.usStatus = 1\n         AND CourseBlock.cbLct != COALESCE(\n             (SELECT cbVersionId\n                FROM CourseBlockReplicate\n               WHERE cbPk = CourseBlock.cbUid\n                 AND cbDestination = :newNodeId), 0) \n      /*psql ON CONFLICT(cbPk, cbDestination) DO UPDATE\n             SET cbPending = true\n      */       \n    ")
  public actual abstract suspend fun replicateOnNewNode(newNodeId: Long): Unit

  @Query(`value` =
      "\n         REPLACE INTO CourseBlockReplicate(cbPk, cbDestination)\n  SELECT DISTINCT CourseBlock.cbUid AS cbPk,\n         UserSession.usClientNodeId AS cbDestination\n    FROM ChangeLog\n         JOIN CourseBlock\n             ON ChangeLog.chTableId = 124\n                AND ChangeLog.chEntityPk = CourseBlock.cbUid\n             JOIN Clazz\n                    ON  Clazz.clazzUid = CourseBlock.cbClazzUid\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              2\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 CourseBlock.cbLct != COALESCE(\n         (SELECT cbVersionId\n            FROM CourseBlockReplicate\n           WHERE cbPk = CourseBlock.cbUid\n             AND cbDestination = UserSession.usClientNodeId), 0)\n /*psql ON CONFLICT(cbPk, cbDestination) DO UPDATE\n     SET cbPending = true\n  */               \n    ")
  public actual abstract suspend fun replicateOnChange(): Unit

  @Query(`value` = "SELECT * FROM CourseBlock WHERE cbUid = :uid")
  public actual abstract suspend fun findByUidAsync(uid: Long): CourseBlock?

  @Update(onConflict = 3)
  public actual abstract suspend fun updateAsync(entity: CourseBlock): Int

  @Insert(onConflict = 1)
  public actual abstract suspend fun replaceListAsync(list: List<CourseBlock>): Unit

  @Query(`value` =
      "\n        SELECT * \n          FROM CourseBlock \n               LEFT JOIN ClazzAssignment as assignment\n               ON assignment.caUid = CourseBlock.cbEntityUid\n               AND CourseBlock.cbType = 103\n               LEFT JOIN CourseDiscussion as courseDiscussion\n               ON CourseDiscussion.courseDiscussionUid = CourseBlock.cbEntityUid\n               AND CourseBlock.cbType = 105\n               LEFT JOIN ContentEntry as entry\n               ON entry.contentEntryUid = CourseBlock.cbEntityUid\n               AND CourseBlock.cbType = 104\n               \n               LEFT JOIN Language\n               ON Language.langUid = entry.primaryLanguageUid\n                AND CourseBlock.cbType = 104\n               \n         WHERE cbClazzUid = :clazzUid\n           AND cbActive\n      ORDER BY cbIndex\n          ")
  public actual abstract suspend fun findAllCourseBlockByClazzUidAsync(clazzUid: Long):
      List<CourseBlockWithEntityDb>

  @Query(`value` =
      "\n         WITH CtePermissionCheck (hasPermission) \n            AS (SELECT EXISTS( \n               SELECT PrsGrpMbr.groupMemberPersonUid\n                  FROM Clazz\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                          549755813888\n                          \n                                                       ) > 0\n             JOIN PersonGroupMember AS PrsGrpMbr\n                   ON ScopedGrant.sgGroupUid = PrsGrpMbr.groupMemberGroupUid\n        \n                 WHERE Clazz.clazzUid = :clazzUid\n                   AND PrsGrpMbr.groupMemberPersonUid = :personUid)), \n                   \n        \n            SubmitterList (submitterId, assignmentUid)\n            AS (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid AS submitterId,\n                       ClazzAssignment.caUid AS assignmentUid\n                  \n                  FROM ClazzEnrolment\n                  \n                       JOIN Person \n                       ON Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid\n                        \n                       JOIN ClazzAssignment\n                       ON ClazzAssignment.caClazzUid = :clazzUid\n\n                       JOIN CourseBlock\n                       ON CourseBlock.cbEntityUid = ClazzAssignment.caUid\n                       AND CourseBlock.cbType = 103\n                       \n                 WHERE ClazzAssignment.caGroupUid = 0\n                   AND clazzEnrolmentClazzUid = :clazzUid\n                   AND clazzEnrolmentActive\n                   AND clazzEnrolmentRole = 1000\n                   AND CourseBlock.cbGracePeriodDate <= ClazzEnrolment.clazzEnrolmentDateLeft\n                   AND ClazzEnrolment.clazzEnrolmentDateJoined <= CourseBlock.cbGracePeriodDate\n              GROUP BY submitterId, assignmentUid\n            UNION                 \n             SELECT DISTINCT CourseGroupMember.cgmGroupNumber AS submitterId,\n                    ClazzAssignment.caUid AS assignmentUid\n               FROM CourseGroupMember\n                    JOIN ClazzAssignment\n                    ON ClazzAssignment.caClazzUid = :clazzUid\n              WHERE CourseGroupMember.cgmSetUid = ClazzAssignment.caGroupUid\n                AND ClazzAssignment.caGroupUid != 0\n                AND CourseGroupMember.cgmGroupNumber != 0\n           GROUP BY submitterId, assignmentUid\n            )\n                   \n                   \n\n        SELECT CourseBlock.*, ClazzAssignment.*, ContentEntry.*, CourseDiscussion.*, ContentEntryParentChildJoin.*, \n               Container.*, CourseAssignmentMark.*, (CourseBlock.cbUid NOT IN (:collapseList)) AS expanded,\n               \n               COALESCE(StatementEntity.resultScoreMax,0) AS resultMax, \n                COALESCE(StatementEntity.resultScoreRaw,0) AS resultScore, \n                COALESCE(StatementEntity.resultScoreScaled,0) AS resultScaled, \n                COALESCE(StatementEntity.extensionProgress,0) AS progress, \n                COALESCE(StatementEntity.resultCompletion,'FALSE') AS contentComplete,\n                COALESCE(StatementEntity.resultSuccess, 0) AS success,\n                \n                COALESCE((CASE WHEN StatementEntity.resultCompletion \n                THEN 1 ELSE 0 END),0) AS totalCompletedContent,\n                \n                0 AS assignmentContentWeight,\n                1 as totalContent, \n                0 as penalty,\n                \n                (SELECT hasPermission FROM CtePermissionCheck) AS hasMetricsPermission,\n                \n             \n                 (SELECT COUNT(*) \n                    FROM SubmitterList \n                   WHERE SubmitterList.assignmentUid = ClazzAssignment.caUid) \n                        AS totalStudents, \n \n               0 AS notSubmittedStudents,\n               \n               (CASE WHEN (SELECT hasPermission \n                          FROM CtePermissionCheck)\n                     THEN (SELECT COUNT(DISTINCT CourseAssignmentSubmission.casSubmitterUid) \n                             FROM CourseAssignmentSubmission\n                                   LEFT JOIN CourseAssignmentMark\n                                   ON CourseAssignmentSubmission.casSubmitterUid = CourseAssignmentMark.camSubmitterUid\n                                   AND CourseAssignmentMark.camAssignmentUid = CourseAssignmentSubmission.casAssignmentUid\n                            WHERE CourseAssignmentMark.camUid IS NULL\n                              AND CourseAssignmentSubmission.casAssignmentUid = ClazzAssignment.caUid\n                              AND CourseAssignmentSubmission.casSubmitterUid IN \n                                                    (SELECT submitterId \n                                                      FROM SubmitterList\n                                                     WHERE SubmitterList.assignmentUid = ClazzAssignment.caUid))  \n                      ELSE 0 END) AS submittedStudents,         \n               \n                (CASE WHEN (SELECT hasPermission \n                           FROM CtePermissionCheck)       \n                   THEN (SELECT COUNT(DISTINCT CourseAssignmentMark.camSubmitterUid) \n                           FROM CourseAssignmentMark\n                            \n                             JOIN CourseAssignmentSubmission\n                             ON CourseAssignmentSubmission.casSubmitterUid = CourseAssignmentMark.camSubmitterUid\n                             AND CourseAssignmentSubmission.casAssignmentUid = CourseAssignmentMark.camAssignmentUid\n                             \n                          WHERE CourseAssignmentMark.camAssignmentUid = ClazzAssignment.caUid \n                            AND CourseAssignmentMark.camSubmitterUid IN (SELECT submitterId \n                                                                            FROM SubmitterList\n                                                                           WHERE SubmitterList.assignmentUid = ClazzAssignment.caUid))\n                   ELSE 0 END) AS markedStudents,\n                   \n                   COALESCE((CASE WHEN CourseAssignmentMark.camUid IS NOT NULL \n                          THEN 2 \n                          WHEN CourseAssignmentSubmission.casUid IS NOT NULL \n                          THEN 1 \n                          ELSE 0 END), \n                               0) AS fileSubmissionStatus\n                \n                \n          FROM CourseBlock \n          \n               LEFT JOIN CourseBlock AS parentBlock\n               ON CourseBlock.cbModuleParentBlockUid = parentBlock.cbUid\n               AND CourseBlock.cbTYpe != 100\n          \n               LEFT JOIN ClazzAssignment\n               ON ClazzAssignment.caUid = CourseBlock.cbEntityUid\n               AND CourseBlock.cbType = 103\n               \n               LEFT JOIN ContentEntry\n               ON ContentEntry.contentEntryUid = CourseBlock.cbEntityUid\n               AND NOT ceInactive\n               AND CourseBlock.cbType = 104\n               \n               LEFT JOIN CourseDiscussion \n                      ON CourseDiscussion.courseDiscussionUid = CourseBlock.cbEntityUid\n                     AND CourseBlock.cbType = 105\n               \n               LEFT JOIN ContentEntryParentChildJoin \n               ON ContentEntryParentChildJoin.cepcjChildContentEntryUid = ContentEntry.contentEntryUid\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              LEFT JOIN StatementEntity\n\t\t\t\t     ON StatementEntity.statementUid = \n                                (SELECT statementUid \n\t\t\t\t\t\t\t       FROM StatementEntity \n                                  WHERE statementContentEntryUid = ContentEntry.contentEntryUid \n\t\t\t\t\t\t\t        AND StatementEntity.statementPersonUid = :personUid\n\t\t\t\t\t\t\t        AND contentEntryRoot \n                               ORDER BY resultScoreScaled DESC, \n                                        extensionProgress DESC, \n                                        resultSuccess DESC \n                                  LIMIT 1) \n                                  \n               LEFT JOIN CourseAssignmentSubmission\n                ON casUid = (SELECT casUid \n                                     FROM CourseAssignmentSubmission\n                                    WHERE casAssignmentUid = ClazzAssignment.caUid\n                                      AND casSubmitterUid = (SELECT (CASE WHEN ref.caGroupUid = 0 \n                                                                          THEN :personUid \n                                                                          WHEN CourseGroupMember.cgmUid IS NULL \n                                                                          THEN 0 \n                                                                          ELSE CourseGroupMember.cgmGroupNumber \n                                                                          END) as submitterUid\n                                                               FROM ClazzAssignment AS ref\n                                                                    LEFT JOIN CourseGroupMember\n                                                                     ON cgmSetUid = ClazzAssignment.caGroupUid\n                                                                     AND cgmPersonUid = :personUid\n                                                              WHERE ref.caUid = ClazzAssignment.caUid)\n                                 ORDER BY casTimestamp DESC\n                                    LIMIT 1)\n                                          \n               LEFT JOIN CourseAssignmentMark\n                      ON camUid = (SELECT camUid \n                                     FROM CourseAssignmentMark\n                                    WHERE camAssignmentUid = ClazzAssignment.caUid\n                                      AND camSubmitterUid = (SELECT (CASE WHEN ref.caGroupUid = 0 \n                                                                          THEN :personUid \n                                                                          WHEN CourseGroupMember.cgmUid IS NULL \n                                                                          THEN 0 \n                                                                          ELSE CourseGroupMember.cgmGroupNumber \n                                                                          END) as submitterUid\n                                                               FROM ClazzAssignment AS ref\n                                                                    LEFT JOIN CourseGroupMember\n                                                                     ON cgmSetUid = ClazzAssignment.caGroupUid\n                                                                     AND cgmPersonUid = :personUid\n                                                              WHERE ref.caUid = ClazzAssignment.caUid)\n                                 ORDER BY camLct DESC\n                                    LIMIT 1)       \n         WHERE CourseBlock.cbClazzUid = :clazzUid\n           AND CourseBlock.cbActive\n           AND NOT CourseBlock.cbHidden\n           AND :currentTime > CourseBlock.cbHideUntilDate\n           AND :currentTime > COALESCE(parentBlock.cbHideUntilDate,0)\n           AND CourseBlock.cbModuleParentBlockUid NOT IN (:collapseList)\n      ORDER BY CourseBlock.cbIndex\n    ")
  public actual abstract fun findAllCourseBlockByClazzUidLive(
    clazzUid: Long,
    personUid: Long,
    collapseList: List<Long>,
    currentTime: Long,
  ): DataSource.Factory<Int, CourseBlockWithCompleteEntity>

  @Query(`value` =
      "\n        UPDATE CourseBlock \n           SET cbActive = :active, \n               cbLct = :changeTime\n         WHERE cbUid = :cbUid")
  public actual abstract suspend fun updateActiveByUid(
    cbUid: Long,
    active: Boolean,
    changeTime: Long,
  ): Unit
}
