package com.ustadmobile.core.db.dao

import androidx.paging.PagingSource
import androidx.room.Dao
import androidx.room.Insert
import androidx.room.Query
import androidx.room.Update
import com.ustadmobile.lib.db.composites.DiscussionPostAndPosterNames
import com.ustadmobile.lib.db.composites.PersonAndPicture
import com.ustadmobile.lib.db.entities.DiscussionPost
import com.ustadmobile.lib.db.entities.DiscussionPostWithDetails
import kotlin.Any
import kotlin.Boolean
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.collections.List
import kotlinx.coroutines.flow.Flow

@Dao
public actual abstract class DiscussionPostDao : BaseDao<DiscussionPost> {
  @Query(`value` =
      "\n        SELECT DiscussionPost.*,\n               Person.firstNames as authorPersonFirstNames,\n               Person.lastName as authorPersonLastName,\n               PersonPicture.personPictureThumbnailUri AS authorPictureUri,\n               MostRecentReply.discussionPostMessage AS postLatestMessage,\n               COALESCE(MostRecentReply.discussionPostStartDate, 0) AS postLatestMessageTimestamp,\n               (SELECT COUNT(*)\n                  FROM DiscussionPost DiscussionPostReplies\n                 WHERE DiscussionPostReplies.discussionPostReplyToPostUid = DiscussionPost.discussionPostUid\n                   AND NOT DiscussionPostReplies.dpDeleted\n               ) AS postRepliesCount\n          FROM DiscussionPost\n               LEFT JOIN DiscussionPost AS MostRecentReply\n                         ON MostRecentReply.discussionPostUid = \n                            (SELECT MostRecentReplyInner.discussionPostUid\n                               FROM DiscussionPost AS MostRecentReplyInner\n                              WHERE MostRecentReplyInner.discussionPostReplyToPostUid = DiscussionPost.discussionPostUid\n                           ORDER BY MostRecentReplyInner.discussionPostStartDate DESC\n                              LIMIT 1  \n                            )\n               LEFT JOIN Person \n                         ON Person.personUid = DiscussionPost.discussionPostStartedPersonUid\n               LEFT JOIN PersonPicture\n                         ON PersonPicture.personPictureUid = DiscussionPost.discussionPostStartedPersonUid\n         WHERE DiscussionPost.discussionPostCourseBlockUid = :courseBlockUid\n           AND DiscussionPost.discussionPostReplyToPostUid = 0\n           AND (NOT DiscussionPost.dpDeleted OR CAST(:includeDeleted AS INTEGER) = 1)\n      ORDER BY DiscussionPost.discussionPostStartDate DESC          \n    ")
  public actual abstract fun getTopLevelPostsByCourseBlockUid(courseBlockUid: Long,
      includeDeleted: Boolean): PagingSource<Int, DiscussionPostWithDetails>

  @Query(`value` =
      "\n        SELECT Person.*, PersonPicture.*\n          FROM Person\n               LEFT JOIN PersonPicture\n                         ON PersonPicture.personPictureUid = Person.personUid\n         WHERE Person.personUid IN\n               (SELECT DISTINCT DiscussionPost.discussionPostStartedPersonUid\n                  FROM DiscussionPost\n                 WHERE DiscussionPost.discussionPostCourseBlockUid = :courseBlockUid\n                   AND DiscussionPost.discussionPostReplyToPostUid = 0)\n    ")
  public actual abstract suspend fun getTopLevelPostsByCourseBlockUidPersons(courseBlockUid: Long):
      List<PersonAndPicture>

  @Query(`value` =
      "\n        SELECT MostRecentReply.*\n          FROM DiscussionPost\n               JOIN DiscussionPost AS MostRecentReply\n                         ON MostRecentReply.discussionPostUid = \n                            (SELECT MostRecentReplyInner.discussionPostUid\n                               FROM DiscussionPost AS MostRecentReplyInner\n                              WHERE MostRecentReplyInner.discussionPostReplyToPostUid = DiscussionPost.discussionPostUid\n                           ORDER BY MostRecentReplyInner.discussionPostStartDate DESC\n                              LIMIT 1  \n                            )\n         WHERE DiscussionPost.discussionPostCourseBlockUid = :courseBlockUid\n           AND DiscussionPost.discussionPostReplyToPostUid = 0 \n    ")
  public actual abstract suspend
      fun getTopLevelPostsByCourseBlockUidLatestMessage(courseBlockUid: Long): List<DiscussionPost>

  @Query(`value` =
      "\n        SELECT DiscussionPost.discussionPostTitle \n          FROM DiscussionPost \n         WHERE DiscussionPost.discussionPostUid = :postUid\n    ")
  public actual abstract suspend fun getPostTitle(postUid: Long): String?

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

  @Query(`value` =
      "\n        SELECT DiscussionPost.discussionPostTitle\n          FROM DiscussionPost\n         WHERE DiscussionPost.discussionPostUid = :uid\n    ")
  public actual abstract fun getTitleByUidAsFlow(uid: Long): Flow<String?>

  @Update(
    entity = Any::class,
    onConflict = 3,
  )
  public actual abstract suspend fun updateAsync(entity: DiscussionPost): Int

  @Query(`value` =
      "\n        SELECT DiscussionPost.*,\n               Person.firstNames,\n               Person.lastName,\n               PersonPicture.personPictureThumbnailUri AS personPictureUri\n          FROM DiscussionPost\n               LEFT JOIN Person\n                         ON Person.personUid = DiscussionPost.discussionPostStartedPersonUid\n               LEFT JOIN PersonPicture\n                         ON PersonPicture.personPictureUid = DiscussionPost.discussionPostStartedPersonUid\n         WHERE (DiscussionPost.discussionPostUid = :postUid\n                 OR DiscussionPost.discussionPostReplyToPostUid= :postUid)\n           AND (NOT DiscussionPost.dpDeleted OR CAST(:includeDeleted AS INTEGER) = 1)      \n            -- Always get the starting post first, followed by replies\n      ORDER BY CASE(DiscussionPost.discussionPostReplyToPostUid)\n               WHEN 0 THEN 0\n               ELSE 1 END ASC,\n               DiscussionPost.discussionPostStartDate DESC \n    ")
  public actual abstract fun findByPostIdWithAllReplies(postUid: Long, includeDeleted: Boolean):
      PagingSource<Int, DiscussionPostAndPosterNames>

  @Query(`value` =
      "\n        SELECT Person.*, PersonPicture.*\n          FROM Person\n               LEFT JOIN PersonPicture\n                         ON PersonPicture.personPictureUid = Person.personUid\n         WHERE Person.personUid IN\n               (SELECT DISTINCT DiscussionPost.discussionPostStartedPersonUid\n                  FROM DiscussionPost\n                 WHERE DiscussionPost.discussionPostUid = :postUid\n                    OR DiscussionPost.discussionPostReplyToPostUid= :postUid)\n    ")
  public actual abstract suspend fun findByPostIdWithAllRepliesPersons(postUid: Long):
      List<PersonAndPicture>

  @Insert(
    onConflict = 1,
    entity = Any::class,
  )
  public actual abstract suspend fun upsertAsync(entity: DiscussionPost)

  @Query(`value` =
      "\n        UPDATE DiscussionPost\n           SET dpDeleted = :deleted,\n               discussionPostLct = :updateTime\n         WHERE discussionPostUid = :uid   \n    ")
  public actual abstract suspend fun setDeletedAsync(
    uid: Long,
    deleted: Boolean,
    updateTime: Long,
  )
}
