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