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

$method = $_SERVER['REQUEST_METHOD'];

if ($method === 'POST') {
    requireRole(['admin']);
    handleImport();
} else {
    requireRole(['admin']);
    handleExport();
}

function handleImport() {
    $table = $_POST['table'] ?? $_GET['table'] ?? '';
    $format = $_POST['format'] ?? '';

$allowedTables = ['items_master', 'grv', 'inventory_transaction', 'orders', 'suppliers', 'users', 'supplier_forecast', 'inventory_valuation', 'valuation'];
    if (!in_array($table, $allowedTables)) {
        jsonResponse(['error' => 'Invalid table'], 400);
    }

    if (!isset($_FILES['file']) || $_FILES['file']['error'] !== UPLOAD_ERR_OK) {
        jsonResponse(['error' => 'No file uploaded'], 400);
    }

    $file = $_FILES['file'];
    $filename = $file['tmp_name'];
    $extension = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));

    try {
        $data = [];
        
        if ($extension === 'xlsx' || $extension === 'xls') {
            $data = readExcelFile($filename);
        } elseif ($extension === 'csv') {
            $data = readCSVFile($filename);
        } else {
            jsonResponse(['error' => 'Unsupported file format'], 400);
        }

        if (empty($data)) {
            jsonResponse(['error' => 'File is empty'], 400);
        }

        $result = importToTable($table, $data);
        jsonResponse($result);
    } catch (Exception $e) {
        jsonResponse(['error' => $e->getMessage()], 500);
    }
}

function readExcelFile($filename) {
    $spreadsheet = null;
    
    // Try IOFactory::load first
    try {
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($filename);
    } catch (Exception $e) {
        if (strpos($e->getMessage(), 'Structured Reference') !== false || strpos($e->getMessage(), 'Table') !== false) {
            // Fallback: Use Xlsx reader with data-only mode to skip formulas/tables
            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
            $reader->setReadDataOnly(true);
// $reader->setReadFormulas(false); // Not available in this PhpSpreadsheet version
            $spreadsheet = $reader->load($filename);
        } else {
            throw $e;
        }
    }
    
    if (!$spreadsheet) {
        throw new Exception('Unable to read Excel file');
    }
    
    $sheet = $spreadsheet->getActiveSheet();
    
    // Clean any remaining formula cells with structured references
    foreach ($sheet->getCoordinates() as $coordinate) {
        $cell = $sheet->getCell($coordinate);
        if ($cell->getDataType() === \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_FORMULA) {
            $formula = $cell->getValue();
            if (strpos($formula, 'Table') !== false || strpos($formula, '[#') !== false) {
                // Evaluate to static value
                try {
                    $value = $cell->getCalculatedValue();
                    $cell->setValueExplicit($value, $cell->getDataType());
                } catch (Exception $calcErr) {
                    $cell->setValue('');
                }
            }
        }
    }
    
    $rows = $sheet->toArray(null, true, true, true);
    
    if (empty($rows)) return [];
    
    $headers = array_shift($rows);
    $headers = array_map(function($h) {
        return strtolower(trim((string)$h));
    }, $headers);
    
    $data = [];
    foreach ($rows as $rowIndex => $row) {
        if (count($data) >= 1000) break; // Safety limit
        
        $assoc = [];
        foreach ($headers as $idx => $header) {
            if (!empty($header)) {
                $assoc[$header] = $row[$idx] ?? '';
            }
        }
        if (!empty(array_filter($assoc, fn($v) => trim($v) !== ''))) {
            $data[] = $assoc;
        }
    }
    
    return $data;
}

function readCSVFile($filename) {
    $handle = fopen($filename, 'r');
    $data = [];
    
    if ($handle === false) {
        return [];
    }
    
    $headers = fgetcsv($handle);
    if ($headers === false) {
        fclose($handle);
        return [];
    }
    
    $headers = array_map(function($h) {
        return strtolower(trim($h, " \t\""));
    }, $headers);
    
    while (($row = fgetcsv($handle)) !== false) {
        $assoc = [];
        foreach ($headers as $idx => $header) {
            if (!empty($header) && isset($row[$idx])) {
                $assoc[$header] = $row[$idx];
            }
        }
        if (!empty(array_filter($assoc))) {
            $data[] = $assoc;
        }
    }
    
    fclose($handle);
    return $data;
}

