DROP PROCEDURE IF EXISTS image_import;
DELIMITER //

CREATE PROCEDURE image_import(mode tinyint)
LANGUAGE SQL
SQL SECURITY DEFINER

BEGIN

DROP TEMPORARY TABLE IF EXISTS table_with_description;
CREATE TEMPORARY TABLE table_with_description
SELECT
	pm.virtuemart_media_id
	, m.file_url
	, m.file_title
	, m.file_meta
	, IFNULL(p.product_sku, REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(m.file_url, '/',-1), '-',1), '.', 1), 'z', 'з')) AS sku
	, pm.virtuemart_product_id
FROM ytgb1_virtuemart_product_medias pm
JOIN ytgb1_virtuemart_products p USING(virtuemart_product_id)
JOIN ytgb1_virtuemart_medias m USING(virtuemart_media_id)
WHERE
	file_type = 'product'
	AND pm.ordering = 1;

DROP TEMPORARY TABLE IF EXISTS new_medias;
CREATE TEMPORARY TABLE new_medias
	SELECT
		pru.product_name
		, infa.virtuemart_product_id
		, p.product_sku as trusted_sku
		, x.*
		, CONCAT(xx.file_meta, '. ', foto_id) as new_file_title
		, CONCAT(xx.file_meta, '. ', foto_id) as new_file_meta
		-- INTO OUTFILE 'possibly_recognized_images.text'
	FROM (
		SELECT
			m.virtuemart_media_id
			, m.file_url
			, REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(m.file_url, '/',-1), '-',1), 'z', 'з') recognized_sku
			, CASE 
				WHEN m.file_url LIKE '%reel.jpg' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(m.file_url, '-',-2), '-',1)
				WHEN m.file_url LIKE '%-__.jpg' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(m.file_url, '-',-1), '.',1)
			END	AS foto_id
		FROM ytgb1_virtuemart_medias m
		WHERE
			-- m.file_url LIKE '%93460.jpg' AND
			m.file_type = "product"
			AND m.published = 1
			AND NOT EXISTS(	SELECT virtuemart_product_id FROM ytgb1_virtuemart_product_medias WHERE virtuemart_media_id = m.virtuemart_media_id)
			AND (m.file_url LIKE '%reel.jpg' OR m.file_url LIKE '%-__.jpg')
	)x
	JOIN ( SELECT p.product_sku, COUNT(*) AS n FROM ytgb1_virtuemart_products p GROUP BY p.product_sku ) p 
		ON x.recognized_sku = p.product_sku AND p.n = 1
	JOIN ytgb1_virtuemart_products infa ON infa.product_sku = p.product_sku
	JOIN ytgb1_virtuemart_products_ru_ru pru ON pru.virtuemart_product_id = infa.virtuemart_product_id
	-- таблица с базовым описанием товара
	JOIN table_with_description xx ON xx.virtuemart_product_id = infa.virtuemart_product_id;

DROP TEMPORARY TABLE IF EXISTS temp_table;
CREATE TEMPORARY TABLE temp_table
SELECT
	recognized_sku
	, COUNT(*) as cnt
FROM new_medias m
GROUP BY recognized_sku;


DROP TEMPORARY TABLE IF EXISTS table_for_insert;
CREATE TEMPORARY TABLE table_for_insert
	SELECT 
		x.virtuemart_product_id
		, m.virtuemart_media_id
		, m.file_url
		, m.recognized_sku		
		, min_order_number
		, foto_id
		, cnt.cnt
		, min_order_number - cnt.cnt + (foto_id - 2) as ordering
	FROM new_medias m
	JOIN temp_table cnt ON cnt.recognized_sku = m.recognized_sku
	JOIN (SELECT
			m.virtuemart_product_id
			, product_sku
			-- , MAX(ordering) max_order_number
			, MIN(ordering) min_order_number
		FROM ytgb1_virtuemart_product_medias m
		JOIN ytgb1_virtuemart_products p USING(virtuemart_product_id)
		GROUP BY
			m.virtuemart_product_id
			, product_sku
	)x ON m.recognized_sku = x.product_sku
	-- отбираем только товары с уникальным sku
	JOIN ( SELECT p.product_sku, COUNT(*) AS n FROM ytgb1_virtuemart_products p GROUP BY p.product_sku ) p 
			ON m.recognized_sku = p.product_sku AND p.n = 1;

IF mode = 0 THEN
	SELECT * FROM new_medias ORDER BY file_url;
	SELECT * FROM table_for_insert ORDER BY file_url;
END IF;
IF mode = 1 THEN
	UPDATE ytgb1_virtuemart_medias m
	JOIN new_medias x ON x.virtuemart_media_id = m.virtuemart_media_id
	SET
		m.file_title = x.new_file_title
		, m.file_meta = x.new_file_meta
		, m.file_description = '';

	INSERT INTO ytgb1_virtuemart_product_medias(virtuemart_product_id, virtuemart_media_id, ordering)
	SELECT virtuemart_product_id, virtuemart_media_id, ordering FROM table_for_insert;
END IF;

END//
DELIMITER ;