package com.ustadmobile.core.db.dao

import androidx.room.Dao
import androidx.room.Insert
import androidx.room.Query
import androidx.room.Update
import com.ustadmobile.lib.db.entities.ParentEnrolmentRequired
import com.ustadmobile.lib.db.entities.PersonParentJoin
import com.ustadmobile.lib.db.entities.PersonParentJoinAndMinorPerson
import kotlin.Any
import kotlin.Boolean
import kotlin.Long
import kotlin.collections.List

@Dao
public actual abstract class PersonParentJoinDao {
  @Insert(
    onConflict = 1,
    entity = Any::class,
  )
  public actual abstract suspend fun upsertAsync(entity: PersonParentJoin): Long

  @Query(`value` =
      "\n        SELECT PersonParentJoin.*, Person.*\n          FROM PersonParentJoin\n     LEFT JOIN Person ON Person.personUid = PersonParentJoin.ppjMinorPersonUid    \n         WHERE PersonParentJoin.ppjUid = :uid\n    ")
  public actual abstract suspend fun findByUidWithMinorAsync(uid: Long):
      PersonParentJoinAndMinorPerson?

  @Query(`value` =
      "\n        SELECT PersonParentJoin.*, Person.*\n          FROM PersonParentJoin\n     LEFT JOIN Person ON Person.personUid = PersonParentJoin.ppjMinorPersonUid    \n         WHERE PersonParentJoin.ppjUid = :uid\n    ")
  public actual abstract suspend fun findByUidWithMinorAsyncFromWeb(uid: Long):
      PersonParentJoinAndMinorPerson?

  @Query(`value` =
      "\n        SELECT PersonParentJoin.*\n          FROM PersonParentJoin\n         WHERE ppjMinorPersonUid = :minorPersonUid \n    ")
  public actual abstract suspend fun findByMinorPersonUid(minorPersonUid: Long):
      List<PersonParentJoin>

  @Query(`value` =
      "\n        SELECT PersonParentJoin.ppjParentPersonUid AS parentPersonUid,\n               ChildEnrolment.clazzEnrolmentClazzUid AS clazzUid\n          FROM PersonParentJoin\n               JOIN ClazzEnrolment ChildEnrolment \n                    ON ChildEnrolment.clazzEnrolmentPersonUid = :minorPersonUid\n                   AND (:clazzUidFilter = 0 OR ChildEnrolment.clazzEnrolmentClazzUid = :clazzUidFilter)\n         WHERE PersonParentJoin.ppjMinorPersonUid = :minorPersonUid\n           AND PersonParentJoin.ppjParentPersonUid != 0\n           AND NOT EXISTS(\n               SELECT clazzEnrolmentUid \n                 FROM ClazzEnrolment\n                WHERE ClazzEnrolment.clazzEnrolmentPersonUid = PersonParentJoin.ppjParentPersonUid\n                  AND ClazzEnrolment.clazzEnrolmentClazzUid = ChildEnrolment.clazzEnrolmentClazzUid\n                  AND ClazzEnrolment.clazzEnrolmentRole = 1003\n                  AND CAST(ClazzEnrolment.clazzEnrolmentActive AS INTEGER) = 1)\n    ")
  public actual abstract suspend
      fun findByMinorPersonUidWhereParentNotEnrolledInClazz(minorPersonUid: Long,
      clazzUidFilter: Long): List<ParentEnrolmentRequired>

  @Query(`value` =
      "\n        SELECT EXISTS(\n               SELECT ppjUid\n                 FROM PersonParentJoin\n                WHERE ppjMinorPersonUid = :minorPersonUid\n                      AND ppjParentPersonUid = :userPersonUid\n                      AND CAST(ppjInactive AS INTEGER) = 0)\n    ")
  public actual abstract suspend fun isParentOf(userPersonUid: Long, minorPersonUid: Long): Boolean

  @Update(
    entity = Any::class,
    onConflict = 3,
  )
  public actual abstract suspend fun updateAsync(personParentJoin: PersonParentJoin)

  @Query(`value` =
      "\n        SELECT EXISTS(\n               SELECT ppjUid\n                 FROM PersonParentJoin\n                WHERE ppjMinorPersonUid = :minorPersonUid\n                  AND CAST(ppjInactive AS INTEGER) = 0\n                  AND ppjStatus = 1)\n    ")
  public actual abstract suspend fun isMinorApproved(minorPersonUid: Long): Boolean
}
