<?php
  
  namespace FootyRoom\Queries;
  
  use Illuminate\Database\Connection;
  
  class FanQuery
  {
      /**
       * @var \Illuminate\Database\Connection
       */
      protected $mysql;
  
      /**
       * Constructor.
       *
       * @param \Illuminate\Database\Connection $mysql
       */
      public function __construct(Connection $mysql)
      {
          $this->mysql = $mysql;
      }
  
      /**
       * Returns number of fans for specified teams on FootyRoom.
       *
       * @param int $homeTeamId
       * @param int $awayTeamId
       *
       * @return object
       */
      public function countHomeAwayFans($homeTeamId, $awayTeamId)
      {
          $fans = $this->mysql
  
          ->select(
              'SELECT homeFans, awayFans
               FROM
              (
                  SELECT COUNT(hf.user_id) AS homeFans
                  FROM fr_users AS hf
                  JOIN teams ON team_id = ?
                  WHERE (hf.clubTeam = teams.team_name AND teams.type = "club")
                      OR (hf.nationalTeam = teams.team_name AND teams.type = "national")
              ) AS h,
              (
                  SELECT COUNT(af.user_id) AS awayFans
                   FROM fr_users AS af
                   JOIN teams ON team_id = ?
                   WHERE (af.clubTeam = teams.team_name AND teams.type = "club")
                      OR (af.nationalTeam = teams.team_name AND teams.type = "national")
              ) AS a',
              [$homeTeamId, $awayTeamId]
          );
  
          if (!$fans) {
              return null;
          }
  
          return $fans[0];
      }
  }