<?php

use Magento\Framework\App\Bootstrap;
require __DIR__ . '/app/bootstrap.php';

$bootstrap = Bootstrap::create(BP, $_SERVER);
$obj = $bootstrap->getObjectManager();

$state = $obj->get('Magento\Framework\App\State');
$state->setAreaCode('frontend');

$categoryFactory = $obj->create('Magento\Catalog\Model\CategoryFactory');
$category = $categoryFactory->create()->load(2159);
$categoryUrl = $category->getUrl();
$urlHelper = $obj->create('Amasty\ShopbySeo\Helper\Url');
$productFactory = $obj->create('Magento\Catalog\Model\ResourceModel\ProductFactory');
$resourceConnection = $obj->create('Magento\Framework\App\ResourceConnection');
$connection  = $resourceConnection->getConnection();
$amasty_amshopby_page_table = $connection->getTableName('amasty_amshopby_page');
$amasty_amshopby_page_store_table = $connection->getTableName('amasty_amshopby_page_store');
$serializer = $obj->create('Magento\Framework\Serialize\SerializerInterface');

$collection = $category->getProductCollection()->addAttributeToSelect('*');
$productsAttribute = [];

define('AUTO_BRAND', 'auto_brand');
define('AUTO_MODEL', 'auto_model');
define('AUTO_STRING', ' auto ');

foreach ($collection as $product) {
    if (!isset($productsAttribute[$product->getData(AUTO_BRAND)]) || !in_array($product->getData(AUTO_MODEL), $productsAttribute[$product->getData(AUTO_BRAND)])) {
        $productsAttribute[$product->getData(AUTO_BRAND)][] = $product->getData(AUTO_MODEL);
    }
}

//queries for marca
foreach ($productsAttribute as $marcaOptionId => $info) {
    $poductReource = $productFactory->create();
    $autoBrand = $poductReource->getAttribute(AUTO_BRAND);
    $optionText = $autoBrand->getSource()->getOptionText($marcaOptionId);

    $pageId = NULL;
    $position = 'replace';
    $url = $urlHelper->seofyUrl($categoryUrl.'?auto_brand='.$marcaOptionId);
    $title = $category->getName().AUTO_STRING.$optionText;
    $description = null;
    $metaTitle = $category->getName().AUTO_STRING.$optionText.' - Vezi oferte si comanda online';
    $metaKeywords = null;
    $metaDescription = 'Descopera gama larga de '.$category->getName().AUTO_STRING.$optionText.' de pe Autogedal ⭐ Consulta-te cu profesionistii din domeniu ⭐ Livrare si retur gratuit';
    //$conditions = "'".'["{\"filter\":\"' . $autoBrand->getId() . '\",\"value\":\"' . $marcaOptionId . '\"}"]'."'";
    $conditions = [
        [
            "filter" => $autoBrand->getId(),
            "value" => (string)$marcaOptionId
        ]
    ];
    $conditions = addslashes(json_encode($conditions));
    $conditions = str_replace('[{', '["{', $conditions);
    $conditions = str_replace('}]', '}"]', $conditions);

    //[\"{\"filter\":\"747\",\"value\":\"11620\"}\"]
    $categories = $category->getId();
    $topBlockId = null;
    $bottomBlockId = 137;
    $image = null;
    
    $page = [
        'page_id' => $pageId,
        'position' => 'replace',
        'url' => $url,
        'title' => $title,
        'description' => $description,
        'meta_title' => $metaTitle,
        'meta_keywords' => $metaKeywords,
        'meta_description' => $metaDescription,
        'conditions' => $conditions,
        'categories' => $categories,
        'top_block_id' => $topBlockId,
        'bottom_block_id' => $bottomBlockId,
        'image' => $image
    ];
    
    $connection->insert($amasty_amshopby_page_table, $page);

    $pageStore = [
        'page_id' => $connection->lastInsertId($amasty_amshopby_page_table),
        'store_id' => 0
    ];

    $connection->insert($amasty_amshopby_page_store_table, $pageStore);

}

