package com.ustadmobile.core.db.dao

import androidx.room.Dao
import androidx.room.Query
import com.ustadmobile.lib.db.entities.ContextXObjectStatementJoin
import kotlin.Long
import kotlin.Unit

@Dao
public actual abstract class ContextXObjectStatementJoinDao : BaseDao<ContextXObjectStatementJoin> {
  @Query(`value` =
      "\n    REPLACE INTO ContextXObjectStatementJoinReplicate(cxosjPk, cxosjDestination)\n    SELECT DISTINCT ContextXObjectStatementJoin.contextXObjectStatementJoinUid AS cxosjPk,\n         UserSession.usClientNodeId AS cxosjDestination\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 & 549755813888) > 0\n             JOIN StatementEntity\n                  ON \n            ((ScopedGrant.sgTableId = -2\n                AND ScopedGrant.sgEntityUid = -2)\n             OR (ScopedGrant.sgTableId = 9\n                AND ScopedGrant.sgEntityUid = StatementEntity.statementPersonUid)\n             OR (ScopedGrant.sgTableId = 6\n                AND ScopedGrant.sgEntityUid = StatementEntity.statementClazzUid)\n             OR (ScopedGrant.sgTableId = 164\n                AND ScopedGrant.sgEntityUid = (\n                    SELECT clazzSchoolUid\n                      FROM Clazz \n                     WHERE clazzUid = StatementEntity.statementClazzUid))\n            )         \n        \n             JOIN ContextXObjectStatementJoin\n                  ON ContextXObjectStatementJoin.contextStatementUid = StatementEntity.statementUid\n   WHERE UserSession.usClientNodeId = :newNodeId\n     -- Temporary measure to prevent admin user getting clogged up\n     -- Restrict to the last 30 days of data\n     AND StatementEntity.timestamp > ( \n   --notpsql\n   strftime('%s', 'now') * 1000\n   --endnotpsql\n   /*psql\n   ROUND(EXTRACT(epoch from NOW())*1000)\n   */\n   - (30 * CAST(86400000 AS BIGINT)))   \n    --notpsql \n     AND ContextXObjectStatementJoin.contextXObjectLct != COALESCE(\n         (SELECT cxosjVersionId\n            FROM ContextXObjectStatementJoinReplicate\n           WHERE cxosjPk = ContextXObjectStatementJoin.contextXObjectStatementJoinUid\n             AND cxosjDestination = UserSession.usClientNodeId), 0) \n    --endnotpsql         \n    /*psql ON CONFLICT(cxosjPk, cxosjDestination) DO UPDATE\n     SET cxosjPending = (SELECT ContextXObjectStatementJoin.contextXObjectLct\n                           FROM ContextXObjectStatementJoin\n                          WHERE ContextXObjectStatementJoin.contextXObjectStatementJoinUid = EXCLUDED.cxosjPk ) \n                                != ContextXObjectStatementJoinReplicate.cxosjVersionId             \n    */       \n    ")
  public actual abstract suspend fun replicateOnNewNode(newNodeId: Long): Unit

  @Query(`value` =
      "\n    REPLACE INTO ContextXObjectStatementJoinReplicate(cxosjPk, cxosjDestination)\n    SELECT DISTINCT ContextXObjectStatementJoin.contextXObjectStatementJoinUid AS cxosjUid,\n         UserSession.usClientNodeId AS cxosjDestination\n    FROM ChangeLog\n         JOIN ContextXObjectStatementJoin\n             ON ChangeLog.chTableId = 66\n                AND ChangeLog.chEntityPk = ContextXObjectStatementJoin.contextXObjectStatementJoinUid\n         JOIN StatementEntity\n               ON ContextXObjectStatementJoin.contextStatementUid = StatementEntity.statementUid\n         JOIN ScopedGrant\n              ON \n            ((ScopedGrant.sgTableId = -2\n                AND ScopedGrant.sgEntityUid = -2)\n             OR (ScopedGrant.sgTableId = 9\n                AND ScopedGrant.sgEntityUid = StatementEntity.statementPersonUid)\n             OR (ScopedGrant.sgTableId = 6\n                AND ScopedGrant.sgEntityUid = StatementEntity.statementClazzUid)\n             OR (ScopedGrant.sgTableId = 164\n                AND ScopedGrant.sgEntityUid = (\n                    SELECT clazzSchoolUid\n                      FROM Clazz\n                     WHERE clazzUid = StatementEntity.statementClazzUid))\n             )\n        \n                 AND (ScopedGrant.sgPermissions & 549755813888) > 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    WHERE UserSession.usClientNodeId != (\n         SELECT nodeClientId \n           FROM SyncNode\n          LIMIT 1)\n   --notpsql       \n     AND ContextXObjectStatementJoin.contextXObjectLct != COALESCE(\n         (SELECT cxosjVersionId\n            FROM ContextXObjectStatementJoinReplicate\n           WHERE cxosjPk = ContextXObjectStatementJoin.contextXObjectStatementJoinUid\n             AND cxosjDestination = UserSession.usClientNodeId), 0)\n    --endnotpsql\n    /*psql ON CONFLICT(cxosjPk, cxosjDestination) DO UPDATE\n     SET cxosjPending = (SELECT ContextXObjectStatementJoin.contextXObjectLct\n                           FROM ContextXObjectStatementJoin\n                          WHERE ContextXObjectStatementJoin.contextXObjectStatementJoinUid = EXCLUDED.cxosjPk ) \n                                != ContextXObjectStatementJoinReplicate.cxosjVersionId             \n    */               \n    ")
  public actual abstract suspend fun replicateOnChange(): Unit

  @Query(`value` =
      "SELECT * FROM ContextXObjectStatementJoin where contextStatementUid = :statementUid and contextXObjectUid = :objectUid")
  public actual abstract fun findByStatementAndObjectUid(statementUid: Long, objectUid: Long):
      ContextXObjectStatementJoin?
}
