<?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.
//
// 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 <http://www.gnu.org/licenses/>.

/**
 * Simulatore contabilità
 *
 * @package    mod_mtcontabilita
 * @copyright  2026 Mediatouch 2000 Srl <https://www.mediatouch.it>
 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
 * @author     Marco Cappuccio <m.cappuccio@mediatouch.it>
 */

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

/**
 * evaluation class
 */
class mtcontabilita_evaluation {

    /**
     * get users listing
     */
    public static function get_users_listing($mtid, $cid, $sort='name', $dir='ASC', $page=0, $recordsperpage=0,
                               $search='', $firstinitial='', $lastinitial='', $extraselect='',
                               array $extraparams=null, $extracontext = null) {

        global $DB;

        $ctxid = \context_course::instance($cid)->id;
        $params = ['mtid' => $mtid, 'ctxid' => $ctxid];

        // FULLNAME portabile per DB diversi.
        $fullname = $DB->sql_fullname('u.firstname', 'u.lastname'); // restituisce l'espr. corretta per il DB in uso

        $where = []; // pezzi addizionali di WHERE

        if (!empty($search)) {
            $search = trim($search);
            $where[] = "("
                . $DB->sql_like($fullname, ':search1', false, false)
                . " OR " . $DB->sql_like('u.email', ':search2', false, false)
                . " OR u.username = :search3"
                . ")";
            $params['search1'] = "%$search%";
            $params['search2'] = "%$search%";
            $params['search3'] = $search;
        }

        if ($firstinitial) {
            $where[] = $DB->sql_like('u.firstname', ':fni', false, false);
            $params['fni'] = "$firstinitial%";
        }
        if ($lastinitial) {
            $where[] = $DB->sql_like('u.lastname', ':lni', false, false);
            $params['lni'] = "$lastinitial%";
        }

        if ($extraselect) {
            // Protegge "id" sciolti → li riferisce a u.id
            $extraselect = preg_replace('~([ =]|^)id([ =]|$)~', '$1u.id$2', $extraselect);
            $where[] = "($extraselect)";
            $params = $params + (array)$extraparams;
        }

        // Sorting sicuro: mappa i nomi "logici" ai campi reali
        $sortmap = [
            'name'      => "$fullname",
            'email'     => 'u.email',
            'scoretot'  => 'a.scoretot',
            'attempt'   => 'a.attempt',
            'id'        => 'a.id',
        ];
        $sortfield = $sortmap[$sort] ?? 'a.id';
        $dir = strtoupper($dir) === 'DESC' ? 'DESC' : 'ASC';

        $extrawhere = $where ? ' AND '.implode(' AND ', $where) : '';

        $sql = "SELECT
            a.id,
            a.userid,
            a.attempt,
            $fullname AS name,
            u.email,
            COALESCE(
                SUM(
                    CASE
                        WHEN ai.bitem = 'i' THEN
                            CASE
                                WHEN ai.scoremanual > 0 THEN ai.scoremanual
                                ELSE ai.score
                            END
                    END
                ), 0
            ) AS score_i,
            COALESCE(
                SUM(
                    CASE
                        WHEN ai.bitem = 's' THEN
                            CASE
                                WHEN ai.scoremanual > 0 THEN ai.scoremanual
                                ELSE ai.score
                            END
                    END
                ), 0
            ) AS score_s
        FROM {mtcontabilita_attempts} a
        JOIN {user} u
          ON u.id = a.userid
        LEFT JOIN {mtcontabilita_attempt_items} ai
          ON ai.attemptid = a.id
         AND ai.bitem IN ('i','s')
        WHERE a.mtcontabilitaid = :mtid
          AND a.state IN ('finished','abandoned')
          AND EXISTS (
                SELECT 1
                  FROM {role_assignments} ra
                 WHERE ra.userid = a.userid
                   AND ra.contextid = :ctxid
              )
          $extrawhere
        GROUP BY
            a.id, a.userid, a.attempt, a.scoretot, u.firstname, u.lastname, u.email
        ORDER BY $sortfield $dir";
        // Nota: $page è l'offset (non il numero pagina).
        return $DB->get_records_sql($sql, $params, $page, $recordsperpage);
    }

