<?php
declare(strict_types=1);

require_once __DIR__ . '/../../modules/system/db.php';

$db = db();

/* Fetch unexported invoices */
$stmt = $db->query("
  SELECT
    i.id AS invoice_id,
    i.invoice_number,
    c.name AS customer_name,
    DATE(i.issued_at) AS invoice_date,
    ii.description,
    ii.line_total AS amount_excl_vat,
    ii.vat_amount,
    ii.line_total_incl AS amount_incl_vat,
    CONCAT(i.vat_rate, '%') AS vat_rate
  FROM invoices i
  JOIN companies c ON c.id = i.company_id
  JOIN invoice_items ii ON ii.invoice_id = i.id
  WHERE i.exported_to_sage = 0
  ORDER BY i.issued_at
");

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (!$rows) {
    exit('No invoices to export');
}

/* Create CSV */
$filename = 'sage_export_' . date('Ymd_His') . '.csv';
$path = __DIR__ . '/files/' . $filename;

$fp = fopen($path, 'w');

/* Header */
fputcsv($fp, [
  'Invoice Number',
  'Customer Name',
  'Invoice Date',
  'Description',
  'Amount Excl VAT',
  'VAT Amount',
  'Total Incl VAT',
  'VAT Rate',
  'VAT Type'
]);

foreach ($rows as $row) {
    fputcsv($fp, [
        $row['invoice_number'],
        $row['customer_name'],
        $row['invoice_date'],
        $row['description'],
        number_format($row['amount_excl_vat'], 2, '.', ''),
        number_format($row['vat_amount'], 2, '.', ''),
        number_format($row['amount_incl_vat'], 2, '.', ''),
        $row['vat_rate'],
        'Standard'
    ]);
}

fclose($fp);

/* Mark invoices as exported */
$invoiceIds = array_unique(array_column($rows, 'invoice_id'));
$in = implode(',', array_map('intval', $invoiceIds));

$db->exec("
  UPDATE invoices
  SET exported_to_sage = 1,
      sage_exported_at = NOW()
  WHERE id IN ($in)
");

echo "Export created: $filename\n";
