<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

/**
 * SQL Model for Structure
 *
 * This file must be in your /system/third_party/structure directory of your ExpressionEngine installation
 *
 * @package             Structure
 * @author              Jack McDade (jack@jackmcdade.com)
 * @copyright			Copyright (c) 2010 Travis Schmeisser
 * @link                http://buildwithstructure.com
 */


class Sql_structure
{
	
	var $site_id;
	var $data  = array();
	var $cids  = array();
	var $lcids = array();
	
	function Sql_structure()
	{
		$this->EE =& get_instance();		
		$this->EE->load->add_package_path(PATH_THIRD . 'structure/');
		$this->EE->load->library('sql_helper');

		$this->site_id = $this->get_site_id();
	}
	
	
	/**
	 * Get global and MSM specific settings
	 * from exp_structure_settings table
	 *
	 * @return array
	 **/
	function get_settings()
	{
		static $settings = NULL;
		
		if (is_array($settings))
		{
			return $settings;
		}
		
		$site_id = '0,'.$this->site_id;

		$sql = "SELECT var_value, var FROM exp_structure_settings WHERE site_id IN ({$site_id})";
		$result = $this->EE->db->query($sql);

		$settings = array(
			'show_picker' 			=> 'y',
			'show_view_page' 		=> 'y',
			'show_status' 			=> 'y',
			'show_page_type' 		=> 'y',
			'show_global_add_page' 	=> 'y',
			'redirect_on_login' 	=> 'n',
			'redirect_on_publish' 	=> 'n'
		);
		
		if ($result->num_rows() > 0)
		{
			foreach ($result->result_array() as $row)
			{
				if ($row['var_value'] != '')
				{
					$settings[$row['var']] = $row['var_value'];
				}
			}
		}
		
		return $settings;
	}
	
	
	/**
	 * Get all data for all Structure Channels
	 *
	 * @return array
	 */
	function get_data()
	{
		$data = array();
		
		$sql = "SELECT node.*, (COUNT(parent.entry_id) - 1) AS depth, expt.title, expt.status
				FROM exp_structure AS node
				INNER JOIN exp_structure AS parent
					ON node.lft BETWEEN parent.lft AND parent.rgt
				INNER JOIN exp_channel_titles AS expt
					ON node.entry_id = expt.entry_id
				WHERE parent.lft > 1
				AND node.site_id = {$this->site_id}
				AND parent.site_id = {$this->site_id}
				GROUP BY node.entry_id
				ORDER BY node.lft";
		$result = $this->EE->db->query($sql);

		if ($result->num_rows() > 0)
		{
			foreach ($result->result_array() as $row)
			{
				$data[$row['entry_id']] = $row;
			}
		}

		return $data;
	}
	
	
	/**
	 * Get selective data on all Structure Channels
	 *
	 * @return array
	 */
	function get_selective_data($parent_id = NULL, $depth = 1, $exclude = NULL, $current_id = NULL, $mode = NULL, $start_from_absolute = FALSE)
	{
		$pages = $this->get_site_pages();

		// -------------------------------------------
		//  Exclude parameter WHERE clause
		// -------------------------------------------
		
		$where_exclude = NULL;
		
		if ($exclude !== NULL && $exclude != "")
		{
			$exclude = explode('|', $exclude);
			
			foreach ($exclude as $id)
			{
				$where_exclude[] = "AND node.lft NOT BETWEEN (SELECT lft FROM exp_structure WHERE entry_id = $id) AND (SELECT rgt FROM exp_structure WHERE entry_id = $id)";
			}
			
			$where_exclude = implode(' ', $where_exclude);
		}
		
		// -------------------------------------------
		//  Mode parameter HAVING clause
		// -------------------------------------------

		$current_depth = $this->EE->uri->total_segments();
		$show_depth = $this->EE->TMPL->fetch_param('show_depth', 0);
		$max_depth = $this->EE->TMPL->fetch_param('max_depth', $current_depth);
		
		if ($mode == 'sub')
		{
			if ($current_depth > 0)
			{
				$parent_depth = $parent_id == 0 ? 0 : $this->get_parent_uri_depth($pages['uris'][$parent_id]);
				$current_depth = $current_depth - $parent_depth + 1;
			}
			
			// Offset for depth
			$max_depth = $max_depth > $current_depth ? $current_depth : $this->EE->TMPL->fetch_param('max_depth', $current_depth);

			// Must be at least your show_depth
			$max_depth = $max_depth < $show_depth ? $show_depth : $max_depth;
			
			// Must be at least 1
			$max_depth = $max_depth < 1 ? 1 : $max_depth;
			
			$depth_clause = "HAVING depth >= $depth AND depth <= $max_depth";
			
			// setting max_depth = 2 lets you get beyond the first level with start_from = "/", but doesn't keep the full tree as u travel deeper
		}
		elseif ($mode == 'full')
		{
			$depth_clause = "HAVING depth >= 1";
			$parent_id = 0;
		}
		elseif ($mode == 'main')
		{
			$depth_clause = "HAVING depth = 1";
			$parent_id = 0;
		}
		else
		{
			$depth_clause = NULL;
		}
		
		// -------------------------------------------
		//  The master query
		// -------------------------------------------
		
		$site_id = ($parent_id == 0) ? 0 : $this->site_id;
		$count_correction = ($parent_id == 0) ? 0 : -1;
		
		$sql = "SELECT node.*, (COUNT(parent.entry_id) + $count_correction - (sub_tree.depth + 1)) AS depth, expt.title, expt.status
				FROM exp_structure AS node
				INNER JOIN exp_channel_titles AS expt
					ON node.entry_id = expt.entry_id,
					(SELECT * FROM exp_structure WHERE site_id IN (0,$this->site_id)) AS parent,
					exp_structure AS sub_parent,
					(	SELECT node.*, (COUNT(parent.entry_id) - 1) AS depth
						FROM exp_structure AS node,
						exp_structure AS parent
						WHERE node.lft BETWEEN parent.lft AND parent.rgt
						AND node.entry_id = '$parent_id'
						AND parent.site_id = '$site_id'
						GROUP BY node.entry_id
						ORDER BY node.lft
					) AS sub_tree
				WHERE node.lft BETWEEN parent.lft AND parent.rgt
					AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
					AND sub_parent.entry_id = sub_tree.entry_id
					AND node.site_id = $this->site_id
					$where_exclude
				GROUP BY node.entry_id
				$depth_clause
				ORDER BY node.lft";
		
		$result = $this->EE->db->query($sql);
		
		// -------------------------------------------
		//  Build data array with entry_ids as keys
		// -------------------------------------------
		
		$data = array();
		$page_data = $result->result_array();
		
		if ($result->num_rows() > 0)
		{			
			// -------------------------------------------
			//  Status parameter
			// -------------------------------------------
			
			$status = $this->EE->TMPL->fetch_param('status', 'open');
			$status_mode = "inclusive";
			
			$closed_parents = array();
			
			if (substr($status, 0, 4) == 'not ')
			{
				$statuses = explode("|", trim(substr(strtolower($status), 3)));
				$status_mode = "exclusive";
			}
			else
			{
				$statuses = explode("|", strtolower($status));
			}
			
			$statuses = array_flip($statuses);
			
			// -------------------------------------------
			//  Show_overview parameter
			// -------------------------------------------
			
			$show_overview = $this->EE->TMPL->fetch_param('show_overview', FALSE);
			if ($mode == 'sub' && $show_overview == 'yes' && $current_depth > 0)
			{
				$overview_node = $this->get_overview($parent_id);
				$overview_node['title'] = $this->EE->TMPL->fetch_param('rename_overview', $overview_node['title']);
				$overview_node['overview'] = $this->EE->TMPL->fetch_param('rename_overview', $overview_node['title']);
				array_unshift($page_data, $overview_node);
			}
			
			// build out the main data array
			foreach ($page_data as $row)
			{
				if ( ! isset($row['entry_id'])) continue;
				
				$data[$row['entry_id']] = $row;
				$data[$row['entry_id']]['uri'] = $this->EE->functions->create_page_url($pages['url'], $pages['uris'][$row['entry_id']]);
				$data[$row['entry_id']]['slug'] = $pages['uris'][$row['entry_id']];
				$data[$row['entry_id']]['classes'] = array();
				$data[$row['entry_id']]['ids'] = array();
				
				// Status conditions
				if ($status_mode == 'exclusive' && array_key_exists(strtolower($row['status']), $statuses))
				{
					$closed_parents[] = array('entry_id' => $row['entry_id'], 'lft' => $row['lft'], 'rgt' => $row['rgt']);
					unset($data[$row['entry_id']]);
				}
				elseif ($status_mode == 'inclusive' && ! array_key_exists(strtolower($row['status']), $statuses))
				{
					$closed_parents[] = array('entry_id' => $row['entry_id'], 'lft' => $row['lft'], 'rgt' => $row['rgt']);
					unset($data[$row['entry_id']]);
				}
				
				// Account for show_overview rename override
				if (array_key_exists('overview', $row) && $row['overview'] == TRUE)
				{
					$data[$row['entry_id']]['overview'] = $row['title'];
				}
			}

			// remove nodes of closed parents
			foreach ($closed_parents as $closed)
			{
				foreach ($data as $key => $page)
				{
					if (($page['lft'] > $closed['lft'] && $page['rgt'] < $closed['rgt']))
					{
						// echo $page['entry_id'];
						unset($data[$page['entry_id']]);
					}
				}
			}
			
			// Remove the pages that aren't in the current path.
			// This is the default "sub nav" behavior
			$listing_ids = $this->get_listing_entry_ids();
			if ($mode == 'sub' && $this->EE->uri->segment($show_depth+1) !== FALSE && (array_key_exists($current_id, $data) || array_key_exists($parent_id, $data) || array_key_exists($current_id, $listing_ids)))
			{	
				$is_listing = FALSE;
				// If a listing entry, use parent as current to prevent sibling children from showing
				if (array_key_exists($current_id, $listing_ids))
				{
					$parent_id = $this->get_parent_id($current_id);
					$is_listing = TRUE;
					$current_id = $parent_id;
				}
				
				if (array_key_exists($current_id, $data) || array_key_exists($parent_id, $data))
				{
					$cur_depth = array_key_exists($current_id, $data) ? $data[$current_id]['depth'] : $data[$parent_id]['depth'];
					
					$path_pages = array_values($this->get_single_path($current_id));
					if ($parent_id == '0')
					{
						$home_node = $this->get_home_node();
						array_unshift($path_pages, $home_node);
					}
					
					$cur_depth = $show_overview == "yes" ? $cur_depth + 1 : $cur_depth;

					foreach ($data as $key => $page)
					{
						if ($this->filter_entry_by_path($page, $data, $current_id, $cur_depth, $parent_id, $path_pages, $start_from_absolute, $is_listing))
							unset($data[$page['entry_id']]);
					}
				}
			}
		}
		
		return $data;
	}
	/**
	 * Filter Tree Path
	 *
	 * Remove unwanted entries from your path
	 * @param $page array
	 * @param $data array
	 * @param $current_id int
	 * @param $cur_depth int
	 * @return bool
	 */
	function filter_entry_by_path($page, $data, $current_id, $current_depth, $parent_id, $path_pages, $start_from_absolute, $is_listing)
	{
		// Sliding offset
		$offset = -2 + $start_from_absolute;
		
		if (is_array($page) && is_array($data) && is_numeric($current_id) && is_numeric($current_depth) && ! isset($page['overview']))
		{
			
			

			if ($parent_id != '0' && ! $this->is_path_decendant($page, $page['depth'] + $offset, $path_pages))
			{
				return TRUE;
			}
			elseif ($parent_id == '0')
			{
				if ( ! $this->is_path_decendant($page, $page['depth']-1, $path_pages))
					return TRUE;
			}
		}
		return FALSE;
	}
	