    /**
     * Numero totale di tentativi per questa attività (solo finished/abandoned),
     * con gli stessi filtri di get_users_listing().
     */
    public static function count_attempts($mtid, $cid, $search='', $firstinitial='', $lastinitial='',
            $extraselect='', array $extraparams=null, $extracontext = null) {

        global $DB;

        $ctxid = \context_course::instance($cid)->id;
        $params = ['mtid' => $mtid, 'ctxid' => $ctxid];

        $fullname = $DB->sql_fullname('u.firstname', 'u.lastname');

        $where = [];

        if (!empty($search)) {
            $search = trim($search);
            $where[] = "("
                . $DB->sql_like($fullname, ':search1', false, false)
                . " OR " . $DB->sql_like('u.email', ':search2', false, false)
                . " OR u.username = :search3"
                . ")";
            $params['search1'] = "%$search%";
            $params['search2'] = "%$search%";
            $params['search3'] = $search;
        }

        if ($firstinitial) {
            $where[] = $DB->sql_like('u.firstname', ':fni', false, false);
            $params['fni'] = "$firstinitial%";
        }
        if ($lastinitial) {
            $where[] = $DB->sql_like('u.lastname', ':lni', false, false);
            $params['lni'] = "$lastinitial%";
        }

        if ($extraselect) {
            // Protegge "id" sciolti → li riferisce a u.id
            $extraselect = preg_replace('~([ =]|^)id([ =]|$)~', '$1u.id$2', $extraselect);
            $where[] = "($extraselect)";
            $params = $params + (array)$extraparams;
        }

        $extrawhere = $where ? ' AND '.implode(' AND ', $where) : '';

        $sql = "SELECT COUNT(DISTINCT a.id)
                FROM {mtcontabilita_attempts} a
                JOIN {user} u
                  ON u.id = a.userid
                WHERE a.mtcontabilitaid = :mtid
                  AND a.state IN ('finished','abandoned')
                  AND EXISTS (
                        SELECT 1
                          FROM {role_assignments} ra
                         WHERE ra.userid = a.userid
                           AND ra.contextid = :ctxid
                      )
                  $extrawhere";

        return (int)$DB->get_field_sql($sql, $params);
    }

    /**
     * get users
     */
    public static function get_users($cid, $get=true, $search='', $confirmed=false, array $exceptions=null, $sort='firstname ASC',
                       $firstinitial='', $lastinitial='', $page='', $recordsperpage='', $fields='*', $extraselect='', array $extraparams=null) {
        global $DB;

        $select = "courseid=$cid";
        $params = [];

        if (!empty($search)) {
            $search = trim($search);
            $select .= " AND (".$DB->sql_like($fullname, ':search1', false)." OR ".$DB->sql_like('email', ':search2', false)." OR username = :search3)";
            $params['search1'] = "%$search%";
            $params['search2'] = "%$search%";
            $params['search3'] = "$search";
        }

        if ($confirmed) {
            $select .= " AND confirmed = 1";
        }

        if ($exceptions) {
            list($exceptions, $eparams) = $DB->get_in_or_equal($exceptions, SQL_PARAMS_NAMED, 'ex', false);
            $params = $params + $eparams;
            $select .= " AND id $exceptions";
        }

        if ($firstinitial) {
            $select .= " AND ".$DB->sql_like('firstname', ':fni', false, false);
            $params['fni'] = "$firstinitial%";
        }
        if ($lastinitial) {
            $select .= " AND ".$DB->sql_like('lastname', ':lni', false, false);
            $params['lni'] = "$lastinitial%";
        }

        if ($extraselect) {
            $select .= " AND $extraselect";
            $params = $params + (array)$extraparams;
        }

        if ($get) {
            return $DB->get_records_select('mtcontabilita_list', $select, $params, $sort, $fields, $page, $recordsperpage);
        } else {
            return $DB->count_records_select('mtcontabilita_list', $select, $params);
        }
    }
}

/**
 * details class
 */
class mtcontabilita_evaluation_details {

    /**
     * Elenco dettagli per attempt, filtrati logicamente per tipo (i = iniziale, s = scritture).
     * Ritorna record grezzi: il pivot/parse (importi dare/avere) lo facciamo in PHP.
     */
    public static function get_users_listing($mtid, $uid, $attemptid, $tp, $sort='id', $dir='ASC',
            $page=0, $recordsperpage=0, $search='', $firstinitial='', $lastinitial='',
            $extraselect='', array $extraparams=null, $extracontext = null) {

        global $DB;

        $params = ['mtid' => $mtid, 'uid' => $uid, 'attemptid' => $attemptid];
        $where = [];
        $tp = ($tp === 's') ? 's' : 'i'; // default 'i'

        if ($extraselect) {
            $where[] = "($extraselect)";
            $params = $params + (array)$extraparams;
        }
        $extrawhere = $where ? ' AND '.implode(' AND ', $where) : '';

        // Mappa campi ordinabili.
        $sortmap = [
            'id'          => 'ai.id',
            'itemdetid'   => 'ai.itemdetid',
            'scheme'      => 'ai.scheme',
            'macroclasse' => 'ai.macroclasse',
            'score'       => 'ai.score',
            'scoremanual' => 'ai.scoremanual',
            'conto'       => 'l.name',
        ];

        $sortfield = $sortmap[$sort] ?? 'ai.id';
        $dir = strtoupper($dir) === 'DESC' ? 'DESC' : 'ASC';

        if ($tp === 's') {
            $sql = "SELECT
                    ai.id,
                    ai.itemdetid,
                    ai.scheme,
                    ai.macroclasse,
                    ai.score,
                    ai.scoremanual,
                    ai.da,
                    ai.daamount,
                    ai.itemdate,
                    NULL AS importo,
                    ai.itemnum AS attemptitemnum,
                    ai.itemnum AS itemnum,
                    l.name AS conto
                FROM {mtcontabilita_attempt_items} ai
                JOIN {mtcontabilita_attempts} a
                ON ai.attemptid = a.id
                LEFT JOIN {mtcontabilita_list} l
                ON l.id = ai.listid
                WHERE a.userid = :uid
                AND a.id = :attemptid
                AND a.mtcontabilitaid = :mtid
                AND a.state IN ('finished','abandoned')
                AND ai.bitem = :tp
                $extrawhere
                ORDER BY $sortfield $dir";
        } else {
            $sql = "SELECT
                ai.id,
                ai.itemdetid,
                ai.scheme,
                ai.macroclasse,
                ai.score,
                ai.scoremanual,
                ai.da,               -- 'd' oppure 'a'
                ai.daamount,         -- JSON dell'utente
                ai.itemdate,         -- <<< AGGIUNTO: data della scrittura
                d.amount AS importo, -- importo originale (solo iniziale)
                d.score  AS scoremax,
                ai.itemnum AS attemptitemnum, -- num scrittura lato attempt (solo scritture)
                d.itemnum AS itemnum,
                l.name AS conto      -- denominazione conto
            FROM {mtcontabilita_attempt_items} ai
            JOIN {mtcontabilita_attempts} a
                ON ai.attemptid = a.id
            LEFT JOIN {mtcontabilita_itemdet} d
                ON d.id = ai.itemdetid
            LEFT JOIN {mtcontabilita_list} l
                ON l.id = COALESCE(d.listid, ai.listid)   -- <<< CAMBIATO: tiene conto delle scritture
            WHERE a.userid = :uid
                AND a.id = :attemptid
                AND a.mtcontabilitaid = :mtid
                AND a.state IN ('finished','abandoned')
                AND ai.bitem = :tp
            $extrawhere
            ORDER BY $sortfield $dir";
        }
        $params['tp'] = $tp;
        return $DB->get_records_sql($sql, $params, $page, $recordsperpage);
    }

