<?php

// Examples of basic IPTVPORTAL API requests


$_iptvportal_server = 'admin.DOMAIN.iptvportal.ru'; // DOMAIN = Your domain in the IPTVPORTAL platform
$_username='admin';
$_password='PASSWORD';


date_default_timezone_set('Europe/Moscow');
set_time_limit(1260);

$_auth_uri='https://'.$_iptvportal_server.'/api/jsonrpc/';
$_jsonsql_uri='https://'.$_iptvportal_server.'/api/jsonsql/';
$_iptvportal_header=null;

function send($url, $data, $extra_headers=null) {
    $ch=curl_init();
    curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 1260);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_TIMEOUT, 1260);
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
    curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
    curl_setopt($ch, CURLOPT_POST, true);
    curl_setopt($ch, CURLOPT_POSTFIELDS, $data);
    if (isset($extra_headers)) {
        curl_setopt($ch, CURLOPT_HTTPHEADER, $extra_headers);
    }
    //echo "HTTP fetching '$url'...\n" . chr(10);
    $content=curl_exec($ch);
    $http_code=curl_getinfo($ch, CURLINFO_HTTP_CODE);
    if ($content === false) {
        $err_msg="HTTP error: $http_code (" . curl_error($ch) . ')' . '\n';
        echo $err_msg . chr(10);
        throw new Exception($err_msg);
    }
    if ($http_code != 200) {
        $err_msg="HTTP request failed ($http_code)\n";
        echo $err_msg . chr(10);
        throw new Exception($err_msg);
		print $content;
    }
    //echo "HTTP OK ($http_code)\n";
    curl_close($ch);
    return $content;
}

// end send -------------------------

function jsonrpc_call($url, $method, $params, $extra_headers=null) {
    static $req_id=1;
	if (is_array($method)) {
		$methodlist = $method;
		//echo "<br/>methodlist = ".json_encode($methodlist)."<br/>";
		$req=array();
		foreach ($methodlist as $method_params) {
			$method = $method_params[0];
			$params = $method_params[1];
			//echo "<br/>method = ".json_encode($method)."<br/>";
			//echo "<br/>params = ".json_encode($params)."<br/>";
			array_push($req,array(
            	"jsonrpc"=>'2.0',
            	"id"=>$req_id++,
            	"method"=>$method,
            	"params"=>$params
    		));
		}
	} else {
		//echo "<br/>method1 = ".json_encode($method)."<br/>";
		//echo "<br/>params1 = ".json_encode($params)."<br/>";
    	$req=array(
            "jsonrpc"=>'2.0',
            "id"=>$req_id++,
            "method"=>$method,
            "params"=>$params
    	);
	}
    $req=json_encode($req);
	//echo "<br/>".$req ."<br/>";
    $res=send($url, $req, $extra_headers=$extra_headers);
    //echo "<br/>".$res ."<br/>";
    $res=json_decode($res, true);
    if (!isset($res)) {
        echo "<br/>"."error: not result\n"."<br/>";
        return null;
    } else if (!array_key_exists('result', $res) || !isset($res ['result'])) {
		if (is_array($res)) {
			return $res;
		} else {
			print_r("<br/> Error 'result not found'".$res ['error']."<br/>");
        	return null;
		}
    } else {
        return $res ['result'];
    }
    return $res;
}


function jsonsql_call($cmd, $params = null) {
    global $_jsonsql_uri, $_iptvportal_header;
    //echo 'iptvportal_header: ';	print_r ($_iptvportal_header);
    return jsonrpc_call($_jsonsql_uri, $cmd, $params, $extra_headers=$_iptvportal_header);
}

function authorize_user($auth_uri, $username, $password) {
    global $_iptvportal_header;
    $res=jsonrpc_call($auth_uri, $cmd="authorize_user", $params=array(
            'username'=>$username,
            'password'=>$password
    ));
    if (isset($res) && array_key_exists('session_id', $res)) {
        $_iptvportal_header=array('Iptvportal-Authorization: ' . 'sessionid=' . $res ['session_id']);
    }
    return $res;
}


//The first step is to authorize and get the session key.
$user=authorize_user($auth_uri=$_auth_uri, $username=$_username, $password=$_password);
//echo 'authorize user result: '; print_r ($user); echo '<br> <br>';

function PrintTable($array_table_head, $array_table_values) {
	print_r('<br/><table border=1>');
	
	print_r('<tr>');
	for ($i=0; $i < count($array_table_head); $i++) {
		print_r('<td>');
		print_r($array_table_head[$i]);
		print_r('</td>');
	}
	print_r('</tr>');
	
	foreach ($array_table_values as $array_table_row) {
		print_r('<tr>');
		for ($i=0; $i < count($array_table_row); $i++) {
			print_r('<td>');
			print_r($array_table_row[$i]);
			print_r('</td>');
		}
		print_r('</tr>');
	}
	
	print_r('</table><br/>');
	return true;
}


