<?php

declare(strict_types=1);

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

use App\Core\Database;

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

$hospital = strtolower((string)($_GET['hospital'] ?? 'njch'));
$schema = strtoupper(trim((string)config('hospitals.' . $hospital . '.database.schema', '')));

echo "Beds Summary Source Check\n";
echo "Hospital: " . strtoupper($hospital) . "\n";
echo "Schema: " . ($schema !== '' ? $schema : 'current user') . "\n\n";

function safe_ident_local(string $identifier): string
{
    $identifier = strtoupper(trim($identifier));
    if ($identifier === '' || !preg_match('/^[A-Z][A-Z0-9_#$]*$/', $identifier)) {
        throw new RuntimeException('Unsafe Oracle identifier: ' . $identifier);
    }
    return $identifier;
}

function qname_local(string $schema, string $table): string
{
    $table = safe_ident_local($table);
    if ($schema === '') {
        return $table;
    }
    return safe_ident_local($schema) . '.' . $table;
}

function table_exists_local(string $hospital, string $schema, string $tableName): bool
{
    // Important: do not use bind variable names like :table because Oracle may treat
    // reserved words as invalid host variables with PDO_OCI (ORA-01745).
    $sql = $schema !== ''
        ? "SELECT COUNT(1) value FROM all_tables WHERE owner = UPPER(:p_owner) AND table_name = UPPER(:p_table_name)"
        : "SELECT COUNT(1) value FROM user_tables WHERE table_name = UPPER(:p_table_name)";

    $params = $schema !== ''
        ? ['p_owner' => $schema, 'p_table_name' => $tableName]
        : ['p_table_name' => $tableName];

    $row = Database::fetchOne($hospital, $sql, $params);
    return ((int)($row['value'] ?? 0)) > 0;
}

function columns_local(string $hospital, string $schema, string $tableName): array
{
    $sql = $schema !== ''
        ? "SELECT column_name FROM all_tab_columns WHERE owner = UPPER(:p_owner) AND table_name = UPPER(:p_table_name) ORDER BY column_id"
        : "SELECT column_name FROM user_tab_columns WHERE table_name = UPPER(:p_table_name) ORDER BY column_id";

    $params = $schema !== ''
        ? ['p_owner' => $schema, 'p_table_name' => $tableName]
        : ['p_table_name' => $tableName];

    $rows = Database::fetchAll($hospital, $sql, $params);
    return array_map(function ($row) { return strtoupper((string)$row['column_name']); }, $rows);
}

function first_col(array $cols, array $candidates): ?string
{
    foreach ($candidates as $c) {
        $uc = strtoupper($c);
        if (in_array($uc, $cols, true)) {
            return $uc;
        }
    }
    return null;
}

function scalar_local(string $hospital, string $sql, array $params = []): ?float
{
    try {
        $row = Database::fetchOne($hospital, $sql, $params);
        if ($row === null || !array_key_exists('value', $row) || $row['value'] === null) {
            return null;
        }
        return (float)$row['value'];
    } catch (Throwable $e) {
        echo "  SQL ERROR: " . $e->getMessage() . "\n";
        return null;
    }
}

$bedTables = ['STP_BEDS', 'STP_BED', 'V_STP_BEDS', 'VW_STP_BEDS'];
$transTables = ['RCP_BED_TRANS', 'RCP_BED_TRANSACTION', 'RCP_BED_TRANSACTIONS'];
$extraTables = ['STP_PLACES', 'STP_HRC', 'RCP_ENTRYEXIT', 'RCP_PATIENT_VISIT', 'RCP_VISIT'];

$bedCols = ['BEDID', 'BED_ID', 'BED_CODE', 'BED_SERIAL', 'BEDNO', 'BED_NO', 'ID'];
$startCols = ['STARTDATE', 'START_DATE', 'FROM_DATE', 'DATE_FROM', 'ENTRY_DATE', 'ADMISSION_DATE', 'BED_START_DATE', 'STARTTIME'];
$endCols = ['ENDDATE', 'END_DATE', 'TO_DATE', 'DATE_TO', 'EXIT_DATE', 'DISCHARGE_DATE', 'BED_END_DATE', 'ENDTIME'];
$unitCols = ['UNIT_ID', 'UNITID', 'WARD_ID', 'WARDID', 'DEPT_ID', 'DEPARTMENT_ID', 'PLACE_ID1', 'ROOMID', 'ROOM_ID', 'PLACE_ID'];
$statusCols = ['ACTIVE_BED','BABY_NORMAL_BED','COMP_PAT_FLAG','STOP_FLAG','BLOCKED_BED','IS_BLOCKED','STATUS','STATUS_ID','BED_STATUS'];

foreach (array_merge($bedTables, $transTables, $extraTables) as $table) {
    echo "TABLE {$table}: ";

    try {
        if (!table_exists_local($hospital, $schema, $table)) {
            echo "not found\n";
            continue;
        }
    } catch (Throwable $e) {
        echo "metadata error: " . $e->getMessage() . "\n";
        continue;
    }

    echo "found\n";
    $qualifiedTable = qname_local($schema, $table);

    try {
        $cols = columns_local($hospital, $schema, $table);
    } catch (Throwable $e) {
        echo "  Column metadata error: " . $e->getMessage() . "\n\n";
        continue;
    }

    echo "  Columns: " . implode(', ', array_slice($cols, 0, 90)) . (count($cols) > 90 ? ' ...' : '') . "\n";
    $match = array_values(array_intersect($cols, array_merge($bedCols, $startCols, $endCols, $unitCols, $statusCols)));
    echo "  Relevant: " . ($match ? implode(', ', $match) : '-') . "\n";

    $rowCount = scalar_local($hospital, "SELECT COUNT(1) value FROM {$qualifiedTable}");
    echo "  Row count: " . ($rowCount === null ? 'unknown/error' : number_format($rowCount, 0)) . "\n";

    $bedCol = first_col($cols, $bedCols);
    if ($bedCol !== null) {
        $distinct = scalar_local($hospital, "SELECT COUNT(DISTINCT {$bedCol}) value FROM {$qualifiedTable}");
        echo "  Distinct beds via {$bedCol}: " . ($distinct === null ? 'unknown/error' : number_format($distinct, 0)) . "\n";
    }

    if (in_array($table, $transTables, true) && $bedCol !== null) {
        $start = first_col($cols, $startCols);
        $end = first_col($cols, $endCols);
        $where = '';
        if ($start !== null) { $where .= " AND (t.{$start} IS NULL OR t.{$start} <= SYSDATE)"; }
        if ($end !== null) { $where .= " AND (t.{$end} IS NULL OR t.{$end} > SYSDATE)"; }
        if (in_array('COMP_PAT_FLAG', $cols, true)) { $where .= " AND NVL(t.COMP_PAT_FLAG, 'P') = 'P'"; }
        $current = scalar_local($hospital, "SELECT COUNT(DISTINCT t.{$bedCol}) value FROM {$qualifiedTable} t WHERE 1=1 {$where}");
        echo "  Current occupied candidate: " . ($current === null ? 'unknown/error' : number_format($current, 0)) . "\n";
        echo "  Current where: " . ($where !== '' ? trim($where) : '-') . "\n";
    }

    echo "\n";
}

echo "Recommended action:\n";
echo "1) Send this output if total_beds / occupancy_rate remain pending.\n";
echo "2) The output will show the exact bed tables and columns available in this branch.\n";
echo "3) If no bed table exists, we will map the bed summary from the legacy Beds page source instead.\n";
