package com.ustadmobile.core.db.dao

import androidx.paging.PagingSource
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.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.composites.MessageAndOtherPerson
import com.ustadmobile.lib.db.entities.Message
import com.ustadmobile.lib.db.entities.Person
import com.ustadmobile.lib.db.entities.PersonPicture
import kotlin.Boolean
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.collections.List

public class MessageDao_JdbcImpl(
  public val _db: RoomDatabase,
) : MessageDao() {
  public val _insertAdapterMessage_abort: EntityInsertionAdapter<Message> = object :
      EntityInsertionAdapter<Message>(_db) {
    override fun makeSql(returnsId: Boolean): String =
        "INSERT INTO Message (messageUid, messageSenderPersonUid, messageToPersonUid, messageText, messageTimestamp, messageLct) VALUES(?, ?, ?, ?, ?, ?)"

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement, entity: Message) {
      if(entity.messageUid == 0L) {
        stmt.setObject(1, null)
      } else {
        stmt.setLong(1, entity.messageUid)
      }
      stmt.setLong(2, entity.messageSenderPersonUid)
      stmt.setLong(3, entity.messageToPersonUid)
      stmt.setString(4, entity.messageText)
      stmt.setLong(5, entity.messageTimestamp)
      stmt.setLong(6, entity.messageLct)
    }
  }

  override suspend fun insert(message: Message) {
    _insertAdapterMessage_abort.insertAsync(message)
  }

  override fun messagesFromOtherUserAsPagingSource(accountPersonUid: Long, otherPersonUid: Long):
      PagingSource<Int, Message> = object : DoorLimitOffsetPagingSource<Message>(db = _db
  , tableNames = arrayOf("Message")
  ) {
    override suspend fun loadRows(_limit: Int, _offset: Int): List<Message> =
        _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT * FROM (
      |        SELECT Message.*
      |          FROM Message
      |         WHERE (Message.messageSenderPersonUid = CAST(? AS BIGINT)
      |                AND Message.messageToPersonUid = CAST(? AS BIGINT))
      |            OR (Message.messageSenderPersonUid = CAST(? AS BIGINT)
      |                AND Message.messageToPersonUid = CAST(? AS BIGINT)) 
      |      ORDER BY Message.messageTimestamp DESC          
      |    ) AS _PagingData LIMIT ? OFFSET ?
      """.trimMargin(),
      postgreSql = """
      |SELECT * FROM (
      |        SELECT Message.*
      |          FROM Message
      |         WHERE (Message.messageSenderPersonUid = ?
      |                AND Message.messageToPersonUid = ?)
      |            OR (Message.messageSenderPersonUid = ?
      |                AND Message.messageToPersonUid = ?) 
      |      ORDER BY Message.messageTimestamp DESC          
      |    ) AS _PagingData LIMIT ? OFFSET ?
      |""".trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,accountPersonUid)
      _stmt.setLong(2,otherPersonUid)
      _stmt.setLong(3,otherPersonUid)
      _stmt.setLong(4,accountPersonUid)
      _stmt.setInt(5,_limit)
      _stmt.setInt(6,_offset)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          val _tmp_messageUid = _result.getLong("messageUid")
          val _tmp_messageSenderPersonUid = _result.getLong("messageSenderPersonUid")
          val _tmp_messageToPersonUid = _result.getLong("messageToPersonUid")
          val _tmp_messageText = _result.getString("messageText")
          val _tmp_messageTimestamp = _result.getLong("messageTimestamp")
          val _tmp_messageLct = _result.getLong("messageLct")
          Message().apply {
            this.messageUid = _tmp_messageUid
            this.messageSenderPersonUid = _tmp_messageSenderPersonUid
            this.messageToPersonUid = _tmp_messageToPersonUid
            this.messageText = _tmp_messageText
            this.messageTimestamp = _tmp_messageTimestamp
            this.messageLct = _tmp_messageLct
          }
        }
      }
    }

    override suspend fun countRows(): Int = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT COUNT(*) FROM (
      |        SELECT Message.*
      |          FROM Message
      |         WHERE (Message.messageSenderPersonUid = CAST(? AS BIGINT)
      |                AND Message.messageToPersonUid = CAST(? AS BIGINT))
      |            OR (Message.messageSenderPersonUid = CAST(? AS BIGINT)
      |                AND Message.messageToPersonUid = CAST(? AS BIGINT)) 
      |      ORDER BY Message.messageTimestamp DESC          
      |    ) AS _PagingCount
      """.trimMargin(),
      postgreSql = """
      |SELECT COUNT(*) FROM (
      |        SELECT Message.*
      |          FROM Message
      |         WHERE (Message.messageSenderPersonUid = ?
      |                AND Message.messageToPersonUid = ?)
      |            OR (Message.messageSenderPersonUid = ?
      |                AND Message.messageToPersonUid = ?) 
      |      ORDER BY Message.messageTimestamp DESC          
      |    ) AS _PagingCount
      |""".trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,accountPersonUid)
      _stmt.setLong(2,otherPersonUid)
      _stmt.setLong(3,otherPersonUid)
      _stmt.setLong(4,accountPersonUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(0) {
          _result.getInt(1)
        }
      }
    }
  }

  override fun conversationsForUserAsPagingSource(searchQuery: String, accountPersonUid: Long):
      PagingSource<Int, MessageAndOtherPerson> = object :
      DoorLimitOffsetPagingSource<MessageAndOtherPerson>(db = _db
  , tableNames = arrayOf("Message", "Person", "PersonPicture")
  ) {
    override suspend fun loadRows(_limit: Int, _offset: Int): List<MessageAndOtherPerson> =
        _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT * FROM (
      |        SELECT Person.*, LatestMessage.*, PersonPicture.*
      |          FROM Person
      |               JOIN Message LatestMessage
      |                    ON LatestMessage.messageUid = 
      |                       (SELECT Message.messageUid
      |                          FROM Message
      |                         WHERE (Message.messageSenderPersonUid = CAST(? AS BIGINT)
      |                                AND Message.messageToPersonUid = Person.personUid)
      |                            OR (Message.messageSenderPersonUid = Person.personUid
      |                                AND Message.messageToPersonUid = CAST(? AS BIGINT))
      |                       ORDER BY Message.messageTimestamp DESC
      |                          LIMIT 1)
      |                          
      |                LEFT JOIN PersonPicture
      |                          ON PersonPicture.personPictureUid = Person.personUid
      |         WHERE ? = '%' 
      |               OR (Person.firstNames || ' ' || Person.lastName) LIKE ?
      |      ORDER BY LatestMessage.messageTimestamp DESC
      |    ) AS _PagingData LIMIT ? OFFSET ?
      """.trimMargin(),
      postgreSql = """
      |SELECT * FROM (
      |        SELECT Person.*, LatestMessage.*, PersonPicture.*
      |          FROM Person
      |               JOIN Message LatestMessage
      |                    ON LatestMessage.messageUid = 
      |                       (SELECT Message.messageUid
      |                          FROM Message
      |                         WHERE (Message.messageSenderPersonUid = ?
      |                                AND Message.messageToPersonUid = Person.personUid)
      |                            OR (Message.messageSenderPersonUid = Person.personUid
      |                                AND Message.messageToPersonUid = ?)
      |                       ORDER BY Message.messageTimestamp DESC
      |                          LIMIT 1)
      |                          
      |                LEFT JOIN PersonPicture
      |                          ON PersonPicture.personPictureUid = Person.personUid
      |         WHERE ? = '%' 
      |               OR (Person.firstNames || ' ' || Person.lastName) LIKE ?
      |      ORDER BY LatestMessage.messageTimestamp DESC
      |    ) AS _PagingData LIMIT ? OFFSET ?
      |""".trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,accountPersonUid)
      _stmt.setLong(2,accountPersonUid)
      _stmt.setString(3,searchQuery)
      _stmt.setString(4,searchQuery)
      _stmt.setInt(5,_limit)
      _stmt.setInt(6,_offset)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          var _tmp_Message_nullCount = 0
          val _tmp_messageUid = _result.getLong("messageUid")
          if(_result.wasNull()) _tmp_Message_nullCount++
          val _tmp_messageSenderPersonUid = _result.getLong("messageSenderPersonUid")
          if(_result.wasNull()) _tmp_Message_nullCount++
          val _tmp_messageToPersonUid = _result.getLong("messageToPersonUid")
          if(_result.wasNull()) _tmp_Message_nullCount++
          val _tmp_messageText = _result.getString("messageText")
          if(_result.wasNull()) _tmp_Message_nullCount++
          val _tmp_messageTimestamp = _result.getLong("messageTimestamp")
          if(_result.wasNull()) _tmp_Message_nullCount++
          val _tmp_messageLct = _result.getLong("messageLct")
          if(_result.wasNull()) _tmp_Message_nullCount++
          val _tmp_Message_isAllNull = _tmp_Message_nullCount == 6
          var _tmp_Person_nullCount = 0
          val _tmp_personUid = _result.getLong("personUid")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_username = _result.getString("username")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_firstNames = _result.getString("firstNames")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_lastName = _result.getString("lastName")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_emailAddr = _result.getString("emailAddr")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_phoneNum = _result.getString("phoneNum")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_gender = _result.getInt("gender")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_active = _result.getBoolean("active")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_dateOfBirth = _result.getLong("dateOfBirth")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personAddress = _result.getString("personAddress")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personOrgId = _result.getString("personOrgId")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personGroupUid = _result.getLong("personGroupUid")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personLct = _result.getLong("personLct")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personCountry = _result.getString("personCountry")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personType = _result.getInt("personType")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personMasterChangeSeqNum = _result.getLong("personMasterChangeSeqNum")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personLocalChangeSeqNum = _result.getLong("personLocalChangeSeqNum")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personLastChangedBy = _result.getInt("personLastChangedBy")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_admin = _result.getBoolean("admin")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personNotes = _result.getString("personNotes")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_fatherName = _result.getString("fatherName")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_fatherNumber = _result.getString("fatherNumber")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_motherName = _result.getString("motherName")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_motherNum = _result.getString("motherNum")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_Person_isAllNull = _tmp_Person_nullCount == 24
          var _tmp_PersonPicture_nullCount = 0
          val _tmp_personPictureUid = _result.getLong("personPictureUid")
          if(_result.wasNull()) _tmp_PersonPicture_nullCount++
          val _tmp_personPictureLct = _result.getLong("personPictureLct")
          if(_result.wasNull()) _tmp_PersonPicture_nullCount++
          val _tmp_personPictureUri = _result.getString("personPictureUri")
          if(_result.wasNull()) _tmp_PersonPicture_nullCount++
          val _tmp_personPictureThumbnailUri = _result.getString("personPictureThumbnailUri")
          if(_result.wasNull()) _tmp_PersonPicture_nullCount++
          val _tmp_fileSize = _result.getInt("fileSize")
          if(_result.wasNull()) _tmp_PersonPicture_nullCount++
          val _tmp_personPictureActive = _result.getBoolean("personPictureActive")
          if(_result.wasNull()) _tmp_PersonPicture_nullCount++
          val _tmp_PersonPicture_isAllNull = _tmp_PersonPicture_nullCount == 6
          MessageAndOtherPerson().apply {
            if(!_tmp_Message_isAllNull) {
              this.message = Message().apply {
                this.messageUid = _tmp_messageUid
                this.messageSenderPersonUid = _tmp_messageSenderPersonUid
                this.messageToPersonUid = _tmp_messageToPersonUid
                this.messageText = _tmp_messageText
                this.messageTimestamp = _tmp_messageTimestamp
                this.messageLct = _tmp_messageLct
              }
            }
            if(!_tmp_Person_isAllNull) {
              this.otherPerson = Person().apply {
                this.personUid = _tmp_personUid
                this.username = _tmp_username
                this.firstNames = _tmp_firstNames
                this.lastName = _tmp_lastName
                this.emailAddr = _tmp_emailAddr
                this.phoneNum = _tmp_phoneNum
                this.gender = _tmp_gender
                this.active = _tmp_active
                this.dateOfBirth = _tmp_dateOfBirth
                this.personAddress = _tmp_personAddress
                this.personOrgId = _tmp_personOrgId
                this.personGroupUid = _tmp_personGroupUid
                this.personLct = _tmp_personLct
                this.personCountry = _tmp_personCountry
                this.personType = _tmp_personType
                this.personMasterChangeSeqNum = _tmp_personMasterChangeSeqNum
                this.personLocalChangeSeqNum = _tmp_personLocalChangeSeqNum
                this.personLastChangedBy = _tmp_personLastChangedBy
                this.admin = _tmp_admin
                this.personNotes = _tmp_personNotes
                this.fatherName = _tmp_fatherName
                this.fatherNumber = _tmp_fatherNumber
                this.motherName = _tmp_motherName
                this.motherNum = _tmp_motherNum
              }
            }
            if(!_tmp_PersonPicture_isAllNull) {
              this.personPicture = PersonPicture().apply {
                this.personPictureUid = _tmp_personPictureUid
                this.personPictureLct = _tmp_personPictureLct
                this.personPictureUri = _tmp_personPictureUri
                this.personPictureThumbnailUri = _tmp_personPictureThumbnailUri
                this.fileSize = _tmp_fileSize
                this.personPictureActive = _tmp_personPictureActive
              }
            }
          }
        }
      }
    }

    override suspend fun countRows(): Int = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT COUNT(*) FROM (
      |        SELECT Person.*, LatestMessage.*, PersonPicture.*
      |          FROM Person
      |               JOIN Message LatestMessage
      |                    ON LatestMessage.messageUid = 
      |                       (SELECT Message.messageUid
      |                          FROM Message
      |                         WHERE (Message.messageSenderPersonUid = CAST(? AS BIGINT)
      |                                AND Message.messageToPersonUid = Person.personUid)
      |                            OR (Message.messageSenderPersonUid = Person.personUid
      |                                AND Message.messageToPersonUid = CAST(? AS BIGINT))
      |                       ORDER BY Message.messageTimestamp DESC
      |                          LIMIT 1)
      |                          
      |                LEFT JOIN PersonPicture
      |                          ON PersonPicture.personPictureUid = Person.personUid
      |         WHERE ? = '%' 
      |               OR (Person.firstNames || ' ' || Person.lastName) LIKE ?
      |      ORDER BY LatestMessage.messageTimestamp DESC
      |    ) AS _PagingCount
      """.trimMargin(),
      postgreSql = """
      |SELECT COUNT(*) FROM (
      |        SELECT Person.*, LatestMessage.*, PersonPicture.*
      |          FROM Person
      |               JOIN Message LatestMessage
      |                    ON LatestMessage.messageUid = 
      |                       (SELECT Message.messageUid
      |                          FROM Message
      |                         WHERE (Message.messageSenderPersonUid = ?
      |                                AND Message.messageToPersonUid = Person.personUid)
      |                            OR (Message.messageSenderPersonUid = Person.personUid
      |                                AND Message.messageToPersonUid = ?)
      |                       ORDER BY Message.messageTimestamp DESC
      |                          LIMIT 1)
      |                          
      |                LEFT JOIN PersonPicture
      |                          ON PersonPicture.personPictureUid = Person.personUid
      |         WHERE ? = '%' 
      |               OR (Person.firstNames || ' ' || Person.lastName) LIKE ?
      |      ORDER BY LatestMessage.messageTimestamp DESC
      |    ) AS _PagingCount
      |""".trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,accountPersonUid)
      _stmt.setLong(2,accountPersonUid)
      _stmt.setString(3,searchQuery)
      _stmt.setString(4,searchQuery)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(0) {
          _result.getInt(1)
        }
      }
    }
  }
}
