<?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>
 */

declare(strict_types=1);

namespace mod_mtcontabilita\reportbuilder\local\entities;

use core_reportbuilder\local\entities\base;
use core_reportbuilder\local\filters\boolean_select;
use core_reportbuilder\local\filters\number;
use core_reportbuilder\local\filters\select;
use core_reportbuilder\local\helpers\custom_fields;
use core_reportbuilder\local\helpers\format;
use core_reportbuilder\local\report\column;
use core_reportbuilder\local\report\filter;
use lang_string;
use mod_mtcontabilita\reportbuilder\local\formatters\mtcontabilita as mtcontabilita_formatter;
use stdClass;

/**
 * session class
 */
class session extends base {

    /** @var customfields */
    protected $customfields;

    /**
     * get alias
     */
    protected function get_default_table_aliases(): array {
        return [
            'mtcontabilita_sessions' => 'aps',
            'mtcontabilita_sessions_dates' => 'sd',
        ];
    }

    /**
     * get title
     */
    protected function get_default_entity_title(): lang_string {
        return new lang_string('mtcontabilitasession', 'mod_mtcontabilita');
    }

    /**
     * initialise
     */
    public function initialise(): base {
        $columns = array_merge($this->get_all_columns(), $this->get_custom_fields()->get_columns());
        foreach ($columns as $column) {
            $this->add_column($column);
        }

        $filters = array_merge($this->get_all_filters(), $this->get_custom_fields()->get_filters());
        foreach ($filters as $filter) {
            $this
                ->add_filter($filter)
                ->add_condition($filter);
        }

        return $this;
    }

