<?php

namespace App\Utils;
use Illuminate\Support\Facades\DB;

use App\Product,
    App\Variation,
    App\TaxRate,
    App\ProductVariation,
    App\Business,
    App\Transaction,
    App\VariationLocationDetails,
    App\ProductRack;

class ProductUtil extends Util
{

	/**
     * Returns the list of barcode types
     * 
     * @return array
     */
    public function barcode_types()
    {
        $types = array( 'C128' => 'Code 128 (C128)', 'C39' => 'Code 39 (C39)', 'EAN13' => 'EAN-13', 'EAN8' => 'EAN-8', 'UPCA' => 'UPC-A', 'UPCE' => 'UPC-E');

        return $types;
    }

    /**
     * Returns the default barcode.
     * 
     * @return string
     */
    public function barcode_default()
    {
        return 'C128';
    }

    /**
     * Create single type product variation
     * 
     * @param (int or object) $product
     * @param $sku
     * @param $purchase_price
     * @param $dpp_inc_tax (default purchase pric including tax)
     * @param $profit_percent
     * @param $selling_price
     *
     * @return boolean
     */
    public function createSingleProductVariation($product, $sku, $purchase_price, $dpp_inc_tax, $profit_percent, $selling_price, $selling_price_inc_tax)
    {
    	if(!is_object($product)){
            $product = Product::find($product);
        }

    	//create product variations
        $product_variation_data = array(
                                    'name' => 'DUMMY',
                                    'is_dummy' => 1
                                );
        $product_variation = $product->product_variations()->create($product_variation_data);
                
        //create variations
        $variation_data = array(
                'name' => 'DUMMY',
                'product_id' => $product->id,
                'sub_sku' => $sku,
                'default_purchase_price' => $this->num_uf($purchase_price),
                'dpp_inc_tax' => $this->num_uf($dpp_inc_tax),
                'profit_percent' => $this->num_uf($profit_percent),
                'default_sell_price' => $this->num_uf($selling_price),
                'sell_price_inc_tax' => $this->num_uf($selling_price_inc_tax)
            );
        $product_variation->variations()->create($variation_data);

        return true;
    }

    /**
     * Create variable type product variation
     * 
     * @param (int or object) $product
     * @param $input_variations
     *
     * @return boolean
     */
    public function createVariableProductVariations($product, $input_variations)
    {
    	if(!is_object($product)){
            $product = Product::find($product);
        }

    	//create product variations
	    foreach( $input_variations as $key => $value){
            if(empty($value['name'])){
                continue;
            }
	        $product_variation_data = array(
	                                    'name' => $value['name'],
	                                    'product_id' => $product->id,
	                                    'is_dummy' => 0
	                                );
	        $product_variation = ProductVariation::create($product_variation_data);
	        
	        //create variations
	        if(!empty($value['variations'])){
                $variation_data = array();

                $c = Variation::withTrashed()
                        ->where('product_id', $product->id)
                        ->count() + 1;
                
	            foreach($value['variations'] as $k => $v){
                    $sub_sku = empty($v['sub_sku'])? $this->generateSubSku($product->sku, $c, $product->barcode_type) :$v['sub_sku'];
	                $variation_data[] = array(
                      'name' => $v['value'],
                      'product_id' => $product->id,
                      'sub_sku' => $sub_sku,
                      'default_purchase_price' => $this->num_uf($v['default_purchase_price']),
                      'dpp_inc_tax' => $this->num_uf($v['dpp_inc_tax']),
                      'profit_percent' => $this->num_uf($v['profit_percent']),
                      'default_sell_price' => $this->num_uf($v['default_sell_price']),
                      'sell_price_inc_tax' => $this->num_uf($v['sell_price_inc_tax'])
                    );
	                $c++;
	            }
	            $product_variation->variations()->createMany($variation_data);
	        }
	        
	    }
	}

