Reservair/includes/Repository/RsvReservationRepository.php

<?php

use Reservair\Database\Db;

class RsvReservationRepository {
    private string $table;
    private string $timetable_reservations_table;

    public function __construct() {
        $this->table                        = Db::prefix() . 'rsv_reservation';
        $this->timetable_reservations_table = Db::prefix() . 'rsv_timetable_reservation';
    }

    public function get_all(?int $limit = null, int $skip = 0) {
        if ($limit === null) {
            return Db::get_results("SELECT * FROM {$this->table} ORDER BY id DESC");
        }
        return Db::get_results(
            "SELECT * FROM {$this->table} ORDER BY id DESC LIMIT %d OFFSET %d",
            [$limit, $skip]
        );
    }

    public function count_all(): int {
        return (int) Db::get_var("SELECT COUNT(*) FROM {$this->table}");
    }

    public function get(int $id) {
        return Db::get_row(
            "SELECT * FROM {$this->table} WHERE id = %d",
            [$id]
        );
    }

    public function get_detail(int $id): ?array {
        $reservation = Db::get_row(
            "SELECT * FROM {$this->table} WHERE id = %d",
            [$id],
            ARRAY_A
        );

        if ($reservation === null) {
            return null;
        }

        $form_submit_table = Db::prefix() . 'rsv_form_submit';
        $form_submit = Db::get_row(
            "SELECT `values` FROM {$form_submit_table} WHERE form_submit_id = %d",
            [(int) $reservation['form_submit_id']],
            ARRAY_A
        );
        $reservation['form_values'] = $form_submit ? json_decode($form_submit['values'], true) : null;

        $reservation['timetable_reservations'] = Db::get_results(
            "SELECT id, timetable_id, `start_utc`, `end_utc` FROM {$this->timetable_reservations_table} WHERE reservation_id = %d",
            [$id],
            ARRAY_A
        );

        $confirmation_table = Db::prefix() . 'rsv_timetable_reservation_confirmation';
        $reservation['pending_confirmation'] = (int) Db::get_var(
            "SELECT COUNT(*) FROM {$confirmation_table} c
             JOIN {$this->timetable_reservations_table} tr ON tr.id = c.timetable_reservation_id
             WHERE tr.reservation_id = %d",
            [$id]
        ) > 0;

        return $reservation;
    }

    public function insert(array $data): int {
        return Db::insert($this->table, $data);
    }

    public function delete(int $id): void {
        Db::delete($this->table, ['id' => $id]);
    }

    public function count_subitems(int $reservation_id): int {
        return (int) Db::get_var(
            "SELECT COUNT(*) FROM {$this->timetable_reservations_table} WHERE reservation_id = %d",
            [$reservation_id]
        );
    }

    public function are_subitems_confirmed(int $reservation_id): bool {
        $result = Db::get_row(
            "SELECT MIN(rtr.is_confirmed) AS is_confirmed
             FROM {$this->table} AS rr
             LEFT JOIN {$this->timetable_reservations_table} AS rtr ON rr.id = rtr.reservation_id
             WHERE rr.id = %d",
            [$reservation_id]
        );
        return $result !== null && $result->is_confirmed == 1;
    }

    /**
     * Whether the reservation has reached a terminal state. `is_confirmed` is
     * NULL while pending, 1 once confirmed and 0 once refused, so any non-NULL
     * value means the outcome is settled.
     */
    public function is_resolved(int $reservation_id): bool {
        $result = Db::get_row(
            "SELECT is_confirmed FROM {$this->table} WHERE id = %d",
            [$reservation_id]
        );
        return $result !== null && $result->is_confirmed !== null && $result->is_confirmed;
    }

    public function set_resolved_state(int $reservation_id, bool $confirmed): void {
        Db::update(
            $this->table,
            ['is_confirmed' => $confirmed ? 1 : 0],
            ['id' => $reservation_id]
        );
    }

    public function get_form_submit_id(int $reservation_id): ?int {
        $result = Db::get_var(
            "SELECT form_submit_id FROM {$this->table} WHERE id = %d",
            [$reservation_id]
        );
        return $result !== null ? (int) $result : null;
    }
}