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.RawQuery
import com.ustadmobile.door.DoorQuery
import com.ustadmobile.lib.db.entities.ContentEntryStatementScoreProgress
import com.ustadmobile.lib.db.entities.Person
import com.ustadmobile.lib.db.entities.PersonWithAttemptsSummary
import com.ustadmobile.lib.db.entities.PersonWithSessionsDisplay
import com.ustadmobile.lib.db.entities.StatementEntity
import com.ustadmobile.lib.db.entities.StatementEntityWithDisplayDetails
import com.ustadmobile.lib.db.entities.StatementReportData
import com.ustadmobile.lib.db.entities.StatementWithSessionDetailDisplay
import com.ustadmobile.lib.db.entities.XLangMapEntry
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.Unit
import kotlin.collections.List

@Dao
public actual abstract class StatementDao : BaseDao<StatementEntity> {
  @Query(`value` =
      "\n     REPLACE INTO StatementEntityReplicate(sePk, seDestination)\n      SELECT DISTINCT StatementEntity.statementUid AS sePk,\n             :newNodeId AS seDestination\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       WHERE UserSession.usClientNodeId = :newNodeId\n         AND UserSession.usStatus = 1\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 StatementEntity.statementLct != COALESCE(\n             (SELECT seVersionId\n                FROM StatementEntityReplicate\n               WHERE sePk = StatementEntity.statementUid\n                 AND seDestination = UserSession.usClientNodeId), 0)\n       --endnotpsql           \n      /*psql ON CONFLICT(sePk, seDestination) DO UPDATE\n             SET sePending = (SELECT StatementEntity.statementLct\n            FROM StatementEntity\n           WHERE StatementEntity.statementUid = EXCLUDED.sePk ) \n                 != StatementEntityReplicate.seVersionId\n      */       \n    ")
  public actual abstract suspend fun replicateOnNewNode(newNodeId: Long): Unit

  @Query(`value` =
      "\n REPLACE INTO StatementEntityReplicate(sePk, seDestination)\n  SELECT DISTINCT StatementEntity.statementUid AS seUid,\n         UserSession.usClientNodeId AS seDestination\n    FROM ChangeLog\n         JOIN StatementEntity\n               ON ChangeLog.chTableId = 60\n                  AND ChangeLog.chEntityPk = 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     AND StatementEntity.statementLct != COALESCE(\n         (SELECT seVersionId\n            FROM StatementEntityReplicate\n           WHERE sePk = StatementEntity.statementUid\n             AND seDestination = UserSession.usClientNodeId), 0)\n /*psql ON CONFLICT(sePk, seDestination) DO UPDATE\n     SET sePending = true\n  */\n    ")
  public actual abstract suspend fun replicateOnChange(): Unit

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

  @Query(`value` = "SELECT * From StatementEntity LIMIT 1")
  public actual abstract fun getOneStatement(): LiveData<StatementEntity?>

  @Query(`value` = "SELECT * FROM StatementEntity WHERE statementId = :id LIMIT 1")
  public actual abstract fun findByStatementId(id: String): StatementEntity?

  @Query(`value` = "SELECT * FROM StatementEntity WHERE statementId IN (:id)")
  public actual abstract fun findByStatementIdList(id: List<String>): List<StatementEntity>

  @RawQuery(observedEntities = arrayOf())
  public actual abstract suspend fun getResults(query: DoorQuery): List<StatementReportData>

  @RawQuery(observedEntities = arrayOf(StatementEntity::class, Person::class, XLangMapEntry::class))
  public actual abstract fun getListResults(query: DoorQuery):
      DataSource.Factory<Int, StatementEntityWithDisplayDetails>

  @Query(`value` = "SELECT * FROM PERSON LIMIT 1")
  public actual abstract fun getPerson(): Person?

  @Query(`value` = "SELECT * FROM XLangMapEntry LIMIT 1")
  public actual abstract fun getXLangMap(): XLangMapEntry?

  @Query(`value` =
      "\n        UPDATE StatementEntity \n           SET extensionProgress = :progress,\n               statementLct = :updateTime \n            WHERE statementUid = :uid")
  public actual abstract fun updateProgress(
    uid: Long,
    progress: Int,
    updateTime: Long,
  ): Unit

  @Query(`value` =
      "\n        SELECT ResultSource.personUid, ResultSource.firstNames, ResultSource.lastName,\n            COUNT(DISTINCT(ResultSource.contextRegistration)) AS attempts, \n            MIN(ResultSource.timestamp) AS startDate, \n            MAX(ResultSource.timestamp) AS endDate, \n            SUM(ResultSource.resultDuration) AS duration, \n            MAX(CASE WHEN ResultSource.contentEntryRoot \n                THEN resultScoreRaw\n                ELSE 0 END) AS resultScore, \n            MAX(CASE WHEN ResultSource.contentEntryRoot \n                THEN resultScoreMax\n                ELSE 0 END) AS resultMax,   \n            MAX(CASE WHEN ResultSource.contentEntryRoot \n                THEN resultScoreScaled\n                ELSE 0 END) AS resultScaled, \n            MAX(ResultSource.extensionProgress) AS progress,\n            0 AS penalty,\n            0 as resultWeight,\n            'FALSE' AS contentComplete,\n            0 AS success,\n            \n            CASE WHEN ResultSource.resultCompletion \n                THEN 1 ELSE 0 END AS totalCompletedContent,\n                \n            1 as totalContent, \n            \n            0 as fileSubmissionStatus, \n         \n            '' AS latestPrivateComment\n        \n         FROM (SELECT Person.personUid, Person.firstNames, Person.lastName, \n            StatementEntity.contextRegistration, StatementEntity.timestamp, \n            StatementEntity.resultDuration, StatementEntity.resultScoreRaw, \n            StatementEntity.resultScoreMax, StatementEntity.resultScoreScaled,\n            StatementEntity.contentEntryRoot, StatementEntity.extensionProgress, \n            StatementEntity.resultCompletion\n            FROM PersonGroupMember\n            \n            JOIN ScopedGrant\n                 ON ScopedGrant.sgGroupUid = PersonGroupMember.groupMemberGroupUid\n                    AND (ScopedGrant.sgPermissions & 549755813888 \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 StatementEntity \n                ON StatementEntity.statementPersonUid = Person.personUid \n                    WHERE PersonGroupMember.groupMemberPersonUid = :accountPersonUid \n                        AND PersonGroupMember.groupMemberActive  \n                        AND statementContentEntryUid = :contentEntryUid\n                        AND Person.firstNames || ' ' || Person.lastName LIKE :searchText              \n                   GROUP BY StatementEntity.statementUid \n                   ORDER BY resultScoreScaled DESC, extensionProgress DESC, resultSuccess DESC) AS ResultSource \n         GROUP BY ResultSource.personUid \n         ORDER BY CASE(:sortOrder) \n                WHEN 1 THEN ResultSource.firstNames\n                WHEN 3 THEN ResultSource.lastName\n                ELSE ''\n            END ASC,\n            CASE(:sortOrder)\n                WHEN 2 THEN ResultSource.firstNames\n                WHEN 4 THEN ResultSource.lastName\n                ELSE ''\n            END DESC,\n            CASE(:sortOrder)\n                WHEN 5 THEN endDate \n                ELSE 0\n            END ASC,\n            CASE(:sortOrder)\n                WHEN 6 then endDate\n                ELSE 0\n            END DESC\n         ")
  public actual abstract fun findPersonsWithContentEntryAttempts(
    contentEntryUid: Long,
    accountPersonUid: Long,
    searchText: String,
    sortOrder: Int,
  ): DataSource.Factory<Int, PersonWithAttemptsSummary>

  @Query(`value` =
      "\n        SELECT \n                COALESCE(StatementEntity.resultScoreMax,0) AS resultMax, \n                COALESCE(StatementEntity.resultScoreRaw,0) AS resultScore, \n                COALESCE(StatementEntity.resultScoreScaled,0) AS resultScaled, \n                COALESCE(StatementEntity.extensionProgress,0) AS progress, \n                COALESCE(StatementEntity.resultCompletion,'FALSE') AS contentComplete,\n                COALESCE(StatementEntity.resultSuccess, 0) AS success,\n                0 as resultWeight,\n                \n                COALESCE((CASE WHEN resultCompletion \n                THEN 1 ELSE 0 END),0) AS totalCompletedContent,\n                \n                1 as totalContent, \n                0 as penalty\n                \n        FROM ContentEntry\n            LEFT JOIN StatementEntity\n\t\t\t\t\t\t\tON StatementEntity.statementUid = \n                                (SELECT statementUid \n\t\t\t\t\t\t\t       FROM StatementEntity \n                                  WHERE statementContentEntryUid = ContentEntry.contentEntryUid \n\t\t\t\t\t\t\t        AND StatementEntity.statementPersonUid = :accountPersonUid\n\t\t\t\t\t\t\t        AND contentEntryRoot \n                               ORDER BY resultScoreScaled DESC, extensionProgress DESC, resultSuccess DESC LIMIT 1)\n                               \n       WHERE contentEntryUid = :contentEntryUid\n    ")
  public actual abstract suspend fun getBestScoreForContentForPerson(contentEntryUid: Long,
      accountPersonUid: Long): ContentEntryStatementScoreProgress?

  @Query(`value` =
      "\n         SELECT COALESCE((\n                SELECT DISTINCT(statementpersonUid)\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          \t           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          \t           LEFT JOIN XObjectEntity\n                       ON XObjectEntity.objectStatementRefUid = SubmissionStatement.statementUid  \n               \n                 WHERE ClazzAssignment.caUid = :assignmentUid\n                   AND XObjectEntity.xobjectUid IS NULL\n                   AND ClazzEnrolment.clazzEnrolmentActive\n                   AND ClazzEnrolment.clazzEnrolmentRole = 1000\n                   AND ClazzEnrolment.clazzEnrolmentPersonUid != :currentStudentUid\n            LIMIT 1),0)\n    ")
  public actual abstract suspend fun findNextStudentNotMarkedForAssignment(assignmentUid: Long,
      currentStudentUid: Long): Long

  @Query(`value` =
      "\n        SELECT * \n          FROM StatementEntity\n         WHERE statementPersonUid = :studentUid\n           AND statementVerbUid = 10008\n           AND xObjectUid = :assignmentObjectUid    \n      ORDER BY timestamp                \n    ")
  public actual abstract suspend fun findSubmittedStatementFromStudent(studentUid: Long,
      assignmentObjectUid: Long): StatementEntity?

  @Query(`value` =
      "\n        SELECT * \n          FROM StatementEntity\n         WHERE statementPersonUid = :studentUid\n           AND statementVerbUid = 10009\n      ORDER BY timestamp                \n    ")
  public actual abstract fun findScoreStatementForStudent(studentUid: Long): StatementEntity?

  @Query(`value` =
      "\n        SELECT MIN(timestamp) AS startDate, \n            MAX(CASE \n                    WHEN StatementEntity.resultSuccess > 0 \n                    AND StatementEntity.contentEntryRoot \n                    THEN StatementEntity.resultSuccess \n                    ELSE 0 END) AS resultSuccess, \n            SUM(CASE \n                     WHEN CAST(resultCompletion AS INTEGER) > 0 \n                     AND StatementEntity.contentEntryRoot \n                     THEN 1 \n                     ELSE 0 END) AS resultComplete, \n            SUM(resultDuration) AS duration, contextRegistration, \n            MAX(CASE WHEN contentEntryRoot \n                     THEN resultScoreRaw ELSE 0 END) AS resultScore, \n            MAX(CASE WHEN contentEntryRoot \n                     THEN resultScoreMax ELSE 0 END) AS resultMax,\n            MAX(CASE WHEN contentEntryRoot \n                     THEN resultScoreScaled ELSE 0 END) AS resultScoreScaled,\n                       \n            SUM(CASE WHEN resultCompletion AND StatementEntity.contentEntryRoot \n                THEN 1 ELSE 0 END) AS totalCompletedContent,\n                \n             1 as totalContent          \n                       \n        FROM StatementEntity \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                AND PersonGroupMember.groupMemberPersonUid = :accountPersonUid\n        WHERE statementContentEntryUid = :contentEntryUid   \n          AND statementPersonUid = :personUid \n        GROUP BY StatementEntity.contextRegistration \n        ORDER BY startDate DESC, resultScoreScaled DESC, extensionProgress DESC, resultSuccess DESC\n         ")
  public actual abstract fun findSessionsForPerson(
    contentEntryUid: Long,
    accountPersonUid: Long,
    personUid: Long,
  ): DataSource.Factory<Int, PersonWithSessionsDisplay>

  @Query(`value` =
      "\n        SELECT StatementEntity.*, VerbEntity.*, \n            verbLangMap.valueLangMap AS verbDisplay, \n            xobjectMap.valueLangMap AS objectDisplay \n        FROM StatementEntity\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                AND PersonGroupMember.groupMemberPersonUid = :accountPersonUid\n                LEFT JOIN VerbEntity \n                    ON VerbEntity.verbUid = StatementEntity.statementVerbUid \n                LEFT JOIN XLangMapEntry verbLangMap \n                    ON verbLangMap.verbLangMapUid = VerbEntity.verbUid\n                LEFT JOIN XLangMapEntry xobjectMap \n                    ON xobjectMap.objectLangMapUid = StatementEntity.xObjectUid\n         WHERE statementContentEntryUid = :contentEntryUid \n            AND statementPersonUid = :personUid \n            AND contextRegistration = :contextRegistration \n         ORDER BY StatementEntity.timestamp DESC\n         ")
  public actual abstract fun findSessionDetailForPerson(
    contentEntryUid: Long,
    accountPersonUid: Long,
    personUid: Long,
    contextRegistration: String,
  ): DataSource.Factory<Int, StatementWithSessionDetailDisplay>

  @Query(`value` =
      "\n        SELECT SUM(resultScoreRaw) AS resultScore, \n               SUM(resultScoreMax) AS resultMax,\n               MAX(extensionProgress) AS progress,\n               0 as resultWeight,\n               0 as penalty,\n               0 as success,\n               'FALSE' as contentComplete,\n               0 AS resultScaled, \n               COALESCE((CASE WHEN resultCompletion \n               THEN 1 ELSE 0 END),0) AS totalCompletedContent,\n                \n                1 as totalContent\n               \n         FROM (SELECT * \n                 FROM StatementEntity \n                WHERE contextRegistration = :contextRegistration\n                  AND NOT contentEntryRoot\n                  AND statementVerbUid = 10007 \n             GROUP BY xObjectUid) AS SessionStatements\n    ")
  public actual abstract suspend fun calculateScoreForSession(contextRegistration: String):
      ContentEntryStatementScoreProgress?

  @Query(`value` =
      "\n        SELECT resultScoreRaw AS resultScore, \n               resultScoreMax AS resultMax,\n               extensionProgress AS progress,\n               0 AS penalty,\n               0 as resultWeight,\n               resultSuccess AS success,\n               resultCompletion AS contentComplete, \n               resultScoreScaled AS resultScaled,\n                1 AS totalCompletedContent,\n                1 as totalContent\n               \n          FROM StatementEntity\n         WHERE resultCompletion\n          AND contextRegistration = :contextRegistration\n          AND contentEntryRoot\n     ORDER BY resultScoreScaled DESC, \n              extensionProgress DESC, \n              resultSuccess DESC \n              LIMIT 1\n    ")
  public actual abstract suspend fun findCompletedScoreForSession(contextRegistration: String):
      ContentEntryStatementScoreProgress?

  @Query(`value` =
      "\n        SELECT contextRegistration \n          FROM StatementEntity\n         WHERE statementPersonUid = :accountPersonUid\n           AND statementContentEntryUid = :entryUid\n           AND NOT EXISTS (SELECT statementUid FROM StatementEntity\n                            WHERE statementPersonUid = :accountPersonUid\n                             AND statementContentEntryUid = :entryUid\n                             AND (statementVerbUid = 10001 \n                                    OR statementVerbUid = 10004))\n      ORDER BY timestamp DESC \n    ")
  public actual abstract suspend fun findLatestRegistrationStatement(accountPersonUid: Long,
      entryUid: Long): String?
}
