<?php

namespace App\Http\Controllers\Stocks;


use App\Models\Stock;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use App\Exports\Stocks\InventoryAndStockExport;
use App\Exports\StockReorderListExport;
use App\Exports\Stocks\SingleProductStockExport;
use Illuminate\Support\Facades\Auth;
use Maatwebsite\Excel\Facades\Excel;
use App\Models\Company;
use App\Models\Product;
use App\Models\VoucherType;

class InventoryAndStockController extends Controller
{
    
    protected  $mainFolderName = 'stocks';
     /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index(Request $request)
    {
        $data = Stock::select([
            'id',
            'entry_date',
            'product_id',
            'pack_qty_in',
            'loose_qty_in',
            'pack_qty_out',
            'loose_qty_out',
            'taxable_unit_price',
            'stock_type',
            'voucher_id',
            'voucher_type',
            'free_qty',
            'created_by_user_id'
        ])->with('getProduct','getCreatedBy')->with(['voucherSaleType','voucherPurchaseType','voucherReturnType','getMovementType']);

        if ($request->ajax()) {
            $sort_by      = $request->get('sortby') ?? 10;
            $sort_type    = $request->get('sorttype');
            $search_query = $request->get('query');

            $data = $data->when(!empty($search_query), function ($query) use ($search_query) {
                return $query->where('name', 'like', '%'.$search_query.'%');
            })
            ->orderBy('id','desc')
            ->paginate($sort_by);

            return view($this->mainFolderName .'.inventory-and-stocks.table', compact('data'));
        } else {
            $data = $data
                ->orderBy('id','desc')
                ->paginate(10);
            return view($this->mainFolderName .'.inventory-and-stocks.index',compact('data'));
        }
    }
    


    public function statement(Request $request)
    {
        $companyDateFormate = phpToJsDateFormat($this->companyDateFormate());

        if ($request->ajax()) {
            $from_date         = $request->from_date ? date('Y-m-d', strtotime($request->from_date)) : date('Y-m-d');
            $to_date           = $request->to_date ? date('Y-m-d', strtotime($request->to_date)) : date('Y-m-d');

            $invoices = $this->statementQuery($request);

            return view($this->mainFolderName .'.inventory-and-stocks.statement.table', compact([
                'invoices',
                'from_date',
                'to_date',
            ]));
        }

        $company       = Company::find(Auth::user()->company_id);
        $companies     = Company::get();
        $voucher_type  = VoucherType::where('code', 'STOCK')->where(['status' => 1])->first();
        $voucher_types = VoucherType::select('name', 'code')->where(['status' => 1])->get();

        return view($this->mainFolderName .'.inventory-and-stocks.statement.index', compact(
            'voucher_types',
            'companies',
            'company',
            'companyDateFormate',
            'voucher_type'
        ));
    }

    public function statementQuery($request)
    {
       
        $company        = $request->company ?? '';
        $search_product = $request->search_product ?? '';
       
        // $created_by  = $request->created_by ?? '';
        // \DB::enableQueryLog();
        $stocks = Stock::select([
                'stocks.id',
                'stocks.entry_date',
                'stocks.product_id',
                'stocks.batch_id',
                'stocks.pack_qty_in',
                'stocks.pack_qty_out',
                'stocks.taxable_unit_price',
                'stocks.stock_type',
                'stocks.voucher_id',
                'stocks.voucher_type',
                'products.id as product_id',
                'products.name as product_name',
                'primary_units.name as primary_unit',
                'product_prices.batch',
                'product_prices.exp_date',
                'products.item_min_stock_qty',
                \DB::raw("SUM(stocks.pack_qty_in) AS stock_in_count"),
                \DB::raw("SUM(stocks.pack_qty_out) AS stock_out_count")
            ])
            ->leftjoin('products','products.id','stocks.product_id')
            ->leftjoin('primary_units','products.primary_unit_id','primary_units.id')
            ->leftjoin('product_prices','products.id','product_prices.product_id')
            ->when($search_product != '', function($query) use($search_product) {
                    $query->where('stocks.product_id', $search_product);
            })
           
            ->when(!empty($company), function($invoices) use($company) {
                $invoices->where('stocks.company_id', '=', $company);
            })
            ->groupBy('stocks.product_id')
            ->orderBy('products.name','asc')
            ->get();
            // dd(\DB::getQueryLog());
            return $stocks;
    }

