package com.ustadmobile.core.db.dao

import androidx.lifecycle.LiveData
import androidx.paging.DataSource
import androidx.room.Dao
import androidx.room.Insert
import androidx.room.Query
import com.ustadmobile.lib.db.entities.CourseAssignmentSubmission
import com.ustadmobile.lib.db.entities.CourseAssignmentSubmissionWithAttachment
import kotlin.Boolean
import kotlin.Int
import kotlin.Long
import kotlin.Unit
import kotlin.collections.List

@Dao
public actual abstract class CourseAssignmentSubmissionDao : BaseDao<CourseAssignmentSubmission> {
  @Query(`value` =
      "\n     REPLACE INTO CourseAssignmentSubmissionReplicate(casPk, casDestination)\n      SELECT DISTINCT CourseAssignmentSubmission.casUid AS casPk,\n             :newNodeId AS casDestination\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 CourseAssignmentSubmission\n                    ON CourseAssignmentSubmission.casAssignmentUid = ClazzAssignment.caUid\n       WHERE UserSession.usClientNodeId = :newNodeId\n         AND UserSession.usStatus = 1\n         AND CourseAssignmentSubmission.casTimestamp != COALESCE(\n             (SELECT casVersionId\n                FROM CourseAssignmentSubmissionReplicate\n               WHERE casPk = CourseAssignmentSubmission.casUid\n                 AND casDestination = :newNodeId), 0) \n      /*psql ON CONFLICT(casPk, casDestination) DO UPDATE\n             SET casPending = true\n      */       \n    ")
  public actual abstract suspend fun replicateOnNewNode(newNodeId: Long): Unit

  @Query(`value` =
      "\n REPLACE INTO CourseAssignmentSubmissionReplicate(casPk, casDestination)\n  SELECT DISTINCT CourseAssignmentSubmission.casUid AS casPk,\n         UserSession.usClientNodeId AS casDestination\n    FROM ChangeLog\n         JOIN CourseAssignmentSubmission\n             ON ChangeLog.chTableId = 522\n                AND ChangeLog.chEntityPk = CourseAssignmentSubmission.casUid\n             JOIN ClazzAssignment\n                    ON CourseAssignmentSubmission.casAssignmentUid = 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 CourseAssignmentSubmission.casTimestamp != COALESCE(\n         (SELECT casVersionId\n            FROM CourseAssignmentSubmissionReplicate\n           WHERE casPk = CourseAssignmentSubmission.casUid\n             AND casDestination = UserSession.usClientNodeId), 0)\n /*psql ON CONFLICT(casPk, casDestination) DO UPDATE\n     SET casPending = true\n  */               \n ")
  public actual abstract suspend fun replicateOnChange(): Unit

  @Insert(onConflict = 3)
  public actual abstract suspend fun insertListAsync(entityList: List<CourseAssignmentSubmission>):
      Unit

  @Query(`value` =
      "\n        SELECT * \n          FROM CourseAssignmentSubmission\n          \n               LEFT JOIN CourseAssignmentSubmissionAttachment\n               ON CourseAssignmentSubmissionAttachment.casaSubmissionUid = CourseAssignmentSubmission.casUid\n               \n         WHERE casAssignmentUid = :assignmentUid\n           AND casSubmitterUid = :submitterUid\n      ORDER BY casTimestamp DESC\n    ")
  public actual abstract fun getAllSubmissionsFromSubmitter(assignmentUid: Long,
      submitterUid: Long): DataSource.Factory<Int, CourseAssignmentSubmissionWithAttachment>

  @Query(`value` =
      "\n        SELECT Count(casUid)\n          FROM CourseAssignmentSubmission\n         WHERE casAssignmentUid = :assignmentUid\n           AND casSubmitterUid = :submitterUid\n           AND casType = 2\n    ")
  public actual abstract suspend fun countFileSubmissionFromStudent(assignmentUid: Long,
      submitterUid: Long): Int

  @Query(`value` =
      "\n        SELECT Count(casUid)\n          FROM CourseAssignmentSubmission\n         WHERE casAssignmentUid = :assignmentUid\n           AND casSubmitterUid = :submitterUid\n    ")
  public actual abstract suspend fun countSubmissionsFromSubmitter(assignmentUid: Long,
      submitterUid: Long): Int

  @Query(`value` =
      "\n           SELECT COALESCE((\n                SELECT (CASE WHEN CourseAssignmentMark.camAssignmentUid IS NOT NULL \n                             THEN 2\n                             ELSE 1 \n                             END) AS status\n                  FROM CourseAssignmentSubmission\n                       \n                       LEFT JOIN CourseAssignmentMark\n                       ON CourseAssignmentMark.camAssignmentUid = :assignmentUid\n                       AND CourseAssignmentMark.camSubmitterUid = :submitterUid\n                       \n                 WHERE CourseAssignmentSubmission.casAssignmentUid = :assignmentUid\n                   AND CourseAssignmentSubmission.casSubmitterUid = :submitterUid\n                 LIMIT 1\n           ),0) AS Status\n    ")
  public actual abstract fun getStatusOfAssignmentForSubmitter(assignmentUid: Long,
      submitterUid: Long): LiveData<Int>

  @Query(`value` =
      "\n        SELECT * \n          FROM CourseAssignmentSubmission\n         WHERE CourseAssignmentSubmission.casAssignmentUid = :assignmentUid\n           AND CourseAssignmentSubmission.casSubmitterUid = :submitterUid\n      ORDER BY casTimestamp DESC\n         LIMIT 1\n    ")
  public actual abstract suspend fun findLastSubmissionFromStudent(submitterUid: Long,
      assignmentUid: Long): CourseAssignmentSubmission?

  @Query(`value` =
      "\n         SELECT NOT EXISTS(SELECT 1\n                        FROM CourseAssignmentSubmission\n                       WHERE CourseAssignmentSubmission.casAssignmentUid = :assignmentUid\n                       LIMIT 1)\n    ")
  public actual abstract fun checkNoSubmissionsMade(assignmentUid: Long): LiveData<Boolean>
}
