<?php

namespace App\Service\Pos;

use App\Models\ClientReward;
use App\Models\ClientRewardHistory;
use App\Models\CustomerPaymentCollection;
use App\Models\productAttribute;
use App\Models\RequisitionMaster;
use App\Models\returnPolicy;
use App\Models\SalesDetails;
use App\Models\SalesMaster;
use App\Models\SalesReturnDetails;
use App\Models\SalesReturnMaster;
use App\Models\UsedProductDetails;
use App\Models\UsedProductMaster;
use App\Models\WarehouseStock;
use Carbon\Carbon;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;

class PosService
{
    public static function salesStore($data)
    {
        return DB::transaction(function () use ($data) {
            try {
                $collection = collect($data);

                // If exchange return: create return first, then link to new invoice after sale is created
                $exchangeReturnMasterId = null;
                if (! empty($collection['exchange_return'])) {
                    $exchangeData = $collection['exchange_return'];
                    $exchangeData['return_date'] = $exchangeData['return_date'] ?? now();
                    $exchangeReturnMasterId = self::SalesReturn($exchangeData, 'exchange');
                }

                $invoiceType = $collection['invoiceType'] ?? 'MRP';

                // Invoice No
                $sell_inovice_no = SalesMaster::orderBy('id', 'desc')->first();

                if ($sell_inovice_no) {
                    $invoice_no = 'PIVO-'.sprintf('%04d', (int) $sell_inovice_no->id + 1).'-'.Carbon::now()->format('Y');
                } else {
                    $invoice_no = 'PIVO-'.'0001'.'-'.Carbon::now()->format('Y');
                }

                $for_master = $collection->only('vat', 'discount', 'after_vat', 'after_discount', 'total', 'grand_total', 'mobilebank_id', 'bank_id', 'discount_second');
                // Round master totals to whole numbers (2.5 => 3)
                $for_master = $for_master->merge([
                    'total' => round((float) ($collection['total'] ?? 0), 0),
                    'grand_total' => round((float) ($collection['grand_total'] ?? 0), 0),
                ]);

                // Calculate change amount if amount_given is provided
                $amountGiven = floatval($collection['amount_given'] ?? 0);
                $grandTotal = floatval($collection['grand_total'] ?? 0);
                $changeAmount = max(0, $amountGiven - $grandTotal);

                $prepare_master = $for_master->merge([
                    'invoice_no' => $invoice_no,
                    'user_id' => Auth::user()->id,
                    'transfer_type' => isset($collection['mobilebank_id']) ? 'mobilebank' : (isset($collection['bank_id']) ? 'bank' : 'cash'),
                    'client_id' => $collection['clientId'] ?? null,
                    'sr_id' => ! empty($collection['sales_person_id']) ? $collection['sales_person_id'] : null,
                    'sales_person_id' => ! empty($collection['sales_person_id']) ? $collection['sales_person_id'] : null,
                    'invoice_type' => $invoiceType,
                    'supplier_id' => $collection['supplier_id'],
                    'discount_type' => $collection['discountType'] ?? null,
                    'location_id' => request()->location_id,
                    'order_no' => $collection['order_no'] ?? null,
                    'select_requisition' => isset($collection['select_requisition']) && $collection['select_requisition'] != null ? $collection['select_requisition']['id'] : null,
                    'amount_given' => $amountGiven,
                    'change_amount' => $changeAmount,
                    'created_at' => Carbon::parse(request()->currentDate),
                    'pos_table_parcel_json' => $collection['pos_table_parcel_json'] ?? null,
                ]);

                $master = SalesMaster::create($prepare_master->toArray());

                // Link exchange return to the new sale (exchange_id lives on sales_return_masters)
                if ($exchangeReturnMasterId) {
                    SalesReturnMaster::where('id', $exchangeReturnMasterId)->update(['exchange_id' => $master->id]);
                }

                foreach ($collection['product'] as $key => $value) {
                    // Safely resolve discount type; default to Fixed when attribute is missing
                    $discountType = $value['attribute']['discount_type'] ?? 'Fixed';

                    $unitPrice = $invoiceType == 'TP'
                        ? ($value['tp_price'] ?? 0)
                        : ($invoiceType == 'MRP' ? ($value['mrp_price'] ?? 0) : 0);

                    $qty = $collection['increment'][$value['id']][0] ?? 0;
                    $subTotal = $unitPrice * $qty;

                    if ($discountType == 'Percentage') {
                        // Percentage discount on line subtotal
                        $afterDiscount = ($subTotal * ($collection['ProductDiscount'][$value['id']][0] ?? 0)) / 100;
                    } else {
                        // Flat discount amount
                        $afterDiscount = (float) ($collection['ProductDiscount'][$value['id']][0] ?? 0);
                    }

                    // Round discount to 2 decimals, total to nearest whole number
                    $roundedDiscount = round($afterDiscount, 2);
                    // e.g. 2.5 => 3, 2.2 => 2
                    $lineTotal = round($subTotal - $roundedDiscount, 0);

                    SalesDetails::create([
                        'sales_masters_id' => $master->id,
                        'created_at' => Carbon::parse(request()->currentDate)->addDay(),
                        'product_id' => $value['product_id'],
                        'attribute_id' => $value['attribute_id'],
                        'warranty_code' => $value['warranty_code'] ?? null,
                        'warranty_time' => $value['warranty_time'] ?? null,
                        'warranty_type' => $value['warranty_type'] ?? null,
                        'qty' => $qty,
                        'fqty' => isset($collection['increment'][$value['id']][2])
                                    ? $collection['increment'][$value['id']][2]
                                    : 0,

                        'discount' => $collection['ProductDiscount'][$value['id']][0] ? $collection['ProductDiscount'][$value['id']][0] : 0,
                        'after_discount' => $roundedDiscount,
                        'discount_type' => $discountType,
                        'batch_no' => $value['batch_no'] ?? null,
                        'category_id' => $value['product']['category_id'],
                        'unit_id' => null,
                        'buy_price' => $value['unit_price'] ?? 0,
                        'price' => $unitPrice,
                        'total' => $lineTotal,

                    ]);

                    // $store_wareHouse = WarehouseStock::when(setting()->location_id == 3, fn($q) => $q->where('batch_no', $value['batch_no']))->where('attribute_id', $value['attribute_id'])->where(function ($query) {
                    //     if (!empty(request()->location_id)) {
                    //         $query->where('warehouse_id', request()->location_id);
                    //     }
                    // })->first();
                    // $currentQty = $store_wareHouse->quantity -( $collection['increment'][$value['id']][0] + (isset($collection['increment'][$value['id']][2]) ? $collection['increment'][$value['id']][2] : 0));
                    // if ($invoiceType == 'TP') {
                    //     $tp_price_total = $store_wareHouse->tp_price * $collection['increment'][$value['id']][0];
                    //     $store_wareHouse->update([
                    //         'quantity' => $currentQty,
                    //         'tp_price_total' => $store_wareHouse->tp_price_total - $tp_price_total,
                    //     ]);
                    // } else {
                    //     $mrp_price_total = $store_wareHouse->mrp_price * $collection['increment'][$value['id']][0];
                    //     $store_wareHouse->update([
                    //         'quantity' => $currentQty,
                    //         'mrp_price_total' => $store_wareHouse->mrp_price_total - $mrp_price_total,
                    //     ]);
                    // }
                }

                if (isset($collection['select_requisition']) && $collection['select_requisition'] != null) {
                    RequisitionMaster::where('id', $collection['select_requisition']['id'])->update([
                        'status' => 2,
                    ]);
                }

                $rewardInfo = null;
                if (isset($collection['payments']) && is_array($collection['payments']) && count($collection['payments']) > 0) {
                    // Handle multiple payments
                    $totalPaymentAmount = 0;
                    foreach ($collection['payments'] as $payment) {
                        if (isset($payment['amount']) && $payment['amount'] > 0) {
                            $totalPaymentAmount += $payment['amount'];
                            $status = $collection['grand_total'] == array_sum(array_column($collection['payments'], 'amount')) ? 'paid' : '';
                            CustomerPaymentCollection::create([
                                'customer_id' => ! empty($collection['clientId']) && $collection['clientId'] != 0
                                    ? $collection['clientId']
                                    : null,

                                'date' => Carbon::parse($payment['date']),
                                'payment' => $payment['amount'],
                                'payment_type' => $payment['method'],
                                'payment_id' => ! empty($payment['account']) ? $payment['account'] : null,
                                'invoice_id' => $master->id,

                                'sales_person_id' => ! empty($collection['sales_person_id']) ? $collection['sales_person_id'] : null,
                                'warehouse_id' => request()->location_id,
                                'status' => $status,
                                'order_no' => $payment['collection_reference'] ?? null,
                                'notes' => $payment['notes'] ?? null,
                                'collection_reference' => $payment['collection_reference'] ?? null,
                            ]);
                        }
                    }

                    // Check and update client rewards if payment >= 1000
                    if (! empty($collection['clientId']) && $collection['clientId'] != 0 && $totalPaymentAmount >= 1000) {
                        $rewardInfo = self::checkAndUpdateClientReward($collection['clientId'], request()->location_id, $totalPaymentAmount, $master->id);
                    }
                }

                return ['master_id' => $master->id, 'reward_info' => $rewardInfo];
            } catch (\Exception $e) {
                DB::rollBack();
                throw $e;
            }
        });
    }

