<?php
// One-step fuel import processor.
// Upload a raw fuel CSV and download a QuickBooks-ready CSV.

const DEFAULT_REMOVE_COLUMNS = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'L', 'M', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X'];

$productMap = [
    '01' => 'Unleaded',
    '1' => 'Unleaded',
    '02' => 'Unleaded Plus',
    '2' => 'Unleaded Plus',
    '06' => 'Road Diesel',
    '6' => 'Road Diesel',
    '07' => 'Farm Diesel',
    '7' => 'Farm Diesel',
];

function load_account_map(string $path): array
{
    $map = [];
    if (!file_exists($path)) {
        return $map;
    }

    $handle = fopen($path, 'r');
    if ($handle === false) {
        return $map;
    }

    while (($data = fgetcsv($handle, null, ',', '"', '\\')) !== false) {
        if (count($data) >= 2) {
            $map[trim((string) $data[0])] = trim((string) $data[1]);
        }
    }

    fclose($handle);
    return $map;
}

function clean_header(string $value): string
{
    return trim(preg_replace('/^\xEF\xBB\xBF/', '', $value));
}

function find_column(array $headers, array $names): int|false
{
    $lookup = [];
    foreach ($headers as $index => $header) {
        $lookup[strtolower(clean_header((string) $header))] = $index;
    }

    foreach ($names as $name) {
        $key = strtolower($name);
        if (array_key_exists($key, $lookup)) {
            return $lookup[$key];
        }
    }

    return false;
}

function column_letter_to_index(string $letter): int
{
    $letter = strtoupper(trim($letter));
    $index = 0;

    for ($i = 0; $i < strlen($letter); $i++) {
        $index = ($index * 26) + (ord($letter[$i]) - ord('A') + 1);
    }

    return $index - 1;
}

function remove_columns(array $row, array $letters): array
{
    $remove = [];
    foreach ($letters as $letter) {
        $remove[column_letter_to_index($letter)] = true;
    }

    $kept = [];
    foreach ($row as $index => $value) {
        if (!isset($remove[$index])) {
            $kept[] = $value;
        }
    }

    return $kept;
}

function format_short_date(string $value): string
{
    $value = trim($value);
    if ($value === '') {
        return $value;
    }

    $formats = ['m/d/y', 'm/d/Y', 'n/j/y', 'n/j/Y', 'Y-m-d', 'm-d-y', 'm-d-Y'];
    foreach ($formats as $format) {
        $date = DateTime::createFromFormat('!' . $format, $value);
        if ($date instanceof DateTime && $date->format($format) === $value) {
            return $date->format('n/j/y');
        }
    }

    $timestamp = strtotime($value);
    if ($timestamp !== false) {
        return date('n/j/y', $timestamp);
    }

    return $value;
}

function map_product_name(string $prodId, array $productMap): string
{
    $prodId = trim($prodId);
    $trimmed = ltrim($prodId, '0');
    if ($trimmed === '') {
        $trimmed = '0';
    }

    if (isset($productMap[$prodId])) {
        return $productMap[$prodId];
    }

    if (isset($productMap[$trimmed])) {
        return $productMap[$trimmed];
    }

    return $prodId;
}

function combine_columns_c_a_d(array $row): array
{
    for ($i = count($row); $i < 4; $i++) {
        $row[$i] = '';
    }

    $colA = trim((string) ($row[0] ?? ''));
    $colC = trim((string) ($row[2] ?? ''));
    $colD = trim((string) ($row[3] ?? ''));

    $parts = [];
    if ($colC !== '') {
        $parts[] = $colC;
    }
    if ($colA !== '') {
        $parts[] = $colA;
    }
    if ($colD !== '') {
        $parts[] = $colD;
    }

    $row[2] = implode(', ', $parts);
    return $row;
}

function read_csv_rows(string $inputPath): array
{
    $rows = [];
    $input = fopen($inputPath, 'r');
    if ($input === false) {
        throw new RuntimeException('Unable to open the input file.');
    }

    while (($row = fgetcsv($input, null, ',', '"', '\\')) !== false) {
        $rows[] = $row;
    }

    fclose($input);
    return $rows;
}

