<?php

declare(strict_types=1);

require_once dirname(__DIR__) . '/app/bootstrap.php';

use App\Core\Database;
use App\Repositories\ExecutiveSummaryRepository;

header('Content-Type: text/plain; charset=utf-8');

$hospital = strtolower(trim((string)($_GET['hospital'] ?? 'njch')));
$hour = (int)env('EXECUTIVE_OPERATIONAL_DAY_START_HOUR', '7');
if ($hour < 0) { $hour = 0; }
if ($hour > 23) { $hour = 23; }

$repo = new ExecutiveSummaryRepository($hospital);
$info = $repo->operationalDayInfo();

$opDayExpr = "TRUNC(SYSDATE - ({$hour}/24))";
$windowStart = "({$opDayExpr} + ({$hour}/24))";
$windowEnd = "({$windowStart} + 1)";
$dateStart = $opDayExpr;
$dateEnd = "({$opDayExpr} + 1)";
$calendarStart = "TRUNC(SYSDATE)";
$calendarEnd = "TRUNC(SYSDATE) + 1";

function money_value($row, $key) {
    if (!$row || !isset($row[$key]) || $row[$key] === null || $row[$key] === '') { return 0.0; }
    return (float)$row[$key];
}

try {
    $sql = "
        SELECT
            TO_CHAR({$opDayExpr}, 'YYYY-MM-DD') operational_date,
            TO_CHAR({$windowStart}, 'YYYY-MM-DD HH24:MI') operational_window_start,
            TO_CHAR({$windowEnd}, 'YYYY-MM-DD HH24:MI') operational_window_end,
            TO_CHAR({$dateStart}, 'YYYY-MM-DD HH24:MI') finance_date_start,
            TO_CHAR({$dateEnd}, 'YYYY-MM-DD HH24:MI') finance_date_end,
            TO_CHAR({$calendarStart}, 'YYYY-MM-DD HH24:MI') calendar_today_start,
            TO_CHAR({$calendarEnd}, 'YYYY-MM-DD HH24:MI') calendar_today_end,
            NVL(SUM(CASE WHEN visit_end_date >= {$windowStart} AND visit_end_date < {$windowEnd}
                         THEN NVL(credit_value,0)+NVL(cash_value,0) ELSE 0 END), 0) operational_window_total,
            NVL(SUM(CASE WHEN visit_end_date >= {$dateStart} AND visit_end_date < {$dateEnd}
                         THEN NVL(credit_value,0)+NVL(cash_value,0) ELSE 0 END), 0) operational_date_total,
            NVL(SUM(CASE WHEN visit_end_date >= {$calendarStart} AND visit_end_date < {$calendarEnd}
                         THEN NVL(credit_value,0)+NVL(cash_value,0) ELSE 0 END), 0) calendar_today_total
        FROM t_inp_outp_revenue_batch
        WHERE visit_end_date >= ({$opDayExpr} - 1)
          AND visit_end_date < (TRUNC(SYSDATE) + 1)
    ";

    $row = Database::fetchOne($hospital, $sql, []);

    echo "Operational Finance Check\n";
    echo "Hospital: " . strtoupper($hospital) . "\n";
    echo "Start hour: {$hour}\n";
    echo "Operational date: " . ($info['operational_date'] ?? '') . "\n";
    echo "Night rollover: " . (!empty($info['is_rollover']) ? 'YES' : 'NO') . "\n\n";

    echo "Operational window: " . ($row['operational_window_start'] ?? '') . " -> " . ($row['operational_window_end'] ?? '') . "\n";
    echo "Finance date range: " . ($row['finance_date_start'] ?? '') . " -> " . ($row['finance_date_end'] ?? '') . "\n";
    echo "Calendar today: " . ($row['calendar_today_start'] ?? '') . " -> " . ($row['calendar_today_end'] ?? '') . "\n\n";

    echo "Operational window total: " . money_value($row, 'operational_window_total') . "\n";
    echo "Operational date total:   " . money_value($row, 'operational_date_total') . "\n";
    echo "Calendar today total:     " . money_value($row, 'calendar_today_total') . "\n\n";

    echo "Recommended value for Today Revenue after Phase 10.3.6: Operational date total\n";
} catch (Throwable $e) {
    echo "ERROR: " . $e->getMessage() . "\n";
}