    public static function salesUpdate($data, $id)
    {

        return DB::transaction(function () use ($data, $id) {
            $collection = collect($data);

            /** @var \App\Models\SalesMaster $master */
            $master = SalesMaster::with('details')->findOrFail($id);

            // Incoming (possibly changed) invoice type; default to existing or MRP
            $invoiceType = ! empty($collection['invoiceType'])
            ? $collection['invoiceType']
            : (! empty($master->invoice_type) ? $master->invoice_type : 'MRP');

            // Preserve invoice_no; update other fields
            $for_master = $collection->only(
                'vat',
                'discount',
                'after_vat',
                'after_discount',
                'total',
                'grand_total',
                'mobilebank_id',
                'bank_id',
                'discount_second'
            );
            // Round master totals to whole numbers (2.5 => 3)
            $for_master = $for_master->merge([
                'total' => round((float) ($collection['total'] ?? 0), 0),
                'grand_total' => round((float) ($collection['grand_total'] ?? 0), 0),
            ]);

            // Calculate change amount if amount_given is provided
            $amountGiven = floatval($collection['amount_given'] ?? $master->amount_given ?? 0);
            $grandTotal = floatval($collection['grand_total'] ?? $master->grand_total ?? 0);
            $changeAmount = max(0, $amountGiven - $grandTotal);

            $prepare_master = $for_master->merge([
                'invoice_no' => $master->invoice_no, // do not change
                'user_id' => Auth::id(),
                'transfer_type' => isset($collection['mobilebank_id']) ? 'mobilebank' : (isset($collection['bank_id']) ? 'bank' : 'cash'),
                'client_id' => $collection['clientId'] ?? null,
                'sr_id' => $collection['srID'],
                'sales_person_id' => ! empty($collection['sales_person_id']) ? $collection['sales_person_id'] : null,
                'invoice_type' => $invoiceType,
                'supplier_id' => $collection['supplier_id'],
                'location_id' => request()->location_id,
                'discount_type' => $collection['discountType'] ?? null,
                'order_no' => $collection['order_no'] ?? null,
                'amount_given' => $amountGiven,
                'change_amount' => $changeAmount,
                // keep original created_at unless caller explicitly wants to change it
                'created_at' => Carbon::parse(request()->currentDate ?? $master->created_at),
                'pos_table_parcel_json' => $collection['pos_table_parcel_json'] ?? $master->pos_table_parcel_json,
            ]);

            // ---------- 1) REVERT OLD STOCK MOVES ----------
            $oldWarehouseId = $master->location_id;
            $oldInvoiceType = $master->invoice_type ?? 'MRP';

            // foreach ($master->details as $old) {
            //     $wsQuery = WarehouseStock::when(setting()->location_id == 3, fn($q) => $q->where('batch_no', $old->batch_no))
            //         ->where('attribute_id', $old->attribute_id)
            //         ->where(function ($q) use ($oldWarehouseId) {
            //             if (!empty($oldWarehouseId)) {
            //                 $q->where('warehouse_id', $oldWarehouseId);
            //             }
            //         });

            //     if ($store = $wsQuery->first()) {
            //         // Put quantity back
            //         $newQty = $store->quantity + $old->qty;

            //         if ($oldInvoiceType === 'TP') {
            //             $deltaTotal = ($store->tp_price ?? 0) * $old->qty;
            //             $store->update([
            //                 'quantity'      => $newQty,
            //                 'tp_price_total' => ($store->tp_price_total ?? 0) + $deltaTotal,
            //             ]);
            //         } else {
            //             $deltaTotal = ($store->mrp_price ?? 0) * $old->qty;
            //             $store->update([
            //                 'quantity'        => $newQty,
            //                 'mrp_price_total' => ($store->mrp_price_total ?? 0) + $deltaTotal,
            //             ]);
            //         }
            //     }
            // }

            // Clear old details (simplest, safest for stock reconciliation)
            SalesDetails::where('sales_masters_id', $master->id)->delete();

            // ---------- 2) UPDATE MASTER ----------
            $master->update($prepare_master->toArray());

            // ---------- Helpers ----------
            $getUnitPrice = function ($row) use ($invoiceType) {
                return $invoiceType === 'TP'
                    ? ($row['tp_price'] ?? 0)
                    : ($invoiceType === 'MRP' ? ($row['mrp_price'] ?? 0) : 0);
            };

            $getAfterDiscount = function ($row, $qty, $discountInput) use ($getUnitPrice) {
                $unit = $getUnitPrice($row);
                $subtotal = $unit * $qty;

                $dtype = data_get($row, 'attribute.discount_type', null);
                if ($dtype === 'Percentage') {
                    // discountInput is expected to be percentage here
                    $after = ($subtotal * (float) $discountInput) / 100;

                    return round($after, 2);
                } else {
                    // discountInput is flat amount on total
                    $after = (float) ($discountInput ?: 0);

                    return round($after, 2);
                }
            };

            // ---------- 3) APPLY NEW DETAILS + STOCK MOVES ----------
            foreach ($collection['product'] as $value) {
                $lineKey = $value['id']; // key used by increment/ProductDiscount arrays

                $qty = (float) ($collection['increment'][$lineKey][0] ?? 0);
                $discountInput = $collection['ProductDiscount'][$lineKey][0] ?? 0;

                $unitPrice = $getUnitPrice($value);
                $afterDiscount = $getAfterDiscount($value, $qty, $discountInput);

                // Nearest whole number for total (2.5 => 3, 2.2 => 2)
                $lineTotal = round(($unitPrice * $qty) - $afterDiscount, 0);

                // Create detail
                SalesDetails::create([
                    'sales_masters_id' => $master->id,
                    'created_at' => Carbon::parse(request()->currentDate ?? $master->created_at)->addDay(),
                    'product_id' => $value['product_id'],
                    'attribute_id' => $value['attribute_id'],
                    'warranty_code' => $value['warranty_code'] ?? null,
                    'warranty_time' => $value['warranty_time'] ?? null,
                    'warranty_type' => $value['warranty_type'] ?? null,
                    'qty' => $qty,
                    'fqty' => $collection['increment'][$value['id']][2]
                            ?? ($collection['increment'][$value['id']] ?? null)[2]
                            ?? 0,

                    'discount' => $discountInput ?: 0,
                    'after_discount' => $afterDiscount,
                    'discount_type' => data_get($value, 'attribute.discount_type'),
                    'batch_no' => $value['batch_no'] ?? null,
                    'category_id' => data_get($value, 'product.category_id'),
                    'unit_id' => null,
                    'buy_price' => $value['unit_price'] ?? 0,
                    'price' => $unitPrice,
                    'total' => $lineTotal,
                ]);

                // Decrease stock for NEW state
                // $wsQuery = WarehouseStock::when(setting()->location_id == 3, fn($q) => $q->where('batch_no', $value['batch_no'] ?? null))
                //     ->where('attribute_id', $value['attribute_id'])
                //     ->where(function ($q) {
                //         if (!empty(request()->location_id)) {
                //             $q->where('warehouse_id', request()->location_id);
                //         }
                //     });

                // if ($store = $wsQuery->first()) {
                //     $newQty = max(0, $store->quantity - ($qty + (isset($collection['increment'][$value['id']][2]) ? $collection['increment'][$value['id']][2] : 0)));

                //     if ($invoiceType === 'TP') {
                //         $deltaTotal = ($store->tp_price ?? 0) * $qty;
                //         $store->update([
                //             'quantity'       => $newQty,
                //             'tp_price_total' => max(0, ($store->tp_price_total ?? 0) - $deltaTotal),
                //         ]);
                //     } else {
                //         $deltaTotal = ($store->mrp_price ?? 0) * $qty;
                //         $store->update([
                //             'quantity'         => $newQty,
                //             'mrp_price_total'  => max(0, ($store->mrp_price_total ?? 0) - $deltaTotal),
                //         ]);
                //     }
                // }
            }

            // ---------- 4) PAYMENT UPSERT ----------

            if (isset($collection['payments']) && is_array($collection['payments']) && count($collection['payments']) > 0) {
                CustomerPaymentCollection::withoutGlobalScope('typeScope')
                    ->where('invoice_id', $master->id)
                    ->delete();

                // Handle multiple payments
                foreach ($collection['payments'] as $payment) {
                    $amount = isset($payment['amount']) ? (int) $payment['amount'] : 0;

                    if ($amount > 0) {

                        $status = $collection['grand_total'] == array_sum(array_column($collection['payments'], 'amount')) ? 'paid' : '';

                        CustomerPaymentCollection::create([
                            'customer_id' => ! empty($collection['clientId']) && $collection['clientId'] != 0
                                ? $collection['clientId']
                                : null,

                            'date' => Carbon::parse($payment['date']),
                            'payment' => $amount,
                            'payment_type' => $payment['method'],
                            'payment_id' => ! empty($payment['account']) ? $payment['account'] : null,
                            'invoice_id' => $master->id,
                            'warehouse_id' => request()->location_id,
                            'status' => $status,
                            'sales_person_id' => ! empty($collection['sales_person_id']) ? $collection['sales_person_id'] : null,
                            'order_no' => $payment['collection_reference'] ?? null,
                            'notes' => $payment['notes'] ?? null,
                            'collection_reference' => $payment['collection_reference'] ?? null,
                        ]);
                    }
                }
            }

            return $master->id;
        });
    }

