<?php
$page_title = 'Import Products';
require_once('includes/load.php');
page_require_level(3);

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

if (isset($_POST['import'])) {
    require_once('includes/php-excel-reader/excel_reader2.php');
    require_once('includes/php-excel-reader/SpreadsheetReader.php');
    
    $uploaded_file = $_FILES['file']['tmp_name'];
    $allowed_file_types = ['application/vnd.ms-excel', 'text/xls', 'text/xlsx', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];
    
    if (!in_array($_FILES['file']['type'], $allowed_file_types)) {
        $session->msg('d', 'Invalid file type. Only Excel files are allowed.');
        redirect('product_new_import.php', false);
    }
    
    try {
        $reader = new SpreadsheetReader($uploaded_file);
        $header = true;
        $success_count = 0;
        $error_count = 0;
        $errors = array();
        
        foreach ($reader as $row) {
            if ($header) {
                $header = false;
                continue;
            }
            
            // Validate required fields
            if (empty($row[0]) {
                $errors[] = "Row skipped: Part Number is required";
                $error_count++;
                continue;
            }
            
            $part_number  = $db->escape($row[0]);
            $name         = $db->escape($row[1] ?? '');
            $new_code     = $db->escape($row[2] ?? '');
            $categorie_id = $db->escape($row[3] ?? '');
            $quantity     = $db->escape($row[4] ?? 0);
            $uom          = $db->escape($row[5] ?? '');
            $buy_price    = $db->escape($row[6] ?? 0);
            $loc_id       = $db->escape($row[7] ?? '');
            
            // Check if product already exists
            $existing = find_by_field('products', 'part_number', $part_number);
            
            if ($existing) {
                // Update existing product
                $sql = "UPDATE products SET 
                        name='{$name}', 
                        new_code='{$new_code}', 
                        categorie_id='{$categorie_id}', 
                        quantity='{$quantity}', 
                        uom='{$uom}', 
                        buy_price='{$buy_price}', 
                        loc_id='{$loc_id}'
                        WHERE part_number='{$part_number}'";
            } else {
                // Insert new product
                $sql = "INSERT INTO products (part_number, name, new_code, categorie_id, quantity, uom, buy_price, loc_id) 
                        VALUES ('{$part_number}', '{$name}', '{$new_code}', '{$categorie_id}', '{$quantity}', '{$uom}', '{$buy_price}', '{$loc_id}')";
            }
            
            if ($db->query($sql)) {
                $success_count++;
            } else {
                $errors[] = "Error processing row with Part Number: {$part_number} - " . $db->get_last_error();
                $error_count++;
            }
        }
        
        if ($success_count > 0) {
            $session->msg('s', "Successfully imported {$success_count} products.");
        }
        if ($error_count > 0) {
            $session->msg('d', "{$error_count} products failed to import. See errors below.");
            $_SESSION['import_errors'] = $errors;
        }
        
        redirect('product_new.php', false);
        
    } catch (Exception $e) {
        $session->msg('d', 'Error reading file: ' . $e->getMessage());
        redirect('product_new_import.php', false);
    }
}
?>

<?php include_once('layouts/header.php'); ?>

<div class="row">
    <div class="col-md-12">
        <?php echo display_msg($msg); ?>
    </div>
</div>

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-default">
            <div class="panel-heading clearfix">
                <strong>
                    <span class="glyphicon glyphicon-import"></span>
                    <span>Import Products from Excel</span>
                </strong>
                <div class="pull-right">
                    <a href="product_new.php" class="btn btn-primary">Back to Products</a>
                </div>
            </div>
            <div class="panel-body">
                <div class="alert alert-info">
                    <p><strong>Instructions:</strong></p>
                    <ol>
                        <li>Download the <a href="includes/product_import_template.xlsx" download>Excel template</a> for reference</li>
                        <li>Ensure your Excel file has the following columns in order:
                            <ul>
                                <li>Part Number (required)</li>
                                <li>Product Name</li>
                                <li>New Code</li>
                                <li>Category ID</li>
                                <li>Quantity</li>
                                <li>Unit of Measure</li>
                                <li>Unit Price</li>
                                <li>Location ID</li>
                            </ul>
                        </li>
                        <li>Existing products with matching Part Numbers will be updated</li>
                        <li>New Part Numbers will be added as new products</li>
                    </ol>
                </div>
                
                <?php if (isset($_SESSION['import_errors']) && !empty($_SESSION['import_errors'])): ?>
                    <div class="alert alert-danger">
                        <h4>Import Errors:</h4>
                        <ul>
                            <?php foreach ($_SESSION['import_errors'] as $error): ?>
                                <li><?php echo $error; ?></li>
                            <?php endforeach; ?>
                        </ul>
                    </div>
                    <?php unset($_SESSION['import_errors']); ?>
                <?php endif; ?>
                
                <form action="product_new_import.php" method="post" enctype="multipart/form-data" class="form-horizontal">
                    <div class="form-group">
                        <label for="file" class="control-label col-md-3">Select Excel File:</label>
                        <div class="col-md-6">
                            <input type="file" name="file" id="file" class="form-control" required accept=".xls,.xlsx">
                        </div>
                    </div>
                    <div class="form-group">
                        <div class="col-md-6 col-md-offset-3">
                            <button type="submit" name="import" class="btn btn-primary">
                                <span class="glyphicon glyphicon-import"></span> Import
                            </button>
                        </div>
                    </div>
                </form>
            </div>
        </div>
    </div>
</div>

<?php include_once('layouts/footer.php'); ?>