    /**
     * Update variable type product variation
     * 
     * @param $product_id
     * @param $input_variations_edit
     *
     * @return boolean
     */
    public function updateVariableProductVariations($product_id, $input_variations_edit)
    {
        $product = Product::find($product_id);

        //Update product variations
        $product_variation_ids = [];
        foreach( $input_variations_edit as $key => $value){

            $product_variation_ids[] = $key;

            $product_variation = ProductVariation::find($key);
            $product_variation->name = $value['name'];
            $product_variation->save();

            //Update existing variations
            $variations_ids = [];
            if(!empty($value['variations_edit'])){
                foreach($value['variations_edit'] as $k => $v){
                    $data = array(
                        'name' => $v['value'],
                        'default_purchase_price' => $this->num_uf($v['default_purchase_price']),
                        'dpp_inc_tax' => $this->num_uf($v['dpp_inc_tax']),
                        'profit_percent' => $this->num_uf($v['profit_percent']),
                        'default_sell_price' => $this->num_uf($v['default_sell_price']),
                        'sell_price_inc_tax' => $this->num_uf($v['sell_price_inc_tax'])
                    );
                    if(!empty($v['sub_sku'])){
                        $data['sub_sku'] = $v['sub_sku'];
                    }
                    Variation::where('id', $k)
                        ->where('product_variation_id', $key)
                        ->update($data);

                    $variations_ids[] = $k;
                }
            }
            Variation::whereNotIn('id', $variations_ids)
                    ->where('product_variation_id', $key)
                    ->delete();

            //Add new variations
            if(!empty($value['variations'])){

                $variation_data = array();
                $c = Variation::withTrashed()
                                ->where('product_id', $product->id)
                                ->count()+1;

                foreach($value['variations'] as $k => $v){
                    $sub_sku = empty($v['sub_sku'])? $this->generateSubSku($product->sku, $c, $product->barcode_type) :$v['sub_sku'];
                    $variation_data[] = array(
                      'name' => $v['value'],
                      'product_id' => $product->id,
                      'sub_sku' => $sub_sku,
                      'default_purchase_price' => $this->num_uf($v['default_purchase_price']),
                      'dpp_inc_tax' => $this->num_uf($v['dpp_inc_tax']),
                      'profit_percent' => $this->num_uf($v['profit_percent']),
                      'default_sell_price' => $this->num_uf($v['default_sell_price']),
                      'sell_price_inc_tax' => $this->num_uf($v['sell_price_inc_tax'])
                    );
                    $c++;
                }
                $product_variation->variations()->createMany($variation_data);
            }
        }

        ProductVariation::where('product_id', $product_id)
                ->whereNotIn('id', $product_variation_ids)
                ->delete();
    }

    /**
     * Checks if products has manage stock enabled then Updates quantity for product and its 
     * variations
     * 
     * @param $location_id
     * @param $product_id
     * @param $variation_id
     * @param $new_quantity
     * @param $old_quantity = 0
     * @param $number_format = null
     *
     * @return boolean
     */
    public function updateProductQuantity($location_id, $product_id, $variation_id, $new_quantity, $old_quantity = 0, $number_format = null)
    {
        $qty_difference = $this->num_uf($new_quantity, $number_format) - $this->num_uf($old_quantity, $number_format);

        $product = Product::find($product_id);

        //Check if stock is enabled or not.
        if($product->enable_stock == 1 && $qty_difference != 0){

            $variation = Variation::where('id', $variation_id)
                            ->where('product_id', $product_id)
                            ->first();
            
            //Add quantity in VariationLocationDetails
            $variation_location_d = VariationLocationDetails
                                    ::where('variation_id', $variation->id)
                                    ->where('product_id', $product_id)
                                    ->where('product_variation_id', $variation->product_variation_id)
                                    ->where('location_id', $location_id)
                                    ->first();

            if(empty($variation_location_d)){
                $variation_location_d = new VariationLocationDetails();
                $variation_location_d->variation_id = $variation->id;
                $variation_location_d->product_id = $product_id;
                $variation_location_d->location_id = $location_id;
                $variation_location_d->product_variation_id = $variation->product_variation_id;
                $variation_location_d->qty_available = 0;
            }

            $variation_location_d->qty_available += $qty_difference;
            $variation_location_d->save();

            //TODO: Add quantity in products table
            // Product::where('id', $product_id)
            //     ->increment('total_qty_available', $qty_difference);
        }
        
        return true;
    }

    /**
     * Checks if products has manage stock enabled then Decrease quantity for product and its variations
     * 
     * @param $product_id
     * @param $variation_id
     * @param $location_id
     * @param $new_quantity
     * @param $old_quantity = 0
     *
     * @return boolean
     */
    public function decreaseProductQuantity($product_id, $variation_id, $location_id, $new_quantity, $old_quantity = 0)
    {
        $qty_difference = $new_quantity - $old_quantity;

        $product = Product::find($product_id);

        //Check if stock is enabled or not.
        if($product->enable_stock == 1){

            //Decrement Quantity in variations location table
            VariationLocationDetails::where('variation_id', $variation_id)
                ->where('product_id', $product_id)
                ->where('location_id', $location_id)
                ->decrement('qty_available', $qty_difference);

            
            // Variation::where('id', $variation_id)
            //     ->where('product_id', $product_id)
            //     ->decrement('qty_available', $qty_difference);

            //TODO: Decrement quantity in products table
            // Product::where('id', $product_id)
            //     ->decrement('total_qty_available', $qty_difference);
        }

        return true;
    }

