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.composites.CommentsAndName
import com.ustadmobile.lib.db.composites.PersonAndPicture
import com.ustadmobile.lib.db.entities.Comments
import com.ustadmobile.lib.db.entities.Person
import kotlin.Any
import kotlin.Boolean
import kotlin.Int
import kotlin.Long
import kotlin.collections.List

@Dao
public actual abstract class CommentsDao {
  @Insert(
    entity = Any::class,
    onConflict = 3,
  )
  public actual abstract suspend fun insertAsync(comments: Comments): Long

  @Query(`value` =
      "\n        UPDATE Comments \n           SET commentsDeleted = :deleted,\n               commentsLct = :changeTime\n         WHERE Comments.commentsUid = :uid\n    ")
  public actual abstract suspend fun updateDeletedByCommentUid(
    uid: Long,
    deleted: Boolean,
    changeTime: Long,
  )

  @Query(`value` =
      "\n        SELECT Comments.*,\n               Person.firstNames AS firstNames, \n               Person.lastName AS lastName,\n               PersonPicture.personPictureThumbnailUri AS pictureUri\n          FROM Comments\n               LEFT JOIN Person \n                    ON Person.personUid = Comments.commentsFromPersonUid\n               LEFT JOIN PersonPicture\n                    ON PersonPicture.personPictureUid = Comments.commentsFromPersonUid\n         WHERE Comments.commentsForSubmitterUid = (\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 Comments.commentsForSubmitterUid != 0\n           AND Comments.commentsEntityUid = :assignmentUid\n           AND (CAST(Comments.commentsDeleted AS INTEGER) = 0 OR CAST(:includeDeleted AS INTEGER) = 1) \n      ORDER BY Comments.commentsDateTimeAdded DESC     \n    ")
  public actual abstract fun findPrivateCommentsForUserByAssignmentUid(
    accountPersonUid: Long,
    assignmentUid: Long,
    includeDeleted: Boolean,
  ): PagingSource<Int, CommentsAndName>

  @Query(`value` =
      "\n        SELECT Person.*, PersonPicture.*\n          FROM Person\n               LEFT JOIN PersonPicture\n                         ON PersonPicture.personPictureUid = Person.personUid\n         WHERE Person.personUid IN\n               (SELECT DISTINCT Comments.commentsFromPersonUid\n                  FROM Comments\n                 WHERE Comments.commentsForSubmitterUid = (\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 Comments.commentsForSubmitterUid != 0\n                   AND Comments.commentsEntityUid = :assignmentUid\n                   AND CAST(Comments.commentsDeleted AS INTEGER) = 0)\n    ")
  public actual abstract suspend
      fun findPrivateCommentsForUserByAssignmentUidPersons(accountPersonUid: Long,
      assignmentUid: Long): List<PersonAndPicture>

  @Query(`value` =
      "\n        SELECT Comments.*,\n               Person.firstNames AS firstNames, \n               Person.lastName AS lastName,\n               PersonPicture.personPictureThumbnailUri AS pictureUri\n          FROM Comments\n               LEFT JOIN Person \n                    ON Person.personUid = Comments.commentsFromPersonUid\n               LEFT JOIN PersonPicture\n                    ON PersonPicture.personPictureUid = Comments.commentsFromPersonUid\n         WHERE Comments.commentsForSubmitterUid = :submitterUid\n           AND Comments.commentsEntityUid = :assignmentUid\n           AND (NOT Comments.commentsDeleted OR CAST(:includeDeleted AS INTEGER) = 1)\n      ORDER BY Comments.commentsDateTimeAdded DESC        \n    ")
  public actual abstract fun findPrivateCommentsForSubmitterByAssignmentUid(
    submitterUid: Long,
    assignmentUid: Long,
    includeDeleted: Boolean,
  ): PagingSource<Int, CommentsAndName>

  @Query(`value` =
      "\n        SELECT Person.*, PersonPicture.*\n          FROM Person\n               LEFT JOIN PersonPicture\n                         ON PersonPicture.personPictureUid = Person.personUid \n         WHERE Person.personUid IN \n               (SELECT Comments.commentsFromPersonUid\n                  FROM Comments\n                 WHERE Comments.commentsForSubmitterUid = :submitterUid\n                   AND Comments.commentsEntityUid = :assignmentUid\n                   AND NOT Comments.commentsDeleted) \n    ")
  public actual abstract
      fun findPrivateCommentsForSubmitterByAssignmentUidPersons(submitterUid: Long,
      assignmentUid: Long): List<PersonAndPicture>

  @Query(`value` =
      "\n        SELECT Comments.*,\n               Person.firstNames AS firstNames, \n               Person.lastName AS lastName,\n               PersonPicture.personPictureThumbnailUri AS pictureUri\n          FROM Comments\n               LEFT JOIN Person \n                    ON Person.personUid = Comments.commentsFromPersonUid\n               LEFT JOIN PersonPicture\n                    ON PersonPicture.personPictureUid = Comments.commentsFromPersonUid\n         WHERE Comments.commentsEntityUid = :assignmentUid\n           AND Comments.commentsForSubmitterUid = 0\n           AND (CAST(Comments.commentsDeleted AS INTEGER) = 0 OR CAST(:includeDeleted AS INTEGER) = 1)\n      ORDER BY Comments.commentsDateTimeAdded DESC     \n    ")
  public actual abstract fun findCourseCommentsByAssignmentUid(assignmentUid: Long,
      includeDeleted: Boolean): PagingSource<Int, CommentsAndName>

  @Query(`value` =
      "\n        SELECT Person.*, PersonPicture.*\n          FROM Person\n               LEFT JOIN PersonPicture\n                         ON PersonPicture.personPictureUid = Person.personUid\n         WHERE Person.personUid IN\n               (SELECT DISTINCT Comments.commentsFromPersonUid\n                  FROM Comments\n                       LEFT JOIN Person \n                            ON Person.personUid = Comments.commentsFromPersonUid\n                 WHERE Comments.commentsEntityUid = :assignmentUid\n                   AND Comments.commentsForSubmitterUid = 0)\n    ")
  public actual abstract suspend fun findCourseCommentsByAssignmentUidPersons(assignmentUid: Long):
      List<Person>
}
