<?php

namespace App\Repository;

use DateTime;
use Doctrine\ORM\EntityRepository;

/**
 * SubjectRepository.
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class StatisticsRepository extends EntityRepository
{
    public function getMembersDataWeekly()
    {
        return $this->getStatisticsDataWeekly('s.activeMembers');
    }

    public function getMembersDataDaily()
    {
        return $this->getStatisticsDataDaily('s.activeMembers');
    }

    public function getNewMembersDataWeekly()
    {
        return $this->getStatisticsDataWeekly('s.activeMembers');
    }

    public function getNewMembersDataDaily()
    {
        return $this->getStatisticsDataDaily('s.activeMembers');
    }

    public function getSentMessagesDataWeekly()
    {
        return $this->getStatisticsDataWeekly('s.messagesSent');
    }

    public function getSentMessagesDataDaily()
    {
        return $this->getStatisticsDataDaily('s.messagesSent');
    }

    public function getReadMessagesDataWeekly()
    {
        return $this->getStatisticsDataWeekly('s.messagesRead');
    }

    public function getReadMessagesDataDaily()
    {
        return $this->getStatisticsDataDaily('s.messagesRead');
    }

    public function getSentRequestsDataWeekly()
    {
        return $this->getRequestsDataWeekly('s.requestsSent');
    }

    public function getSentRequestsDataDaily()
    {
        return $this->getStatisticsDataDaily('s.requestsSent');
    }

    public function getAcceptedRequestsDataWeekly()
    {
        return $this->getRequestsDataWeekly('s.requestsAccepted');
    }

    public function getAcceptedRequestsDataDaily()
    {
        return $this->getStatisticsDataDaily('s.requestsAccepted');
    }

    public function getSentInvitationsDataWeekly()
    {
        return $this->getTripsDataWeekly('s.invitationsSent');
    }

    public function getSentInvitationsDataDaily()
    {
        return $this->getStatisticsDataDaily('s.invitationsSent');
    }

    public function getAcceptedInvitationsDataWeekly()
    {
        return $this->getTripsDataWeekly('s.invitationsAccepted');
    }

    public function getAcceptedInvitationsDataDaily()
    {
        return $this->getStatisticsDataDaily('s.invitationsAccepted');
    }

    public function getCreatedLegsDataWeekly()
    {
        return $this->getTripsDataWeekly('s.legsCreated');
    }

    public function getCreatedLegsDataDaily()
    {
        return $this->getStatisticsDataDaily('s.legsCreated');
    }

    private function getStatisticsDataWeekly($column)
    {
        $result = $this->createQueryBuilder('s')
            ->select('MAX(' . $column . ') AS count, YEARWEEK(s.created) AS week')
            ->groupBy('week')
            ->getQuery()
            ->getResult();

        return $result;
    }

    private function getStatisticsDataDaily($column)
    {
        $result = $this->createQueryBuilder('s')
            ->select($column . ' AS count, s.created AS day')
            ->where('s.created >= :two_months_ago')
            ->setParameter('two_months_ago', (new DateTime())->modify('-2months'))
            ->getQuery()
            ->getResult();

        return $result;
    }

    /**
     * Requests were added April, 2019 so data can only exist after that.
     */
    private function getRequestsDataWeekly($column)
    {
        $result = $this->createQueryBuilder('s')
            ->select('MAX(' . $column . ') AS count, YEARWEEK(s.created) AS week')
            ->where('s.created >= :firstRequest')
            ->setParameter('firstRequest', new DateTime('2019-04-23'))
            ->groupBy('week')
            ->getQuery()
            ->getResult();

        return $result;
    }

    /**
     * Trips were added June, 2022 so data can only exist after that.
     */
    private function getTripsDataWeekly($column)
    {
        $result = $this->createQueryBuilder('s')
            ->select('MAX(' . $column . ') AS count, YEARWEEK(s.created) AS week')
            ->where('s.created >= :firstRequest')
            ->setParameter('firstRequest', new DateTime('2022-06-16'))
            ->groupBy('week')
            ->getQuery()
            ->getResult();

        return $result;
    }
}