function parseDate($value, $rowInfo = '', &$errors = []) {
    if (empty($value) || $value === '' || $value === null) {
        return null;
    }
    
    $value = trim((string)$value);
    
    // Check for Excel serial date (typically 20000-60000)
    $excelDate = null;
    $numericVal = floatval($value);
    if (is_numeric($value) && $numericVal > 20000 && $numericVal < 60000) {
        $excelDate = $numericVal;
    }
    
    $date = null;
    
    if ($excelDate !== null) {
        try {
            $unixDate = ($excelDate - 25569) * 86400;
            $date = date('Y-m-d', $unixDate);
            if ($date && $date !== '1970-01-01' && strlen($date) === 10) {
                return $date;
            }
        } catch (Exception $e) {
        }
    }
    
    $formats = [
        'Y-m-d',
        'm/d/Y',
        'd/m/Y',
        'd-m-Y',
        'Y/m/d',
        'm-d-Y',
        'd.m.Y',
        'Y.m.d',
        'd-M-Y',
        'M d, Y',
        'F d, Y',
        'd F Y',
        'Y F d',
        'ymd',
        'dmy',
        'mdy'
    ];
    
    foreach ($formats as $format) {
        try {
            $d = DateTime::createFromFormat($format, $value);
            if ($d && $d->format($format) === $value) {
                $date = $d->format('Y-m-d');
                if ($date !== '0000-00-00' && $date !== '1970-01-01') {
                    return $date;
                }
            }
        } catch (Exception $e) {
        }
    }
    
    $cleanValue = preg_replace('/[\s\-.\/]+/', '-', $value);
    $parts = explode('-', $cleanValue);
    
    if (count($parts) === 3) {
        $isYmd = strlen($parts[0]) === 4 && is_numeric($parts[0]);
        
        $firstNum = intval($parts[0]);
        $secondNum = intval($parts[1]);
        $thirdNum = intval($parts[2]);
        
        $firstValid = $firstNum >= 1 && $firstNum <= 31;
        $secondValid = $secondNum >= 1 && $secondNum <= 12;
        $thirdValid = $thirdNum >= 1 && $thirdNum <= 31;
        
        $isDmy = $firstValid && $secondValid && $thirdValid;
        $isMdy = $secondValid && $firstValid && $thirdValid && $firstNum <= 12 && $secondNum <= 12;
        
        if ($isYmd) {
            $year = intval($parts[0]);
            $month = intval($parts[1]);
            $day = intval($parts[2]);
            if ($year >= 1900 && $year <= 2100 && $month >= 1 && $month <= 12 && $day >= 1 && $day <= 31) {
                $date = sprintf('%04d-%02d-%02d', $year, $month, $day);
                if (checkdate($month, $day, $year)) {
                    return $date;
                }
            }
        } elseif ($isDmy || $isMdy) {
            if ($isDmy) {
                $day = intval($parts[0]);
                $month = intval($parts[1]);
                $year = intval($parts[2]);
            } else {
                $month = intval($parts[0]);
                $day = intval($parts[1]);
                $year = intval($parts[2]);
            }
            
            if (strlen($year) === 2) {
                $year = $year < 50 ? 2000 + $year : 1900 + $year;
            }
            
            if ($year >= 1900 && $year <= 2100 && $month >= 1 && $month <= 12 && $day >= 1 && $day <= 31) {
                if (checkdate($month, $day, $year)) {
                    return sprintf('%04d-%02d-%02d', $year, $month, $day);
                }
            }
            
            if ($isMdy && $firstNum <= 12 && $secondNum <= 12 && checkdate($firstNum, $secondNum, $year)) {
                return sprintf('%04d-%02d-%02d', $year, $firstNum, $secondNum);
            }
            if ($isDmy && checkdate($secondNum, $firstNum, $year)) {
                return sprintf('%04d-%02d-%02d', $year, $secondNum, $firstNum);
            }
        }
    }
    
    $errorMsg = "Invalid date: '$value'" . ($rowInfo ? " (Row: $rowInfo)" : "");
    $errors[] = $errorMsg;
    error_log($errorMsg);
    
    return null;
}

function importToTable($table, $data) {
    $pdo = getDBConnection();
    $results = ['success' => 0, 'errors' => [], 'count' => 0];

    $pdo->beginTransaction();
    
    try {
        switch ($table) {
            case 'items_master':
                $results = importItemsData($pdo, $data);
                break;
            case 'grv':
                $results = importGRVData($pdo, $data);
                break;
            case 'inventory_transaction':
                $results = importTransactionData($pdo, $data);
                break;
            case 'orders':
                $results = importOrdersData($pdo, $data);
                break;
            case 'suppliers':
                $results = importSuppliersData($pdo, $data);
                break;
            case 'users':
                $results = importUsersData($pdo, $data);
                break;
            case 'supplier_forecast':
                $results = importSupplierForecastData($pdo, $data);
                break;
            case 'inventory_valuation':
            case 'valuation':
                $results = importValuationData($pdo, $data);
            break;
        }
        
        $pdo->commit();
    } catch (Exception $e) {
        $pdo->rollBack();
        $results['errors'][] = $e->getMessage();
    }
    
    return $results;
}

