<?php

namespace App\Http\Controllers\Admin\Purchase;

use App\Http\Controllers\Controller;
use App\Models\Bank;
use App\Models\Client;
use App\Models\Expance;
use App\Models\MobileBanking;
use App\Models\productAttribute;
use App\Models\Purchase;
use App\Models\purchase_maker_masters;
use App\Models\PurchaseDetails;
use App\Models\PurchaseMaker;
use App\Models\PurchaseMaster;
use App\Models\PurchasePayment;
use App\Models\Product;
use App\Models\PurchaseReturnMaster;
use App\Models\SalesMaster;
use App\Models\Setting;
use App\Models\Shop;
use App\Models\Supplier;
use App\Models\Warehouse;
use App\Models\WarehouseStock;
use App\Service\Purchase\PurchaseService;
use App\Traits\HasUsageCheck;
use Carbon\Carbon;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Inertia\Inertia;

class PurchaseController extends Controller
{
    use HasUsageCheck;

    public function index()
    {
        return Inertia::render('Purchase/Purchase');
    }

    public function getData()
    {
        return PurchaseMaster::orderByDesc('id')->with('warehouse', 'PurchaseDetalis', 'supplier', 'PurchaseMaker', 'lcModule', 'purchasePayment')->limit(10)->get();
    }

    public function view(Request $request)
    {
        // Check if this is a pagination request
        $isPaginationRequest = $request->has('page') || $request->has('rows') || $request->has('search');

        // Invoice No
        $purchase_inovice_no = PurchaseMaster::withoutGlobalScope('typeScope')->orderBy('id', 'DESC')->first();
        $invoice_no = invoice_no($purchase_inovice_no?->id ?? false, 'P');

        // Purchase Maker List
        $purchase_maker = purchase_maker_masters::where('status', '0')->get();
        $supplier = Supplier::get();
        // WareHouse
        $warehouse = Warehouse::get();
        // warehouse Show or hide setting
        $setting = Setting::first();
        // Shop information
        $shop = Shop::get();
        $info = auth()->user()->shop_id ? Shop::where('id', auth()->user()->shop_id)->first() : admin_info();

        if ($isPaginationRequest) {
            // Handle pagination request
            $page = $request->get('page', 1);
            $perPage = $request->get('perPage', 10);
            $search = $request->get('search', '');

            $query = PurchaseMaster::with('warehouse', 'PurchaseDetalis', 'supplier', 'PurchaseMaker', 'lcModule', 'purchasePayment')
                ->orderByDesc('id');

            // Apply search filter
            if (! empty($search)) {
                $query->where(function ($q) use ($search) {
                    $q->where('invoice_no', 'LIKE', "%{$search}%")
                        ->orWhere('po_ref', 'LIKE', "%{$search}%")
                        ->orWhereHas('supplier', function ($subQuery) use ($search) {
                            $subQuery->where('name', 'LIKE', "%{$search}%");
                        });
                });
            }

            // Get paginated results
            $purchases = $query->paginate($perPage, ['*'], 'page', $page);

            return response()->json([
                'purchases' => $purchases,
            ]);
        } else {
            // Handle regular request - return all data for initial load
            $purchases = $this->getData();

            return response()->json([
                'invoice_no' => $invoice_no,
                'purchase_maker' => $purchase_maker,
                'supplier' => $supplier,
                'warehouse' => $warehouse,
                'purchases' => $purchases,
                'setting' => $setting,
                'shop' => $shop,
                'info' => $info,
            ]);
        }
    }

    // get Purhchase Maker list
    public function maker_list($id = null)
    {
        $maker_list = PurchaseMaker::where('purchase_maker_masters_id', $id)->get();
        $purchase = purchase_maker_masters::where('id', $id)->select('supplier_id')->first();

        return response()->json(['maker_list' => $maker_list, 'supplier' => $purchase]);
    }

    /**
     * Show the form for creating a new resource.
     */
    public function create()
    {
        //
    }

