#!/usr/bin/php
<?php
#
# Usage: update-siptrunks-conf [add|update|delete] <sip_trunk.name>
#
# This script will update the /etc/asterisk/siptrunks.conf file settings
# and related ps_* tables using the sip_trunk table, then reload pjsip. 
# If <sip_trunk.name> is provided on the command line, then only that
# SIP Trunk is affected, otherwise the operation is performed on all
# enabled SIP Trunks found in the sip_trunk table.
#
# If trunk uses HT813 device, the script will execute the HT813 configuration
# script.
#
# (C) Copyright 2018-2024, Bogen Communications LLC. All rights reserved.
#
require '/usr/local/bin/db_credentials' ;

# Set $debug to TRUE to enable echo output for debugging
$debug = FALSE;

# If <sip_trunk.name> is not specified, we will process all enabled SIP trunks.
$sip_trunk = "";

if (PHP_SAPI == "cli")
{
        $op = $argv[1];
        if ($argc > 2) $sip_trunk = $argv[2];
}
else
{
        $op        = $_GET['argument1'];
        $sip_trunk = $_GET['argument2'];
}

if ($debug) echo "SIP Trunk: ".$sip_trunk.PHP_EOL;

if ($debug == FALSE) {
	mysqli_report(MYSQLI_REPORT_OFF);
}

$db = new mysqli($server, $user, $pass, $database);

if ($db->connect_errno > 0) {
	die('Unable to connect to database ['.$db->connect_error.']');
}

$isHT813Trunk = isHt813Trunk($db,$sip_trunk);

#
# Initialize SET formats for ps_* tables:
#
$set_fmt_auths = " id='%s', auth_type='userpass', password='%s', username='%s', realm='%s'";
$set_fmt_aors = " id='%s', contact='sip:%s:%s', qualify_frequency='60', max_contacts='1', remove_existing='yes', outbound_proxy=%s";
$set_fmt_aors_ht813 = " id='%s', qualify_frequency='60', max_contacts='1', remove_existing='yes', outbound_proxy=%s";
$set_fmt_identity = " id='%s', endpoint='%s', `match`='%s'";
$set_fmt_identity_ht813 = " id='%s', endpoint='%s', `match_header`='%s'";
$set_fmt_identity_zoom = " id='%s', endpoint='%s', `match`=NULL, `match_header`='User-Agent: Zoom PBX'";
$set_fmt_endpoints = " id='%s', transport='%s', context='%s', disallow='%s', allow='%s', outbound_auth='%s', aors='%s', direct_media='no', media_encryption='%s', rtp_symmetric=%s, from_domain=%s, outbound_proxy=%s";
$set_fmt_endpoints_ht813 = " id='%s', transport='%s', context='%s', disallow='%s', allow='%s', outbound_auth='%s', aors='%s', direct_media='no', media_encryption='%s', rtp_symmetric=%s, from_domain=%s, outbound_proxy=%s, media_encryption_optimistic=%s";

# Get lock to prevent more than one script from making updates at the same time.
$lock_file = "/tmp/updateSipTrunks.lock";
$lock_fp = fopen("/tmp/updateSipTrunks.lock", "w");
if (!flock($lock_fp, LOCK_EX)) {
	die('aquire lock failed');
}

#
# If this is an update request for a specific SIP trunk , we may need to change it to an add or delete.
# If the SIP trunk is disabled but the ps_endpoint exists, we need to perform a delete.
# If the SIP trunk is enabled but the ps_endpoint does not exist, we need to perform an add.
# Otherwise we can perform an update.
#
if (($op == "update") && ($sip_trunk != ""))
{
	$trunk_enabled = 0;

	$result = $db->query("SELECT enabled FROM sip_trunk WHERE name='$sip_trunk'");

	while ($row = $result->fetch_assoc())
	{
		$trunk_enabled = $row['enabled'];
	}

	$result->free();

	$ps_endpoint_exists = false;

	$result = $db->query("SELECT id FROM ps_endpoints WHERE id='$sip_trunk'");

	if ($result->num_rows > 0)
	{
		$ps_endpoint_exists = true;
	}

	$result->free();

	if (($trunk_enabled == 0) && ($ps_endpoint_exists == true))
	{
		$op = "delete";
	}
	else
	if (($trunk_enabled == 1) && ($ps_endpoint_exists == false))
	{
		$op = "add";
	}
}

