package com.ustadmobile.core.db.dao

import androidx.room.Dao
import androidx.room.Insert
import androidx.room.Query
import androidx.room.Update
import com.ustadmobile.lib.db.entities.CourseGroupMember
import com.ustadmobile.lib.db.entities.CourseGroupMemberPerson
import kotlin.Long
import kotlin.Unit
import kotlin.collections.List

@Dao
public actual abstract class CourseGroupMemberDao : BaseDao<CourseGroupMember> {
  @Query(`value` =
      "\n     REPLACE INTO CourseGroupMemberReplicate(cgmPk, cgmDestination)\n      SELECT DISTINCT CourseGroupMember.cgmUid AS cgmUid,\n             :newNodeId AS cgmDestination\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             JOIN CourseGroupSet\n                    ON CourseGroupSet.cgsClazzUid = Clazz.clazzUid\n             JOIN CourseGroupMember\n                    ON CourseGroupMember.cgmSetUid = CourseGroupSet.cgsUid       \n       WHERE UserSession.usClientNodeId = :newNodeId\n         AND UserSession.usStatus = 1\n         AND CourseGroupMember.cgmLct != COALESCE(\n             (SELECT cgmVersionId\n                FROM CourseGroupMemberReplicate\n               WHERE cgmPk = CourseGroupMember.cgmUid\n                 AND cgmDestination = :newNodeId), 0) \n      /*psql ON CONFLICT(cgmPk, cgmDestination) DO UPDATE\n             SET cgmPending = true\n      */       \n    ")
  public actual abstract suspend fun replicateOnNewNode(newNodeId: Long): Unit

  @Query(`value` =
      "\n REPLACE INTO CourseGroupMemberReplicate(cgmPk, cgmDestination)\n  SELECT DISTINCT CourseGroupMember.cgmUid AS cgmUid,\n         UserSession.usClientNodeId AS cgmDestination\n    FROM ChangeLog\n         JOIN CourseGroupMember\n               ON ChangeLog.chTableId = 243\n                  AND ChangeLog.chEntityPk = CourseGroupMember.cgmUid\n          JOIN CourseGroupSet\n               ON CourseGroupSet.cgsUid = CourseGroupMember.cgmSetUid       \n          JOIN Clazz \n               ON Clazz.clazzUid = CourseGroupSet.cgsClazzUid \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 CourseGroupMember.cgmLct != COALESCE(\n         (SELECT cgmVersionId\n            FROM CourseGroupMemberReplicate\n           WHERE cgmPk = CourseGroupMember.cgmUid\n             AND cgmDestination = UserSession.usClientNodeId), 0)\n /*psql ON CONFLICT(cgmPk, cgmDestination) DO UPDATE\n     SET cgmPending = true\n  */               \n ")
  public actual abstract suspend fun replicateOnChange(): Unit

  @Query(`value` =
      "\n        SELECT Person.*, CourseGroupMember.* \n          FROM Person\n               JOIN ClazzEnrolment \n               ON Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid\n               AND ClazzEnrolment.clazzEnrolmentRole = 1000\n               AND ClazzEnrolment.clazzEnrolmentOutcome = 200\n               \n               LEFT JOIN CourseGroupMember\n               ON CourseGroupMember.cgmPersonUid = ClazzEnrolment.clazzEnrolmentPersonUid\n               AND CourseGroupMember.cgmSetUid = :setUid\n               \n         WHERE clazzEnrolmentClazzUid = :clazzUid\n      ORDER BY Person.firstNames\n    ")
  public actual abstract suspend fun findByGroupSetAsync(setUid: Long, clazzUid: Long):
      List<CourseGroupMemberPerson>

  @Query(`value` =
      "\n        SELECT Person.*, CourseGroupMember.* \n          FROM Person\n               JOIN ClazzEnrolment \n               ON Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid\n               AND ClazzEnrolment.clazzEnrolmentRole = 1000 \n               AND ClazzEnrolment.clazzEnrolmentOutcome = 200\n               \n               LEFT JOIN CourseGroupMember\n               ON CourseGroupMember.cgmPersonUid = ClazzEnrolment.clazzEnrolmentPersonUid\n               AND CourseGroupMember.cgmSetUid = :setUid\n               \n         WHERE clazzEnrolmentClazzUid = :clazzUid\n      ORDER BY CourseGroupMember.cgmGroupNumber, Person.firstNames\n    ")
  public actual abstract suspend fun findByGroupSetOrderedAsync(setUid: Long, clazzUid: Long):
      List<CourseGroupMemberPerson>

  @Query(`value` =
      "\n        SELECT * \n          FROM CourseGroupMember\n         WHERE cgmPersonUid = :studentUid \n          AND cgmSetUid = :groupSetUid\n         LIMIT 1\n    ")
  public actual abstract suspend fun findByPersonUid(groupSetUid: Long, studentUid: Long):
      CourseGroupMember?

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

  @Update(onConflict = 3)
  public actual abstract suspend fun updateListAsync(entityList: List<CourseGroupMember>): Unit
}