	function is_path_decendant($page, $depth, $path_pages)
	{
	
		if ($page['lft'] > @$path_pages[$depth]['lft'] && $page['rgt'] < @$path_pages[$depth]['rgt'])
			return TRUE;
			
		return FALSE;
	}
	
	function count_segments($uri)
	{
		if ($uri != "")
		{
			$uri = $this->EE->functions->remove_double_slashes(trim(html_entity_decode($uri), '/'));
			$segment_array = explode('/', $uri);
			return count($segment_array);
		}
		return NULL;
	}
	
	
	/**
	 * Get a single path of data, useful for breadcrumbs etc
	 *
	 * @return array
	 */
	function get_single_path($entry_id)
	{
		$listing_ids = $this->get_listing_entry_ids();
		if (array_key_exists($entry_id, $listing_ids))
		{
			$entry_id = $this->get_parent_id($entry_id);
		}

		$sql = "SELECT parent.lft, parent.rgt,expt.title, expt.entry_id
				FROM exp_structure AS node,
					exp_structure AS parent
				INNER JOIN exp_channel_titles AS expt ON parent.entry_id = expt.entry_id
				WHERE node.lft BETWEEN parent.lft AND parent.rgt
					AND node.entry_id = $entry_id
					AND expt.site_id = $this->site_id
				ORDER BY parent.lft";
		
		$result = $this->EE->db->query($sql);

		$data = array();
		$pages = $this->get_site_pages();
		
		if ($result->num_rows() > 0)
		{
			foreach ($result->result_array() as $row)
			{ 
				$data[$row['entry_id']] = $row;
				$data[$row['entry_id']]['uri'] = $this->EE->functions->create_page_url($pages['url'], $pages['uris'][$row['entry_id']]);
			}
		}
		
		return $data;

	}
	