function importItemsData($pdo, $data) {
    $results = ['success' => 0, 'errors' => [], 'count' => 0];
    
    $colMapping = [
        'itemcode' => 'ItemCode', 'itemname' => 'ItemName', 'description' => 'ItemName', 
        'category' => 'Category', 'uom' => 'UnitOfMeasure', 'unitofmeasure' => 'UnitOfMeasure',
        'costprice' => 'UnitCost', 'unitcost' => 'UnitCost', 'supplierid' => 'SupplierID', 
        'reorderlevel' => 'ReorderLevel', 'minstock' => 'ReorderLevel',
        'purpose_of_use' => 'Purpose_of_Use', 'purpose' => 'Purpose_of_Use',
        'jit_jic_class' => 'JIT_JIC_Class', 'jit' => 'JIT_JIC_Class', 'jic' => 'JIT_JIC_Class',
        'sde_class' => 'SDE_Class', 'sde' => 'SDE_Class',
        'ved_class' => 'VED_Class', 'ved' => 'VED_Class',
        'combined' => 'Combined',
        'fsn' => 'FSN'
    ];
    
    $dbCols = ['ItemCode', 'ItemName', 'Category', 'UnitOfMeasure', 'UnitCost', 'ReorderLevel', 'SupplierID', 'Purpose_of_Use', 'JIT_JIC_Class', 'SDE_Class', 'VED_Class', 'Combined', 'FSN'];
    
    $existingItems = [];
    $itemCodes = array_filter(array_map(fn($row) => trim($row['itemcode'] ?? ''), $data));
    if (!empty($itemCodes)) {
        $placeholders = implode(',', array_fill(0, count($itemCodes), '?'));
        $stmt = $pdo->prepare("SELECT ItemCode FROM items_master WHERE ItemCode IN ($placeholders)");
        $stmt->execute(array_values($itemCodes));
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $existingItems[strtolower($row['ItemCode'])] = true;
        }
    }
    
    $insertStmt = $pdo->prepare("INSERT INTO items_master (`ItemCode`, `ItemName`, `Category`, `UnitOfMeasure`, `UnitCost`, `ReorderLevel`, `SupplierID`, `Purpose_of_Use`, `JIT_JIC_Class`, `SDE_Class`, `VED_Class`, `Combined`, `FSN`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
    $updateStmt = $pdo->prepare("UPDATE items_master SET `ItemName` = ?, `Category` = ?, `UnitOfMeasure` = ?, `UnitCost` = ?, `ReorderLevel` = ?, `SupplierID` = ?, `Purpose_of_Use` = ?, `JIT_JIC_Class` = ?, `SDE_Class` = ?, `VED_Class` = ?, `Combined` = ?, `FSN` = ? WHERE ItemCode = ?");
    
    foreach ($data as $idx => $row) {
        $itemCode = trim($row['itemcode'] ?? '');
        if (empty($itemCode)) 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)]);
        
        try {
            $values = [
                $colNames['ItemName'] ?? null,
                $colNames['Category'] ?? null,
                $colNames['UnitOfMeasure'] ?? null,
                $colNames['UnitCost'] ?? null,
                $colNames['ReorderLevel'] ?? 0,
                $colNames['SupplierID'] ?? null,
                $colNames['Purpose_of_Use'] ?? null,
                $colNames['JIT_JIC_Class'] ?? null,
                $colNames['SDE_Class'] ?? null,
                $colNames['VED_Class'] ?? null,
                $colNames['Combined'] ?? null,
                $colNames['FSN'] ?? null
            ];
            
            if ($exists) {
                $updateStmt->execute(array_merge($values, [$itemCode]));
            } else {
                $insertStmt->execute(array_merge([$itemCode], $values));
            }
            $results['success']++;
        } catch (Exception $e) {
            $results['errors'][] = "Row " . ($idx + 1) . ": " . $e->getMessage();
        }
    }
    
    $results['count'] = count($data);
    return $results;
}

