<?php
require_once __DIR__ . '/../config.php';

function cleanItemCode($code) {
    if (empty($code)) return null;
    return strtoupper(trim($code));
}

function calcFulfillment($ordered, $received) {
    if (empty($ordered) || $ordered == 0) return 0;
    return min(100, round(($received / $ordered) * 100, 2));
}

/**
 * Standardized date parser for Excel and String dates
 */
function parseDate($val, $type = 'date') {
    if (empty($val) || $val === '') return null;
    $val = trim($val);
    if ($val === '' || strtolower($val) === 'null' || $val === '0000-00-00' || $val === '0000-00-00 00:00:00') return null;
    
    // Handle Excel numeric dates (serial numbers)
    if (is_numeric($val) && $val > 10000) {
        $excelEpoch = 25569; // 1970-01-01
        $secondsPerDay = 86400;
        $utcDays = floor($val - $excelEpoch);
        $utcSeconds = floor(($val - floor($val)) * $secondsPerDay);
        $date = gmdate('Y-m-d H:i:s', $utcSeconds + ($utcDays * $secondsPerDay));
        if ($date === '1970-01-01' || $date < '1900-01-01') return null;
        $d = new DateTime($date);
    } else {
        // Normalized separators for string parsing
        $cleaned = str_replace(['/', '.', ' '], '-', $val);
        $formats = ['Y-m-d', 'd-m-Y', 'm-d-Y', 'Y-m-d H:i:s', 'd-m-Y H:i:s'];
        
        foreach ($formats as $format) {
            $d = DateTime::createFromFormat($format, $cleaned);
            if ($d && $d->format($format) === $cleaned) {
                break;
            }
        }
        
        if (!isset($d) || !$d) {
            $timestamp = strtotime($val);
            if (!$timestamp) return null;
            $d = new DateTime();
            $d->setTimestamp($timestamp);
        }
    }
    
    $format = $type === 'datetime' ? '%Y/%m/%d %H:%i:%s' : '%Y/%m/%d';
    return $d->format($format);
}

function calcStockOnHand($itemCode) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("
        SELECT COALESCE(SUM(quantity_in), 0) - COALESCE(SUM(quantity_out), 0) as soh 
        FROM inventory_transaction WHERE item_code = ?
    ");
    $stmt->execute([$itemCode]);
    return $stmt->fetch()['soh'] ?? 0;
}

function calcStockValue($itemCode) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("
        SELECT COALESCE(SUM(quantity_in), 0) as qty_in, COALESCE(SUM(quantity_in * unit_cost), 0) as cost_in,
               COALESCE(SUM(quantity_out), 0) as qty_out
        FROM inventory_transaction WHERE item_code = ?
    ");
    $stmt->execute([$itemCode]);
    $row = $stmt->fetch();
    
    if ($row['qty_in'] == 0) return 0;
    $avgCost = $row['qty_in'] > 0 ? ($row['cost_in'] / $row['qty_in']) : 0;
    $soh = $row['qty_in'] - $row['qty_out'];
    
    return $soh * $avgCost;
}

