<?php
###############################################################################
# DomainDB.php
#
# @author Anil Kumar <akumar@codepunch.com>
# @link   https://codepunch.com
#
############################################################################### 

namespace 	CodePunch\DB;

use 		CodePunch\Base\Util as UTIL;
use			CodePunch\Base\Text as TEXT;
use			CodePunch\DB\Audit as AUDIT;
use 		CodePunch\Base\CPLogger;
use 		Exception;

###############################################################################

class DomainDB extends Database {
	
	const	ALLOW_NONE					= 0;
	const 	ALLOW_VIEW 					= 1;
	const	ALLOW_EDIT					= 2;
	const	ALLOW_ADD					= 4;
	const	ALLOW_DELETE				= 8;
	const	ALLOW_LOOKUP				= 16;
	const	ALLOW_UICHANGE				= 32;
	const	ALLOW_DOWNLOAD				= 64;
	const	ALLOW_CATEGORY_EDIT			= 128;
	const	ALLOW_ALL_DOMAINS_EVER		= 256;
	const	ALLOW_AUTOQUERY_EDIT		= 512;
	
	const   ALLOW_ADMINLEVEL			= 65535;
	
	const	DOMAIN_TABLE				= "domains";
	const	CATEGORY_TABLE				= "categories";
	const	SUBDOMAIN_TABLE				= "subdomains";
	const	CATCONN_TABLE 				= "catconn";
	const	REGALIAS_TABLE				= "registrar_aliases";
	const	SETTINGS_TABLE				= "settings";
	const	QUERIES_TABLE				= "queries";
	const	LUQ_TABLE					= "luq";
	const	TLDS_TABLE					= "tlds";
	const	WHOISSERVER_TABLE			= "whoisservers";
	const	CONNECTIONS_TABLE			= "connections";
	const	SEDSERVERS_TABLE			= "sed_servers";
	const	DATAHISTORY_TABLE			= "data_history";
	const	AUDITLOG_TABLE				= "audit_log";
	const	PUBLICSUFFIX_TABLE			= "publicsuffix";
	const	DATACOLUMNS_TABLE			= "datacolumns";
	const	USERGROUPS_TABLE			= "auth_groups";
	const	USERS_TABLE					= "auth_users";
	const	USERCATCONN_TABLE			= "auth_catconn";
	const	USERGROUPCONN_TABLE			= "auth_groupconn";
	const	USERAUTHLOG_TABLE			= "auth_logs";
	const	API_PROFILES_TABLE			= "api_profiles";
	const	LOOKUP_SCHEDULER_TABLE		= "lu_scheduler";
	const	REPORT_SCHEDULER_TABLE		= "report_scheduler";
	const	REPORTS_TABLE				= "reports";
	const	TLD_PRICE_CACHE_TABLE		= "tld_price_cache";
	const	SSL_CERTIFICATE_TABLE		= "sslcerts";
	
	const	DEFAULT_HIGHLIGHT_DAYS		= 30;
	
	###########################################################################
	
	private $domain_table 				= null;
	private $subdomain_table   			= null;
	private $category_table 			= null;
	private $autoqueries_table			= null;
	private $catconn_table  			= null;
	private $settings_table 			= null;
	private $displaycolumns_table		= null;
	private $lookup_queue_table     	= null;
	private $tlds_table      			= null;
	private $whoisserver_table      	= null;
	private $connections_table			= null;
	private $installed_servers_table	= null;
	private $registrar_aliases_table	= null;
	private $data_history_table			= null;
	private $audit_log_table			= null;
	private $publicsuffix_table			= null;
	private $datacolumns_table			= null;
	private $api_profiles_table			= null;
	
	private $user_groups_table 			= null;
	private $user_table   				= null;
	private $auth_log_table   			= null;
	private $category_access_table 		= null;
	private $group_access_table     	= null;
	
	private $lookup_scheduler_table   	= null;
	private $report_scheduler_table 	= null;
	
	private $reports_table 				= null;
	private $tld_price_cache_table		= null;
	private $ssl_certificate_table		= null;
	
	private $domain_suffix_list			= null;
	
	###########################################################################
	
	public function __construct($connparams, $table_prefix, $action=self::READONLY_TABLES)
	{ 
		$this->tableprefix				= trim($table_prefix);
		$this->domain_table 			= trim(strtolower($table_prefix . self::DOMAIN_TABLE));
		$this->subdomain_table 			= trim(strtolower($table_prefix . self::SUBDOMAIN_TABLE));
		$this->category_table 			= trim(strtolower($table_prefix . self::CATEGORY_TABLE));
		$this->catconn_table 			= trim(strtolower($table_prefix . self::CATCONN_TABLE));
		$this->settings_table 			= trim(strtolower($table_prefix . self::SETTINGS_TABLE));
		$this->autoqueries_table		= trim(strtolower($table_prefix . self::QUERIES_TABLE));
		$this->lookup_queue_table		= trim(strtolower($table_prefix . self::LUQ_TABLE));
		$this->tlds_table				= trim(strtolower($table_prefix . self::TLDS_TABLE));
		$this->whoisserver_table		= trim(strtolower($table_prefix . self::WHOISSERVER_TABLE));
		$this->connections_table		= trim(strtolower($table_prefix . self::CONNECTIONS_TABLE));
		$this->installed_servers_table	= trim(strtolower($table_prefix . self::SEDSERVERS_TABLE));
		$this->registrar_aliases_table	= trim(strtolower($table_prefix . self::REGALIAS_TABLE));
		$this->data_history_table		= trim(strtolower($table_prefix . self::DATAHISTORY_TABLE));
		$this->audit_log_table			= trim(strtolower($table_prefix . self::AUDITLOG_TABLE));
		$this->publicsuffix_table		= trim(strtolower($table_prefix . self::PUBLICSUFFIX_TABLE));
		$this->datacolumns_table		= trim(strtolower($table_prefix . self::DATACOLUMNS_TABLE));
		$this->api_profiles_table		= trim(strtolower($table_prefix . self::API_PROFILES_TABLE));
		
		$this->user_groups_table 		= trim(strtolower($table_prefix . self::USERGROUPS_TABLE));
		$this->user_table   			= trim(strtolower($table_prefix . self::USERS_TABLE));
		$this->category_access_table 	= trim(strtolower($table_prefix . self::USERCATCONN_TABLE));
		$this->group_access_table 		= trim(strtolower($table_prefix . self::USERGROUPCONN_TABLE));
		$this->auth_log_table   		= trim(strtolower($table_prefix . self::USERAUTHLOG_TABLE));
		
		$this->lookup_scheduler_table   = trim(strtolower($table_prefix . self::LOOKUP_SCHEDULER_TABLE));
		$this->report_scheduler_table  	= trim(strtolower($table_prefix . self::REPORT_SCHEDULER_TABLE));
		$this->reports_table  			= trim(strtolower($table_prefix . self::REPORTS_TABLE));
		$this->tld_price_cache_table  	= trim(strtolower($table_prefix . self::TLD_PRICE_CACHE_TABLE));
		$this->ssl_certificate_table  	= trim(strtolower($table_prefix . self::SSL_CERTIFICATE_TABLE));
		
		$requiredTables = array($this->domain_table,$this->subdomain_table,$this->category_table,$this->catconn_table,
			$this->settings_table,$this->autoqueries_table,
			$this->lookup_queue_table,$this->tlds_table,$this->whoisserver_table,$this->connections_table,
			$this->installed_servers_table,$this->registrar_aliases_table,$this->data_history_table,
			$this->audit_log_table,$this->publicsuffix_table,$this->user_groups_table,
			$this->user_table,$this->category_access_table,$this->group_access_table,$this->auth_log_table,
			$this->datacolumns_table, $this->api_profiles_table,
			$this->lookup_scheduler_table, $this->report_scheduler_table, $this->reports_table,
			$this->tld_price_cache_table, $this->ssl_certificate_table
			);
		
		$this->setConnectionParams($connparams);
		if($this->getConnection()) {
			$dbInitPath = \CodePunch\Base\Util::get_install_folder_path(). "lib/php/CodePunch/DB/Init";
			if(is_dir($dbInitPath)) {
				// Repair tables if needed
				if($action == self::READONLY_TABLES) {
					$schemaManager = $this->getConnection()->getSchemaManager();
					if ($schemaManager->tablesExist($requiredTables) !== true) {
						$action = self::REPAIR_TABLES;
						$logger = new \CodePunch\Base\CPLogger();
						$logger->error("Missing tables, will attempt to repair.");
						if(UTIL::is_cli()) {
							UTIL::print("Database needs repair because some tables are missing.");
							UTIL::print("Entering maintenance mode.\n");
						}
					}
				}
				$fp = UTIL::get_lock_file("tables");
				if($fp !== false) {
					$this->createTablesFromDefinitions($dbInitPath, $action);
					if($action == self::REPAIR_TABLES) {
						$this->initTables();
						$this->runStartupQueries();
						$this->fixTablesForUpdate();
					}
					flock($fp, LOCK_UN);
					fclose($fp);
				}
				else if($action == self::REPAIR_TABLES) {
					throw new Exception(TEXT::get("db_under_repair"));
				}
			}
			// Finally fix things to account for any changes
			// introduced after the last update.
			if($action == self::READONLY_TABLES)
				$this->fixTablesForUpdate();
		}
	}
	
	###########################################################################
	
	public function runStartupQueries()
	{
		$failed = 0;
		$done = 0;
		$inipath = \CodePunch\Base\Util::get_install_folder_path(). "lib/php/CodePunch/DB/Init/startup.ini";
		$startupinit = parse_ini_file($inipath,true);
		if(is_file($inipath)) {
			$platform = $this->connection->getDatabasePlatform();
			$platformName = strtolower($platform->getName());
			if(isset($startupinit[$platformName])) {
				$logger = new \CodePunch\Base\CPLogger();
				foreach($startupinit[$platformName] as $sql) {
					$sql = $this->customExpandWhere(trim($sql));
					try {
						$stmt = $this->connection->prepare($sql);
						$stmt->execute();
						$done++;
					}
					catch(Exception $e) {
						$logger->error($e->getMessage());
						$failed++;
					}
				}
			}
		}
		return array('failed'=>$failed, 'done'=>$done);
	}
	
	###########################################################################
	
	public function getTableNameWithoutPrefix($tablename) {
		if(UTIL::starts_with(strtolower($tablename), strtolower($this->tableprefix)))
			return substr($tablename, strlen($this->tableprefix));
		else
			return $tablename;
	}
	
	###########################################################################
	
	public function getRealTableName($table) {
		if(!UTIL::starts_with(strtolower($table), strtolower($this->tableprefix)))
			return trim(strtolower($this->tableprefix . $table));
		else
			return trim(strtolower($table));
	}
	
	###########################################################################
	
	public function getDomainSuffixList() {
		if($this->domain_suffix_list == null) {
			$dslist = $this->getFromTable("suffix,tld", $this->getDomainSuffixListTableName(), "", array(), "tld", "DESC");
			if($dslist !== false) {
				$suffixlist = array();
				foreach($dslist as $d) {
					$d = array_change_key_case($d, CASE_LOWER);
					if(!isset($suffixlist[$d['tld']]))
						$suffixlist[$d['tld']] = array();
					$suffixlist[$d['tld']][] = $d['suffix'];
				}
				// Arrange so that longer eTLds are in front.
				foreach($suffixlist as $tld=>&$tldlist) {
					usort($tldlist, function($a, $b) {
						return strlen($b) - strlen($a);
					});
				}
				$this->domain_suffix_list = $suffixlist;
			}
		}
		return $this->domain_suffix_list;
	}
	
	###########################################################################