    /**
     * Get all details for a product from its variation id
     * 
     * @param int $variation_id
     * @param int $business_id
     * @param int $location_id
     * @param bool $check_qty (If false qty_available is not checked)
     *
     * @return array
     */
    public function getDetailsFromVariation($variation_id, $business_id, $location_id = null, $check_qty = true )
    {
        $query = Variation::join('products AS p', 'variations.product_id', '=', 'p.id')
                ->join('product_variations AS pv', 'variations.product_variation_id', '=', 'pv.id')
                ->leftjoin('variation_location_details AS vld', 'variations.id',  '=', 'vld.variation_id')
                ->leftjoin('units', 'p.unit_id', '=', 'units.id')
                ->leftjoin('brands', function ($join) {
                    $join->on('p.brand_id', '=', 'brands.id')
                        ->whereNull('brands.deleted_at');
                })
                ->where('p.business_id', $business_id)
                ->where('variations.id', $variation_id);

        //Add condition for check of quantity. (if stock is not enabled or qty_available > 0)
        if($check_qty){
            $query->where(function ($query) use ($location_id) {
                $query->where('p.enable_stock', '!=', 1)
                    ->orWhere('vld.qty_available', '>', 0);
            });
        }
        
        if( !empty($location_id) ){

            //Check for enable stock, if enabled check for location id.
            $query->where(function ($query) use ($location_id) {
                        $query->where('p.enable_stock', '!=', 1)
                            ->orWhere('vld.location_id', $location_id);
                    });
        }
        
        $products = $query->select(DB::raw("IF(pv.is_dummy = 0, CONCAT(p.name, 
                    ' (', pv.name, ':',variations.name, ')'), p.name) AS product_name"), 
                    'p.id as product_id',
                    'p.tax as tax_id',
                    'p.enable_stock',
                    'p.enable_sr_no',
                    'p.name as product_actual_name',
                    'pv.name as product_variation_name',
                    'pv.is_dummy as is_dummy',
                    'variations.name as variation_name',
                    'variations.sub_sku',
                    'p.barcode_type',
                    'vld.qty_available', 
                    'variations.default_sell_price', 
                    'variations.sell_price_inc_tax', 
                    'variations.id as variation_id',
                    'units.short_name as unit',
                    'units.allow_decimal as unit_allow_decimal',
                    'brands.name as brand',
                    DB::raw("(SELECT purchase_price_inc_tax FROM purchase_lines WHERE 
                        variation_id=variations.id ORDER BY id DESC LIMIT 1) as last_purchased_price")
                )
                ->first();

