<?php

namespace DetailTask;

use ApiTracker;

class WordCloudDiskusiPendidikanTask extends \ScheduledTask
{
    private $wordCloudTracker;
    private $lastUpdate;
    private $where = "";
    private $tempResult;

    /**
     * @return void
     * No logger
     */
    public function processDiskusiPendidikanWordCloud() {
        $this->db->begin();
        try {
            $this->getWordCloudTracker();
            $this->getSplittedWordsFromDiskusiPendidikanAndSaveToWordCloudTableUsingQuery();
            $this->saveLastDiskusiDateToTracker();
            $this->db->commit();
        }
        catch (\Exception $e){
            $this->db->rollback();
        }
    }

    private function getWordCloudTracker()
    {
        $this->wordCloudTracker = ApiTracker::findFirst([
            'conditions' => "name = ?0",
            'bind' => [
                'WORD_CLOUD_DISKUSI_PENDIDIKAN'
            ]
        ]);

        $this->lastUpdate = null;
        $this->where = "";

        if(!empty($this->wordCloudTracker)) {
            $this->lastUpdate = $this->wordCloudTracker->last_update_date;
            $this->where = " AND d.created_at > '" . $this->lastUpdate . "'";
        }
        else {
            $this->wordCloudTracker = new ApiTracker();
            $this->wordCloudTracker->name = 'WORD_CLOUD_DISKUSI_PENDIDIKAN';
        }

    }

    private function getSplittedWordsFromDiskusiPendidikanAndSaveToWordCloudTableUsingQuery()
    {
        $q = "
        INSERT INTO word_cloud (category, date, word, word_count)
        SELECT 'DISKUSI_PENDIDIKAN', day, word, SUM(word_count) AS word_count
        FROM (
          SELECT DATE_TRUNC('day', subquery.created_at) AS day,
                 subquery.word,
                 COUNT(*) AS word_count
          FROM (
            SELECT regexp_split_to_table(regexp_replace(regexp_replace(lower(content), '<[^>]+>(?!\\\\s*<)|&nbsp;', ' ', 'g'), '[^\\w\\s]', '', 'g'), E'\\\\s+') AS word,
                   d.created_at
            FROM diskusi d
            JOIN kategori_diskusi kd ON d.kategori_diskusi_id = kd.id
            WHERE kd.produk_id = '2' $this->where
          ) subquery
          LEFT JOIN word_cloud_stop_words ON lower(subquery.word) = lower(word_cloud_stop_words.word)
          WHERE word_cloud_stop_words.word IS NULL AND subquery.word <> ''
          GROUP BY DATE_TRUNC('day', subquery.created_at), subquery.word
        ) subquery
        GROUP BY day, word;
        ";

        $this->rawQuery($q);
    }

    private function saveLastDiskusiDateToTracker()
    {
        $q = "
                SELECT d.created_at
                FROM diskusi d
                JOIN kategori_diskusi kd ON d.kategori_diskusi_id = kd.id
                WHERE kd.produk_id = '2' $this->where
                ORDER BY d.created_at DESC LIMIT 1
            ";
        $qResult = $this->rawQuery($q);
        if(!empty($qResult)) {
            $this->lastUpdate = $qResult[0]['created_at'];
            $this->wordCloudTracker->last_update_date = $this->lastUpdate;
            $this->wordCloudTracker->save();
        }
    }
}