<?php
    
    namespace artbox\catalog\controllers;

    use artbox\catalog\models\Brand;
    use artbox\catalog\models\Category;
    use artbox\catalog\models\Import;
    use artbox\catalog\models\Product;
    use artbox\catalog\models\ProductOptionExcl;
    use artbox\catalog\models\ProductOptionExclLang;
    use artbox\catalog\models\ProductOptionGroupExcl;
    use artbox\catalog\models\ProductOptionGroupExclLang;
    use artbox\catalog\models\ProductToCategory;
    use artbox\catalog\models\ProductToProductOptionExcl;
    use artbox\catalog\models\Variant;
    use artbox\core\models\Image;
    use artbox\core\models\Language;
    use yii\web\Controller;
    use PHPExcel_IOFactory;
    use yii\web\Response;
    use yii\filters\AccessControl;

    /**
     * Class ImportController
     *
     * @package artbox\catalog\controllers
     */
    class ImportController extends Controller
    {
        protected $addedCategories = [];
    
        protected $addedBrands = [];
    
        public function getViewPath()
        {
            return '@artbox/catalog/views/import';
        }
        public function behaviors()
        {
            return [
                'access' => [
                    'class' => AccessControl::className(),
                    'rules' => [
                        [
                            'actions' => [
                                'login',
                                'error',
                            ],
                            'allow'   => true,
                        ],
                        [
                            'allow' => true,
                            'roles' => [ '@' ],
                        ],
                    ],
                ],
            ];
        }
    
        public function actionIndex()
        {
            return $this->render('index');
        }
    
        public function actionImport($id)
        {
            \Yii::$app->response->format = Response::FORMAT_JSON;
    
            /**
             * @var Import[] $importModels
             */
            $importModels = Import::find()
                                  ->offset($id * 10)
                                  ->limit(10)
                                  ->with(
                                      [
                                          'variant.product',
                                          'categoryLang.category',
                                          'brandLang.brand',
                                          'image',
                                      ]
                                  )
                                  ->all();
    
            if (empty($importModels)) {
                return [
                    'percent' => 100,
                    'finish'  => true,
                ];
            }
    
            $percent = 0;
            foreach ($importModels as $model) {
                $category = $this->resolveCategory($model);
                $brand = $this->resolveBrand($model);
                $this->resolveImage($model);
    
                if (!empty($category)) {
                    $model->groups = $this->parseOptions($model);
                }
    
                if (empty($model->variant)) {
                    $this->createRecord($model);
                } else {
                    $this->updateRecord($model);
                }
    
                $percent = \Yii::$app->formatter->asDecimal(
                    ( ( $id + 1 ) * 1000 / Import::find()
                                                 ->count() ),
                    2
                );
    
            }
    
            return [
                'percent' => $percent,
                'finish'  => false,
            ];
        }
    
        public function actionUpload()
        {
            \Yii::$app->response->format = Response::FORMAT_JSON;
    
            $error = false;
            $files = [];
    
            $uploaddir = \Yii::getAlias('@storage/');
            foreach ($_FILES as $file) {
                if (move_uploaded_file($file[ 'tmp_name' ], $uploaddir . 'import.xlsx')) {
                    $files[] = $uploaddir . $file[ 'name' ];
                } else {
                    $error = true;
                }
            }
    
            $data = ( $error ) ? [ 'error' => 'There was an error uploading your files' ] : [ 'files' => $files ];
    
            $this->populateImportTable();
    
            return $data;
        }
    
        protected function populateImportTable()
        {
            $xlsx = PHPExcel_IOFactory::load(\Yii::getAlias('@storage/import.xlsx'));
    
            $xlsx->setActiveSheetIndex(0);
            $sheet = $xlsx->getActiveSheet();
            $rowIterator = $sheet->getRowIterator();
            $j = 0;
    
            $insert = [];
            foreach ($rowIterator as $row) {
                $j++;
                $cellIterator = $row->getCellIterator();
                $row = [];
                $i = 0;
                foreach ($cellIterator as $cell) {
                    /**
                     * @var \PHPExcel_Cell $cell
                     */
                    $i++;
                    $row[ $i ] = $cell->getValue();
                    if ($i > 14) {
                        break;
                    }
                }
    
                /**
                 * Getting needed cells
                 */
                $insert[] = [
                    $row[ 1 ],
                    $row[ 2 ],
                    $row[ 3 ],
                    $row[ 4 ],
                    $row[ 5 ],
                    $row[ 6 ],
                    $row[ 7 ],
                    $row[ 9 ] * 1 + $row[ 8 ] * 2 + $row[ 10 ] * 4,
                    $row[ 11 ],
                    basename($row[ 11 ]),
                    $row[ 12 ],
                    $row[ 13 ],
                    isset($row[ 14 ]) ? $row[ 14 ] : '',
                ];
    
                /**
                 * Stops when hitting bottom line
                 */
                if (empty($row[ 3 ])) {
                    break;
                }
    
            }
    
            $db = \Yii::$app->db;
    
            $db->createCommand()
               ->truncateTable('import')
               ->execute();
            $db->createCommand()
               ->batchInsert(
                   'import',
                   [
                       'category_name',
                       'brand_name',
                       'product_name',
                       'sku',
                       'description',
                       'price',
                       'price_old',
                       'mask',
                       'image_link',
                       'image_name',
                       'video',
                       'stock',
                       'characteristics',
                   ],
                   $insert
               )
               ->execute();
        }
    
        protected function resolveCategory(Import $import)
        {
            if (empty($import->categoryLang)) {
                if (!empty($import->category_name)) {
    
                    if (array_key_exists($import->category_name, $this->addedCategories)) {
                        $import->categoryId = $this->addedCategories[ $import->category_name ]->id;
                        return $this->addedCategories[ $import->category_name ];
                    }
    
                    $category = new Category();
                    $category->generateLangs();
                    foreach ($category->modelLangs as $categoryLang) {
                        $categoryLang->title = $import->category_name;
                    }
                    $category->saveWithLangs();
                    $this->addedCategories[ $import->category_name ] = $category;
                    $import->categoryId = $category->id;
                    return $category;
                }
            } else {
                $import->categoryId = $import->categoryLang->category->id;
                return $import->categoryLang->category;
            }
            return null;
        }
        protected function resolveBrand(Import $import)
        {
            if (empty($import->brandLang)) {
                if (!empty($import->brand_name)) {
    
                    if (array_key_exists($import->brand_name, $this->addedBrands)) {
                        $import->brandId = $this->addedBrands[ $import->brand_name ]->id;
                        return $this->addedBrands[ $import->brand_name ];
                    }
    
                    $brand = new Brand();
                    $brand->generateLangs();
                    foreach ($brand->modelLangs as $brandLang) {
                        $brandLang->title = $import->brand_name;
                    }
                    $brand->saveWithLangs();
    
                    $this->addedBrands[ $import->brand_name ] = $brand;
    
                    $import->brandId = $brand->id;
                    return $brand;
                }
            } else {
                $import->brandId = $import->brandLang->brand->id;
                return $import->brandLang->brand;
            }
            return null;
        }
        protected function resolveImage(Import $import)
        {
            if (empty($import->image)) {
                if (!empty($import->image_link)) {
                    $image = new Image();
                    $image->fileName = $import->image_name;
                    $image->fileHash = \Yii::$app->getSecurity()
                                                 ->generateRandomString(32);
    
                    if ($image->save()) {
                        $saveFileName = $image->id . "_" . $image->fileHash . "." . pathinfo(
                                $import->image_name,
                                PATHINFO_EXTENSION
                            );
                        file_put_contents(
                            \Yii::getAlias('@storage/') . $saveFileName,
                            file_get_contents($import->image_link)
                        );
                        $import->imageId = $image->id;
                    }
                }
            } else {
                $import->imageId = $import->image->id;
            }
        }
    
        protected function parseOptions(Import $import): array
        {
            $result = [];
            if (!empty($import->characteristics)) {
                $items = explode(';', $import->characteristics);
                foreach ($items as $item) {
                    $data = explode(':', $item);
                    if (!empty($data[ 0 ]) && !empty($data[ 1 ])) {
                        $group = $data[ 0 ];
                        $options = explode('~', $data[ 1 ]);
                        if (!empty($options)) {
                            $result[ $group ] = $options;
                        }
                    }
                }
            }
            return $result;
        }
    
        protected function updateRecord(Import $import)
        {
            /**
             * @var Variant $variant
             */
            $variant = $import->variant;
            $variant->price = $import->price;
            $variant->price_old = $import->price_old;
            $variant->stock = $import->stock;
            $variant->save();
    
            $product = $variant->product;
            $product->mask = $import->mask;
            $product->image_id = $import->imageId;
            $product->brand_id = $import->brandId;
            $product->video = $import->video;
            $product->save();
            foreach ($product->langs as $productLang) {
                $productLang->title = $import->product_name;
                $productLang->description = $import->description;
                $productLang->save();
            }
    
            $product->unlinkAll('categories', true);
            if (!empty($import->categoryId)) {
                $productToCategory = new ProductToCategory();
                $productToCategory->category_id = $import->categoryId;
                $productToCategory->product_id = $product->id;
                $productToCategory->save();
                if (!empty($import->groups)) {
                    $this->processGroups($import, $product);
                }
            }
        }
    
        protected function processGroups(Import $import, Product $product)
        {
            $optionIds = $this->saveGroups($import, $import->categoryId);
            if (!empty($optionIds)) {
                ProductToProductOptionExcl::deleteAll(
                    [
                        'product_id' => $product->id,
                    ]
                );
            }
            $batch = [];
            foreach ($optionIds as $optionId) {
                $batch[] = [
                    $product->id,
                    $optionId,
                ];
            }
            if (!empty($batch)) {
                \Yii::$app->db->createCommand()
                              ->batchInsert(
                                  'product_to_product_option_excl',
                                  [
                                      'product_id',
                                      'product_option_excl_id',
                                  ],
                                  $batch
                              )
                              ->execute();
            }
        }
    
        protected function createRecord(Import $import)
        {
            $product = new Product();
            $product->detachBehavior('defaultVariant');
            $product->generateLangs();
    
            foreach ($product->modelLangs as $modelLang) {
                $modelLang->title = $import->product_name;
                $modelLang->description = $import->description;
            }
    
            $product->mask = $import->mask;
            $product->image_id = $import->imageId;
            $product->brand_id = $import->brandId;
            $product->video = $import->video;
            $product->saveWithLangs();
    
            $variant = new Variant();
            $variant->product_id = $product->id;
            $variant->generateLangs();
    
            $variant->sku = $import->sku;
    
            $variant->price = $import->price;
            $variant->price_old = $import->price_old;
            $variant->stock = $import->stock;
            foreach ($variant->modelLangs as $lang) {
                $lang->title = $import->sku;
            }
            $variant->saveWithLangs();
    
            if (!empty($import->categoryId)) {
                $productToCategory = new ProductToCategory();
                $productToCategory->category_id = $import->categoryId;
                $productToCategory->product_id = $product->id;
                $productToCategory->save();
        
                if (!empty($import->groups)) {
                    $this->processGroups($import, $product);
                }
            }
        }
    
        protected function saveGroups(Import $import, int $categoryId)
        {
            /**
             * @var ProductOptionGroupExcl[] $groups
             */
            $groups = ProductOptionGroupExcl::find()
                                            ->joinWith('lang')
                                            ->joinWith('categories')
                                            ->andWhere(
                                                [
                                                    'category_id' => $categoryId,
                                                    'title'       => array_keys($import->groups),
                                                ]
                                            )
                                            ->indexBy(
                                                function ($row) {
                                                    /**
                                                     * @var ProductOptionGroupExcl $row
                                                     */
                                                    return $row->lang->title;
                                                }
                                            )
                                            ->all();
            $groupMap = array_fill_keys(array_keys($import->groups), null);
            foreach ($groups as $group) {
                $groupMap[ $group->lang->title ] = $group->id;
            }
            $forInsert = [];
            foreach ($groupMap as $title => $group) {
                if (!$group) {
                    $forInsert[] = $title;
                }
            }
            $activeLanguageIds = Language::find()
                                         ->where([ 'status' => true ])
                                         ->column();
            if (!empty($forInsert)) {
                $forInsertItems = [];
                foreach ($forInsert as $index => $title) {
                    $forInsertItems[] = [ true ];
                }
                $result = \Yii::$app->db->createCommand()
                                        ->batchInsert(
                                            'product_option_group_excl',
                                            [
                                                'is_filter',
                                            ],
                                            $forInsertItems
                                        );
                $result->setSql($result->getSql() . ' RETURNING id');
                $groupIds = $result->queryColumn();
                //                $forInsertItems = [];
                $forInsertCategories = [];
                foreach ($groupIds as $index => $groupId) {
                    $forInsertCategories[] = [
                        $categoryId,
                        $groupId,
                    ];
                    foreach ($activeLanguageIds as $activeLanguageId) {
                        $groupMap[ $forInsert[ $index ] ] = $groupId;
                        //                        $forInsertItems[] = [
                        //                            $groupId,
                        //                            $forInsert[ $index ],
                        //                            $activeLanguageId,
                        //                        ];
                        ( new ProductOptionGroupExclLang(
                            [
                                'product_option_group_excl_id' => $groupId,
                                'language_id'                  => $activeLanguageId,
                                'title'                        => $forInsert[ $index ],
                            ]
                        ) )->save(false);
                    }
                }
                \Yii::$app->db->createCommand()
                              ->batchInsert(
                                  'product_option_group_excl_to_category',
                                  [
                                      'category_id',
                                      'product_option_group_excl_id',
                                  ],
                                  $forInsertCategories
                              )
                              ->execute();
                //                \Yii::$app->db->createCommand()
                //                              ->batchInsert(
                //                                  'product_option_group_excl_lang',
                //                                  [
                //                                      'product_option_group_excl_id',
                //                                      'title',
                //                                      'language_id',
                //                                  ],
                //                                  $forInsertItems
                //                              )
                //                              ->execute();
            }
            return $this->saveOptions($import, $groupMap, $activeLanguageIds);
        }
    
        /**
         * $groupMap has following array:
         * groupName => groupId
         *
         * @param \artbox\catalog\models\Import $import
         * @param array                         $groupMap
         * @param array                         $activeLanguageIds
         *
         * @return array
         */
        protected function saveOptions(Import $import, array $groupMap, array $activeLanguageIds): array
        {
            $optionModelsQuery = ProductOptionExcl::find()
                                                  ->joinWith('lang');
            $i = 0;
            foreach ($import->groups as $group => $options) {
                if ($i) {
                    $optionModelsQuery->orWhere(
                        [
                            'product_option_group_excl_id' => $groupMap[ $group ],
                            'value'                        => $options,
                        ]
                    );
                } else {
                    $optionModelsQuery->andWhere(
                        [
                            'product_option_group_excl_id' => $groupMap[ $group ],
                            'value'                        => $options,
                        ]
                    );
                }
                $i++;
            }
            $optionModelsQuery->indexBy(
                function ($row) {
                    /**
                     * @var ProductOptionExcl $row
                     */
                    return $row->lang->value;
                }
            );
            /**
             * @var ProductOptionExcl[] $optionModels
             */
            $optionModels = $optionModelsQuery->all();
            if (!empty($import->groups)) {
                $forInsert = [];
                $optionIds = [];
                $forInsertItems = [];
                foreach ($import->groups as $group => $options) {
                    foreach ($options as $option) {
                        if (array_key_exists(
                                $option,
                                $optionModels
                            ) && $optionModels[ $option ]->groupId == $groupMap[ $group ]
                        ) {
                            $optionIds[] = $optionModels[ $option ]->id;
                        } else {
                            $forInsert[] = $option;
                            $forInsertItems[] = [
                                $groupMap[ $group ],
                                true,
                            ];
                        }
                    }
                }
                if (!empty($forInsertItems)) {
                    $result = \Yii::$app->db->createCommand()
                                            ->batchInsert(
                                                'product_option_excl',
                                                [
                                                    'product_option_group_excl_id',
                                                    'status',
                                                ],
                                                $forInsertItems
                                            );
                    $result->setSql($result->getSql() . ' RETURNING id');
                    $resultIds = $result->queryColumn();
                    //                    $forInsertItems = [];
                    foreach ($resultIds as $index => $resultId) {
                        foreach ($activeLanguageIds as $activeLanguageId) {
                            //                            $forInsertItems[] = [
                            //                                $resultId,
                            //                                $forInsert[ $index ],
                            //                                $activeLanguageId,
                            //                            ];
                            ( new ProductOptionExclLang(
                                [
                                    'product_option_excl_id' => $resultId,
                                    'value'                  => $forInsert[ $index ],
                                    'language_id'            => $activeLanguageId,
                                ]
                            ) )->save(false);
                        }
                    }
                    //                    if (!empty($forInsertItems)) {
                    //                        \Yii::$app->db->createCommand()
                    //                                      ->batchInsert(
                    //                                          'product_option_excl_lang',
                    //                                          [
                    //                                              'product_option_excl_id',
                    //                                              'value',
                    //                                              'language_id',
                    //                                          ],
                    //                                          $forInsertItems
                    //                                      )
                    //                                      ->execute();
                    //                    }
                }
                if (!empty($resultIds)) {
                    $optionIds = array_merge($optionIds, $resultIds);
                }
                return $optionIds;
            }
            return [];
        }
    
        public function actionTest($id)
        {
            \Yii::$app->response->format = Response::FORMAT_JSON;
    
            $array = [
                [
                    'percent' => 10,
                    'finish'  => false,
                ],
                [
                    'percent' => 17,
                    'finish'  => false,
                ],
                [
                    'percent' => 31,
                    'finish'  => false,
                ],
                [
                    'percent' => 44,
                    'finish'  => false,
                ],
                [
                    'percent' => 67,
                    'finish'  => false,
                ],
                [
                    'percent' => 80,
                    'finish'  => false,
                ],
                [
                    'percent' => 100,
                    'finish'  => true,
                ],
            ];
    
            return $array[ $id ];
        }
    
    
        public function actionPrice()
        {
            return $this->render('price');
        }
        public function actionPriceUpload(){
            \Yii::$app->response->format = Response::FORMAT_JSON;
    
            $error = false;
            $files = [];
    
            $uploaddir = \Yii::getAlias('@storage/');
            foreach ($_FILES as $file) {
                if (move_uploaded_file($file[ 'tmp_name' ], $uploaddir . 'price_import.xlsx')) {
                    $files[] = $uploaddir . $file[ 'name' ];
                } else {
                    $error = true;
                }
            }
    
            $data = ( $error ) ? [ 'error' => 'There was an error uploading your files' ] : [ 'files' => $files ];
    
            $this->ImportPrice();
    
            return $data;
        }
        
        // document structure sku price_old price
        public function ImportPrice(){
            $xlsx = PHPExcel_IOFactory::load(\Yii::getAlias('@storage/price_import.xlsx'));
            $xlsx->setActiveSheetIndex(0);
            $sheet = $xlsx->getActiveSheet();
            $rowIterator = $sheet->getRowIterator();
            $j = 0;
            $insert = [];
    
            foreach ($rowIterator as $row) {
                $j++;
                $cellIterator = $row->getCellIterator();
                $row = [];
                $i = 0;
                foreach ($cellIterator as $cell) {
                    /**
                     * @var \PHPExcel_Cell $cell
                     */
                    $i++;
                    $row[ $i ] = $cell->getValue();
                    if ($i > 2) {
                        break;
                    }
                }
                $insert[] = $row;
        
        
                if (empty($row[ 1 ])) {
                    break;
                }
        
            }
            \Yii::$app->db->createCommand()->truncateTable('price_upload')->execute();
            \Yii::$app->db->createCommand()->batchInsert('price_upload',['sku', 'price_old', 'price'], $insert)->execute();
    
            $transaction = \Yii::$app->db->beginTransaction();
            try {
                \Yii::$app->db->createCommand(
                    'UPDATE variant SET price=pu.price
                                            , price_old=pu.price_old
                                             FROM (
                                                SELECT * FROM price_upload
                                             ) pu
                                             WHERE variant.sku = pu.sku'
                )
                              ->execute();
                $transaction->commit();
            } catch (\Exception $e) {
                $transaction->rollBack();
                throw $e;
            } catch (\Throwable $e) {
                $transaction->rollBack();
            }
            
           // print_r($insert);
        }
    }