//queries for marca&model
foreach ($productsAttribute as $marcaOptionId => $info) {
    foreach($info as $key => $modelOptionId){
        $poductReource = $productFactory->create();
        $autoBrand = $poductReource->getAttribute(AUTO_BRAND);
        $autoModel = $poductReource->getAttribute(AUTO_MODEL);

        $optionTextMarca = $autoBrand->getSource()->getOptionText($marcaOptionId);
        $optionTextModel = $autoModel->getSource()->getOptionText($modelOptionId);

        $pageId = NULL;
        $position = 'replace';
        $url = $urlHelper->seofyUrl($categoryUrl.'?auto_brand='.$marcaOptionId.'&'.AUTO_MODEL.'='.$modelOptionId);
        $title = $category->getName().AUTO_STRING.$optionTextMarca.' '.$optionTextModel;
        $description = null;
        $metaTitle = $category->getName().AUTO_STRING.$optionTextMarca.' '.$optionTextModel;
        $metaKeywords = null;
        $metaDescription = 'Descopera gama larga de '.$category->getName().AUTO_STRING.$optionTextMarca.' '.$optionTextModel.' de la Autogedal ⭐ Consulta-te cu profesionistii din domeniu ⭐ Prezenti in toata tara';
        //$conditions = "'".'["{\"filter\":\"' . $autoBrand->getId() . '\",\"value\":\"' . $marcaOptionId . '\"}"]'."'";
        $conditions = [
            [
                "filter" => $autoBrand->getId(),
                "value" => (string)$marcaOptionId
            ],
            [
                "filter" => $autoModel->getId(),
                "value" => (string)$modelOptionId
            ]
        ];
        $conditions = addslashes(json_encode($conditions));
        $conditions = str_replace('[{', '["{', $conditions);
        $conditions = str_replace('}]', '}"]', $conditions);
        $conditions = str_replace('},{', '}","{', $conditions);
    
        //[\"{\"filter\":\"747\",\"value\":\"11620\"}\"]
        $categories = $category->getId();
        $topBlockId = null;
        $bottomBlockId = null;
        $image = null;
        
        $page = [
            'page_id' => $pageId,
            'position' => 'replace',
            'url' => $url,
            'title' => $title,
            'description' => $description,
            'meta_title' => $metaTitle,
            'meta_keywords' => $metaKeywords,
            'meta_description' => $metaDescription,
            'conditions' => $conditions,
            'categories' => $categories,
            'top_block_id' => $topBlockId,
            'bottom_block_id' => $bottomBlockId,
            'image' => $image
        ];
        
        $connection->insert($amasty_amshopby_page_table, $page);

        $pageStore = [
            'page_id' => $connection->lastInsertId($amasty_amshopby_page_table),
            'store_id' => 0
        ];

        $connection->insert($amasty_amshopby_page_store_table, $pageStore);
    }
}

//INSERT INTO `amasty_amshopby_page` (`page_id`, `position`, `url`, `title`, `description`, `meta_title`, `meta_keywords`, `meta_description`, `conditions`, `categories`, `top_block_id`, `bottom_block_id`, `image`)
    //$insertQuery = 'INSERT INTO `amasty_amshopby_page` (`page_id`, `position`, `url`, `title`, `description`, `meta_title`, `meta_keywords`, `meta_description`, `conditions`, `categories`, `top_block_id`, `bottom_block_id`, `image`)';
    //$values[] = 'VALUES (NULL,"'.$position.'","'.$url.'","'.$title.'",NULL,"'.$metaTitle.'",NULL,"'.$metaDescription.'",'.$conditions.','.$categories.',NULL,'.$bottomBlockId.',NULL)';
//VALUES (NULL,"replace","http://autogedal.loc/carlige-de-remorcare/marca-polestar.html","Carlige de remorcare Polestar",NULL,"Carlige de remorcare Polestar - Vezi oferte si comanda online",NULL,"Descopera gama larga de Carlige de remorcare auto Polestar de pe Autogedal ⭐ Consulta-te cu profesionistii din domeniu ⭐ Livrare si retur gratuit","["{\\"filter\\":\\"747\\",\\"value\\":\\"15486\\"}"]",59,NULL,137,NULL)

//echo '<pre>' . var_export($values, true) . '</pre>';