<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);

require 'db.php';
require_once __DIR__ . '/vendor/autoload.php';

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

// Get date range
$from = $_GET['from'] ?? '';
$to   = $_GET['to'] ?? '';

if (!$from || !$to) {
    die('Error: From and To dates are required.');
}

// Fetch transactions
$stmt = $pdo->prepare("
    SELECT po_date, po_number, inv_number, inv_date, vendor, item, department,
           department_remarks, unit, uom, price, row_remarks, sst, total_price,
           min_amount_for_delivery, contact, email, person_in_charge, terms, payment_remarks, remarks
    FROM transactions
    WHERE inv_date BETWEEN :from AND :to
    ORDER BY id, inv_date
");
$stmt->execute(['from' => $from, 'to' => $to]);
$transactions = $stmt->fetchAll(PDO::FETCH_ASSOC);

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

/* =========================
   HEADERS
========================= */
$headers = [
    'ID', 'PO DATE', 'PO NO', 'INV/PI/CN NO', 'INV DATE', 'VENDOR"S', 'ITEM', 'DEPARTMENT',
    'REMARK', 'UNIT', 'UOM', 'PRICES (RM)', 'REMARK', 'SST', 'TOTAL PRICE (RM)',
    'MIN AMOUNT FOR DELIVERY (RM)', 'CONTACT', 'EMAIL', 'PERSON IN CHARGE', 'TERMS',
    'PAYMENT REMARKS', 'REMARKS'
];

$col = 'A';
foreach ($headers as $header) {
    $sheet->setCellValue($col.'1', $header);
    $col++;
}

/* =========================
   IMPORTANT PART
   Force ONLY inv_number column (D) as TEXT
========================= */
$sheet->getStyle('D')->getNumberFormat()
      ->setFormatCode(NumberFormat::FORMAT_TEXT);

/* =========================
   DATA
========================= */
$rowNumber = 2;
$idCounter = 1;

foreach ($transactions as $row) {
    $sheet->setCellValue('A'.$rowNumber, $idCounter++);           // ID
    $sheet->setCellValue('B'.$rowNumber, $row['po_date'] ?? '');  // PO DATE
    $sheet->setCellValue('C'.$rowNumber, $row['po_number'] ?? '');// PO NO
    $sheet->setCellValue('D'.$rowNumber, $row['inv_number'] ?? '');// INV NUMBER (TEXT)
    $sheet->setCellValue('E'.$rowNumber, $row['inv_date'] ?? ''); // INV DATE
    $sheet->setCellValue('F'.$rowNumber, $row['vendor'] ?? '');
    $sheet->setCellValue('G'.$rowNumber, $row['item'] ?? '');
    $sheet->setCellValue('H'.$rowNumber, $row['department'] ?? '');
    $sheet->setCellValue('I'.$rowNumber, $row['department_remarks'] ?? '');
    $sheet->setCellValue('J'.$rowNumber, $row['unit'] ?? '');
    $sheet->setCellValue('K'.$rowNumber, $row['uom'] ?? '');
    $sheet->setCellValue('L'.$rowNumber, $row['price'] ?? '');
    $sheet->setCellValue('M'.$rowNumber, $row['row_remarks'] ?? '');
    $sheet->setCellValue('N'.$rowNumber, $row['sst'] ?? '');
    $sheet->setCellValue('O'.$rowNumber, $row['total_price'] ?? '');
    $sheet->setCellValue('P'.$rowNumber, $row['min_amount_for_delivery'] ?? '');
    $sheet->setCellValue('Q'.$rowNumber, $row['contact'] ?? '');
    $sheet->setCellValue('R'.$rowNumber, $row['email'] ?? '');
    $sheet->setCellValue('S'.$rowNumber, $row['person_in_charge'] ?? '');
    $sheet->setCellValue('T'.$rowNumber, $row['terms'] ?? '');
    $sheet->setCellValue('U'.$rowNumber, $row['payment_remarks'] ?? '');
    $sheet->setCellValue('V'.$rowNumber, $row['remarks'] ?? '');

    $rowNumber++;
}

/* =========================
   OUTPUT
========================= */
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="detail_report.xlsx"');
header('Cache-Control: max-age=0');

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