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