package com.ustadmobile.core.db.dao

import androidx.room.Dao
import androidx.room.Insert
import androidx.room.Query
import com.ustadmobile.lib.db.composites.EditAndViewPermission
import com.ustadmobile.lib.db.composites.PermissionPair
import com.ustadmobile.lib.db.entities.SystemPermission
import kotlin.Any
import kotlin.Boolean
import kotlin.Long
import kotlin.collections.List
import kotlinx.coroutines.flow.Flow

@Dao
public actual abstract class SystemPermissionDao {
  @Query(`value` =
      "\n        SELECT SystemPermission.*\n          FROM SystemPermission\n         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n           AND (CAST(:includeDeleted AS INTEGER) = 1 OR NOT SystemPermission.spIsDeleted)\n    ")
  public actual abstract suspend fun findAllByPersonUid(accountPersonUid: Long,
      includeDeleted: Boolean): List<SystemPermission>

  @Query(`value` =
      "\n        SELECT SystemPermission.*\n          FROM SystemPermission\n         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n         ")
  public actual abstract suspend fun findAllByPersonUidEntities(accountPersonUid: Long):
      List<SystemPermission>

  @Query(`value` =
      "\n        SELECT SystemPermission.*\n          FROM SystemPermission\n         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n         LIMIT 1\n    ")
  public actual abstract fun findByPersonUidAsFlow(accountPersonUid: Long): Flow<SystemPermission?>

  @Query(`value` =
      "\n        SELECT SystemPermission.*\n          FROM SystemPermission\n         WHERE SystemPermission.spToPersonUid = :accountPersonUid\n         LIMIT 1\n    ")
  public actual abstract suspend fun findByPersonUid(accountPersonUid: Long): SystemPermission

  @Query(`value` =
      "\n        SELECT \n        EXISTS(SELECT 1\n                 FROM SystemPermission\n                WHERE :accountPersonUid != 0 \n                  AND SystemPermission.spToPersonUid = :accountPersonUid\n                  AND (SystemPermission.spPermissionsFlag &\n    \n          :permission\n        \n        ) > 0\n                  AND NOT SystemPermission.spIsDeleted)\n      \n    ")
  public actual abstract suspend fun personHasSystemPermission(accountPersonUid: Long,
      permission: Long): Boolean

  @Query(`value` =
      "\n        SELECT \n        EXISTS(SELECT 1\n                 FROM SystemPermission\n                WHERE :accountPersonUid != 0 \n                  AND SystemPermission.spToPersonUid = :accountPersonUid\n                  AND (SystemPermission.spPermissionsFlag &\n    \n          :permission\n        \n        ) > 0\n                  AND NOT SystemPermission.spIsDeleted)\n      \n    ")
  public actual abstract fun personHasSystemPermissionAsFlow(accountPersonUid: Long,
      permission: Long): Flow<Boolean>

  @Query(`value` =
      "\n        SELECT (\n        EXISTS(SELECT 1\n                 FROM SystemPermission\n                WHERE :accountPersonUid != 0 \n                  AND SystemPermission.spToPersonUid = :accountPersonUid\n                  AND (SystemPermission.spPermissionsFlag &\n    \n                :firstPermission\n                \n        ) > 0\n                  AND NOT SystemPermission.spIsDeleted)\n    ) as firstPermission,\n                (\n        EXISTS(SELECT 1\n                 FROM SystemPermission\n                WHERE :accountPersonUid != 0 \n                  AND SystemPermission.spToPersonUid = :accountPersonUid\n                  AND (SystemPermission.spPermissionsFlag &\n    \n                :secondPermission\n                \n        ) > 0\n                  AND NOT SystemPermission.spIsDeleted)\n    ) as secondPermission\n    ")
  public actual abstract fun personHasSystemPermissionPairAsFlow(
    accountPersonUid: Long,
    firstPermission: Long,
    secondPermission: Long,
  ): Flow<PermissionPair>

  @Query(`value` =
      "\n        SELECT (\n        EXISTS(SELECT 1\n                 FROM SystemPermission\n                WHERE :accountPersonUid != 0 \n                  AND SystemPermission.spToPersonUid = :accountPersonUid\n                  AND (SystemPermission.spPermissionsFlag &\n    \n                :firstPermission\n                \n        ) > 0\n                  AND NOT SystemPermission.spIsDeleted)\n    ) as firstPermission,\n                (\n        EXISTS(SELECT 1\n                 FROM SystemPermission\n                WHERE :accountPersonUid != 0 \n                  AND SystemPermission.spToPersonUid = :accountPersonUid\n                  AND (SystemPermission.spPermissionsFlag &\n    \n                :secondPermission\n                \n        ) > 0\n                  AND NOT SystemPermission.spIsDeleted)\n    ) as secondPermission\n    ")
  public actual abstract suspend fun personHasSystemPermissionPair(
    accountPersonUid: Long,
    firstPermission: Long,
    secondPermission: Long,
  ): PermissionPair

  @Query(`value` =
      "\n        /* Similar to the query used on list all persons, however it uses the the otherpersonuid param \n         * in the WHERE clauses to narrow down the search. \n         */\n        WITH CanViewPersonUidsViaCoursePermission(personUid) AS\n              /* Select personUids that can be viewed based on CoursePermission given to the active\n               * user for their enrolment role in that course\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                              AND ClazzEnrolment_ForClazzMember.clazzEnrolmentPersonUid = :otherPersonUid\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                              AND ClazzEnrolment_ForClazzMember.clazzEnrolmentPersonUid = :otherPersonUid\n                WHERE :accountPersonUid != 0\n                  AND CoursePermission.cpToPersonUid = :accountPersonUid)\n                  \n        SELECT (    (SELECT :accountPersonUid = :otherPersonUid)\n                 OR EXISTS(SELECT 1\n                             FROM PersonParentJoin\n                            WHERE PersonParentJoin.ppjMinorPersonUid = :otherPersonUid\n                              AND PersonParentJoin.ppjParentPersonUid = :accountPersonUid)\n                 OR (SELECT \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 (SELECT :otherPersonUid IN \n                             (SELECT CanViewPersonUidsViaCoursePermission.personUid\n                                 FROM CanViewPersonUidsViaCoursePermission))           \n               ) AS hasViewPermission,\n               \n               (   (SELECT :accountPersonUid = :otherPersonUid)\n                OR EXISTS(SELECT 1\n                             FROM PersonParentJoin\n                            WHERE PersonParentJoin.ppjMinorPersonUid = :otherPersonUid\n                              AND PersonParentJoin.ppjParentPersonUid = :accountPersonUid)\n                OR (SELECT \n        EXISTS(SELECT 1\n                 FROM SystemPermission\n                WHERE :accountPersonUid != 0 \n                  AND SystemPermission.spToPersonUid = :accountPersonUid\n                  AND (SystemPermission.spPermissionsFlag &\n     \n                            32768\n                            \n        ) > 0\n                  AND NOT SystemPermission.spIsDeleted)\n    )\n                ) AS hasEditPermission\n    ")
  public actual abstract fun personHasEditAndViewPermissionForPersonAsFlow(accountPersonUid: Long,
      otherPersonUid: Long): Flow<EditAndViewPermission>

  @Insert(
    onConflict = 1,
    entity = Any::class,
  )
  public actual abstract suspend fun upsertAsync(systemPermissions: SystemPermission)
}
