<?php

/**
 * Updates order status and synchronizes accessory stock when status changes
 * to/from cancelled.
 */
function transitionOrderStatusWithStock(PDO $db, string $orderId, string $newStatus): bool {
    $allowed = ['pending', 'confirmed', 'out_for_delivery', 'delivered', 'cancelled'];
    if (!$orderId || !in_array($newStatus, $allowed, true)) return false;

    $db->beginTransaction();
    try {
        $stmt = $db->prepare('SELECT status FROM orders WHERE id = ? FOR UPDATE');
        $stmt->execute([$orderId]);
        $oldStatus = $stmt->fetchColumn();
        if ($oldStatus === false) {
            $db->rollBack();
            return false;
        }

        // Nothing to do.
        if ($oldStatus === $newStatus) {
            $db->commit();
            return true;
        }

        $isCanceling = ($oldStatus !== 'cancelled' && $newStatus === 'cancelled');
        $isReopening = ($oldStatus === 'cancelled' && $newStatus !== 'cancelled');

        if ($isCanceling || $isReopening) {
            $itemStmt = $db->prepare('SELECT product_id, category, quantity FROM order_items WHERE order_id = ? AND product_id IS NOT NULL');
            $itemStmt->execute([$orderId]);
            $items = $itemStmt->fetchAll(PDO::FETCH_ASSOC);

            foreach ($items as $item) {
                if (($item['category'] ?? '') !== 'accessory') continue;

                $qty = (int)($item['quantity'] ?? 0);
                if ($qty <= 0) continue;

                if ($isCanceling) {
                    // Return reserved stock when order gets cancelled.
                    $db->prepare('UPDATE products SET stock = stock + ? WHERE id = ?')
                        ->execute([$qty, $item['product_id']]);
                } elseif ($isReopening) {
                    // Reserve stock again if cancelled order is moved back to active status.
                    $db->prepare('UPDATE products SET stock = GREATEST(0, stock - ?) WHERE id = ?')
                        ->execute([$qty, $item['product_id']]);
                }
            }
        }

        $db->prepare('UPDATE orders SET status = ?, updated_at = NOW() WHERE id = ?')
            ->execute([$newStatus, $orderId]);

        $db->commit();
        return true;
    } catch (Throwable $e) {
        if ($db->inTransaction()) $db->rollBack();
        return false;
    }
}