function importGRVData($pdo, $data) {
    $results = ['success' => 0, 'errors' => [], 'count' => 0, 'headers' => [], 'debug' => [], 'invalid_dates' => []];
    $dateErrors = [];
    
    if (empty($data)) {
        $results['errors'][] = 'No data rows found after parsing';
        return $results;
    }
    
    // Debug log
    $headers = array_keys($data[0] ?? []);
    $results['headers'] = $headers;
    error_log("GRV Import headers: " . json_encode($headers));
    error_log("GRV Import first row raw: " . json_encode($data[0] ?? []));
    if (!empty($data)) {
        error_log("GRV first row sample: " . json_encode($data[0]));
        $results['debug']['first_row'] = $data[0];
        $results['debug']['date_parsing'] = 'Using enhanced date detection';
    }
    
    $results['count'] = count($data);
    
    $colMapping = [
        'grv_id' => 'GRV_ID', 'grvid' => 'GRV_ID', 'id' => 'GRV_ID', 'grvnumber' => 'GRV_ID',
        'supplier_account' => 'Supplier_Account', 'supplier' => 'Supplier_Account', 'supplier_name' => 'Supplier_Account',
        'supplier_description' => 'Supplier_Description', 'supplier_desc' => 'Supplier_Description',
        'grv_date' => 'GRV_Date', 'date' => 'GRV_Date', 'grvdate' => 'GRV_Date',
        'order_no' => 'Order_No', 'order' => 'Order_No', 'orderno' => 'Order_No',
        'supplier_invoice' => 'Supplier_Invoice', 'invoice' => 'Supplier_Invoice',
        'our_reference' => 'Our_Reference', 'reference' => 'Our_Reference',
        'item_code' => 'Item_Code', 'itemcode' => 'Item_Code', 'code' => 'Item_Code',
        'item_description' => 'Item_Description', 'description' => 'Item_Description', 'itemdesc' => 'Item_Description',
        'detail' => 'Detail', 'details' => 'Detail',
        'ordered_qty' => 'Ordered_Qty', 'ordered' => 'Ordered_Qty', 'order_qty' => 'Ordered_Qty',
        'prev_qty' => 'Prev_Qty', 'previous_qty' => 'Prev_Qty',
        'quantity_received' => 'Quantity_Received', 'qty_received' => 'Quantity_Received', 'received' => 'Quantity_Received', 'qty' => 'Quantity_Received',
        'unit_price' => 'Unit_Price', 'price' => 'Unit_Price', 'unitprice' => 'Unit_Price',
        'taxtotal_incl' => 'TaxTotal_Incl', 'tax_total' => 'TaxTotal_Incl', 'total' => 'TaxTotal_Incl',
        'tax' => 'Tax',
        'currency' => 'Currency',
        'item_group' => 'Item_Group', 'group' => 'Item_Group'
    ];
    
    $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'];
    
    $insertStmt = $pdo->prepare("INSERT INTO grv (`" . implode('`, `', $dbCols) . "`) VALUES (" . implode(',', array_fill(0, count($dbCols), '?')) . ")");
    
    foreach ($data as $idx => $row) {
        // Flexible ID matching
        $grvId = trim($row['grv_id'] ?? $row['id'] ?? $row['grvid'] ?? $row['grvnumber'] ?? ('GRV-Row' . ($idx+2)));
        
        $colNames = [];
        foreach ($row as $excelCol => $val) {
            foreach ($colMapping as $mapCol => $dbCol) {
                if (strtolower($excelCol) === strtolower($mapCol) && in_array($dbCol, $dbCols)) {
                    $colNames[$dbCol] = $val;
                    break;
                }
            }
        }
        
        error_log("GRV Row " . ($idx+1) . " colNames: " . json_encode($colNames));
        
        $dateColVariants = ['grv_date', 'grvdate', 'grv_date', 'date', 'grv date', 'date_received', 'received_date', 'grvdate', 'date', 'doc_date'];
        $grvDateValue = null;
        
        // First try direct lookup by exact/near match
        $dateKeys = ['grv_date', 'grvdate', 'GRV_Date', 'GRVDate', 'date', 'Date', 'GRV Date', 'grv date', 'date received', 'received date', 'doc date', 'grvdate', 'DateReceived'];
        foreach ($dateKeys as $key) {
            if (isset($row[$key]) && !empty(trim($row[$key]))) {
                $grvDateValue = $row[$key];
                error_log("GRV date found via exact match: key='$key', value='$grvDateValue'");
                break;
            }
        }
        
        // If not found, scan all columns for date-like content
        if ($grvDateValue === null) {
            foreach ($row as $k => $v) {
                if (empty(trim((string)$v))) continue;
                $normalizedK = strtolower(trim((string)$k));
                $normalizedK = str_replace(['_', ' ', '-'], '', $normalizedK);
                $dateKeywords = ['grvdate', 'date', 'docdate', 'received'];
                foreach ($dateKeywords as $kw) {
                    if (strpos($normalizedK, $kw) !== false) {
                        $grvDateValue = $v;
                        error_log("GRV date found via keyword scan: key='$k', value='$v'");
                        break 2;
                    }
                }
            }
        }
        
        // If still not found, check ALL columns - look for anything that looks like a date
        if ($grvDateValue === null) {
            foreach ($row as $k => $v) {
                $vStr = trim((string)$v);
                if (empty($vStr)) continue;
                // Check if value looks like a date (contains numbers and separators)
                if (preg_match('/^[\d\-\/\.]+$/', $vStr) && strlen($vStr) >= 6) {
                    $grvDateValue = $v;
                    error_log("GRV date found via pattern match: key='$k', value='$v'");
                    break;
                }
            }
        }
        
        // Store parsed date in colNames for fallback
        if ($grvDateValue !== null) {
            $parsedDate = parseDate($grvDateValue, "Row " . ($idx + 2), $dateErrors);
            if ($parsedDate !== null) {
                $colNames['GRV_Date'] = $parsedDate;
                error_log("GRV date parsed: '$grvDateValue' -> '$parsedDate'");
            } else {
                $results['invalid_dates'][] = ['row' => $idx + 2, 'grv_id' => $grvId, 'field' => 'GRV_Date', 'value' => $grvDateValue];
            }
        }
        
        $values = [];
        foreach ($dbCols as $dbCol) {
            $default = match($dbCol) {
                'GRV_Date' => date('Y-m-d'),
                'Ordered_Qty' => 0, 'Prev_Qty' => 0, 'Quantity_Received' => 0, 'Unit_Price' => 0, 'Tax' => 0, 'TaxTotal_Incl' => 0,
                'Currency' => 'USD',
                default => null
            };
            
            if ($dbCol === 'GRV_Date') {
                $values[] = $colNames[$dbCol] ?? $default;
            } else {
                $values[] = $colNames[$dbCol] ?? $default;
            }
        }
        
        try {
            $insertStmt->execute($values);
            $results['success']++;
            error_log("GRV inserted successfully. GRV_Date value: " . ($values[3] ?? 'default'));
        } catch (Exception $e) {
            $errMsg = "Row " . ($idx + 2) . " (GRV_ID:'$grvId'): " . $e->getMessage();
            $results['errors'][] = $errMsg;
            error_log($errMsg);
        }
    }
    
    if (!empty($dateErrors)) {
        $results['errors'] = array_merge($results['errors'], $dateErrors);
    }
    
    return $results;
}