#
# If we're deleting SIP trunk(s), update the siptrunks.conf file first
# to make sure we don't have issues with ps_* table deletes being done
# while PJSIP has a SIP trunk active. That way, PJSIP can drop the SIP trunk(s) 
# before the ps_* table deletes are performed.
#
if ($op == "delete")
{
	if ($sip_trunk != "")
	{
		$sql = "UPDATE sip_trunk SET enabled='0' WHERE name='$sip_trunk'";

		$db->query($sql);

		updateConfFile($db,$debug,$lock_fp,$sip_trunk);
		deduplicateIds($debug,$db,$lock_fp,$lock_file);
		updateAsterisk($server, $debug);
		deleteSipTrunk($debug, $db, $lock_fp, $sip_trunk);

		if ($isHT813Trunk)
		{
			deleteHt813trunk($db,$debug,$sip_trunk);
		}
	}
	fclose($lock_fp);
	unlink($lock_file);
	$db->close();
	exit(0);
}

#
# Read sip_trunk table to determine settings of ps_* tables...
#

$sql = "SELECT enabled,name,username,password,host,disallow,allow,context,custom_settings,enable_srtp,enable_sip_tls,device_cert_file,client_address,`type`,outbound_proxy,authentication_id FROM sip_trunk";

$sql = ($sip_trunk != "") ? $sql." WHERE name='".$sip_trunk."';" : $sql.";";

if ($debug) echo $sql.PHP_EOL;

if (!$result = $db->query($sql)) {
	fclose($lock_fp);
	unlink($lock_file);
	die('There was an error running the query ['.$db->error.']');
}

