package com.ustadmobile.core.db.dao.xapi

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.jdbc.PreparedStatement
import com.ustadmobile.door.jdbc.ext.executeQueryAsyncKmp
import com.ustadmobile.door.jdbc.ext.getLongNullable
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.setLongNullable
import com.ustadmobile.door.jdbc.ext.useResults
import com.ustadmobile.door.room.RoomDatabase
import com.ustadmobile.lib.db.composites.xapi.StateIdAndLastModified
import com.ustadmobile.lib.db.entities.xapi.StateEntity
import kotlin.Boolean
import kotlin.IllegalArgumentException
import kotlin.Long
import kotlin.String
import kotlin.collections.List

public class StateEntityDao_JdbcImpl(
  public val _db: RoomDatabase,
) : StateEntityDao() {
  public val _insertAdapterStateEntity_upsert: EntityInsertionAdapter<StateEntity> = object :
      EntityInsertionAdapter<StateEntity>(_db) {
    override fun makeSql(returnsId: Boolean): String = when(dbType) {
      DoorDbType.SQLITE -> {
        "INSERT OR REPLACE INTO StateEntity (seActorUid, seHash, seActivityUid, seStateId, seLastMod, seTimeStored, seContentType, seCompressed, seContent, seDeleted, seRegistrationHi, seRegistrationLo, seH5PPreloaded, seH5PSubContentId) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
      }
      DoorDbType.POSTGRES ->  {
        "INSERT INTO StateEntity (seActorUid, seHash, seActivityUid, seStateId, seLastMod, seTimeStored, seContentType, seCompressed, seContent, seDeleted, seRegistrationHi, seRegistrationLo, seH5PPreloaded, seH5PSubContentId) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT (seActorUid, seHash) DO UPDATE SET seActivityUid = excluded.seActivityUid,seStateId = excluded.seStateId,seLastMod = excluded.seLastMod,seTimeStored = excluded.seTimeStored,seContentType = excluded.seContentType,seCompressed = excluded.seCompressed,seContent = excluded.seContent,seDeleted = excluded.seDeleted,seRegistrationHi = excluded.seRegistrationHi,seRegistrationLo = excluded.seRegistrationLo,seH5PPreloaded = excluded.seH5PPreloaded,seH5PSubContentId = excluded.seH5PSubContentId" + if(returnsId) { " RETURNING seActorUid" } else "" 
      }
      else -> {
        throw IllegalArgumentException("Unsupported db type")
      }
    }

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement, entity: StateEntity) {
      stmt.setLong(1, entity.seActorUid)
      stmt.setLong(2, entity.seHash)
      stmt.setLong(3, entity.seActivityUid)
      stmt.setString(4, entity.seStateId)
      stmt.setLong(5, entity.seLastMod)
      stmt.setLong(6, entity.seTimeStored)
      stmt.setString(7, entity.seContentType)
      stmt.setInt(8, entity.seCompressed)
      stmt.setString(9, entity.seContent)
      stmt.setBoolean(10, entity.seDeleted)
      stmt.setLongNullable(11, entity.seRegistrationHi)
      stmt.setLongNullable(12, entity.seRegistrationLo)
      stmt.setBoolean(13, entity.seH5PPreloaded)
      stmt.setString(14, entity.seH5PSubContentId)
    }
  }

  override suspend fun upsertAsync(stateEntities: List<StateEntity>) {
    _insertAdapterStateEntity_upsert.insertListAsync(stateEntities)
  }

  override suspend fun findByActorAndHash(
    accountPersonUid: Long,
    actorUid: Long,
    seHash: Long,
    includeDeleted: Boolean,
  ): StateEntity? = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT StateEntity.*
    |          FROM StateEntity
    |         WHERE (SELECT ActorEntity.actorPersonUid
    |                  FROM ActorEntity
    |                 WHERE ActorEntity.actorUid = ?) = ?
    |           AND seActorUid = ?
    |           AND seHash = ?
    |           AND (   CAST(? AS INTEGER) = 1 
    |                OR CAST(StateEntity.seDeleted AS INTEGER) = 0)
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,actorUid)
    _stmt.setLong(2,accountPersonUid)
    _stmt.setLong(3,actorUid)
    _stmt.setLong(4,seHash)
    _stmt.setBoolean(5,includeDeleted)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(null) {
        val _tmp_seActorUid = _result.getLong("seActorUid")
        val _tmp_seHash = _result.getLong("seHash")
        val _tmp_seActivityUid = _result.getLong("seActivityUid")
        val _tmp_seStateId = _result.getStringNonNull("seStateId")
        val _tmp_seLastMod = _result.getLong("seLastMod")
        val _tmp_seTimeStored = _result.getLong("seTimeStored")
        val _tmp_seContentType = _result.getStringNonNull("seContentType")
        val _tmp_seCompressed = _result.getInt("seCompressed")
        val _tmp_seContent = _result.getStringNonNull("seContent")
        val _tmp_seDeleted = _result.getBoolean("seDeleted")
        val _tmp_seRegistrationHi = _result.getLongNullable("seRegistrationHi")
        val _tmp_seRegistrationLo = _result.getLongNullable("seRegistrationLo")
        val _tmp_seH5PPreloaded = _result.getBoolean("seH5PPreloaded")
        val _tmp_seH5PSubContentId = _result.getString("seH5PSubContentId")
        StateEntity().apply {
          this.seActorUid = _tmp_seActorUid
          this.seHash = _tmp_seHash
          this.seActivityUid = _tmp_seActivityUid
          this.seStateId = _tmp_seStateId
          this.seLastMod = _tmp_seLastMod
          this.seTimeStored = _tmp_seTimeStored
          this.seContentType = _tmp_seContentType
          this.seCompressed = _tmp_seCompressed
          this.seContent = _tmp_seContent
          this.seDeleted = _tmp_seDeleted
          this.seRegistrationHi = _tmp_seRegistrationHi
          this.seRegistrationLo = _tmp_seRegistrationLo
          this.seH5PPreloaded = _tmp_seH5PPreloaded
          this.seH5PSubContentId = _tmp_seH5PSubContentId
        }
      }
    }
  }

  override suspend fun findByAgentAndActivity(
    accountPersonUid: Long,
    actorUid: Long,
    seActivityUid: Long,
    registrationUuidHi: Long?,
    registrationUuidLo: Long?,
    modifiedSince: Long,
  ): List<StateEntity> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT StateEntity.*
    |          FROM StateEntity
    |         WHERE (SELECT ActorEntity.actorPersonUid
    |                  FROM ActorEntity
    |                 WHERE ActorEntity.actorUid = ?) = ?
    |           AND seActorUid = ?
    |           AND seActivityUid = ?
    |           AND (? = 0 OR StateEntity.seLastMod > ?)
    |           AND ((    ? IS NULL
    |                 AND StateEntity.seRegistrationHi IS NULL
    |                 AND ? IS NULL 
    |                 AND StateEntity.seRegistrationLo IS NULL)
    |             OR (    StateEntity.seRegistrationHi = ? 
    |                 AND StateEntity.seRegistrationLo = ?))
    |           AND StateEntity.seStateId IS NOT NULL  
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,actorUid)
    _stmt.setLong(2,accountPersonUid)
    _stmt.setLong(3,actorUid)
    _stmt.setLong(4,seActivityUid)
    _stmt.setLong(5,modifiedSince)
    _stmt.setLong(6,modifiedSince)
    _stmt.setLongNullable(7,registrationUuidHi)
    _stmt.setLongNullable(8,registrationUuidLo)
    _stmt.setLongNullable(9,registrationUuidHi)
    _stmt.setLongNullable(10,registrationUuidLo)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_seActorUid = _result.getLong("seActorUid")
        val _tmp_seHash = _result.getLong("seHash")
        val _tmp_seActivityUid = _result.getLong("seActivityUid")
        val _tmp_seStateId = _result.getStringNonNull("seStateId")
        val _tmp_seLastMod = _result.getLong("seLastMod")
        val _tmp_seTimeStored = _result.getLong("seTimeStored")
        val _tmp_seContentType = _result.getStringNonNull("seContentType")
        val _tmp_seCompressed = _result.getInt("seCompressed")
        val _tmp_seContent = _result.getStringNonNull("seContent")
        val _tmp_seDeleted = _result.getBoolean("seDeleted")
        val _tmp_seRegistrationHi = _result.getLongNullable("seRegistrationHi")
        val _tmp_seRegistrationLo = _result.getLongNullable("seRegistrationLo")
        val _tmp_seH5PPreloaded = _result.getBoolean("seH5PPreloaded")
        val _tmp_seH5PSubContentId = _result.getString("seH5PSubContentId")
        StateEntity().apply {
          this.seActorUid = _tmp_seActorUid
          this.seHash = _tmp_seHash
          this.seActivityUid = _tmp_seActivityUid
          this.seStateId = _tmp_seStateId
          this.seLastMod = _tmp_seLastMod
          this.seTimeStored = _tmp_seTimeStored
          this.seContentType = _tmp_seContentType
          this.seCompressed = _tmp_seCompressed
          this.seContent = _tmp_seContent
          this.seDeleted = _tmp_seDeleted
          this.seRegistrationHi = _tmp_seRegistrationHi
          this.seRegistrationLo = _tmp_seRegistrationLo
          this.seH5PPreloaded = _tmp_seH5PPreloaded
          this.seH5PSubContentId = _tmp_seH5PSubContentId
        }
      }
    }
  }

  override suspend fun getStateIds(
    accountPersonUid: Long,
    actorUid: Long,
    seActivityUid: Long,
    registrationUuidHi: Long?,
    registrationUuidLo: Long?,
    modifiedSince: Long,
  ): List<StateIdAndLastModified> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT StateEntity.seStateId, StateEntity.seLastMod
    |          FROM StateEntity
    |         WHERE (SELECT ActorEntity.actorPersonUid
    |                  FROM ActorEntity
    |                 WHERE ActorEntity.actorUid = ?) = ?
    |           AND seActorUid = ?
    |           AND seActivityUid = ?
    |           AND (? = 0 OR StateEntity.seLastMod > ?)
    |           AND ((    ? IS NULL
    |                 AND StateEntity.seRegistrationHi IS NULL
    |                 AND ? IS NULL 
    |                 AND StateEntity.seRegistrationLo IS NULL)
    |             OR (    StateEntity.seRegistrationHi = ? 
    |                 AND StateEntity.seRegistrationLo = ?))
    |           AND StateEntity.seStateId IS NOT NULL 
    |           AND CAST(StateEntity.seDeleted AS INTEGER) = 0      
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,actorUid)
    _stmt.setLong(2,accountPersonUid)
    _stmt.setLong(3,actorUid)
    _stmt.setLong(4,seActivityUid)
    _stmt.setLong(5,modifiedSince)
    _stmt.setLong(6,modifiedSince)
    _stmt.setLongNullable(7,registrationUuidHi)
    _stmt.setLongNullable(8,registrationUuidLo)
    _stmt.setLongNullable(9,registrationUuidHi)
    _stmt.setLongNullable(10,registrationUuidLo)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_seStateId = _result.getStringNonNull("seStateId")
        val _tmp_seLastMod = _result.getLong("seLastMod")
        StateIdAndLastModified().apply {
          this.seStateId = _tmp_seStateId
          this.seLastMod = _tmp_seLastMod
        }
      }
    }
  }

  override suspend fun getH5PPreload(
    accountPersonUid: Long,
    actorUid: Long,
    seActivityUid: Long,
    registrationUuidHi: Long?,
    registrationUuidLo: Long?,
  ): List<StateEntity> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT StateEntity.*
    |          FROM StateEntity
    |         WHERE (SELECT ActorEntity.actorPersonUid
    |                  FROM ActorEntity
    |                 WHERE ActorEntity.actorUid = ?) = ?
    |           AND seActorUid = ?
    |           AND seActivityUid = ? 
    |           AND ((    ? IS NULL
    |                 AND StateEntity.seRegistrationHi IS NULL
    |                 AND ? IS NULL 
    |                 AND StateEntity.seRegistrationLo IS NULL)
    |             OR (    StateEntity.seRegistrationHi = ? 
    |                 AND StateEntity.seRegistrationLo = ?))
    |           AND StateEntity.seH5PSubContentId IS NOT NULL      
    |           AND CAST(StateEntity.seH5PPreloaded AS INTEGER) = 1      
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,actorUid)
    _stmt.setLong(2,accountPersonUid)
    _stmt.setLong(3,actorUid)
    _stmt.setLong(4,seActivityUid)
    _stmt.setLongNullable(5,registrationUuidHi)
    _stmt.setLongNullable(6,registrationUuidLo)
    _stmt.setLongNullable(7,registrationUuidHi)
    _stmt.setLongNullable(8,registrationUuidLo)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_seActorUid = _result.getLong("seActorUid")
        val _tmp_seHash = _result.getLong("seHash")
        val _tmp_seActivityUid = _result.getLong("seActivityUid")
        val _tmp_seStateId = _result.getStringNonNull("seStateId")
        val _tmp_seLastMod = _result.getLong("seLastMod")
        val _tmp_seTimeStored = _result.getLong("seTimeStored")
        val _tmp_seContentType = _result.getStringNonNull("seContentType")
        val _tmp_seCompressed = _result.getInt("seCompressed")
        val _tmp_seContent = _result.getStringNonNull("seContent")
        val _tmp_seDeleted = _result.getBoolean("seDeleted")
        val _tmp_seRegistrationHi = _result.getLongNullable("seRegistrationHi")
        val _tmp_seRegistrationLo = _result.getLongNullable("seRegistrationLo")
        val _tmp_seH5PPreloaded = _result.getBoolean("seH5PPreloaded")
        val _tmp_seH5PSubContentId = _result.getString("seH5PSubContentId")
        StateEntity().apply {
          this.seActorUid = _tmp_seActorUid
          this.seHash = _tmp_seHash
          this.seActivityUid = _tmp_seActivityUid
          this.seStateId = _tmp_seStateId
          this.seLastMod = _tmp_seLastMod
          this.seTimeStored = _tmp_seTimeStored
          this.seContentType = _tmp_seContentType
          this.seCompressed = _tmp_seCompressed
          this.seContent = _tmp_seContent
          this.seDeleted = _tmp_seDeleted
          this.seRegistrationHi = _tmp_seRegistrationHi
          this.seRegistrationLo = _tmp_seRegistrationLo
          this.seH5PPreloaded = _tmp_seH5PPreloaded
          this.seH5PSubContentId = _tmp_seH5PSubContentId
        }
      }
    }
  }
}
