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.ClazzEnrolment
import com.ustadmobile.lib.db.entities.ClazzEnrolmentWithClazz
import com.ustadmobile.lib.db.entities.ClazzEnrolmentWithClazzAndAttendance
import com.ustadmobile.lib.db.entities.ClazzEnrolmentWithLeavingReason
import com.ustadmobile.lib.db.entities.ClazzEnrolmentWithPerson
import com.ustadmobile.lib.db.entities.PersonWithClazzEnrolmentDetails
import kotlin.Boolean
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.Unit
import kotlin.collections.List

@Dao
public actual abstract class ClazzEnrolmentDao : BaseDao<ClazzEnrolment> {
  @Query(`value` =
      "\n     REPLACE INTO ClazzEnrolmentReplicate(cePk, ceDestination)\n      SELECT DISTINCT ClazzEnrolment.clazzEnrolmentUid AS ceUid,\n             :newNodeId AS ceDestination\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                    64 \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             JOIN ClazzEnrolment \n                   ON ClazzEnrolment.clazzEnrolmentClazzUid = Clazz.clazzUid\n       WHERE UserSession.usClientNodeId = :newNodeId\n         AND UserSession.usStatus = 1\n         AND ClazzEnrolment.clazzEnrolmentLct != COALESCE(\n             (SELECT ceVersionId\n                FROM ClazzEnrolmentReplicate\n               WHERE cePk = ClazzEnrolment.clazzEnrolmentUid\n                 AND ceDestination = :newNodeId), 0) \n      /*psql ON CONFLICT(cePk, ceDestination) DO UPDATE\n             SET cePending = true\n      */       \n    ")
  public actual abstract suspend fun replicateOnNewNode(newNodeId: Long): Unit

  @Query(`value` =
      "\n REPLACE INTO ClazzEnrolmentReplicate(cePk, ceDestination)\n  SELECT DISTINCT ClazzEnrolment.clazzEnrolmentUid AS ceUid,\n         UserSession.usClientNodeId AS ceDestination\n    FROM ChangeLog\n         JOIN ClazzEnrolment\n             ON ChangeLog.chTableId = 65\n                AND ChangeLog.chEntityPk = ClazzEnrolment.clazzEnrolmentUid\n         JOIN Clazz\n             ON Clazz.clazzUid = ClazzEnrolment.clazzEnrolmentClazzUid\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 ClazzEnrolment.clazzEnrolmentLct != COALESCE(\n         (SELECT ceVersionId\n            FROM ClazzEnrolmentReplicate\n           WHERE cePk = ClazzEnrolment.clazzEnrolmentUid\n             AND ceDestination = UserSession.usClientNodeId), 0)\n /*psql ON CONFLICT(cePk, ceDestination) DO UPDATE\n     SET cePending = true\n  */               \n    ")
  public actual abstract suspend fun replicateClazzEnrolmentOnChange(): Unit

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

  @Query(`value` =
      "SELECT * FROM ClazzEnrolment WHERE clazzEnrolmentPersonUid = :personUid \n        AND clazzEnrolmentClazzUid = :clazzUid \n        AND clazzEnrolmentOutcome = 200 LIMIT 1")
  public actual abstract suspend fun findByPersonUidAndClazzUidAsync(personUid: Long,
      clazzUid: Long): ClazzEnrolment?

  @Query(`value` =
      "SELECT ClazzEnrolment.*, LeavingReason.*, \n         COALESCE(Clazz.clazzTimeZone, COALESCE(School.schoolTimeZone, 'UTC')) as timeZone\n         FROM ClazzEnrolment LEFT JOIN\n        LeavingReason ON LeavingReason.leavingReasonUid = ClazzEnrolment.clazzEnrolmentLeavingReasonUid\n        LEFT JOIN Clazz ON Clazz.clazzUid = ClazzEnrolment.clazzEnrolmentClazzUid\n        LEFT JOIN School ON School.schoolUid = Clazz.clazzSchoolUid\n        WHERE clazzEnrolmentPersonUid = :personUid \n        AND ClazzEnrolment.clazzEnrolmentActive \n        AND clazzEnrolmentClazzUid = :clazzUid ORDER BY clazzEnrolmentDateLeft DESC")
  public actual abstract fun findAllEnrolmentsByPersonAndClazzUid(personUid: Long, clazzUid: Long):
      DataSource.Factory<Int, ClazzEnrolmentWithLeavingReason>

  @Query(`value` =
      "SELECT ClazzEnrolment.*, LeavingReason.*,\n         COALESCE(Clazz.clazzTimeZone, COALESCE(School.schoolTimeZone, 'UTC')) as timeZone\n         FROM ClazzEnrolment LEFT JOIN\n        LeavingReason ON LeavingReason.leavingReasonUid = ClazzEnrolment.clazzEnrolmentLeavingReasonUid\n        LEFT JOIN Clazz ON Clazz.clazzUid = ClazzEnrolment.clazzEnrolmentClazzUid\n        LEFT JOIN School ON School.schoolUid = Clazz.clazzSchoolUid\n        WHERE ClazzEnrolment.clazzEnrolmentUid = :enrolmentUid")
  public actual abstract suspend fun findEnrolmentWithLeavingReason(enrolmentUid: Long):
      ClazzEnrolmentWithLeavingReason?

  @Query(`value` =
      "\n        UPDATE ClazzEnrolment \n          SET clazzEnrolmentDateLeft = :endDate,\n              clazzEnrolmentLct = :updateTime\n        WHERE clazzEnrolmentUid = :clazzEnrolmentUid")
  public actual abstract suspend fun updateDateLeftByUid(
    clazzEnrolmentUid: Long,
    endDate: Long,
    updateTime: Long,
  ): Unit

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

  @Query(`value` =
      "SELECT ClazzEnrolment.*, Clazz.*, (SELECT ((CAST(COUNT(DISTINCT CASE WHEN \n        ClazzLogAttendanceRecord.attendanceStatus = 1 THEN \n        ClazzLogAttendanceRecord.clazzLogAttendanceRecordUid ELSE NULL END) AS REAL) / \n        MAX(COUNT(ClazzLogAttendanceRecord.clazzLogAttendanceRecordUid),1)) * 100) \n        FROM ClazzLogAttendanceRecord LEFT JOIN ClazzLog ON \n        ClazzLogAttendanceRecord.clazzLogAttendanceRecordClazzLogUid = ClazzLog.clazzLogUid WHERE \n        ClazzLogAttendanceRecord.clazzLogAttendanceRecordPersonUid = :personUid \n        AND ClazzLog.clazzLogClazzUid = Clazz.clazzUid AND ClazzLog.logDate \n        BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined AND ClazzEnrolment.clazzEnrolmentDateLeft) \n        as attendance\n        FROM ClazzEnrolment\n        LEFT JOIN Clazz ON ClazzEnrolment.clazzEnrolmentClazzUid = Clazz.clazzUid\n        WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :personUid\n        AND ClazzEnrolment.clazzEnrolmentActive\n        ORDER BY ClazzEnrolment.clazzEnrolmentDateLeft DESC\n    ")
  public actual abstract fun findAllClazzesByPersonWithClazz(personUid: Long):
      DataSource.Factory<Int, ClazzEnrolmentWithClazzAndAttendance>

  @Query(`value` =
      "SELECT COALESCE(MAX(clazzEnrolmentDateLeft),0) FROM ClazzEnrolment WHERE \n        ClazzEnrolment.clazzEnrolmentPersonUid = :selectedPerson \n        AND ClazzEnrolment.clazzEnrolmentActive \n        AND clazzEnrolmentClazzUid = :selectedClazz AND clazzEnrolmentUid != :selectedEnrolment\n    ")
  public actual abstract suspend fun findMaxEndDateForEnrolment(
    selectedClazz: Long,
    selectedPerson: Long,
    selectedEnrolment: Long,
  ): Long

  @Query(`value` =
      "SELECT ClazzEnrolment.*, Clazz.* \n        FROM ClazzEnrolment \n        LEFT JOIN Clazz ON ClazzEnrolment.clazzEnrolmentClazzUid = Clazz.clazzUid \n        WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :personUid \n        AND ClazzEnrolment.clazzEnrolmentActive\n        ORDER BY ClazzEnrolment.clazzEnrolmentDateLeft DESC\n    ")
  public actual abstract suspend fun findAllClazzesByPersonWithClazzAsListAsync(personUid: Long):
      List<ClazzEnrolmentWithClazz>

  @Query(`value` =
      "\n        SELECT ClazzEnrolment.*, Person.*\n          FROM ClazzEnrolment\n    LEFT JOIN Person ON ClazzEnrolment.clazzEnrolmentPersonUid = Person.personUid\n        WHERE ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid\n              AND :date BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined \n              AND ClazzEnrolment.clazzEnrolmentDateLeft\n              AND CAST(clazzEnrolmentActive AS INTEGER) = 1\n              AND (:roleFilter = 0 OR ClazzEnrolment.clazzEnrolmentRole = :roleFilter)\n              AND (:personUidFilter = 0 OR ClazzEnrolment.clazzEnrolmentPersonUid = :personUidFilter)\n    ")
  public actual abstract suspend fun getAllClazzEnrolledAtTimeAsync(
    clazzUid: Long,
    date: Long,
    roleFilter: Int,
    personUidFilter: Long,
  ): List<ClazzEnrolmentWithPerson>

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

  @Query(`value` = "SELECT * FROM ClazzEnrolment WHERE clazzEnrolmentUid = :uid")
  public actual abstract fun findByUidLive(uid: Long): LiveData<ClazzEnrolment?>

  @Query(`value` =
      "\n                UPDATE ClazzEnrolment\n                   SET clazzEnrolmentActive = :active,\n                       clazzEnrolmentLct= :changeTime\n                WHERE clazzEnrolmentPersonUid = :personUid \n                      AND clazzEnrolmentClazzUid = :clazzUid\n                      AND clazzEnrolmentRole = :roleId")
  public actual abstract suspend fun updateClazzEnrolmentActiveForPersonAndClazz(
    personUid: Long,
    clazzUid: Long,
    roleId: Int,
    active: Boolean,
    changeTime: Long,
  ): Int

  @Query(`value` =
      "\n        SELECT Person.*, \n               (SELECT ((CAST(COUNT(DISTINCT \n                        CASE WHEN ClazzLogAttendanceRecord.attendanceStatus = 1 \n                                  THEN ClazzLogAttendanceRecord.clazzLogAttendanceRecordUid \n                             ELSE NULL \n                             END) \n                        AS REAL) / \n                        MAX(COUNT(ClazzLogAttendanceRecord.clazzLogAttendanceRecordUid),1)) * 100) \n                   FROM ClazzLogAttendanceRecord \n                        JOIN ClazzLog \n                             ON ClazzLogAttendanceRecord.clazzLogAttendanceRecordClazzLogUid = ClazzLog.clazzLogUid \n                  WHERE ClazzLogAttendanceRecord.clazzLogAttendanceRecordPersonUid = Person.personUid \n                    AND ClazzLog.clazzLogClazzUid = :clazzUid)  AS attendance, \n        \n    \t       (SELECT MIN(ClazzEnrolment.clazzEnrolmentDateJoined) \n                  FROM ClazzEnrolment \n                 WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid) AS earliestJoinDate, \n        \n    \t      (SELECT MAX(ClazzEnrolment.clazzEnrolmentDateLeft) \n                 FROM ClazzEnrolment \n                WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid) AS latestDateLeft, \n        \n              (SELECT clazzEnrolmentRole \n                 FROM clazzEnrolment \n                WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid \n                  AND ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid \n        AND ClazzEnrolment.clazzEnrolmentActive) AS enrolmentRole\n        FROM PersonGroupMember\n        \n            JOIN ScopedGrant\n                 ON ScopedGrant.sgGroupUid = PersonGroupMember.groupMemberGroupUid\n                    AND (ScopedGrant.sgPermissions & 64 \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        \n         WHERE PersonGroupMember.groupMemberPersonUid = :accountPersonUid\n           AND PersonGroupMember.groupMemberActive \n           AND Person.personUid IN (SELECT clazzEnrolmentPersonUid \n                                      FROM ClazzEnrolment \n                                     WHERE ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid \n                                       AND ClazzEnrolment.clazzEnrolmentActive \n                                       AND ClazzEnrolment.clazzEnrolmentRole = :roleId \n                                       AND (:filter != 1 \n                                        OR (:currentTime \n                                            BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined \n                                            AND ClazzEnrolment.clazzEnrolmentDateLeft))) \n          AND 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            CASE(:sortOrder)\n                WHEN 5 THEN attendance\n                WHEN 7 THEN earliestJoinDate\n                WHEN 9 THEN latestDateLeft\n                ELSE 0\n            END ASC,\n            CASE(:sortOrder)\n                WHEN 6 THEN attendance\n                WHEN 8 THEN earliestJoinDate\n                WHEN 10 THEN latestDateLeft\n                ELSE 0\n            END DESC\n    ")
  public actual abstract fun findByClazzUidAndRole(
    clazzUid: Long,
    roleId: Int,
    sortOrder: Int,
    searchText: String?,
    filter: Int,
    accountPersonUid: Long,
    currentTime: Long,
  ): DataSource.Factory<Int, PersonWithClazzEnrolmentDetails>

  @Query(`value` =
      "\n        UPDATE ClazzEnrolment \n          SET clazzEnrolmentActive = :enrolled,\n              clazzEnrolmentLct = :timeChanged\n        WHERE clazzEnrolmentUid = :clazzEnrolmentUid")
  public actual abstract fun updateClazzEnrolmentActiveForClazzEnrolment(
    clazzEnrolmentUid: Long,
    enrolled: Boolean,
    timeChanged: Long,
  ): Int

  @Query(`value` =
      "\n            UPDATE ClazzEnrolment \n               SET clazzEnrolmentRole = :newRole,\n                   clazzEnrolmentLct = :updateTime      \n             -- Avoid potential for duplicate approvals if user was previously refused      \n             WHERE clazzEnrolmentUid = COALESCE( \n                    (SELECT clazzEnrolmentUid\n                       FROM ClazzEnrolment\n                      WHERE clazzEnrolmentPersonUid = :personUid \n                            AND clazzEnrolmentClazzUid = :clazzUid\n                            AND clazzEnrolmentRole = :oldRole\n                            AND CAST(clazzEnrolmentActive AS INTEGER) = 1\n                      LIMIT 1), 0)")
  public actual abstract suspend fun updateClazzEnrolmentRole(
    personUid: Long,
    clazzUid: Long,
    newRole: Int,
    oldRole: Int,
    updateTime: Long,
  ): Int
}
