DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_AIEO_CombineVariationVitalsInProductVitals`()
BEGIN
              
              
              INSERT INTO wp_aieo_core_product_vitals (
                  image_url,
                  product_name,
                  product_id,
                  parent_product_id,
                  parent_product_name,
                  parent_sku,
                  brand,
                  brand_id,
                  brand_slug,
                  menu_id,
                  menu_category,
                  head_id,
                  head_category,
                  subcat_id,
                  sub_category,
                  sku,
                  gtin,
                  erp_id,
                  price,
                  regular_price,
                  onsale,
                  role_special_pricing,
                  product_url,
                  link_to_view,
                  link_to_cart,
                  product_description,
                  weight,
                  length,
                  width,
                  height,
                  size,
                  color,
                  stock
              )
              SELECT DISTINCT 
                  CONCAT("https://femme-fatale.local",MAX(vv.imagepath)) AS image_url,
                  MAX(p.post_title) as product_name, 
                  p.ID as product_id,
                  MAX(vv.parent_id) AS parent_product_id, 
                  MAX(pmmpp.post_title) as parent_name,
                  MAX(pmmpps.meta_value) as parent_sku,
                  MAX(tatv.brand) as brand,
                  MAX(tatv.brand_id) as brand_id,
                  MAX(tatv.brand_slug) as brand_slug,
                  MAX(tapcv.top_category_id) as menu_id,
                  MAX(tapcv.top_category) as menu_category,
                  MAX(tapcv.second_level_category_id) as head_id,
                  MAX(tapcv.second_level_category) as head_category,
                  MAX(tapcv.third_level_category_id) as subcat_id,
                  MAX(tapcv.third_level_category) as sub_category,
                  MAX(vv.sku) as sku, 
                  MAX(vv.gtin) as gtin, 
                  MAX(vv.erp_id) as erp_id, 
                  CAST(MAX(REPLACE(vv.price, ',', '.')) AS DECIMAL(12,2)) as price, 
                  CAST(MAX(REPLACE(vv.regular_price, ',', '.')) AS DECIMAL(12,2)) as regular_price,
                  MAX(vv.onsale) AS onsale,
                  MAX(vv.role_special_pricing) AS role_special_pricing,
                  CONCAT('https://femme-fatale.local/?p=', CAST(p.id AS CHAR))  AS product_url,
                  CONCAT('<a href="https://femme-fatale.local/?p=', CAST(p.id AS CHAR) , '">', "Product Details"  COLLATE utf8mb4_bin ,'</a>') as link_to_view,
                  CASE WHEN MAX(vv.stock) > 0 THEN
                      CONCAT('<div class="loop-button-wrap button-layout2"><a href="?add-to-cart=', CAST(p.id AS CHAR), '" data-quantity="1" class="button product_type_simple add_to_cart_button ajax_add_to_cart" data-product_id="', CAST(p.id AS CHAR), ' data-product_sku="', MAX(vv.sku), '" rel="nofollow">', "Αγορά"  COLLATE utf8mb4_bin  , '</a></div>')     
                          ELSE ''
                      END AS link_to_cart,
                  LEFT(MAX(pmmpp.post_excerpt), 160) AS short_description,
                  MAX(vv.weight) AS weight,
                  MAX(vv.length) as length,
                  MAX(vv.width) as width,
                  MAX(vv.height) as height,   
                  MAX(tatv.size) AS size, 
                  MAX(tatv.color) AS color,
                  MAX(vv.stock) AS stock
              FROM 
              wp_posts AS p
              JOIN wp_aieo_core_variation_vitals AS vv ON p.id = vv.product_id
              JOIN wp_aieo_core_product_attributes AS tatv ON p.post_parent = tatv.parent_product_id
              JOIN wp_aieo_core_product_category_hierarchy AS tapcv ON p.post_parent = tapcv.product_id
              JOIN wp_posts AS pmmpp ON pmmpp.ID = p.post_parent
              LEFT JOIN wp_postmeta AS pmmpps ON pmmpp.ID = pmmpps.post_id AND pmmpps.meta_key = '_sku'
              WHERE p.post_type = 'product_variation'
              AND p.post_status = 'publish'
              GROUP BY p.ID;
          END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_AIEO_InsertCoreProductAttributes`(
                IN brandTaxonomy VARCHAR(255), 
                IN sizeTaxonomies VARCHAR(255), 
                IN colorTaxonomies VARCHAR(255)
            )