	public function getSSLCertificateTableName() {
		return $this->ssl_certificate_table;
	}
	
	###########################################################################

	public function getTLDPriceTableName() {
		return $this->tld_price_cache_table;
	}
	
	###########################################################################

	public function getReportsTableName() {
		return $this->reports_table;
	}

	###########################################################################
	
	public function getDataHistoryTableName() {
		return $this->data_history_table;
	}
	
	###########################################################################
	
	public function getLookupSchedulerTableName() {
		return $this->lookup_scheduler_table;
	}
	
	###########################################################################
	
	public function getReportSchedulerTableName() {
		return $this->report_scheduler_table;
	}
	
	###########################################################################
	
	public function getAuthenticationLogTableName() {
		return $this->auth_log_table;
	}
	
	###########################################################################
	
	public function getDomainSuffixListTableName() {
		return $this->publicsuffix_table;
	}
	
	###########################################################################
	
	public function getRegistrarAliasTableName() {
		return $this->registrar_aliases_table;
	}
	
	###########################################################################
	
	public function getDatacolumnsTableName() {
		return $this->datacolumns_table;
	}
	
	###########################################################################
	
	public function getAPIProfilesTableName() {
		return $this->api_profiles_table;
	}
	
	###########################################################################
	
	public function getDomainTableName() {
		return $this->domain_table;
	}
	
	###########################################################################
	
	public function getSubdomainTableName() {
		return $this->subdomain_table;
	}
	
	###########################################################################
	
	public function getCategoryTableName() {
		return $this->category_table;
	}
	
	###########################################################################
	
	public function getCategoryConnectionTableName() {
		return $this->catconn_table;
	}
	
	###########################################################################
	
	public function getAutoQueryTableName() {
		return $this->autoqueries_table;
	}
	
	###########################################################################
	
	public function getUserTableName() {
		return $this->user_table;
	}
	
	###########################################################################
	
	public function getUserGroupsTableName() {
		return $this->user_groups_table;
	}
	
	###########################################################################
	
	public function getUserGroupAccessTableName() {
		return $this->group_access_table;
	}
	
	###########################################################################
	
	public function getUserCategoryAccessTableName() {
		return $this->category_access_table;
	}
	
	###########################################################################
	
	public function getAuditLogTableName() {
		return $this->audit_log_table;
	}
	
	###########################################################################
	
	public function getTLDsTableName() {
		return $this->tlds_table;
	}
	
	###########################################################################
	
	public function getWhoisServersTableName() {
		return $this->whoisserver_table;
	}
	
	###########################################################################
	
	public function getSettingsTableName() {
		return $this->settings_table;
	}
	
	###########################################################################
	
	public function getLookupQueueTableName() {
		return $this->lookup_queue_table;
	}
	
	###########################################################################
	
	public function getConnectionsTableName() {
		return $this->connections_table;
	}
	
	###########################################################################
	
	public function getInstalledServersTableName() {
		return $this->installed_servers_table;
	}
	
	###########################################################################
	
	public function getWhoisServerForTLD($tld) {
		$server = $this->findOneOf($this->getTLDsTableName(), "tld", $tld, "server");
		// Server may be set as "-"
		return trim($server, " -");
	}
	
	###########################################################################

	public function setWhoisServerForTLD($tld, $server)
	{
		return $this->insertOrUpdateTable($this->getTLDsTableName(), array("server"=>$server), "tld", $tld);
	}

	###########################################################################

	public function getRDAPServerForTLD($tld) {
		return $this->findOneOf($this->getTLDsTableName(), "tld", $tld, "rdap_server");
	}

	###########################################################################

	public function setRDAPServerForTLD($tld, $rdapserver) {
		return $this->insertOrUpdateTable($this->getTLDsTableName(), array("rdap_server"=>$rdapserver), "tld", $tld);
	}

	###########################################################################

	public function getLUSquenceForTLD($tld) {
		return $this->findOneOf($this->getTLDsTableName(), "tld", $tld, "lu_sequence");
	}

	###########################################################################

	public function setLUSquenceForTLD($tld, $lusequence) {
		return $this->insertOrUpdateTable($this->getTLDsTableName(), array("lu_sequence"=>$lusequence), "tld", $tld);
	}

	###############################################################################

	public function getParserTableFor($server)
	{
		$xlat = array();
		$rawdata = $this->findOneOf($this->getWhoisServersTableName(), "server", $server, "xlate");
		if($rawdata !== false && $rawdata != "") {
			if($rawdata != "") {
				$entries = explode("\n", $rawdata);
				foreach($entries as $k => $v) {
					$kvpair = explode("=>", $v);
					if(count($kvpair) == 2) {
						$kv = trim($kvpair[0], " \t\n\r\0\x0B'");
						$vv = trim($kvpair[1], " \t\n\r\0\x0B'");
						if($kv != "" && $vv != "")
							$xlat[$kv] = $vv;
					}
				}
			}
		}
		return count($xlat) > 0 ? $xlat : false;
	}
	
	###########################################################################
	
	public function isWriteProtected($did) {
		$status = $this->findOneOf($this->getDomainTableName(), "sid", $did, "write_protect");
		return is_bool($status) ? $status : UTIL::str_to_bool((string)$status);
	}
	
	###########################################################################
	
	public function getDomainID($domain) {
		$did = $this->findOneOf($this->getDomainTableName(), "domain", strtolower($domain), "sid");
		if($did === false) {
			$asciidomain = UTIL::idn_convert($domain);
			$did = $this->findOneOf($this->getDomainTableName(), "ascii_domain", $asciidomain, "sid");
		}
		return $did;
	}
	
	###########################################################################
	
	public function getDomainName($did) {
		return $this->findOneOf($this->getDomainTableName(), "sid", $did, "domain");
	}
	
	###########################################################################
	
	public function getCategoryID($category) {
		return $this->findOneOf($this->getCategoryTableName(), "name", $category, "cid");
	}
	
	###########################################################################
	
	public function addDomainToCategoryByName($domain, $category) {
		$cid = $this->getCategoryID($category);
		if($cid !== false && $cid > 0)
			return $this->addDomainToCategoryByID($domain, $cid);
		return false;
	}
	
	###########################################################################
	
	public function removeDomainFromCategoryByName($domain, $category) {
		$cid = $this->getCategoryID($category);
		if($cid !== false && $cid > 0)
			return $this->removeDomainFromCategoryByID($domain, $cid);
		return false;
	}
	
	###########################################################################
	
	public function addDomainToCategoryByID($domain, $cid) {
		$sid = $this->getDomainID($domain);
		if($sid !== false && $sid > 0)
			return $this->addDomainIDToCategoryByID($sid, $cid);
	}
	
	###########################################################################
	
	public function removeDomainFromCategoryByID($domain, $cid) {
		$sid = $this->getDomainID($domain);
		if($sid !== false && $sid > 0)
			return $this->removeDomainIDFromCategoryByID($sid, $cid);
	}
	
	###########################################################################
	
	public function addDomainIDToCategoryByID($sid, $cid) {
		if($sid !== false && $sid > 0 && $cid !== false && $cid > 0) {
			try {
				$conntable = $this->getCategoryConnectionTableName();
				$ci = $this->getFromTable("id", $conntable, "did=? AND cid=?", array($sid,$cid));
				if($ci === false || !count($ci)) {
					$this->connection->insert($conntable, array("did" => $sid, "cid" => $cid));
					return true;
				}
			}
			catch(Exception $e) {
				$logger = new \CodePunch\Base\CPLogger();
				$logger->error($e->getMessage());
			}
			return false;
		}
	}
	
	###########################################################################
	
	public function removeDomainIDFromCategoryByID($sid, $cid) {
		if($sid !== false && $sid > 0 && $cid !== false && $cid > 0) {
			try {
				$conntable = $this->getCategoryConnectionTableName();
				$ci = $this->getFromTable("id", $conntable, "did=? AND cid=?", array($sid,$cid));
				if($ci !== false && is_array($ci) && count($ci)) {
					$qb = $this->connection->createQueryBuilder();
					$qb->delete($conntable);
					$qb->where("did=? AND cid=?");
					$qb->setParameter(0, $sid);
					$qb->setParameter(1, $cid);
					$data = $qb->execute();
					return true;
				}
			}
			catch(Exception $e) {
				$logger = new \CodePunch\Base\CPLogger();
				$logger->error($e->getMessage());
			}
			return false;
		}
	}
	
	###########################################################################
	
	public function addDomainsToCategoryByName($domains, $category) {
		$added = 0;
		$cid = $this->getCategoryID($category);
		if($cid !== false && $cid > 0) {
			foreach($domains as $domain) {
				$added += $this->addDomainToCategoryByID($domain, $cid) ? 1 : 0;
			}
		}
		return $added;
	}
	
	###########################################################################
	
	public function addDomainsToCategoryByID($domains, $cid) {
		$added = 0;
		foreach($domains as $domain) {
			$sid = $this->getDomainID($domain);
			if($sid !== false && $sid > 0) {
				$added += $this->addDomainIDToCategoryByID($sid, $cid) ? 1 : 0;
			}
		}
		return $added;
	}
	
	###########################################################################
	
	public function addDomainIDsToCategoryByID($sids, $cid) {
		$added = 0;
		foreach($sids as $sid) {
			if($sid !== false && $sid > 0) {
				$added += $this->addDomainIDToCategoryByID($sid, $cid) ? 1 : 0;
			}
		}
		return $added;
	}
	
	###########################################################################
	
	public function setCategoryCounts()
	{
		$cattable = $this->getCategoryTableName();
		$domaintable = $this->getDomainTableName();
		$catconntable = $this->getCategoryConnectionTableName();
		
		$platform = $this->connection->getDatabasePlatform();
		$platformName = strtolower($platform->getName());
		
		$sql = array();
		if($platformName != "mssql")
			$sql[] = "UPDATE $cattable SET dcount = (SELECT count($catconntable.cid) as count FROM $catconntable WHERE $catconntable.cid = $cattable.cid AND $cattable.cid > 1)";
		else {
			// First reset all to 0 for MSSQL
			$sql[] = "update $cattable set dcount=0";
			$sql[] = "update c set c.dcount=p.domaincount FROM $cattable c INNER JOIN (select cid,count(*) AS domaincount from $catconntable GROUP BY cid) p on c.cid = p.cid";
		}
		$sql[] = "update $cattable set dcount=(SELECT count(sid) as count from $domaintable) where cid = 1";
		
		$c = 0;
		foreach($sql as $s) {
			try {
				$stmt = $this->connection->prepare($s);
				$stmt->execute();
				$c++;
			}
			catch(Exception $e) {
				$logger->error($e->getMessage());
			}
		}
		return $c;
	}
	
	###########################################################################
	
	public function addDomain($domain, $profile, $cid=0) {
		return $this->addDomains(array($domain), $profile, $cid);
	}
	
	###########################################################################
	
