<?php
require 'vendor/autoload.php'; // Ensure this path matches your Composer autoload location
require_once('includes/load.php'); // Load database connection

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

$sql = "SELECT 
            od.order_sn AS invoice, 
            opq.resi,
            w.warehouse_name, 
            s.shop_name, 
            opq.is_ndd,
            o.shipping_carrier,
            opq.is_oos_sku,
            CASE 
                WHEN od.model_sku IS NOT NULL AND od.model_sku != '' THEN od.model_sku 
                ELSE od.item_sku 
            END AS sku, 
            od.model_quantity_purchased AS qty, 
            IFNULL(od.model_discounted_price, '') AS price 
        FROM 
            orders_packing_queue opq 
        JOIN 
            order_detail od 
        ON 
            opq.order_sn = od.order_sn COLLATE utf8mb4_unicode_ci 
            AND od.deleted_at IS NULL 
        JOIN warehouse w ON w.zipcode = opq.zipcode
        JOIN `orders` o ON od.order_id = o.id
        JOIN shop s ON o.shop_id = s.shop_id
        WHERE 
            opq.processed = 0 
            AND opq.stok_failed = 1 
            AND opq.approve_required = 0";

$result = $db->query($sql);

// Create new Spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Header columns
$sheet->setCellValue('A1', 'Invoice');
$sheet->setCellValue('B1', 'Warehouse');
$sheet->setCellValue('C1', 'SKU');
$sheet->setCellValue('D1', 'Quantity');
$sheet->setCellValue('E1', 'Price');
$sheet->setCellValue('F1', 'Resi');
$sheet->setCellValue('G1', 'Shop');
$sheet->setCellValue('H1', 'Is Ganti SKU');

// Count occurrences of each invoice
$invoiceCounts = [];
while ($row = $result->fetch_assoc()) {
    $invoice = $row['invoice'];
    $invoiceCounts[$invoice] = isset($invoiceCounts[$invoice]) ? $invoiceCounts[$invoice] + 1 : 1;
}

// Add data from query to sheet
$rowNumber = 2; // Start from the second row (header is in the first row)
$previousInvoice = null;
$result->data_seek(0); // Reset result pointer to re-fetch the data

while ($row = $result->fetch_assoc()) {
    $invoice = $row['invoice'];

    // Add data row
    $sheet->setCellValueExplicit('A' . $rowNumber,  $row['invoice'], \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
    $sheet->setCellValue('B' . $rowNumber, $row['warehouse_name']);
    $sheet->setCellValue('C' . $rowNumber, $row['sku']);
    $sheet->setCellValue('D' . $rowNumber, $row['qty']);
    $sheet->setCellValue('E' . $rowNumber, $row['price']);
    $sheet->setCellValueExplicit('F' . $rowNumber, $row['resi'], \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
    $sheet->setCellValue('G' . $rowNumber, $row['shop_name']);
    $sheet->setCellValue('H' . $rowNumber, (($row['is_ndd'] == 1) || isInstant($row['shipping_carrier']) || $row['is_oos_sku'] == 1) ? 'Ganti SKU' : 'Ganti WH');

    // Apply coloring based on the invoice count
    $currentRow = $sheet->getRowDimension($rowNumber);
    $cellRange = 'A' . $rowNumber . ':H' . $rowNumber;
    $excelRow = $sheet->getStyle($cellRange);

    if ($invoiceCounts[$invoice] === 1) {
        // If invoice appears only once, color white
        $excelRow->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
        $excelRow->getFill()->getStartColor()->setARGB('FFFFFF'); // White background
        $excelRow->getFont()->getColor()->setARGB('000000'); // Black text
    } else if ($invoice === $previousInvoice) {
        // If the invoice is the same as the previous row, color black
        $excelRow->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
        $excelRow->getFill()->getStartColor()->setARGB('01a6a4'); // Blue background
        $excelRow->getFont()->getColor()->setARGB('FFFFFF'); // White text
    } else {
        // If it's a new invoice, color grey
        $excelRow->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
        $excelRow->getFill()->getStartColor()->setARGB('808080'); // Grey background
        $excelRow->getFont()->getColor()->setARGB('FFFFFF'); // White text
    }

    // Update previous invoice
    $previousInvoice = $invoice;
    $rowNumber++;
}

// Set filename for the Excel file
$filename = 'export_orders_oos.xlsx';

// Set headers for Excel file download
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');

// Write Excel file to output
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;
