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.mapRows
import com.ustadmobile.door.jdbc.ext.useResults
import com.ustadmobile.door.room.RoomDatabase
import com.ustadmobile.lib.db.composites.TransferJobAndTotals
import com.ustadmobile.lib.db.entities.TransferJob
import kotlin.Boolean
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.collections.List
import kotlinx.coroutines.flow.Flow

public class TransferJobDao_JdbcImpl(
  public val _db: RoomDatabase,
) : TransferJobDao() {
  public val _insertAdapterTransferJob_abort: EntityInsertionAdapter<TransferJob> = object :
      EntityInsertionAdapter<TransferJob>(_db) {
    override fun makeSql(returnsId: Boolean): String =
        "INSERT INTO TransferJob (tjUid, tjType, tjStatus, tjName, tjUuid, tjTableId, tjEntityUid, tjTimeCreated, tjCreationType, tjOiUid) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement, entity: TransferJob) {
      if(entity.tjUid == 0) {
        stmt.setObject(1, null)
      } else {
        stmt.setInt(1, entity.tjUid)
      }
      stmt.setInt(2, entity.tjType)
      stmt.setInt(3, entity.tjStatus)
      stmt.setString(4, entity.tjName)
      stmt.setString(5, entity.tjUuid)
      stmt.setInt(6, entity.tjTableId)
      stmt.setLong(7, entity.tjEntityUid)
      stmt.setLong(8, entity.tjTimeCreated)
      stmt.setInt(9, entity.tjCreationType)
      stmt.setLong(10, entity.tjOiUid)
    }
  }

  override suspend fun insert(job: TransferJob): Long {
    val _retVal = _insertAdapterTransferJob_abort.insertAndReturnIdAsync(job)
    return _retVal
  }

  override suspend fun findByUid(jobUid: Int): TransferJob? =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT TransferJob.*
    |          FROM TransferJob
    |         WHERE TransferJob.tjUid = ?
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setInt(1,jobUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(null) {
        val _tmp_tjUid = _result.getInt("tjUid")
        val _tmp_tjType = _result.getInt("tjType")
        val _tmp_tjStatus = _result.getInt("tjStatus")
        val _tmp_tjName = _result.getString("tjName")
        val _tmp_tjUuid = _result.getString("tjUuid")
        val _tmp_tjTableId = _result.getInt("tjTableId")
        val _tmp_tjEntityUid = _result.getLong("tjEntityUid")
        val _tmp_tjTimeCreated = _result.getLong("tjTimeCreated")
        val _tmp_tjCreationType = _result.getInt("tjCreationType")
        val _tmp_tjOiUid = _result.getLong("tjOiUid")
        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 fun findByUidAsFlow(jobUid: Int): Flow<TransferJob?> =
      _db.doorFlow(arrayOf("TransferJob")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT TransferJob.*
      |          FROM TransferJob
      |         WHERE TransferJob.tjUid = ?
      |    
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setInt(1,jobUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(null) {
          val _tmp_tjUid = _result.getInt("tjUid")
          val _tmp_tjType = _result.getInt("tjType")
          val _tmp_tjStatus = _result.getInt("tjStatus")
          val _tmp_tjName = _result.getString("tjName")
          val _tmp_tjUuid = _result.getString("tjUuid")
          val _tmp_tjTableId = _result.getInt("tjTableId")
          val _tmp_tjEntityUid = _result.getLong("tjEntityUid")
          val _tmp_tjTimeCreated = _result.getLong("tjTimeCreated")
          val _tmp_tjCreationType = _result.getInt("tjCreationType")
          val _tmp_tjOiUid = _result.getLong("tjOiUid")
          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 updateStatus(jobUid: Int, status: Int) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        UPDATE TransferJob
      |           SET tjStatus = ?
      |         WHERE tjUid = ?  
      |    
      """.trimMargin(),
      readOnly = false,)
    ) { _stmt -> 
      _stmt.setInt(1,status)
      _stmt.setInt(2,jobUid)
      _stmt.executeUpdateAsyncKmp()
    }
  }

  override suspend fun getJobStatus(jobUid: Int): Int =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT COALESCE(
    |            (SELECT TransferJob.tjStatus
    |               FROM TransferJob
    |              WHERE tjUid = ?), 0)
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setInt(1,jobUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(0) {
        _result.getInt(1)
      }
    }
  }

  override suspend fun updateStatusIfComplete(jobUid: Int): Int =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        UPDATE TransferJob
    |           SET tjStatus = 21
    |         WHERE tjUid = ?
    |          AND NOT EXISTS(
    |              SELECT TransferJobItem.tjiUid
    |                FROM TransferJobItem
    |               WHERE TransferJobItem.tjiTjUid = ?
    |                 AND TransferJobItem.tjiStatus != 21) 
    |    
    """.trimMargin(),
    readOnly = false,)
  ) { _stmt -> 
    _stmt.setInt(1,jobUid)
    _stmt.setInt(2,jobUid)
    _stmt.executeUpdateAsyncKmp()
  }

  override suspend fun findJobByEntityAndTableUid(tableId: Int, entityUid: Long): List<TransferJob>
      = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT TransferJob.*
    |          FROM TransferJob
    |         WHERE EXISTS(
    |               SELECT TransferJobItem.tjiUid
    |                 FROM TransferJobItem
    |                WHERE TransferJobItem.tjiTjUid = TransferJob.tjUid
    |                  AND TransferJobItem.tjiTableId = ?
    |                  AND TransferJobItem.tjiEntityUid = CAST(? AS BIGINT)) 
    |    
    """.trimMargin(),
    postgreSql = """
    |
    |        SELECT TransferJob.*
    |          FROM TransferJob
    |         WHERE EXISTS(
    |               SELECT TransferJobItem.tjiUid
    |                 FROM TransferJobItem
    |                WHERE TransferJobItem.tjiTjUid = TransferJob.tjUid
    |                  AND TransferJobItem.tjiTableId = ?
    |                  AND TransferJobItem.tjiEntityUid = ?) 
    |    
    |""".trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setInt(1,tableId)
    _stmt.setLong(2,entityUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_tjUid = _result.getInt("tjUid")
        val _tmp_tjType = _result.getInt("tjType")
        val _tmp_tjStatus = _result.getInt("tjStatus")
        val _tmp_tjName = _result.getString("tjName")
        val _tmp_tjUuid = _result.getString("tjUuid")
        val _tmp_tjTableId = _result.getInt("tjTableId")
        val _tmp_tjEntityUid = _result.getLong("tjEntityUid")
        val _tmp_tjTimeCreated = _result.getLong("tjTimeCreated")
        val _tmp_tjCreationType = _result.getInt("tjCreationType")
        val _tmp_tjOiUid = _result.getLong("tjOiUid")
        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 fun findByContentEntryUidWithTotalsAsFlow(contentEntryUid: Long, jobType: Int):
      Flow<List<TransferJobAndTotals>> = _db.doorFlow(arrayOf("ContentEntryVersion",
      "TransferJobItem", "TransferJobError", "TransferJob")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT 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 
      |    ,
      |               TransferJobError.tjeErrorStr AS latestErrorStr
      |          FROM TransferJob
      |               LEFT JOIN TransferJobError
      |                         ON TransferJobError.tjeId = 
      |                            (SELECT TransferJobError.tjeId
      |                               FROM TransferJobError
      |                              WHERE TransferJob.tjStatus = 22
      |                                AND TransferJobError.tjeTjUid = TransferJob.tjUid
      |                           ORDER BY TransferJobError.tjeDismissed DESC 
      |                              LIMIT 1)
      |         WHERE TransferJob.tjTableId = 738
      |           AND TransferJob.tjEntityUid IN 
      |               
      |        (SELECT ContentEntryVersion.cevUid
      |                         FROM ContentEntryVersion
      |                        WHERE ContentEntryVersion.cevContentEntryUid = CAST(? AS BIGINT))
      |     
      |           AND (   TransferJob.tjStatus < 21
      |                OR (TransferJobError.tjeErrorStr IS NOT NULL AND NOT TransferJobError.tjeDismissed))
      |           AND TransferJob.tjType = ?   
      |    
      """.trimMargin(),
      postgreSql = """
      |
      |        SELECT 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 
      |    ,
      |               TransferJobError.tjeErrorStr AS latestErrorStr
      |          FROM TransferJob
      |               LEFT JOIN TransferJobError
      |                         ON TransferJobError.tjeId = 
      |                            (SELECT TransferJobError.tjeId
      |                               FROM TransferJobError
      |                              WHERE TransferJob.tjStatus = 22
      |                                AND TransferJobError.tjeTjUid = TransferJob.tjUid
      |                           ORDER BY TransferJobError.tjeDismissed DESC 
      |                              LIMIT 1)
      |         WHERE TransferJob.tjTableId = 738
      |           AND TransferJob.tjEntityUid IN 
      |               
      |        (SELECT ContentEntryVersion.cevUid
      |                         FROM ContentEntryVersion
      |                        WHERE ContentEntryVersion.cevContentEntryUid = ?)
      |     
      |           AND (   TransferJob.tjStatus < 21
      |                OR (TransferJobError.tjeErrorStr IS NOT NULL AND NOT TransferJobError.tjeDismissed))
      |           AND TransferJob.tjType = ?   
      |    
      |""".trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,contentEntryUid)
      _stmt.setInt(2,jobType)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          val _tmp_totalSize = _result.getLong("totalSize")
          val _tmp_transferred = _result.getLong("transferred")
          val _tmp_latestErrorStr = _result.getString("latestErrorStr")
          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
          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 findOfflineItemUidForTransferJobUid(jobUid: Int): Long =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT COALESCE(
    |               (SELECT TransferJob.tjOiUid
    |                  FROM TransferJob
    |                 WHERE TransferJob.tjUid = ?), 0)
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setInt(1,jobUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(0L) {
        _result.getLong(1)
      }
    }
  }
}
