#!/usr/bin/env php
<?php
	$root = substr(__DIR__, 0, strrpos(__DIR__, '/'));
	require_once( $root . '/dronebl.config.php');
	require_once( $frameworks_path . '/DroneBL/Database.framework');
	require_once( $frameworks_path . '/DroneBL/Validation.framework');
	require_once( $frameworks_path . '/DroneBL/Cron.framework');
	require_once( $frameworks_path . '/DroneBL/Logging.framework');

	chkprog('Database Maintenance');

	$run_delete = 0;
	$run_vacuum = 0;
	$run_asnupd = 0;
	$run_ticket = 0;
	$run_entryretest = 1;

	$list = array();

	function _db_get_rows($query)
	{
	//	conlog($query); return false;
		return get_db_rows($query);
	}

	function _db_process()
	{
		global $list, $cur;
		$cur = 1;
		$linecount = count($list);

		$tables = array();
		$replies = array();

		foreach($list as $query)
		{
			progress($linecount, 0, 'queries');
			$parts = explode(' ', $query);
			$name = false;
			$type = strtolower($parts[0]);

			switch ($type)
			{
				case 'insert':
				case 'delete':
					$name = $parts[2];
					break;
				case 'update':
					$name = $parts[1];
					break;
			}

			$res = _db_get_rows($query);
			if ($res)
			{
				$rep = false;
				switch ($type)
				{
					case 'insert':
						$replies[$type][] = db_insert_id($res);
						break;
					case 'update':
					case 'delete':
						$rep = db_affected_rows($res);
						$replies[$type][]  = $rep;
						break;
				}
			}
		}
		echo PHP_EOL;
		$list = [];
		return $replies;
	}

	if ($run_delete)
	{
		// Cleanup obsolete records
		$list[] = "DELETE FROM blacklist WHERE listed=0 AND timestamp < now()::abstime::int4 - 63072000";
		$res = _db_process();
		conlog($res['delete'][0].' old entries removed.');
	}

	if ($run_vacuum)
	{
		// Cleanup obsolete duplicate entries (oldest of each type/listed/uid remains)
		$lastip = file_exists($root.'/cron/.dbmaint_lastip') ? file_get_contents($root.'/cron/.dbmaint_lastip') : '';
		$total = 0;
		while (true)
		{
			$iplist = array();
			$query =  get_db_rows("SELECT DISTINCT(ip) FROM blacklist ".($lastip ? "WHERE ip > '".$lastip."'":'')." ORDER BY ip: ASC LIMIT 1000");
			if (!db_num_rows($query))
			{
				unlink($root.'/cron/.dbmaint_lastip');
				break;
			}
				
			while ($row = get_db_tuple($query))
				$iplist[] = $row['ip'];
		
			$cache = array();
			$todel = array();
			conlog('Processing ip range '.$iplist[0].' - '.$iplist[count($iplist)-1]);
			foreach($iplist AS $ip)
			{
				$query =  get_db_rows("SELECT * FROM blacklist WHERE ip='".$ip."' ORDER BY timestamp ASC LIMIT 100");
				while ($row = get_db_tuple($query, DB_ASSOC))
				{
					if (!isset($cache[$ip]['listed_'.$row['listed']]['port_'.$row['port']]['type_'.$row['type']]['uid_'.$row['uid_add']]))
					{
						$cache[$ip]['listed_'.$row['listed']]['port_'.$row['port']]['type_'.$row['type']]['uid_'.$row['uid_add']] = $row['id'];
						continue;
					}
					$todel[] = $row['id'];
				}
			}
			$lastip = $ip;
			if (!empty($todel))
			{
				$total += count($todel);
				$res = get_db_rows("DELETE FROM blacklist WHERE id IN (".implode(',',$todel).")");
				conlog(db_affected_rows($res). ' entries deleted');
			}
			file_put_contents($root.'/cron/.dbmaint_lastip', $lastip);
		}
		conlog('A total of '.$total.' records have been cleaned up.');
	}

	if ($run_asnupd)
	{
		// Update ASN records
		$iplist = array();
		$cur = 1;
		$found = 0;

		$query = get_db_rows("SELECT * FROM blacklist WHERE asn=0 ORDER BY lastcheck ASC LIMIT 10000");
		$linecount = db_num_rows($query);
		while ($row = get_db_tuple($query))
		{
			progress($linecount, $found, 'records without asn');
			$ip = $row['ip'];
			if (isset($iplist[$ip]))
				continue;
			$iplist[$ip] = true;

			if (_isprivate($ip) || whitelisted($ip))
			{
				$list[] = "DELETE FROM blacklist WHERE ip='".$ip."'";
				$found++;
				continue;
			}

			$qry = array();
	
			$asn = _getasn($row['ip']);

			if ($asn && $asn != $row['asn'])
				$qry[] = 'asn='.$asn;

			$ctr = _getcountry($row['ip']);
			if ($ctr && $ctr != $row['country_code'])
				$qry[] = "country_code='".$ctr."'";

			if (!empty($qry))
				$found++;

			$qry[] = 'lastcheck='.time();
		
			$list[] = "UPDATE blacklist SET ".implode(', ',$qry)." WHERE ip='".$row['ip']."'";
		}
		echo PHP_EOL;

		if (!empty($list))
		{
			$res = _db_process();
			conlog((isset($res['delete']) ? array_sum($res['delete']).' entries removed. ' : '').(isset($res['update']) ? array_sum($res['update']).' entries updated.':''));
		}
	}

	if ($run_ticket)
	{	
		// Clean up tickets
		
		$cnt = 0;
		$cur = 1;
		$query = get_db_rows("SELECT ".db_insert_id_field().", incident FROM removerequest");
		$linecount = db_num_rows($query);
		while($tuple = get_db_tuple($query))
		{
			progress($linecount, $cnt, 'tickets');
			$ipdata = get_db_tuple(get_db_rows("SELECT listed FROM blacklist WHERE id='".$tuple['incident']."'"));
			if (!$ipdata || !$ipdata['listed'])
			{
				get_db_rows("DELETE FROM removerequest WHERE ".db_insert_id_field()."='".$tuple[db_insert_id_field()]."'");
				get_db_rows("DELETE FROM ticketmails WHERE ticketid='".$tuple[db_insert_id_field()]."'");
				$cnt++;
			}
		}
		echo PHP_EOL;
	}
	
	if ($run_entryretest)
	{
		$cur = 1;
		$found = 0;

		$query = get_db_rows("SELECT id, ip, port, type FROM blacklist WHERE listed=1 AND (type=12 OR (type IN (8,9,10,11,14) AND port > 0)) ORDER BY entrytest ASC");
		$linecount = db_num_rows($query);
		conlog("Testing entries (".$linecount.")");
		while ($row = get_db_tuple($query))
		{
			progress($linecount, $found, 'entries');
			$toscan = false;
			switch($row['type'])
			{
				case 10:
				case 14:
					$toscan = ['http', 'https', 'socks4', 'socks5'];
					break;
				case 8:
					$toscan = ['socks4', 'socks5'];
					break;
				case 9:
				case 11:
					$toscan = ['http', 'https'];
					break;
				case 12:
					$toscan = ['opendns'];
					break;
			}
			if ($toscan && !testEntry($row['ip'], $toscan, $row['port']))
			{
				echo ' removed';
				get_db_rows("UPDATE blacklist SET listed=0, ts_del=now()::abstime::int WHERE id='".$row['id']."'");
				_activity_log_add(0, 1, "IP <strong>".$row['ip']."</strong> removed by DB Maintenance (Issue resolved).");
				$found++;
			}
			get_db_rows("UPDATE blacklist SET entrytest=now()::abstime::int WHERE id='".$row['id']."'");
			echo PHP_EOL;
		}
		echo PHP_EOL;
	}
	
	endprog();
?>
