<?php
// This file is part of the Studyplan plugin for Moodle
//
// 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/>.

/**
 * Model class for study plan
 * @package    local_treestudyplan
 * @copyright  2023 P.M. Kuipers
 * @license    https://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
 */

namespace local_treestudyplan;

use DateInterval;
use core_external\external_api;
use core_external\external_function_parameters;
use core_external\external_description;
use core_external\external_value;
use core_external\external_multiple_structure;
use core_external\external_single_structure;
use core\context;
use core\context\system as context_system;
use core\context\course as context_course;
use core\context\coursecat as context_coursecat;
use local_treestudyplan\local\helpers\formatter;

defined('MOODLE_INTERNAL') || die();

require_once($CFG->libdir.'/externallib.php');
require_once($CFG->libdir.'/filelib.php');
/**
 * Model class for study plan
 */
class studyplan {

    /** @var string */
    const TABLE = "local_treestudyplan";
    /** @var string */
    const TABLE_COACH = "local_treestudyplan_coach";

    /**
     * Cache retrieved studyitems in this session
     * @var array */
    private static $cache = [];

    /**
     * Cache retrieved pages for the studyplan in this session
     * @var array */
    private $pagecache = [];

    /**
     * Holds database record
     * @var stdClass
     */
    private $r;
    /** @var int */
    private $id;
    /** @var aggregator */
    private $aggregator;

    /**
     * Hold context object once retrieved.
     * @var \context
     */
    private $context = null;
    /**
     * Cache lookup of linked users (saves queries).
     * @var int[]
     */
    private $linkeduserids = null;

    /**
     * Return configured aggregator for this studyplan
     */
    public function aggregator(): aggregator {
        return $this->aggregator;
    }

    /**
     * Find record in database and return management object
     * Cache objects to avoid multiple creation events in one session.
     * @param int $id Id of database record
     */
    public static function find_by_id($id): self {
        if (!array_key_exists($id, self::$cache)) {
            self::$cache[$id] = new self($id);
        }
        return self::$cache[$id];
    }

    /**
     * Construct new instance from DB record
     * @param int $id Id of database record
     */
    private function __construct($id) {
        global $DB;
        $this->id = $id;
        $this->r = $DB->get_record(self::TABLE, ['id' => $id], "*", MUST_EXIST);

        $this->aggregator = aggregator::create_or_default($this->r->aggregation, $this->r->aggregation_config);
    }

    /**
     * Return database identifier
     * @return int
     */
    public function id() {
        return $this->id;
    }

    /**
     * Return short name
     * @return string
     */
    public function shortname() {
        return $this->r->shortname;
    }

    /**
     * Return idnumber
     * @return string
     */
    public function idnumber() {
        return $this->r->idnumber;
    }

    /**
     * Return full name
     * @return string
     */
    public function name() {
        return $this->r->name;
    }

    /**
     * True if studyplan is suspended
     * @return string
     */
    public function suspended() {
        return boolval($this->r->suspended);
    }

    /**
     * Determine earliest start date of a page
     * @return \DateTime|null
     */
    public function startdate() {
        $date = null;
        foreach ($this->pages() as $p) {
            if (!isset($date) || $p->startdate() < $date) {
                $date = $p->startdate();
            }
        }
        return $date;
    }

    /**
     * Determine studyplan icon
     * @return string Url of icon
     */
    private function icon() {
        global $CFG;
        $fs = \get_file_storage();
        if (is_object($fs)) {
            // Returns an array of `stored_file` instances.
            $files = $fs->get_area_files(context_system::instance()->id, 'local_treestudyplan', 'icon', $this->id);

            if (count($files) > 0) {
                $file = array_shift($files);

                if (is_object($file) && $file->get_filename() == ".") {
                    // Get next file if the first is the directory itself.
                    $file = array_shift($files);
                }

                if (is_object($file)) {
                    $url = \moodle_url::make_pluginfile_url(
                        $file->get_contextid(),
                        $file->get_component(),
                        $file->get_filearea(),
                        $file->get_itemid(),
                        $file->get_filepath(),
                        $file->get_filename(),
                        false                     // Do not force download of the file.
                    );
                    return $url->out();
                }
            }
        }
        // Try the configured default in settings.
        $defaulticon = get_config('local_treestudyplan', 'defaulticon');
        if (empty($defaulticon)) {
            // Fall back to the standard (ugly) default image.
            $url = new \moodle_url($CFG->wwwroot . "/local/treestudyplan/pix/default_icon.png");
        } else {
            $url = \moodle_url::make_pluginfile_url(
                        context_system::instance()->id,
                        'local_treestudyplan',
                        'defaulticon',
                        0,
                        "/",
                        $defaulticon
                    );
        }
        return $url->out();
    }

    /**
     * Return description with all file references resolved
     */
    public function description() {
        $text = \file_rewrite_pluginfile_urls(
            // The content of the text stored in the database.
            $this->r->description,
            // The pluginfile URL which will serve the request.
            'pluginfile.php',

            // The combination of contextid / component / filearea / itemid.
            // Form the virtual bucket that file are stored in.
            context_system::instance()->id, // System instance is always used for this.
            'local_treestudyplan',
            'studyplan',
            $this->id
        );
        return $text;
    }
    /**
     * Return the studyplan pages associated with this plan
     * @param bool $refresh Set to true to force a refresh of the pages
     * @return studyplanpage[]
     */
    public function pages($refresh=false): array {
        if (((bool)$refresh) || empty($this->pagecache)) {
            $this->pagecache = studyplanpage::find_studyplan_children($this);
        }
        return $this->pagecache;
    }

