<?php

namespace Mnv\Models;

/**
 * Class Stats
 * @package Mnv\Models
 */
class Stats
{

    /** @var int  */
    public int $visitors = 0;
    /** @var int  */
    public int $visits = 0;
    /** @var int  */
    public int $rssVisits = 0;

    public array $stat = [];
    /** @var  */
    public $days;

    /** @var string  */
    public string $dateFormat = "Y-m-d";

    /**  StatsVisitors  */

    public function getStatDays(?string $dateStart, ?string $dateEnd)
    {
        /** получение самой ранней даты */
        $minDate = connect()->table('stats_cache_daily')->min('cacheDate', 'cacheDate')->get('array');
        if(strtotime($minDate['cacheDate']) > strtotime($dateStart)) {
            $dateStart = $minDate['cacheDate'];
        }

        /** если слишком много дней, сгруппируйте по неделям или месяцам и получить итоговые значения */
        $totalDays = (strtotime($dateEnd) - strtotime($dateStart)) / (60 * 60 * 24);

        if ($totalDays > 300) { // M Y
            connect()->sum('newVisitors', 'newVisitors');
            connect()->sum('retVisitors', 'retVisitors');
            connect()->sum('sectionVisits', 'sectionVisits');
            connect()->sum('articleVisits', 'articleVisits');
            connect()->sum('productVisits', 'productVisits');
            connect()->sum('searchVisits', 'searchVisits');
            connect()->sum('errorVisits', 'errorVisits');
            connect()->sum('rssVisits', 'rssVisits');
            connect()->groupBy(['YEAR(cacheDate)', 'MONTH(cacheDate)']);
            connect()->select('cacheDate');
            $this->dateFormat = "M Y";

        } else if($totalDays > 70) { // \Week W, Y

            connect()->sum('newVisitors', 'newVisitors');
            connect()->sum('retVisitors', 'retVisitors');
            connect()->sum('sectionVisits', 'sectionVisits');
            connect()->sum('articleVisits', 'articleVisits');
            connect()->sum('productVisits', 'productVisits');
            connect()->sum('searchVisits', 'searchVisits');
            connect()->sum('errorVisits', 'errorVisits');
            connect()->sum('rssVisits', 'rssVisits');
            connect()->groupBy(['YEAR(cacheDate)', 'WEEK(cacheDate, 3)']);
            connect()->select('cacheDate');

        } else {

            connect()->select('cacheDate, newVisitors, retVisitors, sectionVisits, articleVisits, productVisits, searchVisits, errorVisits, rssVisits');
        }

        $this->days = connect()->table('stats_cache_daily')->between('cacheDate', $dateStart, $dateEnd)->orderBy('cacheDate')->getAll('array');
//        return connect()->table('stats_cache_daily')->between('cacheDate', $dateStart, $dateEnd)->orderBy('cacheDate')->getAll('array');

    }

    /**
     * StatsVisitors
     *
     * @param string|null $dateStart
     * @param string|null $dateEnd
     * @param int|null $filterTypeId
     * @param string|null $domain
     * @return array|false|mixed|string
     */
    public function getStatsVisitors(?string $dateStart, ?string $dateEnd, ?int $filterTypeId, ?string $domain, $limit, $page)
    {
        if (!empty($filterTypeId)) connect()->where('typeId',  $filterTypeId);

        if (!empty($domain)) {
            if($domain == 'unknown') {
                $domain = '';
            }
            connect()->where("REPLACE(SUBSTRING_INDEX(referer, '/', 3), 'www.', '')",  $domain);
        }

        $visitors = connect()->table('stats_visits')
            ->select('visitorId, referer, firstVisitOn, COUNT(*) AS totalVisits')
            ->usingJoin('stats_visitors', 'visitorId')
            ->usingJoin('stats_user_agents', 'userAgentId')
            ->between('visitDate', $dateStart, $dateEnd)
//            ->where('isBot',  0)
            ->pagination($limit, $page)
            ->orderBy('firstVisitOn DESC')
            ->groupBy('visitorId')
            ->indexKey('visitorId')
            ->getAllIndexes('array');

        return $visitors;

    }

