<?php

declare(strict_types=1);

namespace App\Model;

use Mosaico\Model\BaseModel;

class MigrateMagentoProductModel extends BaseModel
{
	public function getProducts(string $type_id): bool|object
	{
	    $query = $this->connectMagento()->query("
	    		SELECT
	    		DISTINCT(entity_id) FROM catalog_product_entity
	    		WHERE type_id = '" . $type_id . "'
	    	");
	    return $query;
	}

	public function getImageProducts(): bool|object
	{
	    $query = $this->connectMagento()->query("
	    		SELECT
	    		DISTINCT(entity_id) FROM catalog_product_entity
	    		WHERE type_id = 'simple'
	    		OR type_id = 'configurable'
	    	");
	    return $query;
	}

	public function getProductDetails(int $entity_id): bool|object
	{
	    $query = $this->connectMagento()->query("
	    		SELECT
	    		A.entity_id,
	    		A.sku,
	    		B.value as name,
	    		C.value as image,
	    		D.value as price,
	    		E.value as status,
	    		F.value as marca,
	    		G.value as meta_keywords,
	    		H.value as meta_description,
	    		I.value as meta_title,
	    		J.value as weight,
	    		K.value as width,
	    		L.value as height,
	    		Z.categories
	    		FROM catalog_product_entity A
	    		LEFT JOIN
	    		(
	    			SELECT row_id, value FROM catalog_product_entity_varchar
	    			WHERE attribute_id = 217
	    		) as B
	    		ON B.row_id = A.row_id
	    		LEFT JOIN
	    		(
	    			SELECT row_id, value FROM catalog_product_entity_varchar
	    			WHERE attribute_id = 259
	    		) as C
	    		ON C.row_id = A.row_id
	    		LEFT JOIN
	    		(
	    			SELECT row_id, value FROM catalog_product_entity_decimal
	    			WHERE attribute_id = 229
	    		) as D
	    		ON D.row_id = A.row_id
	    		JOIN
	    		(
	    			SELECT row_id, value FROM catalog_product_entity_int
	    			WHERE attribute_id = 289
	    		) as E
	    		ON E.row_id = A.row_id
	    		LEFT JOIN
	    		(
	    			SELECT row_id, value FROM catalog_product_entity_int
	    			WHERE attribute_id = 557
	    		) as F
	    		ON F.row_id = A.row_id
	    		LEFT JOIN
	    		(
	    			SELECT row_id, value FROM catalog_product_entity_text
	    			WHERE attribute_id = 253
	    		) AS G
	    		ON G.row_id = A.row_id
	    		LEFT JOIN
	    		(
	    			SELECT row_id, value FROM catalog_product_entity_varchar
	    			WHERE attribute_id = 256
	    		) AS H
	    		ON H.row_id = A.row_id
	    		LEFT JOIN
	    		(
	    			SELECT row_id, value FROM catalog_product_entity_varchar
	    			WHERE attribute_id = 250
	    		) AS I
	    		ON I.row_id = A.row_id
	    		LEFT JOIN
	    		(
	    			SELECT row_id, value FROM catalog_product_entity_decimal
	    			WHERE attribute_id = 244
	    		) AS J
	    		ON J.row_id = A.row_id
	    		LEFT JOIN
	    		(
	    			SELECT row_id, value FROM catalog_product_entity_decimal
	    			WHERE attribute_id = 535
	    		) AS K
	    		ON K.row_id = A.row_id
	    		LEFT JOIN
	    		(
	    			SELECT row_id, value FROM catalog_product_entity_decimal
	    			WHERE attribute_id = 538
	    		) AS L
	    		ON L.row_id = A.row_id
	    		LEFT JOIN
	    		(
	    			SELECT GROUP_CONCAT(category_id ORDER BY category_id ASC SEPARATOR ', ') as categories
	    			FROM catalog_category_product WHERE product_id = " . $entity_id . "
	    		) as Z
	    		ON 1 = 1
	    		WHERE A.row_id =
	    		(
	    			SELECT max(row_id) as row_id FROM catalog_product_entity
	    			WHERE entity_id = " . $entity_id . "
	    		)
	    		AND E.value = 1
	    	");
	    return $query;
	}

	public function getProductConfigurableDetails(int $entity_id): bool|object
	{
	    $query = $this->connectMagento()->query("
	    		SELECT
	    		A.entity_id,
	    		A.sku,
	    		B.value as name,
	    		C.value as image,
	    		D.value as price,
	    		E.value as status,
	    		F.value as marca,
	    		G.categories
	    		FROM catalog_product_entity A
	    		LEFT JOIN
	    		(
	    			SELECT row_id, value FROM catalog_product_entity_varchar
	    			WHERE attribute_id = 217
	    		) as B
	    		ON B.row_id = A.row_id
	    		LEFT JOIN
	    		(
	    			SELECT row_id, value FROM catalog_product_entity_varchar
	    			WHERE attribute_id = 259
	    		) as C
	    		ON C.row_id = A.row_id
	    		JOIN catalog_product_entity H
	    		ON H.sku = REPLACE(A.sku, '-1', '')
	    		LEFT JOIN
	    		(
	    			SELECT row_id, value FROM catalog_product_entity_decimal
	    			WHERE attribute_id = 229
	    		) as D
	    		ON D.row_id = H.row_id
	    		JOIN
	    		(
	    			SELECT row_id, value FROM catalog_product_entity_int
	    			WHERE attribute_id = 289
	    		) as E
	    		ON E.row_id = A.row_id
	    		LEFT JOIN
	    		(
	    			SELECT row_id, value FROM catalog_product_entity_int
	    			WHERE attribute_id = 557
	    		) as F
	    		ON F.row_id = A.row_id
	    		LEFT JOIN
	    		(
	    			SELECT GROUP_CONCAT(category_id ORDER BY category_id ASC SEPARATOR ', ') as categories
	    			FROM catalog_category_product WHERE product_id = " . $entity_id . "
	    		) as G
	    		ON 1 = 1
	    		WHERE A.row_id =
	    		(
	    			SELECT max(row_id) as row_id FROM catalog_product_entity
	    			WHERE entity_id = " . $entity_id . "
	    		)
	    		AND E.value = 1
	    	");
	    return $query;
	}

	public function insertProducts(array $data): void
    {
        $query = $this->connect()->query("
            INSERT IGNORE INTO catalogo_productos SET
            id_magento 		= " . $data['entity_id'] . ",
	        id_marca 		= " . $data['marca'] . ",
            sku 			= '" . $data['sku'] . "',
            nombre_producto = '" . $data['name'] . "',
            image 			= '" . $data['image'] . "',
            precio 			= " . $data['price'] . "
            ON DUPLICATE KEY UPDATE
            meta_keywords 	= '" . $data['meta_keywords'] . "',
            meta_description = '" . $data['meta_description'] . "',
            meta_title 		= '" . $data['meta_title'] . "',
            weight			= " . $data['weight'] . ",
            height			= " . $data['height'] . ",
            width			= " . $data['width'] . "
            ");
    }

    public function addProductCategories(int $id_magento, string $categories): void
    {
    	$query = $this->connect()->query("
    		UPDATE catalogo_productos SET
    		ids_categorias = 
    		(
		    	SELECT GROUP_CONCAT(id_bigcommerce ORDER BY id_bigcommerce ASC SEPARATOR ', ') as categories
		    	FROM catalogo_categorias 
		    	WHERE id_magento in(" . $categories . ")
    		)
    		WHERE id_magento = "  . $id_magento . "
	    	");
    }

    public function getProductImagesGallery(int $entity_id): bool|object
    {
    	$query = $this->connectMagento()->query("
    		SELECT 
    		value_id,
    		value as image_path
    		FROM  catalog_product_entity_media_gallery 
    		WHERE value_id IN
    		(
    		SELECT value_id 
    		FROM catalog_product_entity_media_gallery_value_to_entity A
    		JOIN catalog_product_entity B
    		ON A.row_id = B.row_id
    		AND B.row_id = 
    		(
    			SELECT max(row_id) as row_id FROM catalog_product_entity
    			WHERE entity_id = " . $entity_id . "
    		)
    		JOIN
    		(
    			SELECT row_id, value FROM catalog_product_entity_int
    			WHERE attribute_id = 289
    		) as C
    		ON C.row_id = B.row_id
    		AND C.value = 1
    		)
    	");

    	return $query;
    }

    public function insertProductImages(array $data): void
    {
        $query = $this->connect()->query("
            INSERT IGNORE INTO catalogo_producto_imagenes SET
            id_magento 			= " . $data['value_id'] . ",
	        id_producto_magento = " . $data['entity_id'] . ",
            image_path 			= '" . $data['image_path'] . "'
            ");
    }

    public function getTextProducts(): bool|object
    {
        $query = $this->connect()->query("
        		SELECT id_magento FROM catalogo_productos
        		WHERE id_bigcommerce IS NOT NULL
        	");
        return $query;
    }

    public function getProductDescription(int $entity_id): bool|object
    {
        $query = $this->connectMagento()->query("
        		SELECT
        		A.entity_id,
        		A.sku,
        		B.value as description
        		FROM catalog_product_entity A
        		LEFT JOIN
        		(
        			SELECT row_id, value FROM catalog_product_entity_text
        			WHERE attribute_id = 223
        		) as B
        		ON B.row_id = A.row_id
        		
        		WHERE A.row_id =
        		(
        			SELECT max(row_id) as row_id FROM catalog_product_entity
        			WHERE entity_id = " . $entity_id . "
        		)
        	");
        return $query;
    }

}