<?php

class ModChattersHelper
{

    public static function getChatters($params,$userid)
    {

        $db= JFactory::getDbo();

        $query = $db->getQuery(true)
            ->select($db->quoteName('id'))
            ->from($db->quoteName('#__reservation_sick'))
            ->where($db->quoteName('userid').'='.$userid);
        $db->setQuery($query);
        $res= $db->loadResult();

        if ($res)
        {
            $group= 'sick';
            $join= 'co.userid';
            $where= 'si.userid';
        }

        else
        {
            $group= 'consultant';
            $join= 'si.userid';
            $where= 'co.userid';
        }



        $db= JFactory::getDbo();

        $query = $db->getQuery(true)
            ->select($db->quoteName(array('u.id','u.name','p.id','se.pay','p.price','se.created','se.id','se.finish'),array('uid','name','pid','pay','price','created','seid','finish')))
            ->from($db->quoteName('#__reservation_session','se'))
            ->join('INNER',$db->quoteName('#__reservation_sick','si').'on ('.$db->quoteName('se.sickid').'='.$db->quoteName('si.id').')')
            ->join('INNER',$db->quoteName('#__reservation_plan','p').'on ('.$db->quoteName('se.planid').'='.$db->quoteName('p.id').')')
            ->join('INNER',$db->quoteName('#__reservation_consultant','co').'on ('.$db->quoteName('p.consultantid').'='.$db->quoteName('co.id').')')
            ->join('INNER',$db->quoteName('#__users','u').'on ('.$db->quoteName($join).'='.$db->quoteName('u.id').')')
            ->where($db->quoteName($where).'='.$userid)
            ->where($db->quoteName('se.pay').'=1');


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

        
        $seidList=[];
        foreach ($res as $item) {
            $seidList[]= $item->seid;
        }
        $lastMessage= self::getLastMessage($seidList);


        $db= JFactory::getDbo();
        $query= $db->getQuery(true)
            ->select($db->quoteName(array('id','seid','token')))
            ->from($db->quoteName('#__reservation_comment','cm'));

        $db->setQuery($query);
        $res2 = $db->loadAssocList('seid');
        
        $messageCount= self::getMessageCount($userid);

        return [$res,$res2,$group, $messageCount, $lastMessage];
    }

    public function getMessageCount($userid)
    {
        $db= JFactory::getDbo();
        $query= $db->getQuery(true)
            ->select('m.from as sender , m.to as receiver, m.seid as seid, count(m.from) as count, m.message as message')
            ->from($db->quoteName('#__reservation_message','m'))
            ->where($db->quoteName('m.to').'='. $db->quote($userid))
            ->where($db->quoteName('m.read').'= 0')
            ->group('sender, receiver, seid');
        
        $db->setQuery($query);
        $result = $db->loadObjectList('sender');
        return $result;
    }
    public function getLastMessage($seidList)
    {

        if (empty($seidList))
            return [];

        $db= JFactory::getDbo();
        
        $subQuery = $db->getQuery(true);
        $subQuery->select('max(id)')
            ->from($db->quoteName('#__reservation_message','m'))
            ->where($db->quoteName('m.seid') . ' IN('.implode(',',$seidList).')' )
            ->group('m.seid');


        $query= $db->getQuery(true)
            ->select('m.seid as seid , m.message as message')
            ->from($db->quoteName('#__reservation_message','m'))
            ->where($db->quoteName('m.id').'IN('.$subQuery.')');


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

}