<?php

namespace ModulesGarden\HostingQuotaNotifications\App\Helper\SmartyHelper;

use \Illuminate\Database\Capsule\Manager as DB;
use ModulesGarden\HostingQuotaNotifications\Core\Helper;
use ModulesGarden\HostingQuotaNotifications\App\Helper\DBModelsHelper;
use ModulesGarden\HostingQuotaNotifications\Core\ModuleConstants;

/**
 * Description of Invoice
 *
 * @author Artur Pilch <artur.pi@modulesgarden.com>
 */
class Invoice
{
    private $data;
    private $output;

    public function getUserID()
    {
        return $this->data['userid'];
    }

    public function __construct($id, $langHelper = false)
    {
        //$this->langHelper = $langHelper;

        $model = DB::table('tblinvoices')
                ->join('tblpaymentgateways', 'tblpaymentgateways.gateway', '=', 'tblinvoices.paymentmethod')
                ->join('tblaccounts', 'tblaccounts.invoiceid', '=', 'tblinvoices.id')
                ->select(
                        "tblinvoices.*", "tblpaymentgateways.value as gateway", DB::raw('IFNULL(SUM(tblaccounts.amountin-tblaccounts.amountout),0)')
                )
                ->where('tblinvoices.id', $id)
                ->first();

        $this->data = (array) $model;

        if (!$this->data['invoicenum'])
        {
            $this->data['invoicenum'] = $this->data['id'];
        }

        $this->data['balance'] = sprintf("%01.2f", $this->data['total'] - $this->data['amountpaid']);
    }

    public function getInvoiceClientCustomFields()
    {
        $model = DB::table('tblcustomfields')
                ->join('tblcustomfieldsvalues', 'tblcustomfieldsvalues.fieldid', '=', 'tblcustomfields.id')
                ->select("tblcustomfields.id", "tblcustomfields.fieldname", "tblcustomfieldsvalues.value as value")
                ->where('tblcustomfieldsvalues.relid', $this->data['userid'])
                ->where('tblcustomfields.type', 'client')
                ->where('tblcustomfields.showinvoice', 'on')
                ->first();
        return (array) $model;
    }

    public function loadAdditionalData()
    {
        //$db = new MG_SMS_DBWorld();
        $model = DB::table('tblaccounts')
                ->select(DB::raw('(tblaccounts.amountin-tblaccounts.amountout) as amount', "transid"))
                ->where('invoiceid', $this->data['id'])
                ->orderBy('id', 'desc')
                ->first();

        $lastpayment = (array) $model;

        $this->data['lastpaymentamount']  = $lastpayment['amount'];
        $this->data['lastpaymenttransid'] = $lastpayment['transid'];


        $model = DB::table('tblinvoiceitems')
                ->join('tblhosting', 'tblhosting.id', '=', 'tblinvoiceitems.relid')
                ->select('tblhosting.subscriptionid')
                ->where('tblinvoiceitems.type', 'Hosting')
                ->where('tblinvoiceitems.invoiceid', $this->data['id'])
                ->where('tblhosting.subscriptionid', '!=', '')
                ->first();

        $this->data['subscrid'] = reset($model);

        $model = DB::table('tblinvoices')
                ->select(DB::raw('SUM(credit) as credit'), DB::raw('SUM(total) as total'))
                ->where('userid', $this->data['userid'])
                ->where('status', 'Unpaid')
                ->first();

        $clienttotals = (array) $model; //$db->raw("SELECT SUM(credit) AS credit, SUM(total) AS total FROM tblinvoices WHERE userid = ? AND status = 'Unpaid'",array($this->data['userid']))->first();

        $model = DB::table('tblaccounts')
                ->select(DB::raw('SUM(amountin-amountout) as summary'))
                ->whereIn('invoiceid', (array) DB::raw("SELECT id FROM tblinvoices WHERE userid = {$this->data['userid']} AND status='Unpaid'"))
                ->first();

        $alldueinvoicespayments = (array) $model;
        $alldueinvoicespayments = reset($alldueinvoicespayments);

        $model = DB::table('tblclients')
                ->select("state", "country")
                ->where('id', $this->data['id'])
                ->first();

        $this->data['clienttotaldue']        = $clienttotals['credit'] + $clienttotals['total'];
        $this->data['clientpreviousbalance'] = $clienttotals['total'] - $this->data['total'];
        $this->data['clientbalancedue']      = $clienttotals['total'] - $alldueinvoicespayments;

        $clientlocation = (array) $model; //$db->raw("SELECT state, country FROM tblclients WHERE id = ?", $this->data['id'])->first();
        //TODO Tax Helper

        $tax                    = TaxesHelper::getTax(1, $clientlocation['state'], $clientlocation['country']);
        $this->data['taxname']  = $tax['name'];
        $tax                    = TaxesHelper::getTax(2, $clientlocation['state'], $clientlocation['country']);
        $this->data['taxname2'] = $tax['name'];
    }