    /**
     * @param string|null $dateStart
     * @param string|null $dateEnd
     * @param int|null $filterTypeId
     * @param string|null $domain
     * @return mixed|string|null
     */
    public function getTotalStatsVisitors(?string $dateStart, ?string $dateEnd, ?int $filterTypeId, ?string $domain)
    {
        if (!empty($filterTypeId)) connect()->where('typeId', $filterTypeId);

        if (!empty($domain)) {
            if ($domain == 'unknown') {
                $domain = '';
            }
            connect()->where("REPLACE(SUBSTRING_INDEX(referer, '/', 3), 'www.', '')",  $domain);
        }

        return connect()->table('stats_visits')
            ->usingJoin('stats_visitors', 'visitorId')
            ->usingJoin('stats_user_agents', 'userAgentId')
            ->count('DISTINCT visitorId', 'count')
            ->between('visitDate',$dateStart, $dateEnd)
            ->where('isBot',0)
            ->getValue();
    }


    public function getStatsVisitor($visitorId)
    {
        return connect()->table('stats_visitors')
            ->select('visitorId, firstVisitOn, referer, visitorIp, userAgent, isBot, visitId, typeId')
            ->usingJoin('stats_visits', 'visitorId')
            ->usingJoin('stats_user_agents', 'userAgentId')
            ->where('isFirst',  1)
            ->where('visitorId', $visitorId)
            ->get('array');
    }


    /**
     * @param string|null $dateStart
     * @param string|null $dateEnd
     * @param int|null $filterTypeId
     * @param int|null $visitorId
     * @return array|null
     */
    public function getStatsVisits(?string $dateStart, ?string $dateEnd, ?int $filterTypeId, ?int $visitorId, $limit, $page): ?array
    {

        if (!empty($filterTypeId)) connect()->where('typeId', $filterTypeId);

        return connect()->table('stats_visits')
            ->between('visitDate',$dateStart, $dateEnd)
            ->where('visitorId', $visitorId)
            ->pagination($limit, $page)
            ->orderBy('visitDate', 'DESC')->indexKey('visitId')->getAllIndexes('array');
    }

    public function getTotalStatsVisits(?string $dateStart, ?string $dateEnd, ?int $filterTypeId, ?int $visitorId)
    {

        if (!empty($filterTypeId)) connect()->where('typeId', $filterTypeId);

        return connect()->table('stats_visits')->count('*', 'count')
            ->between('visitDate',$dateStart, $dateEnd)
            ->where('visitorId',$visitorId)
            ->getValue();
    }

    /**
     * @param array|null $visits
     * @return array|null
     */
    public function sectionVisits(?array $visits): ?array
    {
        if ($sectionVisits = connect()->table('stats_section_visits')->usingJoin('sections', 'sectionId')->select('visitId, sectionId, name, url')->in('visitId', array_keys($visits))->indexKey('visitId')->getAllIndexes('array')) {
            foreach($sectionVisits as $visitId => $visit) $visits[$visitId] += $visit;

            return $visits;
        }

        return null;
    }

    /**
     * @param array|null $visits
     * @return array|null
     */
    public function articleVisits(?array $visits): ?array
    {
        if ($articleVisits = connect()->table('stats_article_visits')->usingJoin('articles', 'articleId')->select('visitId, articleId, title, url')
            ->in('visitId', array_keys($visits))->indexKey('visitId')->getAllIndexes('array')) {
            foreach($articleVisits as $visitId => $visit) $visits[$visitId] += $visit;


            return $visits;
        }
        return null;
    }

    /**
     * @param array|null $visits
     * @return array|null
     */
    public function searchVisits(?array $visits): ?array
    {
        if ($searchVisits = connect()->table('stats_search_visits')->select('visitId, searchQuery, searchPage')->in('visitId', array_keys($visits))->indexKey('visitId')->getAllIndexes('array')) {
            foreach ($searchVisits as $visitId => $visit) $visits[$visitId] += $visit;

            return $visits;
        }
        return null;
    }

    /**
     * @param array|null $visits
     * @return array|null
     */
    public function errorVisits(?array $visits): ?array
    {
        if ($errorVisits = connect()->table('stats_error_visits')
            ->select('visitId, errorCode, requestUri, referer')->in('visitId', array_keys($visits))->indexKey('visitId')->getAllIndexes('array')) {
            foreach ($errorVisits as $visitId => $visit) {
                $visit += parseReferer($visit['referer']);
                $visits[$visitId] += $visit;
            }


            return $visits;
        }
        return null;
    }

