package com.ustadmobile.core.db.dao

import androidx.room.Dao
import androidx.room.Query
import com.ustadmobile.lib.db.entities.ClazzAssignmentRollUp
import kotlin.Long
import kotlin.Unit
import kotlin.collections.List

@Dao
public actual abstract class ClazzAssignmentRollUpDao : BaseDao<ClazzAssignmentRollUp> {
  @Query(`value` =
      " \n        REPLACE INTO ClazzAssignmentRollUp \n                (cachePersonUid, cacheContentEntryUid, cacheClazzAssignmentUid, \n                 cacheStudentScore, cacheMaxScore, cacheWeight,  cacheProgress,\n                 cacheContentComplete, cacheSuccess,cachePenalty, cacheFinalWeightScoreWithPenalty, lastCsnChecked)\n                 \n        WITH MaxScoreTable (maxScore, maxScoreContentEntryUid) \n                AS (SELECT MAX(resultScoreMax), statementContentEntryUid \n                      FROM StatementEntity\n                     WHERE contentEntryRoot \n                  GROUP BY statementContentEntryUid)               \n\n       SELECT clazzEnrolmentPersonUid AS cachePersonUid, \n                COALESCE(cacjContentUid,0) AS cacheContentEntryUid, caUid AS cacheClazzAssignmentUid, \n               COALESCE(resultScoreRaw,0) AS cacheStudentScore, \n              \n              \n               COALESCE((SELECT maxScore \n                          FROM MaxScoreTable \n                         WHERE cacjContentUid = maxScoreContentEntryUid), 0) AS cacheMaxScore,\n                         \n               COALESCE(cacjWeight, 0) AS cacheWeight,\n                        \n                          \n               COALESCE(StatementEntity.extensionProgress,0) AS cacheProgress,\n               COALESCE(StatementEntity.resultCompletion,'FALSE') AS cacheContentComplete, \n               COALESCE(StatementEntity.resultSuccess,0) AS cacheSuccess,\n               (CASE WHEN StatementEntity.timestamp > CourseBlock.cbDeadlineDate\n                     THEN CourseBlock.cbLateSubmissionPenalty \n                     ELSE 0 END) AS cachePenalty,\n                     \n              (CASE WHEN StatementEntity.timestamp > CourseBlock.cbDeadlineDate \n                     THEN (COALESCE(CAST(resultScoreRaw AS REAL),0) / COALESCE((SELECT maxScore \n                          FROM MaxScoreTable WHERE cacjContentUid = maxScoreContentEntryUid),0) * 100 * cacjWeight * (1 - (CAST(cbLateSubmissionPenalty AS REAL)/100)))\n                     ELSE (COALESCE(CAST(resultScoreRaw AS REAL),0) / COALESCE((SELECT maxScore \n                          FROM MaxScoreTable WHERE cacjContentUid = maxScoreContentEntryUid),0) * 100 * cacjWeight)  END) AS cacheFinalWeightScoreWithPenalty,   \n                     \n               0 AS lastCsnChecked\n          FROM ClazzAssignmentContentJoin\n\t            JOIN ClazzAssignment \n                ON ClazzAssignment.caUid = ClazzAssignmentContentJoin.cacjAssignmentUid\n                                \n                JOIN ClazzEnrolment\n                ON ClazzEnrolment.clazzEnrolmentClazzUid = ClazzAssignment.caClazzUid\n                \n                JOIN CourseBlock\n                ON CourseBlock.cbEntityUid = ClazzAssignment.caUid\n               AND CourseBlock.cbType = 103 \n\t\t\t\t\t      \t      \n\t\t\t    LEFT JOIN StatementEntity \n\t            ON statementUid = (SELECT statementUid \n                                     FROM StatementEntity \n                                            LEFT JOIN ClazzAssignment \n                                            ON ClazzAssignment.caUid = ClazzAssignmentContentJoin.cacjAssignmentUid \n                                              JOIN CourseBlock\n                                                ON CourseBlock.cbEntityUid = ClazzAssignment.caUid\n                                               AND CourseBlock.cbType = 103 \n                                    WHERE StatementEntity.statementContentEntryUid = ClazzAssignmentContentJoin.cacjContentUid\n                                      AND StatementEntity.statementPersonUid = ClazzEnrolment.clazzEnrolmentPersonUid\n                                      AND StatementEntity.contentEntryRoot  \n                                      AND StatementEntity.timestamp \n                                            BETWEEN CourseBlock.cbHideUntilDate\n                                            AND CourseBlock.cbGracePeriodDate\n                                  ORDER BY CASE WHEN StatementEntity.timestamp > CourseBlock.cbDeadlineDate \n                                                THEN StatementEntity.resultScoreScaled * (1 - (CAST(CourseBlock.cbLateSubmissionPenalty AS REAL)/100))\n                                                ELSE StatementEntity.resultScoreScaled END DESC, \n                                            StatementEntity.extensionProgress DESC, \n                                            StatementEntity.resultSuccess DESC LIMIT 1)      \n                LEFT JOIN ClazzAssignmentRollUp\n                ON ClazzAssignmentRollUp.cacheContentEntryUid = ClazzAssignmentContentJoin.cacjContentUid \n                AND ClazzAssignmentRollUp.cachePersonUid = ClazzEnrolment.clazzEnrolmentPersonUid\n                AND ClazzAssignmentRollUp.cacheClazzAssignmentUid = ClazzAssignment.caUid\n                                            \n                                            \n\t     WHERE ClazzEnrolment.clazzEnrolmentRole = 1000\n           AND ClazzEnrolment.clazzEnrolmentOutcome = 200\n           AND ClazzEnrolment.clazzEnrolmentActive\n           AND ClazzAssignment.caActive\n           AND ClazzAssignmentContentJoin.cacjActive\n           AND (:clazzUid = 0 OR ClazzAssignment.caClazzUid = :clazzUid)\n           AND (:assignmentUid = 0 OR ClazzAssignment.caUid = :assignmentUid)\n           AND (:personUid = 0 OR ClazzEnrolment.clazzEnrolmentPersonUid = :personUid)\n           AND (COALESCE(StatementEntity.resultScoreRaw,0) >= COALESCE(ClazzAssignmentRollUp.cacheStudentScore,0)\n                    AND COALESCE(StatementEntity.extensionProgress,0) >= COALESCE(ClazzAssignmentRollUp.cacheProgress,0)\n                    AND COALESCE(StatementEntity.resultSuccess,0) >= COALESCE(ClazzAssignmentRollUp.cacheSuccess,0))\n      GROUP BY cacheClazzAssignmentUid, cacheContentEntryUid, cachePersonUid\n         UNION \n         SELECT clazzEnrolmentPersonUid AS cachePersonUid, \n                0 AS cacheContentEntryUid, \n                caUid AS cacheClazzAssignmentUid, \n                COALESCE(MarkingStatement.resultScoreRaw,0) AS cacheStudentScore, \n                COALESCE(cbMaxPoints,0) AS cacheMaxScore,\n                0 AS cacheWeight,\n                \n                COALESCE(MarkingStatement.extensionProgress,0) AS cacheProgress,\n                COALESCE(MarkingStatement.resultCompletion,'FALSE') AS cacheContentComplete, \n                COALESCE(MarkingStatement.resultSuccess,0) AS cacheSuccess,\n                (CASE WHEN SubmissionStatement.timestamp > CourseBlock.cbDeadlineDate \n                     THEN CourseBlock.cbLateSubmissionPenalty \n                     ELSE 0 END) AS cachePenalty,\n                     \n              (CASE WHEN SubmissionStatement.timestamp > CourseBlock.cbDeadlineDate \n                     THEN (COALESCE(CAST(MarkingStatement.resultScoreRaw AS REAL),0) / COALESCE(CourseBlock.cbMaxPoints,0) * \n                            100 * (1 - (CAST(cbLateSubmissionPenalty AS REAL)/100)))\n                     ELSE (COALESCE(CAST(MarkingStatement.resultScoreRaw AS REAL),0) / COALESCE(cbMaxPoints,0) * \n                            100)  END) AS cacheFinalWeightScoreWithPenalty, \n                     \n                   \n               0 AS lastCsnChecked\n         FROM ClazzAssignment\n              JOIN ClazzEnrolment\n              ON ClazzEnrolment.clazzEnrolmentClazzUid = ClazzAssignment.caClazzUid\n              \n               JOIN CourseBlock\n                ON CourseBlock.cbEntityUid = ClazzAssignment.caUid\n               AND CourseBlock.cbType = 103 \n              \n              LEFT JOIN StatementEntity AS SubmissionStatement\n\t          ON SubmissionStatement.statementUid = (SELECT statementUid \n                                   FROM StatementEntity\n                                  WHERE StatementEntity.statementContentEntryUid = 0\n                                    AND xObjectUid = ClazzAssignment.caXObjectUid\n                                    AND StatementEntity.statementPersonUid = ClazzEnrolment.clazzEnrolmentPersonUid\n                                    AND StatementEntity.timestamp \n                                        BETWEEN CourseBlock.cbHideUntilDate\n                                        AND CourseBlock.cbGracePeriodDate\n                               ORDER BY timestamp DESC LIMIT 1\n                                  )\n              LEFT JOIN XObjectEntity AS ObjectStatementRef\n              ON ObjectStatementRef.objectStatementRefUid = SubmissionStatement.statementUid                    \n                                  \n              LEFT JOIN StatementEntity AS MarkingStatement\n               ON MarkingStatement.timestamp = (SELECT timestamp \n                                                  FROM StatementEntity \n                                                 WHERE xObjectUid = ObjectStatementRef.xObjectUid \n                                              ORDER BY timestamp DESC \n                                                 LIMIT 1)\n              \n        WHERE ClazzEnrolment.clazzEnrolmentRole = 1000\n          AND ClazzEnrolment.clazzEnrolmentOutcome = 200\n          AND ClazzEnrolment.clazzEnrolmentActive\n          AND ClazzAssignment.caActive\n          AND ClazzAssignment.caRequireFileSubmission\n          AND (:clazzUid = 0 OR ClazzAssignment.caClazzUid = :clazzUid)\n          AND (:assignmentUid = 0 OR ClazzAssignment.caUid = :assignmentUid)\n          AND (:personUid = 0 OR ClazzEnrolment.clazzEnrolmentPersonUid = :personUid)\n      GROUP BY cacheClazzAssignmentUid, cacheContentEntryUid, cachePersonUid     \n    ")
  public actual abstract suspend fun cacheBestStatements(
    clazzUid: Long,
    assignmentUid: Long,
    personUid: Long,
  ): Unit

  @Query(`value` =
      "\n        DELETE\n         FROM ClazzAssignmentRollUp\n        WHERE cacheContentEntryUid \n                IN (SELECT cacjContentUid \n                     FROM ClazzAssignmentContentJoin\n                    WHERE NOT cacjActive)\n           OR (cacheClazzAssignmentUid \n              IN (SELECT caUid \n                   FROM ClazzAssignment\n                  WHERE caUid = :caUid\n                    AND NOT caRequireFileSubmission) \n               AND cacheContentEntryUid = 0)                                        \n    ")
  public actual abstract suspend fun deleteCachedInactiveContent(caUid: Long): Unit

  @Query(`value` =
      "\n        UPDATE ClazzAssignmentRollUp \n           SET lastCsnChecked = 0\n         WHERE cacheClazzAssignmentUid = :changedAssignmentUid\n    ")
  public actual abstract suspend fun invalidateCacheByAssignment(changedAssignmentUid: Long): Unit

  @Query(`value` =
      "\n        UPDATE ClazzAssignmentRollUp \n           SET lastCsnChecked = 0\n         WHERE cacheClazzAssignmentUid IN (:changedAssignmentUid)\n    ")
  public actual abstract suspend
      fun invalidateCacheByAssignmentList(changedAssignmentUid: List<Long>): Unit
}
