<?php
/**
 * ecshop 无限级上下关系描述
 * CREATE TABLE `ecs_parent` (
 * `parent_id`  int(11) UNSIGNED NOT NULL DEFAULT 0 ,
 * `user_id`  int(11) UNSIGNED NOT NULL DEFAULT 0 ,
 * `level`  int(11) UNSIGNED NOT NULL DEFAULT 1 ,
 * UNIQUE INDEX `parent_id` (`parent_id`, `user_id`) USING BTREE
 * )
 * ENGINE=InnoDB
 * DEFAULT CHARACTER SET=latin1 COLLATE=latin1_swedish_ci
 * ROW_FORMAT=COMPACT;
 */
require_once ('MysqliDb.php');
$dbi = new MysqliDb ([
	'host' => 'localhost',
	'username' => 'root',
	'password' => 'root',
	'db'=> 'test',
	'prefix' => 'ecs_'
]);

$users = [
	1 => 'a',
	2 => 'b',
	3 => 'c',
	4 => 'd',
	5 => 'e',
	6 => 'f',
	7 => 'g',
	8 => 'h',
];

init();
//delete_parent(3);
//add_parent(6, 3);
//update_parent(3, 6);
//show parents
$parents = $dbi->orderBy('parent_id', 'asc')->orderBy('level', 'asc')->get('parent');
foreach ($parents as $parent) {
	echo $users[ $parent['parent_id'] ] . ' -> ' . $users[ $parent['user_id'] ] . ', ' . $parent['level'] . '级'.PHP_EOL;
}

function init(){
	$GLOBALS['dbi']->rawQuery('truncate ecs_parent');
	//a->b
	add_parent(1, 2);
	//b->c
	add_parent(2, 3);
	//c->d
	add_parent(3, 4);
	//d->e
	add_parent(4, 5);
	//a->f
	add_parent(1, 6);
	//f->g
	add_parent(6, 7);
	//g->h
	add_parent(7, 8);
}
/**
 * 新增上下级关系
 * @param integer $parent_id 上级id
 * @param integer $user_id   下级id
 */
function add_parent($parent_id, $user_id){
	$GLOBALS['dbi']->startTransaction();
	try{
		//建立跟上级的关系
		$GLOBALS['dbi']->insert('parent', ['parent_id' => $parent_id, 'user_id' => $user_id, 'level' => 1]);
		//如果上级有上级，把我跟每一个上级建立关系
		$grandpas = $GLOBALS['dbi']->where('user_id', $parent_id)->get('parent', null, ['parent_id', 'level']);
		foreach ($grandpas as $parent){
			$GLOBALS['dbi']->insert('parent', ['parent_id' => $parent['parent_id'], 'user_id' => $user_id, 'level' => $parent['level']+1 ]);
		}
		//如果我有下级，把我所有下级跟我的上级建立关系
		$children = $GLOBALS['dbi']->where('parent_id', $user_id)->get('parent', null, ['user_id', 'level']);
		foreach ($children as $child){
			$GLOBALS['dbi']->insert('parent', ['parent_id' => $parent_id, 'user_id' => $child['user_id'], 'level' => $child['level']+1]);
			//还要跟我的上级的所有上级建立关系
			foreach ($grandpas as $parent){
				$GLOBALS['dbi']->insert('parent', ['parent_id' => $parent['parent_id'], 'user_id' => $child['user_id'], 'level' => $child['level'] + $parent['level'] + 1 ]);
			}
		}
		$GLOBALS['dbi']->commit();
	} catch(Exception $e){
		$GLOBALS['dbi']->rollback();
	}
}
/**
 * 更改上级
 * @param  integer $user_id  用户id
 * @param  integer $parent_id 新上级id，为0时仅去除原有上级
 */
function update_parent($user_id, $parent_id = 0){
	delete_parent($user_id);
	if($parent_id > 0){
		add_parent($parent_id, $user_id);
	}
}
/**
 * 去除上级
 * @param  integer $user_id  用户id
 */
function delete_parent($user_id){
	$GLOBALS['dbi']->startTransaction();
	try{
		//先跟原来的上级们断掉关系
		$GLOBALS['dbi']->where('user_id', $user_id)->delete('parent');
		//要断绝跟上级们的关系，还得加上我的下级们
		$children = $GLOBALS['dbi']->where('parent_id', $user_id)->get('parent', null, ['user_id', 'level']);
		foreach ($children as $child){
			//对每一个下级而言，跟“我”的关系不用变，但是在我之上的更远关系应该删除，无论更远的上级是谁
			$GLOBALS['dbi']->where('user_id', $child['user_id'])->where('level', ['>' => $child['level']])->delete('parent');
		}
		$GLOBALS['dbi']->commit();
	} catch(Exception $e){
		$GLOBALS['dbi']->rollback();
	}
}