package com.ustadmobile.core.db.dao

import androidx.room.Dao
import androidx.room.Query
import com.ustadmobile.lib.db.entities.PersonGroupMember
import kotlin.Boolean
import kotlin.Int
import kotlin.Long
import kotlin.Unit
import kotlin.collections.List

@Dao
public actual abstract class PersonGroupMemberDao : BaseDao<PersonGroupMember> {
  @Query(`value` =
      "\n     REPLACE INTO PersonGroupMemberReplicate(pgmPk, pgmDestination)\n      SELECT DISTINCT PersonGroupMember.groupMemberUid AS pgmUid,\n             :newNodeId AS pgmDestination\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 PersonGroupMember PersonsWithPerm_GroupMember\n                    ON PersonsWithPerm_GroupMember.groupMemberPersonUid = Person.personUid\n       WHERE UserSession.usClientNodeId = :newNodeId\n         AND UserSession.usStatus = 1\n         AND PersonGroupMember.groupMemberLct != COALESCE(\n             (SELECT pgmVersionId\n                FROM PersonGroupMemberReplicate\n               WHERE pgmPk = PersonGroupMember.groupMemberUid\n                 AND pgmDestination = :newNodeId), 0) \n      /*psql ON CONFLICT(pgmPk, pgmDestination) DO UPDATE\n             SET pgmPending = true\n      */       \n    ")
  public actual abstract suspend fun replicateOnNewNode(newNodeId: Long): Unit

  @Query(`value` =
      "\n REPLACE INTO PersonGroupMemberReplicate(pgmPk, pgmDestination)\n  SELECT DISTINCT PersonGroupMember.groupMemberUid AS pgmUid,\n         UserSession.usClientNodeId AS pgmDestination\n    FROM ChangeLog\n         JOIN PersonGroupMember\n             ON ChangeLog.chTableId = 44\n                AND ChangeLog.chEntityPk = PersonGroupMember.groupMemberUid\n         JOIN Person\n              ON PersonGroupMember.groupMemberPersonUid = Person.personUid\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 PersonGroupMember.groupMemberLct != COALESCE(\n         (SELECT pgmVersionId\n            FROM PersonGroupMemberReplicate\n           WHERE pgmPk = PersonGroupMember.groupMemberUid\n             AND pgmDestination = UserSession.usClientNodeId), 0)\n     /*psql ON CONFLICT(pgmPk, pgmDestination) DO UPDATE\n         SET pgmPending = true\n      */               \n     ")
  public actual abstract suspend fun replicateOnChange(): Unit

  @Query(`value` =
      "\n REPLACE INTO PersonGroupMemberReplicate(pgmPk, pgmDestination)\n  SELECT DISTINCT PersonGroupMember.groupMemberUid AS pgmUid,\n         UserSession.usClientNodeId AS pgmDestination\n    FROM ChangeLog\n         JOIN PersonGroupMember\n             ON ChangeLog.chTableId = 44\n                AND ChangeLog.chEntityPk = PersonGroupMember.groupMemberUid\n         JOIN ScopedGrant ScopedGrantEntity\n              ON PersonGroupMember.groupMemberUid = ScopedGrantEntity.sgGroupUid\n         JOIN Clazz \n              ON ScopedGrantEntity.sgTableId = 6\n                 AND ScopedGrantEntity.sgEntityUid = Clazz.clazzUid\n         \n            JOIN ScopedGrant\n                 ON \n            ((ScopedGrant.sgTableId = -2\n                                AND ScopedGrant.sgEntityUid = -2)\n                            OR (ScopedGrant.sgTableId = 6\n                                AND ScopedGrant.sgEntityUid = Clazz.clazzUid)\n                            OR (ScopedGrant.sgTableId = 164\n                                AND ScopedGrant.sgEntityUid = Clazz.clazzSchoolUid))\n        \n                    AND (ScopedGrant.sgPermissions & \n        \n              2\n              \n              \n                                                       ) > 0\n             JOIN PersonGroupMember AS PrsGrpMbr\n                   ON ScopedGrant.sgGroupUid = PrsGrpMbr.groupMemberGroupUid\n                                               \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 PersonGroupMember.groupMemberLct != COALESCE(\n              (SELECT pgmVersionId\n                 FROM PersonGroupMemberReplicate\n                WHERE pgmPk = PersonGroupMember.groupMemberUid\n                  AND pgmDestination = UserSession.usClientNodeId), 0)\n  /*psql ON CONFLICT(pgmPk, pgmDestination) DO UPDATE\n     SET pgmPending = true\n    */                   \n    ")
  public actual abstract suspend fun replicateOnChangeClazzBased(): Unit

  @Query(`value` =
      "\n REPLACE INTO PersonGroupMemberReplicate(pgmPk, pgmDestination)\n      SELECT DISTINCT PersonGroupMember.groupMemberUid AS pgmUid,\n             :newNodeId AS pgmDestination\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        \n                  2 \n                  \n                       ) > 0\n               JOIN Clazz \n                    ON \n            ((ScopedGrant.sgTableId = -2\n                                AND ScopedGrant.sgEntityUid = -2)\n                            OR (ScopedGrant.sgTableId = 6\n                                AND ScopedGrant.sgEntityUid = Clazz.clazzUid)\n                            OR (ScopedGrant.sgTableId = 164\n                                AND ScopedGrant.sgEntityUid = Clazz.clazzSchoolUid))\n        \n        \n             JOIN ScopedGrant ScopedGrantEntity\n                  ON Clazz.clazzUid = ScopedGrantEntity.sgEntityUid\n                     AND ScopedGrantEntity.sgTableId = 6\n             JOIN PersonGroupMember PersonGroupMemberEntity\n                  ON PersonGroupMemberEntity.groupMemberGroupUid = ScopedGrantEntity.sgGroupUid\n       WHERE UserSession.usClientNodeId = :newNodeId\n         AND UserSession.usStatus = 1  \n         AND PersonGroupMember.groupMemberLct != COALESCE(\n             (SELECT pgmVersionId\n                FROM PersonGroupMemberReplicate\n               WHERE pgmPk = PersonGroupMember.groupMemberUid\n                 AND pgmDestination = :newNodeId), 0) \n      /*psql ON CONFLICT(pgmPk, pgmDestination) DO UPDATE\n             SET pgmPending = true\n      */                \n    ")
  public actual abstract suspend fun replicateOnNewNodeClazzBased(newNodeId: Long): Unit

  @Query(`value` =
      "\n REPLACE INTO PersonGroupMemberReplicate(pgmPk, pgmDestination)\n  SELECT DISTINCT PersonGroupMember.groupMemberUid AS pgmUid,\n         UserSession.usClientNodeId AS pgmDestination\n    FROM ChangeLog\n         JOIN PersonGroupMember\n             ON ChangeLog.chTableId = 44\n                AND ChangeLog.chEntityPk = PersonGroupMember.groupMemberUid\n         JOIN ScopedGrant ScopedGrantEntity\n              ON PersonGroupMember.groupMemberUid = ScopedGrantEntity.sgGroupUid\n         JOIN School \n              ON ScopedGrantEntity.sgTableId = 164\n                 AND ScopedGrantEntity.sgEntityUid = School.schoolUid\n         \n            JOIN ScopedGrant\n                 ON \n            ((ScopedGrant.sgTableId = -2\n                    AND ScopedGrant.sgEntityUid = -2)\n                OR (ScopedGrant.sgTableId = 164\n                    AND ScopedGrant.sgEntityUid = School.schoolUid))\n        \n                        AND (SCopedGrant.sgPermissions &\n        \n              536870912\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        \n   WHERE UserSession.usClientNodeId != (\n                SELECT nodeClientId \n                  FROM SyncNode\n                 LIMIT 1)\n     AND PersonGroupMember.groupMemberLct != COALESCE(\n              (SELECT pgmVersionId\n                 FROM PersonGroupMemberReplicate\n                WHERE pgmPk = PersonGroupMember.groupMemberUid\n                  AND pgmDestination = UserSession.usClientNodeId), 0)\n  /*psql ON CONFLICT(pgmPk, pgmDestination) DO UPDATE\n     SET pgmPending = true\n    */                   \n    ")
  public actual abstract suspend fun replicateOnChangeSchoolBased(): Unit

  @Query(`value` =
      "\n REPLACE INTO PersonGroupMemberReplicate(pgmPk, pgmDestination)\n      SELECT DISTINCT PersonGroupMember.groupMemberUid AS pgmUid,\n             :newNodeId AS pgmDestination\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                 \n        \n                  536870912\n                  \n                    ) > 0\n            JOIN School\n                 ON \n            ((ScopedGrant.sgTableId = -2\n                    AND ScopedGrant.sgEntityUid = -2)\n                OR (ScopedGrant.sgTableId = 164\n                    AND ScopedGrant.sgEntityUid = School.schoolUid))\n        \n        \n             JOIN ScopedGrant ScopedGrantEntity\n                  ON School.schoolUid = ScopedGrantEntity.sgEntityUid\n                     AND ScopedGrantEntity.sgTableId = 164\n             JOIN PersonGroupMember PersonGroupMemberEntity\n                  ON PersonGroupMemberEntity.groupMemberGroupUid = ScopedGrantEntity.sgGroupUid\n       WHERE UserSession.usClientNodeId = :newNodeId\n         AND UserSession.usStatus = 1  \n         AND PersonGroupMember.groupMemberLct != COALESCE(\n             (SELECT pgmVersionId\n                FROM PersonGroupMemberReplicate\n               WHERE pgmPk = PersonGroupMember.groupMemberUid\n                 AND pgmDestination = :newNodeId), 0) \n      /*psql ON CONFLICT(pgmPk, pgmDestination) DO UPDATE\n             SET pgmPending = true\n      */                \n    ")
  public actual abstract suspend fun replicateOnNewNodeSchoolBased(newNodeId: Long): Unit

  @Query(`value` =
      "SELECT * FROM PersonGroupMember WHERE groupMemberPersonUid = :personUid AND PersonGroupMember.groupMemberActive")
  public actual abstract suspend fun findAllGroupWherePersonIsIn(personUid: Long):
      List<PersonGroupMember>

  @Query(`value` =
      "SELECT * FROM PersonGroupMember WHERE groupMemberGroupUid = :groupUid \n             AND groupMemberPersonUid = :personUid AND PersonGroupMember.groupMemberActive")
  public actual abstract suspend fun checkPersonBelongsToGroup(groupUid: Long, personUid: Long):
      List<PersonGroupMember>

  @Query(`value` =
      "\n        UPDATE PersonGroupMember \n           SET groupMemberGroupUid = :newGroup,\n               groupMemberLct = :changeTime\n         WHERE groupMemberPersonUid = :personUid \n           AND groupMemberGroupUid = :oldGroup \n           AND PersonGroupMember.groupMemberActive")
  public actual abstract suspend fun moveGroupAsync(
    personUid: Long,
    newGroup: Long,
    oldGroup: Long,
    changeTime: Long,
  ): Int

  @Query(`value` =
      "\n        UPDATE PersonGroupMember \n           SET groupMemberActive = :activeStatus,\n               groupMemberLct = :updateTime\n        WHERE groupMemberPersonUid = :personUid \n          AND groupMemberGroupUid = :groupUid \n          AND PersonGroupMember.groupMemberActive")
  public actual abstract suspend fun updateGroupMemberActive(
    activeStatus: Boolean,
    personUid: Long,
    groupUid: Long,
    updateTime: Long,
  ): Unit

  @Query(`value` =
      "\n        SELECT PersonGroupMember.*\n          FROM PersonGroupMember\n         WHERE PersonGroupMember.groupMemberPersonUid = :personUid\n           AND PersonGroupMember.groupMemberGroupUid = :groupUid\n    ")
  public actual abstract suspend fun findByPersonUidAndGroupUid(personUid: Long, groupUid: Long):
      PersonGroupMember?
}
