package com.ustadmobile.core.db.dao

import androidx.paging.PagingSource
import androidx.room.Dao
import androidx.room.Insert
import androidx.room.Query
import androidx.room.Update
import com.ustadmobile.lib.db.composites.PersonAndListDisplayDetails
import com.ustadmobile.lib.db.composites.PersonAndPicture
import com.ustadmobile.lib.db.composites.PersonNames
import com.ustadmobile.lib.db.entities.Person
import com.ustadmobile.lib.db.entities.PersonAndDisplayDetail
import com.ustadmobile.lib.db.entities.PersonAuth
import com.ustadmobile.lib.db.entities.PersonGroup
import com.ustadmobile.lib.db.entities.PersonGroupMember
import com.ustadmobile.lib.db.entities.PersonUidAndPasswordHash
import kotlin.Any
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.collections.List
import kotlinx.coroutines.flow.Flow

@Dao
public actual abstract class PersonDao : BaseDao<Person> {
  @Insert(
    entity = Any::class,
    onConflict = 3,
  )
  public actual abstract suspend fun insertListAsync(entityList: List<Person>)

  @Insert(
    onConflict = 1,
    entity = Any::class,
  )
  public actual abstract suspend fun insertOrReplace(person: Person)

  @Query(`value` = "SELECT COUNT(*) FROM Person where Person.username = :username")
  public actual abstract suspend fun countUsername(username: String): Int

  @Query(`value` =
      "\n        SELECT Person.personUid, Person.firstNames, Person.lastName, \n               PersonAuth.passwordHash\n          FROM Person\n               JOIN PersonAuth\n                    ON Person.personUid = PersonAuth.personAuthUid\n         WHERE Person.username = :username\n    ")
  public actual abstract suspend fun findUidAndPasswordHashAsync(username: String):
      PersonUidAndPasswordHash?

  @Query(`value` =
      "\n        SELECT Person.*\n          FROM Person\n               JOIN PersonAuth2\n                    ON Person.personUid = PersonAuth2.pauthUid\n         WHERE Person.username = :username \n               AND PersonAuth2.pauthAuth = :passwordHash\n    ")
  public actual abstract suspend fun findByUsernameAndPasswordHash2(username: String,
      passwordHash: String): Person?

  @Insert(
    entity = Any::class,
    onConflict = 3,
  )
  public actual abstract fun insertPersonAuth(personAuth: PersonAuth)

  @Query(`value` = "SELECT Person.* FROM PERSON Where Person.username = :username")
  public actual abstract fun findByUsername(username: String?): Person?

  @Query(`value` = "SELECT Person.* FROM PERSON Where Person.username = :username")
  public actual abstract suspend fun findByUsernameAsync(username: String): Person?

  @Query(`value` =
      "\n        SELECT Person.*\n          FROM Person\n         WHERE Person.dateOfBirth = :nodeId\n           AND Person.personType = 1\n    ")
  public actual abstract suspend fun findSystemAccount(nodeId: Long): Person?

  @Query(`value` = "SELECT * FROM PERSON WHERE Person.personUid = :uid")
  public actual abstract fun findByUid(uid: Long): Person?

  @Query(`value` =
      "\n        SELECT Person.*, PersonPicture.*\n          FROM Person\n               LEFT JOIN PersonPicture\n                    ON PersonPicture.personPictureUid = Person.personUid\n         WHERE Person.personUid = :accountPersonUid           \n    ")
  public actual abstract suspend fun findByUidWithPicture(accountPersonUid: Long): PersonAndPicture?

  @Query(`value` =
      "\n        SELECT Person.*, PersonPicture.*\n          FROM Person\n               LEFT JOIN PersonPicture\n                    ON PersonPicture.personPictureUid = Person.personUid\n         WHERE Person.personUid = :uid           \n    ")
  public actual abstract fun findByUidWithPictureAsFlow(uid: Long): Flow<PersonAndPicture?>

  @Query(`value` = "SELECT * From Person WHERE personUid = :uid")
  public actual abstract fun findByUidLive(uid: Long): Flow<Person?>

  @Query(`value` = "SELECT * FROM Person WHERE personUid = :uid")
  public actual abstract suspend fun findByUidAsync(uid: Long): Person?

  @Query(`value` = "SELECT * FROM Person WHERE personUid = :uid")
  public actual abstract fun findByUidAsFlow(uid: Long): Flow<Person?>

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

  @Insert(
    entity = Any::class,
    onConflict = 3,
  )
  public actual abstract suspend fun insertPersonGroup(personGroup: PersonGroup): Long

  @Insert(
    entity = Any::class,
    onConflict = 3,
  )
  public actual abstract suspend fun insertPersonGroupMember(personGroupMember: PersonGroupMember):
      Long

  @Query(`value` =
      "\n         WITH CanViewPersonUidsViaCoursePermission(personUid) AS\n              /* Select personUids that can be viewed based on CoursePermission given the active user \n                 for their enrolments \n              */\n              (SELECT DISTINCT ClazzEnrolment_ForClazzMember.clazzEnrolmentPersonUid AS personUid\n                 FROM ClazzEnrolment ClazzEnrolment_ForActiveUser\n                      JOIN CoursePermission \n                           ON CoursePermission.cpClazzUid = ClazzEnrolment_ForActiveUser.clazzEnrolmentClazzUid\n                          AND CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForActiveUser.clazzEnrolmentRole\n                          AND (CoursePermission.cpPermissionsFlag & 8192) > 0\n                      JOIN ClazzEnrolment ClazzEnrolment_ForClazzMember\n                           ON ClazzEnrolment_ForClazzMember.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid\n                WHERE :accountPersonUid != 0\n                  AND ClazzEnrolment_ForActiveUser.clazzEnrolmentPersonUid = :accountPersonUid\n                  AND ClazzEnrolment_ForActiveUser.clazzEnrolmentActive\n              \n               UNION\n               /* Select personUids that can be viewed based on CoursePermission for the active user\n                  where the CoursePermission is granted directly to them\n                */   \n               SELECT DISTINCT ClazzEnrolment_ForClazzMember.clazzEnrolmentPersonUid AS personUid\n                 FROM CoursePermission\n                      JOIN ClazzEnrolment ClazzEnrolment_ForClazzMember\n                           ON ClazzEnrolment_ForClazzMember.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid\n                WHERE :accountPersonUid != 0\n                  AND CoursePermission.cpToPersonUid = :accountPersonUid)\n               \n         SELECT Person.*, PersonPicture.*\n           FROM Person\n                LEFT JOIN PersonPicture\n                     ON PersonPicture.personPictureUid = Person.personUid\n          WHERE /* Begin permission check */ \n                (         \n                      (\n        EXISTS(SELECT 1\n                 FROM SystemPermission\n                WHERE :accountPersonUid != 0 \n                  AND SystemPermission.spToPersonUid = :accountPersonUid\n                  AND (SystemPermission.spPermissionsFlag &\n    \n                       8192\n                       \n        ) > 0\n                  AND NOT SystemPermission.spIsDeleted)\n    )\n                    OR (Person.personUid IN \n                               (SELECT CanViewPersonUidsViaCoursePermission.personUid\n                                  FROM CanViewPersonUidsViaCoursePermission))\n                    OR (Person.personUid = :accountPersonUid)\n                )\n                /* End permission check */\n           AND (:excludeClazz = 0 OR :excludeClazz NOT IN\n                    (SELECT clazzEnrolmentClazzUid \n                       FROM ClazzEnrolment \n                      WHERE clazzEnrolmentPersonUid = Person.personUid \n                            AND :timestamp BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined \n                                AND ClazzEnrolment.clazzEnrolmentDateLeft\n                        AND ClazzEnrolment.clazzEnrolmentActive))\n           AND Person.personType = 0                  \n           AND (Person.personUid NOT IN (:excludeSelected))\n           AND (:searchText = '%' \n               OR Person.firstNames || ' ' || Person.lastName LIKE :searchText)\n      GROUP BY Person.personUid, PersonPicture.personPictureUid\n      ORDER BY CASE(:sortOrder)\n               WHEN 1 THEN Person.firstNames\n               WHEN 3 THEN Person.lastName\n               ELSE ''\n               END ASC,\n               CASE(:sortOrder)\n               WHEN 2 THEN Person.firstNames\n               WHEN 4 THEN Person.lastName\n               ELSE ''\n               END DESC\n    ")
  public actual abstract fun findPersonsWithPermissionAsList(
    timestamp: Long,
    excludeClazz: Long,
    excludeSelected: List<Long>,
    accountPersonUid: Long,
    sortOrder: Int,
    searchText: String?,
  ): List<PersonAndListDisplayDetails>

  @Query(`value` =
      "\n         WITH CanViewPersonUidsViaCoursePermission(personUid) AS\n              /* Select personUids that can be viewed based on CoursePermission given the active user \n                 for their enrolments \n              */\n              (SELECT DISTINCT ClazzEnrolment_ForClazzMember.clazzEnrolmentPersonUid AS personUid\n                 FROM ClazzEnrolment ClazzEnrolment_ForActiveUser\n                      JOIN CoursePermission \n                           ON CoursePermission.cpClazzUid = ClazzEnrolment_ForActiveUser.clazzEnrolmentClazzUid\n                          AND CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForActiveUser.clazzEnrolmentRole\n                          AND (CoursePermission.cpPermissionsFlag & 8192) > 0\n                      JOIN ClazzEnrolment ClazzEnrolment_ForClazzMember\n                           ON ClazzEnrolment_ForClazzMember.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid\n                WHERE :accountPersonUid != 0\n                  AND ClazzEnrolment_ForActiveUser.clazzEnrolmentPersonUid = :accountPersonUid\n                  AND ClazzEnrolment_ForActiveUser.clazzEnrolmentActive\n              \n               UNION\n               /* Select personUids that can be viewed based on CoursePermission for the active user\n                  where the CoursePermission is granted directly to them\n                */   \n               SELECT DISTINCT ClazzEnrolment_ForClazzMember.clazzEnrolmentPersonUid AS personUid\n                 FROM CoursePermission\n                      JOIN ClazzEnrolment ClazzEnrolment_ForClazzMember\n                           ON ClazzEnrolment_ForClazzMember.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid\n                WHERE :accountPersonUid != 0\n                  AND CoursePermission.cpToPersonUid = :accountPersonUid)\n               \n         SELECT Person.*, PersonPicture.*\n           FROM Person\n                LEFT JOIN PersonPicture\n                     ON PersonPicture.personPictureUid = Person.personUid\n          WHERE /* Begin permission check */ \n                (         \n                      (\n        EXISTS(SELECT 1\n                 FROM SystemPermission\n                WHERE :accountPersonUid != 0 \n                  AND SystemPermission.spToPersonUid = :accountPersonUid\n                  AND (SystemPermission.spPermissionsFlag &\n    \n                       8192\n                       \n        ) > 0\n                  AND NOT SystemPermission.spIsDeleted)\n    )\n                    OR (Person.personUid IN \n                               (SELECT CanViewPersonUidsViaCoursePermission.personUid\n                                  FROM CanViewPersonUidsViaCoursePermission))\n                    OR (Person.personUid = :accountPersonUid)\n                )\n                /* End permission check */\n           AND (:excludeClazz = 0 OR :excludeClazz NOT IN\n                    (SELECT clazzEnrolmentClazzUid \n                       FROM ClazzEnrolment \n                      WHERE clazzEnrolmentPersonUid = Person.personUid \n                            AND :timestamp BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined \n                                AND ClazzEnrolment.clazzEnrolmentDateLeft\n                        AND ClazzEnrolment.clazzEnrolmentActive))\n           AND Person.personType = 0                  \n           AND (Person.personUid NOT IN (:excludeSelected))\n           AND (:searchText = '%' \n               OR Person.firstNames || ' ' || Person.lastName LIKE :searchText)\n      GROUP BY Person.personUid, PersonPicture.personPictureUid\n      ORDER BY CASE(:sortOrder)\n               WHEN 1 THEN Person.firstNames\n               WHEN 3 THEN Person.lastName\n               ELSE ''\n               END ASC,\n               CASE(:sortOrder)\n               WHEN 2 THEN Person.firstNames\n               WHEN 4 THEN Person.lastName\n               ELSE ''\n               END DESC\n    ")
  public actual abstract fun findPersonsWithPermissionAsPagingSource(
    timestamp: Long,
    excludeClazz: Long,
    excludeSelected: List<Long>,
    accountPersonUid: Long,
    sortOrder: Int,
    searchText: String?,
  ): PagingSource<Int, PersonAndListDisplayDetails>

  @Query(`value` =
      "\n        SELECT Person.*, PersonParentJoin.* \n          FROM Person\n     LEFT JOIN PersonParentJoin on ppjUid = (\n                SELECT ppjUid \n                  FROM PersonParentJoin\n                 WHERE ppjMinorPersonUid = :personUid \n                       AND ppjParentPersonUid = :activeUserPersonUid \n                LIMIT 1)     \n         WHERE Person.personUid = :personUid\n        ")
  public actual abstract fun findByUidWithDisplayDetailsLive(personUid: Long,
      activeUserPersonUid: Long): Flow<PersonAndDisplayDetail?>

  @Query(`value` =
      "\n        SELECT Person.*, PersonParentJoin.* , PersonPicture.*, TransferJobItem.*\n          FROM Person\n               LEFT JOIN PersonParentJoin \n                    ON ppjUid =\n                    (SELECT ppjUid \n                       FROM PersonParentJoin\n                      WHERE ppjMinorPersonUid = :personUid \n                        AND ppjParentPersonUid = :accountPersonUid \n                      LIMIT 1)  \n               LEFT JOIN PersonPicture\n                    ON PersonPicture.personPictureUid = :personUid\n               LEFT JOIN TransferJobItem\n                    ON TransferJobItem.tjiUid = \n                       (SELECT TransferJobItem.tjiUid\n                          FROM TransferJobItem\n                         WHERE TransferJobItem.tjiEntityUid = :personUid\n                           AND TransferJobItem.tjiTableId = 50\n                           AND TransferJobItem.tjiEntityEtag = PersonPicture.personPictureLct\n                           AND TransferJobItem.tjiStatus != 21\n                         LIMIT 1)\n                          \n         WHERE Person.personUid = :personUid\n        ")
  public actual abstract fun findByUidWithDisplayDetailsFlow(personUid: Long,
      accountPersonUid: Long): Flow<PersonAndDisplayDetail?>

  @Query(`value` = "SELECT * FROM Person")
  public actual abstract fun getAllPerson(): List<Person>

  @Query(`value` =
      "\n        SELECT Person.firstNames, Person.lastName\n          FROM Person\n         WHERE Person.personUid = :uid  \n    ")
  public actual abstract fun getNamesByUid(uid: Long): Flow<PersonNames?>

  @Query(`value` =
      "\n        SELECT Person.firstNames, Person.lastName\n          FROM Person\n         WHERE Person.personUid = :uid  \n    ")
  public actual abstract suspend fun getNamesByUidAsync(uid: Long): PersonNames?

  @Query(`value` =
      "\n        UPDATE Person\n           SET username = :username,\n               personLct = :currentTime\n         WHERE Person.personUid = :personUid  \n    ")
  public actual abstract suspend fun updateUsername(
    personUid: Long,
    username: String,
    currentTime: Long,
  ): Int

  @Query(`value` =
      "\n        SELECT Person.username\n          FROM Person\n         WHERE Person.username IN (:usernames)\n    ")
  public actual abstract suspend fun selectExistingUsernames(usernames: List<String>): List<String?>
}