	function reindex_at_one($array)
	{
		$start_at = 1;
		$new_array = array();
		foreach ($array as $key => $row)
		{
			$array[$start_at] = $row;
			$start_at++;
		}
		return $array;
	}
	
	function add_attributes($pages, $entry_id, $mode)
	{
		$top_array = array();
		$separator = $this->EE->config->item('word_separator') != "dash" ? '_' : '-';
		$root_id = $this->EE->TMPL->fetch_param('css_id', 'nav' . $separator . $mode);
		$path_pages = $this->get_single_path($entry_id);
		$parent_id = $this->get_parent_id($entry_id);
		$listing_ids = $this->get_listing_entry_ids();

		foreach ($pages as $key => $page)
		{	
			// add level classes
			if ($this->EE->TMPL->fetch_param('add_level_classes', FALSE) !== FALSE)
			{
				$pages[$key]['classes'][] = 'level' . $separator . $page['depth'];
			}
			
			// add here class
			$current_class = $this->EE->TMPL->fetch_param('current_class', 'here');
			if ($page['entry_id'] == $entry_id && $current_class != "no" && $current_class != "off" && $current_class != "none")
			{
				$pages[$key]['classes'][] = $current_class;
			}
			
			// add here class to listing parent
			if (array_key_exists($entry_id, $listing_ids) && $key == $parent_id)
			{
				$pages[$parent_id]['classes'][] = $current_class;
			}
			
			// add parent-here class
			if (array_key_exists($page['entry_id'], $path_pages) && $page['entry_id'] != $entry_id && ! array_key_exists('overview', $page))
			{
				if ($mode == "main")
				{
					$pages[$key]['classes'][] = $current_class;
				}
				else
				{
					$pages[$key]['classes'][] = 'parent' . $separator . $current_class;
				}
			}

			// add unique ids
			if ($this->EE->TMPL->fetch_param('add_unique_ids', FALSE) == "yes" || $this->EE->TMPL->fetch_param('add_unique_ids', FALSE) == "on")
			{
				$pages[$key]['ids'][] = $page['slug'] == '/' ? $root_id . $separator . 'home' : $root_id . $separator . $this->get_slug($page['slug']);
			}
			elseif ($this->EE->TMPL->fetch_param('add_unique_ids', FALSE) == "entry_ids" || $this->EE->TMPL->fetch_param('add_unique_ids', FALSE) == "entry_id")
			{
				$pages[$key]['ids'][] = $root_id . $separator . $page['entry_id'];
			}
			
			// last class
			if ($page['parent_id'] != 0 && array_key_exists($page['parent_id'], $pages) && ($page['rgt']+1) == $pages[$page['parent_id']]['rgt'])
			{
				$pages[$key]['classes'][] = 'last';
			}
			
			// Build array of top level items
			// if ($page['parent_id'] == 0)
			if ($page['depth'] == 1)
			{
				$top_array[] = $page;
			}	
		}

		// Account for the very last top level item and add class="last"
		if (count($top_array) > 0)
		{
			$last = end($top_array);
			$pages[$last['entry_id']]['classes'][] = 'last';
		}

		return $pages;
	}
	
	function get_slug($uri = FALSE)
	{
		if ($uri !== FALSE)
		{
			$segments = explode('/', trim($uri, '/'));
			return end($segments);
		}
		return FALSE;
	}
	