    public static function SalesReturn($data, $returnType = 'cash')
    {
        $invoice_no = SalesReturnMaster::withoutGlobalScope('typeScope')->orderBy('id', 'desc')->first();

        $invoice_no = invoice_no($invoice_no?->id, 'RT');

        $collection = collect($data);
        $total = array_reduce($collection['list'], function ($acc, $item) {
            return $acc + $item['total'];
        }, 0);
        $salesSupplier = SalesMaster::where('id', $collection['invoice_no'])->first();

        $returnMaster = SalesReturnMaster::create([
            'reference_no' => $invoice_no,
            'return_date' => $collection['return_date'],
            'customer_id' => isset($collection['costomer_id']) ? $collection['costomer_id'] : null,
            'sales_invoice_no' => $collection['invoice_no'],
            'supplier_id' => $collection['supplier_id'] ?? null,
            'total' => $total,
            'status' => 1,
            'shop_id' => auth()->user()->shop_id,
            'create_by' => auth()->user()->id,
            'location_id' => $collection['warehouse_id'],
            'return_type' => $returnType,
        ]);

        foreach ($collection['list'] as $key => $value) {
            SalesReturnDetails::create([
                'sales_return_master_id' => $returnMaster->id,
                'product_id' => $value['product_id'],
                'attribute_id' => $value['attribute_id'],
                'sales_quantity' => $value['sales_quantity'],
                'return_qty' => $value['return_quantity'],
                'sales_price' => $value['sales'],
                'return_price' => $value['return_price'],
                'total' => $value['total'],
            ]);

            WarehouseStock::where('attribute_id', $value['attribute_id'])->where('warehouse_id', $salesSupplier->location_id)->update([
                'quantity' => DB::raw('quantity + '.$value['return_quantity']),
            ]);
        }

        return $returnMaster->id;
    }

