<?php
        /**
	 * Function to populate a custom field cache that can then be easily queried instead
	 * of the unqueryable JSON in the items row
	 *
 	 * @copyright	Copyright (C) 2012 Blue Flame IT Ltd / Phil Taylor. All rights reserved.
	 * @author 		Phil Taylor <phil@phil-taylor.com>
	 * @since		3rd July 2012
 	 */
	 function onAfterK2Save($row, $isNew){
		$db = JFactory::getDbo();

		// If table is empty - populate it the first time!
		$db->setQuery('SELECT COUNT(*) from #__k2_cfcache');
		$res = $db->loadResult();

		if (NULL===$res){ // No table - need to create it the first time
			$db->setQuery('CREATE TABLE IF NOT EXISTS `jos_k2_cfcache` (
				  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
				  `item_id` int(11) unsigned NOT NULL,
				  `field_id` int(11) DEFAULT NULL,
				  `field_name` varchar(255) DEFAULT NULL,
				  `field_value` varchar(255) DEFAULT NULL,
				  PRIMARY KEY (`id`)
				) ENGINE=InnoDB DEFAULT CHARSET=utf8;');
			$db->query();
		}

		if ($res===NULL|| $res==="0"){ // Just created the table or not yet populated
			$db->setQuery('SELECT * from #__k2_items');
			$rows = $db->loadObjectList();
			foreach ($rows as $item){
			$extrafields = json_decode($item->extra_fields);
				foreach ($extrafields as $field){
					if (is_array($field->value)) $field->value = json_encode($field->value);
					$sql = sprintf('INSERT INTO #__k2_cfcache VALUES (NULL, %s, %s, (SELECT NAME FROM `jos_k2_extra_fields` WHERE id = %s),"%s")',
					$item->id, $field->id, $field->id, $field->value);
					$db->setQuery($sql);
					$db->query();
				}
			}
		}

		// clear this items details
		$db->setQuery('DELETE FROM #__k2_cfcache where item_id = '.$row->id);
		$db->query();

		// repopulate this items custom field cache
		$extrafields = json_decode($row->extra_fields);
		foreach ($extrafields as $field){
			if (is_array($field->value)) $field->value = json_encode($field->value);
			$sql = sprintf('INSERT INTO #__k2_cfcache VALUES (NULL, %s, %s, (SELECT NAME FROM `jos_k2_extra_fields` WHERE id = %s),"%s")',
			$row->id, $field->id, $field->id, $field->value);
			$db->setQuery($sql);
			$db->query();
		}
	}