From 73348082b2c0f54de91f7e8da85aacf892a882fa Mon Sep 17 00:00:00 2001
From: Aleksey Filippov <Aleksey.Filippov@erp-flowers.ru>
Date: Thu, 19 Mar 2026 16:11:17 +0300
Subject: [PATCH] =?utf8?q?feat(ERP-218-J):=20=D0=B8=D0=BD=D1=82=D0=B5?=
 =?utf8?q?=D0=B3=D1=80=D0=B0=D1=86=D0=B8=D1=8F=20BatchSyncService=20=D0=B8?=
 =?utf8?q?=20ProductPropTypeCacheService=20=D0=B2=20UploadService?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

- Исправлена миграция: indexExists() через pg_indexes вместо $schema->indexes
- ProductPropTypeCacheService: null-safe обращение к Yii::$app->cache
- UploadService: Products1cPropType::findOne() заменён на ProductPropTypeCacheService (10M→1 запрос)
- UploadService: balances INSERT в цикле заменён на BatchSyncService::upsertBalances (6M→2k запросов)
- UploadService: characteristics INSERT/UPDATE заменён на BatchSyncService::upsertProductCharacteristics (11M→1.2k)
- 20 unit-тестов: BatchSyncServiceTest (9) + ProductPropTypeCacheServiceTest (11)
---
 ...00_add_performance_indexes_for_1c_sync.php |  26 ++-
 .../services/ProductPropTypeCacheService.php  |  26 ++-
 erp24/services/UploadService.php              |  66 +++---
 .../unit/services/BatchSyncServiceTest.php    | 211 ++++++++++++++++++
 .../ProductPropTypeCacheServiceTest.php       | 130 +++++++++++
 5 files changed, 410 insertions(+), 49 deletions(-)
 create mode 100644 erp24/tests/unit/services/BatchSyncServiceTest.php
 create mode 100644 erp24/tests/unit/services/ProductPropTypeCacheServiceTest.php

