package com.ustadmobile.core.db.dao

import androidx.lifecycle.LiveData
import androidx.paging.DataSource
import androidx.room.Dao
import androidx.room.Insert
import androidx.room.Query
import androidx.room.Update
import com.ustadmobile.lib.db.entities.ScopedGrant
import com.ustadmobile.lib.db.entities.ScopedGrantAndName
import com.ustadmobile.lib.db.entities.ScopedGrantWithName
import kotlin.Int
import kotlin.Long
import kotlin.Unit
import kotlin.collections.List

@Dao
public actual abstract class ScopedGrantDao {
  @Query(`value` =
      "\n     REPLACE INTO ScopedGrantReplicate(sgPk, sgDestination)\n      SELECT DISTINCT ScopedGrantWithPerm.sgUid AS sgPk,\n             :newNodeId AS sgDestination\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 ScopedGrant ScopedGrantWithPerm\n                    ON PersonsWithPerm_GroupMember.groupMemberGroupUid = ScopedGrantWithPerm.sgGroupUid\n       WHERE UserSession.usClientNodeId = :newNodeId\n         AND UserSession.usStatus = 1\n         AND ScopedGrantWithPerm.sgLct != COALESCE(\n             (SELECT sgVersionId\n                FROM ScopedGrantReplicate\n               WHERE sgPk = ScopedGrantWithPerm.sgUid\n                 AND sgDestination = :newNodeId), 0) \n      /*psql ON CONFLICT(sgPk, sgDestination) DO UPDATE\n             SET sgPending = true\n      */       \n    ")
  public actual abstract suspend fun replicateOnNewNode(newNodeId: Long): Unit

  @Query(`value` =
      "\n REPLACE INTO ScopedGrantReplicate(sgPk, sgDestination)\n  SELECT DISTINCT ScopedGrantEntity.sgUid AS sgPk,\n         UserSession.usClientNodeId AS sgDestination\n    FROM ChangeLog\n         JOIN ScopedGrant ScopedGrantEntity\n             ON ChangeLog.chTableId = 48\n                AND ChangeLog.chEntityPk = ScopedGrantEntity.sgUid\n         JOIN PersonGroupMember\n              ON PersonGroupMember.groupMemberGroupUid = ScopedGrantEntity.sgGroupUid\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 ScopedGrantEntity.sgLct != COALESCE(\n         (SELECT sgVersionId\n            FROM ScopedGrantReplicate\n           WHERE sgPk = ScopedGrantEntity.sgUid\n             AND sgDestination = UserSession.usClientNodeId), 0)\n /*psql ON CONFLICT(sgPk, sgDestination) DO UPDATE\n     SET sgPending = true\n  */               \n    ")
  public actual abstract suspend fun replicateOnChange(): Unit

  @Query(`value` =
      "\n REPLACE INTO ScopedGrantReplicate(sgPk, sgDestination)\n  SELECT DISTINCT ScopedGrantEntity.sgUid AS sgPk,\n         UserSession.usClientNodeId AS sgDestination\n    FROM ChangeLog\n         JOIN ScopedGrant ScopedGrantEntity\n              ON ChangeLog.chTableId = 48\n                 AND ChangeLog.chEntityPk = ScopedGrantEntity.sgUid\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 ScopedGrantEntity.sgLct != COALESCE(\n         (SELECT sgVersionId\n            FROM ScopedGrantReplicate\n           WHERE sgPk = ScopedGrantEntity.sgUid\n             AND sgDestination = UserSession.usClientNodeId), 0)\n /*psql ON CONFLICT(sgPk, sgDestination) DO UPDATE\n     SET sgPending = true\n  */               \n    ")
  public actual abstract suspend fun replicateOnChangeClazzBased(): Unit

  @Query(`value` =
      "\n     REPLACE INTO ScopedGrantReplicate(sgPk, sgDestination)\n      SELECT DISTINCT ScopedGrantEntity.sgUid AS sgPk,\n             :newNodeId AS sgDestination\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       WHERE UserSession.usClientNodeId = :newNodeId\n         AND UserSession.usStatus = 1\n         AND ScopedGrantEntity.sgLct != COALESCE(\n             (SELECT sgVersionId\n                FROM ScopedGrantReplicate\n               WHERE sgPk = ScopedGrantEntity.sgUid\n                 AND sgDestination = :newNodeId), 0) \n      /*psql ON CONFLICT(sgPk, sgDestination) DO UPDATE\n             SET sgPending = true\n      */\n    ")
  public actual abstract suspend fun replicateOnNewNodeClazzBased(newNodeId: Long): Unit

  @Query(`value` =
      "\n REPLACE INTO ScopedGrantReplicate(sgPk, sgDestination)\n  SELECT DISTINCT ScopedGrantEntity.sgUid AS sgPk,\n         UserSession.usClientNodeId AS sgDestination\n    FROM ChangeLog\n         JOIN ScopedGrant ScopedGrantEntity\n              ON ChangeLog.chTableId = 48\n                 AND ChangeLog.chEntityPk = ScopedGrantEntity.sgUid\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 ScopedGrantEntity.sgLct != COALESCE(\n         (SELECT sgVersionId\n            FROM ScopedGrantReplicate\n           WHERE sgPk = ScopedGrantEntity.sgUid\n             AND sgDestination = UserSession.usClientNodeId), 0)\n /*psql ON CONFLICT(sgPk, sgDestination) DO UPDATE\n     SET sgPending = true\n  */                    \n    ")
  public actual abstract suspend fun replicateOnChangePersonBased(): Unit

  @Query(`value` =
      "\n REPLACE INTO ScopedGrantReplicate(sgPk, sgDestination)\n      SELECT DISTINCT ScopedGrantEntity.sgUid AS sgPk,\n             :newNodeId AS sgDestination\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 ScopedGrantEntity.sgTableId = 9\n                       AND ScopedGrantEntity.sgEntityUid = Person.personUid \n       WHERE UserSession.usClientNodeId = :newNodeId\n         AND UserSession.usStatus = 1 \n         AND ScopedGrantEntity.sgLct != COALESCE(\n             (SELECT sgVersionId\n                FROM ScopedGrantReplicate\n               WHERE sgPk = ScopedGrantEntity.sgUid\n                 AND sgDestination = UserSession.usClientNodeId), 0)\n /*psql ON CONFLICT(sgPk, sgDestination) DO UPDATE\n     SET sgPending = true\n  */                                                       \n    ")
  public actual abstract suspend fun replicateOnNewNodePersonBased(newNodeId: Long): Unit

  @Query(`value` =
      "\n REPLACE INTO ScopedGrantReplicate(sgPk, sgDestination)\n  SELECT DISTINCT ScopedGrantEntity.sgUid AS sgPk,\n         UserSession.usClientNodeId AS sgDestination\n    FROM ChangeLog\n         JOIN ScopedGrant ScopedGrantEntity\n              ON ChangeLog.chTableId = 48\n                 AND ChangeLog.chEntityPk = ScopedGrantEntity.sgUid\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 ScopedGrantEntity.sgLct != COALESCE(\n             (SELECT sgVersionId\n                FROM ScopedGrantReplicate\n               WHERE sgPk = ScopedGrantEntity.sgUid\n                 AND sgDestination = UserSession.usClientNodeId), 0)\n /*psql ON CONFLICT(sgPk, sgDestination) DO UPDATE\n     SET sgPending = true\n  */                 \n    ")
  public actual abstract suspend fun replicateOnChangeSchoolBased(): Unit

  @Query(`value` =
      "\n REPLACE INTO ScopedGrantReplicate(sgPk, sgDestination)\n      SELECT DISTINCT ScopedGrantEntity.sgUid AS sgPk,\n             :newNodeId AS sgDestination\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 ScopedGrantEntity.sgTableId = 164\n                       AND ScopedGrantEntity.sgEntityUid = School.schoolUid\n       WHERE UserSession.usClientNodeId = :newNodeId\n         AND UserSession.usStatus = 1 \n         AND ScopedGrantEntity.sgLct != COALESCE(\n             (SELECT sgVersionId\n                FROM ScopedGrantReplicate\n               WHERE sgPk = ScopedGrantEntity.sgUid\n                 AND sgDestination = UserSession.usClientNodeId), 0)\n /*psql ON CONFLICT(sgPk, sgDestination) DO UPDATE\n     SET sgPending = true\n  */                                                                                 \n    ")
  public actual abstract suspend fun replicateOnNewNodeSchoolBased(newNodeId: Long): Unit

  @Insert(onConflict = 3)
  public actual abstract suspend fun insertAsync(scopedGrant: ScopedGrant): Long

  @Insert(onConflict = 3)
  public actual abstract suspend fun insertListAsync(scopedGrantList: List<ScopedGrant>): Unit

  @Update(onConflict = 3)
  public actual abstract suspend fun updateAsync(scopedGrant: ScopedGrant): Unit

  @Update(onConflict = 3)
  public actual abstract suspend fun updateListAsync(scopedGrantList: List<ScopedGrant>): Unit

  @Query(`value` =
      "\n        SELECT ScopedGrant.*,\n               CASE\n               WHEN Person.firstNames IS NOT NULL THEN Person.firstNames\n               ELSE PersonGroup.groupName \n               END AS name\n          FROM ScopedGrant\n               JOIN PersonGroup \n                    ON ScopedGrant.sgGroupUid = PersonGroup.groupUid\n               LEFT JOIN Person\n                         ON Person.personGroupUid = PersonGroup.groupUid\n         WHERE ScopedGrant.sgTableId = :tableId\n               AND ScopedGrant.sgEntityUid = :entityUid  \n    ")
  public actual abstract suspend fun findByTableIdAndEntityUid(tableId: Int, entityUid: Long):
      List<ScopedGrantAndName>

  @Query(`value` =
      "\n        SELECT ScopedGrant.*,\n               CASE\n               WHEN Person.firstNames IS NOT NULL THEN Person.firstNames\n               ELSE PersonGroup.groupName \n               END AS name\n          FROM ScopedGrant\n               JOIN PersonGroup \n                    ON ScopedGrant.sgGroupUid = PersonGroup.groupUid\n               LEFT JOIN Person\n                         ON Person.personGroupUid = PersonGroup.groupUid\n         WHERE ScopedGrant.sgTableId = :tableId\n               AND ScopedGrant.sgEntityUid = :entityUid  \n    ")
  public actual abstract fun findByTableIdAndEntityUidWithNameAsDataSource(tableId: Int,
      entityUid: Long): DataSource.Factory<Int, ScopedGrantWithName>

  @Query(`value` =
      "\n        SELECT ScopedGrant.*\n          FROM ScopedGrant\n         WHERE sgTableId = :tableId\n           AND sgEntityUid = :entityUid\n    ")
  public actual abstract fun findByTableIdAndEntityIdSync(tableId: Int, entityUid: Long):
      List<ScopedGrant>

  @Query(`value` =
      "\n        SELECT ScopedGrant.*\n          FROM ScopedGrant\n         WHERE sgUid = :sgUid \n    ")
  public actual abstract suspend fun findByUid(sgUid: Long): ScopedGrant?

  @Query(`value` =
      "\n        SELECT ScopedGrant.*, \n               CASE\n               WHEN Person.firstNames IS NOT NULL THEN Person.firstNames\n               ELSE PersonGroup.groupName \n               END AS name\n          FROM ScopedGrant\n               LEFT JOIN PersonGroup \n                    ON ScopedGrant.sgGroupUid = PersonGroup.groupUid\n               LEFT JOIN Person\n                    ON Person.personGroupUid = PersonGroup.groupUid\n         WHERE ScopedGrant.sgUid = :sgUid \n    ")
  public actual abstract fun findByUidLiveWithName(sgUid: Long): LiveData<ScopedGrantWithName?>
}
