<?php

include_once __DIR__ . 'config.php';

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

date_default_timezone_set('Asia/Kuala_Lumpur');
include '/var/www/html/db/connectionADP.php';

$logDir = __DIR__ . '/logs';
if (!is_dir($logDir) && !mkdir($logDir, 0775, true) && !is_dir($logDir)) {
    die('Unable to create log directory: ' . $logDir);
}

$logFile = $logDir . '/container_tracking_' . date('Y-m-d') . '.log';

function writeTrackingLog(string $level, string $message, array $context = []): void
{
    global $logFile;
    $logData = [
        'timestamp' => date('Y-m-d H:i:s'),
        'level'     => $level,
        'message'   => $message,
        'context'   => $context
    ];
    file_put_contents(
        $logFile,
        json_encode($logData, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE | JSON_PARTIAL_OUTPUT_ON_ERROR) . PHP_EOL,
        FILE_APPEND | LOCK_EX
    );
}

function getOracleError($statement = null): array
{
    $error = $statement ? oci_error($statement) : oci_error();
    return [
        'code'    => $error['code'] ?? null,
        'message' => $error['message'] ?? 'Unknown Oracle error',
        'offset'  => $error['offset'] ?? null
    ];
}

function recordContext(string $blNo, string $containerNo, string $poNo, array $extra = []): array
{
    return array_merge([
        'bl_no'        => $blNo,
        'container_no' => $containerNo,
        'po_no'        => $poNo
    ], $extra);
}

$batchStartTime = time();
$batchDuration  = 60;
$pauseDuration  = 10;

$inserted  = 0;
$updated   = 0;
$failed    = 0;
$skipped   = 0;
$processed = 0;

$api_url = API_URL;
$api_key = API_KEY;

if (empty($api_url) || empty($api_key)) {
    writeTrackingLog('ERROR', 'API configuration is missing', [
        'api_url_empty' => empty($api_url),
        'api_key_empty' => empty($api_key),
        'stage'         => 'CONFIGURATION'
    ]);
    exit('API configuration is missing.');
}

writeTrackingLog('INFO', 'Container tracking process started', [
    'api_url' => $api_url,
    'stage'   => 'PROCESS_START'
]);

$blQuery = "SELECT V.BL_NO, V.CONTAINER_NO, V.PO_NUMBER
            FROM APPS.YTY_VW_PO_LATEX_TRACKER V
            WHERE V.ETA_DATE >= TRUNC(SYSDATE) - 5";

$queryblDets = oci_parse($dbcon, $blQuery);
if (!$queryblDets) {
    $failed++;
    $error = getOracleError();
    writeTrackingLog('ERROR', 'Failed to prepare source container query', [
        'oracle_code'   => $error['code'],
        'oracle_error'  => $error['message'],
        'oracle_offset' => $error['offset'],
        'stage'         => 'SOURCE_QUERY_PREPARE'
    ]);
    exit('Error preparing container information: ' . $error['message']);
}

if (!oci_execute($queryblDets)) {
    $failed++;
    $error = getOracleError($queryblDets);
    writeTrackingLog('ERROR', 'Failed to execute source container query', [
        'oracle_code'   => $error['code'],
        'oracle_error'  => $error['message'],
        'oracle_offset' => $error['offset'],
        'stage'         => 'SOURCE_QUERY_EXECUTE'
    ]);
    exit('Error fetching container information: ' . $error['message']);
}

