<?php

namespace Modules\StockIssues\Console\Methods;

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

trait PurchaseLinesFixMethod
{
    /**
     * Create a new command instance.
     *
     * @return string
     */
    public function queryToSql($query)
    {
        return vsprintf(str_replace(array('?'), array('\'%s\''), $query->toSql()), $query->getBindings());
    }

    /**
     * @return void
     */
    private function purchase_fix($_this, &$products, &$notFixed)
    {
        $purchaseLinesQuery = DB::table('purchase_lines')
            ->selectRaw('SUM(purchase_lines.quantity_sold + purchase_lines.mfg_quantity_used)')
            ->leftJoin('transactions', function (JoinClause $join) {
                $join->on('transactions.id', 'purchase_lines.transaction_id');
            })
            ->whereColumn('variation_location_details.location_id', 'transactions.location_id')
            ->whereColumn('variation_location_details.variation_id', 'purchase_lines.variation_id');
        $sellLinesQuery = DB::table('transaction_sell_lines')
            ->selectRaw('SUM(transaction_sell_lines.quantity)')
            ->leftJoin('transactions', function (JoinClause $join) {
                $join->on('transactions.id', 'transaction_sell_lines.transaction_id');
            })
            ->whereColumn('variation_location_details.location_id', 'transactions.location_id')
            ->whereColumn('variation_location_details.variation_id', 'transaction_sell_lines.variation_id');
        $mainQuery = VariationLocationDetails::query()
            ->whereHas('product', function ($q) {
                $q->where('enable_stock', 1);
            })
            ->Where(
                DB::raw('(' . $this->queryToSql($purchaseLinesQuery) . ')'),
                '<>',
                DB::raw('(' . $this->queryToSql($sellLinesQuery) . ')')
            );
        $variations = $mainQuery->get();
        $table = [];
        $variations->each(function (VariationLocationDetails $details) use (&$table, &$products, &$notFixed) {
            $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);
            $sellQuery = $details->sell_lines()
                ->leftJoin('transactions', 'transactions.id', 'transaction_id')
                ->whereIn('transactions.type', ['sell', 'production_sell', 'sell_transfer'])
                ->whereIn('transactions.status', ['final'])
                ->where('transactions.location_id', $details->location_id);

            $purchaseSold = intval($purchaseQuery->sum('quantity_sold') * 10000) + intval($purchaseQuery->sum('mfg_quantity_used') * 10000);
            $realSold = intval($sellQuery->sum('quantity') * 10000);
            $realSellReturned = intval($sellQuery->sum('quantity_returned') * 10000);
            $realSoldAfterReturn = ($realSold - $realSellReturned);
            $sellDiff = abs($realSoldAfterReturn - $purchaseSold);
            $purchase = round(floatval($purchaseQuery->sum('quantity')), 4);

            if ($purchaseSold != $realSoldAfterReturn) {
                $table[] = [
                    $details->location_id,
                    $details->product_id,
                    $purchase,
                    $purchaseSold / 10000,
                    $realSoldAfterReturn / 10000,
                ];
                if ($purchaseSold < $realSoldAfterReturn) {
                    $lines = $purchaseQuery->where(
                        DB::raw('(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))'),
                        '>', 0
                    )->addSelect([
                        'purchase_lines.*',
                        Db::raw('quantity - (quantity_sold + quantity_adjusted + quantity_returned + mfg_quantity_used) as available')
                    ])->get();
                    $diff = $sellDiff / 10000;
                    if (intval($lines->sum('available') * 10000) >= $sellDiff) {
                        $sellDiff /= 10000;
                        $comments = [];
                        while ($sellDiff > 0) {
                            /** @var PurchaseLine $firstLine */
                            $firstLine = $lines->filter(fn($line) => (
                                    $line->quantity - ($line->quantity_sold + $line->quantity_adjusted + $line->quantity_returned + $line->mfg_quantity_used)
                                ) > 0)->first();
                            if ($firstLine) {
                                $qty = $firstLine->quantity - ($firstLine->quantity_sold + $firstLine->quantity_adjusted + $firstLine->quantity_returned + $firstLine->mfg_quantity_used);
                                $minQty = min($qty, $sellDiff);
                                $firstLine->increment('quantity_sold', $minQty);
                                $comments[] = "pl:{$firstLine->id}(+{$minQty})";
                                $sellDiff -= $minQty;
                            }
                        }
                        $products[] = [
                            $details->location_id,
                            $details->product_id,
                            $purchaseSold / 10000,
                            $realSoldAfterReturn / 10000,
                            $diff,
                            implode(' | ', $comments)
                        ];
                    } else
                        $notFixed[] = [
                            $details->location_id,
                            $details->product_id,
                            $details->product->name,
                            $details->product->sku,
                            $purchaseSold / 10000,
                            $realSoldAfterReturn / 10000,
                            "+$diff",
                            'overselling'
                        ];
                } elseif ($purchaseSold > $realSoldAfterReturn) {
                    $lines = $purchaseQuery->where('quantity_sold', '>', 0)->addSelect([
                        'purchase_lines.*'
                    ])->get();
                    $diff = $sellDiff / 10000;
                    if (intval($lines->sum('quantity_sold') * 10000) >= $sellDiff) {
                        $sellDiff /= 10000;
                        $comments = [];
                        while ($sellDiff > 0) {
                            /** @var PurchaseLine $lastLine */
                            $lastLine = $lines->filter(fn($line) => $line->quantity_sold > 0)->last();
                            $qty = floatval($lastLine->quantity_sold);
                            if ($lastLine) {
                                $minQty = min($qty, $sellDiff);
                                $lastLine->decrement('quantity_sold', $minQty);
                                $comments[] = "pl:{$lastLine->id}(-{$minQty})";
                                $sellDiff -= $minQty;
                            }
                        }
                        $products[] = [
                            $details->location_id,
                            $details->product_id,
                            $purchaseSold / 10000,
                            $realSoldAfterReturn / 10000,
                            $diff,
                            implode(' | ', $comments)
                        ];
                    } else
                        $notFixed[] = [
                            $details->location_id,
                            $details->product_id,
                            $details->product->name,
                            $details->product->sku,
                            $purchaseSold / 10000,
                            $realSoldAfterReturn / 10000,
                            "-$diff",
                            'real-sold gt pl-sold'
                        ];
                }
            }
        });

        /*$_this->table([
            'location id',
            'product id',
            'purchase',
            'purchaseSold',
            'realSoldAfterReturn',
        ], $table);*/
    }

}
