<?php
// Enable error display for debugging
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

$page_title = 'Import BOM';
require_once('includes/load.php');
page_require_level(1);

// Include PhpSpreadsheet library
require_once 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;

if (isset($_POST['submit'])) {
    $file = $_FILES['file']['tmp_name'];

    try {
        $spreadsheet = IOFactory::load($file);
        $sheet = $spreadsheet->getActiveSheet();
        $highestRow = $sheet->getHighestRow();

        $errors = array();
        $successCount = 0;

        // Default values
        $level = 1;
        $phantom_bom = 0;

        // Start from row 2 (assuming row 1 is header)
        for ($row = 2; $row <= $highestRow; $row++) {
            $child_bom  = trim($sheet->getCell("A$row")->getValue());
            $quantity   = $sheet->getCell("B$row")->getValue();
            $parent_bom = trim($sheet->getCell("C$row")->getValue());

            // Skip entirely empty rows
            if (empty($child_bom) && empty($quantity) && empty($parent_bom)) {
                continue;
            }

            // Validation
            if (empty($child_bom)) {
                $errors[] = "Row $row: Child BOM is empty.";
                continue;
            }

            if (empty($parent_bom)) {
                $errors[] = "Row $row: Parent BOM is empty.";
                continue;
            }

            if (!is_numeric($quantity) || $quantity <= 0) {
                $errors[] = "Row $row: Quantity must be a positive number.";
                continue;
            }

            // Insert into database
            $sql = "INSERT INTO bom_level (parent_bom, child_bom, qty, level, phantom_bom) 
                    VALUES (
                        '".$db->escape($parent_bom)."', 
                        '".$db->escape($child_bom)."', 
                        '".$db->escape($quantity)."',
                        '$level',
                        '$phantom_bom'
                    )";
            $result = $db->query($sql);

            if ($result) {
                $successCount++;
            } else {
                $errors[] = "Row $row: Failed to import - " . $db->get_last_error();
            }
        }

        // Set success message
        if ($successCount > 0) {
            $session->msg('s', "Successfully imported $successCount BOM items.");
        }

        // Set error messages
        if (!empty($errors)) {
            $session->msg('d', implode("<br>", $errors));
        }

        redirect('import_create_bom.php', false);

    } catch (Exception $e) {
        $session->msg('d', 'Error loading file: ' . $e->getMessage());
        redirect('import_create_bom.php', false);
    }
}

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

<div class="row">
    <div class="col-md-12">
        <?php echo display_msg($msg); ?>
    </div>
    <div class="col-md-12">
        <div class="panel panel-default">
            <div class="panel-heading">
                <h3 class="panel-title">Import BOM</h3>
            </div>
            <div class="panel-body">
                <form method="post" action="import_create_bom.php" enctype="multipart/form-data" class="clearfix">
                    <div class="form-group">
                        <label for="file">Excel File:</label>
                        <input type="file" class="form-control" name="file" id="file" accept=".xls,.xlsx" required>
                        <small class="help-block">File format must be Excel (.xls or .xlsx)</small>
                    </div>

                    <div class="form-group">
                        <h4>Excel File Format Requirements:</h4>
                        <ul>
                            <li>First row should be header (will be skipped during import)</li>
                            <li>Columns must be in this order:</li>
                            <ol>
                                <li>Child BOM (required)</li>
                                <li>Quantity (required, must be positive number)</li>
                                <li>Parent BOM (required)</li>
                            </ol>
                            <li>Level will be automatically set to 1</li>
                            <li>All BOMs will be imported as non-phantom (regular) BOMs</li>
                            <li>Do not include merged cells</li>
                        </ul>

                        <p>Sample Excel format:</p>
                        <table class="table table-bordered">
                            <thead>
                                <tr>
                                    <th>Child BOM</th>
                                    <th>Quantity</th>
                                    <th>Parent BOM</th>
                                </tr>
                            </thead>
                            <tbody>
                                <tr>
                                    <td>PROD001</td>
                                    <td>2</td>
                                    <td>PROD100</td>
                                </tr>
                                <tr>
                                    <td>PROD002</td>
                                    <td>1.5</td>
                                    <td>PROD100</td>
                                </tr>
                            </tbody>
                        </table>
                    </div>

                    <button type="submit" name="submit" class="btn btn-primary">Import BOM</button>
                    <a href="create_bom.php" class="btn btn-default">Back to Manual Entry</a>
                </form>
            </div>
        </div>
    </div>
</div>

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