<?php
ob_start();
require("dbconnect.php");
require("config.php");
require("function.php");

header('Content-Type: application/json');

if (!isset($_SESSION["username_" . $dbname])) {
    echo json_encode(['ok' => false, 'error' => 'Not authenticated']);
    exit;
}

if (!isset($_FILES['xlsxfile']) || $_FILES['xlsxfile']['error'] != UPLOAD_ERR_OK) {
    echo json_encode(['ok' => false, 'error' => 'No file received or upload error.']);
    exit;
}

$tmp = $_FILES['xlsxfile']['tmp_name'];
$ext = strtolower(pathinfo($_FILES['xlsxfile']['name'], PATHINFO_EXTENSION));
if (!in_array($ext, ['xlsx', 'xls'])) {
    echo json_encode(['ok' => false, 'error' => 'Only .xlsx files are supported.']);
    exit;
}

// ── Parse xlsx as zip (xlsx = zip archive) ────────────────────────────────────
$zip = new ZipArchive();
if ($zip->open($tmp) !== true) {
    echo json_encode(['ok' => false, 'error' => 'Could not open Excel file.']);
    exit;
}

// ── Read sheet data ───────────────────────────────────────────────────────────
// Parse the shared strings table
$sharedStrings = [];
$ssContent = $zip->getFromName('xl/sharedStrings.xml');
if ($ssContent !== false) {
    $ssXml = simplexml_load_string($ssContent);
    if ($ssXml) {
        foreach ($ssXml->si as $si) {
            // Concatenate all t elements (handles rich text)
            $txt = '';
            foreach ($si->r as $r) {
                $txt .= (string)$r->t;
            }
            if (empty($txt)) {
                $txt = (string)$si->t;
            }
            $sharedStrings[] = $txt;
        }
    }
}

// Parse the worksheet
$wsContent = $zip->getFromName('xl/worksheets/sheet1.xml');
if ($wsContent === false) {
    $zip->close();
    echo json_encode(['ok' => false, 'error' => 'Could not read worksheet.']);
    exit;
}