while ($row = $result->fetch_assoc())
{
	$trunk_enabled = $row['enabled'];
	$trunk_name = $row['name'];

	if (($sip_trunk == "") && ($trunk_enabled == 0))
	{
		$freq_sql = "UPDATE ps_aors SET qualify_frequency='0' WHERE id='$trunk_name'";

		$db->query($freq_sql);

		continue;
	}

	$trunk_name = preg_replace('/\s+/','_',$trunk_name);
	$host = $row['host'];
	$username = $row['username'];
	$password = $row['password'];
	$disallow = $row['disallow'];
	$allow = $row['allow'];
	#$context = $row['context'];
	$context = "incoming";
	$custom_settings = $row['custom_settings'];
	$enable_tls = $row['enable_sip_tls'] == 1;
	$enable_srtp = $row['enable_srtp'] == 1;
	$transport = "transport-" . ($enable_tls ? "tls" : "udp");
	$encryption = $enable_srtp ? "sdes" : "no";
	$type = $row['type'];
	$rtp_symmetric = ($type !== 5 && $enable_srtp) ? "'yes'" : "NULL";
	$device_cert = $row['device_cert_file'];
	$from_domain = isset($row['client_address']) ? ("'" . $row['client_address'] . "'") : "NULL";
	$authentication_id = $row['authentication_id'];
	$outbound_proxy_sql_template = isset($row['outbound_proxy']) ? ("'sip:" . $row['outbound_proxy'] . "'") : 'NULL';
	if ($enable_tls && isset($device_cert))
	{
		$transport = "transport-$trunk_name";
	}

	switch ($op) {
		case "add":
			$op_fmt = "INSERT INTO %s SET ";
			$op_fmt2 = ";";
			break;

		case "update":
			$op_fmt = "UPDATE %s SET ";
			$op_fmt2 = " WHERE id='%s';";
			break;
	}

	#
	# INSERT/UPDATE/DELETE ps_endpoints
	#
	$op_cmd = sprintf($op_fmt, "ps_endpoints");
	if ($isHT813Trunk) {
		$set_fmt = $op_cmd.$set_fmt_endpoints_ht813;
		$endpoint_sql = sprintf( $set_fmt,
					$trunk_name,
					$transport,
					$context,
					$disallow,
					$allow,
					$trunk_name,
					$trunk_name,
					$encryption,
					$rtp_symmetric,
					$from_domain,
					$outbound_proxy_sql_template,
					$rtp_symmetric);

	} else {
		$set_fmt = $op_cmd.$set_fmt_endpoints;
		$endpoint_sql = sprintf( $set_fmt,
					$trunk_name,
					$transport,
					$context,
					$disallow,
					$allow,
					$trunk_name,
					$trunk_name,
					$encryption,
					$rtp_symmetric,
					$from_domain,
					$outbound_proxy_sql_template);
	}
	$endpoint_sql = $endpoint_sql.sprintf($op_fmt2, $trunk_name);
	if ($debug) echo $endpoint_sql.PHP_EOL;

	#
	# INSERT/UPDATE/DELETE ps_auths
	#
	$op_cmd = sprintf($op_fmt, "ps_auths");
	$set_fmt = $op_cmd.$set_fmt_auths;
	$auth_sql = sprintf( $set_fmt,
			     $trunk_name,
			     $password,
			     isset($authentication_id) ? $authentication_id : $username,
			     $host);
	$auth_sql = $auth_sql.sprintf($op_fmt2, $trunk_name);
	if ($debug) echo $auth_sql.PHP_EOL;

	#
	# INSERT/UPDATE/DELETE ps_aors
	#
	$op_cmd = sprintf($op_fmt, "ps_aors");
	#
	# If trunk is HT813 type, need to use ht813 aors format,
	# don't need to include contact field. The commented code also works,
	# as long as the port number is 5062.
	#
	if ($isHT813Trunk)
	{
		$set_fmt = $op_cmd.$set_fmt_aors_ht813;
		$aor_sql = sprintf( $set_fmt,
				    $trunk_name,
					$outbound_proxy_sql_template);
	}
	else
	{
		$set_fmt = $op_cmd.$set_fmt_aors;
		$aor_sql = sprintf( $set_fmt,
				    $trunk_name,
				    $host,
				    $enable_tls ? "5061" : "5060",
					$outbound_proxy_sql_template);
	}

	$aor_sql = $aor_sql.sprintf($op_fmt2, $trunk_name);

	if ($debug) echo $aor_sql.PHP_EOL;

	#
	# INSERT/UPDATE/DELETE ps_endpoint_id_ips
	#
	# If configuring HT813 trunk, skip identity config because it causes an
	# identity clash with configured HT813 FXS port.
	# The "SELECT id FROM system" is there to allow identity_sql to still
	# be executed without actually inserting/updateing the identity table.
	#
	$op_cmd = sprintf($op_fmt, "ps_endpoint_id_ips");
	if ($isHT813Trunk)
	{
		$set_fmt = $op_cmd.$set_fmt_identity_ht813;

		$match_val = "Contact: <sip:".$trunk_name."@".$host.":5062>";

		$identity_sql = sprintf($set_fmt,
					$trunk_name,
					$trunk_name,
					$match_val);
	}
	elseif ($type == 6) {
		$set_fmt = $op_cmd.$set_fmt_identity_zoom;
		$identity_sql = sprintf($set_fmt,
					$trunk_name,
					$trunk_name);
	}
	else
	{
		$set_fmt = $op_cmd.$set_fmt_identity;
		if ($type == 5) {
			// For twilio, use the known IP ranges
			$host_identifier = '54.172.60.0/30,54.244.51.0/30';
		} else if (isset($row['outbound_proxy'])) {
			// If using a proxy, use it to identify the endpoint
			$host_identifier = parse_url($row['outbound_proxy'], PHP_URL_HOST);
		} else {
			// Use the registration uri to identify inbound calls by default
			$host_identifier = $host;
		}
		$identity_sql = sprintf($set_fmt,
					$trunk_name,
					$trunk_name,
					$host_identifier);
	}

	$identity_sql = $identity_sql.sprintf($op_fmt2, $trunk_name);

	if ($debug) echo $identity_sql.PHP_EOL;

	#
	# Execute SQL commands:
	#
	if (!$aresult = $db->query($endpoint_sql))
	{
		#
		# If INSERT Fails, perform an UPDATE instead
		#

		if ($debug) echo "Updating $trunk_name\n";

		fclose($lock_fp);
		unlink($lock_file);

		exec("/usr/local/bin/update-siptrunks-conf update \"$trunk_name\"");

		$got_lock = FALSE;

		while ($got_lock == FALSE)
		{
			$lock_fp = fopen("/tmp/updateSipTrunks.lock", "w");
			if (flock($lock_fp, LOCK_EX))
			{
				$got_lock = TRUE;
			}
			else
			{
				if ($lock_tries++ > 5)
				{
					die('aquire lock failed');
				}

				sleep(5);
			}
		}

		continue;
	}
	if (!$aresult = $db->query($auth_sql)) {
		fclose($lock_fp);
		unlink($lock_file);
		die('There was an error running the query2 ['.$db->error.']');
	}
	if (!$aresult = $db->query($aor_sql)) {
		fclose($lock_fp);
		unlink($lock_file);
		die('There was an error running the query3 ['.$db->error.']');
	}
	if ($trunk_enabled == 0)
	{
		$freq_sql = "UPDATE ps_aors SET qualify_frequency='0' WHERE id='$trunk_name'";
		$db->query($freq_sql);
	}
	if (!$aresult = $db->query($identity_sql)) {
		fclose($lock_fp);
		unlink($lock_file);
		die('There was an error running the query4 ['.$db->error.']');
	}

	############################
	# Process custom settings...
	############################
	if (($custom_settings != "") && ($op != "delete"))
	{
		if (preg_match("/^ERROR:(.*)/", $custom_settings, $parts))
			continue;

		$sqlchk = array(
			"/update (.*) set(.*)/i",
			"/insert\s*into(.*)values(.*)/i",
			"/insert\s*into(.*)set(.*)/i",
			"/select\s*(.*)from/i",
			"/delete\s*from/i",
			"/create\s*table(.*)/i",
			"/create\s*database(.*)/i",
			"/drop\s*database(.*)/i",
			"/drop\s*table(.*)/i",
			"/set\s*password\s*for(.*)/i",
			"/grant\s*usage\s*on(.*)/i",
			"/grant\s*all\s*privileges(.*)/i",
			"/load\s*data\s*infile(.*)/i",
			"/show\s*tables(.*)/i",
			"/alter\s*table(.*)/i");
		# First, check for complete SQL commands, if found, bail...
		$found_sql_command = FALSE;
		for ($i = 0; $i < count($sqlchk); $i++)
		{
			if (preg_match($sqlchk[$i], $custom_settings, $parts))
			{
				$found_sql_command = TRUE;
				echo "Found SQL CMD: ".$custom_settings.PHP_EOL;
				break;
			}
	   	}

		# If we found an SQL command, bail, DO NOT process the command
		if ($found_sql_command)
		{
			setCustomErrorFlag($debug, $db, $trunk_name, $custom_settings);
			continue;
		}

		#
		# Now update tables found in custom settings...
		#
		$tbl    = array("ps_endpoints",
				"ps_aors",
				"ps_auths",
				"sip_trunk"); 

		$tblId  = array("id",
				"id",
				"id",
				"name");

		$tblchk = array("ENDPOINT",
				"AOR",
				"AUTH",
				"SIPTRUNK");

		for ($i = 0; $i < count($tbl); $i++)
		{
			$tblMatch = "/:".$tblchk[$i].":(.*):".$tblchk[$i].":/";
			if (preg_match($tblMatch, $custom_settings, $tblSet))
			{
				if ($debug) echo $tblSet[1].PHP_EOL;
				$csql = "UPDATE ".$tbl[$i]." SET ".$tblSet[1].
					" WHERE ".$tblId[$i]."='".$trunk_name."';";
				if ($debug) echo $csql.PHP_EOL;
				if (!$cresult = $db->query($csql))
				{
					echo "Custom SQL UPDATE failed: ";
					echo PHP_EOL.$tbl[$i].":".$csql.PHP_EOL;
					echo $db->error.PHP_EOL;
					setCustomErrorFlag($debug,
							   $db,
							   $trunk_name,
							   $custom_settings);
					break;
				}
			}
		}
	}
}