    public static function SalesReturnUpdate($data, $id)
    {

        $collection = collect($data);

        $total = array_reduce($collection['list'], function ($acc, $item) {
            return $acc + $item['total'];
        }, 0);
        $salesSupplier = SalesMaster::where('id', $collection['invoice_no'])->first();

        $returnMaster = SalesReturnMaster::where('id', $id)->update([
            'return_date' => $collection['return_date'],
            'customer_id' => isset($collection['customer_id']) ? $collection['customer_id'] : null,
            'sales_invoice_no' => $collection['invoice_no'],
            'total' => $total,
            'status' => 1,
            'create_by' => auth()->user()->id,
        ]);

        $parentDetails = SalesReturnDetails::where('sales_return_master_id', $id)->get();
        foreach ($parentDetails as $key => $item) {
            WarehouseStock::where('attribute_id', $item->attribute_id)->where('warehouse_id', $salesSupplier->location_id)->update([
                'quantity' => DB::raw('quantity - '.$item->return_qty),
            ]);
            $item->delete();
        }

        foreach ($collection['list'] as $key => $value) {
            SalesReturnDetails::create([
                'sales_return_master_id' => $id,
                'product_id' => $value['product_id'],
                'attribute_id' => $value['attribute_id'],
                'sales_quantity' => $value['sales_quantity'],
                'return_qty' => $value['return_quantity'],
                'sales_price' => $value['sales'],
                'return_price' => $value['return_price'],
                'total' => $value['total'],
            ]);

            WarehouseStock::where('attribute_id', $value['attribute_id'])->where('warehouse_id', $salesSupplier->location_id)->update([
                'quantity' => DB::raw('quantity + '.$value['return_quantity']),
            ]);
        }
    }

