<?php

class ModConsultantsHelper
{

    public static function getConsultants($params)
    {


        // select consultans id that do atleast 1 consultation
        $db = JFactory::getDbo();
        $query = $db->getQuery(true);

        $query
            ->select('distinct co.id as consultantid')
            ->from($db->quoteName('#__reservation_session', 's'))
            ->join('INNER', $db->quoteName('#__reservation_plan', 'p') . ' ON ' . $db->quoteName('s.planid') . ' = ' . $db->quoteName('p.id'))
            ->join('INNER', $db->quoteName('#__reservation_consultant', 'co') . ' ON ' . $db->quoteName('p.consultantid') . ' = ' . $db->quoteName('co.id'))
            ->where($db->quoteName('s.pay').'= 1');

        $db->setQuery($query);


        $results = $db->loadColumn();



        // select consultants info that their ids are in $results

        if($results)
        {
            $db = JFactory::getDbo();
            $query = $db->getQuery(true);

            $query
                ->select($db->quoteName(array('a.id','a.alt','a.image', 'b.name','c.title'),array('consultantid','alt','image','consultantname','specialty')))
                ->from($db->quoteName('#__reservation_consultant', 'a'))
                ->join('INNER', $db->quoteName('#__users', 'b') . ' ON ' . $db->quoteName('a.userid') . ' = ' . $db->quoteName('b.id'))
                ->join('INNER', $db->quoteName('#__categories', 'c') . ' ON ' . $db->quoteName('a.catid') . ' = ' . $db->quoteName('c.id'))
                ->where($db->quoteName('a.published') . ' = 1 ')
                ->where($db->quoteName('a.id') . ' IN ('.implode(',',$results).') ')
                ->order('RAND() LIMIT 8');

            $db->setQuery($query);


            $consultantInfo = $db->loadObjectList();
            $consultantid=[];

            foreach ($consultantInfo as $item) {
                $consultantid[]= $item->consultantid;
            }


            // select number of sessions performed by consultants
            $db = JFactory::getDbo();
            $query = $db->getQuery(true);

            $query
                ->select('co.id as consultantid , COUNT(consultantid) as count')
                ->from($db->quoteName('#__reservation_session', 's'))
                ->join('INNER', $db->quoteName('#__reservation_plan', 'p') . ' ON ' . $db->quoteName('s.planid') . ' = ' . $db->quoteName('p.id'))
                ->join('INNER', $db->quoteName('#__reservation_consultant', 'co') . ' ON ' . $db->quoteName('p.consultantid') . ' = ' . $db->quoteName('co.id'))
                ->where($db->quoteName('s.pay').'= 1')
                ->where($db->quoteName('co.id').' IN ('.implode(',',$consultantid).')')
                ->group('consultantid');



            $db->setQuery($query);


            $sessionCount = $db->loadAssocList('consultantid');

            $commentInfo= self::commentInfo($consultantid);


            return [$consultantInfo,$sessionCount, $commentInfo];
        }

        return [0,0];

    }

    public static function commentInfo($consultantid)
    {
        $db= JFactory::getDbo();
        $query= $db->getQuery(true)
            ->select('consultantid, count(consultantid) as count, sum(rate) as rate')
            ->from($db->quoteName('#__reservation_comment','cm'))
            ->where($db->quoteName('cm.consultantid'). 'IN ('.implode(',',$consultantid).')')
            ->where($db->quoteName('cm.published'). '= 1')
            ->group('consultantid');

        $db->setQuery($query);
        $result= $db->loadObjectList('consultantid');
        return $result;
    }
}