38815-vm/api/import_bom.php

<?php
session_start();
require_once __DIR__ . '/../db/config.php';

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

if (!isset($_SESSION['user_id']) || $_SESSION['role'] !== 'admin') {
    http_response_code(403);
    echo json_encode(['error' => 'Unauthorized']);
    exit;
}

if ($_SERVER['REQUEST_METHOD'] !== 'POST' || !isset($_FILES['bomFile'])) {
    http_response_code(400);
    echo json_encode(['error' => 'No BOM file provided']);
    exit;
}

$file = $_FILES['bomFile'];
if ($file['error'] !== UPLOAD_ERR_OK) {
    http_response_code(400);
    echo json_encode(['error' => 'File upload error']);
    exit;
}

$jobName = $_POST['jobName'] ?? pathinfo($file['name'], PATHINFO_FILENAME);
$existingJobId = $_POST['jobId'] ?? null;

$zip = new ZipArchive();
if ($zip->open($file['tmp_name']) !== TRUE) {
    http_response_code(400);
    echo json_encode(['error' => 'Invalid XLSX file format']);
    exit;
}

try {
    $db = db();
    $db->beginTransaction();

    // 1. Create or Select Job
    if ($existingJobId) {
        $stmt = $db->prepare("SELECT id FROM jobs WHERE id = ?");
        $stmt->execute([$existingJobId]);
        if (!$stmt->fetch()) throw new Exception("Job not found");
        $jobId = $existingJobId;
    } else {
        $stmt = $db->prepare("INSERT INTO jobs (name, status) VALUES (?, 'planned')");
        $stmt->execute([$jobName]);
        $jobId = $db->lastInsertId();
    }

    // 2. Read Shared Strings
    $sharedStringsRaw = $zip->getFromName('xl/sharedStrings.xml');
    $strings = [];
    if ($sharedStringsRaw) {
        $xml = @simplexml_load_string($sharedStringsRaw);
        if ($xml && isset($xml->si)) {
            foreach ($xml->si as $si) {
                $t = '';
                if (isset($si->t)) $t = (string)$si->t;
                elseif (isset($si->r)) foreach ($si->r as $r) $t .= (string)$r->t;
                $strings[] = $t;
            }
        }
    }

    // 3. Extract Image Anchors (Row -> Image Path)
    $rowImages = [];
    $drawingRelsRaw = $zip->getFromName('xl/drawings/_rels/drawing1.xml.rels');
    $rels = [];
    if ($drawingRelsRaw) {
        $xml = @simplexml_load_string($drawingRelsRaw);
        if ($xml) {
            foreach ($xml->Relationship as $rel) {
                $rels[(string)$rel['Id']] = (string)$rel['Target'];
            }
        }
    }
    
    $drawingRaw = $zip->getFromName('xl/drawings/drawing1.xml');
    if ($drawingRaw) {
        $xml = @simplexml_load_string($drawingRaw);
        if ($xml) {
            $xml->registerXPathNamespace('xdr', 'http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing');
            $xml->registerXPathNamespace('a', 'http://schemas.openxmlformats.org/drawingml/2006/main');
            
            $anchors = $xml->xpath('//xdr:twoCellAnchor | //xdr:oneCellAnchor');
            if ($anchors) {
                foreach ($anchors as $anchor) {
                    $from = $anchor->xpath('.//xdr:from/xdr:row');
                    $blip = $anchor->xpath('.//a:blip');
                    if (!$from || !$blip) continue;
                    
                    $row = (int)$from[0];
                    $embedAttr = $blip[0]->attributes('r', true);
                    if (!$embedAttr) {
                        // try to get without true for prefix if namespaced correctly in some versions
                        $embedAttr = $blip[0]->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships');
                    }
                    
                    if ($embedAttr && isset($embedAttr['embed'])) {
                        $rId = (string)$embedAttr['embed'];
                        if (isset($rels[$rId])) {
                            $target = str_replace('../', 'xl/', $rels[$rId]);
                            $rowImages[$row] = $target; // $row is 0-indexed in XML
                        }
                    }
                }
            }
        }
    }

    // 4. Read Rows
    $sheetRaw = $zip->getFromName('xl/worksheets/sheet1.xml');
    if (!$sheetRaw) throw new Exception("Could not find sheet1.xml");
    
    $xml = @simplexml_load_string($sheetRaw);
    if (!$xml) throw new Exception("Failed to parse sheet1.xml");

    $headerMap = [];
    $componentsMap = []; // '1.6' => db id
    $rowIndex = 0;
    
    $stmtComp = $db->prepare("
        INSERT INTO components (job_id, parent_id, name, type, quantity, thickness, material, notes, thumbnail_data, order_index)
        VALUES (?, ?, ?, 'part', ?, ?, ?, ?, ?, ?)
    ");

    if (isset($xml->sheetData->row)) {
        foreach ($xml->sheetData->row as $row) {
            $rowData = [];
            $colIndex = 0;
            
            // Handle sparse columns
            foreach ($row->c as $c) {
                // Excel cell reference like "A1", "C2"
                $cellRef = (string)$c['r'];
                $colLetter = preg_replace('/[0-9]/', '', $cellRef);
                // Convert colLetter (A, B, C...) to index (0, 1, 2...)
                $colNum = 0;
                $len = strlen($colLetter);
                for ($i = 0; $i < $len; $i++) {
                    $colNum = $colNum * 26 + (ord($colLetter[$i]) - 64);
                }
                $colIndex = $colNum - 1;
                
                $val = (string)$c->v;
                if (isset($c['t']) && $c['t'] == 's') {
                    $val = $strings[(int)$val] ?? $val;
                }
                $rowData[$colIndex] = $val;
            }
            
            // Ensure all indexes exist up to max column
            if (!empty($rowData)) {
                $maxKey = max(array_keys($rowData));
                for ($i = 0; $i <= $maxKey; $i++) {
                    if (!isset($rowData[$i])) $rowData[$i] = '';
                }
                ksort($rowData);
            }

            if ($rowIndex === 0) {
                foreach ($rowData as $idx => $val) {
                    $headerMap[trim(strtolower($val))] = $idx;
                }
            } else if (!empty($rowData)) {
                // Data row
                $itemIdx = $headerMap['item'] ?? 0;
                $item = $rowData[$itemIdx] ?? '';
                
                $partIdx = $headerMap['part number'] ?? ($headerMap['filename'] ?? -1);
                $partNumber = $partIdx >= 0 ? ($rowData[$partIdx] ?? 'Unknown Part') : 'Unknown Part';
                
                if (empty($item) && $partNumber === 'Unknown Part') {
                    $rowIndex++;
                    continue; // Skip empty
                }
                
                $qtyIdx = $headerMap['qty'] ?? 6;
                $qty = $rowData[$qtyIdx] ?? 1;
                
                $thickIdx = $headerMap['thickness'] ?? -1;
                $thickness = $thickIdx >= 0 ? ($rowData[$thickIdx] ?? '') : '';
                $thickness = str_replace(' in', '"', trim($thickness)); // convert inches
                
                $matIdx = $headerMap['material'] ?? -1;
                $material = $matIdx >= 0 ? ($rowData[$matIdx] ?? '') : '';
                
                $descIdx = $headerMap['description'] ?? -1;
                $notes = $descIdx >= 0 ? ($rowData[$descIdx] ?? '') : '';

                // Thumbnail
                $thumbnailData = null;
                if (isset($rowImages[$rowIndex])) {
                    $imgData = $zip->getFromName($rowImages[$rowIndex]);
                    if ($imgData) {
                        // guess mime type based on extension
                        $ext = strtolower(pathinfo($rowImages[$rowIndex], PATHINFO_EXTENSION));
                        $mime = ($ext == 'png') ? 'image/png' : 'image/jpeg';
                        $thumbnailData = "data:$mime;base64," . base64_encode($imgData);
                    }
                }

                // Figure out parent
                $parentId = null;
                $parts = explode('.', (string)$item);
                if (count($parts) > 1) {
                    array_pop($parts); // remove last segment
                    $parentItem = implode('.', $parts);
                    if (isset($componentsMap[$parentItem])) {
                        $parentId = $componentsMap[$parentItem];
                    }
                }

                $stmtComp->execute([
                    $jobId,
                    $parentId,
                    $partNumber,
                    (int)$qty,
                    $thickness,
                    $material,
                    $notes,
                    $thumbnailData,
                    $rowIndex // order index
                ]);
                
                $compId = $db->lastInsertId();
                $componentsMap[(string)$item] = $compId;
            }
            $rowIndex++;
        }
    }
    
    $zip->close();
    $db->commit();
    
    echo json_encode(['success' => true, 'job_id' => $jobId, 'message' => "Imported successfully!"]);

} catch (Exception $e) {
    if (isset($db)) $db->rollBack();
    http_response_code(500);
    echo json_encode(['error' => 'Import failed: ' . $e->getMessage()]);
}