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.ContentEntry
import com.ustadmobile.lib.db.entities.ContentEntryButtonModel
import com.ustadmobile.lib.db.entities.ContentEntryContentJobItemParams
import com.ustadmobile.lib.db.entities.ContentEntryWithBlockAndLanguage
import com.ustadmobile.lib.db.entities.ContentEntryWithLanguage
import com.ustadmobile.lib.db.entities.ContentEntryWithMostRecentContainer
import com.ustadmobile.lib.db.entities.ContentEntryWithParentChildJoinAndMostRecentContainer
import com.ustadmobile.lib.db.entities.ContentEntryWithParentChildJoinAndStatusAndMostRecentContainer
import com.ustadmobile.lib.db.entities.ContentJobItemProgressAndStatus
import com.ustadmobile.lib.db.entities.DistinctCategorySchema
import com.ustadmobile.lib.db.entities.DownloadJobSizeInfo
import com.ustadmobile.lib.db.entities.LangUidAndName
import com.ustadmobile.lib.db.entities.Language
import com.ustadmobile.lib.db.entities.UidAndLabel
import kotlin.Boolean
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.Unit
import kotlin.collections.List

@Dao
public actual abstract class ContentEntryDao : BaseDao<ContentEntry> {
  @Query(`value` =
      "\n        REPLACE INTO ContentEntryReplicate(cePk, ceDestination)\n         SELECT DISTINCT contentEntryUid AS ceUid,\n                :newNodeId AS siteDestination\n           FROM ContentEntry\n          WHERE ContentEntry.contentEntryLct != COALESCE(\n                (SELECT ceVersionId\n                   FROM ContentEntryReplicate\n                  WHERE cePk = ContentEntry.contentEntryUid\n                    AND ceDestination = :newNodeId), -1) \n         /*psql ON CONFLICT(cePk, ceDestination) DO UPDATE\n                SET cePending = true\n         */       \n    ")
  public actual abstract suspend fun replicateOnNewNode(newNodeId: Long): Unit

  @Query(`value` =
      "\n        REPLACE INTO ContentEntryReplicate(cePk, ceDestination)\n         SELECT DISTINCT ContentEntry.contentEntryUid AS cePk,\n                UserSession.usClientNodeId AS siteDestination\n           FROM ChangeLog\n                JOIN ContentEntry\n                    ON ChangeLog.chTableId = 42\n                       AND ChangeLog.chEntityPk = ContentEntry.contentEntryUid\n                JOIN UserSession ON UserSession.usStatus = 1\n          WHERE UserSession.usClientNodeId != (\n                SELECT nodeClientId \n                  FROM SyncNode\n                 LIMIT 1)\n            AND ContentEntry.contentEntryLct != COALESCE(\n                (SELECT ceVersionId\n                   FROM ContentEntryReplicate\n                  WHERE cePk = ContentEntry.contentEntryUid\n                    AND ceDestination = UserSession.usClientNodeId), 0)     \n        /*psql ON CONFLICT(cePk, ceDestination) DO UPDATE\n            SET cePending = true\n         */               \n    ")
  public actual abstract suspend fun replicateOnChange(): Unit

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

  @Query(`value` =
      "SELECT ContentEntry.*, Language.* FROM ContentEntry LEFT JOIN Language ON Language.langUid = ContentEntry.primaryLanguageUid WHERE ContentEntry.contentEntryUid=:entryUuid")
  public actual abstract suspend fun findEntryWithLanguageByEntryIdAsync(entryUuid: Long):
      ContentEntryWithLanguage?

  @Query(`value` =
      "\n        SELECT ContentEntry.*, \n               Language.*,\n               CourseBlock.*\n          FROM ContentEntry\n               LEFT JOIN Language \n               ON Language.langUid = ContentEntry.primaryLanguageUid \n               \n               LEFT JOIN CourseBlock\n               ON CourseBlock.cbType = 104\n               AND CourseBlock.cbEntityUid = :entityUid\n               \n         WHERE ContentEntry.contentEntryUid = :entityUid       \n    ")
  public actual abstract suspend fun findEntryWithBlockAndLanguageByUidAsync(entityUid: Long):
      ContentEntryWithBlockAndLanguage?

  @Query(`value` =
      "\n            SELECT ContentEntry.*, Container.* FROM ContentEntry LEFT \n                JOIN Container ON Container.containerUid = (\n                    SELECT containerUid FROM Container WHERE containerContentEntryUid =  ContentEntry.contentEntryUid ORDER BY cntLastModified DESC LIMIT 1) \n            WHERE ContentEntry.contentEntryUid=:entryUuid\n            ")
  public actual abstract suspend fun findEntryWithContainerByEntryId(entryUuid: Long):
      ContentEntryWithMostRecentContainer?

  @Query(`value` =
      "\n            SELECT ContentEntry.*, Container.* FROM ContentEntry LEFT \n                JOIN Container ON Container.containerUid = (\n                    SELECT containerUid FROM Container WHERE containerContentEntryUid =  ContentEntry.contentEntryUid ORDER BY cntLastModified DESC LIMIT 1) \n            WHERE ContentEntry.contentEntryUid=:entryUuid\n            ")
  public actual abstract fun findEntryWithContainerByEntryIdLive(entryUuid: Long):
      LiveData<ContentEntryWithMostRecentContainer?>

  @Query(`value` = "SELECT * FROM ContentEntry WHERE sourceUrl = :sourceUrl LIMIT 1")
  public actual abstract fun findBySourceUrl(sourceUrl: String): ContentEntry?

  @Query(`value` = "SELECT title FROM ContentEntry WHERE contentEntryUid = :contentEntryUid")
  public actual abstract suspend fun findTitleByUidAsync(contentEntryUid: Long): String?

  @Query(`value` =
      "SELECT ContentEntry.* FROM ContentEntry LEFT Join ContentEntryParentChildJoin ON ContentEntryParentChildJoin.cepcjChildContentEntryUid = ContentEntry.contentEntryUid WHERE ContentEntryParentChildJoin.cepcjParentContentEntryUid = :parentUid")
  public actual abstract fun getChildrenByParentUid(parentUid: Long):
      DataSource.Factory<Int, ContentEntry>

  @Query(`value` =
      "\n        SELECT ContentEntry.*\n          FROM ContentEntryParentChildJoin\n               JOIN ContentEntry \n                    ON ContentEntryParentChildJoin.cepcjChildContentEntryUid = ContentEntry.contentEntryUid\n         WHERE ContentEntryParentChildJoin.cepcjParentContentEntryUid = :parentUid\n    ")
  public actual abstract suspend fun getChildrenByParentAsync(parentUid: Long): List<ContentEntry>

  @Query(`value` =
      "\n        SELECT ContentEntry.contentEntryUid AS contentEntryUid, ContentEntry.leaf AS leaf, \n               COALESCE(Container.containerUid, 0) AS mostRecentContainerUid,\n               COALESCE(Container.fileSize, 0) AS mostRecentContainerSize\n          FROM ContentEntryParentChildJoin\n               JOIN ContentEntry \n                    ON ContentEntryParentChildJoin.cepcjChildContentEntryUid = ContentEntry.contentEntryUid\n               LEFT JOIN Container\n                    ON containerUid = \n                        (SELECT COALESCE((\n                                SELECT Container.containerUid \n                                  FROM Container\n                                 WHERE Container.containerContentEntryUid = ContentEntry.contentEntryUid\n                              ORDER BY Container.cntLastModified DESC\n                                 LIMIT 1),0))\n         WHERE ContentEntryParentChildJoin.cepcjParentContentEntryUid = :parentUid\n         LIMIT :limit\n        OFFSET :offset \n    ")
  public actual abstract suspend fun getContentJobItemParamsByParentUid(
    parentUid: Long,
    limit: Int,
    offset: Int,
  ): List<ContentEntryContentJobItemParams>

  @Query(`value` =
      "SELECT COUNT(*) FROM ContentEntry LEFT Join ContentEntryParentChildJoin ON ContentEntryParentChildJoin.cepcjChildContentEntryUid = ContentEntry.contentEntryUid WHERE ContentEntryParentChildJoin.cepcjParentContentEntryUid = :parentUid")
  public actual abstract suspend fun getCountNumberOfChildrenByParentUUidAsync(parentUid: Long): Int

  @Query(`value` = "SELECT * FROM ContentEntry where contentEntryUid = :parentUid LIMIT 1")
  public actual abstract suspend fun getContentByUuidAsync(parentUid: Long): ContentEntry?

  @Query(`value` =
      "SELECT ContentEntry.* FROM ContentEntry LEFT JOIN ContentEntryRelatedEntryJoin ON ContentEntryRelatedEntryJoin.cerejRelatedEntryUid = ContentEntry.contentEntryUid WHERE ContentEntryRelatedEntryJoin.relType = 1 AND ContentEntryRelatedEntryJoin.cerejRelatedEntryUid != :entryUuid")
  public actual abstract suspend fun findAllLanguageRelatedEntriesAsync(entryUuid: Long):
      List<ContentEntry>

  @Query(`value` =
      "SELECT DISTINCT ContentCategory.contentCategoryUid, ContentCategory.name AS categoryName, ContentCategorySchema.contentCategorySchemaUid, ContentCategorySchema.schemaName FROM ContentEntry LEFT JOIN ContentEntryContentCategoryJoin ON ContentEntryContentCategoryJoin.ceccjContentEntryUid = ContentEntry.contentEntryUid LEFT JOIN ContentCategory ON ContentCategory.contentCategoryUid = ContentEntryContentCategoryJoin.ceccjContentCategoryUid LEFT JOIN ContentCategorySchema ON ContentCategorySchema.contentCategorySchemaUid = ContentCategory.ctnCatContentCategorySchemaUid LEFT JOIN ContentEntryParentChildJoin ON ContentEntryParentChildJoin.cepcjChildContentEntryUid = ContentEntry.contentEntryUid WHERE ContentEntryParentChildJoin.cepcjParentContentEntryUid = :parentUid AND ContentCategory.contentCategoryUid != 0 ORDER BY ContentCategory.name")
  public actual abstract suspend fun findListOfCategoriesAsync(parentUid: Long):
      List<DistinctCategorySchema>

  @Query(`value` =
      "SELECT DISTINCT Language.* from Language LEFT JOIN ContentEntry ON ContentEntry.primaryLanguageUid = Language.langUid LEFT JOIN ContentEntryParentChildJoin ON ContentEntryParentChildJoin.cepcjChildContentEntryUid = ContentEntry.contentEntryUid WHERE ContentEntryParentChildJoin.cepcjParentContentEntryUid = :parentUid ORDER BY Language.name")
  public actual abstract suspend fun findUniqueLanguagesInListAsync(parentUid: Long): List<Language>

  @Query(`value` =
      "SELECT DISTINCT Language.langUid, Language.name AS langName from Language\n        LEFT JOIN ContentEntry ON ContentEntry.primaryLanguageUid = Language.langUid\n        LEFT JOIN ContentEntryParentChildJoin ON ContentEntryParentChildJoin.cepcjChildContentEntryUid = ContentEntry.contentEntryUid \n        WHERE ContentEntryParentChildJoin.cepcjParentContentEntryUid = :parentUid ORDER BY Language.name")
  public actual abstract suspend fun findUniqueLanguageWithParentUid(parentUid: Long):
      List<LangUidAndName>

  @Update(onConflict = 3)
  public actual abstract override fun update(entity: ContentEntry): Unit

  @Query(`value` = "SELECT * FROM ContentEntry WHERE contentEntryUid = :entryUid")
  public actual abstract suspend fun findByUidAsync(entryUid: Long): ContentEntry?

  @Query(`value` =
      "\n        SELECT ContentEntry.*, Language.*\n          FROM ContentEntry\n               LEFT JOIN Language \n                      ON Language.langUid = ContentEntry.primaryLanguageUid \n         WHERE ContentEntry.contentEntryUid = :uid              \n    ")
  public actual abstract suspend fun findByUidWithLanguageAsync(uid: Long):
      ContentEntryWithLanguage?

  @Query(`value` = "SELECT * FROM ContentEntry WHERE contentEntryUid = :entryUid")
  public actual abstract fun findByUid(entryUid: Long): ContentEntry?

  @Query(`value` = "SELECT * FROM ContentEntry WHERE title = :title")
  public actual abstract fun findByTitle(title: String): LiveData<ContentEntry?>

  @Query(`value` =
      "\n       SELECT COALESCE((SELECT CAST(cjIsMeteredAllowed AS INTEGER) \n                FROM ContentJobItem \n                JOIN ContentJob\n                    ON ContentJobItem.cjiJobUid = ContentJob.cjUid\n               WHERE cjiContentEntryUid = :contentEntryUid\n                AND cjiRecursiveStatus >= 4\n                AND cjiRecursiveStatus <= 20 LIMIT 1),\n                CAST(((SELECT connectivityState\n                        FROM ConnectivityStatus\n                       LIMIT 1) = 3) AS INTEGER),\n                0) AS Status\n    ")
  public actual abstract suspend fun isMeteredAllowedForEntry(contentEntryUid: Long): Boolean

  @Query(`value` =
      "SELECT ContentEntry.* FROM ContentEntry WHERE ContentEntry.sourceUrl = :sourceUrl")
  public actual abstract suspend fun findBySourceUrlWithContentEntryStatusAsync(sourceUrl: String):
      ContentEntry?

  @Query(`value` =
      "\n            SELECT ContentEntry.*, ContentEntryParentChildJoin.*, Container.*, \n                COALESCE(StatementEntity.resultScoreMax,0) AS resultMax, \n                COALESCE(StatementEntity.resultScoreRaw,0) AS resultScore, \n                COALESCE(StatementEntity.resultScoreScaled,0) AS resultScaled, \n                COALESCE(StatementEntity.extensionProgress,0) AS progress, \n                COALESCE(StatementEntity.resultCompletion,'FALSE') AS contentComplete,\n                COALESCE(StatementEntity.resultSuccess, 0) AS success,\n                COALESCE((CASE WHEN StatementEntity.resultCompletion \n                THEN 1 ELSE 0 END),0) AS totalCompletedContent,\n                0 AS assignmentContentWeight,\n                \n                1 as totalContent, \n                \n                0 as penalty\n            FROM ContentEntry \n                    LEFT JOIN ContentEntryParentChildJoin \n                    ON ContentEntryParentChildJoin.cepcjChildContentEntryUid = ContentEntry.contentEntryUid \n                    \n                    LEFT JOIN StatementEntity\n\t\t\t\t\t\t\tON StatementEntity.statementUid = \n                                (SELECT statementUid \n\t\t\t\t\t\t\t       FROM StatementEntity \n                                  WHERE statementContentEntryUid = ContentEntry.contentEntryUid \n\t\t\t\t\t\t\t        AND StatementEntity.statementPersonUid = :personUid\n\t\t\t\t\t\t\t        AND contentEntryRoot \n                               ORDER BY resultScoreScaled DESC, extensionProgress DESC, resultSuccess DESC LIMIT 1)\n                    \n                    LEFT JOIN Container \n                    ON Container.containerUid = \n                        (SELECT containerUid \n                           FROM Container \n                          WHERE containerContentEntryUid = ContentEntry.contentEntryUid \n                       ORDER BY cntLastModified DESC LIMIT 1)\n            WHERE ContentEntryParentChildJoin.cepcjParentContentEntryUid = :parentUid \n            AND (:langParam = 0 OR ContentEntry.primaryLanguageUid = :langParam) \n            AND (NOT ContentEntry.ceInactive OR ContentEntry.ceInactive = :showHidden) \n            AND (NOT ContentEntry.leaf OR NOT ContentEntry.leaf = :onlyFolder) \n            AND (ContentEntry.publik \n                 OR (SELECT username\n                        FROM Person\n                       WHERE personUid = :personUid) IS NOT NULL) \n            AND \n            (:categoryParam0 = 0 OR :categoryParam0 \n                IN (SELECT ceccjContentCategoryUid \n                      FROM ContentEntryContentCategoryJoin \n                     WHERE ceccjContentEntryUid = ContentEntry.contentEntryUid)) \n            ORDER BY ContentEntryParentChildJoin.childIndex,\n                     CASE(:sortOrder)\n                     WHEN 1 THEN ContentEntry.title\n                     ELSE ''\n                     END ASC,\n                     CASE(:sortOrder)\n                     WHEN 2 THEN ContentEntry.title\n                     ELSE ''\n                     END DESC,             \n                     ContentEntry.contentEntryUid")
  public actual abstract fun getChildrenByParentUidWithCategoryFilterOrderByName(
    parentUid: Long,
    langParam: Long,
    categoryParam0: Long,
    personUid: Long,
    showHidden: Boolean,
    onlyFolder: Boolean,
    sortOrder: Int,
  ): DataSource.Factory<Int, ContentEntryWithParentChildJoinAndStatusAndMostRecentContainer>

  @Query(`value` =
      "\n        SELECT ContentEntry.*, ContentEntryParentChildJoin.*, Container.*, \n                COALESCE(StatementEntity.resultScoreMax,0) AS resultMax, \n                COALESCE(StatementEntity.resultScoreRaw,0) AS resultScore, \n                COALESCE(StatementEntity.resultScoreScaled,0) AS resultScaled, \n                COALESCE(StatementEntity.extensionProgress,0) AS progress, \n                COALESCE(StatementEntity.resultCompletion,'FALSE') AS contentComplete,\n                COALESCE(StatementEntity.resultSuccess, 0) AS success,\n                COALESCE((CASE WHEN StatementEntity.resultCompletion \n                THEN 1 ELSE 0 END),0) AS totalCompletedContent,\n                0 AS assignmentContentWeight,\n                \n                1 as totalContent, \n                \n                0 as penalty\n          FROM CourseBlock\n               JOIN ContentEntry \n                    ON CourseBlock.cbType = 104\n                       AND ContentEntry.contentEntryUid = CourseBlock.cbEntityUid\n                       AND CAST(CourseBlock.cbActive AS INTEGER) = 1\n               LEFT JOIN ContentEntryParentChildJoin \n                    ON ContentEntryParentChildJoin.cepcjUid = 0 \n               LEFT JOIN StatementEntity\n\t\t\t\t\t\t\tON StatementEntity.statementUid = \n                                (SELECT statementUid \n\t\t\t\t\t\t\t       FROM StatementEntity \n                                  WHERE statementContentEntryUid = ContentEntry.contentEntryUid \n\t\t\t\t\t\t\t        AND StatementEntity.statementPersonUid = :personUid\n\t\t\t\t\t\t\t        AND contentEntryRoot \n                               ORDER BY resultScoreScaled DESC, extensionProgress DESC, resultSuccess DESC LIMIT 1)     \n               LEFT JOIN Container \n                    ON Container.containerUid = \n                        (SELECT containerUid \n                           FROM Container \n                          WHERE containerContentEntryUid = ContentEntry.contentEntryUid \n                       ORDER BY cntLastModified DESC LIMIT 1)  \n                               \n         WHERE CourseBlock.cbClazzUid IN\n               (SELECT ClazzEnrolment.clazzEnrolmentClazzUid\n                  FROM ClazzEnrolment\n                 WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :personUid)\n    ")
  public actual abstract fun getContentFromMyCourses(personUid: Long):
      DataSource.Factory<Int, ContentEntryWithParentChildJoinAndStatusAndMostRecentContainer>

  @Query(`value` =
      "\n        SELECT ContentEntry.*, ContentEntryParentChildJoin.*, Container.*, \n                COALESCE(StatementEntity.resultScoreMax,0) AS resultMax, \n                COALESCE(StatementEntity.resultScoreRaw,0) AS resultScore, \n                COALESCE(StatementEntity.resultScoreScaled,0) AS resultScaled, \n                COALESCE(StatementEntity.extensionProgress,0) AS progress, \n                COALESCE(StatementEntity.resultCompletion,'FALSE') AS contentComplete,\n                COALESCE(StatementEntity.resultSuccess, 0) AS success,\n                COALESCE((CASE WHEN StatementEntity.resultCompletion \n                THEN 1 ELSE 0 END),0) AS totalCompletedContent,\n                0 AS assignmentContentWeight,\n                \n                1 as totalContent, \n                \n                0 as penalty\n          FROM ContentEntry\n               LEFT JOIN ContentEntryParentChildJoin \n                    ON ContentEntryParentChildJoin.cepcjUid = 0 \n               LEFT JOIN StatementEntity\n\t\t\t\t\t\t\tON StatementEntity.statementUid = \n                                (SELECT statementUid \n\t\t\t\t\t\t\t       FROM StatementEntity \n                                  WHERE statementContentEntryUid = ContentEntry.contentEntryUid \n\t\t\t\t\t\t\t        AND StatementEntity.statementPersonUid = :personUid\n\t\t\t\t\t\t\t        AND contentEntryRoot \n                               ORDER BY resultScoreScaled DESC, extensionProgress DESC, resultSuccess DESC LIMIT 1)     \n               LEFT JOIN Container \n                    ON Container.containerUid = \n                        (SELECT containerUid \n                           FROM Container \n                          WHERE containerContentEntryUid = ContentEntry.contentEntryUid \n                       ORDER BY cntLastModified DESC LIMIT 1)  \n         WHERE ContentEntry.contentOwner = :personUid\n           AND NOT EXISTS(\n               SELECT ContentEntryParentChildJoin.cepcjUid \n                 FROM ContentEntryParentChildJoin\n                WHERE ContentEntryParentChildJoin.cepcjChildContentEntryUid = ContentEntry.contentEntryUid)\n    ")
  public actual abstract fun getContentByOwner(personUid: Long):
      DataSource.Factory<Int, ContentEntryWithParentChildJoinAndStatusAndMostRecentContainer>

  @Update(onConflict = 3)
  public actual abstract suspend fun updateAsync(entity: ContentEntry): Int

  @Query(`value` =
      "SELECT ContentEntry.* FROM ContentEntry LEFT JOIN ContentEntryParentChildJoin ON ContentEntryParentChildJoin.cepcjChildContentEntryUid = ContentEntry.contentEntryUid WHERE ContentEntryParentChildJoin.cepcjParentContentEntryUid = :parentUid")
  public actual abstract fun getChildrenByAll(parentUid: Long): List<ContentEntry>

  @Query(`value` = "SELECT * FROM ContentEntry where contentEntryUid = :parentUid LIMIT 1")
  public actual abstract fun findLiveContentEntry(parentUid: Long): LiveData<ContentEntry?>

  @Query(`value` =
      "SELECT COALESCE((SELECT contentEntryUid \n                                      FROM ContentEntry \n                                     WHERE entryId = :objectId \n                                     LIMIT 1),0) AS ID")
  public actual abstract fun getContentEntryUidFromXapiObjectId(objectId: String): Long

  @Query(`value` = "SELECT * FROM ContentEntry WHERE sourceUrl LIKE :sourceUrl")
  public actual abstract fun findSimilarIdEntryForKhan(sourceUrl: String): List<ContentEntry>

  @Query(`value` =
      "\n        WITH RECURSIVE \n               ContentEntry_recursive(contentEntryUid, containerSize) AS (\n               SELECT contentEntryUid, \n                            (SELECT COALESCE((SELECT fileSize \n                                           FROM Container \n                                          WHERE containerContentEntryUid = ContentEntry.contentEntryUid \n                                       ORDER BY cntLastModified DESC LIMIT 1), 0)) AS containerSize \n                 FROM ContentEntry \n                WHERE contentEntryUid = :contentEntryUid\n                  AND NOT ceInactive\n        UNION \n            SELECT ContentEntry.contentEntryUid, \n                (SELECT COALESCE((SELECT fileSize \n                                    FROM Container \n                                   WHERE containerContentEntryUid = ContentEntry.contentEntryUid \n                                ORDER BY cntLastModified DESC LIMIT 1), 0)) AS containerSize  \n                  FROM ContentEntry\n             LEFT JOIN ContentEntryParentChildJoin \n                    ON ContentEntryParentChildJoin.cepcjChildContentEntryUid = ContentEntry.contentEntryUid,\n                            ContentEntry_recursive\n                  WHERE ContentEntryParentChildJoin.cepcjParentContentEntryUid = ContentEntry_recursive.contentEntryUid\n                    AND NOT ceInactive)\n        SELECT COUNT(*) AS numEntries, \n               SUM(containerSize) AS totalSize \n          FROM ContentEntry_recursive")
  public actual abstract suspend fun getRecursiveDownloadTotals(contentEntryUid: Long):
      DownloadJobSizeInfo?

  @Query(`value` =
      "WITH RECURSIVE ContentEntry_recursive(\n            contentEntryUid, title, ceInactive, contentFlags, description, entryId, author, publisher, licenseType, licenseName, licenseUrl, sourceUrl, thumbnailUrl, lastModified, primaryLanguageUid, languageVariantUid, leaf, publik,  completionCriteria, minScore, contentOwner, contentTypeFlag, contentEntryLocalChangeSeqNum, contentEntryMasterChangeSeqNum, contentEntryLastChangedBy, contentEntryLct,\n            \n            cepcjUid, cepcjChildContentEntryUid, cepcjParentContentEntryUid, childIndex, cepcjLocalChangeSeqNum, cepcjMasterChangeSeqNum, cepcjLastChangedBy, cepcjLct,\n            \n            containerUid, cntLocalCsn, cntMasterCsn, cntLastModBy, fileSize, containerContentEntryUid, cntLastModified, mimeType, remarks, mobileOptimized, cntNumEntries, cntLct\n            ) AS (\n            SELECT ContentEntry.contentEntryUid, ContentEntry.title, ContentEntry.ceInactive, ContentEntry.contentFlags, ContentEntry.description, ContentEntry.entryId, ContentEntry.author, ContentEntry.publisher, ContentEntry.licenseType, ContentEntry.licenseName, ContentEntry.licenseUrl, ContentEntry.sourceUrl, ContentEntry.thumbnailUrl, ContentEntry.lastModified, ContentEntry.primaryLanguageUid, ContentEntry.languageVariantUid, ContentEntry.leaf, ContentEntry.publik, ContentEntry.completionCriteria, ContentEntry.minScore, ContentEntry.contentOwner, ContentEntry.contentTypeFlag, ContentEntry.contentEntryLocalChangeSeqNum, ContentEntry.contentEntryMasterChangeSeqNum, ContentEntry.contentEntryLastChangedBy, ContentEntry.contentEntryLct,\n            ContentEntryParentChildJoin.cepcjUid, ContentEntryParentChildJoin.cepcjChildContentEntryUid, ContentEntryParentChildJoin.cepcjParentContentEntryUid, ContentEntryParentChildJoin.childIndex, ContentEntryParentChildJoin.cepcjLocalChangeSeqNum, ContentEntryParentChildJoin.cepcjMasterChangeSeqNum, ContentEntryParentChildJoin.cepcjLastChangedBy, ContentEntryParentChildJoin.cepcjLct,\n            Container.containerUid, Container.cntLocalCsn, Container.cntMasterCsn, Container.cntLastModBy, Container.fileSize, Container.containerContentEntryUid, Container.cntLastModified, Container.mimeType, Container.remarks, Container.mobileOptimized, Container.cntNumEntries, Container.cntLct\n            FROM \n            ContentEntry\n            LEFT JOIN ContentEntryParentChildJoin ON ContentEntry.contentEntryUid = ContentEntryParentChildJoin.cepcjChildContentEntryUid \n            LEFT JOIN Container ON Container.containerUid = (SELECT COALESCE((SELECT containerUid FROM Container WHERE containerContentEntryUid = ContentEntry.contentEntryUid ORDER BY cntLastModified DESC LIMIT 1), 0))\n            WHERE ContentEntry.contentEntryUid = :contentEntryUid\n            UNION\n            SELECT ContentEntry.contentEntryUid, ContentEntry.title, ContentEntry.ceInactive, ContentEntry.contentFlags, ContentEntry.description, ContentEntry.entryId, ContentEntry.author, ContentEntry.publisher, ContentEntry.licenseType, ContentEntry.licenseName, ContentEntry.licenseUrl, ContentEntry.sourceUrl, ContentEntry.thumbnailUrl, ContentEntry.lastModified, ContentEntry.primaryLanguageUid, ContentEntry.languageVariantUid, ContentEntry.leaf, ContentEntry.publik, ContentEntry.completionCriteria, ContentEntry.minScore, ContentEntry.contentOwner, ContentEntry.contentTypeFlag, ContentEntry.contentEntryLocalChangeSeqNum, ContentEntry.contentEntryMasterChangeSeqNum, ContentEntry.contentEntryLastChangedBy, ContentEntry.contentEntryLct,\n            ContentEntryParentChildJoin.cepcjUid, ContentEntryParentChildJoin.cepcjChildContentEntryUid, ContentEntryParentChildJoin.cepcjParentContentEntryUid, ContentEntryParentChildJoin.childIndex, ContentEntryParentChildJoin.cepcjLocalChangeSeqNum, ContentEntryParentChildJoin.cepcjMasterChangeSeqNum, ContentEntryParentChildJoin.cepcjLastChangedBy, ContentEntryParentChildJoin.cepcjLct, \n            Container.containerUid, Container.cntLocalCsn, Container.cntMasterCsn, Container.cntLastModBy, Container.fileSize, Container.containerContentEntryUid, Container.cntLastModified, Container.mimeType, Container.remarks, Container.mobileOptimized, Container.cntNumEntries, Container.cntLct\n            FROM \n            ContentEntry\n            LEFT JOIN ContentEntryParentChildJoin ON ContentEntry.contentEntryUid = ContentEntryParentChildJoin.cepcjChildContentEntryUid \n            LEFT JOIN Container ON Container.containerUid = (SELECT COALESCE((SELECT containerUid FROM Container WHERE containerContentEntryUid = ContentEntry.contentEntryUid ORDER BY cntLastModified DESC LIMIT 1), 0)),\n            ContentEntry_recursive\n            WHERE ContentEntryParentChildJoin.cepcjParentContentEntryUid = ContentEntry_recursive.contentEntryUid)\n            SELECT * FROM ContentEntry_recursive")
  public actual abstract fun getAllEntriesRecursively(contentEntryUid: Long):
      DataSource.Factory<Int, ContentEntryWithParentChildJoinAndMostRecentContainer>

  @Query(`value` =
      "WITH RECURSIVE ContentEntry_recursive(\n            contentEntryUid, title, ceInactive, contentFlags, description, entryId, author, publisher, licenseType, licenseName, licenseUrl, sourceUrl, thumbnailUrl, lastModified, primaryLanguageUid, languageVariantUid, leaf, publik,  completionCriteria, minScore, contentOwner, contentTypeFlag, contentEntryLocalChangeSeqNum, contentEntryMasterChangeSeqNum, contentEntryLastChangedBy, contentEntryLct,\n            \n            cepcjUid, cepcjChildContentEntryUid, cepcjParentContentEntryUid, childIndex, cepcjLocalChangeSeqNum, cepcjMasterChangeSeqNum, cepcjLastChangedBy, cepcjLct,\n            \n            containerUid, cntLocalCsn, cntMasterCsn, cntLastModBy, fileSize, containerContentEntryUid, cntLastModified, mimeType, remarks, mobileOptimized, cntNumEntries, cntLct\n            ) AS (\n            SELECT ContentEntry.contentEntryUid, ContentEntry.title, ContentEntry.ceInactive, ContentEntry.contentFlags, ContentEntry.description, ContentEntry.entryId, ContentEntry.author, ContentEntry.publisher, ContentEntry.licenseType, ContentEntry.licenseName, ContentEntry.licenseUrl, ContentEntry.sourceUrl, ContentEntry.thumbnailUrl, ContentEntry.lastModified, ContentEntry.primaryLanguageUid, ContentEntry.languageVariantUid, ContentEntry.leaf, ContentEntry.publik, ContentEntry.completionCriteria, ContentEntry.minScore, ContentEntry.contentOwner, ContentEntry.contentTypeFlag, ContentEntry.contentEntryLocalChangeSeqNum, ContentEntry.contentEntryMasterChangeSeqNum, ContentEntry.contentEntryLastChangedBy, ContentEntry.contentEntryLct,\n            ContentEntryParentChildJoin.cepcjUid, ContentEntryParentChildJoin.cepcjChildContentEntryUid, ContentEntryParentChildJoin.cepcjParentContentEntryUid, ContentEntryParentChildJoin.childIndex, ContentEntryParentChildJoin.cepcjLocalChangeSeqNum, ContentEntryParentChildJoin.cepcjMasterChangeSeqNum, ContentEntryParentChildJoin.cepcjLastChangedBy, ContentEntryParentChildJoin.cepcjLct,\n            Container.containerUid, Container.cntLocalCsn, Container.cntMasterCsn, Container.cntLastModBy, Container.fileSize, Container.containerContentEntryUid, Container.cntLastModified, Container.mimeType, Container.remarks, Container.mobileOptimized, Container.cntNumEntries, Container.cntLct\n            FROM \n            ContentEntry\n            LEFT JOIN ContentEntryParentChildJoin ON ContentEntry.contentEntryUid = ContentEntryParentChildJoin.cepcjChildContentEntryUid \n            LEFT JOIN Container ON Container.containerUid = (SELECT COALESCE((SELECT containerUid FROM Container WHERE containerContentEntryUid = ContentEntry.contentEntryUid ORDER BY cntLastModified DESC LIMIT 1), 0))\n            WHERE ContentEntry.contentEntryUid = :contentEntryUid\n            UNION\n            SELECT ContentEntry.contentEntryUid, ContentEntry.title, ContentEntry.ceInactive, ContentEntry.contentFlags, ContentEntry.description, ContentEntry.entryId, ContentEntry.author, ContentEntry.publisher, ContentEntry.licenseType, ContentEntry.licenseName, ContentEntry.licenseUrl, ContentEntry.sourceUrl, ContentEntry.thumbnailUrl, ContentEntry.lastModified, ContentEntry.primaryLanguageUid, ContentEntry.languageVariantUid, ContentEntry.leaf, ContentEntry.publik, ContentEntry.completionCriteria, ContentEntry.minScore, ContentEntry.contentOwner, ContentEntry.contentTypeFlag, ContentEntry.contentEntryLocalChangeSeqNum, ContentEntry.contentEntryMasterChangeSeqNum, ContentEntry.contentEntryLastChangedBy, ContentEntry.contentEntryLct,\n            ContentEntryParentChildJoin.cepcjUid, ContentEntryParentChildJoin.cepcjChildContentEntryUid, ContentEntryParentChildJoin.cepcjParentContentEntryUid, ContentEntryParentChildJoin.childIndex, ContentEntryParentChildJoin.cepcjLocalChangeSeqNum, ContentEntryParentChildJoin.cepcjMasterChangeSeqNum, ContentEntryParentChildJoin.cepcjLastChangedBy, ContentEntryParentChildJoin.cepcjLct, \n            Container.containerUid, Container.cntLocalCsn, Container.cntMasterCsn, Container.cntLastModBy, Container.fileSize, Container.containerContentEntryUid, Container.cntLastModified, Container.mimeType, Container.remarks, Container.mobileOptimized, Container.cntNumEntries, Container.cntLct\n            FROM \n            ContentEntry\n            LEFT JOIN ContentEntryParentChildJoin ON ContentEntry.contentEntryUid = ContentEntryParentChildJoin.cepcjChildContentEntryUid \n            LEFT JOIN Container ON Container.containerUid = (SELECT COALESCE((SELECT containerUid FROM Container WHERE containerContentEntryUid = ContentEntry.contentEntryUid ORDER BY cntLastModified DESC LIMIT 1), 0)),\n            ContentEntry_recursive\n            WHERE ContentEntryParentChildJoin.cepcjParentContentEntryUid = ContentEntry_recursive.contentEntryUid)\n            SELECT * FROM ContentEntry_recursive")
  public actual abstract fun getAllEntriesRecursivelyAsList(contentEntryUid: Long):
      List<ContentEntryWithParentChildJoinAndMostRecentContainer>

  @Query(`value` =
      "\n            UPDATE ContentEntry \n               SET ceInactive = :ceInactive,\n                   contentEntryLct = :changedTime        \n            WHERE ContentEntry.contentEntryUid = :contentEntryUid")
  public actual abstract fun updateContentEntryInActive(
    contentEntryUid: Long,
    ceInactive: Boolean,
    changedTime: Long,
  ): Unit

  @Query(`value` =
      "\n        UPDATE ContentEntry \n           SET contentTypeFlag = :contentFlag,\n               contentEntryLct = :changedTime \n         WHERE ContentEntry.contentEntryUid = :contentEntryUid")
  public actual abstract fun updateContentEntryContentFlag(
    contentFlag: Int,
    contentEntryUid: Long,
    changedTime: Long,
  ): Unit

  @Insert(onConflict = 1)
  public actual abstract fun replaceList(entries: List<ContentEntry>): Unit

  @Query(`value` =
      "Select ContentEntry.contentEntryUid AS uid, ContentEntry.title As labelName \n                    from ContentEntry WHERE contentEntryUid IN (:contentEntryUids)")
  public actual abstract suspend fun getContentEntryFromUids(contentEntryUids: List<Long>):
      List<UidAndLabel>

  @Insert(onConflict = 1)
  public actual abstract fun insertWithReplace(entry: ContentEntry): Unit

  @Query(`value` =
      "SELECT ContentEntry.*, Language.* FROM ContentEntry LEFT JOIN Language ON Language.langUid = ContentEntry.primaryLanguageUid")
  public actual abstract fun findAllLive(): LiveData<List<ContentEntryWithLanguage>>

  @Query(`value` =
      "SELECT EXISTS(SELECT 1 FROM ContentEntry WHERE ContentEntry.contentEntryUid = :contentEntryUid AND :accountPersonUid IN (\n            SELECT DISTINCT Person.PersonUid FROM Person\n            LEFT JOIN PersonGroupMember ON Person.personUid = PersonGroupMember.groupMemberPersonUid\n            LEFT JOIN EntityRole ON EntityRole.erGroupUid = PersonGroupMember.groupMemberGroupUid\n            LEFT JOIN Role ON EntityRole.erRoleUid = Role.roleUid\n            WHERE \n            CAST(Person.admin AS INTEGER) = 1\n            OR \n            (EntityRole.ertableId = 42 AND \n            EntityRole.erEntityUid = ContentEntry.contentEntryUid AND\n            (Role.rolePermissions &  \n         :permission ) > 0)))")
  public actual abstract suspend fun personHasPermissionWithContentEntry(
    accountPersonUid: Long,
    contentEntryUid: Long,
    permission: Long,
  ): Boolean

  @Query(`value` =
      "\n        UPDATE ContentEntry\n           SET ceInactive = :inactive,\n               contentEntryLct = :changedTime\n         WHERE contentEntryUid IN \n               (SELECT cjiContentEntryUid \n                  FROM ContentJobItem\n                 WHERE cjiJobUid = :jobId\n                   AND CAST(ContentJobItem.cjiContentDeletedOnCancellation AS INTEGER) = 1)\n    ")
  public actual abstract suspend fun updateContentEntryActiveByContentJobUid(
    jobId: Long,
    inactive: Boolean,
    changedTime: Long,
  ): Unit

  @Query(`value` =
      "\n        UPDATE ContentEntry \n           SET ceInactive = :toggleVisibility, \n               contentEntryLct = :changedTime \n         WHERE contentEntryUid IN (:selectedItem)")
  public actual abstract suspend fun toggleVisibilityContentEntryItems(
    toggleVisibility: Boolean,
    selectedItem: List<Long>,
    changedTime: Long,
  ): Unit

  @Query(`value` =
      "\nSELECT ContentEntry.*\n  FROM ContentEntry\n       JOIN Container ON Container.containerUid = \n       (SELECT containerUid \n          FROM Container\n         WHERE Container.containercontententryUid = ContentEntry.contentEntryUid\n           AND Container.cntLastModified = \n               (SELECT MAX(ContainerInternal.cntLastModified)\n                  FROM Container ContainerInternal\n                 WHERE ContainerInternal.containercontententryUid = ContentEntry.contentEntryUid))\n WHERE ContentEntry.leaf \n   AND NOT ContentEntry.ceInactive\n   AND (NOT EXISTS \n       (SELECT ContainerEntry.ceUid\n          FROM ContainerEntry\n         WHERE ContainerEntry.ceContainerUid = Container.containerUid)\n        OR Container.fileSize = 0)   \n    ")
  public actual abstract suspend
      fun findContentEntriesWhereIsLeafAndLatestContainerHasNoEntriesOrHasZeroFileSize():
      List<ContentEntry>

  @Query(`value` =
      "\n        WITH ContentEntryContainerUids AS \n             (SELECT Container.containerUid\n                FROM Container\n               WHERE Container.containerContentEntryUid = :contentEntryUid\n                   AND Container.fileSize > 0),\n                   \n             \n            LatestDownloadedContainer(containerUid) AS\n             (SELECT COALESCE(\n                     (SELECT containerUid\n                        FROM Container\n                       WHERE Container.containerContentEntryUid = :contentEntryUid \n                         AND EXISTS(\n                             SELECT 1\n                               FROM ContainerEntry\n                              WHERE ContainerEntry.ceContainerUid = Container.containerUid)\n                    ORDER BY cntLastModified DESC\n                       LIMIT 1), 0))\n        ,\n                            \n             \n            ActiveContentJobItems(cjiRecursiveStatus, cjiPluginId) AS\n             (SELECT cjiRecursiveStatus, cjiPluginId\n                FROM ContentJobItem\n               WHERE cjiContentEntryUid = :contentEntryUid\n                 AND cjiStatus BETWEEN 4 AND 20)\n        ,\n                  \n            ShowDownload(showDownload) AS \n            (SELECT CAST(:platformDownloadEnabled AS INTEGER) = 1\n                AND (SELECT containerUid FROM LatestDownloadedContainer) = 0\n                AND (SELECT COUNT(*) FROM ActiveContentJobItems) = 0\n                AND (SELECT COUNT(*) FROM ContentEntryContainerUids) > 0)\n                   \n        SELECT (SELECT showDownload FROM ShowDownload)\n               AS showDownloadButton,\n        \n               CAST(:platformDownloadEnabled AS INTEGER) = 0\n               OR (SELECT containerUid FROM LatestDownloadedContainer) != 0          \n               AS showOpenButton,\n       \n               (SELECT NOT showDownload FROM ShowDownload)\n           AND (SELECT COUNT(*) FROM ActiveContentJobItems) = 0    \n           AND (SELECT COALESCE(\n                       (SELECT cntLastModified\n                          FROM Container\n                         WHERE containerContentEntryUid = :contentEntryUid\n                           AND fileSize > 0\n                      ORDER BY cntLastModified DESC), 0)) \n               > (SELECT COALESCE(\n                         (SELECT cntLastModified\n                            FROM Container\n                           WHERE Container.containerUid = \n                                 (SELECT LatestDownloadedContainer.containerUid\n                                    FROM LatestDownloadedContainer)), 0)) \n               AS showUpdateButton,\n               \n               CAST(:platformDownloadEnabled AS INTEGER) = 1\n           AND (SELECT containerUid FROM LatestDownloadedContainer) != 0\n           AND (SELECT COUNT(*) FROM ActiveContentJobItems) = 0    \n               AS showDeleteButton,\n               \n               (SELECT COUNT(*) \n                  FROM ActiveContentJobItems \n                 WHERE cjiPluginId = 10) > 0\n               AS showManageDownloadButton\n    ")
  public actual abstract suspend fun buttonsToShowForContentEntry(contentEntryUid: Long,
      platformDownloadEnabled: Boolean): ContentEntryButtonModel?

  @Query(`value` =
      "\n        SELECT ContentJobItem.cjiRecursiveStatus AS status\n         FROM ContentJobItem\n        WHERE ContentJobItem.cjiContentEntryUid = :contentEntryUid\n          AND ContentJobItem.cjiPluginId != 14\n          AND ContentJobItem.cjiStatus BETWEEN 4 AND 25\n          AND NOT EXISTS(\n              SELECT 1\n                FROM ContentJobItem ContentJobItemInternal\n               WHERE ContentJobItemInternal.cjiContentEntryUid = :contentEntryUid\n                 AND ContentJobItemInternal.cjiPluginId = 14\n                 AND ContentJobItemInternal.cjiFinishTime > ContentJobItem.cjiStartTime)\n     ORDER BY ContentJobItem.cjiFinishTime DESC\n        LIMIT 1\n    ")
  public actual abstract suspend fun statusForDownloadDialog(contentEntryUid: Long): Int

  @Query(`value` =
      "\n        SELECT ContentJobItem.cjiRecursiveStatus AS status, \n               ContentJobItem.cjiRecursiveProgress AS progress,\n               ContentJobItem.cjiRecursiveTotal AS total\n         FROM ContentJobItem\n        WHERE ContentJobItem.cjiContentEntryUid = :contentEntryUid\n          AND ContentJobItem.cjiPluginId != 14\n          AND ContentJobItem.cjiStatus BETWEEN 4 AND 25\n          AND NOT EXISTS(\n              SELECT 1\n                FROM ContentJobItem ContentJobItemInternal\n               WHERE ContentJobItemInternal.cjiContentEntryUid = :contentEntryUid\n                 AND ContentJobItemInternal.cjiPluginId = 14\n                 AND ContentJobItemInternal.cjiFinishTime > ContentJobItem.cjiStartTime)\n     ORDER BY ContentJobItem.cjiFinishTime DESC\n        LIMIT 1\n    ")
  public actual abstract suspend fun statusForContentEntryList(contentEntryUid: Long):
      ContentJobItemProgressAndStatus?
}