	/**
	 * Get the HTML code for an unordered list of the tree
	 * @return string HTML code for an unordered list of the whole tree
	 */
	function generate_nav($entry_id, $current_id = FALSE, $depth = 1, $exclude = NULL, $mode, $start_from_absolute)
	{
		$html = '';
		$separator = $this->EE->config->item('word_separator') != "dash" ? '_' : '-';
		
		// Fallback to entry_id if no current_id (e.g. sitemap usage)
		if ($current_id === FALSE)
			$current_id = $entry_id;

		$pages = $this->get_selective_data($entry_id, $depth, $exclude, $current_id, $mode, $start_from_absolute);
		$pages = $this->add_attributes($pages, $current_id, $mode);

		$tree = array_values($pages);
		if (count($tree) < 1)
			return NULL;
			
		$custom_title_fields = $this->create_custom_titles();
				
		for ($i=0, $foo = count($tree); $i < $foo; $i++)
		{
			// Build class string if any exist
			$classes = count($tree[$i]['classes']) > 0 ? ' class="'. implode(' ',$tree[$i]['classes']) .'"' : NULL;
			
			// Build id string if any exist
			$ids = count($tree[$i]['ids']) > 0 ? ' id="'. implode(' ',$tree[$i]['ids']) .'"' : NULL;
			
			// Title field: custom|title
			$title = $custom_title_fields !== FALSE ? $custom_title_fields[$tree[$i]['entry_id']] : $tree[$i]['title'];
			
			// Show Overview ovverride
			if (array_key_exists('overview', $tree[$i]))
				$title = $tree[$i]['overview'];
			
			// Add span hook if desired
			$title_output = $this->EE->TMPL->fetch_param('add_span', FALSE) == "yes" ? "<span>" . $title . "</span>" : $title;
			
			// -------------------------------------------
			//  The list item itself
			// -------------------------------------------
			
			$html .= '<li'. $classes . $ids . '><a href="' . $tree[$i]['uri'] . '">' . $title_output .'</a>';
			
			// Closing up a level
			if ($tree[$i]['depth'] < @$tree[$i+1]['depth'])
			{
				$html .= "\n<ul>\n";
			}
			// Closing up a list item
			elseif (@$tree[$i]['depth'] == @$tree[$i+1]['depth'])
			{
				$html .= "</li>\n";
			}
			// Closing up multiple levels and list items
			else
			{
				$diff = (array_key_exists($i+1, $tree)) ? $tree[$i]['depth'] - $tree[$i+1]['depth'] : $tree[$i]['depth'] - 1 ;
				$html .= str_repeat("</li>\n</ul>\n", $diff) . "</li>\n";
			}
		}
		
		
		// Add the unordered list element
		if ($this->EE->TMPL->fetch_param('include_ul', 'yes') == 'yes')
		{
			// Add css class
			$css_class = $this->EE->TMPL->fetch_param('css_class', NULL);
			if ($css_class !== NULL && $css_class != '')
				$css_class = " class=\"$css_class\"";
				
			
			$css_id = $this->EE->TMPL->fetch_param('css_id', 'nav' . $separator . $mode);
			$root_id =  " id=\"" . $css_id . "\"";
			
			if ($css_id == 'none' || $css_id == 'no')
				$root_id = NULL;
			
			
			$html = $this->EE->TMPL->fetch_param('wrap_start', NULL) . "<ul$root_id$css_class>\n". $html ."</ul>" . $this->EE->TMPL->fetch_param('wrap_end', NULL);
		}
		
		return $html;
	}
	
	
	function create_custom_titles($include_listings = FALSE)
	{
		$custom_titles = $this->EE->TMPL->fetch_param('channel:title', FALSE);
		if ($custom_titles === FALSE)
			return FALSE;
			
		$custom_titles = explode('|', $custom_titles);
		
		// Load the Channel API
		$this->EE->load->library('api');
		$this->EE->api->instantiate('channel_fields');
		
		$title_fields = array();
		foreach ($custom_titles as $pair)
		{
			if (strstr($pair, ':') === FALSE)
				return FALSE;
		
			$exploded = explode(':', $pair);
			$result = $this->EE->sql_helper->row("SELECT channel_id, channel_name FROM exp_channels WHERE channel_name = '$exploded[0]'");
			
			if ($result === NULL)
				return FALSE; // Invalid parameter
		
			$title_fields[$result['channel_id']] = $exploded[1];
		}
		
		
		$c_fields = $this->EE->api_channel_fields->fetch_custom_channel_fields();
		$c_fields = $c_fields['custom_channel_fields'][$this->site_id];

		$sql_fields = array();
		foreach ($title_fields as $channel_id => $field)
		{
			$sql_fields[$channel_id]['field_id'] = array_key_exists($field, $c_fields) ? $c_fields[$field] : FALSE;
			$sql_fields[$channel_id]['field_name'] = array_key_exists($field, $c_fields) ? $field : FALSE;
		}
		
		$structure_channels = $this->get_structure_channels('page');		
		if ($include_listings === TRUE)
			$structure_channels = $structure_channels+$this->get_structure_channels('listing');
						
		$sql_channels = implode(',', array_keys($structure_channels));
		
		$select_statement = array();
		foreach ($sql_fields as $channel_id => $field)
		{
			if ($field['field_id'] !== FALSE)
			{
				// $select_statement[] = '(c_data.field_id_' . $field['field_id'] . ') AS ' . $field['field_name'];	
				$select_statement[] = '(c_data.field_id_' . $field['field_id'] . ') AS `' . $field['field_name'] . '`';
			
			}
		}
		
		if (count($select_statement) == 0)
			return FALSE;
		
		$sql = "SELECT c_data.channel_id, c_data.entry_id, ". implode(',', $select_statement) .", c_titles.title
				FROM exp_channel_data AS c_data
					INNER JOIN exp_channel_titles AS c_titles ON c_data.entry_id = c_titles.entry_id
				WHERE c_data.channel_id IN ($sql_channels)
				AND c_data.site_id = $this->site_id";
		
		$result = $this->EE->db->query($sql);

		$page_titles = array();
		if ($result->num_rows() > 0)
		{
			foreach ($result->result_array() as $page)
			{
				if (array_key_exists($page['channel_id'], $sql_fields)
				&& $sql_fields[$page['channel_id']]['field_name'] !== FALSE
				&& $page[$sql_fields[$page['channel_id']]['field_name']] !== NULL
				&& $page[$sql_fields[$page['channel_id']]['field_name']] != '')
				{
					$page_titles[$page['entry_id']] = $page[$sql_fields[$page['channel_id']]['field_name']];
				}
				else
				{
					$page_titles[$page['entry_id']] = $page['title'];
				}
			}
		}
		
		return $page_titles;
		
	}
	
	
	function generate_breadcrumbs($entry_id)
	{
		$pages = $this->get_single_path($entry_id);
		
		$html = '';
		foreach($pages as $page)
		{
			$html .= $page['title'] . " &raquo; ";
		}
		
		echo $html;
		
	}
	
	
	function get_overview($entry_id)
	{
		$sql = "SELECT node.*, (1) AS depth,
					if((node.rgt - node.lft) = 1,1,0) AS isLeaf, 
					((node.rgt - node.lft - 1) DIV 2) AS numChildren, expt.status, expt.title
				FROM exp_structure AS node
				INNER JOIN exp_structure AS parent
					ON node.lft BETWEEN parent.lft AND parent.rgt
				INNER JOIN exp_channel_titles AS expt
					ON node.entry_id = expt.entry_id
				WHERE node.entry_id = '$entry_id'
				AND parent.site_id IN (0,$this->site_id)
				GROUP BY node.lft 
				LIMIT 1";

		$result = $this->EE->db->query($sql);
	
		if ($result->num_rows == 0)
		{
			return FALSE;
		}
		
		$result_row = "";
		foreach ($result->result_array() as $row)
		{
			$result_row = $row;
		}
		
		return $result_row;
		
	}
	
	
	function get_parent_id($entry_id)
	{
		if (is_numeric($entry_id))
		{
			$listing_ids = $this->get_listing_entry_ids();
			if (array_key_exists($entry_id, $listing_ids))
			{
				$sql = "SELECT parent_id FROM exp_structure_listings WHERE entry_id = $entry_id AND site_id = $this->site_id";
			}
			else
			{
				$sql = "SELECT parent_id FROM exp_structure WHERE entry_id = $entry_id AND site_id = $this->site_id";
			}
			
			$result = $this->EE->sql_helper->row($sql);
		
			// Get homepage instead
			if ($result['parent_id'] == 0)
			{
				$sql = "SELECT entry_id FROM exp_structure WHERE lft = 2 AND site_id = $this->site_id";
				$result = $this->EE->sql_helper->row($sql);
			
				return $result['entry_id'];
			}
		
			return $result['parent_id'];
		}
		return FALSE;
	}
	
