<?xml version="1.0" encoding="UTF-8" ?>
<XMLDB PATH="local/ascend_rewards/db" VERSION="20251210" COMMENT="Apex Rewards plugin"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:noNamespaceSchemaLocation="../../../lib/xmldb/xmldb.xsd"
>
<!--
This file is part of Moodle - https://moodle.org/

Moodle is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

Moodle is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with Moodle.  If not, see <https://www.gnu.org/licenses/>.
-->
  <TABLES>
    <TABLE NAME="local_ascend_rewards_coins" COMMENT="Coin ledger for Apex Rewards">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true" />
        <FIELD NAME="userid" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" />
        <FIELD NAME="badgeid" TYPE="int" LENGTH="10" NOTNULL="false" SEQUENCE="false" />
        <FIELD NAME="courseid" TYPE="int" LENGTH="10" NOTNULL="false" SEQUENCE="false" />
        <FIELD NAME="coins" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" />
        <FIELD NAME="xp" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" />
        <FIELD NAME="timecreated" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" />
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id" />
        <KEY NAME="userid" TYPE="foreign" FIELDS="userid" REFTABLE="user" REFFIELDS="id" />
      </KEYS>
      <INDEXES>
        <INDEX NAME="userid_idx" UNIQUE="false" FIELDS="userid" />
        <INDEX NAME="timecreated_idx" UNIQUE="false" FIELDS="timecreated" />
        <INDEX NAME="badgeid_idx" UNIQUE="false" FIELDS="badgeid" />
        <INDEX NAME="courseid_idx" UNIQUE="false" FIELDS="courseid" />
      </INDEXES>
    </TABLE>
    <TABLE NAME="local_ascend_rewards_gameboard" COMMENT="Weekly gameboard for badge awards">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true" />
        <FIELD NAME="userid" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" />
        <FIELD NAME="badgeid" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" />
        <FIELD NAME="courseid" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" />
        <FIELD NAME="week" TYPE="char" LENGTH="10" NOTNULL="true" SEQUENCE="false" />
        <FIELD NAME="position" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" />
        <FIELD NAME="coins" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" />
        <FIELD NAME="timecreated" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" />
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id" />
        <KEY NAME="userid" TYPE="foreign" FIELDS="userid" REFTABLE="user" REFFIELDS="id" />
      </KEYS>
      <INDEXES>
        <INDEX NAME="userid_week" UNIQUE="false" FIELDS="userid,week" />
        <INDEX NAME="userid_badge" UNIQUE="false" FIELDS="userid,badgeid,week" />
      </INDEXES>
    </TABLE>
    <TABLE NAME="local_ascend_badge_cache" COMMENT="Cache for badge qualifying activities">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true" />
        <FIELD NAME="userid" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" />
        <FIELD NAME="courseid" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" />
        <FIELD NAME="badgeid" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" />
        <FIELD NAME="activities" TYPE="text" NOTNULL="true" SEQUENCE="false" />
        <FIELD NAME="metadata" TYPE="text" NOTNULL="true" SEQUENCE="false" />
        <FIELD NAME="timecreated" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" />
        <FIELD NAME="timemodified" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" />
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id" />
      </KEYS>
      <INDEXES>
        <INDEX NAME="userid_courseid_badgeid" UNIQUE="true" FIELDS="userid,courseid,badgeid" />
        <INDEX NAME="idx_userid" UNIQUE="false" FIELDS="userid" />
        <INDEX NAME="idx_courseid" UNIQUE="false" FIELDS="courseid" />
        <INDEX NAME="idx_badgeid" UNIQUE="false" FIELDS="badgeid" />
      </INDEXES>
    </TABLE>
    <TABLE NAME="local_ascend_rewards_badges" COMMENT="Badge configuration and enablement">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true" />
        <FIELD NAME="code" TYPE="char" LENGTH="64" NOTNULL="true" SEQUENCE="false" />
        <FIELD NAME="badgeid" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" />
        <FIELD NAME="courseid" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" />
        <FIELD NAME="enabled" TYPE="int" LENGTH="2" NOTNULL="true" DEFAULT="1" SEQUENCE="false" />
        <FIELD NAME="timecreated" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" />
        <FIELD NAME="timemodified" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" />
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id" />
        <KEY NAME="uniq_code_scope" TYPE="unique" FIELDS="code,courseid" />
      </KEYS>
      <INDEXES>
        <INDEX NAME="badgeid_idx" UNIQUE="false" FIELDS="badgeid" />
      </INDEXES>
    </TABLE>
    <TABLE NAME="local_ascend_rewards_badgerlog" COMMENT="Badge awarding audit log">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true" />
        <FIELD NAME="userid" TYPE="int" LENGTH="10" NOTNULL="false" SEQUENCE="false" />
        <FIELD NAME="badgeid" TYPE="int" LENGTH="10" NOTNULL="false" SEQUENCE="false" />
        <FIELD NAME="status" TYPE="char" LENGTH="20" NOTNULL="false" SEQUENCE="false" />
        <FIELD NAME="message" TYPE="text" NOTNULL="false" SEQUENCE="false" />
        <FIELD NAME="timecreated" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" />
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id" />
      </KEYS>
      <INDEXES>
        <INDEX NAME="user_time_idx" UNIQUE="false" FIELDS="userid,timecreated" />
      </INDEXES>
    </TABLE>
    <TABLE NAME="local_ascend_mysterybox" COMMENT="Mystery box openings and rewards tracking">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true" />
        <FIELD NAME="userid" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" />
        <FIELD NAME="reward_type" TYPE="char" LENGTH="50" NOTNULL="true" SEQUENCE="false" />
        <FIELD NAME="reward_id" TYPE="char" LENGTH="255" NOTNULL="true" SEQUENCE="false" />
        <FIELD NAME="reward_rarity" TYPE="char" LENGTH="20" NOTNULL="true" SEQUENCE="false" />
        <FIELD NAME="pity_triggered" TYPE="int" LENGTH="1" NOTNULL="true" DEFAULT="0" SEQUENCE="false" />
        <FIELD NAME="timecreated" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" />
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id" />
        <KEY NAME="userid" TYPE="foreign" FIELDS="userid" REFTABLE="user" REFFIELDS="id" />
      </KEYS>
      <INDEXES>
        <INDEX NAME="mbox_timecreated_idx" UNIQUE="false" FIELDS="timecreated" />
        <INDEX NAME="mbox_reward_type_idx" UNIQUE="false" FIELDS="reward_type" />
      </INDEXES>
    </TABLE>
    <TABLE NAME="local_ascend_avatar_unlocks" COMMENT="Avatar, pet, and villain unlock tracking with level-based system">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true" />
        <FIELD NAME="userid" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" />
        <FIELD NAME="avatar_name" TYPE="char" LENGTH="64" NOTNULL="true" SEQUENCE="false" />
        <FIELD NAME="avatar_level" TYPE="int" LENGTH="2" NOTNULL="true" SEQUENCE="false" COMMENT="1-8 for avatar levels (1-4 regular, 5-8 epic)" />
        <FIELD NAME="pet_id" TYPE="int" LENGTH="10" NOTNULL="false" SEQUENCE="false" COMMENT="NULL if avatar, pet ID if pet" />
        <FIELD NAME="villain_id" TYPE="int" LENGTH="10" NOTNULL="false" SEQUENCE="false" COMMENT="NULL if not villain, villain ID if villain" />
        <FIELD NAME="unlock_type" TYPE="char" LENGTH="20" NOTNULL="true" SEQUENCE="false" COMMENT="token or coin" />
        <FIELD NAME="timecreated" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" />
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id" />
        <KEY NAME="userid" TYPE="foreign" FIELDS="userid" REFTABLE="user" REFFIELDS="id" />
      </KEYS>
      <INDEXES>
        <INDEX NAME="user_avatar_pet_villain_idx" UNIQUE="true" FIELDS="userid,avatar_name,pet_id,villain_id" />
        <INDEX NAME="user_level_idx" UNIQUE="false" FIELDS="userid,avatar_level" />
      </INDEXES>
    </TABLE>
    <TABLE NAME="local_ascend_level_tokens" COMMENT="Level-up unlock tokens for avatars and pets">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true" />
        <FIELD NAME="userid" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" />
        <FIELD NAME="tokens_available" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" />
        <FIELD NAME="tokens_used" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" />
        <FIELD NAME="timemodified" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" />
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id" />
        <KEY NAME="userid" TYPE="foreign" FIELDS="userid" REFTABLE="user" REFFIELDS="id" UNIQUE="true" />
      </KEYS>
    </TABLE>
    <TABLE NAME="local_ascend_xp" COMMENT="XP tracking - SEPARATE from coins. XP never decreases. Rankings use XP only.">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true" />
        <FIELD NAME="userid" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" />
        <FIELD NAME="courseid" TYPE="int" LENGTH="10" NOTNULL="false" SEQUENCE="false" COMMENT="0 = site-wide, otherwise course-specific XP" />
        <FIELD NAME="xp" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false" />
        <FIELD NAME="timemodified" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" />
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id" />
        <KEY NAME="userid" TYPE="foreign" FIELDS="userid" REFTABLE="user" REFFIELDS="id" />
      </KEYS>
      <INDEXES>
        <INDEX NAME="userid_courseid" UNIQUE="true" FIELDS="userid,courseid" />
        <INDEX NAME="xp_idx" UNIQUE="false" FIELDS="xp" />
      </INDEXES>
    </TABLE>
  </TABLES>
</XMLDB>