BEGIN

                TRUNCATE TABLE wp_aieo_core_product_attributes;
                
                SET @sizeTaxQuery = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS temp_size_tax (object_id INT, 
    name VARCHAR(255) COLLATE utf8mb4_unicode_ci) SELECT trs.object_id, ts.name FROM wp_term_relationships trs JOIN wp_term_taxonomy xs ON xs.term_taxonomy_id = trs.term_taxonomy_id JOIN wp_terms ts ON ts.term_id = xs.term_id WHERE FIND_IN_SET(xs.taxonomy, ''', sizeTaxonomies, ''') > 0;');
                SET @colorTaxQuery = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS temp_color_tax (object_id INT,
    name VARCHAR(255) COLLATE utf8mb4_unicode_ci
) SELECT trc.object_id, tc.name FROM wp_term_relationships trc JOIN wp_term_taxonomy xc ON xc.term_taxonomy_id = trc.term_taxonomy_id JOIN wp_terms tc ON tc.term_id = xc.term_id WHERE FIND_IN_SET(xc.taxonomy, ''', colorTaxonomies, ''') > 0;');

                PREPARE stmt FROM @sizeTaxQuery;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;

                PREPARE stmt FROM @colorTaxQuery;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;

                
                INSERT INTO wp_aieo_core_product_attributes 
                    (product_id, parent_product_id, effective_product_id, brand_id, brand, brand_slug, product_stock, product_count, size, color)
                SELECT 
                    CASE 
                        WHEN p.id IS NULL THEN tr.object_id 
                        ELSE p.id 
                    END AS product_id, 
                    tr.object_id AS parent_product_id, 
                    tr.object_id AS effective_product_id, 
                    t.term_id AS brand_id, 
                    t.name AS brand, 
                    t.slug AS brand_slug,
                    '0' AS product_stock,  
                    '0' AS product_count,
                    MAX(ts.name COLLATE utf8mb4_unicode_ci) AS size,
                    MAX(tc.name COLLATE utf8mb4_unicode_ci) AS color
                FROM 
                wp_posts p
                JOIN 
                wp_term_relationships tr ON (p.id = tr.object_id OR p.post_parent = tr.object_id) AND p.post_type LIKE '%product%' AND p.post_status = 'publish'
                JOIN 
                wp_term_taxonomy xt ON xt.term_taxonomy_id = tr.term_taxonomy_id AND xt.taxonomy COLLATE utf8mb4_unicode_ci = brandTaxonomy
                JOIN 
                wp_terms t ON t.term_id = xt.term_id
                LEFT JOIN 
                    temp_size_tax ts ON p.id = ts.object_id
                LEFT JOIN 
                    temp_color_tax tc ON p.id = tc.object_id
                GROUP BY 
                    p.id, tr.object_id, t.term_id, t.name, t.slug
                ORDER BY 
                    p.id, t.slug DESC;

                -- Drop temporary tables
                DROP TEMPORARY TABLE IF EXISTS temp_size_tax;
                DROP TEMPORARY TABLE IF EXISTS temp_color_tax;
            END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_AIEO_InsertProductCategoryHierarchy`()
BEGIN
            -- Truncate the table directly
            TRUNCATE TABLE wp_aieo_core_product_category_hierarchy;

            INSERT INTO wp_aieo_core_product_category_hierarchy
                (product_id, top_category_id, top_category, second_level_category_id, second_level_category, third_level_category_id, third_level_category)
            SELECT
                p.ID AS product_id,
                tc.top_category_id,
                tc.top_category,
                sc.second_level_category_id,
                sc.second_level_category,
                thc.third_level_category_id,
                thc.third_level_category
            FROM 
                wp_posts p
            LEFT JOIN (
                SELECT
                    tr.object_id AS product_id,
                    MIN(tt.term_id) AS top_category_id,
                    MIN(t.name) AS top_category
                FROM
                    wp_term_relationships tr
                JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
                JOIN wp_terms t ON tt.term_id = t.term_id
                WHERE tt.taxonomy = 'product_cat' AND tt.parent = 0
                GROUP BY tr.object_id
            ) AS tc ON p.ID = tc.product_id
            LEFT JOIN (
                SELECT
                    tr.object_id AS product_id,
                    MIN(tt.term_id) AS second_level_category_id,
                    MIN(t.name) AS second_level_category
                FROM
                    wp_term_relationships tr
                JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
                JOIN wp_terms t ON tt.term_id = t.term_id
                WHERE tt.taxonomy = 'product_cat' AND tt.parent IN (SELECT term_id FROM wp_term_taxonomy WHERE parent = 0)
                GROUP BY tr.object_id
            ) AS sc ON p.ID = sc.product_id
            LEFT JOIN (
                SELECT
                    tr.object_id AS product_id,
                    MIN(tt.term_id) AS third_level_category_id,
                    MIN(t.name) AS third_level_category
                FROM
                    wp_term_relationships tr
                JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
                JOIN wp_terms t ON tt.term_id = t.term_id
                WHERE tt.taxonomy = 'product_cat' AND tt.parent IN (SELECT term_id FROM wp_term_taxonomy WHERE parent IN (SELECT term_id FROM wp_term_taxonomy WHERE parent = 0))
                GROUP BY tr.object_id
            ) AS thc ON p.ID = thc.product_id
            WHERE p.post_type IN ('product', 'product_variation') AND p.post_status = 'publish'
            GROUP BY p.ID
            ORDER BY p.ID;
        END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_AIEO_InsertProductVitals`()
