<?php
/** 
 * @package     VikRestaurants
 * @subpackage  core
 * @author      E4J s.r.l.
 * @copyright   Copyright (C) 2023 E4J s.r.l. All Rights Reserved.
 * @license     http://www.gnu.org/licenses/gpl-2.0.html GNU/GPL
 * @link        https://vikwp.com
 */

namespace E4J\VikRestaurants\Upselling\Statistics;

// No direct access
defined('ABSPATH') or die('No script kiddies please!');

use E4J\VikRestaurants\Upselling\StatisticsRepository;

/**
 * Fetches the statistics from the database.
 * 
 * @since 1.11
 */
class DatabaseRepository implements StatisticsRepository
{
    /**
     * Item pairs cache.
     * 
     * @var object[]
     */
    protected static $pairs = null;

    /**
     * Items count cache.
     * 
     * @var array
     */
    protected static $items = null;

    /**
     * Items creation cache.
     * 
     * @var array
     */
    protected static $creations = null;

    /**
     * A configuration array.
     * 
     * @var array
     */
    protected $options;

    /**
     * The total number of orders.
     * 
     * @var int
     */
    protected $totalOrders = null;

    /**
     * Class constructor.
     * 
     * @param  array  $options  A configuration array.
     */
    public function __construct(array $options = [])
    {
        if (!empty($options['since']) && !is_numeric($options['since']))
        {
            // convert specified date and time into a UNIX timestamp
            $options['since'] = \E4J\VikRestaurants\Helpers\DateHelper::getTimestamp($options['since']);
        }

        $this->options = $options;
    }

    /**
     * @inheritDoc
     */
    public function train()
    {
        if ($this->getTotalOrders() == 0 || !$this->shouldTrain())
        {
            // do not proceed in case of no orders received or in case the training process should be skipped
            return;
        }

        // Register last training date before completing the process.
        // This way, in case of execution errors or timeouts, the issue won't occur more than once per day.
        \VREFactory::getConfig()->set('upselling_training_upto', \JFactory::getDate('now')->toSql());

        // ignore memory limit
        set_time_limit(0);

        $db = \JFactory::getDbo();

        // truncate item pairs
        $db->setQuery('TRUNCATE TABLE `#__vikrestaurants_upsell_item_pairs`');
        $db->execute();

        // create inner query to prevent duplicate records
        $distinctItemsQuery = $db->getQuery(true)
            ->select('DISTINCT ' . $db->qn('i.id_res') . ', ' . $db->qn('i.id_product'))
            ->from($db->qn('#__vikrestaurants_takeaway_res_prod_assoc', 'i'));

        if (!empty($this->options['since']))
        {
            // take only the orders created after the specified date
            $distinctItemsQuery->innerjoin($db->qn('#__vikrestaurants_takeaway_reservation', 'o') . ' ON ' . $db->qn('o.id') . ' = ' . $db->qn('i.id_res'));
            $distinctItemsQuery->where($db->qn('o.created_on') . ' >= ' . $this->options['since']);
        }

        $query = $db->getQuery(true);

        // pull all the item pairs
        $query->select($db->qn('a.id_product', 'item_a'));
        $query->select($db->qn('b.id_product', 'item_b'));
        $query->select('COUNT(1) AS ' . $db->qn('count'));
            
        // use sub query because the same product cannot figure more than once per order
        $query->from('(' . $distinctItemsQuery . ') AS ' . $db->qn('a'));
        $query->innerjoin('(' . $distinctItemsQuery . ') AS ' . $db->qn('b') . ' ON ' . $db->qn('a.id_res') . ' = ' . $db->qn('b.id_res'));
        
        // prevent the query from including mirror (and self) combinations
        $query->where($db->qn('a.id_product') . ' < ' . $db->qn('b.id_product'));
        $query->group($db->qn('a.id_product'));
        $query->group($db->qn('b.id_product'));

        $db->setQuery($query);
        $records = $db->loadObjectList();

        if ($records)
        {
            // prepare insert query
            $insert = $db->getQuery(true)
                ->insert($db->qn('#__vikrestaurants_upsell_item_pairs'))
                ->columns($db->qn(['item_a', 'item_b', 'count']));

            foreach ($records as $record)
            {
                $insert->values(implode(', ', get_object_vars($record)));
            }

            $db->setQuery($insert);
            $db->execute();
        }

        // truncate item pairs
        $db->setQuery('TRUNCATE TABLE `#__vikrestaurants_upsell_item_totals`');
        $db->execute();

        $query = $db->getQuery(true);

        // pull all the item counts
        $query->select($db->qn('d.id_product', 'item'));
        $query->select('COUNT(1) AS ' . $db->qn('count'));

        // use sub query because the same product cannot figure more than once per order
        $query->from('(' . $distinctItemsQuery . ') AS ' . $db->qn('d'));
        $query->group($db->qn('d.id_product'));

        $db->setQuery($query);
        $records = $db->loadObjectList();

        if ($records)
        {
            // prepare insert query
            $insert = $db->getQuery(true)
                ->insert($db->qn('#__vikrestaurants_upsell_item_totals'))
                ->columns($db->qn(['item', 'count']));

            foreach ($records as $record)
            {
                $insert->values(implode(', ', get_object_vars($record)));
            }

            $db->setQuery($insert);
            $db->execute();
        }
    }