while ($blRow = oci_fetch_array($queryblDets, OCI_ASSOC + OCI_RETURN_NULLS)) {
    $processed++;
    $bl_no       = trim((string)($blRow['BL_NO'] ?? ''));
    $containerNo = trim((string)($blRow['CONTAINER_NO'] ?? ''));
    $po_no       = trim((string)($blRow['PO_NUMBER'] ?? ''));

    writeTrackingLog('INFO', 'Processing container', recordContext($bl_no, $containerNo, $po_no, [
        'record_number' => $processed,
        'stage'         => 'RECORD_START'
    ]));

    if ($containerNo === '') {
        $failed++;
        writeTrackingLog('ERROR', 'Container number is empty', recordContext($bl_no, $containerNo, $po_no, [
            'stage' => 'SOURCE_DATA_VALIDATION'
        ]));
        continue;
    }

    if ((time() - $batchStartTime) >= $batchDuration) {
        writeTrackingLog('INFO', 'Batch limit reached. Pausing API processing', [
            'stage'         => 'BATCH_PAUSE',
            'sleep_seconds' => $pauseDuration
        ]);
        sleep($pauseDuration);
        $batchStartTime = time();
    }

    $queryParams = http_build_query([
        'referenceType'   => 'CONTAINER',
        'referenceNumber' => $containerNo
    ]);
    $requestUrl = $api_url . '?' . $queryParams;

    writeTrackingLog('INFO', 'Calling tracking API', recordContext($bl_no, $containerNo, $po_no, [
        'url'   => $requestUrl,
        'stage' => 'API_REQUEST'
    ]));

    $ch = curl_init();
    curl_setopt_array($ch, [
        CURLOPT_URL            => $requestUrl,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_CONNECTTIMEOUT => 10,
        CURLOPT_TIMEOUT        => 60,
        CURLOPT_HTTPGET        => true,
        CURLOPT_HTTPHEADER     => [
            'Accept: application/json',
            'Authorization: Bearer ' . $api_key,
            'User-Agent: YTY Group-BookingTracking/1.0'
        ]
    ]);

    $apiResponse = curl_exec($ch);
    $httpStatus  = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    $curlError   = curl_error($ch);
    $curlErrno   = curl_errno($ch);
    $totalTime   = curl_getinfo($ch, CURLINFO_TOTAL_TIME);
    curl_close($ch);

    if ($apiResponse === false || $curlError !== '' || $httpStatus < 200 || $httpStatus >= 300) {
        $failed++;
        writeTrackingLog('ERROR', 'Tracking API request failed', recordContext($bl_no, $containerNo, $po_no, [
            'http_status'  => $httpStatus,
            'curl_errno'   => $curlErrno,
            'curl_error'   => $curlError,
            'total_time'   => $totalTime,
            'url'          => $requestUrl,
            'api_response' => substr((string)$apiResponse, 0, 10000),
            'stage'        => 'API_REQUEST'
        ]));
        continue;
    }

    writeTrackingLog('INFO', 'Tracking API request successful', recordContext($bl_no, $containerNo, $po_no, [
        'http_status'     => $httpStatus,
        'total_time'      => $totalTime,
        'response_length' => strlen((string)$apiResponse),
        'stage'           => 'API_RESPONSE'
    ]));

    $result = json_decode($apiResponse);
    if (json_last_error() !== JSON_ERROR_NONE || !$result) {
        $failed++;
        writeTrackingLog('ERROR', 'Invalid JSON response from tracking API', recordContext($bl_no, $containerNo, $po_no, [
            'json_error'   => json_last_error_msg(),
            'api_response' => substr((string)$apiResponse, 0, 10000),
            'stage'        => 'JSON_DECODE'
        ]));
        continue;
    }

    $data           = $result->data ?? null;
    $apiContainerNo = '';
    $apiBlNo        = '';
    $containers     = [];

    if (!$data && isset($result->shipment)) {
        $shipment       = $result->shipment;
        $apiContainerNo = trim((string)($shipment->references->containerNumber ?? $shipment->referenceNumber ?? ''));
        $apiBlNo        = trim((string)($shipment->references->blNumber ?? ''));
        $containers     = $shipment->containers ?? [];
    } else {
        $apiContainerNo = '';
        $apiBlNo        = trim((string)($data->metadata->bl_number ?? $data->metadata->bl_no ?? ''));
        $containers     = $data->containers ?? [];
    }

    if (!is_array($containers) || count($containers) === 0) {
        if ($apiContainerNo !== '' && strcasecmp($apiContainerNo, $containerNo) === 0) {
            $containers = [];
        } else {
            $failed++;
            writeTrackingLog('ERROR', 'No container data returned by API', recordContext($bl_no, $containerNo, $po_no, [
                'api_container_no' => $apiContainerNo,
                'api_bl_no'        => $apiBlNo,
                'stage'            => 'API_CONTAINER_DATA'
            ]));
            continue;
        }
    }

    $container = null;
    foreach ($containers as $item) {
        $itemNo = trim((string)($item->number ?? ''));
        if ($itemNo !== '' && strcasecmp($itemNo, $containerNo) === 0) {
            $container = $item;
            break;
        }
    }

    if (!$container && count($containers) === 1) {
        $container = $containers[0];
        writeTrackingLog('WARNING', 'Container matched using single-container fallback', recordContext($bl_no, $containerNo, $po_no, [
            'stage' => 'CONTAINER_MATCH_FALLBACK'
        ]));
    }

    if (!$container) {
        $failed++;
        $availableContainers = array_map(fn($item) => trim((string)($item->number ?? '')), $containers);
        writeTrackingLog('ERROR', 'Requested container not found in API response', recordContext($bl_no, $containerNo, $po_no, [
            'api_bl_no'            => $apiBlNo,
            'available_containers' => $availableContainers,
            'stage'                => 'CONTAINER_MATCH'
        ]));
        continue;
    }

    $containerNumber = trim((string)($container->number ?? $apiContainerNo));
    if ($containerNumber === '') {
        $failed++;
        writeTrackingLog('ERROR', 'Container number is empty in API response', recordContext($bl_no, $containerNo, $po_no, [
            'stage' => 'API_CONTAINER_NUMBER'
        ]));
        continue;
    }

    $isoCode = trim((string)($container->iso_code ?? $container->isoCode ?? ''));
    if ($bl_no === '') {
        $bl_no = $apiBlNo;
    }

    $sid = null;
    if ($bl_no !== '' && $containerNumber !== '') {
        $sqlExists = "SELECT SID FROM APPS.XXYTY_CONTAINER_BOOKING_DET
                      WHERE UPPER(TRIM(BL_NO)) = UPPER(TRIM(:bl_no))
                      AND UPPER(TRIM(CONTAINER_NO)) = UPPER(TRIM(:container_no))";
        $queryExists = oci_parse($dbcon, $sqlExists);
        if ($queryExists) {
            oci_bind_by_name($queryExists, ':bl_no', $bl_no);
            oci_bind_by_name($queryExists, ':container_no', $containerNumber);
        }
    } else {
        $sqlExists = "SELECT SID FROM APPS.XXYTY_CONTAINER_BOOKING_DET
                      WHERE UPPER(TRIM(CONTAINER_NO)) = UPPER(TRIM(:container_no))";
        $queryExists = oci_parse($dbcon, $sqlExists);
        if ($queryExists) {
            oci_bind_by_name($queryExists, ':container_no', $containerNumber);
        }
    }

    if (!$queryExists) {
        $failed++;
        $error = getOracleError();
        writeTrackingLog('ERROR', 'Failed to prepare existing-record query', recordContext($bl_no, $containerNumber, $po_no, [
            'oracle_code'   => $error['code'],
            'oracle_error'  => $error['message'],
            'oracle_offset' => $error['offset'],
            'stage'         => 'SELECT_EXISTING_RECORD_PREPARE'
        ]));
        continue;
    }

    if (!oci_execute($queryExists)) {
        $failed++;
        $error = getOracleError($queryExists);
        writeTrackingLog('ERROR', 'Failed to execute existing-record query', recordContext($bl_no, $containerNumber, $po_no, [
            'oracle_code'   => $error['code'],
            'oracle_error'  => $error['message'],
            'oracle_offset' => $error['offset'],
            'stage'         => 'SELECT_EXISTING_RECORD'
        ]));
        continue;
    }

    $existing = oci_fetch_array($queryExists, OCI_ASSOC + OCI_RETURN_NULLS);

    if ($existing && !empty($existing['SID'])) {
        $sid = $existing['SID'];
        $sqlUpdate = "UPDATE APPS.XXYTY_CONTAINER_BOOKING_DET
                      SET BL_NO = :bl_no,
                          CONTAINER_NO = :container_no,
                          CONTAINER_ISO_CODE = :iso_code,
                          SO_NO = :po_no,
                          updated_date = SYSDATE

                      WHERE SID = :sid";

        $queryUpdate = oci_parse($dbcon, $sqlUpdate);
        if (!$queryUpdate) {
            $failed++;
            $error = getOracleError();
            writeTrackingLog('ERROR', 'Failed to prepare container UPDATE', recordContext($bl_no, $containerNumber, $po_no, [
                'sid'          => $sid,
                'oracle_code'  => $error['code'],
                'oracle_error' => $error['message'],
                'stage'        => 'UPDATE_CONTAINER_PREPARE'
            ]));
            continue;
        }

        oci_bind_by_name($queryUpdate, ':bl_no', $bl_no);
        oci_bind_by_name($queryUpdate, ':container_no', $containerNumber);
        oci_bind_by_name($queryUpdate, ':iso_code', $isoCode);
        oci_bind_by_name($queryUpdate, ':po_no', $po_no);
        oci_bind_by_name($queryUpdate, ':sid', $sid);

        if (!oci_execute($queryUpdate)) {
            $failed++;
            $error = getOracleError($queryUpdate);
            writeTrackingLog('ERROR', 'Container UPDATE failed', recordContext($bl_no, $containerNumber, $po_no, [
                'sid'           => $sid,
                'iso_code'      => $isoCode,
                'oracle_code'   => $error['code'],
                'oracle_error'  => $error['message'],
                'oracle_offset' => $error['offset'],
                'stage'         => 'UPDATE_CONTAINER'
            ]));
            continue;
        }

        $updated++;
        writeTrackingLog('INFO', 'Container updated successfully', recordContext($bl_no, $containerNumber, $po_no, [
            'sid'      => $sid,
            'iso_code' => $isoCode,
            'stage'    => 'UPDATE_CONTAINER_SUCCESS'
        ]));
    } else {
        $sqlMax   = "SELECT NVL(MAX(SID) + 1, 1) MAX_ID FROM APPS.XXYTY_CONTAINER_BOOKING_DET";
        $queryMax = oci_parse($dbcon, $sqlMax);

        if (!$queryMax) {
            $failed++;
            $error = getOracleError();
            writeTrackingLog('ERROR', 'Failed to prepare MAX SID query', recordContext($bl_no, $containerNumber, $po_no, [
                'oracle_code'  => $error['code'],
                'oracle_error' => $error['message'],
                'stage'        => 'MAX_SID_PREPARE'
            ]));
            continue;
        }

        if (!oci_execute($queryMax)) {
            $failed++;
            $error = getOracleError($queryMax);
            writeTrackingLog('ERROR', 'Failed to execute MAX SID query', recordContext($bl_no, $containerNumber, $po_no, [
                'oracle_code'  => $error['code'],
                'oracle_error' => $error['message'],
                'stage'        => 'MAX_SID_EXECUTE'
            ]));
            continue;
        }

        $maxRecord = oci_fetch_array($queryMax, OCI_ASSOC + OCI_RETURN_NULLS);
        if (!$maxRecord || empty($maxRecord['MAX_ID'])) {
            $failed++;
            writeTrackingLog('ERROR', 'Unable to generate new SID', recordContext($bl_no, $containerNumber, $po_no, [
                'stage' => 'GENERATE_SID'
            ]));
            continue;
        }

        $sid = $maxRecord['MAX_ID'];
        $sqlInsert = "INSERT INTO APPS.XXYTY_CONTAINER_BOOKING_DET
                      (SID, BL_NO, CONTAINER_NO, CONTAINER_ISO_CODE, SO_NO, CREATION_DATE, BOOKING_TYPE, TRANSACTION_TYPE)
                      VALUES
                      (:sid, :bl_no, :container_no, :iso_code, :po_no, SYSDATE, 'BL', 'I')";

        $queryInsert = oci_parse($dbcon, $sqlInsert);
        if (!$queryInsert) {
            $failed++;
            $error = getOracleError();
            writeTrackingLog('ERROR', 'Failed to prepare container INSERT', recordContext($bl_no, $containerNumber, $po_no, [
                'sid'          => $sid,
                'oracle_code'  => $error['code'],
                'oracle_error' => $error['message'],
                'stage'        => 'INSERT_CONTAINER_PREPARE'
            ]));
            continue;
        }

        oci_bind_by_name($queryInsert, ':sid', $sid);
        oci_bind_by_name($queryInsert, ':bl_no', $bl_no);
        oci_bind_by_name($queryInsert, ':container_no', $containerNumber);
        oci_bind_by_name($queryInsert, ':iso_code', $isoCode);
        oci_bind_by_name($queryInsert, ':po_no', $po_no);

        if (!oci_execute($queryInsert)) {
            $failed++;
            $error = getOracleError($queryInsert);
            writeTrackingLog('ERROR', 'Container INSERT failed', recordContext($bl_no, $containerNumber, $po_no, [
                'sid'           => $sid,
                'iso_code'      => $isoCode,
                'oracle_code'   => $error['code'],
                'oracle_error'  => $error['message'],
                'oracle_offset' => $error['offset'],
                'stage'         => 'INSERT_CONTAINER'
            ]));
            continue;
        }

        $inserted++;
        writeTrackingLog('INFO', 'Container inserted successfully', recordContext($bl_no, $containerNumber, $po_no, [
            'sid'      => $sid,
            'iso_code' => $isoCode,
            'stage'    => 'INSERT_CONTAINER_SUCCESS'
        ]));
    }

    $sqlDeleteRoute   = "DELETE FROM APPS.XXYTY_CONTAINER_ROUTE_DETAILS WHERE SID = :sid";
    $queryDeleteRoute = oci_parse($dbcon, $sqlDeleteRoute);

    if (!$queryDeleteRoute) {
        $failed++;
        $error = getOracleError();
        writeTrackingLog('ERROR', 'Failed to prepare route DELETE query', recordContext($bl_no, $containerNumber, $po_no, [
            'sid'          => $sid,
            'oracle_code'  => $error['code'],
            'oracle_error' => $error['message'],
            'stage'        => 'DELETE_ROUTE_PREPARE'
        ]));
        continue;
    }

    oci_bind_by_name($queryDeleteRoute, ':sid', $sid);

    if (!oci_execute($queryDeleteRoute)) {
        $failed++;
        $error = getOracleError($queryDeleteRoute);
        writeTrackingLog('ERROR', 'Route DELETE failed', recordContext($bl_no, $containerNumber, $po_no, [
            'sid'           => $sid,
            'oracle_code'   => $error['code'],
            'oracle_error'  => $error['message'],
            'oracle_offset' => $error['offset'],
            'stage'         => 'DELETE_ROUTE'
        ]));
        continue;
    }

    $events = $container->events ?? [];
    if (!is_array($events) || count($events) === 0) {
        $skipped++;
        writeTrackingLog('WARNING', 'No tracking events returned for container', recordContext($bl_no, $containerNumber, $po_no, [
            'sid'   => $sid,
            'stage' => 'NO_EVENTS'
        ]));
        continue;
    }

    $routeInserted = 0;
    $routeFailed   = 0;

    foreach ($events as $eventIndex => $event) {
        $eventLocation = $event->location ?? null;
        $coordinates   = is_object($eventLocation) ? ($eventLocation->coordinates ?? null) : null;

        $location = [
            'name'         => is_object($eventLocation) ? trim((string)($eventLocation->name ?? '')) : '',
            'state'        => is_object($eventLocation) ? trim((string)($eventLocation->state ?? '')) : '',
            'country'      => is_object($eventLocation) ? trim((string)($eventLocation->country ?? '')) : '',
            'country_code' => is_object($eventLocation) ? trim((string)($eventLocation->countryCode ?? $eventLocation->country_code ?? '')) : '',
            'locode'       => is_object($eventLocation) ? trim((string)($eventLocation->unLocode ?? $eventLocation->locode ?? '')) : '',
            'lat'          => is_object($coordinates) ? trim((string)($coordinates->lat ?? '')) : '',
            'lng'          => is_object($coordinates) ? trim((string)($coordinates->lng ?? '')) : '',
            'timezone'     => is_object($eventLocation) ? trim((string)($eventLocation->timezone ?? '')) : ''
        ];

        $actual    = !empty($event->isActual ?? $event->actual) ? 'Y' : 'N';
        $eventDate = null;
        $rawDate   = $event->date ?? $event->event_local_datetime ?? null;

        if (!empty($rawDate)) {
            $timestamp = strtotime((string)$rawDate);
            if ($timestamp !== false) {
                $eventDate = date('Y-m-d H:i:s', $timestamp);
            } else {
                writeTrackingLog('WARNING', 'Unable to parse event date', recordContext($bl_no, $containerNumber, $po_no, [
                    'sid'         => $sid,
                    'event_index' => $eventIndex,
                    'raw_date'    => $rawDate,
                    'stage'       => 'EVENT_DATE_PARSE'
                ]));
            }
        }

        $description   = trim((string)($event->description ?? ''));
        $status        = trim((string)($event->eventTypeCode ?? ''));
        $eventTimezone = trim((string)($event->event_timezone ?? $event->timezone ?? $location['timezone'] ?? ''));

        $sqlRoute = "INSERT INTO APPS.XXYTY_CONTAINER_ROUTE_DETAILS
                     (SID, BL_NO, CONTAINER_NO, PORT_NAME, STATE, COUNTRY, COUNTRY_CODE, LOCODE, LAT, LNG, ACTUAL_ROUTE, DESCRIPTION_C, LOCATION_DATE, STATUS, EVENT_TIMEZONE)
                     VALUES
                     (:sid, :bl_no, :container_no, :port_name, :state, :country, :country_code, :locode, :lat, :lng, :actual_route, :description_c, TO_DATE(:location_date, 'YYYY-MM-DD HH24:MI:SS'), :status, :event_timezone)";

        $queryRoute = oci_parse($dbcon, $sqlRoute);
        if (!$queryRoute) {
            $failed++;
            $routeFailed++;
            $error = getOracleError();
            writeTrackingLog('ERROR', 'Failed to prepare route INSERT', recordContext($bl_no, $containerNumber, $po_no, [
                'sid'           => $sid,
                'event_index'   => $eventIndex,
                'port_name'     => $location['name'],
                'country'       => $location['country'],
                'locode'        => $location['locode'],
                'location_date' => $eventDate,
                'status'        => $status,
                'oracle_code'   => $error['code'],
                'oracle_error'  => $error['message'],
                'stage'         => 'INSERT_ROUTE_PREPARE'
            ]));
            continue;
        }

        oci_bind_by_name($queryRoute, ':sid', $sid);
        oci_bind_by_name($queryRoute, ':bl_no', $bl_no);
        oci_bind_by_name($queryRoute, ':container_no', $containerNumber);
        oci_bind_by_name($queryRoute, ':port_name', $location['name']);
        oci_bind_by_name($queryRoute, ':state', $location['state']);
        oci_bind_by_name($queryRoute, ':country', $location['country']);
        oci_bind_by_name($queryRoute, ':country_code', $location['country_code']);
        oci_bind_by_name($queryRoute, ':locode', $location['locode']);
        oci_bind_by_name($queryRoute, ':lat', $location['lat']);
        oci_bind_by_name($queryRoute, ':lng', $location['lng']);
        oci_bind_by_name($queryRoute, ':actual_route', $actual);
        oci_bind_by_name($queryRoute, ':description_c', $description);
        oci_bind_by_name($queryRoute, ':location_date', $eventDate);
        oci_bind_by_name($queryRoute, ':status', $status);
        oci_bind_by_name($queryRoute, ':event_timezone', $eventTimezone);

        if (!oci_execute($queryRoute)) {
            $error = getOracleError($queryRoute);
            $failed++;
            $routeFailed++;
            writeTrackingLog('ERROR', 'Route INSERT failed', recordContext($bl_no, $containerNumber, $po_no, [
                'sid'            => $sid,
                'event_index'    => $eventIndex,
                'port_name'      => $location['name'],
                'state'          => $location['state'],
                'country'        => $location['country'],
                'country_code'   => $location['country_code'],
                'locode'         => $location['locode'],
                'lat'            => $location['lat'],
                'lng'            => $location['lng'],
                'actual_route'   => $actual,
                'description'    => $description,
                'location_date'  => $eventDate,
                'status'         => $status,
                'event_timezone' => $eventTimezone,
                'oracle_code'    => $error['code'],
                'oracle_error'   => $error['message'],
                'oracle_offset'  => $error['offset'],
                'stage'          => 'INSERT_ROUTE'
            ]));
            continue;
        }

        $routeInserted++;
        writeTrackingLog('INFO', 'Route event inserted successfully', recordContext($bl_no, $containerNumber, $po_no, [
            'sid'         => $sid,
            'event_index' => $eventIndex,
            'port_name'   => $location['name'],
            'status'      => $status,
            'stage'       => 'INSERT_ROUTE_SUCCESS'
        ]));
    }

    writeTrackingLog('INFO', 'Container processing completed', recordContext($bl_no, $containerNumber, $po_no, [
        'sid'            => $sid,
        'route_inserted' => $routeInserted,
        'route_failed'   => $routeFailed,
        'stage'          => 'RECORD_COMPLETE'
    ]));
}

if (!oci_commit($dbcon)) {
    $error = getOracleError($dbcon);
    writeTrackingLog('ERROR', 'Database commit failed', [
        'oracle_code'   => $error['code'],
        'oracle_error'  => $error['message'],
        'oracle_offset' => $error['offset'],
        'stage'         => 'DATABASE_COMMIT'
    ]);
    exit('Database commit failed: ' . $error['message']);
}

$summary = [
    'processed' => $processed,
    'inserted'  => $inserted,
    'updated'   => $updated,
    'failed'    => $failed,
    'skipped'   => $skipped,
    'stage'     => 'PROCESS_COMPLETE'
];

writeTrackingLog('INFO', 'Container tracking completed', $summary);

echo 'Container tracking completed. Processed: ' . $processed .
    ', Inserted: ' . $inserted .
    ', Updated: ' . $updated .
    ', Failed: ' . $failed .
    ', Skipped: ' . $skipped . '.';

exit;
