<?php

namespace FootyRoom\Queries;

use DateTime;
use FootyRoom\Queries\Match\Match;
use FootyRoom\Queries\Match\Team;
use FootyRoom\Support\AutoMapper;
use FootyRoom\Support\MongoClient;
use Illuminate\Database\DatabaseManager;
use MongoDB\BSON\UTCDateTime;
use MongoDB\BSON\Regex as MongoRegex;

class MatchQuery
{
    /**
     * @var \Illuminate\Database\Connection
     */
    protected $mysql;

    /**
     * @var \FootyRoom\Support\MongoClient
     */
    protected $mongo;

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

    /**
     * Find matches.
     *
     * @param array $options Various query parameters. Check source code to
     * know what's available.
     *
     * @return array[]
     */
    public function find($options): array
    {
        $query = [];

        $mongoOptions = array_merge([
            'projection' => [
                'matchId' => 1,
                'homeTeam' => 1,
                'awayTeam' => 1,
                'homeScore' => 1,
                'awayScore' => 1,
                'homeScorePEN' => 1,
                'awayScorePEN' => 1,
                'homeAggrWin' => 1,
                'awayAggrWin' => 1,
                'datetime' => 1,
                'status' => 1,
                'statusType' => 1,
                'timerStart' => 1,
                'stageId' => 1,
                'stageTree' => 1,
                'posts' => 1,
                'incidents' => 1,
                'hasMedia' => 1,
                '_id' => 0,
            ],
            'sort' => ['datetime' => 1],
        ], $options['mongoOptions'] ?? []);

        if (isset($options['date'])) {
            $query['datetime'] = [
                '$gte' => new UTCDateTime($options['date'] * 1000),
                '$lt' => new UTCDateTime(($options['date'] + 86400) * 1000),
            ];
        }

        if (isset($options['minDate'])) {
            $query['datetime'] = [
                '$gte' => new UTCDateTime($options['minDate'] * 1000),
            ];
        }

        if (isset($options['maxDate'])) {
            $query['datetime']['$lte'] = new UTCDateTime($options['maxDate'] * 1000);
        }

        if (isset($options['stageTree'])) {
            $query['stageTree'] = new MongoRegex("^{$options['stageTree']}");
        }

        // Allow to overwrite query if needed.
        $query = array_merge($query, $options['query'] ?? []);

        $cursor = $this->mongo->footyroom->matches->find($query, $mongoOptions);

        $matches = [];

        foreach ($cursor as $match) {
            if (isset($mongoOptions['projection']['timerStart'])) {
                $match['timerStart'] = (int) (string) ($match['timerStart'] ?? 0);
            } 

            if (isset($match['datetime'])) {
                $match['time'] = $match['datetime']->toDateTime()->format('H:i');
                $match['date'] = $match['datetime']->toDateTime()->format('Y-m-d');
                unset($match['datetime']);
            }

            // Get only most important incidents.
            if (isset($mongoOptions['projection']['incidents']) && isset($match['incidents'])) {
                $incidents = [];
                
                foreach ($match['incidents'] as $incident) {
                    if ($incident['code']['category'] === 'g' || $incident['code']['id'] === 'r' || $incident['code']['id'] === 'y2') {
                        $incidents[] = $incident;
                    }
                }

                $match['incidents'] = $incidents;
            }

            $matches[] = $match;
        }

        return $matches;
    }

    /**
     * Finds match by id.
     *
     * @param int $matchId
     * @param array $fields
     *
     * @return object|null
     */
    public function findById($matchId, array $fields)
    {
        $matchDto = $this->mongo->footyroom->matches->findOne(['matchId' => $matchId], ['projection' => $fields]);

        if (!$matchDto) {
            return null;
        }

        $match = AutoMapper::map($matchDto, Match::class, ['id' => 'matchId']);

        if ($match->timerStart) {
            $match->timerStart = $match->timerStart->toDateTime();
        }

        if ($match->datetime) {
            $match->datetime = $match->datetime->toDateTime();
        }

        if ($match->homeTeam) {
            $match->homeTeam = AutoMapper::map($matchDto->homeTeam, Team::class);
        }

        if ($match->awayTeam) {
            $match->awayTeam = AutoMapper::map($matchDto->awayTeam, Team::class);
        }

        return $match;
    }