    public static function DirectReturn($data)
    {

        DB::transaction(function () use ($data) {
            try {
                $collect = collect($data);
                $total = array_reduce($collect['list'], function ($acc, $item) {
                    return $acc + $item['total'];
                }, 0);
                $returnMaster = SalesReturnMaster::create([
                    'reference_no' => $collect['reference_no'],
                    'return_date' => $collect['return_date'],
                    'total' => $total,
                    'supplier_id' => null,
                    'customer_id' => isset($collect['customer_id']) ? $collect['customer_id'] : null,
                    'create_by' => auth()->user()->id,
                    'policy' => isset($collect['auto_merge']) && $collect['auto_merge'] ? true : false,
                    'location_id' => $collect['warehouse_id'],
                    'status' => 2,
                ]);
                foreach ($collect['list'] as $key => $value) {
                    SalesReturnDetails::create([
                        'sales_return_master_id' => $returnMaster->id,
                        'product_id' => $value['product_id'],
                        'attribute_id' => $value['attribute_id'],
                        'sales_price' => $value['price'],
                        'return_price' => $value['price'],
                        'return_qty' => $value['quantity'],
                        'invoice_type' => $value['type'] ?? 'MRP',
                        'total' => $value['total'],
                    ]);

                    if (setting()->business_id == 7) {
                        $productAttr = productAttribute::where('id', $value['attribute_id'])->first();

                        if ($productAttr->gold_weight == $value['gold_weight'] && $productAttr->diamond_weight == $value['diamond_weight']) {

                            WarehouseStock::where('attribute_id', $value['attribute_id'])
                                ->where('warehouse_id', $collect['warehouse_id'])
                                ->update([
                                    'quantity' => DB::raw('quantity +'.$value['quantity']),
                                ]);
                        } else {
                            $newProduct = productAttribute::create([
                                'product_id' => $value['product_id'],
                                'sku' => $productAttr->sku,
                                'barcode' => str_pad(mt_rand(0, 999999999999), 10, '0', STR_PAD_LEFT),
                                'color_id' => $productAttr->color_id,
                                'size_id' => $productAttr->size_id,
                                'unit_id' => $productAttr->unit_id,
                                'brand_id' => $productAttr->brand_id,
                                'unit_price' => $productAttr->unit_price,
                                'mrp' => $productAttr->mrp_price,
                                'tp' => $productAttr->tp_price,
                                'gold_weight' => $value['gold_weight'],
                                'diamond_weight' => $value['diamond_weight'],
                                'gold_unit' => $productAttr->gold_unit,
                                'diamond_unit' => $productAttr->diamond_unit,

                            ]);
                            WarehouseStock::create([
                                'warehouse_id' => $collect['warehouse_id'],
                                'product_id' => $value['product_id'],
                                'attribute_id' => $newProduct->id,
                                'supplier_id' => null,
                                'batch_no' => null,
                                'quantity' => $value['quantity'],
                                'unit_price' => $productAttr->unit_price,
                                'mrp_price' => $productAttr->mrp_price,
                                'mrp_price_total' => $productAttr->mrp_price * $value['quantity'],
                                'tp_price' => $productAttr->tp_price,
                                'tp_price_total' => $productAttr->tp_price * $value['quantity'],
                            ]);
                            SalesReturnDetails::where('sales_return_master_id', $returnMaster->id)->where('attribute_id', $value['attribute_id'])->update([
                                'attribute_id' => $newProduct->id,
                            ]);
                        }
                    } else {
                        $warehouseStock = WarehouseStock::where('attribute_id', $value['attribute_id'])
                            ->where('warehouse_id', $collect['warehouse_id'])
                            ->first();

                        if ($warehouseStock) {
                            // Update existing stock
                            $warehouseStock->update([
                                'quantity' => DB::raw('quantity + '.$value['quantity']),
                            ]);
                        } else {
                            // Create new warehouse stock from product attribute data
                            $productAttr = productAttribute::where('id', $value['attribute_id'])->first();
                            if ($productAttr) {
                                WarehouseStock::create([
                                    'warehouse_id' => $collect['warehouse_id'],
                                    'product_id' => $value['product_id'],
                                    'attribute_id' => $value['attribute_id'],
                                    'supplier_id' => null,
                                    'batch_no' => null,
                                    'quantity' => $value['quantity'],
                                    'unit_price' => $productAttr->unit_price,
                                    'mrp_price' => $productAttr->mrp,
                                    'mrp_price_total' => $productAttr->mrp * $value['quantity'],
                                    'tp_price' => $productAttr->tp,
                                    'tp_price_total' => $productAttr->tp * $value['quantity'],
                                ]);
                            }
                        }
                    }
                }
            } catch (\Exception $e) {
                DB::rollBack();
                throw $e;
            }
        });
    }

