package com.ustadmobile.core.db.dao

import androidx.lifecycle.LiveData
import androidx.paging.DataSource
import androidx.room.Dao
import androidx.room.Insert
import androidx.room.Query
import androidx.room.Update
import com.ustadmobile.lib.db.entities.AccessToken
import com.ustadmobile.lib.db.entities.AuditLog
import com.ustadmobile.lib.db.entities.Person
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 com.ustadmobile.lib.db.entities.PersonWithAccount
import com.ustadmobile.lib.db.entities.PersonWithDisplayDetails
import com.ustadmobile.lib.db.entities.PersonWithPersonParentJoin
import kotlin.Boolean
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.Unit
import kotlin.collections.List

@Dao
public actual abstract class PersonDao : BaseDao<Person> {
  @Query(`value` =
      "\n     REPLACE INTO PersonReplicate(personPk, personDestination)\n      SELECT DISTINCT Person.personUid AS personUid,\n             :newNodeId AS personDestination\n        FROM UserSession\n             JOIN PersonGroupMember\n                ON UserSession.usPersonUid = PersonGroupMember.groupMemberPersonUid\n                   \n            JOIN ScopedGrant\n                 ON ScopedGrant.sgGroupUid = PersonGroupMember.groupMemberGroupUid\n                    AND (ScopedGrant.sgPermissions &\n                   64\n                   \n                                                    ) > 0\n            JOIN Person \n                 ON \n                ((ScopedGrant.sgTableId = -2\n                    AND ScopedGrant.sgEntityUid = -2)\n                 OR (ScopedGrant.sgTableId = 9\n                    AND ScopedGrant.sgEntityUid = Person.personUid)\n                 OR (ScopedGrant.sgTableId = 6       \n                    AND Person.personUid IN (\n                        SELECT DISTINCT clazzEnrolmentPersonUid\n                          FROM ClazzEnrolment\n                         WHERE clazzEnrolmentClazzUid =ScopedGrant.sgEntityUid \n                           AND ClazzEnrolment.clazzEnrolmentActive))\n                 OR (ScopedGrant.sgTableId = 164\n                    AND Person.personUid IN (\n                        SELECT DISTINCT schoolMemberPersonUid\n                          FROM SchoolMember\n                         WHERE schoolMemberSchoolUid = ScopedGrant.sgEntityUid\n                           AND schoolMemberActive))\n                           )    \n        \n        \n       WHERE Person.personType = 0\n         AND UserSession.usClientNodeId = :newNodeId\n         AND Person.personLct != COALESCE(\n             (SELECT personVersionId\n                FROM PersonReplicate\n               WHERE personPk = Person.personUid\n                 AND personDestination = :newNodeId), 0)              \n      /*psql ON CONFLICT(personPk, personDestination) DO UPDATE\n             SET personPending = true\n      */       \n    ")
  public actual abstract suspend fun replicateOnNewNode(newNodeId: Long): Unit

  @Query(`value` =
      "\n REPLACE INTO PersonReplicate(personPk, personDestination)\n  SELECT DISTINCT Person.personUid AS personUid,\n         UserSession.usClientNodeId AS personDestination\n    FROM ChangeLog\n         JOIN Person\n             ON ChangeLog.chTableId = 9\n                AND ChangeLog.chEntityPk = Person.personUid\n         \n            JOIN ScopedGrant \n                   ON \n            ((ScopedGrant.sgTableId = -2\n                    AND ScopedGrant.sgEntityUid = -2)\n                 OR (ScopedGrant.sgTableId = 9\n                    AND ScopedGrant.sgEntityUid = Person.personUid)\n                 OR (ScopedGrant.sgTableId = 6       \n                    AND ScopedGrant.sgEntityUid IN (\n                        SELECT DISTINCT clazzEnrolmentClazzUid\n                          FROM ClazzEnrolment\n                         WHERE clazzEnrolmentPersonUid = Person.personUid \n                           AND ClazzEnrolment.clazzEnrolmentActive))\n                 OR (ScopedGrant.sgTableId = 164\n                    AND ScopedGrant.sgEntityUid IN (\n                        SELECT DISTINCT schoolMemberSchoolUid\n                          FROM SchoolMember\n                         WHERE schoolMemberPersonUid = Person.personUid\n                           AND schoolMemberActive))\n                           )\n        \n                   AND (ScopedGrant.sgPermissions & \n        \n            64\n            \n                                                     ) > 0\n             JOIN PersonGroupMember AS PrsGrpMbr\n                   ON ScopedGrant.sgGroupUid = PrsGrpMbr.groupMemberGroupUid\n              JOIN UserSession\n                   ON UserSession.usPersonUid = PrsGrpMbr.groupMemberPersonUid\n                      AND UserSession.usStatus = 1\n        \n   WHERE Person.personType = 0\n     AND UserSession.usClientNodeId != (\n         SELECT nodeClientId \n           FROM SyncNode\n          LIMIT 1)\n     AND Person.personLct != COALESCE(\n         (SELECT personVersionId\n            FROM PersonReplicate\n           WHERE personPk = Person.personUid\n             AND personDestination = UserSession.usClientNodeId), 0)\n /*psql ON CONFLICT(personPk, personDestination) DO UPDATE\n     SET personPending = true\n  */               \n ")
  public actual abstract suspend fun replicateOnChange(): Unit

  @Insert(onConflict = 3)
  public actual abstract suspend fun insertListAsync(entityList: List<Person>): Unit

  @Insert(onConflict = 1)
  public actual abstract suspend fun insertOrReplace(person: Person): Unit

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

  @Query(`value` =
      "SELECT EXISTS(SELECT token FROM AccessToken WHERE token = :token  and accessTokenPersonUid = :personUid)")
  public actual abstract fun isValidToken(token: String, personUid: Long): Boolean

  @Insert(onConflict = 3)
  public actual abstract fun insertAccessToken(token: AccessToken): Unit

  @Query(`value` =
      "\n        SELECT Person.personUid, Person.admin, 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(onConflict = 3)
  public actual abstract fun insertPersonAuth(personAuth: PersonAuth): Unit

  @Query(`value` =
      "\n        SELECT EXISTS(\n                SELECT 1\n                  FROM Person\n                  JOIN ScopedGrant\n                       ON \n                ((ScopedGrant.sgTableId = -2\n                    AND ScopedGrant.sgEntityUid = -2)\n                 OR (ScopedGrant.sgTableId = 9\n                    AND ScopedGrant.sgEntityUid = Person.personUid)\n                 OR (ScopedGrant.sgTableId = 6       \n                    AND Person.personUid IN (\n                        SELECT DISTINCT clazzEnrolmentPersonUid\n                          FROM ClazzEnrolment\n                         WHERE clazzEnrolmentClazzUid =ScopedGrant.sgEntityUid \n                           AND ClazzEnrolment.clazzEnrolmentActive))\n                 OR (ScopedGrant.sgTableId = 164\n                    AND Person.personUid IN (\n                        SELECT DISTINCT schoolMemberPersonUid\n                          FROM SchoolMember\n                         WHERE schoolMemberSchoolUid = ScopedGrant.sgEntityUid\n                           AND schoolMemberActive))\n                           )    \n        \n                  JOIN PersonGroupMember \n                       ON ScopedGrant.sgGroupUid = PersonGroupMember.groupMemberGroupUid\n                 WHERE Person.personUid = :personUid\n                   AND (ScopedGrant.sgPermissions & :permission) > 0\n                   AND PersonGroupMember.groupMemberPersonUid = :accountPersonUid\n                 LIMIT 1)\n    ")
  public actual abstract suspend fun personHasPermissionAsync(
    accountPersonUid: Long,
    personUid: Long,
    permission: Long,
  ): Boolean

  @Query(`value` =
      "SELECT COALESCE((SELECT admin FROM Person WHERE personUid = :accountPersonUid), 0)")
  public actual abstract suspend fun personIsAdmin(accountPersonUid: Long): Boolean

  @Query(`value` = "SELECT Person.* FROM PERSON Where Person.username = :username")
  public actual abstract fun findByUsername(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` =
      "SELECT Person.*, null as newPassword, null as currentPassword,null as confirmedPassword FROM PERSON WHERE Person.personUid = :uid")
  public actual abstract suspend fun findPersonAccountByUid(uid: Long): PersonWithAccount?

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

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

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

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

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

  @Query(`value` =
      "\n         SELECT Person.* \n           FROM PersonGroupMember \n                \n            JOIN ScopedGrant\n                 ON ScopedGrant.sgGroupUid = PersonGroupMember.groupMemberGroupUid\n                    AND (ScopedGrant.sgPermissions &\n                    64\n                    \n                                                    ) > 0\n            JOIN Person \n                 ON \n                ((ScopedGrant.sgTableId = -2\n                    AND ScopedGrant.sgEntityUid = -2)\n                 OR (ScopedGrant.sgTableId = 9\n                    AND ScopedGrant.sgEntityUid = Person.personUid)\n                 OR (ScopedGrant.sgTableId = 6       \n                    AND Person.personUid IN (\n                        SELECT DISTINCT clazzEnrolmentPersonUid\n                          FROM ClazzEnrolment\n                         WHERE clazzEnrolmentClazzUid =ScopedGrant.sgEntityUid \n                           AND ClazzEnrolment.clazzEnrolmentActive))\n                 OR (ScopedGrant.sgTableId = 164\n                    AND Person.personUid IN (\n                        SELECT DISTINCT schoolMemberPersonUid\n                          FROM SchoolMember\n                         WHERE schoolMemberSchoolUid = ScopedGrant.sgEntityUid\n                           AND schoolMemberActive))\n                           )    \n        \n        \n         WHERE PersonGroupMember.groupMemberPersonUid = :accountPersonUid\n           AND PersonGroupMember.groupMemberActive \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 (:excludeSchool = 0 OR :excludeSchool NOT IN\n                    (SELECT schoolMemberSchoolUid\n                      FROM SchoolMember \n                     WHERE schoolMemberPersonUid = Person.personUid \n                       AND :timestamp BETWEEN SchoolMember.schoolMemberJoinDate\n                            AND SchoolMember.schoolMemberLeftDate ))\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\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 findPersonsWithPermission(
    timestamp: Long,
    excludeClazz: Long,
    excludeSchool: Long,
    excludeSelected: List<Long>,
    accountPersonUid: Long,
    sortOrder: Int,
    searchText: String?,
  ): DataSource.Factory<Int, PersonWithDisplayDetails>

  @Query(`value` =
      "\n         SELECT Person.* \n           FROM PersonGroupMember \n                \n            JOIN ScopedGrant\n                 ON ScopedGrant.sgGroupUid = PersonGroupMember.groupMemberGroupUid\n                    AND (ScopedGrant.sgPermissions &\n                    64\n                    \n                                                    ) > 0\n            JOIN Person \n                 ON \n                ((ScopedGrant.sgTableId = -2\n                    AND ScopedGrant.sgEntityUid = -2)\n                 OR (ScopedGrant.sgTableId = 9\n                    AND ScopedGrant.sgEntityUid = Person.personUid)\n                 OR (ScopedGrant.sgTableId = 6       \n                    AND Person.personUid IN (\n                        SELECT DISTINCT clazzEnrolmentPersonUid\n                          FROM ClazzEnrolment\n                         WHERE clazzEnrolmentClazzUid =ScopedGrant.sgEntityUid \n                           AND ClazzEnrolment.clazzEnrolmentActive))\n                 OR (ScopedGrant.sgTableId = 164\n                    AND Person.personUid IN (\n                        SELECT DISTINCT schoolMemberPersonUid\n                          FROM SchoolMember\n                         WHERE schoolMemberSchoolUid = ScopedGrant.sgEntityUid\n                           AND schoolMemberActive))\n                           )    \n        \n        \n         WHERE PersonGroupMember.groupMemberPersonUid = :accountPersonUid\n           AND PersonGroupMember.groupMemberActive \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 (:excludeSchool = 0 OR :excludeSchool NOT IN\n                    (SELECT schoolMemberSchoolUid\n                      FROM SchoolMember \n                     WHERE schoolMemberPersonUid = Person.personUid \n                       AND :timestamp BETWEEN SchoolMember.schoolMemberJoinDate\n                            AND SchoolMember.schoolMemberLeftDate ))\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\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,
    excludeSchool: Long,
    excludeSelected: List<Long>,
    accountPersonUid: Long,
    sortOrder: Int,
    searchText: String?,
  ): List<Person>

  @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): LiveData<PersonWithPersonParentJoin?>

  @Insert(onConflict = 3)
  public actual abstract fun insertAuditLog(entity: AuditLog): Long

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