//start sql select -----------------------------------------------------------------------------------------------------------------------


print_r('<br/><br/>------------------------------------------------------------------------------------------------<br/><br/>');
print_r('Get subscribers <br/>');

$jsonrcp_list = array();
$jsonrcp = array("select", array(
	"data"=> array(
		array("s"=>"id"), 
		array("s"=>"username"),
		array("s"=>"password"),
		array("s"=>"disabled"),
	),
	"from"=> array(
		array("table"=> "subscriber", "as"=> "s"),
	),
	//"where"=> array(),
	//"group_by"=>array(),
	"order_by"=> array( array("s"=>"username") )
));
array_push($jsonrcp_list,$jsonrcp);
$res_jsonsql_call=jsonsql_call($jsonrcp_list);

foreach ($res_jsonsql_call as $r) {
	if (isset($r['result'])){
		PrintTable($jsonrcp_list[0][1]['data'],$r['result']);
	} else {
		print_r("'result' not found: ".json_encode($r['error']) );
	}
}


print_r('<br/><br/>------------------------------------------------------------------------------------------------<br/><br/>');
print_r('Get subscribers and subscriber_package <br/>');

$jsonrcp_list = array();
$jsonrcp = array("select", array(
	"data"=> array(
		array("s"=>"id"), 
		array("s"=>"username"),
		array("s"=>"password"),
		array("s"=>"disabled"),
		array("p"=>"id"),
		array("p"=>"name"),
		array("sp"=>"disabled"),
	),
	"from"=> array(
		array("table"=> "subscriber", "as"=> "s"),
		array('join'=> "subscriber_package", 'as'=> "sp", 'on'=> 
			array('and'=> array( 
				array('eq'=> array( array('sp'=>"subscriber_id"), array('s'=>"id") )), 
			))
		),
		array('join'=> "package", 'as'=> "p", 'on'=> 
			array('and'=> array( 
				array('eq'=> array( array('p'=>"id"), array('sp'=>"package_id") )), 
			))
		),
	),
	//"where"=> array('in'=> array( array('s'=>"username"), ['username_1','username_2','username_3'] ))  , // -- if need select only for users list
	//"group_by"=>array(),
	//"order_by"=> array()
));
array_push($jsonrcp_list,$jsonrcp);
$res_jsonsql_call=jsonsql_call($jsonrcp_list);

foreach ($res_jsonsql_call as $r) {
	if (isset($r['result'])){
		PrintTable($jsonrcp_list[0][1]['data'],$r['result']);
	} else {
		print_r("'result' not found: ".json_encode($r['error']) );
	}
}


print_r('<br/><br/>------------------------------------------------------------------------------------------------<br/><br/>');
print_r('Get ALL subscribers and subscriber_package <br/>');

$jsonrcp_list = array();
$jsonrcp = array("select", array(
	"data"=> array(
		array("s"=>"id"), 
		array("s"=>"username"),
		array("s"=>"password"),
		array("s"=>"disabled"),
		array("p"=>"id"),
		array("p"=>"name"),
		array("sp"=>"disabled"),
	),
	"from"=> array(
		array("table"=> "subscriber", "as"=> "s"),
		array('join'=> "subscriber_package", "join_type"=> "left", 'as'=> "sp", 'on'=> 
			array('and'=> array( 
				array('eq'=> array( array('sp'=>"subscriber_id"), array('s'=>"id") )), 
			))
		),
		array('join'=> "package", "join_type"=> "left", 'as'=> "p", 'on'=> 
			array('and'=> array( 
				array('eq'=> array( array('p'=>"id"), array('sp'=>"package_id") )), 
			))
		),
	),
	//"where"=> array('in'=> array( array('s'=>"username"), ['username_1','username_2','username_3'] ))  , // -- if need select only for users list
	//"group_by"=>array(),
	//"order_by"=> array()
));
array_push($jsonrcp_list,$jsonrcp);
$res_jsonsql_call=jsonsql_call($jsonrcp_list);

foreach ($res_jsonsql_call as $r) {
	if (isset($r['result'])){
		PrintTable($jsonrcp_list[0][1]['data'],$r['result']);
	} else {
		print_r("'result' not found: ".json_encode($r['error']) );
	}
}


print_r('<br/><br/>------------------------------------------------------------------------------------------------<br/><br/>');
print_r('create subscriber (without checking its existence) <br/>');

$jsonrcp_list = array();
$jsonrcp = array("insert", array (
    "into" => "subscriber",
    "columns" => array ("username", "password"),
    "values" => array (
        "username" => "new_subscriber_0",
        "password" => "111",
    ),
    "returning" => array("id","username"),
));
array_push($jsonrcp_list,$jsonrcp);
$res_jsonsql_call=jsonsql_call($jsonrcp_list);

