package com.ustadmobile.core.db.dao

import androidx.lifecycle.LiveData
import androidx.paging.DataSource
import androidx.room.Dao
import androidx.room.Query
import androidx.room.Update
import com.ustadmobile.lib.db.entities.Clazz
import com.ustadmobile.lib.db.entities.ClazzWithDisplayDetails
import com.ustadmobile.lib.db.entities.ClazzWithHolidayCalendarAndSchoolAndTerminology
import com.ustadmobile.lib.db.entities.ClazzWithListDisplayDetails
import com.ustadmobile.lib.db.entities.ClazzWithSchool
import com.ustadmobile.lib.db.entities.UidAndLabel
import kotlin.Boolean
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.Unit
import kotlin.collections.List

@Dao
public actual abstract class ClazzDao : BaseDao<Clazz> {
  @Query(`value` =
      "\n     REPLACE INTO ClazzReplicate(clazzPk, clazzDestination)\n      SELECT DISTINCT Clazz.clazzUid AS clazzUid,\n             :newNodeId AS clazzDestination\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                    2 \n                    \n                       ) > 0\n               JOIN Clazz \n                    ON \n            ((ScopedGrant.sgTableId = -2\n                                AND ScopedGrant.sgEntityUid = -2)\n                            OR (ScopedGrant.sgTableId = 6\n                                AND ScopedGrant.sgEntityUid = Clazz.clazzUid)\n                            OR (ScopedGrant.sgTableId = 164\n                                AND ScopedGrant.sgEntityUid = Clazz.clazzSchoolUid))\n        \n        \n       WHERE UserSession.usClientNodeId = :newNodeId \n         AND Clazz.clazzLct != COALESCE(\n             (SELECT clazzVersionId\n                FROM ClazzReplicate\n               WHERE clazzPk = Clazz.clazzUid\n                 AND clazzDestination = :newNodeId), 0) \n      /*psql ON CONFLICT(clazzPk, clazzDestination) DO UPDATE\n             SET clazzPending = true\n      */       \n    ")
  public actual abstract suspend fun replicateOnNewNode(newNodeId: Long): Unit

  @Query(`value` =
      "\n REPLACE INTO ClazzReplicate(clazzPk, clazzDestination)\n  SELECT DISTINCT Clazz.clazzUid AS clazzUid,\n         UserSession.usClientNodeId AS clazzDestination\n    FROM ChangeLog\n         JOIN Clazz\n             ON ChangeLog.chTableId = 6\n                AND ChangeLog.chEntityPk = Clazz.clazzUid\n         \n            JOIN ScopedGrant\n                 ON \n            ((ScopedGrant.sgTableId = -2\n                                AND ScopedGrant.sgEntityUid = -2)\n                            OR (ScopedGrant.sgTableId = 6\n                                AND ScopedGrant.sgEntityUid = Clazz.clazzUid)\n                            OR (ScopedGrant.sgTableId = 164\n                                AND ScopedGrant.sgEntityUid = Clazz.clazzSchoolUid))\n        \n                    AND (ScopedGrant.sgPermissions & \n        \n                    2\n                    \n              \n                                                       ) > 0\n             JOIN PersonGroupMember AS PrsGrpMbr\n                   ON ScopedGrant.sgGroupUid = PrsGrpMbr.groupMemberGroupUid\n                                               \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 Clazz.clazzLct != COALESCE(\n         (SELECT clazzVersionId\n            FROM ClazzReplicate\n           WHERE clazzPk = Clazz.clazzUid\n             AND clazzDestination = UserSession.usClientNodeId), 0)\n  /*psql ON CONFLICT(clazzPk, clazzDestination) DO UPDATE\n      SET clazzPending = true\n   */               \n ")
  public actual abstract suspend fun replicateOnChange(): Unit

  @Query(`value` = "SELECT * FROM Clazz WHERE clazzUid = :uid")
  public actual abstract fun findByUid(uid: Long): Clazz?

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

  @Query(`value` = "SELECT * FROM Clazz WHERE clazzCode = :code")
  public actual abstract suspend fun findByClazzCode(code: String): Clazz?

  @Query(`value` = "SELECT * FROM Clazz WHERE clazzCode = :code")
  public actual abstract suspend fun findByClazzCodeFromWeb(code: String): Clazz?

  @Query(`value` = "SELECT * FROM Clazz WHERE CAST(isClazzActive AS INTEGER) = 1")
  public actual abstract fun findAllLive(): LiveData<List<Clazz>>

  @Query(`value` = "SELECT * FROM Clazz WHERE CAST(isClazzActive AS INTEGER) = 1")
  public actual abstract fun findAll(): List<Clazz>

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

  @Query(`value` =
      "\n        SELECT Clazz.*, \n               HolidayCalendar.*, \n               School.*,\n               CourseTerminology.*\n          FROM Clazz \n               LEFT JOIN HolidayCalendar \n               ON Clazz.clazzHolidayUMCalendarUid = HolidayCalendar.umCalendarUid\n               \n               LEFT JOIN School \n               ON School.schoolUid = Clazz.clazzSchoolUid\n               \n               LEFT JOIN CourseTerminology\n               ON CourseTerminology.ctUid = Clazz.clazzTerminologyUid\n         WHERE Clazz.clazzUid = :uid")
  public actual abstract suspend fun findByUidWithHolidayCalendarAsync(uid: Long):
      ClazzWithHolidayCalendarAndSchoolAndTerminology?

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

  @Query(`value` =
      "SELECT * FROM Clazz WHERE clazzSchoolUid = :schoolUid AND CAST(isClazzActive AS INTEGER) = 1 ")
  public actual abstract suspend fun findAllClazzesBySchool(schoolUid: Long): List<Clazz>

  @Query(`value` =
      "SELECT * FROM Clazz WHERE clazzSchoolUid = :schoolUid AND CAST(isClazzActive AS INTEGER) = 1 ")
  public actual abstract fun findAllClazzesBySchoolLive(schoolUid: Long):
      DataSource.Factory<Int, Clazz>

  @Query(`value` =
      "\n        SELECT Clazz.*, ClazzEnrolment.*,\n               (SELECT COUNT(*) \n                  FROM ClazzEnrolment \n                 WHERE ClazzEnrolment.clazzEnrolmentClazzUid = Clazz.clazzUid \n                   AND clazzEnrolmentRole = 1000 \n                   AND :currentTime BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined \n                       AND ClazzEnrolment.clazzEnrolmentDateLeft) AS numStudents,\n               (SELECT COUNT(*) \n                  FROM ClazzEnrolment \n                 WHERE ClazzEnrolment.clazzEnrolmentClazzUid = Clazz.clazzUid \n                   AND clazzEnrolmentRole = 1001\n                   AND :currentTime BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined \n                        AND ClazzEnrolment.clazzEnrolmentDateLeft) AS numTeachers,\n               '' AS teacherNames,\n               0 AS lastRecorded,\n               CourseTerminology.*\n          FROM PersonGroupMember\n               \n               JOIN ScopedGrant\n                    ON ScopedGrant.sgGroupUid = PersonGroupMember.groupMemberGroupUid\n                        AND (ScopedGrant.sgPermissions & \n        \n                    :permission\n                    \n                       ) > 0\n               JOIN Clazz \n                    ON \n            ((ScopedGrant.sgTableId = -2\n                                AND ScopedGrant.sgEntityUid = -2)\n                            OR (ScopedGrant.sgTableId = 6\n                                AND ScopedGrant.sgEntityUid = Clazz.clazzUid)\n                            OR (ScopedGrant.sgTableId = 164\n                                AND ScopedGrant.sgEntityUid = Clazz.clazzSchoolUid))\n        \n                  \n               LEFT JOIN ClazzEnrolment \n                    ON ClazzEnrolment.clazzEnrolmentUid =\n                       COALESCE(\n                       (SELECT ClazzEnrolment.clazzEnrolmentUid \n                          FROM ClazzEnrolment\n                         WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid\n                           AND ClazzEnrolment.clazzEnrolmentActive\n                           AND ClazzEnrolment.clazzEnrolmentClazzUid = Clazz.clazzUid LIMIT 1), 0)\n                LEFT JOIN CourseTerminology   \n                ON CourseTerminology.ctUid = Clazz.clazzTerminologyUid           \n\n         WHERE PersonGroupMember.groupMemberPersonUid = :accountPersonUid\n           AND PersonGroupMember.groupMemberActive \n           AND CAST(Clazz.isClazzActive AS INTEGER) = 1\n           AND Clazz.clazzName like :searchQuery\n           AND (Clazz.clazzUid NOT IN (:excludeSelectedClazzList))\n           AND ( :excludeSchoolUid = 0 OR Clazz.clazzUid NOT IN (SELECT cl.clazzUid FROM Clazz AS cl WHERE cl.clazzSchoolUid = :excludeSchoolUid) ) \n           AND ( :excludeSchoolUid = 0 OR Clazz.clazzSchoolUid = 0 )\n           AND ( :filter = 0 OR (CASE WHEN :filter = 5 \n                                      THEN :currentTime BETWEEN Clazz.clazzStartTime AND Clazz.clazzEndTime\n                                      ELSE :currentTime > Clazz.clazzEndTime \n                                      END))\n           AND ( :selectedSchool = 0 OR Clazz.clazzSchoolUid = :selectedSchool)\n      GROUP BY Clazz.clazzUid, ClazzEnrolment.clazzEnrolmentUid, CourseTerminology.ctUid\n      ORDER BY CASE :sortOrder\n               WHEN 3 THEN Clazz.attendanceAverage\n               ELSE 0\n               END ASC,\n               CASE :sortOrder\n               WHEN 1 THEN Clazz.clazzName\n               ELSE ''\n               END ASC,\n               CASE :sortOrder\n               WHEN 4 THEN Clazz.attendanceAverage\n               ELSE 0\n               END DESC,\n               CASE :sortOrder\n               WHEN 2 THEN clazz.Clazzname\n               ELSE ''\n               END DESC\n    ")
  public actual abstract fun findClazzesWithPermission(
    searchQuery: String,
    accountPersonUid: Long,
    excludeSelectedClazzList: List<Long>,
    excludeSchoolUid: Long,
    sortOrder: Int,
    filter: Int,
    currentTime: Long,
    permission: Long,
    selectedSchool: Long,
  ): DataSource.Factory<Int, ClazzWithListDisplayDetails>

  @Query(`value` =
      "SELECT Clazz.clazzUid AS uid, Clazz.clazzName AS labelName From Clazz WHERE clazzUid IN (:ids)")
  public actual abstract suspend fun getClassNamesFromListOfIds(ids: List<Long>): List<UidAndLabel>

  @Query(`value` =
      "SELECT * FROM Clazz WHERE clazzName = :name and CAST(isClazzActive AS INTEGER) = 1")
  public actual abstract fun findByClazzName(name: String): List<Clazz>

  @Query(`value` =
      "\n        UPDATE Clazz \n           SET attendanceAverage = \n               COALESCE(CAST(\n                    (SELECT SUM(clazzLogNumPresent) \n                       FROM ClazzLog \n                      WHERE clazzLogClazzUid = :clazzUid\n                       AND clazzLogStatusFlag = 4) AS REAL) /\n                    \n                    CAST(MAX(1.0, \n                        (SELECT SUM(clazzLogNumPresent) + SUM(clazzLogNumPartial) + SUM(clazzLogNumAbsent)\n                        FROM ClazzLog \n                       WHERE clazzLogClazzUid = :clazzUid \n                        AND clazzLogStatusFlag = 4)) AS REAL), 0),\n               clazzLct = :timeChanged         \n         WHERE clazzUid = :clazzUid\n    ")
  public actual abstract suspend fun updateClazzAttendanceAverageAsync(clazzUid: Long,
      timeChanged: Long): Unit

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

  @Query(`value` =
      "\n        SELECT ScopedGrant.sgPermissions\n          FROM Clazz\n               JOIN ScopedGrant\n                    ON \n            ((ScopedGrant.sgTableId = -2\n                                AND ScopedGrant.sgEntityUid = -2)\n                            OR (ScopedGrant.sgTableId = 6\n                                AND ScopedGrant.sgEntityUid = Clazz.clazzUid)\n                            OR (ScopedGrant.sgTableId = 164\n                                AND ScopedGrant.sgEntityUid = Clazz.clazzSchoolUid))\n        \n               JOIN PersonGroupMember AS PrsGrpMbr\n                    ON ScopedGrant.sgGroupUid = PrsGrpMbr.groupMemberGroupUid\n         WHERE Clazz.clazzUid = :clazzUid\n           AND (ScopedGrant.sgPermissions & 4294967296) > 0\n           AND PrsGrpMbr.groupMemberPersonUid = :accountPersonUid\n    ")
  public actual abstract suspend fun selectDelegatablePermissions(accountPersonUid: Long,
      clazzUid: Long): List<Long>

  @Query(`value` =
      "\n        SELECT Clazz.*, \n               HolidayCalendar.*, \n               School.*,\n               (SELECT COUNT(*) \n                  FROM ClazzEnrolment \n                 WHERE ClazzEnrolment.clazzEnrolmentClazzUid = Clazz.clazzUid \n                   AND clazzEnrolmentRole = 1000 \n                   AND :currentTime BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined \n                        AND ClazzEnrolment.clazzEnrolmentDateLeft) AS numStudents,\n               (SELECT COUNT(*) \n                  FROM ClazzEnrolment \n                 WHERE ClazzEnrolment.clazzEnrolmentClazzUid = Clazz.clazzUid \n                   AND clazzEnrolmentRole = 1001 \n                   AND :currentTime BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined \n                       AND ClazzEnrolment.clazzEnrolmentDateLeft) AS numTeachers,\n                CourseTerminology.*      \n         FROM Clazz \n              LEFT JOIN HolidayCalendar \n              ON Clazz.clazzHolidayUMCalendarUid = HolidayCalendar.umCalendarUid\n              LEFT JOIN School \n              ON School.schoolUid = Clazz.clazzSchoolUid\n              LEFT JOIN CourseTerminology\n              ON CourseTerminology.ctUid = Clazz.clazzTerminologyUid\n        WHERE Clazz.clazzUid = :clazzUid")
  public actual abstract fun getClazzWithDisplayDetails(clazzUid: Long, currentTime: Long):
      LiveData<ClazzWithDisplayDetails?>

  @Query(`value` =
      "\n        SELECT Clazz.*, \n               HolidayCalendar.*, \n               School.*,\n               CourseTerminology.*\n         FROM Clazz \n              LEFT JOIN HolidayCalendar \n              ON ((clazz.clazzHolidayUMCalendarUid != 0 \n                AND HolidayCalendar.umCalendarUid = clazz.clazzHolidayUMCalendarUid)\n                OR clazz.clazzHolidayUMCalendarUid = 0 AND clazz.clazzSchoolUid = 0 \n                AND HolidayCalendar.umCalendarUid = (SELECT schoolHolidayCalendarUid \n                                                       FROM School \n                                                      WHERE schoolUid = clazz.clazzSchoolUid))\n              LEFT JOIN School \n              ON School.schoolUid = Clazz.clazzSchoolUid\n              \n              LEFT JOIN CourseTerminology\n              ON CourseTerminology.ctUid = Clazz.clazzTerminologyUid\n                \n        WHERE :filterUid = 0 \n           OR Clazz.clazzUid = :filterUid\n    ")
  public actual abstract fun findClazzesWithEffectiveHolidayCalendarAndFilter(filterUid: Long):
      List<ClazzWithHolidayCalendarAndSchoolAndTerminology>

  @Query(`value` =
      "SELECT Clazz.*, School.* FROM Clazz LEFT JOIN School ON School.schoolUid = Clazz.clazzSchoolUid WHERE clazz.clazzUid = :clazzUid")
  public actual abstract suspend fun getClazzWithSchool(clazzUid: Long): ClazzWithSchool?
}
