<?php
require '../vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// SETTINGS
$primaryFile = 'original_stock.xlsx';
$secondaryFile = 'inventory_items_xero.xlsx';
$primaryMatchCol = 'C'; // Column to match in primary (e.g. 'C')
$secondaryMatchCol = 'B'; // Column to match in secondary (e.g. 'B')

// Load spreadsheets
$primarySpreadsheet = IOFactory::load($primaryFile);
$secondarySpreadsheet = IOFactory::load($secondaryFile);

$primarySheet = $primarySpreadsheet->getActiveSheet();
$secondarySheet = $secondarySpreadsheet->getActiveSheet();

// Convert column letters to numbers
$primaryColIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($primaryMatchCol);
$secondaryColIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($secondaryMatchCol);

// Step 1: Build lookup from secondary file [match value => A column value]
$secondaryData = [];
foreach ($secondarySheet->getRowIterator() as $row) {
    $rowIndex = $row->getRowIndex();
    $matchValue = $secondarySheet->getCellByColumnAndRow($secondaryColIndex, $rowIndex)->getValue();
    $aValue = $secondarySheet->getCellByColumnAndRow(1, $rowIndex)->getValue(); // Column A
    if ($matchValue !== null) {
        $secondaryData[$matchValue] = $aValue;
    }
}

// Step 2: Go through primary file and update A column if match is found
foreach ($primarySheet->getRowIterator() as $row) {
    $rowIndex = $row->getRowIndex();
    $primaryMatchValue = $primarySheet->getCellByColumnAndRow($primaryColIndex, $rowIndex)->getValue();

    if (isset($secondaryData[$primaryMatchValue])) {
        $primarySheet->setCellValueByColumnAndRow(1, $rowIndex, $secondaryData[$primaryMatchValue]);
    }
}

// Save updated primary file
$writer = new Xlsx($primarySpreadsheet);
$writer->save('primary_updated.xlsx');

echo "Done. Saved as 'primary_updated.xlsx'.\n";