<?php
  
  namespace FootyRoom\Queries;
  
  use MongoDB\BSON\UTCDateTime;
  use FootyRoom\Support\MongoClient;
  use FootyRoom\Support\Utils\Permutator;
  use FootyRoom\Support\AutoMapper;
  use Illuminate\Database\Connection;
  use FootyRoom\Queries\Post\MatchPost;
  use Illuminate\Database\Query\Builder;
  
  class MatchPostQuery
  {
      /**
       * @var \Illuminate\Database\Connection
       */
      protected $mysql;
  
      /**
       * @var \FootyRoom\Support\MongoClient
       */
      protected $mongo;
  
      /**
       * Constructor.
       *
       * @param \Illuminate\Database\Connection $mysql
       * @param \FootyRoom\Support\MongoClient $mongo
       */
      public function __construct(Connection $mysql, MongoClient $mongo)
      {
          $this->mysql = $mysql;
          $this->mongo = $mongo->footyroom;
      }
  
      /**
       * Common query wrapper for posts that takes subQuery for needed posts.
       *
       * @param string $subQuery
       * @param array $params
       *
       * @return \FootyRoom\Queries\Post\MatchPost[]
       */
      protected function select($subQuery, $params = [])
      {
          $postDtos = $this->mysql
  
          ->select(
              'SELECT
  			 p.id,
  			 p.match_id AS matchId,
  			 DATE_FORMAT(p.post_date, "%Y-%m-%dT%TZ") AS date,
               DATE_FORMAT(p.datetime, "%Y-%m-%dT%TZ") AS matchDate,
  			 p.post_title as title,
  			 p.post_name as name,
  			 p.comment_count as commentCount,
  			 p.view_count as viewCount,
  			 p.sticky,
  			 pm.meta_value AS thumbnailUrl,
  			 t1.team_name as homeTeamName,
  			 t2.team_name as awayTeamName,
  			 s.id AS competitionId,
  			 s.name AS competitionName
  			 FROM ('.$subQuery.') as p
  			 LEFT JOIN wp_postmeta AS pm ON pm.meta_key = "pft_widescreen" AND pm.post_id = p.id
  			 LEFT JOIN stages AS s ON s.tree = SUBSTRING_INDEX(p.stage_tree, ",", 1) AND s.type = "competition"
  			 JOIN teams AS t1 ON t1.team_id = p.home_team
  			 JOIN teams AS t2 ON t2.team_id = p.away_team',
               $params
          );
  
          if (!$postDtos) {
              return [];
          }
  
          $posts = [];
  
          foreach ($postDtos as $post) {
              $posts[] = AutoMapper::map($post, MatchPost::class);
          }
  
          return $posts;
      }
  
      /**
       * Finds match posts.
       *
       * @param int $offset
       * @param int $limit
       *
       * @return \FootyRoom\Queries\Post\MatchPost[]
       */
      public function find($offset = 0, $limit = 24)
      {
          $subQuery = $this->mysql
  
          ->table($this->mysql->raw('wp_posts AS p FORCE KEY(type_status_date)'))
          ->select('*')
          ->join('wp_postmeta AS pm', 'p.id', '=', 'pm.post_id')
          ->join('matches AS m', 'm.match_id', '=', 'pm.meta_value')
          ->where('p.post_status', '=', 'publish')
          ->where('p.post_type', '=', 'post')
          ->where('pm.meta_key', '=', 'match_id')
          ->orderBy('p.post_date', 'desc')
          ->offset($offset)
          ->limit($limit);
  
          return $this->select($subQuery->toSql(), $subQuery->getBindings());
      }
  
      /**
       * Finds match posts of specific stage tree.
       *
       * @param string $tree
       * @param int $offset
       * @param int $limit
       *
       * @return \FootyRoom\Queries\Post\MatchPost[]
       */
      public function findByStageTree($tree, $offset = 0, $limit = 24)
      {
          return $this->select(
              'SELECT *
  			 FROM wp_posts AS p
  			 JOIN wp_postmeta AS pm ON p.id = pm.post_id AND pm.meta_key = "match_id"
  			 JOIN matches AS m ON CAST(m.match_id AS CHAR) = pm.meta_value
  			 WHERE p.post_status = "publish" AND p.post_type = "post" AND (m.stage_tree LIKE ? OR m.stage_tree = ?)
  			 ORDER BY p.post_date DESC
  			 LIMIT ?, ?',
              [$tree.',%', $tree, $offset, $limit]
          );
      }
  
      /**
       * Find legendary matches.
       *
       * @param int $offset
       * @param int $limit
       *
       * @return \FootyRoom\Queries\Post\MatchPost[]|null
       */
      public function findLegendary($offset = 0, $limit = 24): ?array
      {
          // Request legendary matches from MongoDb.
          $cursor = $this->mongo->matches->find(
              [
                  'legendary.createdAt' => ['$gt' => new UTCDateTime(0)],
                  'posts.review.status' => 'publish',
              ],
              [
                  'limit' => $limit,
                  'skip' => $offset,
                  'sort' => ['legendary.createdAt' => -1],
                  'projection' => [
                      'posts.review.id' => 1,
                      'datetime' => 1,
                      'legendary.stageName' => 1,
                      'legendary.createdAt' => 1
                  ],
              ]
          );
  
          // Iterate over them to get post ids of the matches.
          $matches = [];
          $postIds = [];
  
          foreach ($cursor as $match) {
              $postIds[] = $match['posts']['review']['id'];
              $matches[] = $match;
          }
  
          if (!$postIds) {
              return null;
          }
  
          // Get posts from MySql not sorted in any way.
          $unsortedPosts = $this->select(
              'SELECT *
  			 FROM wp_posts AS p
  			 JOIN wp_postmeta AS pm ON p.id = pm.post_id AND pm.meta_key = "match_id"
  			 JOIN matches AS m ON m.match_id = pm.meta_value
  			 WHERE p.ID IN(?'.str_repeat(',? ', count($postIds) - 1).')',
               $postIds
          );
  
          $indexedPosts = [];
  
          // Index all posts by id.
          foreach ($unsortedPosts as $post) {
              $indexedPosts[$post->id] = $post;
          }
  
          $posts = [];
  
          // Now put some data from matches into posts and sort by createdAt.
          foreach ($matches as $match) {
              $post = $indexedPosts[$match['posts']['review']['id']];
  
              $post->matchDate = $match['datetime']->toDateTime()->format('Y-m-d H:m:s');
              $post->date = $match['legendary']['createdAt']->toDateTime()->format('Y-m-d H:m:s');
  
              $post->stageName = isset($match['legendary']['stageName']) ?
                  $match['legendary']['stageName'] : null;
  
              $posts[] = $post;
          }
  
          return $posts;
      }
  
      /**
       * Finds match posts by title.
       *
       * @param string $q
       * @param int $offset
       * @param int $limit
       *
       * @return \FootyRoom\Queries\Post\MatchPost[]
       */
      public function findByTitle($q, $offset, $limit)
      {
          $subQuery = $this->mysql
  
          ->table($this->mysql->raw('wp_posts AS p FORCE KEY(post_date)'))
          ->select('*')
          ->join('wp_postmeta AS pm', 'p.id', '=', 'pm.post_id')
          ->join('matches AS m', 'm.match_id', '=', 'pm.meta_value')
          ->where('p.post_status', '=', 'publish')
          ->where('p.post_type', '=', 'post')
          ->where('pm.meta_key', '=', 'match_id')
          ->orderBy('p.post_date', 'desc')
          ->offset($offset)
          ->limit($limit);
  
          // Remove team separators such as 'v', 'vs', ':', '-', etc.
          $q = str_replace([' v ', ' vs ', ' v. ', ' vs. ', ' versus ', ':', '-'], ' ', $q);
  
          $this->textSearch($q, 'p.post_title', $subQuery);
  
          return $this->select($subQuery->toSql(), $subQuery->getBindings());
      }
  
      /**
       * Finds match posts by post slug.
       *
       * @param string $slug
       *
       * @return \FootyRoom\Queries\Post\MatchPost|null
       */
      public function findBySlug($slug)
      {
          $subQuery = $this->mysql
  
          ->table('wp_posts AS p')
          ->select('*')
          ->join('wp_postmeta AS pm', 'p.id', '=', 'pm.post_id')
          ->join('matches AS m', 'm.match_id', '=', 'pm.meta_value')
          ->where('p.post_status', '=', 'publish')
          ->where('pm.meta_key', '=', 'match_id')
          ->where('p.post_name', '=', $slug);
  
          $post = $this->select($subQuery->toSql(), $subQuery->getBindings());
  
          if (isset($post[0])) {
              return $post[0];
          } else {
              return null;
          }
      }
  
      /**
       * Finds match post by its match id.
       *
       * @param int $matchId
       *
       * @return \FootyRoom\Queries\Post\MatchPost|null
       */
      public function findByMatchId($matchId)
      {
          $post = $this->select(
              'SELECT *
  			 FROM wp_posts AS p
  			 JOIN wp_postmeta AS pm ON p.id = pm.post_id AND pm.meta_key = "match_id" AND pm.meta_value = ?
  			 JOIN matches AS m ON m.match_id = pm.meta_value
  			 LIMIT 0, 1',
              [(string) $matchId]
          );
  
          if (isset($post[0])) {
              return $post[0];
          } else {
              null;
          }
      }
  
      /**
       * This function tries its best to simulate full text search without a real
       * full text capability on hand.
       *
       * @param string $q
       * @param string $field Field or column on which to perform search.
       * @param \Illuminate\Database\Query\Builder $query
       */
      protected function textSearch($q, $field, $query)
      {
          $allWords = explode(' ', $q);
  
          // Take only first 6 words to prevent performance problems with large word set.
          $words = array_slice($allWords, 0, 6);
  
          // If we have more than 4 words then we don't search all permutation of
          // string because it will take too long.
          if (count($words) > 4) {
              $query->where($field, 'LIKE', '%'.implode('%', $words).'%');
          } else {
              $query->where(function (Builder $query) use ($words, $field) {
                  $permutations = Permutator::permute($words);
  
                  foreach ($permutations as $permutation) {
                      $query->orWhere($field, 'LIKE', '%'.implode('%', $permutation).'%');
                  }
              });
          }
      }
  }