Block restricted customer order items

[codex] Fix limited backoffice price saves on legacy schema

services/nginx/app/classes/customer_product_rule_service.php

@@ -0,0 +1,158 @@
+<?php
+
+namespace classes;
+
+use objects\orders_o;
+use objects\products_o;
+use objects\users_o;
+
+class customer_product_rule_service
+{
+    public const BLOCK_MESSAGE = 'This product is not allowed for the selected customer';
+
+    private const ADDON_CATEGORY_ID = 4;
+    private const TANK_CLEANING_CATEGORY_ID = 5;
+
+    /**
+     * @return array{rule:string,message:string}|null
+     */
+    public function firstViolationForOrderItem(int $orderId, int $productId, ?int $relatedItemId): ?array
+    {
+        $order = (new orders_o())->getOrderById($orderId);
+        if (!$order->exists()) {
+            return null;
+        }
+
+        $product = (new products_o())->getProductById($productId);
+        if (!$product->exists()) {
+            return null;
+        }
+
+        $customer = (new users_o())->getUserByCustomerNumber((int)$order->customer_id->value());
+        if (!$customer->exists()) {
+            return null;
+        }
+
+        $categoryId = (int)$product->category->value();
+        $categoryName = $this->categoryName($categoryId);
+        $searchableProduct = $this->searchableProductText($product, $categoryName);
+        $isTankCleaningProduct = $this->isTankCleaningProduct($categoryId, $searchableProduct);
+
+        if ($customer->doesUserHaveAttribute('restrictAdditionalServices')
+            && $this->isAdditionalServiceProduct($orderId, $relatedItemId, $categoryId, $searchableProduct)) {
+            return $this->violation('restrictAdditionalServices');
+        }
+
+        if ($customer->doesUserHaveAttribute('restrictTankCleaning') && $isTankCleaningProduct) {
+            return $this->violation('restrictTankCleaning');
+        }
+
+        if ($customer->doesUserHaveAttribute('onlyTankCleaning') && !$isTankCleaningProduct) {
+            return $this->violation('onlyTankCleaning');
+        }
+
+        if ($customer->doesUserHaveAttribute('restrictSpotFree')
+            && $this->containsAny($searchableProduct, ['spot free', 'spotfree'])) {
+            return $this->violation('restrictSpotFree');
+        }
+
+        if ($customer->doesUserHaveAttribute('restrictInteriorCleaning')
+            && $this->containsAny($searchableProduct, ['interior', 'indvendig'])) {
+            return $this->violation('restrictInteriorCleaning');
+        }
+
+        return null;
+    }
+
+    /**
+     * @return array{rule:string,message:string}
+     */
+    private function violation(string $rule): array
+    {
+        return [
+            'rule' => $rule,
+            'message' => self::BLOCK_MESSAGE,
+        ];
+    }
+
+    private function isAdditionalServiceProduct(int $orderId, ?int $relatedItemId, int $categoryId, string $searchableProduct): bool
+    {
+        if ($relatedItemId !== null && $relatedItemId > 0) {
+            return true;
+        }
+
+        if ($categoryId === self::ADDON_CATEGORY_ID) {
+            return true;
+        }
+
+        if ($this->containsAny($searchableProduct, ['add-on', 'add on', 'addon', 'tilvalg'])) {
+            return true;
+        }
+
+        return $this->countStandaloneOrderItems($orderId) > 0;
+    }
+
+    private function isTankCleaningProduct(int $categoryId, string $searchableProduct): bool
+    {
+        if ($categoryId === self::TANK_CLEANING_CATEGORY_ID) {
+            return true;
+        }
+
+        return $this->containsAny($searchableProduct, ['tank cleaning', 'tankcleaning', 'tankrens', 'tank rens']);
+    }
+
+    private function searchableProductText(products_o $product, string $categoryName): string
+    {
+        return strtolower(trim((string)$product->name->value() . ' ' . $categoryName));
+    }
+
+    /**
+     * @param array<int, string> $terms
+     */
+    private function containsAny(string $value, array $terms): bool
+    {
+        foreach ($terms as $term) {
+            if ($term !== '' && str_contains($value, $term)) {
+                return true;
+            }
+        }
+
+        return false;
+    }
+
+    private function categoryName(int $categoryId): string
+    {
+        global $db;
+
+        if ($categoryId <= 0) {
+            return '';
+        }
+
+        $result = $db->query('SELECT name FROM categories WHERE id = ' . $categoryId . ' LIMIT 1');
+        if (!$result || $result->num_rows === 0) {
+            return '';
+        }
+
+        $row = $result->fetch_assoc();
+        return strtolower((string)($row['name'] ?? ''));
+    }
+
+    private function countStandaloneOrderItems(int $orderId): int
+    {
+        global $db;
+
+        $result = $db->query(
+            'SELECT COUNT(*) AS item_count
+             FROM order_items
+             WHERE order_id = ' . $orderId . '
+               AND deleted_at IS NULL
+               AND (related_item_id IS NULL OR related_item_id = 0)'
+        );
+        if (!$result) {
+            return 0;
+        }
+
+        $row = $result->fetch_assoc();
+        return (int)($row['item_count'] ?? 0);
+    }
+}