function importTransactionData($pdo, $data) {
    $results = ['success' => 0, 'errors' => [], 'count' => 0, 'invalid_dates' => []];
    $dateErrors = [];
    
    $colMapping = [
        'item_code' => 'item_code', 'source_type' => 'source_type', 'item_description' => 'item_description',
        'transaction_date' => 'transaction_date', 'reference' => 'reference', 'trans_type' => 'trans_type',
        'quantity_in' => 'quantity_in', 'quantity_out' => 'quantity_out', 'unit_cost' => 'unit_cost'
    ];
    
    $dbCols = [
        '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'
    ];
    
    $insertStmt = $pdo->prepare("INSERT INTO inventory_transaction (`" . implode('`, `', $dbCols) . "`) VALUES (" . implode(',', array_fill(0, count($dbCols), '?')) . ")");
    
    foreach ($data as $idx => $row) {
        $itemCode = trim($row['item_code'] ?? '');
        if (empty($itemCode)) continue;
        
        $colNames = [];
        foreach ($row as $col => $val) {
            $dbCol = $colMapping[strtolower($col)] ?? null;
            if ($dbCol && in_array($dbCol, $dbCols)) {
                $colNames[$dbCol] = $val;
            }
        }
        
        try {
            $values = [];
            $transactionDateValue = null;
            $expiryDateValue = null;
            
            foreach ($dbCols as $dbCol) {
                $val = null;
                foreach ($row as $k => $v) {
                    $normalizedK = strtolower(str_replace(['_', ' ', '-'], '', trim((string)$k)));
                    $normalizedCol = strtolower(str_replace(['_', ' ', '-'], '', $dbCol));
                    if ($normalizedK === $normalizedCol) {
                        $val = $v;
                        break;
                    }
                }
                
                if ($dbCol === 'transaction_date') {
                    $transactionDateValue = $val;
                    if (!empty($val)) {
                        $parsedDate = parseDate($val, "Row " . ($idx + 1), $dateErrors);
                        $values[] = $parsedDate;
                        if ($parsedDate === null) {
                            $results['invalid_dates'][] = ['row' => $idx + 1, 'item_code' => $itemCode, 'field' => 'transaction_date', 'value' => $val];
                        }
                        continue;
                    }
                } elseif ($dbCol === 'expiry_date') {
                    $expiryDateValue = $val;
                    if (!empty($val)) {
                        $parsedDate = parseDate($val, "Row " . ($idx + 1), $dateErrors);
                        $values[] = $parsedDate;
                        if ($parsedDate === null) {
                            $results['invalid_dates'][] = ['row' => $idx + 1, 'item_code' => $itemCode, 'field' => 'expiry_date', 'value' => $val];
                        }
                        continue;
                    }
                }
                $values[] = $val;
            }
            
            $insertStmt->execute($values);
            $results['success']++;
        } catch (Exception $e) {
            $results['errors'][] = "Row " . ($idx + 1) . ": " . $e->getMessage();
        }
    }
    
    if (!empty($dateErrors)) {
        $results['errors'] = array_merge($results['errors'], $dateErrors);
    }
    
    $results['count'] = count($data);
    return $results;
}

function importOrdersData($pdo, $data) {
    $results = ['success' => 0, 'errors' => [], 'count' => 0, 'invalid_dates' => []];
    $dateErrors = [];
    
    $colMapping = [
        'orderid' => 'OrderID', 'sourceid' => 'SourceID', 'buyer' => 'Buyer', 'reference' => 'Reference',
        'itemcode' => 'ItemCode', 'itemdescription' => 'ItemDescription', 'uom' => 'UOM', 'itemcategory' => 'ItemCategory',
        'itemgroup' => 'ItemGroup', 'goodtype' => 'GoodType', 'detail' => 'Detail', 'quantity' => 'Quantity',
        'unitprice' => 'UnitPrice', 'grossprice' => 'GrossPrice', 'division' => 'Division',
        'supplysource' => 'SupplySource', 'supplier' => 'Supplier', 'location' => 'Location',
        'referencerq' => 'ReferenceRQ', 'itemtype' => 'ItemType', 'po_date' => 'PO_Date',
        'po_dateapproved' => 'PO_DateApproved', 'paymentterms' => 'PaymentTerms', 'source' => 'Source',
        'goodssupplied' => 'GoodsSupplied'
    ];
    
    $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'];
    
    $existingOrders = [];
    $orderIds = array_filter(array_map(fn($row) => trim($row['orderid'] ?? ''), $data));
    if (!empty($orderIds)) {
        $placeholders = implode(',', array_fill(0, count($orderIds), '?'));
        $stmt = $pdo->prepare("SELECT OrderID FROM orders WHERE OrderID IN ($placeholders)");
        $stmt->execute(array_values($orderIds));
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $existingOrders[$row['OrderID']] = true;
        }
    }
    
    $insertStmt = $pdo->prepare("INSERT INTO orders (`" . implode('`, `', $dbCols) . "`) VALUES (" . implode(',', array_fill(0, count($dbCols), '?')) . ")");
    $updateStmt = $pdo->prepare("UPDATE orders SET `" . implode('` = ?, `', array_slice($dbCols, 1)) . "` = ? WHERE OrderID = ?");
    
    foreach ($data as $idx => $row) {
        $orderId = trim($row['orderid'] ?? '');
        if (empty($orderId)) continue;
        
        $colNames = [];
        foreach ($row as $col => $val) {
            $dbCol = $colMapping[strtolower($col)] ?? null;
            if ($dbCol && in_array($dbCol, $dbCols)) {
                $colNames[$dbCol] = $val;
            }
        }
        
        $poDateVariants = ['po_date', 'podate', 'po date', 'date', 'order_date', 'orderdate', 'order date'];
        $poDateApprovedVariants = ['po_dateapproved', 'po_date_approved', 'podateapproved', 'approved_date', 'approval_date', 'dateapproved', 'approved date'];
        
        $poDateValue = null;
        $poDateApprovedValue = null;
        
        foreach ($poDateVariants as $variant) {
            foreach ($row as $k => $v) {
                $normalizedK = strtolower(str_replace(['_', ' ', '-'], '', trim((string)$k)));
                $normalizedVariant = strtolower(str_replace(['_', ' ', '-'], '', $variant));
                if ($normalizedK === $normalizedVariant && !empty($v)) {
                    $poDateValue = $v;
                    break;
                }
            }
        }
        
        foreach ($poDateApprovedVariants as $variant) {
            foreach ($row as $k => $v) {
                $normalizedK = strtolower(str_replace(['_', ' ', '-'], '', trim((string)$k)));
                $normalizedVariant = strtolower(str_replace(['_', ' ', '-'], '', $variant));
                if ($normalizedK === $normalizedVariant && !empty($v)) {
                    $poDateApprovedValue = $v;
                    break;
                }
            }
        }
        
        $exists = isset($existingOrders[$orderId]);
        
        try {
            $values = [];
            foreach ($dbCols as $dbCol) {
                if ($dbCol === 'PO_Date') {
                    if ($poDateValue !== null) {
                        $parsedDate = parseDate($poDateValue, "Row " . ($idx + 1), $dateErrors);
                        $values[] = $parsedDate;
                        if ($parsedDate === null) {
                            $results['invalid_dates'][] = ['row' => $idx + 1, 'order_id' => $orderId, 'field' => 'PO_Date', 'value' => $poDateValue];
                        }
                    } else {
                        $values[] = $colNames[$dbCol] ?? null;
                    }
                } elseif ($dbCol === 'PO_DateApproved') {
                    if ($poDateApprovedValue !== null) {
                        $parsedDate = parseDate($poDateApprovedValue, "Row " . ($idx + 1), $dateErrors);
                        $values[] = $parsedDate;
                        if ($parsedDate === null) {
                            $results['invalid_dates'][] = ['row' => $idx + 1, 'order_id' => $orderId, 'field' => 'PO_DateApproved', 'value' => $poDateApprovedValue];
                        }
                    } else {
                        $values[] = $colNames[$dbCol] ?? null;
                    }
                } else {
                    $values[] = $colNames[$dbCol] ?? null;
                }
            }
            
            if ($exists) {
                array_pop($values); // Remove OrderID for update
                $updateStmt->execute($values);
            } else {
                $insertStmt->execute($values);
            }
            $results['success']++;
        } catch (Exception $e) {
            $results['errors'][] = "Row " . ($idx + 1) . ": " . $e->getMessage();
        }
    }
    
    if (!empty($dateErrors)) {
        $results['errors'] = array_merge($results['errors'], $dateErrors);
    }
    
    $results['count'] = count($data);
    return $results;
}

