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.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.composites.DiscussionPostAndPosterNames
import com.ustadmobile.lib.db.composites.PersonAndPicture
import com.ustadmobile.lib.db.entities.DiscussionPost
import com.ustadmobile.lib.db.entities.DiscussionPostWithDetails
import com.ustadmobile.lib.db.entities.Person
import com.ustadmobile.lib.db.entities.PersonPicture
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 DiscussionPostDao_JdbcImpl(
  public val _db: RoomDatabase,
) : DiscussionPostDao() {
  public val _insertAdapterDiscussionPost_upsert: EntityInsertionAdapter<DiscussionPost> = object :
      EntityInsertionAdapter<DiscussionPost>(_db) {
    override fun makeSql(returnsId: Boolean): String = when(dbType) {
      DoorDbType.SQLITE -> {
        "INSERT OR REPLACE INTO DiscussionPost (discussionPostUid, discussionPostReplyToPostUid, discussionPostTitle, discussionPostMessage, discussionPostStartDate, discussionPostCourseBlockUid, dpDeleted, discussionPostStartedPersonUid, discussionPostClazzUid, discussionPostLct, discussionPostVisible, discussionPostArchive) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
      }
      DoorDbType.POSTGRES ->  {
        "INSERT INTO DiscussionPost (discussionPostUid, discussionPostReplyToPostUid, discussionPostTitle, discussionPostMessage, discussionPostStartDate, discussionPostCourseBlockUid, dpDeleted, discussionPostStartedPersonUid, discussionPostClazzUid, discussionPostLct, discussionPostVisible, discussionPostArchive) VALUES(COALESCE(?,nextval('DiscussionPost_discussionPostUid_seq')), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT (discussionPostUid) DO UPDATE SET discussionPostReplyToPostUid = excluded.discussionPostReplyToPostUid,discussionPostTitle = excluded.discussionPostTitle,discussionPostMessage = excluded.discussionPostMessage,discussionPostStartDate = excluded.discussionPostStartDate,discussionPostCourseBlockUid = excluded.discussionPostCourseBlockUid,dpDeleted = excluded.dpDeleted,discussionPostStartedPersonUid = excluded.discussionPostStartedPersonUid,discussionPostClazzUid = excluded.discussionPostClazzUid,discussionPostLct = excluded.discussionPostLct,discussionPostVisible = excluded.discussionPostVisible,discussionPostArchive = excluded.discussionPostArchive" + if(returnsId) { " RETURNING discussionPostUid" } else "" 
      }
      else -> {
        throw IllegalArgumentException("Unsupported db type")
      }
    }

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement, entity: DiscussionPost) {
      if(entity.discussionPostUid == 0L) {
        stmt.setObject(1, null)
      } else {
        stmt.setLong(1, entity.discussionPostUid)
      }
      stmt.setLong(2, entity.discussionPostReplyToPostUid)
      stmt.setString(3, entity.discussionPostTitle)
      stmt.setString(4, entity.discussionPostMessage)
      stmt.setLong(5, entity.discussionPostStartDate)
      stmt.setLong(6, entity.discussionPostCourseBlockUid)
      stmt.setBoolean(7, entity.dpDeleted)
      stmt.setLong(8, entity.discussionPostStartedPersonUid)
      stmt.setLong(9, entity.discussionPostClazzUid)
      stmt.setLong(10, entity.discussionPostLct)
      stmt.setBoolean(11, entity.discussionPostVisible)
      stmt.setBoolean(12, entity.discussionPostArchive)
    }
  }

  public val _insertAdapterDiscussionPost_abort: EntityInsertionAdapter<DiscussionPost> = object :
      EntityInsertionAdapter<DiscussionPost>(_db) {
    override fun makeSql(returnsId: Boolean): String = when(dbType) {
      DoorDbType.SQLITE -> {
        "INSERT INTO DiscussionPost (discussionPostUid, discussionPostReplyToPostUid, discussionPostTitle, discussionPostMessage, discussionPostStartDate, discussionPostCourseBlockUid, dpDeleted, discussionPostStartedPersonUid, discussionPostClazzUid, discussionPostLct, discussionPostVisible, discussionPostArchive) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
      }
      DoorDbType.POSTGRES ->  {
        "INSERT INTO DiscussionPost (discussionPostUid, discussionPostReplyToPostUid, discussionPostTitle, discussionPostMessage, discussionPostStartDate, discussionPostCourseBlockUid, dpDeleted, discussionPostStartedPersonUid, discussionPostClazzUid, discussionPostLct, discussionPostVisible, discussionPostArchive) VALUES(COALESCE(?,nextval('DiscussionPost_discussionPostUid_seq')), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" + if(returnsId) { " RETURNING discussionPostUid" } else "" 
      }
      else -> {
        throw IllegalArgumentException("Unsupported db type")
      }
    }

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement, entity: DiscussionPost) {
      if(entity.discussionPostUid == 0L) {
        stmt.setObject(1, null)
      } else {
        stmt.setLong(1, entity.discussionPostUid)
      }
      stmt.setLong(2, entity.discussionPostReplyToPostUid)
      stmt.setString(3, entity.discussionPostTitle)
      stmt.setString(4, entity.discussionPostMessage)
      stmt.setLong(5, entity.discussionPostStartDate)
      stmt.setLong(6, entity.discussionPostCourseBlockUid)
      stmt.setBoolean(7, entity.dpDeleted)
      stmt.setLong(8, entity.discussionPostStartedPersonUid)
      stmt.setLong(9, entity.discussionPostClazzUid)
      stmt.setLong(10, entity.discussionPostLct)
      stmt.setBoolean(11, entity.discussionPostVisible)
      stmt.setBoolean(12, entity.discussionPostArchive)
    }
  }

  override suspend fun upsertAsync(entity: DiscussionPost) {
    _insertAdapterDiscussionPost_upsert.insertAsync(entity)
  }

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

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

  public override fun insertList(entityList: List<DiscussionPost>) {
    _insertAdapterDiscussionPost_abort.insertList(entityList)
  }

  override suspend fun updateAsync(entity: DiscussionPost): Int {
    var _result = 0
    val _sql =
        "UPDATE DiscussionPost SET discussionPostReplyToPostUid = ?, discussionPostTitle = ?, discussionPostMessage = ?, discussionPostStartDate = ?, discussionPostCourseBlockUid = ?, dpDeleted = ?, discussionPostStartedPersonUid = ?, discussionPostClazzUid = ?, discussionPostLct = ?, discussionPostVisible = ?, discussionPostArchive = ? WHERE discussionPostUid = ?"
    _db.prepareAndUseStatementAsync(_sql) {
       _stmt ->
      _stmt.setLong(1, entity.discussionPostReplyToPostUid)
      _stmt.setString(2, entity.discussionPostTitle)
      _stmt.setString(3, entity.discussionPostMessage)
      _stmt.setLong(4, entity.discussionPostStartDate)
      _stmt.setLong(5, entity.discussionPostCourseBlockUid)
      _stmt.setBoolean(6, entity.dpDeleted)
      _stmt.setLong(7, entity.discussionPostStartedPersonUid)
      _stmt.setLong(8, entity.discussionPostClazzUid)
      _stmt.setLong(9, entity.discussionPostLct)
      _stmt.setBoolean(10, entity.discussionPostVisible)
      _stmt.setBoolean(11, entity.discussionPostArchive)
      _stmt.setLong(12, entity.discussionPostUid)
      _result += _stmt.executeUpdateAsyncKmp()
    }
    return _result
  }

  public override fun update(entity: DiscussionPost) {
    val _sql =
        "UPDATE DiscussionPost SET discussionPostReplyToPostUid = ?, discussionPostTitle = ?, discussionPostMessage = ?, discussionPostStartDate = ?, discussionPostCourseBlockUid = ?, dpDeleted = ?, discussionPostStartedPersonUid = ?, discussionPostClazzUid = ?, discussionPostLct = ?, discussionPostVisible = ?, discussionPostArchive = ? WHERE discussionPostUid = ?"
    _db.prepareAndUseStatement(_sql) {
       _stmt ->
      _stmt.setLong(1, entity.discussionPostReplyToPostUid)
      _stmt.setString(2, entity.discussionPostTitle)
      _stmt.setString(3, entity.discussionPostMessage)
      _stmt.setLong(4, entity.discussionPostStartDate)
      _stmt.setLong(5, entity.discussionPostCourseBlockUid)
      _stmt.setBoolean(6, entity.dpDeleted)
      _stmt.setLong(7, entity.discussionPostStartedPersonUid)
      _stmt.setLong(8, entity.discussionPostClazzUid)
      _stmt.setLong(9, entity.discussionPostLct)
      _stmt.setBoolean(10, entity.discussionPostVisible)
      _stmt.setBoolean(11, entity.discussionPostArchive)
      _stmt.setLong(12, entity.discussionPostUid)
      _stmt.executeUpdate()
    }
  }

  override fun getTopLevelPostsByCourseBlockUid(courseBlockUid: Long, includeDeleted: Boolean):
      PagingSource<Int, DiscussionPostWithDetails> = object :
      DoorLimitOffsetPagingSource<DiscussionPostWithDetails>(db = _db
  , tableNames = arrayOf("Person", "PersonPicture", "DiscussionPost")
  ) {
    override suspend fun loadRows(_limit: Int, _offset: Int): List<DiscussionPostWithDetails> =
        _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT * FROM (
      |        SELECT DiscussionPost.*,
      |               Person.firstNames as authorPersonFirstNames,
      |               Person.lastName as authorPersonLastName,
      |               PersonPicture.personPictureThumbnailUri AS authorPictureUri,
      |               MostRecentReply.discussionPostMessage AS postLatestMessage,
      |               COALESCE(MostRecentReply.discussionPostStartDate, 0) AS postLatestMessageTimestamp,
      |               (SELECT COUNT(*)
      |                  FROM DiscussionPost DiscussionPostReplies
      |                 WHERE DiscussionPostReplies.discussionPostReplyToPostUid = DiscussionPost.discussionPostUid
      |                   AND NOT DiscussionPostReplies.dpDeleted
      |               ) AS postRepliesCount
      |          FROM DiscussionPost
      |               LEFT JOIN DiscussionPost AS MostRecentReply
      |                         ON MostRecentReply.discussionPostUid = 
      |                            (SELECT MostRecentReplyInner.discussionPostUid
      |                               FROM DiscussionPost AS MostRecentReplyInner
      |                              WHERE MostRecentReplyInner.discussionPostReplyToPostUid = DiscussionPost.discussionPostUid
      |                           ORDER BY MostRecentReplyInner.discussionPostStartDate DESC
      |                              LIMIT 1  
      |                            )
      |               LEFT JOIN Person 
      |                         ON Person.personUid = DiscussionPost.discussionPostStartedPersonUid
      |               LEFT JOIN PersonPicture
      |                         ON PersonPicture.personPictureUid = DiscussionPost.discussionPostStartedPersonUid
      |         WHERE DiscussionPost.discussionPostCourseBlockUid = ?
      |           AND DiscussionPost.discussionPostReplyToPostUid = 0
      |           AND (NOT DiscussionPost.dpDeleted OR CAST(? AS INTEGER) = 1)
      |      ORDER BY DiscussionPost.discussionPostStartDate DESC          
      |    ) AS _PagingData LIMIT ? OFFSET ?
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,courseBlockUid)
      _stmt.setBoolean(2,includeDeleted)
      _stmt.setInt(3,_limit)
      _stmt.setInt(4,_offset)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          val _tmp_authorPersonFirstNames = _result.getString("authorPersonFirstNames")
          val _tmp_authorPersonLastName = _result.getString("authorPersonLastName")
          val _tmp_authorPictureUri = _result.getString("authorPictureUri")
          val _tmp_postLatestMessage = _result.getString("postLatestMessage")
          val _tmp_postRepliesCount = _result.getInt("postRepliesCount")
          val _tmp_postLatestMessageTimestamp = _result.getLong("postLatestMessageTimestamp")
          val _tmp_discussionPostUid = _result.getLong("discussionPostUid")
          val _tmp_discussionPostReplyToPostUid = _result.getLong("discussionPostReplyToPostUid")
          val _tmp_discussionPostTitle = _result.getString("discussionPostTitle")
          val _tmp_discussionPostMessage = _result.getString("discussionPostMessage")
          val _tmp_discussionPostStartDate = _result.getLong("discussionPostStartDate")
          val _tmp_discussionPostCourseBlockUid = _result.getLong("discussionPostCourseBlockUid")
          val _tmp_dpDeleted = _result.getBoolean("dpDeleted")
          val _tmp_discussionPostStartedPersonUid =
              _result.getLong("discussionPostStartedPersonUid")
          val _tmp_discussionPostClazzUid = _result.getLong("discussionPostClazzUid")
          val _tmp_discussionPostLct = _result.getLong("discussionPostLct")
          val _tmp_discussionPostVisible = _result.getBoolean("discussionPostVisible")
          val _tmp_discussionPostArchive = _result.getBoolean("discussionPostArchive")
          DiscussionPostWithDetails().apply {
            this.authorPersonFirstNames = _tmp_authorPersonFirstNames
            this.authorPersonLastName = _tmp_authorPersonLastName
            this.authorPictureUri = _tmp_authorPictureUri
            this.postLatestMessage = _tmp_postLatestMessage
            this.postRepliesCount = _tmp_postRepliesCount
            this.postLatestMessageTimestamp = _tmp_postLatestMessageTimestamp
            this.discussionPostUid = _tmp_discussionPostUid
            this.discussionPostReplyToPostUid = _tmp_discussionPostReplyToPostUid
            this.discussionPostTitle = _tmp_discussionPostTitle
            this.discussionPostMessage = _tmp_discussionPostMessage
            this.discussionPostStartDate = _tmp_discussionPostStartDate
            this.discussionPostCourseBlockUid = _tmp_discussionPostCourseBlockUid
            this.dpDeleted = _tmp_dpDeleted
            this.discussionPostStartedPersonUid = _tmp_discussionPostStartedPersonUid
            this.discussionPostClazzUid = _tmp_discussionPostClazzUid
            this.discussionPostLct = _tmp_discussionPostLct
            this.discussionPostVisible = _tmp_discussionPostVisible
            this.discussionPostArchive = _tmp_discussionPostArchive
          }
        }
      }
    }

    override suspend fun countRows(): Int = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT COUNT(*) FROM (
      |        SELECT DiscussionPost.*,
      |               Person.firstNames as authorPersonFirstNames,
      |               Person.lastName as authorPersonLastName,
      |               PersonPicture.personPictureThumbnailUri AS authorPictureUri,
      |               MostRecentReply.discussionPostMessage AS postLatestMessage,
      |               COALESCE(MostRecentReply.discussionPostStartDate, 0) AS postLatestMessageTimestamp,
      |               (SELECT COUNT(*)
      |                  FROM DiscussionPost DiscussionPostReplies
      |                 WHERE DiscussionPostReplies.discussionPostReplyToPostUid = DiscussionPost.discussionPostUid
      |                   AND NOT DiscussionPostReplies.dpDeleted
      |               ) AS postRepliesCount
      |          FROM DiscussionPost
      |               LEFT JOIN DiscussionPost AS MostRecentReply
      |                         ON MostRecentReply.discussionPostUid = 
      |                            (SELECT MostRecentReplyInner.discussionPostUid
      |                               FROM DiscussionPost AS MostRecentReplyInner
      |                              WHERE MostRecentReplyInner.discussionPostReplyToPostUid = DiscussionPost.discussionPostUid
      |                           ORDER BY MostRecentReplyInner.discussionPostStartDate DESC
      |                              LIMIT 1  
      |                            )
      |               LEFT JOIN Person 
      |                         ON Person.personUid = DiscussionPost.discussionPostStartedPersonUid
      |               LEFT JOIN PersonPicture
      |                         ON PersonPicture.personPictureUid = DiscussionPost.discussionPostStartedPersonUid
      |         WHERE DiscussionPost.discussionPostCourseBlockUid = ?
      |           AND DiscussionPost.discussionPostReplyToPostUid = 0
      |           AND (NOT DiscussionPost.dpDeleted OR CAST(? AS INTEGER) = 1)
      |      ORDER BY DiscussionPost.discussionPostStartDate DESC          
      |    ) AS _PagingCount
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,courseBlockUid)
      _stmt.setBoolean(2,includeDeleted)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(0) {
          _result.getInt(1)
        }
      }
    }
  }

  override suspend fun getTopLevelPostsByCourseBlockUidPersons(courseBlockUid: Long):
      List<PersonAndPicture> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT Person.*, PersonPicture.*
    |          FROM Person
    |               LEFT JOIN PersonPicture
    |                         ON PersonPicture.personPictureUid = Person.personUid
    |         WHERE Person.personUid IN
    |               (SELECT DISTINCT DiscussionPost.discussionPostStartedPersonUid
    |                  FROM DiscussionPost
    |                 WHERE DiscussionPost.discussionPostCourseBlockUid = ?
    |                   AND DiscussionPost.discussionPostReplyToPostUid = 0)
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,courseBlockUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        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
        PersonAndPicture().apply {
          if(!_tmp_Person_isAllNull) {
            this.person = 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.picture = 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 getTopLevelPostsByCourseBlockUidLatestMessage(courseBlockUid: Long):
      List<DiscussionPost> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT MostRecentReply.*
    |          FROM DiscussionPost
    |               JOIN DiscussionPost AS MostRecentReply
    |                         ON MostRecentReply.discussionPostUid = 
    |                            (SELECT MostRecentReplyInner.discussionPostUid
    |                               FROM DiscussionPost AS MostRecentReplyInner
    |                              WHERE MostRecentReplyInner.discussionPostReplyToPostUid = DiscussionPost.discussionPostUid
    |                           ORDER BY MostRecentReplyInner.discussionPostStartDate DESC
    |                              LIMIT 1  
    |                            )
    |         WHERE DiscussionPost.discussionPostCourseBlockUid = ?
    |           AND DiscussionPost.discussionPostReplyToPostUid = 0 
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,courseBlockUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_discussionPostUid = _result.getLong("discussionPostUid")
        val _tmp_discussionPostReplyToPostUid = _result.getLong("discussionPostReplyToPostUid")
        val _tmp_discussionPostTitle = _result.getString("discussionPostTitle")
        val _tmp_discussionPostMessage = _result.getString("discussionPostMessage")
        val _tmp_discussionPostStartDate = _result.getLong("discussionPostStartDate")
        val _tmp_discussionPostCourseBlockUid = _result.getLong("discussionPostCourseBlockUid")
        val _tmp_dpDeleted = _result.getBoolean("dpDeleted")
        val _tmp_discussionPostStartedPersonUid = _result.getLong("discussionPostStartedPersonUid")
        val _tmp_discussionPostClazzUid = _result.getLong("discussionPostClazzUid")
        val _tmp_discussionPostLct = _result.getLong("discussionPostLct")
        val _tmp_discussionPostVisible = _result.getBoolean("discussionPostVisible")
        val _tmp_discussionPostArchive = _result.getBoolean("discussionPostArchive")
        DiscussionPost().apply {
          this.discussionPostUid = _tmp_discussionPostUid
          this.discussionPostReplyToPostUid = _tmp_discussionPostReplyToPostUid
          this.discussionPostTitle = _tmp_discussionPostTitle
          this.discussionPostMessage = _tmp_discussionPostMessage
          this.discussionPostStartDate = _tmp_discussionPostStartDate
          this.discussionPostCourseBlockUid = _tmp_discussionPostCourseBlockUid
          this.dpDeleted = _tmp_dpDeleted
          this.discussionPostStartedPersonUid = _tmp_discussionPostStartedPersonUid
          this.discussionPostClazzUid = _tmp_discussionPostClazzUid
          this.discussionPostLct = _tmp_discussionPostLct
          this.discussionPostVisible = _tmp_discussionPostVisible
          this.discussionPostArchive = _tmp_discussionPostArchive
        }
      }
    }
  }

  override suspend fun getPostTitle(postUid: Long): String? =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT DiscussionPost.discussionPostTitle 
    |          FROM DiscussionPost 
    |         WHERE DiscussionPost.discussionPostUid = ?
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,postUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(null) {
        _result.getString(1)
      }
    }
  }

  override suspend fun findByUid(uid: Long): DiscussionPost? =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT * 
    |         FROM DiscussionPost
    |        WHERE DiscussionPost.discussionPostUid = ?
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,uid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(null) {
        val _tmp_discussionPostUid = _result.getLong("discussionPostUid")
        val _tmp_discussionPostReplyToPostUid = _result.getLong("discussionPostReplyToPostUid")
        val _tmp_discussionPostTitle = _result.getString("discussionPostTitle")
        val _tmp_discussionPostMessage = _result.getString("discussionPostMessage")
        val _tmp_discussionPostStartDate = _result.getLong("discussionPostStartDate")
        val _tmp_discussionPostCourseBlockUid = _result.getLong("discussionPostCourseBlockUid")
        val _tmp_dpDeleted = _result.getBoolean("dpDeleted")
        val _tmp_discussionPostStartedPersonUid = _result.getLong("discussionPostStartedPersonUid")
        val _tmp_discussionPostClazzUid = _result.getLong("discussionPostClazzUid")
        val _tmp_discussionPostLct = _result.getLong("discussionPostLct")
        val _tmp_discussionPostVisible = _result.getBoolean("discussionPostVisible")
        val _tmp_discussionPostArchive = _result.getBoolean("discussionPostArchive")
        DiscussionPost().apply {
          this.discussionPostUid = _tmp_discussionPostUid
          this.discussionPostReplyToPostUid = _tmp_discussionPostReplyToPostUid
          this.discussionPostTitle = _tmp_discussionPostTitle
          this.discussionPostMessage = _tmp_discussionPostMessage
          this.discussionPostStartDate = _tmp_discussionPostStartDate
          this.discussionPostCourseBlockUid = _tmp_discussionPostCourseBlockUid
          this.dpDeleted = _tmp_dpDeleted
          this.discussionPostStartedPersonUid = _tmp_discussionPostStartedPersonUid
          this.discussionPostClazzUid = _tmp_discussionPostClazzUid
          this.discussionPostLct = _tmp_discussionPostLct
          this.discussionPostVisible = _tmp_discussionPostVisible
          this.discussionPostArchive = _tmp_discussionPostArchive
        }
      }
    }
  }

  override fun getTitleByUidAsFlow(uid: Long): Flow<String?> =
      _db.doorFlow(arrayOf("DiscussionPost")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT DiscussionPost.discussionPostTitle
      |          FROM DiscussionPost
      |         WHERE DiscussionPost.discussionPostUid = ?
      |    
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,uid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(null) {
          _result.getString(1)
        }
      }
    }
  }

  override fun findByPostIdWithAllReplies(postUid: Long, includeDeleted: Boolean):
      PagingSource<Int, DiscussionPostAndPosterNames> = object :
      DoorLimitOffsetPagingSource<DiscussionPostAndPosterNames>(db = _db
  , tableNames = arrayOf("Person", "PersonPicture", "DiscussionPost")
  ) {
    override suspend fun loadRows(_limit: Int, _offset: Int): List<DiscussionPostAndPosterNames> =
        _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT * FROM (
      |        SELECT DiscussionPost.*,
      |               Person.firstNames,
      |               Person.lastName,
      |               PersonPicture.personPictureThumbnailUri AS personPictureUri
      |          FROM DiscussionPost
      |               LEFT JOIN Person
      |                         ON Person.personUid = DiscussionPost.discussionPostStartedPersonUid
      |               LEFT JOIN PersonPicture
      |                         ON PersonPicture.personPictureUid = DiscussionPost.discussionPostStartedPersonUid
      |         WHERE (DiscussionPost.discussionPostUid = ?
      |                 OR DiscussionPost.discussionPostReplyToPostUid= ?)
      |           AND (NOT DiscussionPost.dpDeleted OR CAST(? AS INTEGER) = 1)      
      |            -- Always get the starting post first, followed by replies
      |      ORDER BY CASE(DiscussionPost.discussionPostReplyToPostUid)
      |               WHEN 0 THEN 0
      |               ELSE 1 END ASC,
      |               DiscussionPost.discussionPostStartDate DESC 
      |    ) AS _PagingData LIMIT ? OFFSET ?
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,postUid)
      _stmt.setLong(2,postUid)
      _stmt.setBoolean(3,includeDeleted)
      _stmt.setInt(4,_limit)
      _stmt.setInt(5,_offset)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          val _tmp_firstNames = _result.getString("firstNames")
          val _tmp_lastName = _result.getString("lastName")
          val _tmp_personPictureUri = _result.getString("personPictureUri")
          var _tmp_DiscussionPost_nullCount = 0
          val _tmp_discussionPostUid = _result.getLong("discussionPostUid")
          if(_result.wasNull()) _tmp_DiscussionPost_nullCount++
          val _tmp_discussionPostReplyToPostUid = _result.getLong("discussionPostReplyToPostUid")
          if(_result.wasNull()) _tmp_DiscussionPost_nullCount++
          val _tmp_discussionPostTitle = _result.getString("discussionPostTitle")
          if(_result.wasNull()) _tmp_DiscussionPost_nullCount++
          val _tmp_discussionPostMessage = _result.getString("discussionPostMessage")
          if(_result.wasNull()) _tmp_DiscussionPost_nullCount++
          val _tmp_discussionPostStartDate = _result.getLong("discussionPostStartDate")
          if(_result.wasNull()) _tmp_DiscussionPost_nullCount++
          val _tmp_discussionPostCourseBlockUid = _result.getLong("discussionPostCourseBlockUid")
          if(_result.wasNull()) _tmp_DiscussionPost_nullCount++
          val _tmp_dpDeleted = _result.getBoolean("dpDeleted")
          if(_result.wasNull()) _tmp_DiscussionPost_nullCount++
          val _tmp_discussionPostStartedPersonUid =
              _result.getLong("discussionPostStartedPersonUid")
          if(_result.wasNull()) _tmp_DiscussionPost_nullCount++
          val _tmp_discussionPostClazzUid = _result.getLong("discussionPostClazzUid")
          if(_result.wasNull()) _tmp_DiscussionPost_nullCount++
          val _tmp_discussionPostLct = _result.getLong("discussionPostLct")
          if(_result.wasNull()) _tmp_DiscussionPost_nullCount++
          val _tmp_discussionPostVisible = _result.getBoolean("discussionPostVisible")
          if(_result.wasNull()) _tmp_DiscussionPost_nullCount++
          val _tmp_discussionPostArchive = _result.getBoolean("discussionPostArchive")
          if(_result.wasNull()) _tmp_DiscussionPost_nullCount++
          val _tmp_DiscussionPost_isAllNull = _tmp_DiscussionPost_nullCount == 12
          DiscussionPostAndPosterNames().apply {
            this.firstNames = _tmp_firstNames
            this.lastName = _tmp_lastName
            this.personPictureUri = _tmp_personPictureUri
            if(!_tmp_DiscussionPost_isAllNull) {
              this.discussionPost = DiscussionPost().apply {
                this.discussionPostUid = _tmp_discussionPostUid
                this.discussionPostReplyToPostUid = _tmp_discussionPostReplyToPostUid
                this.discussionPostTitle = _tmp_discussionPostTitle
                this.discussionPostMessage = _tmp_discussionPostMessage
                this.discussionPostStartDate = _tmp_discussionPostStartDate
                this.discussionPostCourseBlockUid = _tmp_discussionPostCourseBlockUid
                this.dpDeleted = _tmp_dpDeleted
                this.discussionPostStartedPersonUid = _tmp_discussionPostStartedPersonUid
                this.discussionPostClazzUid = _tmp_discussionPostClazzUid
                this.discussionPostLct = _tmp_discussionPostLct
                this.discussionPostVisible = _tmp_discussionPostVisible
                this.discussionPostArchive = _tmp_discussionPostArchive
              }
            }
          }
        }
      }
    }

    override suspend fun countRows(): Int = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT COUNT(*) FROM (
      |        SELECT DiscussionPost.*,
      |               Person.firstNames,
      |               Person.lastName,
      |               PersonPicture.personPictureThumbnailUri AS personPictureUri
      |          FROM DiscussionPost
      |               LEFT JOIN Person
      |                         ON Person.personUid = DiscussionPost.discussionPostStartedPersonUid
      |               LEFT JOIN PersonPicture
      |                         ON PersonPicture.personPictureUid = DiscussionPost.discussionPostStartedPersonUid
      |         WHERE (DiscussionPost.discussionPostUid = ?
      |                 OR DiscussionPost.discussionPostReplyToPostUid= ?)
      |           AND (NOT DiscussionPost.dpDeleted OR CAST(? AS INTEGER) = 1)      
      |            -- Always get the starting post first, followed by replies
      |      ORDER BY CASE(DiscussionPost.discussionPostReplyToPostUid)
      |               WHEN 0 THEN 0
      |               ELSE 1 END ASC,
      |               DiscussionPost.discussionPostStartDate DESC 
      |    ) AS _PagingCount
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,postUid)
      _stmt.setLong(2,postUid)
      _stmt.setBoolean(3,includeDeleted)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(0) {
          _result.getInt(1)
        }
      }
    }
  }

  override suspend fun findByPostIdWithAllRepliesPersons(postUid: Long): List<PersonAndPicture> =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT Person.*, PersonPicture.*
    |          FROM Person
    |               LEFT JOIN PersonPicture
    |                         ON PersonPicture.personPictureUid = Person.personUid
    |         WHERE Person.personUid IN
    |               (SELECT DISTINCT DiscussionPost.discussionPostStartedPersonUid
    |                  FROM DiscussionPost
    |                 WHERE DiscussionPost.discussionPostUid = ?
    |                    OR DiscussionPost.discussionPostReplyToPostUid= ?)
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,postUid)
    _stmt.setLong(2,postUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        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
        PersonAndPicture().apply {
          if(!_tmp_Person_isAllNull) {
            this.person = 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.picture = 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 setDeletedAsync(
    uid: Long,
    deleted: Boolean,
    updateTime: Long,
  ) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        UPDATE DiscussionPost
      |           SET dpDeleted = ?,
      |               discussionPostLct = ?
      |         WHERE discussionPostUid = ?   
      |    
      """.trimMargin(),
      readOnly = false,)
    ) { _stmt -> 
      _stmt.setBoolean(1,deleted)
      _stmt.setLong(2,updateTime)
      _stmt.setLong(3,uid)
      _stmt.executeUpdateAsyncKmp()
    }
  }
}