foreach ($res_jsonsql_call as $r) {
	if (isset($r['result'])){
		print_r($r['result']);
	} else {
		print_r("'result' not found: ".json_encode($r['error']) );
	}
}


print_r('<br/><br/>------------------------------------------------------------------------------------------------<br/><br/>');
print_r('create subscribers list with checking of subscriber existence <br/>');

$subscriber_username_password = array(
	array("new_subscriber_1","pass111"),
	array("new_subscriber_2","pass222"),
	array("new_subscriber_3","pass333"),
);

$jsonrcp_list = array();
$jsonrcp = array("insert", array (
    "into" => "subscriber",
    "columns" => array ("username", "password"),
	"select" => array(
		"data" => array ("username", "password"),
		"from"=> array(
			"values" => $subscriber_username_password,
			"as"=> array("values_array","username","password") // first value = table name. following = column names
		),
		"where" => array(
			"not_in"=> array(
				array("values_array"=>"username"),
				array("select" => array(
					"data" => array ("username"),
					"from"=> array(
						array("table"=> "subscriber", "as"=> "s"),
					),
				)),
			),
		),
	),
    "returning" => array("id","username"),
));
array_push($jsonrcp_list,$jsonrcp);
$res_jsonsql_call=jsonsql_call($jsonrcp_list);

foreach ($res_jsonsql_call as $r) {
	if (isset($r['result'])){
		print_r($r['result']);
	} else {
		print_r("'result' not found: ".json_encode($r['error']) );
	}
}


print_r('<br/><br/>------------------------------------------------------------------------------------------------<br/><br/>');
print_r('update subscribers list <br/>');

$subscriber_username_password = array(
	array("new_subscriber_1","pass111"),
	array("new_subscriber_2","pass222"),
	array("new_subscriber_3","pass333"),
);

$jsonrcp_list = array();
$jsonrcp = array("update", array (
    "table" => array("table"=> "subscriber", "as"=> "s"),
    "set" => array (
		"username" => array('values_array'=>"username"),
		"password" => array('values_array'=>"password"),
	),
	"from"=> array(
		"values" => $subscriber_username_password,
		"as"=> array("values_array","username","password") // first value = table name. following = column names
	),
	"where"=> array(
		"eq" => array( array("s"=>"username"), array("values_array"=>"username") ),
	),
    "returning" => array( array("s"=>"id"), array("s"=>"username") ),
));
array_push($jsonrcp_list,$jsonrcp);
$res_jsonsql_call=jsonsql_call($jsonrcp_list);

foreach ($res_jsonsql_call as $r) {
	if (isset($r['result'])){
		print_r($r['result']);
	} else {
		print_r("'result' not found: ".json_encode($r['error']) );
	}
}


print_r('<br/><br/>------------------------------------------------------------------------------------------------<br/><br/>');
print_r('add packages to subscriber <br/>');

$jsonrcp_list = array();
$jsonrcp = array("insert", array (
    "into" => "subscriber_package",
    "columns" => array ("subscriber_id", "package_id", "enabled"),
    "select" => array (
        "data" => array (array ("s" => "id"), array ("p" => "id"), true),
        "from" => array (array (
            "table" => "subscriber", "as" => "s"
        ), array (
            "table" => "package", "as" => "p"
        )),
        "where" => array (
            "and" => array (array (
                "eq" => array (array ("s" => "username"), "123456")
            ), array (
                "in" => array (array ("p" => "name"), "Paid_Package_1", "Paid_Package_2", "Paid_Package_3")
            ))
        )
    ),
    "returning" => array ("subscriber_id", "package_id")
));
array_push($jsonrcp_list,$jsonrcp);
$res_jsonsql_call=jsonsql_call($jsonrcp_list);

foreach ($res_jsonsql_call as $r) {
	if (isset($r['result'])){
		print_r($r['result']);
	} else {
		print_r("'result' not found: ".json_encode($r['error']) );
	}
}


print_r('<br/><br/>------------------------------------------------------------------------------------------------<br/><br/>');
print_r('add packages to subscribers list with checking of subscriber_package existence <br/>');
print_r('if the subscriber_package already exists, then updating the information <br/>');

$subscriber_username_package = array(
	array("new_test_subscriber_1","Paid_Package_1"),
	array("new_test_subscriber_1","Paid_Package_2"),
	array("new_test_subscriber_2","Paid_Package_2"),
	array("new_test_subscriber_3","Paid_Package_2"),
);


