<?php

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

namespace CodePunch\LU\Registrars;

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

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

class SEDv3Import extends RegistrarAPI {
	
	private $db_config_database = null;
	private $db_config_username = null;
	private $db_config_password = null;
	private $db_config_host     = null;
	private $db_table_prefix    = null;
	
	private $connection			= null;
	
	private $oldcategories		= null;
	private $newcategories		= null;
	
	const  AUDIT_SOURCE_STRING	= "Imported from v3 DB";
	
	###########################################################################
	
	public function __construct($cm, $dbname, $username, $passwd, $hostname, $prefix)
	{ 
		parent::__construct($cm);
		$this->db_config_database = $dbname;
		$this->db_config_username = $username;
		$this->db_config_password = $passwd;
		$this->db_config_host = $hostname;
		$this->db_table_prefix = $prefix;
	}
	
	###########################################################################
	
	public function supported() {
		return array('domainlist');
	}
	
	###########################################################################
	
	public static function getKeyNames()
	{
		return array('Database Name', 'User Name', 'Password', 'Hostname', 'Table Prefix');
	}
	
	###########################################################################
	
	private function importCategories()
	{
		$logger = new \CodePunch\Base\CPLogger();
		$cattable = $this->db_table_prefix . "catlist";
		$sql = "SELECT CategoryID,CategoryName from $cattable";
		$stmt = $this->connection->prepare($sql); 
		$stmt->execute();
		$result = $stmt->setFetchMode(\PDO::FETCH_ASSOC); 
		$rows = $stmt->fetchAll();
		$missingcats = array();
		if($rows !== false && is_array($rows)) {
			$auth = $this->getConnectionManager()->getAuthentication();
			$db = $auth->getDatabase();
			$ourcats = $db->getFromTable("name", $db->getCategoryTableName());
			$existingcats = array_values(UTIL::array_flatten($ourcats));
			$this->oldcategories = array();
			foreach($rows as $row) {
				$this->oldcategories[$row['CategoryID']] = $row['CategoryName'];
				if(strcasecmp($row['CategoryName'], "All Domains")) {
					if(!UTIL::in_array_casei($row['CategoryName'], $existingcats))
						$missingcats[] = $row['CategoryName'];
				}
			}
			if(count($missingcats) && UTIL::is_in_debug_mode()) {
				$logger->debug("Found new categories: " . implode(", ", $missingcats));
			}
			$this->newcategories = array();
			foreach($missingcats as $cat) {
				if($db->insertIntoTable($db->getCategoryTableName(), array('name'=>$cat))) {
					AUDIT::add($db, \CodePunch\DB\Audit::ADD_CATEGORY, "Added $cat", self::AUDIT_SOURCE_STRING);
				}
			}
			$missingcats = $db->getFromTable("cid,name", $db->getCategoryTableName());
			foreach($missingcats as $catinfo) {
				$this->newcategories[$catinfo['name']] = $catinfo['cid'];
			}
		}
	}
	
	###########################################################################
	
