<?php
  
  namespace FootyRoom\Queries\Ban;
  
  use DateTime;
  use FootyRoom\Support\AutoMapper;
  use Illuminate\Database\Connection;
  
  class BanQueryHandler
  {
      /**
       * @var \Illuminate\Database\Connection
       */
      protected $mysql;
  
      /**
       * Constructor.
       *
       * @param \Illuminate\Database\Connection $mysql
       */
      public function __construct(Connection $mysql)
      {
          $this->mysql = $mysql;
      }
  
      /**
       * Query handler.
       *
       * @param \FootyRoom\Queries\Ban\ActiveBanQuery $query
       *
       * @return \FootyRoom\Queries\Ban\Ban
       */
      public function findActiveBan(ActiveBanQuery $query)
      {
          $ban = $this->mysql
  
          ->table('bans')
          ->select([
              'id',
              'entity_type as entityType',
              'entity_id as entityId',
              'type',
              'duration',
              'created_at as createdAt',
              'username as createdBy',
          ])
          ->join('fr_users', 'bans.created_by', '=', 'user_id')
          ->where('entity_type', '=', $query->getEntityType())
          ->where('entity_id', '=', $query->getEntityId())
          ->where('type', '=', $query->getType())
          ->whereRaw('DATE_ADD(created_at, INTERVAL `duration` SECOND) > NOW()')
          ->orderBy('id', 'desc')
          ->first();
  
          if (!$ban) {
              return null;
          }
  
          $ban->createdAt = new DateTime($ban->createdAt);
  
          return AutoMapper::map($ban, Ban::class);
      }
  
      /**
       * Get list of users who have ban records.
       *
       * @return \FootyRoom\Queries\Ban\BanSummary[]
       */
      public function findBanSummaries(int $offset = 20, string $sortBy = 'banCount', string $username = null): array
      {
          $query = $this->mysql
              ->table('bans')
              ->select(
                  'bans.entity_id as userId',
                  'fr_users.username',
                  $this->mysql->raw('count(*) as banCount'),
                  $this->mysql->raw('MAX(created_at) as latestBan')
              )
              ->groupBy('userId')
              ->where('entity_type', '=', 'user_id')
              ->where('type', '=', 'comment')
              ->orderBy($sortBy, 'DESC')
              ->offset($offset)
              ->limit(20)
              ->join('fr_users', 'bans.entity_id', '=', 'fr_users.user_id');
  
          if ($username) {
              $query->where('fr_users.username', '=', $username);
          }
  
          $bans = $query->get();
  
          $bansSummaries = [];
          foreach ($bans as $ban) {
              $ban->latestBan = new DateTime($ban->latestBan);
              $bansSummaries[] = AutoMapper::map($ban, BanSummary::class);
          }
  
          return $bansSummaries;
      }
  
      /**
       * Find comment bans by user id.
       *
       * @return \FootyRoom\Queries\Ban\Ban[]
       */
      public function findCommentBansByUserId(int $userId): array
      {
          $bans = $this->mysql
              ->table('bans')
              ->select([
                  'entity_id as entityId',
                  'created_at as createdAt',
                  'fr_users.username as createdBy',
                  'duration',
              ])
              ->where('entity_type', '=', 'user_id')
              ->where('entity_id', '=', $userId)
              ->where('type', '=', 'comment')
              ->orderBy('created_at', 'DESC')
              ->join('fr_users', 'bans.created_by', '=', 'fr_users.user_id')
              ->get();
  
          $banModels = [];
  
          foreach ($bans as $ban) {
              $ban->createdAt = new DateTime($ban->createdAt);
              $banModels[] = AutoMapper::map($ban, Ban::class);
          }
  
          return $banModels;
      }
  }