<?php
require 'vendor/autoload.php'; // Make sure to include PhpSpreadsheet

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Database connection
$dbHost = 'localhost';
$dbUser = 'wiselie_nanda';
$dbPass = 'Clemira123';
$dbName = 'wiselie_inventory';
$conn = new mysqli($dbHost, $dbUser, $dbPass, $dbName);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['excel_file'])) {
    // Process uploaded file
    $inputFileName = $_FILES['excel_file']['tmp_name'];
    
    try {
        $spreadsheet = IOFactory::load($inputFileName);
        $worksheet = $spreadsheet->getActiveSheet();
        $rows = $worksheet->toArray();
        
        // Remove header if exists
        array_shift($rows);
        
        $results = [];
        
        foreach ($rows as $row) {
            $resi = $row[0]; // Assuming resi is in first column
            
            // Query database
            $stmt = $conn->prepare("SELECT packer_no, loc FROM awb WHERE resi = ?");
            $stmt->bind_param("s", $resi);
            $stmt->execute();
            $stmt->store_result();
            
            if ($stmt->num_rows > 0) {
                $stmt->bind_result($packer_no, $loc);
                $stmt->fetch();
                $results[] = [
                    'resi' => $resi,
                    'packer_no' => $packer_no,
                    'loc' => $loc
                ];
            } else {
                $results[] = [
                    'resi' => $resi,
                    'packer_no' => 'Not Found',
                    'loc' => 'Not Found'
                ];
            }
            $stmt->close();
        }
        
        // Create new spreadsheet for download
        $outputSpreadsheet = new Spreadsheet();
        $outputSheet = $outputSpreadsheet->getActiveSheet();
        
        // Set headers
        $outputSheet->setCellValue('A1', 'Resi');
        $outputSheet->setCellValue('B1', 'Packer No');
        $outputSheet->setCellValue('C1', 'Location');
        
        // Fill data
        $rowNumber = 2;
        foreach ($results as $result) {
            $outputSheet->setCellValue('A' . $rowNumber, $result['resi']);
            $outputSheet->setCellValue('B' . $rowNumber, $result['packer_no']);
            $outputSheet->setCellValue('C' . $rowNumber, $result['loc']);
            $rowNumber++;
        }
        
        // Save to temporary file
        $writer = new Xlsx($outputSpreadsheet);
        $filename = 'packer_validation_results_' . date('YmdHis') . '.xlsx';
        $temp_file = tempnam(sys_get_temp_dir(), 'phpxltmp');
        $writer->save($temp_file);
        
        // Download the file
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        readfile($temp_file);
        unlink($temp_file);
        exit;
        
    } catch (Exception $e) {
        die("Error processing file: " . $e->getMessage());
    }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Packer No Validation</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            margin: 20px;
        }
        .container {
            max-width: 600px;
            margin: 0 auto;
            padding: 20px;
            border: 1px solid #ddd;
            border-radius: 5px;
        }
        h1 {
            text-align: center;
        }
        .form-group {
            margin-bottom: 15px;
        }
        label {
            display: block;
            margin-bottom: 5px;
            font-weight: bold;
        }
        input[type="file"] {
            padding: 8px;
            width: 100%;
            box-sizing: border-box;
        }
        button {
            background-color: #4CAF50;
            color: white;
            padding: 10px 15px;
            border: none;
            border-radius: 4px;
            cursor: pointer;
            font-size: 16px;
        }
        button:hover {
            background-color: #45a049;
        }
        .note {
            margin-top: 20px;
            padding: 10px;
            background-color: #f8f9fa;
            border-radius: 4px;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>Packer No Validation</h1>
        <form action="" method="post" enctype="multipart/form-data">
            <div class="form-group">
                <label for="excel_file">Upload Excel File:</label>
                <input type="file" name="excel_file" id="excel_file" accept=".xlsx,.xls" required>
            </div>
            <button type="submit">Process and Download</button>
        </form>
        <div class="note">
            <p><strong>Note:</strong> The Excel file should have the resi numbers in the first column (Column A). The downloaded file will contain:</p>
            <ul>
                <li>Column A: Resi</li>
                <li>Column B: Packer No</li>
                <li>Column C: Location</li>
            </ul>
        </div>
    </div>
</body>
</html>