<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class CaseReviewRequest extends Model
{
    protected $table = 'case_review_requests';

    public $timestamps = false;

    protected $fillable = [
        'app_id',
        'request_message',
        'request_by',
        'request_time',
        'response_message',
        'response_by',
        'response_time',
        'status',
    ];

    /**
     * Get the count of pending review requests for a specific application.
     *
     * @param int $appId
     * @return int
     */
    public static function getPendingCount($appId)
    {
        return self::where('app_id', $appId)->where('status', 'PENDING')->count();
    }

    /**
     * Check if an application has any pending review requests.
     *
     * @param int $appId
     * @return bool
     */
    public static function hasPendingRequests($appId)
    {
        return self::getPendingCount($appId) > 0;
    }

    /**
     * Get a distinct array of app_ids that have at least one case review request (any status),
     * filtered by the given array of application IDs.
     *
     * @param array $appIds
     * @return array
     */
    public static function getAppIdsWithRequests(array $appIds)
    {
        if (empty($appIds)) {
            return [];
        }

        return self::whereIn('app_id', $appIds)
            ->distinct()
            ->pluck('app_id')
            ->map(function ($v) {
                return (string) $v;
            })->all();
    }

    /**
     * Get the count of PENDING requests mapped by app_id for the given array of application IDs.
     *
     * @param array $appIds
     * @return array associative array where key is app_id and value is pending count
     */
    public static function getPendingCountsForApps(array $appIds)
    {
        if (empty($appIds)) {
            return [];
        }

        return self::whereIn('app_id', $appIds)
            ->where('status', 'PENDING')
            ->select('app_id', \DB::raw('COUNT(*) as pending_count'))
            ->groupBy('app_id')
            ->pluck('pending_count', 'app_id')
            ->mapWithKeys(function ($count, $appId) {
                return [(string) $appId => (int) $count];
            })
            ->all();
    }

    /**
     * Get the user who requested the review.
     */
    public function requester()
    {
        return $this->belongsTo(User::class, 'request_by', 'id');
    }

    /**
     * Get the user who responded to the review.
     */
    public function responder()
    {
        return $this->belongsTo(User::class, 'response_by', 'id');
    }
}