    /**
     * Return the context the studyplan is associated to
     * @return \context
     */
    public function context(): context {
        global $DB;
        if (!isset($this->context)) {
            try {
                $this->context = contextinfo::context_by_id($this->r->context_id);
            } catch (\dml_missing_record_exception $x) {
                /* The associated context cannot be found.
                   Probably the category was removed, but the studyplan was not.
                   Revert the studyplan back to the system context to avoid lost studyplans.
                   Note that the daily cleanup task calls this function to make sure
                   studyplans without a valid context are not lost.
                */
                $this->context = context_system::instance();
                $this->r->context_id = $this->context->id;
                $DB->update_record(self::TABLE, $this->r);
            }
        }
        return $this->context;
    }

    /**
     * Webservice structure for basic info
     * @param int $value Webservice requirement constant
     */
    public static function simple_structure($value = VALUE_REQUIRED): external_description {
        return new external_single_structure([
            "id" => new external_value(PARAM_INT, 'id of studyplan'),
            "userid" => new external_value(PARAM_INT, 'id of user the plan is shown for', VALUE_OPTIONAL),
            "name" => new external_value(PARAM_TEXT, 'name of studyplan'),
            "shortname" => new external_value(PARAM_TEXT, 'shortname of studyplan'),
            "display" => new external_single_structure([
                "name" => new external_value(PARAM_RAW, 'full name of studyplan (filtered)'),
                "shortname" => new external_value(PARAM_RAW, 'short name of studyplan (filtered)'),
                "idnumber" => new external_value(PARAM_RAW, 'idnumber of curriculum (filtered)'),
                "description" => new external_value(PARAM_RAW, 'description of studyplan (filtered'),
            ], "display names with filters applied"),
            "idnumber" => new external_value(PARAM_TEXT, 'idnumber of curriculum'),
            "context_id" => new external_value(PARAM_INT, 'context_id of studyplan'),
            "description" => new external_value(PARAM_RAW, 'description of studyplan'),
            "descriptionformat" => new external_value(PARAM_INT, 'description format'),
            "icon" => new external_value(PARAM_RAW, 'icon for this plan'),
            "aggregation" => new external_value(PARAM_TEXT, 'selected aggregator'),
            "aggregation_config" => new external_value(PARAM_TEXT, 'config string for aggregator'),
            "aggregation_info" => aggregator::basic_structure(),
            "pages" => new external_multiple_structure(studyplanpage::simple_structure(), 'pages'),
            "progress" => new external_value(PARAM_FLOAT, "fraction of completed modules", VALUE_OPTIONAL),
            "amteaching" => new external_value(
                                PARAM_BOOL,
                                "Current user is teaching one or more courses in this studyplan",
                                VALUE_OPTIONAL
                            ),
            "suspended" => new external_value(PARAM_BOOL, 'if studyplan is suspended', VALUE_OPTIONAL),
        ], 'Basic studyplan info', $value);
    }

    /**
     * Webservice model for basic info
     * @param int|null $userid Optional id of user, so progress/teaching info for user can be included
     * @return array Webservice data model
     */
    public function simple_model($userid=null) {
        $pages = [];
        foreach ($this->pages() as $p) {
            $pages[] = $p->simple_model();
        }

        $model = [
            'id' => $this->r->id,
            'name' => $this->r->name,
            'shortname' => $this->r->shortname,
            'display' => [
                'name' => formatter::format_string($this->r->name),
                'shortname' => formatter::format_string($this->r->shortname),
                'idnumber' => formatter::format_string(($this->r->idnumber)),
                'description' => formatter::format_text($this->description()),
            ],
            'idnumber' => $this->r->idnumber,
            'context_id' => $this->context()->id,
            'description' => $this->description(),
            'descriptionformat' => $this->r->descriptionformat,
            'icon' => $this->icon(),
            'aggregation' => $this->r->aggregation,
            'aggregation_config' => $this->aggregator->config_string(),
            'aggregation_info' => $this->aggregator->basic_model(),
            'pages' => $pages,
            'suspended' => boolval($this->r->suspended),
        ];
        if (isset($userid)) {
            $model["userid"] = $userid;
            $model["progress"] = $this->scanuserprogress($userid);
            $model['amteaching'] = teachingfinder::is_teaching_studyplan($this, $userid);
        }
        return $model;
    }

    /**
     * Webservice model for basic info in coach mode
     * @return array Webservice data model
     */
    public function simple_model_coach() {
        $model = $this->simple_model();
        $users = $this->find_linked_userids();
        $sum = 0;
        foreach ($users as $uid) {
            $sum += $this->scanuserprogress($uid);
        }

        $model["progress"] = $sum / count($users);

        return $model;
    }

    /**
     * Webservice structure for editor info
     * @param int $value Webservice requirement constant
     */
    public static function editor_structure($value = VALUE_REQUIRED): external_description {
        return new external_single_structure([
            "id" => new external_value(PARAM_INT, 'id of studyplan'),
            "name" => new external_value(PARAM_TEXT, 'name of studyplan'),
            "shortname" => new external_value(PARAM_TEXT, 'shortname of studyplan'),
            "display" => new external_single_structure([
                "name" => new external_value(PARAM_RAW, 'full name of studyplan (filtered)'),
                "shortname" => new external_value(PARAM_RAW, 'short name of studyplan (filtered)'),
                "idnumber" => new external_value(PARAM_RAW, 'idnumber of curriculum (filtered)'),
                "description" => new external_value(PARAM_RAW, 'description of studyplan (filtered'),
            ], "display names with filters applied"),
            "idnumber" => new external_value(PARAM_TEXT, 'idnumber of curriculum'),
            "description" => new external_value(PARAM_RAW, 'description of studyplan'),
            "descriptionformat" => new external_value(PARAM_INT, 'description format'),
            "icon" => new external_value(PARAM_RAW, 'icon for this plan'),
            "context_id" => new external_value(PARAM_INT, 'context_id of studyplan'),
            "aggregation" => new external_value(PARAM_TEXT, 'selected aggregator'),
            "aggregation_config" => new external_value(PARAM_TEXT, 'config string for aggregator'),
            "aggregation_info" => aggregator::basic_structure(),
            "pages" => new external_multiple_structure(studyplanpage::editor_structure()),
            "advanced" => new external_single_structure([
                "force_scales" => new external_single_structure([
                    "scales" => new external_multiple_structure(new external_single_structure([
                            "id" => new external_value(PARAM_INT, 'id of scale'),
                            "name" => new external_value(PARAM_TEXT, 'name of scale'),
                        ])),
                    ], "Scale forcing on stuff", VALUE_OPTIONAL),
                ], "Advanced features available", VALUE_OPTIONAL),
            "suspended" => new external_value(PARAM_BOOL, 'if studyplan is suspended', VALUE_OPTIONAL),
        ], 'Studyplan full structure', $value);
    }

