
<?php 
$folder = $folder ?? "006_RFI/001_Created_RFI";
$columnName = "";
$subFolder = "";

if (strpos($folder, "001_Created_RFI") !== false) {
    $columnName = "created_rfi";
    $subFolder = "001_Created_RFI";
} elseif (strpos($folder, "002_Signed_RFI") !== false) {
    $columnName = "signed_rfi";
    $subFolder = "002_Signed_RFI";
}

if ($columnName == "") {
    dump("RFI Sync: Unknown folder $folder");
    return;
}

$rfiDatas = db("r_f_i_s");

if (isset($fileName)) {
    // Optimization: only check RFIs that might match the file name parts
    // We don't know the exact format, but we know it should contain itp and rfi_no
    // To be safe and specific, we'll fetch all and match in PHP
}

$rfiDatas = $rfiDatas->get();

DB::beginTransaction();
$count = 0;

try {
    foreach ($rfiDatas as $rfi) {
        $itp = trim($rfi->itp);
        $rfi_no = trim($rfi->rfi_no);

        if ($itp == "" || $rfi_no == "") continue;

        // If specific fileName is provided, we check if this RFI matches it
        if (isset($fileName)) {
            // Pattern check: fileName should contain rfi_no
            $pattern = "/" . preg_quote($rfi_no, '/') . ".*\.pdf/i";
            if (!preg_match($pattern, $fileName)) {
                continue;
            }
        }

        $searchPath = storage_path("documents/006_RFI/$subFolder/*$rfi_no*.pdf");
        $foundFiles = glob($searchPath);

        $updateData = [$columnName => null];
        if (isset($foundFiles[0])) {
            $updateData[$columnName] = str_replace(base_path() . "/", "", $foundFiles[0]);
            db("r_f_i_s")->where("id", $rfi->id)->update($updateData);
            $count++;
        }
    }
    DB::commit();
    dump("RFI Sync: Found and updated $count rows in r_f_i_s for $columnName");
} catch (\Throwable $th) {
    DB::rollback();
    dump("RFI Sync Error: " . $th->getMessage());
}
?>