    public static function DirectReturnUpdate($data, $id)
    {
        DB::transaction(function () use ($data, $id) {
            try {
                $collect = collect($data);

                $total = array_reduce($collect['list'], function ($acc, $item) {
                    return $acc + $item['total'];
                }, 0);

                $salesReturnMaster = SalesReturnMaster::where('id', $id)->where('policy', false)->first();
                if ($salesReturnMaster) {
                    $parentDetails = SalesReturnDetails::where('sales_return_master_id', $salesReturnMaster->id)->get();
                    foreach ($parentDetails as $key => $item) {
                        WarehouseStock::where('attribute_id', $item->attribute_id)->where('warehouse_id', $collect['warehouse_id'])->update([
                            'quantity' => DB::raw('quantity - '.$item->return_qty),
                        ]);
                        $item->delete();
                    }
                } else {
                    SalesReturnDetails::where('sales_return_master_id', $id)->delete();
                }

                // remove Previous policy
                returnPolicy::where('sales_return_master_id', $id)->delete();

                SalesReturnMaster::where('id', $id)->update([
                    'return_date' => $collect['return_date'],
                    'total' => $total,
                    'supplier_id' => $collect['supplier_id'],
                    'location_id' => $collect['warehouse_id'],
                    'policy' => isset($collect['auto_merge']) && $collect['auto_merge'] ? true : false,
                ]);

                foreach ($collect['list'] as $key => $value) {
                    SalesReturnDetails::create([
                        'sales_return_master_id' => $id,
                        'product_id' => $value['product_id'],
                        'attribute_id' => $value['attribute_id'],
                        'sales_price' => $value['price'],
                        'return_price' => $value['price'],
                        'return_qty' => $value['quantity'],
                        'invoice_type' => $value['type'] ?? 'MRP',
                        'total' => $value['total'],

                    ]);

                    if (setting()->business_id == 7) {
                        $productAttr = productAttribute::where('id', $value['attribute_id'])->first();

                        if ($productAttr->gold_weight == $value['gold_weight'] && $productAttr->diamond_weight == $value['diamond_weight']) {
                            WarehouseStock::where('attribute_id', $value['attribute_id'])
                                ->where('warehouse_id', $collect['warehouse_id'])
                                ->update([
                                    'quantity' => DB::raw('quantity +'.$value['quantity']),
                                ]);
                        } else {

                            $newProduct = productAttribute::create([
                                'product_id' => $value['product_id'],
                                'sku' => $productAttr->sku,
                                'barcode' => str_pad(mt_rand(0, 999999999999), 10, '0', STR_PAD_LEFT),
                                'color_id' => $productAttr->color_id,
                                'size_id' => $productAttr->size_id,
                                'unit_id' => $productAttr->unit_id,
                                'brand_id' => $productAttr->brand_id,
                                'unit_price' => $productAttr->unit_price,
                                'mrp' => $productAttr->mrp_price,
                                'tp' => $productAttr->tp_price,
                                'gold_weight' => $value['gold_weight'],
                                'diamond_weight' => $value['diamond_weight'],
                                'gold_unit_id' => $productAttr->gold_unit_id,
                                'diamond_unit_id' => $productAttr->diamond_unit_id,

                            ]);
                            WarehouseStock::create([
                                'warehouse_id' => $collect['warehouse_id'],
                                'product_id' => $value['product_id'],
                                'attribute_id' => $newProduct->id,
                                'supplier_id' => null,
                                'batch_no' => null,
                                'quantity' => $value['quantity'],
                                'unit_price' => $productAttr->unit_price,
                                'mrp_price' => $productAttr->mrp_price,
                                'mrp_price_total' => $productAttr->mrp_price * $value['quantity'],
                                'tp_price' => $productAttr->tp_price,
                                'tp_price_total' => $productAttr->tp_price * $value['quantity'],
                            ]);
                            SalesReturnDetails::where('sales_return_master_id', $id)->where('attribute_id', $value['attribute_id'])->update([
                                'attribute_id' => $newProduct->id,
                            ]);
                        }
                    } else {

                        $warehouseStock = WarehouseStock::where('attribute_id', $value['attribute_id'])
                            ->where('warehouse_id', $collect['warehouse_id'])
                            ->first();

                        if ($warehouseStock) {
                            // Update existing stock
                            $warehouseStock->update([
                                'quantity' => DB::raw('quantity + '.$value['quantity']),
                            ]);
                        } else {
                            // Create new warehouse stock from product attribute data
                            $productAttr = productAttribute::where('id', $value['attribute_id'])->first();
                            if ($productAttr) {
                                WarehouseStock::create([
                                    'warehouse_id' => $collect['warehouse_id'],
                                    'product_id' => $value['product_id'],
                                    'attribute_id' => $value['attribute_id'],
                                    'supplier_id' => null,
                                    'batch_no' => null,
                                    'quantity' => $value['quantity'],
                                    'unit_price' => $productAttr->unit_price,
                                    'mrp_price' => $productAttr->mrp,
                                    'mrp_price_total' => $productAttr->mrp * $value['quantity'],
                                    'tp_price' => $productAttr->tp,
                                    'tp_price_total' => $productAttr->tp * $value['quantity'],
                                ]);
                            }
                        }
                    }
                }
            } catch (\Exception $e) {
                DB::rollBack();
                throw $e;
            }
        });
    }

