<?php
// This file is part of Moodle - http://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.

namespace local_kid\local;

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

/**
 * Read-only, filtered/paginated access to `local_kid_journal` for
 * `pages/journal.php`. Never writes — {@see journal_service::observe()} is
 * the only write path for this table.
 */
class journal_repository {

    /** @var string[] Sortable columns, all real fields on local_kid_journal — never a raw user-supplied column name. */
    private const SORTABLE = ['timecreated', 'userid', 'domain', 'action'];

    /**
     * @param array $filters possible keys: userid, promotionid, domain, action, studentquery, datefrom, dateto (all optional)
     * @param string $sort one of SORTABLE
     * @param string $dir 'ASC'|'DESC'
     * @return array{rows: \stdClass[], total: int}
     */
    public static function get_filtered(array $filters, string $sort, string $dir, int $page, int $perpage): array {
        global $DB;

        [$where, $params] = self::build_where($filters);
        $sort = in_array($sort, self::SORTABLE, true) ? $sort : 'timecreated';
        $dir = $dir === 'ASC' ? 'ASC' : 'DESC';

        $countsql = "SELECT COUNT(1)
                       FROM {local_kid_journal} j
                       LEFT JOIN {local_kid_student} s ON s.id = j.studentid
                       LEFT JOIN {user} su ON su.id = s.userid
                      WHERE $where";
        $total = (int) $DB->count_records_sql($countsql, $params);

        $sql = "SELECT j.*, u.firstname AS authorfirstname, u.lastname AS authorlastname,
                       su.firstname AS studentfirstname, su.lastname AS studentlastname,
                       p.name AS promotionname
                  FROM {local_kid_journal} j
                  JOIN {user} u ON u.id = j.userid
                  LEFT JOIN {local_kid_student} s ON s.id = j.studentid
                  LEFT JOIN {user} su ON su.id = s.userid
                  LEFT JOIN {local_kid_promotion} p ON p.id = j.promotionid
                 WHERE $where
                 ORDER BY j.$sort $dir, j.id $dir";

        $rows = $DB->get_records_sql($sql, $params, $page * $perpage, $perpage);

        return ['rows' => array_values($rows), 'total' => $total];
    }

    /**
     * @return array{0: string, 1: array}
     */
    private static function build_where(array $filters): array {
        global $DB;

        $conditions = ['1=1'];
        $params = [];

        if (!empty($filters['userid'])) {
            $conditions[] = 'j.userid = :userid';
            $params['userid'] = (int) $filters['userid'];
        }
        if (!empty($filters['promotionid'])) {
            $conditions[] = 'j.promotionid = :promotionid';
            $params['promotionid'] = (int) $filters['promotionid'];
        }
        if (!empty($filters['domain'])) {
            $conditions[] = 'j.domain = :domain';
            $params['domain'] = $filters['domain'];
        }
        if (!empty($filters['action'])) {
            $conditions[] = 'j.action = :action';
            $params['action'] = $filters['action'];
        }
        if (!empty($filters['datefrom'])) {
            $conditions[] = 'j.timecreated >= :datefrom';
            $params['datefrom'] = (int) $filters['datefrom'];
        }
        if (!empty($filters['dateto'])) {
            $conditions[] = 'j.timecreated <= :dateto';
            $params['dateto'] = (int) $filters['dateto'];
        }
        if (!empty($filters['studentquery'])) {
            $like1 = $DB->sql_like('su.firstname', ':sq1', false, false);
            $like2 = $DB->sql_like('su.lastname', ':sq2', false, false);
            $conditions[] = "($like1 OR $like2)";
            $needle = '%' . $DB->sql_like_escape($filters['studentquery']) . '%';
            $params['sq1'] = $needle;
            $params['sq2'] = $needle;
        }

        return [implode(' AND ', $conditions), $params];
    }

    /**
     * @return array userid => full name, only authors who actually have a journal row.
     */
    public static function get_distinct_authors(): array {
        global $DB;

        $sql = "SELECT DISTINCT u.id, u.firstname, u.lastname
                  FROM {local_kid_journal} j
                  JOIN {user} u ON u.id = j.userid
                 ORDER BY u.lastname, u.firstname";
        $out = [];
        foreach ($DB->get_records_sql($sql) as $u) {
            $out[(int) $u->id] = fullname($u);
        }
        return $out;
    }

    /**
     * @return string[] action values actually present, for the filter dropdown.
     */
    public static function get_distinct_actions(): array {
        global $DB;
        $actions = $DB->get_records_sql('SELECT DISTINCT action FROM {local_kid_journal} ORDER BY action');
        return array_keys($actions);
    }
}
