package com.ustadmobile.core.db.dao

import androidx.paging.PagingSource
import androidx.room.Dao
import androidx.room.Insert
import androidx.room.Query
import com.ustadmobile.lib.db.entities.CourseAssignmentSubmission
import kotlin.Any
import kotlin.Boolean
import kotlin.Int
import kotlin.Long
import kotlin.collections.List
import kotlinx.coroutines.flow.Flow

@Dao
public actual abstract class CourseAssignmentSubmissionDao : BaseDao<CourseAssignmentSubmission> {
  @Insert(
    entity = Any::class,
    onConflict = 3,
  )
  public actual abstract suspend fun insertListAsync(entityList: List<CourseAssignmentSubmission>)

  @Query(`value` =
      "\n        SELECT * \n          FROM CourseAssignmentSubmission\n         WHERE casAssignmentUid = :assignmentUid\n           AND casSubmitterUid = :submitterUid\n      ORDER BY casTimestamp DESC\n    ")
  public actual abstract fun getAllSubmissionsFromSubmitter(assignmentUid: Long,
      submitterUid: Long): PagingSource<Int, CourseAssignmentSubmission>

  @Query(`value` =
      "\n         SELECT CourseAssignmentSubmission.*\n          FROM CourseAssignmentSubmission\n         WHERE casSubmitterUid = (\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    ")
  public actual abstract fun getAllSubmissionsForUser(accountPersonUid: Long, assignmentUid: Long):
      Flow<List<CourseAssignmentSubmission>>

  @Query(`value` =
      "\n        SELECT CourseAssignmentSubmission.*\n          FROM CourseAssignmentSubmission\n         WHERE CourseAssignmentSubmission.casAssignmentUid = :assignmentUid\n           AND CourseAssignmentSubmission.casSubmitterUid = :submitterUid\n      ORDER BY CourseAssignmentSubmission.casTimestamp DESC      \n    ")
  public actual abstract fun getAllSubmissionsFromSubmitterAsFlow(submitterUid: Long,
      assignmentUid: Long): Flow<List<CourseAssignmentSubmission>>

  @Query(`value` =
      "\n        SELECT CourseAssignmentSubmission.*\n          FROM CourseAssignmentSubmission\n         WHERE casSubmitterUid = (\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           AND CourseAssignmentSubmission.casAssignmentUid = :assignmentUid\n      ORDER BY casTimestamp DESC\n         LIMIT 1\n    ")
  public actual abstract suspend fun getLatestSubmissionForUserAsync(accountPersonUid: Long,
      assignmentUid: Long): CourseAssignmentSubmission?

  @Query(`value` =
      "\n        SELECT EXISTS\n               (SELECT CourseAssignmentSubmission.casUid\n                  FROM CourseAssignmentSubmission\n                 WHERE CourseAssignmentSubmission.casSubmitterUid = (\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                   AND CourseAssignmentSubmission.casAssignmentUid = :assignmentUid)\n    ")
  public actual abstract suspend fun doesUserHaveSubmissions(accountPersonUid: Long,
      assignmentUid: Long): Boolean

  @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): Flow<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): Flow<Boolean>

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

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

  @Query(`value` =
      "\n        SELECT CourseAssignmentSubmission.*\n          FROM CourseAssignmentSubmission\n         WHERE casUid = :submissionUid\n    ")
  public actual abstract fun findByUidAsFlow(submissionUid: Long): Flow<CourseAssignmentSubmission?>

  @Query(`value` =
      "\n        SELECT CourseAssignmentSubmission.*\n          FROM CourseAssignmentSubmission\n         WHERE CourseAssignmentSubmission.casAssignmentUid = :assignmentUid\n           AND CourseAssignmentSubmission.casSubmitterUid = \n               (\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 CourseAssignmentSubmission.casTimestamp DESC\n    ")
  public actual abstract fun findByAssignmentUidAndAccountPersonUid(accountPersonUid: Long,
      assignmentUid: Long): Flow<List<CourseAssignmentSubmission>>
}