    /**
     * Finds distinct date & times of matches played in specified stage tree.
     *
     * @param string $stageTree
     *
     * @return \MongoDB\BSON\UTCDateTime[]
     */
    public function distinctTimesByStageTree($stageTree)
    {
        // (6-20ms)
        return $this->mongo->footyroom->matches->distinct('datetime', [
            '$or' => [
                ['stageTree' => new MongoRegex("^$stageTree,")],
                ['stageTree' => $stageTree],
            ],
        ]);
    }

    public function findBasicById($matchId)
    {
        $matchDto = $this->mysql

        ->table('matches')
        ->select([
            'match_id as id',
            'datetime',
            'home_score as homeScore',
            'away_score as awayScore',
            'season',
            'stage',
            'stage_tree as stageTree',
            'stage_id as stageId',
            't1.team_name as homeTeamName',
            't1.team_id as homeTeamId',
            't2.team_name as awayTeamName',
            't2.team_id as awayTeamId',
        ])
        ->join('teams as t1', 'home_team', '=', 't1.team_id')
        ->join('teams as t2', 'away_team', '=', 't2.team_id')
        ->where('match_id', '=', $matchId);

        $match = AutoMapper::map($matchDto, Match::class);

        $match->datetime = new DateTime($match->datetime);

        $match->homeTeam = AutoMapper::map($matchDto, Team::class, [
            'name' => 'homeTeamName',
            'id' => 'homeTeamId',
        ]);

        $match->awayTeam = AutoMapper::map($matchDto, Team::class, [
            'name' => 'awayTeamName',
            'id' => 'awayTeamId',
        ]);

        return $match;
    }

    /**
     * Increments comment count.
     *
     * @param int $matchId
     * @param int $value
     *
     * @return int Number of affected rows.
     */
    public function incrementCommentCount($matchId, $value = 1)
    {
        $increment = function () use ($matchId, $value) {
            return $this->mysql->update(
                'UPDATE wp_posts AS p
				 JOIN wp_postmeta AS pm ON p.id = pm.post_id
				 SET p.comment_count = p.comment_count + ?
				 WHERE pm.meta_key = "match_id" AND pm.meta_value = ?',
                 [(int) $value, (string) $matchId]
            );
        };

        if (!$increment()) {
            $this->insertMatchPost($matchId);

            $increment();
        }
    }

    /**
     * Decrements comment count.
     *
     * @param int $matchId
     * @param int $value
     *
     * @return int Number of affected rows.
     */
    public function decrementCommentCount($matchId, $value = 1)
    {
        return $this->incrementCommentCount($matchId, -$value);
    }

    /**
     * Increment view count in match post.
     *
     * @param int $matchId
     * @param int $value
     *
     * @return int Number of affected rows.
     */
    private function incrementViewCountInMatchPost($matchId, $value)
    {
        return $this->mysql->update(
            'UPDATE wp_posts AS p
			 JOIN wp_postmeta AS pm ON p.id = pm.post_id
			 SET p.view_count = p.view_count + ?
			 WHERE pm.meta_key = "match_id" AND pm.meta_value = ?',
             [$value, (string) $matchId]
        );
    }

    /**
     * Increment view count.
     *
     * @param int $matchId
     * @param int $value
     */
    public function incrementViewCount($matchId, $value = 1)
    {
        $updated = $this->incrementViewCountInMatchPost($matchId, $value);

        if (!$updated) {
            $this->insertMatchPost($matchId);

            $this->incrementViewCountInMatchPost($matchId, $value);
        }
    }

    /**
     * Insert a speacial match "post" for tracking counts of comments and views.
     *
     * @param int $matchId
     */
    protected function insertMatchPost($matchId)
    {
        $this->mysql->insert('
			INSERT INTO wp_posts (post_author, post_status)
			SELECT 1, "draft" FROM DUAL
			WHERE NOT EXISTS (
				SELECT post_id FROM wp_postmeta
				WHERE meta_key = "match_id" AND meta_value = ?
			)
		', [(string) $matchId]);

        $postId = $this->mysql->getPdo()->lastInsertId();

        $this->mysql->insert('
			INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
			SELECT ?, "match_id", ? FROM DUAL
			WHERE NOT EXISTS (
				SELECT post_id FROM wp_postmeta
				WHERE meta_key = "match_id" AND meta_value = ?
			)
		', [$postId, (string) $matchId, (string) $matchId]);
    }
}