package com.ustadmobile.core.db.dao

import androidx.paging.DataSource
import androidx.room.Dao
import androidx.room.Query
import com.ustadmobile.lib.db.entities.Message
import com.ustadmobile.lib.db.entities.MessageWithPerson
import kotlin.Int
import kotlin.Long
import kotlin.Unit

@Dao
public actual abstract class MessageDao : BaseDao<Message> {
  @Query(`value` =
      "\n     REPLACE INTO MessageReplicate(messagePk, messageDestination)\n      SELECT DISTINCT Message.messageUid AS messagePk,\n             :newNodeId AS messageDestination\n        FROM UserSession\n             JOIN Message ON\n                  ((    Message.messageTableId = 127\n                    AND Message.messageEntityUid IN\n                        (SELECT ChatMember.chatMemberChatUid \n                          FROM ChatMember\n                         WHERE ChatMember.chatMemberPersonUid = UserSession.usPersonUid))\n                  OR UserSession.usSessionType = 2)\n       WHERE UserSession.usClientNodeId = :newNodeId\n         AND UserSession.usStatus = 1\n         AND Message.messageLct != COALESCE(\n             (SELECT messageVersionId\n                FROM MessageReplicate\n               WHERE messagePk = Message.messageUid\n                 AND messageDestination = :newNodeId), 0) \n      /*psql ON CONFLICT(messagePk, messageDestination) DO UPDATE\n             SET messagePending = true\n      */       \n    ")
  public actual abstract suspend fun replicateOnNewNodeChats(newNodeId: Long): Unit

  @Query(`value` =
      "\n     REPLACE INTO MessageReplicate(messagePk, messageDestination)\n      SELECT DISTINCT Message.messageUid AS messagePk,\n             :newNodeId AS messageDestination\n        FROM UserSession\n        \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            \n             JOIN Message \n                  ON Message.messageTableId = 132\n                  AND Message.messageClazzUid = Clazz.clazzUid\n            \n            \n       WHERE UserSession.usClientNodeId = :newNodeId\n         AND UserSession.usStatus = 1\n         AND Message.messageLct != COALESCE(\n             (SELECT messageVersionId\n                FROM MessageReplicate\n               WHERE messagePk = Message.messageUid\n                 AND messageDestination = :newNodeId), 0) \n      /*psql ON CONFLICT(messagePk, messageDestination) DO UPDATE\n             SET messagePending = true\n      */       \n    ")
  public actual abstract suspend fun replicateOnNewNodePosts(newNodeId: Long): Unit

  @Query(`value` =
      "\n         REPLACE INTO MessageReplicate(messagePk, messageDestination)\n          SELECT DISTINCT Message.messageUid AS messageUid,\n                 UserSession.usClientNodeId AS messageDestination\n            FROM ChangeLog\n                 JOIN Message\n                     ON ChangeLog.chTableId = 126\n                        AND ChangeLog.chEntityPk = Message.messageUid\n                        AND Message.messageTableId = 127\n                 JOIN UserSession ON\n                      ((UserSession.usPersonUid IN \n                           (SELECT ChatMember.chatMemberPersonUid\n                              FROM ChatMember\n                             WHERE ChatMember.chatMemberChatUid = Message.messageEntityUid))\n                       OR UserSession.usSessionType = 2)       \n           WHERE UserSession.usStatus = 1\n             AND UserSession.usClientNodeId != (\n                 SELECT nodeClientId \n                   FROM SyncNode\n                  LIMIT 1)\n             AND Message.messageLct != COALESCE(\n                 (SELECT messageVersionId\n                    FROM MessageReplicate\n                   WHERE messagePk = Message.messageUid\n                     AND messageDestination = UserSession.usClientNodeId), 0)\n         /*psql ON CONFLICT(messagePk, messageDestination) DO UPDATE\n             SET messagePending = true\n          */               \n    ")
  public actual abstract suspend fun replicateOnChangeChat(): Unit

  @Query(`value` =
      "\n         REPLACE INTO MessageReplicate(messagePk, messageDestination)\n          SELECT DISTINCT Message.messageUid AS messageUid,\n                 UserSession.usClientNodeId AS messageDestination\n            FROM ChangeLog\n            \n                 JOIN Message\n                     ON ChangeLog.chTableId = 126\n                        AND ChangeLog.chEntityPk = Message.messageUid\n                        AND Message.messageTableId = 132\n                        \n                 JOIN Clazz\n                      ON Clazz.clazzUid = Message.messageClazzUid\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                       \n           WHERE UserSession.usStatus = 1\n             AND UserSession.usClientNodeId != (\n                 SELECT nodeClientId \n                   FROM SyncNode\n                  LIMIT 1)\n             AND Message.messageLct != COALESCE(\n                 (SELECT messageVersionId\n                    FROM MessageReplicate\n                   WHERE messagePk = Message.messageUid\n                     AND messageDestination = UserSession.usClientNodeId), 0)\n         /*psql ON CONFLICT(messagePk, messageDestination) DO UPDATE\n             SET messagePending = true\n          */               \n    ")
  public actual abstract suspend fun replicateOnChangePosts(): Unit

  @Query(`value` =
      "\n       SELECT\n              Message.*,\n              Person.*,\n              MessageRead.*\n        FROM Message\n        LEFT JOIN Person\n          ON Message.messageSenderPersonUid = Person.personUid\n        LEFT JOIN MessageRead\n          ON MessageRead.messageReadMessageUid = Message.messageUid\n             AND MessageRead.messageReadPersonUid = :loggedInPersonUid\n       WHERE Message.messageTableId = :tableId\n              AND Message.messageEntityUid = :entityUid\n    ORDER BY Message.messageTimestamp DESC\n    ")
  public actual abstract fun findAllMessagesByChatUid(
    entityUid: Long,
    tableId: Int,
    loggedInPersonUid: Long,
  ): DataSource.Factory<Int, MessageWithPerson>
}
