<?php

namespace App\Model;

use Nette\Database\SqlLiteral;
use Nette\Database\Table\Selection;

class ArticleManager extends BaseManager
{
    function getPublicArticles()
    {
        return $this->database->table('articles')
            ->select('articles.*, users.id AS user_id')
            ->where('articles.deleted_at IS NULL')
            ->where('articles.created_at <= NOW()')
            ->where('status', 'APPROVED')
            ->order('articles.created_at DESC');
    }

    function getAllArticles()
    {
        return $this->database->table('articles')
            ->select('articles.*, users.id AS user_id')
            ->where('articles.deleted_at IS NULL')
            ->order('articles.created_at DESC');
    }

    function getPostsCount(Selection $selection): int
    {
        return $selection
            ->count();
    }

    function getTopArticles()
    {
        return $this->database->table('articles')->limit(5)
            ->where('status', 'APPROVED')
            ->order('usage_count DESC');
    }

    function getRecentPosts()
    {
        return $this->database->table('articles')->limit(5)
            ->where('status', 'APPROVED')
            ->order('created_at DESC');
    }

    function getApprovedArticle($postId)
    {
        return $this->database->table('articles')
            ->where('status', 'APPROVED')
            ->where('id', $postId);
    }

    function getArticle($postId)
    {
        return $this->database->table('articles')
            ->where('id', $postId)->fetch();
    }

    function getComments($articleId)
    {
        return $this->database->fetchAll('SELECT comments.id AS id, text, likes, article_id, parent_comment_id, username AS author, comments.created_at AS created_at
                                              FROM comments
                                              LEFT JOIN users ON users.id = comments.user_id
                                              WHERE article_id = ?', $articleId);
    }

    function getFiles($articleId)
    {
        return $this->database->table('files')
                ->where('article_id', $articleId);
    }

    function getFile($articleId, $fileId)
    {
        return $this->database->table('files')
                ->where('id', $fileId)
                ->where('article_id', $articleId)
                ->fetch();
    }

    function getTopics()
    {
        return $this->database->table('topics')
                //->order('topic')
                ->where('is_archived = 0');
    }

    function userSawArticle($userId, $articleId): void
    {
        $seenRow = $this->database->table('user_saw_article')
                        ->where('user_id', $userId)
                        ->where('article_id', $articleId)->fetch();

        if ($seenRow != null)
            return;

        $this->database->table('user_saw_article')
            ->insert(
                [
                    'user_id' => $userId,
                    'article_id' => $articleId,
                    'display_time' => new SqlLiteral('NOW()')
                ]
            );

        // Cache value
        $this->database->table('articles')
            ->where('id', $articleId)
            ->update([
                'usage_count' => $this->getUsageCount($articleId)
            ]);
    }

    function getUsageCount($articleId): int
    {
        return $this->database->table('user_saw_article')
            ->where('article_id', $articleId)->count();
    }
}