function getLowStockItems() {
    $pdo = getDBConnection();
    $stmt = $pdo->query("
        SELECT i.ItemCode, i.ItemName, i.Category, i.UnitOfMeasure, i.ReorderLevel,
               COALESCE(SUM(t.quantity_in), 0) - COALESCE(SUM(t.quantity_out), 0) as StockOnHand
        FROM items_master i
        LEFT JOIN inventory_transaction t ON i.ItemCode = t.item_code
        GROUP BY i.ItemCode
        HAVING StockOnHand < i.ReorderLevel OR StockOnHand IS NULL
        ORDER BY StockOnHand ASC
    ");
    return $stmt->fetchAll();
}

function getInventorySummary() {
    $pdo = getDBConnection();
    $stmt = $pdo->query("
        SELECT 
            source_type as Category,
            COUNT(DISTINCT item_code) as ItemCount,
            COALESCE(SUM(quantity_in), 0) as TotalIn,
            COALESCE(SUM(quantity_out), 0) as TotalOut,
            COALESCE(SUM(quantity_in * unit_cost), 0) as TotalValue
        FROM inventory_transaction
        GROUP BY source_type
    ");
    return $stmt->fetchAll();
}

function getProcurementStats($startDate = null, $endDate = null) {
    $pdo = getDBConnection();
    $sql = "SELECT 
                o.OrderID, o.ItemCode, o.ItemDescription, o.Quantity as OrderedQty, o.UnitPrice,
                o.Supplier, o.PO_Date, o.PO_DateApproved,
                g.Quantity_Received,
                CASE WHEN o.Quantity > 0 AND g.Quantity_Received IS NOT NULL 
                     THEN g.Quantity_Received / o.Quantity * 100 ELSE 0 END as FulfillmentPct
            FROM orders o
            LEFT JOIN grv g ON o.Reference = g.Order_No";
    $params = [];
    
    if ($startDate && $endDate) {
        $sql .= " WHERE STR_TO_DATE(o.PO_Date, '%Y/%m/%d') BETWEEN ? AND ?";
        $params = [$startDate, $endDate];
    }
    
    $sql .= " ORDER BY o.PO_Date DESC";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function getSupplierPerformance() {
    $pdo = getDBConnection();
    $stmt = $pdo->query("
        SELECT s.SupplierID, s.SupplierName, s.LeadTime_Days, s.Rating,
               COUNT(DISTINCT o.OrderID) as TotalOrders,
               COALESCE(SUM(o.Quantity * o.UnitPrice), 0) as TotalValue,
               AVG(g.Quantity_Received / o.Quantity * 100) as AvgFulfillment
        FROM suppliers s
        LEFT JOIN orders o ON s.SupplierID = o.Supplier
        LEFT JOIN grv g ON o.Reference = g.Order_No
        GROUP BY s.SupplierID
        ORDER BY TotalValue DESC
    ");
    return $stmt->fetchAll();
}

function getDashboardKPIs() {
    $pdo = getDBConnection();
    
    $totalOrders = $pdo->query("SELECT COUNT(*) as cnt FROM orders")->fetch()['cnt'] ?? 0;
    $totalItems = $pdo->query("SELECT COUNT(*) as cnt FROM items_master")->fetch()['cnt'] ?? 0;
    $totalSuppliers = $pdo->query("SELECT COUNT(*) as cnt FROM suppliers")->fetch()['cnt'] ?? 0;
    
    $lowStock = $pdo->query("
        SELECT COUNT(*) as cnt FROM (
            SELECT i.ItemCode, i.ReorderLevel, COALESCE(SUM(t.quantity_in), 0) - COALESCE(SUM(t.quantity_out), 0) as soh
            FROM items_master i
            LEFT JOIN inventory_transaction t ON i.ItemCode = t.item_code
            GROUP BY i.ItemCode, i.ReorderLevel
            HAVING soh < i.ReorderLevel OR (soh = 0 AND i.ReorderLevel > 0)
        ) as low_stock
    ")->fetch()['cnt'] ?? 0;
    
    $pendingPOs = $pdo->query("SELECT COUNT(*) as cnt FROM orders WHERE PO_DateApproved IS NULL OR PO_DateApproved = ''")->fetch()['cnt'] ?? 0;
    
    $totalValue = $pdo->query("SELECT COALESCE(SUM(quantity_in * unit_cost), 0) as val FROM inventory_transaction")->fetch()['val'] ?? 0;
    
    return [
        'total_orders' => $totalOrders,
        'total_items' => $totalItems,
        'total_suppliers' => $totalSuppliers,
        'low_stock' => $lowStock,
        'pending_pos' => $pendingPOs,
        'total_value' => $totalValue
    ];
}

function checkReorderAlert($itemCode) {
    $soh = calcStockOnHand($itemCode);
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("SELECT ReorderLevel FROM items_master WHERE ItemCode = ?");
    $stmt->execute([$itemCode]);
    $reorderLevel = $stmt->fetch()['ReorderLevel'] ?? 0;
    
    return $soh <= $reorderLevel;
}

function exportInventoryTransactions($filters = []) {
    $pdo = getDBConnection();
    $sql = "SELECT * FROM inventory_transaction WHERE 1=1";
    $params = [];

    if (!empty($filters['start_date'])) {
        $sql .= " AND transaction_date >= ?";
        $params[] = $filters['start_date'];
    }
    if (!empty($filters['end_date'])) {
        $sql .= " AND transaction_date <= ?";
        $params[] = $filters['end_date'];
    }
    if (!empty($filters['item_code'])) {
        $sql .= " AND item_code = ?";
        $params[] = $filters['item_code'];
    }
    if (!empty($filters['source_type'])) {
        $sql .= " AND source_type = ?";
        $params[] = $filters['source_type'];
    }
    if (!empty($filters['trans_type'])) {
        $sql .= " AND trans_type = ?";
        $params[] = $filters['trans_type'];
    }

    $sql .= " ORDER BY transaction_date DESC, id DESC";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function importInventoryTransactions($data, $dryRun = true) {
    error_log("lib: importInventoryTransactions called. Dry run: " . ($dryRun ? 'yes' : 'no') . ", rows: " . count($data));
    $pdo = getDBConnection();
    $results = ['success' => 0, 'errors' => [], 'warnings' => [], 'skipped' => 0, 'count' => 0];

    $data = normalizeSpreadsheetRows($data);
    if (empty($data)) {
        $results['errors'][] = 'Empty import data';
        return $results;
    }

    $firstRow = reset($data);
    $requiredCols = ['item_code', 'transaction_date', 'trans_type'];
    
    error_log('Import headers found for transactions: ' . json_encode(array_keys($firstRow ?? [])));
    file_put_contents(__DIR__ . '/../import_log.txt', date('Y-m-d H:i:s') . " Transactions dryRun=" . ($dryRun ? 'yes' : 'no') . ", headers=" . json_encode(array_keys($firstRow ?? [])) . ", rows=" . (count($data) - 1) . "\n", FILE_APPEND);
    $results['debug'] = [
        'dryRun' => $dryRun ? 'yes' : 'no',
        'headers_found' => array_keys($firstRow ?? []),
        'required_cols' => $requiredCols
    ];
    foreach ($requiredCols as $col) {
        if (!isset($firstRow[$col])) {
            $results['errors'][] = "Missing required column: $col";
            return $results;
        }
    }

    $dbCols = ['id', 'source_type', 'item_code', 'item_description', 'other_description', 'item_group', 'group_category', 'transaction_date', 'reference', 'trans_type', 'division', 'allocation', 'warehouse', 'gl_account', 'quantity_in', 'quantity_out', 'transfers', 'batch_number', 'expiry_date', 'medicines_code', 'dh_code', 'doc_code', 'reg_serial_number', 'odo_hr_reading', 'meter_reading_before', 'meter_reading_after', 'meter_quantity', 'receiver', 'issuer', 'details', 'unit_cost', 'action_type'];

    $colMapping = [
        'item_code' => 'item_code', 'itemcode' => 'item_code', 'item_description' => 'item_description', 'itemdescription' => 'item_description', 'other_description' => 'other_description',
        'item_group' => 'item_group', 'itemgroup' => 'item_group', 'group_category' => 'group_category', 'transaction_date' => 'transaction_date',
        'reference' => 'reference', 'trans_type' => 'trans_type', 'division' => 'division',
        'allocation' => 'allocation', 'warehouse' => 'warehouse', 'gl_account' => 'gl_account',
        'quantity_in' => 'quantity_in', 'quantityin' => 'quantity_in', 'quantity_out' => 'quantity_out', 'quantityout' => 'quantity_out', 'transfers' => 'transfers',
        'batch_number' => 'batch_number', 'batchnumber' => 'batch_number', 'expiry_date' => 'expiry_date', 'expirydate' => 'expiry_date', 'medicines_code' => 'medicines_code',
        'dh_code' => 'dh_code', 'dhcode' => 'dh_code', 'doc_code' => 'doc_code', 'reg_serial_number' => 'reg_serial_number',
        'odo_hr_reading' => 'odo_hr_reading', 'odohrreading' => 'odo_hr_reading', 'meter_reading_before' => 'meter_reading_before',
        'meter_reading_after' => 'meter_reading_after', 'meter_quantity' => 'meter_quantity', 'meterquantity' => 'meter_quantity',
        'receiver' => 'receiver', 'issuer' => 'issuer', 'details' => 'details',
        'unit_cost' => 'unit_cost', 'action_type' => 'action_type', 'source_type' => 'source_type'
    ];

    $transDateCols = ['transaction_date', 'expiry_date'];

    $allItemCodes = array_filter(array_unique(array_map(fn($row) => trim($row['item_code'] ?? ''), $data)));
    $existingItems = [];
    if (!empty($allItemCodes)) {
        $placeholders = implode(',', array_fill(0, count($allItemCodes), '?'));
        $stmt = $pdo->prepare("SELECT ItemCode FROM items_master WHERE ItemCode IN ($placeholders)");
        $stmt->execute(array_values($allItemCodes));
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $existingItems[strtolower($row['ItemCode'])] = true;
        }
    }

    $pdo->beginTransaction();
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $colNamesList = array_filter($dbCols, fn($c) => $c !== 'id');
    $insertCols = '`' . implode('`, `', $colNamesList) . '`';
    $placeholders = implode(', ', array_fill(0, count($colNamesList), '?'));
    
    $updateParts = [];
    foreach ($colNamesList as $col) {
        $updateParts[] = "`$col` = VALUES(`$col`)";
    }
    $updateSql = " ON DUPLICATE KEY UPDATE " . implode(', ', $updateParts);
    
    $insertStmt = $pdo->prepare("INSERT INTO inventory_transaction ($insertCols) VALUES ($placeholders)" . $updateSql);

    foreach ($data as $idx => $row) {
        if ($idx === 0 || empty(array_filter($row))) continue;

        $itemCode = trim($row['item_code'] ?? '');
        if (empty($itemCode)) {
            $results['skipped']++;
            continue;
        }

        if (!isset($existingItems[strtolower($itemCode)])) {
            $results['warnings'][] = "Item code not found in items_master: $itemCode (row " . ($idx + 1) . ")";
        }

        $colNames = [];
        foreach ($row as $col => $val) {
            $dbCol = $colMapping[strtolower($col)] ?? null;
            if ($dbCol && in_array($dbCol, $dbCols)) {
                $colNames[$dbCol] = $val;
            }
        }

        foreach ($transDateCols as $dateCol) {
            if (array_key_exists($dateCol, $colNames)) {
                $colNames[$dateCol] = parseDate($colNames[$dateCol]);
            }
        }

        if (empty($colNames)) {
            $results['skipped']++;
            continue;
        }

        if ($dryRun) {
            $results['success']++;
            $results['count']++;
            continue;
        }

        try {
            $values = [];
            foreach ($colNamesList as $col) {
                $values[] = $colNames[$col] ?? null;
            }
            if (!$insertStmt->execute($values)) {
                throw new Exception("Execution failed for row " . ($idx + 1));
            }
            $results['success']++;
            $results['count']++;
        } catch (Exception $e) {
            error_log("lib insert error row " . ($idx + 1) . ": " . $e->getMessage());
            $results['errors'][] = "Insert failed (row " . ($idx + 1) . "): " . $e->getMessage();
        }
    }

    if ($dryRun) {
        $pdo->rollBack();
    } else {
        $pdo->commit();
    }

    error_log("lib importInventoryTransactions complete: " . json_encode($results));
    
    if ($dryRun) {
        $results['would_import'] = $results['success'];
        unset($results['success']);
    } else {
        $results['imported'] = $results['success'];
        unset($results['success']);
    }
    $results['message'] = 'Inventory transactions import completed';
    if (!empty($results['errors'])) {
        $results['message'] .= ' with ' . count($results['errors']) . ' errors';
    }
    $results['total_rows'] = count($data) - 1;
    return $results;
}

function normalizeSpreadsheetRows(array $data): array {
    $data = array_values($data);
    if (empty($data)) {
        return [];
    }

    $headerRow = array_shift($data);
    $headers = [];
    foreach ($headerRow as $key => $value) {
        $normalized = preg_replace('/[^\w]+/', '_', strtolower(trim((string)$value)));
        $normalized = trim($normalized, '_');
        if ($normalized === '') {
            continue;
        }
        $headers[$key] = $normalized;
    }

    $normalizedRows = [];
    $normalizedRows[] = array_combine(array_values($headers), array_map(fn($v) => $v, $headerRow));

    foreach ($data as $row) {
        $newRow = [];
        foreach ($headers as $colIndex => $columnName) {
            $newRow[$columnName] = $row[$colIndex] ?? null;
        }
        $normalizedRows[] = $newRow;
    }

    return $normalizedRows;
}

function exportOrders($filters = []) {
    $pdo = getDBConnection();
    $sql = "SELECT * FROM orders WHERE 1=1";
    $params = [];

    if (!empty($filters['start_date'])) {
        $sql .= " AND STR_TO_DATE(PO_Date, '%Y/%m/%d') >= ?";
        $params[] = $filters['start_date'];
    }
    if (!empty($filters['end_date'])) {
        $sql .= " AND STR_TO_DATE(PO_Date, '%Y/%m/%d') <= ?";
        $params[] = $filters['end_date'];
    }
    if (!empty($filters['supplier'])) {
        $sql .= " AND Supplier = ?";
        $params[] = $filters['supplier'];
    }
    if (!empty($filters['status'])) {
        $status = $filters['status'];
        if ($status === 'Pending') {
            $sql .= " AND (PO_DateApproved IS NULL OR PO_DateApproved = '')";
        } elseif ($status === 'Approved') {
            $sql .= " AND PO_DateApproved IS NOT NULL AND PO_DateApproved != '' AND (SELECT COUNT(*) FROM grv WHERE Order_No = orders.Reference) = 0";
        } elseif ($status === 'Partial') {
            $sql .= " AND (SELECT COUNT(*) FROM grv WHERE Order_No = orders.Reference) > 0 AND (SELECT SUM(Quantity_Received) FROM grv WHERE Order_No = orders.Reference) < orders.Quantity";
        } elseif ($status === 'Received') {
            $sql .= " AND (SELECT SUM(Quantity_Received) FROM grv WHERE Order_No = orders.Reference) >= orders.Quantity";
        }
    }

    $sql .= " ORDER BY PO_Date DESC, OrderID DESC";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function importOrders($data, $dryRun = true) {
    error_log("=== importOrders STARTED. dryRun=" . ($dryRun ? 'TRUE' : 'FALSE') . ", rows=" . count($data));
    file_put_contents(__DIR__ . '/../import_log.txt', date('Y-m-d H:i:s') . " importOrders dryRun=" . ($dryRun ? 'TRUE' : 'FALSE') . ", rows=" . count($data) . "\\n", FILE_APPEND);
    $pdo = getDBConnection();
    $results = ['success' => 0, 'errors' => [], 'warnings' => [], 'skipped' => 0, 'count' => 0];

    $data = normalizeSpreadsheetRows($data);
    if (empty($data)) {
        $results['errors'][] = 'Empty import data';
        return $results;
    }

    $firstRow = reset($data);
    
    $hasItemCode = isset($firstRow['item_code']) || isset($firstRow['itemcode']);
    $hasQuantity = isset($firstRow['quantity']);
    $hasPODate = isset($firstRow['po_date']) || isset($firstRow['po_date']);
    $hasOrderID = isset($firstRow['orderid']) || isset($firstRow['order_id']);

    if (!$hasItemCode || !$hasQuantity || !$hasPODate || !$hasOrderID) {
        $results['errors'][] = "Missing required columns: item_code, quantity, po_date, orderid. Found: " . implode(', ', array_keys($firstRow));
        return $results;
    }

    $dbCols = ['OrderID', 'SourceID', 'Buyer', 'Reference', 'ItemCode', 'ItemDescription', 'UOM', 'ItemCategory', 'ItemGroup', 'GoodType', 'Detail', 'Quantity', 'UnitPrice', 'GrossPrice', 'Division', 'SupplySource', 'Supplier', 'Location', 'ReferenceRQ', 'ItemType', 'PO_Date', 'PO_DateApproved', 'PaymentTerms', 'Source', 'GoodsSupplied'];

    $colMapping = [
        'orderid' => 'OrderID', 'order_id' => 'OrderID', 'sourceid' => 'SourceID', 'source_id' => 'SourceID', 'buyer' => 'Buyer', 'reference' => 'Reference',
        'itemcode' => 'ItemCode', 'item_code' => 'ItemCode', 'itemdescription' => 'ItemDescription', 'item_description' => 'ItemDescription', 'uom' => 'UOM',
        'itemcategory' => 'ItemCategory', 'item_category' => 'ItemCategory', 'itemgroup' => 'ItemGroup', 'item_group' => 'ItemGroup', 'goodtype' => 'GoodType', 'good_type' => 'GoodType',
        'detail' => 'Detail', 'quantity' => 'Quantity', 'unitprice' => 'UnitPrice',
        'grossprice' => 'GrossPrice', 'gross_price' => 'GrossPrice', 'division' => 'Division', 'supplysource' => 'SupplySource', 'supply_source' => 'SupplySource',
        'supplier' => 'Supplier', 'location' => 'Location', 'referencerq' => 'ReferenceRQ',
        'itemtype' => 'ItemType', 'item_type' => 'ItemType',
        'po_date' => 'PO_Date', 'po_date' => 'PO_Date', 'po date' => 'PO_Date',
        'po_dateapproved' => 'PO_DateApproved', 'po_date_approved' => 'PO_DateApproved', 'po dateapproved' => 'PO_DateApproved',
        'paymentterms' => 'PaymentTerms', 'payment_terms' => 'PaymentTerms', 'source' => 'Source', 'goodssupplied' => 'GoodsSupplied', 'goods_supplied' => 'GoodsSupplied'
    ];

    $dateCols = ['PO_Date', 'PO_DateApproved'];

    $pdo->beginTransaction();
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    file_put_contents(__DIR__ . '/../import_log.txt', "Started transaction for orders import\n", FILE_APPEND);
    $insertCols = '`' . implode('`, `', $dbCols) . '`';
    $placeholders = implode(', ', array_fill(0, count($dbCols), '?'));
    
    // Build ON DUPLICATE KEY UPDATE clause to ensure existing records are updated
    $updateParts = [];
    foreach ($dbCols as $col) {
        if ($col === 'OrderID') continue; // Don't update the primary key/unique identifier
        $updateParts[] = "`$col` = VALUES(`$col`)";
    }
    $updateSql = " ON DUPLICATE KEY UPDATE " . implode(', ', $updateParts);

    $insertStmt = $pdo->prepare("INSERT INTO orders ($insertCols) VALUES ($placeholders)" . $updateSql);
    
    error_log("Import function: dryRun=" . ($dryRun ? 'true' : 'false') . ", data rows=" . count($data));

    foreach ($data as $idx => $row) {
        if ($idx === 0 || empty(array_filter($row))) continue;

        $itemCode = trim($row['item_code'] ?? $row['itemcode'] ?? '');
        $normCode = cleanItemCode($itemCode);
        if (empty($normCode)) {
            $results['skipped']++;
            continue;
        }

        $colNames = [];
        foreach ($row as $col => $val) {
            $normalizedCol = strtolower(trim(str_replace(' ', '_', $col)));
            $dbCol = $colMapping[$normalizedCol] ?? null;
            if ($dbCol && in_array($dbCol, $dbCols)) {
                $colNames[$dbCol] = $val;
            }
        }

        foreach ($dateCols as $dateCol) {
            if (array_key_exists($dateCol, $colNames)) {
                $type = $dateCol === 'PO_DateApproved' ? 'datetime' : 'date';
                $parsedDate = parseDate($colNames[$dateCol], $type);
                $colNames[$dateCol] = $parsedDate;
            }
        }

        if (empty($colNames)) {
            $results['skipped']++;
            continue;
        }

        if ($dryRun) {
            $results['success']++;
            $results['count']++;
            continue;
        }

        try {
            $values = [];
            foreach ($dbCols as $col) {
                $values[] = $colNames[$col] ?? null;
            }
            if (!$insertStmt->execute($values)) {
                throw new Exception("Execution failed for row " . ($idx + 1));
            }
            $results['success']++;
            $results['count']++;
        } catch (Exception $e) {
            $results['errors'][] = "Insert failed (row " . ($idx + 1) . "): " . $e->getMessage();
        }
    }

    if ($dryRun) {
        $pdo->rollBack();
    } else {
        $pdo->commit();
    }

    $results['total_rows'] = count($data) - 1;
    return $results;
}

function exportGRV($filters = []) {
    $pdo = getDBConnection();
    $sql = "SELECT * FROM grv WHERE 1=1";
    $params = [];

    if (!empty($filters['start_date'])) {
        $sql .= " AND GRV_Date >= ?";
        $params[] = $filters['start_date'];
    }
    if (!empty($filters['end_date'])) {
        $sql .= " AND GRV_Date <= ?";
        $params[] = $filters['end_date'];
    }
    if (!empty($filters['order_no'])) {
        $sql .= " AND Order_No LIKE ?";
        $params[] = '%' . $filters['order_no'] . '%';
    }
    if (!empty($filters['supplier'])) {
        $sql .= " AND Supplier_Account LIKE ?";
        $params[] = '%' . $filters['supplier'] . '%';
    }

    $sql .= " ORDER BY GRV_Date DESC, GRV_ID DESC";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function importGRV($data, $dryRun = true) {
    $pdo = getDBConnection();
    $results = ['success' => 0, 'errors' => [], 'warnings' => [], 'skipped' => 0, 'count' => 0];

    $data = normalizeSpreadsheetRows($data);
    if (empty($data)) {
        $results['errors'][] = 'Empty import data';
        return $results;
    }

    $firstRow = reset($data);
    $requiredCols = ['grv_date'];
    foreach ($requiredCols as $col) {
        if (!isset($firstRow[$col])) {
            $results['errors'][] = "Missing required column: $col";
            return $results;
        }
    }

    $dbCols = ['GRV_ID', 'Supplier_Account', 'Supplier_Description', 'GRV_Date', 'Order_No', 'Supplier_Invoice', 'Our_Reference', 'Item_Code', 'Item_Description', 'Detail', 'Ordered_Qty', 'Prev_Qty', 'Quantity_Received', 'Unit_Price', 'Tax', 'TaxTotal_Incl', 'Currency', 'Item_Group'];

    $colMapping = [
        'grv_id' => 'GRV_ID', 'supplier_account' => 'Supplier_Account', 'supplier_description' => 'Supplier_Description',
        'grv_date' => 'GRV_Date', 'order_no' => 'Order_No', 'supplier_invoice' => 'Supplier_Invoice',
        'our_reference' => 'Our_Reference', 'item_code' => 'Item_Code', 'item_description' => 'Item_Description',
        'detail' => 'Detail', 'ordered_qty' => 'Ordered_Qty', 'prev_qty' => 'Prev_Qty',
        'quantity_received' => 'Quantity_Received', 'unit_price' => 'Unit_Price',
        'tax' => 'Tax', 'taxtotal_incl' => 'TaxTotal_Incl', 'currency' => 'Currency', 'item_group' => 'Item_Group'
    ];

    $grvDateCols = ['GRV_Date'];

    $allItemCodes = array_filter(array_unique(array_map(fn($row) => trim($row['item_code'] ?? ''), $data)));
    $existingItems = [];
    if (!empty($allItemCodes)) {
        $placeholders = implode(',', array_fill(0, count($allItemCodes), '?'));
        $stmt = $pdo->prepare("SELECT ItemCode FROM items_master WHERE ItemCode IN ($placeholders)");
        $stmt->execute(array_values($allItemCodes));
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $existingItems[strtolower($row['ItemCode'])] = true;
        }
    }

    $allOrderNos = array_filter(array_unique(array_map(fn($row) => trim($row['order_no'] ?? ''), $data)));

    $validOrders = [];
    if (!empty($allOrderNos)) {
        $placeholders = implode(',', array_fill(0, count($allOrderNos), '?'));
        $stmt = $pdo->prepare("SELECT Reference FROM orders WHERE Reference IN ($placeholders)");
        $stmt->execute(array_values($allOrderNos));
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $validOrders[$row['Reference']] = true;
        }
    }

    $pdo->beginTransaction();
    $insertCols = '`' . implode('`, `', $dbCols) . '`';
    $placeholders = implode(', ', array_fill(0, count($dbCols), '?'));
    
    $updateParts = [];
    foreach ($dbCols as $col) {
        if ($col === 'GRV_ID') continue;
        $updateParts[] = "`$col` = VALUES(`$col`)";
    }
    $updateSql = " ON DUPLICATE KEY UPDATE " . implode(', ', $updateParts);
    
    $insertStmt = $pdo->prepare("INSERT INTO grv ($insertCols) VALUES ($placeholders)" . $updateSql);

    foreach ($data as $idx => $row) {
        if ($idx === 0 || empty(array_filter($row))) continue;

        $orderNo = trim($row['order_no'] ?? '');
        $grvDate = trim($row['grv_date'] ?? '');
        $itemCode = trim($row['item_code'] ?? '');

        if (empty($grvDate)) {
            $results['skipped']++;
            continue;
        }

        if (!empty($itemCode) && !isset($existingItems[strtolower($itemCode)])) {
            $results['warnings'][] = "Item code not found in items_master: $itemCode (row " . ($idx + 1) . ")";
        }

        if (!empty($orderNo) && !isset($validOrders[$orderNo])) {
            $results['warnings'][] = "Order reference not found: $orderNo (row " . ($idx + 1) . ")";
        }

        $colNames = [];
        foreach ($row as $col => $val) {
            $dbCol = $colMapping[strtolower($col)] ?? null;
            if ($dbCol && in_array($dbCol, $dbCols)) {
                $colNames[$dbCol] = $val;
            }
        }

        foreach ($grvDateCols as $dateCol) {
            if (array_key_exists($dateCol, $colNames)) {
                $colNames[$dateCol] = parseDate($colNames[$dateCol]);
            }
        }

        if (empty($colNames)) {
            $results['skipped']++;
            continue;
        }

        if ($dryRun) {
            $results['success']++;
            $results['count']++;
            continue;
        }

        try {
            $values = [];
            foreach ($dbCols as $col) {
                $values[] = $colNames[$col] ?? null;
            }
            $insertStmt->execute($values);
            $results['success']++;
            $results['count']++;
        } catch (Exception $e) {
            $results['errors'][] = "Insert failed (row " . ($idx + 1) . "): " . $e->getMessage();
        }
    }

    if ($dryRun) {
        $pdo->rollBack();
    } else {
        $pdo->commit();
    }

    $results['total_rows'] = count($data) - 1;
    return $results;
}

function exportItems($filters = []) {
    $pdo = getDBConnection();
    $sql = "SELECT * FROM items_master WHERE 1=1";
    $params = [];

    if (!empty($filters['item_code'])) {
        $sql .= " AND ItemCode LIKE ?";
        $params[] = '%' . $filters['item_code'] . '%';
    }
    if (!empty($filters['category'])) {
        $sql .= " AND Category LIKE ?";
        $params[] = '%' . $filters['category'] . '%';
    }

    $sql .= " ORDER BY ItemCode ASC";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function importItems($data, $dryRun = true) {
    $pdo = getDBConnection();
    $results = ['success' => 0, 'errors' => [], 'warnings' => [], 'skipped' => 0, 'count' => 0];

    $data = normalizeSpreadsheetRows($data);
    if (empty($data)) {
        $results['errors'][] = 'Empty import data';
        return $results;
    }

    $firstRow = reset($data);
    $requiredCols = ['itemcode'];
    foreach ($requiredCols as $col) {
        if (!isset($firstRow[$col])) {
            $results['errors'][] = "Missing required column: $col";
            return $results;
        }
    }

    $dbCols = ['ItemCode', 'ItemName', 'Category', 'UnitOfMeasure', 'ReorderLevel', 'UnitCost', 'SupplierID'];

    $colMapping = [
        'itemcode' => 'ItemCode', 'itemname' => 'ItemName', 'category' => 'Category',
        'unitofmeasure' => 'UnitOfMeasure', 'reorderlevel' => 'ReorderLevel', 'unitcost' => 'UnitCost',
        'supplierid' => 'SupplierID'
    ];

    $allItemCodes = array_filter(array_map(fn($row) => trim($row['itemcode'] ?? ''), $data));
    $supplierIds = array_filter(array_unique(array_map(fn($row) => trim($row['supplierid'] ?? ''), $data)));

    $existingItems = [];
    if (!empty($allItemCodes)) {
        $placeholders = implode(',', array_fill(0, count($allItemCodes), '?'));
        $stmt = $pdo->prepare("SELECT ItemCode FROM items_master WHERE ItemCode IN ($placeholders)");
        $stmt->execute(array_values($allItemCodes));
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $existingItems[strtolower($row['ItemCode'])] = true;
        }
    }

    $pdo->beginTransaction();
    $insertStmt = $pdo->prepare("INSERT INTO items_master (`ItemCode`, `ItemName`, `Category`, `UnitOfMeasure`, `ReorderLevel`, `UnitCost`, `SupplierID`) VALUES (?, ?, ?, ?, ?, ?, ?)");
    $updateStmt = $pdo->prepare("UPDATE items_master SET `ItemName` = ?, `Category` = ?, `UnitOfMeasure` = ?, `ReorderLevel` = ?, `UnitCost` = ?, `SupplierID` = ? WHERE ItemCode = ?");

    foreach ($data as $idx => $row) {
        if ($idx === 0 || empty(array_filter($row))) continue;

        $itemCode = trim($row['itemcode'] ?? '');
        if (empty($itemCode)) {
            $results['skipped']++;
            continue;
        }

        $colNames = [];
        foreach ($row as $col => $val) {
            $dbCol = $colMapping[strtolower($col)] ?? null;
            if ($dbCol && in_array($dbCol, $dbCols)) {
                $colNames[$dbCol] = $val;
            }
        }

        $exists = isset($existingItems[strtolower($itemCode)]);

        if ($dryRun) {
            $results['success']++;
            $results['count']++;
            continue;
        }

        try {
            $values = [
                $colNames['ItemName'] ?? null,
                $colNames['Category'] ?? null,
                $colNames['UnitOfMeasure'] ?? null,
                $colNames['ReorderLevel'] ?? null,
                $colNames['UnitCost'] ?? null,
                $colNames['SupplierID'] ?? null
            ];

            if ($exists) {
                $updateStmt->execute(array_merge($values, [$itemCode]));
            } else {
                $insertStmt->execute(array_merge([$itemCode], $values));
            }
            $results['success']++;
            $results['count']++;
        } catch (Exception $e) {
            $results['errors'][] = "Insert failed (row " . ($idx + 1) . "): " . $e->getMessage();
        }
    }

    if ($dryRun) {
        $pdo->rollBack();
    } else {
        $pdo->commit();
    }

    $results['total_rows'] = count($data) - 1;
    return $results;
}

function exportSuppliers($filters = []) {
    $pdo = getDBConnection();
    $sql = "SELECT * FROM suppliers WHERE 1=1";
    $params = [];

    if (!empty($filters['supplier'])) {
        $sql .= " AND (SupplierName LIKE ? OR SupplierID LIKE ?)";
        $params[] = '%' . $filters['supplier'] . '%';
        $params[] = '%' . $filters['supplier'] . '%';
    }

    $sql .= " ORDER BY SupplierName ASC";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function importSuppliers($data, $dryRun = true) {
    $pdo = getDBConnection();
    $results = ['success' => 0, 'errors' => [], 'skipped' => 0, 'count' => 0];

    $data = normalizeSpreadsheetRows($data);
    if (empty($data)) {
        $results['errors'][] = 'Empty import data';
        return $results;
    }

    $firstRow = reset($data);
    $requiredCols = ['supplierid'];
    foreach ($requiredCols as $col) {
        if (!isset($firstRow[$col])) {
            $results['errors'][] = "Missing required column: $col";
            return $results;
        }
    }

    $dbCols = ['SupplierID', 'SupplierName', 'ContactPerson', 'Phone', 'Email', 'Address', 'LeadTime_Days', 'Rating'];

    $colMapping = [
        'supplierid' => 'SupplierID', 'suppliername' => 'SupplierName', 'contactperson' => 'ContactPerson',
        'phone' => 'Phone', 'email' => 'Email', 'address' => 'Address',
        'leadtim_days' => 'LeadTime_Days', 'rating' => 'Rating'
    ];

    $allSupplierIDs = array_filter(array_unique(array_map(fn($row) => trim($row['supplierid'] ?? ''), $data)));

    $existingSuppliers = [];
    if (!empty($allSupplierIDs)) {
        $placeholders = implode(',', array_fill(0, count($allSupplierIDs), '?'));
        $stmt = $pdo->prepare("SELECT SupplierID FROM suppliers WHERE SupplierID IN ($placeholders)");
        $stmt->execute(array_values($allSupplierIDs));
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $existingSuppliers[$row['SupplierID']] = true;
        }
    }

    $pdo->beginTransaction();
    $insertCols = '`' . implode('`, `', $dbCols) . '`';
    $placeholders = implode(', ', array_fill(0, count($dbCols), '?'));
    $insertStmt = $pdo->prepare("INSERT INTO suppliers ($insertCols) VALUES ($placeholders)");
    $updateParts = [];
    foreach (array_slice($dbCols, 1) as $col) {
        $updateParts[] = "`$col` = ?";
    }
    $updateSql = "UPDATE suppliers SET " . implode(', ', $updateParts) . " WHERE SupplierID = ?";
    $updateStmt = $pdo->prepare($updateSql);

    foreach ($data as $idx => $row) {
        if ($idx === 0 || empty(array_filter($row))) continue;

        $supplierID = trim($row['supplierid'] ?? '');
        if (empty($supplierID)) {
            $results['skipped']++;
            continue;
        }

        $colNames = [];
        foreach ($row as $col => $val) {
            $dbCol = $colMapping[strtolower($col)] ?? null;
            if ($dbCol && in_array($dbCol, $dbCols)) {
                $colNames[$dbCol] = $val;
            }
        }

        if (empty($colNames)) {
            $results['skipped']++;
            continue;
        }

        $exists = isset($existingSuppliers[$supplierID]);

        if ($dryRun) {
            $results['success']++;
            $results['count']++;
            continue;
        }

        try {
            if ($exists) {
                $values = [];
                foreach (array_slice($dbCols, 1) as $col) {
                    $values[] = $colNames[$col] ?? null;
                }
                $updateStmt->execute(array_merge($values, [$supplierID]));
            } else {
                $values = [];
                foreach ($dbCols as $col) {
                    $values[] = $colNames[$col] ?? null;
                }
                $insertStmt->execute($values);
            }
            $results['success']++;
            $results['count']++;
        } catch (Exception $e) {
            $results['errors'][] = "Insert failed (row " . ($idx + 1) . "): " . $e->getMessage();
        }
    }

    if ($dryRun) {
        $pdo->rollBack();
    } else {
        $pdo->commit();
    }

    $results['total_rows'] = count($data) - 1;
    return $results;
}

function exportUsers($filters = []) {
    $pdo = getDBConnection();
    $sql = "SELECT id, username, email, full_name, role, active, created_at, last_login FROM users WHERE 1=1";
    $params = [];

    if (!empty($filters['role'])) {
        $sql .= " AND role = ?";
        $params[] = $filters['role'];
    }

    $sql .= " ORDER BY username ASC";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function importUsers($data, $dryRun = true) {
    $pdo = getDBConnection();
    $results = ['success' => 0, 'errors' => [], 'skipped' => 0, 'count' => 0];

    $data = normalizeSpreadsheetRows($data);
    if (empty($data)) {
        $results['errors'][] = 'Empty import data';
        return $results;
    }

    $firstRow = reset($data);
    $requiredCols = ['username'];
    foreach ($requiredCols as $col) {
        if (!isset($firstRow[$col])) {
            $results['errors'][] = "Missing required column: $col";
            return $results;
        }
    }

    $dbCols = ['username', 'password', 'email', 'full_name', 'role', 'active'];

    $colMapping = [
        'username' => 'username', 'password' => 'password', 'email' => 'email',
        'full_name' => 'full_name', 'role' => 'role', 'active' => 'active'
    ];

    $allUsernames = array_filter(array_unique(array_map(fn($row) => trim($row['username'] ?? ''), $data)));

    $existingUsers = [];
    if (!empty($allUsernames)) {
        $placeholders = implode(',', array_fill(0, count($allUsernames), '?'));
        $stmt = $pdo->prepare("SELECT username FROM users WHERE username IN ($placeholders)");
        $stmt->execute(array_values($allUsernames));
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $existingUsers[$row['username']] = true;
        }
    }

    $pdo->beginTransaction();
    $insertCols = '`' . implode('`, `', $dbCols) . '`';
    $placeholders = implode(', ', array_fill(0, count($dbCols), '?'));
    $insertStmt = $pdo->prepare("INSERT INTO users ($insertCols) VALUES ($placeholders)");
    $updateParts = array_filter($dbCols, fn($c) => $c !== 'password');
    $updateParts = array_map(fn($c) => "`$c` = ?", $updateParts);
    $updateSql = "UPDATE users SET " . implode(', ', $updateParts) . " WHERE username = ?";
    $updateStmt = $pdo->prepare($updateSql);

    foreach ($data as $idx => $row) {
        if ($idx === 0 || empty(array_filter($row))) continue;

        $username = trim($row['username'] ?? '');
        if (empty($username)) {
            $results['skipped']++;
            continue;
        }

        if (!in_array(strtolower($row['role'] ?? ''), ['admin', 'manager', 'user'])) {
            $results['errors'][] = "Invalid role: " . ($row['role'] ?? '') . " (row " . ($idx + 1) . ")";
            $results['skipped']++;
            continue;
        }

        $colNames = [];
        foreach ($row as $col => $val) {
            $dbCol = $colMapping[strtolower(trim($col))] ?? null;
            if ($dbCol && in_array($dbCol, $dbCols)) {
                if ($dbCol === 'active') {
                    $val = in_array(strtolower($val), ['1', 'true', 'yes', 'active']) ? 1 : 0;
                }
                $colNames[$dbCol] = $val;
            }
        }

        if (empty($colNames)) {
            $results['skipped']++;
            continue;
        }

        $exists = isset($existingUsers[$username]);

        if ($dryRun) {
            $results['success']++;
            $results['count']++;
            continue;
        }

        try {
            if ($exists) {
                unset($colNames['password']);
                if (!empty($colNames)) {
                    $values = [];
                    foreach (array_filter($dbCols, fn($c) => $c !== 'password') as $col) {
                        $values[] = $colNames[$col] ?? null;
                    }
                    $updateStmt->execute(array_merge($values, [$username]));
                }
            } else {
                $values = [];
                foreach ($dbCols as $col) {
                    $values[] = $colNames[$col] ?? null;
                }
                $insertStmt->execute($values);
            }
            $results['success']++;
            $results['count']++;
        } catch (Exception $e) {
            $results['errors'][] = "Insert failed (row " . ($idx + 1) . "): " . $e->getMessage();
        }
    }

    if ($dryRun) {
        $pdo->rollBack();
    } else {
        $pdo->commit();
    }

    $results['total_rows'] = count($data) - 1;
    return $results;
}
?>