	public function addDomains($domains, $profile="", $cid=0, $source="") {
		$added_dids = array();
		$added_sds = array();
		$existing_dids = array();
		$connected_cids = array();
		
		$cids = array();
		if(!is_array($cid) && $cid > 1)
			$cids = array($cid);
		else if(is_array($cid))
			$cids = $cid;
		
		$table = $this->getDomainTableName();
		foreach($domains as $domain) {
			if(is_array($domain)) {
				$dinfo = $domain;
				$domain = UTIL::get_from_array($dinfo['domain'], "");
				if($domain == "")
					continue;
			}
			$di = UTIL::clean_domain_name($domain, $this->getDomainSuffixList());
			$domain = $di['domain'];
			$tld = $di['tld'];
			$subdomain = $di['subdomain'];
			if($domain == "")
				continue;
			try {
				$sid = $this->findOneOf($table, "domain", $domain, "sid");
				if($sid === false) {
					$ascii = UTIL::idn_convert($domain);
					$this->connection->insert($table, 
						array("domain" => $domain, "added_on" => new \DateTime(), "ascii_domain" => $ascii, 'tld'=>$tld, 'api_profile'=>$profile, 'import_source'=>$source),
						array(\PDO::PARAM_STR, "datetime", \PDO::PARAM_STR, \PDO::PARAM_STR, \PDO::PARAM_STR, \PDO::PARAM_STR)
					); 
					$sid = $this->findOneOf($table, "domain", $domain, "sid");
					$added_dids[] = $sid;
				}
				else if($sid > 0 && $profile != "") {
					$capi = $this->findOneOf($table, "sid", $sid, "api_profile");
					if($capi === false || $capi == "" || $capi == null) {
						$this->updateTable($table, array("api_profile"=>$profile), "sid=?", array($sid));
					}
				}
				if($sid !== false && $sid > 0 && count($cids)) {
					foreach($cids as $cid) {
						if($cid > 1) {
							if($this->addDomainIDToCategoryByID($sid, $cid) !== false)
								$connected_cids[] = $cid;
						}
					}
				}
				if($sid !== false && $sid > 0) {
					if(isset($dinfo)) {
						$dinfo['sid'] = $sid;
						unset($dinfo['domain']);
						$this->updateDomainTable($dinfo);
						unset($dinfo);
					}
					if($this->addSubdomain($sid, $subdomain) !== false)
						$added_sds[] = $subdomain;
					$existing_dids[] = $sid;
				}
			}
			catch(Exception $e) {
				$logger = new \CodePunch\Base\CPLogger();
				$logger->error($e->getMessage());
			}
		}
		$this->setCategoryCounts();
		return array('added'=>$added_dids, 'cids' => $connected_cids, 'sds' => $added_sds, 'existing'=>$existing_dids);
	}
	
	###########################################################################
	
	public function deleteDomains($ids)
	{
		$dids = explode(",", $ids);
		$valid = array();
		foreach($dids as $did) {
			if(is_numeric($did))
				$valid[] = $did;
		}
		$deleted = 0;
		foreach($valid as $sid) {
			$status = $this->deleteFromTable($this->getDomainTableName(), "sid = ?", array($sid));
			if($status !== false && $status > 0) {
				$status = $this->deleteFromTable($this->getCategoryConnectionTableName(), "did = ?", array($sid));
				$status = $this->deleteFromTable($this->getSubdomainTableName(), "sid = ?", array($sid));
				$status = $this->deleteFromTable($this->getLookupQueueTableName(), "sid = ?", array($sid));
				$status = $this->deleteFromTable($this->getDataHistoryTableName(), "sid = ?", array($sid));
				$deleted++;
			}
		}
		$this->setCategoryCounts();
		return $deleted;
	}
	
	###########################################################################
	
	public function getDomainData($select, $where="", $params=array(), $cid=0, $orderby="", $sortorder="asc", $limit=null)
	{
		try {
			$domaintable = $this->getDomainTableName();
			$catconntable = $this->getCategoryConnectionTableName();
			if($cid > 0) {
				$queryBuilder = $this->connection->createQueryBuilder();
				foreach($select as &$s) {
					if(strtolower(mb_substr($s, 0, 2)) != "d.")
						$s = "d.$s";
				}
				$select[] = "c.did";
				$queryBuilder->select($select);
				$queryBuilder->from($domaintable, 'd');
				$queryBuilder->where($where);
				$index = 0;
				foreach($params as $p) {
						$queryBuilder->setParameter($index, $p);
						$index++;
				}
				$queryBuilder->innerJoin('d', $catconntable, 'c', "c.cid=$cid AND c.did = d.sid");
				$queryBuilder->groupBy($select);
				$data = $queryBuilder->execute()->fetchAll();
				return $data;
			}
			else {
				return $this->getFromTable($select, $domaintable, $where, $params, $orderby, $sortorder, $limit);
			}
		}
		catch(Exception $e) {
			$logger = new \CodePunch\Base\CPLogger();
			$logger->error($e->getMessage());
		}
	}
	
	###########################################################################
	
	public function setDomainRowEditedToNow($sid)
	{
		$editinfo = array('edited'=>1, 'manual_edited_at'=>date("Y-m-d H:i:s"));
		return $this->updateTable($this->getDomainTableName(), $editinfo, "sid=?", array($sid));
	}
	
	###########################################################################
	
	public function resetDomainRowEdited($sid)
	{
		$editinfo = array('edited'=>0, 'manual_edited_at'=>null);
		return $this->updateTable($this->getDomainTableName(), $editinfo, "sid=?", array($sid));
	}
	
	###########################################################################
	
	public function insertIntoTable($tablename, $rows) 
	{
		if(strtolower($tablename) != strtolower($this->getDomainTableName()))
			return parent::insertIntoTable($tablename, $rows);
		try {
			$keys = array_keys($rows);
			$found = array();
			$domain = null;
			foreach($keys as $key) {
				if(strtolower($key) == "domain") {
					$found[] = "domain";
					$rows[$key] = UTIL::clean_domain_name($rows[$key], $this->getDomainSuffixList())['domain'];
					$domain = $rows[$key];
				}
				else if(strtolower($key) == "ascii_domain") 
					$found[] = "ascii_domain";
				else if(strtolower($key) == "added_on") 
					$found[] = "added_on";
			}
			if(!in_array("ascii_domain", $found) && $domain != null) {
				$rows['ascii_domain'] = UTIL::idn_convert($domain);
			}
			if(!in_array("added_on", $found) && $domain != null) {
				$rows['added_on'] = date("Y-m-d H:i:s");
			}
			if($this->connection->insert($tablename, $rows))
				return true;
		}
		catch(Exception $e) {
			$logger = new \CodePunch\Base\CPLogger();
			$logger->error($e->getMessage());
		}
		return false;
	}
	
	###########################################################################
	
	public function updateDomainTable($dataset) {
		$columns = array_map("strtolower", $this->getCurrentDomainColumnNames());
		$validdata = array();
		$sid = -1;
		$domain = "";
		$asciidomain = "";
		foreach($dataset as $key=>$value) {
			if(strtolower($key) == "sid") {
				$sid = $value;
				continue;
			}
			else if(strtolower($key) == "domain") {
				$domain = $value;
				continue;
			}
			else if(strtolower($key) == "ascii_domain") {
				$asciidomain = $value;
				continue;
			}
			else if(in_array(strtolower($key), $columns)) {
				if(strtolower($key) == "domain_id" && mb_strlen($value) > 64)
					$value = mb_substr($value, 0, 64);
				else if(strtolower($key) == "address" && mb_strlen($value) > 255)
					$value = mb_substr($value, 0, 255);
				$validdata[$key] = $value;
			}
		}
		if($sid == -1) {
			if($domain !== "")
				$sid = $this->getDomainID($domain);
			else if($asciidomain != "") 
				$sid = $this->findOneOf($this->getDomainTableName(), "ascii_domain", $asciidomain, "sid");
		}
		if($sid !== false && $sid > 0) {
			$wp = $this->findOneOf($this->getDomainTableName(), "sid", $sid, "write_protect");
			if($wp == 0)
				return $this->updateTable($this->getDomainTableName(), $validdata, "sid=?", array($sid));
			else {
				$domain = $this->getDomainName($sid);
				$logger = new \CodePunch\Base\CPLogger();
				$logger->error("Row edit failed for write protected domain $domain");
			}
		}
		return false;
	}
	
	###########################################################################
	
	public function getWhoisServerInfo($server) {
		$si = $this->getFromTable("*", $this->getWhoisServersTableName(), "server=?", array($server));
		if($si !== false && is_array($si)) {
			if(isset($si[0])) {
				$row = $si[0];
				$row = array_change_key_case($row, CASE_LOWER);
				return $row;
			}
		}
		return false;
	}
	
	###########################################################################
	
	public function addUserToGroupsByName($uid, $groups) {
		$added = 0;
		if($uid > 0) {
			foreach($groups as $g) {
				$g = trim($g);
				$gid = $this->FindOneOf($this->getUserGroupsTableName(), "name", $g, "id");
				if($gid !== false && $gid > 0) {
					if($this->insertIntoTable($this->getUserGroupAccessTableName(), array('userid'=>$uid, 'gid'=>$gid)))
						$added++;
				}
			}
		}
		return $added;
	}
	
	###########################################################################
	
	public function addUserToCategoriesByName($uid, $categories) {
		$added = 0;
		if($uid > 0) {
			foreach($categories as $c) {
				$c = trim($c);
				$cid = $this->FindOneOf($this->getCategoryTableName(), "name", $c, "cid");
				if($cid !== false && $cid > 0) {
					if($this->insertIntoTable($this->getUserCategoryAccessTableName(), array('userid'=>$uid, 'cid'=>$cid)))
						$added++;
				}
			}
		}
		return $added;
	}
	
	###########################################################################
	
	public function addUserToCategoriesByID($uid, $cids) 
	{
		$added = 0;
		if($uid > 0) {
			foreach($cids as $cid) {
				if($cid > 1) {
					if(!$this->hasRow($this->getUserCategoryAccessTableName(), array('userid', 'cid'), array($uid, $cid))) {
						if($this->insertIntoTable($this->getUserCategoryAccessTableName(), array('userid'=>$uid, 'cid'=>$cid)))
							$added++;
					}
				}
			}
		}
		return $added;
	}
	
	###########################################################################
	
	public function addUserToAllCategories($uid) 
	{
		return $this->addUserToCategoriesByID($uid, $this->getAllCategoryIDs());
	}
	
	###########################################################################
	
	public function createUser($name, $password, $active=1, $groups=array(), $cids=array(), $rights=0, $dname="", $acslevel=0, $email="") {
		if(strlen($name) < 5 || strlen($password) < 8) 
			throw new Exception(TEXT::get("db_invalidusercreateinfo"));
		$uid = $this->findOneOf($this->getUserTableName(), "name", $name, "id");
		if($uid !== false && $uid > 0)
			throw new Exception(TEXT::get("db_userexists"));
		if($email != "") {
			if(!filter_var($email, FILTER_VALIDATE_EMAIL)) 
				throw new Exception(TEXT::get("db_invaliduseremail"));
		}
		
		$securepass = UTIL::generateHash($password);
		try {
			$rows = array('name'=>$name, 'password'=>$securepass, 'rights'=>$rights, 'email'=>$email, 'active'=>$active, 'display_name'=>$dname, 'acslevel'=>$acslevel, 'added_on'=>date("Y-m-d H:i:s"));
			$status = $this->insertIntoTable($this->getUserTableName(), $rows);
			if($status) {
				$uid = $this->findOneOf($this->getUserTableName(), "name", $name, "id");
				if($uid !== false && $uid > 0) {
					if(!count($groups))
						$groups[] = "standard";
					$this->addUserToGroupsByName($uid, $groups);
					$this->addUserToCategoriesByID($uid, $cids);
				}
			}
			return true;
		}
		catch(Exception $e) {
			$logger = new \CodePunch\Base\CPLogger();
			$logger->error($e->getMessage());
		}
		return false;
	}
	
	###########################################################################
	
	public function getUserGroups($uid) {
		$gids = array();
		try {
			$queryBuilder = $this->connection->createQueryBuilder();
			$queryBuilder->select("gid");
			$queryBuilder->from($this->getUserGroupAccessTableName());
			$queryBuilder->where("userid=?");
			$queryBuilder->setParameter(0, $uid);
			$rows = $queryBuilder->execute()->fetchAll();
			foreach($rows as $row) {
				$row = array_change_key_case($row, CASE_LOWER);
				$gids[] = $row['gid'];
			}
			return $gids;
		}
		catch(Exception $e) {
			$logger = new \CodePunch\Base\CPLogger();
			$logger->error($e->getMessage());
		}
		return false;
	}
	