#
# Now that the ps_* tables have been updated (add/update), we can safely update
# the siptrunks.conf file and notify PJSIP of the change.
#
updateConfFile($db,$debug,$lock_fp,"");
deduplicateIds($debug,$db,$lock_fp, $lock_file);
updateAsterisk($server, $debug);

fclose($lock_fp);
unlink($lock_file);
$db->close();
exit(0);

###############
# END OF MAIN #
###############

#########################################################################
# setCustomErrorFlag() will prepend "ERROR:" to sip_trunk.custom_settings
#########################################################################

function setCustomErrorFlag($debug, $db, $trunk_name, $custom_settings)
{
	$errSet = "ERROR: ".$custom_settings;
	$errSql = "UPDATE sip_trunk SET custom_settings=\"".$errSet.
		  "\" WHERE name='".$trunk_name."'";
	if ($debug) echo "errSQL: ".$errSql.PHP_EOL;
	if (!$result = $db->query($errSql)) {
		echo "There was an error running errSql [".$db->error."]";
	}
}

###########################################################
# updateConfFile() will update /etc/asterisk/siptrunks.conf
###########################################################

function updateConfFile($db,$debug,$lock_fp, $sip_trunk)
{

$siptrunks_conf_file = "/etc/asterisk/siptrunks.conf";

$sqlUCF = "SELECT id,enabled,name,username,password,host,context,custom_settings,enable_srtp,enable_sip_tls,device_cert_file,client_address,`type`,outbound_proxy
	   FROM sip_trunk";

$sqlUCF.= ($sip_trunk != "") ? " WHERE name != '$sip_trunk';" : ";";

if ($debug) echo $sqlUCF.PHP_EOL;

if (!$resultUCF = $db->query($sqlUCF)) {
	fclose($lock_fp);
	unlink($lock_file);
	die('There was an error running the query ['.$db->error.']');
}

#
# Update siptrunks.conf with all enabled SIP Trunks
#
$conf_file_txt = ";SIP Trunk Configuration".PHP_EOL.";".PHP_EOL;

$sipS_port=5062;
shell_exec('mkdir -p --mode=770 /etc/asterisk/trunk_keys');
shell_exec('chown root:www-data /etc/asterisk/trunk_keys');
$unused_key_files = array_diff(scandir('/etc/asterisk/trunk_keys'), array('..', '.'));
while ($rowUCF = $resultUCF->fetch_assoc())
{
	$trunk_id = $rowUCF['id'];
	$trunk_name = $rowUCF['name'];
	$trunk_name = preg_replace('/\s+/','_',$trunk_name);
	$host = $rowUCF['host'];
	$username = $rowUCF['username'];
	$password = $rowUCF['password'];
	$context = $rowUCF['context'];
	$custom_settings = $rowUCF['custom_settings'];
	$enable_tls = $rowUCF['enable_sip_tls'] == 1;
	$outbound_proxy = $rowUCF['outbound_proxy'];
	$transport_suffix = $enable_tls ? "\\;transport=tls":"";
	if (isset($outbound_proxy)) {
		$server_uri = $outbound_proxy;
	} else {
		$server_uri = $host . ($enable_tls ? ":5061" : "");
	}
	$server_uri = 'sip:' . $server_uri . $transport_suffix;
	$contact_user = isset($outbound_proxy) ? $username : $context;
	$client_uri = "sip:$username@" . (isset($rowUCF['client_address']) ? $rowUCF['client_address'] : $host);
	$transport = "transport-" . ($enable_tls ? "tls" : "udp");
	$device_cert = $rowUCF['device_cert_file'];
	$type = $rowUCF['type'];
	if (isset($device_cert) && ($key = array_search($device_cert, $unused_key_files)) !== false) {
		unset($unused_key_files[$key]);
	}
	# We only care about enabled SIP Trunks
	if ($rowUCF['enabled'] == 0) continue;

	#
	# Don't add HT813-based SIP Trunks to Asterisk config file
	# (because we don't need to register with the HT813), instead,
	# update the config file for the HT813 device.
	#
	if (isHt813Trunk($db,$trunk_name))
	{
		addHt813Trunk($db,$debug,$trunk_id);
		continue;
	}

	if ($enable_tls && isset($device_cert))
	{
		$conf_text = <<<CONFTEXT
type=transport
protocol=tls
tos=cs3
cos=3
cipher=ECDHE-RSA-AES256-GCM-SHA384,AES256-SHA256,DHE-RSA-AES256-GCM-SHA384,DHE-RSA-AES256-SHA256,AES256-GCM-SHA384,DHE-RSA-AES128-GCM-SHA256,DHE-RSA-AES128-SHA256,AES128-GCM-SHA256,ECDHE-ECDSA-AES256-GCM-SHA384,DHE-RSA-AES256-GCM-SHA384
method=tlsv1_2
CONFTEXT;
		// run openssl x509 to dump the certificate text
		$output = shell_exec("openssl x509 -in " . escapeshellarg("/etc/asterisk/trunk_keys/$device_cert") . " -noout -text");

		// look for required usages
		$hasClientAuth   = (strpos($output, "TLS Web Client Authentication") !== false);
		$hasDigitalSig   = (strpos($output, "Digital Signature") !== false);
		$conf_text = $conf_text.PHP_EOL."cert_file=/etc/asterisk/trunk_keys/$device_cert";
		$sipS_port=$sipS_port + 1;
		$transport = "transport-$trunk_name";
		$conf_text = "[$transport]".PHP_EOL.$conf_text.PHP_EOL."bind=0.0.0.0:$sipS_port".PHP_EOL.PHP_EOL;

		if ($hasClientAuth && $hasDigitalSig)
		{
			$conf_file_txt = $conf_file_txt.$conf_text;
		}
		else {
			echo("Device certificate file $device_cert is not valid for " . (!$hasClientAuth ? ("TLS client authentication " . (!$hasDigitalSig ? "or " : "")) : "") . (!$hasDigitalSig ? "digitally signing TLS traffic" :"") . ", using default transport for trunk $trunk_name instead\n");
		}
	}
	if ($type !== 5) {
		$proxy_config = isset($outbound_proxy) ? "outbound_proxy=sip:$outbound_proxy" : ";outbound_proxy=";
		$conf_text = <<<CONFTEXT
[$trunk_name]
type=registration
$proxy_config
server_uri=$server_uri
client_uri=$client_uri
contact_user=$contact_user
retry_interval=5
forbidden_retry_interval=60
expiration=3600
transport=$transport
outbound_auth=$trunk_name
max_retries=500000
auth_rejection_permanent=no

CONFTEXT;
	}

	$conf_file_txt = $conf_file_txt.$conf_text.PHP_EOL;
}
if (!empty($unused_key_files)) echo("Deleting the following unused key files in /etc/asterisk/trunk_keys: " . implode(", ", $unused_key_files) . "\n");

foreach($unused_key_files as $unused_key_file) {
	unlink("/etc/asterisk/trunk_keys/$unused_key_file");
}
#
# Create configuration file:
#

if ($debug) echo $conf_file_txt.PHP_EOL;

#
# Create the new /etc/asterisk/siptrunks.conf file
#
$tmpconf = "/tmp/newsiptrunks.conf";

$newconf = fopen($tmpconf, "w")
	or die("Unable to open /tmp conf file!\n");

fwrite($newconf, $conf_file_txt);
fclose($newconf);

copy($tmpconf, $siptrunks_conf_file);
unlink($tmpconf);
}