function read_html_table_rows(string $inputPath): array
{
    $html = file_get_contents($inputPath);
    if ($html === false) {
        throw new RuntimeException('Unable to read the input file.');
    }

    libxml_use_internal_errors(true);
    $dom = new DOMDocument();
    $loaded = $dom->loadHTML($html);
    libxml_clear_errors();

    if (!$loaded) {
        throw new RuntimeException('Unable to read the old Excel/web page file.');
    }

    $rows = [];
    $xpath = new DOMXPath($dom);
    foreach ($xpath->query('//table//tr') as $tr) {
        $row = [];
        foreach ($xpath->query('./th|./td', $tr) as $cell) {
            $row[] = trim(preg_replace('/\s+/', ' ', html_entity_decode($cell->textContent, ENT_QUOTES | ENT_HTML5, 'UTF-8')));
        }

        if (count($row) > 0 && count(array_filter($row, fn($value) => trim((string) $value) !== '')) > 0) {
            $rows[] = $row;
        }
    }

    return $rows;
}

function read_input_rows(string $inputPath): array
{
    $sample = file_get_contents($inputPath, false, null, 0, 2048);
    if ($sample === false) {
        throw new RuntimeException('Unable to read the input file.');
    }

    if (preg_match('/<(html|table|tr|td|th)\b/i', $sample)) {
        return read_html_table_rows($inputPath);
    }

    return read_csv_rows($inputPath);
}

function write_csv_row($output, array $row): void
{
    fputcsv($output, $row, ',', '"', '\\', "\r\n");
}

function write_rows_to_csv(array $rows, string $csvPath): void
{
    $output = fopen($csvPath, 'w');
    if ($output === false) {
        throw new RuntimeException('Unable to create the temporary CSV file.');
    }

    foreach ($rows as $row) {
        write_csv_row($output, $row);
    }

    fclose($output);
}

function convert_input_to_real_csv(string $inputPath): string
{
    $rows = read_input_rows($inputPath);
    if (count($rows) === 0) {
        throw new RuntimeException('The input file appears to be empty.');
    }

    $csvPath = tempnam(sys_get_temp_dir(), 'fuel_real_csv_');
    if ($csvPath === false) {
        throw new RuntimeException('Unable to create the temporary CSV file.');
    }

    write_rows_to_csv($rows, $csvPath);
    return $csvPath;
}

function process_fuel_csv(string $inputPath, $outputHandle, array $productMap, array $acctMap): void
{
    $realCsvPath = convert_input_to_real_csv($inputPath);
    $rows = read_csv_rows($realCsvPath);
    @unlink($realCsvPath);

    $headers = array_shift($rows);
    if ($headers === null) {
        throw new RuntimeException('The input file appears to be empty.');
    }

    $headers = array_map(fn($header) => clean_header((string) $header), $headers);
    $singleDriverIndex = find_column($headers, ['Single/Driver', 'Single Driver', 'Driver', 'Card', 'CardId', 'Card ID']);
    $dateIndex = find_column($headers, ['Date', 'TranDate', 'Transaction Date']);
    $prodIdIndex = find_column($headers, ['ProdId', 'ProductId', 'Product ID']);
    $quantityIndex = find_column($headers, ['Quantity', 'Qty']);
    $priceIndex = find_column($headers, ['Price', 'Rate']);
    $accountIndex = find_column($headers, ['Account', 'Acct']);
    $acctNameIndex = find_column($headers, ['AcctName', 'Account Name', 'Customer', 'Name']);
    $driverNameIndex = find_column($headers, ['DrvrName', 'Driver Name', 'DriverName']);

    $requiredColumns = [
        'Single/Driver' => $singleDriverIndex,
        'Date' => $dateIndex,
        'ProdId' => $prodIdIndex,
        'Quantity' => $quantityIndex,
        'Price' => $priceIndex,
        'Account' => $accountIndex,
        'AcctName' => $acctNameIndex,
        'DrvrName' => $driverNameIndex,
    ];

    $missing = [];
    foreach ($requiredColumns as $name => $index) {
        if ($index === false) {
            $missing[] = $name;
        }
    }

    if (!empty($missing)) {
        throw new RuntimeException('Missing expected column(s): ' . implode(', ', $missing));
    }

    $outputHeaders = ['Single/Driver', 'Date', 'ProdId', 'Quantity', 'Price', 'Account', 'AcctName', 'DrvrName', 'Product'];
    write_csv_row($outputHandle, $outputHeaders);

    foreach ($rows as $row) {
        $singleDriver = trim((string) ($row[$singleDriverIndex] ?? ''));
        $date = format_short_date((string) ($row[$dateIndex] ?? ''));
        $quantity = trim((string) ($row[$quantityIndex] ?? ''));
        $price = trim((string) ($row[$priceIndex] ?? ''));
        $account = trim((string) ($row[$accountIndex] ?? ''));
        $acctName = trim((string) ($row[$acctNameIndex] ?? ''));
        $driverName = trim((string) ($row[$driverNameIndex] ?? ''));
        $productName = map_product_name((string) ($row[$prodIdIndex] ?? ''), $productMap);

        if ($account !== '' && isset($acctMap[$account])) {
            $acctName = $acctMap[$account];
        }

        $combinedProdId = implode(', ', array_filter([$productName, $singleDriver, $quantity], fn($value) => trim((string) $value) !== ''));

        $row = [
            $singleDriver,
            $date,
            $combinedProdId,
            $quantity,
            $price,
            $account,
            $acctName,
            $driverName,
            $productName,
        ];

        write_csv_row($outputHandle, $row);
    }
}

