<?php

declare(strict_types=1);

function sparrow_supplier_money($value): float
{
    if ($value === null || $value === '') {
        return 0.00;
    }

    return round((float) $value, 2);
}

function sparrow_supplier_clean_text($value): string
{
    return trim((string) ($value ?? ''));
}

function sparrow_supplier_normalize_order_number($value): string
{
    $value = strtoupper(trim((string) ($value ?? '')));

    if ($value === '') {
        return '';
    }

    // Allow "order 21", "Order #21", "order-21", etc.
    $value = preg_replace('/^ORDER\s*#?\s*/i', '', $value);

    if ($value === null) {
        return '';
    }

    $value = strtoupper(trim((string) $value));

    if ($value === '') {
        return '';
    }

    // Allow "SSC21", "SSC-21", or common typo "SCC-21"
    if (preg_match('/^(SSC|SCC)-?(\d+)$/', $value, $matches)) {
        return 'SSC-' . str_pad($matches[2], 3, '0', STR_PAD_LEFT);
    }

    // Allow plain "21" or "021"
    if (preg_match('/^\d+$/', $value)) {
        return 'SSC-' . str_pad($value, 3, '0', STR_PAD_LEFT);
    }

    // For non-order labels like Personal, Waste, Possible Use, etc.
    return $value;
}