    /**
     * Webservice model for editor info
     * @return array Webservice data model
     */
    public function editor_model() {
        $model = [
            'id' => $this->r->id,
            'name' => $this->r->name,
            'shortname' => $this->r->shortname,
            'display' => [
                'name' => formatter::format_string($this->r->name),
                'shortname' => formatter::format_string($this->r->shortname),
                'idnumber' => formatter::format_string(($this->r->idnumber)),
                'description' => formatter::format_text($this->description()),
            ],
            'idnumber' => $this->r->idnumber,
            'description' => $this->description(),
            'descriptionformat' => $this->r->descriptionformat,
            'icon' => $this->icon(),
            'context_id' => $this->context()->id,
            "aggregation" => $this->r->aggregation,
            "aggregation_config" => $this->aggregator->config_string(),
            'aggregation_info' => $this->aggregator->basic_model(),
            'pages' => [],
            'suspended' => boolval($this->r->suspended),
            'advanced' => [],
        ];

        foreach ($this->pages() as $p) {
            $model['pages'][] = $p->editor_model();
        }

        if (has_capability('local/treestudyplan:forcescales', context_system::instance())) {


            // Get a list of available scales.
            $scales = array_map( function($scale) {
                return [ "id" => $scale->id, "name" => $scale->name ];
            }, \grade_scale::fetch_all(['courseid' => 0]) );

            $model['advanced']['force_scales'] = [
                'scales' => $scales,
            ];
        }

        return $model;
    }

    /**
     * Webservice structure for teacher info
     * @param int $value Webservice requirement constant
     */
    public static function teacher_structure($value = VALUE_REQUIRED): external_description {
        return new external_single_structure([
            "id" => new external_value(PARAM_INT, 'id of studyplan'),
            "name" => new external_value(PARAM_TEXT, 'name of studyplan'),
            "shortname" => new external_value(PARAM_TEXT, 'shortname of studyplan'),
            "display" => new external_single_structure([
                "name" => new external_value(PARAM_RAW, 'full name of studyplan (filtered)'),
                "shortname" => new external_value(PARAM_RAW, 'short name of studyplan (filtered)'),
                "idnumber" => new external_value(PARAM_RAW, 'idnumber of curriculum (filtered)'),
                "description" => new external_value(PARAM_RAW, 'description of studyplan (filtered'),
            ], "display names with filters applied"),
            "idnumber" => new external_value(PARAM_TEXT, 'idnumber of curriculum'),
            "description" => new external_value(PARAM_RAW, 'description of studyplan'),
            "descriptionformat" => new external_value(PARAM_INT, 'description format'),
            "icon" => new external_value(PARAM_RAW, 'icon for this plan'),
            "context_id" => new external_value(PARAM_INT, 'context_id of studyplan'),
            "aggregation" => new external_value(PARAM_TEXT, 'selected aggregator'),
            "aggregation_config" => new external_value(PARAM_TEXT, 'config string for aggregator'),
            "aggregation_info" => aggregator::basic_structure(),
            "pages" => new external_multiple_structure(studyplanpage::teacher_structure()),
            "suspended" => new external_value(PARAM_BOOL, 'if studyplan is suspended', VALUE_OPTIONAL),
        ], 'Studyplan full structure', $value);
    }

    /**
     * Webservice model for teacher info
     * @return array Webservice data model
     */
    public function teacher_model() {
        $model = [
            'id' => $this->r->id,
            'name' => $this->r->name,
            'shortname' => $this->r->shortname,
            'display' => [
                'name' => formatter::format_string($this->r->name),
                'shortname' => formatter::format_string($this->r->shortname),
                'idnumber' => formatter::format_string(($this->r->idnumber)),
                'description' => formatter::format_text($this->description()),
            ],
            'idnumber' => $this->r->idnumber,
            'description' => $this->description(),
            'descriptionformat' => $this->r->descriptionformat,
            'icon' => $this->icon(),
            'context_id' => $this->context()->id,
            "aggregation" => $this->r->aggregation,
            "aggregation_config" => $this->aggregator->config_string(),
            'aggregation_info' => $this->aggregator->basic_model(),
            'pages' => [],
            'suspended' => boolval($this->r->suspended),
        ];

        foreach ($this->pages() as $p) {
            $model['pages'][] = $p->teacher_model();
        }

        return $model;
    }

