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.SchoolMember
import com.ustadmobile.lib.db.entities.SchoolMemberWithPerson
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.Unit
import kotlin.collections.List

@Dao
public actual abstract class SchoolMemberDao : BaseDao<SchoolMember> {
  @Query(`value` =
      "\n     REPLACE INTO SchoolMemberReplicate(smPk, smDestination)\n      SELECT DISTINCT SchoolMember.schoolMemberUid AS smPk,\n             :newNodeId AS smDestination\n        FROM UserSession\n             JOIN PersonGroupMember\n                  ON UserSession.usPersonUid = PersonGroupMember.groupMemberPersonUid\n             JOIN ScopedGrant\n                  ON ScopedGrant.sgGroupUid = PersonGroupMember.groupMemberGroupUid\n                     AND (ScopedGrant.sgPermissions &  64) > 0\n             JOIN SchoolMember\n                  ON \n            ((ScopedGrant.sgTableId = -2\n                  AND ScopedGrant.sgEntityUid = -2)\n              OR (ScopedGrant.sgTableId = 9\n                  AND ScopedGrant.sgEntityUid = SchoolMember.schoolMemberPersonUid)\n              OR (ScopedGrant.sgTableId = 164\n                 AND ScopedGrant.sgEntityUid = SchoolMember.schoolMemberSchoolUid))\n        \n       WHERE UserSession.usClientNodeId = :newNodeId\n         AND UserSession.usStatus = 1\n         AND SchoolMember.schoolMemberLct != COALESCE(\n             (SELECT smVersionId\n                FROM SchoolMemberReplicate\n               WHERE smPk = SchoolMember.schoolMemberUid\n                 AND smDestination = :newNodeId), 0) \n      /*psql ON CONFLICT(smPk, smDestination) DO UPDATE\n             SET smPending = true\n      */       \n    ")
  public actual abstract suspend fun replicateOnNewNode(newNodeId: Long): Unit

  @Query(`value` =
      "\n REPLACE INTO SchoolMemberReplicate(smPk, smDestination)\n  SELECT DISTINCT SchoolMember.schoolMemberUid AS smUid,\n         UserSession.usClientNodeId AS smDestination\n    FROM ChangeLog\n         JOIN SchoolMember\n              ON ChangeLog.chTableId = 200\n                  AND ChangeLog.chEntityPk = SchoolMember.schoolMemberUid\n         \n            JOIN ScopedGrant\n                 ON \n            ((ScopedGrant.sgTableId = -2\n                  AND ScopedGrant.sgEntityUid = -2)\n              OR (ScopedGrant.sgTableId = 9\n                  AND ScopedGrant.sgEntityUid = SchoolMember.schoolMemberPersonUid)\n              OR (ScopedGrant.sgTableId = 164\n                 AND ScopedGrant.sgEntityUid = SchoolMember.schoolMemberSchoolUid))\n        \n                    AND (ScopedGrant.sgPermissions &\n        \n              64\n              \n            ) > 0  \n            JOIN PersonGroupMember \n                   ON ScopedGrant.sgGroupUid = PersonGroupMember.groupMemberGroupUid\n            JOIN UserSession\n                   ON UserSession.usPersonUid = PersonGroupMember.groupMemberPersonUid\n                      AND UserSession.usStatus =1\n        \n   WHERE UserSession.usClientNodeId != (\n         SELECT nodeClientId \n           FROM SyncNode\n          LIMIT 1)\n     AND SchoolMember.schoolMemberLct != COALESCE(\n         (SELECT smVersionId\n            FROM SchoolMemberReplicate\n           WHERE smPk = SchoolMember.schoolMemberUid\n             AND smDestination = UserSession.usClientNodeId), 0)\n /*psql ON CONFLICT(smPk, smDestination) DO UPDATE\n     SET smPending = true\n  */               \n ")
  public actual abstract suspend fun replicateOnChange(): Unit

  @Query(`value` =
      "SELECT * FROM SchoolMember WHERE schoolMemberUid = :schoolMemberUid  AND CAST(schoolMemberActive AS INTEGER) = 1")
  public actual abstract fun findByUidAsync(schoolMemberUid: Long): SchoolMember?

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

  @Query(`value` =
      "\n        SELECT * FROM SchoolMember WHERE schoolMemberSchoolUid = :schoolUid\n        AND schoolMemberPersonUid = :personUid\n        AND (:role = 0 OR schoolMemberRole = :role)\n        AND (:timeFilter = 0 OR :timeFilter BETWEEN SchoolMember.schoolMemberJoinDate AND SchoolMember.schoolMemberLeftDate) \n        AND CAST(schoolMemberActive AS INTEGER) = 1\n    ")
  public actual abstract suspend fun findBySchoolAndPersonAndRole(
    schoolUid: Long,
    personUid: Long,
    role: Int,
    timeFilter: Long,
  ): List<SchoolMember>

  @Query(`value` =
      "\n        SELECT SchoolMember.*, Person.*\n          FROM PersonGroupMember\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          LEFT JOIN SchoolMember ON Person.personUid = SchoolMember.schoolMemberPersonUid \n         WHERE PersonGroupMember.groupMemberPersonUid = :accountPersonUid \n         AND PersonGroupMember.groupMemberActive  \n        AND SchoolMember.schoolMemberActive\n        AND SchoolMember.schoolMemberSchoolUid = :schoolUid \n        AND SchoolMember.schoolMemberRole = :role\n        AND Person.active\n        AND (Person.firstNames || ' ' || Person.lastName) LIKE :searchQuery\n        GROUP BY Person.personUid, SchoolMember.schoolMemberUid\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            ")
  public actual abstract fun findAllActiveMembersBySchoolAndRoleUid(
    schoolUid: Long,
    role: Int,
    sortOrder: Int,
    searchQuery: String,
    accountPersonUid: Long,
  ): DataSource.Factory<Int, SchoolMemberWithPerson>

  @Query(`value` =
      "SELECT SchoolMember.*, Person.* FROM SchoolMember\n        LEFT JOIN Person ON Person.personUid = SchoolMember.schoolMemberPersonUid\n        WHERE CAST(SchoolMember.schoolMemberActive AS INTEGER) = 1\n        AND SchoolMember.schoolMemberSchoolUid = :schoolUid \n        AND SchoolMember.schoolMemberRole = :role\n        AND CAST(Person.active AS INTEGER) = 1\n        AND (Person.firstNames || ' ' || Person.lastName) LIKE :searchQuery\n        ORDER BY Person.firstNames DESC")
  public actual abstract suspend fun findAllTest(
    schoolUid: Long,
    role: Int,
    searchQuery: String,
  ): List<SchoolMemberWithPerson>
}
