<?php


namespace AcyChecker\Classes;


use AcyChecker\Libraries\AcycClass;
use AcyCheckerCmsServices\Database;

class TestClass extends AcycClass
{
    const STATUS = [
        'tested' => 1,
        'progress' => 0,
    ];
    const NO_STATUS = 'all';

    public function __construct()
    {
        parent::__construct();

        $this->table = 'test';
        $this->pkey = 'email';
    }

    public function getMatchingElements(&$settings)
    {
        $query = 'SELECT * FROM #__acyc_test AS test';
        $queryCount = 'SELECT COUNT(*) FROM #__acyc_test AS test';

        $where = $this->buildFiltersFromSettings($settings);
        $query .= $where;
        $queryCount .= $where;

        $query .= ' ORDER BY test.date DESC';

        $elements = Database::loadObjectList($query, 'email', $settings['offset'], $settings['per_page']);
        $total = Database::loadResult($queryCount);

        if (empty(count($elements)) && !empty($total)) {
            $settings['offset'] = 0;
            $settings['page'] = 1;
            $elements = Database::loadObjectList($query, 'email', $settings['offset'], $settings['per_page']);
        }

        return [
            'elements' => $elements,
            'total' => $total,
        ];
    }

    private function buildFiltersFromSettings($settings)
    {
        $filters = [];

        if (isset($settings['current_status']) && $settings['current_status'] !== self::NO_STATUS && in_array($settings['current_status'], array_keys(self::STATUS))) {
            $filters[] = 'test.status = '.intval(self::STATUS[$settings['current_status']]);
        }

        if (!empty($settings['search'])) {
            $search = Database::escapeDB('%'.$settings['search'].'%');
            $filters[] = 'test.email LIKE '.$search.' OR test.final_status LIKE '.$search.' OR test.raw_result LIKE '.$search;
        }

        return empty($filters) ? '' : ' WHERE ('.implode(') AND (', $filters).')';
    }

    public function getStatusNumber($status, $settings)
    {
        $query = 'SELECT COUNT(*) FROM #__acyc_test AS test';

        $settings['current_status'] = $status;

        $query .= $this->buildFiltersFromSettings($settings);

        return Database::loadResult($query);
    }

    public function getTotalTest()
    {
        return Database::loadResult('SELECT COUNT(*) FROM #__acyc_test');
    }

    public function getTotalDisposable()
    {
        return Database::loadResult('SELECT COUNT(*) FROM #__acyc_test WHERE disposable = 1');
    }

    public function getTotalFree()
    {
        return Database::loadResult('SELECT COUNT(*) FROM #__acyc_test WHERE free = 1');
    }

    public function getTotalAcceptAll()
    {
        return Database::loadResult('SELECT COUNT(*) FROM #__acyc_test WHERE accept_all = 1');
    }

    public function getTotalRoleEmail()
    {
        return Database::loadResult('SELECT COUNT(*) FROM #__acyc_test WHERE role_email = 1');
    }

    public function getBlockedUsersRepartition()
    {
        $date = Database::escapeDB(date('Y-m-d', strtotime(date('Y-m-1'))));

        return Database::loadObjectList(
            'SELECT block_reason, COUNT(block_reason) AS value FROM #__acyc_test WHERE block_reason IS NOT NULL AND `date` > '.$date.' GROUP BY block_reason'
        );
    }

    public function getNumberOfTestByDay()
    {
        $date = Database::escapeDB(date('Y-m-d', strtotime(date('Y-m-1'))));

        return Database::loadObjectList(
            'SELECT COUNT(*) AS count, DATE_FORMAT(`date`, "%Y-%m-%d") AS date_formated FROM #__acyc_test WHERE `date` > '.$date.' GROUP BY date_formated',
            'date_formated'
        );
    }

    public function deleteAllTests()
    {
        $query = 'DELETE FROM #__acyc_test WHERE status = 1';

        return Database::query($query);
    }
}