<?php

namespace App\Models\Sales;

use App\Models\Product;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class SaleReturnDetails extends Model
{
    use HasFactory,SoftDeletes;

    protected $fillable = [
        'sale_return_invoice_id',
        'sale_invoice_detail_id',
        'product_id',
        'product_price_id',
        'item_batch',
        'main_qty',
        'alt_qty',
        'free_qty',
        'price',
        'basic_amount',
        'discount_percentage',
        'discount_amount',
        'tax_slab_id',
        'tax_calculation_type',
        'tax_cess_settings',
        'tax_cess_amount',
        'tax_special_cess_amount',
        'total_amount',
        'from_date_time',
        'to_date_time',
        'gross_weight',
        'net_weight',
        'charge_weight',
        'making_charge',
        'additional_cost',
        'other_charges',
        'godown_id',
        'item_details',
        'batch_id',
        'customer_amount',
        'tpa_amount'
    ];

    public function getProduct()
    {
        return $this->hasOne(Product::class,'id','product_id')->with('getProductPrice','getTaxMaster','getProductBrand');
    }

    static public function getReportsAndInsightsStatement($where = [], $filters = [])
    {
        $from_date         = $filters["from_date"] ?? '';
        $to_date           = $filters["to_date"] ?? '';
        $company           = $filters["company"] ?? '';
        $search_account    = $filters["search_account"] ?? '';
        $created_by        = $filters["created_by"] ?? '';
        $voucher_type_code = $filters["voucher_type_code"] ?? '';
        $brand_id          = $filters["brand_id"] ?? '';
        $group_id          = $filters["group_id"] ?? '';
        $category_id       = $filters["category_id"] ?? '';
        $division_id       = $filters["division_id"] ?? '';
        $product_id        = $filters["product_id"] ?? '';
        $doctor            = $filters["doctor_id"] ?? '';

        return self::with('getProduct') 
                ->leftJoin('sale_returns','sale_return_details.sale_return_invoice_id','sale_returns.id')
                ->leftJoin('tax_masters','sale_return_details.tax_slab_id','tax_masters.id')
                ->leftjoin('products', 'sale_return_details.product_id', 'products.id')
                ->leftjoin('product_categories', 'products.category_id', 'product_categories.id')
                ->leftjoin('sale_invoice_batches', 'sale_return_details.batch_id', 'sale_invoice_batches.id')
                ->leftJoin('accounts as bill_to','sale_returns.bill_to_account_id','bill_to.id')
                ->leftJoin('accounts as doctor','sale_returns.doctor_id','doctor.id')
                ->leftjoin('accounts', 'accounts.id', 'sale_returns.patient_id')
                ->leftjoin('voucher_types', 'voucher_types.code', 'sale_returns.voucher_type_code')
                ->leftjoin('voucher_masters', 'voucher_types.voucher_master_id', 'voucher_masters.id')
                ->when(!empty($search_account), function($saleInvoices) use($search_account) {
                    $saleInvoices->where('accounts.name', 'LIKE', '%' . $search_account . '%');
                })
                ->when(!empty($brand_id), function ($query) use ($brand_id) {
                    return $query->where('products.brand_id', $brand_id);
                })
                ->when(!empty($doctor), function ($query) use ($doctor) {
                    return $query->where('sale_returns.doctor_id', $doctor);
                })
                ->when(!empty($product_id), function ($query) use ($product_id) {
                    return $query->where('products.id', $product_id);
                })
                ->when(!empty($group_id), function ($query) use ($group_id) {
                    return $query->where('products.composition_id', $group_id);
                })
                ->when(!empty($division_id), function ($query) use ($division_id) {
                    return $query->where('products.business_division_id', $division_id);
                })
                ->when(!empty($category_id), function ($query) use ($category_id) {
                    return $query->where('products.category_id', $category_id);
                })
                ->when(!empty($voucher_type_code), function($saleInvoices) use($voucher_type_code) {
                    $saleInvoices->where('sale_returns.voucher_type_code', '=', $voucher_type_code);
                })
                ->when(!empty($created_by), function($saleInvoices) use($created_by) {
                    $saleInvoices->where('sale_returns.operator_id', '=', $created_by);
                })
                ->when(!empty($company), function($saleInvoices) use($company) {
                    $saleInvoices->where('sale_returns.company_id', '=', $company);
                })
                ->when(!empty($from_date) && !empty($to_date), function($query) use ($from_date, $to_date) {
                    $query->when($from_date == $to_date, function($query2) use ($from_date) {
                        $query2->whereDate('sale_returns.invoice_date', $from_date);
                    })
                    ->when($from_date != $to_date, function($query2) use ($from_date, $to_date) {
                        $query2->whereBetween('sale_returns.invoice_date', [$from_date, $to_date]);
                    });
                })
                ->select([
                    'sale_returns.id',
                    'sale_returns.invoice_date',
                    'sale_returns.main_invoice_no',
                    'sale_returns.voucher_type_invoice_no',
                    'sale_returns.patient_id',
                    'sale_returns.voucher_type_code',
                    'voucher_types.name as voucher_type_name',
                    'sale_returns.payment_mode_id',
                    'sale_returns.invoice_type',
                    'tax_masters.total_percentage as tax_percentage',
                    'sale_return_details.product_id',
                    'sale_return_details.batch_id',
                    'sale_return_details.main_qty',
                    'sale_return_details.alt_qty',
                    'sale_return_details.price',
                    'sale_return_details.basic_amount',
                    'sale_return_details.tax_calculation_type',
                    'sale_return_details.total_amount',
                    'sale_return_details.discount_percentage',
                    'products.name as product_name',
                    'products.shortcode',
                    'products.hsncode',
                    'accounts.name as account_name',
                    'product_categories.name as product_category',
                    'accounts.code as account_code',
                    'doctor.name as doctor_name',
                    'doctor.code as doctor_code',
                    'bill_to.name as bill_to_name',
                    'bill_to.code as bill_to_code',
                    'sale_invoice_batches.invoice_batch_no',
                    'tax_masters.name as tax',
                    'voucher_masters.tax_calculation'
                ])
                ->groupBy('sale_return_details.id')
                ->get();
    }

}
