package com.ustadmobile.core.db.dao

import androidx.paging.PagingSource
import androidx.room.Dao
import androidx.room.Query
import com.ustadmobile.lib.db.composites.CourseAssignmentMarkAndMarkerName
import com.ustadmobile.lib.db.composites.PersonAndPicture
import com.ustadmobile.lib.db.entities.AverageCourseAssignmentMark
import com.ustadmobile.lib.db.entities.CourseAssignmentMark
import com.ustadmobile.lib.db.entities.CourseAssignmentMarkWithPersonMarker
import kotlin.Boolean
import kotlin.Int
import kotlin.Long
import kotlin.collections.List
import kotlinx.coroutines.flow.Flow

@Dao
public actual abstract class CourseAssignmentMarkDao : BaseDao<CourseAssignmentMark> {
  @Query(`value` =
      "\n        SELECT NOT EXISTS(SELECT 1\n                        FROM CourseAssignmentMark\n                       WHERE CourseAssignmentMark.camAssignmentUid = :assignmentUid\n                       LIMIT 1)\n    ")
  public actual abstract fun checkNoSubmissionsMarked(assignmentUid: Long): Flow<Boolean>

  @Query(`value` =
      "\n         WITH ScoreByMarker (averageScore, averagePenalty) AS (\n                 SELECT AVG(camMark), AVG(camPenalty)\n                   FROM courseAssignmentMark\n                        JOIN ClazzAssignment\n                        ON caUid = courseAssignmentMark.camAssignmentUid         \n                    AND camAssignmentUid = :assignmentUid\n                    AND camSubmitterUid = :submitterUid\n                  WHERE camLct = (SELECT MAX(mark.camLct) \n                                    FROM CourseAssignmentMark As mark\n                                    WHERE mark.camAssignmentUid = :assignmentUid\n                                     AND mark.camSubmitterUid = :submitterUid\n                                     AND (caMarkingType = 1\n                                       OR mark.camMarkerSubmitterUid = courseAssignmentMark.camMarkerSubmitterUid))\n                )                       \n\n         SELECT COALESCE(averageScore, -1) AS averageScore, COALESCE(averagePenalty, -1) AS averagePenalty\n           FROM ScoreByMarker\n    ")
  public actual abstract fun getMarkOfAssignmentForSubmitterLiveData(assignmentUid: Long,
      submitterUid: Long): Flow<AverageCourseAssignmentMark?>

  @Query(`value` =
      "\n        SELECT CourseAssignmentMark.*,\n               Person.firstNames AS markerFirstNames,\n               Person.lastName AS markerLastName,\n               PersonPicture.personPictureThumbnailUri AS markerPictureUri\n          FROM CourseAssignmentMark\n               LEFT JOIN Person\n                         ON Person.personUid = CourseAssignmentMark.camMarkerPersonUid\n               LEFT JOIN PersonPicture\n                         ON PersonPicture.personPictureUid = CourseAssignmentMark.camMarkerPersonUid\n         WHERE (\n        SELECT CASE\n                    -- When assignment is individual then the submitter uid is the personuid if they are enrolled in the course otherwise zero \n                    WHEN (SELECT caGroupUid\n                            FROM ClazzAssignment\n                           WHERE caUid = :assignmentUid) = 0\n                         THEN (\n        SELECT COALESCE(\n                (SELECT ClazzEnrolment.clazzEnrolmentPersonUid\n                   FROM ClazzEnrolment\n                  WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid\n                    AND ClazzEnrolment.clazzEnrolmentRole = 1000\n                    AND ClazzEnrolment.clazzEnrolmentClazzUid = \n                        (SELECT ClazzAssignment.caClazzUid\n                           FROM ClazzAssignment\n                          WHERE ClazzAssignment.caUid = :assignmentUid)\n                  LIMIT 1), 0)\n    )\n                    -- When assignment is by groups but the active user is not an enrolled student then the submitter uid is zero     \n                    WHEN (SELECT caGroupUid\n                            FROM ClazzAssignment\n                           WHERE caUid = :assignmentUid) != 0\n                          AND (\n        SELECT COALESCE(\n                (SELECT ClazzEnrolment.clazzEnrolmentPersonUid\n                   FROM ClazzEnrolment\n                  WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid\n                    AND ClazzEnrolment.clazzEnrolmentRole = 1000\n                    AND ClazzEnrolment.clazzEnrolmentClazzUid = \n                        (SELECT ClazzAssignment.caClazzUid\n                           FROM ClazzAssignment\n                          WHERE ClazzAssignment.caUid = :assignmentUid)\n                  LIMIT 1), 0)\n    ) = 0\n                          THEN 0\n                    -- When assignment is by groups and the person is an enrolled student the submitter uid is the \n                    -- group that they are assigned to. If they are not assigned to a group but are enrolled\n                    -- then we submitter uid = SUBMITTER_ENROLLED_BUT_NOT_IN_GROUP\n                    ELSE COALESCE(\n                          (SELECT CourseGroupMember.cgmGroupNumber\n                             FROM CourseGroupMember\n                            WHERE (\n        SELECT COALESCE(\n                (SELECT ClazzEnrolment.clazzEnrolmentPersonUid\n                   FROM ClazzEnrolment\n                  WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid\n                    AND ClazzEnrolment.clazzEnrolmentRole = 1000\n                    AND ClazzEnrolment.clazzEnrolmentClazzUid = \n                        (SELECT ClazzAssignment.caClazzUid\n                           FROM ClazzAssignment\n                          WHERE ClazzAssignment.caUid = :assignmentUid)\n                  LIMIT 1), 0)\n    ) > 0\n                              AND CourseGroupMember.cgmSetUid = \n                                  (SELECT caGroupUid\n                                     FROM ClazzAssignment\n                                    WHERE caUid = :assignmentUid)\n                              AND CourseGroupMember.cgmPersonUid = :accountPersonUid\n                            LIMIT 1), -1)\n                    END\n    ) > 0\n           AND CourseAssignmentMark.camAssignmentUid = :assignmentUid\n           AND CourseAssignmentMark.camSubmitterUid = (\n        SELECT CASE\n                    -- When assignment is individual then the submitter uid is the personuid if they are enrolled in the course otherwise zero \n                    WHEN (SELECT caGroupUid\n                            FROM ClazzAssignment\n                           WHERE caUid = :assignmentUid) = 0\n                         THEN (\n        SELECT COALESCE(\n                (SELECT ClazzEnrolment.clazzEnrolmentPersonUid\n                   FROM ClazzEnrolment\n                  WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid\n                    AND ClazzEnrolment.clazzEnrolmentRole = 1000\n                    AND ClazzEnrolment.clazzEnrolmentClazzUid = \n                        (SELECT ClazzAssignment.caClazzUid\n                           FROM ClazzAssignment\n                          WHERE ClazzAssignment.caUid = :assignmentUid)\n                  LIMIT 1), 0)\n    )\n                    -- When assignment is by groups but the active user is not an enrolled student then the submitter uid is zero     \n                    WHEN (SELECT caGroupUid\n                            FROM ClazzAssignment\n                           WHERE caUid = :assignmentUid) != 0\n                          AND (\n        SELECT COALESCE(\n                (SELECT ClazzEnrolment.clazzEnrolmentPersonUid\n                   FROM ClazzEnrolment\n                  WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid\n                    AND ClazzEnrolment.clazzEnrolmentRole = 1000\n                    AND ClazzEnrolment.clazzEnrolmentClazzUid = \n                        (SELECT ClazzAssignment.caClazzUid\n                           FROM ClazzAssignment\n                          WHERE ClazzAssignment.caUid = :assignmentUid)\n                  LIMIT 1), 0)\n    ) = 0\n                          THEN 0\n                    -- When assignment is by groups and the person is an enrolled student the submitter uid is the \n                    -- group that they are assigned to. If they are not assigned to a group but are enrolled\n                    -- then we submitter uid = SUBMITTER_ENROLLED_BUT_NOT_IN_GROUP\n                    ELSE COALESCE(\n                          (SELECT CourseGroupMember.cgmGroupNumber\n                             FROM CourseGroupMember\n                            WHERE (\n        SELECT COALESCE(\n                (SELECT ClazzEnrolment.clazzEnrolmentPersonUid\n                   FROM ClazzEnrolment\n                  WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid\n                    AND ClazzEnrolment.clazzEnrolmentRole = 1000\n                    AND ClazzEnrolment.clazzEnrolmentClazzUid = \n                        (SELECT ClazzAssignment.caClazzUid\n                           FROM ClazzAssignment\n                          WHERE ClazzAssignment.caUid = :assignmentUid)\n                  LIMIT 1), 0)\n    ) > 0\n                              AND CourseGroupMember.cgmSetUid = \n                                  (SELECT caGroupUid\n                                     FROM ClazzAssignment\n                                    WHERE caUid = :assignmentUid)\n                              AND CourseGroupMember.cgmPersonUid = :accountPersonUid\n                            LIMIT 1), -1)\n                    END\n    )\n      ORDER BY CourseAssignmentMark.camLct DESC    \n    ")
  public actual abstract fun getAllMarksForUserAsFlow(accountPersonUid: Long, assignmentUid: Long):
      Flow<List<CourseAssignmentMarkAndMarkerName>>

  @Query(`value` =
      "\n        SELECT CourseAssignmentMark.*,\n               Person.firstNames AS markerFirstNames,\n               Person.lastName AS markerLastName,\n               PersonPicture.personPictureThumbnailUri AS markerPictureUri\n          FROM CourseAssignmentMark\n               LEFT JOIN Person\n                         ON Person.personUid = CourseAssignmentMark.camMarkerPersonUid\n               LEFT JOIN PersonPicture\n                         ON PersonPicture.personPictureUid = CourseAssignmentMark.camMarkerPersonUid\n         WHERE CourseAssignmentMark.camAssignmentUid = :assignmentUid\n           AND CourseAssignmentMark.camSubmitterUid = :submitterUid\n      ORDER BY CourseAssignmentMark.camLct DESC                             \n    ")
  public actual abstract fun getAllMarksForSubmitterAsFlow(submitterUid: Long, assignmentUid: Long):
      Flow<List<CourseAssignmentMarkAndMarkerName>>

  @Query(`value` =
      "\n        SELECT Person.*, PersonPicture.*\n          FROM Person\n               LEFT JOIN PersonPicture\n                         ON PersonPicture.personPictureUid = Person.personUid\n         WHERE PersonUid IN\n               (SELECT CourseAssignmentMark.camMarkerPersonUid\n                  FROM CourseAssignmentMark\n                 WHERE CourseAssignmentMark.camAssignmentUid = :assignmentUid\n                   AND CourseAssignmentMark.camSubmitterUid = :submitterUid)\n    ")
  public actual abstract suspend fun getAllMarksForSubmitterAsFlowMarkerPersons(submitterUid: Long,
      assignmentUid: Long): List<PersonAndPicture>

  @Query(`value` =
      "\n          WITH ScoreByMarker AS (\n                 SELECT *\n                   FROM courseAssignmentMark    \n                  WHERE camAssignmentUid = :assignmentUid\n                    AND camSubmitterUid = :submitterUid\n                    AND (:filter = 0 OR camLct = (SELECT MAX(mark.camLct) \n                                    FROM CourseAssignmentMark As mark\n                                    WHERE mark.camAssignmentUid = :assignmentUid\n                                      AND mark.camSubmitterUid = :submitterUid\n                                      AND mark.camMarkerSubmitterUid = courseAssignmentMark.camMarkerSubmitterUid\n                                      ))                 \n                ORDER BY camLct DESC)    \n                \n          SELECT marker.*, ScoreByMarker.*, (ClazzAssignment.caGroupUid != 0) AS isGroup\n            FROM ScoreByMarker\n                 JOIN Person As marker\n                 ON Marker.personUid = ScoreByMarker.camMarkerPersonUid\n                 JOIN ClazzAssignment\n                 ON ClazzAssignment.caUid = :assignmentUid\n    ")
  public actual abstract fun getAllMarksOfAssignmentForSubmitter(
    assignmentUid: Long,
    submitterUid: Long,
    filter: Int,
  ): PagingSource<Int, CourseAssignmentMarkWithPersonMarker>

  @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                   JOIN ClazzAssignment\n                   ON ClazzAssignment.caUid = CourseAssignmentSubmission.casAssignmentUid\n              \n                   LEFT JOIN CourseAssignmentMark\n                   ON CourseAssignmentMark.camSubmitterUid = CourseAssignmentSubmission.casSubmitterUid\n                   AND CourseAssignmentMark.camAssignmentUid = ClazzAssignment.caUid\n                   \n                   LEFT JOIN PeerReviewerAllocation\n                   ON praAssignmentUid = ClazzAssignment.caUid\n                   AND praToMarkerSubmitterUid = :submitterUid\n                   \n             WHERE CourseAssignmentSubmission.casSubmitterUid != :submitterUid\n               AND CourseAssignmentSubmission.casSubmitterUid != :markerUid\n               AND CourseAssignmentSubmission.casAssignmentUid = :assignmentUid\n               AND CourseAssignmentMark.camUid IS NULL\n               AND (ClazzAssignment.caMarkingType = 1 \n                    OR PeerReviewerAllocation.praMarkerSubmitterUid = :markerUid)\n          GROUP BY casSubmitterUid\n         LIMIT 1),0)\n    ")
  public actual abstract suspend fun findNextSubmitterToMarkForAssignment(
    assignmentUid: Long,
    submitterUid: Long,
    markerUid: Long,
  ): Long
}
