<?php

/*
 * @component UserPoints
 * @copyright Copyright (C) 2015-2022 Bernard Gilly - Adrien Roussel - Martin Brampton
 * @license : GNU/GPL
 * @Website : https://remository.com
 */
namespace BlackSheepResearch\Component\UserPoints\Administrator\Models;

// Check to ensure this file is included in Joomla!
defined('_JEXEC') or die();

use Joomla\CMS\Factory;
use Joomla\CMS\Language\Text;
use Joomla\CMS\HTML\HTMLHelper;
use Joomla\CMS\Plugin\PluginHelper;
use BlackSheepResearch\Component\UserPoints\Site\UserPoints;
use BlackSheepResearch\Component\UserPoints\Administrator\Model;
use BlackSheepResearch\Component\UserPoints\Site\Problems\SQL;
use BlackSheepResearch\Component\UserPoints\Site\Problems\Rule;
use BlackSheepResearch\Component\UserPoints\Site\Problems\Medal;
use BlackSheepResearch\Component\UserPoints\Site\Problems\PointsUser;
use BlackSheepResearch\Component\UserPoints\Site\Problems\PointsDetails;

class Statistics extends Model {

    function __construct() {
        parent::__construct();
    }

    function _needsync() {

        $db = SQL::getInstance();

        $this->_purge_old_users();

        $query = "SELECT count(*) FROM #__users";
        $db->setQuery($query);
        $totalUSers = $db->loadResult();

        $totalReferral = ( PointsUser::countAll() - 1 ); // remove the GUEST user in count

        return $totalUSers != $totalReferral ? true : false;
    }

    function _purge_old_users() {
        PointsUser::purge();
    }

    function _load_users() {

        $app = Factory::getApplication();

        $db = SQL::getInstance();

        $filter_levelrank = Factory::getApplication()->input->get('filterlevelrank', 0, 'int');
        $filter_order = $app->getUserStateFromRequest("com_userpoints.filter_order", 'filter_order', 'name', 'cmd');
        $filter_order_Dir = $app->getUserStateFromRequest("com_userpoints.filter_order_Dir", 'filter_order_Dir', '', 'word');
        $search = strtolower($app->getUserStateFromRequest("com_userpoints.search", 'search', '', 'string'));

        // check filter order
        $check_filter_order = array('userid', 'published', 'levelrank', 'name', 'username', 'points', 'last_update', 'refferaluser');
        if (!in_array($filter_order, $check_filter_order)) {
            $filter_order = 'name';
        }

        $orderby = " ORDER BY `$filter_order` $filter_order_Dir";

        // Get the pagination request variables
        list($limitstart, $limit) = PointsUser::getLimitsFromRequest();

        $where = array();

        if ($filter_levelrank > 0) {
            $where[] = 'levelrank = ' . (int) $filter_levelrank;
        }

        if ($search) {
            $where[] = 'LOWER(name) LIKE ' . $db->quote('%' . $db->escape($search, true) . '%');
            $where[] = 'LOWER(username) LIKE ' . $db->quote('%' . $db->escape($search, true) . '%');
            $where[] = 'LOWER(referreid) LIKE ' . $db->quote('%' . $db->escape($search, true) . '%');
        }
        $wheretext = ( count($where) ? " WHERE (" . implode(' OR ', $where) . ")" : "" );

        $query = "SELECT * FROM #__alpha_userpoints_combined " . $wheretext . $orderby;
        $total = $this->_getListCount($query);
        $result = $this->_getList($query, $limitstart, $limit);
        // table ordering
        $lists['order_Dir'] = $filter_order_Dir;
        $lists['order'] = $filter_order;
        // search filter
        $lists['search'] = $search;

        // get list level / rank
        $query = "SELECT * FROM #__alpha_userpoints_levelrank WHERE typerank='0'";
        $db->setQuery($query);
        $resultsrank = $db->loadObjectList();

        $javascript = 'onchange="document.adminForm.submit();"';
        if ($resultsrank) {
            $oplistRank[] = HTMLHelper::_('select.option', '-1', '- ' . Text::_('AUP_SELECT_A_RANK') . ' -');
            $oplistRank[] = HTMLHelper::_('select.option', '0', '- ' . Text::_('AUP_ALL') . ' -');
            foreach ($resultsrank as $resultrank) {
                $oplistRank[] = HTMLHelper::_('select.option', $resultrank->id, Text::_($resultrank->rank));
            }
            $lists['levelrank'] = HTMLHelper::_('select.genericlist', $oplistRank, 'filterlevelrank', 'class="inputbox" size="1" ' . $javascript, 'value', 'text', $filter_levelrank);
        } else {
            $lists['levelrank'] = "";
        }

        // Check if level/rank(s) exist
        $db->setQuery("SELECT count(*) FROM #__alpha_userpoints_levelrank WHERE typerank='0'");
        $ranksexist = $db->loadResult();

        // Check if medal(s) exist
        $db->setQuery("SELECT count(*) FROM #__alpha_userpoints_levelrank WHERE typerank='1'");
        $medalsexist = $db->loadResult();

        return array($result, $total, $limit, $limitstart, $lists, $ranksexist, $medalsexist);
    }

