package com.ustadmobile.core.db.dao

import androidx.paging.PagingSource
import com.ustadmobile.door.DoorDbType
import com.ustadmobile.door.DoorQuery
import com.ustadmobile.door.EntityInsertionAdapter
import com.ustadmobile.door.PreparedStatementConfig
import com.ustadmobile.door.ext.createArrayOrProxyArrayOf
import com.ustadmobile.door.ext.hasListOrArrayParams
import com.ustadmobile.door.ext.prepareAndUseStatement
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.paging.DoorLimitOffsetPagingSource
import com.ustadmobile.door.room.RoomDatabase
import com.ustadmobile.lib.db.entities.Report
import kotlin.Boolean
import kotlin.IllegalArgumentException
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.collections.List
import kotlinx.coroutines.flow.Flow

public class ReportDao_JdbcImpl(
  public val _db: RoomDatabase,
) : ReportDao() {
  public val _insertAdapterReport_upsert: EntityInsertionAdapter<Report> = object :
      EntityInsertionAdapter<Report>(_db) {
    override fun makeSql(returnsId: Boolean): String = when(dbType) {
      DoorDbType.SQLITE -> {
        "INSERT OR REPLACE INTO Report (reportUid, reportTitle, reportOptions, reportIsTemplate, reportLastModTime, reportOwnerPersonUid) VALUES(?, ?, ?, ?, ?, ?)"
      }
      DoorDbType.POSTGRES ->  {
        "INSERT INTO Report (reportUid, reportTitle, reportOptions, reportIsTemplate, reportLastModTime, reportOwnerPersonUid) VALUES(COALESCE(?,nextval('Report_reportUid_seq')), ?, ?, ?, ?, ?) ON CONFLICT (reportUid) DO UPDATE SET reportTitle = excluded.reportTitle,reportOptions = excluded.reportOptions,reportIsTemplate = excluded.reportIsTemplate,reportLastModTime = excluded.reportLastModTime,reportOwnerPersonUid = excluded.reportOwnerPersonUid" + if(returnsId) { " RETURNING reportUid" } else "" 
      }
      else -> {
        throw IllegalArgumentException("Unsupported db type")
      }
    }

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement, entity: Report) {
      if(entity.reportUid == 0L) {
        stmt.setObject(1, null)
      } else {
        stmt.setLong(1, entity.reportUid)
      }
      stmt.setString(2, entity.reportTitle)
      stmt.setString(3, entity.reportOptions)
      stmt.setBoolean(4, entity.reportIsTemplate)
      stmt.setLong(5, entity.reportLastModTime)
      stmt.setLong(6, entity.reportOwnerPersonUid)
    }
  }

  public val _insertAdapterReport_abort: EntityInsertionAdapter<Report> = object :
      EntityInsertionAdapter<Report>(_db) {
    override fun makeSql(returnsId: Boolean): String = when(dbType) {
      DoorDbType.SQLITE -> {
        "INSERT INTO Report (reportUid, reportTitle, reportOptions, reportIsTemplate, reportLastModTime, reportOwnerPersonUid) VALUES(?, ?, ?, ?, ?, ?)"
      }
      DoorDbType.POSTGRES ->  {
        "INSERT INTO Report (reportUid, reportTitle, reportOptions, reportIsTemplate, reportLastModTime, reportOwnerPersonUid) VALUES(COALESCE(?,nextval('Report_reportUid_seq')), ?, ?, ?, ?, ?)" + if(returnsId) { " RETURNING reportUid" } else "" 
      }
      else -> {
        throw IllegalArgumentException("Unsupported db type")
      }
    }

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement, entity: Report) {
      if(entity.reportUid == 0L) {
        stmt.setObject(1, null)
      } else {
        stmt.setLong(1, entity.reportUid)
      }
      stmt.setString(2, entity.reportTitle)
      stmt.setString(3, entity.reportOptions)
      stmt.setBoolean(4, entity.reportIsTemplate)
      stmt.setLong(5, entity.reportLastModTime)
      stmt.setLong(6, entity.reportOwnerPersonUid)
    }
  }

  override fun replaceList(entityList: List<Report>) {
    _insertAdapterReport_upsert.insertList(entityList)
  }

  public override fun insert(entity: Report): Long {
    val _retVal = _insertAdapterReport_abort.insertAndReturnId(entity)
    return _retVal
  }

  public override suspend fun insertAsync(entity: Report): Long {
    val _retVal = _insertAdapterReport_abort.insertAndReturnIdAsync(entity)
    return _retVal
  }

  public override fun insertList(entityList: List<Report>) {
    _insertAdapterReport_abort.insertList(entityList)
  }

  override suspend fun updateAsync(entity: Report) {
    val _sql =
        "UPDATE Report SET reportTitle = ?, reportOptions = ?, reportIsTemplate = ?, reportLastModTime = ?, reportOwnerPersonUid = ? WHERE reportUid = ?"
    _db.prepareAndUseStatementAsync(_sql) {
       _stmt ->
      _stmt.setString(1, entity.reportTitle)
      _stmt.setString(2, entity.reportOptions)
      _stmt.setBoolean(3, entity.reportIsTemplate)
      _stmt.setLong(4, entity.reportLastModTime)
      _stmt.setLong(5, entity.reportOwnerPersonUid)
      _stmt.setLong(6, entity.reportUid)
      _stmt.executeUpdateAsyncKmp()
    }
  }

  public override fun update(entity: Report) {
    val _sql =
        "UPDATE Report SET reportTitle = ?, reportOptions = ?, reportIsTemplate = ?, reportLastModTime = ?, reportOwnerPersonUid = ? WHERE reportUid = ?"
    _db.prepareAndUseStatement(_sql) {
       _stmt ->
      _stmt.setString(1, entity.reportTitle)
      _stmt.setString(2, entity.reportOptions)
      _stmt.setBoolean(3, entity.reportIsTemplate)
      _stmt.setLong(4, entity.reportLastModTime)
      _stmt.setLong(5, entity.reportOwnerPersonUid)
      _stmt.setLong(6, entity.reportUid)
      _stmt.executeUpdate()
    }
  }

  override fun getResults(query: DoorQuery): List<Report> =
      _db.prepareAndUseStatement(PreparedStatementConfig(query.sql, hasListParams =
      query.hasListOrArrayParams(), readOnly = true)
  ) { _stmt -> 
    query.bindToPreparedStmt(_stmt, _db)
    _stmt.executeQuery().useResults{ _result -> 
      _result.mapRows {
        val _tmp_reportUid = _result.getLong("reportUid")
        val _tmp_reportTitle = _result.getString("reportTitle")
        val _tmp_reportOptions = _result.getString("reportOptions")
        val _tmp_reportIsTemplate = _result.getBoolean("reportIsTemplate")
        val _tmp_reportLastModTime = _result.getLong("reportLastModTime")
        val _tmp_reportOwnerPersonUid = _result.getLong("reportOwnerPersonUid")
        Report().apply {
          this.reportUid = _tmp_reportUid
          this.reportTitle = _tmp_reportTitle
          this.reportOptions = _tmp_reportOptions
          this.reportIsTemplate = _tmp_reportIsTemplate
          this.reportLastModTime = _tmp_reportLastModTime
          this.reportOwnerPersonUid = _tmp_reportOwnerPersonUid
        }
      }
    }
  }

  override suspend fun deleteReportByUid(reportUid: Long) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = "DELETE FROM Report WHERE reportUid = ?",
      readOnly = false,)
    ) { _stmt -> 
      _stmt.setLong(1,reportUid)
      _stmt.executeUpdateAsyncKmp()
    }
  }

  override fun findAllReports(): PagingSource<Int, Report> = object :
      DoorLimitOffsetPagingSource<Report>(db = _db
  , tableNames = arrayOf("Report")
  ) {
    override suspend fun loadRows(_limit: Int, _offset: Int): List<Report> =
        _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql =
          "SELECT * FROM (SELECT * FROM Report ORDER BY reportTitle ASC) AS _PagingData LIMIT ? OFFSET ?",
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setInt(1,_limit)
      _stmt.setInt(2,_offset)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          val _tmp_reportUid = _result.getLong("reportUid")
          val _tmp_reportTitle = _result.getString("reportTitle")
          val _tmp_reportOptions = _result.getString("reportOptions")
          val _tmp_reportIsTemplate = _result.getBoolean("reportIsTemplate")
          val _tmp_reportLastModTime = _result.getLong("reportLastModTime")
          val _tmp_reportOwnerPersonUid = _result.getLong("reportOwnerPersonUid")
          Report().apply {
            this.reportUid = _tmp_reportUid
            this.reportTitle = _tmp_reportTitle
            this.reportOptions = _tmp_reportOptions
            this.reportIsTemplate = _tmp_reportIsTemplate
            this.reportLastModTime = _tmp_reportLastModTime
            this.reportOwnerPersonUid = _tmp_reportOwnerPersonUid
          }
        }
      }
    }

    override suspend fun countRows(): Int = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = "SELECT COUNT(*) FROM (SELECT * FROM Report ORDER BY reportTitle ASC) AS _PagingCount",
      readOnly = true,)
    ) { _stmt -> 
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(0) {
          _result.getInt(1)
        }
      }
    }
  }

  override fun findAllActiveReport(searchBit: String, isTemplate: Boolean):
      PagingSource<Int, Report> = object : DoorLimitOffsetPagingSource<Report>(db = _db
  , tableNames = arrayOf("Report")
  ) {
    override suspend fun loadRows(_limit: Int, _offset: Int): List<Report> =
        _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT * FROM (
      |        SELECT * FROM Report 
      |        WHERE reportIsTemplate = ?
      |        AND reportTitle LIKE ?
      |        ORDER BY reportTitle
      |    ) AS _PagingData LIMIT ? OFFSET ?
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setBoolean(1,isTemplate)
      _stmt.setString(2,searchBit)
      _stmt.setInt(3,_limit)
      _stmt.setInt(4,_offset)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          val _tmp_reportUid = _result.getLong("reportUid")
          val _tmp_reportTitle = _result.getString("reportTitle")
          val _tmp_reportOptions = _result.getString("reportOptions")
          val _tmp_reportIsTemplate = _result.getBoolean("reportIsTemplate")
          val _tmp_reportLastModTime = _result.getLong("reportLastModTime")
          val _tmp_reportOwnerPersonUid = _result.getLong("reportOwnerPersonUid")
          Report().apply {
            this.reportUid = _tmp_reportUid
            this.reportTitle = _tmp_reportTitle
            this.reportOptions = _tmp_reportOptions
            this.reportIsTemplate = _tmp_reportIsTemplate
            this.reportLastModTime = _tmp_reportLastModTime
            this.reportOwnerPersonUid = _tmp_reportOwnerPersonUid
          }
        }
      }
    }

    override suspend fun countRows(): Int = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT COUNT(*) FROM (
      |        SELECT * FROM Report 
      |        WHERE reportIsTemplate = ?
      |        AND reportTitle LIKE ?
      |        ORDER BY reportTitle
      |    ) AS _PagingCount
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setBoolean(1,isTemplate)
      _stmt.setString(2,searchBit)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(0) {
          _result.getInt(1)
        }
      }
    }
  }

  override suspend fun findByUid(entityUid: Long): Report? =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = "SELECT * FROM Report WHERE reportUid = ?",
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,entityUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(null) {
        val _tmp_reportUid = _result.getLong("reportUid")
        val _tmp_reportTitle = _result.getString("reportTitle")
        val _tmp_reportOptions = _result.getString("reportOptions")
        val _tmp_reportIsTemplate = _result.getBoolean("reportIsTemplate")
        val _tmp_reportLastModTime = _result.getLong("reportLastModTime")
        val _tmp_reportOwnerPersonUid = _result.getLong("reportOwnerPersonUid")
        Report().apply {
          this.reportUid = _tmp_reportUid
          this.reportTitle = _tmp_reportTitle
          this.reportOptions = _tmp_reportOptions
          this.reportIsTemplate = _tmp_reportIsTemplate
          this.reportLastModTime = _tmp_reportLastModTime
          this.reportOwnerPersonUid = _tmp_reportOwnerPersonUid
        }
      }
    }
  }

  override fun findByUidLive(uid: Long): Flow<Report?> = _db.doorFlow(arrayOf("Report")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = "SELECT * FROM Report WHERE reportUid = ?",
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,uid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(null) {
          val _tmp_reportUid = _result.getLong("reportUid")
          val _tmp_reportTitle = _result.getString("reportTitle")
          val _tmp_reportOptions = _result.getString("reportOptions")
          val _tmp_reportIsTemplate = _result.getBoolean("reportIsTemplate")
          val _tmp_reportLastModTime = _result.getLong("reportLastModTime")
          val _tmp_reportOwnerPersonUid = _result.getLong("reportOwnerPersonUid")
          Report().apply {
            this.reportUid = _tmp_reportUid
            this.reportTitle = _tmp_reportTitle
            this.reportOptions = _tmp_reportOptions
            this.reportIsTemplate = _tmp_reportIsTemplate
            this.reportLastModTime = _tmp_reportLastModTime
            this.reportOwnerPersonUid = _tmp_reportOwnerPersonUid
          }
        }
      }
    }
  }

  override fun findAllActiveReportLive(isTemplate: Boolean): Flow<List<Report>> =
      _db.doorFlow(arrayOf("Report")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT * FROM Report 
      |        WHERE reportIsTemplate = ?
      |        ORDER BY reportTitle ASC
      |    
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setBoolean(1,isTemplate)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          val _tmp_reportUid = _result.getLong("reportUid")
          val _tmp_reportTitle = _result.getString("reportTitle")
          val _tmp_reportOptions = _result.getString("reportOptions")
          val _tmp_reportIsTemplate = _result.getBoolean("reportIsTemplate")
          val _tmp_reportLastModTime = _result.getLong("reportLastModTime")
          val _tmp_reportOwnerPersonUid = _result.getLong("reportOwnerPersonUid")
          Report().apply {
            this.reportUid = _tmp_reportUid
            this.reportTitle = _tmp_reportTitle
            this.reportOptions = _tmp_reportOptions
            this.reportIsTemplate = _tmp_reportIsTemplate
            this.reportLastModTime = _tmp_reportLastModTime
            this.reportOwnerPersonUid = _tmp_reportOwnerPersonUid
          }
        }
      }
    }
  }

  override fun findAllActiveReportList(isTemplate: Boolean): List<Report> =
      _db.prepareAndUseStatement(PreparedStatementConfig(
    sql = """
    |
    |        SELECT * FROM Report 
    |        WHERE reportIsTemplate = ?
    |        ORDER BY reportTitle ASC
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setBoolean(1,isTemplate)
    _stmt.executeQuery().useResults{ _result -> 
      _result.mapRows {
        val _tmp_reportUid = _result.getLong("reportUid")
        val _tmp_reportTitle = _result.getString("reportTitle")
        val _tmp_reportOptions = _result.getString("reportOptions")
        val _tmp_reportIsTemplate = _result.getBoolean("reportIsTemplate")
        val _tmp_reportLastModTime = _result.getLong("reportLastModTime")
        val _tmp_reportOwnerPersonUid = _result.getLong("reportOwnerPersonUid")
        Report().apply {
          this.reportUid = _tmp_reportUid
          this.reportTitle = _tmp_reportTitle
          this.reportOptions = _tmp_reportOptions
          this.reportIsTemplate = _tmp_reportIsTemplate
          this.reportLastModTime = _tmp_reportLastModTime
          this.reportOwnerPersonUid = _tmp_reportOwnerPersonUid
        }
      }
    }
  }

  override fun findByUidList(uidList: List<Long>): List<Long> =
      _db.prepareAndUseStatement(PreparedStatementConfig(
    sql = "SELECT reportUid FROM Report WHERE reportUid IN (?)",
    hasListParams = true,
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setArray(1, _stmt.getConnection().createArrayOrProxyArrayOf("BIGINT",
        uidList.toTypedArray()))
    _stmt.executeQuery().useResults{ _result -> 
      _result.mapRows {
        _result.getLong(1)
      }
    }
  }

  override suspend fun toggleVisibilityReportItems(
    toggleVisibility: Boolean,
    selectedItem: List<Long>,
    updateTime: Long,
  ) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        UPDATE Report 
      |        SET reportIsTemplate = ?,
      |            reportLastModTime = ?
      |        WHERE reportUid IN (?)
      |    
      """.trimMargin(),
      hasListParams = true,
      readOnly = false,)
    ) { _stmt -> 
      _stmt.setBoolean(1,toggleVisibility)
      _stmt.setLong(2,updateTime)
      _stmt.setArray(3, _stmt.getConnection().createArrayOrProxyArrayOf("BIGINT",
          selectedItem.toTypedArray()))
      _stmt.executeUpdateAsyncKmp()
    }
  }
}