	###########################################################################
	
	public function getUserGroupNames($uid) {
		$gnames = array();
		$gids = $this->getUserGroups($uid);
		foreach($gids as $gid) {
			$gnames[] = $this->findOneOf($this->getUserGroupsTableName(), "id", $gid, "name");
		}
		return $gnames;
	}
	
	###########################################################################
	
	public function createUserFromAnother($existinguser, $newuser, $displayname="", $password="")
	{
		$rows = $this->getFromTable("*", $this->getUserTableName(), "name=? AND active='1'", array($existinguser));
		if($rows !== false) {
			$rows = UTIL::array_flatten($rows);
			$rows = array_change_key_case($rows, CASE_LOWER);
			$userid = $rows['id'];
			$groups = $this->getUserGroupNames($userid);
			$catids = $this->getCategoryIDsForUser($userid);
			$acslevel = $rows['acslevel'];
			$rights = $rows['rights'];
			$active = $rows['active'];
			if($password == "")
				$password = UTIL::random_string(14);
			if($displayname == "")
				$displayname = $rows['display_name'];
			return $this->createUser($newuser, $password, $active, $groups, $catids, $rights, $displayname, $acslevel);
		}
		return false;
	}
	
	###########################################################################

	public function getUserNameFromId($userid)
	{
		if($userid == \CodePunch\Config\Auth::SETUPADMIN_USERID)
			$user = "Setup Administrator";
		else if($userid == \CodePunch\Config\Auth::CLI_ACCESS_USERID)
			$user = "System";
		else
			$user = $this->findOneOf($this->getUserTableName(), "id", $userid, "name");
		return $user !== false ? $user : "";
	}

	###########################################################################
	
	public function authenticationLog($status, $user, $userid) 
	{
		$ip = $_SERVER['REMOTE_ADDR'];
		$key = (!$status) ? 'login_last_failed_at' : 'login_last_succeeded_at';
		$idata['login_fail_count'] = $status ? 0 : 1;
		$idata[$key] = date("Y-m-d H:i:s");
		
		if($userid > 0 && $status === true)
			$this->updateTable($this->getUserTableName(), array('last_sign_in_stamp'=>$idata[$key]), "id=?", array($userid));
		
		$rows = $this->getFromTable("*", $this->getAuthenticationLogTableName(), "ip=?", array($ip));
		if($rows !== false && isset($rows[0])) {
			$row = $this->fetchRow($rows, 0);
			$idata['login_fail_count'] = $status ? 0 : $row['login_fail_count'] + 1;
			$this->updateTable($this->getAuthenticationLogTableName(), $idata, "ip=?", array($ip));
		}	
		else {
			$idata['ip'] = $ip;
			$this->insertIntoTable($this->getAuthenticationLogTableName(), $idata);
		} 
		return $idata['login_fail_count'];
	}
	
	###########################################################################
	
	public function isLoginAllowed($maxlogin, $lockoutmin, &$timewait) 
	{
		$ip = $_SERVER['REMOTE_ADDR'];
		$rows = $this->getFromTable("*", $this->getAuthenticationLogTableName(), "ip=?", array($ip));
		if($rows !== false && isset($rows[0])) {
			$row = $this->fetchRow($rows, 0);
			if($row['login_fail_count'] > $maxlogin) {
				$waittill = strtotime($row['login_last_failed_at']) + ($lockoutmin*60);
				$timenow = time();
				if($timenow < $waittill) {
					$timewait = $waittill - $timenow;
					return false;
				}
			}
		}
		return true;
	}
	
	###########################################################################
	
	public function validateLogin($user, $pass) {
		$responce = array('status'=>'notok', 'user'=>'', 'group'=>'', 'error'=>'', 'secondary'=>0);
		$rows = $this->getFromTable("*", $this->getUserTableName(), "name=? AND active='1'", array($user));
		if($rows !== false) {
			$rows = UTIL::array_flatten($rows);
			$rows = array_change_key_case($rows, CASE_LOWER);
			if(isset($rows['id']) && isset($rows['password'])) {
				$storedHash = $rows['password'];
				$userpass = UTIL::generateHash($pass, $storedHash);
				if($userpass == $storedHash)
				{
					$responce['status'] = 'ok';
					$responce['userid'] = $rows['id'];
					$gids = $this->getUserGroups($rows['id']);
					$adminid = $this->findOneOf($this->getUserGroupsTableName(), "name", "admin", "id");
					$responce['admin'] = in_array($adminid, $gids) ? true : false;
					$responce['fullname'] = $rows['full_name'];
					$responce['displayname'] = $rows['display_name'];
					$responce['user'] = $rows['name'];
					$responce['rights'] = $rows['rights'];
					if($responce['rights']&self::ALLOW_ALL_DOMAINS_EVER)
						$this->addUserToAllCategories($responce['userid']);
					$responce['lastsignin'] = $rows['last_sign_in_stamp'];
				}
			}
		}
		if($responce['status'] != 'ok') 
			$responce['error'] = "Invalid name or password";
		return $responce;
	}
	
	###########################################################################
	
	public function getAllCategoryIDs()
	{
		$cids = array();
		$rows = $this->getFromTable("cid", $this->getCategoryTableName());
		if($rows !== false) {
			$index = 0;
			foreach($rows as $row) {
				$row = $this->fetchRow($rows, $index++);
				if(isset($row['cid'])) {
					$cid = intval($row['cid']);
					if($cid > 1)
						$cids[] = $cid;
				}
			}
		}
		return $cids;
	}

	###########################################################################

	public function isPrivilegedUser($userid)
	{
		if($this->isAdminuser($userid) || $userid == \CodePunch\Config\Auth::SETUPADMIN_USERID || $userid == \CodePunch\Config\Auth::CLI_ACCESS_USERID)
			return true;
		return false;
	}

	###########################################################################

	public function isAdminUser($userid)
	{
		$gids = $this->getUserGroups($userid);
		$adminid = $this->findOneOf($this->getUserGroupsTableName(), "name", "admin", "id");
		return in_array($adminid, $gids) ? true : false;
	}
	
	###########################################################################
	
	public function getCategoryIDsForUser($userid)
	{
		$cids = array();
		if($this->isAdminuser($userid))
			$rows = $this->getFromTable("cid", $this->getCategoryTableName());
		else 
			$rows = $this->getFromTable("cid", $this->getUserCategoryAccessTableName(), "userid=?", array($userid));
		if($rows !== false) {
			$index = 0;
			foreach($rows as $row) {
				$row = $this->fetchRow($rows, $index++);
				if(isset($row['cid'])) {
					$cid = intval($row['cid']);
					if($cid > 1)
						$cids[] = $cid;
				}
			}
		}
		return $cids;
	}
	
	###########################################################################
	
	public function getCategoryIDsForCurrentUser($auth)
	{
		$cids = array();
		if($auth->isAdmin() || UTIL::is_cli())
			$rows = $this->getFromTable("cid", $this->getCategoryTableName());
		else {
			$userid = $auth->getUserID();
			$rows = ($userid === false) ? false : $this->getFromTable("cid", $this->getUserCategoryAccessTableName(), "userid=?", array($userid));
		}
		if($rows !== false) {
			$index = 0;
			foreach($rows as $row) {
				$row = $this->fetchRow($rows, $index++);
				if(isset($row['cid'])) {
					$cid = intval($row['cid']);
					if($cid > 1)
						$cids[] = $cid;
				}
			}
		}
		// Returning an empty array will signal access to all categories.
		// So we will add an impossible category id
		if(count($cids) == 0)
			$cids[] = -10;
		
		return $cids;
	}
	
	###########################################################################
	
	public function isDomainInAnyAllowedCategory($sid, $auth)
	{
		$rights = $auth->getUserAccess();
		if($auth->isAdmin() || ($rights&\CodePunch\DB\DomainDB::ALLOW_ALL_DOMAINS_EVER)) 
			return true;
		
		$cids = $this->getCategoryIDsForCurrentUser($auth);
		$sid = intval($sid);
		$rows = $this->getFromTable("cid,did", $this->getCategoryConnectionTableName(), "did=$sid AND cid IN (?)", array(array($cids, \Doctrine\DBAL\Connection::PARAM_INT_ARRAY)));
		if($rows !== false && count($rows)) {
			$row = $this->fetchRow($rows, 0);
			if(isset($row['cid']) && in_array($row['cid'], $cids))
				return true;
		}
		return false;
	}
	
	###########################################################################
	
	public function auditLog($action, $description=null, $params=null)
	{
		$uid = \CodePunch\Config\Auth::getCurrentUserID();
		$remote = isset($_SERVER['REMOTE_ADDR']) ? $_SERVER['REMOTE_ADDR'] : "";
		if(UTIL::is_cli()) {
			if($remote == "")
				$remote = "Terminal";
		}
		$lastconn = date("Y-m-d H:i:s");
		return $this->insertIntoTable($this->getAuditLogTableName(), 
			array('action'=>$action, 'description'=>$description, 'params'=>$params, 'timeat'=>$lastconn, 'userid'=>$uid, 'remote'=>$remote));
	}

	###########################################################################

	public function getAuditData($auth,$maxcount=50, $keyword="")
	{
		$data = array();
		$query = ($keyword == "") ? "" : "description LIKE ?";
		$kwdata = ($keyword == "") ? array() : array('%' . trim(str_replace("%", "", $keyword)) . '%');
		$rows = $this->getFromTable("*", $this->getAuditLogTableName(), $query, $kwdata, "timeat", "desc", $maxcount);
		if($rows !== false) {
			$index = 0;
			$audit = new AUDIT($this);
			$lookup = new \CodePunch\LU\LookupManager($auth);
			foreach($rows as $row) {
				$row = $this->fetchRow($rows, $index++);
				$action = $row['action'];
				$description = $row['description'];
				$userid = $row['userid'];
				$user = $this->getUserNameFromId($userid);
				$params = $row['params'];
				if($action == AUDIT::SCHEDULE_LOOKUPS || $action == AUDIT::DELETE_LOOKUP_QUEUE)
					$params = $lookup->getLookupTypeLabel($params);
				$remote = $row['remote'];
				$timeat = $row['timeat'];
				$info = str_replace(array("audit_", "unknown"), "", $audit->getDescription($action));
				$info = trim(ucwords(str_replace("_", " ", $info)));

				//$rowdata = "User [$user] $info at $timeat from [$remote]";
				$rowdata = "[AE#$action] $timeat: User [$user] $info from [$remote]";
				$extrainfo = array();
				$extrainfo[] = $description != "" ? "*# $description #*" : "";
				$extrainfo[] = $params;
				$extrainfo = array_filter($extrainfo);
				if(count($extrainfo))
					$rowdata .= " " . implode(", ", $extrainfo) . "";
				$data[] = $rowdata;
			}
		}
		return $data;
	}
	
	###########################################################################
	
	public function isSubdomain($sid, $sd)
	{
		$subdomain = UTIL::clean_subdomain($sd);
		if($subdomain != "" && $sid > 0) {
			$rows = $this->getFromTable("subdomain", $this->getSubdomainTableName(), "sid=? AND subdomain=? AND auto_added=?", array($sid, $subdomain, \CodePunch\LU\LookupManager::SD_USER_ROWS));
			if($rows !== false  && count($rows))
				return true;
		}
		return false;
	}

	###########################################################################
	
