package com.ustadmobile.core.db.dao

import androidx.lifecycle.LiveData
import androidx.room.Dao
import androidx.room.Query
import androidx.room.Update
import com.ustadmobile.lib.db.entities.PersonGroup
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.Unit

@Dao
public actual abstract class PersonGroupDao : BaseDao<PersonGroup> {
  @Query(`value` =
      "\n     REPLACE INTO PersonGroupReplicate(pgPk, pgDestination)\n      SELECT DISTINCT \n      PersonGroup.groupUid AS pgUid\n    ,\n             :newNodeId AS pgDestination\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             JOIN PersonGroup\n                    ON PersonGroup.groupUid = PersonsWithPerm_GroupMember.groupMemberGroupUid\n       WHERE UserSession.usClientNodeId = :newNodeId\n         AND UserSession.usStatus = 1\n         AND \n      PersonGroup.groupLct != COALESCE(\n         (SELECT pgVersionId\n            FROM PersonGroupReplicate\n           WHERE pgPk = PersonGroup.groupUid\n             AND pgDestination = UserSession.usClientNodeId), 0)\n    \n      /*psql ON CONFLICT(pgPk, pgDestination) DO UPDATE\n             SET pgPending = true\n      */       \n    ")
  public actual abstract suspend fun replicateOnNewNode(newNodeId: Long): Unit

  @Query(`value` =
      "\n REPLACE INTO PersonGroupReplicate(pgPk, pgDestination)\n  SELECT DISTINCT \n      PersonGroup.groupUid AS pgUid\n     ,\n         UserSession.usClientNodeId AS pgDestination\n    FROM ChangeLog\n         JOIN PersonGroup\n              ON ChangeLog.chTableId = 43\n                AND ChangeLog.chEntityPk = PersonGroup.groupUid\n         JOIN PersonGroupMember\n              ON PersonGroupMember.groupMemberGroupUid = PersonGroup.groupUid\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 \n            UserSession.usClientNodeId != (\n                 SELECT nodeClientId \n                   FROM SyncNode\n                  LIMIT 1)\n        \n     AND \n      PersonGroup.groupLct != COALESCE(\n         (SELECT pgVersionId\n            FROM PersonGroupReplicate\n           WHERE pgPk = PersonGroup.groupUid\n             AND pgDestination = UserSession.usClientNodeId), 0)\n    \n /*psql ON CONFLICT(pgPk, pgDestination) DO UPDATE\n     SET pgPending = true\n  */               \n    ")
  public actual abstract suspend fun replicateOnChange(): Unit

  @Query(`value` =
      "\n REPLACE INTO PersonGroupReplicate(pgPk, pgDestination)\n       SELECT DISTINCT PersonGroup.groupUid AS pgUid,\n              UserSession.usClientNodeId AS pgDestination\n         FROM ChangeLog\n              JOIN PersonGroup\n                   ON ChangeLog.chTableId = 43\n                      AND ChangeLog.chEntityPk = PersonGroup.groupUid     \n         JOIN ScopedGrant ScopedGrantEntity\n              ON PersonGroup.groupUid = 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 PersonGroup.groupLct != COALESCE(\n              (SELECT pgVersionId\n                 FROM PersonGroupReplicate\n                WHERE pgPk = PersonGroup.groupUid\n                  AND pgDestination = UserSession.usClientNodeId), 0)\n    /*psql ON CONFLICT(pgPk, pgDestination) DO UPDATE\n     SET pgPending = true\n    */               \n    ")
  public actual abstract suspend fun replicateOnChangeClazzBased(): Unit

  @Query(`value` =
      "\n REPLACE INTO PersonGroupReplicate(pgPk, pgDestination)\n  SELECT DISTINCT \n      PersonGroup.groupUid AS pgUid\n     ,\n         UserSession.usClientNodeId AS pgDestination\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 PersonGroup\n              ON ScopedGrantEntity.sgGroupUid = PersonGroup.groupUid\n   WHERE UserSession.usClientNodeId = :newNodeId\n         AND UserSession.usStatus = 1\n         AND PersonGroup.groupLct != COALESCE(\n              (SELECT pgVersionId\n                 FROM PersonGroupReplicate\n                WHERE pgPk = PersonGroup.groupUid\n                  AND pgDestination = UserSession.usClientNodeId), 0) \n      /*psql ON CONFLICT(pgPk, pgDestination) DO UPDATE\n             SET pgPending = true\n      */      \n    ")
  public actual abstract suspend fun replicateOnNewNodeClazzBased(newNodeId: Long): Unit

  @Query(`value` =
      "\n REPLACE INTO PersonGroupReplicate(pgPk, pgDestination)\n       SELECT DISTINCT PersonGroup.groupUid AS pgUid,\n              UserSession.usClientNodeId AS pgDestination\n         FROM ChangeLog\n              JOIN PersonGroup\n                   ON ChangeLog.chTableId = 43\n                      AND ChangeLog.chEntityPk = PersonGroup.groupUid     \n         JOIN ScopedGrant ScopedGrantEntity\n              ON PersonGroup.groupUid = ScopedGrantEntity.sgGroupUid\n         JOIN Person \n              ON ScopedGrantEntity.sgTableId = 9\n                 AND ScopedGrantEntity.sgEntityUid = 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 PersonGroup.groupLct != COALESCE(\n              (SELECT pgVersionId\n                 FROM PersonGroupReplicate\n                WHERE pgPk = PersonGroup.groupUid\n                  AND pgDestination = UserSession.usClientNodeId), 0)\n    /*psql ON CONFLICT(pgPk, pgDestination) DO UPDATE\n     SET pgPending = true\n    */                     \n    ")
  public actual abstract suspend fun replicateOnChangePersonBased(): Unit

  @Query(`value` =
      "\n REPLACE INTO PersonGroupReplicate(pgPk, pgDestination)\n  SELECT DISTINCT \n      PersonGroup.groupUid AS pgUid\n     ,\n         UserSession.usClientNodeId AS pgDestination\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 ScopedGrant ScopedGrantEntity\n              ON Person.personUid = ScopedGrantEntity.sgEntityUid\n                 AND ScopedGrantEntity.sgTableId = 9\n         JOIN PersonGroup\n              ON ScopedGrantEntity.sgGroupUid = PersonGroup.groupUid\n   WHERE UserSession.usClientNodeId = :newNodeId\n         AND UserSession.usStatus = 1\n         AND PersonGroup.groupLct != COALESCE(\n              (SELECT pgVersionId\n                 FROM PersonGroupReplicate\n                WHERE pgPk = PersonGroup.groupUid\n                  AND pgDestination = UserSession.usClientNodeId), 0) \n      /*psql ON CONFLICT(pgPk, pgDestination) DO UPDATE\n             SET pgPending = true\n      */      \n    ")
  public actual abstract suspend fun replicateOnNewNodePersonBased(newNodeId: Long): Unit

  @Query(`value` =
      "\n REPLACE INTO PersonGroupReplicate(pgPk, pgDestination)\n       SELECT DISTINCT PersonGroup.groupUid AS pgUid,\n              UserSession.usClientNodeId AS pgDestination\n         FROM ChangeLog\n              JOIN PersonGroup\n                   ON ChangeLog.chTableId = 43\n                      AND ChangeLog.chEntityPk = PersonGroup.groupUid     \n         JOIN ScopedGrant ScopedGrantEntity\n              ON PersonGroup.groupUid = 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 PersonGroup.groupLct != COALESCE(\n              (SELECT pgVersionId\n                 FROM PersonGroupReplicate\n                WHERE pgPk = PersonGroup.groupUid\n                  AND pgDestination = UserSession.usClientNodeId), 0)\n    /*psql ON CONFLICT(pgPk, pgDestination) DO UPDATE\n     SET pgPending = true\n    */                     \n    ")
  public actual abstract suspend fun replicateOnChangeSchoolBased(): Unit

  @Query(`value` =
      "\n REPLACE INTO PersonGroupReplicate(pgPk, pgDestination)\n  SELECT DISTINCT \n      PersonGroup.groupUid AS pgUid\n     ,\n         UserSession.usClientNodeId AS pgDestination\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 = 9\n         JOIN PersonGroup\n              ON ScopedGrantEntity.sgGroupUid = PersonGroup.groupUid\n   WHERE UserSession.usClientNodeId = :newNodeId\n         AND UserSession.usStatus = 1\n         AND PersonGroup.groupLct != COALESCE(\n              (SELECT pgVersionId\n                 FROM PersonGroupReplicate\n                WHERE pgPk = PersonGroup.groupUid\n                  AND pgDestination = UserSession.usClientNodeId), 0) \n      /*psql ON CONFLICT(pgPk, pgDestination) DO UPDATE\n             SET pgPending = true\n      */      \n    ")
  public actual abstract suspend fun replicateOnNewNodeSchoolBased(newNodeId: Long): Unit

  @Query(`value` = "SELECT * FROM PersonGroup WHERE groupUid = :uid")
  public actual abstract fun findByUid(uid: Long): PersonGroup?

  @Query(`value` = "SELECT * FROM PersonGroup WHERE groupUid = :uid")
  public actual abstract suspend fun findByUidAsync(uid: Long): PersonGroup?

  @Query(`value` = "SELECT * FROM PersonGroup WHERE groupUid = :uid")
  public actual abstract fun findByUidLive(uid: Long): LiveData<PersonGroup?>

  @Update(onConflict = 3)
  public actual abstract suspend fun updateAsync(entity: PersonGroup): Int

  @Query(`value` =
      "\n        Select CASE\n               WHEN Person.firstNames IS NOT NULL THEN Person.firstNames\n               ELSE PersonGroup.groupName \n               END AS name\n          FROM PersonGroup\n               LEFT JOIN Person\n                         ON Person.personGroupUid = PersonGroup.groupUid\n         WHERE PersonGroup.groupUid = :groupUid\n         LIMIT 1\n    ")
  public actual abstract suspend fun findNameByGroupUid(groupUid: Long): String?
}
