<?php


namespace Modules\AdvancedTransfer\Entities\Services;


use App\Business;
use App\BusinessLocation;
use App\Exceptions\PurchaseSellMismatch;
use App\Product;
use App\PurchaseLine;
use App\Transaction;
use App\Variation;
use Illuminate\Support\Facades\DB;

class TransactionService
{

    /**
     * Retrieves all available lot numbers of a product from variation id
     *
     * @param  int $variation_id
     * @param  int $business_id
     * @param  int $location_id
     *
     * @return boolean
     */
    public function getLotDataFromVariation($variation_id, $business_id, $location_id, $exclude_empty_lot = false)
    {
        $query = PurchaseLine::join(
            'transactions as T',
            'purchase_lines.transaction_id',
            '=',
            'T.id'
        )
            ->where('T.business_id', $business_id)
            ->where('T.location_id', $location_id)
            // ->whereIn('T.type', ['purchase', 'purchase_transfer'])
            ->whereIn('T.type', ['purchase', 'purchase_transfer', 'opening_stock'])
            ->where('T.status', 'received')
            ->where('purchase_lines.variation_id', $variation_id);

        if ($exclude_empty_lot) {
            $query->whereRaw('(purchase_lines.quantity_sold + purchase_lines.quantity_adjusted + purchase_lines.quantity_returned) < purchase_lines.quantity');
        } else {
            $query->whereRaw('(purchase_lines.quantity_sold + purchase_lines.quantity_adjusted + purchase_lines.quantity_returned) <= purchase_lines.quantity');
        }

        $purchase_lines = $query->select(
            'purchase_lines.id as purchase_line_id',
            'lot_number',
            'purchase_lines.exp_date as exp_date',
            'purchase_lines.purchase_price',
            'purchase_lines.quantity',
            DB::raw('(purchase_lines.quantity - (purchase_lines.quantity_sold + purchase_lines.quantity_adjusted + purchase_lines.quantity_returned)) AS qty_available')
        )->get();
        return $purchase_lines;
    }

    /**
     * Batch version of getLotDataFromVariation().
     * Returns a Collection keyed by "variationId_locationId".
     * Each value is a Collection of lot rows.
     *
     * @param array $variationIds
     * @param int   $business_id
     * @param array $locationIds
     * @return \Illuminate\Support\Collection
     */
    public function getLotDataBatch(array $variationIds, int $business_id, array $locationIds): \Illuminate\Support\Collection
    {
        if (empty($variationIds) || empty($locationIds)) {
            return collect();
        }

        return PurchaseLine::join('transactions as T', 'purchase_lines.transaction_id', '=', 'T.id')
            ->where('T.business_id', $business_id)
            ->whereIn('T.location_id', $locationIds)
            ->whereIn('T.type', ['purchase', 'purchase_transfer', 'opening_stock'])
            ->where('T.status', 'received')
            ->whereIn('purchase_lines.variation_id', $variationIds)
            ->whereRaw('(purchase_lines.quantity_sold + purchase_lines.quantity_adjusted + purchase_lines.quantity_returned) < purchase_lines.quantity')
            ->select(
                'purchase_lines.id as purchase_line_id',
                'purchase_lines.variation_id',
                'T.location_id',
                'lot_number',
                'purchase_lines.exp_date',
                'purchase_lines.purchase_price',
                'purchase_lines.quantity',
                DB::raw('(purchase_lines.quantity - (purchase_lines.quantity_sold + purchase_lines.quantity_adjusted + purchase_lines.quantity_returned)) AS qty_available')
            )
            ->get()
            ->groupBy(fn($r) => $r->variation_id . '_' . $r->location_id);
    }

    /**
     * Retrieves purchase prices of a product depend on lot and expiry
     *
     * @param  int $quantity
     * @param  int $variation_id
     * @param  int $business_id
     * @param  int $location_id
     *
     * @return array
     */
    public function getPurchasePriceFromVariationByQuantity($quantity, $variation_id, $business_id, $location_id): array
    {
        $result = [];
        $modifiedQuantity = $quantity;
        $lotsData = $this->getLotDataFromVariation($variation_id, $business_id, $location_id, true);
        foreach($lotsData as $lotData){
            if($modifiedQuantity <= $lotData->qty_available){
                $result[] = [
                    'purchase_price' => $lotData->purchase_price,
                    'quantity' => $modifiedQuantity,
                ];
                break;
            }
            $result[] = [
                'purchase_price' => $lotData->purchase_price,
                'quantity' => $lotData->qty_available,
            ];
            $modifiedQuantity -= $lotData->qty_available;
        }

        return $result;
    }

