<?php

namespace Modules\Vrc\Services\Compat;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Modules\Vrc\Entities\VrcVehicle;

/**
 * Suggests fitting ERP products for a VRC vehicle by reusing the ProductCompat
 * module's part↔device compatibility data (spec R3.3). Soft dependency: returns
 * an empty list if ProductCompat isn't installed. Verified compatibilities are
 * preferred; falls back to all matches when none are verified.
 */
class VrcPartSuggester
{
    public function available(): bool
    {
        return class_exists(\Modules\ProductCompat\Entities\PcDeviceModel::class)
            && Schema::hasTable('pc_device_compat')
            && Schema::hasTable('pc_device_models');
    }

    /**
     * Compatible, sellable ERP products for a vehicle (with live stock).
     *
     * @return array<int,array{product_id:int,variation_id:?int,name:string,sub_sku:?string,price:float,qty_available:float}>
     */
    public function forVehicle(int $businessId, VrcVehicle $vehicle, int $limit = 20): array
    {
        if (! $this->available()) {
            return [];
        }

        $make = optional($vehicle->make)->name;
        $model = optional($vehicle->model)->name;
        if (empty($model)) {
            return [];
        }

        $key = \Modules\ProductCompat\Entities\PcDeviceModel::makeKey($make, null, $model);

        $verifiedExists = DB::table('pc_device_compat as c')
            ->join('pc_device_models as m', 'm.id', '=', 'c.device_model_id')
            ->where('c.business_id', $businessId)
            ->where('m.normalized_key', $key)
            ->whereNotNull('c.verified_at')->exists();

        $rows = DB::table('pc_device_compat as c')
            ->join('pc_device_models as m', 'm.id', '=', 'c.device_model_id')
            ->join('products as p', 'p.id', '=', 'c.product_id')
            ->leftJoin('variations as v', 'v.product_id', '=', 'p.id')
            ->leftJoin('variation_location_details as vld', 'vld.variation_id', '=', 'v.id')
            ->where('c.business_id', $businessId)
            ->where('m.normalized_key', $key)
            ->where('p.not_for_selling', 0)
            ->when($verifiedExists, fn ($q) => $q->whereNotNull('c.verified_at'))
            ->groupBy('p.id', 'p.name', 'v.id', 'v.sub_sku', 'v.default_sell_price')
            ->selectRaw('p.id as product_id, p.name, v.id as variation_id, v.sub_sku, v.default_sell_price as price, COALESCE(SUM(vld.qty_available),0) as qty_available')
            ->limit($limit)->get();

        return $rows->map(fn ($r) => [
            'product_id'    => (int) $r->product_id,
            'variation_id'  => $r->variation_id ? (int) $r->variation_id : null,
            'name'          => (string) $r->name,
            'sub_sku'       => $r->sub_sku,
            'price'         => (float) $r->price,
            'qty_available' => (float) $r->qty_available,
        ])->all();
    }
}
