package com.ustadmobile.core.db.dao

import com.ustadmobile.door.DoorDbType
import com.ustadmobile.door.EntityInsertionAdapter
import com.ustadmobile.door.PreparedStatementConfig
import com.ustadmobile.door.ext.prepareAndUseStatementAsync
import com.ustadmobile.door.flow.doorFlow
import com.ustadmobile.door.jdbc.PreparedStatement
import com.ustadmobile.door.jdbc.ext.executeQueryAsyncKmp
import com.ustadmobile.door.jdbc.ext.executeUpdateAsyncKmp
import com.ustadmobile.door.jdbc.ext.mapNextRow
import com.ustadmobile.door.jdbc.ext.useResults
import com.ustadmobile.door.room.RoomDatabase
import com.ustadmobile.lib.db.composites.OfflineItemAndState
import com.ustadmobile.lib.db.composites.TransferJobAndTotals
import com.ustadmobile.lib.db.entities.OfflineItem
import com.ustadmobile.lib.db.entities.TransferJob
import kotlin.Boolean
import kotlin.Long
import kotlin.String
import kotlinx.coroutines.flow.Flow

public class OfflineItemDao_JdbcImpl(
  public val _db: RoomDatabase,
) : OfflineItemDao() {
  public val _insertAdapterOfflineItem_abort: EntityInsertionAdapter<OfflineItem> = object :
      EntityInsertionAdapter<OfflineItem>(_db) {
    override fun makeSql(returnsId: Boolean): String =
        "INSERT INTO OfflineItem (oiUid, oiNodeId, oiClazzUid, oiCourseBlockUid, oiContentEntryUid, oiActive, oiLct) VALUES(?, ?, ?, ?, ?, ?, ?)"

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement, entity: OfflineItem) {
      if(entity.oiUid == 0L) {
        stmt.setObject(1, null)
      } else {
        stmt.setLong(1, entity.oiUid)
      }
      stmt.setLong(2, entity.oiNodeId)
      stmt.setLong(3, entity.oiClazzUid)
      stmt.setLong(4, entity.oiCourseBlockUid)
      stmt.setLong(5, entity.oiContentEntryUid)
      stmt.setBoolean(6, entity.oiActive)
      stmt.setLong(7, entity.oiLct)
    }
  }

  override suspend fun insertAsync(item: OfflineItem): Long {
    val _retVal = _insertAdapterOfflineItem_abort.insertAndReturnIdAsync(item)
    return _retVal
  }

  override fun findByContentEntryUid(contentEntryUid: Long, nodeId: Long):
      Flow<OfflineItemAndState?> = _db.doorFlow(arrayOf("OfflineItem", "TransferJob",
      "TransferJobItem")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT OfflineItem.*,
      |               TransferJob.*,
      |               
      |        (SELECT SUM(TransferJobItem.tjTotalSize)
      |                   FROM TransferJobItem
      |                  WHERE TransferJobItem.tjiTjUid =  TransferJob.tjUid) AS totalSize,
      |                (SELECT SUM(TransferJobItem.tjTransferred)
      |                   FROM TransferJobItem
      |                  WHERE TransferJobItem.tjiTjUid =  TransferJob.tjUid) AS transferred 
      |    ,
      |               CAST(OfflineItem.oiActive AS INTEGER) = 1 AND (SELECT EXISTS(
      |                       SELECT CompletedJob.tjUid
      |                         FROM TransferJob CompletedJob
      |                        WHERE CompletedJob.tjTableId = 738
      |                          AND CompletedJob.tjEntityUid IN  
      |                              
      |        (SELECT ContentEntryVersion.cevUid
      |                         FROM ContentEntryVersion
      |                        WHERE ContentEntryVersion.cevContentEntryUid = CAST(? AS BIGINT))
      |    
      |                          AND CompletedJob.tjStatus = 21
      |                          AND CompletedJob.tjType = 2
      |                          AND CompletedJob.tjTimeCreated >= OfflineItem.oiLct
      |                        LIMIT 1      
      |               )) AS readyForOffline,
      |               NULL AS latestErrorStr
      |          FROM OfflineItem
      |               LEFT JOIN TransferJob 
      |                         ON TransferJob.tjUid = 
      |                         (SELECT TransferJob.tjUid
      |                            FROM TransferJob
      |                           WHERE TransferJob.tjTableId = 738
      |                             AND TransferJob.tjEntityUid IN  
      |                                 
      |        (SELECT ContentEntryVersion.cevUid
      |                         FROM ContentEntryVersion
      |                        WHERE ContentEntryVersion.cevContentEntryUid = CAST(? AS BIGINT))
      |    
      |                             AND TransferJob.tjStatus < 21
      |                        ORDER BY TransferJob.tjTimeCreated DESC     
      |                           LIMIT 1)
      |         WHERE OfflineItem.oiNodeId = CAST(? AS BIGINT)
      |           AND OfflineItem.oiContentEntryUid = CAST(? AS BIGINT)
      |      ORDER BY OfflineItem.oiLct DESC
      |         LIMIT 1     
      |    
      """.trimMargin(),
      postgreSql = """
      |
      |        SELECT OfflineItem.*,
      |               TransferJob.*,
      |               
      |        (SELECT SUM(TransferJobItem.tjTotalSize)
      |                   FROM TransferJobItem
      |                  WHERE TransferJobItem.tjiTjUid =  TransferJob.tjUid) AS totalSize,
      |                (SELECT SUM(TransferJobItem.tjTransferred)
      |                   FROM TransferJobItem
      |                  WHERE TransferJobItem.tjiTjUid =  TransferJob.tjUid) AS transferred 
      |    ,
      |               CAST(OfflineItem.oiActive AS INTEGER) = 1 AND (SELECT EXISTS(
      |                       SELECT CompletedJob.tjUid
      |                         FROM TransferJob CompletedJob
      |                        WHERE CompletedJob.tjTableId = 738
      |                          AND CompletedJob.tjEntityUid IN  
      |                              
      |        (SELECT ContentEntryVersion.cevUid
      |                         FROM ContentEntryVersion
      |                        WHERE ContentEntryVersion.cevContentEntryUid = ?)
      |    
      |                          AND CompletedJob.tjStatus = 21
      |                          AND CompletedJob.tjType = 2
      |                          AND CompletedJob.tjTimeCreated >= OfflineItem.oiLct
      |                        LIMIT 1      
      |               )) AS readyForOffline,
      |               NULL AS latestErrorStr
      |          FROM OfflineItem
      |               LEFT JOIN TransferJob 
      |                         ON TransferJob.tjUid = 
      |                         (SELECT TransferJob.tjUid
      |                            FROM TransferJob
      |                           WHERE TransferJob.tjTableId = 738
      |                             AND TransferJob.tjEntityUid IN  
      |                                 
      |        (SELECT ContentEntryVersion.cevUid
      |                         FROM ContentEntryVersion
      |                        WHERE ContentEntryVersion.cevContentEntryUid = ?)
      |    
      |                             AND TransferJob.tjStatus < 21
      |                        ORDER BY TransferJob.tjTimeCreated DESC     
      |                           LIMIT 1)
      |         WHERE OfflineItem.oiNodeId = ?
      |           AND OfflineItem.oiContentEntryUid = ?
      |      ORDER BY OfflineItem.oiLct DESC
      |         LIMIT 1     
      |    
      |""".trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,contentEntryUid)
      _stmt.setLong(2,contentEntryUid)
      _stmt.setLong(3,nodeId)
      _stmt.setLong(4,contentEntryUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(null) {
          val _tmp_readyForOffline = _result.getBoolean("readyForOffline")
          var _tmp_OfflineItem_nullCount = 0
          val _tmp_oiUid = _result.getLong("oiUid")
          if(_result.wasNull()) _tmp_OfflineItem_nullCount++
          val _tmp_oiNodeId = _result.getLong("oiNodeId")
          if(_result.wasNull()) _tmp_OfflineItem_nullCount++
          val _tmp_oiClazzUid = _result.getLong("oiClazzUid")
          if(_result.wasNull()) _tmp_OfflineItem_nullCount++
          val _tmp_oiCourseBlockUid = _result.getLong("oiCourseBlockUid")
          if(_result.wasNull()) _tmp_OfflineItem_nullCount++
          val _tmp_oiContentEntryUid = _result.getLong("oiContentEntryUid")
          if(_result.wasNull()) _tmp_OfflineItem_nullCount++
          val _tmp_oiActive = _result.getBoolean("oiActive")
          if(_result.wasNull()) _tmp_OfflineItem_nullCount++
          val _tmp_oiLct = _result.getLong("oiLct")
          if(_result.wasNull()) _tmp_OfflineItem_nullCount++
          val _tmp_OfflineItem_isAllNull = _tmp_OfflineItem_nullCount == 7
          var _tmp_TransferJobAndTotals_nullCount = 0
          val _tmp_totalSize = _result.getLong("totalSize")
          if(_result.wasNull()) _tmp_TransferJobAndTotals_nullCount++
          val _tmp_transferred = _result.getLong("transferred")
          if(_result.wasNull()) _tmp_TransferJobAndTotals_nullCount++
          val _tmp_latestErrorStr = _result.getString("latestErrorStr")
          if(_result.wasNull()) _tmp_TransferJobAndTotals_nullCount++
          val _tmp_TransferJobAndTotals_isAllNull = _tmp_TransferJobAndTotals_nullCount == 3
          var _tmp_TransferJob_nullCount = 0
          val _tmp_tjUid = _result.getInt("tjUid")
          if(_result.wasNull()) _tmp_TransferJob_nullCount++
          val _tmp_tjType = _result.getInt("tjType")
          if(_result.wasNull()) _tmp_TransferJob_nullCount++
          val _tmp_tjStatus = _result.getInt("tjStatus")
          if(_result.wasNull()) _tmp_TransferJob_nullCount++
          val _tmp_tjName = _result.getString("tjName")
          if(_result.wasNull()) _tmp_TransferJob_nullCount++
          val _tmp_tjUuid = _result.getString("tjUuid")
          if(_result.wasNull()) _tmp_TransferJob_nullCount++
          val _tmp_tjTableId = _result.getInt("tjTableId")
          if(_result.wasNull()) _tmp_TransferJob_nullCount++
          val _tmp_tjEntityUid = _result.getLong("tjEntityUid")
          if(_result.wasNull()) _tmp_TransferJob_nullCount++
          val _tmp_tjTimeCreated = _result.getLong("tjTimeCreated")
          if(_result.wasNull()) _tmp_TransferJob_nullCount++
          val _tmp_tjCreationType = _result.getInt("tjCreationType")
          if(_result.wasNull()) _tmp_TransferJob_nullCount++
          val _tmp_tjOiUid = _result.getLong("tjOiUid")
          if(_result.wasNull()) _tmp_TransferJob_nullCount++
          val _tmp_TransferJob_isAllNull = _tmp_TransferJob_nullCount == 10
          OfflineItemAndState().apply {
            this.readyForOffline = _tmp_readyForOffline
            if(!_tmp_OfflineItem_isAllNull) {
              this.offlineItem = OfflineItem().apply {
                this.oiUid = _tmp_oiUid
                this.oiNodeId = _tmp_oiNodeId
                this.oiClazzUid = _tmp_oiClazzUid
                this.oiCourseBlockUid = _tmp_oiCourseBlockUid
                this.oiContentEntryUid = _tmp_oiContentEntryUid
                this.oiActive = _tmp_oiActive
                this.oiLct = _tmp_oiLct
              }
            }
            if(!_tmp_TransferJobAndTotals_isAllNull) {
              this.activeDownload = TransferJobAndTotals().apply {
                this.totalSize = _tmp_totalSize
                this.transferred = _tmp_transferred
                this.latestErrorStr = _tmp_latestErrorStr
                if(!_tmp_TransferJob_isAllNull) {
                  this.transferJob = TransferJob().apply {
                    this.tjUid = _tmp_tjUid
                    this.tjType = _tmp_tjType
                    this.tjStatus = _tmp_tjStatus
                    this.tjName = _tmp_tjName
                    this.tjUuid = _tmp_tjUuid
                    this.tjTableId = _tmp_tjTableId
                    this.tjEntityUid = _tmp_tjEntityUid
                    this.tjTimeCreated = _tmp_tjTimeCreated
                    this.tjCreationType = _tmp_tjCreationType
                    this.tjOiUid = _tmp_tjOiUid
                  }
                }
              }
            }
          }
        }
      }
    }
  }

  override suspend fun updateActiveByOfflineItemUid(oiUid: Long, active: Boolean) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        UPDATE OfflineItem
      |           SET oiActive = ?
      |         WHERE oiUid = CAST(? AS BIGINT)   
      |    
      """.trimMargin(),
      postgreSql = """
      |
      |        UPDATE OfflineItem
      |           SET oiActive = ?
      |         WHERE oiUid = ?   
      |    
      |""".trimMargin(),
      readOnly = false,)
    ) { _stmt -> 
      _stmt.setBoolean(1,active)
      _stmt.setLong(2,oiUid)
      _stmt.executeUpdateAsyncKmp()
    }
  }
}
