<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Hash;
use Carbon\Carbon;
use App\Services\AccountingService;

class SalesOrderController extends Controller
{
    /**
     * ========================================================================
     * FITUR BARU: AUTO-GRADING PELANGGAN BERDASARKAN UMUR PIUTANG (AR AGING)
     * ========================================================================
     */
    private function _calculateCustomerGrade($customerId)
    {
        // Tarik data 3 bulan terakhir (90 Hari)
        $threeMonthsAgo = Carbon::today()->subDays(90)->toDateString();

        $creditSales = DB::table('sales')
            ->where('customer_id', $customerId)
            ->where('payment_method', 'kredit')
            ->where('delivery_status', '!=', 'void')
            ->where('created_at', '>=', $threeMonthsAgo)
            ->get();

        if ($creditSales->isEmpty()) {
            return 'A'; 
        }

        $maxDaysToPay = 0;

        foreach ($creditSales as $sale) {
            $invoiceDate = Carbon::parse($sale->created_at);

            $lastPayment = DB::table('debt_payments')
                ->where('sale_id', $sale->id)
                ->where('payment_status', 'completed')
                ->orderBy('payment_date', 'desc')
                ->first();

            if ($sale->payment_status === 'paid' && $lastPayment) {
                $payDate = Carbon::parse($lastPayment->payment_date);
                $days = $invoiceDate->diffInDays($payDate);
            } else {
                $days = $invoiceDate->diffInDays(Carbon::today());
            }

            if ($days > $maxDaysToPay) {
                $maxDaysToPay = $days;
            }
        }

        if ($maxDaysToPay <= 30) return 'A'; 
        if ($maxDaysToPay <= 60) return 'B'; 
        if ($maxDaysToPay <= 90) return 'C'; 
        return 'D';                          
    }

