<?php

declare(strict_types=1);

namespace App\Business\Purchase;

use App\Business\Purchase\Enums\PurchaseExceptionResolutionResult;
use App\Business\Purchase\Enums\PurchaseExceptionStatus;
use App\Business\Purchase\Enums\PurchaseOrderStatus;
use App\Business\Purchase\Enums\PurchaseRequestStatus;
use App\Models\DberpPurchaseException;
use App\Models\DberpPurchaseOrder;
use App\Models\DberpPurchasePriceReferenceSnapshot;
use App\Models\DberpPurchaseRequest;
use Illuminate\Support\Facades\DB;

final class PurchaseCollaborationReminderService
{
    /**
     * @return array<string, int>
     */
    public function overviewMetrics(): array
    {
        return [
            'pending_purchase_request_count' => DberpPurchaseRequest::query()
                ->where('status', PurchaseRequestStatus::Pending->value)
                ->count(),
            'pending_convert_request_count' => DberpPurchaseRequest::query()
                ->whereIn('status', [
                    PurchaseRequestStatus::Pending->value,
                    PurchaseRequestStatus::PartiallyConverted->value,
                ])
                ->where('remaining_quantity', '>', 0)
                ->count(),
            'urgent_pending_purchase_request_count' => DberpPurchaseRequest::query()
                ->whereIn('status', [
                    PurchaseRequestStatus::Pending->value,
                    PurchaseRequestStatus::PartiallyConverted->value,
                ])
                ->where('urgency_level', '!=', 'normal')
                ->count(),
            'pending_arrival_order_count' => DberpPurchaseOrder::query()
                ->whereIn('status', [
                    PurchaseOrderStatus::Confirmed->value,
                    PurchaseOrderStatus::PartiallyReceived->value,
                ])
                ->where('remaining_arrival_quantity', '>', 0)
                ->count(),
            'overdue_arrival_order_count' => DberpPurchaseOrder::query()
                ->whereIn('status', [
                    PurchaseOrderStatus::Confirmed->value,
                    PurchaseOrderStatus::PartiallyReceived->value,
                ])
                ->where('remaining_arrival_quantity', '>', 0)
                ->whereDate('expected_arrival_date', '<', now()->toDateString())
                ->count(),
            'partial_arrival_order_count' => DberpPurchaseOrder::query()
                ->where('status', PurchaseOrderStatus::PartiallyReceived->value)
                ->count(),
            'pending_purchase_exception_count' => DberpPurchaseException::query()
                ->whereIn('status', [PurchaseExceptionStatus::Pending->value, PurchaseExceptionStatus::Processing->value])
                ->count(),
            'pending_resupply_exception_count' => DberpPurchaseException::query()
                ->whereIn('status', [PurchaseExceptionStatus::Pending->value, PurchaseExceptionStatus::Processing->value])
                ->where('resolution_result', PurchaseExceptionResolutionResult::Resupply->value)
                ->count(),
            'high_frequency_exception_supplier_count' => DberpPurchaseException::query()
                ->select('supplier_id')
                ->whereNotNull('supplier_id')
                ->whereDate('created_at', '>=', now()->subDays(30)->toDateString())
                ->groupBy('supplier_id')
                ->havingRaw('COUNT(*) >= 2')
                ->count(),
            'supplier_performance_attention_count' => DB::table('dberp_suppliers')
                ->whereIn('id', function ($query): void {
                    $query->select('supplier_id')
                        ->from('dberp_purchase_exceptions')
                        ->whereNotNull('supplier_id')
                        ->whereDate('created_at', '>=', now()->subDays(30)->toDateString())
                        ->groupBy('supplier_id')
                        ->havingRaw('COUNT(*) >= 2');
                })
                ->orWhereIn('id', function ($query): void {
                    $query->select('supplier_id')
                        ->from('dberp_purchase_return_orders')
                        ->whereNotNull('supplier_id')
                        ->where('status', 'effective')
                        ->whereDate('business_time', '>=', now()->subDays(30)->toDateString())
                        ->groupBy('supplier_id')
                        ->havingRaw('COUNT(*) >= 1');
                })
                ->count(),
            'stale_purchase_price_reference_count' => DberpPurchasePriceReferenceSnapshot::query()
                ->where('scope_type', 'supplier_product')
                ->where(function ($query): void {
                    $query->whereNull('latest_transaction_date')
                        ->orWhereDate('latest_transaction_date', '<', now()->subDays(30)->toDateString());
                })
                ->count(),
        ];
    }

    /**
     * @return list<array<string, string|int>>
     */
    public function reminderItems(): array
    {
        $metrics = $this->overviewMetrics();

        return [
            ['key' => 'pending_purchase_request', 'title' => (string) __('dberp.widgets.purchase_workbench.reminder_pending_purchase_request'), 'count' => $metrics['pending_purchase_request_count'], 'amount' => ''],
            ['key' => 'pending_convert_request', 'title' => (string) __('dberp.widgets.purchase_workbench.reminder_pending_convert_request'), 'count' => $metrics['pending_convert_request_count'], 'amount' => ''],
            ['key' => 'urgent_purchase_request', 'title' => (string) __('dberp.widgets.purchase_workbench.reminder_urgent_purchase_request'), 'count' => $metrics['urgent_pending_purchase_request_count'], 'amount' => ''],
            ['key' => 'pending_arrival_order', 'title' => (string) __('dberp.widgets.purchase_workbench.reminder_pending_arrival_order'), 'count' => $metrics['pending_arrival_order_count'], 'amount' => ''],
            ['key' => 'overdue_arrival_order', 'title' => (string) __('dberp.widgets.purchase_workbench.reminder_overdue_arrival_order'), 'count' => $metrics['overdue_arrival_order_count'], 'amount' => ''],
            ['key' => 'partial_arrival_order', 'title' => (string) __('dberp.widgets.purchase_workbench.reminder_partial_arrival_order'), 'count' => $metrics['partial_arrival_order_count'], 'amount' => ''],
            ['key' => 'pending_purchase_exception', 'title' => (string) __('dberp.widgets.purchase_workbench.reminder_pending_purchase_exception'), 'count' => $metrics['pending_purchase_exception_count'], 'amount' => ''],
            ['key' => 'pending_resupply_exception', 'title' => (string) __('dberp.widgets.purchase_workbench.reminder_pending_resupply_exception'), 'count' => $metrics['pending_resupply_exception_count'], 'amount' => ''],
            ['key' => 'high_frequency_exception_supplier', 'title' => (string) __('dberp.widgets.purchase_workbench.reminder_high_frequency_exception_supplier'), 'count' => $metrics['high_frequency_exception_supplier_count'], 'amount' => ''],
            ['key' => 'supplier_performance_attention', 'title' => (string) __('dberp.widgets.purchase_workbench.reminder_supplier_performance_attention'), 'count' => $metrics['supplier_performance_attention_count'], 'amount' => ''],
            ['key' => 'stale_purchase_price_reference', 'title' => (string) __('dberp.widgets.purchase_workbench.reminder_stale_purchase_price_reference'), 'count' => $metrics['stale_purchase_price_reference_count'], 'amount' => ''],
        ];
    }
}
