package com.ustadmobile.core.db.dao

import androidx.room.Dao
import androidx.room.Insert
import androidx.room.Query
import androidx.room.Update
import com.ustadmobile.lib.db.composites.CourseGroupMemberAndPerson
import com.ustadmobile.lib.db.composites.PersonAndPicture
import com.ustadmobile.lib.db.entities.ClazzEnrolment
import com.ustadmobile.lib.db.entities.CourseGroupMember
import com.ustadmobile.lib.db.entities.CourseGroupMemberAndName
import kotlin.Any
import kotlin.Int
import kotlin.Long
import kotlin.collections.List
import kotlinx.coroutines.flow.Flow

@Dao
public actual abstract class CourseGroupMemberDao : BaseDao<CourseGroupMember> {
  @Query(`value` =
      "\n        SELECT CourseGroupMember.*\n          FROM CourseGroupMember\n         WHERE cgmSetUid = :groupSetUid \n    ")
  public actual abstract suspend fun findByGroupSetUidAsync(groupSetUid: Long):
      List<CourseGroupMember>

  @Query(`value` =
      "\n        SELECT * \n          FROM CourseGroupMember\n         WHERE cgmPersonUid = :studentUid \n          AND cgmSetUid = :groupSetUid\n         LIMIT 1\n    ")
  public actual abstract suspend fun findByPersonUid(groupSetUid: Long, studentUid: Long):
      CourseGroupMember?

  @Insert(
    entity = Any::class,
    onConflict = 3,
  )
  public actual abstract suspend fun insertListAsync(entityList: List<CourseGroupMember>)

  @Update(
    entity = Any::class,
    onConflict = 3,
  )
  public actual abstract suspend fun updateListAsync(entityList: List<CourseGroupMember>)

  @Query(`value` =
      "\n        --First get a list of all enrolments - this may contains duplicates for students who leave and re-enrol\n        WITH AllEnrollmentsAndActiveStatus(enrolledPersonUid, isActive) AS \n             (SELECT ClazzEnrolment.clazzEnrolmentPersonUid AS enrolledPersonUid,\n                     (:time BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined AND ClazzEnrolment.clazzEnrolmentDateLeft) AS isActive\n                FROM ClazzEnrolment\n               WHERE ClazzEnrolment.clazzEnrolmentClazzUid = \n        CASE(:clazzUid)\n                         WHEN 0 THEN \n                                (SELECT CourseGroupSet.cgsClazzUid\n                                   FROM CourseGroupSet\n                                  WHERE CourseGroupSet.cgsUid = :cgsUid)\n                         ELSE :clazzUid\n                     END\n    \n                 AND ClazzEnrolment.clazzEnrolmentRole = 1000),\n        --Consolidate and removes any duplicates\n             EnrolledStudentPersonUids(enrolledPersonUid, isActive) AS\n             (SELECT DISTINCT AllEnrollmentsAndActiveStatus.enrolledPersonUid,\n                     (SELECT CAST(AllEnrollmentsInner.isActive AS INTEGER)\n                        FROM AllEnrollmentsAndActiveStatus AllEnrollmentsInner\n                       WHERE AllEnrollmentsInner.enrolledPersonUid = AllEnrollmentsAndActiveStatus.enrolledPersonUid\n                    ORDER BY AllEnrollmentsInner.isActive DESC\n                       LIMIT 1) AS isActive\n                FROM AllEnrollmentsAndActiveStatus)\n        \n        -- Now create a list with each students name, the coursegroupmember object if any and active status        \n        SELECT (Person.firstNames || ' ' || Person.lastName) AS name,\n               Person.personUid,\n               CourseGroupMember.*,\n               PersonPicture.*,\n               EnrolledStudentPersonUids.isActive AS enrolmentIsActive,\n               PersonPicture.personPictureThumbnailUri AS pictureUri\n          FROM EnrolledStudentPersonUids\n               JOIN Person\n                    ON Person.personUid = EnrolledStudentPersonUids.enrolledPersonUid \n               LEFT JOIN PersonPicture\n                         ON PersonPicture.personPictureUid = Person.personUid  \n               -- LEFT JOIN will use the most recent member in case of duplicate assignments eg if      \n               LEFT JOIN CourseGroupMember\n                         ON CourseGroupMember.cgmUid = \n                            (SELECT CourseGroupMember.cgmUid\n                               FROM CourseGroupMember\n                              WHERE CourseGroupMember.cgmPersonUid = EnrolledStudentPersonUids.enrolledPersonUid\n                                AND CourseGroupMember.cgmSetUid = :cgsUid \n                           ORDER BY CourseGroupMember.cgmLct DESC        \n                              LIMIT 1)\n         WHERE (:activeFilter = 0 OR :activeFilter = EnrolledStudentPersonUids.isActive)  \n               /* \n                * Begin permission check -  must have course view members permission, or active \n                * user must be in the same group \n                */ \n            AND (\n                    ((\n             /* If the accountPersonUid is the owner of the course, all permissions are granted */\n             (COALESCE(\n                          (SELECT _Clazz_Permission.clazzOwnerPersonUid \n                             FROM Clazz _Clazz_Permission\n                            WHERE _Clazz_Permission.clazzUid = :clazzUid), 0) = :accountPersonUid)\n              /* \n              If there is a CoursePermission entity that is for the course as per the clazzUid\n              parameter that is granted to the person directly or to the enrolmentRole that the \n              person has in the course, then permission is granted.\n              */              \n              OR EXISTS(SELECT CoursePermission.cpUid\n                          FROM CoursePermission\n                               \n        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson \n                        ON CoursePermission.cpToEnrolmentRole != 0\n                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = \n                           (SELECT COALESCE(\n                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid \n                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner\n                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid\n                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = :accountPersonUid\n                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive\n                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   \n                                     LIMIT 1), 0))\n    \n                         WHERE CoursePermission.cpClazzUid = :clazzUid\n                           AND (CoursePermission.cpToPersonUid = :accountPersonUid \n                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)\n                           AND (CoursePermission.cpPermissionsFlag & \n         8192\n                     \n        ) > 0)\n              OR EXISTS(SELECT SystemPermission.spUid\n                          FROM SystemPermission\n                         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n                           AND (SystemPermission.spPermissionsFlag & \n     8192\n                     \n        ) > 0)\n               )\n    )\n                  OR EXISTS(\n                     SELECT 1\n                       FROM CourseGroupMember _CourseGroupMemberForActivePerson\n                      WHERE _CourseGroupMemberForActivePerson.cgmPersonUid = :accountPersonUid\n                        AND _CourseGroupMemberForActivePerson.cgmGroupNumber = CourseGroupMember.cgmGroupNumber)     \n                 )\n      ORDER BY Person.firstNames, Person.lastName ASC\n    ")
  public actual abstract suspend fun findByCourseGroupSetAndClazz(
    cgsUid: Long,
    clazzUid: Long,
    time: Long,
    activeFilter: Int,
    accountPersonUid: Long,
  ): List<CourseGroupMemberAndName>

  @Query(`value` =
      "\n        --First get a list of all enrolments - this may contains duplicates for students who leave and re-enrol\n        WITH AllEnrollmentsAndActiveStatus(enrolledPersonUid, isActive) AS \n             (SELECT ClazzEnrolment.clazzEnrolmentPersonUid AS enrolledPersonUid,\n                     (:time BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined AND ClazzEnrolment.clazzEnrolmentDateLeft) AS isActive\n                FROM ClazzEnrolment\n               WHERE ClazzEnrolment.clazzEnrolmentClazzUid = \n        CASE(:clazzUid)\n                         WHEN 0 THEN \n                                (SELECT CourseGroupSet.cgsClazzUid\n                                   FROM CourseGroupSet\n                                  WHERE CourseGroupSet.cgsUid = :cgsUid)\n                         ELSE :clazzUid\n                     END\n    \n                 AND ClazzEnrolment.clazzEnrolmentRole = 1000),\n        --Consolidate and removes any duplicates\n             EnrolledStudentPersonUids(enrolledPersonUid, isActive) AS\n             (SELECT DISTINCT AllEnrollmentsAndActiveStatus.enrolledPersonUid,\n                     (SELECT CAST(AllEnrollmentsInner.isActive AS INTEGER)\n                        FROM AllEnrollmentsAndActiveStatus AllEnrollmentsInner\n                       WHERE AllEnrollmentsInner.enrolledPersonUid = AllEnrollmentsAndActiveStatus.enrolledPersonUid\n                    ORDER BY AllEnrollmentsInner.isActive DESC\n                       LIMIT 1) AS isActive\n                FROM AllEnrollmentsAndActiveStatus)\n        \n        -- Now create a list with each students name, the coursegroupmember object if any and active status        \n        SELECT (Person.firstNames || ' ' || Person.lastName) AS name,\n               Person.personUid,\n               CourseGroupMember.*,\n               PersonPicture.*,\n               EnrolledStudentPersonUids.isActive AS enrolmentIsActive,\n               PersonPicture.personPictureThumbnailUri AS pictureUri\n          FROM EnrolledStudentPersonUids\n               JOIN Person\n                    ON Person.personUid = EnrolledStudentPersonUids.enrolledPersonUid \n               LEFT JOIN PersonPicture\n                         ON PersonPicture.personPictureUid = Person.personUid  \n               -- LEFT JOIN will use the most recent member in case of duplicate assignments eg if      \n               LEFT JOIN CourseGroupMember\n                         ON CourseGroupMember.cgmUid = \n                            (SELECT CourseGroupMember.cgmUid\n                               FROM CourseGroupMember\n                              WHERE CourseGroupMember.cgmPersonUid = EnrolledStudentPersonUids.enrolledPersonUid\n                                AND CourseGroupMember.cgmSetUid = :cgsUid \n                           ORDER BY CourseGroupMember.cgmLct DESC        \n                              LIMIT 1)\n         WHERE (:activeFilter = 0 OR :activeFilter = EnrolledStudentPersonUids.isActive)  \n               /* \n                * Begin permission check -  must have course view members permission, or active \n                * user must be in the same group \n                */ \n            AND (\n                    ((\n             /* If the accountPersonUid is the owner of the course, all permissions are granted */\n             (COALESCE(\n                          (SELECT _Clazz_Permission.clazzOwnerPersonUid \n                             FROM Clazz _Clazz_Permission\n                            WHERE _Clazz_Permission.clazzUid = :clazzUid), 0) = :accountPersonUid)\n              /* \n              If there is a CoursePermission entity that is for the course as per the clazzUid\n              parameter that is granted to the person directly or to the enrolmentRole that the \n              person has in the course, then permission is granted.\n              */              \n              OR EXISTS(SELECT CoursePermission.cpUid\n                          FROM CoursePermission\n                               \n        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson \n                        ON CoursePermission.cpToEnrolmentRole != 0\n                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = \n                           (SELECT COALESCE(\n                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid \n                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner\n                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid\n                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = :accountPersonUid\n                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive\n                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   \n                                     LIMIT 1), 0))\n    \n                         WHERE CoursePermission.cpClazzUid = :clazzUid\n                           AND (CoursePermission.cpToPersonUid = :accountPersonUid \n                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)\n                           AND (CoursePermission.cpPermissionsFlag & \n         8192\n                     \n        ) > 0)\n              OR EXISTS(SELECT SystemPermission.spUid\n                          FROM SystemPermission\n                         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n                           AND (SystemPermission.spPermissionsFlag & \n     8192\n                     \n        ) > 0)\n               )\n    )\n                  OR EXISTS(\n                     SELECT 1\n                       FROM CourseGroupMember _CourseGroupMemberForActivePerson\n                      WHERE _CourseGroupMemberForActivePerson.cgmPersonUid = :accountPersonUid\n                        AND _CourseGroupMemberForActivePerson.cgmGroupNumber = CourseGroupMember.cgmGroupNumber)     \n                 )\n      ORDER BY Person.firstNames, Person.lastName ASC\n    ")
  public actual abstract fun findByCourseGroupSetAndClazzAsFlow(
    cgsUid: Long,
    clazzUid: Long,
    time: Long,
    activeFilter: Int,
    accountPersonUid: Long,
  ): Flow<List<CourseGroupMemberAndName>>

  @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 ClazzEnrolment.clazzEnrolmentPersonUid\n                  FROM ClazzEnrolment\n                 WHERE ClazzEnrolment.clazzEnrolmentClazzUid = \n        CASE(:clazzUid)\n                         WHEN 0 THEN \n                                (SELECT CourseGroupSet.cgsClazzUid\n                                   FROM CourseGroupSet\n                                  WHERE CourseGroupSet.cgsUid = :cgsUid)\n                         ELSE :clazzUid\n                     END\n    )\n    ")
  public actual abstract suspend fun findByCourseGroupSetAndClazzAsFlowPersons(clazzUid: Long,
      cgsUid: Long): List<PersonAndPicture>

  @Query(`value` =
      "\n        SELECT ClazzEnrolment.*\n          FROM ClazzEnrolment\n         WHERE ClazzEnrolment.clazzEnrolmentClazzUid = \n        CASE(:clazzUid)\n                         WHEN 0 THEN \n                                (SELECT CourseGroupSet.cgsClazzUid\n                                   FROM CourseGroupSet\n                                  WHERE CourseGroupSet.cgsUid = :cgsUid)\n                         ELSE :clazzUid\n                     END\n    \n    ")
  public actual abstract suspend fun findByCourseGroupSetAndClazzAsFlowEnrolments(clazzUid: Long,
      cgsUid: Long): List<ClazzEnrolment>

  @Insert(
    onConflict = 1,
    entity = Any::class,
  )
  public actual abstract suspend fun upsertListAsync(list: List<CourseGroupMember>)

  @Query(`value` =
      "\n        SELECT CourseGroupMember.*, Person.*\n          FROM CourseGroupMember\n               JOIN Person \n                    ON Person.personUid = CourseGroupMember.cgmPersonUid\n         WHERE (    CourseGroupMember.cgmSetUid = :courseGroupSetUid\n                AND CourseGroupMember.cgmGroupNumber = :groupNum)\n           AND (    /* Grant permission where the active person is in the group */ \n                    EXISTS(SELECT 1\n                             FROM CourseGroupMember CourseGroupMemberInternal\n                            WHERE CourseGroupMemberInternal.cgmSetUid = :courseGroupSetUid\n                              AND CourseGroupMemberInternal.cgmPersonUid = :accountPersonUid)\n                    /* Grant permission where the activepersonuid is in a group assigned to mark this group */\n                 OR EXISTS(SELECT 1\n                             FROM PeerReviewerAllocation\n                            WHERE PeerReviewerAllocation.praAssignmentUid = :assignmentUid\n                              AND PeerReviewerAllocation.praMarkerSubmitterUid = :groupNum\n                              AND EXISTS(SELECT 1\n                                           FROM CourseGroupMember CourseGroupMemberInternal\n                                          WHERE CourseGroupMemberInternal.cgmSetUid = PeerReviewerAllocation.praMarkerSubmitterUid\n                                            AND CourseGroupMemberInternal.cgmPersonUid = :accountPersonUid)) \n                    /* Grant permission where the active person has the select person permission for the class */                        \n                 OR ((\n             /* If the accountPersonUid is the owner of the course, all permissions are granted */\n             (COALESCE(\n                          (SELECT _Clazz_Permission.clazzOwnerPersonUid \n                             FROM Clazz _Clazz_Permission\n                            WHERE _Clazz_Permission.clazzUid = :clazzUid), 0) = :accountPersonUid)\n              /* \n              If there is a CoursePermission entity that is for the course as per the clazzUid\n              parameter that is granted to the person directly or to the enrolmentRole that the \n              person has in the course, then permission is granted.\n              */              \n              OR EXISTS(SELECT CoursePermission.cpUid\n                          FROM CoursePermission\n                               \n        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson \n                        ON CoursePermission.cpToEnrolmentRole != 0\n                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = \n                           (SELECT COALESCE(\n                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid \n                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner\n                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid\n                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = :accountPersonUid\n                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive\n                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   \n                                     LIMIT 1), 0))\n    \n                         WHERE CoursePermission.cpClazzUid = :clazzUid\n                           AND (CoursePermission.cpToPersonUid = :accountPersonUid \n                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)\n                           AND (CoursePermission.cpPermissionsFlag & \n         8192\n                     \n        ) > 0)\n              OR EXISTS(SELECT SystemPermission.spUid\n                          FROM SystemPermission\n                         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n                           AND (SystemPermission.spPermissionsFlag & \n     8192\n                     \n        ) > 0)\n               )\n    )    \n               )\n               \n    ")
  public actual abstract suspend fun findByCourseGroupSetAndGroupNumAsync(
    courseGroupSetUid: Long,
    groupNum: Int,
    clazzUid: Long,
    assignmentUid: Long,
    accountPersonUid: Long,
  ): List<CourseGroupMemberAndPerson>
}
