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