$jsonrcp_list = array();
$jsonrcp = array("insert", array (
    "into" => "subscriber_package",
    "columns" => array ("subscriber_id","package_id","enabled"),
	"select" => array(
		"data" => array (array('s'=>"id"),array('p'=>"id"),true),
		"from"=> array(
			array( "values" => $subscriber_username_package, 
					"as"=> array("values_array","username","package_name") // first value = table name. following = column names
			),
			array('join'=> "subscriber", 'as'=> "s", 'on'=> 
				array('and'=> array( 
					array('eq'=> array( array('s'=>"username"), array('values_array'=>"username") )),
				))
			),
			array('join'=> "package", 'as'=> "p", 'on'=> 
				array('and'=> array( 
					array('eq'=> array( array('p'=>"name"), array('values_array'=>"package_name") )), 
				))
			),
		),
		"where" => array(
			"not_in"=> array(
				array("concat" => array( array("s"=>"id"),'---',array("p"=>"id") )),
				array("select" => array(
					"data" => array("concat" => array( array("sp"=>"subscriber_id"),'---',array("sp"=>"package_id") )),
					"from"=> array(
						array("table"=> "subscriber_package", "as"=> "sp"),
					),
				)),
			),
		),
	),
    "returning" => array("subscriber_id","package_id"),
));
array_push($jsonrcp_list,$jsonrcp);

$jsonrcp = array("update", array (
    "table" => array("table"=> "subscriber_package", "as"=> "sp"),
    "set" => array (
		"enabled" => true,
	),
//	"from" => array( 
//		"values" => $subscriber_username_package_demanded, 
//		"as"=> array("values_array","username","package_name") // first value = table name. following = column names
//	),
	"where" => array(
		"in"=> array(
			array("concat" => array( array("sp"=>"subscriber_id"),'---',array("sp"=>"package_id") )),
			array("select"=> array(
				"data" => array("concat" => array( array("s"=>"id"),'---',array("p"=>"id") )),
				"from"=> array( 
					array("values" => $subscriber_username_package, 
						"as"=> array("values_array","username","package_name") // first value = table name. following = column names
					), 
					array('join'=> "subscriber", 'as'=> "s", 'on'=> 
						array('and'=> array( 
							array('eq'=> array( array('s'=>"username"), array('values_array'=>"username") )),
						))
					),
					array('join'=> "package", 'as'=> "p", 'on'=> 
						array('and'=> array( 
							array('eq'=> array( array('p'=>"name"), array('values_array'=>"package_name") )), 
						))
					),
				),

			))
		)
	
		
	),
    "returning" => array("subscriber_id","package_id"),
));
array_push($jsonrcp_list,$jsonrcp);

$res_jsonsql_call=jsonsql_call($jsonrcp_list);

foreach ($res_jsonsql_call as $r) {
	if (isset($r['result'])){
		//print_r($r['result']);
		PrintTable($jsonrcp_list[0][1]['returning'],$r['result']);
	} else {
		print_r("'result' not found: ".json_encode($r['error']) );
	}
}


print_r('<br/><br/>------------------------------------------------------------------------------------------------<br/><br/>');
print_r('update subscriber (disconnection) <br/>');

$jsonrcp_list = array();
$jsonrcp = array("update", array (
    "table" => "subscriber",
    "set" => array (
        "disabled" => true
    ),
    "where" => array ("eq" => array ("username", "123456")),
    "returning" => array("id","username"),
));
array_push($jsonrcp_list,$jsonrcp);
$res_jsonsql_call=jsonsql_call($jsonrcp_list);

foreach ($res_jsonsql_call as $r) {
	if (isset($r['result'])){
		print_r($r['result']);
	} else {
		print_r("'result' not found: ".json_encode($r['error']) );
	}
}


print_r('<br/><br/>------------------------------------------------------------------------------------------------<br/><br/>');
print_r('delete custom packages at subscriber <br/>');

$jsonrcp_list = array();
$jsonrcp = array("delete", array (
    "from" => "subscriber_package",
    "where" => array ("and" => array (
        array ("in" => array ("subscriber_id", array (
            "select" => array (
                "data" => "id",
                "from" => "subscriber",
                "where" => array ("eq" => array ("username", "123456"))
            )
        ))), array ("in" => array ("package_id", array (
            "select" => array (
                "data" => "id",
                "from" => "package",
                "where" => array ("in" => array ("name", array("Paid_Package_1", "Paid_Package_2") ))
            )
        )))
    )),
    "returning" => array("subscriber_id","package_id")
));
array_push($jsonrcp_list,$jsonrcp);
$res_jsonsql_call=jsonsql_call($jsonrcp_list);

foreach ($res_jsonsql_call as $r) {
	if (isset($r['result'])){
		print_r($r['result']);
	} else {
		print_r("'result' not found: ".json_encode($r['error']) );
	}
}


print_r('<br/><br/>------------------------------------------------------------------------------------------------<br/><br/>');
print_r('delete all packages at subscriber <br/>');