    public function query($request,$min_stock_wise = 0)
    {
        $from_date      = $request->from_date ? date('Y-m-d', strtotime($request->from_date)) : date('Y-m-d');
        $to_date        = $request->to_date ? date('Y-m-d', strtotime($request->to_date)) : date('Y-m-d');
        $company        = $request->company ?? '';
        $search_product = $request->search_product ?? '';
        $days           = (Int)$request->days ?? '';
        // $created_by  = $request->created_by ?? '';
        // \DB::enableQueryLog();
        $stocks = Stock::select([
                'stocks.id',
                'stocks.entry_date',
                'stocks.product_id',
                'stocks.batch_id',
                'stocks.pack_qty_in',
                'stocks.pack_qty_out',
                'stocks.taxable_unit_price',
                'stocks.stock_type',
                'stocks.voucher_id',
                'stocks.voucher_type',
                'products.id as product_id',
                'products.name as product_name',
                'primary_units.name as primary_unit',
                'product_prices.batch',
                'product_prices.exp_date',
                'products.item_min_stock_qty',
                \DB::raw("SUM(stocks.pack_qty_in) AS stock_in_count"),
                \DB::raw("SUM(stocks.pack_qty_out) AS stock_out_count")
            ])
            ->leftjoin('products','products.id','stocks.product_id')
            ->leftjoin('primary_units','products.primary_unit_id','primary_units.id')
            ->leftjoin('product_prices','products.id','product_prices.product_id')
            ->when($search_product != '', function($query) use($search_product) {
                    $query->where('products.id', $search_product);
            })
            // ->when(!empty($created_by), function($invoices) use($created_by) {
            //     $invoices->where('stocks.created_by_user_id', '=', $created_by);
            // })
            ->when(!empty($days) && $days > 0, function($invoices) use($days) {
                $invoices->whereBetween('product_prices.exp_date', [now(), now()->addDays($days)]);
            })
            ->when(!empty($company), function($invoices) use($company) {
                $invoices->where('stocks.company_id', '=', $company);
            })
            ->when($min_stock_wise == 1, function($invoices) use($company) {
                $invoices->havingRaw("SUM(stocks.pack_qty_in) - SUM(stocks.pack_qty_out) < products.item_min_stock_qty");
            })
            ->groupBy('stocks.product_id')
            ->orderBy('products.name','asc')
            ->get();
            // dd(\DB::getQueryLog());
            return $stocks;
    }

    public function statementExport(Request $request)
    {
        $companyDateFormate = phpToJsDateFormat($this->companyDateFormate());

        $from_date         = $request->from_date ? date('Y-m-d', strtotime($request->from_date)) : date('Y-m-d');
        $to_date           = $request->to_date ? date('Y-m-d', strtotime($request->to_date)) : date('Y-m-d');

        $invoices = $this->query($request);
        $invoices = $invoices->toArray();

        $fileName = 'Inventory-Stock-Statements'.date('Y-m-d').'.xlsx';

        return Excel::download(new InventoryAndStockExport($invoices), $fileName);
    }

    public function getCurrentStockAndPrimaryUnit(Request $request) {
        $productId = $request->productId;
        $stockData = Stock::select([
            'products.name as product_name',
            'primary_units.name as primary_unit',
            \DB::raw("SUM(stocks.pack_qty_in) AS stock_in_count"),
            \DB::raw("SUM(stocks.pack_qty_out) AS stock_out_count")])
        ->leftjoin('products','products.id','stocks.product_id')
        ->leftjoin('primary_units','products.primary_unit_id','primary_units.id')
        ->where('products.id', $productId)
        ->groupBy('stocks.product_id')
        ->orderBy('products.name','asc')
        ->first();
        $data['primary_unit']=$stockData->primary_unit??'';
        $data['current_stock']=isset($stockData->stock_in_count) && $stockData->stock_in_count!='' ? (($stockData->stock_in_count-$stockData->stock_out_count)>0?$stockData->stock_in_count-$stockData->stock_out_count:0):0;

        // dd($stockData);
        return response()->json([
            'success' => true,
            'message' => ['Deleted successfully'],
            'data'    => $data
        ]);
    }

