package com.ustadmobile.core.db.dao

import androidx.paging.DataSource
import androidx.room.Dao
import androidx.room.Query
import androidx.room.Update
import com.ustadmobile.lib.db.entities.School
import com.ustadmobile.lib.db.entities.SchoolWithHolidayCalendar
import com.ustadmobile.lib.db.entities.SchoolWithMemberCountAndLocation
import kotlin.Boolean
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.Unit

@Dao
public actual abstract class SchoolDao : BaseDao<School> {
  @Query(`value` =
      "\n     REPLACE INTO SchoolReplicate(schoolPk, schoolDestination)\n      SELECT DISTINCT School.schoolUid AS schoolPk,\n             :newNodeId AS schoolDestination\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                 \n        \n                  536870912\n                  \n                    ) > 0\n            JOIN School\n                 ON \n            ((ScopedGrant.sgTableId = -2\n                    AND ScopedGrant.sgEntityUid = -2)\n                OR (ScopedGrant.sgTableId = 164\n                    AND ScopedGrant.sgEntityUid = School.schoolUid))\n        \n        \n       WHERE UserSession.usClientNodeId = :newNodeId\n         AND UserSession.usStatus = 1\n         AND School.schoolLct != COALESCE(\n             (SELECT schoolVersionId\n                FROM SchoolReplicate\n               WHERE schoolPk = School.schoolUid\n                 AND schoolDestination = :newNodeId), 0) \n      /*psql ON CONFLICT(schoolPk, schoolDestination) DO UPDATE\n             SET schoolPending = true\n      */       \n    ")
  public actual abstract suspend fun replicateOnNewNode(newNodeId: Long): Unit

  @Query(`value` =
      "\n REPLACE INTO SchoolReplicate(schoolPk, schoolDestination)\n  SELECT DISTINCT School.schoolUid AS schoolUid,\n         UserSession.usClientNodeId AS schoolDestination\n    FROM ChangeLog\n         JOIN School\n              ON ChangeLog.chTableId = 164\n                  AND ChangeLog.chEntityPk = School.schoolUid\n              \n            JOIN ScopedGrant\n                 ON \n            ((ScopedGrant.sgTableId = -2\n                    AND ScopedGrant.sgEntityUid = -2)\n                OR (ScopedGrant.sgTableId = 164\n                    AND ScopedGrant.sgEntityUid = School.schoolUid))\n        \n                        AND (SCopedGrant.sgPermissions &\n        \n                  536870912\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        \n   WHERE UserSession.usClientNodeId != (\n         SELECT nodeClientId \n           FROM SyncNode\n          LIMIT 1)\n     AND School.schoolLct != COALESCE(\n         (SELECT schoolVersionId\n            FROM SchoolReplicate\n           WHERE schoolPk = School.schoolUid\n             AND schoolDestination = UserSession.usClientNodeId), 0)\n /*psql ON CONFLICT(schoolPk, schoolDestination) DO UPDATE\n     SET schoolPending = true\n  */               \n    ")
  public actual abstract suspend fun replicateOnChange(): Unit

  @Query(`value` =
      "SELECT * FROM School WHERE schoolUid = :schoolUid AND CAST(schoolActive AS INTEGER) = 1")
  public actual abstract suspend fun findByUidAsync(schoolUid: Long): School?

  @Query(`value` =
      "SELECT School.*, HolidayCalendar.* FROM School \n            LEFT JOIN HolidayCalendar ON School.schoolHolidayCalendarUid = HolidayCalendar.umCalendarUid\n            WHERE School.schoolUid = :uid")
  public actual abstract suspend fun findByUidWithHolidayCalendarAsync(uid: Long):
      SchoolWithHolidayCalendar?

  @Query(`value` = "SELECT * FROM School WHERE schoolCode = :code")
  public actual abstract suspend fun findBySchoolCode(code: String): School?

  @Query(`value` = "SELECT * FROM School WHERE schoolCode = :code")
  public actual abstract suspend fun findBySchoolCodeFromWeb(code: String): School?

  @Query(`value` =
      "\n    Select EXISTS(\n           SELECT School.schoolUid \n             FROM School\n                  \n            JOIN ScopedGrant\n                 ON \n            ((ScopedGrant.sgTableId = -2\n                    AND ScopedGrant.sgEntityUid = -2)\n                OR (ScopedGrant.sgTableId = 164\n                    AND ScopedGrant.sgEntityUid = School.schoolUid))\n        \n                        AND (SCopedGrant.sgPermissions &\n         :permission) > 0\n             JOIN PersonGroupMember AS PrsGrpMbr\n                   ON ScopedGrant.sgGroupUid = PrsGrpMbr.groupMemberGroupUid\n                      AND PrsGrpMbr.groupMemberPersonUid = :accountPersonUid\n            WHERE School.schoolUid = :schoolUid)      \n    ")
  public actual abstract suspend fun personHasPermissionWithSchool(
    accountPersonUid: Long,
    schoolUid: Long,
    permission: Long,
  ): Boolean

  @Query(`value` =
      "\n       SELECT School.*, \n              (SELECT COUNT(*) \n                  FROM SchoolMember \n                 WHERE SchoolMember.schoolMemberSchoolUid = School.schoolUid \n                   AND CAST(SchoolMember.schoolMemberActive AS INTEGER) = 1 \n                   AND SchoolMember.schoolMemberRole = 1003) as numStudents,\n              (SELECT COUNT(*) \n                 FROM SchoolMember \n                WHERE SchoolMember.schoolMemberSchoolUid = School.schoolUid \n                  AND CAST(SchoolMember.schoolMemberActive AS INTEGER) = 1 \n                  AND SchoolMember.schoolMemberRole = 1004) as numTeachers, \n               '' as locationName,\n              (SELECT COUNT(*) \n                 FROM Clazz \n                WHERE Clazz.clazzSchoolUid = School.schoolUid \n                  AND CAST(Clazz.clazzUid AS INTEGER) = 1 ) as clazzCount\n         FROM PersonGroupMember\n              \n            JOIN ScopedGrant \n                 ON ScopedGrant.sgGroupUid = PersonGroupMember.groupMemberGroupUid\n                        AND (ScopedGrant.sgPermissions &\n                 \n        \n                    :permission\n                    \n                    ) > 0\n            JOIN School\n                 ON \n            ((ScopedGrant.sgTableId = -2\n                    AND ScopedGrant.sgEntityUid = -2)\n                OR (ScopedGrant.sgTableId = 164\n                    AND ScopedGrant.sgEntityUid = School.schoolUid))\n        \n        \n        WHERE PersonGroupMember.groupMemberPersonUid = :personUid\n          AND PersonGroupMember.groupMemberActive \n          AND CAST(schoolActive AS INTEGER) = 1\n          AND schoolName LIKE :searchBit\n     GROUP BY School.schoolUid\n     ORDER BY CASE(:sortOrder)\n              WHEN 1 THEN School.schoolName\n              ELSE ''\n              END ASC,\n              CASE(:sortOrder)\n              WHEN 2 THEN School.schoolName\n              ELSE ''\n              END DESC")
  public actual abstract fun findAllActiveSchoolWithMemberCountAndLocationName(
    searchBit: String,
    personUid: Long,
    permission: Long,
    sortOrder: Int,
  ): DataSource.Factory<Int, SchoolWithMemberCountAndLocation>

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