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.jdbc.PreparedStatement
import com.ustadmobile.door.jdbc.ext.executeQueryAsyncKmp
import com.ustadmobile.door.jdbc.ext.executeUpdateAsyncKmp
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.lib.db.entities.ReportQueryResult
import kotlin.Boolean
import kotlin.IllegalArgumentException
import kotlin.Long
import kotlin.String
import kotlin.collections.List

public class ReportQueryResultDao_JdbcImpl(
  public val _db: RoomDatabase,
) : ReportQueryResultDao() {
  public val _insertAdapterReportQueryResult_abort: EntityInsertionAdapter<ReportQueryResult> =
      object : EntityInsertionAdapter<ReportQueryResult>(_db) {
    override fun makeSql(returnsId: Boolean): String = when(dbType) {
      DoorDbType.SQLITE -> {
        "INSERT INTO ReportQueryResult (rqrUid, rqrReportUid, rqrLastModified, rqrLastValidated, rqrReportSeriesUid, rqrXAxis, rqrYAxis, rqrSubgroup, rqrTimeZone) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)"
      }
      DoorDbType.POSTGRES ->  {
        "INSERT INTO ReportQueryResult (rqrUid, rqrReportUid, rqrLastModified, rqrLastValidated, rqrReportSeriesUid, rqrXAxis, rqrYAxis, rqrSubgroup, rqrTimeZone) VALUES(COALESCE(?,nextval('ReportQueryResult_rqrUid_seq')), ?, ?, ?, ?, ?, ?, ?, ?)" + if(returnsId) { " RETURNING rqrUid" } else "" 
      }
      else -> {
        throw IllegalArgumentException("Unsupported db type")
      }
    }

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement, entity: ReportQueryResult) {
      if(entity.rqrUid == 0L) {
        stmt.setObject(1, null)
      } else {
        stmt.setLong(1, entity.rqrUid)
      }
      stmt.setLong(2, entity.rqrReportUid)
      stmt.setLong(3, entity.rqrLastModified)
      stmt.setLong(4, entity.rqrLastValidated)
      stmt.setInt(5, entity.rqrReportSeriesUid)
      stmt.setString(6, entity.rqrXAxis)
      stmt.setDouble(7, entity.rqrYAxis)
      stmt.setString(8, entity.rqrSubgroup)
      stmt.setString(9, entity.rqrTimeZone)
    }
  }

  override suspend fun insertAllAsync(results: List<ReportQueryResult>) {
    _insertAdapterReportQueryResult_abort.insertListAsync(results)
  }

  override suspend fun deleteByReportUidAndTimeZone(reportUid: Long, timeZone: String) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        DELETE FROM ReportQueryResult
      |         WHERE rqrReportUid = ?
      |           AND rqrTimeZone = ?
      |    
      """.trimMargin(),
      readOnly = false,)
    ) { _stmt -> 
      _stmt.setLong(1,reportUid)
      _stmt.setString(2,timeZone)
      _stmt.executeUpdateAsyncKmp()
    }
  }

  override suspend fun getAllByReportUidAndTimeZone(reportUid: Long, timeZone: String):
      List<ReportQueryResult> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT ReportQueryResult.*
    |          FROM ReportQueryResult
    |         WHERE ReportQueryResult.rqrReportUid = ? 
    |           AND ReportQueryResult.rqrTimeZone = ?
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,reportUid)
    _stmt.setString(2,timeZone)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_rqrUid = _result.getLong("rqrUid")
        val _tmp_rqrReportUid = _result.getLong("rqrReportUid")
        val _tmp_rqrLastModified = _result.getLong("rqrLastModified")
        val _tmp_rqrLastValidated = _result.getLong("rqrLastValidated")
        val _tmp_rqrReportSeriesUid = _result.getInt("rqrReportSeriesUid")
        val _tmp_rqrXAxis = _result.getStringNonNull("rqrXAxis")
        val _tmp_rqrYAxis = _result.getDouble("rqrYAxis")
        val _tmp_rqrSubgroup = _result.getStringNonNull("rqrSubgroup")
        val _tmp_rqrTimeZone = _result.getStringNonNull("rqrTimeZone")
        ReportQueryResult().apply {
          this.rqrUid = _tmp_rqrUid
          this.rqrReportUid = _tmp_rqrReportUid
          this.rqrLastModified = _tmp_rqrLastModified
          this.rqrLastValidated = _tmp_rqrLastValidated
          this.rqrReportSeriesUid = _tmp_rqrReportSeriesUid
          this.rqrXAxis = _tmp_rqrXAxis
          this.rqrYAxis = _tmp_rqrYAxis
          this.rqrSubgroup = _tmp_rqrSubgroup
          this.rqrTimeZone = _tmp_rqrTimeZone
        }
      }
    }
  }

  override suspend fun isReportFresh(
    reportUid: Long,
    timeZone: String,
    freshThresholdTime: Long,
  ): Boolean = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT COALESCE(
    |               (SELECT ReportQueryResult.rqrLastModified
    |                  FROM ReportQueryResult
    |                 WHERE ReportQueryResult.rqrReportUid = ?
    |                   AND ReportQueryResult.rqrTimeZone = ?
    |                 LIMIT 1), 0) >= 
    |               (SELECT MAX(?, 
    |                            (SELECT COALESCE(
    |                                    (SELECT Report.reportLastModTime
    |                                       FROM Report
    |                                      WHERE Report.reportUid = ?), 0))))
    |    
    """.trimMargin(),
    postgreSql = """
    |
    |        SELECT COALESCE(
    |               (SELECT ReportQueryResult.rqrLastModified
    |                  FROM ReportQueryResult
    |                 WHERE ReportQueryResult.rqrReportUid = ?
    |                   AND ReportQueryResult.rqrTimeZone = ?
    |                 LIMIT 1), 0) >= 
    |               (SELECT GREATEST(?, 
    |                            (SELECT COALESCE(
    |                                    (SELECT Report.reportLastModTime
    |                                       FROM Report
    |                                      WHERE Report.reportUid = ?), 0))))
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,reportUid)
    _stmt.setString(2,timeZone)
    _stmt.setLong(3,freshThresholdTime)
    _stmt.setLong(4,reportUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(false) {
        _result.getBoolean(1)
      }
    }
  }
}
