<?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,
          ]);
      }
  }