    public function expiryStatement(Request $request)
    {
        $companyDateFormate = phpToJsDateFormat($this->companyDateFormate());

        if ($request->ajax()) {
            $from_date         = $request->from_date ? date('Y-m-d', strtotime($request->from_date)) : date('Y-m-d');
            $to_date           = $request->to_date ? date('Y-m-d', strtotime($request->to_date)) : date('Y-m-d');

            $invoices = $this->query($request);

            return view($this->mainFolderName .'.expiry-statement.table', compact([
                'invoices',
                'from_date',
                'to_date',
            ]));
        }

        $company       = Company::find(Auth::user()->company_id);
        $companies     = Company::get();
        $voucher_type  = VoucherType::where('code', 'STOCK')->where(['status' => 1])->first();
        $voucher_types = VoucherType::select('name', 'code')->where(['status' => 1])->get();

        return view($this->mainFolderName .'.expiry-statement.index', compact(
            'voucher_types',
            'companies',
            'company',
            'companyDateFormate',
            'voucher_type'
        ));
    }

    public function stockReorderList(Request $request)
    {
        $companyDateFormate = phpToJsDateFormat($this->companyDateFormate());

        if ($request->ajax()) {
            $min_stock_wise = 1;
            $invoices = $this->query($request, $min_stock_wise);

            return view($this->mainFolderName .'.inventory-and-stocks.reorder-statement.table', compact([
                'invoices'
            ]));
        }

        $min_stock_wise = 1;
        $invoices = $this->query($request, $min_stock_wise);

        $company       = Company::find(Auth::user()->company_id);
        $companies     = Company::get();
        $voucher_type  = VoucherType::where('code', 'STOCK')->where(['status' => 1])->first();
        $voucher_types = VoucherType::select('name', 'code')->where(['status' => 1])->get();

        return view($this->mainFolderName .'.inventory-and-stocks.reorder-statement.index', compact(
            'voucher_types',
            'companies',
            'company',
            'companyDateFormate',
            'invoices',
            'voucher_type'
        ));
    }

    public function stockReorderListExport(Request $request)
    {
        $min_stock_wise = 1;
        $invoices = $this->query($request, $min_stock_wise);
        $invoices = $invoices->toArray();

        $fileName = 'Inventory-Stock-Reorder-List'.date('Y-m-d').'.xlsx';

        return Excel::download(new StockReorderListExport($invoices), $fileName);
    }

    public function singleProductStatement(Request $request,$id)
    {
        $companyDateFormate = phpToJsDateFormat($this->companyDateFormate());
        if(isset($request->search_product) && $request->search_product!='')
        {
            $id = $request->search_product;
        }else{
            $id = $id;
        }
        $productData = Product::select('name')->find($id);
        $company       = Company::find(Auth::user()->company_id);
        $invoices = Stock::select(['*'])
        ->with('getProduct')
        ->with(['voucherSaleType','voucherPurchaseType','voucherReturnType','getCreatedBy','getMovementType'])
        ->where('product_id',$id)
        ->get();
        if ($request->ajax()) {
            $min_stock_wise = 1;
            // $invoices = $this->query($request, $min_stock_wise);
            return view($this->mainFolderName .'.inventory-and-stocks.single-product-statement.table', compact([
                'invoices',
                'productData',
                'company'
            ]));
        }

        $min_stock_wise = 1;
        // $invoices = $this->query($request, $min_stock_wise);

        
        $companies     = Company::get();
        $voucher_type  = VoucherType::where('code', 'STOCK')->where(['status' => 1])->first();
        $voucher_types = VoucherType::select('name', 'code')->where(['status' => 1])->get();

        return view($this->mainFolderName .'.inventory-and-stocks.single-product-statement.index', compact(
            'voucher_types',
            'companies',
            'company',
            'companyDateFormate',
            'invoices',
            'voucher_type',
            'id',
            'productData'
        ));
    }

