<?php
require 'vendor/autoload.php';

// Database configuration
define('DB_HOST', 'localhost');
define('DB_USER', 'wiselie_nanda');
define('DB_PASS', 'Clemira123');
define('DB_NAME', 'wiselie_inventory');

// Connect to database
try {
    $pdo = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME, DB_USER, DB_PASS);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->exec("set names utf8mb4");
} catch (PDOException $e) {
    die("Database connection failed: " . $e->getMessage());
}

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['excel_file'])) {
    $allowed_types = ['application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'application/vnd.ms-excel'];
    if (!in_array($_FILES['excel_file']['type'], $allowed_types)) {
        die("Error: Only Excel files are allowed.");
    }

    $file = $_FILES['excel_file']['tmp_name'];
    
    try {
        $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        $spreadsheet = $reader->load($file);
        $worksheet = $spreadsheet->getActiveSheet();
        
        $outputData = [];
        $notFoundResi = [];
        
        foreach ($worksheet->getRowIterator(2) as $row) {
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(false);
            
            $rowData = [];
            foreach ($cellIterator as $cell) {
                $rowData[] = $cell->getValue();
            }
            
            $resi = trim($rowData[0] ?? '');
            $invoice = trim($rowData[1] ?? '');
            $warehouse = trim($rowData[2] ?? '');
            
            if (empty($resi)) continue;
            
            $stmt = $pdo->prepare("SELECT scan_in, scan_out FROM resi WHERE resi = ?");
            $stmt->execute([$resi]);
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            
            if ($result) {
                $scan_in = $result['scan_in'] ? date('Y-m-d H:i:s', strtotime($result['scan_in'])) : 'Not Scanned';
                $scan_out = $result['scan_out'] ? date('Y-m-d H:i:s', strtotime($result['scan_out'])) : 'Not Scanned';
            } else {
                $scan_in = 'Not Found';
                $scan_out = 'Not Found';
                $notFoundResi[] = $resi;
            }
            
            $outputData[] = [
                'resi' => $resi,
                'invoice' => $invoice,
                'warehouse' => $warehouse,
                'scan_in' => $scan_in,
                'scan_out' => $scan_out
            ];
        }
        
        $outputSpreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $outputSheet = $outputSpreadsheet->getActiveSheet();
        
        // Set headers
        $headers = ['Resi', 'Invoice', 'Warehouse', 'Scan In', 'Scan Out'];
        foreach ($headers as $index => $header) {
            $col = chr(65 + $index); // A, B, C, D, E
            $outputSheet->setCellValue($col.'1', $header)
                       ->getStyle($col.'1')
                       ->getFont()
                       ->setBold(true);
        }
        
        $rowNumber = 2;
        foreach ($outputData as $row) {
            // Set resi as TEXT format explicitly
            $resiCell = $outputSheet->getCell('A'.$rowNumber);
            $resiCell->setValueExplicit($row['resi'], \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
            
            // Set invoice as TEXT format explicitly
            $invoiceCell = $outputSheet->getCell('B'.$rowNumber);
            $invoiceCell->setValueExplicit($row['invoice'], \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
            
            // Set warehouse
            $outputSheet->setCellValue('C'.$rowNumber, $row['warehouse']);
            
            // Set scan data
            $outputSheet->setCellValue('D'.$rowNumber, $row['scan_in']);
            $outputSheet->setCellValue('E'.$rowNumber, $row['scan_out']);
            
            if ($row['scan_in'] === 'Not Found') {
                $outputSheet->getStyle('A'.$rowNumber.':E'.$rowNumber)
                    ->getFill()
                    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                    ->getStartColor()
                    ->setARGB('FFFF0000');
            }
            
            $rowNumber++;
        }
        
        // Format all cells in column A and B as text
        $outputSheet->getStyle('A2:B'.$rowNumber)
            ->getNumberFormat()
            ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT);
        
        // Auto-size columns
        foreach (range('A', 'E') as $columnID) {
            $outputSheet->getColumnDimension($columnID)->setAutoSize(true);
        }
        
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="resi_validation_'.date('Ymd_His').'.xlsx"');
        header('Cache-Control: max-age=0');
        
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($outputSpreadsheet);
        $writer->save('php://output');
        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>Resi Validation Tool</title>
    <style>
        body { font-family: Arial, sans-serif; max-width: 800px; margin: 0 auto; padding: 20px; }
        .upload-box { border: 2px dashed #ccc; padding: 30px; text-align: center; margin: 20px 0; border-radius: 5px; }
        .btn { background-color: #4CAF50; color: white; padding: 10px 20px; border: none; border-radius: 4px; cursor: pointer; }
        .instructions { background-color: #f8f9fa; padding: 15px; border-radius: 5px; margin-bottom: 20px; }
        table { width: 100%; border-collapse: collapse; margin-top: 20px; }
        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
        th { background-color: #f2f2f2; }
    </style>
</head>
<body>
    <h1>Resi Scan Validation Tool</h1>
    <div class="instructions">
        <h3>Instructions:</h3>
        <ol>
            <li>Prepare an Excel file with this format:
                <table>
                    <tr>
                        <th>Column A</th>
                        <th>Column B</th>
                        <th>Column C</th>
                    </tr>
                    <tr>
                        <td>Resi Number</td>
                        <td>Invoice</td>
                        <td>Warehouse</td>
                    </tr>
                </table>
            </li>
            <li>The output will preserve all original data and add scan information:
                <table>
                    <tr>
                        <th>Column A</th>
                        <th>Column B</th>
                        <th>Column C</th>
                        <th>Column D</th>
                        <th>Column E</th>
                    </tr>
                    <tr>
                        <td>Original Resi</td>
                        <td>Original Invoice</td>
                        <td>Original Warehouse</td>
                        <td>Scan In</td>
                        <td>Scan Out</td>
                    </tr>
                </table>
            </li>
        </ol>
    </div>
    <form method="post" enctype="multipart/form-data">
        <div class="upload-box">
            <input type="file" name="excel_file" accept=".xlsx,.xls" required>
            <p>Only Excel files (.xlsx, .xls) allowed</p>
        </div>
        <button type="submit" class="btn">Validate and Download Results</button>
    </form>
</body>
</html>