####################################################################
# deduplicateIds() set duplicate trunk hosts to identify by username
####################################################################
function deduplicateIds($debug, $db, $lock_fp, $lock_file)
{
	# Set all non-ht813 trunk ps_endpoint_id_ips to match the host and not the
	# header. In the case of deleted/disabled trunks, this is the simplest way
	# to reset match_header regular expressions that are set in the query below
	$mysql = <<< SQLQUERY
	UPDATE ps_endpoint_id_ips
	JOIN sip_trunk ON sip_trunk.name=ps_endpoint_id_ips.id
	SET ps_endpoint_id_ips.match=sip_trunk.host, ps_endpoint_id_ips.match_header=NULL
	WHERE sip_trunk.type <> 4 AND sip_trunk.outbound_proxy <> NULL
	SQLQUERY;
	if (!$result = $db->query($mysql)) {
		fclose($lock_fp);
		unlink($lock_file);
		die('Error running the query ['.$db->error.']');
	}
	# Set all zoom trunks to have user agent identifiers
	$mysql = <<< SQLQUERY
	UPDATE ps_endpoint_id_ips
	JOIN sip_trunk ON sip_trunk.name=ps_endpoint_id_ips.id
	SET ps_endpoint_id_ips.match=NULL, ps_endpoint_id_ips.match_header='User-Agent: Zoom PBX'
	WHERE sip_trunk.type = 6
	SQLQUERY;
	if (!$result = $db->query($mysql)) {
		fclose($lock_fp);
		unlink($lock_file);
		die('Error running the query ['.$db->error.']');
	}

	# Any enabled trunk that isn't ht813, doesn't have a proxy, and whose host
	# that matches that of another enabled trunk will have its `match` set to
	# null (do not match on host, because it will confuse the duplicate), and
	# its `match_header` to a regular expression matching `To` headers that
	# match the username that the trunk is registered to on the external PBX
	$mysql = <<< SQLQUERY
	UPDATE ps_endpoint_id_ips
	JOIN sip_trunk ON sip_trunk.name=ps_endpoint_id_ips.id
	SET ps_endpoint_id_ips.match=NULL, ps_endpoint_id_ips.match_header=concat('To: /<sip:', sip_trunk.username, '@.*/')
	WHERE sip_trunk.host IN (SELECT host from sip_trunk WHERE enabled=1 GROUP BY host HAVING COUNT(host) > 1) AND sip_trunk.outbound_proxy IS NULL AND sip_trunk.type <> 4
	SQLQUERY;
	if (!$result = $db->query($mysql)) {
		fclose($lock_fp);
		unlink($lock_file);
		die('Error running the query ['.$db->error.']');
	}
	# Any enabled trunk that isn't ht813, and whose outbound proxy matches that
	# of another enabled trunk will have its `match` set to null (do not match
	# on proxy, because it will confuse the duplicate), and its `match_header`
	# to a regular expression matching `To` headers that match the username
	# that the trunk is registered to on the external PBX
	$mysql = <<< SQLQUERY
	UPDATE ps_endpoint_id_ips
	JOIN sip_trunk ON sip_trunk.name=ps_endpoint_id_ips.id
	SET ps_endpoint_id_ips.match=NULL, ps_endpoint_id_ips.match_header=concat('To: /<sip:', sip_trunk.username, '@.*/')
	WHERE sip_trunk.outbound_proxy IN (SELECT outbound_proxy from sip_trunk WHERE enabled=1 GROUP BY outbound_proxy HAVING COUNT(outbound_proxy) > 1) AND sip_trunk.outbound_proxy IS NOT NULL AND sip_trunk.type <> 4
	SQLQUERY;
	if (!$result = $db->query($mysql)) {
		fclose($lock_fp);
		unlink($lock_file);
		die('Error running the query ['.$db->error.']');
	}
}


