<?php

namespace App\Services\ClassPreparation;

use Illuminate\Support\Facades\DB;

class ClassPreparationInventoryService
{
    public function buildAvailability(string $schoolYear, string $semester, bool $limitToSemester, array $allowed): array
    {
        $inventoryMap = array_fill_keys($allowed, 0);

        $joinRows = DB::table('inventory_items as ii')
            ->select('ic.name AS item_name, COALESCE(SUM(CASE WHEN ii.good_qty IS NOT NULL AND ii.good_qty > 0 THEN ii.good_qty WHEN ii.`condition`=\"good\" THEN ii.quantity ELSE ii.quantity END),0) AS available')
            ->join('inventory_categories as ic', 'ic.id', '=', 'ii.category_id')
            ->where('ii.type', 'classroom')
            ->where('ii.school_year', $schoolYear)
            ->whereIn('ic.name', $allowed);

        if ($limitToSemester && $semester !== '') {
            $joinRows->where('ii.semester', $semester);
        }

        $joinRows = $joinRows->groupBy('ic.name')->get()->toArray();

        foreach ($joinRows as $r) {
            $name = (string) ($r->item_name ?? '');
            if ($name !== '' && isset($inventoryMap[$name])) {
                $inventoryMap[$name] = max($inventoryMap[$name], (int) ($r->available ?? 0));
            }
        }

        $nameRows = DB::table('inventory_items')
            ->select('name AS item_name, COALESCE(SUM(CASE WHEN good_qty IS NOT NULL AND good_qty > 0 THEN good_qty WHEN `condition`=\"good\" THEN quantity ELSE quantity END),0) AS available')
            ->where('type', 'classroom')
            ->where('school_year', $schoolYear)
            ->whereIn('name', $allowed);

        if ($limitToSemester && $semester !== '') {
            $nameRows->where('semester', $semester);
        }

        $nameRows = $nameRows->groupBy('name')->get()->toArray();

        foreach ($nameRows as $r) {
            $name = (string) ($r->item_name ?? '');
            if ($name !== '' && isset($inventoryMap[$name])) {
                $inventoryMap[$name] = max($inventoryMap[$name], (int) ($r->available ?? 0));
            }
        }

        $needsFallback = array_filter($inventoryMap, static fn ($v) => (int) $v === 0);
        if (! empty($needsFallback)) {
            $fallbackRows = DB::table('inventory_items')
                ->select('name AS item_name, COALESCE(SUM(CASE WHEN good_qty IS NOT NULL AND good_qty > 0 THEN good_qty WHEN `condition`=\"good\" THEN quantity ELSE quantity END),0) AS available')
                ->where('type', 'classroom')
                ->whereIn('name', array_keys($needsFallback))
                ->groupBy('name')
                ->get()
                ->toArray();

            foreach ($fallbackRows as $r) {
                $name = (string) ($r->item_name ?? '');
                if ($name !== '' && isset($inventoryMap[$name])) {
                    $inventoryMap[$name] = max($inventoryMap[$name], (int) ($r->available ?? 0));
                }
            }
        }

        foreach ($inventoryMap as $name => $value) {
            if ((int) $value !== 0) {
                continue;
            }

            $goodQty = (int) DB::table('inventory_items')
                ->where('type', 'classroom')
                ->where('name', $name)
                ->sum('good_qty');

            if ($goodQty > 0) {
                $inventoryMap[$name] = $goodQty;

                continue;
            }

            $qty = (int) DB::table('inventory_items')
                ->where('type', 'classroom')
                ->where('name', $name)
                ->sum('quantity');

            if ($qty > 0) {
                $inventoryMap[$name] = $qty;
            }
        }

        return $inventoryMap;
    }
}