package com.ustadmobile.core.db.dao

import androidx.paging.PagingSource
import com.ustadmobile.door.DoorDbType
import com.ustadmobile.door.PreparedStatementConfig
import com.ustadmobile.door.ext.createArrayOrProxyArrayOf
import com.ustadmobile.door.ext.prepareAndUseStatementAsync
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.paging.DoorLimitOffsetPagingSource
import com.ustadmobile.door.room.RoomDatabase
import com.ustadmobile.lib.db.entities.DeletedItem
import kotlin.Boolean
import kotlin.Int
import kotlin.Long
import kotlin.collections.List

public class DeletedItemDao_JdbcImpl(
  public val _db: RoomDatabase,
) : DeletedItemDao() {
  override suspend fun insertDeletedItemForContentEntryParentChildJoin(
    cepcjUid: Long,
    time: Long,
    deletedByPersonUid: Long,
  ) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        INSERT INTO DeletedItem(delItemName, delItemIconUri, delItemLastModTime, delItemTimeDeleted, delItemEntityTable, delItemEntityUid, delItemDeletedByPersonUid, delItemStatus, delItemIsFolder)
      |        SELECT (SELECT ContentEntry.title
      |                  FROM ContentEntry
      |                 WHERE ContentEntry.contentEntryUid = 
      |                       (SELECT ContentEntryParentChildJoin.cepcjChildContentEntryUid
      |                          FROM ContentEntryParentChildJoin
      |                         WHERE ContentEntryParentChildJoin.cepcjUid = CAST(? AS BIGINT))) AS delItemName,
      |               NULL as delItemIconUri,
      |               CAST(? AS BIGINT) AS delItemLastModTime,
      |               CAST(? AS BIGINT) AS delItemTimeDeleted,
      |               7 AS delItemEntityTable,
      |               CAST(? AS BIGINT) AS delItemEntityUid,
      |               CAST(? AS BIGINT) AS delItemDeletedByPersonUid,
      |               1 AS delItemStatus,
      |               (SELECT NOT ContentEntry.leaf
      |                  FROM ContentEntry
      |                 WHERE ContentEntry.contentEntryUid = 
      |                       (SELECT ContentEntryParentChildJoin.cepcjChildContentEntryUid
      |                          FROM ContentEntryParentChildJoin
      |                         WHERE ContentEntryParentChildJoin.cepcjUid = CAST(? AS BIGINT))) AS delItemIsFolder
      |    
      """.trimMargin(),
      postgreSql = """
      |
      |        INSERT INTO DeletedItem(delItemName, delItemIconUri, delItemLastModTime, delItemTimeDeleted, delItemEntityTable, delItemEntityUid, delItemDeletedByPersonUid, delItemStatus, delItemIsFolder)
      |        SELECT (SELECT ContentEntry.title
      |                  FROM ContentEntry
      |                 WHERE ContentEntry.contentEntryUid = 
      |                       (SELECT ContentEntryParentChildJoin.cepcjChildContentEntryUid
      |                          FROM ContentEntryParentChildJoin
      |                         WHERE ContentEntryParentChildJoin.cepcjUid = ?)) AS delItemName,
      |               NULL as delItemIconUri,
      |               ? AS delItemLastModTime,
      |               ? AS delItemTimeDeleted,
      |               7 AS delItemEntityTable,
      |               ? AS delItemEntityUid,
      |               ? AS delItemDeletedByPersonUid,
      |               1 AS delItemStatus,
      |               (SELECT NOT ContentEntry.leaf
      |                  FROM ContentEntry
      |                 WHERE ContentEntry.contentEntryUid = 
      |                       (SELECT ContentEntryParentChildJoin.cepcjChildContentEntryUid
      |                          FROM ContentEntryParentChildJoin
      |                         WHERE ContentEntryParentChildJoin.cepcjUid = ?)) AS delItemIsFolder
      |    
      |""".trimMargin(),
      readOnly = false,)
    ) { _stmt -> 
      _stmt.setLong(1,cepcjUid)
      _stmt.setLong(2,time)
      _stmt.setLong(3,time)
      _stmt.setLong(4,cepcjUid)
      _stmt.setLong(5,deletedByPersonUid)
      _stmt.setLong(6,cepcjUid)
      _stmt.executeUpdateAsyncKmp()
    }
  }

  override suspend fun findByTableIdAndEntityUid(tableId: Int, entityUid: Long): List<DeletedItem> =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT DeletedItem.*
    |          FROM DeletedItem
    |         WHERE DeletedItem.delItemEntityTable = ?
    |           AND DeletedItem.delItemEntityUid = CAST(? AS BIGINT)
    |    
    """.trimMargin(),
    postgreSql = """
    |
    |        SELECT DeletedItem.*
    |          FROM DeletedItem
    |         WHERE DeletedItem.delItemEntityTable = ?
    |           AND DeletedItem.delItemEntityUid = ?
    |    
    |""".trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setInt(1,tableId)
    _stmt.setLong(2,entityUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_delItemUid = _result.getLong("delItemUid")
        val _tmp_delItemName = _result.getString("delItemName")
        val _tmp_delItemIconUri = _result.getString("delItemIconUri")
        val _tmp_delItemLastModTime = _result.getLong("delItemLastModTime")
        val _tmp_delItemTimeDeleted = _result.getLong("delItemTimeDeleted")
        val _tmp_delItemEntityTable = _result.getInt("delItemEntityTable")
        val _tmp_delItemEntityUid = _result.getLong("delItemEntityUid")
        val _tmp_delItemDeletedByPersonUid = _result.getLong("delItemDeletedByPersonUid")
        val _tmp_delItemStatus = _result.getInt("delItemStatus")
        val _tmp_delItemIsFolder = _result.getBoolean("delItemIsFolder")
        DeletedItem().apply {
          this.delItemUid = _tmp_delItemUid
          this.delItemName = _tmp_delItemName
          this.delItemIconUri = _tmp_delItemIconUri
          this.delItemLastModTime = _tmp_delItemLastModTime
          this.delItemTimeDeleted = _tmp_delItemTimeDeleted
          this.delItemEntityTable = _tmp_delItemEntityTable
          this.delItemEntityUid = _tmp_delItemEntityUid
          this.delItemDeletedByPersonUid = _tmp_delItemDeletedByPersonUid
          this.delItemStatus = _tmp_delItemStatus
          this.delItemIsFolder = _tmp_delItemIsFolder
        }
      }
    }
  }

  override fun findDeletedItemsForUser(personUid: Long, includeActionedItems: Boolean):
      PagingSource<Int, DeletedItem> = object : DoorLimitOffsetPagingSource<DeletedItem>(db = _db
  , tableNames = arrayOf("DeletedItem")
  ) {
    override suspend fun loadRows(_limit: Int, _offset: Int): List<DeletedItem> =
        _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT * FROM (
      |        SELECT DeletedItem.*
      |          FROM DeletedItem
      |         WHERE (  (CAST(? AS INTEGER) = 1)
      |                OR DeletedItem.delItemStatus = 1)
      |           AND DeletedItem.delItemDeletedByPersonUid = CAST(? AS BIGINT)       
      |      ORDER BY DeletedItem.delItemTimeDeleted DESC            
      |    ) AS _PagingData LIMIT ? OFFSET ?
      """.trimMargin(),
      postgreSql = """
      |SELECT * FROM (
      |        SELECT DeletedItem.*
      |          FROM DeletedItem
      |         WHERE (  (CAST(? AS INTEGER) = 1)
      |                OR DeletedItem.delItemStatus = 1)
      |           AND DeletedItem.delItemDeletedByPersonUid = ?       
      |      ORDER BY DeletedItem.delItemTimeDeleted DESC            
      |    ) AS _PagingData LIMIT ? OFFSET ?
      |""".trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setBoolean(1,includeActionedItems)
      _stmt.setLong(2,personUid)
      _stmt.setInt(3,_limit)
      _stmt.setInt(4,_offset)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          val _tmp_delItemUid = _result.getLong("delItemUid")
          val _tmp_delItemName = _result.getString("delItemName")
          val _tmp_delItemIconUri = _result.getString("delItemIconUri")
          val _tmp_delItemLastModTime = _result.getLong("delItemLastModTime")
          val _tmp_delItemTimeDeleted = _result.getLong("delItemTimeDeleted")
          val _tmp_delItemEntityTable = _result.getInt("delItemEntityTable")
          val _tmp_delItemEntityUid = _result.getLong("delItemEntityUid")
          val _tmp_delItemDeletedByPersonUid = _result.getLong("delItemDeletedByPersonUid")
          val _tmp_delItemStatus = _result.getInt("delItemStatus")
          val _tmp_delItemIsFolder = _result.getBoolean("delItemIsFolder")
          DeletedItem().apply {
            this.delItemUid = _tmp_delItemUid
            this.delItemName = _tmp_delItemName
            this.delItemIconUri = _tmp_delItemIconUri
            this.delItemLastModTime = _tmp_delItemLastModTime
            this.delItemTimeDeleted = _tmp_delItemTimeDeleted
            this.delItemEntityTable = _tmp_delItemEntityTable
            this.delItemEntityUid = _tmp_delItemEntityUid
            this.delItemDeletedByPersonUid = _tmp_delItemDeletedByPersonUid
            this.delItemStatus = _tmp_delItemStatus
            this.delItemIsFolder = _tmp_delItemIsFolder
          }
        }
      }
    }

    override suspend fun countRows(): Int = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT COUNT(*) FROM (
      |        SELECT DeletedItem.*
      |          FROM DeletedItem
      |         WHERE (  (CAST(? AS INTEGER) = 1)
      |                OR DeletedItem.delItemStatus = 1)
      |           AND DeletedItem.delItemDeletedByPersonUid = CAST(? AS BIGINT)       
      |      ORDER BY DeletedItem.delItemTimeDeleted DESC            
      |    ) AS _PagingCount
      """.trimMargin(),
      postgreSql = """
      |SELECT COUNT(*) FROM (
      |        SELECT DeletedItem.*
      |          FROM DeletedItem
      |         WHERE (  (CAST(? AS INTEGER) = 1)
      |                OR DeletedItem.delItemStatus = 1)
      |           AND DeletedItem.delItemDeletedByPersonUid = ?       
      |      ORDER BY DeletedItem.delItemTimeDeleted DESC            
      |    ) AS _PagingCount
      |""".trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setBoolean(1,includeActionedItems)
      _stmt.setLong(2,personUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(0) {
          _result.getInt(1)
        }
      }
    }
  }

  override suspend fun updateStatusByUids(
    uidList: List<Long>,
    newStatus: Int,
    updateTime: Long,
  ) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        UPDATE DeletedItem
      |           SET delItemStatus = ?,
      |               delItemLastModTime = CAST(? AS BIGINT)
      |         WHERE delItemUid IN (?)
      |    
      """.trimMargin(),
      hasListParams = true,
      postgreSql = """
      |
      |        UPDATE DeletedItem
      |           SET delItemStatus = ?,
      |               delItemLastModTime = ?
      |         WHERE delItemUid IN (?)
      |    
      |""".trimMargin(),
      readOnly = false,)
    ) { _stmt -> 
      _stmt.setInt(1,newStatus)
      _stmt.setLong(2,updateTime)
      _stmt.setArray(3, _stmt.getConnection().createArrayOrProxyArrayOf("BIGINT",
          uidList.toTypedArray()))
      _stmt.executeUpdateAsyncKmp()
    }
  }
}
