package com.ustadmobile.core.db.dao

import androidx.lifecycle.LiveData
import androidx.room.Dao
import androidx.room.Query
import com.ustadmobile.lib.db.entities.CourseAssignmentMark
import kotlin.Long
import kotlin.Unit

@Dao
public actual abstract class CourseAssignmentMarkDao : BaseDao<CourseAssignmentMark> {
  @Query(`value` =
      "\n     REPLACE INTO CourseAssignmentMarkReplicate(camPk, camDestination)\n      SELECT DISTINCT CourseAssignmentMark.camUid AS camPk,\n             :newNodeId AS camDestination\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 CourseAssignmentMark\n                    ON CourseAssignmentMark.camAssignmentUid = ClazzAssignment.caUid\n                    \n       WHERE UserSession.usClientNodeId = :newNodeId\n         AND UserSession.usStatus = 1\n         AND CourseAssignmentMark.camLct != COALESCE(\n             (SELECT camVersionId\n                FROM CourseAssignmentMarkReplicate\n               WHERE camPk = CourseAssignmentMark.camUid\n                 AND camDestination = :newNodeId), 0) \n      /*psql ON CONFLICT(camPk, camDestination) DO UPDATE\n             SET camPending = true\n      */       \n    ")
  public actual abstract suspend fun replicateOnNewNode(newNodeId: Long): Unit

  @Query(`value` =
      "\n REPLACE INTO CourseAssignmentMarkReplicate(camPk, camDestination)\n  SELECT DISTINCT CourseAssignmentMark.camUid AS camPk,\n         UserSession.usClientNodeId AS camDestination\n    FROM ChangeLog\n         JOIN CourseAssignmentMark\n             ON ChangeLog.chTableId = 523\n                AND ChangeLog.chEntityPk = CourseAssignmentMark.camUid\n             JOIN ClazzAssignment\n                    ON CourseAssignmentMark.camAssignmentUid = ClazzAssignment.caUid\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 CourseAssignmentMark.camLct != COALESCE(\n         (SELECT camVersionId\n            FROM CourseAssignmentMarkReplicate\n           WHERE camPk = CourseAssignmentMark.camUid\n             AND camDestination = UserSession.usClientNodeId), 0)\n /*psql ON CONFLICT(camPk, camDestination) DO UPDATE\n     SET camPending = true\n  */               \n ")
  public actual abstract suspend fun replicateOnChange(): Unit

  @Query(`value` =
      "\n        SELECT * \n          FROM CourseAssignmentMark               \n         WHERE camAssignmentUid = :assignmentUid\n           AND camSubmitterUid = :submitterUid\n      ORDER BY camLct DESC\n         LIMIT 1\n    ")
  public actual abstract fun getMarkOfAssignmentForSubmitterLiveData(assignmentUid: Long,
      submitterUid: Long): LiveData<CourseAssignmentMark?>

  @Query(`value` =
      "\n        SELECT * \n          FROM CourseAssignmentMark\n         WHERE camAssignmentUid = :assignmentUid\n           AND camSubmitterUid = :submitterUid\n      ORDER BY camLct DESC\n         LIMIT 1\n    ")
  public actual abstract fun getMarkOfAssignmentForStudent(assignmentUid: Long, submitterUid: Long):
      CourseAssignmentMark?

  @Query(`value` =
      "\n         SELECT COALESCE((\n            SELECT casSubmitterUid\n              FROM CourseAssignmentSubmission\n              \n                   LEFT JOIN CourseAssignmentMark\n                   ON CourseAssignmentMark.camSubmitterUid = CourseAssignmentSubmission.casSubmitterUid\n                   AND CourseAssignmentMark.camAssignmentUid = :assignmentUid\n                   \n             WHERE CourseAssignmentSubmission.casSubmitterUid != :submitterUid\n               AND CourseAssignmentMark.camUid IS NULL\n          GROUP BY casSubmitterUid\n         LIMIT 1),0)\n    ")
  public actual abstract suspend fun findNextSubmitterToMarkForAssignment(assignmentUid: Long,
      submitterUid: Long): Long
}
