<?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).'%');
                }
            });
        }
    }
}