    public static function SalesDelete($id)
    {

        try {
            DB::beginTransaction();
            $salesMaster = SalesMaster::where('id', $id)->first();
            $salesDetails = SalesDetails::where('sales_masters_id', $id)->get();
            foreach ($salesDetails as $key => $value) {
                WarehouseStock::where('warehouse_id', $salesMaster->location_id)->where('attribute_id', $value->attribute_id)->update([
                    'quantity' => DB::raw('quantity +'.$value->qty),
                ]);
            }

            $requisition = RequisitionMaster::find($salesMaster->select_requisition);
            if ($requisition) {
                $requisition->status = 0;
                $requisition->save();
            }
            CustomerPaymentCollection::where('invoice_id', $salesMaster->id)->delete();
            $salesMaster->delete();
            DB::commit();
        } catch (\Exception $e) {
            DB::rollBack();
            throw $e;
        }

    }

    public static function UsedProduct($data)
    {
        DB::transaction(function () use ($data) {
            try {
                $collect = collect($data);
                $total = array_reduce($collect['list'], function ($acc, $item) {
                    return $acc + $item['total'];
                }, 0);

                // Generate reference number if not provided
                $referenceNo = $collect['reference_no'];
                if (empty($referenceNo)) {
                    $lastUsedProduct = UsedProductMaster::orderBy('id', 'DESC')->first();
                    $referenceNo = invoice_no($lastUsedProduct?->id ?? false, 'UP');
                }

                // Format date to MySQL date format (YYYY-MM-DD)
                $usedDate = $collect['used_date'] ?? $collect['return_date'];
                if ($usedDate) {
                    // If it's an ISO string, extract just the date part
                    if (is_string($usedDate) && strpos($usedDate, 'T') !== false) {
                        $usedDate = Carbon::parse($usedDate)->format('Y-m-d');
                    } elseif ($usedDate instanceof \DateTime) {
                        $usedDate = Carbon::instance($usedDate)->format('Y-m-d');
                    }
                }

                $usedProductMaster = UsedProductMaster::create([
                    'reference_no' => $referenceNo,
                    'used_date' => $usedDate,
                    'total' => $total,
                    'create_by' => auth()->user()->id,
                    'location_id' => $collect['warehouse_id'],
                ]);
                foreach ($collect['list'] as $key => $value) {
                    // Check stock availability before creating
                    $warehouseStock = WarehouseStock::where('attribute_id', $value['attribute_id'])
                        ->where('warehouse_id', $collect['warehouse_id'])
                        ->first();

                    if (! $warehouseStock) {
                        throw new \Exception('Product not found in warehouse stock for attribute ID: '.$value['attribute_id']);
                    }

                    $availableStock = (float) $warehouseStock->quantity;
                    $requestedQty = (float) $value['quantity'];

                    if ($requestedQty > $availableStock) {
                        throw new \Exception('Insufficient stock. Available: '.$availableStock.', Requested: '.$requestedQty.' for product attribute ID: '.$value['attribute_id']);
                    }

                    UsedProductDetails::create([
                        'used_product_master_id' => $usedProductMaster->id,
                        'product_id' => $value['product_id'],
                        'attribute_id' => $value['attribute_id'],
                        'price' => $value['price'],
                        'used_qty' => $value['quantity'],
                        'invoice_type' => $value['type'] ?? 'MRP',
                        'total' => $value['total'],
                    ]);

                    // Decrease warehouse stock
                    $warehouseStock->update([
                        'quantity' => DB::raw('quantity - '.$value['quantity']),
                    ]);
                }
            } catch (\Exception $e) {
                DB::rollBack();
                throw $e;
            }
        });
    }