    /**
     * @param array|null $visits
     * @return array|null
     */
    public function rssVisits(?array $visits): ?array
    {
        global $SECTIONS;

        if ($rssVisits = connect()->table('stats_rss_visits')->select('*')->in('visitId', array_keys($visits))->indexKey('visitId')->getAllIndexes('array')) {
            foreach($rssVisits as $visitId => $visit){
                if(!empty($SECTIONS[$visit['sectionId']])) $visit['section'] = $SECTIONS[$visit['sectionId']];
                $visits[$visitId] += $visit;
            }

            return $visits;
        }
        return null;
    }

    /**
     * @param $a
     * @param $b
     */
    public function sortByVisitDate(&$a, &$b)
    {
        if ($a == $b) {
            return 0;
        }
        return (strtotime($a['visitDate']) < strtotime($b['visitDate'])) ? 1 : -1;
    }

    /**
     * @param $visits
     * @param $visitId
     * @return array|false|mixed|string
     */
    public function getVisitInfo($visits, $visitId)
    {

        if (!empty($visits[$visitId])) {
            return $visits[$visitId];
        }

        $visit = connect()->table('stats_visits')->where('visitId', $visitId)->get('array');
        $visit['typeName']  =  lang('visitTypes:' . $visit['typeId']);
        $visit['visitDate'] =  langDate(adjustTime($visit['visitDate'], false, 'd.m, H:i:s'));

        switch($visit['typeId']) {
            case 1:
                break;
            case 2:
                $visit += connect()->table('stats_section_visits')->select('sectionId, name, url')->usingJoin('sections', 'sectionId')->where('visitId', $visitId)->get('array');

                break;
            case 3:
                $visit += connect()->table('stats_article_visits')->select('articleId, title, url')->usingJoin('articles', 'articleId')->where('visitId', $visitId)->get('array');
                break;
            case 4:
                $visit += connect()->table('stats_search_visits')->select('searchQuery, searchPage')->where('visitId', $visitId)->get('array');
                break;
            case 6:
                $visit += connect()->table('stats_error_visits')->select('visitId, errorCode, requestUri, referer')->where('visitId', $visitId)->get('array');
                $visit['referer'] = parseReferer($visit['referer']);
                break;
            case 7:
                $visit += connect()->table('stats_rss_visits')->select('*')->where('visitId', $visitId)->get('array');
                if(!empty($SECTIONS[$visit['sectionId']])) $visit['section'] = $SECTIONS[$visit['sectionId']];
                break;

        }

        return $visit;
    }



    /**  StatsErrors  */

    /**
     * StatsErrors
     * @param string|null $dateStart
     * @param string|null $dateEnd
     * @param int|null $filterErrorCode
     * @return array|false|mixed|string
     */
    public function getStatsErrors(?string $dateStart, ?string $dateEnd, ?int $filterErrorCode, $limit, $page)
    {

        if (!empty($filterErrorCode)) connect()->where('errorCode', $filterErrorCode);

        return connect()->table('stats_visits')
            ->select('requestUri, referer, errorCode, COUNT(*) AS totalVisits')
            ->usingJoin('stats_error_visits','visitId')
            ->between('visitDate',$dateStart, $dateEnd)
            ->where('typeId', 6)
            ->pagination($limit, $page)
            ->orderBy('totalVisits', 'DESC')
            ->groupBy('requestUri')->getAll('array');
    }

    /**
     * @param string|null $dateStart
     * @param string|null $dateEnd
     * @param int|null $filterErrorCode
     * @return mixed|string|null
     */
    public function getTotalStatsErrors(?string $dateStart, ?string $dateEnd, ?int $filterErrorCode)
    {
        if (!empty($filterErrorCode)) connect()->where('errorCode', $filterErrorCode);

        return connect()->table('stats_visits')
            ->count('DISTINCT requestUri', 'totalVisits')
            ->usingJoin('stats_error_visits','visitId')
            ->between('visitDate',$dateStart, $dateEnd)
            ->where('typeId', 6)
            ->getValue();
    }


    /**
     * StatsError
     * @param string|null $dateStart
     * @param string|null $dateEnd
     * @param string|null $requestUri
     * @return array|false|mixed|string
     */
    public function getStatsError(?string $dateStart, ?string $dateEnd, ?string $requestUri, $limit, $page)
    {
        return connect()->table('stats_visits')
            ->usingJoin('stats_error_visits', 'visitId')
            ->between('visitDate',$dateStart, $dateEnd)
            ->where('requestUri', $requestUri)
            ->pagination($limit, $page)
            ->orderBy('visitDate', 'DESC')
            ->indexKey('visitId')
            ->getAllIndexes('array');
    }

