package com.ustadmobile.core.db.dao

import androidx.paging.DataSource
import androidx.room.Dao
import androidx.room.Query
import com.ustadmobile.lib.db.entities.LearnerGroupMember
import com.ustadmobile.lib.db.entities.LearnerGroupMemberWithPerson
import kotlin.Int
import kotlin.Long
import kotlin.Unit
import kotlin.collections.List

@Dao
public actual abstract class LearnerGroupMemberDao : BaseDao<LearnerGroupMember> {
  @Query(`value` =
      "\n     REPLACE INTO LearnerGroupMemberReplicate(lgmPk, lgmDestination)\n      SELECT DISTINCT LearnerGroupMember.learnerGroupMemberUid AS lgmPk,\n             :newNodeId AS lgmDestination\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                  64\n                  \n                                                    ) > 0\n            JOIN Person \n                 ON \n                ((ScopedGrant.sgTableId = -2\n                    AND ScopedGrant.sgEntityUid = -2)\n                 OR (ScopedGrant.sgTableId = 9\n                    AND ScopedGrant.sgEntityUid = Person.personUid)\n                 OR (ScopedGrant.sgTableId = 6       \n                    AND Person.personUid IN (\n                        SELECT DISTINCT clazzEnrolmentPersonUid\n                          FROM ClazzEnrolment\n                         WHERE clazzEnrolmentClazzUid =ScopedGrant.sgEntityUid \n                           AND ClazzEnrolment.clazzEnrolmentActive))\n                 OR (ScopedGrant.sgTableId = 164\n                    AND Person.personUid IN (\n                        SELECT DISTINCT schoolMemberPersonUid\n                          FROM SchoolMember\n                         WHERE schoolMemberSchoolUid = ScopedGrant.sgEntityUid\n                           AND schoolMemberActive))\n                           )    \n        \n        \n             JOIN LearnerGroupMember\n                  ON LearnerGroupMember.learnerGroupMemberPersonUid = Person.personUid\n       WHERE UserSession.usClientNodeId = :newNodeId\n         AND UserSession.usStatus = 1\n         AND LearnerGroupMember.learnerGroupMemberLct != COALESCE(\n             (SELECT lgmVersionId\n                FROM LearnerGroupMemberReplicate\n               WHERE lgmPk = LearnerGroupMember.learnerGroupMemberUid\n                 AND lgmDestination = :newNodeId), 0) \n      /*psql ON CONFLICT(lgmPk, lgmDestination) DO UPDATE\n             SET lgmPending = true\n      */       \n    ")
  public actual abstract suspend fun replicateOnNewNode(newNodeId: Long): Unit

  @Query(`value` =
      "\n REPLACE INTO LearnerGroupMemberReplicate(lgmPk, lgmDestination)\n  SELECT DISTINCT LearnerGroupMember.learnerGroupMemberUid AS lgmUid,\n         UserSession.usClientNodeId AS lgmDestination\n    FROM ChangeLog\n         JOIN LearnerGroupMember\n              ON ChangeLog.chTableId = 300\n                 AND ChangeLog.chEntityPk = LearnerGroupMember.learnerGroupMemberUid\n         JOIN Person\n              ON Person.personUid = LearnerGroupMember.learnerGroupMemberPersonUid\n         \n            JOIN ScopedGrant \n                   ON \n            ((ScopedGrant.sgTableId = -2\n                    AND ScopedGrant.sgEntityUid = -2)\n                 OR (ScopedGrant.sgTableId = 9\n                    AND ScopedGrant.sgEntityUid = Person.personUid)\n                 OR (ScopedGrant.sgTableId = 6       \n                    AND ScopedGrant.sgEntityUid IN (\n                        SELECT DISTINCT clazzEnrolmentClazzUid\n                          FROM ClazzEnrolment\n                         WHERE clazzEnrolmentPersonUid = Person.personUid \n                           AND ClazzEnrolment.clazzEnrolmentActive))\n                 OR (ScopedGrant.sgTableId = 164\n                    AND ScopedGrant.sgEntityUid IN (\n                        SELECT DISTINCT schoolMemberSchoolUid\n                          FROM SchoolMember\n                         WHERE schoolMemberPersonUid = Person.personUid\n                           AND schoolMemberActive))\n                           )\n        \n                   AND (ScopedGrant.sgPermissions & \n        \n              64\n              \n                                                     ) > 0\n             JOIN PersonGroupMember AS PrsGrpMbr\n                   ON ScopedGrant.sgGroupUid = PrsGrpMbr.groupMemberGroupUid\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 LearnerGroupMember.learnerGroupMemberLct != COALESCE(\n         (SELECT lgmVersionId\n            FROM LearnerGroupMemberReplicate\n           WHERE lgmPk = LearnerGroupMember.learnerGroupMemberUid\n             AND lgmDestination = UserSession.usClientNodeId), 0)\n /*psql ON CONFLICT(lgmPk, lgmDestination) DO UPDATE\n     SET lgmPending = true\n  */\n    ")
  public actual abstract suspend fun replicateOnChange(): Unit

  @Query(`value` =
      "SELECT LearnerGroupMember.*, Person.* FROM LearnerGroupMember \n        LEFT JOIN Person ON LearnerGroupMember.learnerGroupMemberPersonUid = Person.personUid \n        LEFT JOIN GroupLearningSession ON \n    GroupLearningSession.groupLearningSessionLearnerGroupUid = LearnerGroupMember.learnerGroupMemberLgUid \n    WHERE GroupLearningSession.groupLearningSessionLearnerGroupUid = :learnerGroupUid \n    AND GroupLearningSession.groupLearningSessionContentUid = :contentEntryUid \n    ORDER BY learnerGroupMemberRole ASC\n    ")
  public actual abstract fun findLearnerGroupMembersByGroupIdAndEntry(learnerGroupUid: Long,
      contentEntryUid: Long): DataSource.Factory<Int, LearnerGroupMemberWithPerson>

  @Query(`value` =
      "SELECT LearnerGroupMember.*, Person.* FROM LearnerGroupMember \n        LEFT JOIN Person ON LearnerGroupMember.learnerGroupMemberPersonUid = Person.personUid \n        LEFT JOIN GroupLearningSession ON \n    GroupLearningSession.groupLearningSessionLearnerGroupUid = LearnerGroupMember.learnerGroupMemberLgUid \n    WHERE GroupLearningSession.groupLearningSessionLearnerGroupUid = :learnerGroupUid \n    AND GroupLearningSession.groupLearningSessionContentUid = :contentEntryUid \n    ORDER BY learnerGroupMemberRole ASC\n    ")
  public actual abstract suspend
      fun findLearnerGroupMembersByGroupIdAndEntryList(learnerGroupUid: Long,
      contentEntryUid: Long): List<LearnerGroupMemberWithPerson>
}