    /**
     * Store a newly created resource in storage.
     */
    public function store(Request $request)
    {

        PurchaseService::PurchaseStore(request()->all());
        // Invoice No
        $purchase_inovice = PurchaseMaster::orderBy('id', 'DESC')->first();
        $invoice_no = invoice_no($purchase_inovice?->id ?? false, 'P');

        // Purchase Maker List
        $purchase_maker = purchase_maker_masters::where('status', '0')->get();

        return response()->json(['success' => 'Purchase insert Successfully ', 'invoice_no' => $invoice_no, 'purchase_maker' => $purchase_maker, 'purchase_inovice' => $purchase_inovice]);
    }

    /**
     * Display the specified resource.
     */
    public function show(string $id)
    {
        //
    }

    /**
     * Show the form for editing the specified resource.
     */
    public function edit(string $id)
    {
        $details = PurchaseDetails::select(
            'purchase_master_id',
            'product_id',
            'attribute_id',

            DB::raw('GROUP_CONCAT(batch_no ORDER BY id SEPARATOR ",") AS batch_no'),
            DB::raw('GROUP_CONCAT(base_quantity ORDER BY id SEPARATOR ",") AS base_quantity'),

            DB::raw('SUM(quantity) AS quantity'),
            DB::raw('SUM(unit_price_total) AS unit_price_total'),

            DB::raw('MIN(unit_price) AS unit_price'),
            DB::raw('MIN(tp_price) AS tp_price'),
            DB::raw('MIN(mrp_price) AS mrp_price'),
            DB::raw('MIN(vat) AS vat'),
            DB::raw('MIN(tax) AS tax'),
            DB::raw('MIN(discount) AS discount'),
            DB::raw('MIN(pack_qty) AS pack_qty'),
            DB::raw('MIN(pack_price) AS pack_price'),
            DB::raw('MIN(gift_qty) AS gift_qty'),
            DB::raw('MIN(conversion_input) AS conversion_input'),

            DB::raw('GROUP_CONCAT(mfg_date ORDER BY id SEPARATOR ",") AS mfg_date'),
            DB::raw('GROUP_CONCAT(exp_date ORDER BY id SEPARATOR ",") AS exp_date')
        )
            ->where('purchase_master_id', $id)
            ->groupBy('purchase_master_id', 'product_id', 'attribute_id')
            ->with('attribute')
            ->get();

        return response()->json(['purchase_detalis' => $details]);
    }

    /**
     * Update the specified resource in storage.
     */
    public function update(Request $request, string $id)
    {
        PurchaseService::PurchaseUpdate(request()->all(), $id);

        // Invoice No
        $purchase_inovice_no = PurchaseMaster::withoutGlobalScope('typeScope')->orderBy('id', 'DESC')->first();
        $invoice_no = invoice_no($purchase_inovice_no?->id ?? false, 'P');

        //  Purchase

        // Purchase Maker List
        $purchase_maker = purchase_maker_masters::where('status', '0')->get();

        return response()->json(['success' => 'Purchase Update Successfully ', 'invoice_no' => $invoice_no, 'purchase_maker' => $purchase_maker]);
    }

    /**
     * Remove the specified resource from storage.
     */
    public function destroy(string $id)
    {
        if ($this->PurchaseCheck($id)) {
            return response()->json(['error' => 'This Purchase already used '], 422);
        }
        // Find the purchase with details
        $purchase = PurchaseMaster::with('PurchaseDetalis')->findOrFail($id);

        // foreach ($purchase->PurchaseDetalis as $detail) {
        //     // Adjust stock here
        //     WarehouseStock::where('product_id', $detail->product_id)
        //         ->where('attribute_id', $detail->attribute_id)
        //         ->where('warehouse_id', $purchase->warehouse_id)
        //         ->decrement('quantity', $detail->quantity + (int) ($detail->gift_qty ?? 0));
        // }
        // Delete the purchase payment
        $purchase->purchasePayment()->delete();

        // First delete the details
        $purchase->PurchaseDetalis()->delete();
        $purchase->delete();

        // Invoice No
        $purchase_inovice_no = PurchaseMaster::withoutGlobalScope('typeScope')->orderBy('id', 'DESC')->first();
        $invoice_no = invoice_no($purchase_inovice_no?->id ?? false, 'P');

        return response()->json(['success' => 'Purchase Delete Successfully ', 'invoice_no' => $invoice_no]);
    }