    public static function UsedProductUpdate($data, $id)
    {
        DB::transaction(function () use ($data, $id) {
            try {
                $collect = collect($data);

                $total = array_reduce($collect['list'], function ($acc, $item) {
                    return $acc + $item['total'];
                }, 0);

                $usedProductMaster = UsedProductMaster::where('id', $id)->first();
                if ($usedProductMaster) {
                    // Restore previous stock (increase back)
                    $parentDetails = UsedProductDetails::where('used_product_master_id', $usedProductMaster->id)->get();
                    foreach ($parentDetails as $key => $item) {
                        WarehouseStock::where('attribute_id', $item->attribute_id)
                            ->where('warehouse_id', $collect['warehouse_id'])
                            ->update([
                                'quantity' => DB::raw('quantity + '.$item->used_qty),
                            ]);
                        $item->delete();
                    }
                } else {
                    UsedProductDetails::where('used_product_master_id', $id)->delete();
                }

                // Format date to MySQL date format (YYYY-MM-DD)
                $usedDate = $collect['used_date'] ?? $collect['return_date'];
                if ($usedDate) {
                    // If it's an ISO string, extract just the date part
                    if (is_string($usedDate) && strpos($usedDate, 'T') !== false) {
                        $usedDate = Carbon::parse($usedDate)->format('Y-m-d');
                    } elseif ($usedDate instanceof \DateTime) {
                        $usedDate = Carbon::instance($usedDate)->format('Y-m-d');
                    }
                }

                UsedProductMaster::where('id', $id)->update([
                    'used_date' => $usedDate,
                    'total' => $total,
                    'location_id' => $collect['warehouse_id'],
                ]);

                foreach ($collect['list'] as $key => $value) {
                    // Check stock availability before creating
                    $warehouseStock = WarehouseStock::where('attribute_id', $value['attribute_id'])
                        ->where('warehouse_id', $collect['warehouse_id'])
                        ->first();

                    if (! $warehouseStock) {
                        throw new \Exception('Product not found in warehouse stock for attribute ID: '.$value['attribute_id']);
                    }

                    $availableStock = (float) $warehouseStock->quantity;
                    $requestedQty = (float) $value['quantity'];

                    if ($requestedQty > $availableStock) {
                        throw new \Exception('Insufficient stock. Available: '.$availableStock.', Requested: '.$requestedQty.' for product attribute ID: '.$value['attribute_id']);
                    }

                    UsedProductDetails::create([
                        'used_product_master_id' => $id,
                        'product_id' => $value['product_id'],
                        'attribute_id' => $value['attribute_id'],
                        'price' => $value['price'],
                        'used_qty' => $value['quantity'],
                        'invoice_type' => $value['type'] ?? 'MRP',
                        'total' => $value['total'],
                    ]);

                    // Decrease warehouse stock
                    $warehouseStock->update([
                        'quantity' => DB::raw('quantity - '.$value['quantity']),
                    ]);
                }
            } catch (\Exception $e) {
                DB::rollBack();
                throw $e;
            }
        });
    }

    /**
     * Check and update client reward based on payment amount
     *
     * @param  int  $clientId
     * @param  int  $warehouseId
     * @param  float  $paymentAmount
     * @param  int  $salesMasterId
     * @return array|null Reward information with message
     */
    public static function checkAndUpdateClientReward($clientId, $warehouseId, $paymentAmount, $salesMasterId)
    {
        if ($paymentAmount < 1000) {
            return null;
        }

        $today = Carbon::today();

        // Get or create client reward record
        $reward = ClientReward::firstOrCreate(
            [
                'client_id' => $clientId,
                'warehouse_id' => $warehouseId,
            ],
            [
                'consecutive_days' => 0,
                'last_payment_date' => null,
                'last_payment_amount' => 0,
                'is_active' => true,
            ]
        );

        $message = null;
        $messageType = null;
        $paymentCount = 0;

        // Increment payment count (count how many times client paid >= 1000, not day-based)
        $reward->consecutive_days += 1; // Using consecutive_days field to store payment count
        $paymentCount = $reward->consecutive_days;

        // If payment count exceeds 16, reset to 1
        if ($paymentCount > 16) {
            $reward->consecutive_days = 1;
            $paymentCount = 1;
        }

        // Determine message based on payment count
        if ($paymentCount == 1) {
            $message = 'Client take reward card';
            $messageType = 'reward_card';
        } elseif ($paymentCount == 6) {
            $message = '6 payment combine take special gift';
            $messageType = 'special_gift';
            $reward->total_rewards_earned += 1;
        } elseif ($paymentCount == 11) {
            $message = '11 payment combine take special gift';
            $messageType = 'special_gift';
            $reward->total_rewards_earned += 1;
        } elseif ($paymentCount == 16) {
            $message = '16 payment combine take special gift';
            $messageType = 'special_gift';
            $reward->total_rewards_earned += 1;
        } else {
            // For other payment counts (2, 3, 4, 5, 7, 8, 9, 10, 12, 13, 14, 15) - just show count
            $message = "Payment {$paymentCount}";
            $messageType = 'payment_count';
        }

        // Update reward record
        $reward->last_payment_date = $today;
        $reward->last_payment_amount = $paymentAmount;
        $reward->save();

        // Save to history
        ClientRewardHistory::create([
            'client_id' => $clientId,
            'warehouse_id' => $warehouseId,
            'sales_master_id' => $salesMasterId,
            'day_number' => $paymentCount, // Storing payment count in day_number field
            'payment_amount' => $paymentAmount,
            'payment_date' => $today,
            'message_type' => $messageType,
            'message' => $message,
        ]);

        // Always return reward info with payment count
        return [
            'day_number' => $paymentCount,
            'message' => $message,
            'message_type' => $messageType,
            'consecutive_days' => $reward->consecutive_days,
        ];
    }
}