	public function addSubdomain($sid, $sd)
	{
		$subdomain = UTIL::clean_subdomain($sd);
		if($subdomain != "" && $sid > 0) {
			$rows = $this->getFromTable("subdomain", $this->getSubdomainTableName(), "sid=? AND subdomain=? AND auto_added=?", array($sid, $subdomain, \CodePunch\LU\LookupManager::SD_USER_ROWS));
			if($rows === false || !count($rows)) {
				return $this->insertIntoTable($this->getSubdomainTableName(), array('sid'=>$sid, 'subdomain'=>$subdomain, 'auto_added'=>\CodePunch\LU\LookupManager::SD_USER_ROWS, 'added_on'=>date("Y-m-d H:i:s")));
			}
			else {
			}
		}
		return false;
	}
	
	###########################################################################
	
	public function isTXTName($sid, $sd)
	{
		$subdomain = UTIL::clean_subdomain($sd);
		if($subdomain != "" && $sid > 0) {
			$rows = $this->getFromTable("subdomain", $this->getSubdomainTableName(), "sid=? AND subdomain=? AND auto_added=?", array($sid, $subdomain, \CodePunch\LU\LookupManager::SD_TXT_ROWS));
			if($rows !== false && count($rows))
				return true;
		}
		return false;
	}
	
	###########################################################################
	
	public function addTXTName($sid, $sd)
	{
		$subdomain = UTIL::clean_subdomain($sd);
		if($subdomain != "" && $sid > 0) {
			$rows = $this->getFromTable("subdomain", $this->getSubdomainTableName(), "sid=? AND subdomain=? AND auto_added=?", array($sid, $subdomain, \CodePunch\LU\LookupManager::SD_TXT_ROWS));
			if($rows === false || !count($rows)) {
				return $this->insertIntoTable($this->getSubdomainTableName(), array('sid'=>$sid, 'subdomain'=>$subdomain, 'auto_added'=>\CodePunch\LU\LookupManager::SD_TXT_ROWS, 'added_on'=>date("Y-m-d H:i:s")));
			}
			else {
			}
		}
		return false;
	}
	
	###########################################################################

	public function addDefaultSubdomains($sid, $sddata)
	{
		$added = 0;
		$sddata = trim($sddata);
		if($sddata != "")
		{
			$subdomains = explode(",", $sddata);
			foreach($subdomains as $sd) {
				if($this->addSubdomain($sid, $sd) !== false)
					$added++;
			}
		}
		if($this->addSubdomain($sid, "@") !== false)
			$added++;
		return $added;
	}
	
	###########################################################################
	
	public function customExpandWhere($where)
	{
		$where = parent::customExpandWhere($where);
		// Category Query
		$pos = strpos($where, "[IN_");
		while($pos !== false) {
			$tleft = substr($where, 0, $pos);
			$tright = substr($where, $pos+4);
			$pend = strpos($tright, "]");
			if($pend !== false) {
				$category = substr($tright, 0, $pend);
				$tright = substr($tright, $pend+1);
				$catid = intval($this->getCategoryID($category));
				$dc = " ((d.sid IN (SELECT DISTINCT c.did from [CATCONNTABLE] c WHERE c.did=d.sid AND (c.cid = $catid)))) ";
				$where = $tleft . $dc . $tright;
				$pos = strpos($where, "[IN_");
			}
			else
				break;
		}
		$pos = strpos($where, "[NOTIN_");
		while($pos !== false) {
			$tleft = substr($where, 0, $pos);
			$tright = substr($where, $pos+7);
			$pend = strpos($tright, "]");
			if($pend !== false) {
				$category = substr($tright, 0, $pend);
				$tright = substr($tright, $pend+1);
				$catid = intval($this->getCategoryID($category));
				$dc = " ((d.sid NOT IN (SELECT DISTINCT c.did from [CATCONNTABLE] c WHERE c.did=d.sid AND (c.cid = $catid)))) ";
				$where = $tleft . $dc . $tright;
				$pos = strpos($where, "[NOTIN_");
			}
			else
				break;
		}
		//
		$where = str_ireplace("[DOMAINTABLE]", $this->getDomainTableName(), $where);
		$where = str_ireplace("[CATCONNTABLE]", $this->getCategoryConnectionTableName(), $where);
		$where = str_ireplace("[CATTABLE]", $this->getCategoryTableName(), $where);
		$where = str_ireplace("[SUBDOMAINTABLE]", $this->getSubdomainTableName(), $where);
		$where = str_ireplace("[REPSCHEDULETABLE]", $this->getReportSchedulerTableName(), $where);
		$where = str_ireplace("[LUSCHEDULETABLE]", $this->getLookupSchedulerTableName(), $where);
		
		$where = str_ireplace("[IS_HOSTNAME]", '(s.auto_added = 0)', $where);
		$where = str_ireplace("[IS_DNS_RECORD]", '(s.auto_added = 1)', $where);
		$where = str_ireplace("[IS_SSL_RECORD]", '(s.auto_added = 2)', $where);
		$where = str_ireplace("[IS_COOKIE]", '(s.auto_added = 7)', $where);
		$where = str_ireplace("[IS_TXT_RECORD]", '(s.auto_added = 1 AND s.record_type=\'TXT\')', $where);
		$where = str_ireplace("[IS_A_RECORD]", '(s.auto_added = 1 AND s.record_type=\'A\')', $where);
		$where = str_ireplace("[IS_AAAA_RECORD]", '(s.auto_added = 1 AND s.record_type=\'AAAA\')', $where);
		$where = str_ireplace("[IS_NS_RECORD]", '(s.auto_added = 1 AND s.record_type=\'NS\')', $where);
		$where = str_ireplace("[IS_MX_RECORD]", '(s.auto_added = 1 AND s.record_type=\'MX\')', $where);
		$where = str_ireplace("[IS_CNAME_RECORD]", '(s.auto_added = 1 AND s.record_type=\'CNAME\')', $where);
		$where = str_ireplace("[IS_VALID_DNSRECORD]", ' (record_type is NOT NULL AND record_type != \'\')', $where);
		return $where;
	}
	
	###########################################################################
	
	public function getColumnDetails($table, $extraquery="", $params=array())
	{
		$uid = \CodePunch\Config\Auth::getCurrentUserID();
		$acslevel = 0;
		if($uid > 0)
			$acslevel = $this->findOneOf($this->getUserTableName(), "id", $uid, "acslevel");
		if(\CodePunch\Config\Auth::isAdmin())
			$acslevel = 24;
		
		$index = 0;
		$columns = array();
		$table = $this->getTableNameWithoutPrefix($table);
		$query = "server=? AND tablename=? AND acslevel <= ?";
		$qdata = array("*", $table, $acslevel);
		if($extraquery != "")
			$query .= " AND $extraquery";
		if(count($params))
			$qdata = array_merge($qdata, $params);
		$ci = $this->getFromTable("*", $this->getDatacolumnsTableName(), "$query ORDER BY name ASC", $qdata);
		if($ci !== false) {
			foreach($ci as $c) {
				$tr = $this->fetchRow($ci, $index++);
				$name = UTIL::get_from_array($tr['name'], "");
				if($name != "") {
					unset($tr['name']);
					unset($tr['tablename']);
					unset($tr['server']);
					unset($tr['id']);
					$columns[$name] = $tr;
				}
			}
		}
		return $columns;
	}
	
	###########################################################################
	
	public function getReportColumnInfo($columnname)
	{
		$columnname = strtolower($columnname);
		if(substr($columnname, 0, 2) == "d.") {
			$columnname = substr($columnname, 2);
			$ci = $this->getColumnDetails($this->getDomainTableName());
		}
		else if(substr($columnname, 0, 2) == "s.") {
			$columnname = substr($columnname, 2);
			$ci = $this->getColumnDetails($this->getSubdomainTableName());
		}
		else {
			$ci = $this->getColumnDetails($this->getDomainTableName());
			if(!isset($ci[$columnname]))
				$ci = $this->getColumnDetails($this->getSubdomainTableName());
			
		}
		if(isset($ci[$columnname]))
			return $ci[$columnname];
		
		if($columnname == "hostname") {
			return array('name'=>$columnname, 'tablename'=>'', 'label'=>'Hostname');
		}
		
		return false;
	}
	
	###########################################################################
	
	public function getColumnNames($tablename)
	{
		$columns = array();
		$ci = $this->getColumnDetails($tablename);
		foreach($ci as $k=>$c) {
			$columns[] = $k;
		}
		return $columns;
	}
	
	###########################################################################
	
	public function getCurrentDomainColumnNames()
	{
		return $this->getColumnNames($this->getDomainTableName());
	}
	
	###########################################################################
	
	public function getDetailsOfAllDomainColumns()
	{
		return $this->getColumnDetails($this->getDomainTableName());
	}
	
	###########################################################################
	
	public function getEditableDomainColumnNames()
	{
		$columns = array();
		$ci = $this->getColumnDetails($this->getDomainTableName());
		foreach($ci as $k=>$c) {
			if($c['editable']) {
				$columns[] = $k;
			}
		}
		return $columns;
	}
	
	###########################################################################
	
	public function getCustomDomainColumnNames()
	{
		$columns = array();
		$ci = $this->getColumnDetails($this->getDomainTableName());
		foreach($ci as $k=>$c) {
			if($c['custom']) {
				$columns[] = $k;
			}
		}
		return $columns;
	}
	
	###########################################################################
	
	public function getDefaultDomainColumnNames()
	{
		$columns = array();
		$ci = $this->getColumnDetails($this->getDomainTableName());
		foreach($ci as $k=>$c) {
			if(!$c['custom']) {
				$columns[] = $k;
			}
		}
		return $columns;
	}
	
	###########################################################################
	
	public function getCurrentSubdomainColumnNames()
	{
		$columns = array();
		$ci = $this->getColumnDetails($this->getSubdomainTableName());
		foreach($ci as $k=>$c) {
			$columns[] = $k;
		}
		return $columns;
	}
	
	###########################################################################
	
	public function getEditableSubdomainColumnNames()
	{
		$columns = array();
		$ci = $this->getColumnDetails($this->getSubdomainTableName());
		foreach($ci as $k=>$c) {
			if($c['editable']) {
				$columns[] = $k;
			}
		}
		return $columns;
	}
	
	###########################################################################

	public function getGridReportColumnNames($multitable=false)
	{
		$columns = array();
		$ci = $this->getColumnDetails($this->getDomainTableName());
		foreach($ci as $k=>$c) {
			if($c['gridview']) {
				$columns[] = "d." . $k;
			}
		}
		if($multitable) {
			$ci = $this->getColumnDetails($this->getSubdomainTableName());
			foreach($ci as $k=>$c) {
				if($c['gridview']) {
					$columns[] = "s." . $k;
				}
			}
			$columns = array_merge(array('s.hostname','d.days_to_expiry'), $columns);
		}
		return $columns;
	}

	###########################################################################
	// Get column details for a specific installation server, if not present
	// get the default details (serverid=*).
	
	public function getDetailsOfDomainColumn($columnname, $serverid="*")
	{
		$index = 0;
		$table = $this->getTableNameWithoutPrefix($this->getDomainTableName());
		$ci = $this->getFromTable("*", $this->getDatacolumnsTableName(), "server=? AND tablename=? AND name=?", array($serverid, $table, $columnname));
		if($ci !== false) {
			foreach($ci as $c) {
				$tr = $this->fetchRow($ci, $index++);
				return $tr;
			}
		}
		return false;
	}
	
	###############################################################################
	