    /**
     * Check the validity of the quantity from Location X
     *
     * @param $nstPrepareStock
     * @param $transferQuantity
     * @return bool
     * @throws PurchaseSellMismatch
     */
    public function checkQuantityMismatch($nstPrepareStock, $transferQuantity)
    {
        //Map sell lines with purchase lines
        $business = ['id' => $nstPrepareStock->business_id,
            'accounting_method' => request()->session()->get('business.accounting_method'),
            'location_id' => $nstPrepareStock->from_location_id
        ];

        $allow_overselling = !empty($business['pos_settings']['allow_overselling']) ? true : false;

        //Set flag to check for expired items during SELLING only.
        $stop_selling_expired = false;
        if (session()->has('business') && request()->session()->get('business')['enable_product_expiry'] == 1 && request()->session()->get('business')['on_product_expiry'] == 'stop_selling') {
            $stop_selling_expired = true;
        }

        //Check if stock is not enabled then no need to assign purchase & sell
        $product = Product::find($nstPrepareStock->product_id);
        if ($product->enable_stock != 1) {
            return true;
        }

        $qty_sum_query = $this->get_pl_quantity_sum_string('PL');

        //Get purchase lines, only for products with enable stock.
        $query = Transaction::join('purchase_lines AS PL', 'transactions.id', '=', 'PL.transaction_id')
            ->where('transactions.business_id', $nstPrepareStock->business_id)
            ->where('transactions.location_id', $nstPrepareStock->from_location_id)
            ->whereIn('transactions.type', ['purchase', 'purchase_transfer',
                'opening_stock', 'production_purchase'])
            ->where('transactions.status', 'received')
            ->whereRaw("( $qty_sum_query ) < PL.quantity")
            ->where('PL.product_id', $nstPrepareStock->product_id)
            ->where('PL.variation_id', $nstPrepareStock->variation_id);

        //If product expiry is enabled then check for on expiry conditions
        if ($stop_selling_expired && empty($nstPrepareStock->purchase_lines_id)) {
            $stop_before = request()->session()->get('business')['stop_selling_before'];
            $expiry_date = \Carbon::today()->addDays($stop_before)->toDateString();
            $query->where( function($q) use($expiry_date) {
                $q->whereNull('PL.exp_date')
                    ->orWhereRaw('PL.exp_date > ?', [$expiry_date]);
            });
        }

        //If lot number present consider only lot number purchase line
        if (!empty($nstPrepareStock->purchase_lines_id)) {
            $query->where('PL.id', $nstPrepareStock->purchase_lines_id);
        }

        //Sort according to LIFO or FIFO
        if ($business['accounting_method'] == 'lifo') {
            $query = $query->orderBy('transaction_date', 'desc');
        } else {
            $query = $query->orderBy('transaction_date', 'asc');
        }

        $rows = $query->select(
            'PL.id as purchase_lines_id',
            DB::raw("(PL.quantity - ( $qty_sum_query )) AS quantity_available"),
            'PL.quantity_sold as quantity_sold',
            'PL.quantity_adjusted as quantity_adjusted',
            'PL.quantity_returned as quantity_returned',
            'PL.mfg_quantity_used as mfg_quantity_used',
            'transactions.invoice_no'
        )
        ->lockForUpdate()
        ->get();

        $purchase_sell_map = [];

        //Iterate over the rows, assign the purchase line to sell lines.
        $qty_selling = $transferQuantity;
        foreach ($rows as $k => $row) {
            $qty_allocated = 0;

            //Check if qty_available is more or equal
            if ($qty_selling <= $row->quantity_available) {
                $qty_allocated = $qty_selling;
                $qty_selling = 0;
            } else {
                $qty_selling = $qty_selling - $row->quantity_available;
                $qty_allocated = $row->quantity_available;
            }
        }

        if (! ($qty_selling == 0 || is_null($qty_selling))) {
            //If overselling not allowed through exception else create mapping with blank purchase_line_id
            if (!$allow_overselling) {
                $variation = Variation::find($nstPrepareStock->variation_id);
                $mismatch_name = $product->name;
                if (!empty($variation->sub_sku)) {
                    $mismatch_name .= ' ' . 'SKU: ' . $variation->sub_sku;
                }
                if (!empty($qty_selling)) {
                    $mismatch_name .= ' ' . 'Quantity: ' . abs($qty_selling);
                }

                $mismatch_error = trans(
                    "messages.purchase_sell_mismatch_exception",
                    ['product' => $mismatch_name]
                );

                if ($stop_selling_expired) {
                    $mismatch_error .= __('lang_v1.available_stock_expired');
                }

                $business_name = optional(Business::find($business['id']))->name;
                $location_name = optional(BusinessLocation::find($business['location_id']))->name;
                \Log::emergency($mismatch_error . ' Business: ' . $business_name . ' Location: ' . $location_name);
                throw new PurchaseSellMismatch($mismatch_error);
            }
        }
        return true;
    }

    /**
     *
     * Generates string to calculate sum of purchase line quantity used
     */
    public function get_pl_quantity_sum_string($table_name = '')
    {
        $table_name = !empty($table_name) ? $table_name . '.' : '';
        $string = $table_name . "quantity_sold + " . $table_name . "quantity_adjusted + " . $table_name . "quantity_returned + " . $table_name . "mfg_quantity_used";

        return $string;
    }

}