$jsonrcp_list = array();
$jsonrcp = array("delete", array (
    "from" => "subscriber_package",
    "where" => array ("in" => array ("subscriber_id", array (
        "select" => array (
            "data" => "id",
            "from" => "subscriber",
            "where" => array ("eq" => array ("username", "123456"))
        )
    ))),
    "returning" => array("subscriber_id","package_id")
));
array_push($jsonrcp_list,$jsonrcp);
$res_jsonsql_call=jsonsql_call($jsonrcp_list);

foreach ($res_jsonsql_call as $r) {
	if (isset($r['result'])){
		print_r($r['result']);
	} else {
		print_r("'result' not found: ".json_encode($r['error']) );
	}
}

//-----------------------------------------------------------------------------------------------------------------------
// channel list insert+update


print_r('<br/><br/>------------------------------------------------------------------------------------------------<br/><br/>');
print_r('create channel list with checking of existence <br/>');
print_r('if the already exists, then updating the information <br/>');

$media_columns_insert = array("id", "index", "name", "protocol", "inet_addr", "port", "path","channel_id", "is_tv");
$media_columns_update = array("id", "index", "name", "protocol", "inet_addr", "port", "path","channel_id", "is_tv", "timeshift_url", "timeshift_archive_length",);

//$ts_interval = array('cast' => array('24:00:00', 'interval'));
$ts_interval = array('cast' => array('7 days', 'interval'));

$media_list = array(
	array(10001, 1,"The Tast Channel 1","http","streamer.net",80,"ch_name_1/index.m3u8",4710, true , "http://streamer.net:80/ch_name_1", $ts_interval ),
	array(10002, 2,"The Tast Channel 2","http","streamer.net",80,"ch_name_2/index.m3u8",5150, true , "http://streamer.net:80/ch_name_2", $ts_interval ),
	array(10003, 3,"The Tast Channel 3","http","streamer.net",80,"ch_name_3/index.m3u8",5170, true , "http://streamer.net:80/ch_name_3", $ts_interval ),
);

$table_set__media_columns_update = array();
foreach($media_columns_update as $k => $param) {
    $table_set__media_columns_update[$param] = array( 'values_array' => $media_columns_update[$k] );
}
//print_r($table_set__media_columns_update);

$jsonrcp_list = array();
$jsonrcp = array("insert", array (
    "into" => "media",
    "columns" => $media_columns_insert,
	"select" => array(
		"data" => $media_columns_insert,
		"from"=> array(
			"values" => array_slice($media_list,0,count($media_columns_insert)),
			"as"=> array_merge(["values_array"], $media_columns_insert), // first value = table name. following = column names  // insert "values_array" to first value in columns names array
		),
		"where" => array(
			"not_in"=> array(
				array("values_array"=>"id"),
				array("select" => array(
					"data" => array ("id"),
					"from"=> array(
						array("table"=> "media", "as"=> "m"),
					),
				)),
			),
		),
	),
    "returning" => array("id","name"),
));
array_push($jsonrcp_list,$jsonrcp);

$jsonrcp = array("update", array (
    "table" => array("table"=> "media", "as"=> "m"),
    "set" => $table_set__media_columns_update, // format: "table m param 1" => array('values_array'=>"table values_array param 1"), ...
	"from" => array(
		"values"=> array_slice($media_list,0,count($media_columns_update)), 
		"as"=> array_merge(["values_array"], $media_columns_update)
	),
    "where" => array ('and'=> array( 
		array('eq'=> array( array('m'=>"id"), array('values_array'=>"id") )), 
	)),
    "returning" => array(array('m'=>"id"),array('m'=>"name")),
));
array_push($jsonrcp_list,$jsonrcp);
//print_r(json_encode($jsonrcp_list));
$res_jsonsql_call=jsonsql_call($jsonrcp_list);

foreach ($res_jsonsql_call as $r) {
	if (isset($r['result'])){
		//print_r($r['result']);
		PrintTable($jsonrcp_list[0][1]['returning'],$r['result']);
	} else {
		//print_r("'result' not found: ".json_encode($r['error']) );
		print_r( $r['error'] );
	}
}


print_r('<br/><br/>------------------------------------------------------------------------------------------------<br/><br/>');
print_r('add packages to media list with checking of existence <br/>');
print_r('if the already exists, then updating the information <br/>');

$media_name__package_name = array(
	array("The Tast Channel 1","Paid_Package_1"),
	array("The Tast Channel 2","Paid_Package_2"),
	array("The Tast Channel 3","Paid_Package_2"),
);