	function get_home_page_id()
	{
		$sql = "SELECT entry_id FROM exp_structure WHERE lft = 2 AND site_id = $this->site_id";
		$result = $this->EE->sql_helper->row($sql);
	
		return $result['entry_id'];
	}
	
	function get_home_node()
	{
		$sql = "SELECT * FROM exp_structure WHERE entry_id = 0";
		$result = $this->EE->sql_helper->row($sql);
		// print_r($result);
	
		return $result;
	}
	
	
	function get_page_title($entry_id)
	{
		if (is_numeric($entry_id))
		{
			$this->EE->db->where('entry_id', $entry_id);
			$this->EE->db->limit(1);
			
			$query = $this->EE->db->get('exp_channel_titles');
			
			if ($query->num_rows() == 1)
			{			
				 $row = $query->row(); 
				 return $row->title;
			}
		}
		return FALSE;
	}
	
	function is_listing_entry($entry_id)
	{
		if (is_numeric($entry_id))
		{
			$sql = "SELECT entry_id FROM exp_structure_listings WHERE entry_id = $entry_id AND site_id = $this->site_id";
			$result = $this->EE->sql_helper->row($sql);
			
			if ($result !== NULL)
			{
				return TRUE;
			}
			return FALSE;
		}
		return FALSE;
	}
	
	
	function get_listing_entry_ids()
	{
		if ( ! isset($this->EE->session->cache['structure']['listing_ids']))
		{
			$sql = "SELECT entry_id FROM exp_structure_listings WHERE site_id = $this->site_id";
			$result = $this->EE->db->query($sql);
		
			if ($result->num_rows > 0)
			{
				$data = array();
				foreach ($result->result_array() as $row)
				{ 
					$this->EE->session->cache['structure']['listing_ids'][$row['entry_id']] = $row['entry_id'];
				}
			}
			else
			{
				$this->EE->session->cache['structure']['listing_ids'] = array();
			}
		}
		return $this->EE->session->cache['structure']['listing_ids'];
	}
	
	
	/**
	 * Get all data from the exp_structure_channels table
	 * @param $type|unmanaged|page|listing|asset  
	 * @param $channel_id you can pass a channel_id to retreive it's data
	 * @param $order pass it 'alpha' to order by channel title
	 * @return array An array of channel_ids and it's associated template_id, type and channel_title
	 */
	function get_structure_channels($type = '', $channel_id = '', $order = '')
	{
		$site_id = $this->EE->config->item('site_id');
		
		// Get Structure Channel Data
		$sql = "SELECT ec.channel_id, ec.channel_title, ec.site_id, esc.template_id, esc.type, esc.split_assets
				FROM exp_channels AS ec
				LEFT JOIN exp_structure_channels as esc USING (channel_id)
				WHERE ec.site_id = '$site_id'";
		if ($type != '') $sql .= " AND esc.type = '$type'";
		if ($channel_id != '') $sql .= " AND esc.channel_id = '$channel_id'";
		if ($order == 'alpha') $sql .= " ORDER BY ec.channel_title";
		
		$results = $this->EE->db->query($sql);
		
		if ($results->num_rows > 0)
		{
			// Format the array nicely
			$channel_data = array();
			foreach($results->result_array() as $key => $value)
			{
				$channel_data[$value['channel_id']] = $value;
			}

			return $channel_data;
		}
		
		return FALSE;
	}
	
	
	function get_child_entries($parent_id)
	{
		if ($parent_id !== FALSE && is_numeric($parent_id))
		{
			$sql = "SELECT entry_id FROM exp_structure WHERE parent_id = $parent_id AND entry_id != '0' AND site_id = $this->site_id ORDER BY lft ASC";
			$results = $this->EE->db->query($sql);
		
			$entries = array();
			if ($results->num_rows() > 0)
			{
				foreach ($results->result_array() as $row)
				{
					$entries[] = $row['entry_id'];
				}
				return $entries;
			}
		}
		return FALSE;
	}

	
	/**
	 * Get data from the exp_sites table
	 *
	 * @return array with site_id as key
	 */
	function get_site_pages()
	{
		// $sql = "SELECT site_pages FROM exp_sites WHERE site_id = $this->site_id";
		// $pages_array = $this->EE->sql_helper->row($sql);
		// $site_pages = unserialize(base64_decode($pages_array['site_pages']));
		// print_r($site_pages);
		// die();

		$site_pages = $this->EE->config->item('site_pages');
		$site_pages = $site_pages[$this->site_id];
		
		return $site_pages;
	}
	
	
	function get_channel_data($channel_id)
	{
		$sql = "SELECT * FROM exp_structure_channels
				WHERE channel_id = $channel_id
				AND site_id = $this->site_id";
				
		return $this->EE->sql_helper->row($sql);
	}
	
	
	/**
	 * Get Templates
	 *
	 * @return Single dimensional array of templates, ids and names
	 **/
	function get_templates()
	{
		$sql = "SELECT tg.group_name, t.template_id, t.template_name
				FROM   exp_template_groups tg, exp_templates t
				WHERE  tg.group_id = t.group_id 
				AND tg.site_id = {$this->site_id}
				ORDER BY tg.group_name, t.template_name";
		$templates = $this->EE->db->query($sql);
		
		return $templates->result_array();
	}
	
	
	function get_listing_parent($channel_id)
	{
		$sql = "SELECT entry_id FROM exp_structure
				WHERE listing_cid = $channel_id
				AND site_id = $this->site_id";
		$result = $this->EE->sql_helper->row($sql);
		
		if ($result !== NULL)
		{
			return $result['entry_id'];
		}
		
		return FALSE;
	}
	