BEGIN
            TRUNCATE TABLE wp_aieo_core_product_vitals;

            INSERT INTO wp_aieo_core_product_vitals
                (image_url, product_name, product_id, parent_product_id, parent_product_name, parent_sku, 
                 brand, brand_id, brand_slug, menu_id, menu_category, head_id, head_category, subcat_id, 
                 sub_category, sku, gtin, erp_id, price, regular_price, onsale, role_special_pricing, product_url, 
                 link_to_view, link_to_cart, link_to_fav, product_description, weight, size, color, stock, length, 
                 width, height)
            SELECT 
                concat('https://femme-fatale.gr/wp-content/uploads/', MAX(pm2.meta_value)) AS image_url,
                MAX(p.post_title) as product_name, 
                p.ID as product_id,
                p.ID AS parent_product_id, 
                MAX(p.post_title) AS parent_product_name,
                MAX(pm4.meta_value) AS parent_sku,
                MAX(tat.brand) AS brand,
                MAX(tat.brand_id) AS brand_id,
                MAX(tat.brand_slug) AS brand_slug,
                MAX(tapc.top_category_id) as menu_id,
                MAX(tapc.top_category) as menu_category,
                MAX(tapc.second_level_category_id) as head_id,
                MAX(tapc.second_level_category) as head_category,
                MAX(tapc.third_level_category_id) as subcat_id,
                MAX(tapc.third_level_category) as sub_category,
                MAX(pm4.meta_value) as sku, 
                MAX(pm0.meta_value) AS gtin, 
                MAX(pm9.meta_value) as erp_id,
                CAST(MAX(REPLACE(pm3.meta_value, ',', '.')) AS DECIMAL(12,2)) as price, 
                CAST(MAX(REPLACE(pm_regular.meta_value, ',', '.')) AS DECIMAL(12,2)) as regular_price,
                CASE WHEN MAX(pm_regular.meta_value) - MAX(pm3.meta_value) > 0 THEN '1' ELSE '0' END AS onsale,
                CASE WHEN MAX(pm6.meta_value) IS NOT NULL THEN 'YES' ELSE 'NO' END AS role_special_pricing,
                concat('https://femme-fatale.gr/?p=', CAST(p.id AS CHAR))  AS product_url,
                Concat('<a href="https://femme-fatale.gr/?p=', CAST(p.id AS CHAR) , '">', "Αναλυτική περιγραφή"  COLLATE utf8mb4_bin ,'</a>') as link_to_view,
                CASE WHEN MAX(pm5.meta_value) > 0 THEN
                    Concat('<div class="loop-button-wrap button-layout2"><a href="?add-to-cart=', CAST(p.id AS CHAR), '" data-quantity="1" class="button product_type_simple add_to_cart_button ajax_add_to_cart" data-product_id="', CAST(p.id AS CHAR), ' data-product_sku="', MAX(pm4.meta_value), '" rel="nofollow">', "Αγορά"  COLLATE utf8mb4_bin  , '</a></div>')
                ELSE ''
                END AS link_to_cart, 
                '''' AS link_to_fav,
                LEFT(MAX(p.post_excerpt), 160) AS product_description,
                MAX(pm10.meta_value) AS weight, 
                MAX(tat.size) AS size,
                MAX(tat.color) AS color, 
                MAX(pm5.meta_value) AS stock,
                MAX(pm_length.meta_value) AS length,
                MAX(pm_width.meta_value) AS width,
                MAX(pm_height.meta_value) AS height
            FROM 
                wp_posts AS p
            LEFT JOIN wp_pods_parent_product_cat_hierarchy AS tapc ON p.ID = tapc.product_id
            LEFT JOIN wp_postmeta AS pm0 ON p.id = pm0.post_id AND pm0.meta_key = 'hwp_product_gtin'
            LEFT JOIN wp_postmeta AS pm1 ON p.id = pm1.post_id AND pm1.meta_key = '_thumbnail_id'
            LEFT JOIN wp_postmeta AS pm2 ON pm1.meta_value = pm2.post_id AND pm2.meta_key = '_wp_attached_file'
            LEFT JOIN wp_postmeta AS pm3 ON p.id = pm3.post_id AND pm3.meta_key = '_price'
            LEFT JOIN wp_postmeta AS pm_regular ON p.ID = pm_regular.post_id AND pm_regular.meta_key = '_regular_price'
            LEFT JOIN wp_postmeta AS pm4 ON p.id = pm4.post_id AND pm4.meta_key = '_sku'
            LEFT JOIN wp_postmeta AS pm5 ON p.id = pm5.post_id AND pm5.meta_key = '_stock'
            LEFT JOIN wp_postmeta AS pm6 ON p.id = pm6.post_id AND pm6.meta_key = 'woocommerce_ultimate_pricing_prices' AND pm6.meta_value LIKE '%customer_%'
            LEFT JOIN wp_postmeta AS pm9 ON p.id = pm9.post_id AND pm9.meta_key = 'soft1_id'
            LEFT JOIN wp_postmeta AS pm10 ON p.id = pm10.post_id AND pm10.meta_key = '_weight'
            LEFT JOIN wp_postmeta AS pm_length ON p.id = pm_length.post_id AND pm_length.meta_key = '_length'
            LEFT JOIN wp_postmeta AS pm_width ON p.id = pm_width.post_id AND pm_width.meta_key = '_width'
            LEFT JOIN wp_postmeta AS pm_height ON p.id = pm_height.post_id AND pm_height.meta_key = '_height'
            LEFT JOIN wp_aieo_core_product_attributes AS tat ON p.id = tat.product_id
            WHERE 
                p.post_type IN ('product') AND p.post_status = 'publish'
            GROUP BY 
                p.ID
            ORDER BY 
                p.ID, MAX(tapc.top_category);
        END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_AIEO_InsertVariationVitals`()
BEGIN
          TRUNCATE TABLE wp_aieo_core_variation_vitals;

          INSERT INTO wp_aieo_core_variation_vitals (product_id, parent_id, sku, gtin, price, regular_price, onsale, role_special_pricing, stock, weight, length, width, height, imagepath, erp_id)
          SELECT DISTINCT
              p.ID AS product_id,
              p.post_parent AS parent_id,
              pm.meta_value AS sku,
              pm2.meta_value AS gtin,
              pm3.meta_value AS price,
              pm_regular.meta_value AS regular_price,
            CASE 
                  WHEN pm_regular.meta_value -  pm3.meta_value > 0 THEN '1'
                  ELSE '0'
              END AS onsale,
              CASE 
                  WHEN pm6.meta_value IS NOT NULL THEN 'YES'
                  ELSE 'NO'
              END AS role_special_pricing,
              pm4.meta_value AS stock,
              pm5.meta_value AS weight,
              pm10.meta_value AS length,
              pm11.meta_value AS width,
              pm12.meta_value AS height,
              pm8.meta_value AS imagepath,
              pm9.meta_value AS erp_id 
          FROM 
              wp_posts AS p
          LEFT JOIN wp_postmeta AS pm ON p.id = pm.post_id AND pm.meta_key = '_sku'
          LEFT JOIN wp_postmeta AS pm2 ON p.id = pm2.post_id AND pm2.meta_key = 'hwp_var_gtin'
          LEFT JOIN wp_postmeta AS pm3 ON p.id = pm3.post_id AND pm3.meta_key = '_price'
          LEFT JOIN wp_postmeta AS pm_regular ON p.ID = pm_regular.post_id AND pm_regular.meta_key = '_regular_price'
          LEFT JOIN wp_postmeta AS pm4 ON p.id = pm4.post_id AND pm4.meta_key = '_stock'
          LEFT JOIN wp_postmeta AS pm5 ON p.post_parent = pm5.post_id AND pm5.meta_key = '_weight'
          LEFT JOIN wp_postmeta AS pm6 ON p.id = pm6.post_id AND pm6.meta_key = 'woocommerce_ultimate_pricing_prices' AND pm6.meta_value LIKE '%customer_%'
          LEFT JOIN wp_postmeta AS pm7 ON p.ID = pm7.post_id AND pm7.meta_key = 'botiga_variation_gallery'
          LEFT JOIN wp_postmeta AS pm8 ON substring_index(substring_index(pm7.meta_value, ':"', -1), '";', 1) = pm8.post_id AND pm8.meta_key = '_wp_attached_file'
          LEFT JOIN wp_postmeta AS pm9 ON p.id = pm9.post_id AND pm9.meta_key = 'iconic_cffv_100545_soft1_id'
          LEFT JOIN wp_postmeta AS pm10 ON p.id = pm10.post_id AND pm10.meta_key = '_length'
          LEFT JOIN wp_postmeta AS pm11 ON p.id = pm10.post_id AND pm11.meta_key = '_width'
          LEFT JOIN wp_postmeta AS pm12 ON p.id = pm10.post_id AND pm12.meta_key = '_height'
          WHERE (p.post_type = 'product_variation' AND p.post_status = 'publish');

          END$$
DELIMITER ;