barometer-server/lib/session_schedule.php

<?php
/**
 * Next program session appointments (date-only, DD-MM-YYYY).
 */

/** @return bool */
function qdb_scheduled_date_format_valid(string $date): bool
{
    if (!preg_match('/^\d{2}-\d{2}-\d{4}$/', $date)) {
        return false;
    }
    [$d, $m, $y] = array_map('intval', explode('-', $date));
    return checkdate($m, $d, $y);
}

/** Start of calendar day in server default timezone. */
function qdb_scheduled_date_to_start_of_day_unix(string $date): ?int
{
    if (!qdb_scheduled_date_format_valid($date)) {
        return null;
    }
    [$d, $m, $y] = explode('-', $date);
    $ts = mktime(0, 0, 0, (int)$m, (int)$d, (int)$y);
    return $ts !== false ? $ts : null;
}

function qdb_scheduled_date_is_after_today(string $date, ?int $nowTs = null): bool
{
    $dayStart = qdb_scheduled_date_to_start_of_day_unix($date);
    if ($dayStart === null) {
        return false;
    }
    $nowTs ??= time();
    $todayStart = strtotime('today', $nowTs);
    return $dayStart > $todayStart;
}

/**
 * @return array{scheduledDate: string, programCycleNumber: int, programSessionNumber: int, programSessionID: ?string, updatedAt: int}|null
 */
function qdb_get_client_session_schedule(PDO $pdo, string $clientCode): ?array
{
    $stmt = $pdo->prepare(
        'SELECT scheduledDate, programCycleNumber, programSessionNumber, programSessionID, updatedAt
         FROM client_session_schedule WHERE clientCode = :cc'
    );
    $stmt->execute([':cc' => $clientCode]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$row) {
        return null;
    }
    return [
        'scheduledDate'        => (string)$row['scheduledDate'],
        'programCycleNumber'   => (int)$row['programCycleNumber'],
        'programSessionNumber' => (int)$row['programSessionNumber'],
        'programSessionID'     => $row['programSessionID'] !== null && $row['programSessionID'] !== ''
            ? (string)$row['programSessionID']
            : null,
        'updatedAt'            => (int)$row['updatedAt'],
    ];
}

function qdb_upsert_client_session_schedule(
    PDO $pdo,
    string $clientCode,
    string $scheduledDate,
    int $programCycleNumber,
    int $programSessionNumber,
    ?string $programSessionID = null
): void {
    if (!qdb_scheduled_date_format_valid($scheduledDate)) {
        json_error('INVALID_FIELD', 'scheduledDate must be DD-MM-YYYY', 400);
    }
    if (!qdb_scheduled_date_is_after_today($scheduledDate)) {
        json_error('INVALID_FIELD', 'scheduledDate must be in the future', 400);
    }
    if ($programCycleNumber < 1 || $programSessionNumber < 1) {
        json_error('INVALID_FIELD', 'program cycle and session must be positive', 400);
    }

    $pdo->prepare(
        'INSERT INTO client_session_schedule (
            clientCode, scheduledDate, programCycleNumber, programSessionNumber, programSessionID, updatedAt
        ) VALUES (
            :cc, :sd, :pcn, :psn, :psid, :uat
        )
        ON CONFLICT(clientCode) DO UPDATE SET
            scheduledDate        = excluded.scheduledDate,
            programCycleNumber   = excluded.programCycleNumber,
            programSessionNumber = excluded.programSessionNumber,
            programSessionID     = excluded.programSessionID,
            updatedAt            = excluded.updatedAt'
    )->execute([
        ':cc'   => $clientCode,
        ':sd'   => $scheduledDate,
        ':pcn'  => $programCycleNumber,
        ':psn'  => $programSessionNumber,
        ':psid' => $programSessionID,
        ':uat'  => time(),
    ]);
}

/**
 * RBAC-scoped schedule rows for the website Schedule view.
 *
 * @return list<array<string, mixed>>
 */
function qdb_list_session_schedules_scoped(PDO $pdo, array $tokenRec): array
{
    [$rbacClause, $rbacParams] = rbac_client_filter($tokenRec, 'cl');
    $sql = "
        SELECT css.clientCode, css.scheduledDate, css.programCycleNumber, css.programSessionNumber,
               css.programSessionID, css.updatedAt,
               cl.coachID, co.username AS coachUsername,
               sv.username AS supervisorUsername
        FROM client_session_schedule css
        INNER JOIN client cl ON cl.clientCode = css.clientCode
        LEFT JOIN coach co ON co.coachID = cl.coachID
        LEFT JOIN supervisor sv ON sv.supervisorID = co.supervisorID
        WHERE ($rbacClause)
        ORDER BY css.scheduledDate ASC, co.username ASC, css.clientCode ASC
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute($rbacParams);
    $rows = [];
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
        $rows[] = [
            'clientCode'           => $row['clientCode'],
            'scheduledDate'        => $row['scheduledDate'],
            'programCycleNumber'   => (int)$row['programCycleNumber'],
            'programSessionNumber' => (int)$row['programSessionNumber'],
            'programSessionID'     => $row['programSessionID'],
            'coachID'              => $row['coachID'],
            'coachUsername'        => $row['coachUsername'] ?? $row['coachID'],
            'supervisorUsername'   => $row['supervisorUsername'] ?? '',
            'updatedAt'            => (int)$row['updatedAt'],
        ];
    }
    return $rows;
}