    public function singleProductStatementExport(Request $request,$id='')
    {
        if(isset($request->search_product) && $request->search_product!='')
        {
            $ids = $request->search_product;
        }else{
            $ids = $id;
        }
        $invoices = Stock::select(['*'])
                    ->with('getProduct')
                    ->with(['voucherSaleType','voucherPurchaseType','voucherReturnType','getCreatedBy','getMovementType'])
                    ->where('product_id',$ids)
                    ->get();
        $invoices = $invoices->toArray();

        $fileName = 'Product-wise-Stock-List'.date('Y-m-d').'.xlsx';

        return Excel::download(new SingleProductStockExport($invoices), $fileName);
    }

    public function stockInStatement(Request $request)
    {
        $companyDateFormate = phpToJsDateFormat($this->companyDateFormate());

        if ($request->ajax()) {
            $from_date         = $request->from_date ? date('Y-m-d', strtotime($request->from_date)) : date('Y-m-d');
            $to_date           = $request->to_date ? date('Y-m-d', strtotime($request->to_date)) : date('Y-m-d');
            $search_product    = $request->search_product??'';

            $invoices = Stock::select(['*'])
                        ->with('getProduct')
                        ->with(['voucherSaleType','voucherPurchaseType','voucherReturnType','getCreatedBy','getMovementType'])
                        ->where('pack_qty_in','!=',0)
                        ->when(!empty($from_date) && !empty($to_date), function ($query) use ($from_date, $to_date) {
                            $query->whereDate('entry_date', '>=', $from_date)
                            ->whereDate('entry_date', '<=', $to_date);
                        })
                        ->when(!empty($search_product), function ($query) use ($search_product) {
                            $query->where('product_id', $search_product);
                        })
                        
                        ->get();
            return view($this->mainFolderName .'.inventory-and-stocks.in-statement.table', compact([
                'invoices',
                'from_date',
                'to_date',
            ]));
        }

        $company       = Company::find(Auth::user()->company_id);
        $companies     = Company::get();
        $voucher_type  = VoucherType::where('code', 'STOCK')->where(['status' => 1])->first();
        $voucher_types = VoucherType::select('name', 'code')->where(['status' => 1])->get();

        return view($this->mainFolderName .'.inventory-and-stocks.in-statement.index', compact(
            'voucher_types',
            'companies',
            'company',
            'companyDateFormate',
            'voucher_type'
        ));
    }

    public function stockOutStatement(Request $request)
    {
        $companyDateFormate = phpToJsDateFormat($this->companyDateFormate());

        if ($request->ajax()) {
            $from_date         = $request->from_date ? date('Y-m-d', strtotime($request->from_date)) : date('Y-m-d');
            $to_date           = $request->to_date ? date('Y-m-d', strtotime($request->to_date)) : date('Y-m-d');
            $search_product    = $request->search_product??'';
            $invoices = Stock::select(['*'])
                        ->with('getProduct')
                        ->with(['voucherSaleType','voucherPurchaseType','voucherReturnType','getCreatedBy','getMovementType'])
                        ->where('pack_qty_out','!=',0)
                        ->when(!empty($from_date) && !empty($to_date), function ($query) use ($from_date, $to_date) {
                            $query->whereDate('entry_date', '>=', $from_date)
                            ->whereDate('entry_date', '<=', $to_date);
                        })
                        ->when(!empty($search_product), function ($query) use ($search_product) {
                            $query->where('product_id', $search_product);
                        })
                        
                        ->get();
            return view($this->mainFolderName .'.inventory-and-stocks.out-statement.table', compact([
                'invoices',
                'from_date',
                'to_date',
            ]));
        }

        $company       = Company::find(Auth::user()->company_id);
        $companies     = Company::get();
        $voucher_type  = VoucherType::where('code', 'STOCK')->where(['status' => 1])->first();
        $voucher_types = VoucherType::select('name', 'code')->where(['status' => 1])->get();

        return view($this->mainFolderName .'.inventory-and-stocks.out-statement.index', compact(
            'voucher_types',
            'companies',
            'company',
            'companyDateFormate',
            'voucher_type'
        ));
    }
}