    private function _calculateCartInternal($customerId, $items, $taxMethod)
    {
        try {
            $customer = DB::table('customers')->where('id', $customerId)->first();
            
            $subtotal = 0;
            $globalDiscount = 0;
            $processedItems = [];
            $bonusItems = [];

            $today = Carbon::today()->toDateString();
            $activePrograms = DB::table('sales_programs')
                ->where('is_active', 1)
                ->where('start_date', '<=', $today)
                ->where('end_date', '>=', $today)
                ->get();

            foreach ($items as $reqItem) {
                $qty = (float) ($reqItem['qty'] ?? 0);
                if ($qty <= 0) continue;

                $product = DB::table('products')->where('id', $reqItem['product_id'])->first();
                if (!$product) continue;

                $basePrice = (float) ($reqItem['price'] ?? 0);
                $itemTotalPrice = $basePrice * $qty;
                $itemDiscount = 0;
                $appliedProgramId = null;

                $unitName = $reqItem['unit'] ?? $reqItem['unit_name'] ?? $product->unit ?? 'Pcs';

                foreach ($activePrograms as $promo) {
                    $targetTypes = is_string($promo->target_customer_types) ? json_decode($promo->target_customer_types, true) : $promo->target_customer_types;
                    
                    if (!empty($targetTypes) && is_array($targetTypes) && count($targetTypes) > 0) {
                        if (!in_array($customer->customer_type_id, $targetTypes)) {
                            continue; 
                        }
                    }

                    $isMatch = false;
                    
                    if ($promo->scope === 'global') $isMatch = true;
                    elseif ($promo->scope === 'category' && $promo->category_id == $product->category_id) $isMatch = true;
                    elseif ($promo->scope === 'product' && $promo->product_id == $product->id) $isMatch = true;

                    if ($isMatch) {
                        $validTier = DB::table('sales_program_tiers')
                            ->where('sales_program_id', $promo->id)
                            ->where('min_qty', '<=', $qty)
                            ->orderBy('min_qty', 'desc')
                            ->first();

                        if ($validTier) {
                            $appliedProgramId = $promo->id;
                            $multiplier = ($validTier->is_multiple) ? floor($qty / $validTier->min_qty) : 1;
                            
                            if ($validTier->reward_type === 'discount_percent') {
                                $pct = (float) $validTier->reward_value;
                                $itemDiscount = $itemTotalPrice * ($pct / 100);
                            } elseif ($validTier->reward_type === 'discount_nominal') {
                                $itemDiscount = (float) $validTier->reward_value * $multiplier;
                            } elseif ($validTier->reward_type === 'bonus_item') {
                                $bonusProduct = DB::table('products')->where('id', $validTier->bonus_product_id)->first();
                                if ($bonusProduct) {
                                    $bonusFullName = $bonusProduct->variant_name 
                                        ? $bonusProduct->name . ' - ' . $bonusProduct->variant_name 
                                        : $bonusProduct->name;

                                    $bonusItems[] = [
                                        'product_id' => $bonusProduct->id,
                                        'product_name' => $bonusFullName,
                                        'qty' => $validTier->bonus_qty * $multiplier,
                                        'unit' => $bonusProduct->unit ?? 'Pcs',
                                        'program_name' => $promo->nama_program
                                    ];
                                }
                            }
                            break; 
                        }
                    }
                }

                $netTotalPrice = $itemTotalPrice - $itemDiscount;
                $subtotal += $itemTotalPrice;
                $globalDiscount += $itemDiscount;

                $productFullName = $product->variant_name 
                    ? $product->name . ' - ' . $product->variant_name 
                    : $product->name;

                $processedItems[] = [
                    'product_id' => $product->id,
                    'product_name' => $productFullName,
                    'qty' => $qty,
                    'unit' => $unitName, 
                    'price' => $basePrice,
                    'discount_amount' => $itemDiscount,
                    'total_price' => $netTotalPrice,
                    'applied_program_id' => $appliedProgramId
                ];
            }

            $taxMethod = $taxMethod ?? 'include';

            $taxSetting = DB::table('settings')->where('key', 'ppn_rate')->first();
            $ppnRate = $taxSetting ? ((float) $taxSetting->value / 100) : 0.11;

            $netTotalCart = $subtotal - $globalDiscount;

            $dpp = 0;
            $taxAmount = 0;
            $grandTotal = 0;

            if ($taxMethod === 'exclude') {
                $dpp = $netTotalCart;
                $taxAmount = $dpp * $ppnRate;
                $grandTotal = $dpp + $taxAmount;
            } elseif ($taxMethod === 'include') {
                $grandTotal = $netTotalCart;
                $dpp = $grandTotal / (1 + $ppnRate);
                $taxAmount = $grandTotal - $dpp;
            } else {
                $dpp = $netTotalCart;
                $taxAmount = 0;
                $grandTotal = $dpp;
            }

            return [
                'success' => true,
                'data' => [
                    'subtotal' => $subtotal,
                    'discount_amount' => $globalDiscount,
                    'dpp' => round($dpp, 2),
                    'tax_amount' => round($taxAmount, 2),
                    'grand_total' => round($grandTotal, 2),
                    'tax_method' => $taxMethod,
                    'items' => $processedItems,
                    'bonus_items' => $bonusItems
                ]
            ];

        } catch (\Exception $e) {
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }

    public function getPendingQueue(Request $request)
    {
        try {
            $user = $request->user(); 
            if (!$user) return response()->json(['success' => false, 'message' => 'Unauthenticated.'], 401);

            $requestedBranch = $request->query('branch_id');
            $branchId = ($user->role === 'owner') ? $requestedBranch : $user->branch_id;

            $query = DB::table('sales_orders')
                ->whereIn('status', ['pending', 'reschedule', 'partially_delivered']);
                
            if (!empty($branchId) && $branchId !== 'all' && $branchId !== 'null' && $branchId !== 'undefined') {
                $query->where('fulfillment_branch_id', $branchId);
            }

            $dateFilter = $request->query('date') ?? $request->query('delivery_date');
            if (!empty($dateFilter) && $dateFilter !== 'all') {
                $query->whereDate('delivery_date', $dateFilter);
            }
            
            $orders = $query->orderBy('created_at', 'asc')->get();

            $filteredOrders = []; 

            foreach ($orders as $order) {
                $items = DB::table('sales_order_items')
                    ->join('products', 'sales_order_items.product_id', '=', 'products.id')
                    ->leftJoin('product_stocks', function($join) use ($order) {
                        $join->on('sales_order_items.product_id', '=', 'product_stocks.product_id')
                             ->where('product_stocks.branch_id', '=', $order->fulfillment_branch_id);
                    })
                    ->where('sales_order_items.sales_order_id', $order->id) 
                    ->whereRaw('sales_order_items.qty > sales_order_items.qty_delivered')
                    ->select(
                        'sales_order_items.*', 
                        'products.name as product_name', 
                        'products.variant_name', 
                        'products.sku',
                        'products.unit as unit_1',              
                        'products.buy_unit as unit_2',          
                        'products.unit_3_name as unit_3',       
                        'products.unit_4_name as unit_4',       
                        'products.conversion_factor as conv_2',  
                        'products.conversion_3_qty as conv_3',   
                        'products.conversion_4_qty as conv_4',   
                        'product_stocks.quantity as physical_qty',
                        'product_stocks.reserved_qty'
                    )
                    ->get();
                
                if (count($items) > 0) {
                    foreach ($items as $item) {
                        $item->product_name = $item->variant_name ? $item->product_name . ' - ' . $item->variant_name : $item->product_name;
                        
                        $availBase = (float)($item->physical_qty ?? 0) - (float)($item->reserved_qty ?? 0);
                        $uOrder = strtoupper(trim($item->unit_name));
                        
                        $finalAvailable = $availBase; 

                        if ($uOrder === strtoupper(trim($item->unit_2 ?? '')) && $item->conv_2 > 0) {
                            $finalAvailable = $availBase / $item->conv_2;
                        } elseif ($uOrder === strtoupper(trim($item->unit_3 ?? '')) && $item->conv_3 > 0) {
                            $finalAvailable = $availBase / $item->conv_3;
                        } elseif ($uOrder === strtoupper(trim($item->unit_4 ?? '')) && $item->conv_4 > 0) {
                            $finalAvailable = $availBase / $item->conv_4;
                        }

                        $item->stock_ready_converted = floor($finalAvailable);
                    }
                    $order->items = $items;
                    $filteredOrders[] = $order; 
                }
            }

            $driverQuery = DB::table('users')->where('role', 'driver');
            $helperQuery = DB::table('users')->where('role', 'helper');

            if (!empty($branchId) && $branchId !== 'all' && $branchId !== 'null' && $branchId !== 'undefined') {
                $driverQuery->where(function($q) use ($branchId) {
                    $q->where('branch_id', $branchId)->orWhereNull('branch_id');
                });
                
                $helperQuery->where(function($q) use ($branchId) {
                    $q->where('branch_id', $branchId)->orWhereNull('branch_id');
                });
            }
            
            $drivers = $driverQuery->select('id', 'name')->get();
            $helpers = $helperQuery->select('id', 'name')->get(); 

            return response()->json([
                'success' => true,
                'data' => $filteredOrders, 
                'drivers' => $drivers,
                'helpers' => $helpers 
            ]);

        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    public function processBulkDelivery(Request $request)
    {
        $request->validate([
            'driver_id' => 'required|exists:users,id',
            'helper_id' => 'nullable|exists:users,id', 
            'delivery_date' => 'required|date',
            'deliveries' => 'required|array'
        ]);

        $userId = auth()->id();
        if (!$userId) return response()->json(['success' => false, 'message' => 'Sesi tidak valid.'], 401);

        $salesman = \App\Models\Salesman::where('user_id', $userId)->first();
        $isCanvas = $salesman && $salesman->salesman_type === 'CANVAS';

        DB::beginTransaction();
        try {
            $processedInvoices = [];
            $newSalesIds = []; 

            foreach ($request->deliveries as $delivery) {
                $so = DB::table('sales_orders')
                    ->where('id', $delivery['so_id'])
                    ->lockForUpdate() 
                    ->first();
                
                if (!$so || !in_array($so->status, ['pending', 'reschedule', 'partially_delivered'])) {
                    continue; 
                }

                $dueDays = $so->due_days ?? 0;
                $customerData = DB::table('customers')->where('id', $so->customer_id)->first();
                
                if ($dueDays == 0) {
                    $dueDays = $customerData->due_days ?? 0;
                }

                $dueDate = null;
                if ($so->payment_method === 'kredit' && $dueDays > 0) {
                    $dueDate = Carbon::parse($request->delivery_date)->addDays($dueDays)->toDateString();
                }

                $hasValidItem = false;
                $deliverySubtotal = 0;
                $totalCogs = 0; 
                
                $baseInvoice = str_replace('SO-', 'INV-', $so->so_number);
                
                $validDoCount = DB::table('sales')
                    ->where('sales_order_id', $so->id)
                    ->where('delivery_status', '!=', 'void')
                    ->count();
                
                if ($validDoCount > 0) {
                    $baseInvoice .= '-' . ($validDoCount + 1);
                }

                $voidCount = DB::table('sales')
                    ->where('sales_order_id', $so->id)
                    ->where('delivery_status', 'void')
                    ->count();
                
                if ($voidCount > 0) {
                    $invoiceNumber = $baseInvoice . '-R' . $voidCount;
                } else {
                    $invoiceNumber = $baseInvoice;
                }

                $saleItemsToInsert = [];
                $stockUpdates = [];

                foreach ($delivery['items'] as $itemReq) {
                    $qtyLoad = (float) $itemReq['qty_load'];
                    if ($qtyLoad <= 0) continue;
                    
                    $hasValidItem = true;
                    $soItem = DB::table('sales_order_items')->where('id', $itemReq['id'])->first();
                    $product = DB::table('products')->where('id', $soItem->product_id)->first();
                    
                    $unitNetPrice = $soItem->qty > 0 ? ($soItem->total_price / $soItem->qty) : 0;
                    $itemTotalDo = $qtyLoad * $unitNetPrice;
                    $deliverySubtotal += $itemTotalDo;

                    $multiplier = 1;
                    $productFullName = 'Item Penjualan';
                    if ($product) {
                        $productFullName = $product->variant_name ? $product->name . ' - ' . $product->variant_name : $product->name;
                        $uName = strtoupper(trim($soItem->unit_name));
                        if ($uName === strtoupper(trim($product->buy_unit ?? ''))) $multiplier = $product->conversion_factor ?? 1;
                        elseif ($uName === strtoupper(trim($product->unit_3_name ?? ''))) $multiplier = $product->conversion_3_qty ?? 1;
                        elseif ($uName === strtoupper(trim($product->unit_4_name ?? ''))) $multiplier = $product->conversion_4_qty ?? 1;
                        
                        $totalCogs += ((float) ($product->buy_price ?? 0) * ($qtyLoad * $multiplier));
                    }

                    $totalBaseQty = $qtyLoad * $multiplier;

                    $saleItemsToInsert[] = [
                        'product_id' => $soItem->product_id,
                        'product_name' => $productFullName,
                        'unit' => $soItem->unit_name,
                        'quantity' => $qtyLoad,
                        'unit_price' => $unitNetPrice, 
                        'total_price' => $itemTotalDo,
                        'base_conversion_factor' => $multiplier,
                    ];

                    $stockUpdates[] = [
                        'so_item_id' => $soItem->id,
                        'product_id' => $soItem->product_id,
                        'qty_load' => $qtyLoad,
                        'total_base_qty' => $totalBaseQty,
                        'unit_name' => $soItem->unit_name
                    ];
                }

                if (!$hasValidItem) continue;

                $taxSetting = DB::table('settings')->where('key', 'ppn_rate')->first();
                $ppnRate = $taxSetting ? ((float) $taxSetting->value / 100) : 0.11;
                $dpp = 0; $taxAmount = 0; $grandTotal = 0;

                if ($customerData && $customerData->is_pkp == 0) {
                    $dpp = $deliverySubtotal / (1 + $ppnRate);
                    $taxAmount = $deliverySubtotal - $dpp;
                    $grandTotal = $deliverySubtotal + ($validDoCount == 0 ? $so->shipping_fee : 0);
                } else {
                    $defaultTax = $customerData ? $customerData->default_tax_method : 'include';
                    if ($defaultTax === 'exclude') {
                        $dpp = $deliverySubtotal;
                        $taxAmount = $dpp * $ppnRate;
                        $grandTotal = $deliverySubtotal + $taxAmount + ($validDoCount == 0 ? $so->shipping_fee : 0);
                    } elseif ($defaultTax === 'include') {
                        $dpp = $deliverySubtotal / (1 + $ppnRate);
                        $taxAmount = $deliverySubtotal - $dpp;
                        $grandTotal = $deliverySubtotal + ($validDoCount == 0 ? $so->shipping_fee : 0);
                    } else {
                        $dpp = $deliverySubtotal;
                        $grandTotal = $deliverySubtotal + ($validDoCount == 0 ? $so->shipping_fee : 0);
                    }
                }

                $saleId = DB::table('sales')->insertGetId([
                    'sales_order_id' => $so->id,
                    'branch_id' => $so->fulfillment_branch_id,
                    'user_id' => $userId, 
                    'customer_id' => $so->customer_id,
                    'customer_name' => $so->customer_name,
                    'invoice_number' => $invoiceNumber,
                    'subtotal' => $deliverySubtotal,
                    'tax_amount' => $taxAmount, 
                    'discount_amount' => 0, 
                    'shipping_fee' => $validDoCount == 0 ? $so->shipping_fee : 0, 
                    'grand_total' => $grandTotal, 
                    'payment_method' => $so->payment_method,
                    'payment_status' => 'pending',
                    'is_online_order' => str_contains($so->so_number, 'ONL') ? 1 : 0,
                    'delivery_lat' => $so->delivery_lat,
                    'delivery_lng' => $so->delivery_lng,
                    'shipping_address' => $so->shipping_address,
                    'driver_id' => $request->driver_id,
                    'helper_id' => $request->helper_id, 
                    'delivery_status' => 'dikirim',
                    'delivery_date' => $request->delivery_date,
                    'due_date' => $dueDate,
                    'due_days' => $dueDays, 
                    'created_at' => Carbon::now(),
                    'updated_at' => Carbon::now(),
                ]);

                $newSalesIds[] = $saleId;

                foreach ($stockUpdates as $idx => $st) {
                    $si = $saleItemsToInsert[$idx];
                    $si['sale_id'] = $saleId;
                    $si['created_at'] = Carbon::now();
                    $si['updated_at'] = Carbon::now();
                    DB::table('sale_items')->insert($si);

                    if ($isCanvas) {
                        \App\Models\CanvasStock::where('salesman_id', $salesman->id)
                            ->where('product_id', $st['product_id'])
                            ->decrement('quantity', $st['total_base_qty']);
                    } else {
                        $stock = DB::table('product_stocks')->where('branch_id', $so->fulfillment_branch_id)->where('product_id', $st['product_id'])->first();
                        if ($stock) {
                            DB::table('product_stocks')->where('id', $stock->id)->update([
                                'quantity' => DB::raw("quantity - " . $st['total_base_qty']),
                                'reserved_qty' => DB::raw("GREATEST(reserved_qty - " . $st['total_base_qty'] . ", 0)")
                            ]);
                        }
                    }

                    DB::table('stock_transactions')->insert([
                        'branch_id' => $so->fulfillment_branch_id,
                        'product_id' => $st['product_id'],
                        'type' => 'sale',
                        'input_quantity' => -$st['qty_load'],
                        'input_unit' => $st['unit_name'],
                        'actual_quantity' => -$st['total_base_qty'],
                        'user_id' => $userId,
                        'note' => 'Barang Keluar Armada (SJ: ' . $invoiceNumber . ')',
                        'created_at' => Carbon::now(),
                        'updated_at' => Carbon::now()
                    ]);

                    DB::table('sales_order_items')
                        ->where('id', $st['so_item_id'])
                        ->update(['qty_delivered' => DB::raw("qty_delivered + " . $st['qty_load'])]);
                }

                $hasRemaining = DB::table('sales_order_items')
                    ->where('sales_order_id', $so->id)
                    ->whereRaw('qty > qty_delivered')
                    ->exists();

                DB::table('sales_orders')->where('id', $so->id)->update([
                    'status' => $hasRemaining ? 'partially_delivered' : 'dikirim',
                    'updated_at' => Carbon::now()
                ]);

                if (!$isCanvas) {
                    AccountingService::log([
                        'branch_id'   => $so->fulfillment_branch_id,
                        'entry_date'  => $request->delivery_date,
                        'description' => 'Faktur Penjualan ' . $invoiceNumber . ' (' . $so->customer_name . ')',
                        'reference'   => $invoiceNumber,
                        'source_type' => 'sales_invoice',
                        'user_id'     => $userId,
                        'items'       => [
                            ['account_id' => AccountingService::getAccountId('receivable_account'), 'debit' => $grandTotal, 'credit' => 0],
                            ['account_id' => AccountingService::getAccountId('ppn_output_account'), 'debit' => 0, 'credit' => $taxAmount],
                            ['account_id' => AccountingService::getAccountId('sales_revenue_account'), 'debit' => 0, 'credit' => $dpp + ($validDoCount == 0 ? $so->shipping_fee : 0)],
                            ['account_id' => AccountingService::getAccountId('cost_of_goods_sold_account'), 'debit' => $totalCogs, 'credit' => 0],
                            ['account_id' => AccountingService::getAccountId('inventory_account'), 'debit' => 0, 'credit' => $totalCogs],
                        ]
                    ]);
                }
            }

            $printData = [];
            if (count($newSalesIds) > 0) {
                $printData = DB::table('sales')
                    ->leftJoin('sales_orders', 'sales.sales_order_id', '=', 'sales_orders.id')
                    ->leftJoin('users as driver', 'sales.driver_id', '=', 'driver.id')
                    ->leftJoin('users as helper', 'sales.helper_id', '=', 'helper.id')
                    ->whereIn('sales.id', $newSalesIds)
                    ->select('sales.*', 'driver.name as driver_name', 'helper.name as helper_name', 'sales_orders.created_at as so_date')
                    ->get();

                foreach ($printData as $doc) {
                    $doc->items = DB::table('sale_items')->where('sale_id', $doc->id)->get();
                }
            }

            DB::commit();

            return response()->json([
                'success' => true,
                'message' => 'Berhasil diproses! ' . count($processedInvoices) . ' Surat Jalan diterbitkan.',
                'invoices' => $processedInvoices,
                'print_data' => $printData
            ]);

        } catch (\Exception $e) {
            DB::rollBack();
            return response()->json(['success' => false, 'message' => $e->getMessage()]);
        }
    }

    public function generateManifest(Request $request)
    {
        $request->validate(['so_ids' => 'required|array']);

        $manifestItems = DB::table('sales_order_items')
            ->join('products', 'sales_order_items.product_id', '=', 'products.id')
            ->whereIn('sales_order_items.sales_order_id', $request->so_ids)
            ->whereRaw('sales_order_items.qty > sales_order_items.qty_delivered')
            ->select(
                'products.name as product_name',
                'products.variant_name',
                'products.sku',
                'sales_order_items.unit_name',
                DB::raw('SUM(sales_order_items.qty - sales_order_items.qty_delivered) as total_qty')
            )
            ->groupBy('products.name', 'products.variant_name', 'products.sku', 'sales_order_items.unit_name')
            ->orderBy('products.name', 'asc')
            ->get();
            
        foreach ($manifestItems as $item) {
            $item->product_name = $item->variant_name ? $item->product_name . ' - ' . $item->variant_name : $item->product_name;
        }

        return response()->json([
            'success' => true,
            'data' => $manifestItems
        ]);
    }

    public function reportFailedDelivery(Request $request)
    {
        $request->validate([
            'sale_id' => 'required',
            'reason' => 'required|string'
        ]);

        $userId = auth()->id();
        if (!$userId) return response()->json(['success' => false, 'message' => 'Sesi tidak valid.'], 401);

        DB::beginTransaction();
        try {
            $sale = DB::table('sales')->where('id', $request->sale_id)->lockForUpdate()->first();
            
            if (!$sale || !$sale->sales_order_id) {
                throw new \Exception("Data Faktur tidak valid atau tidak memiliki referensi SO.");
            }

            if ($sale->delivery_status === 'void') {
                throw new \Exception("Gagal! Faktur ini sudah dibatalkan (VOID) sebelumnya oleh Supir atau sistem. Tidak dapat diproses ulang.");
            }

            $so = DB::table('sales_orders')->where('id', $sale->sales_order_id)->first();
            $totalCogs = 0; 

            $saleItems = DB::table('sale_items')->where('sale_id', $sale->id)->get();
            foreach ($saleItems as $item) {
                $actualQty = $item->quantity * $item->base_conversion_factor;
                
                $product = DB::table('products')->where('id', $item->product_id)->first();
                if ($product) {
                    $totalCogs += ((float) ($product->buy_price ?? 0) * $actualQty);
                }

                $stock = DB::table('product_stocks')
                            ->where('branch_id', $sale->branch_id)
                            ->where('product_id', $item->product_id)
                            ->first();

                if ($stock) {
                    DB::table('product_stocks')->where('id', $stock->id)->update([
                        'quantity' => DB::raw("quantity + $actualQty"),
                        'reserved_qty' => DB::raw("reserved_qty + $actualQty")
                    ]);
                }

                DB::table('stock_transactions')->insert([
                    'branch_id' => $sale->branch_id,
                    'product_id' => $item->product_id,
                    'type' => 'sale_return',
                    'input_quantity' => $item->quantity,
                    'input_unit' => $item->unit,
                    'actual_quantity' => $actualQty,
                    'user_id' => $userId, 
                    'note' => 'Retur Gagal Kirim: ' . $sale->invoice_number,
                    'created_at' => Carbon::now(),
                    'updated_at' => Carbon::now()
                ]);

                DB::table('sales_order_items')
                    ->where('sales_order_id', $so->id) 
                    ->where('product_id', $item->product_id)
                    ->where('unit_name', $item->unit)
                    ->update(['qty_delivered' => DB::raw("GREATEST(qty_delivered - " . $item->quantity . ", 0)")]);
            }

            DB::table('delivery_logs')->insert([
                'sale_id' => $sale->id,
                'sales_order_id' => $so->id,
                'driver_id' => $sale->driver_id,
                'status' => 'failed_reschedule',
                'reason' => $request->reason,
                'latitude' => $request->latitude ?? null,
                'longitude' => $request->longitude ?? null,
                'created_at' => Carbon::now(),
                'updated_at' => Carbon::now()
            ]);

            DB::table('sales')->where('id', $sale->id)->update([
                'delivery_status' => 'void',
                'payment_status' => 'void', 
                'updated_at' => Carbon::now()
            ]);

            $hasDeliveredItems = DB::table('sales_order_items')
                    ->where('sales_order_id', $so->id)
                    ->whereRaw('qty_delivered > 0')
                    ->exists();

            DB::table('sales_orders')->where('id', $so->id)->update([
                'status' => $hasDeliveredItems ? 'partially_delivered' : 'reschedule',
                'updated_at' => Carbon::now()
            ]);

            AccountingService::log([
                'branch_id'   => $sale->branch_id,
                'entry_date'  => Carbon::today()->toDateString(),
                'description' => 'Batal Kirim/VOID SJ: ' . $sale->invoice_number,
                'reference'   => $sale->invoice_number . '-VOID',
                'source_type' => 'sale_return_void',
                'user_id'     => $userId,
                'items'       => [
                    ['account_id' => AccountingService::getAccountId('receivable_account'), 'debit' => 0, 'credit' => $sale->grand_total],
                    ['account_id' => AccountingService::getAccountId('ppn_output_account'), 'debit' => $sale->tax_amount ?? 0, 'credit' => 0],
                    ['account_id' => AccountingService::getAccountId('sales_revenue_account'), 'debit' => $sale->grand_total - ($sale->tax_amount ?? 0), 'credit' => 0],
                    ['account_id' => AccountingService::getAccountId('cost_of_goods_sold_account'), 'debit' => 0, 'credit' => $totalCogs],
                    ['account_id' => AccountingService::getAccountId('inventory_account'), 'debit' => $totalCogs, 'credit' => 0],
                ]
            ]);

            DB::commit();

            return response()->json([
                'success' => true,
                'message' => 'Laporan Gagal Kirim tersimpan. Faktur dibatalkan (VOID) dan pesanan dikembalikan ke Antrean.'
            ]);

        } catch (\Exception $e) {
            DB::rollBack();
            return response()->json(['success' => false, 'message' => $e->getMessage()]);
        }
    }

    public function calculateCart(Request $request)
    {
        $request->validate([
            'customer_id' => 'required',
            'items' => 'required|array',
            'tax_method' => 'nullable|in:non,include,exclude'
        ]);

        $result = $this->_calculateCartInternal($request->customer_id, $request->items, $request->tax_method);
        return response()->json($result);
    }

    public function storeManualOrder(Request $request)
    {
        $request->validate([
            'customer_id' => 'required',
            'salesman_id' => 'required',
            'payment_method' => 'required|in:cash,kredit',
            'due_days' => 'nullable|integer',
            'delivery_date' => 'nullable|date',
            'calculated_data' => 'required|array',
            'fulfillment_branch_id' => 'nullable|exists:branches,id', 
        ]);

        $userId = auth()->id();
        $user = auth()->user(); 
        if (!$userId) return response()->json(['success' => false, 'message' => 'Sesi tidak valid.'], 401);

        $salesman = \App\Models\Salesman::where('user_id', $userId)->first();
        $isCanvas = $salesman && $salesman->salesman_type === 'CANVAS';

        DB::beginTransaction();
        try {
            $secureCalc = $this->_calculateCartInternal(
                $request->customer_id, 
                $request->calculated_data['items'] ?? [], 
                $request->calculated_data['tax_method'] ?? 'include'
            );

            if (!$secureCalc['success']) {
                throw new \Exception("Gagal menghitung validasi keranjang: " . $secureCalc['message']);
            }
            $calc = $secureCalc['data']; 

            $originBranchId = $user->branch_id ?? 1; 
            
            if ($user->role === 'owner') {
                $fulfillmentBranchId = $request->fulfillment_branch_id ?? $originBranchId;
            } else {
                $fulfillmentBranchId = $originBranchId;
            }
            
            $customer = DB::table('customers')->where('id', $request->customer_id)->first();
            if (!$customer) {
                throw new \Exception("Pelanggan tidak ditemukan.");
            }
            if (isset($customer->is_active) && $customer->is_active == 0) {
                throw new \Exception("Gagal! Pelanggan ini berstatus Non-Aktif. Tidak dapat membuat pesanan untuk pelanggan yang dinonaktifkan.");
            }

            $finalDueDays = 0;
            if ($request->payment_method === 'kredit') {
                $finalDueDays = $request->filled('due_days') ? (int)$request->due_days : (int)($customer->due_days ?? 0);
            }

            $orderStatus = $isCanvas ? 'dikirim' : 'pending'; 
            $catatanKredit = '';

            if ($request->payment_method === 'kredit' && !$isCanvas) { 
                $limit = (float) $customer->credit_limit;

                if ($limit <= 0) {
                    throw new \Exception("Gagal! Pelanggan tidak memiliki plafon kredit (Limit: Rp 0). Transaksi kredit tidak diizinkan.");
                }

                $creditCheck = \App\Models\SalesOrder::checkCreditHold($customer->id, $calc['grand_total']);
                $grade = $this->_calculateCustomerGrade($customer->id);
                $isGradeHold = in_array($grade, ['B', 'C', 'D']); 

                if ($creditCheck['is_hold'] || $isGradeHold) {
                    $orderStatus = \App\Models\SalesOrder::STATUS_CREDIT_HOLD ?? 'credit_hold';
                    
                    $reasons = [];
                    if ($creditCheck['is_hold']) $reasons[] = $creditCheck['message'];
                    if ($isGradeHold) $reasons[] = "Pelanggan Grade $grade (Ada histori telat bayar > 30 hari dalam 3 bln terakhir)";
                    
                    $catatanKredit = "\n[" . implode(" | ", $reasons) . "]";
                }
            }

            foreach ($calc['items'] as &$item) {
                $product = DB::table('products')->where('id', $item['product_id'])->first();
                $multiplier = 1;
                $uName = strtoupper(trim($item['unit']));
                
                if ($uName === strtoupper(trim($product->buy_unit ?? ''))) $multiplier = $product->conversion_factor ?? 1;
                elseif ($uName === strtoupper(trim($product->unit_3_name ?? ''))) $multiplier = $product->conversion_3_qty ?? 1;
                elseif ($uName === strtoupper(trim($product->unit_4_name ?? ''))) $multiplier = $product->conversion_4_qty ?? 1;

                $baseQty = $item['qty'] * $multiplier;
                $item['base_qty'] = $baseQty; 

                if ($isCanvas) {
                    $cStock = \App\Models\CanvasStock::where('salesman_id', $salesman->id)
                                ->where('product_id', $item['product_id'])->first();
                    if (!$cStock || $baseQty > $cStock->quantity) {
                         $avail = $cStock ? $cStock->quantity : 0;
                         throw new \Exception("Gagal! Stok {$item['product_name']} di mobil tidak cukup. Tersedia: {$avail}, Diminta: {$baseQty}.");
                    }
                } else {
                    $stock = DB::table('product_stocks')
                        ->where('branch_id', $fulfillmentBranchId)
                        ->where('product_id', $item['product_id'])
                        ->first();

                    $realAvail = $stock ? ($stock->quantity - $stock->reserved_qty) : 0;
                    if (!$stock || ($baseQty > $realAvail)) {
                        throw new \Exception("Gagal! Stok {$item['product_name']} tidak cukup di gudang pilihan. Tersedia (Satuan Terkecil): {$realAvail}, Diminta: {$baseQty}.");
                    }
                }
            }
            unset($item); 

            foreach ($calc['bonus_items'] as &$bonus) {
                $product = DB::table('products')->where('id', $bonus['product_id'])->first();
                $multiplier = 1;
                $uName = strtoupper(trim($bonus['unit']));
                
                if ($uName === strtoupper(trim($product->buy_unit ?? ''))) $multiplier = $product->conversion_factor ?? 1;
                elseif ($uName === strtoupper(trim($product->unit_3_name ?? ''))) $multiplier = $product->conversion_3_qty ?? 1;
                elseif ($uName === strtoupper(trim($product->unit_4_name ?? ''))) $multiplier = $product->conversion_4_qty ?? 1;

                $baseQty = $bonus['qty'] * $multiplier;
                $bonus['base_qty'] = $baseQty;

                if (!$isCanvas) { 
                    $bStock = DB::table('product_stocks')
                        ->where('branch_id', $fulfillmentBranchId)
                        ->where('product_id', $bonus['product_id'])
                        ->first();

                    $bRealAvail = $bStock ? ($bStock->quantity - $bStock->reserved_qty) : 0;
                    if (!$bStock || ($baseQty > $bRealAvail)) {
                        throw new \Exception("Gagal! Stok Bonus {$bonus['product_name']} tidak cukup. Tersedia (Satuan Terkecil): {$bRealAvail}, Diminta: {$baseQty}.");
                    }
                }
            }
            unset($bonus); 

            $soNumber = 'SO-' . date('Ymd') . '-' . rand(1000, 9999);
            
            $terminNote = '';
            if ($request->payment_method === 'kredit' && $finalDueDays > 0) {
                $terminNote = " [Termin: " . $finalDueDays . " Hari]";
            }

            $finalNote = ($request->note ?? 'Pesanan B2B Manual') . $terminNote . $catatanKredit;

            $soId = DB::table('sales_orders')->insertGetId([
                'so_number' => $soNumber,
                'branch_id' => $originBranchId, 
                'fulfillment_branch_id' => $fulfillmentBranchId,
                'customer_id' => $customer->id,
                'user_id' => $userId,
                'customer_name' => $customer->name,
                'customer_phone' => $customer->phone,
                'shipping_address' => $customer->address,
                'subtotal' => $calc['subtotal'],
                'discount_amount' => $calc['discount_amount'],
                'grand_total' => $calc['grand_total'],
                'payment_method' => $request->payment_method,
                'due_days' => $finalDueDays, 
                'status' => $orderStatus, 
                'note' => $finalNote,
                'delivery_date' => $request->delivery_date ?? Carbon::today(),
                'created_at' => Carbon::now(),
                'updated_at' => Carbon::now(),
            ]);

            foreach ($calc['items'] as $orderItem) {
                DB::table('sales_order_items')->insert([
                    'sales_order_id' => $soId,
                    'product_id' => $orderItem['product_id'],
                    'unit_name' => $orderItem['unit'],
                    'qty' => $orderItem['qty'], 
                    'qty_delivered' => 0, 
                    'price' => $orderItem['price'],
                    'discount_nominal' => $orderItem['discount_amount'],
                    'total_price' => $orderItem['total_price'],
                    'is_free_item' => 0,
                    'applied_program_id' => $orderItem['applied_program_id'],
                    'created_at' => Carbon::now(),
                    'updated_at' => Carbon::now()
                ]);

                if ($isCanvas) {
                    \App\Models\CanvasStock::where('salesman_id', $salesman->id)
                        ->where('product_id', $orderItem['product_id'])
                        ->decrement('quantity', $orderItem['base_qty']);
                } else {
                    $stock = DB::table('product_stocks')->where('branch_id', $fulfillmentBranchId)->where('product_id', $orderItem['product_id'])->first();
                    if ($stock) DB::table('product_stocks')->where('id', $stock->id)->increment('reserved_qty', $orderItem['base_qty']);
                }
            }

            foreach ($calc['bonus_items'] as $bonusItem) {
                DB::table('sales_order_items')->insert([
                    'sales_order_id' => $soId,
                    'product_id' => $bonusItem['product_id'],
                    'unit_name' => $bonusItem['unit'],
                    'qty' => $bonusItem['qty'],
                    'qty_delivered' => 0, 
                    'price' => 0,
                    'discount_nominal' => 0,
                    'total_price' => 0,
                    'is_free_item' => 1,
                    'created_at' => Carbon::now(),
                    'updated_at' => Carbon::now()
                ]);

                if (!$isCanvas) {
                    $stock = DB::table('product_stocks')->where('branch_id', $fulfillmentBranchId)->where('product_id', $bonusItem['product_id'])->first();
                    if ($stock) DB::table('product_stocks')->where('id', $stock->id)->increment('reserved_qty', $bonusItem['base_qty']);
                }
            }

            DB::commit();
            
            $msg = $orderStatus === 'credit_hold'
                ? "PERINGATAN: Pesanan {$soNumber} tersimpan, namun DITAHAN (Credit Hold) karena sistem mendeteksi Grade Toko buruk atau limit habis. Menunggu Approval."
                : "Pesanan berhasil disimpan dengan nomor: {$soNumber}";

            return response()->json([
                'success' => true,
                'message' => $msg,
                'so_number' => $soNumber,
                'status' => $orderStatus
            ]);

        } catch (\Exception $e) {
            DB::rollBack();
            return response()->json(['success' => false, 'message' => $e->getMessage()]);
        }
    }

    public function updateManualOrder(Request $request, $id)
    {
        $request->validate([
            'customer_id' => 'required',
            'payment_method' => 'required|in:cash,kredit',
            'due_days' => 'nullable|integer',
            'delivery_date' => 'nullable|date',
            'calculated_data' => 'required|array',
            'fulfillment_branch_id' => 'nullable|exists:branches,id',
        ]);

        $userId = auth()->id();
        $user = auth()->user();
        if (!$userId) return response()->json(['success' => false, 'message' => 'Sesi tidak valid.'], 401);

        $salesman = \App\Models\Salesman::where('user_id', $userId)->first();
        $isCanvas = $salesman && $salesman->salesman_type === 'CANVAS';

        DB::beginTransaction();
        try {
            $so = DB::table('sales_orders')->where('id', $id)->lockForUpdate()->first();
            
            if (!$so) {
                throw new \Exception("Pesanan tidak ditemukan.");
            }

            if (!in_array($so->status, ['pending', 'credit_hold', 'reschedule'])) {
                throw new \Exception("Gagal! Pesanan dengan status '{$so->status}' sudah diproses gudang/dikirim dan tidak dapat diedit lagi.");
            }

            $secureCalc = $this->_calculateCartInternal(
                $request->customer_id, 
                $request->calculated_data['items'] ?? [], 
                $request->calculated_data['tax_method'] ?? 'include'
            );

            if (!$secureCalc['success']) {
                throw new \Exception("Gagal menghitung keranjang: " . $secureCalc['message']);
            }
            $calc = $secureCalc['data']; 

            $originBranchId = $user->branch_id ?? 1; 
            $fulfillmentBranchId = ($user->role === 'owner') ? ($request->fulfillment_branch_id ?? $so->fulfillment_branch_id) : $so->fulfillment_branch_id;

            $customer = DB::table('customers')->where('id', $request->customer_id)->first();
            
            $finalDueDays = 0;
            if ($request->payment_method === 'kredit') {
                $finalDueDays = $request->filled('due_days') ? (int)$request->due_days : (int)($customer->due_days ?? 0);
            }

            $orderStatus = $isCanvas ? 'dikirim' : 'pending';
            $catatanKredit = '';

            if ($request->payment_method === 'kredit' && !$isCanvas) {
                $limit = (float) $customer->credit_limit;
                if ($limit <= 0) {
                    throw new \Exception("Gagal! Pelanggan tidak memiliki plafon kredit (Limit: Rp 0).");
                }

                $creditCheck = \App\Models\SalesOrder::checkCreditHold($customer->id, $calc['grand_total'], $so->id); 
                $grade = $this->_calculateCustomerGrade($customer->id);
                $isGradeHold = in_array($grade, ['B', 'C', 'D']);

                if ($creditCheck['is_hold'] || $isGradeHold) {
                    $orderStatus = \App\Models\SalesOrder::STATUS_CREDIT_HOLD ?? 'credit_hold';
                    
                    $reasons = [];
                    if ($creditCheck['is_hold']) $reasons[] = $creditCheck['message'];
                    if ($isGradeHold) $reasons[] = "Pelanggan Grade $grade (Ada histori telat bayar > 30 hari dalam 3 bln terakhir)";
                    
                    $catatanKredit = "\n[" . implode(" | ", $reasons) . "]";
                }
            }

            $oldItems = DB::table('sales_order_items')->where('sales_order_id', $so->id)->get();
            foreach ($oldItems as $oItem) {
                $product = DB::table('products')->where('id', $oItem->product_id)->first();
                $multiplier = 1;
                $uName = strtoupper(trim($oItem->unit_name));
                if ($uName === strtoupper(trim($product->buy_unit ?? ''))) $multiplier = $product->conversion_factor ?? 1;
                elseif ($uName === strtoupper(trim($product->unit_3_name ?? ''))) $multiplier = $product->conversion_3_qty ?? 1;
                elseif ($uName === strtoupper(trim($product->unit_4_name ?? ''))) $multiplier = $product->conversion_4_qty ?? 1;

                $baseSisaQty = $oItem->qty * $multiplier;

                if ($isCanvas) {
                    \App\Models\CanvasStock::where('salesman_id', $salesman->id)
                        ->where('product_id', $oItem->product_id)
                        ->increment('quantity', $baseSisaQty);
                } else {
                    DB::table('product_stocks')
                        ->where('branch_id', $so->fulfillment_branch_id)
                        ->where('product_id', $oItem->product_id)
                        ->update(['reserved_qty' => DB::raw("GREATEST(reserved_qty - {$baseSisaQty}, 0)")]);
                }
            }

            DB::table('sales_order_items')->where('sales_order_id', $so->id)->delete();

            foreach ($calc['items'] as &$item) {
                $product = DB::table('products')->where('id', $item['product_id'])->first();
                $multiplier = 1;
                $uName = strtoupper(trim($item['unit']));
                if ($uName === strtoupper(trim($product->buy_unit ?? ''))) $multiplier = $product->conversion_factor ?? 1;
                elseif ($uName === strtoupper(trim($product->unit_3_name ?? ''))) $multiplier = $product->conversion_3_qty ?? 1;
                elseif ($uName === strtoupper(trim($product->unit_4_name ?? ''))) $multiplier = $product->conversion_4_qty ?? 1;

                $baseQty = $item['qty'] * $multiplier;
                $item['base_qty'] = $baseQty; 

                if ($isCanvas) {
                    $cStock = \App\Models\CanvasStock::where('salesman_id', $salesman->id)->where('product_id', $item['product_id'])->first();
                    if (!$cStock || $baseQty > $cStock->quantity) {
                         throw new \Exception("Stok Mobil {$item['product_name']} tidak cukup.");
                    }
                } else {
                    $stock = DB::table('product_stocks')->where('branch_id', $fulfillmentBranchId)->where('product_id', $item['product_id'])->first();
                    $realAvail = $stock ? ($stock->quantity - $stock->reserved_qty) : 0;
                    if (!$stock || ($baseQty > $realAvail)) {
                        throw new \Exception("Stok Gudang {$item['product_name']} tidak cukup. Tersedia: {$realAvail}, Diminta: {$baseQty}.");
                    }
                }
            }
            unset($item);

            foreach ($calc['bonus_items'] as &$bonus) {
                $product = DB::table('products')->where('id', $bonus['product_id'])->first();
                $multiplier = 1;
                $uName = strtoupper(trim($bonus['unit']));
                if ($uName === strtoupper(trim($product->buy_unit ?? ''))) $multiplier = $product->conversion_factor ?? 1;
                elseif ($uName === strtoupper(trim($product->unit_3_name ?? ''))) $multiplier = $product->conversion_3_qty ?? 1;
                elseif ($uName === strtoupper(trim($product->unit_4_name ?? ''))) $multiplier = $product->conversion_4_qty ?? 1;

                $baseQty = $bonus['qty'] * $multiplier;
                $bonus['base_qty'] = $baseQty;

                if (!$isCanvas) {
                    $bStock = DB::table('product_stocks')->where('branch_id', $fulfillmentBranchId)->where('product_id', $bonus['product_id'])->first();
                    $bRealAvail = $bStock ? ($bStock->quantity - $bStock->reserved_qty) : 0;
                    if (!$bStock || ($baseQty > $bRealAvail)) throw new \Exception("Stok Bonus {$bonus['product_name']} tidak cukup.");
                }
            }
            unset($bonus);

            $terminNote = ($request->payment_method === 'kredit' && $finalDueDays > 0) ? " [Termin: {$finalDueDays} Hari]" : '';
            $finalNote = ($request->note ?? 'Pesanan B2B (Diedit)') . $terminNote . $catatanKredit . "\n[Diedit oleh {$user->name} tgl " . now()->format('d/m/Y H:i') . "]";

            DB::table('sales_orders')->where('id', $so->id)->update([
                'fulfillment_branch_id' => $fulfillmentBranchId,
                'customer_id' => $customer->id,
                'customer_name' => $customer->name,
                'customer_phone' => $customer->phone,
                'shipping_address' => $customer->address,
                'subtotal' => $calc['subtotal'],
                'discount_amount' => $calc['discount_amount'],
                'grand_total' => $calc['grand_total'],
                'payment_method' => $request->payment_method,
                'due_days' => $finalDueDays, 
                'status' => $orderStatus, 
                'note' => $finalNote,
                'delivery_date' => $request->delivery_date ?? Carbon::today(),
                'updated_at' => Carbon::now(),
            ]);

            foreach ($calc['items'] as $orderItem) {
                DB::table('sales_order_items')->insert([
                    'sales_order_id' => $so->id,
                    'product_id' => $orderItem['product_id'],
                    'unit_name' => $orderItem['unit'],
                    'qty' => $orderItem['qty'], 
                    'qty_delivered' => 0, 
                    'price' => $orderItem['price'],
                    'discount_nominal' => $orderItem['discount_amount'],
                    'total_price' => $orderItem['total_price'],
                    'is_free_item' => 0,
                    'applied_program_id' => $orderItem['applied_program_id'],
                    'created_at' => Carbon::now(),
                    'updated_at' => Carbon::now()
                ]);

                if ($isCanvas) {
                    \App\Models\CanvasStock::where('salesman_id', $salesman->id)->where('product_id', $orderItem['product_id'])->decrement('quantity', $orderItem['base_qty']);
                } else {
                    DB::table('product_stocks')->where('branch_id', $fulfillmentBranchId)->where('product_id', $orderItem['product_id'])->increment('reserved_qty', $orderItem['base_qty']);
                }
            }

            foreach ($calc['bonus_items'] as $bonusItem) {
                DB::table('sales_order_items')->insert([
                    'sales_order_id' => $so->id,
                    'product_id' => $bonusItem['product_id'],
                    'unit_name' => $bonusItem['unit'],
                    'qty' => $bonusItem['qty'],
                    'qty_delivered' => 0, 
                    'price' => 0,
                    'discount_nominal' => 0,
                    'total_price' => 0,
                    'is_free_item' => 1,
                    'created_at' => Carbon::now(),
                    'updated_at' => Carbon::now()
                ]);

                if (!$isCanvas) {
                    DB::table('product_stocks')->where('branch_id', $fulfillmentBranchId)->where('product_id', $bonusItem['product_id'])->increment('reserved_qty', $bonusItem['base_qty']);
                }
            }

            DB::commit();
            
            $msg = $orderStatus === 'credit_hold'
                ? "Pesanan berhasil diubah, namun DITAHAN (Credit Hold) karena melebihi batas hutang atau grade menurun."
                : "Pesanan {$so->so_number} berhasil diperbarui.";

            return response()->json([
                'success' => true,
                'message' => $msg,
                'so_number' => $so->so_number,
                'status' => $orderStatus
            ]);

        } catch (\Exception $e) {
            DB::rollBack();
            return response()->json(['success' => false, 'message' => $e->getMessage()]);
        }
    }

    public function getMasterData(Request $request)
    {
        try {
            $customers = DB::table('customers')
                ->where('is_active', 1)
                ->select('id', 'name', 'customer_code', 'price_level', 'credit_limit', 'salesman_id', 'due_days', 'is_pkp', 'default_tax_method', 'address')
                ->orderBy('name', 'asc')
                ->get();

            $parentIds = DB::table('products')
                ->whereNotNull('variant_group_id')
                ->pluck('variant_group_id')
                ->map(function($val) {
                    return (int) str_replace('VAR-', '', $val);
                })
                ->filter()
                ->unique()
                ->toArray();

            $products = DB::table('products')
                ->where('is_active', 1)
                ->whereIn('product_type', ['trading', 'finished_good'])
                ->whereNotIn('id', $parentIds)
                ->orderBy('name', 'asc')
                ->get();

            $productPrices = DB::table('product_prices')->get();
            $pricesGrouped = collect($productPrices)->groupBy('product_id');

            foreach ($products as $product) {
                $product->name = $product->variant_name ? $product->name . ' - ' . $product->variant_name : $product->name;
                $product->prices = $pricesGrouped->get($product->id, []);
            }

            $salesmen = DB::table('salesmen')
                ->join('users', 'salesmen.user_id', '=', 'users.id')
                ->select('salesmen.id', 'users.name', 'salesmen.salesman_type')
                ->get();

            $drivers = DB::table('users')
                ->where('role', 'driver')
                ->select('id', 'name')
                ->orderBy('name', 'asc')
                ->get();

            return response()->json([
                'success' => true,
                'data' => [
                    'customers' => $customers,
                    'products' => $products,
                    'salesmen' => $salesmen,
                    'drivers' => $drivers 
                ]
            ]);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()]);
        }
    }

    public function checkCustomerCredit($customerId)
    {
        try {
            $customer = DB::table('customers')->where('id', $customerId)->first();
            if (!$customer) throw new \Exception("Pelanggan tidak ditemukan");

            $pendingSales = DB::table('sales')
                ->where('customer_id', $customerId)
                ->where('payment_status', 'pending')
                ->get();

            $pendingOrders = DB::table('sales_orders')
                ->where('customer_id', $customerId)
                ->whereIn('status', ['pending', 'reschedule', 'credit_hold', 'partially_delivered'])
                ->get();

            $totalDebt = 0;
            $overdueCount = 0;
            $today = Carbon::today();

            foreach ($pendingSales as $sale) {
                $actualPaid = max((float)($sale->paid_amount ?? 0), (float)($sale->dp_amount ?? 0));
                $sisaHutang = (float)$sale->grand_total - $actualPaid;
                
                if ($sisaHutang > 0) {
                    $totalDebt += $sisaHutang;
                    if ($sale->due_date && Carbon::parse($sale->due_date)->lt($today)) {
                        $overdueCount++;
                    }
                }
            }

            foreach ($pendingOrders as $order) {
                $totalDebt += (float) $order->grand_total;
            }

            // PERBAIKAN: Hitung dan lampirkan Grade Pelanggan
            $grade = $this->_calculateCustomerGrade($customerId);

            return response()->json([
                'success' => true,
                'data' => [
                    'total_debt' => $totalDebt,
                    'overdue_count' => $overdueCount,
                    'credit_limit' => (float)$customer->credit_limit,
                    'grade' => $grade // <--- INJEKSI DATA GRADE KE FRONTEND
                ]
            ]);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()]);
        }
    }

    public function getCreditHoldOrders(Request $request)
    {
        $user = auth()->user();
        if (!$user) return response()->json(['success' => false, 'message' => 'Akses ditolak.'], 401);

        $requestedBranch = $request->query('branch_id');
        $branchId = ($user->role === 'owner') ? ($requestedBranch ?? $user->branch_id) : $user->branch_id;

        $orders = DB::table('sales_orders')
            ->where('branch_id', $branchId)
            ->where('status', 'credit_hold')
            ->orderBy('created_at', 'desc')
            ->get();

        foreach ($orders as $order) {
            $items = DB::table('sales_order_items')
                ->join('products', 'sales_order_items.product_id', '=', 'products.id')
                ->where('sales_order_id', $order->id)
                ->select('sales_order_items.*', 'products.name', 'products.variant_name', 'products.sku')
                ->get();
            
            foreach ($items as $item) {
                $item->product_name = $item->variant_name ? $item->name . ' - ' . $item->variant_name : $item->name;
            }
            
            $order->items = $items;
        }

        return response()->json([
            'success' => true,
            'data' => $orders
        ]);
    }

    /**
     * ========================================================================
     * PROSES APPROVAL DENGAN OTORISASI BERTINGKAT (TIERED AUTHORIZATION)
     * ========================================================================
     */
    public function processCreditApproval(Request $request)
    {
        $request->validate([
            'order_id' => 'required',
            'action' => 'required|in:approve,reject',
            'reason' => 'nullable|string',
            'password' => 'required' 
        ]);

        $userId = auth()->id();
        if (!$userId) return response()->json(['success' => false, 'message' => 'Sesi tidak valid.'], 401);

        // PERBAIKAN: Masukkan Supervisor ke dalam daftar wewenang
        $authorizedUsers = DB::table('users')
            ->whereIn('role', ['owner', 'manager', 'supervisor_admin', 'supervisor_sales'])
            ->get();

        $authorizer = null;
        foreach ($authorizedUsers as $user) {
            if (Hash::check($request->password, $user->password)) {
                $authorizer = $user;
                break;
            }
        }

        if (!$authorizer) {
            return response()->json(['success' => false, 'message' => 'Otorisasi Gagal! Password salah atau Anda tidak memiliki hak Approval.']);
        }

        DB::beginTransaction();
        try {
            $order = DB::table('sales_orders')->where('id', $request->order_id)->lockForUpdate()->first();
            if (!$order || $order->status !== 'credit_hold') {
                throw new \Exception("Pesanan tidak ditemukan atau status tidak valid.");
            }

            // ========================================================================
            // TIERED AUTHORIZATION: PENGECEKAN LIMIT & GRADE BAGI SUPERVISOR
            // ========================================================================
            if ($request->action === 'approve') {
                $role = strtolower($authorizer->role);
                $isSupervisor = in_array($role, ['supervisor_admin', 'supervisor_sales']);

                if ($isSupervisor) {
                    // 1. Cek Grade Pelanggan
                    $grade = $this->_calculateCustomerGrade($order->customer_id);
                    if (in_array($grade, ['B', 'C', 'D'])) {
                        throw new \Exception("Akses Ditolak: Pelanggan berada di Grade {$grade}. Approval untuk histori pembayaran buruk hanya bisa dilakukan oleh Manager atau Owner.");
                    }

                    // 2. Cek Total Piutang Berjalan + Pesanan Ini
                    $pendingSales = DB::table('sales')->where('customer_id', $order->customer_id)->where('payment_status', 'pending')->get();
                    $pendingOrders = DB::table('sales_orders')
                        ->where('customer_id', $order->customer_id)
                        ->whereIn('status', ['pending', 'reschedule', 'credit_hold', 'partially_delivered'])
                        ->where('id', '!=', $order->id)
                        ->get();

                    $totalDebt = 0;
                    foreach ($pendingSales as $sale) {
                        $actualPaid = max((float)($sale->paid_amount ?? 0), (float)($sale->dp_amount ?? 0));
                        $totalDebt += ((float)$sale->grand_total - $actualPaid);
                    }
                    foreach ($pendingOrders as $po) {
                        $totalDebt += (float) $po->grand_total;
                    }
                    $totalDebt += (float) $order->grand_total;

                    if ($totalDebt > 10000000) {
                        throw new \Exception("Akses Ditolak: Total eksposur piutang pelanggan (Rp " . number_format($totalDebt, 0, ',', '.') . ") melebihi batas wewenang Supervisor (Maks Rp 10 Juta). Approval harus dilakukan oleh Manager atau Owner.");
                    }
                }
            }
            // ========================================================================

            if ($request->action === 'approve') {
                $noteAppend = "\n[APPROVED by {$authorizer->name} ({$authorizer->role}) pada " . Carbon::now()->toDateTimeString() . "]";
                if ($request->reason) $noteAppend .= " Catatan: " . $request->reason;

                DB::table('sales_orders')->where('id', $order->id)->update([
                    'status' => 'pending',
                    'note' => $order->note . $noteAppend,
                    'updated_at' => Carbon::now()
                ]);
                $msg = "Pesanan berhasil disetujui oleh {$authorizer->name} dan masuk ke antrean pengiriman.";
            } else {
                $noteAppend = "\n[REJECTED by {$authorizer->name} ({$authorizer->role}) pada " . Carbon::now()->toDateTimeString() . "]";
                if ($request->reason) $noteAppend .= " Alasan Penolakan: " . $request->reason;

                DB::table('sales_orders')->where('id', $order->id)->update([
                    'status' => 'cancelled',
                    'note' => $order->note . $noteAppend,
                    'updated_at' => Carbon::now()
                ]);

                $items = DB::table('sales_order_items')->where('sales_order_id', $order->id)->get();
                foreach ($items as $item) {
                    $product = DB::table('products')->where('id', $item->product_id)->first();
                    $multiplier = 1;
                    $uName = strtoupper(trim($item->unit_name));
                    
                    if ($uName === strtoupper(trim($product->buy_unit ?? ''))) $multiplier = $product->conversion_factor ?? 1;
                    elseif ($uName === strtoupper(trim($product->unit_3_name ?? ''))) $multiplier = $product->conversion_3_qty ?? 1;
                    elseif ($uName === strtoupper(trim($product->unit_4_name ?? ''))) $multiplier = $product->conversion_4_qty ?? 1;

                    $baseQtyToRelease = $item->qty * $multiplier;

                    $stock = DB::table('product_stocks')
                        ->where('branch_id', $order->branch_id)
                        ->where('product_id', $item->product_id)
                        ->first();

                    if ($stock) {
                        DB::table('product_stocks')->where('id', $stock->id)->update([
                            'reserved_qty' => DB::raw("GREATEST(reserved_qty - {$baseQtyToRelease}, 0)")
                        ]);
                    }
                }
                $msg = "Pesanan ditolak oleh {$authorizer->name} dan dibatalkan. Booking stok telah dilepaskan kembali.";
            }

            DB::commit();
            return response()->json(['success' => true, 'message' => $msg]);

        } catch (\Exception $e) {
            DB::rollBack();
            return response()->json(['success' => false, 'message' => $e->getMessage()]);
        }
    }

    public function getDeliveryHistory(Request $request)
    {
        try {
            $user = $request->user();
            if (!$user) return response()->json(['success' => false, 'message' => 'Unauthenticated.'], 401);

            $requestedBranch = $request->query('branch_id');
            $branchId = ($user->role === 'owner') ? ($requestedBranch ?? 'all') : $user->branch_id;

            $query = DB::table('sales')
                ->leftJoin('customers', 'sales.customer_id', '=', 'customers.id') // FIX 1: Ganti jadi leftJoin
                ->join('sales_orders', 'sales.sales_order_id', '=', 'sales_orders.id') 
                ->leftJoin('districts', 'customers.district_id', '=', 'districts.id')
                ->leftJoin('cities', 'districts.city_id', '=', 'cities.id')
                ->leftJoin('users', 'sales.driver_id', '=', 'users.id')
                ->leftJoin('users as helper', 'sales.helper_id', '=', 'helper.id') 
                ->select(
                    'sales.*', 
                    'sales_orders.created_at as so_date',
                    // FIX 2: Hapus customers.name & customers.address karena sales.* sudah memuat sales.customer_name & sales.shipping_address bawaan dari POS Online
                    'districts.name as district_name',
                    'cities.name as city_name',
                    'users.name as driver_name',
                    'helper.name as helper_name' 
                )
                ->whereNotNull('sales.delivery_status')
                ->where('sales.sales_order_id', '!=', 0); 

            if (!empty($branchId) && $branchId !== 'all' && $branchId !== 'null' && $branchId !== 'undefined') {
                $query->where('sales.branch_id', $branchId);
            }

            $history = $query->orderBy('sales.updated_at', 'desc')->get();

            $formattedData = $history->map(function($item) {
                $item->customer_region = strtoupper(($item->district_name ?? '') . ', ' . ($item->city_name ?? ''));
                $item->items = DB::table('sale_items')->where('sale_id', $item->id)->get();
                return $item;
            });

            return response()->json(['success' => true, 'data' => $formattedData]);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    public function authorizeReprint(Request $request)
    {
        $request->validate([
            'password' => 'required'
        ]);

        try {
            $authorizedUsers = DB::table('users')
                ->whereIn('role', ['owner', 'supervisor', 'kepala_gudang'])
                ->get();

            $isMatched = false;
            foreach ($authorizedUsers as $user) {
                if (Hash::check($request->password, $user->password)) {
                    $isMatched = true;
                    break;
                }
            }

            if (!$isMatched) {
                return response()->json(['success' => false, 'message' => 'Otorisasi Gagal! Password tidak valid.']);
            }

            return response()->json(['success' => true]);

        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => 'Terjadi kesalahan sistem.']);
        }
    }

    public function updateFulfillmentBranch(Request $request)
    {
        $request->validate([
            'sales_order_id' => 'required|exists:sales_orders,id',
            'new_branch_id' => 'required|exists:branches,id'
        ]);

        $user = auth()->user();
        
        if ($user->role !== 'owner') {
            return response()->json([
                'success' => false, 
                'message' => 'Akses ditolak. Hanya Owner yang dapat mengalihkan gudang pengirim.'
            ], 403);
        }

        DB::beginTransaction();
        try {
            $so = DB::table('sales_orders')->where('id', $request->sales_order_id)->first();
            
            if (!in_array($so->status, ['pending', 'credit_hold'])) {
                throw new \Exception("Pesanan status '{$so->status}' sudah tidak dapat dialihkan gudangnya.");
            }

            if ($so->fulfillment_branch_id == $request->new_branch_id) {
                throw new \Exception("Gudang tujuan sama dengan gudang saat ini.");
            }

            $items = DB::table('sales_order_items')->where('sales_order_id', $so->id)->get();

            foreach ($items as $item) {
                $product = DB::table('products')->where('id', $item->product_id)->first();
                $multiplier = 1;
                $uName = strtoupper(trim($item->unit_name));
                
                if ($uName === strtoupper(trim($product->buy_unit ?? ''))) $multiplier = $product->conversion_factor ?? 1;
                elseif ($uName === strtoupper(trim($product->unit_3_name ?? ''))) $multiplier = $product->conversion_3_qty ?? 1;
                elseif ($uName === strtoupper(trim($product->unit_4_name ?? ''))) $multiplier = $product->conversion_4_qty ?? 1;

                $baseSisaQty = $item->qty * $multiplier;

                DB::table('product_stocks')
                    ->where('branch_id', $so->fulfillment_branch_id)
                    ->where('product_id', $item->product_id)
                    ->update([
                        'reserved_qty' => DB::raw("GREATEST(reserved_qty - {$baseSisaQty}, 0)")
                    ]);
            }

            foreach ($items as $item) {
                $product = DB::table('products')->where('id', $item->product_id)->first();
                $multiplier = 1;
                $uName = strtoupper(trim($item->unit_name));
                
                if ($uName === strtoupper(trim($product->buy_unit ?? ''))) $multiplier = $product->conversion_factor ?? 1;
                elseif ($uName === strtoupper(trim($product->unit_3_name ?? ''))) $multiplier = $product->conversion_3_qty ?? 1;
                elseif ($uName === strtoupper(trim($product->unit_4_name ?? ''))) $multiplier = $product->conversion_4_qty ?? 1;

                $baseSisaQty = $item->qty * $multiplier;

                $newStock = DB::table('product_stocks')
                    ->where('branch_id', $request->new_branch_id)
                    ->where('product_id', $item->product_id)
                    ->first();

                $availableAtNewBranch = $newStock ? ($newStock->quantity - $newStock->reserved_qty) : 0;

                if ($baseSisaQty > $availableAtNewBranch && !$item->is_free_item) {
                    $productName = DB::table('products')->where('id', $item->product_id)->value('name');
                    throw new \Exception("Gudang tujuan tidak cukup stok untuk [{$productName}]. Tersedia (Satuan Dasar): {$availableAtNewBranch}");
                }

                DB::table('product_stocks')
                    ->where('branch_id', $request->new_branch_id)
                    ->where('product_id', $item->product_id)
                    ->increment('reserved_qty', $baseSisaQty);
            }

            DB::table('sales_orders')->where('id', $so->id)->update([
                'fulfillment_branch_id' => $request->new_branch_id,
                'note' => $so->note . "\n[LOG SYSTEM: Gudang dialihkan dari ID {$so->fulfillment_branch_id} ke ID {$request->new_branch_id} oleh Owner pada " . now()->format('d/m/Y H:i') . "]",
                'updated_at' => now()
            ]);

            DB::commit();
            return response()->json([
                'success' => true, 
                'message' => 'Berhasil mengalihkan pesanan ke gudang baru.'
            ]);

        } catch (\Exception $e) {
            DB::rollBack();
            return response()->json([
                'success' => false, 
                'message' => 'Gagal mengalihkan: ' . $e->getMessage()
            ], 500);
        }
    }

    public function getPendingCount()
    {
        try {
            $count = DB::table('sales_orders')
                ->where('status', 'pending')
                ->count();

            return response()->json([
                'success' => true,
                'count' => $count
            ]);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    public function markDelivered(Request $request)
    {
        $request->validate([
            'sale_id' => 'required',
            'pod_photo' => 'nullable|string', 
            'pod_latitude' => 'nullable|string',
            'pod_longitude' => 'nullable|string',
        ]);

        DB::beginTransaction();
        try {
            $sale = DB::table('sales')->where('id', $request->sale_id)->lockForUpdate()->first();
            
            if (!$sale) {
                throw new \Exception("Data Surat Jalan tidak ditemukan.");
            }
            if ($sale->delivery_status === 'terkirim') {
                throw new \Exception("Surat Jalan ini sudah berstatus terkirim sebelumnya.");
            }

            DB::table('sales')->where('id', $request->sale_id)->update([
                'delivery_status' => 'selesai',
                'pod_photo' => $request->pod_photo ?? ($sale->pod_photo ?? null),
                'pod_lat' => $request->pod_latitude ?? ($sale->delivery_lat ?? null),
                'pod_lng' => $request->pod_longitude ?? ($sale->delivery_lng ?? null),
                'updated_at' => Carbon::now()
            ]);

            DB::table('delivery_logs')->insert([
                'sale_id' => $sale->id,
                'sales_order_id' => $sale->sales_order_id,
                'driver_id' => $sale->driver_id,
                'status' => 'delivered',
                'reason' => 'Pesanan telah diterima oleh pelanggan',
                'latitude' => $request->pod_latitude,
                'longitude' => $request->pod_longitude,
                'created_at' => Carbon::now(),
                'updated_at' => Carbon::now()
            ]);

            DB::commit();

            return response()->json([
                'success' => true, 
                'message' => 'Status pengiriman berhasil diperbarui menjadi Terkirim beserta Bukti Pengiriman (POD).'
            ]);
        } catch (\Exception $e) {
            DB::rollBack();
            return response()->json(['success' => false, 'message' => $e->getMessage()]);
        }
    }

    public function cancelOrder(Request $request)
    {
        $request->validate([
            'so_id' => 'required',
            'reason' => 'required|string'
        ]);

        $user = auth()->user();

        DB::beginTransaction();
        try {
            $so = DB::table('sales_orders')->where('id', $request->so_id)->lockForUpdate()->first();
            
            if (!$so) {
                throw new \Exception("Pesanan tidak ditemukan.");
            }

            if (in_array($so->status, ['completed', 'cancelled'])) {
                throw new \Exception("Pesanan dengan status '{$so->status}' tidak dapat dibatalkan.");
            }

            $items = DB::table('sales_order_items')->where('sales_order_id', $so->id)->get();

            foreach ($items as $item) {
                $sisaQty = $item->qty - $item->qty_delivered;
                
                if ($sisaQty > 0) {
                    $product = DB::table('products')->where('id', $item->product_id)->first();
                    $multiplier = 1;
                    $uName = strtoupper(trim($item->unit_name));
                    
                    if ($uName === strtoupper(trim($product->buy_unit ?? ''))) $multiplier = $product->conversion_factor ?? 1;
                    elseif ($uName === strtoupper(trim($product->unit_3_name ?? ''))) $multiplier = $product->conversion_3_qty ?? 1;
                    elseif ($uName === strtoupper(trim($product->unit_4_name ?? ''))) $multiplier = $product->conversion_4_qty ?? 1;

                    $baseSisaQty = $sisaQty * $multiplier;

                    $stock = DB::table('product_stocks')
                        ->where('branch_id', $so->fulfillment_branch_id)
                        ->where('product_id', $item->product_id)
                        ->first();

                    if ($stock) {
                        DB::table('product_stocks')->where('id', $stock->id)->update([
                            'reserved_qty' => DB::raw("GREATEST(reserved_qty - {$baseSisaQty}, 0)")
                        ]);
                    }
                }
            }

            $cancelLog = "\n[VOID/BATAL oleh " . ($user->name ?? 'Admin') . " pada " . Carbon::now()->format('d/m/Y H:i') . "] Alasan: " . $request->reason;
            
            DB::table('sales_orders')->where('id', $so->id)->update([
                'status' => 'cancelled',
                'note' => $so->note . $cancelLog,
                'updated_at' => Carbon::now()
            ]);

            DB::commit();

            return response()->json([
                'success' => true,
                'message' => 'Pesanan berhasil dibatalkan dan antrean stok telah dilepas.'
            ]);

        } catch (\Exception $e) {
            DB::rollBack();
            return response()->json(['success' => false, 'message' => $e->getMessage()]);
        }
    }

    public function getCashBankAccounts(Request $request)
    {
        try {
            $user = $request->user();

            $query = DB::table('accounts')
                ->where('is_active', 1)
                ->where(function($q) {
                    $q->where('code', 'like', '1%') 
                      ->where(function($sq) {
                          $sq->where('name', 'like', '%Kas%')
                             ->orWhere('name', 'like', '%Bank%')
                             ->orWhere('name', 'like', '%Tunai%');
                      });
                })
                ->select('id', 'code', 'name', 'category') 
                ->orderBy('code', 'asc');

            $requestedBranch = $request->query('branch_id'); 
            $branchToFilter = ($user && $user->role === 'owner') ? $requestedBranch : ($user->branch_id ?? null);

            if (!empty($branchToFilter) && $branchToFilter !== 'all') {
                $mappedIds = DB::table('branch_accounts')
                    ->where('branch_id', $branchToFilter)
                    ->where('is_active', 1)
                    ->pluck('account_id')
                    ->toArray();

                if (!empty($mappedIds)) {
                    $query->whereIn('id', $mappedIds);
                } else {
                    $query->whereIn('id', [0]); 
                }
            }

            $accounts = $query->get();

            return response()->json([
                'success' => true,
                'data' => $accounts
            ]);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    public function show($id)
    {
        try {
            $order = DB::table('sales_orders')->where('id', $id)->first();
            
            if (!$order) {
                return response()->json([
                    'success' => false, 
                    'message' => 'Pesanan tidak ditemukan.'
                ], 404);
            }

            // Ambil item-item di dalam order tersebut
            $items = DB::table('sales_order_items')
                ->where('sales_order_id', $order->id)
                ->get();

            // Format penamaan produk
            foreach ($items as $item) {
                $product = DB::table('products')->where('id', $item->product_id)->first();
                if ($product) {
                    $item->product_name = $product->variant_name 
                        ? $product->name . ' - ' . $product->variant_name 
                        : $product->name;
                }
            }

            $order->items = $items;

            return response()->json([
                'success' => true,
                'data' => $order
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false, 
                'message' => 'Terjadi kesalahan sistem: ' . $e->getMessage()
            ], 500);
        }
    }
}