<?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 directory of active students across all
 * promotions, for `pages/annuaire.php`. One row per (student, promotion)
 * membership — never merged across promotions, since a dossier
 * (`fiche_builder`) is itself scoped to a single promotion.
 *
 * Never writes, never queries Moodle-native tables directly beyond the
 * {user} join already used throughout the plugin (e.g.
 * {@see promotion_repository::get_active_students()}) — the
 * promotion↔cohort link itself is resolved upstream by
 * {@see promotion_repository::diff_and_apply_membership()}, which already
 * keeps {local_kid_student} in sync with the native {cohort_members} table
 * in read-only fashion.
 */
class annuaire_repository {

    /** @var array<string, string> GET-friendly sort key => real SQL column. */
    private const SORTMAP = [
        'lastname' => 'u.lastname',
        'firstname' => 'u.firstname',
        'promotionname' => 'p.name',
    ];

    /**
     * @param array $filters possible keys: sifirst, silast, studentquery, promotionid (all optional)
     * @param string $sort one of self::SORTMAP keys
     * @param string $dir 'ASC'|'DESC'
     * @return array{rows: \stdClass[], total: int}
     */
    public static function search(array $filters, string $sort, string $dir, int $page, int $perpage): array {
        global $DB;

        [$where, $params] = self::build_where($filters);
        $sortcol = self::SORTMAP[$sort] ?? self::SORTMAP['lastname'];
        $dir = $dir === 'DESC' ? 'DESC' : 'ASC';

        $countsql = "SELECT COUNT(1)
                       FROM {local_kid_student} s
                       JOIN {user} u ON u.id = s.userid
                       JOIN {local_kid_promotion} p ON p.id = s.promotionid
                      WHERE $where";
        $total = (int) $DB->count_records_sql($countsql, $params);

        $sql = "SELECT s.id AS studentid, s.promotionid, u.id AS userid,
                       u.firstname, u.lastname, p.name AS promotionname
                  FROM {local_kid_student} s
                  JOIN {user} u ON u.id = s.userid
                  JOIN {local_kid_promotion} p ON p.id = s.promotionid
                 WHERE $where
                 ORDER BY $sortcol $dir, s.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 = ["s.status = 'active'", 'p.active = 1'];
        $params = [];

        if (!empty($filters['promotionid'])) {
            $conditions[] = 's.promotionid = :promotionid';
            $params['promotionid'] = (int) $filters['promotionid'];
        }

        $sifirst = $filters['sifirst'] ?? '';
        if ($sifirst !== '' && $sifirst !== 'all') {
            $conditions[] = $DB->sql_like('u.firstname', ':sifirst', false, false);
            $params['sifirst'] = $DB->sql_like_escape($sifirst) . '%';
        }

        $silast = $filters['silast'] ?? '';
        if ($silast !== '' && $silast !== 'all') {
            $conditions[] = $DB->sql_like('u.lastname', ':silast', false, false);
            $params['silast'] = $DB->sql_like_escape($silast) . '%';
        }

        if (!empty($filters['studentquery'])) {
            $like1 = $DB->sql_like('u.firstname', ':sq1', false, false);
            $like2 = $DB->sql_like('u.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];
    }
}
