package com.ustadmobile.core.db.dao

import androidx.paging.DataSource
import androidx.room.Dao
import androidx.room.Query
import com.ustadmobile.lib.db.entities.Chat
import com.ustadmobile.lib.db.entities.ChatWithLatestMessageAndCount
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.Unit

@Dao
public actual abstract class ChatDao : BaseDao<Chat> {
  @Query(`value` =
      "\n     REPLACE INTO chatReplicate(chatPk, chatDestination)\n      SELECT DISTINCT Chat.chatUid AS chatPk,\n             :newNodeId AS chatDestination\n        FROM UserSession \n             JOIN Chat ON \n                  ((Chat.chatUid IN \n                       (SELECT ChatMember.chatMemberChatUid \n                          FROM ChatMember\n                         WHERE ChatMember.chatMemberPersonUid = UserSession.usPersonUid))\n                   OR UserSession.usSessionType = 2)\n                  AND UserSession.usStatus = 1 \n       WHERE UserSession.usClientNodeId = :newNodeId\n         AND Chat.chatLct != COALESCE(\n             (SELECT chatVersionId\n                FROM chatReplicate\n               WHERE chatPk = Chat.chatUid\n                 AND chatDestination = :newNodeId), 0) \n      /*psql ON CONFLICT(chatPk, chatDestination) DO UPDATE\n             SET chatPending = true\n      */       \n    ")
  public actual abstract suspend fun replicateOnNewNode(newNodeId: Long): Unit

  @Query(`value` =
      "\n         REPLACE INTO chatReplicate(chatPk, chatDestination)\n          SELECT DISTINCT Chat.chatUid AS chatUid,\n                 UserSession.usClientNodeId AS chatDestination\n            FROM ChangeLog\n                 JOIN Chat\n                      ON ChangeLog.chTableId = 127\n                         AND ChangeLog.chEntityPk = Chat.chatUid\n                 JOIN UserSession ON \n                      ((UserSession.usPersonUid IN \n                           (SELECT ChatMember.chatMemberPersonUid \n                              FROM ChatMember \n                             WHERE ChatMember.chatMemberChatUid = Chat.chatUid))\n                       OR UserSession.usSessionType = 2 )\n                      AND UserSession.usStatus = 1\n           WHERE UserSession.usClientNodeId != (\n                 SELECT nodeClientId \n                   FROM SyncNode\n                  LIMIT 1)\n             AND Chat.chatLct != COALESCE(\n                 (SELECT chatVersionId\n                    FROM chatReplicate\n                   WHERE chatPk = Chat.chatUid\n                     AND chatDestination = UserSession.usClientNodeId), 0)\n         /*psql ON CONFLICT(chatPk, chatDestination) DO UPDATE\n             SET chatPending = true\n          */               \n    ")
  public actual abstract suspend fun replicateOnChange(): Unit

  @Query(`value` =
      "\n        SELECT Chat.*,\n               Message.messageText AS latestMessage,\n               Message.messageTimestamp AS latestMessageTimestamp,\n               op.personUid AS otherPersonUid,\n               op.firstNames AS otherPersonFirstNames,\n               op.lastName AS otherPersonLastName,\n               (\n\t\t\t\tSELECT COUNT(*) \n\t\t\t\t  FROM Message \n\t\t\t\t WHERE Message.messageTableId = 127 \n\t\t\t\t   AND Message.messageEntityUid = Chat.chatUid \n\t\t\t\t   AND Message.messageSenderPersonUid != :personUid\n\t\t\t\t   AND Message.messageTimestamp > coalesce((\n\t\t\t\t\t\tSELECT MessageRead.messageReadLct FROM MessageRead \n\t\t\t\t\t\tWHERE MessageRead.messageReadPersonUid = :personUid\n\t\t\t\t\t\tAND MessageRead.messageReadMessageUid = Message.messageUid \n\t\t\t\t      ), 0)\n\t\t\t\t\t\n\t\t\t\t\n\t\t\t   ) AS unreadMessageCount,\n        \n               (SELECT COUNT(*)\n                  FROM ChatMember mm\n                  WHERE mm.chatMemberChatUid = Chat.chatUid ) AS numMembers\n          FROM ChatMember\n               LEFT JOIN Chat \n                    ON Chat.chatUid = ChatMember.chatMemberChatUid\n               LEFT JOIN Message \n                    ON Message.messageUid =\n                        (SELECT messageUid\n                           FROM Message\n                          WHERE messageEntityUid = Chat.chatUid\n                            AND messageTableId = 127\n                       ORDER BY messageTimestamp DESC\n                          LIMIT 1)\n               LEFT JOIN Person op \n                    ON op.personUid =\n                       (SELECT pp.personUid\n                          FROM ChatMember cm\n                               LEFT JOIN Person pp \n                                    ON pp.personUid = cm.chatMemberPersonUid\n                         WHERE cm.chatMemberChatUid = Chat.chatUid\n                           AND cm.chatMemberPersonUid != :personUid\n                           AND cm.chatMemberLeftDate = 9223372036854775807\n                         LIMIT 1)\n         WHERE ChatMember.chatMemberPersonUid = :personUid\n           AND ChatMember.chatMemberLeftDate = 9223372036854775807\n           AND Chat.chatUid != 0 \n           AND op.firstNames||' '||op.lastName LIKE :searchBit \n        -- When in search mode we need to add all Persons who match the search to the list, even if\n        -- no chat has started\n        UNION\n        SELECT Chat.*,\n               '' AS latestMessage,\n                    0 AS latestMessageTimestamp,\n                    Person.personUid AS otherPersonUid,\n                    Person.firstNames AS otherPersonFirstNames,\n                    Person.lastName AS otherPersonLastName,\n                    0 AS unreadMessageCount,\n                    0 AS numMembers\n                              \n          FROM PersonGroupMember\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\t\t  \n               LEFT JOIN Chat\n                    ON Chat.chatUid = 0\n         WHERE :searchBit != '%'\n           AND PersonGroupMember.groupMemberPersonUid = :personUid\n           AND Person.personUid != :personUid\n        \n           AND Person.personUid NOT IN\n\t\t\t   (\n\t\t\t\tSELECT chatpeople.personUid \n\t\t\t\t  FROM ChatMember cmm\n\t\t\t\t\t   LEFT JOIN Chat cc \n\t\t\t\t\t\t\t  ON cc.chatUid = cmm.chatMemberChatUid \n\t\t\t   \n\t\t\t\t LEFT JOIN Person chatpeople \n                    ON chatpeople.personUid =\n                       (SELECT chatpeopleother.personUid\n                          FROM ChatMember cm\n                               LEFT JOIN Person chatpeopleother \n                                    ON chatpeopleother.personUid = cm.chatMemberPersonUid\n                         WHERE cm.chatMemberChatUid = cc.chatUid\n                           AND cm.chatMemberPersonUid != :personUid\n                         LIMIT 1)\n\t\t\t\t\t\t \n\t\t\t\t WHERE cc.chatUid != 0 \n\t\t\t\t   AND cmm.chatMemberPersonUid = :personUid\n\t\t\t\t )\n                            \n           AND Person.firstNames||' '||Person.lastName LIKE :searchBit \n         ORDER BY latestMessageTimestamp DESC\n    ")
  public actual abstract fun findAllChatsForUser(searchBit: String, personUid: Long):
      DataSource.Factory<Int, ChatWithLatestMessageAndCount>

  @Query(`value` =
      "\n        SELECT CASE\n                   WHEN Chat.chatGroup THEN Chat.chatTitle\n                   ELSE Person.firstNames||' '||Person.lastName\n               END AS title\n        FROM Chat\n        LEFT JOIN Person \n        ON CAST(Chat.chatGroup AS INTEGER) = 0\n           AND Person.personUid =\n          (SELECT pp.personUid\n           FROM ChatMember cm\n           LEFT JOIN Person pp ON pp.personUid = cm.chatMemberPersonUid\n           WHERE cm.chatMemberChatUid = Chat.chatUid\n             AND cm.chatMemberPersonUid != :personUid\n             AND cm.chatMemberLeftDate = 9223372036854775807\n           LIMIT 1)\n        WHERE Chat.chatUid = :chatUid\n    ")
  public actual abstract suspend fun getTitleChat(chatUid: Long, personUid: Long): String?

  @Query(`value` =
      "\n        SELECT Chat.*\n          FROM ChatMember\n          LEFT JOIN Chat ON Chat.chatUid = ChatMember.chatMemberChatUid\n         WHERE ChatMember.chatMemberPersonUid = :otherPersonUid\n           AND CAST(Chat.chatGroup AS INTEGER) = 0\n           AND Chat.chatUid IN \n               (\n                SELECT ChatMember.chatMemberChatUid\n                  FROM ChatMember\n                 WHERE ChatMember.chatMemberChatUid = Chat.chatUid\n                   AND ChatMember.chatMemberPersonUid = :loggedInPersonUid \n               ) \n           AND :otherPersonUid != :loggedInPersonUid\n    ")
  public actual abstract suspend fun getChatByOtherPerson(otherPersonUid: Long,
      loggedInPersonUid: Long): Chat?
}
