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.PersonParentJoinWithMinorPerson
import kotlin.Boolean
import kotlin.Long
import kotlin.Unit
import kotlin.collections.List

@Dao
public actual abstract class PersonParentJoinDao {
  @Query(`value` =
      "\n     REPLACE INTO PersonParentJoinReplicate(ppjPk, ppjDestination)\n      SELECT DISTINCT PersonParentJoin.ppjUid AS ppjPk,\n             :newNodeId AS ppjDestination\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             JOIN PersonParentJoin\n                  ON PersonParentJoin.ppjParentPersonUid = Person.personUid       \n       WHERE UserSession.usStatus = 1\n         AND PersonParentJoin.ppjLct != COALESCE(\n             (SELECT ppjVersionId\n                FROM PersonParentJoinReplicate\n               WHERE ppjPk = PersonParentJoin.ppjUid\n                 AND ppjDestination = :newNodeId), 0) \n      /*psql ON CONFLICT(ppjPk, ppjDestination) DO UPDATE\n             SET ppjPending = true\n      */       \n    ")
  public actual abstract suspend fun replicateOnNewNode(newNodeId: Long): Unit

  @Query(`value` =
      "\n REPLACE INTO PersonParentJoinReplicate(ppjPk, ppjDestination)\n  SELECT DISTINCT PersonParentJoin.ppjUid AS ppjUid,\n         UserSession.usClientNodeId AS ppjDestination\n    FROM ChangeLog\n         JOIN PersonParentJoin\n             ON ChangeLog.chTableId = 512\n                AND ChangeLog.chEntityPk = PersonParentJoin.ppjUid\n         JOIN Person\n              ON PersonParentJoin.ppjParentPersonUid = 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 UserSession.usClientNodeId != (\n         SELECT nodeClientId\n           FROM SyncNode\n          LIMIT 1)\n     AND PersonParentJoin.ppjLct != COALESCE(\n         (SELECT ppjVersionId\n            FROM PersonParentJoinReplicate\n           WHERE ppjPk = PersonParentJoin.ppjUid\n             AND ppjDestination = UserSession.usClientNodeId), 0)\n /*psql ON CONFLICT(ppjPk, ppjDestination) DO UPDATE\n     SET ppjPending = true\n  */\n    ")
  public actual abstract suspend fun replicateOnChange(): Unit

  @Insert(onConflict = 3)
  public actual abstract suspend fun insertAsync(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):
      PersonParentJoinWithMinorPerson?

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

  @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(onConflict = 3)
  public actual abstract suspend fun updateAsync(personParentJoin: PersonParentJoin): Unit

  @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
}