	public function getColumnNameWithTablePrefix($column)
	{
		if(!UTIL::starts_with($column, "s.") && !UTIL::starts_with($column, "d.")) {
			$rows = $this->getCurrentDomainColumnNames();
			if($rows !== false) {
				if(UTIL::in_array_casei($column, $rows))
					return "d." . $column;
			}	
			$rows = $this->getCurrentSubdomainColumnNames();
			if($rows !== false) {
				if(UTIL::in_array_casei($column, $rows))
					return "s." . $column;
			}
		}
		return $column;
	}
	
	###############################################################################
	
	public function getRegistrarAPIInfo($row, &$info, $auth)
	{
		$registrar = $row['registrar'];
		$keys = $row['apikeys'];
		if(strpos($keys, "\n") !== false)
			$apikeys = explode("\n", $keys);
		else
			$apikeys = explode(";", $keys);
		foreach($apikeys as $key) {
			$p = strpos($key, "]=");
			if($p !== false && $p > 0) {
				$kname = trim(substr($key, 0, $p), "\t\n\r []");
				$kvalue = trim(substr($key, $p+2));
				if($kvalue != "" && $kvalue !== false)
					$kvalue = $auth->decrypt($kvalue);
				$info['params'][] = $kvalue;
				$info['keys'][] = $kname;
			}
		}
		$class = "\\CodePunch\\LU\\Registrars\\" . $registrar;
		if(class_exists($class)) {
			$info['class'] = $class;
			$info['registrar'] = $registrar;
			
			$luManager = new \CodePunch\LU\LookupManager($auth);
			$regapi = new $info['class']($luManager, ...$info['params']);
			$info['supported'] = $regapi->supported();
		}
		return $info;
	}
	
	###############################################################################
	
	public function getRegistrarAPIClass($apiprofile, $auth)
	{
		$responce = array('class'=>'', 'params'=>array(), 'keys'=>array());
		$rows = $this->getFromTable("*", $this->getAPIProfilesTableName(), "name=?", array($apiprofile));
		if($rows !== false && isset($rows[0])) {
			$row = $this->fetchRow($rows, 0);
			$this->getRegistrarAPIInfo($row, $responce, $auth);
		}
		return $responce;
	}
	
	###############################################################################
	# Add a prefix to domain (d.) or subdomain (s.) column names
	
	public function formatSortingColumn($column, &$maintablecolumns=null)
	{
		// Special case
		if($column == "hostname") 
			$column = "s.$column";
		else if($column == "days_to_expiry" || $column == "d.days_to_expiry" || $column == "s.days_to_expiry")
			$column = "";
		else if(strtolower(mb_substr($column, 0, 2)) != "d." && strtolower(mb_substr($column, 0, 2)) != "s.") {
			if($maintablecolumns == null || $maintablecolumns === false || $maintablecolumns == array())
				$maintablecolumns = $this->getCurrentDomainColumnNames();
			if(in_array($column, $maintablecolumns))
				$column = "d.$column";
			else 
				$column = "s.$column";
		}
		return $column;
	}
	
	###############################################################################
	# Add a prefix to domain (d.) or subdomain (s.) column names
	
	public function formatSelectOrGroupColumn($column, &$maintablecolumns=null, $forselect=false)
	{
		// Special case
		if($column == "hostname" || $column == "s.hostname") {
			$platform = $this->connection->getDatabasePlatform();
			$platformName = $platform->getName();
			// ::WARNING:: PLATFORM DEPENDENT CODE //
			if(strtolower($platformName) == "oracle")
				$column = "CONCAT(s.subdomain,CONCAT('.', d.domain))";
			else if(strtolower($platformName) == "sqlite")
				$column = "s.subdomain || '.' || d.domain";
			else
				$column = "CONCAT('', s.subdomain, '.', d.domain)";
			if($forselect)
				$column .= " AS s_hostname";
		}
		else if(strtolower(mb_substr($column, 0, 2)) != "d." && strtolower(mb_substr($column, 0, 2)) != "s.") {
			if($maintablecolumns == null || $maintablecolumns === false || $maintablecolumns == array())
				$maintablecolumns = $this->getCurrentDomainColumnNames();
			if(in_array($column, $maintablecolumns))
				$column = "d.$column";
			else {
				if(!$forselect)
					$column = "s.$column";
				else
					$column = "s.$column as s_$column";
			}
		}
		else if($forselect && strtolower(mb_substr($column, 0, 2)) == "s.") {
			$column = substr($column, 2);
			$column = "s.$column as s_$column";
		}
		return $column;
	}
	
	###############################################################################
	# return a where Query that will find domains that belong to a specific category 
	# identified by $cid or all domains if $cid <= 1
	# $validcids is an array of category ids the domains should always belong to.
	# if $validcids is empty, this condition is ignored.
	
	public function getCategoryQuery($cid, $validcids)
	{
		$catconntable = $this->getCategoryConnectionTableName();
		if($cid > 1 && count($validcids) && !in_array($cid, $validcids))
			return "(1=0)"; // No results because the $cid is not in valid cid list.
		else if($cid > 1)
			return "(d.sid IN (SELECT DISTINCT c.did from $catconntable c WHERE (c.did=d.sid) AND (c.did=d.sid AND (c.cid=$cid))))";
		else if(count($validcids)) {
			$wc = "c.cid in (" . implode(",", $validcids) . ")";
			return "(d.sid IN (SELECT DISTINCT c.did from $catconntable c WHERE (c.did=d.sid) AND (c.did=d.sid AND ($wc))))";
		}
		return "";
	}
	
	###############################################################################
	# return a where Query that will find domains that belong to a specific category 
	# identified by $cid or all domains if $cid <= 1
	# $auth is used to identify current $validcids
	# if $auth is null and $validcids is empty all categories will be allowed.
	# if $auth is not null, passed $validcids will be ignored
	
	public function getCategoryQueryForCurrentUser($cid, $auth=null, $validcids=array())
	{
		if($auth) {
			$rights = $auth->getUserAccess();
			if(!$auth->isAdmin() && !($rights&\CodePunch\DB\DomainDB::ALLOW_ALL_DOMAINS_EVER))
				$validcids = $this->getCategoryIDsForCurrentUser($auth);
		}
		return $this->getCategoryQuery($cid, $validcids);
	}
	
	###############################################################################
	
	public function getDomainReportRows($query, $params=null, $columns="d.domain", $sidx = "d.domain", $sord="asc", $start=0, $limit=null)
	{
		$maintable = $this->getDomainTableName();
		try {
			$alldomaincolumns = null;
			$has_subdomain_columns = false;
			$queryBuilder = $this->connection->createQueryBuilder();
			if($columns == "*") {
				$alldomaincolumns = $this->getCurrentDomainColumnNames();
				$select = $alldomaincolumns;
			}
			else
				$select = explode(",", $columns);
			// The sort column should not be ambiguous.
			if($sidx != "" && substr($sidx, 1, 1) != ".") 
				$sidx = $this->formatSortingColumn($sidx, $alldomaincolumns);
			$groupby = $select;
			if(!in_array($sidx, $select) && $sidx != "") {
				//$select[] = $sidx;
				//$groupby[] = $sidx;
				$sidx = "";
			}
			foreach($select as &$s) {
				$s = $this->formatSelectOrGroupColumn($s, $alldomaincolumns, true);
				if($has_subdomain_columns === false && (strtolower(mb_substr($s, 0, 2)) == "s." || strtolower(mb_substr($s, 0, 6)) == "concat"))
					$has_subdomain_columns = true;
			}
			foreach($groupby as &$s) 
				$s = $this->formatSelectOrGroupColumn($s, $alldomaincolumns, false);

			$queryBuilder->select($select);
			$queryBuilder->from($maintable, 'd');
			if($has_subdomain_columns) {
				$subdomaintable = $this->getSubdomainTableName();
				$queryBuilder->innerJoin('d', $subdomaintable, 's', "d.sid=s.sid");
			}
			if($query != "") {
				$queryBuilder->where($this->customExpandWhere($query));
				if($params == null || $params === false || $params == "")
					$params = array();
				if(!is_array($params))
					$params = explode(",", $params);
				$index = 0;
				foreach($params as $p) {
					if(!is_array($p)) {
						if(strtolower($p) == "empty")
							$p = "";
						$p = UTIL::expand_date_codes($p);
						$queryBuilder->setParameter($index, $p);
						$index++;
					}
					else if(count($p) == 2) {
						$queryBuilder->setParameter($index, $p[0], $p[1]);
						$index++;
					}
				}
			}
			if(isset($groupby))
				$queryBuilder->groupBy($groupby);
			if($sidx != "" && $sidx != "s.hostname") 
				$queryBuilder->orderby($sidx, $sord);
			else if($sidx == "s.hostname") 
				$queryBuilder->orderby("domain,subdomain", $sord);
			if($limit != null && $start >= 0) {
				$queryBuilder->setFirstResult($start);
				$queryBuilder->setMaxResults($limit);
			}
			//UTIL::debug_print($queryBuilder->getSQL());
			$rows = $queryBuilder->execute()->fetchAll();
			foreach($rows as &$row)
				$row = array_change_key_case($row, CASE_LOWER);
			return $rows;
		}
		catch(Exception $e) {
			$logger = new \CodePunch\Base\CPLogger();
			$logger->error($e->getMessage());
		}
		return false;
	}
	
	###############################################################################
	
	public function saveDomainRowsToCSVFile($filename, $allowedcolumns, $query, $params, $sortby="domain", $sortorder="asc", $pagesize=500, $maxrows=5000, $delimiter=",")
	{
		$buffer = false;
		if($filename == "") {
			$filename = "php://temp";
			$buffer = true;
		}
		$delimiter = UTIL::sanitizeCSVDelimiter($delimiter);

		$rowcount = 0;
		$csv = "";
		$output = fopen($filename,'r+');
		if($output) {
			$select = implode(",", $allowedcolumns);
			fputcsv($output, $allowedcolumns, $delimiter);
			$start = 0;
			if($maxrows <= 0) 
				$maxrows = 500000000; // Just a random large number
			while($rowcount < $maxrows) {
				$rows = $this->getDomainReportRows($query, $params, $select, $sortby, $sortorder, $start, $pagesize);
				if($rows !== false && count($rows)) {
					$index = 0;
					foreach($rows as $row) {
						$row = $this->fetchRow($rows, $index++);
						if(array_key_exists('days_to_expiry', $row))
							$row['days_to_expiry'] = $this->findDaysToExpiry($row);
						fputcsv($output, $row, $delimiter);
					}
					$rowcount += count($rows);
					$start += count($rows);
				}
				else
					break;
			}
			if($buffer === true) {
				rewind($output);
				$csv = stream_get_contents($output);
			}
			fclose($output);
		}
		return $buffer ? $csv : $rowcount;
	}
	
	###############################################################################

	public function updateDataHistory($sid, $ftype, $fval)
	{
		$conntime = date("Y-m-d H:i:s");
		if($ftype == intval(\CodePunch\LU\LookupManager::DOMAIN_RECORDS) || $ftype == intval(\CodePunch\LU\LookupManager::AUTH_DOMAIN_RECORDS))
			return $this->insertIntoTable($this->getDataHistoryTableName(), array('sid'=>$sid, 'ftype'=>intval($ftype), 'tvalue'=>$fval, 'fvalue'=>0, 'lookedup_at'=>$conntime));
		else
			return $this->insertIntoTable($this->getDataHistoryTableName(), array('sid'=>$sid, 'ftype'=>intval($ftype), 'fvalue'=>intval($fval), 'lookedup_at'=>$conntime));
	}
	
	###############################################################################
	
