package com.ustadmobile.core.db.dao

import androidx.lifecycle.LiveData
import androidx.paging.DataSource
import androidx.room.Dao
import androidx.room.Query
import androidx.room.Update
import com.ustadmobile.lib.db.entities.AssignmentProgressSummary
import com.ustadmobile.lib.db.entities.ClazzAssignment
import com.ustadmobile.lib.db.entities.ClazzAssignmentWithCourseBlock
import com.ustadmobile.lib.db.entities.PersonGroupAssignmentSummary
import kotlin.Boolean
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.Unit
import kotlin.collections.List

@Dao
public actual abstract class ClazzAssignmentDao : BaseDao<ClazzAssignment>,
    OneToManyJoinDao<ClazzAssignment> {
  @Query(`value` =
      "\n     REPLACE INTO ClazzAssignmentReplicate(caPk, caDestination)\n      SELECT DISTINCT ClazzAssignment.caUid AS caUid,\n             :newNodeId AS caDestination\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       WHERE UserSession.usClientNodeId = :newNodeId\n         AND UserSession.usStatus = 1\n         AND ClazzAssignment.caLct != COALESCE(\n             (SELECT caVersionId\n                FROM ClazzAssignmentReplicate\n               WHERE caPk = ClazzAssignment.caUid\n                 AND caDestination = :newNodeId), 0) \n      /*psql ON CONFLICT(caPk, caDestination) DO UPDATE\n             SET caPending = true\n      */       \n    ")
  public actual abstract suspend fun replicateOnNewNode(newNodeId: Long): Unit

  @Query(`value` =
      "\n REPLACE INTO ClazzAssignmentReplicate(caPk, caDestination)\n  SELECT DISTINCT ClazzAssignment.caUid AS caUid,\n         UserSession.usClientNodeId AS caDestination\n    FROM ChangeLog\n         JOIN ClazzAssignment\n             ON ChangeLog.chTableId = 520\n                AND ChangeLog.chEntityPk = 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 ClazzAssignment.caLct != COALESCE(\n         (SELECT caVersionId\n            FROM ClazzAssignmentReplicate\n           WHERE caPk = ClazzAssignment.caUid\n             AND caDestination = UserSession.usClientNodeId), 0)\n /*psql ON CONFLICT(caPk, caDestination) DO UPDATE\n     SET caPending = true\n  */               \n ")
  public actual abstract suspend fun replicateOnChange(): Unit

  @Query(`value` =
      "\n        SELECT * \n          FROM ClazzAssignment\n         WHERE ClazzAssignment.caClazzUid = :clazzUid\n    ")
  public actual abstract suspend fun getAllAssignmentsByClazzUidAsync(clazzUid: Long):
      List<ClazzAssignment>

  @Query(`value` =
      "\n        UPDATE ClazzAssignment \n           SET caActive = :active, \n               caLct = :changeTime\n         WHERE caUid = :cbUid")
  public actual abstract suspend fun updateActiveByUid(
    cbUid: Long,
    active: Boolean,
    changeTime: Long,
  ): Unit

  @Query(`value` =
      "\n            \n            WITH SubmitterList (submitterId, name)\n            AS (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid AS submitterId, \n                       Person.firstNames || ' ' || Person.lastName AS name\n                  FROM ClazzEnrolment\n                  \n                       JOIN Person \n                       ON Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid\n                        \n                       JOIN ClazzAssignment\n                       ON ClazzAssignment.caUid = :assignmentUid\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, name\n            UNION                 \n             SELECT DISTINCT CourseGroupMember.cgmGroupNumber AS submitterId,\n                    :group || ' ' || CourseGroupMember.cgmGroupNumber AS name  \n               FROM CourseGroupMember\n                    JOIN ClazzAssignment\n                    ON ClazzAssignment.caUid = :assignmentUid\n              WHERE CourseGroupMember.cgmSetUid = ClazzAssignment.caGroupUid\n                AND ClazzAssignment.caGroupUid != 0\n                AND CourseGroupMember.cgmGroupNumber != 0\n           GROUP BY submitterId, name\n            )\n        \n            \n            SELECT (SELECT COUNT(*) FROM SubmitterList) AS totalStudents,\n            \n                    0 as notSubmittedStudents,\n                    \n                    (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 CourseAssignmentSubmission.casAssignmentUid = :assignmentUid\n                       AND CourseAssignmentMark.camUid IS NULL\n                       AND CourseAssignmentSubmission.casSubmitterUid IN (SELECT submitterId \n                                                                            FROM SubmitterList))\n                      AS submittedStudents,\n                     \n                     \n                     (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 = :assignmentUid\n                         AND CourseAssignmentMark.camSubmitterUid IN (SELECT submitterId \n                                                                            FROM SubmitterList))\n                         AS markedStudents, \n                         \n                         'TRUE' AS hasMetricsPermission\n                         \n         FROM  ClazzAssignment\n        WHERE caActive\n          AND caClazzUid = :clazzUid \n          AND caUid = :assignmentUid                  \n    ")
  public actual abstract fun getProgressSummaryForAssignment(
    assignmentUid: Long,
    clazzUid: Long,
    group: String,
  ): LiveData<AssignmentProgressSummary?>

  @Query(`value` =
      "\n         \n            WITH SubmitterList (submitterId, name)\n            AS (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid AS submitterId, \n                       Person.firstNames || ' ' || Person.lastName AS name\n                  FROM ClazzEnrolment\n                  \n                       JOIN Person \n                       ON Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid\n                        \n                       JOIN ClazzAssignment\n                       ON ClazzAssignment.caUid = :assignmentUid\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, name\n            UNION                 \n             SELECT DISTINCT CourseGroupMember.cgmGroupNumber AS submitterId,\n                    :group || ' ' || CourseGroupMember.cgmGroupNumber AS name  \n               FROM CourseGroupMember\n                    JOIN ClazzAssignment\n                    ON ClazzAssignment.caUid = :assignmentUid\n              WHERE CourseGroupMember.cgmSetUid = ClazzAssignment.caGroupUid\n                AND ClazzAssignment.caGroupUid != 0\n                AND CourseGroupMember.cgmGroupNumber != 0\n           GROUP BY submitterId, name\n            )\n        \n        \n         SELECT submitterId AS submitterUid,\n                name, \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                (CASE WHEN ClazzAssignment.caGroupUid = 0 \n                 THEN 'TRUE' \n                 ELSE 'FALSE' END) AS isGroupAssignment,\n                 \n                 cm.commentsText AS latestPrivateComment \n\n           FROM SubmitterList\n                JOIN ClazzAssignment\n                ON ClazzAssignment.caUid = :assignmentUid\n           \n                LEFT JOIN CourseAssignmentMark\n                ON CourseAssignmentMark.camUid = (SELECT camUid\n                                                    FROM CourseAssignmentMark\n                                                   WHERE camAssignmentUid = :assignmentUid\n                                                     AND camSubmitterUid = SubmitterList.submitterId\n                                                ORDER BY camLct DESC \n                                                   LIMIT 1)\n                \n                LEFT JOIN CourseAssignmentSubmission\n                ON CourseAssignmentSubmission.casUid = (SELECT casUid\n                                                          FROM CourseAssignmentSubmission\n                                                         WHERE casAssignmentUid = :assignmentUid\n                                                           AND casSubmitterUid = SubmitterList.submitterId\n                                                      ORDER BY casTimestamp DESC \n                                                         LIMIT 1)\n                LEFT JOIN Comments AS cm \n                    ON cm.commentsUid = (\n                                 SELECT Comments.commentsUid \n                                   FROM Comments \n                                  WHERE Comments.commentsEntityType = 520\n                                    AND commentsEntityUid = :assignmentUid\n                                    AND NOT commentsInActive\n                                    AND NOT commentsPublic\n                                    AND (CASE WHEN ClazzAssignment.caGroupUid = 0\n                                              THEN commentsPersonUid = SubmitterList.submitterId\n                                              ELSE commentSubmitterUid = SubmitterList.submitterId END)\n                               ORDER BY commentsDateTimeAdded DESC LIMIT 1)                                                      \n                                                                      \n          WHERE name LIKE :searchText\n       ORDER BY name \n    ")
  public actual abstract fun getSubmitterListForAssignment(
    assignmentUid: Long,
    clazzUid: Long,
    group: String,
    searchText: String,
  ): DataSource.Factory<Int, PersonGroupAssignmentSummary>

  @Query(`value` =
      "\n        SELECT (CASE WHEN ClazzAssignment.caGroupUid = 0 \n                     THEN :personUid \n                     WHEN CourseGroupMember.cgmUid IS NULL \n                     THEN 0 \n                     ELSE CourseGroupMember.cgmGroupNumber END) as submitterUid\n          FROM ClazzAssignment\n               LEFT JOIN CourseGroupMember\n               ON cgmSetUid = ClazzAssignment.caGroupUid\n               AND cgmPersonUid = :personUid\n         WHERE caUid = :assignmentUid\n    ")
  public actual abstract suspend fun getSubmitterUid(assignmentUid: Long, personUid: Long): Long

  @Update(onConflict = 3)
  public actual abstract suspend fun updateAsync(clazzAssignment: ClazzAssignment): Unit

  @Query(`value` =
      "\n        SELECT * \n          FROM ClazzAssignment \n         WHERE caUid = :uid\n    ")
  public actual abstract suspend fun findByUidAsync(uid: Long): ClazzAssignment?

  @Query(`value` =
      "\n          SELECT COALESCE((\n           SELECT caGroupUid\n           FROM ClazzAssignment\n          WHERE caUid = :uid),-1)\n    ")
  public actual abstract suspend fun getGroupUidFromAssignment(uid: Long): Long

  @Query(`value` =
      "\n        SELECT * \n          FROM ClazzAssignment\n               LEFT JOIN CourseBlock\n               ON CourseBlock.cbEntityUid = ClazzAssignment.caUid\n               AND CourseBlock.cbType = 103\n         WHERE caUid = :uid\n    ")
  public actual abstract suspend fun findByUidWithBlockAsync(uid: Long):
      ClazzAssignmentWithCourseBlock?

  @Query(`value` = "\n        SELECT * \n          FROM ClazzAssignment LIMIT 1\n    ")
  public actual abstract fun findClazzAssignment(): ClazzAssignment?

  @Query(`value` =
      "SELECT * \n                      FROM ClazzAssignment \n                     WHERE caUid = :uid")
  public actual abstract fun findByUidLive(uid: Long): LiveData<ClazzAssignment?>
}
