<?php

namespace MGModule\DNSManager2\mgLibs\custom\manager;

use \MGModule\DNSManager2\mgLibs\lang;
use \MGModule\DNSManager2\mgLibs\MySQL\query;
use MGModule\DNSManager2\models\custom\globalsetting\GlobalSetting;
use \MGModule\DNSManager2\models\custom\package;
use \MGModule\DNSManager2\models\custom\package\setting\PackageSettingEnum;
use \MGModule\DNSManager2\models\custom\zone;
use \MGModule\DNSManager2\models\custom\zone\ZoneTypeEnum;
use \MGModule\DNSManager2\models\custom\package\item\PackageItemTypeEnum;
use \MGModule\DNSManager2\models\whmcs\service;
use \MGModule\DNSManager2\models\whmcs\clients;

class ClientHelper {
    private $clientid;

    public function __construct($clientid) {
        $this->clientid = $clientid;
    }

    public function canUserCreatesNewZonesIn($type, $relid)
    {
        $zone = new zone\Zone();
        $zone->type = $type;
        $zone->relid = $relid;
        $zone->clientid = $this->clientid;

        $item = $zone->getRelatedItem();

        if($item->packageItemType() === PackageItemTypeEnum::ADDON)
        {
            $hostingClient = $item->__call('client', array());
            $tempClientId = $hostingClient->id;
        }
        else
        {
            $tempClientId = $item->clientID();
        }

        if($tempClientId != $this->clientid)
        {
            return false;
        }

        if(!in_array($item->status(), array('ACTIVE','PENDING TRANSFER','PENDING REGISTRATION')))
            return false;

        $package = $zone->getPackage();
        if($package === false)
            return false;

        $count = zone\Repository::factory()->byClientID($this->clientid)->byType($type)->byRelID($relid)->count();

        return $package->getSettings(package\setting\PackageSettingEnum::OWN_ZONES_PER_ITEM)->value > $count;
    }

    public function isZoneOwnedByClient($zone) {
        return $zone->clientid == $this->clientid;
    }

    public function getIPsForService($serviceid) {
        return service\service::factory($serviceid)->getIPs();
    }

    public function getClientIPs() {
        return service\repository::factory()->byDomainStatus('Active')->byClientID($this->clientid)->getIPs();
    }

    public function getClientDomains() {
        $out = array();
        $res = query::query('SELECT domain FROM tblhosting WHERE userid = :userid AND domainstatus = "Active"', array('userid' => $this->clientid));
        while($domain = $res->fetchColumn()) {
            $out[] = $domain;
        }

        $res = query::query('SELECT domain FROM tbldomains WHERE userid = :userid AND status = "Active"', array('userid' => $this->clientid));
        while($domain = $res->fetchColumn()) {
            $out[] = $domain;
        }

        return array_unique(array_filter($out));
    }

    public function getGroups() {
        $hostings = query::query("SELECT CONCAT('#', tblhosting.id, ' ', tblproductgroups.name, ' - ', tblproducts.name) AS group_name,
                tblhosting.id AS product_id, tblproductgroups.name AS product_group_name, tblproducts.name AS product_name,
                zones.value AS zones_available,ips.value AS ips_available, blacklist.value AS blacklist,
                :type `type`, tblhosting.id AS relid,
                dns_manager2_zone.id AS zone_id, dns_manager2_zone.name AS zone_name, dns_manager2_zone.status AS zone_status,
       			dns_manager2_zone.is_locked as locked,
                tblhosting.domain AS domain
            FROM tblhosting
            INNER JOIN tblproducts ON tblproducts.id = tblhosting.packageid
            INNER JOIN tblproductgroups ON tblproductgroups.id = tblproducts.gid
            INNER JOIN dns_manager2_packageitem ON dns_manager2_packageitem.relid = tblproducts.id
                AND dns_manager2_packageitem.type = :type2
            INNER JOIN dns_manager2_packagesetting AS zones ON zones.packageid = dns_manager2_packageitem.packageid 
                AND zones.key = :setting
            LEFT JOIN dns_manager2_packagesetting AS ips ON ips.packageid = dns_manager2_packageitem.packageid 
                AND ips.key = :setting2
            LEFT JOIN dns_manager2_packagesetting AS blacklist ON blacklist.packageid = dns_manager2_packageitem.packageid 
                AND blacklist.key = :setting3
            LEFT JOIN dns_manager2_zone ON dns_manager2_zone.type = :type3
                AND dns_manager2_zone.relid = tblhosting.id AND dns_manager2_zone.clientid = :clientid
            WHERE tblhosting.domainstatus IN ('Active') 
                AND tblhosting.userid = :clientid2 ", array(
            'type' => ZoneTypeEnum::HOSTING,
            'type2' => ZoneTypeEnum::HOSTING,
            'type3' => ZoneTypeEnum::HOSTING,
            'clientid' => $this->clientid,
            'clientid2' => $this->clientid,
            'setting' => PackageSettingEnum::OWN_ZONES_PER_ITEM,
            'setting2' => PackageSettingEnum::ALLOWED_IPS,
            'setting3' => PackageSettingEnum::IP_BLACKLIST,
        ))->fetchAll();