    /**
     * Add a new studyplan
     * @param array $fields Properties for study line ['name', 'shortname', 'description', 'idnumber', 'context_id', 'aggregation',
     *                      'aggregation_config', 'periods', 'startdate', 'enddate'];
     * @param bool $bare If true, do not create a first page with copy of studyplan names
     */
    public static function add($fields, $bare = false): self {
        global $DB;

        $addable = ['name', 'shortname', 'description', 'descriptionformat',
                    'idnumber', 'context_id', 'aggregation', 'aggregation_config'];
        $info = ['enddate' => null, "template" => 0, "suspended" => 0];
        foreach ($addable as $f) {
            if (array_key_exists($f, $fields)) {
                $info[$f] = $fields[$f];
            }
        }
        $id = $DB->insert_record(self::TABLE, $info);
        $plan = self::find_by_id($id); // Make sure the new studyplan is immediately cached.

        // Add a single page and initialize it with placeholder data.
        // This makes it easier to create a new study plan.
        // On import, adding an empty page messes things up , so we have an option to skip this....
        if (!$bare) {

            $pageaddable = ['name', 'shortname', 'description', 'descriptionformat', 'periods', 'startdate', 'enddate'];
            $pageinfo = ['studyplan_id' => $id];
            foreach ($pageaddable as $f) {
                if (array_key_exists($f, $fields)) {
                    if ($f == "name") {
                        $pageinfo["fullname"] = $fields[$f];
                    } else {
                        $pageinfo[$f] = $fields[$f];
                    }
                }
            }

            $page = studyplanpage::add($pageinfo, $bare);
            $plan->page_cache = [$page];
        }

        return $plan;
    }

    /**
     * Edit study line properties
     * @param array $fields Changed properties for study line ['name', 'shortname', 'description', 'idnumber',
     *                      'context_id', 'aggregation', 'aggregation_config', 'suspended']
     */
    public function edit($fields): self {
        global $DB;
        $editable = [
            'name',
            'shortname',
            'description',
            'descriptionformat',
            'idnumber',
            'context_id',
            'aggregation',
            'aggregation_config',
            'suspended',
            'template',
        ];
        $info = ['id' => $this->id ];
        foreach ($editable as $f) {
            if (array_key_exists($f, $fields)) {
                $info[$f] = $fields[$f];
            }
        }
        $DB->update_record(self::TABLE, $info);
        // Reload record after edit.
        $this->r = $DB->get_record(self::TABLE, ['id' => $this->id], "*", MUST_EXIST);

        // Reload the context...
        $this->context = null;
        $this->context();
        // Reload aggregator.
        $this->aggregator = aggregator::create_or_default($this->r->aggregation, $this->r->aggregation_config);

        return $this;
    }

    /**
     * Delete studyline
     * @param bool $force Force deletion even if study line contains items
     */
    public function delete($force = false): success {
        global $DB;

        if ($force) {
            $children = studyplanpage::find_studyplan_children($this);
            foreach ($children as $c) {
                $c->delete($force);
            }
        }

        if ($DB->count_records('local_treestudyplan_page', ['studyplan_id' => $this->id]) > 0) {
            return success::fail('cannot delete studyplan that still has pages');
        } else {
            // Delete any links to this studyplan before deleting the studyplan itself.
            $DB->delete_records("local_treestudyplan_coach", ["studyplan_id" => $this->id]);
            $DB->delete_records("local_treestudyplan_cohort", ["studyplan_id" => $this->id]);
            $DB->delete_records("local_treestudyplan_user", ["studyplan_id" => $this->id]);

            $DB->delete_records('local_treestudyplan', ['id' => $this->id]);
            return success::success();
        }
    }

    /**
     * Find all studyplans in a given context or the system context
     * @param int $contextid Optional contextid to search in. ANY context used if left empty
     * @return studyplan[]
     */
    public static function find_all($contextid = -1): array {
        global $DB;
        $list = [];

        if ($contextid <= 0) {
            $ids = $DB->get_fieldset_select(self::TABLE, "id", "");
        } else {
            if ($contextid == 1) {
                $contextid = 1;
                $where = "context_id <= :contextid OR context_id IS NULL";
            } else {
                $where = "context_id = :contextid";
            }
            $ids = $DB->get_fieldset_select(self::TABLE, "id", $where, ["contextid" => $contextid]);
        }

        foreach ($ids as $id) {
            $list[] = self::find_by_id($id);
        }
        return $list;
    }

    /**
     * Find all template studyplans in a given context or the system context
     * @param int $contextid Optional contextid to search in. ANY context used if left empty
     * @return studyplan[]
     */
    public static function find_template($contextid = -1): array {
        global $DB;
        $list = [];

        $templatewhere = "template = 1";
        if ($contextid <= 0) {
            $ids = $DB->get_fieldset_select(self::TABLE, "id", $templatewhere);
        } else {
            if ($contextid == 1) {
                $contextid = 1;
                $where = "context_id <= :contextid OR context_id IS NULL";
            } else {
                $where = "context_id = :contextid";
            }
            $ids = $DB->get_fieldset_select(self::TABLE, "id", "{$where} AND {$templatewhere}", ["contextid" => $contextid]);
        }

        foreach ($ids as $id) {
            $list[] = self::find_by_id($id);
        }
        return $list;
    }

    /**
     * Count all template studyplans in a given context or the system context
     * @param int $contextid Optional contextid to search in. ANY context used if left empty
     * @return int
     */
    public static function count_template($contextid = -1): int {
        global $DB;

        $templatewhere = "template = 1";
        if ($contextid <= 0) {
            return $DB->count_records_select(self::TABLE, $templatewhere);
        } else {
            if ($contextid == 1) {
                $contextid = 1;
                $where = "context_id <= :contextid OR context_id IS NULL";
            } else {
                $where = "context_id = :contextid";
            }
            return $DB->count_records_select(self::TABLE, "{$where} AND {$templatewhere}", ["contextid" => $contextid]);
        }
    }

