<?php

namespace App\Http\Controllers;

use Carbon\Carbon;
use App\Models\Company;
use App\Models\Currency;
use App\Models\ItemUnit;
use App\Models\Supplier;
use App\Models\CategorySupplier;
use App\Models\DeleteLog;
use App\Models\TaxMaster;
use App\Models\Warehouse;
use App\Models\Department;
use App\Models\GoodReceipt;
use App\Models\PurchaseSparepart;
use Illuminate\Http\Request;
use App\Models\PurchaseOrderSparepart;
use App\Models\InventoryDetail;
use App\Http\Controllers\Module;
use App\Models\GoodReceiptSparepartDetail;
use Illuminate\Support\Facades\DB;
use App\Models\PurchaseOrderSparepartDetail;
use App\Models\PurchaseRequisitionSparepart;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Auth;
use Illuminate\Database\QueryException;
use Illuminate\Support\Facades\Validator;
use App\Models\CurrencyConversion;
use App\Models\Periode;
use App\Models\Sparepart;

class PurchaseOrderSparepartController extends Controller
{
    public function index($dateStart, $dateEnd)
    {
        $purchaseOrders = PurchaseOrderSparepart::with(['details.sparepart', 'details.unitRelation', 'details.requisition', 'department', 'supplier.currency', 'warehouse'])
        ->whereBetween('document_date', [$dateStart, $dateEnd])->whereIn('department_id',$this->getDepartment())->get();
        $periods = Periode::all();
        foreach ($purchaseOrders as $po) {
            $po->closed = Module::checkPeriodeBack($periods, $po->document_date);
        }
        return response()->json(["po" => $purchaseOrders, "role" => $this->getRole()]);
    }

    public function report()
    {
        try {
            $baseCurrency = Currency::where('is_base', true)->first()->currency_code ?? 'IDR';
            $purchaseOrders = PurchaseOrderSparepart::with([
                'details' => function ($query) {
                    $query->where(function ($q) {
                        $q->whereNull('is_cancel')
                            ->orWhere(function ($subQ) {
                                $subQ->whereNotNull('is_cancel')
                                    ->where('qty', '>', 0);
                            });
                    })->with(['sparepart', 'unitRelation', 'requisition']);
                },
                'department',
                'supplier.currency',
                'creator'
            ])
                ->get()
                ->map(function ($po) use ($baseCurrency) {
                    $po->details = $po->details->filter(function ($detail) {
                        return is_null($detail->is_cancel) || (!is_null($detail->is_cancel) && $detail->qty > 0);
                    })->values();
                    $po->currency_code = $po->supplier->currency->currency_code ?? $baseCurrency;
                    return $po;
                })
                ->filter(function ($po) {
                    return $po->details->isNotEmpty();
                })
                ->values();

            return response()->json([
                "po" => $purchaseOrders,
                "role" => $this->getRole()
            ], 200);
        } catch (\Exception $e) {
            Log::error('Report Error: ' . $e->getMessage());
            return response()->json(['error' => 'Failed to load purchase order report: ' . $e->getMessage()], 500);
        }
    }

    public function summary()
    {
        try {
            $baseCurrency = Currency::where('is_base', true)->first()->currency_code ?? 'IDR';
            $purchaseOrders = PurchaseOrderSparepart::with(['department', 'supplier.currency'])
                ->get()
                ->map(function ($po) use ($baseCurrency) {
                    $po->currency_code = $po->supplier->currency->currency_code ?? $baseCurrency;
                    return $po;
                });

            return response()->json([
                "po" => $purchaseOrders,
                "role" => $this->getRole()
            ], 200);
        } catch (\Exception $e) {
            Log::error('Summary Error: ' . $e->getMessage());
            return response()->json(['error' => 'Failed to load purchase order summary: ' . $e->getMessage()], 500);
        }
    }

    public function show($id)
    {
        $purchaseOrder = PurchaseOrderSparepart::with(['details.sparepart', 'details.unitRelation', 'department', 'supplier', 'details.requisition'])->findOrFail($id);
        return response()->json($purchaseOrder);
    }