        if(GlobalSetting::byKey('prevent_zone_delete_after_terminate') == 'on'){
            $zonesForTerminatedProducts = query::query("SELECT CONCAT('#', tblhosting.id, ' ', tblproductgroups.name, ' - ', tblproducts.name) AS group_name,
                tblhosting.id AS product_id, tblproductgroups.name AS product_group_name, tblproducts.name AS product_name,
                zones.value AS zones_available,ips.value AS ips_available, blacklist.value AS blacklist,
                :type `type`, tblhosting.id AS relid,
                dns_manager2_zone.id AS zone_id, dns_manager2_zone.name AS zone_name, dns_manager2_zone.status AS zone_status,
       			dns_manager2_zone.is_locked as locked,
                tblhosting.domain AS domain
            FROM tblhosting
            INNER JOIN tblproducts ON tblproducts.id = tblhosting.packageid
            INNER JOIN tblproductgroups ON tblproductgroups.id = tblproducts.gid
            INNER JOIN dns_manager2_packageitem ON dns_manager2_packageitem.relid = tblproducts.id
                AND dns_manager2_packageitem.type = :type2
            INNER JOIN dns_manager2_packagesetting AS zones ON zones.packageid = dns_manager2_packageitem.packageid 
                AND zones.key = :setting
            LEFT JOIN dns_manager2_packagesetting AS ips ON ips.packageid = dns_manager2_packageitem.packageid 
                AND ips.key = :setting2
            LEFT JOIN dns_manager2_packagesetting AS blacklist ON blacklist.packageid = dns_manager2_packageitem.packageid 
                AND blacklist.key = :setting3
            LEFT JOIN dns_manager2_zone ON dns_manager2_zone.type = :type3
                AND dns_manager2_zone.relid = tblhosting.id AND dns_manager2_zone.clientid = :clientid
            WHERE tblhosting.domainstatus IN ('Suspended', 'Terminated') 
                AND tblhosting.userid = :clientid2 AND dns_manager2_zone.relid = tblhosting.id", [
                'type'      => ZoneTypeEnum::HOSTING,
                'type2'     => ZoneTypeEnum::HOSTING,
                'type3'     => ZoneTypeEnum::HOSTING,
                'clientid'  => $this->clientid,
                'clientid2' => $this->clientid,
                'setting'   => PackageSettingEnum::OWN_ZONES_PER_ITEM,
                'setting2'  => PackageSettingEnum::ALLOWED_IPS,
                'setting3'  => PackageSettingEnum::IP_BLACKLIST,
            ])->fetchAll();
            $hostings = array_merge($hostings, $zonesForTerminatedProducts);
        }


        $domains = query::query("SELECT CONCAT('#', tbldomains.id, ' ', tbldomains.domain) AS group_name, 
                tbldomains.id AS product_id, '' AS product_group_name, tbldomains.domain AS product_name,
                zones.value AS zones_available, ips.value AS allowed_ips, blacklist.value AS blacklist,
                :type `type`, tbldomains.id AS relid,
                dns_manager2_zone.id AS zone_id, dns_manager2_zone.name AS zone_name, dns_manager2_zone.status AS zone_status,
                dns_manager2_zone.is_locked as locked,
                tbldomains.domain AS domain
            FROM tbldomains
            INNER JOIN dns_manager2_packageitem ON dns_manager2_packageitem.relid = (SELECT tbldomainpricing.id FROM tbldomainpricing WHERE tbldomainpricing.extension = SUBSTRING(tbldomains.domain, LOCATE('.', tbldomains.domain)) LIMIT 1)
                AND dns_manager2_packageitem.type = :type2
            INNER JOIN dns_manager2_packagesetting AS zones ON zones.packageid = dns_manager2_packageitem.packageid 
                AND zones.key = :setting
            LEFT JOIN dns_manager2_packagesetting AS ips ON ips.packageid = dns_manager2_packageitem.packageid 
                AND ips.key = :setting2
            LEFT JOIN dns_manager2_packagesetting AS blacklist ON blacklist.packageid = dns_manager2_packageitem.packageid 
                AND blacklist.key = :setting3
            LEFT JOIN dns_manager2_zone ON dns_manager2_zone.type = :type3
                AND dns_manager2_zone.relid = tbldomains.id AND dns_manager2_zone.clientid = :clientid
            WHERE tbldomains.status IN ('Active','Pending Transfer','Pending Registration')
                AND tbldomains.userid = :clientid2 ", array(
            'type' => ZoneTypeEnum::DOMAIN,
            'type2' => ZoneTypeEnum::DOMAIN,
            'type3' => ZoneTypeEnum::DOMAIN,
            'clientid' => $this->clientid,
            'clientid2' => $this->clientid,
            'setting' => PackageSettingEnum::OWN_ZONES_PER_ITEM,
            'setting2' => PackageSettingEnum::ALLOWED_IPS,
            'setting3' => PackageSettingEnum::IP_BLACKLIST,
        ))->fetchAll();
        //INNER JOIN dns_manager2_packageitem ON dns_manager2_packageitem.relid = SUBSTRING(tbldomains.domain, LOCATE('.', tbldomains.domain))

        $addons = query::query("SELECT CONCAT('#', tblhostingaddons.id, ' ', tbladdons.name) AS group_name, zones.value AS zones_available,ips.value AS ips_available,
                blacklist.value AS blacklist, tblhostingaddons.id AS product_id, '' AS product_group_name, tbladdons.name AS product_name,
                :type `type`, tblhostingaddons.id AS relid,
                dns_manager2_zone.id AS zone_id, dns_manager2_zone.name AS zone_name, dns_manager2_zone.status AS zone_status,
                dns_manager2_zone.is_locked as locked,
                tblhosting.domain AS domain
            FROM tblhostingaddons
            INNER JOIN tbladdons ON tbladdons.id = tblhostingaddons.addonid
            INNER JOIN tblhosting ON tblhosting.id = tblhostingaddons.hostingid
            INNER JOIN dns_manager2_packageitem ON dns_manager2_packageitem.relid = tbladdons.id
                AND dns_manager2_packageitem.type = :type2
            INNER JOIN dns_manager2_packagesetting AS zones ON zones.packageid = dns_manager2_packageitem.packageid 
                AND zones.key = :setting
            LEFT JOIN dns_manager2_packagesetting AS ips ON ips.packageid = dns_manager2_packageitem.packageid 
                AND ips.key = :setting2
            LEFT JOIN dns_manager2_packagesetting AS blacklist ON blacklist.packageid = dns_manager2_packageitem.packageid 
                AND blacklist.key = :setting3
            LEFT JOIN dns_manager2_zone ON dns_manager2_zone.type = :type3
                AND dns_manager2_zone.relid = tblhostingaddons.id AND dns_manager2_zone.clientid = :clientid
            WHERE tblhostingaddons.status IN ('Active')
                AND tblhosting.userid = :clientid2 ", array(
                    'type' => ZoneTypeEnum::ADDON, 
                    'type2' => ZoneTypeEnum::ADDON, 
                    'type3' => ZoneTypeEnum::ADDON, 
                    'clientid' => $this->clientid, 
                    'clientid2' => $this->clientid,
                    'setting' => PackageSettingEnum::OWN_ZONES_PER_ITEM,
                    'setting2' => PackageSettingEnum::ALLOWED_IPS,
                    'setting3' => PackageSettingEnum::IP_BLACKLIST,
        ))->fetchAll();
        $other = query::query("SELECT :othergroupname AS group_name, zones.value AS zones_available, ips.value AS ips_available, blacklist.value AS blacklist,
                0 `type`, 0 AS relid,
                dns_manager2_zone.id AS zone_id, dns_manager2_zone.name AS zone_name, dns_manager2_zone.status AS zone_status,
                dns_manager2_zone.is_locked as locked,
                '' AS domain
            FROM dns_manager2_packageitem
            INNER JOIN dns_manager2_packagesetting AS zones ON zones.packageid = dns_manager2_packageitem.packageid 
                AND zones.key = :setting
            LEFT JOIN dns_manager2_packagesetting AS ips ON ips.packageid = dns_manager2_packageitem.packageid 
                AND ips.key = :setting2
            LEFT JOIN dns_manager2_packagesetting AS blacklist ON blacklist.packageid = dns_manager2_packageitem.packageid 
                AND blacklist.key = :setting3
            LEFT JOIN dns_manager2_zone ON dns_manager2_zone.type = :type
                AND dns_manager2_zone.relid = 0 AND dns_manager2_zone.clientid = :clientid
            WHERE dns_manager2_packageitem.relid = 0
                AND dns_manager2_packageitem.type = 0", 
                array(
                    'othergroupname'=>lang::absoluteT('otherGroupName'),
                    'type' => ZoneTypeEnum::OTHER,
                    'clientid' => $this->clientid, 
                    'setting' => PackageSettingEnum::OWN_ZONES_PER_ITEM,
                    'setting2' => PackageSettingEnum::ALLOWED_IPS,
                    'setting3' => PackageSettingEnum::IP_BLACKLIST,
                ))->fetchAll();

        $groups = array(); //TODO: zrobić coś z tymi query i foreachmi; brzydkie!!!

        foreach($hostings as $hosting)
            $groups[lang::T('service') . ' ' . $hosting['group_name'] . (empty($hosting['domain'])?'':" ({$hosting['domain']})")][] = $hosting;

        foreach($domains as $domain)
            $groups[lang::T('domain') . ' ' . $domain['group_name'] . (empty($domain['domain'])?'':" ({$domain['domain']})")][] = $domain;

        foreach($addons as $addon)
            $groups[lang::T('addon') . ' ' . $addon['group_name'] . (empty($addon['domain'])?'':" ({$addon['domain']})")][] = $addon;

        foreach($other as $item)
            $groups[$item['group_name']][] = $item;

        return $groups;
    }
    public function getPackageAllowedIps($relid,$type){
        switch ( $type )
        {
            case 0:
                $type = 'dns_manager2_packageitem';
                break;
            case 1:
                $type = 'tbldomains';
                break;
            case 2:
                $type = 'tblhosting';
                break;
            case 3:
                $type = 'tblhostingaddons';
                break;
            default :
                $type = false;
                break;
        }
        if ( $type )
        {
            $sql    = "SELECT ips.value AS allowed_ips
				FROM $type
				INNER JOIN dns_manager2_packagesetting AS ips ON ips.packageid = ips.packageid AND ips.key = 'allowed_ips'
				WHERE $type.id = :relid;";
            $result = query::query($sql, ['relid' => $relid])->fetch();
        }
        else
        {
            $result = false;
        }

        return $result;
    }
    public function getPackageIpBlacklist($relid,$type){
        switch ( $type )
        {
            case 0:
                $type = 'dns_manager2_packageitem';
                break;
            case 1:
                $type = 'tbldomains';
                break;
            case 2:
                $type = 'tblhosting';
                break;
            case 3:
                $type = 'tblhostingaddons';
                break;
            default :
                $type = false;
                break;
        }

        if ( $type )
        {
            $sql    = "SELECT ips.value AS ip_blacklist
				FROM $type
				INNER JOIN dns_manager2_packagesetting AS ips ON ips.packageid = ips.packageid AND ips.key = 'ip_blacklist'
				WHERE $type.id = :relid;";
            $result = query::query($sql, ['relid' => $relid])->fetch();
        }
        else
        {
            $result = false;
        }

        return $result;
    }
    public static function getAllClients()
    {
        $query      = query::query("SELECT id,firstname,lastname FROM tblclients")->fetchAll();
        $clients    = array();

        foreach($query as $q)
        {
            $clients[] = [
                'id'    => $q['id'],
                'name'  => $q['firstname'] . ' ' . $q['lastname']
            ];
        }

        return $clients;
    }

    public static function getZones($clientId)
    {

        return query::query("SELECT * FROM dns_manager2_zone WHERE clientid = {$clientId}")->fetchAll();
    }

    public static function getServerStatusByZoneId($zoneId)
    {
        return query::query("SELECT dns_manager2_server.status FROM dns_manager2_server 
                                      INNER JOIN dns_manager2_zone ON dns_manager2_server.id = dns_manager2_zone.serverid
                                      WHERE dns_manager2_zone.id = ?" , [$zoneId]

        )->fetch();
    }

    public static function getClientLink($clientId)
    {
        $clientRecord = clients\client::factory($clientId);

        return $clientRecord->getProfileLink();
    }

}