	function get_listing_channel($entry_id)
	{
		if (is_numeric($entry_id))
		{
			$listing_ids = $this->get_listing_entry_ids();
			
			if (array_key_exists($entry_id, $listing_ids))
				$entry_id = $this->get_parent_id($entry_id);
			
			$sql = "SELECT listing_cid FROM exp_structure
					WHERE entry_id = $entry_id
					AND site_id = $this->site_id";
			$result = $this->EE->sql_helper->row($sql);

			if ($result !== NULL && $result['listing_cid'] != '0')
				return $result['listing_cid'];	
		}
		return FALSE;
	}
	
	
	function get_listing_channel_short_name($channel_id)
	{
		$sql = "SELECT channel_name FROM exp_channels
				WHERE channel_id = $channel_id
				AND site_id = $this->site_id";
		$result = $this->EE->sql_helper->row($sql);
		
		if ($result !== NULL && $result['channel_name'] != '0')
			return $result['channel_name'];
		
		return FALSE;
	}
	
	
	
	/**
	 * User Access
	 *
	 * @param string $perm 
	 * @param array $settings 
	 * @return void
	 */
	function user_access($perm, $settings = array())
	{
		
		$user_group = $this->EE->session->userdata['group_id'];

		// super admins always have access
		if ($user_group == 1)
		{
			return TRUE;
		}

		$admin_perm = 'perm_admin_structure_' . $user_group;
		$this_perm	= $perm . '_' . $user_group;

		if ($settings !== array())
		{
			if ((isset($settings[$admin_perm]) || isset($settings[$this_perm])))
			{
				return true;
			}
			else
			{
				return FALSE;
			}
		}

		// settings were not passed we have to go to the DB for the check
		$result = $this->EE->db->query("SELECT var FROM exp_structure_settings WHERE var = '$admin_perm' OR var = '$this_perm'");
		if ($result->num_rows() > 0)
		{
			return TRUE;
		}

		return FALSE;
	}
	
	
	/**
	 * Module Is Installed
	 *
	 * @return bool TRUE if installed
	 * @return bool FALSE if not installed
	 */
	function module_is_installed()
	{
		$results = $this->EE->db->query("SELECT module_id FROM exp_modules WHERE module_name = 'Structure'");

	    if ($results->num_rows > 0)
		{
			return TRUE;
		}
		else
		{
			return FALSE;
		}
	}
	
	
	/**
	 * Extension Is Installed
	 *
	 * @return bool TRUE if installed
	 * @return bool FALSE if not installed
	 */
	function extension_is_installed()
	{
		$results = $this->EE->db->query("SELECT * FROM exp_extensions WHERE class = 'Structure_ext' AND enabled='y'");
	    if ($results->num_rows > 0)
		{
			return TRUE;
		}
		else
		{
			return FALSE;
		}
	}
	
