<?php

$dsn = 'mysql:dbname=XXX;host=127.0.0.1';
$user = 'XXX';
$password = 'XXX';

try {
    $db = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    die();
}

$products = $db->query("
				SELECT
					lily_product.product_id AS id,
					lily_product.model AS product_model,
					lily_product.price AS product_price,
					lily_product_description.name AS product_name,
					lily_product_description.description AS product_description,
					lily_product_description.sizes AS product_sizes_table,
					GROUP_CONCAT(DISTINCT lily_category_description.name) AS product_categories,
					GROUP_CONCAT(DISTINCT lily_option_value_description.name) AS product_sizes,
					GROUP_CONCAT(DISTINCT lily_product_image.image ORDER BY lily_product_image.sort_order ASC) AS product_images,
					lily_url_alias.keyword as slug
				FROM lily_product
				LEFT JOIN lily_product_to_category
				ON lily_product.product_id = lily_product_to_category.product_id
				LEFT JOIN lily_category_description
				ON lily_product_to_category.category_id = lily_category_description.category_id AND lily_category_description.language_id = 8
				LEFT JOIN lily_product_description
				ON lily_product.product_id = lily_product_description.product_id AND lily_product_description.language_id = 8
				LEFT JOIN lily_product_option_value
				ON lily_product.product_id = lily_product_option_value.product_id AND lily_product_option_value.quantity > 0
				LEFT JOIN lily_option_value_description
				ON lily_product_option_value.option_value_id = lily_option_value_description.option_value_id AND lily_option_value_description.language_id = 8
				LEFT JOIN lily_product_image
				ON lily_product_image.product_id = lily_product.product_id
				LEFT JOIN lily_url_alias
				ON lily_url_alias.query = CONCAT('product_id=',lily_product.product_id)
				WHERE lily_product.quantity > 0 AND lily_product.status != 0
				GROUP BY id
");

header('Content-Type: text/xml; charset=utf-8');

echo '<?xml version="1.0" encoding="UTF-8" ?><products>';

foreach ($products as $product) {
	$categories = explode(',',$product['product_categories']);
	$sizes = explode(',',$product['product_sizes']);
	$images = explode(',', $product['product_images']);
	if (isset($categories[0])) {
		$categories[0] = 0;
		$product['product_categories'] = 0;
		if (isset($categories[1]) && $categories[0] == 'Всички категории') {
			$categories[0] = $categories[1];
		}
	}
	$size_details = array();
	if ($product['product_sizes_table']) {
		$product['product_sizes_table'] = html_entity_decode($product['product_sizes_table'], ENT_QUOTES, 'UTF-8');
		//die(var_dump($product['product_sizes_table']));

		$html = new DOMDocument();
		$html->loadHTML('<?xml encoding="UTF-8">' . $product['product_sizes_table']);

		$tables = $html->getElementsByTagName('table');
		if ($tables->length === 0) {
			continue;
		} else {
			$table = $tables->item(0);
		}
		$trs = $table->getElementsByTagName('tr');
		for ($i = 0; $i < $trs->length; $i++) {
			$tds = $trs->item($i)->getElementsByTagName('td');
			for ($j = 0; $j < $tds->length; $j++) {
				$size_details[$j][$i] = $tds->item($j)->nodeValue;
				//die(var_dump($tds->item($j)->nodeValue));
			}
		}
	}
	// if (! empty($size_details))
	// die(var_dump($size_details));
	echo '
		<product>
			<CategoryName><![CDATA['.$categories[0].']]></CategoryName>
			<ProductCode><![CDATA['.$product['product_model'].']]></ProductCode>
			<ProductName><![CDATA['.$product['product_name'].']]></ProductName>
			<ProductDescription><![CDATA['.html_entity_decode($product['product_description'], ENT_QUOTES, 'UTF-8').']]></ProductDescription>
			<ProductSizesTable><![CDATA['.($product['product_sizes_table'] ?: '').']]></ProductSizesTable>
			<ProductPrice><![CDATA['.round((float)$product['product_price'], 2).']]></ProductPrice>
			<ProductImages>';
	foreach ($images as $image) {
		echo '<ProductImage><![CDATA[https://www.lily.bg/image/'.$image.']]></ProductImage>';
	}
	echo '</ProductImages>
			<ProductUrl><![CDATA[https://www.lily.bg/'.$product['slug'].']]></ProductUrl>
			<ProductSizes>';
	if ($product['product_sizes']) {
		//var_dump($sizes);
		foreach ($sizes as $size) {
			echo '<ProductSize><![CDATA['.$size.']]></ProductSize>';
		}
	} else {
		echo '<ProductSize><![CDATA[ONESIZE]]></ProductSize>';
	}
	echo '</ProductSizes><ProductSizeDetails>';

	foreach ($size_details as $i => $size) {
		if ($product['product_model'] == '399669-C06') {
			//var_dump($size_details);
		}
		if ($i > 0) {
			echo '<ProductSize>';
			foreach ($size as $j => $value) {
				echo '<SizeInfo><SizeType><![CDATA[' . $size_details[0][$j] . ']]></SizeType><SizeValue><![CDATA[' . $value . ']]></SizeValue></SizeInfo>';
			}
			echo '</ProductSize>';
		}
	}

	echo '</ProductSizeDetails></product>';
}
echo '</products>';