    public function getFormatedData()
    {
        global $currency;
        $this->output = $this->data;
        $array        = array("date", "duedate", "datepaid");
        foreach ($array as $v)
        {
            if (isset($this->output[$v]))
                $this->output[$v] = (substr($this->output[$v], 0, 10) != "0000-00-00" ? fromMySQLDate($this->output[$v], ($v == "datepaid" ? "1" : "0"), 1) : "");
        }

        $currency = getCurrency($this->data["userid"]);
        $array    = array("subtotal", "credit", "tax", "tax2", "total", "balance", "amountpaid", "clienttotaldue", "clientpreviousbalance", "clientbalancedue", "lastpaymentamount");
        foreach ($array as $v)
        {
            if (isset($this->output[$v]))
                $this->output[$v] = formatCurrency($this->output[$v]);
        }

        $this->output['taxrate']    = $this->output['taxrate'] . "%";
        $this->output['taxrate2']   = $this->output['taxrate2'] . "%";
        $this->output['statuslang'] = Helper\sl('lang')->T("invoices" . strtolower($this->data['status']));
        $this->output['pagetitle']  = Helper\sl('lang')->T("invoicenumber") . $this->data["invoicenum"];
        $this->output['payto']      = DBModelsHelper::getWHMCSConfigValue("InvoicePayTo");
        $this->output['notes']      = $this->data['notes'];

        return $this->output;
    }

    public function getMergeFields()
    {
        $lang     = Helper\sl('lang');
        $map      = self::getMergeFieldsMap();
        $this->loadAdditionalData();
        $formated = $this->getFormatedData();
        $fields   = array();

        foreach ($map as $k => $v)
            $fields[$k] = $formated[$v];

        $invoiceitems       = $this->getFormatedInvoiceItems();
        $invoicedescription = "";

        $invoicedescription .= "------------------------------------------------------<br>\r\n";
        $invoicedescription .= $lang->T('invoicessubtotal') . ": " . $formated['subtotal'] . "<br>\r\n";

        if ($formated['taxrate'] > 0)
        {
            $invoicedescription .= $formated['taxrate'] . "% " . $formated['taxname'] . ": " . $formated['tax'] . "<br>\r\n";
        }


        if ($formated['taxrate2'] > 0)
        {
            $invoicedescription .= $formated['taxrate2'] . "% " . $formated['taxname2'] . ": " . $formated['tax2'] . "<br>\r\n";
        }

        $invoicedescription .= $lang->T('invoicescredit') . ": " . $formated['credit'] . "<br>\r\n";
        $invoicedescription .= $lang->T('invoicestotal') . ": " . $formated['total'] . "";

        $systemurl                       = DBModelsHelper::getSystemURL();
        $fields['invoice_items']         = $invoiceitems;
        $fields['invoice_html_contents'] = $invoicedescription;
        $fields['invoice_link']          = $systemurl . "viewinvoice.php?id=" . $this->data['id']; //"<a href=\"" . $systemurl . "/viewinvoice.php?id=" . $this->data['id'] . "\">" . $systemurl . "/viewinvoice.php?id=" . $this->data['id'] . "</a>";
        $fields['invoice_payment_link']  = $this->getPaymentLink();
        return $fields;
    }

