<?xml version="1.0" encoding="UTF-8" ?>
<XMLDB PATH="local/o365/db" VERSION="20241004" COMMENT="XMLDB file for Moodle local/o365 plugin"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:noNamespaceSchemaLocation="../../../lib/xmldb/xmldb.xsd"
>
  <TABLES>
    <TABLE NAME="local_o365_cronqueue" COMMENT="Queued events for the next cron run">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true"/>
        <FIELD NAME="operation" TYPE="char" LENGTH="255" NOTNULL="true" SEQUENCE="false" COMMENT="the operation to be performed"/>
        <FIELD NAME="data" TYPE="text" NOTNULL="true" SEQUENCE="false" COMMENT="the operation data"/>
        <FIELD NAME="timecreated" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="the time the operation was created"/>
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id"/>
      </KEYS>
      <INDEXES>
        <INDEX NAME="operation" UNIQUE="false" FIELDS="operation"/>
      </INDEXES>
    </TABLE>
    <TABLE NAME="local_o365_calidmap" COMMENT="Maps local event ids to o365 event ids">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true"/>
        <FIELD NAME="eventid" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="moodle event id"/>
        <FIELD NAME="outlookeventid" TYPE="char" LENGTH="255" NOTNULL="true" SEQUENCE="false" COMMENT="outlook event id"/>
        <FIELD NAME="origin" TYPE="char" LENGTH="255" NOTNULL="true" SEQUENCE="false" COMMENT="event origin, moodle or o365"/>
        <FIELD NAME="userid" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="moodle user id linked to outlookeventid"/>
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id"/>
      </KEYS>
      <INDEXES>
        <INDEX NAME="event" UNIQUE="false" FIELDS="eventid"/>
        <INDEX NAME="oevent" UNIQUE="false" FIELDS="outlookeventid"/>
      </INDEXES>
    </TABLE>
    <TABLE NAME="local_o365_calsub" COMMENT="Tracks calendar subscriptions">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true"/>
        <FIELD NAME="user_id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="user id"/>
        <FIELD NAME="caltype" TYPE="char" LENGTH="31" NOTNULL="true" SEQUENCE="false" COMMENT="the type of calendar - site,user,course,group"/>
        <FIELD NAME="caltypeid" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="the id of the entity the calendar is for, i.e. courseid"/>
        <FIELD NAME="o365calid" TYPE="char" LENGTH="255" NOTNULL="true" SEQUENCE="false" COMMENT="the ID of the o365 calendar to sync to"/>
        <FIELD NAME="isprimary" TYPE="int" LENGTH="1" NOTNULL="true" DEFAULT="1" SEQUENCE="false" COMMENT="Is the o365 calendar the default."/>
        <FIELD NAME="syncbehav" TYPE="char" LENGTH="255" NOTNULL="true" SEQUENCE="false" COMMENT="the sync behavior"/>
        <FIELD NAME="timecreated" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="time subscription created"/>
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id"/>
      </KEYS>
      <INDEXES>
        <INDEX NAME="usrcal" UNIQUE="true" FIELDS="user_id, caltype, caltypeid"/>
      </INDEXES>
    </TABLE>
    <TABLE NAME="local_o365_connections" COMMENT="Stores information on which accounts are connected.">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true"/>
        <FIELD NAME="muserid" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="moodle user id"/>
        <FIELD NAME="entraidupn" TYPE="char" LENGTH="255" NOTNULL="true" SEQUENCE="false" COMMENT="entra ID upn"/>
        <FIELD NAME="uselogin" TYPE="int" LENGTH="1" NOTNULL="true" DEFAULT="0" SEQUENCE="false" COMMENT="whether the connection uses login"/>
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id"/>
      </KEYS>
      <INDEXES>
        <INDEX NAME="muserid" UNIQUE="true" FIELDS="muserid"/>
        <INDEX NAME="entraidupn" UNIQUE="true" FIELDS="entraidupn"/>
      </INDEXES>
    </TABLE>
    <TABLE NAME="local_o365_token" COMMENT="Stores tokens.">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true"/>
        <FIELD NAME="user_id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="user id"/>
        <FIELD NAME="scope" TYPE="text" NOTNULL="true" SEQUENCE="false" COMMENT="token scope"/>
        <FIELD NAME="tokenresource" TYPE="char" LENGTH="127" NOTNULL="true" SEQUENCE="false" COMMENT="token resource"/>
        <FIELD NAME="token" TYPE="text" NOTNULL="true" SEQUENCE="false" COMMENT="token"/>
        <FIELD NAME="expiry" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="token expiry"/>
        <FIELD NAME="refreshtoken" TYPE="text" NOTNULL="true" SEQUENCE="false" COMMENT="refresh token"/>
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id"/>
      </KEYS>
      <INDEXES>
        <INDEX NAME="usrresscp" UNIQUE="false" FIELDS="user_id, tokenresource"/>
      </INDEXES>
    </TABLE>
    <TABLE NAME="local_o365_objects" COMMENT="Stores information about Microsoft 365 objects.">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true"/>
        <FIELD NAME="type" TYPE="char" LENGTH="255" NOTNULL="true" SEQUENCE="false" COMMENT="the type of object this record represents"/>
        <FIELD NAME="subtype" TYPE="char" LENGTH="255" NOTNULL="true" SEQUENCE="false" COMMENT="the subtype of object this record represents"/>
        <FIELD NAME="objectid" TYPE="char" LENGTH="255" NOTNULL="true" SEQUENCE="false" COMMENT="o365 object id"/>
        <FIELD NAME="moodleid" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="corresponding moodle id"/>
        <FIELD NAME="o365name" TYPE="text" NOTNULL="true" SEQUENCE="false" COMMENT="identifiable name in o365"/>
        <FIELD NAME="tenant" TYPE="char" LENGTH="255" NOTNULL="true" SEQUENCE="false" COMMENT="O365 tenant empty is host"/>
        <FIELD NAME="metadata" TYPE="text" NOTNULL="false" SEQUENCE="false" COMMENT="additional metadata"/>
        <FIELD NAME="timecreated" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="time record created"/>
        <FIELD NAME="timemodified" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="time record modified"/>
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id"/>
      </KEYS>
      <INDEXES>
        <INDEX NAME="moodleid" UNIQUE="false" FIELDS="moodleid"/>
        <INDEX NAME="objectid" UNIQUE="false" FIELDS="objectid"/>
      </INDEXES>
    </TABLE>
    <TABLE NAME="local_o365_appassign" COMMENT="Stores information on which accounts are assigned to the app.">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true"/>
        <FIELD NAME="muserid" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="moodle user id"/>
        <FIELD NAME="assigned" TYPE="int" LENGTH="1" NOTNULL="true" DEFAULT="0" SEQUENCE="false" COMMENT="Is assigned to application"/>
        <FIELD NAME="photoid" TYPE="char" LENGTH="255" NOTNULL="true" SEQUENCE="false" COMMENT="photo etag id"/>
        <FIELD NAME="photoupdated" TYPE="int" LENGTH="10" NOTNULL="false" SEQUENCE="false" COMMENT="last time photo was updated"/>
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id"/>
      </KEYS>
      <INDEXES>
        <INDEX NAME="muserid" UNIQUE="true" FIELDS="muserid"/>
      </INDEXES>
    </TABLE>
    <TABLE NAME="local_o365_matchqueue" COMMENT="Stores the queue of users waiting to be matched.">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true"/>
        <FIELD NAME="musername" TYPE="char" LENGTH="255" NOTNULL="true" SEQUENCE="false" COMMENT="moodle username"/>
        <FIELD NAME="o365username" TYPE="char" LENGTH="255" NOTNULL="true" SEQUENCE="false" COMMENT="o365 username"/>
        <FIELD NAME="openidconnect" TYPE="int" LENGTH="1" NOTNULL="true" DEFAULT="0" SEQUENCE="false"/>
        <FIELD NAME="completed" TYPE="int" LENGTH="1" NOTNULL="true" DEFAULT="0" SEQUENCE="false" COMMENT="whether this was completed successfully"/>
        <FIELD NAME="errormessage" TYPE="text" NOTNULL="true" SEQUENCE="false" COMMENT="if there was an error, stores the error message"/>
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id"/>
      </KEYS>
      <INDEXES>
        <INDEX NAME="musername" UNIQUE="false" FIELDS="musername"/>
        <INDEX NAME="completed" UNIQUE="false" FIELDS="completed"/>
        <INDEX NAME="o365username" UNIQUE="false" FIELDS="o365username"/>
      </INDEXES>
    </TABLE>
    <TABLE NAME="local_o365_calsettings" COMMENT="Tracks user level Outlook calendar sync settings">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true"/>
        <FIELD NAME="user_id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="user id"/>
        <FIELD NAME="o365calid" TYPE="char" LENGTH="255" NOTNULL="true" SEQUENCE="false" COMMENT="the ID of the o365 calendar to sync to"/>
        <FIELD NAME="timecreated" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false" COMMENT="time subscription created"/>
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id"/>
      </KEYS>
      <INDEXES>
        <INDEX NAME="usrcal" UNIQUE="true" FIELDS="user_id"/>
      </INDEXES>
    </TABLE>
    <TABLE NAME="local_o365_teams_cache" COMMENT="Stores Teams cache">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true"/>
        <FIELD NAME="objectid" TYPE="char" LENGTH="36" NOTNULL="true" SEQUENCE="false"/>
        <FIELD NAME="name" TYPE="char" LENGTH="264" NOTNULL="false" SEQUENCE="false"/>
        <FIELD NAME="description" TYPE="text" NOTNULL="false" SEQUENCE="false"/>
        <FIELD NAME="url" TYPE="text" NOTNULL="false" SEQUENCE="false"/>
        <FIELD NAME="locked" TYPE="int" LENGTH="1" NOTNULL="false" SEQUENCE="false"/>
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id"/>
      </KEYS>
      <INDEXES>
        <INDEX NAME="objectid" UNIQUE="false" FIELDS="objectid"/>
      </INDEXES>
    </TABLE>
    <TABLE NAME="local_o365_groups_cache" COMMENT="Stores Groups cache. This is primarily used for cohort sync, but is used to clean up groups in local_o365_objects and teams caches too">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true"/>
        <FIELD NAME="objectid" TYPE="char" LENGTH="36" NOTNULL="true" SEQUENCE="false"/>
        <FIELD NAME="name" TYPE="char" LENGTH="256" NOTNULL="false" SEQUENCE="false"/>
        <FIELD NAME="description" TYPE="text" NOTNULL="false" SEQUENCE="false"/>
        <FIELD NAME="not_found_since" TYPE="int" LENGTH="10" NOTNULL="true" DEFAULT="0" SEQUENCE="false"/>
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id"/>
      </KEYS>
      <INDEXES>
        <INDEX NAME="objectid" UNIQUE="false" FIELDS="objectid"/>
        <INDEX NAME="not_found_since" UNIQUE="false" FIELDS="not_found_since"/>
      </INDEXES>
    </TABLE>
    <TABLE NAME="local_o365_course_request" COMMENT="Table to store course requests for o365">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true"/>
        <FIELD NAME="requestid" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="false"/>
        <FIELD NAME="teamoid" TYPE="char" LENGTH="255" NOTNULL="true" SEQUENCE="false"/>
        <FIELD NAME="teamname" TYPE="char" LENGTH="255" NOTNULL="true" SEQUENCE="false"/>
        <FIELD NAME="courseshortname" TYPE="char" LENGTH="255" NOTNULL="true" SEQUENCE="false"/>
        <FIELD NAME="requeststatus" TYPE="int" LENGTH="2" NOTNULL="true" DEFAULT="0" SEQUENCE="false"/>
        <FIELD NAME="courseid" TYPE="int" LENGTH="10" NOTNULL="false" DEFAULT="0" SEQUENCE="false"/>
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id"/>
      </KEYS>
      <INDEXES>
        <INDEX NAME="requeststatus" UNIQUE="false" FIELDS="requeststatus"/>
      </INDEXES>
    </TABLE>
  </TABLES>
</XMLDB>