    public function purchaseProductSearch()
    {
        $search = trim(request()->data);
        $category_id = request()->category_id;
        $supplier_id = request()->supplier_id;
        $warehouse_id = request()->form_warehouse_id ?? 1;

        // Determine permission-based default type filter using Spatie permissions
        $canRow = auth()->user()->can('product_for_row_material');
        $canFinish = auth()->user()->can('product_for_finish_product');
        $canSupplierWiseProductView = auth()->user()->can('supplier_wise_product_view');

        $query = productAttribute::query()

            // JOIN products + brands
            ->leftJoin('products', 'products.id', '=', 'product_attributes.product_id')
            ->leftJoin('brands', 'brands.id', '=', 'product_attributes.brand_id')

            // Only active products (join bypasses Product global scope)
            ->where('products.status', Product::STATUS_ACTIVE)

            // CATEGORY filter
            ->when($category_id, function ($q) use ($category_id) {
                $q->where('products.category_id', $category_id);
            })
            ->when($supplier_id && $canSupplierWiseProductView, function ($q) use ($supplier_id) {
                $q->where('product_attributes.supplier_id', $supplier_id);
            })
            ->when($canRow && ! $canFinish, function ($query) {
                $query->where('products.type', 'row_product');
            })
            ->when($canFinish && ! $canRow, function ($query) {
                $query->where('products.type', 'finish_product');
            })

            // MAIN SEARCH (Following POS search pattern)
            ->when($search !== '', function ($q) use ($search) {

                $lower = strtolower($search);

                $q->where(function ($q2) use ($lower, $search) {

                    // Match attribute fields
                    $q2->whereRaw('product_attributes.barcode LIKE ?', ["{$lower}%"])
                        ->orWhereRaw('product_attributes.sku LIKE ?', ["{$lower}%"])

                        // Match product name
                        ->orWhereRaw('LOWER(products.name) = ?', [$lower])
                        ->orWhereRaw('LOWER(products.name) LIKE ?', ["{$lower}%"])
                        ->orWhereRaw('LOWER(products.name) LIKE ?', ["%{$lower}%"])
                        ->orWhereRaw('SOUNDEX(products.name) = SOUNDEX(?)', [$search])

                        // Fulltext (only works if index exists)
                        ->orWhereRaw('MATCH(products.name) AGAINST(? IN NATURAL LANGUAGE MODE)', [$search]);
                });
            })

            // Relevance-based sorting (Following POS pattern)
            ->when($search !== '', function ($q) use ($search) {
                $q->orderByRaw('
                CASE
                    WHEN LOWER(products.name) = ? THEN 1
                    WHEN LOWER(products.name) LIKE ? THEN 2
                    WHEN LOWER(products.name) LIKE ? THEN 3
                    WHEN SOUNDEX(products.name) = SOUNDEX(?) THEN 4
                    ELSE 5
                END ASC
            ', [
                    strtolower($search),
                    strtolower($search).'%',
                    '%'.strtolower($search).'%',
                    $search,
                ]);
            })

            // MUST SELECT product_attributes.* after join
            ->select('product_attributes.*')

            ->limit($search ? 25 : 40)

            // RELATIONS (for JSON output)
            ->with([
                'product',
                'brand',
                'size',
                'color',
                'unit',
                'model',
                'goldUnit',
                'productType',
                'diamondUnit',

                'warehouse' => function ($q) use ($warehouse_id) {
                    $q->where('warehouse_id', $warehouse_id);
                },

                'batch' => function ($q) use ($warehouse_id) {
                    $q->where('warehouse_id', $warehouse_id)
                        ->select('id', 'attribute_id', 'batch_no', 'quantity');
                },
            ]);

