-- Identificare i valori orfani
SELECT * 
FROM wp_termmeta 
WHERE term_id NOT IN (SELECT term_id FROM wp_terms);

-- Eliminare i valori orfani
DELETE FROM wp_termmeta 
WHERE term_id NOT IN (SELECT term_id FROM wp_terms);

SELECT t.term_id AS category_id, t.name AS category_name, meta.meta_value AS magento_category_id
FROM wpcv_terms t
INNER JOIN wpcv_term_taxonomy tt ON t.term_id = tt.term_id
LEFT JOIN wpcv_termmeta meta ON t.term_id = meta.term_id AND meta.meta_key = '_id_categoria_magento'
WHERE tt.taxonomy = 'product_cat';

CREATE TABLE `prodotti_attributi` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `entity_id` int(11) DEFAULT NULL,
  `sku` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `attribute_code` varchar(255) DEFAULT NULL,
  `value` text,
  PRIMARY KEY (`id`),
  KEY `idx_entity_id` (`entity_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1245674 DEFAULT CHARSET=utf8

INSERT INTO prodotti_attributi (entity_id, sku, created_at, updated_at, attribute_code, value)
SELECT
    e.entity_id,
    e.sku,
    e.created_at,
    e.updated_at,
    ea.attribute_code,
    CASE ea.backend_type
        WHEN 'varchar' THEN ev.value
        WHEN 'int' THEN ei.value
        WHEN 'decimal' THEN ed.value
        WHEN 'text' THEN et.value
        WHEN 'datetime' THEN edate.value
        ELSE NULL
    END AS value
FROM catalog_product_entity AS e
JOIN eav_attribute AS ea ON ea.entity_type_id = 4 -- 4 is typically for products in Magento 2
LEFT JOIN catalog_product_entity_varchar AS ev ON ev.attribute_id = ea.attribute_id AND ev.entity_id = e.entity_id AND ea.backend_type = 'varchar'
LEFT JOIN catalog_product_entity_int AS ei ON ei.attribute_id = ea.attribute_id AND ei.entity_id = e.entity_id AND ea.backend_type = 'int'
LEFT JOIN catalog_product_entity_decimal AS ed ON ed.attribute_id = ea.attribute_id AND ed.entity_id = e.entity_id AND ea.backend_type = 'decimal'
LEFT JOIN catalog_product_entity_text AS et ON et.attribute_id = ea.attribute_id AND et.entity_id = e.entity_id AND ea.backend_type = 'text'
LEFT JOIN catalog_product_entity_datetime AS edate ON edate.attribute_id = ea.attribute_id AND edate.entity_id = e.entity_id AND ea.backend_type = 'datetime';

CREATE INDEX idx_entity_id ON prodotti_attributi (entity_id);

SELECT
    cpe.entity_id AS 'Product_ID', 
    cpev.value AS 'Product_Name', 
    GROUP_CONCAT(DISTINCT ccv.value) AS 'Category_Names',
    GROUP_CONCAT(DISTINCT ccp.category_id) AS 'Category_IDs',
    MAX(ur.request_path) AS 'Product_URL'
FROM
    catalog_product_entity AS cpe
JOIN
    catalog_product_entity_varchar AS cpev ON cpe.entity_id = cpev.entity_id AND cpev.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product'))
LEFT JOIN
    catalog_category_product AS ccp ON cpe.entity_id = ccp.product_id
LEFT JOIN
    catalog_category_entity_varchar AS ccv ON ccp.category_id = ccv.entity_id AND ccv.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_category'))
LEFT JOIN
    url_rewrite AS ur ON ur.entity_id = cpe.entity_id AND ur.entity_type = 'product' AND ur.redirect_type = 0 AND ur.store_id = 1
GROUP BY
    cpe.entity_id, cpev.value;

SELECT
  cce.entity_id AS category_id,
  ccev.value AS category_name,
  cce.parent_id,
  CASE
    WHEN cce.parent_id = 1 THEN 'Principale'
    ELSE 'Sottocategoria'
  END AS tipo_categoria,
  ccei.value AS is_active
FROM
  catalog_category_entity AS cce
  JOIN catalog_category_entity_varchar AS ccev ON cce.entity_id = ccev.entity_id
  JOIN eav_attribute AS ea ON ccev.attribute_id = ea.attribute_id
  AND ea.attribute_code = 'name'
  JOIN catalog_category_entity_int AS ccei ON cce.entity_id = ccei.entity_id
  JOIN eav_attribute AS eai ON ccei.attribute_id = eai.attribute_id
  AND eai.attribute_code = 'is_active'
WHERE
  ccev.store_id = 0
  AND ccei.value = 1
GROUP by
  category_id;