############################################################
# updateAsterisk() will tell Asterisk to reload pjsip module
############################################################

function updateAsterisk($server, $debug)
{
        if (($socket = fsockopen($server,"5038",$errno,$errstr,30)) == FALSE)
	{
		return;
	}

	stream_set_timeout($socket, 1);

        $retstr=fgets($socket);
	if ($debug) echo "0:$retstr";

	fputs($socket, "Action: Login\r\nUserName: webui\r\nSecret: BogenNyq1\r\n\r\n");

	# Read "Response: Success"
        $retstr=fgets($socket);
	if ($debug) echo "1:$retstr";

	# Read "Message: Authentication accepted"
        $retstr=fgets($socket);
	if ($debug) echo "2:$retstr";

        fputs($socket, "Action: Command\r\n");
        fputs($socket, "Command: pjsip reload\r\n\r\n");

	# Read "Response: Success"
        $retstr=fgets($socket);
	if ($debug) echo "3:$retstr";

        fputs($socket, "Action: Logoff\r\n\r\n");

	$loopCount = 0;
	while (strpos($retstr,"Goodbye") === false)
	{
		$retstr=fgets($socket);

		if ($debug) echo "5:$retstr";

		if ($loopCount++ > 20) break;
	}

        fclose($socket);
}

###############################################################
# deleteSipTrunk() will delete a SIP trunk from all ps_* tables
###############################################################