        return $products;
    }

    /**
     * Calculates the total amount of invoice
     * 
     * @param array $products
     * @param int $tax_id
     * @param array $discount['discount_type', 'discount_amount']
     *
     * @return Mixed (false, array)
     */
    public function calculateInvoiceTotal($products, $tax_id, $discount = null){

        if(empty($products)){
            return false;
        }

        $output = ['total_before_tax' => 0, 'tax' => 0, 'discount' => 0, '  final_total' => 0];

        //Sub Total
        foreach ($products as $product) {
            $output['total_before_tax'] += $this->num_uf($product['unit_price_inc_tax']) * $this->num_uf($product['quantity']);

            //Add modifier price to total if exists
            if(!empty($product['modifier_price'])){
                foreach ($product['modifier_price'] as $modifier_price) {
                    $output['total_before_tax'] += $this->num_uf($modifier_price);
                }
            }
        }

        //Calculate discount
        if(is_array($discount)){
            if($discount['discount_type'] == 'fixed'){
                $output['discount'] = $this->num_uf($discount['discount_amount']);
            } else {
                $output['discount'] = ($this->num_uf($discount['discount_amount'])/100)*$output['total_before_tax'];
            }
        }

        //Tax
        $output['tax'] = 0;
        if(!empty($tax_id)){
            $tax_details = TaxRate::find($tax_id);
            if(!empty($tax_details)){
                $output['tax'] = ($tax_details->amount/100) * ($output['total_before_tax'] - $output['discount']);
            }
        }
        
        //Calculate total
        $output['final_total'] = $output['total_before_tax'] + $output['tax'] - $output['discount'];
        
        return $output;
    }

    /**
     * Generates product sku
     * 
     * @param string $string
     *
     * @return generated sku (string)
     */
    public function generateProductSku( $string ){

        $business_id = request()->session()->get('user.business_id');
        $sku_prefix = Business::where('id', $business_id)->value('sku_prefix');

        return $sku_prefix . str_pad($string, 4, '0', STR_PAD_LEFT);
    }

    /**
     * Gives list of trending products
     * 
     * @param int $business_id
     * @param array $filters
     *
     * @return Obj
     */
    public function getTrendingProducts($business_id, $filters = array())
    {
        $query = Transaction::join( 'transaction_sell_lines as tsl', 
                                    'transactions.id', '=', 'tsl.transaction_id')
                    ->join('products as p',  'tsl.product_id', '=', 'p.id')
                    ->leftjoin('units as u', 'u.id', '=', 'p.unit_id')
                    ->where('transactions.business_id', $business_id)
                    ->where('transactions.type', 'sell')
                    ->where('transactions.status', 'final');

        $permitted_locations = auth()->user()->permitted_locations();
        if($permitted_locations != 'all'){
            $query->whereIn('transactions.location_id', $permitted_locations);
        }
        if(!empty($filters['location_id'])){
            $query->where('transactions.location_id', $filters['location_id']);
        }
        if(!empty($filters['category'])){
            $query->where('p.category_id', $filters['category']);
        }
        if(!empty($filters['sub_category'])){
            $query->where('p.sub_category_id', $filters['sub_category']);
        }
        if(!empty($filters['brand'])){
            $query->where('p.brand_id', $filters['brand']);
        }
        if(!empty($filters['unit'])){
            $query->where('p.unit_id', $filters['unit']);
        }
        if(!empty($filters['limit'])){
            $query->limit($filters['limit']);
        } else {
            $query->limit(5);
        }
        if(!empty($filters['start_date']) && !empty($filters['end_date'])){
            $query->whereBetween(DB::raw('date(transaction_date)'), [$filters['start_date'], 
                $filters['end_date']]);
        }

        $sell_return_query = "(SELECT SUM(TPL.quantity) FROM transactions AS T JOIN purchase_lines AS TPL ON T.id=TPL.transaction_id WHERE TPL.product_id=tsl.product_id AND T.type='sell_return'";
        if($permitted_locations != 'all'){
            $sell_return_query .= ' AND T.location_id IN ('
             . implode(',', $permitted_locations) . ') ';
        }
        if(!empty($filters['start_date']) && !empty($filters['end_date'])){
            $sell_return_query .= ' AND date(T.transaction_date) BETWEEN \'' . $filters['start_date'] . '\' AND \'' . $filters['end_date'] . '\'';
        }
        $sell_return_query .= ')';

        $products = $query->select( DB::raw("(SUM(tsl.quantity) - COALESCE($sell_return_query, 0)) as total_unit_sold"),
                                'p.name as product',
                                'u.short_name as unit')
                        ->groupBy('tsl.product_id')
                        ->orderBy('total_unit_sold', 'desc')
                        ->get();
        return $products;

    }

    /**
     * Gives list of products based on products id and variation id
     * 
     * @param int $business_id
     * @param int $product_id
     * @param int $variation_id = null
     *
     * @return Obj
     */
    public function getDetailsFromProduct($business_id, $product_id, $variation_id = null)
    {
        $product = Product::leftjoin('variations as v', 'products.id', '=', 'v.product_id')
                        ->where('products.business_id', $business_id);

        if( !is_null($variation_id) && $variation_id !== '0'){
            $product->where('v.id', $variation_id);
        }

        $product->where('products.id', $product_id);

        $products = $product->select('products.id as product_id', 
                        'products.name as product_name', 'v.id as variation_id', 'v.name as variation_name')
                    ->get();

        return $products;
    }

    /**
     * F => D (Previous product Increase)
     * D => F (All product decrease)
     * F => F (Newly added product drerease)
     * 
     * @param  object $transaction_before
     * @param  object  $transaction
     * @param  array  $input
     *
     * @return void
     */
    public function adjustProductStockForInvoice($status_before, $transaction, $input){

        if($status_before == 'final' && $transaction->status == 'draft'){

            foreach ($input['products'] as $product) {
                if(!empty($product['transaction_sell_lines_id'])){
                    $this->updateProductQuantity($input['location_id'], $product['product_id'], $product['variation_id'], $product['quantity']);
                }
            }

        } elseif($status_before == 'draft' && $transaction->status == 'final') {
            foreach ($input['products'] as $product) {
                $this->decreaseProductQuantity(
                            $product['product_id'],
                            $product['variation_id'],
                            $input['location_id'],
                            $this->num_uf($product['quantity'])
                        );
            }
            
        } elseif($status_before == 'final' && $transaction->status == 'final'){
            foreach ($input['products'] as $product) {
                if(empty($product['transaction_sell_lines_id'])){
                    $this->decreaseProductQuantity(
                            $product['product_id'],
                            $product['variation_id'],
                            $input['location_id'],
                            $this->num_uf($product['quantity'])
                        );
                }
            }
        }
    }

    /**
     * Updates variation from purchase screen
     * 
     * @param array $variation_data
     *
     * @return void
     */
    public function updateProductFromPurchase($variation_data){
        $variation_details = Variation::where('id', $variation_data['variation_id'])
                                        ->with(['product', 'product.product_tax'])
                                        ->first();
        $tax_rate = 0;
        if(!empty($variation_details->product->product_tax->amount)){
            $tax_rate = $variation_details->product->product_tax->amount;
        }

        if(($variation_details->default_purchase_price != $variation_data['pp_without_discount']) || 
            ($variation_details->default_sell_price != $variation_data['default_sell_price'])
            ){
            //Set default purchase price exc. tax
            $variation_details->default_purchase_price = $variation_data['pp_without_discount'];

            //Set default purchase price inc. tax
            $variation_details->dpp_inc_tax = $this->calc_percentage($variation_details->default_purchase_price, $tax_rate, $variation_details->default_purchase_price);
       
            //Set default sell price exc. tax
            $variation_details->default_sell_price = $variation_data['default_sell_price'];

            //set profit margin
            $variation_details->profit_percent = $this->get_percent($variation_details->default_purchase_price, $variation_details->default_sell_price);

            //set sell price inc. tax
            $variation_details->sell_price_inc_tax = $this->calc_percentage($variation_details->default_sell_price, $tax_rate, $variation_details->default_sell_price);
            
            $variation_details->save();
        }

    }

    /**
     * Generated SKU based on the barcode type.
     * 
     * @param string $sku
     * @param string $c
     * @param string $barcode_type
     *
     * @return void
     */
    public function generateSubSku($sku, $c, $barcode_type){
        $sub_sku = $sku . $c;

        if(in_array($barcode_type, ['C128', 'C39'])){
            $sub_sku = $sku . '-' . $c;
        }

        return $sub_sku;
    }

    /**
     * Add rack details.
     * 
     * @param int $business_id
     * @param int $product_id
     * @param array $product_racks
     * @param array $product_racks
     *
     * @return void
     */
    public function addRackDetails($business_id, $product_id, $product_racks){

        if(!empty($product_racks)){
            $data = [];
            foreach ($product_racks as $location_id => $detail) {
                $data[] = ['business_id' => $business_id,
                        'location_id' => $location_id,
                        'product_id' => $product_id,
                        'rack' => !empty($detail['rack']) ? $detail['rack'] : null,
                        'row' => !empty($detail['row']) ? $detail['row'] : null,
                        'position' => !empty($detail['position']) ? $detail['position'] : null,
                        'created_at' => \Carbon::now()->toDateTimeString(),
                        'updated_at' => \Carbon::now()->toDateTimeString()
                    ];
            }

            ProductRack::insert($data);
        }
    }

    /**
     * Get rack details.
     * 
     * @param int $business_id
     * @param int $product_id
     *
     * @return void
     */
    public function getRackDetails($business_id, $product_id, $get_location = false){

        $query = ProductRack::where('product_racks.business_id', $business_id)
                    ->where('product_id', $product_id);

        if($get_location){
            $racks = $query->join('business_locations AS BL', 'product_racks.location_id', '=', 'BL.id')
                ->select(['product_racks.rack', 
                        'product_racks.row', 
                        'product_racks.position',
                        'BL.name'])
                ->get();
        } else {
            $racks = collect($query->select(['rack', 'row', 'position', 'location_id'])->get());

            $racks = $racks->mapWithKeys(function ($item, $key) {
                return [$item['location_id'] => $item->toArray()];
            })->toArray();
        }

        return $racks;
    }

    /**
     * Update rack details.
     * 
     * @param int $business_id
     * @param int $product_id
     * @param array $product_racks
     *
     * @return void
     */
    public function updateRackDetails($business_id, $product_id, $product_racks){

        if(!empty($product_racks)){
            foreach ($product_racks as $location_id => $details) {
                ProductRack::where('business_id', $business_id)
                    ->where('product_id', $product_id)
                    ->where('location_id', $location_id)
                    ->update(['rack' => !empty($details['rack']) ? $details['rack'] : null, 
                            'row' => !empty($details['row']) ? $details['row'] : null,
                            'position' => !empty($details['position']) ? $details['position'] : null
                        ]);
            }
        }
    }
}

?>