<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class SaleInvoiceDetail extends Model
{
    use HasFactory,SoftDeletes;

    protected $fillable = [
        'sale_invoice_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 getSaleInvoiceSubDetails()
    {
        return $this->hasOne(SaleInvoiceSubDetails::class);
    }

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

    public function getProductTestPackage()
    {
        return $this->hasMany(ProductTestPackage::class,'product_id','product_id')->with('getTestPackageGroupTestItem','getTestPackage');
    }

    public function getBatch()
    {
        return $this->belongsTo(SaleInvoiceBatch::class,'batch_id');
    }

    public function getProductPrice()
    {
        return $this->hasOne(ProductPrice::class,'id','product_price_id')->select('exp_date');
    }

    static public function getReferralVoucherStatement($where = [], $filters = [])
    {
        $doctor     = $filters['doctor'];
        $start_date = $filters['start_date'];
        $end_date   = $filters['end_date'];
        $statement_by   = $filters['statement_by'] ?? '';

        $data = self::select([
                'sale_invoice_details.id',
                \DB::raw('sum(sale_invoice_details.basic_amount) as basic_amount'),
                \DB::raw('sum(sale_invoice_details.discount_amount) as sale_discount_amount'),
                \DB::raw('sum(sale_invoice_details.main_qty) as referral_count'),
                'sale_invoice_details.product_id',
                'sale_invoice_details.created_at',
                'sale_invoices.main_invoice_no',
                'sale_invoices.voucher_type_invoice_no',
                'sale_invoices.invoice_date',
                'products.name as product',
                'accounts.name as doctor',
                'accounts.code as doctor_code',
                'incentive_settings.date_upto',
                'incentive_slabs.slab_name',
                'patient.name as patient_name',
                'patient.code as patient_code',
                'patient_title.name as patient_title',
                'product_brands.name as brand',
                'sale_others_transactions.price as discount_amount',
            ])
            ->leftjoin('sale_invoice_batches','sale_invoice_details.sale_invoice_id', '=', 'sale_invoice_batches.invoice_id')
            ->leftjoin('accounts','sale_invoice_batches.consultant_id', '=', 'accounts.id')
            ->leftjoin('sale_invoices','sale_invoice_details.sale_invoice_id', '=', 'sale_invoices.id')
            ->leftjoin('incentive_settings','incentive_settings.account_id', '=', 'accounts.id')
            ->leftjoin('products','sale_invoice_details.product_id', '=', 'products.id')
            ->leftjoin('product_brands','product_brands.id', '=', 'products.brand_id')
            ->leftjoin('incentive_slabs','incentive_slabs.id', '=', 'incentive_settings.slab_id')
            ->leftjoin('accounts as patient','sale_invoices.patient_id', '=', 'patient.id')
            ->leftjoin('account_titles as patient_title','patient_title.id', '=', 'patient.account_title_id')
            ->leftjoin('sale_others_transactions', function($join) {
                $join->on('sale_others_transactions.sale_invoice_batch_id', '=', 'sale_invoice_batches.id')
                    ->where('transaction_type', 'DEBIT');
            })
            ->when(!empty($doctor), function($query) use ($doctor) {
                $query->where('sale_invoice_batches.consultant_id', $doctor);
            })
            ->when(!empty($start_date) && !empty($end_date), function($query) use ($start_date, $end_date) {
                $query->whereBetween('sale_invoice_details.created_at', [
                    date('Y-m-d', strtotime($start_date)),
                    date('Y-m-d', strtotime($end_date))
                ]);
            })
            ->where(function($query) {
                $query->whereNull('sale_invoices.refferal_lab_id')
                    ->orWhere('sale_invoices.refferal_lab_id','0');
            })
            ->where($where);

            $data = $data->groupBy('sale_invoice_details.product_id');

            $data = $data->orderBy('products.name' , 'ASC')
                ->whereNotNull('products.id')
                ->get()
                ->map(function ($saleInvoiceDetails, $productId) {
                    $productId = $saleInvoiceDetails->product_id ?? '';
                    $id = $saleInvoiceDetails->id ?? '';

                    $details = IncentiveSlabDetail::select([
                            'incentive_slab_details.calculation_type',
                            'incentive_slab_details.percentage',
                            'incentive_slab_details.price',
                        ])
                        ->leftjoin('incentive_slabs','incentive_slabs.id','incentive_slab_details.slab_id')
                        ->leftjoin('account_types','incentive_slabs.account_type_id','account_types.id')
                        ->where([
                            'incentive_slab_details.product_id' => $productId,
                            'account_types.type_code'                => 'DOCTOR'
                        ])->first();

                    return [
                        'incentive_slab_details' => $details,
                        'sale_invoice_details'   => $saleInvoiceDetails
                    ];
                });

        return $data;
    }

         static public function getReferralLabVoucherStatement($where = [], $filters = [])
        {
            $lab          = $filters['lab'];
            $start_date   = $filters['start_date'];
            $end_date     = $filters['end_date'];
            $statement_by = $filters['statement_by'] ?? '';

            $data = self::select([
                'sale_invoice_details.id',
                \DB::raw('sum(sale_invoice_details.basic_amount) as basic_amount'),
                \DB::raw('sum(sale_invoice_details.discount_amount) as sale_discount_amount'),
                \DB::raw('sum(sale_invoice_details.main_qty) as referral_count'),
                'sale_invoice_details.product_id',
                'sale_invoice_details.created_at',
                'sale_invoices.main_invoice_no',
                'sale_invoices.voucher_type_invoice_no',
                'sale_invoices.invoice_date',
                'products.name as product',
                'accounts.name as labpartner',
                'accounts.code as labpartner_code',
                'incentive_settings.date_upto',
                'incentive_slabs.slab_name',
                'patient.name as patient_name',
                'patient.code as patient_code',
                'patient_title.name as patient_title',
                'product_brands.name as brand',
                'sale_others_transactions.price as discount_amount',
            ])
            ->leftjoin('sale_invoice_batches', 'sale_invoice_details.sale_invoice_id', '=', 'sale_invoice_batches.invoice_id')
            ->leftjoin('sale_invoices', 'sale_invoice_details.sale_invoice_id', '=', 'sale_invoices.id')
            ->leftjoin('accounts', 'sale_invoices.refferal_lab_id', '=', 'accounts.id')
            ->leftjoin('incentive_settings', 'incentive_settings.account_id', '=', 'accounts.id')
            ->leftjoin('products', 'sale_invoice_details.product_id', '=', 'products.id')
            ->leftjoin('product_brands', 'product_brands.id', '=', 'products.brand_id')
            ->leftjoin('incentive_slabs', 'incentive_slabs.id', '=', 'incentive_settings.slab_id')
            ->leftjoin('accounts as patient', 'sale_invoices.patient_id', '=', 'patient.id')
            ->leftjoin('account_titles as patient_title', 'patient_title.id', '=', 'patient.account_title_id')
            ->leftjoin('sale_others_transactions', function($join) {
                $join->on('sale_others_transactions.sale_invoice_batch_id', '=', 'sale_invoice_batches.id')
                    ->where('transaction_type', 'DEBIT');
            })
            ->leftjoin('account_contacts', 'account_contacts.account_id', '=', 'accounts.id') // Join account_contacts
            ->when(!empty($lab), function($query) use ($lab) {
                $query->where('sale_invoices.refferal_lab_id', $lab); // Filter by selected lab
            })
            ->when(!empty($start_date) && !empty($end_date), function($query) use ($start_date, $end_date) {
                $query->whereBetween('sale_invoice_details.created_at', [
                    date('Y-m-d', strtotime($start_date)),
                    date('Y-m-d', strtotime($end_date))
                ]);
            })
            ->where($where)
            ->whereNotNull('account_contacts.organization'); // Ensure organization is not null
           
            $data = $data->groupBy('sale_invoice_details.product_id');

            $data = $data->orderBy('products.name', 'ASC')
                ->whereNotNull('products.id')
                ->get()
                ->map(function ($saleInvoiceDetails) {
                    $productId = $saleInvoiceDetails->product_id ?? '';

                    $details = IncentiveSlabDetail::select([
                        'incentive_slab_details.calculation_type',
                        'incentive_slab_details.percentage',
                        'incentive_slab_details.price',
                    ])
                    ->leftjoin('incentive_slabs', 'incentive_slabs.id', 'incentive_slab_details.slab_id')
                    ->leftjoin('account_types', 'incentive_slabs.account_type_id', 'account_types.id')
                    ->where([
                        'incentive_slab_details.product_id' => $productId,
                        'account_types.type_code' => 'Referral Partner'
                    ])->first();

                    return [
                        'incentive_slab_details' => $details,
                        'sale_invoice_details'   => $saleInvoiceDetails
                    ];
                });

            return $data;
        }



    static public function getReferralAgentVoucherStatement($where = [], $filters = [])
    {
        $agent     = $filters['agent'];
        $start_date = $filters['start_date'];
        $end_date   = $filters['end_date'];
        $statement_by   = $filters['statement_by'] ?? '';

        $data = self::select([
                'sale_invoice_details.id',
                \DB::raw('sum(sale_invoice_details.basic_amount) as basic_amount'),
                \DB::raw('sum(sale_invoice_details.discount_amount) as sale_discount_amount'),
                \DB::raw('sum(sale_invoice_details.main_qty) as referral_count'),
                'sale_invoice_details.product_id',
                'sale_invoice_details.created_at',
                'sale_invoices.main_invoice_no',
                'sale_invoices.voucher_type_invoice_no',
                'sale_invoices.invoice_date',
                'products.name as product',
                'accounts.name as agent',
                'accounts.code as agent_code',
                'incentive_settings.date_upto',
                'incentive_slabs.slab_name',
                'patient.name as patient_name',
                'patient.code as patient_code',
                'patient_title.name as patient_title',
                'product_brands.name as brand',
                'sale_others_transactions.price as discount_amount',
            ])
                ->leftjoin('sale_invoice_batches','sale_invoice_details.sale_invoice_id', '=', 'sale_invoice_batches.invoice_id')
                ->leftjoin('accounts','sale_invoice_batches.consultant_id', '=', 'accounts.id')
                ->leftjoin('sale_invoices','sale_invoice_details.sale_invoice_id', '=', 'sale_invoices.id')
                ->leftjoin('incentive_settings','incentive_settings.account_id', '=', 'accounts.id')
                ->leftjoin('products','sale_invoice_details.product_id', '=', 'products.id')
                ->leftjoin('product_brands','product_brands.id', '=', 'products.brand_id')
                ->leftjoin('incentive_slabs','incentive_slabs.id', '=', 'incentive_settings.slab_id')
                ->leftjoin('accounts as patient','sale_invoices.patient_id', '=', 'patient.id')
                ->leftjoin('account_titles as patient_title','patient_title.id', '=', 'patient.account_title_id')
                ->leftjoin('sale_others_transactions', function($join) {
                    $join->on('sale_others_transactions.sale_invoice_batch_id', '=', 'sale_invoice_batches.id')
                        ->where('transaction_type', 'DEBIT');
                })
                ->when(!empty($agent), function($query) use ($agent) {
                $query->where('sale_invoices.collection_agent_id', $agent); 
            })
                ->when(!empty($start_date) && !empty($end_date), function($query) use ($start_date, $end_date) {
                    $query->whereBetween('sale_invoice_details.created_at', [
                        date('Y-m-d', strtotime($start_date)),
                        date('Y-m-d', strtotime($end_date))
                    ]);
                })
                ->where($where);

                $data = $data->groupBy('sale_invoice_details.product_id');

            $data = $data->orderBy('products.name' , 'ASC')
                ->whereNotNull('products.id')
                ->get()
                ->map(function ($saleInvoiceDetails, $productId) {
                    $productId = $saleInvoiceDetails->product_id ?? '';
                    $id = $saleInvoiceDetails->id ?? '';

                    $details = IncentiveSlabDetail::select([
                            'incentive_slab_details.calculation_type',
                            'incentive_slab_details.percentage',
                            'incentive_slab_details.price',
                        ])
                        ->leftjoin('incentive_slabs','incentive_slabs.id','incentive_slab_details.slab_id')
                        ->leftjoin('account_types','incentive_slabs.account_type_id','account_types.id')
                        ->where([
                            'incentive_slab_details.product_id' => $productId,
                            'account_types.type_code'                => 'COLLECTION_AGENT'
                        ])->first();

                    return [
                        'incentive_slab_details' => $details,
                        'sale_invoice_details'   => $saleInvoiceDetails
                    ];
                });

        return $data;
    }

    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_invoices','sale_invoice_details.sale_invoice_id','sale_invoices.id')
                ->leftJoin('tax_masters','sale_invoice_details.tax_slab_id','tax_masters.id')
                ->leftjoin('products', 'sale_invoice_details.product_id', 'products.id')
                ->leftjoin('product_categories', 'products.category_id', 'product_categories.id')
                ->leftjoin('sale_invoice_batches', 'sale_invoice_details.batch_id', 'sale_invoice_batches.id')
                ->leftJoin('accounts as bill_to','sale_invoices.bill_to_account_id','bill_to.id')
                ->leftJoin('accounts as doctor','sale_invoices.doctor_id','doctor.id')
                ->leftjoin('accounts', 'accounts.id', 'sale_invoices.patient_id')
                ->leftJoin('account_contacts','account_contacts.account_id','sale_invoices.patient_id')
                ->leftJoin('account_contacts as bill_to_contact','sale_invoices.bill_to_account_id','bill_to_contact.account_id')
                ->leftjoin('voucher_types', 'voucher_types.code', 'sale_invoices.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_invoices.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_invoices.voucher_type_code', '=', $voucher_type_code);
                })
                ->when(!empty($created_by), function($saleInvoices) use($created_by) {
                    $saleInvoices->where('sale_invoices.operator_id', '=', $created_by);
                })
                ->when(!empty($company), function($saleInvoices) use($company) {
                    $saleInvoices->where('sale_invoices.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_invoices.invoice_date', $from_date);
                    })
                ->when($from_date != $to_date, function($query2) use ($from_date, $to_date) {
                    $query2->whereDate('sale_invoices.invoice_date', '>=', $from_date)->whereDate('sale_invoices.invoice_date', '<=', $to_date);
                });
                })
                ->select([
                    'sale_invoices.id',
                    'sale_invoices.invoice_date',
                    'sale_invoices.main_invoice_no',
                    'sale_invoices.voucher_type_invoice_no',
                    'sale_invoices.patient_id',
                    'sale_invoices.voucher_type_code',
                    'voucher_types.name as voucher_type_name',
                    'sale_invoices.payment_mode_id',
                    'sale_invoices.invoice_type',
                    'tax_masters.total_percentage as tax_percentage',
                    'sale_invoice_details.product_id',
                    'sale_invoice_details.batch_id',
                    'sale_invoice_details.main_qty',
                    'sale_invoice_details.alt_qty',
                    'sale_invoice_details.basic_amount',
                    'sale_invoice_details.tax_calculation_type',
                    'sale_invoice_details.total_amount',
                    'sale_invoice_details.price',
                    'sale_invoice_details.discount_percentage',
                    'sale_invoice_details.discount_percentage',
                    'sale_invoice_details.discount_percentage',
                    'sale_invoice_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',
                    'account_contacts.phone_no as patient_phone_no',
                    'bill_to_contact.phone_no as bill_to_phone_no'
                ])
                ->groupBy('sale_invoice_details.id')
                ->orderBy('sale_invoices.invoice_date','DESC')
                ->get();
    }
}
