<?php

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

use apexl\Io\modules\component\entities\componentEntity;
use apexl\Io\modules\component\entities\componentTypeEntity;
use apexl\Io\modules\component\entities\propertyEntity;
use apexl\Io\operators\entityDatabaseOperator;

class subscriptionOperator extends entityDatabaseOperator{

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

    public function fetchAllForTroubleshoot(){
        return $this->vault->select('subscription s')->fields(['s.id','s.product_quantity','s.product_id', 's.enabled','s.suspended','s.date_starts','s.payment_provider','s.next_billing_date','s.cancellation_date','s.last_billing_date','u.email', 's.user_id', 's.price_override', 's.tbshoot_notes'])
            ->leftjoin('users u', 's.user_id=u.id', '','')
            ->where('s.tbshoot_omit', 0)
            ->execute()
            ->fetchAll();
    }

    public function getByCompany($companyId){
        return $this->vault->select('subscription')->fields()
            ->where('CompanyId', $companyId)
            ->execute()
            ->fetchAll();
    }

    public function getByUser($userId){
        return $this->vault->select('subscription')->fields()
            ->where('user_id', $userId)
            ->execute()
            ->fetchAll();
    }

    public function getByCode($code){
        $subscription = $this->vault->select('subscription')->fields()
            ->where('code', $code)
            ->execute()
            ->fetchAssoc();

        return (object)$subscription;
    }

    public function disableSubscription($id){
        return $this->vault->update('subscription')->fields(['enabled' => 0])->where('id', $id)->execute();
    }

    public function enableSubscription($id){
        return $this->vault->update('subscription')->fields(['enabled' => 1])->where('id', $id)->execute();
    }

    public function getTypesForSubscription($subscriptionId){
        return $this->vault->select('subscription_types')->fields()
            ->where('SubscriptionId', $subscriptionId)
            ->execute()
            ->fetchAll();
    }

    public function getByNextBillingDate($nextBillingDate, $limit=20){
        return $this->vault
            ->select($this->dbTable)
            ->fields()
            ->where('next_billing_date', $nextBillingDate, '<=')
            ->limit($limit)
            ->execute()
            ->fetchAll();
    }