$jsonrcp_list = array();
$jsonrcp = array("insert", array (
    "into" => "package_media",
    "columns" => array ("media_id","package_id","disabled"),
	"select" => array(
		"data" => array (array('m'=>"id"),array('p'=>"id"),false),
		"from"=> array(
			array( "values" => $media_name__package_name, 
					"as"=> array("values_array","media_name","package_name") // first value = table name. following = column names
			),
			array('join'=> "media", 'as'=> "m", 'on'=> 
				array('and'=> array( 
					array('eq'=> array( array('m'=>"name"), array('values_array'=>"media_name") )),
				))
			),
			array('join'=> "package", 'as'=> "p", 'on'=> 
				array('and'=> array( 
					array('eq'=> array( array('p'=>"name"), array('values_array'=>"package_name") )), 
				))
			),
		),
		"where" => array(
			"not_in"=> array(
				array("concat" => array( array("m"=>"id"),'---',array("p"=>"id") )),
				array("select" => array(
					"data" => array("concat" => array( array("pm"=>"media_id"),'---',array("pm"=>"package_id") )),
					"from"=> array(
						array("table"=> "package_media", "as"=> "pm"),
					),
				)),
			),
		),
	),
    "returning" => array("media_id","package_id"),
));
array_push($jsonrcp_list,$jsonrcp);

$jsonrcp = array("update", array (
    "table" => array("table"=> "package_media", "as"=> "pm"),
    "set" => array (
		"disabled" => false,
	),
	"where" => array(
		"in"=> array(
			array("concat" => array( array("pm"=>"media_id"),'---',array("pm"=>"package_id") )),
			array("select"=> array(
				"data" => array("concat" => array( array("m"=>"id"),'---',array("p"=>"id") )),
				"from"=> array( 
					array("values" => $media_name__package_name, 
						"as"=> array("values_array","media_name","package_name") // first value = table name. following = column names
					), 
					array('join'=> "media", 'as'=> "m", 'on'=> 
						array('and'=> array( 
							array('eq'=> array( array('m'=>"name"), array('values_array'=>"media_name") )),
						))
					),
					array('join'=> "package", 'as'=> "p", 'on'=> 
						array('and'=> array( 
							array('eq'=> array( array('p'=>"name"), array('values_array'=>"package_name") )), 
						))
					),
				),

			))
		)
	
		
	),
    "returning" => array("media_id","package_id"),
));
array_push($jsonrcp_list,$jsonrcp);

$res_jsonsql_call=jsonsql_call($jsonrcp_list);

foreach ($res_jsonsql_call as $r) {
	if (isset($r['result'])){
		//print_r($r['result']);
		PrintTable($jsonrcp_list[0][1]['returning'],$r['result']);
	} else {
		print_r("'result' not found: ".json_encode($r['error']) );
	}
}


//-----------------------------------------------------------------------------------------------------------------------
// vod list insert+update


print_r('<br/><br/>------------------------------------------------------------------------------------------------<br/><br/>');
print_r('create VOD list with checking of existence <br/>');
print_r('if the already exists, then updating the information <br/>');

$media_columns_update = array(
	"id", 
	"index", 
	"name",
	"is_vod",
	
	"protocol",
	"inet_addr", 
	"port", 
	"path",
	"is_folder", 
	"parent_id",
	"auth",
	"duration",
	"country",
	"year",
	"director",
	"stars",
	"genre",
	"description",
	"rating",
	"kp_rating",
	"imdb_rating",
	"thumbnail_url",
	"image_url",
	"trailer_url",
	"trailer_duration",
	"age_limit",
	"comment",
);
$media_columns_insert = array_slice($media_columns_update,0,4); // first 4 columns are required