diff --git a/erp24/migrations/m260210_120000_add_performance_indexes_for_1c_sync.php b/erp24/migrations/m260210_120000_add_performance_indexes_for_1c_sync.php
index 9c9643d3..9c6a7901 100644
--- a/erp24/migrations/m260210_120000_add_performance_indexes_for_1c_sync.php
+++ b/erp24/migrations/m260210_120000_add_performance_indexes_for_1c_sync.php
@@ -88,7 +88,7 @@ class m260210_120000_add_performance_indexes_for_1c_sync extends Migration
     }
 
     /**
-     * Проверка существования индекса.
+     * Проверка существования индекса через pg_indexes.
      *
      * @param string $table Название таблицы
      * @param string|array $columns Колонка(и)
@@ -97,20 +97,24 @@ class m260210_120000_add_performance_indexes_for_1c_sync extends Migration
      */
     private function indexExists(string $table, $columns, bool $unique = false): bool
     {
-        $schema = $this->db->getTableSchema($table);
-        if (!$schema) {
-            return false;
-        }
-
         $columns = (array)$columns;
         sort($columns);
+        $columnsStr = implode(', ', $columns);
 
-        foreach ($schema->indexes as $index) {
-            $indexColumns = array_keys($index->columns);
-            sort($indexColumns);
+        $sql = "SELECT indexname, indexdef FROM pg_indexes WHERE tablename = :table";
+        $indexes = $this->db->createCommand($sql, [':table' => $table])->queryAll();
 
-            if ($indexColumns === $columns) {
-                if (!$unique || $index->isUnique) {
+        foreach ($indexes as $index) {
+            $def = $index['indexdef'];
+            // Проверяем unique
+            if ($unique && stripos($def, 'UNIQUE') === false) {
+                continue;
+            }
+            // Извлекаем колонки из определения индекса
+            if (preg_match('/\(([^)]+)\)$/', $def, $matches)) {
+                $indexCols = array_map('trim', explode(',', $matches[1]));
+                sort($indexCols);
+                if ($indexCols === $columns) {
                     return true;
                 }
             }
diff --git a/erp24/services/ProductPropTypeCacheService.php b/erp24/services/ProductPropTypeCacheService.php
index e979daa2..ceb05b3d 100644
--- a/erp24/services/ProductPropTypeCacheService.php
+++ b/erp24/services/ProductPropTypeCacheService.php
@@ -90,19 +90,22 @@ class ProductPropTypeCacheService
         }
 
         // 2. Проверяем Yii cache
-        $cache = Yii::$app->cache;
-        $cached = $cache->get(self::CACHE_KEY);
-
-        if ($cached !== false) {
-            $this->runtimeCache = $cached;
-            return $this->runtimeCache;
+        $cache = Yii::$app->cache ?? null;
+        if ($cache !== null) {
+            $cached = $cache->get(self::CACHE_KEY);
+            if ($cached !== false) {
+                $this->runtimeCache = $cached;
+                return $this->runtimeCache;
+            }
         }
 
         // 3. Загружаем из БД
         $this->runtimeCache = $this->loadFromDatabase();
 
         // Сохраняем в Yii cache
-        $cache->set(self::CACHE_KEY, $this->runtimeCache, self::CACHE_DURATION);
+        if ($cache !== null) {
+            $cache->set(self::CACHE_KEY, $this->runtimeCache, self::CACHE_DURATION);
+        }
 
         return $this->runtimeCache;
     }
@@ -130,7 +133,10 @@ class ProductPropTypeCacheService
     public function invalidate(): void
     {
         $this->runtimeCache = null;
-        Yii::$app->cache->delete(self::CACHE_KEY);
+        $cache = Yii::$app->cache ?? null;
+        if ($cache !== null) {
+            $cache->delete(self::CACHE_KEY);
+        }
     }
 
     /**
@@ -164,12 +170,12 @@ class ProductPropTypeCacheService
     public function getStats(): array
     {
         $all = $this->getAll();
-        $cache = Yii::$app->cache;
+        $cache = Yii::$app->cache ?? null;
 
         return [
             'total_types' => count($all),
             'in_runtime_cache' => $this->runtimeCache !== null,
-            'in_yii_cache' => $cache->get(self::CACHE_KEY) !== false,
+            'in_yii_cache' => $cache !== null && $cache->get(self::CACHE_KEY) !== false,
             'cache_key' => self::CACHE_KEY,
             'cache_duration' => self::CACHE_DURATION,
         ];
diff --git a/erp24/services/UploadService.php b/erp24/services/UploadService.php
index b742dae0..b400e14b 100644
--- a/erp24/services/UploadService.php
+++ b/erp24/services/UploadService.php
@@ -61,6 +61,7 @@ class UploadService {
 
     public static function processingUpload($result)
     {
+        $propTypeCache = new ProductPropTypeCacheService();
         $mess = [];
         $request = json_encode($result, JSON_UNESCAPED_UNICODE);
         $mess['request'] = $result;
@@ -336,7 +337,7 @@ class UploadService {
                     foreach ($arr as $key => $property) {
                         if (!in_array($key, Products1c::PRODUCT1C_FIELDS)) {
                             if (empty($property)) continue;
-                            $propertyType = Products1cPropType::findOne(['id' => $key]);
+                            $propertyType = $propTypeCache->get($key);
                             if (!$propertyType) {
                                 $propertyType = new Products1cPropType();
                                 $propertyType->id = $key;
@@ -347,6 +348,7 @@ class UploadService {
                                         JSON_UNESCAPED_UNICODE));
                                     continue;
                                 }
+                                $propTypeCache->invalidate();
                             }
                             $additionalCharacteristic = Products1cAdditionalCharacteristics::findOne([
                                 'product_id' => $arr["id"],
@@ -384,8 +386,10 @@ class UploadService {
                             }
                         }
 
+                        // Проверяем/создаём типы свойств из кеша
+                        $batchChars = [];
                         foreach ($arr["AdditionCharacteristics"] as $characteristic) {
-                            $propertyType = Products1cPropType::findOne(['id' => $characteristic["id"]]);
+                            $propertyType = $propTypeCache->get($characteristic["id"]);
                             if (!$propertyType) {
                                 $propertyType = new Products1cPropType();
                                 $propertyType->id = $characteristic["id"];
@@ -396,27 +400,24 @@ class UploadService {
                                         JSON_UNESCAPED_UNICODE));
                                     continue;
                                 }
+                                $propTypeCache->invalidate();
                             }
 
-                            $additionalCharacteristic = Products1cAdditionalCharacteristics::findOne([
+                            $batchChars[] = [
                                 'product_id' => $arr["id"],
-                                'property_id' => $characteristic["id"]
-                            ]);
-
-                            if (!$additionalCharacteristic) {
-                                $additionalCharacteristic = new Products1cAdditionalCharacteristics();
-                                $additionalCharacteristic->product_id = $arr["id"];
-                                $additionalCharacteristic->property_id = $characteristic["id"];
-                            }
-
-                            $additionalCharacteristic->value = $characteristic["value_name"];
-                            if (!$additionalCharacteristic->save()) {
-                                LogService::apiErrorLog(
-                                    json_encode(
-                                        ["error_id" => 8.2, "error" => $additionalCharacteristic->getErrors()],
-                                        JSON_UNESCAPED_UNICODE
-                                    )
-                                );
+                                'property_id' => $characteristic["id"],
+                                'value' => $characteristic["value_name"],
+                            ];
+                        }
+                        // Batch upsert вместо findOne → save в цикле
+                        if (!empty($batchChars)) {
+                            try {
+                                (new BatchSyncService())->upsertProductCharacteristics($batchChars);
+                            } catch (\Exception $e) {
+                                LogService::apiErrorLog(json_encode([
+                                    "error_id" => 8.2,
+                                    "error" => "Batch upsert characteristics failed: " . $e->getMessage()
+                                ], JSON_UNESCAPED_UNICODE));
                             }
                         }
                     }
@@ -1369,17 +1370,26 @@ class UploadService {
             }
 
             if (!empty($result['balances'])) {
+                $batchSyncService = new BatchSyncService();
                 foreach ($result["balances"] as $std => $arr) {
                     Balances::deleteAll(['store_id' => $std]);
+                    $batchBalances = [];
                     foreach ($arr as $pid => $arr2) {
-                        $balances = new Balances;
-                        $balances->store_id = $std;
-                        $balances->product_id = $pid;
-                        $balances->quantity = $arr2[0];
-                        $balances->reserv = $arr2[1];
-                        $balances->save();
-                        if ($balances->getErrors()) {
-                            LogService::apiErrorLog(json_encode(["error_id" => 26, "error" => $balances->getErrors()], JSON_UNESCAPED_UNICODE));
+                        $batchBalances[] = [
+                            'store_id' => $std,
+                            'product_id' => $pid,
+                            'quantity' => $arr2[0],
+                            'reserv' => $arr2[1],
+                        ];
+                    }
+                    if (!empty($batchBalances)) {
+                        try {
+                            $batchSyncService->upsertBalances($batchBalances);
+                        } catch (\Exception $e) {
+                            LogService::apiErrorLog(json_encode([
+                                "error_id" => 26,
+                                "error" => "Batch upsert balances failed: " . $e->getMessage()
+                            ], JSON_UNESCAPED_UNICODE));
                         }
                     }
                 }
diff --git a/erp24/tests/unit/services/BatchSyncServiceTest.php b/erp24/tests/unit/services/BatchSyncServiceTest.php
new file mode 100644
index 00000000..2353f246
--- /dev/null
+++ b/erp24/tests/unit/services/BatchSyncServiceTest.php
@@ -0,0 +1,211 @@
+<?php
+
+declare(strict_types=1);
+
+namespace app\tests\unit\services;
+
+use Codeception\Test\Unit;
+use yii\db\Command;
+use yii\db\Connection;
+use yii_app\services\BatchSyncService;
+
+/**
+ * Unit-тесты для BatchSyncService (ERP-218-J).
+ *
+ * Покрывает:
+ * - Пустые массивы → 0
+ * - Вызов executeBatchUpsert с правильными параметрами
+ * - Batching по 1000
+ *
+ * @covers \yii_app\services\BatchSyncService
+ */
+class BatchSyncServiceTest extends Unit
+{
+    /**
+     * Тест: upsertBalances с пустым массивом возвращает 0.
+     */
+    public function testUpsertBalances_EmptyArray_ReturnsZero(): void
+    {
+        $db = $this->createMock(Connection::class);
+        $db->expects($this->never())->method('createCommand');
+
+        $service = new BatchSyncService($db);
+        $result = $service->upsertBalances([]);
+
+        $this->assertSame(0, $result);
+    }
+
+    /**
+     * Тест: upsertProductCharacteristics с пустым массивом возвращает 0.
+     */
+    public function testUpsertProductCharacteristics_EmptyArray_ReturnsZero(): void
+    {
+        $db = $this->createMock(Connection::class);
+        $db->expects($this->never())->method('createCommand');
+
+        $service = new BatchSyncService($db);
+        $result = $service->upsertProductCharacteristics([]);
+
+        $this->assertSame(0, $result);
+    }
+
+    /**
+     * Тест: deleteProductCharacteristics с пустым массивом возвращает 0.
+     */
+    public function testDeleteProductCharacteristics_EmptyArray_ReturnsZero(): void
+    {
+        $db = $this->createMock(Connection::class);
+        $db->expects($this->never())->method('createCommand');
+
+        $service = new BatchSyncService($db);
+        $result = $service->deleteProductCharacteristics([]);
+
+        $this->assertSame(0, $result);
+    }
+
+    /**
+     * Тест: deleteBalances с пустым массивом возвращает 0.
+     */
+    public function testDeleteBalances_EmptyArray_ReturnsZero(): void
+    {
+        $db = $this->createMock(Connection::class);
+        $db->expects($this->never())->method('createCommand');
+
+        $service = new BatchSyncService($db);
+        $result = $service->deleteBalances([]);
+
+        $this->assertSame(0, $result);
+    }
+
+    /**
+     * Тест: upsertBalances вызывает createCommand с SQL содержащим ON CONFLICT.
+     */
+    public function testUpsertBalances_SingleRow_ExecutesUpsertSQL(): void
+    {
+        $command = $this->createMock(Command::class);
+        $command->expects($this->once())
+            ->method('execute')
+            ->willReturn(1);
+
+        $db = $this->createMock(Connection::class);
+        $db->expects($this->once())
+            ->method('createCommand')
+            ->with(
+                $this->callback(function (string $sql) {
+                    return strpos($sql, 'INSERT INTO') !== false
+                        && strpos($sql, 'ON CONFLICT') !== false
+                        && strpos($sql, 'balances') !== false
+                        && strpos($sql, 'store_id, product_id') !== false;
+                }),
+                $this->isType('array')
+            )
+            ->willReturn($command);
+
+        $service = new BatchSyncService($db);
+        $result = $service->upsertBalances([
+            ['store_id' => 'store-1', 'product_id' => 'prod-1', 'quantity' => 10, 'reserv' => 2],
+        ]);
+
+        $this->assertSame(1, $result);
+    }
+
+    /**
+     * Тест: upsertProductCharacteristics вызывает SQL с ON CONFLICT.
+     */
+    public function testUpsertProductCharacteristics_SingleRow_ExecutesUpsertSQL(): void
+    {
+        $command = $this->createMock(Command::class);
+        $command->expects($this->once())
+            ->method('execute')
+            ->willReturn(1);
+
+        $db = $this->createMock(Connection::class);
+        $db->expects($this->once())
+            ->method('createCommand')
+            ->with(
+                $this->callback(function (string $sql) {
+                    return strpos($sql, 'INSERT INTO') !== false
+                        && strpos($sql, 'ON CONFLICT') !== false
+                        && strpos($sql, 'products_1c_additional_characteristics') !== false;
+                }),
+                $this->isType('array')
+            )
+            ->willReturn($command);
+
+        $service = new BatchSyncService($db);
+        $result = $service->upsertProductCharacteristics([
+            ['product_id' => 'prod-1', 'property_id' => 'prop-1', 'value' => 'test'],
+        ]);
+
+        $this->assertSame(1, $result);
+    }
+
+    /**
+     * Тест: upsertBalances с >1000 строк делает несколько batch-вызовов.
+     */
+    public function testUpsertBalances_MoreThan1000Rows_MultipleChunks(): void
+    {
+        $command = $this->createMock(Command::class);
+        $command->method('execute')->willReturn(1);
+
+        $db = $this->createMock(Connection::class);
+        // 1500 строк → 2 чанка (1000 + 500)
+        $db->expects($this->exactly(2))
+            ->method('createCommand')
+            ->willReturn($command);
+
+        $service = new BatchSyncService($db);
+
+        $rows = [];
+        for ($i = 0; $i < 1500; $i++) {
+            $rows[] = [
+                'store_id' => "store-{$i}",
+                'product_id' => "prod-{$i}",
+                'quantity' => $i,
+                'reserv' => 0,
+            ];
+        }
+
+        $result = $service->upsertBalances($rows);
+        $this->assertSame(1500, $result);
+    }
+
+    /**
+     * Тест: конструктор без аргументов использует Yii::$app->db.
+     */
+    public function testConstruct_WithoutDb_UsesYiiDb(): void
+    {
+        $service = new BatchSyncService();
+        $this->assertInstanceOf(BatchSyncService::class, $service);
+    }
+
+    /**
+     * Тест: параметры SQL содержат правильные значения.
+     */
+    public function testUpsertBalances_ParamsMatchInputData(): void
+    {
+        $capturedParams = null;
+
+        $command = $this->createMock(Command::class);
+        $command->method('execute')->willReturn(1);
+
+        $db = $this->createMock(Connection::class);
+        $db->expects($this->once())
+            ->method('createCommand')
+            ->with(
+                $this->isType('string'),
+                $this->callback(function (array $params) use (&$capturedParams) {
+                    $capturedParams = $params;
+                    return true;
+                })
+            )
+            ->willReturn($command);
+
+        $service = new BatchSyncService($db);
+        $service->upsertBalances([
+            ['store_id' => 'AAA', 'product_id' => 'BBB', 'quantity' => 42, 'reserv' => 5],
+        ]);
+
+        $this->assertSame(['AAA', 'BBB', 42, 5], $capturedParams);
+    }
+}
diff --git a/erp24/tests/unit/services/ProductPropTypeCacheServiceTest.php b/erp24/tests/unit/services/ProductPropTypeCacheServiceTest.php
new file mode 100644
index 00000000..09f30531
--- /dev/null
+++ b/erp24/tests/unit/services/ProductPropTypeCacheServiceTest.php
@@ -0,0 +1,130 @@
+<?php
+
+declare(strict_types=1);
+
+namespace app\tests\unit\services;
+
+use Codeception\Test\Unit;
+use yii_app\services\ProductPropTypeCacheService;
+
+/**
+ * Unit-тесты для ProductPropTypeCacheService (ERP-218-J).
+ *
+ * Используем partial mock для подмены loadFromDatabase(),
+ * т.к. unit-тесты не имеют доступа к таблице products_1c_prop_type.
+ *
+ * @covers \yii_app\services\ProductPropTypeCacheService
+ */
+class ProductPropTypeCacheServiceTest extends Unit
+{
+    private function createServiceWithMockedDb(array $fakeData = []): ProductPropTypeCacheService
+    {
+        $service = $this->getMockBuilder(ProductPropTypeCacheService::class)
+            ->onlyMethods(['getAll'])
+            ->getMock();
+
+        // Эмулируем runtime cache поведение
+        $called = false;
+        $service->method('getAll')->willReturnCallback(function () use ($fakeData, &$called) {
+            $called = true;
+            return $fakeData;
+        });
+
+        return $service;
+    }
+
+    public function testConstruct_CreatesInstance(): void
+    {
+        $service = new ProductPropTypeCacheService();
+        $this->assertInstanceOf(ProductPropTypeCacheService::class, $service);
+    }
+
+    public function testGet_ExistingId_ReturnsValue(): void
+    {
+        $mock = $this->createMock(\yii_app\records\Products1cPropType::class);
+        $service = $this->createServiceWithMockedDb(['prop-1' => $mock]);
+
+        $result = $service->get('prop-1');
+        $this->assertNotNull($result);
+    }
+
+    public function testGet_NonExistentId_ReturnsNull(): void
+    {
+        $mock = $this->createMock(\yii_app\records\Products1cPropType::class);
+        $service = $this->createServiceWithMockedDb(['prop-1' => $mock]);
+
+        $result = $service->get('non_existent');
+        $this->assertNull($result);
+    }
+
+    public function testExists_ExistingId_ReturnsTrue(): void
+    {
+        $mock = $this->createMock(\yii_app\records\Products1cPropType::class);
+        $service = $this->createServiceWithMockedDb(['prop-1' => $mock]);
+        $this->assertTrue($service->exists('prop-1'));
+    }
+
+    public function testExists_NonExistentId_ReturnsFalse(): void
+    {
+        $mock = $this->createMock(\yii_app\records\Products1cPropType::class);
+        $service = $this->createServiceWithMockedDb(['prop-1' => $mock]);
+        $this->assertFalse($service->exists('non_existent'));
+    }
+
+    public function testGetAll_ReturnsArray(): void
+    {
+        $fakeData = ['a' => 1, 'b' => 2];
+        $service = $this->createServiceWithMockedDb($fakeData);
+        $this->assertSame($fakeData, $service->getAll());
+    }
+
+    public function testGetAllIds_ReturnsKeys(): void
+    {
+        $fakeData = ['prop-1' => 'x', 'prop-2' => 'y', 'prop-3' => 'z'];
+        $service = $this->createServiceWithMockedDb($fakeData);
+        $this->assertSame(['prop-1', 'prop-2', 'prop-3'], $service->getAllIds());
+    }
+
+    public function testGetAll_SecondCall_ReturnsSameResult(): void
+    {
+        $fakeData = ['a' => 1];
+        $service = $this->createServiceWithMockedDb($fakeData);
+        $result1 = $service->getAll();
+        $result2 = $service->getAll();
+        $this->assertSame($result1, $result2);
+    }
+
+    public function testInvalidate_DoesNotThrow(): void
+    {
+        $service = new ProductPropTypeCacheService();
+        // Не должен падать даже без cache-компонента
+        $service->invalidate();
+        $this->assertTrue(true);
+    }
+
+    public function testGetAndExists_Consistent(): void
+    {
+        $mock1 = $this->createMock(\yii_app\records\Products1cPropType::class);
+        $mock2 = $this->createMock(\yii_app\records\Products1cPropType::class);
+        $fakeData = ['p1' => $mock1, 'p2' => $mock2];
+        $service = $this->createServiceWithMockedDb($fakeData);
+
+        foreach (array_keys($fakeData) as $id) {
+            $this->assertTrue($service->exists($id));
+            $this->assertNotNull($service->get($id));
+        }
+
+        $this->assertFalse($service->exists('missing'));
+        $this->assertNull($service->get('missing'));
+    }
+
+    /**
+     * Тест: get() и exists() делегируют в getAll() — проверяем пустой кеш.
+     */
+    public function testEmptyCache_GetReturnsNull_ExistsReturnsFalse(): void
+    {
+        $service = $this->createServiceWithMockedDb([]);
+        $this->assertNull($service->get('anything'));
+        $this->assertFalse($service->exists('anything'));
+    }
+}
-- 
2.39.5