    /**
     * Find all studyplans in a given context or the system context with a specific short name
     * (Used in generating random grades for development)
     * @param string $shortname Shortname to match
     * @param int $contextid Optional contextid to search in. All contexts searched if empty.
     * @return studyplan[]
     */
    public static function find_by_shortname($shortname, $contextid = 0): array {
        global $DB;
        $list = [];

        $where = "shortname = :shortname ";
        if ($contextid == 1) {
            $where .= " AND (context_id = :contextid OR context_id IS NULL)";
        } else if ($contextid > 1) {
            $where .= " AND context_id = :contextid";
        }

        $ids = $DB->get_fieldset_select(self::TABLE, "id", $where, ["shortname" => $shortname, "contextid" => $contextid]);
        foreach ($ids as $id) {
            $list[] = self::find_by_id($id);
        }
        return $list;
    }

    /**
     * Find all studyplans in a given context or the system context with a specific idnumber
     * @param string $idnumber IDNumber to match
     * @param int $contextid Optional contextid to search in. All contexts searched if empty.
     * @return studyplan[]
     */
    public static function find_by_idnumber($idnumber, $contextid = 0): array {
        global $DB;
        $list = [];

        $where = "idnumber = :idnumber ";
        if ($contextid == 1) {
            $where .= " AND (context_id = :contextid OR context_id IS NULL)";
        } else if ($contextid > 1) {
            $where .= " AND context_id = :contextid";
        }

        $ids = $DB->get_fieldset_select(self::TABLE, "id", $where, ["idnumber" => $idnumber, "contextid" => $contextid]);
        foreach ($ids as $id) {
            $list[] = self::find_by_id($id);
        }
        return $list;
    }

    /**
     * Find all studyplans in a given context or the system context with a specific full name
     * @param string $name Full name to match
     * @param int $contextid Optional contextid to search in. All contexts searched if empty.
     * @return studyplan[]
     */
    public static function find_by_fullname($name, $contextid = 0): array {
        global $DB;
        $list = [];

        $where = "name = :name ";
        if ($contextid == 1) {
            $where .= " AND (context_id = :contextid OR context_id IS NULL)";
        } else if ($contextid > 1) {
            $where .= " AND context_id = :contextid";
        }

        $ids = $DB->get_fieldset_select(self::TABLE, "id", $where, ["name" => $name, "contextid" => $contextid]);
        foreach ($ids as $id) {
            $list[] = self::find_by_id($id);
        }
        return $list;
    }

    /**
     * Find all studyplans for a given user
     * @param int $userid Id of the user to search for
     * @return studyplan[]
     */
    public static function find_for_user($userid): array {
        global $DB;

        $sql = "SELECT s.id FROM {local_treestudyplan} s
                INNER JOIN {local_treestudyplan_cohort} j ON j.studyplan_id = s.id
                INNER JOIN {cohort_members} cm ON j.cohort_id = cm.cohortid
                INNER JOIN {user} u ON cm.userid = u.id
                WHERE cm.userid = :userid AND u.deleted != 1";
        $cohortplanids = $DB->get_fieldset_sql($sql, ['userid' => $userid]);

        $sql = "SELECT s.id FROM {local_treestudyplan} s
                INNER JOIN {local_treestudyplan_user} j ON j.studyplan_id = s.id
                INNER JOIN {user} u ON j.user_id = u.id
                WHERE j.user_id = :userid AND u.deleted != 1";
        $userplanids = $DB->get_fieldset_sql($sql, ['userid' => $userid]);

        $plans = [];
        foreach ($cohortplanids as $id) {
            $plans[$id] = self::find_by_id($id);
        }
        foreach ($userplanids as $id) {
            if (!array_key_exists($id, $plans)) {
                $plans[$id] = self::find_by_id($id);
            }
        }

        return $plans;

    }

    /**
     * Check if a given user has associated studyplans
     * @param int $userid Id of the user to search for
     */
    public static function exist_for_user($userid): bool {
        global $DB;
        $count = 0;
        $sql = "SELECT COUNT(s.id) FROM {local_treestudyplan} s
                INNER JOIN {local_treestudyplan_cohort} j ON j.studyplan_id = s.id
                INNER JOIN {cohort_members} cm ON j.cohort_id = cm.cohortid
                INNER JOIN {user} u ON cm.userid = u.id
                WHERE cm.userid = :userid  AND u.deleted != 1";
        $count += $DB->count_records_sql($sql, ['userid' => $userid]);

        $sql = "SELECT COUNT(s.id) FROM {local_treestudyplan} s
                INNER JOIN {local_treestudyplan_user} j ON j.studyplan_id = s.id
                INNER JOIN {user} u ON j.user_id = u.id
                WHERE j.user_id = :userid AND u.deleted != 1";
        $count += $DB->count_records_sql($sql, ['userid' => $userid]);

        return ($count > 0);
    }

    /**
     * Retrieve the users linked to this studyplan.
     * @return \stdClass[] User objects
     */
    public function find_linked_users(): array {
        global $DB;

        $users = [];

        $uids = $this->find_linked_userids();

        foreach ($uids as $uid) {
            $users[] = $DB->get_record("user", ["id" => $uid]);
        }

        return $users;
    }

    /**
     * Retrieve the user id's of the users linked to this studyplan.
     * @return array of int (User Id)
     */
    public function find_linked_userids(): array {
        global $DB;

        if ($this->linkeduserids === null) {
            $uids = [];
            // First get directly linked userids.
            $sql = "SELECT j.user_id FROM {local_treestudyplan_user} j
                    INNER JOIN {user} u ON j.user_id = u.id
                    WHERE j.studyplan_id = :planid  AND u.deleted != 1";
            $ulist = $DB->get_fieldset_sql($sql, ['planid' => $this->id]);

            $uids = array_merge($uids, $ulist);

            // Next het users linked though cohort.
            $sql = "SELECT cm.userid FROM {local_treestudyplan_cohort} j
                    INNER JOIN {cohort_members} cm ON j.cohort_id = cm.cohortid
                    INNER JOIN {user} u ON cm.userid = u.id
                    WHERE j.studyplan_id = :planid AND u.deleted != 1";
            $ulist = $DB->get_fieldset_sql($sql, ['planid' => $this->id]);

            $uids = array_merge($uids, $ulist);

            $this->linkeduserids = array_unique($uids);
        }
        return $this->linkeduserids;
    }