	/**
	 * Get Module ID
	 *
	 * @return numeral Module's ID
	 */
	function get_module_id()
	{
		$results = $this->EE->db->query("SELECT module_id FROM exp_modules WHERE module_name = 'Structure'");
		
		if ($results->num_rows > 0)
		{
			return $results->row('module_id');
		}
		else
		{
			return FALSE;
		}
	}
	
	
	/**
	 * Get Member Groups
	 *
	 * @return Array of member groups with access to Structure
	 */
	function get_member_groups()
	{
		$sql = "SELECT mg.group_id AS id, mg.group_title AS title 
				FROM exp_member_groups AS mg
				INNER JOIN exp_module_member_groups AS modmg
				ON (mg.group_id = modmg.group_id) 
				WHERE mg.can_access_cp = 'y' 
					AND mg.can_access_publish = 'y'
					AND mg.can_access_edit = 'y' 
					AND mg.group_id <> 1 
					AND modmg.module_id = {$this->get_module_id()}
					AND mg.site_id = {$this->site_id}
				ORDER BY mg.group_id";
				
		$groups = $this->EE->db->query($sql);
		
		if ($groups->num_rows > 0)
		{
			return $groups->result_array();
		}
		else
		{
			return FALSE;
		}
	}
	
	
	/**
	 * Get Entry Title
	 *
	 * @param string $entry_id 
	 * @return string Entry Title or NULL
	 */
	function get_entry_title($entry_id)
	{
		if ( ! is_numeric($entry_id))
			return NULL;
			
		$sql = "SELECT title FROM exp_channel_titles WHERE entry_id = $entry_id";
		$result = $this->EE->db->query($sql);

		if ($result->num_rows > 0)
		{
			return $result->row('title');
		}
		else
		{
			return NULL;
		}
	}
	
	/**
	 * Get entries by channel
	 *
	 * @param int $channel_id
	 * @return $entry_ids array of entry_ids or FALSE
	 **/
	function get_entries_by_channel($channel_id)
	{
		if ( ! is_numeric($channel_id))
			return FALSE;
			
		$sql = "SELECT entry_id FROM exp_channel_titles WHERE channel_id = $channel_id AND site_id = $this->site_id";
		$result = $this->EE->db->query($sql);
		
		if ($result->num_rows = 0)
			return FALSE;

		$entry_ids = array();
		foreach ($result->result_array() as $row)
		{
			$entry_ids[] = $row['entry_id'];
		}
		
		return $entry_ids;	
	}
	
	function get_entry_titles_by_channel($channel_id)
	{
		if ( ! is_numeric($channel_id))
			return FALSE;
			
		$sql = "SELECT entry_id, title FROM exp_channel_titles WHERE channel_id = $channel_id AND site_id = $this->site_id";
		$result = $this->EE->db->query($sql);
		
		if ($result->num_rows = 0)
			return FALSE;

		$entry_ids = array();
		foreach ($result->result_array() as $row)
		{
			$entry_ids[] = array('entry_id' => $row['entry_id'], 'title' => $row['title']);
		}
		return $entry_ids;	
	}
	
	function get_split_assets()
	{
		$sql = "SELECT channel_id FROM exp_structure_channels WHERE type = 'asset' AND split_assets = 'y'";
		$result = $this->EE->db->query($sql);

		if ($result->num_rows > 0)
		{
			$data = array();
			foreach ($result->result_array() as $channel_id => $row)
			{
				$data[$row['channel_id']] = $this->get_entry_titles_by_channel($row['channel_id']);
			}
			return $data;
		}
		return NULL;
	}
	
	function get_listing_data()
	{
		$listing_channels = $this->get_structure_channels('listing');
		// print_r($listing_channels);
	}
	
	function get_channel_listing_entries($channel_id)
	{
		if ( ! is_numeric($channel_id))
			return FALSE;
			
		$sql = "SELECT * FROM exp_structure_listings WHERE channel_id = $channel_id AND site_id = $this->site_id";
		$result = $this->EE->db->query($sql);
		
		if ($result->num_rows > 0)
		{
			$listings = array();
			foreach ($result->result_array() as $entry)
			{
				$listings[$entry['entry_id']] = $entry;
			}
			
			return $listings;
		}
		return FALSE;
	}
	
	function get_post_vals($post_fields)
	{
		$post_vals = array();
		foreach($post_fields as $postname => $notused)
		{
			$val = $this->EE->input->get_post($postname);
			if ($val !== FALSE)
				$post_vals[$postname] = $val;
		}
		return $post_vals;
	}
	
	/**
	 * Create email
	 *
	 * @return integer|null site_id on success or NULL on error
	 **/
	function create_group($named_fields_array)
	{	
		$id = $this->EE->db->insert(
			'exp_structure_nav_groups',
			array_merge(
				$named_fields_array,
				array('site_id' => $this->site_id)
			));
		if ( ! is_null($id))
		{
			return $this->site_id;
		}
		return NULL;
	}
	
	
	
	function cleanup_check()
	{
		$vals = array();
		
		// Remove extraneous entries in exp_structure
		$site_pages = $this->get_site_pages();
		$keys = array_keys($site_pages['uris']);
		$entry_ids = implode(",", $keys);
		
		$sql = "SELECT * FROM exp_structure
				WHERE site_id = $this->site_id
				AND entry_id NOT IN ($entry_ids)";
				
		$query = $this->EE->db->query($sql);
 		$vals['duplicate_entries'] = $query->num_rows();

		// Duplicate Right Values
		$sql = "SELECT entry_id, rgt, 
		 			COUNT(rgt) AS duplicates
				FROM exp_structure
				WHERE site_id = $this->site_id
				GROUP BY rgt
					HAVING ( COUNT(rgt) > 1 )";
					
		$query = $this->EE->db->query($sql);
		$vals['duplicate_rights'] = $query->num_rows();
		
		// Duplicate Left Values
		$sql = "SELECT entry_id, lft, 
		 			COUNT(rgt) AS duplicates
				FROM exp_structure
				WHERE site_id = $this->site_id
				GROUP BY lft
					HAVING ( COUNT(lft) > 1 )";
					
		$query = $this->EE->db->query($sql);
		$vals['duplicate_lefts'] = $query->num_rows();

		return $vals;
	}
	
	
	/**
	 * Clean up invalid Structure data
	 **/
	function cleanup()
	{
		$vars = array();
		
		// Remove extraneous entries in exp_structure
		$site_pages = $this->get_site_pages();
		$keys = array_keys($site_pages['uris']);
		$entry_ids = implode(",", $keys);
		
		// Delete Structure entries
		$sql = "DELETE FROM exp_structure
				WHERE site_id = $this->site_id
				AND entry_id NOT IN ($entry_ids)";
				
		$query = $this->EE->db->query($sql);

		// Delete Listing entries
		$sql = "DELETE FROM exp_structure_listings
				WHERE site_id = $this->site_id
				AND entry_id NOT IN ($entry_ids)";
				
		$query = $this->EE->db->query($sql);
			
		// Adjust the root node's right value
		$sql = "SELECT MAX(rgt) AS max_right FROM exp_structure where site_id != 0";
		$query = $this->EE->db->query($sql);
		$max_right = $query->row('max_right') + 1;
		
		$sql = "UPDATE exp_structure SET rgt = $max_right WHERE site_id = 0";
		$this->EE->db->query($sql);
		
		return $vars;
	}
	
