<?php

namespace FootyRoom\Queries;

use Illuminate\Database\Connection;

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

    /**
     * Constructor.
     *
     * @param \Illuminate\Database\Connection $mysql
     */
    public function __construct(Connection $mysql)
    {
        $this->mysql = $mysql;
    }

    /**
     * Find seasons of specified competition.
     *
     * Only returns seasons that actually have matches with review posts.
     *
     * @param int $competitionId
     *
     * @return object[]
     */
    public function findSeasons($competitionId)
    {
        $matches = $this->mysql->select(
            'SELECT DISTINCT stage_tree
             FROM matches
             WHERE stage_tree LIKE ? OR stage_tree = ?',
            ["$competitionId,%", "$competitionId"]
        );

        // Extract seasons.
        $seasonsStageIds = [];

        foreach ($matches as $match) {
            $stages = explode(',', $match->stage_tree);
            $seasonsStageIds[] = $stages[1];
        }

        $stageIds = implode(',', array_unique($seasonsStageIds));

        if (!$stageIds) {
            return [];
        }

        $seasons = $this->mysql->select(
            "SELECT s.id, s.name, s.type, s.meta, s.parent, s.tree
			 FROM stages AS s
			 WHERE s.id IN ($stageIds) AND s.type = 'season'"
        );

        // Unserialize seasons meta.
        foreach ($seasons as $season) {
            $season->meta = json_decode($season->meta);
        }

        // Sort by year, descending.
        @usort($seasons, function ($a, $b) {
            if ($a->meta->year > $b->meta->year) {
                return -1;
            }
            if ($a->meta->year < $b->meta->year) {
                return 1;
            }

            return 0;
        });

        return $seasons;
    }

    /**
     * Finds season that specified stage belongs to.
     *
     * @param int $stageId
     *
     * @return object|null
     */
    public function findSeasonOf($stageId)
    {
        $stages = $this->mysql->select(
            'SELECT season.id, season.name, season.type, season.meta, season.parent, season.tree
			 FROM stages as stage 
			 JOIN stages as season ON (stage.tree LIKE CONCAT(season.tree, ",%") OR stage.tree = season.tree) AND season.type = "season"
			 WHERE stage.id = ?',
            [$stageId]
        );

        if (!$stages) {
            return null;
        }

        $season = $stages[0];

        $season->meta = json_decode($season->meta);

        return $season;
    }

    /**
     * Find stages of specified tree.
     *
     * Only returns stages that have matches in them.
     *
     * @param string $tree
     *
     * @return object[]|null
     */
    public function findStagesByTree($tree)
    {
        $matches = $this->mysql->select(
            'SELECT DISTINCT stage_tree
             FROM matches
             WHERE stage_tree LIKE ?',
            ["$tree,%"]
        );

        // Extract stages.
        $stageIds = [];
        
        foreach ($matches as $match) {
            $stageIds = array_merge($stageIds, explode(',', $match->stage_tree));
        }
        
        $stageIds = implode(',', array_unique($stageIds));

        if (!$stageIds) {
            return [];
        }

        $stages = $this->mysql->select(
            "SELECT s.id, s.name, s.type, s.meta, s.parent, s.tree
			 FROM stages AS s
			 WHERE s.tree LIKE ? AND s.id IN ($stageIds)",
             ["$tree,%"]
        );

        if (!$stages) {
            return null;
        }

        foreach ($stages as $stage) {
            $stage->meta = json_decode($stage->meta);
        }

        return $stages;
    }

    /**
     * Finds stage by id.
     *
     * @param int $id
     *
     * @return object|null
     */
    public function findById($id)
    {
        $stage = $this->mysql

        ->table('stages')
        ->where('id', '=', $id)
        ->first();

        if ($stage) {
            $stage->meta = json_decode($stage->meta);
        }

        return $stage;
    }
}