<?php

class WordCloudController extends BaseController
{
    private $limit = 10;
    private $limitQ = "";
    public function get() {
        $getParams = $this->request->get();
        $getParams['type'] = array_key_exists('type', $getParams) ? $getParams['type'] : 'ALL';
        $getParams['date'] = array_key_exists('date', $getParams) ? $getParams['date'] : null;
        $getParams['limit'] = $this->getDefaultLimit($getParams);

        $params = $this->validate(
            $getParams,
            [
                'type' => ['validators' => [[
                    'name' => 'inclusion_in',
                    'params' => ['ALL', 'DAY', 'WEEK', 'MONTH', 'YEAR']
                ]]],
                'date' => ['validators' => ['required']],
                'limit' => ['validators' => ['digit']]
            ]
        );

        if($params['limit'] != null) {
            $this->limit = $params['limit'];
            $this->limitQ = 'LIMIT ' . $params['limit'];
        }

        $result = [];

        if($params['type'] == 'ALL') {
            $result = $this->getAllWordCloudData();
        }
        else if($params['type'] == 'DAY') {
            $result = $this->getDayWordCloudData($params['date']);
        }
        else if($params['type'] == 'WEEK') {
            $result = $this->getWeekWordCloudData($params['date']);
        }
        else if($params['type'] == 'MONTH') {
            $result = $this->getMonthWordCloudData($params['date']);
        }
        else if($params['type'] == 'YEAR') {
            $result = $this->getYearWordCloudData($params['date']);
        }


        return new SimpleResponse($result);
    }

    private function getAllWordCloudData()
    {
        $q = WordCloud::query()
            ->columns('word, SUM(word_count) AS word_count')
            ->where("category = 'DISKUSI_PENDIDIKAN'")
            ->groupBy('word')
            ->orderBy('SUM(word_count) DESC');
        if($this->limit != null) {
            $q->limit($this->limit);
        }
        $result = $q->execute();
        return $result;
    }

    private function getDayWordCloudData($date)
    {
        return $this->getWordCloudDataByDateFormat($date, 'YYYY-MM-DD');
    }

    private function getWeekWordCloudData($date)
    {
        $q = "
            SELECT 
                (date '$date' - INTERVAL '7 day') AS start_date,
                (date '$date' - INTERVAL '1 day') AS end_date,
                word, 
                SUM(word_count) AS word_count
            FROM word_cloud
            WHERE date >= (date '$date' - INTERVAL '7 day')
                AND date <= (date '$date' - INTERVAL '1 day')
                AND category = 'DISKUSI_PENDIDIKAN'
            GROUP BY word
            ORDER BY SUM(word_count) DESC
            $this->limitQ
        ";

        return $this->rawQuery($q);
    }

    private function getMonthWordCloudData($date)
    {
        return $this->getWordCloudDataByDateFormat($date, 'YYYY-MM');

    }

    private function getYearWordCloudData($date)
    {
        return $this->getWordCloudDataByDateFormat($date, 'YYYY');
    }

    private function getWordCloudDataByDateFormat($date, $dateFormat) {

        $q = "
        SELECT word, SUM(word_count) AS word_count
        FROM word_cloud
        WHERE to_char(date, '$dateFormat') = to_char('$date'::timestamp, '$dateFormat')
        GROUP BY word
        ORDER BY SUM(word_count) DESC
        $this->limitQ
        ";
        return $this->rawQuery($q);
    }
}