    /**
     * get columns
     */
    protected function get_all_columns(): array {
        global $DB;

        $dbfamily = $DB->get_dbfamily();
        $session = $this->get_table_alias('mtcontabilita_sessions');

        // Column description.
        $descriptionfieldsql = "{$session}.description";
        if ($dbfamily === 'oracle') {
            $descriptionfieldsql = $DB->sql_order_by_text($descriptionfieldsql, 1024);
        }
        $columns[] = (new column(
            'description',
            new lang_string('description'),
            $this->get_entity_name()
        ))
            ->add_joins($this->get_joins())
            ->add_join("LEFT JOIN {modules} m ON m.name = 'mtcontabilita'")
            ->add_join("
                LEFT JOIN {course_modules} cm
                       ON {$session}.mtcontabilita = cm.instance AND cm.course = c.id AND cm.module = m.id
            ")
            ->set_type(column::TYPE_LONGTEXT)
            ->add_field($descriptionfieldsql, 'description')
            ->add_field("{$session}.descriptionformat")
            ->add_field("{$session}.mtcontabilita", 'mtcontabilitaid')
            ->add_field('cm.id')
            ->set_is_sortable(false)
            ->add_callback([mtcontabilita_formatter::class, 'description']);

        // Column allow wait listing.
        $columns[] = (new column(
            'allowwaitlist',
            new lang_string('allowwaitlist', 'mod_mtcontabilita'),
            $this->get_entity_name()
        ))
            ->add_joins($this->get_joins())
            ->set_type(column::TYPE_BOOLEAN)
            ->add_field("{$session}.allowwaitlist")
            ->set_is_sortable(true)
            ->add_callback([format::class, 'boolean_as_text']);

        // Column allow cancellations.
        $columns[] = (new column(
            'allowcancellations',
            new lang_string('allowcancellations', 'mod_mtcontabilita'),
            $this->get_entity_name()
        ))
            ->add_joins($this->get_joins())
            ->set_type(column::TYPE_BOOLEAN)
            ->add_field("{$session}.allowcancellations")
            ->set_is_sortable(true)
            ->add_callback([format::class, 'boolean_as_text']);

        // Bookings query.
        $bookingsquery = self::get_bookings_query($session);

        // Column seatsbooked.
        $column = (new column(
            'seatsbooked',
            new lang_string('seatsbooked', 'mod_mtcontabilita'),
            $this->get_entity_name()
        ))
            ->add_joins($this->get_joins())
            ->set_type(column::TYPE_INTEGER)
            ->set_is_sortable(true)
            ->add_field($bookingsquery, 'seatsbooked');

        // TODO: See WP-1397 - seats booked is already an aggregate function, so can't be grouped by in MSSQL/Oracle.
        if ($dbfamily === 'mssql' || $dbfamily === 'oracle') {
            $column->set_groupby_sql("{$session}.id");
        }
        // MSSQL can not aggregate columns with sub-query, so we'll disable all aggregation methods for them.
        if ($dbfamily === 'mssql') {
            $column->set_disabled_aggregation_all();
        }
        $columns[] = $column;

        // Column session status.
        $columns[] = (new column(
            'status',
            new lang_string('sessionstatus', 'mod_mtcontabilita'),
            $this->get_entity_name()
        ))
            ->add_joins($this->get_joins())
            ->set_type(column::TYPE_TEXT)
            ->add_field("{$session}.id", 'sessionid')
            ->set_is_sortable(true)
            ->add_callback([mtcontabilita_formatter::class, 'sessionstatus']);

        return $columns;
    }

    /**
     * gt filters
     */
    protected function get_all_filters(): array {
        $session = $this->get_table_alias('mtcontabilita_sessions');
        // Filter allowwaitlist.
        $filters[] = (new filter(
            boolean_select::class,
            'allowwaitlist',
            new lang_string('allowwaitlist', 'mod_mtcontabilita'),
            $this->get_entity_name(),
            "{$session}.allowwaitlist"
        ))
            ->add_joins($this->get_joins());

        // Filter allowcancellations.
        $filters[] = (new filter(
            boolean_select::class,
            'allowcancellations',
            new lang_string('allowcancellations', 'mod_mtcontabilita'),
            $this->get_entity_name(),
            "{$session}.allowcancellations"
        ))
            ->add_joins($this->get_joins());

        // Bookings query.
        $bookingsquery = self::get_bookings_query($session);

        // Filter seatsbooked.
        $filters[] = (new filter(
            number::class,
            'seatsbooked',
            new lang_string('seatsbooked', 'mod_mtcontabilita'),
            $this->get_entity_name(),
            $bookingsquery
        ))
            ->add_joins($this->get_joins());

        return $filters;
    }

    /**
     * get fields
     */
    protected function get_custom_fields(): custom_fields {
        if ($this->customfields === null) {
            $session = $this->get_table_alias('mtcontabilita_sessions');
            $this->customfields = new custom_fields("{$session}.id", $this->get_entity_name(),
                'mod_mtcontabilita', 'mtcontabilita', 0);
            $this->customfields->add_joins($this->get_joins());
        }
        return $this->customfields;
    }

    /**
     * get booking query
     */
    private static function get_bookings_query(string $tablealias): string {
        $excludestatuses = [
            MOD_MTCONTABILITA_STATUS_USER_CANCELLED,
            MOD_MTCONTABILITA_STATUS_DECLINED,
            MOD_MTCONTABILITA_STATUS_REQUESTED,
            MOD_MTCONTABILITA_STATUS_WAITLISTED,
        ];
        $excludesql = 'NOT IN (' . implode(',', $excludestatuses) . ')';

        // Bookings query.
        return "(
            SELECT COUNT(asu.id)
              FROM {mtcontabilita_signups} asu
              JOIN {mtcontabilita_signups_status} asus
                ON asu.id = asus.signupid
              JOIN (
                SELECT signupid, max(timecreated) AS timecreated
                  FROM {mtcontabilita_signups_status}
              GROUP BY signupid
                 ) asus2
                ON asus.signupid = asus2.signupid AND asus.timecreated = asus2.timecreated
             WHERE asus.statuscode {$excludesql} AND asu.sessionid = {$tablealias}.id)";
    }
}