	private function importCustomColumns()
	{
		$logger = new \CodePunch\Base\CPLogger();
		$auth = $this->getConnectionManager()->getAuthentication();
		$db = $auth->getDatabase();
			
		$table = $this->db_table_prefix . "customfields";
		$sql = "SELECT name,ftype,fdefault from $table";
		$stmt = $this->connection->prepare($sql); 
		$stmt->execute();
		$result = $stmt->setFetchMode(\PDO::FETCH_ASSOC); 
		$rows = $stmt->fetchAll();

		foreach($rows as $row) {
			$fwidth = "";
			$fname = $row['name'];
			$flabel = ucwords(str_replace("_", " ", $fname));
			$fname = strtolower(UTIL::strip_punctuation($fname, true));
			$ftype = strtolower($row['ftype']);
			if(UTIL::starts_with($ftype, "varchar")) {
				$fwidth = trim(str_ireplace("varchar", "", $ftype), " ()");
				$ftype = "string";
			}
			else if(UTIL::starts_with($ftype, "bigint")) {
				$fwidth = trim(str_ireplace("bigint", "", $ftype), " ()");
				$ftype = "integer";
			}
			$fdefault = "empty";
			$findex = "";
			$dcolumns = $db->getDetailsOfAllDomainColumns();
			if(isset($dcolumns[$fname]['custom'])) {
				if($dcolumns[$fname]['custom'] == 0) {
					UTIL::debug_cli_print('A default column $fname exists');
					continue;
				}
			}
			if($db->connection->getDatabasePlatform()->getReservedKeywordsList()->isKeyword($fname)) {
				UTIL::debug_cli_print("$fname is a reserved keyword");
				continue;
			}
			$erows = $db->getFromTable("id", $db->getDatacolumnsTableName(), "name=? AND tablename=?", array($fname, \CodePunch\DB\DomainDB::DOMAIN_TABLE));
			if($erows !== false && isset($erows[0])) {
				//UTIL::debug_cli_print("$fname already exists");
			}
			else {
				// Create Domain Field
				$keys = array();
				$columninfo = array('name'=>$fname, 'type'=>$ftype, 'options'=>array());
				if($fdefault != "empty") {
					$columninfo['options'] = array('default'=>$fdefault);
					$columninfo['options']['notnull'] = true;
				}
				else
					$columninfo['options']['notnull'] = false;
				if($ftype == "string")
					$columninfo['options']['length'] = $fwidth;
				$istatus = $db->insertColumnsAndKeys($db->getDomainTableName(),  array($columninfo), $keys);
				if($istatus) {
					$gridview = ($ftype == "text") ? 0 : 1;
					$datacolumn = array('name'=>$fname,'tablename'=>\CodePunch\DB\DomainDB::DOMAIN_TABLE,'server'=>'*','label'=>$flabel,'editable'=>1,'gridview'=>$gridview,'width'=>100,'fieldtype'=>$ftype,'custom'=>1);
					if($db->insertIntoTable($db->getDatacolumnsTableName(), $datacolumn) === false) {
						UTIL::debug_cli_print("Unable to insert column $fname info into data columns table"); 
						$logger->error("Unable to insert column $fname info into data columns table");
					}
					AUDIT::add($db, \CodePunch\DB\Audit::ADD_CUSTOM_COLUMN, "Added $fname", self::AUDIT_SOURCE_STRING);
				}
				else {
					UTIL::debug_cli_print(TEXT::get('db_custom_col_add_error'));
					$logger->error(TEXT::get('db_custom_col_add_error'));
				}
			}
		}
	}
	
	###########################################################################
	
	private function xlate_domain_row(&$row)
	{
		$xlate = array(
		'lookedup' => 'primary_whois_checked_at',
		'ping_time' => 'pingtime',
		'redirect_url' => 'redirect_urls',
		'ip_checked_at' => 'rootdns_checked_at',
		'dns_checked_at' => 'subdomains_checked_at',
		'whois_server' => 'primary_whois_server'
		);
		$keys = array_keys($xlate);
		foreach($keys as $k){
			if(isset($row[$k])) {
				$row[$xlate[$k]] = $row[$k];
			}
			unset($row[$k]);
		}
		unset($row['sid']);
		
		# May be avoid this.
		/*
		if(isset($row['registrar_whois'] && isset($row['primary_whois_checked_at'])) {
			if(strlen($row['registrar_whois']) > 10 &&  UTIL::is_a_date($row['primary_whois_checked_at']))
				$row['secondary_whois_checked_at'] = $row['primary_whois_checked_at'];
		}
		*/
		
		return $row;
	}
	
	###########################################################################
	
