<?php

namespace apexl\Io\modules\invoice\entities\operators;

use apexl\Io\operators\entityDatabaseOperator;

class invoiceOperator extends entityDatabaseOperator{

    public function __construct($table, $primaryKey = 'id')
    {
        parent::__construct($table, $primaryKey);
    }

    public function loadByProviderId($providerId) {
        $entity = $this->vault->select($this->dbTable)
            ->fields()
            ->where('provider_id', $providerId)
            ->execute()
            ->fetchAssoc();

        return ['updateEntityData' => true, 'data' => $entity];
    }

    public function loadByRemoteOrderId($remoteOrderId) {
        $entity = $this->vault->select($this->dbTable)
            ->fields()
            ->where('remote_order_id', $remoteOrderId)
            ->execute()
            ->fetchAssoc();

        return ['updateEntityData' => true, 'data' => $entity];
    }

    public function loadByPaymentId($paymentId) {

        $invoicePayment = $this->vault->select('invoice_payment')
            ->fields()
            ->where('payment_id', $paymentId)
            ->execute()
            ->fetchAssoc();
        if ($invoicePayment) {

            $entity = $this->vault->select($this->dbTable)
                ->fields()
                ->where('id', $invoicePayment['invoice_id'])
                ->execute()
                ->fetchAssoc();

            return ['updateEntityData' => true, 'data' => $entity];
        }

        return null;
    }

    public function getChargeable($limit, $userId=null)
    {
        $query = $this->vault
            ->select($this->dbTable)
            ->fields()
            ->where('paid', 0)
            ->where('abandoned', 0)
            ->where('on_hold', 0)
            ->where('billing_enabled', 1);
        if ($userId) {
            $query->where('user_id', $userId);
        }
        return $query->orderBy('last_payment_attempt', 'ASC')
            ->limit($limit)
            ->execute()
            ->fetchAll();
    }

    public function getRequiringProviderUpload($limit=1)
    {
        return $this->vault
            ->select($this->dbTable)
            ->fields()
            ->where('send_to_provider', 1)
            ->where('provider_id',NULL, "IS NULL")
            ->limit($limit)
            ->execute()
            ->fetchAll();
    }

    public function getOverdue($daysCutoff=3, $limit=1)
    {
        $date = new \DateTime('now');
        $date->sub(new \DateInterval('P'.$daysCutoff.'D'));

        return $this->vault
            ->select($this->dbTable)
            ->fields()
            ->where('paid', 0)
            ->where('abandoned', 0)
            ->where('invoice_date', $date->format('Y-m-d'), '<')
            ->orderBy('last_payment_attempt', 'ASC')
            ->limit($limit)
            ->execute()
            ->fetchAll();
    }

    public function getOverdueBySubscription()
    {
        $date = new \DateTime('now');
        $date->sub(new \DateInterval('P2D'));
        
        $results = $this->vault
            ->select($this->dbTable . ' i')
            ->fields(['i.id', 'iv.subscription_id'])
            ->join('invoice_subscription iv', 'i.id=iv.invoice_id', '','')
            ->where('paid', 0)
            ->where('abandoned', 0)
            ->where('invoice_date', $date->format('Y-m-d'), '<')
            ->execute()
            ->fetchAll();

        $subsOverdue = [];
        foreach ($results as $row) {
            $subsOverdue[$row->subscription_id] = $row;
        }

        return $subsOverdue;
    }

    public function getForSubscription($subscriptionId)
    {
        return $this->vault
            ->select($this->dbTable)
            ->fields($this->dbTable.'.*')
            ->leftjoin('invoice_subscription', $this->dbTable.'.id=invoice_subscription.invoice_id', '','')
            ->where('subscription_id', $subscriptionId)
            ->orderby('invoice_date', 'DESC')
            ->execute()
            ->fetchAll();

    }

    public function getForUser($userId)
    {
        return $this->vault
            ->select($this->dbTable)
            ->fields($this->dbTable.'.*')
            ->where('user_id', $userId)
            ->execute()
            ->fetchAll();

    }

    public function getSubscriptionInvoices($startDate, $endDate, $paid=1)
    {
        if (!in_array($paid, [0,1])) $paid = 0;

        return $this->vault
            ->select($this->dbTable)
            ->fields($this->dbTable.'.*')
            ->join('invoice_subscription', $this->dbTable.'.id=invoice_subscription.invoice_id', '','')
            ->where('invoice_date', $startDate->format('Y-m-d'), '>=')
            ->where('invoice_date', $endDate->format('Y-m-d'), '<=')
            ->where('paid', $paid)
            ->execute()
            ->fetchAll();
    }

    public function getByIds($invoiceIds)
    {
        if (!$invoiceIds) return $invoiceIds;

        return $this->vault
            ->select($this->dbTable)
            ->fields($this->dbTable.'.*')
            ->where('id', $invoiceIds, 'IN')
            ->execute()
            ->fetchAll();
    }

    public function getInvoiceNumberCache(\DateTime $minDate)
    {
        $invoices = [];
        $rows = $this->vault
            ->select($this->dbTable)
            ->fields('invoice_number')
            ->where('invoice_date', $minDate->format('Y-m-d'), '>=')
            ->orderBy('invoice_date', 'ASC')
            ->execute()
            ->fetchAll();
        foreach ($rows as $row) {
            $invoices[$row->invoice_number] = $row;
        }

        return $invoices;
    }

    public function getInvoiceFiguresForDates(\DateTime $startDate, \DateTime $endDate)
    {
        $totals = (object)[
            'total' => 0,
            'paid' => 0,
            'outstanding' => 0
        ];

        $rows = $this->vault
            ->select($this->dbTable)
            ->fields(['paid', 'SUM(total+vat) AS amount'])
            ->where('invoice_date', $startDate->format('Y-m-d H:i:s'), ">=")
            ->where('invoice_date', $endDate->format('Y-m-d H:i:s'), "<=")
            ->groupBy('paid')
            ->execute()
            ->fetchAll();

        foreach ($rows as $row) {
            if ($row->paid == '1') {
                $totals->paid = $row->amount;
            } else {
                $totals->outstanding = $row->amount;
            }
        }

        $totals->total = $totals->paid + $totals->outstanding;

        return $totals;
    }

    public function getByUserAndTransactionData($userId, $transDataSearch, $numRecords=10)
    {
        return $this->vault
            ->select($this->dbTable)
            ->fields()
            ->where('user_id', $userId)
            ->where('transaction_data', '%' . $transDataSearch . '%', 'LIKE')
            ->orderBy('id', 'DESC')
            ->limit($numRecords)
            ->execute()
            ->fetchAll();
    }
}