	function update_root_node()
	{
		$sql = "SELECT MAX(rgt) AS max_right FROM exp_structure where site_id != 0";
		$query = $this->EE->db->query($sql);
		$max_right = $query->row('max_right') + 1;
		
		$sql = "UPDATE exp_structure SET rgt = $max_right WHERE site_id = 0";
		$this->EE->db->query($sql);
	}
	
	function get_parent_uri_depth($uri)
	{
		if ($uri === NULL)
			return 0;
			
		$uri = trim($uri, '/');
		$parent_uri_array = explode('/', $uri);

		return count($parent_uri_array);	
	}
	
	function get_uri()
	{
		$uri = $this->EE->functions->remove_double_slashes('/'.$this->EE->uri->uri_string().'/');
		
		$segment_count = $this->EE->uri->total_segments();
		$last_segment = $this->EE->uri->segment($segment_count);
				
		$pagination_segment = FALSE;
		if(substr($last_segment,0,1) == 'P')
		{
			$pagination_segment = $segment_count;
			$pagination_page = substr($last_segment,1);

			$clean_array	= array();
			$dirty_array	= explode('/', $this->EE->uri->uri_string);

			// move any segments that don't match patterns to clean array
			foreach ($dirty_array as $segment)
			{
				if ($pagination_segment !== FALSE && $segment != 'P'.$pagination_page)
				{
					array_push($clean_array, $segment);	
				}
			}
			$uri = '/'.implode('/',$clean_array).'/';
		}
		return $uri;
	}
	
	
	function create_site_pages_array()
	{
		$data = array();
		
		$sql = "SELECT node.*, (COUNT(parent.entry_id) - 1) AS depth, expt.title, expt.status, expt.url_title
				FROM exp_structure AS node
				INNER JOIN exp_structure AS parent
					ON node.lft BETWEEN parent.lft AND parent.rgt
				INNER JOIN exp_channel_titles AS expt
					ON node.entry_id = expt.entry_id
				WHERE parent.lft > 1
				AND node.site_id = {$this->site_id}
				AND parent.site_id = {$this->site_id}
				GROUP BY node.entry_id
				ORDER BY node.lft";
		$result = $this->EE->db->query($sql);

		if ($result->num_rows() > 0)
		{
			foreach ($result->result_array() as $row)
			{
				$data[$row['entry_id']] = $row;
			}
		}
		$uris = array();
		foreach ($data as $key => $row)
		{
			$uris[$key] = '/';
			if ($row['parent_id'] != 0)
			{
				if($data[$row['parent_id']]['parent_id'] != 0)
				{
					if ($data[$data[$row['parent_id']]['parent_id']]['parent_id'] != 0)
					{
						// too much recursion... will fix if need be
						// if ($data[$data[$data[$row['parent_id']]['parent_id']]['parent_id']] != 0)
						// {
						// 	$new_array[$key] .= $data[$data[$data[$data[$row['parent_id']]['parent_id']]['parent_id']]['parent_id']]['url_title'].'/';
						// }
						$uris[$key] .= $data[$data[$data[$row['parent_id']]['parent_id']]['parent_id']]['url_title'].'/';
					}
					$uris[$key] .= $data[$data[$row['parent_id']]['parent_id']]['url_title'].'/';
				}
				$uris[$key] .= $data[$row['parent_id']]['url_title'].'/';
			}
			$uris[$key] .= $row['url_title'].'/';
		}
		
		// Replace underscores with hyphens
		$uris = str_replace('_', '-', $uris);
		
		$listings = array();
		$sql = "SELECT * FROM exp_structure_listings WHERE site_id = $this->site_id";
		$results = $this->EE->db->query($sql);
		
		foreach ($results->result_array() as $row)
		{
			$uris[$row['entry_id']] = $uris[$row['parent_id']].$row['uri'].'/';
		}
		
		// Get Structure Channel Data
		$sql = "SELECT ec.channel_id, ec.channel_title, esc.template_id, esc.type, ec.site_id
				FROM exp_channels AS ec 
				LEFT JOIN exp_structure_channels AS esc ON ec.channel_id = esc.channel_id
				WHERE ec.site_id = '$this->site_id'";
		
		$results = $this->EE->db->query($sql);
		

		// Format the array nicely
		$channel_data = array();
		foreach($results->result_array() as $key => $value)
		{
			$channel_data[$value['channel_id']] = $value;
			unset($channel_data[$value['channel_id']]['channel_id']);
		}
		
		$templates = array();
		
		foreach ($data as $key => $row)
		{
			$templates[$key] = $channel_data[$row['channel_id']]['template_id'];
		}
		
		$new_site_pages_array = array();
		$new_site_pages_array[$this->site_id]['uris'] = $uris;
		$new_site_pages_array[$this->site_id]['templates'] = $templates;
		$new_site_pages_array[$this->site_id]['url'] = $this->EE->functions->fetch_site_index(1, 0);
		
		$new_site_pages_array_insert = base64_encode(serialize($new_site_pages_array));
		
		echo $new_site_pages_array_insert;
		// die();
	}	
	
	function get_site_id()
	{
		$site_id = is_numeric($this->EE->config->item('site_id')) ? $this->EE->config->item('site_id') : 1;
		return $site_id;
	}
}