services/nginx/app/classes/limited_backoffice_service.php

@@ -291,10 +291,15 @@ class limited_backoffice_service
         $mysqli->begin_transaction();
 
         try {
+            $priceUpdateAssignments = ['`price` = VALUES(`price`)'];
+            if ($this->tableHasColumn('product_department_prices', 'updated_at')) {
+                $priceUpdateAssignments[] = '`updated_at` = CURRENT_TIMESTAMP';
+            }
+
             $statement = $mysqli->prepare(
                 'INSERT INTO `product_department_prices` (`department_id`, `product_id`, `price`)
                  VALUES (?, ?, ?)
-                 ON DUPLICATE KEY UPDATE `price` = VALUES(`price`), `updated_at` = CURRENT_TIMESTAMP'
+                 ON DUPLICATE KEY UPDATE ' . implode(', ', $priceUpdateAssignments)
             );
             if ($statement === false) {
                 throw new \RuntimeException('Unable to prepare department price update.');

services/nginx/app/routes/orderItemsRoute.php

@@ -3,6 +3,7 @@
 namespace routes;
 
 use classes\authentication;
+use classes\customer_product_rule_service;
 use objects\logs_o;
 use objects\order_items_o;
 use objects\orders_o;
@@ -70,6 +71,10 @@ class orderItemsRoute
                         $price = (int)self::getParameter('price');
                     }
                 }
+                $order = (new orders_o())->getOrderById((int)$data['order_id']);
+                if (!$order->exists()) {
+                    $response->error('Order not found', 404);
+                }
                 $product = (new products_o())->getProductById((int)$data['product_id']);
                 if (!$product->exists()) {
                     $response->error('Product not found', 404);
@@ -77,6 +82,19 @@ class orderItemsRoute
                 if ($product->requiresOrderItemNote() && trim((string)($notes ?? '')) === '') {
                     $response->error('Notes is required for this product', 400);
                 }
+                $customerRuleViolation = (new customer_product_rule_service())
+                    ->firstViolationForOrderItem((int)$data['order_id'], (int)$data['product_id'], $related_item_id);
+                if ($customerRuleViolation !== null) {
+                    (new logs_o())->add(
+                        'order_items',
+                        'global',
+                        1,
+                        $user->id,
+                        'ORDER_ITEM_RESTRICTED_BY_CUSTOMER_RULE',
+                        'Blocked product ' . (int)$data['product_id'] . ' on order ' . (int)$data['order_id'] . ' by rule ' . $customerRuleViolation['rule']
+                    );
+                    $response->error($customerRuleViolation['message'], 400);
+                }
 
                 // Add the order item to the order This is done individually, to make the notes to the individual order items possible
                 $order_items = (new order_items_o());

services/nginx/app/tests/Api/LimitedBackofficeApiTest.php

@@ -85,6 +85,32 @@ function limited_backoffice_without_users_deleted_at(callable $callback): void
     }
 }
 
