<?php 

/*

	- MYSQL version < 8.0 does not support Recursive Table Expression!
	- MYSQL "stored procedure" does not allow more than 225 recursions
	- The only option for cross server compactibility is "Nested Set Model"
	- However, `user synthetics` uses Adjacency List Model
	
	- Fortunately, most modern server uses MariaDB in PHPMYAdmin (which supports recursion)
	- And for those using lower version of MYSQL, we'll look into your case in the future
	
*/

class hierarchy {
	
	protected string $recursiveChildren;
	protected string $recursiveParent;
	
	public function __construct() {
		
		$prefix = DB_TABLE_PREFIX;
		
		$this->recursiveChildren = "
		
			WITH RECURSIVE cte as ( 
			
				SELECT 
					{$prefix}_users.*, 
					0 as depth 
				FROM {$prefix}_users
				WHERE id = '%1\$s' 
				
					UNION ALL 
					
				SELECT 
					{$prefix}_users.*, 
					depth + 1 
				FROM cte 
				INNER JOIN (
					SELECT * FROM {$prefix}_users 
					WHERE id <> parent
				) AS {$prefix}_users
					ON cte.id = {$prefix}_users.parent 
				
			) SELECT * FROM cte 
				GROUP BY id 
				ORDER BY depth, id
			
		";
		
		$this->recursiveParent = "
			
			WITH RECURSIVE cte AS (
			
				SELECT 
					{$prefix}_users.*,
					0 as depth
				FROM {$prefix}_users
				WHERE id = '%s'
				
					UNION
					
				SELECT 
					{$prefix}_users.*,
					depth + 1
				FROM (
					SELECT * FROM {$prefix}_users 
					WHERE id <> parent
				) AS {$prefix}_users
				INNER JOIN cte 
					ON cte.parent = {$prefix}_users.id
				WHERE cte.id <> cte.parent
					
			) SELECT * FROM cte
				GROUP BY id
				ORDER BY depth
			
		";
		
	}
	
	
	public function __get( $property ) {
		return property_exists( $this, $property ) ? $this->{$property} : null;
	}
	
	
	protected function traversal( string $recursion, ?int $userid, ?string $query = null ) {
		
		if( !$userid ) return false;
		
		$SQL = sprintf("
			SELECT * FROM (
				{$recursion}
			) AS hierarchy
			WHERE depth > 0 AND {$query}
		", $userid);
		
		return uss::$global['mysqli']->query( $SQL );
		
	}
	
	public function descendants_of( ?int $userid, ?string $query = '1' ) {
		return $this->traversal( $this->recursiveChildren, $userid, $query );
	}
	
	
	public function ancestors_of( ?int $userid, ?string $query = '1' ) {
		return $this->traversal( $this->recursiveParent, $userid, $query );
	}

}