$media_list = array(
	array(
		220000, // "id"
		1, // "index"
		"Serial", // "name"
		true, // "is_vod"
		
		NULL, // "protocol"
		NULL, // "inet_addr"
		NULL, // "port"
		NULL, // "path"
		true, // "is_folder"
		0, // "parent_id"
		"", // "auth"
		NULL, // "duration"
		"USA1", // "country":
		2018, // "year"
		"Name1", // "director"
		"Actors1", // "stars"
		"comedy1", // "genre"
		"Info1", // "description"
		4, // "rating"
		4, // "kp_rating"
		4, // "imdb_rating"
		"http://thumbnail_url.com/image11.png", // "thumbnail_url"
		"http://image_url.com/image11.png", // "image_url"
		NULL, // "trailer_url"
		NULL, // "trailer_duration"
		NULL, // "age_limit"
		"comment to media", // "comment"
	),
	array(
		220001, // "id"
		1, // "index"
		"Series 1", // "name"
		true, // "is_vod"
		
		"http", // "protocol"
		"streamer.net", // "inet_addr"
		80, // "port"
		"ch_name_1/index.m3u8", // "path"
		false, // "is_folder"
		220000, // "parent_id"
		"arescrypt", // "auth"
		array('cast' => array('01:21:33', 'interval')), // "duration"
		"USA1", // "country":
		2018, // "year"
		"Name1", // "director"
		"Actors1", // "stars"
		"comedy1", // "genre"
		"Info1", // "description"
		4, // "rating"
		4, // "kp_rating"
		4, // "imdb_rating"
		"http://thumbnail_url.com/image11.png", // "thumbnail_url"
		"http://image_url.com/image11.png", // "image_url"
		"http://movie_trailer.com/video11.m3u8", // "trailer_url"
		array('cast' => array('00:15:00', 'interval')), // "trailer_duration"
		NULL, // "age_limit"
		"comment to media", // "comment"
	),
	array(
		220002, // "id"
		1, // "index"
		"Series 2", // "name"
		true, // "is_vod"
		
		"http", // "protocol"
		"streamer.net", // "inet_addr"
		80, // "port"
		"ch_name_1/index.m3u8", // "path"
		false, // "is_folder"
		220000, // "parent_id"
		"arescrypt", // "auth"
		array('cast' => array('01:21:33', 'interval')), // "duration"
		"USA1", // "country":
		2018, // "year"
		"Name1", // "director"
		"Actors1", // "stars"
		"comedy1", // "genre"
		"Info1", // "description"
		4, // "rating"
		4, // "kp_rating"
		4, // "imdb_rating"
		"http://thumbnail_url.com/image11.png", // "thumbnail_url"
		"http://image_url.com/image11.png", // "image_url"
		"http://movie_trailer.com/video11.m3u8", // "trailer_url"
		array('cast' => array('00:15:00', 'interval')), // "trailer_duration"
		18, // "age_limit"
		"comment to media", // "comment"
	),
);

$table_set__media_columns_update = array();
foreach($media_columns_update as $k => $param) {
    $table_set__media_columns_update[$param] = array( 'values_array' => $media_columns_update[$k] );
}
//print_r($table_set__media_columns_update);

$jsonrcp_list = array();
$jsonrcp = array("insert", array (
    "into" => "media",
    "columns" => $media_columns_insert,
	"select" => array(
		"data" => $media_columns_insert,
		"from"=> array(
			"values" => array_slice($media_list,0,count($media_columns_insert)),
			"as"=> array_merge(["values_array"], $media_columns_insert), // first value = table name. following = column names  // insert "values_array" to first value in columns names array
		),
		"where" => array(
			"not_in"=> array(
				array("values_array"=>"id"),
				array("select" => array(
					"data" => array ("id"),
					"from"=> array(
						array("table"=> "media", "as"=> "m"),
					),
				)),
			),
		),
	),
    "returning" => array("id","name"),
));
array_push($jsonrcp_list,$jsonrcp);

$jsonrcp = array("update", array (
    "table" => array("table"=> "media", "as"=> "m"),
    "set" => $table_set__media_columns_update, // format: "table m param 1" => array('values_array'=>"table values_array param 1"), ...
	"from" => array(
		"values"=> array_slice($media_list,0,count($media_columns_update)), 
		"as"=> array_merge(["values_array"], $media_columns_update)
	),
    "where" => array ('and'=> array( 
		array('eq'=> array( array('m'=>"id"), array('values_array'=>"id") )), 
	)),
    "returning" => array(array('m'=>"id"),array('m'=>"name")),
));
array_push($jsonrcp_list,$jsonrcp);
//print_r(json_encode($jsonrcp_list));
$res_jsonsql_call=jsonsql_call($jsonrcp_list);

foreach ($res_jsonsql_call as $r) {
	if (isset($r['result'])){
		//print_r($r['result']);
		PrintTable($jsonrcp_list[0][1]['returning'],$r['result']);
	} else {
		//print_r("'result' not found: ".json_encode($r['error']) );
		print_r( $r['error'] );
	}
}


print_r('<br/><br/>------------------------------------------------------------------------------------------------<br/><br/>');
print_r('add rubrics to VOD list at IDs with checking of existence <br/>');
print_r('if the already exists, then updating the information <br/>');

$media_id__playlist_name = array(
	array(220000,"vod-all"),
	array(220000,"vod-thriller"),
	array(220000,"vod-action"),
	
	array(220001,"vod-all"),
	array(220001,"vod-thriller"),
	array(220001,"vod-action"),
	
	array(220002,"vod-all"),
	array(220002,"vod-thriller"),
	array(220002,"vod-action"),
);