    function _edit_user() {

        $db = SQL::getInstance();

        $cid = Factory::getApplication()->input->get('cid', array(0), 'array');

        if (!is_array($cid)) {
            $cid = array(0);
        }

        $lists = array();

        $db->setQuery(sprintf("SELECT * FROM `#__alpha_userpoints_combined` WHERE id = %d", intval($cid[0])));
        $row = $db->loadObject();

        // get username of the referral user
        $query = "SELECT username AS referralusername FROM #__alpha_userpoints_combined WHERE referreid = '$row->referraluser'";
        $db->setQuery($query);
        $row->referralusername = $db->loadResult();

        // get list level / rank
        $query = "SELECT * FROM #__alpha_userpoints_levelrank WHERE typerank='0'";
        $db->setQuery($query);
        $results = $db->loadObjectList();

        $oplistRank[] = HTMLHelper::_('select.option', '0', '- ' . Text::_('AUP_SELECT_A_RANK') . ' -');
        foreach ($results as $result) {
            $oplistRank[] = HTMLHelper::_('select.option', $result->id, Text::_($result->rank));
        }
        $listrank = HTMLHelper::_('select.genericlist', $oplistRank, 'levelrank', 'class="inputbox" size="1"', 'value', 'text', $row->levelrank);

        // get list medals
        $queryMedals = "SELECT * FROM #__alpha_userpoints_levelrank WHERE typerank='1'";
        $db->setQuery($queryMedals);
        $results1 = $db->loadObjectList();
        $oplistRank1[] = HTMLHelper::_('select.option', '0', '- ' . Text::_('AUP_SELECT_MEDAL') . ' -');
        foreach ($results1 as $result1) {
            $oplistRank1[] = HTMLHelper::_('select.option', $result1->id, Text::_($result1->rank));
        }
        $listmedals = HTMLHelper::_('select.genericlist', $oplistRank1, 'medal', 'class="inputbox" size="1"', 'value', 'text', '0');

        // Check if medal(s) exist
        $queryMedals = "SELECT count(*) FROM #__alpha_userpoints_levelrank WHERE typerank='1'";
        $db->setQuery($queryMedals);
        $medalsexist = $db->loadResult();

        $medals = sprintf("SELECT m.id, m.medaldate, m.reason, lv.rank, lv.description, lv.icon, lv.image "
                . " FROM #__alpha_userpoints_medals AS m, #__alpha_userpoints_levelrank AS lv "
                . " WHERE m.rid = %d AND m.medal=lv.id"
                . " ORDER BY m.medaldate DESC", $row->id);
        $db->setQuery($medals);
        $medalslistuser = $db->loadObjectList();

        return array($row, $listrank, $medalsexist, $medalslistuser, $listmedals);
    }

    function _save_user($apply = 0) {
        $app = Factory::getApplication();
        // initialize variables
        $post = $app->input->post->getArray();
        $id = $app->input->get('id', 0, 'int');
        
        $row = new PointsUser();
        // last update
        $row->last_update = SQL::datetimeSQL();
        if ($post['oldrank'] != $post['levelrank']) {
            $row->leveldate = SQL::datetimeSQL();
        }
        if ($post['levelrank'] == 0) {
            $row->leveldate = SQL::nulldateSQL();
        }
        $row->save($post);

        $referreid = $post['referreid'];
        $this->recalculateUser($referreid);

        $msg = Text::_('AUP_GENERALSTATSSAVED');
        if (!$apply) {
            $this->redirect('index.php?option=com_userpoints&task=statistics', $msg);
        } else {
            $this->redirect('index.php?option=com_userpoints&task=edituser&cid[]=' . $id . '', $msg);
        }
    }

    function recalculateUser($referrerid) {
        PointsUser::withReferrerID($referrerid)->refresh();
    }

    function _save_medaluser() {
        $app = Factory::getApplication();

        // initialize variables
        $db = SQL::getInstance();
        $post = $app->input->post->getArray();
        $row = new Medal();

        // last update
        $row->medaldate = SQL::dateSQL();

        if ($post['reason'] == '') {
            $queryDescMedals = "SELECT description FROM #__alpha_userpoints_levelrank 
			WHERE typerank='1' AND id='" . intval($post['medal']) . "'";
            $db->setQuery($queryDescMedals);
            $medaldescription = $db->loadResult();
            $post['reason'] = $medaldescription;
        }

        $row->save($post);

        $query = "SELECT * FROM #__alpha_userpoints_levelrank WHERE typerank='1' AND id='" . intval($post['medal']) . "'";
        $db->setQuery($query);
        $medal = $db->loadObject();

        $userinfo = UserPoints::getUserInfo($post['referreid']);

        // load external plugins
        PluginHelper::importPlugin('userpoints');
        // load external plugins
        $results = $this->triggerEvent('onUnlockMedalUserPoints', array(&$userinfo, &$medal));

        $msg = Text::_('AUP_GENERALSTATSSAVED');
        $this->redirect('index.php?option=com_userpoints&task=edituser&cid[]=' . $post['rid'], $msg);
    }