    /**
     * Check if this studyplan is linked to a particular user
     * @param int|stdClass $user The userid or user record of the user
     */
    public function has_linked_user($user) {
        if (is_int($user)) {
            $userid = $user;
        } else {
            $userid = $user->id;
        }
        $uids = $this->find_linked_userids();
        if (in_array($userid, $uids)) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * Check if this studyplan is linked to a particular user
     * @param int|stdClass|null $user The userid or user record of the user Leave empty to check current user.
     */
    public function is_coach($user=null) {
        global $DB, $USER;

        if (! (\get_config("local_treestudyplan", "enablecoach"))) {
            // If coach role is not available, return false immediately.
            return false;
        }
        if ($user == null) {
            $user = $USER;
            $userid = $USER->id;
        } else if (is_numeric($user)) {
            $userid = intval($user);
        } else {
            $userid = $user->id;
        }
        $r = $DB->get_record(self::TABLE_COACH, ["studyplan_id" => $this->id, "user_id" => $userid]);

        if ($r && has_capability(associationservice::CAP_COACH, $this->context(), $userid)) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * Webservice structure for userinfo
     * @param int $value Webservice requirement constant
     */
    public static function user_structure($value = VALUE_REQUIRED): external_description {
        return new external_single_structure([
            "id" => new external_value(PARAM_INT, 'id of studyplan'),
            "userid" => new external_value(PARAM_INT, 'id of user the plan is shown for'),
            "name" => new external_value(PARAM_TEXT, 'name of studyplan'),
            "shortname" => new external_value(PARAM_TEXT, 'shortname of studyplan'),
            "display" => new external_single_structure([
                "name" => new external_value(PARAM_RAW, 'full name of studyplan (filtered)'),
                "shortname" => new external_value(PARAM_RAW, 'short name of studyplan (filtered)'),
                "idnumber" => new external_value(PARAM_RAW, 'idnumber of curriculum (filtered)'),
                "description" => new external_value(PARAM_RAW, 'description of studyplan (filtered'),
            ], "display names with filters applied"),
            "description" => new external_value(PARAM_RAW, 'description of studyplan'),
            "descriptionformat" => new external_value(PARAM_INT, 'description format'),
            "icon" => new external_value(PARAM_RAW, 'icon for this plan'),
            "progress" => new external_value(PARAM_FLOAT, "fraction of completed modules"),
            "idnumber" => new external_value(PARAM_TEXT, 'idnumber of curriculum'),
            "pages" => new external_multiple_structure(studyplanpage::user_structure()),
            "aggregation_info" => aggregator::basic_structure(),
        ], 'Studyplan with user info', $value);

    }

    /**
     * Scan user progress (completed modules) over all pages for a specific user
     * @param int $userid ID of user to check for
     * @return float Fraction of completion
     */
    private function scanuserprogress($userid) {
        $progress = 0;
        $pages = $this->pages();
        foreach ($pages as $p) {
            $prg = $p->scanuserprogress($userid);
            $progress += $prg;
        }
        // Now average it out over the amount of pages.
        if (count($pages) > 0) {
            return $progress / count($pages);
        } else {
            return 0;
        }
    }

    /**
     * Webservice model for user info
     * @param int $userid ID of user to check specific info for
     * @return array Webservice data model
     */
    public function user_model($userid) {
        $progress = $this->scanuserprogress($userid);
        if (is_nan($progress)) {
            $progress = 0;
        }
        $model = [
            'id' => $this->r->id,
            'userid' => $userid,
            'name' => $this->r->name,
            'shortname' => $this->r->shortname,
            'display' => [
                'name' => formatter::format_string($this->r->name),
                'shortname' => formatter::format_string($this->r->shortname),
                'idnumber' => formatter::format_string(($this->r->idnumber)),
                'description' => formatter::format_text($this->description()),
            ],
            'description' => $this->description(),
            'descriptionformat' => $this->r->descriptionformat,
            'icon' => $this->icon(),
            'idnumber' => $this->r->idnumber,
            'progress' => $progress,
            'pages' => [],
            'aggregation_info' => $this->aggregator->basic_model(),
        ];

        foreach ($this->pages() as $p) {
            $model['pages'][] = $p->user_model($userid);
        }
        return $model;
    }

    /**
     * Duplicate a studyplan by id
     * Function used by webservices and returns webservices model
     * @param int $planid Id if studyplan
     * @param string $name New fullname of studyplan
     * @param string $shortname New shortname of studyplan
     * @return array Simple webservices model of plan
     */
    public static function duplicate_plan($planid, $name, $shortname): array {
        $ori = self::find_by_id($planid);
        $new = $ori->duplicate($name, $shortname, $ori->context()->id);
        return $new->simple_model();
    }

    /**
     * Duplicate this studyplan
     * @param string $name New fullname of studyplan
     * @param string $shortname New shortname of studyplan
     * @param int $contextid Id of context for new plan
     * @param string|null $idnumber New idnumber (duplicate old one if left empty)
     * @param string|null $newstartdate If provided, all dates in the copy will be shifted so the plan starts at this date
     */
    public function duplicate($name, $shortname, $contextid, $idnumber=null, $newstartdate = null): self {
        // First duplicate the studyplan structure.
        $newplan = self::add([
            'name' => $name,
            'shortname' => $shortname,
            'idnumber' => ($idnumber ? $idnumber : $this->r->idnumber),
            'context_id' => $contextid,
            'description' => $this->r->description,
            'descriptionformat' => $this->r->descriptionformat,
            'aggregation' => $this->r->aggregation,
            'aggregation_config' => $this->r->aggregation_config,
        ], true);

        // Copy any files related to this studyplan.
        $areas = ["icon", "studyplan"];
        $fs = \get_file_storage();
        if (is_object($fs)) {
            foreach ($areas as $area) {
                $files = $fs->get_area_files(
                    context_system::instance()->id,
                    "local_treestudyplan",
                    $area,
                    $this->id()
                );
                foreach ($files as $file) {
                    $path = $file->get_filepath();
                    $filename = $file->get_filename();
                    if ($filename != ".") {
                        // Prepare new file info for the target file.
                        $fileinfo = [
                            'contextid' => context_system::instance()->id, // System context.
                            'component' => 'local_treestudyplan',           // Your component name.
                            'filearea'  => $area,                           // Area name.
                            'itemid'    => $newplan->id(),                  // Study plan id.
                            'filepath'  => $path,                           // Original file path.
                            'filename'  => $filename,                       // Original file name.
                        ];
                        // Copy existing file into new file.
                        $fs->create_file_from_storedfile($fileinfo, $file);
                    }
                }
            }
        }

        // Next, copy the studylines.
        $timeless = \get_config("local_treestudyplan", "timelessperiods");
        $oldstart = $this->startdate();
        if (!$timeless && $newstartdate && is_object($oldstart)) {
            $newstart = new \DateTime(date("Y-m-d", $newstartdate));
            $timeoffset = $oldstart->diff($newstart);
        } else {
            $timeoffset = new \DateInterval("P0D");
        }

        foreach ($this->pages() as $page) {
            $page->duplicate($newplan, $timeoffset);
        }

        return $newplan;
    }

    /**
     * Description of export structure for webservices
     */
    public static function export_structure(): external_description {
        return new external_single_structure([
            "format" => new external_value(PARAM_TEXT, 'format of studyplan export'),
            "content" => new external_value(PARAM_RAW, 'exported studyplan content'),
        ], 'Exported studyplan');
    }

    /**
     * Export this page into a json model
     * @return array
     */
    public function export_plan() {
        $model = $this->export_model();
        $json = json_encode([
            "type" => "studyplan",
            "version" => 2.0,
            "studyplan" => $model,
            ], \JSON_PRETTY_PRINT);
        return [ "format" => "application/json", "content" => $json];
    }

    /**
     * Export essential information for export
     * @return array information model
     */
    public function export_model() {
        $exportfiles = $this->export_files('studyplan');
        $iconfiles = $this->export_files('icon');
        $model = [
            'name' => $this->r->name,
            'shortname' => $this->r->shortname,
            'display' => [
                'name' => formatter::format_string($this->r->name),
                'shortname' => formatter::format_string($this->r->shortname),
                'idnumber' => formatter::format_string(($this->r->idnumber)),
                'description' => formatter::format_text($this->description()),
            ],
            'idnumber' => $this->r->idnumber,
            'description' => $this->r->description,
            'descriptionformat' => $this->r->descriptionformat,
            "aggregation" => $this->r->aggregation,
            "aggregation_config" => json_decode($this->aggregator->config_string()),
            'aggregation_info' => $this->aggregator->basic_model(),
            'pages' => $this->export_pages_model(),
            'files' => $exportfiles,
            'iconfiles' => $iconfiles,
        ];
        return $model;
    }

    /**
     * Export files from file storage
     * @param string $area Name of the file area to export
     * @return array information model
     */
    public function export_files($area) {
        $exportfiles = [];
        $fs = get_file_storage();
        if (is_object($fs)) {
            $files = $fs->get_area_files(
                context_system::instance()->id,
                'local_treestudyplan',
                $area,
                $this->id);
            foreach ($files as $file) {
                if ($file->get_filename() != ".") {
                    $contents = $file->get_content();
                    $exportfiles[] = [
                        "name" => $file->get_filename(),
                        "path" => $file->get_filepath(),
                        "content" => convert_uuencode($contents),
                    ];
                }
            }
        }
        return $exportfiles;
    }

    /**
     * Import previously exported files into the file storage
     * @param mixed $importfiles List of files to import from string in the format exported in export_model()
     * @param string $area Name of the file area to import
     */
    public function import_files($importfiles, $area) {
        $fs = get_file_storage();
        if (is_object($fs)) {
            foreach ($importfiles as $file) {
                if ($file['name'] != ".") {
                    $fileinfo = [
                        'contextid' => context_system::instance()->id,   // ID of the system context.
                        'component' => 'local_treestudyplan', // Your component name.
                        'filearea'  => $area,       // Usually = table name.
                        'itemid'    => $this->id,              // ID of the studyplanpage.
                        'filepath'  => $file["path"],         // Path of the file.
                        'filename'  => $file["name"],         // Name of the file..
                    ];

                    $fs->create_file_from_string($fileinfo, convert_uudecode($file["content"]));
                }
            }
        }
    }

    /**
     * Export all pages
     * @return array information model
     */
    public function export_pages_model() {
        $pages = [];
        foreach ($this->pages() as $p) {
            $pages[] = $p->export_model();
        }
        return $pages;
    }

    /**
     * Import studyplan from file contents
     * @param string $content String
     * @param string $format Format description
     * @param int $contextid The context to import into
     */
    public static function import_studyplan($content, $format = "application/json", $contextid = 1) {
        if ($format != "application/json") {
            return false;
        }

        $content = json_decode($content, true);
        if ($content["type"] == "studyplan" && $content["version"] >= 2.0) {

            $planmodel = $content["studyplan"];
            // Make sure the aggregation_config is re-encoded as json text.
            $planmodel["aggregation_config"] = json_encode( $planmodel["aggregation_config"]);

            // And make sure the context_id is set to the provided context for import.
            $planmodel["context_id"] = $contextid;

            // Create a new plan, based on the given parameters - this is the import studyplan part.
            $plan = self::add( $planmodel, true);

            // Import the files.
            if (isset( $planmodel['files']) && is_array( $planmodel['files'])) {
                $plan->import_files( $planmodel['files'], "studyplan");
            }
            // Import the icon.
            if (isset( $planmodel['iconfiles']) && is_array( $planmodel['iconfiles'])) {
                $plan->import_files( $planmodel['iconfiles'], "icon");
            }

            // Now import each page.
            return $plan->import_pages_model($planmodel["pages"]);

        } else {
            debugging("Invalid format and type: {$content['type']} version {$content['version']}");
            return false;
        }
    }

    /**
     * Import studyplan pages from file contents
     * @param string $content String
     * @param string $format Format description
     */
    public function import_pages($content, $format = "application/json") {
        if ($format != "application/json") {
            return false;
        }
        $content = json_decode($content, true);
        if ($content["version"] >= 2.0) {
            if ($content["type"] == "studyplanpage") {
                // Import single page from a studyplanpage (wrapped in array of one page).
                return $this->import_pages_model([$content["page"]]);
            } else if ($content["type"] == "studyplan") {
                // Import all pages from the studyplan.
                return $this->import_pages_model($content["studyplan"]["pages"]);
            }
        } else {
            return false;
        }
    }

    /**
     * Import pages from decoded array model
     * @param array $model Decoded array
     */
    protected function import_pages_model($model): bool {
        $this->pages(); // Make sure the page cache is initialized, since we will be adding to it.
        foreach ($model as $p) {
            $p["studyplan_id"] = $this->id();
            $page = studyplanpage::add($p);
            $this->page_cache[] = $page;
            $page->import_periods_model($p["perioddesc"]);
            $page->import_studylines_model($p["studylines"]);
            if ($p['files']) {
                $page->import_files($p["files"], 'studyplanpage');
            }
        }
        return true;
    }

    /**
     * Mark the studyplan as changed regarding courses and associated cohorts
     */
    public function mark_csync_changed() {
        global $DB;
        $DB->update_record(self::TABLE, ['id' => $this->id, "csync_flag" => 1]);
        // Manually set it in the cache, if something unexpected happened, an exception has already been thrown anyway.
        $this->r->csync_flag = 1;
    }

    /**
     * Clear the studyplan as changed regarding courses and associated cohorts
     */
    public function clear_csync_changed() {
        global $DB;
        $DB->update_record(self::TABLE, ['id' => $this->id, "csync_flag" => 0]);
        // Manually set it in the cache, if something unexpected happened, an exception has already been thrown anyway.
        $this->r->csync_flag = 0;
    }

    /**
     * Check if  the studyplan as changed regarding courses and associated cohorts
     */
    public function has_csync_changed(): bool {
        return ($this->r->csync_flag > 0) ? true : false;
    }

    /**
     * See if the specified course id is linked in this studyplan
     * @param int $courseid Id of course to check
     */
    public function course_linked($courseid): bool {
        global $DB;

        $sql = "SELECT COUNT(i.id)
                FROM {local_treestudyplan}
                INNER JOIN {local_treestudyplan_line} l ON p.id = l.studyplan_id
                 {local_treestudyplan_item} i ON l.id = i.line_id
                WHERE p.id = :planid
                   AND i.course_id = :courseid";
        $count = $DB->get_field_sql($sql, ["courseid" => $courseid, "planid" => $this->id]);

        return ($count > 0) ? true : false;
    }

    /**
     * Get all study lines linked to this plan (quickly)
     * Used for cohort enrolment cascading
     * @return studyline[]
     */
    public function get_all_studylines(): array {
        global $DB;

        $sql = "SELECT l.id
                FROM {local_treestudyplan} p
                INNER JOIN {local_treestudyplan_page} pg ON p.id = pg.studyplan_id
                INNER JOIN {local_treestudyplan_line} l ON pg.id = l.page_id
                WHERE p.id = :studyplan_id";
        $fields = $DB->get_fieldset_sql($sql, ["studyplan_id" => $this->id]);

        $list = [];
        foreach ($fields as $id) {
            $list[] = studyline::find_by_id($id);
        }
        return $list;
    }

    /**
     * List the cohort id's associated with this studyplan
     */
    public function get_linked_cohort_ids() {
        global $DB;

        $sql = "SELECT DISTINCT j.cohort_id FROM {local_treestudyplan_cohort} j
                WHERE j.studyplan_id = :studyplan_id";
        $fields = $DB->get_fieldset_sql($sql, ['studyplan_id' => $this->id]);
        return $fields;
    }

    /**
     * List the user id's explicitly associated with this studyplan
     * @return int[]
     */
    public function get_linked_user_ids(): array {
        global $DB;

        $sql = "SELECT DISTINCT j.user_id FROM {local_treestudyplan_user} j
                WHERE j.studyplan_id = :studyplan_id";
        $fields = $DB->get_fieldset_sql($sql, ['studyplan_id' => $this->id]);
        return $fields;
    }
    /**
     * See if the specified badge is linked in this studyplan
     * @param int $badgeid Badge id
     */
    public function badge_linked($badgeid): bool {
        global $DB;

        $sql = "SELECT COUNT(i.id)
                FROM {local_treestudyplan}
                INNER JOIN {local_treestudyplan_line} l ON p.id = l.studyplan_id
                INNER JOIN {local_treestudyplan_item} i ON l.id = i.line_id
                WHERE p.id = :planid
                   AND i.badge_id = :badgeid";
        $count = $DB->get_field_sql($sql, ["badgeid" => $badgeid, "planid" => $this->id]);

        return ($count > 0) ? true : false;
    }
}