    public static function getMergeFieldsNames()
    {
        return array_keys(self::getMergeFieldsMap());
    }

    private static function getMergeFieldsMap()
    {
        return [
            'invoice_id'                   => 'id',
            'invoice_num'                  => 'invoicenum',
            'invoice_date_created'         => 'date',
            'invoice_date_due'             => 'duedate',
            'invoice_date_paid'            => 'datepaid',
            'invoice_items'                => '',
            'invoice_html_contents'        => '',
            'invoice_subtotal'             => 'subtotal',
            'invoice_tax'                  => 'tax',
            'invoice_tax_rate'             => 'taxrate',
            'invoice_tax2'                 => 'tax2',
            'invoice_tax_rate2'            => 'taxrate2',
            'invoice_credit'               => 'credit',
            'invoice_total'                => 'total',
            'invoice_amount_paid'          => 'amountpaid',
            'invoice_balance'              => 'balance',
            'invoice_last_payment_amount'  => 'lastpaymentamount',
            'invoice_last_payment_transid' => 'lastpaymenttransid',
            'invoice_payment_method'       => 'gateway',
            'invoice_payment_link'         => '',
            'invoice_subscription_id'      => 'subscrid',
            'invoice_status'               => 'statuslang',
            'invoice_link'                 => '',
            'invoice_previous_balance'     => 'clientpreviousbalance',
            'invoice_total_balance_due'    => 'clientbalancedue',
            'invoice_notes'                => 'notes',
            'invoice_all_due_total'        => 'clienttotaldue'
        ];
    }

    private function getFormatedInvoiceItems($decode = false)
    {
        //$db = new MG_SMS_DBWorld();
        $invoiceitems = array();



        if (DBModelsHelper::getWHMCSConfigValue("GroupSimilarLineItems"))
        {

            $items = DB::table('tblinvoiceitems')
                    ->select(DB::raw('COUNT(*) as qty', "id", "type", "relid", "description", "amount", "taxed"))
                    ->where('invoiceid', $this->data['id'])
                    ->groupBy('description', 'amount')
                    ->orderBy('id', 'asc')
                    ->first();
            $items = (array) $items;
        }
        else
        {

            $items = DB::table('tblinvoiceitems')
                    ->select(DB::raw('0 as qty', "id", "type", "relid", "description", "amount", "taxed"))
                    ->where('invoiceid', $this->data['id'])
                    ->orderBy('id', 'asc')
                    ->first();
            $items = (array) $items;
        }

        foreach ($items as $item)
        {
            if ($item['qty'] > 1)
            {
                $item['description'] = $item['qty'] . " x " . $item['description'] . " @ " . $item['amount'] . Helper\sl('lang')->T("invoiceqtyeach");
                $item['amount']      *= $item['qty'];
            }

            if ($decode)
                $item['description'] = htmlspecialchars(html_entity_decode($item['description'], ENT_QUOTES));
            else
                $item['description'] = $item['description'];

            $item['rawamount'] = $item['amount'];
            $item['amount']    = formatCurrency($item['amount']);
            $invoiceitems[]    = $item;
        }

        return $invoiceitems;
    }

    public function getPaymentLink()
    {

        if (!function_exists("getGatewayVariables"))
        {
            require_once ModuleConstants::getFullPathWhmcs() . "/includes/gatewayfunctions.php";
        }

        $params        = getGatewayVariables($this->data["paymentmethod"], $this->data["id"], $this->data["balance"]);
        $paymentbutton = (function_exists($this->data["paymentmethod"] . "_link") ? call_user_func($this->data["paymentmethod"] . "_link", $params) : "");
        return $paymentbutton;
    }
}