function safe_download_name(string $name): string
{
    $name = preg_replace('/[^A-Za-z0-9._-]/', '_', $name);
    $name = preg_replace('/\.[^.]*$/', '', $name);
    return 'fuel_import_ready_' . $name . '.csv';
}

function render_page(?string $error = null): void
{
    $errorHtml = $error ? '<p class="error">' . htmlspecialchars($error, ENT_QUOTES, 'UTF-8') . '</p>' : '';
    echo <<<HTML
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>One-Step Fuel Import Processor</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
  body{font-family:Arial,sans-serif;margin:50px auto;max-width:680px;color:#222}
  .panel{border:1px solid #d8d8d8;border-radius:8px;padding:24px}
  h1{font-size:24px;margin:0 0 12px}
  p{line-height:1.45}
  input,button{font-size:16px;margin:10px 0}
  button{padding:10px 16px;cursor:pointer}
  .error{color:#a40000;font-weight:bold}
  .small{color:#555;font-size:14px}
</style>
</head>
<body>
  <div class="panel">
    <h1>One-Step Fuel Import Processor</h1>
    {$errorHtml}
    <p>Upload the raw fuel file. It can be a normal CSV or the old Excel/web-page format. The file is first converted to a real CSV, then the script creates the same export layout as your finished example file.</p>
    <form method="post" enctype="multipart/form-data">
      <input type="file" name="fuel_file" accept=".csv,.txt,.xls,.html,.htm" required><br>
      <button type="submit">Process and Download</button>
    </form>
    <p class="small">Uses account_map.csv from this same folder.</p>
  </div>
</body>
</html>
HTML;
}

if (PHP_SAPI === 'cli') {
    if ($argc < 3) {
        fwrite(STDERR, "Usage: php process-fuel-import.php input.csv output.csv\n");
        exit(1);
    }

    $acctMap = load_account_map(__DIR__ . '/account_map.csv');
    $output = fopen($argv[2], 'w');
    if ($output === false) {
        fwrite(STDERR, "Unable to open output file.\n");
        exit(1);
    }

    process_fuel_csv($argv[1], $output, $productMap, $acctMap);
    fclose($output);
    echo "Created {$argv[2]}\n";
    exit;
}

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    try {
        if (!isset($_FILES['fuel_file']) || !is_uploaded_file($_FILES['fuel_file']['tmp_name'])) {
            throw new RuntimeException('No file was uploaded.');
        }

        $acctMap = load_account_map(__DIR__ . '/account_map.csv');
        $downloadName = safe_download_name(basename($_FILES['fuel_file']['name']));

        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="' . $downloadName . '"');
        header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');

        $output = fopen('php://output', 'w');
        process_fuel_csv($_FILES['fuel_file']['tmp_name'], $output, $productMap, $acctMap);
        fclose($output);
        exit;
    } catch (Throwable $e) {
        http_response_code(400);
        render_page($e->getMessage());
        exit;
    }
}

render_page();