        return response()->json(['produts' => $query->get()]);
    }

    public function purchaseProductSearchBarcode()
    {
        $search_element = request()->data;
        $supplier_id = request()->supplier_id;
        $category_id = request()->category_id;

        if (strlen($search_element) != 0) {
            $products = productAttribute::whereHas('product')
                ->where(function ($query) use ($search_element, $category_id) {
                    $query->where('barcode', $search_element)
                        ->orWhere('sku', $search_element)
                        ->orWhereHas('product', function ($query) use ($search_element, $category_id) {
                            $query->where('name', 'LIKE', '%'.$search_element.'%')
                                ->when($category_id, function ($query) use ($category_id) {
                                    $query->where('category_id', $category_id);
                                });
                        });
                })
                ->when($supplier_id, function ($query) use ($supplier_id) {
                    $query->where('supplier_id', $supplier_id);
                })
                ->with(['brand', 'size', 'color', 'product', 'unit', 'warehouse'])
                ->limit(20)
                ->get();
        } else {
            $products = productAttribute::whereHas('product')
                ->with('brand', 'size', 'color', 'product', 'unit', 'warehouse', 'shopWarehouse')
                ->when($category_id, function ($query) use ($category_id) {
                    $query->whereHas('product', function ($query) use ($category_id) {
                        $query->where('category_id', $category_id);
                    });
                })
                ->when($supplier_id, function ($query) use ($supplier_id) {
                    $query->where('supplier_id', $supplier_id);
                })
                ->limit(30)
                ->get();
        }

        return response()->json(['produts' => $products]);
    }

    public function clientSearch()
    {

        $client = Client::when(request()->filled('data'), function ($q) {
            $q->where(function ($sub) {
                $search = request()->data;
                $sub->where('name', 'LIKE', "%{$search}%")
                    ->orWhere('number', 'LIKE', "%{$search}%");
            });
        }, function ($q) {
            $q->latest()->limit(10);
        })
            ->get();

        return response()->json($client);
    }

    public function invoiceSearch()
    {
        // Load sales invoice with line details, sales returns, and customer (for auto-select client on return)
        $query = SalesMaster::with(['details', 'salesReturn', 'customer'])
            ->where('location_id', auth()->user()->shop_id);

        // Filter by customer if provided (client-wise invoices); otherwise search all invoices by invoice no
        if (request()->filled('customer_id')) {
            $query->where('client_id', request()->customer_id);
        }

        // Support search on the middle section of invoice_no (e.g. PIVO-0002-2026 → "0002")
        if (request()->filled('data')) {
            $search = request()->data;
            // Extract the middle part between first and second '-' and search there
            $query->whereRaw(
                "SUBSTRING_INDEX(SUBSTRING_INDEX(invoice_no, '-', 2), '-', -1) LIKE ?",
                ['%'.$search.'%']
            );
        }

        $invoices = $query->orderByDesc('id')->limit(20)->get();

        return response()->json($invoices);
    }

    public function purchaseReturn()
    {
        return Inertia::render('Purchase/PurchaseReturn');
    }

    public function purchaseView()
    {
        $type = request()->get('type', 'regular'); // Get type from request, default to 'regular'

        // Reference No
        $purchaseReturn = PurchaseReturnMaster::where('type', $type)->orderby('reference_no', 'DESC')->first();
        $reference = invoice_no($purchaseReturn?->id ?? false, $type === 'direct' ? 'DPR' : 'PR');
        $supplier = Supplier::limit('20')->get();

        $purchaseReturn = PurchaseReturnMaster::where('shop_id', auth()->user()->shop_id)
            ->where('type', $type)
            ->with('supplier', 'invoice', 'details', 'returnProduct', 'purchaseDetails', 'purchaseDetail')
            ->orderby('id', 'DESC')
            ->get();

        return response()->json(['reference' => $reference, 'supplier' => $supplier, 'purchaseReturn' => $purchaseReturn, 'type' => $type]);
    }

    public function purchaseInvoiceSearch()
    {
        $invoice = PurchaseMaster::where('invoice_no', request()->data)->with('PurchaseDetalis')->where('shop_id', auth()->user()->shop_id)->limit(1)->get();

        return response()->json($invoice);
    }

    public function purchaseReturnStore()
    {
        PurchaseService::purchaseReturn(request()->all());
        $purchaseReturn = PurchaseReturnMaster::where('type', 'regular')->orderby('id', 'DESC')->first();
        $reference = invoice_no($purchaseReturn?->id ?? false, 'PR');
        $purchaseReturn = PurchaseReturnMaster::with('supplier', 'invoice', 'details', 'returnProduct', 'purchaseDetail')
            ->where('shop_id', auth()->user()->shop_id)
            ->where('type', 'regular')
            ->orderby('id', 'DESC')
            ->get();

        return response()->json(['success' => 'Return Successfully', 'reference' => $reference, 'purchaseReturn' => $purchaseReturn]);
    }

    public function purchaseReturnUpdate($id)
    {
        PurchaseService::purchaseReturnUpdate(request()->all(), $id);
        $purchaseReturn = PurchaseReturnMaster::where('type', 'regular')->orderby('id', 'DESC')->first();
        $reference = invoice_no($purchaseReturn?->id ?? false, 'PR');
        $purchaseReturn = PurchaseReturnMaster::with('supplier', 'invoice', 'details', 'returnProduct', 'purchaseDetail')
            ->where('shop_id', auth()->user()->shop_id)
            ->where('type', 'regular')
            ->orderby('id', 'DESC')
            ->get();

        return response()->json(['success' => 'Return Update Successfully', 'purchaseReturn' => $purchaseReturn, 'reference' => $reference]);
    }

    public function directPurchaseReturn()
    {
        return Inertia::render('Purchase/DirectPurchaseReturn');
    }

    public function DirectPurchaseReturnView()
    {
        $purchaseReturn = PurchaseReturnMaster::withoutGlobalScope('typeScope')
            ->where('type', 'direct')
            ->orderby('reference_no', 'DESC')
            ->first();
        $reference = invoice_no($purchaseReturn?->id ?? false, 'DPR');

        $suppliers = Supplier::get();
        $purchaseReturn = PurchaseReturnMaster::withoutGlobalScope('typeScope')
            ->where('type', 'direct')
            ->where('shop_id', auth()->user()->shop_id)
            ->with(['supplier', 'details' => function ($query) {
                $query->with('product', 'attribute.brand', 'attribute.color', 'attribute.size');
            }])
            ->orderby('id', 'DESC')
            ->get();

        return response()->json(['reference' => $reference, 'purchaseReturn' => $purchaseReturn, 'suppliers' => $suppliers]);
    }

    public function DirectPurchaseReturnStore()
    {
        PurchaseService::DirectPurchaseReturn(request()->all());
        $purchaseReturn = PurchaseReturnMaster::withoutGlobalScope('typeScope')
            ->where('type', 'direct')
            ->orderby('reference_no', 'DESC')
            ->first();
        $reference = invoice_no($purchaseReturn?->id ?? false, 'DPR');

        $purchaseReturn = PurchaseReturnMaster::withoutGlobalScope('typeScope')
            ->where('type', 'direct')
            ->where('shop_id', auth()->user()->shop_id)
            ->with(['supplier', 'details' => function ($query) {
                $query->with('product', 'attribute.brand', 'attribute.color', 'attribute.size');
            }])
            ->orderby('id', 'DESC')
            ->get();

        return response()->json(['reference' => $reference, 'purchaseReturn' => $purchaseReturn]);
    }

    public function DirectPurchaseReturnUpdate($id)
    {
        PurchaseService::DirectPurchaseReturnUpdate(request()->all(), $id);

        $purchaseReturn = PurchaseReturnMaster::withoutGlobalScope('typeScope')
            ->where('type', 'direct')
            ->orderby('reference_no', 'DESC')
            ->first();
        $reference = invoice_no($purchaseReturn?->id ?? false, 'DPR');

        $purchaseReturn = PurchaseReturnMaster::withoutGlobalScope('typeScope')
            ->where('type', 'direct')
            ->where('shop_id', auth()->user()->shop_id)
            ->with(['supplier', 'details' => function ($query) {
                $query->with('product', 'attribute.brand', 'attribute.color', 'attribute.size');
            }])
            ->orderby('id', 'DESC')
            ->get();

        return response()->json(['reference' => $reference, 'purchaseReturn' => $purchaseReturn]);
    }

    public function expenseReport()
    {
        return Inertia::render('Report/ExpenseReport');
    }

    public function expenseReportSearch()
    {

        // Remove the timezone information from the string
        if (request()->start != 'null') {
            $dateStringWithoutTimezone = substr(request()->start, 0, strpos(request()->start, 'GMT'));
            // Parse the date string without timezone to a Carbon instance
            $start = Carbon::parse($dateStringWithoutTimezone)->subDay();
        } else {
            $start = null;
        }
        if (request()->end !== 'null' && request()->end !== 'undefined' && ! empty(request()->end)) {

            $dateStringWithoutTimezone = substr(request()->end, 0, strpos(request()->end, 'GMT'));
            // Parse the date string without timezone to a Carbon instance
            $end = Carbon::parse($dateStringWithoutTimezone);
        } else {
            $end = false;
        }

        if ($end) {
            $expense = Expance::selectRaw('date as date, shop_id')
                ->groupBy('date', 'shop_id')

                ->whereBetween('date', [$start, $end])
                ->orderBY('date', 'ASC')
                ->get()
                ->map(function ($expense) {
                    $expense->getlist = Expance::whereDateIs($expense->date)->with('catagory')->get();

                    return $expense;
                });
        } else {
            $expense = Expance::selectRaw('date as date,shop_id')
                ->groupBy('date', 'shop_id')

                ->orderBY('date', 'ASC')
                ->get()
                ->map(function ($expense) {
                    $expense->getlist = Expance::whereDateIs($expense->date)->with('catagory')->get();

                    return $expense;
                });
        }

        return response()->json($expense);
    }

    public function purchasePaymentView()
    {
        $bank = Bank::get();
        $mobileBank = MobileBanking::get();

        return Inertia::render('Purchase/PurchasePayment', compact('bank', 'mobileBank'));
    }

    public function supplierSearchDue()
    {
        $suppliers = Supplier::where('name', 'LIKE', '%'.request()->data.'%')->orwhere('number', request()->data)->with('purchase', 'payment', 'returnTotal')->limit(30)->get();

        return response()->json(['suppliers' => $suppliers]);
    }

    public function supplierDuePayment()
    {

        PurchaseService::purchasePayment(request()->all());
        $payment = $this->allData();

        return response()->json(['success' => 'Data insert successfully', 'payment' => $payment]);
    }

    public function supplierDuePaymentUpdate($id)
    {
        PurchaseService::purchasePaymentUpdate(request()->all(), $id);
        $payment = $this->allData();

        return response()->json(['success' => 'Data insert successfully', 'payment' => $payment]);
    }

    public function allData()
    {
        return PurchasePayment::selectRaw('
        DATE(date) as date,
        payment,
        supplier_id,
        payment_id,
        id,
        order_no,
        payment_mode,
        payment_type,
        collection_reference,
        note,

        -- Total purchases minus returns
        COALESCE(
            (SELECT SUM(total)
             FROM purchase_masters
             WHERE supplier_id = purchase_payments.supplier_id),
            0
        ) - COALESCE(
            (SELECT SUM(total)
             FROM purchase_return_masters
             WHERE supplier_id = purchase_payments.supplier_id),
            0
        ) as total_sales,

        -- Opening balance from supplier table
        COALESCE(
            (SELECT opening_balance
             FROM suppliers 
             WHERE suppliers.id = purchase_payments.supplier_id
             LIMIT 1),
            0
        ) as opening,

        -- Previous payments (excluding current)
        COALESCE(
            (SELECT SUM(payment)
             FROM purchase_payments AS previous_payments
             WHERE previous_payments.supplier_id = purchase_payments.supplier_id
             AND previous_payments.id < purchase_payments.id),
            0
        ) as prev_payment
    ')
            ->with('supplier')
            ->latest()
            ->get();
    }

    public function purchasePaymentList()
    {

        $payment = $this->allData();

        return response()->json($payment);
    }

    public function purchasePaymentDelete(): JsonResponse
    {

        // // Deleting the record based on the provided request ID
        PurchasePayment::where('id', request()->id)->delete();

        // Fetching aggregated data
        $payment = $this->allData();

        // Returning the fetched data as a JSON response
        return response()->json(['success' => 'Data Delete Successfully ', 'payment' => $payment]);
    }
}