function sparrow_supplier_get_receipts(): array
{
    $pdo = sparrow_db();

    $stmt = $pdo->query("
        SELECT
            r.*,
            COUNT(i.id) AS item_count,
            COALESCE(SUM(i.item_cost), 0) AS items_total
        FROM supplier_receipts r
        LEFT JOIN supplier_receipt_items i
            ON i.supplier_receipt_id = r.id
        GROUP BY r.id
        ORDER BY r.created_at DESC, r.id DESC
    ");

    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function sparrow_supplier_get_receipt_items(int $receipt_id): array
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        SELECT *
        FROM supplier_receipt_items
        WHERE supplier_receipt_id = :receipt_id
        ORDER BY id ASC
    ");

    $stmt->execute([
        ':receipt_id' => $receipt_id,
    ]);

    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function sparrow_supplier_get_receipt(int $receipt_id): ?array
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        SELECT *
        FROM supplier_receipts
        WHERE id = :id
        LIMIT 1
    ");

    $stmt->execute([
        ':id' => $receipt_id,
    ]);

    $receipt = $stmt->fetch(PDO::FETCH_ASSOC);

    return $receipt ?: null;
}

function sparrow_supplier_update_receipt(int $receipt_id, array $data, array $items): void
{
    $pdo = sparrow_db();
	
	$current_receipt = sparrow_supplier_get_receipt($receipt_id);

if (!$current_receipt) {
    throw new RuntimeException(
        'That supplier receipt could not be found.'
    );
}

if (($current_receipt['status'] ?? '') === 'applied') {
    throw new RuntimeException(
        'Applied supplier receipts cannot be edited. Reverse the application first.'
    );
}

    $supplier_name = sparrow_supplier_clean_text($data['supplier_name'] ?? '');
    $receipt_number = sparrow_supplier_clean_text($data['receipt_number'] ?? '');
    $order_date = sparrow_supplier_clean_text($data['order_date'] ?? '');
    $notes = sparrow_supplier_clean_text($data['notes'] ?? '');

    if ($supplier_name === '') {
        throw new RuntimeException('Supplier name is required.');
    }

    $subtotal = sparrow_supplier_money($data['subtotal'] ?? 0);
    $shipping_amount = sparrow_supplier_money($data['shipping_amount'] ?? 0);
    $tax_amount = sparrow_supplier_money($data['tax_amount'] ?? 0);
    $travel_amount = sparrow_supplier_money($data['travel_amount'] ?? 0);
    $discount_amount = sparrow_supplier_money($data['discount_amount'] ?? 0);

    $clean_items = [];

    foreach ($items as $item) {
        $description = sparrow_supplier_clean_text($item['description'] ?? '');

        if ($description === '') {
            continue;
        }

        $quantity = sparrow_supplier_money($item['quantity'] ?? 1);

        if ($quantity <= 0) {
            $quantity = 1;
        }

        $unit_cost = sparrow_supplier_money($item['unit_cost'] ?? 0);
        $item_cost = sparrow_supplier_money($item['item_cost'] ?? 0);

        if ($item_cost <= 0 && $unit_cost > 0) {
            $item_cost = round($quantity * $unit_cost, 2);
        }

        $clean_items[] = [
            'item_type' => sparrow_supplier_clean_text($item['item_type'] ?? 'Sparrow Order'),
            'goes_with_order_number' => sparrow_supplier_normalize_order_number($item['goes_with_order_number'] ?? ''),
            'description' => $description,
            'quantity' => $quantity,
            'unit_cost' => $unit_cost,
            'item_cost' => $item_cost,
            'accuracy' => sparrow_supplier_clean_text($item['accuracy'] ?? 'Exact'),
            'possible_order_use' => !empty($item['possible_order_use']) ? 1 : 0,
            'notes' => sparrow_supplier_clean_text($item['notes'] ?? ''),
        ];
    }

    $pdo->beginTransaction();

    try {
        $stmt = $pdo->prepare("
            UPDATE supplier_receipts
            SET
                supplier_name = :supplier_name,
                receipt_number = :receipt_number,
                order_date = :order_date,
                subtotal = :subtotal,
                shipping_amount = :shipping_amount,
                tax_amount = :tax_amount,
                travel_amount = :travel_amount,
                discount_amount = :discount_amount,
                notes = :notes,
                updated_at = NOW()
            WHERE id = :id
            LIMIT 1
        ");

        $stmt->execute([
            ':supplier_name' => $supplier_name,
            ':receipt_number' => $receipt_number !== '' ? $receipt_number : null,
            ':order_date' => $order_date !== '' ? $order_date : null,
            ':subtotal' => $subtotal,
            ':shipping_amount' => $shipping_amount,
            ':tax_amount' => $tax_amount,
            ':travel_amount' => $travel_amount,
            ':discount_amount' => $discount_amount,
            ':notes' => $notes !== '' ? $notes : null,
            ':id' => $receipt_id,
        ]);

        $delete_stmt = $pdo->prepare("
            DELETE FROM supplier_receipt_items
            WHERE supplier_receipt_id = :receipt_id
        ");

        $delete_stmt->execute([
            ':receipt_id' => $receipt_id,
        ]);

        if ($clean_items) {
            $item_stmt = $pdo->prepare("
                INSERT INTO supplier_receipt_items (
                    supplier_receipt_id,
                    item_type,
                    goes_with_order_number,
                    description,
                    quantity,
                    unit_cost,
                    item_cost,
                    accuracy,
                    possible_order_use,
                    notes
                ) VALUES (
                    :supplier_receipt_id,
                    :item_type,
                    :goes_with_order_number,
                    :description,
                    :quantity,
                    :unit_cost,
                    :item_cost,
                    :accuracy,
                    :possible_order_use,
                    :notes
                )
            ");

            foreach ($clean_items as $item) {
                $item_stmt->execute([
                    ':supplier_receipt_id' => $receipt_id,
                    ':item_type' => $item['item_type'],
                    ':goes_with_order_number' => $item['goes_with_order_number'] !== '' ? $item['goes_with_order_number'] : null,
                    ':description' => $item['description'],
                    ':quantity' => $item['quantity'],
                    ':unit_cost' => $item['unit_cost'],
                    ':item_cost' => $item['item_cost'],
                    ':accuracy' => $item['accuracy'],
                    ':possible_order_use' => $item['possible_order_use'],
                    ':notes' => $item['notes'] !== '' ? $item['notes'] : null,
                ]);
            }
        }

        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

function sparrow_supplier_create_receipt(array $data, array $items): int
{
    $pdo = sparrow_db();

    $supplier_name = sparrow_supplier_clean_text($data['supplier_name'] ?? '');
    $receipt_number = sparrow_supplier_clean_text($data['receipt_number'] ?? '');
    $order_date = sparrow_supplier_clean_text($data['order_date'] ?? '');
    $notes = sparrow_supplier_clean_text($data['notes'] ?? '');

    if ($supplier_name === '') {
        throw new RuntimeException('Supplier name is required.');
    }

    $subtotal = sparrow_supplier_money($data['subtotal'] ?? 0);
    $shipping_amount = sparrow_supplier_money($data['shipping_amount'] ?? 0);
    $tax_amount = sparrow_supplier_money($data['tax_amount'] ?? 0);
    $travel_amount = sparrow_supplier_money($data['travel_amount'] ?? 0);
    $discount_amount = sparrow_supplier_money($data['discount_amount'] ?? 0);

    $clean_items = [];

    foreach ($items as $item) {
        $description = sparrow_supplier_clean_text($item['description'] ?? '');

        if ($description === '') {
            continue;
        }

        $quantity = sparrow_supplier_money($item['quantity'] ?? 1);

        if ($quantity <= 0) {
            $quantity = 1;
        }

        $unit_cost = sparrow_supplier_money($item['unit_cost'] ?? 0);
        $item_cost = sparrow_supplier_money($item['item_cost'] ?? 0);

        if ($item_cost <= 0 && $unit_cost > 0) {
            $item_cost = round($quantity * $unit_cost, 2);
        }

        $clean_items[] = [
            'item_type' => sparrow_supplier_clean_text($item['item_type'] ?? 'Sparrow Order'),
            'goes_with_order_number' => sparrow_supplier_normalize_order_number($item['goes_with_order_number'] ?? ''),
            'description' => $description,
            'quantity' => $quantity,
            'unit_cost' => $unit_cost,
            'item_cost' => $item_cost,
            'accuracy' => sparrow_supplier_clean_text($item['accuracy'] ?? 'Exact'),
            'possible_order_use' => !empty($item['possible_order_use']) ? 1 : 0,
            'notes' => sparrow_supplier_clean_text($item['notes'] ?? ''),
        ];
    }

    $pdo->beginTransaction();

    try {
        $stmt = $pdo->prepare("
            INSERT INTO supplier_receipts (
                supplier_name,
                receipt_number,
                order_date,
                subtotal,
                shipping_amount,
                tax_amount,
                travel_amount,
                discount_amount,
                notes,
                status
            ) VALUES (
                :supplier_name,
                :receipt_number,
                :order_date,
                :subtotal,
                :shipping_amount,
                :tax_amount,
                :travel_amount,
                :discount_amount,
                :notes,
                'draft'
            )
        ");

        $stmt->execute([
            ':supplier_name' => $supplier_name,
            ':receipt_number' => $receipt_number !== '' ? $receipt_number : null,
            ':order_date' => $order_date !== '' ? $order_date : null,
            ':subtotal' => $subtotal,
            ':shipping_amount' => $shipping_amount,
            ':tax_amount' => $tax_amount,
            ':travel_amount' => $travel_amount,
            ':discount_amount' => $discount_amount,
            ':notes' => $notes !== '' ? $notes : null,
        ]);

        $receipt_id = (int) $pdo->lastInsertId();

        if ($clean_items) {
            $item_stmt = $pdo->prepare("
                INSERT INTO supplier_receipt_items (
                    supplier_receipt_id,
                    item_type,
                    goes_with_order_number,
                    description,
                    quantity,
                    unit_cost,
                    item_cost,
                    accuracy,
                    possible_order_use,
                    notes
                ) VALUES (
                    :supplier_receipt_id,
                    :item_type,
                    :goes_with_order_number,
                    :description,
                    :quantity,
                    :unit_cost,
                    :item_cost,
                    :accuracy,
                    :possible_order_use,
                    :notes
                )
            ");

            foreach ($clean_items as $item) {
                $item_stmt->execute([
                    ':supplier_receipt_id' => $receipt_id,
                    ':item_type' => $item['item_type'],
                    ':goes_with_order_number' => $item['goes_with_order_number'] !== '' ? $item['goes_with_order_number'] : null,
                    ':description' => $item['description'],
                    ':quantity' => $item['quantity'],
                    ':unit_cost' => $item['unit_cost'],
                    ':item_cost' => $item['item_cost'],
                    ':accuracy' => $item['accuracy'],
                    ':possible_order_use' => $item['possible_order_use'],
                    ':notes' => $item['notes'] !== '' ? $item['notes'] : null,
                ]);
            }
        }

        $pdo->commit();

        return $receipt_id;
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

function sparrow_supplier_allowed_statuses(): array
{
    return [
        'draft' => 'Draft',
        'reviewed' => 'Reviewed',
        'ready_to_apply' => 'Ready to Apply',
    ];
}

function sparrow_supplier_status_label(string $status): string
{
    if ($status === 'applied') {
        return 'Applied';
    }

    $statuses = sparrow_supplier_allowed_statuses();

    return $statuses[$status] ?? 'Draft';
}

function sparrow_supplier_update_status(int $receipt_id, string $status): void
{
    $allowed_statuses = sparrow_supplier_allowed_statuses();

    if (!array_key_exists($status, $allowed_statuses)) {
        throw new RuntimeException('That supplier status is not allowed.');
    }

    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        UPDATE supplier_receipts
        SET
            status = :status,
            updated_at = NOW()
        WHERE id = :id
        LIMIT 1
    ");
	
	$current_receipt = sparrow_supplier_get_receipt($receipt_id);

if (!$current_receipt) {
    throw new RuntimeException(
        'That supplier receipt could not be found.'
    );
}

if (($current_receipt['status'] ?? '') === 'applied') {
    throw new RuntimeException(
        'Applied supplier receipts are locked. Reverse the application first.'
    );
}

    $stmt->execute([
        ':status' => $status,
        ':id' => $receipt_id,
    ]);
}

function sparrow_supplier_get_ready_apply_preview(): array
{
    $pdo = sparrow_db();

    $stmt = $pdo->query("
        SELECT *
        FROM supplier_receipts
        WHERE status = 'ready_to_apply'
        ORDER BY order_date DESC, id DESC
    ");

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

    $preview_rows = [];

    foreach ($receipts as $receipt) {
        $receipt_id = (int) $receipt['id'];
        $items = sparrow_supplier_get_receipt_items($receipt_id);

        $all_item_total = 0.00;
        $order_groups = [];

        foreach ($items as $item) {
            $item_cost = (float) ($item['item_cost'] ?? 0);
            $all_item_total += $item_cost;

            $item_type = (string) ($item['item_type'] ?? '');
            $order_number = strtoupper(trim((string) ($item['goes_with_order_number'] ?? '')));

            if ($item_type !== 'Sparrow Order' || $order_number === '') {
                continue;
            }

            if (!isset($order_groups[$order_number])) {
                $order_groups[$order_number] = [
                    'order_number' => $order_number,
                    'item_total' => 0.00,
                    'item_count' => 0,
                    'items' => [],
                ];
            }

            $order_groups[$order_number]['item_total'] += $item_cost;
            $order_groups[$order_number]['item_count']++;

            $order_groups[$order_number]['items'][] = [
                'description' => $item['description'] ?? '',
                'quantity' => (float) ($item['quantity'] ?? 0),
                'unit_cost' => (float) ($item['unit_cost'] ?? 0),
                'item_cost' => $item_cost,
                'accuracy' => $item['accuracy'] ?? '',
                'notes' => $item['notes'] ?? '',
            ];
        }

        if ($all_item_total <= 0 || empty($order_groups)) {
            continue;
        }

        $shipping_amount = (float) ($receipt['shipping_amount'] ?? 0);
        $tax_amount = (float) ($receipt['tax_amount'] ?? 0);
        $travel_amount = (float) ($receipt['travel_amount'] ?? 0);
        $discount_amount = (float) ($receipt['discount_amount'] ?? 0);

        foreach ($order_groups as $order_group) {
            $share = $order_group['item_total'] / $all_item_total;

            $shipping_share = round($shipping_amount * $share, 2);
            $tax_share = round($tax_amount * $share, 2);
            $travel_share = round($travel_amount * $share, 2);
            $discount_share = round($discount_amount * $share, 2);

            $total_internal_cost = round(
                $order_group['item_total']
                + $shipping_share
                + $tax_share
                + $travel_share
                - $discount_share,
                2
            );

            $order_match = sparrow_supplier_find_order_by_number($order_group['order_number']);
$application_status = sparrow_supplier_get_application_status($receipt_id, $order_group['order_number']);

$preview_rows[] = [
    'receipt_id' => $receipt_id,
    'supplier_name' => $receipt['supplier_name'] ?? '',
    'receipt_number' => $receipt['receipt_number'] ?? '',
    'order_date' => $receipt['order_date'] ?? '',
    'order_number' => $order_group['order_number'],
    'order_id' => $order_match['id'] ?? null,
    'order_found' => $order_match ? true : false,
    'customer_name' => $order_match['customer_name'] ?? '',
    'order_status' => $order_match['order_status'] ?? ($order_match['status'] ?? ''),
    'already_applied' => $application_status ? true : false,
    'applied_at' => $application_status['applied_at'] ?? '',
    'application_id' => $application_status['id'] ?? null,
    'item_count' => $order_group['item_count'],
    'item_total' => round($order_group['item_total'], 2),
    'shipping_share' => $shipping_share,
    'tax_share' => $tax_share,
    'travel_share' => $travel_share,
    'discount_share' => $discount_share,
    'total_internal_cost' => $total_internal_cost,
    'items' => $order_group['items'],
];
        }
    }

    return $preview_rows;
}

function sparrow_supplier_find_order_by_number(string $order_number): ?array
{
    $pdo = sparrow_db();

    $order_number = sparrow_supplier_normalize_order_number($order_number);

    if ($order_number === '') {
        return null;
    }

    $columns_stmt = $pdo->query("SHOW COLUMNS FROM orders");
    $columns = $columns_stmt->fetchAll(PDO::FETCH_COLUMN);

    if (!$columns || !in_array('order_number', $columns, true)) {
        return null;
    }

    $select_columns = ['id', 'order_number'];

    foreach (['customer_name', 'customer_phone', 'order_status', 'status'] as $possible_column) {
        if (in_array($possible_column, $columns, true)) {
            $select_columns[] = $possible_column;
        }
    }

    $sql = "
        SELECT " . implode(', ', $select_columns) . "
        FROM orders
        WHERE order_number = :order_number
        LIMIT 1
    ";

    $stmt = $pdo->prepare($sql);

    $stmt->execute([
        ':order_number' => $order_number,
    ]);

    $order = $stmt->fetch(PDO::FETCH_ASSOC);

    return $order ?: null;
}

function sparrow_supplier_get_tracked_non_order_items(): array
{
    $pdo = sparrow_db();

    $stmt = $pdo->query("
        SELECT
            i.*,
            r.supplier_name,
            r.receipt_number,
            r.order_date,
            r.status AS receipt_status
        FROM supplier_receipt_items i
        INNER JOIN supplier_receipts r
            ON r.id = i.supplier_receipt_id
        WHERE i.item_type <> 'Sparrow Order'
        ORDER BY r.order_date DESC, r.id DESC, i.id ASC
    ");

    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function sparrow_supplier_delete_receipt(int $receipt_id): void
{
    if ($receipt_id <= 0) {
        throw new RuntimeException('Missing supplier receipt draft ID.');
    }

    $pdo = sparrow_db();
	
	$current_receipt = sparrow_supplier_get_receipt($receipt_id);

if (!$current_receipt) {
    throw new RuntimeException(
        'That supplier receipt could not be found.'
    );
}

if (($current_receipt['status'] ?? '') === 'applied') {
    throw new RuntimeException(
        'Applied supplier receipts cannot be deleted. Reverse the application first.'
    );
}

    $stmt = $pdo->prepare("
        DELETE FROM supplier_receipts
        WHERE id = :id
        LIMIT 1
    ");

    $stmt->execute([
        ':id' => $receipt_id,
    ]);
}

function sparrow_supplier_get_application_status(int $receipt_id, string $order_number): ?array
{
    $pdo = sparrow_db();

    $order_number = sparrow_supplier_normalize_order_number($order_number);

    if ($receipt_id <= 0 || $order_number === '') {
        return null;
    }

    $stmt = $pdo->prepare("
        SELECT *
        FROM supplier_receipt_applications
        WHERE supplier_receipt_id = :receipt_id
          AND order_number = :order_number
          AND status = 'applied'
        LIMIT 1
    ");

    $stmt->execute([
        ':receipt_id' => $receipt_id,
        ':order_number' => $order_number,
    ]);

    $application = $stmt->fetch(PDO::FETCH_ASSOC);

    return $application ?: null;
}

function sparrow_supplier_get_applied_history(): array
{
    $pdo = sparrow_db();

    $columns_stmt = $pdo->query("
        SHOW COLUMNS FROM supplier_receipt_applications
    ");

    $columns = $columns_stmt->fetchAll(PDO::FETCH_COLUMN);

    if (!$columns) {
        return [];
    }

    $select_parts = [
        'a.id AS application_id',
        'a.supplier_receipt_id',
        'a.order_number',
        'a.status',
        'r.supplier_name',
        'r.receipt_number',
        'r.order_date',
        'o.id AS matched_order_id',
        'o.customer_name',
    ];

$optional_columns = [
    'order_id',
    'total_internal_cost',
    'applied_at',
    'created_at',
    'reversed_at',
    'reversal_reason',
];

foreach ($optional_columns as $column) {
    if (in_array($column, $columns, true)) {
        $select_parts[] = 'a.' . $column;
    }
}

if (in_array('item_cost_amount', $columns, true)) {
    $select_parts[] = 'a.item_cost_amount AS item_total';
}

if (in_array('shipping_amount', $columns, true)) {
    $select_parts[] = 'a.shipping_amount AS shipping_share';
}

if (in_array('tax_amount', $columns, true)) {
    $select_parts[] = 'a.tax_amount AS tax_share';
}

if (in_array('travel_amount', $columns, true)) {
    $select_parts[] = 'a.travel_amount AS travel_share';
}

if (in_array('discount_amount', $columns, true)) {
    $select_parts[] = 'a.discount_amount AS discount_share';
}

    $sql = "
        SELECT
            " . implode(",\n            ", $select_parts) . "
        FROM supplier_receipt_applications a
        INNER JOIN supplier_receipts r
            ON r.id = a.supplier_receipt_id
        LEFT JOIN orders o
            ON o.order_number = a.order_number
        WHERE a.status IN ('applied', 'reversed')
        ORDER BY a.id DESC
    ";

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

    foreach ($rows as &$row) {
        $row['item_total'] = sparrow_supplier_money(
            $row['item_total'] ?? 0
        );

        $row['shipping_share'] = sparrow_supplier_money(
            $row['shipping_share'] ?? 0
        );

        $row['tax_share'] = sparrow_supplier_money(
            $row['tax_share'] ?? 0
        );

        $row['travel_share'] = sparrow_supplier_money(
            $row['travel_share'] ?? 0
        );

        $row['discount_share'] = sparrow_supplier_money(
            $row['discount_share'] ?? 0
        );

        $row['total_internal_cost'] = sparrow_supplier_money(
            $row['total_internal_cost'] ?? (
                $row['item_total']
                + $row['shipping_share']
                + $row['tax_share']
                + $row['travel_share']
                - $row['discount_share']
            )
        );

        $row['applied_date'] =
            $row['applied_at']
            ?? $row['created_at']
            ?? '';
    }

    unset($row);

    return $rows;
}

function sparrow_supplier_apply_ready_receipts(): array
{
    $pdo = sparrow_db();

    /*
     * Build the current preview before starting.
     * Nothing is written during this step.
     */
    $preview_rows = sparrow_supplier_get_ready_apply_preview();

    if (!$preview_rows) {
        throw new RuntimeException(
            'There are no Ready to Apply supplier rows.'
        );
    }

    foreach ($preview_rows as $preview_row) {
        if (empty($preview_row['order_found'])) {
            throw new RuntimeException(
                'One or more supplier rows do not match an order.'
            );
        }

        if (!empty($preview_row['already_applied'])) {
            throw new RuntimeException(
                'One or more supplier rows have already been applied.'
            );
        }

        if ((int) ($preview_row['order_id'] ?? 0) <= 0) {
            throw new RuntimeException(
                'A matched order is missing its database ID.'
            );
        }
    }

    $application_columns_stmt = $pdo->query("
        SHOW COLUMNS FROM supplier_receipt_applications
    ");

    $application_columns = $application_columns_stmt->fetchAll(
        PDO::FETCH_COLUMN
    );

    $required_application_columns = [
        'supplier_receipt_id',
        'order_number',
        'status',
    ];

    foreach ($required_application_columns as $required_column) {
        if (!in_array(
            $required_column,
            $application_columns,
            true
        )) {
            throw new RuntimeException(
                'The supplier application table is missing the '
                . $required_column
                . ' column.'
            );
        }
    }

    $pdo->beginTransaction();

    try {
        $applied_count = 0;
        $applied_total = 0.00;
        $affected_orders = [];

        foreach ($preview_rows as $preview_row) {
            $receipt_id = (int) $preview_row['receipt_id'];
            $order_id = (int) $preview_row['order_id'];
            $order_number = sparrow_supplier_normalize_order_number(
                $preview_row['order_number'] ?? ''
            );

            /*
             * Lock and re-check the receipt.
             */
            $receipt_stmt = $pdo->prepare("
                SELECT id, status
                FROM supplier_receipts
                WHERE id = :id
                LIMIT 1
                FOR UPDATE
            ");

            $receipt_stmt->execute([
                ':id' => $receipt_id,
            ]);

            $receipt = $receipt_stmt->fetch(PDO::FETCH_ASSOC);

            if (!$receipt) {
                throw new RuntimeException(
                    'A supplier receipt could not be found.'
                );
            }

            if (($receipt['status'] ?? '') !== 'ready_to_apply') {
                throw new RuntimeException(
                    'A supplier receipt is no longer Ready to Apply.'
                );
            }

            /*
             * Lock and re-check the matching order.
             */
            $order_stmt = $pdo->prepare("
                SELECT
                    id,
                    order_number,
                    supplier_item_cost,
                    shipping_cost,
                    tax_cost,
                    other_cost,
                    discount_cost
                FROM orders
                WHERE id = :id
                  AND order_number = :order_number
                LIMIT 1
                FOR UPDATE
            ");

            $order_stmt->execute([
                ':id' => $order_id,
                ':order_number' => $order_number,
            ]);

            $order = $order_stmt->fetch(PDO::FETCH_ASSOC);

            if (!$order) {
                throw new RuntimeException(
                    'A supplier row no longer matches its order.'
                );
            }

            /*
             * Duplicate protection inside the transaction.
             */
            $duplicate_stmt = $pdo->prepare("
                SELECT id
                FROM supplier_receipt_applications
                WHERE supplier_receipt_id = :receipt_id
                  AND order_number = :order_number
                  AND status = 'applied'
                LIMIT 1
                FOR UPDATE
            ");

            $duplicate_stmt->execute([
                ':receipt_id' => $receipt_id,
                ':order_number' => $order_number,
            ]);

            if ($duplicate_stmt->fetch(PDO::FETCH_ASSOC)) {
                throw new RuntimeException(
                    'Supplier costs were already applied to '
                    . $order_number
                    . '.'
                );
            }

$item_total = sparrow_supplier_money(
    $preview_row['item_total'] ?? 0
);

$shipping_share = sparrow_supplier_money(
    $preview_row['shipping_share'] ?? 0
);

$tax_share = sparrow_supplier_money(
    $preview_row['tax_share'] ?? 0
);

$travel_share = sparrow_supplier_money(
    $preview_row['travel_share'] ?? 0
);

$discount_share = sparrow_supplier_money(
    $preview_row['discount_share'] ?? 0
);

            $total_internal_cost = sparrow_supplier_money(
                $preview_row['total_internal_cost'] ?? 0
            );

            /*
             * Add this receipt's shares to existing order costs.
             * Never replace costs already saved from another receipt.
             */
            $update_order_stmt = $pdo->prepare("
                UPDATE orders
                SET
                    supplier_item_cost =
                        COALESCE(supplier_item_cost, 0)
                        + :item_total,

                    shipping_cost =
                        COALESCE(shipping_cost, 0)
                        + :shipping_share,

                    tax_cost =
                        COALESCE(tax_cost, 0)
                        + :tax_share,

                    other_cost =
                        COALESCE(other_cost, 0)
                        + :travel_share,

                    discount_cost =
                        COALESCE(discount_cost, 0)
                        + :discount_share

                WHERE id = :order_id
                LIMIT 1
            ");

            $update_order_stmt->execute([
                ':item_total' => $item_total,
                ':shipping_share' => $shipping_share,
                ':tax_share' => $tax_share,
                ':travel_share' => $travel_share,
                ':discount_share' => $discount_share,
                ':order_id' => $order_id,
            ]);

            /*
             * Build the application record using only columns that
             * actually exist in the current database table.
             */
            $application_data = [
                'supplier_receipt_id' => $receipt_id,
                'order_number' => $order_number,
                'status' => 'applied',
            ];

            $optional_application_data = [
    'order_id' => $order_id,
    'item_cost_amount' => $item_total,
    'shipping_amount' => $shipping_share,
    'tax_amount' => $tax_share,
    'travel_amount' => $travel_share,
    'discount_amount' => $discount_share,
    'total_internal_cost' => $total_internal_cost,
];

            foreach (
                $optional_application_data as $column => $value
            ) {
                if (in_array(
                    $column,
                    $application_columns,
                    true
                )) {
                    $application_data[$column] = $value;
                }
            }

            $insert_columns = array_keys($application_data);
            $insert_placeholders = [];

            foreach ($insert_columns as $column) {
                $insert_placeholders[] = ':' . $column;
            }

            $insert_sql = "
                INSERT INTO supplier_receipt_applications (
                    " . implode(', ', $insert_columns) . "
                ) VALUES (
                    " . implode(', ', $insert_placeholders) . "
                )
            ";

            $insert_application_stmt = $pdo->prepare($insert_sql);

            $insert_parameters = [];

            foreach ($application_data as $column => $value) {
                $insert_parameters[':' . $column] = $value;
            }

            $insert_application_stmt->execute(
                $insert_parameters
            );

            $applied_count++;
            $applied_total += $total_internal_cost;
            $affected_orders[$order_number] = true;
        }

        /*
         * Mark receipts as applied only after every row succeeded.
         */
        $receipt_ids = [];

        foreach ($preview_rows as $preview_row) {
            $receipt_ids[] = (int) $preview_row['receipt_id'];
        }

        $receipt_ids = array_values(array_unique($receipt_ids));

        foreach ($receipt_ids as $receipt_id) {
            $mark_receipt_stmt = $pdo->prepare("
                UPDATE supplier_receipts
                SET
                    status = 'applied',
                    updated_at = NOW()
                WHERE id = :id
                  AND status = 'ready_to_apply'
                LIMIT 1
            ");

            $mark_receipt_stmt->execute([
                ':id' => $receipt_id,
            ]);
        }

        $pdo->commit();

        return [
            'applied_count' => $applied_count,
            'order_count' => count($affected_orders),
            'applied_total' => round($applied_total, 2),
        ];
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }

        throw $e;
    }
}

function sparrow_supplier_reverse_application(
    int $application_id,
    string $reason = ''
): array {
    if ($application_id <= 0) {
        throw new RuntimeException(
            'Missing supplier application ID.'
        );
    }

    $reason = trim($reason);

    if ($reason === '') {
        throw new RuntimeException(
            'A reversal reason is required.'
        );
    }

    $pdo = sparrow_db();
    $pdo->beginTransaction();

    try {
        /*
         * Lock the saved application record.
         * The saved amounts are the source of truth.
         */
        $application_stmt = $pdo->prepare("
            SELECT *
            FROM supplier_receipt_applications
            WHERE id = :id
            LIMIT 1
            FOR UPDATE
        ");

        $application_stmt->execute([
            ':id' => $application_id,
        ]);

        $application = $application_stmt->fetch(
            PDO::FETCH_ASSOC
        );

        if (!$application) {
            throw new RuntimeException(
                'That supplier application could not be found.'
            );
        }

        if (($application['status'] ?? '') !== 'applied') {
            throw new RuntimeException(
                'That supplier application is not currently applied.'
            );
        }

        $receipt_id = (int) (
            $application['supplier_receipt_id'] ?? 0
        );

        $order_number =
            sparrow_supplier_normalize_order_number(
                $application['order_number'] ?? ''
            );

        $order_id = (int) (
            $application['order_id'] ?? 0
        );

        if ($order_id <= 0 && $order_number !== '') {
            $order_match =
                sparrow_supplier_find_order_by_number(
                    $order_number
                );

            $order_id = (int) (
                $order_match['id'] ?? 0
            );
        }

        if ($order_id <= 0) {
            throw new RuntimeException(
                'The matching order could not be found.'
            );
        }

$item_total = sparrow_supplier_money(
    $application['item_cost_amount'] ?? $application['item_total'] ?? 0
);

$shipping_share = sparrow_supplier_money(
    $application['shipping_amount'] ?? $application['shipping_share'] ?? 0
);

$tax_share = sparrow_supplier_money(
    $application['tax_amount'] ?? $application['tax_share'] ?? 0
);

$travel_share = sparrow_supplier_money(
    $application['travel_amount'] ?? $application['travel_share'] ?? 0
);

$discount_share = sparrow_supplier_money(
    $application['discount_amount'] ?? $application['discount_share'] ?? 0
);

        /*
         * Lock the order and confirm every cost bucket still
         * contains at least the amount we intend to remove.
         */
        $order_stmt = $pdo->prepare("
            SELECT
                id,
                order_number,
                supplier_item_cost,
                shipping_cost,
                tax_cost,
                other_cost,
                discount_cost
            FROM orders
            WHERE id = :id
            LIMIT 1
            FOR UPDATE
        ");

        $order_stmt->execute([
            ':id' => $order_id,
        ]);

        $order = $order_stmt->fetch(PDO::FETCH_ASSOC);

        if (!$order) {
            throw new RuntimeException(
                'The matching order could not be found.'
            );
        }

        $checks = [
            'Supplier Items' => [
                (float) ($order['supplier_item_cost'] ?? 0),
                $item_total,
            ],
            'Shipping' => [
                (float) ($order['shipping_cost'] ?? 0),
                $shipping_share,
            ],
            'Tax' => [
                (float) ($order['tax_cost'] ?? 0),
                $tax_share,
            ],
            'Travel / Other' => [
                (float) ($order['other_cost'] ?? 0),
                $travel_share,
            ],
            'Supplier Discount' => [
                (float) ($order['discount_cost'] ?? 0),
                $discount_share,
            ],
        ];

        foreach ($checks as $label => $amounts) {
            [$current_amount, $remove_amount] = $amounts;

            if (
                round($current_amount, 2)
                < round($remove_amount, 2)
            ) {
                throw new RuntimeException(
                    $label
                    . ' is lower than the saved amount being reversed. '
                    . 'The order may have been edited manually.'
                );
            }
        }

        /*
         * Subtract only the exact amounts stored when Apply ran.
         */
        $update_order_stmt = $pdo->prepare("
            UPDATE orders
            SET
                supplier_item_cost = GREATEST(
                    0,
                    COALESCE(supplier_item_cost, 0)
                    - :item_total
                ),
                shipping_cost = GREATEST(
                    0,
                    COALESCE(shipping_cost, 0)
                    - :shipping_share
                ),
                tax_cost = GREATEST(
                    0,
                    COALESCE(tax_cost, 0)
                    - :tax_share
                ),
                other_cost = GREATEST(
                    0,
                    COALESCE(other_cost, 0)
                    - :travel_share
                ),
                discount_cost = GREATEST(
                    0,
                    COALESCE(discount_cost, 0)
                    - :discount_share
                )
            WHERE id = :order_id
            LIMIT 1
        ");

        $update_order_stmt->execute([
            ':item_total' => $item_total,
            ':shipping_share' => $shipping_share,
            ':tax_share' => $tax_share,
            ':travel_share' => $travel_share,
            ':discount_share' => $discount_share,
            ':order_id' => $order_id,
        ]);

        /*
         * Keep the history row, but mark it reversed.
         */
        $reverse_stmt = $pdo->prepare("
            UPDATE supplier_receipt_applications
            SET
                status = 'reversed',
                reversed_at = NOW(),
                reversal_reason = :reversal_reason
            WHERE id = :id
              AND status = 'applied'
            LIMIT 1
        ");

        $reverse_stmt->execute([
            ':reversal_reason' => $reason,
            ':id' => $application_id,
        ]);

        if ($reverse_stmt->rowCount() !== 1) {
            throw new RuntimeException(
                'The application could not be marked reversed.'
            );
        }

        /*
         * Return the receipt to Reviewed rather than immediately
         * making it eligible to apply again.
         */
        $remaining_stmt = $pdo->prepare("
    SELECT COUNT(*)
    FROM supplier_receipt_applications
    WHERE supplier_receipt_id = :receipt_id
      AND status = 'applied'
");

$remaining_stmt->execute([
    ':receipt_id' => $receipt_id,
]);

$remaining_applied = (int) $remaining_stmt->fetchColumn();

if ($remaining_applied === 0) {
    $receipt_stmt = $pdo->prepare("
        UPDATE supplier_receipts
        SET
            status = 'reviewed',
            updated_at = NOW()
        WHERE id = :id
        LIMIT 1
    ");

    $receipt_stmt->execute([
        ':id' => $receipt_id,
    ]);
}

        $pdo->commit();

        return [
            'application_id' => $application_id,
            'receipt_id' => $receipt_id,
            'order_id' => $order_id,
            'order_number' => $order_number,
            'reversed_total' => sparrow_supplier_money(
                $item_total
                + $shipping_share
                + $tax_share
                + $travel_share
                - $discount_share
            ),
        ];
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }

        throw $e;
    }
}

function sparrow_supplier_import_wordpress_export(
    array $data
): array {
    $pdo = sparrow_db();

    $drafts = $data['drafts'] ?? [];

    if (!is_array($drafts)) {
        throw new RuntimeException(
            'The WordPress export does not contain valid receipts.'
        );
    }

    $result = [
        'receipts_imported' => 0,
        'receipts_skipped' => 0,
        'items_imported' => 0,
        'applications_imported' => 0,
        'applications_skipped' => 0,
        'orders_updated' => 0,
        'missing_orders' => [],
    ];

    foreach ($drafts as $draft) {
        if (!is_array($draft)) {
            continue;
        }

        $supplier_name_raw = strtolower(
            trim((string) ($draft['supplier_name'] ?? ''))
        );

        $supplier_map = [
            'jiffy' => 'Jiffy',
            'shirtspace' => 'ShirtSpace',
            'texas vinyl supply' => 'Texas Vinyl Supply',
            'amazon' => 'Amazon',
            'other' => 'Other',
        ];

        $supplier_name =
            $supplier_map[$supplier_name_raw]
            ?? (
                trim((string) ($draft['supplier_name'] ?? ''))
                ?: 'Other'
            );

        $receipt_number = trim(
            (string) ($draft['receipt_number'] ?? '')
        );

        /*
         * Duplicate protection.
         */
$duplicate_stmt = $pdo->prepare("
    SELECT id
    FROM supplier_receipts
    WHERE supplier_name = :supplier_name
      AND (
            receipt_number = :receipt_number
            OR (
                receipt_number IS NULL
                AND :empty_receipt_number = ''
            )
          )
    LIMIT 1
");

$duplicate_stmt->execute([
    ':supplier_name' => $supplier_name,
    ':receipt_number' => $receipt_number,
    ':empty_receipt_number' => $receipt_number,
]);

        if ($duplicate_stmt->fetchColumn()) {
            $result['receipts_skipped']++;
            continue;
        }

        $items = is_array($draft['items'] ?? null)
            ? $draft['items']
            : [];

        $applied_groups =
            is_array($draft['applied_groups'] ?? null)
                ? $draft['applied_groups']
                : [];

        $sparrow_order_labels = [];

        foreach ($items as $item) {
            if (!is_array($item)) {
                continue;
            }

            if (
                ($item['type'] ?? '') === 'sparrow_order'
                && trim((string) ($item['label'] ?? '')) !== ''
            ) {
                $sparrow_order_labels[
                    sparrow_supplier_normalize_order_number(
                        $item['label']
                    )
                ] = true;
            }
        }

        $all_groups_applied = true;

        foreach (
            array_keys($sparrow_order_labels)
            as $order_label
        ) {
            if (!array_key_exists(
                $order_label,
                $applied_groups
            )) {
                $all_groups_applied = false;
                break;
            }
        }

        $receipt_status = (
            $applied_groups
            && $all_groups_applied
        )
            ? 'applied'
            : 'reviewed';

        $pdo->beginTransaction();

        try {
            $receipt_stmt = $pdo->prepare("
                INSERT INTO supplier_receipts (
                    supplier_name,
                    receipt_number,
                    order_date,
                    subtotal,
                    shipping_amount,
                    tax_amount,
                    travel_amount,
                    discount_amount,
                    notes,
                    status,
                    created_at,
                    updated_at
                ) VALUES (
                    :supplier_name,
                    :receipt_number,
                    :order_date,
                    :subtotal,
                    :shipping_amount,
                    :tax_amount,
                    0.00,
                    :discount_amount,
                    :notes,
                    :status,
                    :created_at,
                    :updated_at
                )
            ");

            $created_at = trim(
                (string) ($draft['created_at'] ?? '')
            );

            $updated_at = trim(
                (string) ($draft['updated_at'] ?? '')
            );

            $receipt_stmt->execute([
                ':supplier_name' => $supplier_name,
                ':receipt_number' =>
                    $receipt_number !== ''
                        ? $receipt_number
                        : null,
                ':order_date' =>
                    trim((string) ($draft['order_date'] ?? ''))
                    ?: null,
                ':subtotal' => sparrow_supplier_money(
                    $draft['subtotal'] ?? 0
                ),
                ':shipping_amount' => sparrow_supplier_money(
                    $draft['shipping'] ?? 0
                ),
                ':tax_amount' => sparrow_supplier_money(
                    $draft['tax'] ?? 0
                ),
                ':discount_amount' => sparrow_supplier_money(
                    $draft['discount'] ?? 0
                ),
                ':notes' =>
                    trim((string) (
                        $draft['supplier_notes'] ?? ''
                    )) ?: null,
                ':status' => $receipt_status,
                ':created_at' =>
                    $created_at !== ''
                        ? $created_at
                        : date('Y-m-d H:i:s'),
                ':updated_at' =>
                    $updated_at !== ''
                        ? $updated_at
                        : date('Y-m-d H:i:s'),
            ]);

            $receipt_id = (int) $pdo->lastInsertId();

            $item_stmt = $pdo->prepare("
                INSERT INTO supplier_receipt_items (
                    supplier_receipt_id,
                    item_type,
                    goes_with_order_number,
                    description,
                    quantity,
                    unit_cost,
                    item_cost,
                    accuracy,
                    possible_order_use,
                    notes
                ) VALUES (
                    :supplier_receipt_id,
                    :item_type,
                    :goes_with_order_number,
                    :description,
                    :quantity,
                    :unit_cost,
                    :item_cost,
                    :accuracy,
                    :possible_order_use,
                    :notes
                )
            ");

            $type_map = [
                'sparrow_order' => 'Sparrow Order',
                'free_add_on' => 'Free Add-On',
                'personal' => 'Personal',
                'gift' => 'Gift',
                'freebie' => 'Freebie',
                'practice' => 'Practice/Test',
                'waste' => 'Waste',
                'other' => 'Other',
            ];

            $accuracy_map = [
                'exact' => 'Exact',
                'estimated' => 'Estimated',
                'unknown_old_stash' => 'Check Label',
            ];

            foreach ($items as $item) {
                if (!is_array($item)) {
                    continue;
                }

                $description = trim(
                    (string) ($item['description'] ?? '')
                );

                if ($description === '') {
                    continue;
                }

                $old_type = strtolower(
                    trim((string) ($item['type'] ?? 'other'))
                );

                $item_type =
                    $type_map[$old_type] ?? 'Other';

                $order_label = trim(
                    (string) ($item['label'] ?? '')
                );

                if ($item_type === 'Sparrow Order') {
                    $order_label =
                        sparrow_supplier_normalize_order_number(
                            $order_label
                        );
                }

                $old_accuracy = strtolower(
                    trim((string) (
                        $item['accuracy'] ?? 'exact'
                    ))
                );

                $item_stmt->execute([
                    ':supplier_receipt_id' => $receipt_id,
                    ':item_type' => $item_type,
                    ':goes_with_order_number' =>
                        $order_label !== ''
                            ? $order_label
                            : null,
                    ':description' => $description,
                    ':quantity' => sparrow_supplier_money(
                        $item['qty'] ?? 1
                    ),
                    ':unit_cost' => sparrow_supplier_money(
                        $item['unit_cost'] ?? 0
                    ),
                    ':item_cost' => sparrow_supplier_money(
                        $item['amount'] ?? 0
                    ),
                    ':accuracy' =>
                        $accuracy_map[$old_accuracy]
                        ?? 'Check Label',
                    ':possible_order_use' => 0,
                    ':notes' =>
                        trim((string) ($item['notes'] ?? ''))
                        ?: null,
                ]);

                $result['items_imported']++;
            }

            foreach (
                $applied_groups
                as $old_order_number => $group
            ) {
                if (!is_array($group)) {
                    continue;
                }

                $order_number =
                    sparrow_supplier_normalize_order_number(
                        $old_order_number
                    );

                $order = sparrow_supplier_find_order_by_number(
                    $order_number
                );

                if (!$order) {
                    $result['applications_skipped']++;
                    $result['missing_orders'][$order_number] =
                        true;
                    continue;
                }

                $application_check = $pdo->prepare("
                    SELECT id
                    FROM supplier_receipt_applications
                    WHERE supplier_receipt_id = :receipt_id
                      AND order_number = :order_number
                      AND status = 'applied'
                    LIMIT 1
                ");

                $application_check->execute([
                    ':receipt_id' => $receipt_id,
                    ':order_number' => $order_number,
                ]);

                if ($application_check->fetchColumn()) {
                    $result['applications_skipped']++;
                    continue;
                }

                $item_total = sparrow_supplier_money(
                    $group['item_total'] ?? 0
                );

                $shipping_share = sparrow_supplier_money(
                    $group['shipping_share'] ?? 0
                );

                $tax_share = sparrow_supplier_money(
                    $group['tax_share'] ?? 0
                );

                $discount_share = sparrow_supplier_money(
                    $group['credit_share'] ?? 0
                );

                $total_internal_cost =
                    sparrow_supplier_money(
                        $item_total
                        + $shipping_share
                        + $tax_share
                        - $discount_share
                    );

                /*
                 * Restore old WordPress costs.
                 * Travel remains zero because it was not stored.
                 */
                $order_update = $pdo->prepare("
                    UPDATE orders
                    SET
                        supplier_item_cost =
                            COALESCE(
                                supplier_item_cost,
                                0
                            ) + :item_total,
                        shipping_cost =
                            COALESCE(
                                shipping_cost,
                                0
                            ) + :shipping_amount,
                        tax_cost =
                            COALESCE(
                                tax_cost,
                                0
                            ) + :tax_amount,
                        discount_cost =
                            COALESCE(
                                discount_cost,
                                0
                            ) + :discount_amount
                    WHERE id = :order_id
                    LIMIT 1
                ");

                $order_update->execute([
                    ':item_total' => $item_total,
                    ':shipping_amount' => $shipping_share,
                    ':tax_amount' => $tax_share,
                    ':discount_amount' => $discount_share,
                    ':order_id' => (int) $order['id'],
                ]);

                $application_stmt = $pdo->prepare("
                    INSERT INTO supplier_receipt_applications (
                        supplier_receipt_id,
                        order_id,
                        order_number,
                        item_cost_amount,
                        shipping_amount,
                        tax_amount,
                        travel_amount,
                        discount_amount,
                        total_internal_cost,
                        status,
                        notes,
                        applied_at,
                        created_at,
                        updated_at
                    ) VALUES (
                        :supplier_receipt_id,
                        :order_id,
                        :order_number,
                        :item_cost_amount,
                        :shipping_amount,
                        :tax_amount,
                        0.00,
                        :discount_amount,
                        :total_internal_cost,
                        'applied',
                        :notes,
                        :applied_at,
                        :created_at,
                        :updated_at
                    )
                ");

                $applied_at = trim(
                    (string) ($group['applied_at'] ?? '')
                );

                $application_note =
                    'Imported from WordPress Supplier Splitter. '
                    . 'Travel was not stored in the old system.';

                $application_stmt->execute([
                    ':supplier_receipt_id' => $receipt_id,
                    ':order_id' => (int) $order['id'],
                    ':order_number' => $order_number,
                    ':item_cost_amount' => $item_total,
                    ':shipping_amount' => $shipping_share,
                    ':tax_amount' => $tax_share,
                    ':discount_amount' => $discount_share,
                    ':total_internal_cost' =>
                        $total_internal_cost,
                    ':notes' => $application_note,
                    ':applied_at' =>
                        $applied_at !== ''
                            ? $applied_at
                            : date('Y-m-d H:i:s'),
                    ':created_at' =>
                        $applied_at !== ''
                            ? $applied_at
                            : date('Y-m-d H:i:s'),
                    ':updated_at' => date('Y-m-d H:i:s'),
                ]);

                $result['applications_imported']++;
                $result['orders_updated']++;
            }

            /*
             * If none of the old applied groups matched current
             * Backyard orders, do not leave the receipt locked.
             */
            if (
                $applied_groups
                && $receipt_status === 'applied'
            ) {
                $active_count_stmt = $pdo->prepare("
                    SELECT COUNT(*)
                    FROM supplier_receipt_applications
                    WHERE supplier_receipt_id = :receipt_id
                      AND status = 'applied'
                ");

                $active_count_stmt->execute([
                    ':receipt_id' => $receipt_id,
                ]);

                if (
                    (int) $active_count_stmt->fetchColumn()
                    === 0
                ) {
                    $unlock_stmt = $pdo->prepare("
                        UPDATE supplier_receipts
                        SET status = 'reviewed'
                        WHERE id = :id
                        LIMIT 1
                    ");

                    $unlock_stmt->execute([
                        ':id' => $receipt_id,
                    ]);
                }
            }

            $pdo->commit();
            $result['receipts_imported']++;
        } catch (Throwable $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }

            throw new RuntimeException(
                'Receipt '
                . (
                    $receipt_number !== ''
                        ? $receipt_number
                        : '(no receipt number)'
                )
                . ' could not be imported: '
                . $e->getMessage(),
                0,
                $e
            );
        }
    }

    $result['missing_orders'] = array_keys(
        $result['missing_orders']
    );

    return $result;
}