<?php
  
  namespace FootyRoom\Repositories;
  
  use Illuminate\Database\Connection;
  
  class PreviewRepository
  {
      /**
       * @var \Illuminate\Database\Connection
       */
      protected $mysql;
  
      /**
       * Constructor.
       *
       * @param \Illuminate\Database\Connection $mysql
       */
      public function __construct(Connection $mysql)
      {
          $this->mysql = $mysql;
      }
  
      /**
       * Find published preview posts for matches that have not finished.
       *
       * @param int $offset
       * @param int $limit
       *
       * @return object[];
       */
      public function findPosts($offset = 0, $limit = 20): array
      {
          return $this->mysql
  
          ->select(
              'SELECT
  			 m.match_id as matchId,
  			 pm.post_id as postId,
  			 p.post_date as postDate,
  			 t1.nice_name as homeTeamName,
  			 t2.nice_name as awayTeamName,
  			 t1.team_id as homeTeamId,
  			 t2.team_id as awayTeamId
  			 FROM wp_posts AS p
  			 JOIN wp_postmeta AS pm ON pm.meta_key = "match_id_preview" AND pm.post_id = p.id
  			 JOIN matches AS m ON m.match_id = pm.meta_value
  			 JOIN teams AS t1 ON t1.team_id = home_team
  			 JOIN teams AS t2 ON t2.team_id = away_team
  			 WHERE m.status_type != "finished" AND m.status_type != "interrupted"
  			 	AND p.post_status = "publish" AND p.post_type = "preview"
  			 ORDER BY p.post_date DESC
  			 LIMIT ?, ?',
               [$offset, $limit]
          );
      }
  }