	public function setRowHighlights($days)
	{
		$daysinfuture = date("Y-m-d", time() + $days*24*3600);
		$sixmonthsago = date("Y-m-d", time() - 180*24*3600);
		$today = date("Y-m-d");
		$domaintable = $this->getDomainTableName();
		$queries = array( 
		"UPDATE $domaintable SET r_h_disp = 0 WHERE registry_expiry IS NOT NULL OR registrar_expiry IS NOT NULL",
		"UPDATE $domaintable SET r_h_disp = 10 WHERE edited = '1'",
		"UPDATE $domaintable SET r_h_disp = 14 WHERE registry_expiry IS NULL AND registrar_expiry IS NULL",
		"UPDATE $domaintable SET r_h_disp = 19 WHERE write_protect = '1'",
		"UPDATE $domaintable SET r_h_disp = 11 WHERE status = 'Setup Error'",
		"UPDATE $domaintable SET r_h_disp = 12 WHERE availability = 'Available'",
		"UPDATE $domaintable SET r_h_disp = 13 WHERE primary_whois_checked_at IS NOT NULL AND primary_whois_checked_at < '$sixmonthsago'",
		"UPDATE $domaintable SET r_h_disp = 15 WHERE registry_expiry IS NOT NULL AND registry_expiry <  '$daysinfuture'",
		"UPDATE $domaintable SET r_h_disp = 18 WHERE status LIKE '%pendingdelete%'",
		"UPDATE $domaintable SET r_h_disp = 20 WHERE status LIKE '%nameserverdiscrepancy%'",
		"UPDATE $domaintable SET r_h_disp = 17 WHERE (registrar_expiry IS NOT NULL AND registrar_expiry <=  '$today') OR (registry_expiry IS NOT NULL  AND registry_expiry <= '$today')",
		"UPDATE $domaintable SET r_h_disp = 16 WHERE registrar_expiry IS NOT NULL AND registry_expiry IS NOT NULL AND registrar_expiry <  '$daysinfuture' AND registry_expiry >=  '$daysinfuture'"
		);

		foreach($queries as $query)
		{
			try {
				/*
				if($query == "STATUSQUERY") {
					$query = "";
					$sarray_s = trim(getConfigData('highlight_status_codes', null));
					if($sarray_s != "") {
						$sarray = explode(",", $sarray_s);
						$s1 = "";
						foreach($sarray as $s)
						{
							$s = trim($s);
							if($s != "")
							{
								if($s1 != "")
									$s1 .= " OR";
								$s1 .= " status LIKE '%$s%'";
							}
						}
						$s1 = trim($s1);
						if($s1 != "")
							$query = "UPDATE $domaintable SET r_h_disp = 18 WHERE ($s1)";
					}
				}
				*/
				if($query != "")
				{
					$statement = $this->connection->prepare($query);
					$statement->execute();
				}
			}
			catch (PDOException $e) 
			{
				$this->setError($e->getMessage());
				return false;
			}
		}
		return true;
	}
	
	###############################################################################
	
	public function initTables($tables=array())
	{
		$tabledatafolder = UTIL::get_install_folder_path() . "lib/php/CodePunch/DB/Init/";
		if(count($tables) == 0) {
			// Repair data columns table if no tables are mentioned.
			$dctable = $this->getDatacolumnsTableName();
			$tables = array($this->getTableNameWithoutPrefix($dctable));
		}
		return $this->updateInitDataFromFile($tabledatafolder, $tables);
	}
	
	###############################################################################
	
	public static function repairTables($tables=array())
	{
		$auth = new \CodePunch\Config\Auth(\CodePunch\DB\Database::REPAIR_TABLES);
		if($auth) {
			$db = $auth->getDatabase();
			if($db) 
				return $db->initTables($tables);
		}
		return 0;
	}

	###############################################################################
	// Temporary method to create missing columns. This is required for
	// backward compatibility and can be removed a few updates later.
	public function fixTablesForUpdate() {
		// Check if database was fixed for current build.
		$last_dbfixed_build = 5000;
		$this_build = intval(\CodePunch\Config\Defaults::BUILD_INDEX);
		$rows = $this->getFromTable("svalue", $this->getSettingsTableName(), "name=? AND server=?", array("last_dbfixed_build", "*"));
		if($rows !== false  && isset($rows[0])) {
			$row = $this->fetchRow($rows, 0);
			if(isset($row['svalue'])) {
				$last_dbfixed_build = intval($row['svalue']);
				if($last_dbfixed_build >= $this_build || $last_dbfixed_build >= 6000) // Ignore Version 6+ builds (6000+)
					return;
			}
		}
		else {
			$this->insertIntoTable($this->getSettingsTableName(), array('name'=>'last_dbfixed_build', 'svalue'=>$last_dbfixed_build, 'server'=>'*'));
			return;
		}

		$dbcheckmsg = "Application updated from build $last_dbfixed_build to $this_build, performing database checks";
		$logger = new \CodePunch\Base\CPLogger();
		$logger->info($dbcheckmsg);
		\CodePunch\DB\Audit::add($this, \CodePunch\DB\Audit::APP_UPDATE_DBCHECK, $dbcheckmsg, "");
		$added = 0;
		
		// Jan 28 2025
		// Change default LU to RDAP
		if($last_dbfixed_build < 5078) {
			$this->updateTable($this->getTLDsTableName(), array('lu_sequence'=>1), "1=1", array());
		}
		
		// March 2022
		// Create the Cookie Columns in Subdomain Table
		if(\CodePunch\Config\Auth::hasProClass('Cookies')) {
			$cookie_columns = array(
				array('name'=>'cookie_type', 'type'=>'string', 'options'=>array('length'=>32,'notnull'=>false,'default'=>'')),
				array('name'=>'cookie_name', 'type'=>'string', 'options'=>array('length'=>2048,'notnull'=>false,'default'=>'')),
				array('name'=>'cookie_value', 'type'=>'string', 'options'=>array('length'=>2048,'notnull'=>false,'default'=>'')),
				array('name'=>'cookie_domain', 'type'=>'string', 'options'=>array('length'=>255,'notnull'=>false,'default'=>'')),
				array('name'=>'cookie_thirdparty', 'type'=>'string', 'options'=>array('length'=>255,'notnull'=>false,'default'=>'')),
				array('name'=>'cookie_expiry', 'type'=>'datetime', 'options'=>array('notnull'=>false,'default'=>null))
			);
			foreach($cookie_columns as $cc) {
				$ci = $this->getColumnInfo($this->getSubdomainTableName(), $cc['name']);
				if($ci === false) {
					$this->insertColumnsAndKeys($this->getSubdomainTableName(), array($cc), array());
					if(!$this->hasRow($this->getDatacolumnsTableName(), array('name', 'tablename'), array($cc['name'], self::SUBDOMAIN_TABLE))) {
						$label = ucfirst(str_replace("_", " ", $cc['name']));
						$datacolumn = array('name'=>$cc['name'],'tablename'=>self::SUBDOMAIN_TABLE,'server'=>'*','label'=>$label,'editable'=>0,'gridview'=>1,'width'=>160,'fieldtype'=>'string','custom'=>0);
						if($this->insertIntoTable($this->getDatacolumnsTableName(), $datacolumn) !== false) {
							$added++;
							\CodePunch\DB\Audit::add($this, \CodePunch\DB\Audit::REPAIR_SUBDOMAIN_COLUMN, "Added " . $cc['name'], "");
						}
					}
				}
			}
		}
		
		// August 2023
		// Create 'subdomains_autofound_at' column
		$ci = $this->getColumnInfo($this->getDomainTableName(), 'subdomains_autofound_at');
		if($ci === false) {
			$cc = array('name'=>'subdomains_autofound_at', 'type'=>"datetime", 'options'=>array('notnull'=>false,'default'=>null));
			$this->insertColumnsAndKeys($this->getDomainTableName(), array($cc), array('index'=>'subdomains_autofound_at'));
			if(!$this->hasRow($this->getDatacolumnsTableName(), array('name', 'tablename'), array('subdomains_autofound_at', self::DOMAIN_TABLE))) {
				$label = 'Subdomains Auto-found At';
				$datacolumn = array('name'=>$cc['name'],'tablename'=>self::DOMAIN_TABLE,'server'=>'*','label'=>$label,'editable'=>0,'gridview'=>1,'width'=>160,'fieldtype'=>'datetime','custom'=>0);
				if($this->insertIntoTable($this->getDatacolumnsTableName(), $datacolumn) !== false) {
					$added++;
					\CodePunch\DB\Audit::add($this, \CodePunch\DB\Audit::REPAIR_DOMAIN_COLUMN, "Added subdomains_autofound_at", "");
				}
			}
		}
		$this->fixSubdomainDuplicates();
		
		// March 2022
		// Create 'websites_checked_at' column
		$ci = $this->getColumnInfo($this->getDomainTableName(), 'websites_checked_at');
		if($ci === false) {
			$cc = array('name'=>'websites_checked_at', 'type'=>"datetime", 'options'=>array('notnull'=>false,'default'=>null));
			$this->insertColumnsAndKeys($this->getDomainTableName(), array($cc), array());
			if(!$this->hasRow($this->getDatacolumnsTableName(), array('name', 'tablename'), array('websites_checked_at', self::DOMAIN_TABLE))) {
				$label = 'Websites Checked At';
				$datacolumn = array('name'=>$cc['name'],'tablename'=>self::DOMAIN_TABLE,'server'=>'*','label'=>$label,'editable'=>0,'gridview'=>1,'width'=>160,'fieldtype'=>'datetime','custom'=>0);
				if($this->insertIntoTable($this->getDatacolumnsTableName(), $datacolumn) !== false) {
					$added++;
					\CodePunch\DB\Audit::add($this, \CodePunch\DB\Audit::REPAIR_DOMAIN_COLUMN, "Added websites_checked_at", "");
				}
			}
		}
		
		// May 4 2022
		// Rename the Label for subdomain 'serial' column to 'SSL Serial'
		$this->updateTable($this->getDatacolumnsTableName(), array('label'=>'SSL Serial'), "name=? AND tablename=? AND server=?", array('serial', 'subdomains', '*'));
		
		// Fill RDAP table if required
		$rdapcount = $this->getRowCount($this->getTLDsTableName());
		if($rdapcount < 1000)
			\CodePunch\LU\RDAP::fill($this);
		
		$this->updateTable($this->getSettingsTableName(), array('svalue'=>$this_build), "name=? AND server=?", array('last_dbfixed_build', '*'));
		return $added;
	}
	
	###############################################################################
	
	public function fixSubdomainDuplicates() {
		// Make sure that subdomain table doesn't end up having multiple entries
		// that share sid,auto_added,subdomain,record_type and record_value
		
		// First clean up the subdomain table by removing duplicates
		$sdtablename = $this->getSubdomainTableName();
		$sql  = "DELETE a FROM $sdtablename a INNER JOIN $sdtablename b";
		$sql .= " ON a.sid = b.sid AND a.auto_added = b.auto_added AND a.subdomain = b.subdomain AND a.record_type = b.record_type AND a.record_value = b.record_value";
		$sql .= " WHERE a.hid > b.hid";
		
		try {
			$stmt = $this->connection->prepare($sql);
			$stmt->execute();
		}
		catch(\Exception $e) {
			$logger = new \CodePunch\Base\CPLogger();
			$logger->error($e->getMessage());
			return false;
		}
		
		
		// add a unique index with the columns.
		// THis will not work because of the limit on index size (3072)
		/*
		$ucolumns = implode("|",['sid','auto_added','subdomain','record_type','record_value']);
		$indexname = "subdomain_nodups_idx";
		if($this->createSQLIndex($sdtablename, "unique", $ucolumns, $indexname) === true) {
			UTIL::debug_cli_print("Index created on $sdtablename\n");
		}
		*/
	}

	###############################################################################