    /**
     * Checks whether the repository should perform the training process or not.
     * The training should occur at most once per day.
     * 
     * @return  bool
     */
    protected function shouldTrain()
    {
        $lastTraining = \VREFactory::getConfig()->get('upselling_training_upto');

        if (!$lastTraining)
        {
            // system never trained before
            return true;
        }

        $nextTraining = \JFactory::getDate($lastTraining);
        $nextTraining->modify('+1 day');

        // can train in case the current time reached or exceeded the next expected training
        return \JFactory::getDate('now') >= $nextTraining;
    }

    /**
     * @inheritDoc
     */
    public function getItemsPair(int $itemA, int $itemB)
    {
        foreach (static::loadPairs() as $pair)
        {
            // check both the possible combinations
            if (($pair->item_a == $itemA && $pair->item_b == $itemB)
                || ($pair->item_a == $itemB && $pair->item_b == $itemA)) {
                return $pair->count;
            }
        }

        // no A+B pair found
        return 0;
    }

    /**
     * @inheritDoc
     */
    public function getItemTotal(int $item)
    {
        $items = static::loadItems();

        return $items[$item] ?? 0;
    }

    /**
     * @inheritDoc
     */
    public function getTotalOrders()
    {
        if ($this->totalOrders === null)
        {
            $db = \JFactory::getDbo();

            $query = $db->getQuery(true)
                ->select('COUNT(1)')
                ->from($db->qn('#__vikrestaurants_takeaway_reservation'));

            if (!empty($this->options['since']))
            {
                // take only the orders created after the specified date
                $query->where($db->qn('created_on') . ' >= ' . $this->options['since']);
            }

            $db->setQuery($query);
            $this->totalOrders = (int) $db->loadResult();
        }

        return $this->totalOrders;
    }

    /**
     * @inheritDoc
     */
    public function getItemCreation(int $item)
    {
        $items = static::loadCreations();

        return $items[$item] ?? null;
    }

    /**
     * Loads all the item pairs and related sales.
     * 
     * @return  object[]
     */
    protected static function loadPairs()
    {
        if (static::$pairs === null)
        {
            $db = \JFactory::getDbo();

            $query = $db->getQuery(true)
                ->select($db->qn('item_a'))
                ->select($db->qn('item_b'))
                ->select($db->qn('count'))
                ->from($db->qn('#__vikrestaurants_upsell_item_pairs'));

            $db->setQuery($query);
            static::$pairs = $db->loadObjectList();
        }

        return static::$pairs;
    }

    /**
     * Loads all the item sales.
     * 
     * @return  array
     */
    protected static function loadItems()
    {
        if (static::$items === null)
        {
            static::$items = [];

            $db = \JFactory::getDbo();

            $query = $db->getQuery(true)
                ->select($db->qn('item'))
                ->select($db->qn('count'))
                ->from($db->qn('#__vikrestaurants_upsell_item_totals'));

            $db->setQuery($query);
            
            // create item => count lookup
            foreach ($db->loadObjectList() as $row)
            {
                static::$items[$row->item] = $row->count;
            }
        }

        return static::$items;
    }

    /**
     * Loads all the item creations.
     * 
     * @return  array
     */
    protected static function loadCreations()
    {
        if (static::$creations === null)
        {
            static::$creations = [];

            $db = \JFactory::getDbo();

            $query = $db->getQuery(true)
                ->select($db->qn('i.id_product'))
                ->select('MIN(' . $db->qn('o.created_on') . ') AS ' . $db->qn('firstPurchase'))
                ->from($db->qn('#__vikrestaurants_takeaway_reservation', 'o'))
                ->innerjoin($db->qn('#__vikrestaurants_takeaway_res_prod_assoc', 'i') . ' ON ' . $db->qn('i.id_res') . ' = ' . $db->qn('o.id'))
                ->group($db->qn('i.id_product'));

            $db->setQuery($query);
            
            // create item => date lookup
            foreach ($db->loadObjectList() as $row)
            {
                static::$creations[$row->id_product] = $row->firstPurchase;
            }
        }

        return static::$creations;
    }
}
