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.Container
import com.ustadmobile.lib.db.entities.ContainerUidAndMimeType
import com.ustadmobile.lib.db.entities.ContainerWithContentEntry
import kotlin.Boolean
import kotlin.Long
import kotlin.String
import kotlin.Unit
import kotlin.collections.List

@Dao
public actual abstract class ContainerDao : BaseDao<Container> {
  @Query(`value` =
      "\n         REPLACE INTO ContainerReplicate(containerPk, containerDestination)\n          SELECT DISTINCT Container.containerUid AS containerPk,\n                 :newNodeId AS containerDestination\n            FROM Container\n           WHERE Container.cntLct != COALESCE(\n                 (SELECT containerVersionId\n                    FROM ContainerReplicate\n                   WHERE containerPk = Container.containerUid\n                     AND containerDestination = :newNodeId), 0) \n          /*psql ON CONFLICT(containerPk, containerDestination) DO UPDATE\n                 SET containerPending = true\n          */       \n    ")
  public actual abstract suspend fun replicateOnNewNode(newNodeId: Long): Unit

  @Query(`value` =
      "\n REPLACE INTO ContainerReplicate(containerPk, containerDestination)\n  SELECT DISTINCT Container.containerUid AS containerUid,\n         UserSession.usClientNodeId AS containerDestination\n    FROM ChangeLog\n         JOIN Container\n             ON ChangeLog.chTableId = 51\n                AND ChangeLog.chEntityPk = Container.containerUid\n         JOIN UserSession ON UserSession.usStatus = 1\n   WHERE UserSession.usClientNodeId != (\n         SELECT nodeClientId \n           FROM SyncNode\n          LIMIT 1)\n     AND Container.cntLct != COALESCE(\n         (SELECT containerVersionId\n            FROM ContainerReplicate\n           WHERE containerPk = Container.containerUid\n             AND containerDestination = UserSession.usClientNodeId), 0)\n /*psql ON CONFLICT(containerPk, containerDestination) DO UPDATE\n     SET containerPending = true\n  */               \n    ")
  public actual abstract suspend fun replicateOnChange(): Unit

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

  @Query(`value` =
      "Select Container.* FROM Container WHERE Container.containerContentEntryUid = :contentEntry ORDER BY Container.cntLastModified DESC LIMIT 1")
  public actual abstract suspend
      fun getMostRecentDownloadedContainerForContentEntryAsync(contentEntry: Long): Container?

  @Query(`value` =
      "\n            SELECT Container.*\n            \n            FROM Container\n             WHERE Container.containerContentEntryUid = :contentEntryUid\n               AND \n            Container.fileSize > 0\n             \n          ORDER BY Container.cntLastModified DESC \n          LIMIT 1\n        \n        ")
  public actual abstract fun getMostRecentContainerForContentEntry(contentEntryUid: Long):
      Container?

  @Query(`value` = "SELECT * FROM Container WHERE containerUid = :uid")
  public actual abstract fun findByUid(uid: Long): Container?

  @Query(`value` =
      "\n        SELECT(COALESCE((\n               SELECT fileSize\n                 FROM Container\n                WHERE containerUid = :uid), 0))\n    ")
  public actual abstract suspend fun findSizeByUid(uid: Long): Long

  @Query(`value` =
      "SELECT recent.* FROM Container recent LEFT JOIN Container old ON (recent.containerContentEntryUid = old.containerContentEntryUid AND recent.cntLastModified < old.cntLastModified) WHERE old.containerUid IS NULL AND recent.containerContentEntryUid IN (:contentEntries)")
  public actual abstract suspend
      fun findRecentContainerToBeMonitoredWithEntriesUid(contentEntries: List<Long>):
      List<Container>

  @Query(`value` =
      "Select Container.* FROM Container \n                    WHERE Container.containerContentEntryUid = :contentEntryUid\n                    ORDER BY Container.cntLastModified DESC")
  public actual abstract suspend fun findContainersForContentEntryUid(contentEntryUid: Long):
      List<Container>

  @Query(`value` =
      "\n          SELECT EXISTS(SELECT 1\n                          FROM Container \n                         WHERE Container.containerContentEntryUid = :contentEntryUid\n                           AND NOT EXISTS (SELECT ContainerEntry.ceUid \n                                         FROM ContainerEntry\n                                        WHERE ContainerEntry.ceContainerUid = Container.containerUid)   \n                      ORDER BY cntLastModified DESC LIMIT 1)\n    ")
  public actual abstract fun hasContainerWithFilesToDownload(contentEntryUid: Long):
      LiveData<Boolean>

  @Query(`value` =
      "\n            SELECT Container.*\n              FROM Container\n             WHERE Container.containerContentEntryUid = :contentEntryUid\n               AND EXISTS (SELECT ContainerEntry.ceUid \n                             FROM ContainerEntry\n                            WHERE ContainerEntry.ceContainerUid = Container.containerUid)     \n          ORDER BY Container.cntLastModified DESC LIMIT 1\n    ")
  public actual abstract suspend fun findContainerWithFilesByContentEntryUid(contentEntryUid: Long):
      Container?

  @Query(`value` =
      "SELECT Container.* FROM Container LEFT JOIN ContentEntry ON ContentEntry.contentEntryUid = containerContentEntryUid WHERE ContentEntry.publik")
  public actual abstract fun findAllPublikContainers(): List<Container>

  @Query(`value` = "SELECT * From Container WHERE Container.containerUid = :containerUid LIMIT 1")
  public actual abstract suspend fun findByUidAsync(containerUid: Long): Container?

  @Query(`value` =
      "\n            UPDATE Container \n               SET cntNumEntries = COALESCE(\n                   (SELECT COUNT(*) \n                      FROM ContainerEntry \n                     WHERE ceContainerUid = Container.containerUid), 0),\n                   fileSize = COALESCE(\n                   (SELECT SUM(ContainerEntryFile.ceCompressedSize) AS totalSize \n                      FROM ContainerEntry\n                      JOIN ContainerEntryFile ON ContainerEntry.ceCefUid = ContainerEntryFile.cefUid\n                     WHERE ContainerEntry.ceContainerUid = Container.containerUid), 0),\n                   cntLct = :changeTime   \n                     \n             WHERE containerUid = :containerUid\n        ")
  public actual abstract suspend fun updateContainerSizeAndNumEntriesAsync(containerUid: Long,
      changeTime: Long): Unit

  @Query(`value` =
      "SELECT Container.containerUid FROM Container WHERE Container.containerUid = :containerUid AND (SELECT COUNT(*) FROM ContainerEntry WHERE ceContainerUid = Container.containerUid) = Container.cntNumEntries")
  public actual abstract fun findLocalAvailabilityByUid(containerUid: Long): Long

  @Query(`value` = "SELECT * FROM Container WHERE Container.containerUid = :containerUid")
  public actual abstract fun findAllWithId(containerUid: Long): List<Container>

  @Query(`value` =
      "SELECT Container.*, ContentEntry.entryId, ContentEntry.sourceUrl FROM Container LEFT JOIN ContentEntry ON Container.containerContentEntryUid = ContentEntry.contentEntryUid WHERE ContentEntry.publisher LIKE '%Khan Academy%' AND Container.mimeType = 'video/mp4'")
  public actual abstract fun findKhanContainers(): List<ContainerWithContentEntry>

  @Query(`value` = "DELETE FROM Container WHERE containerUid = :containerUid")
  public actual abstract fun deleteByUid(containerUid: Long): Unit

  @Query(`value` =
      "UPDATE Container SET mimeType = :mimeType WHERE Container.containerUid = :containerUid")
  public actual abstract fun updateMimeType(mimeType: String, containerUid: Long): Unit

  @Query(`value` =
      "\n            SELECT Container.*\n            \n            FROM Container\n             WHERE Container.containerContentEntryUid = :contentEntryUid\n               AND \n            Container.fileSize > 0\n             \n          ORDER BY Container.cntLastModified DESC \n          LIMIT 1\n        \n        ")
  public actual abstract suspend
      fun getMostRecentContainerForContentEntryAsync(contentEntryUid: Long): Container?

  @Query(`value` =
      "\n        SELECT COALESCE((\n                SELECT containerUid \n                 \n            FROM Container\n             WHERE Container.containerContentEntryUid = :contentEntryUid\n               AND \n            Container.fileSize > 0\n             \n          ORDER BY Container.cntLastModified DESC \n          LIMIT 1\n        ), 0)\n    ")
  public actual abstract suspend
      fun getMostRecentContainerUidForContentEntryAsync(contentEntryUid: Long): Long

  @Query(`value` =
      "\n        SELECT Container.containerUid, Container.mimeType \n          FROM Container\n         WHERE Container.containerContentEntryUid = :contentEntryUid\n           AND \n            Container.fileSize > 0\n        \n           AND (CAST(:downloadRequired AS INTEGER) = 0\n                OR EXISTS (SELECT ContainerEntry.ceUid \n                             FROM ContainerEntry\n                            WHERE ContainerEntry.ceContainerUid = Container.containerUid))\n      ORDER BY Container.cntLastModified DESC \n         LIMIT 1\n    ")
  public actual abstract suspend
      fun getMostRecentAvailableContainerUidAndMimeType(contentEntryUid: Long,
      downloadRequired: Boolean): ContainerUidAndMimeType?

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

  @Insert(onConflict = 1)
  public actual abstract fun insertWithReplace(container: Container): Unit

  @Query(`value` =
      "\n        SELECT COALESCE(\n               (SELECT fileSize\n                  FROM Container\n                 WHERE containerUid = :containerUid), -1)\n    ")
  public actual abstract suspend fun getContainerSizeByUid(containerUid: Long): Long
}