    /**
     * @param string|null $dateStart
     * @param string|null $dateEnd
     * @param string|null $requestUri
     * @return mixed|string|null
     */
    public function getTotalStatsError(?string $dateStart, ?string $dateEnd, ?string $requestUri)
    {
        return connect()->table('stats_visits')
            ->usingJoin('stats_error_visits', 'visitId')
            ->count('*', 'count')
            ->between('visitDate',$dateStart, $dateEnd)
            ->where('requestUri', $requestUri)
            ->getValue();
    }


    /**  StatsBots  */

    /**
     * StatsBots
     * @param string|null $dateStart
     * @param string|null $dateEnd
     * @param int|null $filterTypeId
     * @return array|mixed|null
     */
    public function getStatsBots(?string $dateStart, ?string $dateEnd, ?int $filterTypeId, $limit, $page)
    {

        if (!empty($filterTypeId)) connect()->where('typeId', $filterTypeId);

        return connect()->table('stats_visits')
            ->select('visitorId, userAgent, COUNT(*) AS totalVisits')
            ->usingJoin('stats_visitors', 'visitorId')
            ->usingJoin('stats_user_agents', 'userAgentId')
            ->between('visitDate',$dateStart, $dateEnd)
            ->where('isBot',1)
            ->pagination($limit, $page)
            ->orderBy('totalVisits', 'DESC')
            ->groupBy('visitorId')
            ->indexKey('visitorId')
            ->getAllIndexes('array');
    }

    /**
     * @param string|null $dateStart
     * @param string|null $dateEnd
     * @param int|null $filterTypeId
     * @return mixed|string|null
     */
    public function getTotalStatsBots(?string $dateStart, ?string $dateEnd, ?int $filterTypeId)
    {
        if (!empty($filterTypeId)) connect()->where('typeId', $filterTypeId);

        return connect()->table('stats_visits')
            ->count('DISTINCT visitorId', 'totalVisits')
            ->usingJoin('stats_visitors', 'visitorId')
            ->usingJoin('stats_user_agents', 'userAgentId')
            ->between('visitDate',$dateStart, $dateEnd)
            ->where('isBot',1)
            ->getValue();
    }


    /**  StatsReferer  */

    /**
     * StatsBots
     * @param string|null $dateStart
     * @param string|null $dateEnd
     * @param int|null $filterTypeId
     * @return array|mixed|null
     */
    public function getStatsReferer(?string $dateStart, ?string $dateEnd, ?int $filterTypeId, $limit, $page)
    {

        if (!empty($filterTypeId)) connect()->where('typeId', $filterTypeId);

        return connect()->table('stats_visits')
            ->select("REPLACE(SUBSTRING_INDEX(referer, '/', 3), 'www.', '') AS domain")
            ->count('DISTINCT visitorId', 'totalVisitors')
            ->usingJoin('stats_visitors', 'visitorId')
            ->usingJoin('stats_user_agents', 'userAgentId')
            ->between('visitDate',$dateStart, $dateEnd)
            ->where('isBot', 0)
            ->pagination($limit, $page)
            ->orderBy('totalVisitors', 'DESC')
            ->groupBy('domain')
            ->getAll('array');
    }

    /**
     * @param string|null $dateStart
     * @param string|null $dateEnd
     * @param int|null $filterTypeId
     * @return mixed|string|null
     */
    public function getTotalStatsReferer(?string $dateStart, ?string $dateEnd, ?int $filterTypeId)
    {
        if (!empty($filterTypeId)) connect()->where('typeId', $filterTypeId);

        return connect()->table('stats_visits')
            ->count("DISTINCT REPLACE(SUBSTRING_INDEX(referer, '/', 3), 'www.', '')")
            ->usingJoin('stats_visitors', 'visitorId', 'LEFT ')
            ->usingJoin('stats_user_agents', 'userAgentId', 'LEFT ')
            ->between('visitDate',$dateStart, $dateEnd)
            ->where('isBot',  0)
            ->getValue();
    }