+function limited_backoffice_without_department_prices_updated_at(callable $callback): void
+{
+    $db = api_test_runtime()->db();
+    $column = $db->query("SHOW COLUMNS FROM `product_department_prices` LIKE 'updated_at'");
+    if ($column === false) {
+        throw new RuntimeException('Unable to inspect product_department_prices.updated_at test column.');
+    }
+
+    $hadColumn = (int)$column->num_rows > 0;
+    if ($hadColumn) {
+        $db->query('ALTER TABLE `product_department_prices` DROP COLUMN `updated_at`');
+    }
+
+    try {
+        $callback();
+    } finally {
+        if ($hadColumn) {
+            $db->query(
+                'ALTER TABLE `product_department_prices`
+                 ADD COLUMN `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
+                 AFTER `created_at`'
+            );
+        }
+    }
+}
+
 it('lists and updates explicit prices only for assigned departments', function (): void {
     api_test_covers('GET /limited-backoffice/departments', 'happy');
     api_test_covers('GET /limited-backoffice/departments/{departmentId}/prices', 'happy');
@@ -148,6 +174,43 @@ it('lists and updates explicit prices only for assigned departments', function (
     expect(limited_backoffice_price_value((int)$otherDepartment['id'], (int)$product['id']))->toBe(4321);
 });
 
+it('updates department prices when the price table has no updated_at column', function (): void {
+    api_test_covers('PUT /limited-backoffice/departments/{departmentId}/prices', 'schema compatibility');
+
+    $department = api_fixtures()->createDepartment(['name' => 'Limited Prices Legacy Schema']);
+    $category = api_fixtures()->createCategory(['name' => 'Limited Prices Legacy Category']);
+    $products = [
+        api_fixtures()->createProduct(['name' => 'Legacy Price One', 'category' => $category['id']]),
+        api_fixtures()->createProduct(['name' => 'Legacy Price Two', 'category' => $category['id']]),
+        api_fixtures()->createProduct(['name' => 'Legacy Price Three', 'category' => $category['id']]),
+    ];
+    api_fixtures()->linkDepartmentCategory((int)$department['id'], (int)$category['id']);
+
+    foreach ($products as $index => $product) {
+        limited_backoffice_price_insert((int)$department['id'], (int)$product['id'], 100 + $index);
+    }
+
+    $session = limited_backoffice_manager_session([(int)$department['id']]);
+    limited_backoffice_without_department_prices_updated_at(function () use ($department, $products, $session): void {
+        $updated = api_client()->put('/limited-backoffice/departments/' . (int)$department['id'] . '/prices', [
+            'prices' => [
+                ['product_id' => (int)$products[0]['id'], 'price' => '999999'],
+                ['product_id' => (int)$products[1]['id'], 'price' => '999999'],
+                ['product_id' => (int)$products[2]['id'], 'price' => '99999'],
+            ],
+        ], $session['headers']);
+
+        $updated
+            ->assertStatus(200)
+            ->assertEnvelope()
+            ->assertSuccess();
+
+        expect(limited_backoffice_price_value((int)$department['id'], (int)$products[0]['id']))->toBe(999999);
+        expect(limited_backoffice_price_value((int)$department['id'], (int)$products[1]['id']))->toBe(999999);
+        expect(limited_backoffice_price_value((int)$department['id'], (int)$products[2]['id']))->toBe(99999);
+    });
+});
+
 it('rejects cross-department price access, body spoofing, and outside products', function (): void {
     api_test_covers('GET /limited-backoffice/departments/{departmentId}/prices', 'auth');
     api_test_covers('PUT /limited-backoffice/departments/{departmentId}/prices', 'auth');

services/nginx/app/tests/Api/OrderItemsApiTest.php

@@ -4,6 +4,38 @@ declare(strict_types=1);
 
 usesApiSuite();
 
+function create_order_item_rule_fixture(array $customerAttributes = []): array
+{
+    $customer = api_fixtures()->createUser(['display_name' => 'Order Item Rule Customer']);
+    foreach ($customerAttributes as $attribute) {
+        api_fixtures()->addCustomerAttribute((int)$customer['id'], (string)$attribute);
+    }
+
+    $department = api_fixtures()->createDepartment();
+    $order = api_fixtures()->createOrder([
+        'customer_id' => $customer['customer_number'],
+        'department_id' => $department['id'],
+        'reference' => 'RULE-CHECK',
+    ]);
+    $session = api_fixtures()->createUserSession([], ['group_id' => 1]);
+
+    return [
+        'customer' => $customer,
+        'department' => $department,
+        'order' => $order,
+        'session' => $session,
+    ];
+}
+
+function post_order_item(array $order, array $product, array $headers, array $overrides = []): \Tests\Support\Api\ApiResponse
+{
+    return api_client()->post('/order/items', array_merge([
+        'order_id' => $order['id'],
+        'product_id' => $product['id'],
+        'quantity' => 1,
+    ], $overrides), $headers);
+}
+
 it('requires notes when adding the extraordinary chemistry product to an order', function (): void {
     api_test_covers('POST /order/items', 'validation');
 
@@ -111,3 +143,126 @@ it('returns the extraordinary chemistry product with requires_note enabled', fun
 
     expect($response->data()['requires_note'] ?? null)->toBeTrue();
 });
+
+it('blocks addon products added as standalone additional order items for customers restricted from additional services', function (): void {
+    api_test_covers('POST /order/items', 'customer-rule-validation');
+
+    $fixture = create_order_item_rule_fixture(['restrictAdditionalServices']);
+    $primaryProduct = api_fixtures()->createProduct([
+        'name' => 'Primary truck wash',
+        'price' => 200,
+    ]);
+    $addonProduct = api_fixtures()->createProduct([
+        'name' => 'Drying add-on',
+        'category' => 4,
+        'price' => 50,
+    ]);
+
+    post_order_item($fixture['order'], $primaryProduct, $fixture['session']['headers'])
+        ->assertStatus(200)
+        ->assertEnvelope()
+        ->assertSuccess();
+
+    post_order_item($fixture['order'], $addonProduct, $fixture['session']['headers'])
+        ->assertStatus(400)
+        ->assertEnvelope()
+        ->assertSuccess(false)
+        ->assertMessage(\classes\customer_product_rule_service::BLOCK_MESSAGE);
+});
+
+it('allows standalone additional order items when the customer is not restricted from additional services', function (): void {
+    api_test_covers('POST /order/items', 'customer-rule-validation');
+
+    $fixture = create_order_item_rule_fixture();
+    $primaryProduct = api_fixtures()->createProduct([
+        'name' => 'Primary unrestricted truck wash',
+        'price' => 200,
+    ]);
+    $addonProduct = api_fixtures()->createProduct([
+        'name' => 'Unrestricted add-on',
+        'category' => 4,
+        'price' => 50,
+    ]);
+
+    post_order_item($fixture['order'], $primaryProduct, $fixture['session']['headers'])
+        ->assertStatus(200)
+        ->assertEnvelope()
+        ->assertSuccess();
+
+    post_order_item($fixture['order'], $addonProduct, $fixture['session']['headers'])
+        ->assertStatus(200)
+        ->assertEnvelope()
+        ->assertSuccess();
+});
+
+it('blocks related addon order items for customers restricted from additional services', function (): void {
+    api_test_covers('POST /order/items', 'customer-rule-validation');
+
+    $fixture = create_order_item_rule_fixture(['restrictAdditionalServices']);
+    $cashier = api_fixtures()->createUser(['display_name' => 'Order Item Rule Cashier']);
+    $primaryProduct = api_fixtures()->createProduct([
+        'name' => 'Primary related truck wash',
+        'price' => 200,
+    ]);
+    $addonProduct = api_fixtures()->createProduct([
+        'name' => 'Related extra brush',
+        'price' => 35,
+    ]);
+    $primaryItem = api_fixtures()->createOrderItem([
+        'order_id' => $fixture['order']['id'],
+        'product_id' => $primaryProduct['id'],
+        'cashier_id' => $cashier['id'],
+        'price' => 200,
+    ]);
+
+    post_order_item($fixture['order'], $addonProduct, $fixture['session']['headers'], [
+        'related_item_id' => $primaryItem['id'],
+    ])
+        ->assertStatus(400)
+        ->assertEnvelope()
+        ->assertSuccess(false)
+        ->assertMessage(\classes\customer_product_rule_service::BLOCK_MESSAGE);
+});
+
+it('blocks named restricted service products for the selected customer', function (string $attribute, array $productAttributes): void {
+    api_test_covers('POST /order/items', 'customer-rule-validation');
+
+    $fixture = create_order_item_rule_fixture([$attribute]);
+    $product = api_fixtures()->createProduct($productAttributes);
+
+    post_order_item($fixture['order'], $product, $fixture['session']['headers'])
+        ->assertStatus(400)
+        ->assertEnvelope()
+        ->assertSuccess(false)
+        ->assertMessage(\classes\customer_product_rule_service::BLOCK_MESSAGE);
+})->with([
+    'spot free' => ['restrictSpotFree', ['name' => 'Spot Free rinse', 'price' => 80]],
+    'interior cleaning' => ['restrictInteriorCleaning', ['name' => 'Indvendig vask', 'price' => 125]],
+    'tank cleaning' => ['restrictTankCleaning', ['name' => 'Tankrens', 'category' => 5, 'price' => 300]],
+]);
+
+it('only allows tank cleaning products when the customer has the only tank cleaning rule', function (): void {
+    api_test_covers('POST /order/items', 'customer-rule-validation');
+
+    $fixture = create_order_item_rule_fixture(['onlyTankCleaning']);
+    $nonTankProduct = api_fixtures()->createProduct([
+        'name' => 'Exterior truck wash',
+        'price' => 180,
+    ]);
+    $tankProduct = api_fixtures()->createProduct([
+        'name' => 'Tank cleaning',
+        'category' => 5,
+        'price' => 300,
+    ]);
+
+    post_order_item($fixture['order'], $nonTankProduct, $fixture['session']['headers'])
+        ->assertStatus(400)
+        ->assertEnvelope()
+        ->assertSuccess(false)
+        ->assertMessage(\classes\customer_product_rule_service::BLOCK_MESSAGE);
+
+    post_order_item($fixture['order'], $tankProduct, $fixture['session']['headers'])
+        ->assertStatus(200)
+        ->assertEnvelope()
+        ->assertSuccess();
+});