<?php

use Joomla\CMS\Factory;
use Joomla\CMS\MVC\Model\ListModel;

defined('_JEXEC') or die('Restricted access');

class guruAdminModelgurureviews extends ListModel
{
    protected function populateState($ordering = null, $direction = null)
    {
        parent::populateState($ordering, $direction);

        $app = Factory::getApplication();
        $course_search = $app->getUserStateFromRequest('com_guru.reviews.course_search', 'course_search');
        $report_type = $app->getUserStateFromRequest('com_guru.reviews.report_type', 'report_type', '-1');
        $review_state = $app->getUserStateFromRequest('com_guru.reviews.review_state', 'review_state', '-1');

        $this->setState('course_search', trim($course_search));
        $this->setState('report_type', trim($report_type));
        $this->setState('review_state', trim($review_state));
    }

    public function getListQuery()
    {
        $db = Factory::getDbo();

        $query = $db->getQuery(true)
            ->select([
                'a.*',
                'b.name AS course_name',
                'COALESCE(SUM(CASE WHEN c.resolved = 0 THEN 1 ELSE 0 END), 0) AS num_report',
                'CONCAT(d.firstname, " ", d.lastname) as reviewer',
            ])
            ->from('#__guru_review a')
            ->innerJoin('#__guru_program b ON b.id = a.course_id')
            ->innerJoin('#__guru_customer d on d.id = a.user_id')
            ->leftJoin('#__guru_review_report c on c.review_id = a.id')
            ->group('a.id')
            ->order('a.id DESC');

        $course_search = $this->getState('course_search');
        if ($course_search) {
            $query->where('b.name LIKE ' . $db->q("%$course_search%"));
        }

        $report_type = $this->getState('report_type', -1);
        if ($report_type == 0) {
            $query->where('c.report_type IN (1,2,3)');
            $query->where('c.resolved = 0');
        } elseif ($report_type != -1) {
            $query->where('c.report_type = ' . $db->q($report_type));
            $query->where('c.resolved = 0');
        }

        $review_state = $this->getState('review_state', -1);
        if ($review_state != -1) {
            $query->where('a.state = ' . $db->q($review_state));
        }

        return $query;
    }
}