    public function getByPaymentId($paymentId){

        $rows = $this->vault
            ->select('subscription')
            ->fields('subscription.id')
            ->leftjoin('subscription_payment', $this->dbTable.'.id=subscription_payment.subscription_id', '','')
            ->where('payment_id', $paymentId)
            ->execute()
            ->fetchAll();

        foreach ($rows as $row) {
            $entity = $this->vault
                ->select($this->dbTable)
                ->fields()
                ->where('id', $row->id)
                ->execute()
            ->fetchAssoc();

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

        return null;
    }

    /**
     * Get all subscriptions for which billing hasn't been attempted in the last X hours, indexed by user
     * @param $nextBillingDate
     * @param $limit
     * @return mixed
     */
    public function getSubscriptionsToBill($nextBillingDate, $limit=10, $cutoffHours=6){
        $time = new \DateTime('now');
        $timeInterval = $cutoffHours*60;
        $time->sub(new \DateInterval("PT".$timeInterval."M"));

        $checksCutoff = new \DateTime('now');
        $checksCutoff->add(new \DateInterval('P3M'));

        $createdCutoff = new \DateTime('now'); // Ensure subscription is at least one hour old, to avoid clashes with incoming orders
        $createdCutoff->sub(new \DateInterval("PT1H"));

        $rows = $this->vault
            ->select($this->dbTable)
            ->fields($this->dbTable.'.*')
            ->leftjoin('subscription_payment', $this->dbTable.'.id=subscription_payment.subscription_id', '','')
            ->leftjoin('payment', 'payment.id=subscription_payment.payment_id AND payment.created > '.$time->format('U'), '','')
            ->where('next_billing_date', $nextBillingDate, '<=')
            ->where('payment.id',NULL, "IS NULL")
            ->where($this->dbTable.'.created', $createdCutoff->format('Y-m-d H:i:s'), '<=')
            ->where('(enabled', 1)
            ->where('cancellation_date', $checksCutoff->format('Y-m-d'), '<=', 'OR', ')')
            //->where('payment_provider', 'stripe')
            //->where('subscription.id', [X], 'IN')
            ->orderBy($this->dbTable.'.last_checked', 'ASC')
            ->limit($limit)
            ->execute()
            ->fetchAll();

        // Index by userId
        $subscriptionsByUser = [];
        $count = 0;
        foreach ($rows as $row) {
            if (!isset($subscriptionsByUser[$row->user_id][$row->payment_provider])) {
                $subscriptionsByUser[$row->user_id][$row->payment_provider] = [];
                $count++;
            }
            $subscriptionsByUser[$row->user_id][$row->payment_provider][$row->id] = $row;

            if ($count >= $limit) break;
        }

        return $subscriptionsByUser;
    }


    public function getByUserCompanyProductAndDate($userId, $companyId, $productId, $dateStarts){

        $query = $this->vault->select('subscription')->fields()
            ->where('user_id', $userId)
            ->where('product_id', $productId)
            ->where('date_starts', $dateStarts)
            ;

        if ($companyId && $companyId > 0) {
            $query->where('company_id', $companyId);
        }

        return $query->execute()
            ->fetchAssoc();
    }

    public function getEndOfGracePeriodCache(){
        $data = $this->vault->select('subscription')->fields('id, grace_period_ends')
            ->execute()
            ->fetchAll();
        $results = [];
        foreach ($data as $item) {
            if ($item->grace_period_ends) {
                $results[$item->id] = $item->grace_period_ends;
            }
        }

        return $results;
    }

    public function getWordPressOrderInfo($orderId){
        $this->setOperatorVault('geosim');
        $orderData = $this->vault->select('wp_postmeta')->fields()->where('post_id', $orderId)->execute()->fetchAll();
        $this->setOperatorVault('default');
        return $orderData;
    }

    public function getWordPressOrderStatus($orderId){
        $this->setOperatorVault('geosim');
        $orderData = $this->vault->select('wp_wc_order_stats')->fields()->where('order_id', $orderId)->execute()->fetchAssoc();
        $this->setOperatorVault('default');
        return $orderData;
    }

    /**
     * Function to get all active subscriptions that are:
     *   1. next payment overdue by 7 days
     *   2. have 3 payment attempts in the last 7 days which do not have status='successful'
     *
     *  These subscriptions are singled out for suspension
     * @param $numFailedPayments
     * @return void
     */
    public function getOverdueWithFailedPaymentAttempts($numFailedPayments=3)
    {
        $time = new \DateTime('now');
        $time->sub(new \DateInterval("P7D")); // Find all payment attempts in the last 7 days

        $subscriptions = $this->vault
            ->select($this->dbTable)
            ->fields([$this->dbTable.'.*', 'count(payment.id) as numPayments'])
            ->leftjoin('subscription_payment', $this->dbTable.'.id=subscription_payment.subscription_id', '','')
            ->leftjoin('payment', 'payment.id=subscription_payment.payment_id AND payment.created > '.$time->format('U'), '','')
            ->where('next_billing_date', $time->format('Y-m-d'), '<=')
            ->where('payment.status', 'successful', "!=")
            ->where('enabled', true)
            ->groupBy($this->dbTable.'.id')
            ->orderBy($this->dbTable.'.last_checked', 'ASC')
            ->execute()
            ->fetchAll();
        foreach ($subscriptions as $id => $subscription) {
            if ($subscription->numPayments < $numFailedPayments) {
               unset($subscriptions[$id]);
            }
        }

        return $subscriptions;
    }

    public function getByInvoiceId($invoiceId)
    {
        return $this->vault
            ->select($this->dbTable)
            ->fields($this->dbTable.'.*')
            ->leftjoin('invoice_subscription', $this->dbTable.'.id=invoice_subscription.subscription_id', '','')
            ->where('invoice_id', $invoiceId)
            ->execute()
            ->fetchAll();
    }

    public function getWithProducts($subscriptionIds)
    {
        return $this->vault
            ->select($this->dbTable)
            ->fields([$this->dbTable.'.*', 'DATE_FORMAT(next_billing_date, "%D %b %Y") AS next_billing_date', 'DATE_FORMAT(last_billing_date, "%D %b %Y") AS last_billing_date', 'DATE_FORMAT(date_starts, "%D %b %Y") AS start_date', 'product.product_name, product.code as wpId, product.price'])
            ->leftjoin('product', $this->dbTable.'.product_id=product.id', '','')
            ->where('subscription.id', $subscriptionIds, 'IN')
            ->execute()
            ->fetchAll();
    }

    public function getScheduledForCancellation($date=null)
    {
        if (is_null($date)) {
            $date = new \DateTime('now');
        }

        return $this->vault
            ->select($this->dbTable)
            ->fields()
            ->where('cancellation_date', $date->format('Y-m-d'), '=')
            ->where('enabled', '1')
            //->limit($limit)
            ->execute()
            ->fetchAll();
    }

    public function getAllIndexedByRemoteId()
    {
        $results = $this->vault
            ->select($this->dbTable)
            ->fields(['id','remote_subscription_id'])
            ->where('remote_subscription_id', '', 'IS NOT NULL')
            ->execute()
            ->fetchAll();

        $indexed = [];
        foreach ($results as $result) {
            $indexed[$result->remote_subscription_id][] = $result;
        }

        return $indexed;
    }
}