    /**
     * Обновить статистику если были посещения
     */
    public function updateStatsCacheDaily(): void
    {
        /** найти самую последнюю дату */
        $firstDate = connect()->table('stats_cache_daily')->max('cacheDate', 'cacheDate')->get();
        $firstDate = $firstDate->cacheDate;

        if (empty($firstDate)) {
            $firstDate =  connect()->table('stats_visits')->min('visitDate', 'visitDate')->get();
            $firstDate = $firstDate->visitDate;
        }

        /** нахождение общего количества дней */
        $totalDays = (int)((strtotime(adjustTime(gmNow())) - strtotime($firstDate)) / (60 * 60 * 24));

        /** запрашивать данные за каждый день */
        if(!empty($firstDate)) {
            for ($i = 0; $i <= $totalDays; $i++) {
                $cacheDay = array();
                $cacheDay['cacheDate'] = date('Y-m-d', strtotime("$firstDate $i days"));

                $startDate = adjustTime($cacheDay['cacheDate'], true);
                $endDate = date('Y-m-d H:i:s', strtotime("$startDate +1 day -1 second"));

                /** новые и вернувшиеся посетители */
                $visitors = $this->visitors($startDate, $endDate);
                $cacheDay['newVisitors'] = empty($visitors[0]) ? 0 : $visitors[0];
                $cacheDay['retVisitors'] = empty($visitors[1]) ? 0 : $visitors[1];

                /** посещения по типу */
                $visits = $this->visits($startDate, $endDate);
                $cacheDay['sectionVisits'] = empty($visits[2]) ? 0 : $visits[2];
                $cacheDay['articleVisits'] = empty($visits[3]) ? 0 : $visits[3];
                $cacheDay['productVisits'] = empty($visits[9]) ? 0 : $visits[9];
                $cacheDay['searchVisits'] = empty($visits[4]) ? 0 : $visits[4];
                $cacheDay['errorVisits'] = empty($visits[6]) ? 0 : $visits[6];
                $cacheDay['rssVisits'] = empty($visits[7]) ? 0 : $visits[7];


                /** посещения по времени создания страницы */
                $genTimes = $this->genTimes($startDate, $endDate);
                $cacheDay['pageGenTime100'] = empty($genTimes[0]) ? 0 : $genTimes[0];
                $cacheDay['pageGenTime250'] = empty($genTimes[1]) ? 0 : $genTimes[1];
                $cacheDay['pageGenTime500'] = empty($genTimes[2]) ? 0 : $genTimes[2];
                $cacheDay['pageGenTime1000'] = empty($genTimes[3]) ? 0 : $genTimes[3];
                $cacheDay['pageGenTime2000'] = empty($genTimes[4]) ? 0 : $genTimes[4];
                $cacheDay['pageGenTimeMore'] = empty($genTimes[5]) ? 0 : $genTimes[5];

                connect()->table('stats_cache_daily')->replace($cacheDay);
            }
        }
    }


    /**
     * новые и вернувшиеся посетители
     *
     *
     * @param $dateStart
     * @param $dateEnd
     * @return array
     */
    private function visitors($dateStart, $dateEnd): ?array
    {

        return connect()->table('stats_visits')
            ->usingJoin('stats_visitors', 'visitorId', 'LEFT ')
            ->usingJoin('stats_user_agents', 'userAgentId', 'LEFT ')
            ->select("TO_DAYS('$dateStart') - TO_DAYS(firstVisitOn) >= 1 AS isReturning")
            ->between('visitDate',$dateStart, $dateEnd)
            ->where('isBot',0)
            ->count('DISTINCT visitorId', 'totalVisitors')
            ->groupBy('isReturning')
            ->indexKey('isReturning')
            ->valueKey('totalVisitors')
            ->getAllIndexes();
    }

    /**
     * посещения по типу
     *
     * @param $dateStart
     * @param $dateEnd
     * @return array|null
     */
    private function visits($dateStart, $dateEnd): ?array
    {
        return connect()->table('stats_visits')->select('typeId')
            ->usingJoin('stats_visitors', 'visitorId','LEFT ')
            ->usingJoin('stats_user_agents', 'userAgentId', 'LEFT ')
             ->count('*','totalVisits')
             ->between('visitDate',$dateStart, $dateEnd)
             ->where('isBot', 0)
             ->groupBy('typeId')
            ->indexKey('typeId')
            ->valueKey('totalVisits')
            ->getAllIndexes();
    }


    /**
     * посещения по времени создания страницы
     *
     * @param $dateStart
     * @param $dateEnd
     * @return array
     */
    private function genTimes($dateStart, $dateEnd): ?array
    {
        return connect()->table('stats_visits')
            ->select('INTERVAL(loadTime * 1000, 100, 250, 500, 1000, 2000) AS pgtGroup')
            ->count( '*', 'totalVisits')
            ->between('visitDate', $dateStart, $dateEnd)
            ->groupBy('pgtGroup')
            ->indexKey('pgtGroup')
            ->valueKey('totalVisits')
            ->getAllIndexes();

    }

}