function deleteSipTrunk($debug, $db, $lock_fp, $sip_trunk)
{
	$sql_fmt = "DELETE FROM %s WHERE id='%s';";

	$tbl = array("ps_endpoints",
		     "ps_aors",
		     "ps_auths",
		     "ps_endpoint_id_ips"); 

	for ($i = 0; $i < count($tbl); $i++)
	{
		$delSQL = sprintf($sql_fmt, $tbl[$i], $sip_trunk);
		if (!$result = $db->query($delSQL)) {
			fclose($lock_fp);
			unlink($lock_file);
			die('Error running the query ['.$db->error.']');
		}
	
	}
}

function addHt813Trunk($db,$debug,$trunk_id)
{
	$sql = "UPDATE sip_trunk SET type='4' WHERE id='$trunk_id'";

	$db->query($sql);

	exec("/usr/local/bin/create-ht813-cfg -a -t $trunk_id > /dev/null 2>&1");
}

function deleteHt813trunk($db,$debug,$sip_trunk)
{
	$sql = "SELECT id FROM sip_trunk WHERE name='$sip_trunk'";

	if ($result = $db->query($sql))
	{
		if ($result->num_rows > 0)
		{
			$row = $result->fetch_assoc();

			$trunk_id = $row['id'];

			exec("/usr/local/bin/create-ht813-cfg -d -t $trunk_id > /dev/null 2>&1");
		}
	}
}

function isHt813Trunk($db,$sip_trunk) {
	if (strpos($sip_trunk, 'ht813-') === 0) {
		return true;
	} else {
		$sql = "SELECT `type` FROM sip_trunk WHERE name='$sip_trunk'";
		$result = $db->query($sql);
		return $result && $result->num_rows > 0 && $result->fetch_assoc()['type'] == 4;
	}
}

?>
