<?php

namespace ModulesGarden\HostingQuotaNotifications\App\Libs\Database;

use \ModulesGarden\HostingQuotaNotifications\App\Helper\Tables;
use \Illuminate\Database\Capsule\Manager as DB;
use WHMCS\Database\Capsule as Capsule;

/**
 * Description of DatabaseClear
 *
 * @author Artur Pilch <artur.pi@modulesgarden.com>
 */
class DatabaseClear
{
    protected $dbViews;

    public function __construct()
    {
        $this->dbViews = new DatabaseViews();

    }

    public function clear()
    {
        $this->saveBillingCycles();

        $this->dbViews->createHostingInvoiceDates();
        $this->removeOldUsage();
        $this->removeOldNotifications();
        $this->removeOldBillingCycles();
        //$this->dbViews->dropView(Tables::tableViewsNames(Tables::HOSTING_DATES_VIEW));

        $this->addNewUsageStatisitcs();


    }

    private function saveBillingCycles()
    {
        $hostingDueDateTable = Tables::tableNames('hostingDueDates');
        $hostingTable        = Tables::tableNames('hosting');

        DB::statement("INSERT IGNORE INTO {$hostingDueDateTable}  (`hid`, `duedate`)
                        SELECT {$hostingTable}.id, {$hostingTable}.nextduedate 
                        FROM {$hostingTable}");

    }

    private function removeOldUsage($resetDay = '1')
    {
        $pdo = Capsule::connection()->getPdo();
        $pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, true);
        
        $hostingDateView = Tables::tableViewsNames(Tables::HOSTING_DATES_VIEW);

        $hostingDueDateTable = Tables::tableNames('hostingDueDates');
        $hostingTable        = Tables::tableNames('hosting');
        $usageTable          = Tables::tableNames('usage');

        $sth = $pdo->prepare("DELETE {$usageTable}.* FROM {$usageTable}
                    INNER JOIN {$hostingDueDateTable} ON {$hostingDueDateTable}.hid = {$usageTable}.hid
                    INNER JOIN {$hostingTable} ON {$hostingTable}.id = {$hostingDueDateTable}.hid
                    LEFT JOIN {$hostingDateView} ON {$hostingTable}.id = {$hostingDateView}.id
                    WHERE
                    (
                        (
                           {$hostingDueDateTable}.duedate <= {$hostingDateView}.date
                            AND {$hostingDueDateTable}.duedate <> '0000-00-00'
                            AND {$hostingTable}.billingcycle <> 'One Time'
                        )
                        OR
                        (
                            (
                                {$hostingDueDateTable}.duedate = '0000-00-00'
                                OR {$hostingTable}.billingcycle = 'One Time'
                            )
                            AND DAYOFMONTH(CURDATE()) = ?
                        )
                    )
                    OR
                    (
                      {$hostingTable}.domainstatus = 'Terminated'
                      AND DATE_ADD({$hostingTable}.termination_date, INTERVAL 14 DAY) < CURDATE()
                    )
                    ");
        $sth->execute([$resetDay]); 
    }

    private function removeOldNotifications($resetDay = '1')
    {
        $pdo = Capsule::connection()->getPdo();
        $pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, true);
        
        $hostingDateView = Tables::tableViewsNames(Tables::HOSTING_DATES_VIEW);

        $hostingDueDateTable = Tables::tableNames('hostingDueDates');
        $notificationTable   = Tables::tableNames('notifications');
        $hostingTable        = Tables::tableNames('hosting');

        $sth = $pdo->prepare("DELETE {$notificationTable}.* FROM {$notificationTable}
                    INNER JOIN {$hostingDueDateTable} ON {$hostingDueDateTable}.hid = {$notificationTable}.hostingid
                    INNER JOIN {$hostingTable} ON {$hostingTable}.id = {$hostingDueDateTable}.hid
                    LEFT JOIN {$hostingDateView} ON {$hostingTable}.id = {$hostingDateView}.id
                    WHERE
                    (
                        (
                          {$hostingDueDateTable}.duedate <= {$hostingDateView}.date
                            AND {$hostingDueDateTable}.duedate <> '0000-00-00' 
                            AND {$hostingTable}.billingcycle <> 'One Time'
                        )
                        OR  
                        (
                            ({$hostingDueDateTable}.duedate = '0000-00-00' 
                                OR {$hostingTable}.billingcycle = 'One Time'
                            ) 
                            AND DAYOFMONTH(CURDATE()) = ? 
                        )
                    )
                    OR 
                    (
                      {$hostingTable}.domainstatus = 'Terminated'
                      AND DATE_ADD({$hostingTable}.termination_date, INTERVAL 14 DAY) < CURDATE()
                    )
                ");
        $sth->execute([$resetDay]); 
    }

    private function removeOldBillingCycles($resetDay = '1')
    {
        $pdo = Capsule::connection()->getPdo();
        $pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, true);
        
        $hostingDateView = Tables::tableViewsNames(Tables::HOSTING_DATES_VIEW);

        $hostingDueDateTable = Tables::tableNames('hostingDueDates');
        $hostingTable        = Tables::tableNames('hosting');

        $sth = $pdo->prepare("DELETE {$hostingDueDateTable}.* FROM {$hostingDueDateTable}
                    INNER JOIN {$hostingTable} ON {$hostingTable}.id = {$hostingDueDateTable}.hid
                    LEFT JOIN {$hostingDateView} ON {$hostingTable}.id = {$hostingDateView}.id
                    WHERE
                    (
                        (
                            {$hostingDueDateTable}.duedate <= {$hostingDateView}.date
                            AND {$hostingDueDateTable}.duedate <> '0000-00-00' 
                            AND {$hostingTable}.billingcycle <> 'One Time'
                        ) 
                        OR 
                        (
                            ({$hostingDueDateTable}.duedate = '0000-00-00'
                                OR {$hostingTable}.billingcycle = 'One Time'
                            )
                            AND DAYOFMONTH(CURDATE()) = ? 
                        )
                    )
                    OR 
                    (
                      {$hostingTable}.domainstatus = 'Terminated'
                      AND DATE_ADD({$hostingTable}.termination_date, INTERVAL 14 DAY) < CURDATE()
                    ) 
                    ");
        $sth->execute([$resetDay]); 
    }

    private function addNewUsageStatisitcs()
    {
        $hostingDueDateTable = Tables::tableNames('hostingDueDates');
        $usageTable          = Tables::tableNames('usage');
        $hostingTable        = Tables::tableNames('hosting');

        DB::statement("INSERT IGNORE INTO {$usageTable} (hid, bw, disk, date)
                        SELECT h.id, h.bwusage as bw, h.diskusage as disk, CURDATE() as date 
                        FROM {$hostingTable} h 
                        INNER JOIN {$hostingDueDateTable} 
                        ON h.id = {$hostingDueDateTable}.hid");

    }
}
