<?php

namespace Modules\StockIssues\Console\Methods;

use Illuminate\Database\Query\JoinClause;
use Illuminate\Support\Facades\DB;
use Modules\StockIssues\Models\VariationLocationDetails;

trait StockFixMethod
{
    /**
     * @return void
     */
    private function fix_stock($_this, &$products)
    {
        $mainQuery = VariationLocationDetails::query()
            ->select([
                'variation_location_details.id',
                'variation_location_details.product_id',
                'variation_location_details.variation_id',
                'variation_location_details.location_id',
                DB::raw("SUM(IF(transactions.type='purchase' AND transactions.status='received', purchase_lines.quantity, 0)) as total_purchase"),
                DB::raw("SUM(IF(transactions.type='purchase' OR transactions.type='purchase_return', purchase_lines.quantity_returned, 0)) as total_purchase_return"),
                DB::raw("SUM(purchase_lines.quantity_adjusted) as total_adjusted"),
                DB::raw("SUM(IF(transactions.type='opening_stock', purchase_lines.quantity, 0)) as total_opening_stock"),
                DB::raw("SUM(IF(transactions.type='purchase_transfer', purchase_lines.quantity, 0)) as total_purchase_transfer"),
                DB::raw('SUM(purchase_lines.quantity_sold) as `purchase_lines_sold`'),
                DB::raw('SUM(purchase_lines.quantity - (purchase_lines.quantity_sold + purchase_lines.quantity_adjusted + purchase_lines.quantity_returned + purchase_lines.mfg_quantity_used)) as `purchase_lines_stock`'),

                DB::raw('variation_location_details.qty_available as `variation_stock`')
            ])
            ->join('products', function (JoinClause $join) {
                $join->on('variation_location_details.product_id', '=', 'products.id');
            })
            ->join('purchase_lines', function (JoinClause $join) {
                $join->on('variation_location_details.variation_id', '=', 'purchase_lines.variation_id');
            })
            ->join('transactions', function (JoinClause $join) {
                $join->on(function ($q) {
                    $q->whereRaw('transactions.id = purchase_lines.transaction_id');
                })->whereRaw('transactions.location_id = variation_location_details.location_id');
            })
            ->groupBy([
                'purchase_lines.variation_id',
                'transactions.location_id',
            ])
            //->where('transactions.location_id', DB::raw('variation_location_details.location_id'))
            ->where('variation_location_details.qty_available', '>', 0)
            ->where('products.enable_stock', 1)
            ->having(
                DB::raw('SUM(purchase_lines.quantity - (purchase_lines.quantity_sold + purchase_lines.quantity_adjusted + purchase_lines.quantity_returned + purchase_lines.po_quantity_purchased + purchase_lines.mfg_quantity_used))'),
                '<>',
                DB::raw('variation_location_details.qty_available')
            );
        $variations = $mainQuery->get();
        $variations->groupBy('location_id')->each(function ($variations, $location_id) use (&$products) {
            $variations->each(function (VariationLocationDetails $details) use ($location_id, &$products) {
                $purchaseQuery = $details->purchase_lines()
                    ->leftJoin('transactions', 'transactions.id', 'transaction_id')
                    ->where(function ($query) {
                        $query->where(function ($query) {
                            $query->whereIn('transactions.status', ['received'])
                                ->whereIn('transactions.type', ['purchase', 'purchase_transfer', 'opening_stock', 'production_purchase']);
                        })->orWhere('transactions.type', 'purchase_return');
                    })
                    ->where('transactions.location_id', $details->location_id);
                $adjustQuery = $details->stock_adjust_lines()
                    ->leftJoin('transactions', 'transactions.id', 'transaction_id')
                    ->whereIn('transactions.type', ['stock_adjustment'])
                    ->where('transactions.location_id', $details->location_id);
                $sellQuery = $details->sell_lines()
                    ->leftJoin('transactions', 'transactions.id', 'transaction_id')
                    ->whereIn('transactions.status', ['final'])
                    ->where('transactions.location_id', $details->location_id);
                //todo split mfg from sold


                $PLStock = round(floatval((clone $purchaseQuery)->sum(DB::raw('quantity - (quantity_sold + quantity_adjusted + quantity_returned + po_quantity_purchased + mfg_quantity_used)'))), 4);
                $purchase = round(floatval((clone $purchaseQuery)->sum('quantity')), 4);
                $quantityAdjusted = round(floatval((clone $adjustQuery)->sum('quantity')), 4);
                $quantityReturned = round(floatval((clone $purchaseQuery)->sum('quantity_returned')), 4);
                $poQuantityPurchased = round(floatval((clone $purchaseQuery)->sum('po_quantity_purchased')), 4);

                $mfgQuantityUsed = round(floatval((clone $sellQuery)->whereIn('transactions.type', ['production_sell'])->sum('quantity')), 4);
                $realSold = round(floatval((clone $sellQuery)->whereIn('transactions.type', ['sell', 'sell_transfer'])->sum('quantity')), 4);
                $realSellReturned = round(floatval((clone $sellQuery)->whereIn('transactions.type', ['sell', 'sell_transfer', 'production_sell'])->sum('quantity_returned')), 4);
                $realSoldAfterReturn = round((($realSold + $mfgQuantityUsed) - $realSellReturned), 4);
                $realStock = round((
                    $purchase - ($realSoldAfterReturn + $quantityAdjusted + $quantityReturned + $poQuantityPurchased + $mfgQuantityUsed)
                ), 4);
                $variationStock = round($details->variation_stock, 4);
                if ($PLStock != $variationStock) {
                    if ($PLStock == $realStock) {
                        $products[] = [
                            $location_id,
                            $details->product_id,
                            $details->id,
                            $PLStock,
                            $variationStock,
                            $realStock,
                            "update VLD: {$realStock}"
                        ];
                        //$diff = round($realStock - $variationStock, 4);
                        $details->update(['qty_available' => $realStock]);
                    } /*elseif ($variationStock == $realStock) {
                        $diff = round($realStock - $PLStock, 4);
                    }*/
                }
            });
        });
    }
}