function importSuppliersData($pdo, $data) {
    $results = ['success' => 0, 'errors' => [], 'count' => 0];
    
    $colMapping = [
        'supplierid' => 'SupplierID', 'suppliername' => 'SupplierName', 'name' => 'SupplierName',
        'contactperson' => 'ContactPerson', 'contact_person' => 'ContactPerson',
        'phone' => 'Phone', 'contact_number' => 'Phone', 'email' => 'Email', 
        'address' => 'Address', 'leadtime_days' => 'LeadTime_Days', 'rating' => 'Rating'
    ];
    
    $dbCols = ['SupplierID', 'SupplierName', 'ContactPerson', 'Phone', 'Email', 'Address', 'LeadTime_Days', 'Rating'];
    
    $insertStmt = $pdo->prepare("INSERT INTO suppliers (`SupplierID`, `SupplierName`, `ContactPerson`, `Phone`, `Email`, `Address`, `LeadTime_Days`, `Rating`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
    
    foreach ($data as $idx => $row) {
        $supplierId = trim($row['supplierid'] ?? '');
        if (empty($supplierId)) continue;
        
        $colNames = [];
        foreach ($row as $col => $val) {
            $dbCol = $colMapping[strtolower($col)] ?? null;
            if ($dbCol && in_array($dbCol, $dbCols)) {
                $colNames[$dbCol] = $val;
            }
        }
        
        try {
            $values = [
                $colNames['SupplierID'] ?? null,
                $colNames['SupplierName'] ?? null,
                $colNames['ContactPerson'] ?? null,
                $colNames['Phone'] ?? null,
                $colNames['Email'] ?? null,
                $colNames['Address'] ?? null,
                $colNames['LeadTime_Days'] ?? 0,
                $colNames['Rating'] ?? 0
            ];
            
            $insertStmt->execute($values);
            $results['success']++;
        } catch (Exception $e) {
            $results['errors'][] = "Row " . ($idx + 1) . ": " . $e->getMessage();
        }
    }
    
    $results['count'] = count($data);
    return $results;
}

function importUsersData($pdo, $data) {
    $results = ['success' => 0, 'errors' => [], 'count' => 0];
    
    $colMapping = [
        'id' => 'id', 'username' => 'username', 'email' => 'email',
        'full_name' => 'full_name', 'role' => 'role', 'active' => 'active',
        'created_at' => 'created_at'
    ];
    
    $dbCols = ['username', 'email', 'full_name', 'role', 'active', 'created_at'];
    
    $insertStmt = $pdo->prepare("INSERT INTO users (`username`, `email`, `full_name`, `role`, `active`, `created_at`) VALUES (?, ?, ?, ?, ?, ?)");
    
    foreach ($data as $idx => $row) {
        $username = trim($row['username'] ?? '');
        if (empty($username)) continue;
        
        $colNames = [];
        foreach ($row as $col => $val) {
            $dbCol = $colMapping[strtolower($col)] ?? null;
            if ($dbCol && in_array($dbCol, $dbCols)) {
                $colNames[$dbCol] = $val;
            }
        }
        
        try {
            $values = [
                $colNames['username'] ?? null,
                $colNames['email'] ?? null,
                $colNames['full_name'] ?? null,
                $colNames['role'] ?? 'User',
                $colNames['active'] ?? 1,
                $colNames['created_at'] ?? date('Y-m-d H:i:s')
            ];
            
            $insertStmt->execute($values);
            $results['success']++;
        } catch (Exception $e) {
            $results['errors'][] = "Row " . ($idx + 1) . ": " . $e->getMessage();
        }
    }
    
    $results['count'] = count($data);
    return $results;
}

function importValuationData($pdo, $data) {
    $results = ['success' => 0, 'errors' => [], 'count' => 0];
    
    $valuationDate = date('Y-m-d');
    
    $insertStmt = $pdo->prepare("INSERT INTO inventory_valuation (item_code, item_description, item_group, group_category, qty, unit_cost, valuation_date) VALUES (?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE item_description = VALUES(item_description), item_group = VALUES(item_group), group_category = VALUES(group_category), qty = VALUES(qty), unit_cost = VALUES(unit_cost)");
    
    foreach ($data as $idx => $row) {
        try {
            $insertStmt->execute([
                $row['itemcode'] ?? $row['item_code'] ?? '',
                $row['itemdescription'] ?? $row['item_description'] ?? null,
                $row['itemgroup'] ?? $row['item_group'] ?? null,
                $row['groupcategory'] ?? $row['group_category'] ?? null,
                (int)($row['qty'] ?? $row['quantity'] ?? 0),
                (float)($row['unitcost'] ?? $row['unit_cost'] ?? 0),
                $valuationDate
            ]);
            $results['success']++;
        } catch (Exception $e) {
            $results['errors'][] = "Row " . ($idx + 1) . ": " . $e->getMessage();
        }
    }
    
    $results['count'] = count($data);
    return $results;
}

function importSupplierForecastData($pdo, $data) {
    $results = ['success' => 0, 'errors' => [], 'count' => 0];
    
    $colMapping = [
        'supplier_id' => 'supplier_id', 'supplier_name' => 'supplier_name',
        'item_code' => 'item_code', 'item_description' => 'item_description',
        'forecast_month' => 'forecast_month', 'forecast_month' => 'forecast_month',
        'quantity_forecast' => 'quantity_forecast', 'quantity' => 'quantity_forecast',
        'unit_price' => 'unit_price', 'estimated_value' => 'estimated_value',
        'notes' => 'notes'
    ];
    
    $dbCols = ['supplier_id', 'supplier_name', 'item_code', 'item_description', 'forecast_month', 'quantity_forecast', 'unit_price', 'estimated_value', 'notes'];
    
    $insertStmt = $pdo->prepare("INSERT INTO supplier_forecasts (`" . implode('`, `', $dbCols) . "`) VALUES (" . implode(',', array_fill(0, count($dbCols), '?')) . ")");
    
    foreach ($data as $idx => $row) {
        $colNames = [];
        foreach ($row as $col => $val) {
            $dbCol = $colMapping[strtolower($col)] ?? null;
            if ($dbCol && in_array($dbCol, $dbCols)) {
                $colNames[$dbCol] = $val;
            }
        }
        
        $quantityForecast = floatval($colNames['quantity_forecast'] ?? 0);
        $unitPrice = floatval($colNames['unit_price'] ?? 0);
        $estimatedValue = $quantityForecast * $unitPrice;
        
        try {
            $values = [
                $colNames['supplier_id'] ?? null,
                $colNames['supplier_name'] ?? null,
                $colNames['item_code'] ?? null,
                $colNames['item_description'] ?? null,
                $colNames['forecast_month'] ?? null,
                $quantityForecast,
                $unitPrice,
                $estimatedValue,
                $colNames['notes'] ?? null
            ];
            
            $insertStmt->execute($values);
            $results['success']++;
        } catch (Exception $e) {
            $results['errors'][] = "Row " . ($idx + 1) . ": " . $e->getMessage();
        }
    }
    
    $results['count'] = count($data);
    return $results;
}

function handleExport() {
    $type = $_GET['type'] ?? '';
    $filters = array_filter([
        'start_date' => $_GET['start_date'] ?? '',
        'end_date' => $_GET['end_date'] ?? '',
        'supplier' => $_GET['supplier'] ?? '',
        'status' => $_GET['status'] ?? '',
        'item_code' => $_GET['item_code'] ?? '',
        'source_type' => $_GET['source_type'] ?? '',
        'trans_type' => $_GET['trans_type'] ?? '',
        'order_no' => $_GET['order_no'] ?? '',
        'category' => $_GET['category'] ?? '',
        'role' => $_GET['role'] ?? ''
    ]);

    $exportMap = [
        'items_master' => 'exportItems',
        'grv' => 'exportGRV',
        'inventory_transaction' => 'exportInventoryTransactionsAPI',
        'orders' => 'exportOrders',
        'suppliers' => 'exportSuppliers',
        'users' => 'exportUsers',
        'supplier_forecast' => 'exportSupplierForecast',
        'valuation' => 'exportValuation',
        'inventory_valuation' => 'exportValuation'
    ];

    if (!isset($exportMap[$type])) {
        jsonResponse(['error' => 'Invalid type'], 400);
    }

    $exportFunc = $exportMap[$type];
    if (!function_exists($exportFunc)) {
        jsonResponse(['error' => 'Export function not found'], 500);
    }

    $data = $exportFunc($filters);
    $headers = [];
    $defaultHeaders = [
'items_master' => ['ItemCode', 'ItemName', 'Category', 'UnitOfMeasure', 'ReorderLevel', 'UnitCost'],
        'grv' => ['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'],
        'inventory_transaction' => ['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'],
        'orders' => ['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'],
        'suppliers' => ['SupplierID', 'SupplierName', 'ContactPerson', 'Phone', 'Email', 'Address', 'LeadTime_Days', 'Rating'],
        'users' => ['id', 'username', 'email', 'full_name', 'role', 'active', 'created_at'],
        'supplier_forecast' => ['supplier_id', 'supplier_name', 'item_code', 'item_description', 'forecast_month', 'quantity_forecast', 'unit_price', 'estimated_value', 'notes'],
        'valuation' => ['ItemCode', 'ItemDescription', 'ItemGroup', 'GroupCategory', 'Qty', 'UnitCost', 'Value', 'ValuationDate'],
    ];
    $headers = $defaultHeaders[$type] ?? [];

    $format = $_GET['format'] ?? 'xlsx';
    
    if ($format === 'csv') {
        exportToCSV($headers, $data, $type);
    } else {
        exportToExcel($headers, $data, $type);
    }
}

function exportItems($filters = []) {
    $pdo = getDBConnection();
$stmt = $pdo->query("SELECT ItemCode, ItemName, Category, UnitOfMeasure, ReorderLevel, UnitCost FROM items_master");
    return $stmt->fetchAll(PDO::FETCH_NUM);
}

function exportGRV($filters = []) {
    $pdo = getDBConnection();
    $stmt = $pdo->query("SELECT * FROM grv");
    return $stmt->fetchAll(PDO::FETCH_NUM);
}

function exportInventoryTransactionsAPI($filters = []) {
    $pdo = getDBConnection();
    $stmt = $pdo->query("SELECT * FROM inventory_transaction");
    return $stmt->fetchAll(PDO::FETCH_NUM);
}

function exportSuppliers($filters = []) {
    $pdo = getDBConnection();
    $stmt = $pdo->query("SELECT * FROM suppliers");
    return $stmt->fetchAll(PDO::FETCH_NUM);
}

function exportOrders($filters = []) {
    $pdo = getDBConnection();
    $stmt = $pdo->query("SELECT * FROM orders");
    return $stmt->fetchAll(PDO::FETCH_NUM);
}

function exportUsers($filters = []) {
    $pdo = getDBConnection();
    $stmt = $pdo->query("SELECT id, username, email, full_name, role, active, created_at FROM users");
    return $stmt->fetchAll(PDO::FETCH_NUM);
}

function exportSupplierForecast($filters = []) {
    $pdo = getDBConnection();
    $sql = "SELECT supplier_id, supplier_name, item_code, item_description, forecast_month, quantity_forecast, unit_price, estimated_value, notes FROM supplier_forecasts";
    $conditions = [];
    $params = [];
    
    if (!empty($filters['supplier'])) {
        $conditions[] = "supplier_id = ?";
        $params[] = $filters['supplier'];
    }
    if (!empty($filters['month'])) {
        $conditions[] = "forecast_month = ?";
        $params[] = $filters['month'];
    }
    if (!empty($filters['start_date'])) {
        $conditions[] = "forecast_month >= ?";
        $params[] = $filters['start_date'];
    }
    if (!empty($filters['end_date'])) {
        $conditions[] = "forecast_month <= ?";
        $params[] = $filters['end_date'];
    }
    
    if (!empty($conditions)) {
        $sql .= " WHERE " . implode(" AND ", $conditions);
    }
    $sql .= " ORDER BY forecast_month DESC, supplier_name ASC";
    
    if (!empty($params)) {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_NUM);
    }
    $stmt = $pdo->query($sql);
    return $stmt->fetchAll(PDO::FETCH_NUM);
}