    /**
     * Numero di righe di dettaglio per un tentativo (per tipo i/s),
     * usato per avere una paging bar coerente con la get_users_listing().
     */
    public static function count_items($mtid, $uid, $attemptid, $tp,
            $search='', $firstinitial='', $lastinitial='',
            $extraselect='', array $extraparams=null, $extracontext = null) {

        global $DB;

        $params = ['mtid' => $mtid, 'uid' => $uid, 'attemptid' => $attemptid];
        $where = [];
        $tp = ($tp === 's') ? 's' : 'i';

        if ($extraselect) {
            $where[] = "($extraselect)";
            $params = $params + (array)$extraparams;
        }
        $extrawhere = $where ? ' AND '.implode(' AND ', $where) : '';

        // Per le scritture conto le DISTINTE scritture (itemnum).
        $countfield = ($tp === 's') ? 'COUNT(DISTINCT ai.itemnum)' : 'COUNT(*)';

        $sql = "SELECT $countfield
                FROM {mtcontabilita_attempt_items} ai
                JOIN {mtcontabilita_attempts} a
                ON ai.attemptid = a.id
            WHERE a.userid = :uid
                AND a.id = :attemptid
                AND a.mtcontabilitaid = :mtid
                AND a.state IN ('finished','abandoned')
                AND ai.bitem = :tp
                $extrawhere";

        $params['tp'] = $tp;

        return (int)$DB->get_field_sql($sql, $params);
    }

    /**
     * get users
     */
    public static function get_users($cid, $get=true, $search='', $confirmed=false, array $exceptions=null,
            $sort='firstname ASC', $firstinitial='', $lastinitial='', $page='', $recordsperpage='',
            $fields='*', $extraselect='', array $extraparams=null) {
        global $DB;

        $select = "courseid=$cid";
        $params = [];

        if (!empty($search)) {
            $search = trim($search);
            // Qui non c'è join a user; lascio invariato come nel tuo file.
            $select .= " AND (".$DB->sql_like($fullname, ':search1', false)." OR ".$DB->sql_like('email', ':search2', false)." OR username = :search3)";
            $params['search1'] = "%$search%";
            $params['search2'] = "%$search%";
            $params['search3'] = "$search";
        }

        if ($confirmed) {
            $select .= " AND confirmed = 1";
        }

        if ($exceptions) {
            list($exceptions, $eparams) = $DB->get_in_or_equal($exceptions, SQL_PARAMS_NAMED, 'ex', false);
            $params = $params + $eparams;
            $select .= " AND id $exceptions";
        }

        if ($firstinitial) {
            $select .= " AND ".$DB->sql_like('firstname', ':fni', false, false);
            $params['fni'] = "$firstinitial%";
        }
        if ($lastinitial) {
            $select .= " AND ".$DB->sql_like('lastname', ':lni', false, false);
            $params['lni'] = "$lastinitial%";
        }

        if ($extraselect) {
            $select .= " AND $extraselect";
            $params = $params + (array)$extraparams;
        }

        if ($get) {
            return $DB->get_records_select('mtcontabilita_list', $select, $params, $sort, $fields, $page, $recordsperpage);
        } else {
            return $DB->count_records_select('mtcontabilita_list', $select, $params);
        }
    }
}