    function _delete_medaluser() {

        $app = Factory::getApplication();

        // initialize variables
        $db = SQL::getInstance();
        $cid = Factory::getApplication()->input->get('cid', 0, 'int');
        $rid = Factory::getApplication()->input->get('rid', 0, 'int');

        if ($cid) {
            // remove user medals
            $query = "DELETE FROM #__alpha_userpoints_medals"
                    . "\n WHERE `id`=$cid AND `rid`=$rid";
            $db->setQuery($query);
            $db->execute();
        }

        $this->redirect('index.php?option=com_userpoints&task=edituser&cid[]=' . $rid);
    }

    function _load_top10() {
        $db = SQL::getInstance();
        $query = "SELECT * FROM `#__alpha_userpoints_combined` ORDER BY points DESC, name ASC";
        $result = $this->_getList($query, 0, 10);
        return $result;
    }

    private function makeConditions($pointscondition) {
        $date_start = Factory::getApplication()->input->get('date_start', '', 'string');
        $date_end = Factory::getApplication()->input->get('date_end', '', 'string');
        $rule = Factory::getApplication()->input->get('rule', '0', 'int');
        $where = ['published = 1'];
        $where[] = "points $pointscondition";
        if ($date_start AND $date_end AND $date_start <= $date_end) {
            $db = SQL::getInstance();
            $where[] = "insert_date >= {$db->quote($date_start)} AND insert_date <= {$db->quote($date_end)}";
        }
        if ($rule) {
            $rule_id = Rule::withRuleID($rule)->plugin_function;
            if ($rule_id) {
                $where[] = "rule_id='$rule_id'";
            }
        }
        return implode(' AND ', $where);
    }
    
    function _pointsearned() {
        $where = $this->makeConditions(' > 0.0 ');
        $db = SQL::getInstance();

        $query = "SELECT *, points AS sumpoints FROM #__alpha_userpoints_combined WHERE $where "
            . " ORDER BY points DESC LIMIT 10";
        $db->setQuery($query);
        return $db->loadObjectList();
    }

    function _pointsspent() {
        $where = $this->makeConditions(' <= 0.0 ');
        $db = SQL::getInstance();

        $query = "SELECT *, points AS sumpoints FROM #__alpha_userpoints_combined WHERE $where "
                . " ORDER BY points ASC LIMIT 10";
        $db->setQuery($query);
        return $db->loadObjectList();
    }

    function _getListRules($rule_input) {
        $rules = Rule::getAll();
        $oplistRules[] = HTMLHelper::_('select.option', '0', '- ' . Text::_('AUP_ALL') . ' -');
        foreach ($rules as $rule) {
            if ($rule->published) {
                $oplistRules[] = HTMLHelper::_('select.option', $rule->id, Text::_($rule->rule_name));
            }
        }
        $listRules = HTMLHelper::_('select.genericlist', $oplistRules, 'rule', 'class="inputbox" size="1"', 'value', 'text', $rule_input);

        return $listRules;
    }

    function _totalcurrentcommunitypoints() {
        return PointsDetails::totalPoints(0);
    }

    function _totalcommunitypointsearned() {
        return PointsDetails::totalPoints(1);
    }

    function _totalcommunitypointsspent() {
        return PointsDetails::totalPoints(-1);
    }

    function _get_num_users() {
        return PointsUser::countByFieldvalue('blocked', 0);
    }

    function _average_points_earned_by_day() {
        $numdays = PointsDetails::getDateRange();
        $currentpointscommunityearned = $this->_totalcommunitypointsearned();
        $numusers = $this->_get_num_users();

        if ($numdays AND $numusers) {
            return round((($currentpointscommunityearned / $numdays) / $numusers), 2);
        }
        return 0;
    }

    function _average_points_spent_by_day() {
        $numdays = PointsDetails::getDateRange();
        $currentpointscommunityspent = $this->_totalcommunitypointsspent();
        $numusers = $this->_get_num_users();

        if ($numdays AND $numusers) {
            return round((($currentpointscommunityspent / $numdays) / $numusers), 2);
        }
        return 0;
    }

    function _get_inactive_members() {
        $inactive_members = $num_days = 0;
        $inactive_user_rule = UserPoints::checkRuleEnabled('sysplgup_inactiveuser');
        if ($inactive_user_rule AND $inactive_user_rule->published) {
            $num_days = intval($inactive_user_rule->content_items);
            $inactive_members = PointsUsers::inactiveCount($num_days);
        }
        return [$inactive_members, $num_days];
    }

}