$wsXml = simplexml_load_string($wsContent);
$wsXml->registerXPathNamespace('ns', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');

$cellData = [];  // [rowNum][colNum] = value (1-indexed)
foreach ($wsXml->sheetData->row as $row) {
    $rowNum = (int)$row['r'];
    foreach ($row->c as $cell) {
        $ref  = (string)$cell['r'];
        $type = (string)$cell['t'];
        // Parse column letter from ref (e.g. A1 -> col 1)
        preg_match('/^([A-Z]+)(\d+)$/', $ref, $m);
        $colStr = $m[1];
        $col = 0;
        foreach (str_split($colStr) as $ch) {
            $col = $col * 26 + (ord($ch) - 64);
        }
        $val = (string)$cell->v;
        if ($type === 's' && isset($sharedStrings[(int)$val])) {
            $val = $sharedStrings[(int)$val];
        }
        $cellData[$rowNum][$col] = $val;
    }
}

// First row is headers — build column map
$headers = [];
if (isset($cellData[1])) {
    foreach ($cellData[1] as $col => $hdr) {
        $headers[$col] = strtolower(trim(str_replace([' ', '.'], '', $hdr)));
    }
}

// Column name aliases
$aliases = [
    'description' => ['description','particular','desc'],
    'ctn'         => ['ctn','carton','cartons'],
    'qty'         => ['qty','quantity'],
    'tqty'        => ['ttlqty','tqty','totalqty','tqty'],
    'amt'         => ['ttlamount','ttlamt','amt','amount','totalamount'],
    'cbm'         => ['cbm'],
    'weight'      => ['weight','wt'],
];
$fieldByCol = [];
foreach ($headers as $col => $hdr) {
    foreach ($aliases as $field => $aliasList) {
        if (in_array($hdr, $aliasList) && !isset($fieldByCol[$col])) {
            $fieldByCol[$col] = $field;
        }
    }
}

// Build data rows (rows 2+)
$maxRow = max(array_keys($cellData));
$rows = [];
for ($r = 2; $r <= $maxRow; $r++) {
    if (!isset($cellData[$r])) continue;
    $rowData = ['description'=>'','ctn'=>'','qty'=>'','tqty'=>'','amt'=>'','cbm'=>'','weight'=>''];
    foreach ($cellData[$r] as $col => $val) {
        if (isset($fieldByCol[$col])) {
            $rowData[$fieldByCol[$col]] = $val;
        }
    }
    // Skip completely empty rows
    $meaningful = array_filter($rowData, function($v) { return $v !== ''; });
    if (empty($meaningful)) continue;
    $rows[] = ['_excelRow' => $r, 'data' => $rowData];
}

// ── Extract embedded images + map to excel row ────────────────────────────────
$images = [];  // excelRow (1-based) => base64 data URI

$drawingContent = $zip->getFromName('xl/drawings/drawing1.xml');
$relsContent    = $zip->getFromName('xl/drawings/_rels/drawing1.xml.rels');

if ($drawingContent !== false && $relsContent !== false) {
    // Build rId -> image file path map
    $relsXml = simplexml_load_string($relsContent);
    $relsXml->registerXPathNamespace('r', 'http://schemas.openxmlformats.org/package/2006/relationships');
    $ridMap = [];
    foreach ($relsXml->Relationship as $rel) {
        $rid    = (string)$rel['Id'];
        $target = (string)$rel['Target'];
        // Normalise: ../media/image1.png -> xl/media/image1.png
        $target = preg_replace('/^\.\.\//', 'xl/', $target);
        $ridMap[$rid] = $target;
    }

    // Parse drawing XML for image row anchors
    $drawXml = simplexml_load_string($drawingContent);
    $drawXml->registerXPathNamespace('xdr', 'http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing');
    $drawXml->registerXPathNamespace('a',   'http://schemas.openxmlformats.org/drawingml/2006/main');
    $drawXml->registerXPathNamespace('r',   'http://schemas.openxmlformats.org/officeDocument/2006/relationships');

    foreach ($drawXml->children('http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing') as $nodeName => $anchor) {
        if ($nodeName !== 'twoCellAnchor' && $nodeName !== 'oneCellAnchor') continue;
        $ns  = 'http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing';
        $from = $anchor->children($ns)->from;
        if (!$from) continue;
        // from->row is 0-based
        $fromRow0 = (int)(string)$from->children('http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing')->row;
        $excelRow = $fromRow0 + 1;  // convert to 1-based Excel row

        // Get rId from blipFill
        $pic = $anchor->children($ns)->pic;
        if (!$pic) continue;
        $blip = $pic->blipFill->children('http://schemas.openxmlformats.org/drawingml/2006/main')->blip;
        if (!$blip) continue;
        $rId = (string)$blip->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships')['embed'];
        if (!$rId || !isset($ridMap[$rId])) continue;

        $imgPath = $ridMap[$rId];
        $imgBytes = $zip->getFromName($imgPath);
        if ($imgBytes === false) continue;

        $ext = strtolower(pathinfo($imgPath, PATHINFO_EXTENSION));
        $mime = $ext === 'jpg' || $ext === 'jpeg' ? 'image/jpeg' : "image/$ext";
        $images[$excelRow] = 'data:' . $mime . ';base64,' . base64_encode($imgBytes);
    }
}

$zip->close();

// Map images to data row index (0-based output row index)
// rows[] has _excelRow — match image excelRow to the closest data row
$output = [];
foreach ($rows as $idx => $r) {
    $excelRow = $r['_excelRow'];
    // Find image at or near this excel row (within ±3 rows, take closest)
    $bestRid  = null;
    $bestDist = 999;
    foreach ($images as $imgRow => $b64) {
        $dist = abs($imgRow - $excelRow);
        if ($dist < $bestDist && $dist <= 3) {
            $bestDist = $dist;
            $bestRid  = $imgRow;
        }
    }
    $entry = $r['data'];
    $entry['image'] = $bestRid !== null ? $images[$bestRid] : '';
    $output[] = $entry;
}

echo json_encode(['ok' => true, 'rows' => $output]);