function exportValuation($filters = []) {
    $pdo = getDBConnection();
    $date = $filters['date'] ?? date('Y-m-d');
    $stmt = $pdo->prepare("SELECT item_code, item_description, item_group, group_category, qty, unit_cost, value, valuation_date FROM inventory_valuation WHERE valuation_date = ? ORDER BY item_code");
    $stmt->execute([$date]);
    return $stmt->fetchAll(PDO::FETCH_NUM);
}

function exportToExcel($headers, $data, $type) {
    $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();

    $sheet->fromArray($headers, null, 'A1');
    
    $headerRange = 'A1:' . getExcelColumnLetter(count($headers) - 1) . '1';
    $sheet->getStyle($headerRange)->applyFromArray([
        'font' => ['bold' => true],
        'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['rgb' => '4472C4']],
        'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER]
    ]);

    if (!empty($data)) {
        $sheet->fromArray($data, null, 'A2');
    }

    $colCount = count($headers);
    $lastCol = '';
    for ($i = 0; $i < $colCount; $i++) {
        $lastCol = chr(65 + ($i % 26));
        if ($i >= 26) {
            $lastCol = chr(64 + floor($i / 26)) . $lastCol;
        }
        $sheet->getColumnDimension($lastCol)->setAutoSize(true);
    }

    $filename = ucfirst($type) . '_Export_' . date('Y-m-d_His') . '.xlsx';
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');

    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
    $writer->save('php://output');
    exit;
}

function exportToCSV($headers, $data, $type) {
    $filename = ucfirst($type) . '_Export_' . date('Y-m-d_His') . '.csv';
    
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');
    
    $output = fopen('php://output', 'w');
    fputcsv($output, $headers);
    
    foreach ($data as $row) {
        fputcsv($output, $row);
    }
    
    fclose($output);
    exit;
}

function getExcelColumnLetter($index) {
    $letter = '';
    for ($i = 0; $i <= $index; $i++) {
        $letter = chr(65 + ($i % 26));
        if ($i >= 26) {
            $letter = chr(64 + floor($i / 26)) . $letter;
        }
    }
    return $letter;
}