    public function store(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'purchase_order_number' => 'required|unique:purchase_order_spareparts,purchase_order_number',
            'document_date' => 'required|date',
            'department_id' => 'required|exists:department,id',
            'supplier_id' => 'required|exists:suppliers,id',
            'warehouse_id' => 'required|exists:warehouse,id',
            'notes' => 'nullable|string',
            'discount' => 'nullable|numeric|min:0',
            'tax_revenue' => 'nullable|string',
            'tax_code' => 'nullable|string',
            'details' => 'required|array',
            'details.*.purchase_requisition_sparepart_id' => 'nullable|exists:purchase_requisition_spareparts,id',
            'details.*.purchase_requisition_sparepart_detail_id' => 'nullable|exists:purchase_requisition_sparepart_details,id',
            'details.*.sparepart_id' => 'required|exists:spareparts,id',
            'details.*.unit' => 'required|exists:item_units,id',
            'details.*.qty' => 'required|numeric|min:0',
            'details.*.price' => 'required|numeric|min:0',
            'details.*.discount' => 'nullable|numeric|min:0',
            'details.*.description' => 'nullable|string',
        ]);

        if ($validator->fails()) {
            return response()->json(['errors' => $validator->errors()], 422);
        }

        DB::beginTransaction();
        try {
            $company = Company::first();
            $department = Department::find($request->department_id);
            $date = Carbon::parse($request->document_date);
            $nobukti = Module::generateDocumentNumber($company->companyCode, $department->department_code, $date->month, $date->year, "POSP", 1);
            $sup = Supplier::find($request->supplier_id);

            $subtotal = array_sum(array_map(function ($detail) {
                return ($detail['qty'] * $detail['price']) - ($detail['discount'] ?? 0);
            }, $request->details));
            $discount = $request->discount ?? 0;
            $tax = 0;
            $totalAllItemBeforeTax = 0;
            $totalAllDiscountDetail = 0;
            $totalAllAfterDiscountBeforeTax = 0;
            $total = $subtotal - $discount + $tax;

            $tax_tariff = null;
            $tax_revenue_tariff = null;
            if ($request->tax_revenue) {
                $tax_revenue_tariff = TaxMaster::where("tax_code", $request->tax_revenue)->first()->tariff;
            }
            
            $taxs = TaxMaster::where('tax_code', $request->tax_code)->first();
            if ($sup) {
                $tax_tariff = ($sup->pkp == 1) ? TaxMaster::where("tax_code", $request->tax_code)->first()->tariff : null;
            }

            $curConversion = 1;
            if (!is_null($sup->currency_id)) {
                $cur = Currency::find($sup->currency_id);
                if ($cur->is_base == false) {
                    $conver = CurrencyConversion::where("to_currency_id", $sup->currency_id)
                        ->where("date_start", "<=", $request->document_date)->orderBy("date_start", "desc")->orderBy("created_at", "desc")->first();
                    if ($conver) {
                        $curConversion = $conver->conversion;
                    }
                }
            }

            $purchaseOrder = PurchaseOrderSparepart::create([
                'purchase_order_number' => $nobukti,
                'document_date' => $request->document_date,
                'department_id' => $request->department_id,
                'supplier_id' => $request->supplier_id,
                'warehouse_id' => $request->warehouse_id,
                'notes' => $request->notes,
                'status' => 'Not',
                'subtotal' => $subtotal,
                'discount' => $discount,
                'tax' => $tax,
                'total' => $total,
                'tax_code' => $request->tax_code,
                'tax_tariff' => $tax_tariff,
                'tax_revenue' => $request->tax_revenue,
                'tax_revenue_tariff' => $tax_revenue_tariff,
                'created_by' => auth()->id(),
                'updated_by' => auth()->id(),
            ]);

            $prUpdates = [];
            $revenueTax = 0;
            $addTax = 0;
            $services = 0;
            $taxed = 0;
            foreach ($request->details as $detail) {
                $sparepart = Sparepart::find($detail['sparepart_id']);
                $detailSubtotal = ($detail['qty'] * $detail['price']) - ($detail['discount'] ?? 0);

                if ($sup->pkp == 1) {
                    if ($sup->include == 1) {
                        $totalAllAfterDiscountBeforeTax += (($detail['qty'] * $detail['price']) / (1 + $taxs->tariff / 100)) - ($detail['discount'] ?? 0);
                    } else {
                        $totalAllAfterDiscountBeforeTax += $detail['qty'] * $detail['price'] - ($detail['discount'] ?? 0);
                    }
                } else {
                    $totalAllAfterDiscountBeforeTax += $detail['qty'] * $detail['price'] - ($detail['discount'] ?? 0);
                }

                $totalPriceBeforeTaxBeforeDiscount = 0;
                $totalPriceBeforeTaxAfterDiscount = 0;
                $totalDiscountPerDetail = 0;
                $discPerDetail = 0;
                if ($sup->pkp == 1) {
                    if (strtolower($sparepart->type) == 'service') {
                        $services += $detail['qty'] * $detail['price'];
                    }

                    if ($sup->include == 1) {
                        $totalPriceBeforeTaxBeforeDiscount = ($detail['qty'] * $detail['price']) / (1 + $taxs->tariff / 100);
                        $totalPriceBeforeTaxAfterDiscount = $totalPriceBeforeTaxBeforeDiscount - ($detail['discount'] ?? 0);
                        $discPerDetail = $totalPriceBeforeTaxAfterDiscount / $totalAllAfterDiscountBeforeTax * ($purchaseOrder->discount ?? 0);
                        $totalPriceBeforeTaxAfterDiscount -= $discPerDetail;
                        $totalDiscountPerDetail = ($detail['discount'] ?? 0) + $discPerDetail;
                        $totalAllDiscountDetail += ($detail['discount'] ?? 0);
                        $taxed += $totalPriceBeforeTaxAfterDiscount;
                    } else {
                        $totalPriceBeforeTaxBeforeDiscount = $detail['qty'] * $detail['price'];
                        $totalPriceBeforeTaxAfterDiscount = $totalPriceBeforeTaxBeforeDiscount - ($detail['discount'] ?? 0);
                        $discPerDetail = $totalPriceBeforeTaxAfterDiscount / $totalAllAfterDiscountBeforeTax * ($purchaseOrder->discount ?? 0);
                        $totalPriceBeforeTaxAfterDiscount -= $discPerDetail;
                        $totalDiscountPerDetail = ($detail['discount'] ?? 0) + $discPerDetail;
                        $totalAllDiscountDetail += ($detail['discount'] ?? 0);
                        $taxed += $totalPriceBeforeTaxAfterDiscount;
                    }
                } else {
                    $revenueTax = 0;
                    $addTax = 0;
                    $totalPriceBeforeTaxBeforeDiscount = $detail['qty'] * $detail['price'];
                    $totalPriceBeforeTaxAfterDiscount = $totalPriceBeforeTaxBeforeDiscount - ($detail['discount'] ?? 0);
                    $discPerDetail = $totalPriceBeforeTaxAfterDiscount / $totalAllAfterDiscountBeforeTax * ($purchaseOrder->discount ?? 0);
                    $totalPriceBeforeTaxAfterDiscount -= $discPerDetail;
                    $totalDiscountPerDetail = ($detail['discount'] ?? 0) + $discPerDetail;
                    $totalAllDiscountDetail += ($detail['discount'] ?? 0);
                }
                $totalAllItemBeforeTax += $totalPriceBeforeTaxAfterDiscount;
                $add_tax_detail = ($sup->pkp == 1) ? $totalPriceBeforeTaxAfterDiscount * $taxs->tax_base * $taxs->tariff / 100 : null;

                $sparepartDetail = $sparepart->details()->where('unit_id', $detail['unit'])->first();

                PurchaseOrderSparepartDetail::create([
                    'purchase_order_sparepart_id' => $purchaseOrder->id,
                    'purchase_requisition_sparepart_id' => $detail['purchase_requisition_sparepart_id'] ?? null,
                    'purchase_requisition_sparepart_detail_id' => $detail['purchase_requisition_sparepart_detail_id'] ?? null,
                    'sparepart_id' => $detail['sparepart_id'],
                    'unit_id' => $detail['unit'],
                    'qty' => $detail['qty'],
                    'price' => $detail['price'],
                    'discount' => $detail['discount'] ?? 0,
                    'subtotal' => $detailSubtotal,
                    'base_unit' => $detail['unit'],
                    'base_qty' => $sparepartDetail ? $sparepartDetail->conversion : 1,
                    'qty_left' => $detail['qty'],
                    'add_tax_detail' => $add_tax_detail,
                    'description' => $detail['description'],
                    'created_by' => auth()->id(),
                    'updated_by' => auth()->id(),
                ]);

                if ($detail['purchase_requisition_sparepart_id']) {
                    $prId = $detail['purchase_requisition_sparepart_id'];
                    $purchaseRequisition = PurchaseRequisitionSparepart::find($prId);
                    if ($purchaseRequisition) {
                        $prDetail = $purchaseRequisition->details()->where('id', $detail['purchase_requisition_sparepart_detail_id'])->first();
                        if ($prDetail) {
                            $newQtyLeft = max(0, $prDetail->qty_left - $detail['qty']);
                            $prDetail->update(['qty_left' => $newQtyLeft]);
                            if (!isset($prUpdates[$prId])) {
                                $prUpdates[$prId] = $purchaseRequisition;
                            }
                        }
                    }
                }
            }

            foreach ($prUpdates as $prId => $purchaseRequisition) {
                $remainingQtyLeft = $purchaseRequisition->details()->sum('qty_left');
                $newStatus = $remainingQtyLeft == 0 ? 'order' : 'partial';
                $purchaseRequisition->update(['status' => $newStatus]);
            }

            if ($sup->pkp == 1) {
                $addTax = $taxed * $taxs->tax_base * $taxs->tariff / 100;
                $revenueTax = $services * $tax_revenue_tariff / 100;
            } else {
                $revenueTax = 0;
                $addTax = 0;
            }
            $purchaseOrder->tax = $addTax;
            $purchaseOrder->add_tax_revenue = $revenueTax;
            $purchaseOrder->subtotal = $totalAllItemBeforeTax;
            $purchaseOrder->total = $totalAllItemBeforeTax + $purchaseOrder->tax + $purchaseOrder->add_tax_revenue;
            $purchaseOrder->save();

            DB::commit();
            return response()->json(null);
        } catch (\Exception $e) {
            DB::rollback();
            return response()->json(['error' => 'Server error ' . $e->getMessage()], 500);
        }
    }

    public function update(Request $request, $id)
    {
        $purchaseOrder = PurchaseOrderSparepart::findOrFail($id);

        $validator = Validator::make($request->all(), [
            'department_id' => 'required|exists:department,id',
            'supplier_id' => 'required|exists:suppliers,id',
            'warehouse_id' => 'required|exists:warehouse,id',
            'notes' => 'nullable|string',
            'discount' => 'nullable|numeric|min:0',
            'tax_revenue' => 'nullable|string',
            'tax_code' => 'nullable|string',
            'details' => 'required|array',
            'details.*.purchase_requisition_sparepart_id' => 'nullable|exists:purchase_requisition_spareparts,id',
            'details.*.purchase_requisition_sparepart_detail_id' => 'nullable|exists:purchase_requisition_sparepart_details,id',
            'details.*.sparepart_id' => 'required|exists:spareparts,id',
            'details.*.unit' => 'required|exists:item_units,id',
            'details.*.qty' => 'required|numeric|min:0',
            'details.*.price' => 'required|numeric|min:0',
            'details.*.discount' => 'nullable|numeric|min:0',
            'details.*.description' => 'nullable|string',
        ], [
            'details.*.unit.exists' => 'The selected unit is invalid.'
        ]);

        if ($validator->fails()) {
            return response()->json(['errors' => $validator->errors()], 422);
        }

        DB::beginTransaction();
        try {
            $details = array_map(function ($detail) {
                if (is_array($detail['unit'])) {
                    $detail['unit'] = $detail['unit']['id'];
                }
                return $detail;
            }, $request->details);

            $sup = Supplier::find($request->supplier_id);

            $subtotal = array_sum(array_map(function ($detail) {
                return ($detail['qty'] * $detail['price']) - ($detail['discount'] ?? 0);
            }, $details));
            $discount = $request->discount ?? 0;
            $tax = 0;
            $totalAllItemBeforeTax = 0;
            $totalAllDiscountDetail = 0;
            $totalAllAfterDiscountBeforeTax = 0;
            $total = $subtotal - $discount + $tax;

            $tax_tariff = null;
            $tax_revenue_tariff = null;
            if ($request->tax_revenue) {
                $tax_revenue_tariff = TaxMaster::where("tax_code", $request->tax_revenue)->first()->tariff;
            }
            $taxs = TaxMaster::where('tax_code', $request->tax_code)->first();
            if ($sup) {
                $tax_tariff = ($sup->pkp == 1) ? TaxMaster::where("tax_code", $request->tax_code)->first()->tariff : null;
            }

            $curConversion = 1;
            if (!is_null($sup->currency_id)) {
                $cur = Currency::find($sup->currency_id);
                if ($cur->is_base == false) {
                    $conver = CurrencyConversion::where("to_currency_id", $sup->currency_id)
                        ->where("date_start", "<=", $request->document_date)->orderBy("date_start", "desc")->orderBy("created_at", "desc")->first();
                    if ($conver) {
                        $curConversion = $conver->conversion;
                    }
                }
            }

            $oldDetails = $purchaseOrder->details()->get();
            $oldPRs = $oldDetails->pluck('purchase_requisition_sparepart_id')->filter()->unique()->toArray();

            foreach ($oldDetails as $oldDetail) {
                if ($oldDetail->purchase_requisition_sparepart_id) {
                    $pr = PurchaseRequisitionSparepart::find($oldDetail->purchase_requisition_sparepart_id);
                    if ($pr) {
                        $prDetail = $pr->details()->where('id', $oldDetail->purchase_requisition_sparepart_detail_id)->first();
                        if ($prDetail) {
                            $prDetail->update(['qty_left' => $prDetail->qty_left + $oldDetail->qty]);
                        }
                    }
                }
            }

            foreach ($oldPRs as $prId) {
                $purchaseRequisition = PurchaseRequisitionSparepart::find($prId);
                if ($purchaseRequisition) {
                    $remainingQtyLeft = $purchaseRequisition->details()->sum('qty_left');
                    $newStatus = $remainingQtyLeft == $purchaseRequisition->details()->sum('qty') ? 'Not' : 'partial';
                    $purchaseRequisition->update(['status' => $newStatus]);
                }
            }

            $purchaseOrder->update([
                'department_id' => $request->department_id,
                'supplier_id' => $request->supplier_id,
                'warehouse_id' => $request->warehouse_id,
                'notes' => $request->notes,
                'subtotal' => $subtotal,
                'discount' => $discount,
                'tax' => $tax,
                'total' => $total,
                'tax_code' => $request->tax_code,
                'tax_revenue' => $request->tax_revenue,
                'tax_tariff' => $tax_tariff,
                'tax_revenue_tariff' => $tax_revenue_tariff,
                'updated_by' => auth()->id(),
            ]);

            $purchaseOrder->details()->delete();

            $prUpdates = [];
            $revenueTax = 0;
            $addTax = 0;
            $services = 0;
            $taxed = 0;
            foreach ($details as $detail) {
                $sparepart = Sparepart::find($detail['sparepart_id']);
                $detailSubtotal = ($detail['qty'] * $detail['price']) - ($detail['discount'] ?? 0);

                if ($sup->pkp == 1) {
                    if ($sup->include == 1) {
                        $totalAllAfterDiscountBeforeTax += (($detail['qty'] * $detail['price']) / (1 + $taxs->tariff / 100)) - ($detail['discount'] ?? 0);
                    } else {
                        $totalAllAfterDiscountBeforeTax += $detail['qty'] * $detail['price'] - ($detail['discount'] ?? 0);
                    }
                } else {
                    $totalAllAfterDiscountBeforeTax += $detail['qty'] * $detail['price'] - ($detail['discount'] ?? 0);
                }

                $totalPriceBeforeTaxBeforeDiscount = 0;
                $totalPriceBeforeTaxAfterDiscount = 0;
                $totalDiscountPerDetail = 0;
                $discPerDetail = 0;
                if ($sup->pkp == 1) {
                    if (strtolower($sparepart->type) == 'service') {
                        $services += $detail['qty'] * $detail['price'];
                    }

                    if ($sup->include == 1) {
                        $totalPriceBeforeTaxBeforeDiscount = ($detail['qty'] * $detail['price']) / (1 + $taxs->tariff / 100);
                        $totalPriceBeforeTaxAfterDiscount = $totalPriceBeforeTaxBeforeDiscount - ($detail['discount'] ?? 0);
                        $discPerDetail = $totalAllAfterDiscountBeforeTax > 0 ? $totalPriceBeforeTaxAfterDiscount / $totalAllAfterDiscountBeforeTax * ($purchaseOrder->discount ?? 0) : 0;
                        $totalPriceBeforeTaxAfterDiscount -= $discPerDetail;
                        $totalDiscountPerDetail = ($detail['discount'] ?? 0) + $discPerDetail;
                        $totalAllDiscountDetail += ($detail['discount'] ?? 0);
                        $taxed += $totalPriceBeforeTaxAfterDiscount;
                    } else {
                        $totalPriceBeforeTaxBeforeDiscount = $detail['qty'] * $detail['price'];
                        $totalPriceBeforeTaxAfterDiscount = $totalPriceBeforeTaxBeforeDiscount - ($detail['discount'] ?? 0);
                        $discPerDetail = $totalAllAfterDiscountBeforeTax > 0 ? $totalPriceBeforeTaxAfterDiscount / $totalAllAfterDiscountBeforeTax * ($purchaseOrder->discount ?? 0) : 0;
                        $totalPriceBeforeTaxAfterDiscount -= $discPerDetail;
                        $totalDiscountPerDetail = ($detail['discount'] ?? 0) + $discPerDetail;
                        $totalAllDiscountDetail += ($detail['discount'] ?? 0);
                        $taxed += $totalPriceBeforeTaxAfterDiscount;
                    }
                } else {
                    $revenueTax = 0;
                    $addTax = 0;
                    $totalPriceBeforeTaxBeforeDiscount = $detail['qty'] * $detail['price'];
                    $totalPriceBeforeTaxAfterDiscount = $totalPriceBeforeTaxBeforeDiscount - ($detail['discount'] ?? 0);
                    $discPerDetail = $totalAllAfterDiscountBeforeTax > 0 ? $totalPriceBeforeTaxAfterDiscount / $totalAllAfterDiscountBeforeTax * ($purchaseOrder->discount ?? 0) : 0;
                    $totalPriceBeforeTaxAfterDiscount -= $discPerDetail;
                    $totalDiscountPerDetail = ($detail['discount'] ?? 0) + $discPerDetail;
                    $totalAllDiscountDetail += ($detail['discount'] ?? 0);
                }
                $totalAllItemBeforeTax += $totalPriceBeforeTaxAfterDiscount;
                $add_tax_detail = ($sup->pkp == 1) ? $totalPriceBeforeTaxAfterDiscount * $taxs->tax_base * $taxs->tariff / 100 : null;

                $sparepartDetail = $sparepart->details()->where('unit_id', $detail['unit'])->first();

                PurchaseOrderSparepartDetail::create([
                    'purchase_order_sparepart_id' => $purchaseOrder->id,
                    'purchase_requisition_sparepart_id' => $detail['purchase_requisition_sparepart_id'] ?? null,
                    'purchase_requisition_sparepart_detail_id' => $detail['purchase_requisition_sparepart_detail_id'] ?? null,
                    'sparepart_id' => $detail['sparepart_id'],
                    'unit_id' => $detail['unit'],
                    'qty' => $detail['qty'],
                    'price' => $detail['price'],
                    'discount' => $detail['discount'] ?? 0,
                    'subtotal' => $detailSubtotal,
                    'base_unit' => $detail['unit'],
                    'base_qty' => $sparepartDetail ? $sparepartDetail->conversion : 1,
                    'qty_left' => $detail['qty'],
                    'add_tax_detail' => $add_tax_detail,
                    'description' => $detail['description'],
                    'created_by' => auth()->id(),
                    'updated_by' => auth()->id(),
                ]);

                if ($detail['purchase_requisition_sparepart_id']) {
                    $prId = $detail['purchase_requisition_sparepart_id'];
                    $purchaseRequisition = PurchaseRequisitionSparepart::find($prId);
                    if ($purchaseRequisition) {
                        $prDetail = $purchaseRequisition->details()->where('id', $detail['purchase_requisition_sparepart_detail_id'])->first();
                        if ($prDetail) {
                            $newQtyLeft = max(0, $prDetail->qty_left - $detail['qty']);
                            $prDetail->update(['qty_left' => $newQtyLeft]);
                            if (!isset($prUpdates[$prId])) {
                                $prUpdates[$prId] = $purchaseRequisition;
                            }
                        }
                    }
                }
            }

            foreach ($prUpdates as $prId => $purchaseRequisition) {
                $remainingQtyLeft = $purchaseRequisition->details()->sum('qty_left');
                $newStatus = $remainingQtyLeft == 0 ? 'order' : 'partial';
                $purchaseRequisition->update(['status' => $newStatus]);
            }

            if ($sup->pkp == 1) {
                $addTax = $taxed * $taxs->tax_base * $taxs->tariff / 100;
                $revenueTax = $services * $tax_revenue_tariff / 100;
            } else {
                $revenueTax = 0;
                $addTax = 0;
            }
            $purchaseOrder->tax = $addTax;
            $purchaseOrder->add_tax_revenue = $revenueTax;
            $purchaseOrder->subtotal = $totalAllItemBeforeTax;
            $purchaseOrder->total = $totalAllItemBeforeTax + $purchaseOrder->tax + $purchaseOrder->add_tax_revenue;
            $purchaseOrder->save();

            DB::commit();
            return response()->json(null);
        } catch (\Exception $e) {
            DB::rollback();
            return response()->json(['error' => 'Server error'], 500);
        }
    }

    public function destroy(Request $request, $id)
    {
        try {
            DB::beginTransaction();
            $purchaseOrder = PurchaseOrderSparepart::findOrFail($id);
            $oldDetails = $purchaseOrder->details()->get();
            $prIds = $oldDetails->pluck('purchase_requisition_sparepart_id')->filter()->unique()->toArray();

            foreach ($oldDetails as $oldDetail) {
                if ($oldDetail->purchase_requisition_sparepart_id) {
                    $pr = PurchaseRequisitionSparepart::find($oldDetail->purchase_requisition_sparepart_id);
                    if ($pr) {
                        $prDetail = $pr->details()->where('id', $oldDetail->purchase_requisition_sparepart_detail_id)->first();
                        if ($prDetail) {
                            $newQtyLeft = $prDetail->qty_left + $oldDetail->qty;
                            $prDetail->update(['qty_left' => $newQtyLeft]);
                        }
                    }
                }
            }

            $purchaseOrder->details()->delete();
            $purchaseOrder->delete();

            DeleteLog::create([
                'document_number' => $purchaseOrder->purchase_order_number,
                'document_date' => $purchaseOrder->document_date,
                'delete_notes' => $request->delete_notes ?? 'No notes provided',
                'company_code' => null,
                'department_code' => $purchaseOrder->department->department_code,
                'deleted_by' => Auth::check() ? Auth::user()->name : 'system',
                'type' => 'Purchase Order Sparepart',
            ]);

            foreach ($prIds as $prId) {
                $purchaseRequisition = PurchaseRequisitionSparepart::find($prId);
                if ($purchaseRequisition) {
                    $remainingQtyLeft = $purchaseRequisition->details()->sum('qty_left');
                    $newStatus = $remainingQtyLeft == $purchaseRequisition->details()->sum('qty') ? 'Not' : 'partial';
                    $purchaseRequisition->update(['status' => $newStatus]);
                }
            }

            DB::commit();
            return response()->json(null, 204);
        } catch (QueryException $e) {
            DB::rollBack();
            if ($e->getCode() === '23000') {
                return response()->json(['error' => 'Cannot delete purchase order because it is being used in other records'], 422);
            }
            return response()->json(['error' => 'Failed to delete purchase order'], 500);
        } catch (\Exception $e) {
            DB::rollBack();
            return response()->json(['error' => 'Failed to delete purchase order'], 500);
        }
    }

    public function cancel(Request $request, $id)
    {
        DB::beginTransaction();
        try {
            $purchaseOrder = PurchaseOrderSparepart::find($id);
            if (!$purchaseOrder) {
                return response()->json(['message' => 'Purchase order not found'], 404);
            }

            $validator = Validator::make($request->all(), [
                'cancel_notes' => 'required|string',
            ]);

            if ($validator->fails()) {
                return response()->json(['errors' => $validator->errors()], 422);
            }

            $purchaseOrder->status = "canceled";
            $purchaseOrder->save();

            foreach ($purchaseOrder->details as $detail) {
                if ($detail->qty_left > 0) {
                    $detail->update(["qty_left" => 0, "is_cancel" => 1, "cancel_notes" => $request->cancel_notes, "canceled_at" => now(), "canceled_by" => auth()->id()]);
                }
            }

            DB::commit();
            return response()->json($purchaseOrder->load('details'));
        } catch (\Exception $e) {
            DB::rollback();
            return response()->json(['error' => 'Server error'], 500);
        }
    }

    public function getMasterData()
    {
        $Listdepartment = $this->getDepartment();
        $departments = Department::whereIn("id",$Listdepartment)->get();
        $cat = CategorySupplier::whereIn("category_supplier_code",['Jasa','Sparepart'])->select('category_supplier_code')->pluck("category_supplier_code")->toArray();
        $suppliers = Supplier::with(['currency'])->whereIn("category_supplier",$cat)->get();

        $taxMaster = TaxMaster::all();
        $currencies = Currency::all();
        $purchaseRequisitions = PurchaseRequisitionSparepart::with(['details.sparepart', 'details.unitRelation', 'details' => function ($q) {
            $q->where("qty_left", ">", 0);
        }])->whereRaw('id in (select distinct purchase_req_id from purchase_requisition_sparepart_details where qty_left > 0)')
        ->whereIn("department_id",$Listdepartment)->get();

        $sparepartPurchases = PurchaseSparepart::with(['sparepart', 'supplier'])->get();
        $spareparts = Sparepart::with(['details.unit'])->get();
        $itemUnit = ItemUnit::get();
        $gudangUser = DB::table("user_warehouse")->where("user_id",Auth::user()->id)->select("warehouse_id")->get()->pluck("warehouse_id")->toArray();
        $warehouse = Warehouse::whereIn("id",$gudangUser)->get();
        $company = Company::first();
        $date = Carbon::now();
        $nobukti = Module::generateDocumentNumber($company->companyCode, $departments->first()->department_code, $date->month, $date->year, "POSP", 0);

        $response = [
            'departments' => $departments,
            'suppliers' => $suppliers,
            'purchase_requisitions' => $purchaseRequisitions,
            'sparepart_purchases' => $sparepartPurchases,
            'spareparts' => $spareparts,
            'item_unit' => $itemUnit,
            'warehouses' => $warehouse,
            'nobukti' => $nobukti,
            'taxMaster' => $taxMaster,
            'currencies' => $currencies,
            "role" => $this->getRole()
        ];
        return response()->json($response);
    }

    public function getNomorBukti(Request $request)
    {
        $company = Company::first();
        $department = Department::find($request->department_id);
        $date = Carbon::parse($request->document_date);
        $nobukti = Module::generateDocumentNumber($company->companyCode, $department->department_code, $date->month, $date->year, "POSP", 0);
        return response()->json($nobukti);
    }

    public function updatePurchaseRequisition(Request $request, $id)
    {
        $validator = Validator::make($request->all(), [
            'status' => 'required|in:Not,partial,order',
            'details' => 'required|array',
            'details.*.sparepart_id' => 'required|exists:spareparts,id',
            'details.*.qty' => 'required|numeric|min:0',
            'details.*.qty_left' => 'required|numeric|min:0',
        ]);

        if ($validator->fails()) {
            return response()->json(['errors' => $validator->errors()], 422);
        }

        DB::beginTransaction();

        try {
            $purchaseRequisition = PurchaseRequisitionSparepart::find($id);
            if (!$purchaseRequisition) {
                return response()->json(['error' => 'Purchase requisition not found'], 404);
            }

            $purchaseRequisition->update(['status' => $request->status]);

            foreach ($request->details as $detail) {
                $prDetail = $purchaseRequisition->details()->where('sparepart_id', $detail['sparepart_id'])->first();
                if ($prDetail) {
                    $prDetail->update([
                        'qty' => $detail['qty'],
                        'qty_left' => $detail['qty_left'],
                    ]);
                }
            }

            DB::commit();
            return response()->json($purchaseRequisition->load('details'));
        } catch (\Exception $e) {
            DB::rollBack();
            return response()->json(['error' => 'Failed to update purchase requisition'], 500);
        }
    }

    public function print($type, $id)
    {
        $purchaseOrders = PurchaseOrderSparepart::with(['details.sparepart', 'supplier.currency'])->where("id", $id)->firstOrFail();
        $iu = ItemUnit::get();
        $imagePath = storage_path('app/images/logo-only.png');
        $imageData = file_get_contents($imagePath);

        foreach ($purchaseOrders->details as $detail) {
            $detail->unit = $iu->where("id", $detail->unit_id)->first()->unit_name;
        }
        $username = Auth::user()->name;
        $totalHuruf = ucwords($this->numberToWords($purchaseOrders->total)) . ' Rupiah';
        $pdf = \PDF::loadView('print.purchase_order_sparepart_pdf', compact('purchaseOrders', 'totalHuruf', 'imageData', 'type', 'username'))->setPaper('A5', 'landscape');
        return ["data" => "data:application/pdf;base64," . base64_encode($pdf->stream()), "role" => $this->getRole()];
    }

    public function increasePrintNumber($id)
    {
        $po = PurchaseOrderSparepart::find($id);
        $po->print_number = ($po->print_number ?? 0) + 1;
        $po->save();
        return response()->json(null);
    }

    public function gridList()
    {
        $purchaseDetails = PurchaseOrderSparepartDetail::with(['purchaseOrder.supplier', 'sparepart', 'unitRelation', 'requisition'])
            ->get()
            ->groupBy('purchase_order_sparepart_id')
            ->map(function ($details) {
                $purchaseOrder = $details->first()->purchaseOrder;
                $inventoryDetails = InventoryDetail::selectRaw('sparepart_id, AVG(total / quantity) as avg_price')
                    ->whereIn('sparepart_id', $details->pluck('sparepart_id'))
                    ->where('transaction_type', 'Purchase Invoice')
                    ->groupBy('sparepart_id')
                    ->get()
                    ->keyBy('sparepart_id');

                return $details->map(function ($detail) use ($purchaseOrder, $inventoryDetails) {
                $totalQty = $purchaseOrder->details->sum('qty');
                $receivedQty = $purchaseOrder->details->sum(function ($d) {
                        return $d->qty - ($d->requisition->qty_left ?? 0);
                    });
                    $status = ($receivedQty >= $totalQty) ? 'Received' : 'Process';
                    $avgPrice = $inventoryDetails->get($detail->sparepart_id)?->avg_price ?? 0;

                    $lastPrice = PurchaseOrderSparepartDetail::where('sparepart_id', $detail->sparepart_id)
                    ->where('unit_id', $detail->unit_id)
                        ->orderBy('created_at', 'desc')
                        ->first()
                    ?->price ?? 0;

                    return [
                        'id' => $purchaseOrder->purchase_order_number . '-' . $detail->id,
                        'vendor' => $purchaseOrder->supplier->supplier_name ?? 'N/A',
                    'sparepart' => $detail->sparepart->sparepart_name ?? 'N/A',
                        'unit' => $detail->unitRelation->unit_name ?? 'N/A',
                        'qty' => $detail->qty,
                        'status' => $status,
                        'last_price' => $lastPrice,
                        'avg_price' => $avgPrice,
                    ];
                });
            })
            ->flatten(1);

        return response()->json(['data' => $purchaseDetails, 'role' => $this->getRole()]);
    }

    public function hitungUlang(){
        $head = PurchaseOrderSparepart::where("tax_revenue","PPN")->get();
        try {
            DB::beginTransaction();
            
            if($head){
                foreach($head as $header){
                    $tax = 0;
                    $totalAllItemBeforeTax = 0;
                    $totalAllDiscountDetail = 0;
                    $totalAllAfterDiscountBeforeTax = 0;
                    $tax_tariff = null;
                    $tax_revenue_tariff = null;
                    $sup = Supplier::find($header->supplier_id);

                    if($header->tax_revenue == "PPN"){
                        $header->tax_code = $header->tax_revenue;
                        $header->tax_revenue = null;
                    }

                    if ($header->tax_revenue) {
                        $tax_revenue_tariff = TaxMaster::where("tax_code", $header->tax_revenue)->first()->tariff;
                    }
                    
                    $taxs = TaxMaster::where('tax_code', $header->tax_code)->first();
                    if ($sup) {
                        $tax_tariff = ($sup->pkp == 1) ? TaxMaster::where("tax_code", $header->tax_code)->first()->tariff : null;
                    }

                    $curConversion = 1;
                    if (!is_null($sup->currency_id)) {
                        $cur = Currency::find($sup->currency_id);
                        if ($cur->is_base == false) {
                            $conver = CurrencyConversion::where("to_currency_id", $sup->currency_id)
                                ->where("date_start", "<=", $header->document_date)->orderBy("date_start", "desc")->orderBy("created_at", "desc")->first();
                            if ($conver) {
                                $curConversion = $conver->conversion;
                            }
                        }
                    }

                    $purchaseOrder = PurchaseOrderSparepart::find($header->id);

                    $purchaseOrder->update([
                        'tax_code' => $header->tax_code,
                        'tax_revenue' => $header->tax_revenue,
                        'tax_tariff' => $tax_tariff,
                        'tax_revenue_tariff' => $tax_revenue_tariff
                    ]);

                    $revenueTax = 0;$addTax = 0;$services = 0;$taxed = 0;$totalAllItemBeforeTax = 0;
                    foreach($header->details()->get() as $detail){
                        $sparepart = Sparepart::find($detail->sparepart_id);
                        $detailSubtotal = ($detail->qty * $detail->price) - ($detail->discount ?? 0);

                        if ($sup->pkp == 1) {
                            if ($sup->include == 1) {
                                $totalAllAfterDiscountBeforeTax += (($detail->qty * $detail->price) / (1 + $taxs->tariff / 100)) - ($detail->discount ?? 0);
                            } else {
                                $totalAllAfterDiscountBeforeTax += $detail->qty * $detail->price - ($detail->discount ?? 0);
                            }
                        } else {
                            $totalAllAfterDiscountBeforeTax += $detail->qty * $detail->price - ($detail->discount ?? 0);
                        }

                        $totalPriceBeforeTaxBeforeDiscount = 0;
                        $totalPriceBeforeTaxAfterDiscount = 0;
                        $totalDiscountPerDetail = 0;
                        $discPerDetail = 0;
                        if ($sup->pkp == 1) {
                            if (strtolower($sparepart->type) == 'service') {
                                $services += $detail->qty * $detail->price;
                            }

                            if ($sup->include == 1) {
                                $totalPriceBeforeTaxBeforeDiscount = ($detail->qty * $detail->price) / (1 + $taxs->tariff / 100);
                                $totalPriceBeforeTaxAfterDiscount = $totalPriceBeforeTaxBeforeDiscount - ($detail->discount ?? 0);
                                $discPerDetail = $totalAllAfterDiscountBeforeTax > 0 ? $totalPriceBeforeTaxAfterDiscount / $totalAllAfterDiscountBeforeTax * ($purchaseOrder->discount ?? 0) : 0;
                                $totalPriceBeforeTaxAfterDiscount -= $discPerDetail;
                                $totalDiscountPerDetail = ($detail->discount ?? 0) + $discPerDetail;
                                $totalAllDiscountDetail += ($detail->discount ?? 0);
                                $taxed += $totalPriceBeforeTaxAfterDiscount;
                            } else {
                                $totalPriceBeforeTaxBeforeDiscount = $detail->qty * $detail->price;
                                $totalPriceBeforeTaxAfterDiscount = $totalPriceBeforeTaxBeforeDiscount - ($detail->discount ?? 0);
                                $discPerDetail = $totalAllAfterDiscountBeforeTax > 0 ? $totalPriceBeforeTaxAfterDiscount / $totalAllAfterDiscountBeforeTax * ($purchaseOrder->discount ?? 0) : 0;
                                $totalPriceBeforeTaxAfterDiscount -= $discPerDetail;
                                $totalDiscountPerDetail = ($detail->discount ?? 0) + $discPerDetail;
                                $totalAllDiscountDetail += ($detail->discount ?? 0);
                                $taxed += $totalPriceBeforeTaxAfterDiscount;
                            }
                        } else {
                            $revenueTax = 0;
                            $addTax = 0;
                            $totalPriceBeforeTaxBeforeDiscount = $detail->qty * $detail->price;
                            $totalPriceBeforeTaxAfterDiscount = $totalPriceBeforeTaxBeforeDiscount - ($detail->discount ?? 0);
                            $discPerDetail = $totalAllAfterDiscountBeforeTax > 0 ? $totalPriceBeforeTaxAfterDiscount / $totalAllAfterDiscountBeforeTax * ($purchaseOrder->discount ?? 0) : 0;
                            $totalPriceBeforeTaxAfterDiscount -= $discPerDetail;
                            $totalDiscountPerDetail = ($detail->discount ?? 0) + $discPerDetail;
                            $totalAllDiscountDetail += ($detail->discount ?? 0);
                        }
                        $totalAllItemBeforeTax += $totalPriceBeforeTaxAfterDiscount;
                        $add_tax_detail = ($sup->pkp == 1) ? $totalPriceBeforeTaxAfterDiscount * $taxs->tax_base * $taxs->tariff / 100 : null;

                        $det = PurchaseOrderSparepartDetail::find($detail->id);
                        $det->update([
                            'subtotal' => $detailSubtotal,
                            'add_tax_detail' => $add_tax_detail,
                        ]);
                    }
                    if ($sup->pkp == 1) {
                        $addTax = $taxed * $taxs->tax_base * $taxs->tariff / 100;
                        $revenueTax = $services * $tax_revenue_tariff / 100;
                    } else {
                        $revenueTax = 0;
                        $addTax = 0;
                    }
                    $purchaseOrder->tax = $addTax;
                    $purchaseOrder->add_tax_revenue = $revenueTax;
                    $purchaseOrder->subtotal = $totalAllItemBeforeTax;
                    $purchaseOrder->total = $totalAllItemBeforeTax + $purchaseOrder->tax + $purchaseOrder->add_tax_revenue;
                    $purchaseOrder->save();
                }
            }

            DB::commit();
        } catch (\Exception $e) {
            DB::rollback();
            //dd($e->getMessage());
            dd($e->getTrace());
            return response()->json(['error' => 'Failed'], 500);
        }
    }
}