package com.ustadmobile.libcache.db.dao

import com.ustadmobile.door.DoorDbType
import com.ustadmobile.door.EntityInsertionAdapter
import com.ustadmobile.door.PreparedStatementConfig
import com.ustadmobile.door.ext.prepareAndUseStatement
import com.ustadmobile.door.ext.prepareAndUseStatementAsync
import com.ustadmobile.door.jdbc.PreparedStatement
import com.ustadmobile.door.jdbc.ext.executeQueryAsyncKmp
import com.ustadmobile.door.jdbc.ext.getStringNonNull
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.libcache.db.entities.CacheEntry
import kotlin.Boolean
import kotlin.IllegalArgumentException
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.collections.List

public class CacheEntryDao_JdbcImpl(
  public val _db: RoomDatabase,
) : CacheEntryDao() {
  public val _insertAdapterCacheEntry_abort: EntityInsertionAdapter<CacheEntry> = object :
      EntityInsertionAdapter<CacheEntry>(_db) {
    override fun makeSql(returnsId: Boolean): String = when(dbType) {
      DoorDbType.SQLITE -> {
        "INSERT INTO CacheEntry (key, url, message, statusCode, cacheFlags, method, lastAccessed, lastValidated, integrity, responseHeaders, storageUri, storageSize, uncompressedSize) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
      }
      DoorDbType.POSTGRES ->  {
        "INSERT INTO CacheEntry (key, url, message, statusCode, cacheFlags, method, lastAccessed, lastValidated, integrity, responseHeaders, storageUri, storageSize, uncompressedSize) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" + if(returnsId) { " RETURNING key" } else "" 
      }
      else -> {
        throw IllegalArgumentException("Unsupported db type")
      }
    }

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement, entity: CacheEntry) {
      stmt.setString(1, entity.key)
      stmt.setString(2, entity.url)
      stmt.setString(3, entity.message)
      stmt.setInt(4, entity.statusCode)
      stmt.setInt(5, entity.cacheFlags)
      stmt.setInt(6, entity.method)
      stmt.setLong(7, entity.lastAccessed)
      stmt.setLong(8, entity.lastValidated)
      stmt.setString(9, entity.integrity)
      stmt.setString(10, entity.responseHeaders)
      stmt.setString(11, entity.storageUri)
      stmt.setLong(12, entity.storageSize)
      stmt.setLong(13, entity.uncompressedSize)
    }
  }

  public val _insertAdapterCacheEntry_upsert: EntityInsertionAdapter<CacheEntry> = object :
      EntityInsertionAdapter<CacheEntry>(_db) {
    override fun makeSql(returnsId: Boolean): String = when(dbType) {
      DoorDbType.SQLITE -> {
        "INSERT OR REPLACE INTO CacheEntry (key, url, message, statusCode, cacheFlags, method, lastAccessed, lastValidated, integrity, responseHeaders, storageUri, storageSize, uncompressedSize) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
      }
      DoorDbType.POSTGRES ->  {
        "INSERT INTO CacheEntry (key, url, message, statusCode, cacheFlags, method, lastAccessed, lastValidated, integrity, responseHeaders, storageUri, storageSize, uncompressedSize) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT (key) DO UPDATE SET url = excluded.url,message = excluded.message,statusCode = excluded.statusCode,cacheFlags = excluded.cacheFlags,method = excluded.method,lastAccessed = excluded.lastAccessed,lastValidated = excluded.lastValidated,integrity = excluded.integrity,responseHeaders = excluded.responseHeaders,storageUri = excluded.storageUri,storageSize = excluded.storageSize,uncompressedSize = excluded.uncompressedSize" + if(returnsId) { " RETURNING key" } else "" 
      }
      else -> {
        throw IllegalArgumentException("Unsupported db type")
      }
    }

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement, entity: CacheEntry) {
      stmt.setString(1, entity.key)
      stmt.setString(2, entity.url)
      stmt.setString(3, entity.message)
      stmt.setInt(4, entity.statusCode)
      stmt.setInt(5, entity.cacheFlags)
      stmt.setInt(6, entity.method)
      stmt.setLong(7, entity.lastAccessed)
      stmt.setLong(8, entity.lastValidated)
      stmt.setString(9, entity.integrity)
      stmt.setString(10, entity.responseHeaders)
      stmt.setString(11, entity.storageUri)
      stmt.setLong(12, entity.storageSize)
      stmt.setLong(13, entity.uncompressedSize)
    }
  }

  override suspend fun insertAsync(entry: CacheEntry): Long {
    val _retVal = _insertAdapterCacheEntry_abort.insertAndReturnIdAsync(entry)
    return _retVal
  }

  override fun insertList(entry: List<CacheEntry>) {
    _insertAdapterCacheEntry_abort.insertList(entry)
  }

  override fun upsertList(entry: List<CacheEntry>) {
    _insertAdapterCacheEntry_upsert.insertList(entry)
  }

  override fun updateList(entry: List<CacheEntry>) {
    val _sql =
        "UPDATE CacheEntry SET url = ?, message = ?, statusCode = ?, cacheFlags = ?, method = ?, lastAccessed = ?, lastValidated = ?, integrity = ?, responseHeaders = ?, storageUri = ?, storageSize = ?, uncompressedSize = ? WHERE key = ?"
    _db.prepareAndUseStatement(_sql) {
       _stmt ->
      _stmt.getConnection().setAutoCommit(false)
      for(_entity in entry) {
        _stmt.setString(1, _entity.url)
        _stmt.setString(2, _entity.message)
        _stmt.setInt(3, _entity.statusCode)
        _stmt.setInt(4, _entity.cacheFlags)
        _stmt.setInt(5, _entity.method)
        _stmt.setLong(6, _entity.lastAccessed)
        _stmt.setLong(7, _entity.lastValidated)
        _stmt.setString(8, _entity.integrity)
        _stmt.setString(9, _entity.responseHeaders)
        _stmt.setString(10, _entity.storageUri)
        _stmt.setLong(11, _entity.storageSize)
        _stmt.setLong(12, _entity.uncompressedSize)
        _stmt.setString(13, _entity.key)
        _stmt.executeUpdate()
      }
      _stmt.getConnection().commit()
    }
  }

  override fun delete(entries: List<CacheEntry>) {
    var _numChanges = 0
    _db.prepareAndUseStatement("DELETE FROM CacheEntry WHERE key = ?") {
       _stmt ->
      _stmt.getConnection().setAutoCommit(false)
      for(_entity in entries) {
        _stmt.setString(1, _entity.key)
        _numChanges += _stmt.executeUpdate()
      }
      _stmt.getConnection().commit()
    }
  }

  override suspend fun findByUrlAsync(url: String): CacheEntry? =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT CacheEntry.*
    |          FROM CacheEntry
    |         WHERE CacheEntry.url = ? 
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setString(1,url)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(null) {
        val _tmp_key = _result.getStringNonNull("key")
        val _tmp_url = _result.getStringNonNull("url")
        val _tmp_message = _result.getStringNonNull("message")
        val _tmp_statusCode = _result.getInt("statusCode")
        val _tmp_cacheFlags = _result.getInt("cacheFlags")
        val _tmp_method = _result.getInt("method")
        val _tmp_lastAccessed = _result.getLong("lastAccessed")
        val _tmp_lastValidated = _result.getLong("lastValidated")
        val _tmp_integrity = _result.getString("integrity")
        val _tmp_responseHeaders = _result.getStringNonNull("responseHeaders")
        val _tmp_storageUri = _result.getStringNonNull("storageUri")
        val _tmp_storageSize = _result.getLong("storageSize")
        val _tmp_uncompressedSize = _result.getLong("uncompressedSize")
        CacheEntry().apply {
          this.key = _tmp_key
          this.url = _tmp_url
          this.message = _tmp_message
          this.statusCode = _tmp_statusCode
          this.cacheFlags = _tmp_cacheFlags
          this.method = _tmp_method
          this.lastAccessed = _tmp_lastAccessed
          this.lastValidated = _tmp_lastValidated
          this.integrity = _tmp_integrity
          this.responseHeaders = _tmp_responseHeaders
          this.storageUri = _tmp_storageUri
          this.storageSize = _tmp_storageSize
          this.uncompressedSize = _tmp_uncompressedSize
        }
      }
    }
  }

  override fun findEntryAndBodyByKey(key: String): CacheEntry? =
      _db.prepareAndUseStatement(PreparedStatementConfig(
    sql = """
    |
    |        SELECT CacheEntry.*
    |          FROM CacheEntry
    |         WHERE CacheEntry.key = ?
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setString(1,key)
    _stmt.executeQuery().useResults{ _result -> 
      _result.mapNextRow(null) {
        val _tmp_key = _result.getStringNonNull("key")
        val _tmp_url = _result.getStringNonNull("url")
        val _tmp_message = _result.getStringNonNull("message")
        val _tmp_statusCode = _result.getInt("statusCode")
        val _tmp_cacheFlags = _result.getInt("cacheFlags")
        val _tmp_method = _result.getInt("method")
        val _tmp_lastAccessed = _result.getLong("lastAccessed")
        val _tmp_lastValidated = _result.getLong("lastValidated")
        val _tmp_integrity = _result.getString("integrity")
        val _tmp_responseHeaders = _result.getStringNonNull("responseHeaders")
        val _tmp_storageUri = _result.getStringNonNull("storageUri")
        val _tmp_storageSize = _result.getLong("storageSize")
        val _tmp_uncompressedSize = _result.getLong("uncompressedSize")
        CacheEntry().apply {
          this.key = _tmp_key
          this.url = _tmp_url
          this.message = _tmp_message
          this.statusCode = _tmp_statusCode
          this.cacheFlags = _tmp_cacheFlags
          this.method = _tmp_method
          this.lastAccessed = _tmp_lastAccessed
          this.lastValidated = _tmp_lastValidated
          this.integrity = _tmp_integrity
          this.responseHeaders = _tmp_responseHeaders
          this.storageUri = _tmp_storageUri
          this.storageSize = _tmp_storageSize
          this.uncompressedSize = _tmp_uncompressedSize
        }
      }
    }
  }

  override fun findByRequestBatchId(batchId: Int): List<CacheEntry> =
      _db.prepareAndUseStatement(PreparedStatementConfig(
    sql = """
    |
    |        SELECT CacheEntry.*
    |          FROM CacheEntry
    |         WHERE CacheEntry.key IN
    |               (SELECT RequestedEntry.requestedKey
    |                  FROM RequestedEntry
    |                 WHERE RequestedEntry.batchId = ?)
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setInt(1,batchId)
    _stmt.executeQuery().useResults{ _result -> 
      _result.mapRows {
        val _tmp_key = _result.getStringNonNull("key")
        val _tmp_url = _result.getStringNonNull("url")
        val _tmp_message = _result.getStringNonNull("message")
        val _tmp_statusCode = _result.getInt("statusCode")
        val _tmp_cacheFlags = _result.getInt("cacheFlags")
        val _tmp_method = _result.getInt("method")
        val _tmp_lastAccessed = _result.getLong("lastAccessed")
        val _tmp_lastValidated = _result.getLong("lastValidated")
        val _tmp_integrity = _result.getString("integrity")
        val _tmp_responseHeaders = _result.getStringNonNull("responseHeaders")
        val _tmp_storageUri = _result.getStringNonNull("storageUri")
        val _tmp_storageSize = _result.getLong("storageSize")
        val _tmp_uncompressedSize = _result.getLong("uncompressedSize")
        CacheEntry().apply {
          this.key = _tmp_key
          this.url = _tmp_url
          this.message = _tmp_message
          this.statusCode = _tmp_statusCode
          this.cacheFlags = _tmp_cacheFlags
          this.method = _tmp_method
          this.lastAccessed = _tmp_lastAccessed
          this.lastValidated = _tmp_lastValidated
          this.integrity = _tmp_integrity
          this.responseHeaders = _tmp_responseHeaders
          this.storageUri = _tmp_storageUri
          this.storageSize = _tmp_storageSize
          this.uncompressedSize = _tmp_uncompressedSize
        }
      }
    }
  }

  override fun findEntriesWithLock(batchId: Int): List<String> =
      _db.prepareAndUseStatement(PreparedStatementConfig(
    sql = """
    |
    |        SELECT RequestedEntry.requestedKey
    |          FROM RequestedEntry
    |         WHERE RequestedEntry.batchId = ?
    |           AND EXISTS(
    |               SELECT RetentionLock.lockId
    |                 FROM RetentionLock
    |                WHERE RetentionLock.lockKey = RequestedEntry.requestedKey)
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setInt(1,batchId)
    _stmt.executeQuery().useResults{ _result -> 
      _result.mapRows {
        _result.getStringNonNull(1)
      }
    }
  }

  override fun updateLastAccessedTime(key: String, lastAccessTime: Long) {
    _db.prepareAndUseStatement(PreparedStatementConfig(
      sql = """
      |
      |        UPDATE CacheEntry
      |           SET lastAccessed = ?
      |         WHERE key = ?  
      |    
      """.trimMargin(),
      readOnly = false,)
    ) { _stmt -> 
      _stmt.setLong(1,lastAccessTime)
      _stmt.setString(2,key)
      _stmt.executeUpdate()
    }
  }

  override fun findEvictableEntries(batchSize: Int): List<CacheEntry> =
      _db.prepareAndUseStatement(PreparedStatementConfig(
    sql = """
    |
    |        SELECT CacheEntry.*
    |          FROM CacheEntry
    |         WHERE NOT EXISTS(
    |               SELECT RetentionLock.lockId
    |                 FROM RetentionLock
    |                WHERE RetentionLock.lockKey = CacheEntry.key) 
    |      ORDER BY lastAccessed ASC           
    |         LIMIT ?       
    |      
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setInt(1,batchSize)
    _stmt.executeQuery().useResults{ _result -> 
      _result.mapRows {
        val _tmp_key = _result.getStringNonNull("key")
        val _tmp_url = _result.getStringNonNull("url")
        val _tmp_message = _result.getStringNonNull("message")
        val _tmp_statusCode = _result.getInt("statusCode")
        val _tmp_cacheFlags = _result.getInt("cacheFlags")
        val _tmp_method = _result.getInt("method")
        val _tmp_lastAccessed = _result.getLong("lastAccessed")
        val _tmp_lastValidated = _result.getLong("lastValidated")
        val _tmp_integrity = _result.getString("integrity")
        val _tmp_responseHeaders = _result.getStringNonNull("responseHeaders")
        val _tmp_storageUri = _result.getStringNonNull("storageUri")
        val _tmp_storageSize = _result.getLong("storageSize")
        val _tmp_uncompressedSize = _result.getLong("uncompressedSize")
        CacheEntry().apply {
          this.key = _tmp_key
          this.url = _tmp_url
          this.message = _tmp_message
          this.statusCode = _tmp_statusCode
          this.cacheFlags = _tmp_cacheFlags
          this.method = _tmp_method
          this.lastAccessed = _tmp_lastAccessed
          this.lastValidated = _tmp_lastValidated
          this.integrity = _tmp_integrity
          this.responseHeaders = _tmp_responseHeaders
          this.storageUri = _tmp_storageUri
          this.storageSize = _tmp_storageSize
          this.uncompressedSize = _tmp_uncompressedSize
        }
      }
    }
  }

  override fun totalEvictableSize(): Long = _db.prepareAndUseStatement(PreparedStatementConfig(
    sql = """
    |
    |        SELECT SUM(CacheEntry.storageSize)
    |          FROM CacheEntry
    |         WHERE NOT EXISTS(
    |               SELECT RetentionLock.lockId
    |                 FROM RetentionLock
    |                WHERE RetentionLock.lockKey = CacheEntry.key)  
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.executeQuery().useResults{ _result -> 
      _result.mapNextRow(0L) {
        _result.getLong(1)
      }
    }
  }

  override fun updateValidation(
    key: String,
    headers: String,
    lastValidated: Long,
    lastAccessed: Long,
  ) {
    _db.prepareAndUseStatement(PreparedStatementConfig(
      sql = """
      |
      |        UPDATE CacheEntry
      |           SET responseHeaders = ?,
      |               lastValidated = ?,
      |               lastAccessed = ?
      |         WHERE key = ?      
      |    
      """.trimMargin(),
      readOnly = false,)
    ) { _stmt -> 
      _stmt.setString(1,headers)
      _stmt.setLong(2,lastValidated)
      _stmt.setLong(3,lastAccessed)
      _stmt.setString(4,key)
      _stmt.executeUpdate()
    }
  }
}