$jsonrcp_list = array();
$jsonrcp = array("insert", array (
    "into" => "playlist_media",
    "columns" => array ("media_id","playlist_id","disabled"),
	"select" => array(
		"data" => array (array('values_array'=>"media_id"),array('p'=>"id"),false),
		"from"=> array(
			array( "values" => $media_id__playlist_name, 
					"as"=> array("values_array","media_id","playlist_name") // first value = table name. following = column names
			),
			array('join'=> "playlist", 'as'=> "p", 'on'=> 
				array('and'=> array( 
					array('eq'=> array( array('p'=>"name"), array('values_array'=>"playlist_name") )), 
				))
			),
		),
		"where" => array(
			"not_in"=> array(
				array("concat" => array( array("values_array"=>"media_id"),'---',array("p"=>"id") )),
				array("select" => array(
					"data" => array("concat" => array( array("pm"=>"media_id"),'---',array("pm"=>"playlist_id") )),
					"from"=> array(
						array("table"=> "playlist_media", "as"=> "pm"),
					),
				)),
			),
		),
	),
    "returning" => array("media_id","playlist_id"),
));
array_push($jsonrcp_list,$jsonrcp);

$jsonrcp = array("update", array (
    "table" => array("table"=> "playlist_media", "as"=> "pm"),
    "set" => array (
		"disabled" => false,
	),
	"where" => array(
		"in"=> array(
			array("concat" => array( array("pm"=>"media_id"),'---',array("pm"=>"playlist_id") )),
			array("select"=> array(
				"data" => array("concat" => array( array("values_array"=>"media_id"),'---',array("p"=>"id") )),
				"from"=> array( 
					array("values" => $media_id__playlist_name, 
						"as"=> array("values_array","media_id","playlist_name") // first value = table name. following = column names
					), 
					array('join'=> "playlist", 'as'=> "p", 'on'=> 
						array('and'=> array( 
							array('eq'=> array( array('p'=>"name"), array('values_array'=>"playlist_name") )), 
						))
					),
				),

			))
		)
	
		
	),
    "returning" => array("media_id","playlist_id"),
));
array_push($jsonrcp_list,$jsonrcp);

$res_jsonsql_call=jsonsql_call($jsonrcp_list);

foreach ($res_jsonsql_call as $r) {
	if (isset($r['result'])){
		//print_r($r['result']);
		PrintTable($jsonrcp_list[0][1]['returning'],$r['result']);
	} else {
		print_r("'result' not found: ".json_encode($r['error']) );
	}
}


print_r('<br/><br/>------------------------------------------------------------------------------------------------<br/><br/>');
print_r('add packages to VOD list at IDs with checking of existence <br/>');
print_r('if the already exists, then updating the information <br/>');

$media_id__package_name = array(
	array(220000,"Paid_Package_1"),
	array(220001,"Paid_Package_2"),
	array(220002,"Paid_Package_2"),
);


$jsonrcp_list = array();
$jsonrcp = array("insert", array (
    "into" => "package_media",
    "columns" => array ("media_id","package_id","disabled"),
	"select" => array(
		"data" => array (array('values_array'=>"media_id"),array('p'=>"id"),false),
		"from"=> array(
			array( "values" => $media_id__package_name, 
					"as"=> array("values_array","media_id","package_name") // first value = table name. following = column names
			),
			array('join'=> "package", 'as'=> "p", 'on'=> 
				array('and'=> array( 
					array('eq'=> array( array('p'=>"name"), array('values_array'=>"package_name") )), 
				))
			),
		),
		"where" => array(
			"not_in"=> array(
				array("concat" => array( array("values_array"=>"media_id"),'---',array("p"=>"id") )),
				array("select" => array(
					"data" => array("concat" => array( array("pm"=>"media_id"),'---',array("pm"=>"package_id") )),
					"from"=> array(
						array("table"=> "package_media", "as"=> "pm"),
					),
				)),
			),
		),
	),
    "returning" => array("media_id","package_id"),
));
array_push($jsonrcp_list,$jsonrcp);

$jsonrcp = array("update", array (
    "table" => array("table"=> "package_media", "as"=> "pm"),
    "set" => array (
		"disabled" => false,
	),
	"where" => array(
		"in"=> array(
			array("concat" => array( array("pm"=>"media_id"),'---',array("pm"=>"package_id") )),
			array("select"=> array(
				"data" => array("concat" => array( array("values_array"=>"media_id"),'---',array("p"=>"id") )),
				"from"=> array( 
					array("values" => $media_id__package_name, 
						"as"=> array("values_array","media_id","package_name") // first value = table name. following = column names
					), 
					array('join'=> "package", 'as'=> "p", 'on'=> 
						array('and'=> array( 
							array('eq'=> array( array('p'=>"name"), array('values_array'=>"package_name") )), 
						))
					),
				),

			))
		)
	
		
	),
    "returning" => array("media_id","package_id"),
));
array_push($jsonrcp_list,$jsonrcp);

$res_jsonsql_call=jsonsql_call($jsonrcp_list);

foreach ($res_jsonsql_call as $r) {
	if (isset($r['result'])){
		//print_r($r['result']);
		PrintTable($jsonrcp_list[0][1]['returning'],$r['result']);
	} else {
		print_r("'result' not found: ".json_encode($r['error']) );
	}
}


//-----------------------------------------------------------------------------------------------------------------------


?>