	private function importDomains(callable $callback=null)
	{
		$logger = new \CodePunch\Base\CPLogger();
		$auth = $this->getConnectionManager()->getAuthentication();
		$db = $auth->getDatabase();
		
		$table = $this->db_table_prefix . "domains";
		$sql = "SELECT * from $table";
		$stmt = $this->connection->prepare($sql); 
		$stmt->execute();
		$result = $stmt->setFetchMode(\PDO::FETCH_ASSOC); 
		$rows = $stmt->fetchAll();

		$auditinfo = array();
		$added_count = 0;
		$domain_count = 0;
		$domaininfo = $db->getDetailsOfAllDomainColumns();
		$domaincolumns = array();
		foreach($domaininfo as $k=>$c) {
			$domaincolumns[] = $k;
		}
		foreach($rows as $row) {
			$osid = intval($row['sid']);
			$domain = $row['domain'];
			$row = $this->xlate_domain_row($row);
			$validcolumns = array();
			foreach($row as $column=>$value) {
				if(UTIL::in_array_casei($column, $domaincolumns)) {
					$validcolumns[] = $column;
				}
				else {
					//UTIL::debug_cli_print("Ignoring $column");
				}
			}
			
			// Find Categories for domain in v3 DB
			$cattable = $this->db_table_prefix . "catconn";
			$sql = "SELECT CategoryID from $cattable WHERE sid=$osid";
			$stmt = $this->connection->prepare($sql); 
			$stmt->execute();
			$result = $stmt->setFetchMode(\PDO::FETCH_ASSOC); 
			$ocids = $stmt->fetchAll();
			$oldcids = array();
			$cids = array();
			foreach($ocids as $ocid) {
				$oldcids[] = $ocid['CategoryID'];
				$catname = $this->oldcategories[$ocid['CategoryID']];
				$cids[] = $this->newcategories[$catname];
			}
			
			$status = $db->addDomain($domain, "", $cids);
			$sid = $db->getDomainID($domain);
			if($sid >= 0 && $sid !== false) {
				$db->updateDomainTable($row);
				if($status['added']) {
					UTIL::debug_cli_print("Added $domain");
					if(count($auditinfo) < 25)
						$auditinfo[] = $domain;
					$added_count++;
				}
			}
			
			// Find subdomains and import those too.
			$subdomains = array();
			$sdtable = $this->db_table_prefix . "subdomains";
			$sql = "SELECT subdomain from $sdtable WHERE (auto_added IS NULL OR auto_added = 0) AND sid=$osid";
			$stmt = $this->connection->prepare($sql); 
			$stmt->execute();
			$result = $stmt->setFetchMode(\PDO::FETCH_ASSOC); 
			$sds = $stmt->fetchAll();
			foreach($sds as $sd) {
				$subdomain = trim($sd['subdomain']);
				if($db->addSubdomain($sid, $subdomain)) {
					UTIL::debug_cli_print("Added $subdomain.$domain");
				}
			}
			
			// Don't import more than 1000 domains if not in CLI
			if(!UTIL::is_cli() && domain_count >= 1000)
				break;
			
			$domain_count++;
		}
		$formatted = UTIL::implode_limited(",", $auditinfo, 20);
		AUDIT::add($db, AUDIT::ADD_DOMAINS, "$added_count domains imported from version 3 Db", $formatted);
		$db->setCategoryCounts();
	}

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

	public function isWorking()
	{
		try {
			if($this->db_config_host != "" && $this->db_config_database != "") {
				$connstr = sprintf("mysql:host=%s;dbname=%s", $this->db_config_host, $this->db_config_database);
				$conn = new \PDO($connstr, $this->db_config_username, $this->db_config_password, array(\PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
				return true;
			}
			else
				return "Missing connection parameters. Please provide all the details.";
		}
		catch(PDOException $e) {
			return "Error: " . $e->getMessage();
		}
		return "Unable to connect";
	}

	###########################################################################
	
	public function domainlist(callable $callback=null)
	{
		// Open Database
		try {
			$connstr = sprintf("mysql:host=%s;dbname=%s", $this->db_config_host, $this->db_config_database);
			$this->connection = new \PDO($connstr, $this->db_config_username, $this->db_config_password, array(\PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
			$this->connection->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
			
			$this->importCategories();
			$this->importCustomColumns();
			$this->importDomains($callback);
			
			$this->connection = null;
		}
		catch(PDOException $e) {
			throw new Exception("Database Error: " . $e->getMessage());
		}
	}
}

###############################################################################
	
?>