<?php
namespace App\Helpers;

use App\Models\FarmerBatch;
use App\Models\FarmerInvoiceItem;
use App\Models\ProductPrice;
use Illuminate\Support\Facades\DB;

class Helpers
{

    /**
     * farmers active chicks quantity
     */
    public static function farmersActiveBatchCickQuantity()
    {
        return FarmerBatch::where("status", "active")->sum("chicks_quantity");
    }

    /**
     * farmers active chick amount
     */
    public static function farmersActiveBatchCickAmount() 
    {
        $activeBatchChicks = FarmerBatch::where("status", "active")->get();
        $totalAmount = 0;
        foreach($activeBatchChicks as $activeBatchChick) {
            $chickPrice = ProductPrice::where("product_id", $activeBatchChick->product_id)->where("batch_no", $activeBatchChick->chicks_batch_no)->first();
            $totalAmount += $activeBatchChick->chicks_quantity * $chickPrice->cost_price;
        }
        return $totalAmount;
    }

    /**
     * total active batch feed
     */
    public static function totalActiveBatchFeed()
    {
        $feeds = FarmerInvoiceItem::whereIn("batch_number", function($query) {
            return $query->select('batch_number')->from("farmer_batches")->where("status", "active");
        })->whereIn("product_id", function($query) {
            return $query->select("id")->from("products")->whereIn("subcategory_id", function($query) {
                return $query->from("sub_categories")->select("id")->whereIn("category_id", function($query) {
                    return $query->select("id")->from("categories")->where("slug", "feeds");
                });
            });
        })->selectRaw("SUM(total_selling) as total_selling, SUM(quantity) as quantity")->first();
        return $feeds;
    }

    /**
     * total active batch medicine
     */
    public static function totalActiveBatchMedicine()
    {
        $medicines = FarmerInvoiceItem::whereIn("batch_number", function($query) {
            return $query->select('batch_number')->from("farmer_batches")->where("status", "active");
        })->whereIn("product_id", function($query) {
            return $query->select("id")->from("products")->whereIn("subcategory_id", function($query) {
                return $query->from("sub_categories")->select("id")->whereIn("category_id", function($query) {
                    return $query->select("id")->from("categories")->where("slug", "medicines");
                });
            });
        })->selectRaw("SUM(total_selling) as total_selling, SUM(quantity) as quantity")->first();
        return $medicines;
    }

    /*
     * Chicks Stock
     * */

    public static function totalChicksQuantity()
    {
        $chicks = DB::table('product_prices')
            ->join('products','product_prices.product_id','=','products.id')
            ->join('sub_categories','products.subcategory_id','=','sub_categories.id')
            ->join('categories','sub_categories.category_id','=','categories.id')
            ->selectRaw('product_prices.product_id,sum(product_prices.quantity) quantity,sum(product_prices.sold) sold,sum(product_prices.quantity - product_prices.sold) stock')
            ->where('categories.name','=','Chicks')
            ->groupBy('product_prices.product_id')
            ->get();
        return $chicks->sum('stock');
    }

    /*
     *  Feed Quantity
     * */

    public static function totalFeedQuantity()
    {
        $feed = DB::table('product_prices')
            ->join('products','product_prices.product_id','=','products.id')
            ->join('sub_categories','products.subcategory_id','=','sub_categories.id')
            ->join('categories','sub_categories.category_id','=','categories.id')
            ->selectRaw('product_prices.product_id,sum(product_prices.quantity) quantity,sum(product_prices.sold) sold,sum(product_prices.quantity - product_prices.sold) stock')
            ->where('categories.name','=','Feeds')
            ->groupBy('product_prices.product_id')
            ->get();
        return $feed->sum('stock');
    }

    /*
     * Medicine Quantity
     * */
    public static function  totalMedicineQuantity()
    {
        $medicine = DB::table('product_prices')
            ->join('products','product_prices.product_id','=','products.id')
            ->join('sub_categories','products.subcategory_id','=','sub_categories.id')
            ->join('categories','sub_categories.category_id','=','categories.id')
            ->selectRaw('product_prices.product_id,sum(product_prices.quantity) quantity,sum(product_prices.sold) sold,sum(product_prices.quantity - product_prices.sold) stock')
            ->where('categories.name','=','Medicines')
            ->groupBy('product_prices.product_id')
            ->get();
        return $medicine->sum('stock');
    }

    /*
     * Total Stock
     * */

    public static function totalStock()
    {
        $total = DB::table('product_prices')
            ->join('products','product_prices.product_id','=','products.id')
            ->selectRaw('product_prices.product_id,sum(product_prices.quantity) quantity,sum(product_prices.sold) sold,sum(product_prices.quantity - product_prices.sold) stock')
            ->groupBy('product_prices.product_id')
            ->get();
        return $total->sum('stock');
    }
}
