<?php

namespace FootyRoom\Queries;

use FootyRoom\Queries\Post\Post;
use FootyRoom\Support\AutoMapper;
use Illuminate\Database\DatabaseManager;

class PostQuery
{
    /**
     * @var \Illuminate\Database\DatabaseManager
     */
    protected $mysql;

    /**
     * Constructor.
     *
     * @param \Illuminate\Database\DatabaseManager $db
     */
    public function __construct(DatabaseManager $db)
    {
        $this->mysql = $db;
    }

    /**
     * Find post by its id.
     *
     * @param string $id
     *
     * @return object[]
     */
    public function findById($id)
    {
        return $this->mysql

        ->table('wp_posts')
        ->select([
            'id as id',
            'post_title as title',
            'post_name as slug'
        ])
        ->join('wp_term_relationships', 'object_id', '=', 'id')
        ->join('wp_terms', 'term_id', '=', 'term_taxonomy_id')
        ->where('id', '=', $id)
        ->where('wp_terms.slug', '=', 'lemix')
        ->where('post_status', '=', 'publish')
        ->first();
    }

    /**
     * Find post by its slug.
     *
     * @param string $slug
     *
     * @return object[]
     */
    public function findBySlug($slug)
    {
        return $this->mysql

        ->table('wp_posts')
        ->select([
            'id as id',
            'post_title as title',
        ])
        ->where('post_name', '=', $slug)
        ->first();
    }

    /**
     * Find meta.
     *
     * @param int $postId
     * @param string[] $metaKeys List of meta keys to return.
     *
     * @return string[string][int]
     */
    public function findMetaByPostId($postId, $metaKeys = null)
    {
        $query = $this->mysql

        ->table('wp_postmeta')
        ->select([
            'meta_key as key',
            'meta_value as value',
        ])
        ->where('post_id', '=', $postId);

        if ($metaKeys) {
            $query->whereIn('meta_key', $metaKeys);
        }

        $metaDtos = $query->get();

        $meta = [];

        foreach ($metaDtos as $metaDto) {
            $meta[$metaDto->key][] = $metaDto->value;
        }

        return $meta;
    }

    /**
     * Returns posts in the same category as given post id.
     *
     * @param int $postId
     * @param int $limit
     *
     * @return object[]
     */
    public function findRelated($postId, $limit)
    {
        return $this->mysql->select(
            'SELECT
                p.id,
                p.post_title as title,
                p.post_date as date,
                p.post_name as slug,
                pm.meta_value AS thumbnailUrl
             FROM wp_posts as p
             STRAIGHT_JOIN wp_term_relationships AS otr ON otr.object_id = ?
             JOIN wp_term_relationships AS rtr ON rtr.object_id = p.ID AND rtr.term_taxonomy_id = otr.term_taxonomy_id
             LEFT JOIN wp_postmeta AS pm ON pm.meta_key = "pft_widescreen" AND pm.post_id = p.id
             WHERE p.post_status = "publish" AND p.post_type = "post"
             HAVING p.ID != ?
             ORDER BY p.post_date DESC
             LIMIT 0, ?',
            [$postId, $postId, $limit]
        );
    }

    /**
     * Finds posts in specific categories.
     *
     * @param array   $categoryIds
     * @param int $offset
     * @param int $limit
     *
     * @return \FootyRoom\Queries\Post\Post[]
     */
    public function findByCategoryIds(array $categoryIds, $offset = 0, $limit = 24)
    {
        $postDtos = $this->mysql->select(
            'SELECT
                p.id,
                p.post_title as title,
                p.post_date as date,
                p.post_name as slug,
                p.sticky,
                p.comment_count as commentCount,
                p.view_count as viewCount,
                t.name as categoryName,
                pm.meta_value AS thumbnailUrl
             FROM wp_posts as p
             STRAIGHT_JOIN wp_term_relationships AS tr ON tr.object_id = p.ID AND tr.term_taxonomy_id IN(?)
             JOIN wp_terms AS t ON t.term_id = tr.term_taxonomy_id
             LEFT JOIN wp_postmeta AS pm ON pm.meta_key = "pft_widescreen" AND pm.post_id = p.id
             WHERE p.post_status = "publish" AND p.post_type = "post"
             ORDER BY p.post_date DESC
             LIMIT ?, ?',
            [implode(',', $categoryIds), $offset, $limit]
        );

        $posts = [];
        
        foreach ($postDtos as $post) {
            $posts[] = AutoMapper::map($post, Post::class);
        }

        return $posts;
    }

    /**
     * Returns view count of a post.
     *
     * @param int $postId
     *
     * @return int
     */
    public function getViewCount($postId)
    {
        return $this->mysql

        ->table('wp_posts')
        ->where('ID', '=', $postId)
        ->value('view_count');
    }

    /**
     * Increment view count.
     *
     * @param int $postId
     * @param int $value
     */
    public function incrementViewCount($postId, $value = 1)
    {
        $this->mysql

        ->table('wp_posts')
        ->where('ID', '=', $postId)
        ->increment('view_count', $value);
    }

    /**
     * Update data in wp_posts.
     *
     * @param int $postId
     * @param int $userId
     * @param int $commentId
     * @param date $commentDate
     */
    public function updateLastComment($postId, $userId, $commentId, $commentDate)
    {
        $this->mysql

        ->table('wp_posts')
        ->where('ID', '=', $postId)
        ->update([
            'last_comment_id' => $commentId,
            'last_comment_user_id' => $userId,
            'last_comment_date' => $commentDate,
        ]);
    }
}