	public static function getDefaultEditableSubdomainColumnNames() {
		return array('ssl_valid_from','ssl_valid_to','ssl_issued_to','ssl_issued_by','subject_key_id','subject_alt_name','subject_alt_name_text','notes_a','notes_b','notes_c','notes_d','signature_type','serial');
	}

	###############################################################################
	// Will find the minimum days to any expiry (registry/registrar/ssl)
	
	public function findDaysToExpiry($row)
	{
		$far_future = strtotime("3000-01-31");
		$expiry_date = $far_future; 
		
		$registry_expiry = UTIL::find_first_in_array($row, array('registry_expiry', 'd.registry_expiry'));
		$registrar_expiry = UTIL::find_first_in_array($row, array('registrar_expiry', 'd.registrar_expiry'));
		$ssl_expiry = UTIL::find_first_in_array($row, array('ssl_valid_to', 's.ssl_valid_to', 's_ssl_valid_to'));
		
		if(UTIL::is_a_date($registry_expiry)) 
			$expiry_date = min($expiry_date, strtotime($registry_expiry));
		if(UTIL::is_a_date($registrar_expiry)) 
			$expiry_date = min($expiry_date, strtotime($registrar_expiry));
		if(UTIL::is_a_date($ssl_expiry)) 
			$expiry_date = min($expiry_date, strtotime($ssl_expiry));
		if($expiry_date != $far_future) {
			$td = UTIL::get_date_difference($expiry_date);
			return sprintf("%02d", $td);
		}
		return null;
	}

	###############################################################################

	public static function extractDateString($datestr, $onlydate=false)
	{
		if($datestr != "" && (strpos($datestr, ":") === false || $onlydate === true)) {
			$wp = new \CodePunch\LU\WhoisParser(NULL,"","");
			return $wp->getDateFromString($datestr);
		}

		if($datestr !== false && $datestr != null) {
			$stamp = strtotime($datestr);
			if(is_numeric($stamp)) {
				$month = date('m', $stamp);
				$day   = date('d', $stamp);
				$year  = date('Y', $stamp);
				if(checkdate($month, $day, $year)) {
					if(strpos($datestr, ":") !== false && $onlydate === false)
						return date("Y-m-d H:i:s", $stamp);
					else
						return date("Y-m-d", $stamp);
				}
			}
		}
		return null;
	}

	###############################################################################

	public function formatDate($key, &$value)
	{
		$cia = $this->getReportColumnInfo($key);
		if(isset($cia['fieldtype'])) {
			$ft = $cia['fieldtype'];
			if($ft == "date" || $ft == "datetime")
				$value = self::extractDateString($value);
		}
	}

	###############################################################################

	public function formatDates(&$data_array)
	{
		if(is_array($data_array)) {
			foreach($data_array as $key => $val) {
				$cia = $this->getReportColumnInfo($key);
				if(isset($cia['fieldtype'])) {
					$ft = $cia['fieldtype'];
					if($ft == "date" || $ft == "datetime")
						$data_array[$key] = self::extractDateString($val);
				}
			}
		}
	}

	###############################################################################

	public function getIndexData($table="")
	{
		$idxdata = array();
		$schemaManager = $this->connection->getSchemaManager();
		$tables = $this->getAllTableNames();
		foreach ($tables as $tname) {
			if($table != "" && strcasecmp($tname, $table))
				continue;
			$idxdata[$tname] = array();
			$indexes = $schemaManager->listTableIndexes($tname);
			foreach ($indexes as $index) {
				$idxinfo = array();
				$idxinfo['name'] = $index->getName();
				$idxinfo['unique'] = $index->isUnique();
				$idxinfo['primary'] = $index->isPrimary();
				$idxinfo['simple'] = $index->isSimpleIndex();
				$idxinfo['columns'] = $index->getColumns();
				$idxdata[$tname][] =  $idxinfo;
			}
		}
		return $idxdata;
	}

	###############################################################################

	public function cleanupSQLIndexForTable($tabinfo, $ignorecustomdomaincolumns=false, $simulate=false)
	{
		$schemaManager = $this->connection->getSchemaManager();
		$tablename = $this->tableprefix . $tabinfo['name'];
		if(isset($tabinfo['column'])) {
			$initdata = isset($tabinfo['init']) ? $tabinfo['init'] : "";
			$keys = array();
			if(isset($tabinfo['keys']))
				$keys = $tabinfo['keys'];

			// Delete all extraneous indexes
			$indexes = $schemaManager->listTableIndexes($tablename);
			foreach ($indexes as $index) {
				$idxcolstr = implode("|", $index->getColumns());
				$match = false;
				$indextype = ($index->isSimpleIndex()) ? "index" : ($index->isPrimary() ? "primary" : "unique");
				foreach($keys as $type=>$cols) {
					if(!is_array($cols))
						$cols = array($cols);
					foreach($cols as $cname) {
						if(!strcasecmp($cname, $idxcolstr) && $idxcolstr != "" && !strcasecmp($type, $indextype)) {
							$match = true;
						}
					}
				}

				$idxcolstr = implode(", ", $index->getColumns());
				$idxtypename = ($indextype == "index") ? "simple" : $indextype;
				$extra = "";

				if(!$match) {
					if(!strcasecmp($tablename, $this->getDomainTableName())) {
						$customcolumns = $this->getCustomDomainColumnNames();
						$idcolumns = $index->getColumns();
						if(count($idcolumns) == 1 && UTIL::in_array_casei($idcolumns[0], $customcolumns)) {
							if($ignorecustomdomaincolumns) {
								UTIL::debug_cli_print("Ignoring $idxtypename index " . $index->getName() . "($idxcolstr) from $tablename because it is a custom column.\n");
								$match = true;
							}
							else
								$extra = " (*warning*: custom domain column)";
						}
					}
				}

				if(!$match) {
					UTIL::debug_cli_print("Dropping $idxtypename index " . $index->getName() . "($idxcolstr) from $tablename $extra\n");
					if(!$simulate)
						$this->deleteIndex($tablename, $index->getName());
				}
			}

			// Create missing indexes
			foreach($keys as $type=>$cols) {
				if(!is_array($cols))
					$cols = array($cols);
				foreach($cols as $cname) {
					$exists = 0;
					foreach($indexes as $index) {
						$indextype = ($index->isSimpleIndex()) ? "index" : ($index->isPrimary() ? "primary" : "unique");
						$idxcolumns = implode("|", $index->getColumns());
						if(!strcasecmp($idxcolumns, $cname) && !strcasecmp($type, $indextype)) {
							$exists = 1;
						}
					}
					if(!$exists) {
						$idxtypename = ($type == "index") ? "simple" : $type;
						UTIL::debug_cli_print("Creating $idxtypename index " . "($cname) for $tablename\n");
						$indexname = "";
						if(!$simulate) {
							if($this->createSQLIndex($tablename, $type, $cname, $indexname) === true)
								UTIL::debug_cli_print("$indexname created for $tablename.");
							else
								UTIL::debug_cli_print(\CodePunch\Base\RecentlyLoggedErrors::getLast());
						}
					}
				}
			}
		}
	}

	###############################################################################

	public function cleanupSQLIndexes($table="", $ignorecustomdomaincolumns=false, $simulate=false)
	{
		$tabledatafolder = UTIL::get_install_folder_path() . "lib/php/CodePunch/DB/Init/";
		$files = UTIL::find_all_matched_files($tabledatafolder, "*.xml");
		foreach($files as $file) {
			$tabledatafile = $tabledatafolder . $file;
			$table_data = array();
			$dbdata = file_get_contents($tabledatafile);
			if(!function_exists("simplexml_load_string"))
				throw new Exception(TEXT::get("config_missing_php_simplexml"));
			$xml = simplexml_load_string($dbdata);
			if(isset($xml->table))
			{
				$t = json_decode(json_encode($xml), true);
				if(isset($t['table'])) {
					$tables = $t['table'];
					if(!isset($tables[0]))
						$tables = array($tables);
					foreach($tables as $tab) {
						$tname = $this->tableprefix . $tab['name'];
						if($table != "" && strcasecmp($tname, $table))
							continue;
						$this->cleanupSQLIndexForTable($tab, $ignorecustomdomaincolumns, $simulate);
					}
				}
			}
		}
	}
	
	###############################################################################
	# Check the registrar domain data and construct the 'status' column by
	# combining the current registry and the new registrar status columns.
	# check if NS data is completely different for registry and registrar, if
	# different ignore the registrar data and set an extra 'nameserverdiscrepancy'
	# status.
	#
	
	public function analyseRegistrarDomainData($sid, $ddata) {
		// Keep old status entries too
		$oldstatus = false;
		$olddata = $this->getFromTable("status,ns1,ns2,ns3,ns4,ns5,ns6,ns7,ns8", $this->getDomainTableName(), "sid=?", array($sid));
		if($olddata !== false && isset($olddata[0])) {
			$olddata = $this->fetchRow($olddata, 0);
			$oldstatus = $olddata['status'];
			unset($olddata['status']);
			foreach($olddata as $key=>$value) {
				if(is_null($value) || $value == '')
					unset($olddata[$key]);
			}
			$oldns = array();
			$newns = array();
			$nskeys = explode(",", "ns1,ns2,ns3,ns4,ns5,ns6,ns7,ns8");
			foreach($nskeys as $nsk) {
				if(isset($ddata[$nsk]) && !is_null($ddata[$nsk]) && $ddata[$nsk] != '')
					$oldns[] = strtolower($ddata[$nsk]);
			}
			foreach($olddata as $ns => $nsv) {
				$newns[] = strtolower($nsv);
			}
			$added = array_diff($oldns, $newns);
			$deleted = array_diff($newns, $oldns);
			if(count($added) && count($deleted)) {
				if(!isset($ddata['status']))
					$ddata['status'] = 'nameserverdiscrepancy';
				else
					$ddata['status'] = 'nameserverdiscrepancy,' . $ddata['status'];
				// Remove new name servers and keep the registry name servers
				foreach($nskeys as $nsk) {
					unset($ddata[$nsk]);
				}
			}
		}
		if($oldstatus !== false && $oldstatus != "") {
			$os = array_map("trim", explode(",", $oldstatus));
			$os = array_map(function($string) { return str_replace(" ", "", $string); }, $os);
			if(count($os) && isset($ddata['status'])) {
				$ns = array_map("trim", explode(",", $ddata['status']));
				$ns = array_map(function($string) { return str_replace(" ", "", $string); }, $ns);
				foreach($os as $o) {
					if(!UTIL::in_array_casei($o, $ns))
						array_unshift($ns, $o);
				}
				$ddata['status'] = implode(", ", $ns);
			}
		}
		return $ddata;
	}
	
	public function addSSLCertificate($auth, $parsedssl, $cert, $domain, $live_at) {
		$cm = $auth->getProClass('SSL');
		if($cm)
			return $cm->addSSLCertificate($parsedssl, $cert, $domain, $live_at);
		throw new Exception(TEXT::get("require_pro_edition"));
	}
	
	# Import SSL certificate data from subdomain table to sslcerts table
	public function importSSLCertsFromSubdomains($auth, $deleteold=false) {
		$cm = $auth->getProClass('SSL');
		if($cm)
			return $cm->importSSLCertsFromSubdomains($deleteold);
		throw new Exception(TEXT::get("require_pro_edition"));
	}
	
	# Import SSL certificate data from raw data.
	public function importSSCertificate($auth, $certdata, $domain, $live_at) {
		$cm = $auth->getProClass('SSL');
		if($cm) {
			$parsedssl = openssl_x509_parse($certdata);
			return $cm->addSSLCertificate($parsedssl, $certdata, $domain, $live_at);
		}
		throw new Exception(TEXT::get("require_pro_edition"));
	}
}

###############################################################################
