package com.ustadmobile.core.db.dao

import androidx.lifecycle.LiveData
import androidx.room.Dao
import androidx.room.Insert
import androidx.room.Query
import com.ustadmobile.lib.db.entities.UserSession
import com.ustadmobile.lib.db.entities.UserSessionAndPerson
import kotlin.Int
import kotlin.Long
import kotlin.Unit
import kotlin.collections.List

@Dao
public actual abstract class UserSessionDao {
  @Query(`value` =
      "\n        REPLACE INTO UserSessionReplicate(usPk, usDestination)\n         SELECT DISTINCT UserSessionSubject.usUid AS usPk,\n                UserSession.usClientNodeId AS usDestination\n           FROM ChangeLog\n                JOIN UserSession UserSessionSubject\n                     ON ChangeLog.chTableId = 679\n                        AND ChangeLog.chEntityPk = UserSessionSubject.usUid\n                        AND UserSessionSubject.usSessionType = 1\n                JOIN Person\n                     ON UserSessionSubject.usPersonUid = 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                    /* Modify second part of query - remove requirement for session to be active.\n                     * This ensures that deactivations are distributed\n                     */\n                    ) > 0\n                     JOIN PersonGroupMember AS PrsGrpMbr\n                          ON ScopedGrant.sgGroupUid = PrsGrpMbr.groupMemberGroupUid\n                     JOIN UserSession\n                          ON UserSession.usPersonUid = PrsGrpMbr.groupMemberPersonUid\n          WHERE UserSessionSubject.usClientNodeId = UserSessionSubject.usClientNodeId                \n          --notpsql              \n            AND UserSessionSubject.usLct != COALESCE(\n                (SELECT usVersionId\n                   FROM UserSessionReplicate\n                  WHERE UserSessionReplicate.usPk = UserSessionSubject.usUid\n                    AND UserSessionReplicate.usDestination = UserSession.usClientNodeId), 0)\n          --endnotpsql                       \n        /*psql ON CONFLICT(usPk, usDestination) \n                DO UPDATE SET usPending = \n                   (SELECT UserSession.usLct\n                      FROM UserSession\n                     WHERE UserSession.usUid = EXCLUDED.usPk ) \n                        != UserSessionReplicate.usVersionId\n         */         \n    ")
  public actual abstract suspend fun updateReplicationTrackers(): Unit

  @Query(`value` =
      "\n        REPLACE INTO UserSessionReplicate(usPk, usDestination)\n         SELECT DISTINCT UserSessionSubject.usUid AS usPk,\n                UserSession.usClientNodeId AS usDestination\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 UserSession UserSessionSubject\n                     ON UserSessionSubject.usPersonUid = Person.personUid\n                        AND UserSessionSubject.usSessionType = 1\n                        AND UserSessionSubject.usClientNodeId = :newNodeId\n          WHERE UserSession.usClientNodeId = :newNodeId\n          --notpsql\n            AND UserSessionSubject.usLct != COALESCE(\n                (SELECT usVersionId\n                   FROM UserSessionReplicate\n                  WHERE UserSessionReplicate.usPk = UserSessionSubject.usUid\n                    AND UserSessionReplicate.usDestination = UserSession.usClientNodeId), 0)\n          --endnotpsql          \n         /*psql ON CONFLICT(usPk, usDestination) \n                DO UPDATE SET usPending = \n                   (SELECT UserSession.usLct\n                      FROM UserSession\n                     WHERE UserSession.usUid = EXCLUDED.usPk ) \n                        != UserSessionReplicate.usVersionId\n         */\n    ")
  public actual abstract suspend fun updateReplicationTrackersOnNewNode(newNodeId: Long): Unit

  @Insert(onConflict = 3)
  public actual abstract suspend fun insertSession(session: UserSession): Long

  @Query(`value` =
      "\n        SELECT UserSession.*\n          FROM UserSession\n         WHERE usPersonUid = :personUid \n    ")
  public actual abstract suspend fun findSessionsByPerson(personUid: Long): List<UserSession>

  @Query(`value` =
      "\n            SELECT UserSession.*, Person.*\n              FROM UserSession\n                   JOIN Person ON UserSession.usPersonUid = Person.personUid\n             WHERE UserSession.usClientNodeId = (\n                   SELECT COALESCE(\n                          (SELECT nodeClientId \n                            FROM SyncNode\n                           LIMIT 1), 0))\n               AND UserSession.usStatus = 1        \n            ")
  public actual abstract fun findAllLocalSessionsLive(): LiveData<List<UserSessionAndPerson>>

  @Query(`value` =
      "\n            SELECT UserSession.*, Person.*\n              FROM UserSession\n                   JOIN Person ON UserSession.usPersonUid = Person.personUid\n             WHERE UserSession.usClientNodeId = (\n                   SELECT COALESCE(\n                          (SELECT nodeClientId \n                            FROM SyncNode\n                           LIMIT 1), 0))\n               AND UserSession.usStatus = 1        \n            ")
  public actual abstract suspend fun findAllLocalSessionsAsync(): List<UserSessionAndPerson>

  @Query(`value` =
      "\n        SELECT COUNT(*)\n          FROM UserSession\n               JOIN Person \n                    ON UserSession.usPersonUid = Person.personUid\n         WHERE UserSession.usClientNodeId = (\n                   SELECT COALESCE(\n                          (SELECT nodeClientId \n                            FROM SyncNode\n                           LIMIT 1), 0))\n           AND UserSession.usStatus = 1                \n           AND (:maxDateOfBirth = 0 OR Person.dateOfBirth < :maxDateOfBirth)                 \n    ")
  public actual abstract suspend fun countAllLocalSessionsAsync(maxDateOfBirth: Long): Int

  @Query(`value` =
      "\n        UPDATE UserSession\n           SET usAuth = null,\n               usStatus = :newStatus,\n               usReason = :reason,\n               usLcb = COALESCE(\n                               (SELECT nodeClientId\n                                  FROM SyncNode\n                                 LIMIT 1), 0)\n         WHERE UserSession.usUid = :sessionUid                        \n               \n    ")
  public actual abstract suspend fun endSession(
    sessionUid: Long,
    newStatus: Int,
    reason: Int,
  ): Unit

  @Query(`value` =
      "\n        SELECT UserSession.*\n          FROM UserSession\n         WHERE UserSession.usUid = :sessionUid\n         LIMIT 1\n    ")
  public actual abstract fun findByUidLive(sessionUid: Long): LiveData<UserSession?>

  @Query(`value` =
      "\n        UPDATE UserSession\n           SET usAuth = null,\n               usStatus = :newStatus,\n               usReason = :reason,\n               usLct = :changeTime\n         WHERE usPersonUid = :personUid\n           AND usClientNodeId != :exemptNodeId\n           AND usStatus != :newStatus                     \n    ")
  public actual abstract suspend fun endOtherSessions(
    personUid: Long,
    exemptNodeId: Long,
    newStatus: Int,
    reason: Int,
    changeTime: Long,
  ): Unit

  @Query(`value` =
      "\n        SELECT DISTINCT UserSession.usClientNodeId\n          FROM UserSession\n         WHERE UserSession.usPersonUid IN (:personUids)\n           AND UserSession.usStatus = 1\n    ")
  public actual abstract suspend fun findActiveNodeIdsByPersonUids(personUids: List<Long>):
      List<Long>

  @Query(`value` =
      "\n        SELECT DISTINCT UserSession.usClientNodeId\n          FROM UserSession\n               JOIN PersonGroupMember \n                    ON PersonGroupMember.groupMemberPersonUid = UserSession.usPersonUid\n         WHERE PersonGroupMember.groupMemberGroupUid IN (:groupUids)            \n    ")
  public actual abstract suspend fun findActiveNodesIdsByGroupUids(groupUids: List<Long>):
      List<Long>

  @Query(`value` =
      "\n        SELECT UserSession.usClientNodeId\n          FROM ScopedGrant\n               JOIN PersonGroupMember \n                    ON PersonGroupMember.groupMemberGroupUid = ScopedGrant.sgGroupUid\n               JOIN UserSession\n                    ON UserSession.usPersonUid = PersonGroupMember.groupMemberPersonUid\n         WHERE (ScopedGrant.sgTableId = 6 AND ScopedGrant.sgEntityUid IN (:clazzUids))\n            OR (ScopedGrant.sgTableId = 164 AND ScopedGrant.sgEntityUid IN \n                (SELECT clazzSchoolUid\n                   FROM Clazz\n                  WHERE clazzUid IN (:clazzUids)))\n          \n    ")
  public actual abstract suspend
      fun findAllActiveNodeIdsWithClazzBasedPermission(clazzUids: List<Long>): List<Long>

  @Query(`value` =
      "\n        SELECT UserSession.usClientNodeId\n          FROM ScopedGrant\n               JOIN PersonGroupMember \n                    ON PersonGroupMember.groupMemberGroupUid = ScopedGrant.sgGroupUid\n               JOIN UserSession\n                    ON UserSession.usPersonUid = PersonGroupMember.groupMemberPersonUid\n         WHERE ScopedGrant.sgTableId = 164 \n           AND ScopedGrant.sgEntityUid IN (:schoolUids) \n    ")
  public actual abstract suspend
      fun findAllActiveNodeIdsWithSchoolBasedPermission(schoolUids: List<Long>): List<Long>
}
