<?php

namespace App\Models;

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

class SaleInvoice extends Model
{
    use HasFactory,SoftDeletes;

    protected $fillable = [
        'main_invoice_no',
        'branch_invoice_no',
        'voucher_type_invoice_no',
        'invoice_date',
        'voucher_type_code',
        'patient_id',
        'doctor_id',
        'collection_agent_id',
        'refferal_lab_id',
        'reff_no',
        'payment_term_id',
        'billing_amount',
        'received_amount',
        'discount_amount',
        'due_amount',
        'order_date',
        'remarks',
        'broker_id',
        'operator_id',
        'company_id',
        'status',
        'financial_year_id',
        'industry_type_id',
        'dispatch_from_id',
        'ship_to_id',
        'tracking_id',
        'special_case_id',
        'last_voucher_count',
        'bussiness_source_id',
        'payment_mode_id',
        'last_voucher_type_count',
        'invoice_type',
        'transaction_mode_id',
        'updated_by',
        'deleted_by',
        'bill_to_account_id',
        'tpa_corporate_id',
        'last_branch_count'
    ];

    public function getSaleInvoiceDelivery()
    {
        return $this->hasOne(SaleInvoiceDelivery::class);
    }

    public function getSaleInvoiceDetail()
    {
        return $this->hasMany(SaleInvoiceDetail::class,'sale_invoice_id','id')->with('getSaleInvoiceSubDetails');
    }

    public function getSaleInvoiceSubDetail()
    {
        return $this->hasMany(SaleInvoiceSubDetails::class)->with('getTestPackage','tracking');
    }

    public function getSaleInvoiceDetailWithProductTestPackage()
    {
        return $this->hasMany(SaleInvoiceDetail::class)->with('getProductTestPackage','getProduct');
    }

    public function getSaleInvoiceDetails()
    {
        return $this->hasMany(SaleInvoiceDetail::class)->with('getSaleInvoiceSubDetails')->with('getProduct');
    }

    public function getSaleInvoiceBatch()
    {
        return $this->hasOne(SaleInvoiceBatch::class,'invoice_id','id')->select('invoice_id','invoice_batch_no');
    }

    public function getSaleInvoiceDiscounts()
    {
        return $this->hasOne(SaleInvoiceDetail::class)->select(['sale_invoice_id','batch_id',\DB::raw('SUM(discount_amount) as discount_amount')])->groupBy('sale_invoice_id');;
    }


    public function getPatient()
    {
        return $this->hasOne(Account::class, 'id', 'patient_id')->with('first_account_address','account_contact', 'account_title');
    }

    public function getDoctor()
    {
        return $this->hasOne(Account::class, 'id', 'doctor_id')->with('first_account_address','account_contact', 'account_title');
    }

    public function getCollectionAgent()
    {
        return $this->hasOne(Account::class, 'id', 'collection_agent_id')->with('first_account_address','account_contact');
    }

    public function getRefferalLab()
    {
        return $this->hasOne(Account::class, 'id', 'refferal_lab_id')->with('first_account_address','account_contact');
    }

    public function getOperator()
    {
        return $this->hasOne(User::class, 'id', 'operator_id');
    }

    public function getAccountGroup()
    {
       return $this->belongsTo(AccountingGroup::class,'transaction_mode_id');
    }

    public function getPaymentMode()
    {
       return $this->belongsTo(PaymentMode::class,'payment_mode_id');
    }

    public function getPaymentTerm()
    {
       return $this->belongsTo(PaymentTerm::class,'payment_term_id');
    }

    public function getCompany()
    {
       return $this->belongsTo(Company::class,'company_id')->with('getCompanyAddress')->with('getCompanybank');
    }

    public function getVoucherType()
    {
       return $this->belongsTo(VoucherType::class,'code','voucher_type_code');
    }

    public function getCustomer()
    {
        return $this->hasOne(Account::class, 'id', 'patient_id')->with('first_account_address','account_contact', 'account_title');
    }

    public function user(){
        return $this->hasOne(User::class,'id','created_by');
    }

    public function accountSaleReceiptTransaction(){
        return $this->hasOne(User::class,'id','created_by');
    }

    public function acReceipt(){
        return $this->hasOne(AcReceipt::class,'voucher_id','id')
            ->where(['module_code' => 'SALES'])
            ->with('accountReceiptTransaction');
    }

    public function getCreatedBy()
    {
       return $this->hasOne(User::class,'id','operator_id')->select(['id','name','prepared_by']);
    }

    public function getVoucher()
    {
        return $this->hasOne(VoucherType::class,'code','voucher_type_code')->with('voucherTerm');
    }

    public function getBatchs()
    {
        return $this->hasMany(SaleInvoiceBatch::class, 'invoice_id', 'id');
    }

    public function getBatch()
    {
        return $this->hasOne(SaleInvoiceBatch::class, 'invoice_id', 'id');
    }

    static public function getSaleInvoices($where = [], $filter = [], $groupByDepartment = 0) {
        $data =  self::select([
            'sale_invoices.id',
            'sale_invoices.main_invoice_no',
            'sale_invoices.voucher_type_invoice_no',
            'sale_invoice_batches.invoice_batch_no',
            'sale_invoice_batches.id as batch_id',
            'sale_invoice_details.id as sale_invoice_details_id',
            'sale_invoice_batches.invoice_batch_date',
            'sale_invoices.invoice_date',
            'sale_invoices.patient_id',
            'sale_invoices.created_at',
            'sale_invoice_sub_details.data_referrence_id',
            'task_statuses.name as status',
            'task_statuses.code as status_code',
            'test_packages.id AS test_package_id',
            'test_packages.name AS test_package_name',
            'test_departments.id AS department_id',
            'test_departments.name AS department_name',
            'sitrc.remarks AS remarks',
            'accounts.name AS patent_name',
            'account_contacts.date_of_birth',
            'account_contacts.phone_no',
            'account_contacts.whatsapp_no',
            'account_contacts.address',
            'genders.name as gender',
            'account_titles.name as account_title',
            'test_dept_permissions.user_id as test_dept_permission_user',
        ])
            ->when(!isset($filter['hide_status']) || $filter['hide_status'] == false, function($query) {
                $query->addSelect(\DB::raw('(SELECT CASE WHEN COUNT(sale_invoice_sub_details.id) = COUNT(sit.id) THEN TRUE ELSE FALSE END) AS all_tests_updated'))
                ->addSelect(\DB::raw('(SELECT CASE WHEN COUNT(sale_invoice_sub_details.id) = COUNT(sitr.id) THEN TRUE ELSE FALSE END) AS all_tests_reviewed'))
                ->addSelect(\DB::raw('(SELECT CASE WHEN COUNT(sale_invoice_sub_details.id) = COUNT(sitsc.id) THEN TRUE ELSE FALSE END) AS sample_collection_status'))
                ->addSelect(\DB::raw('(SELECT CASE WHEN COUNT(sale_invoice_sub_details.id) = COUNT(sitrc.id) THEN TRUE ELSE FALSE END) AS all_tests_recheck'));
            })

            ->leftjoin('accounts','accounts.id','sale_invoices.patient_id')
            ->leftjoin('account_contacts','accounts.id','account_contacts.account_id')
            ->leftjoin('genders','genders.id','account_contacts.account_gender_id')
            ->leftjoin('account_titles','account_titles.id','accounts.account_title_id')

            ->leftjoin('sale_invoice_details','sale_invoice_details.sale_invoice_id','sale_invoices.id')
            ->leftjoin('sale_invoice_batches', 'sale_invoice_batches.id', 'sale_invoice_details.batch_id')
            ->leftjoin('sale_invoice_sub_details','sale_invoice_sub_details.sale_invoice_detail_id','sale_invoice_details.id')
            ->leftjoin('task_statuses','sale_invoice_sub_details.task_status_id','task_statuses.id')
            ->leftjoin('test_packages','test_packages.id','sale_invoice_sub_details.data_referrence_id')
            ->leftjoin('test_departments','test_departments.id','test_packages.department_id')
            ->leftjoin('test_dept_permissions','test_dept_permissions.test_departments_id','test_departments.id')
            ->when(!in_array('Admin',\Auth()->user()->roles->pluck('name')->all()), function($query) {
                $query->where('sale_invoices.operator_id', \Auth()->id())
                    ->where('sale_invoices.company_id', \Auth()->user()->company_id);
            })
            ->when(in_array('Admin',\Auth()->user()->roles->pluck('name')->all()), function($query) {
                $query->where('sale_invoices.company_id', \Auth()->user()->company_id);
            })

            ->when(!isset($filter['hide_status']) || $filter['hide_status'] == false, function($query) {
                $query->leftJoin('sale_invoice_task_trackings as sit', function($track) {
                    $track->on('sale_invoice_sub_details.id', '=', 'sit.sale_invoice_sub_details_id')
                    ->where('sit.tracking_type', 'RESULT_UPDATED')
                    ->where('sit.deleted_at', NULL);
                })
                ->leftJoin('sale_invoice_task_trackings as sitr', function($track) {
                    $track->on('sale_invoice_sub_details.id', '=', 'sitr.sale_invoice_sub_details_id')
                    ->where('sitr.tracking_type', 'RESULT_REVIEWED')
                    ->where('sitr.deleted_at', NULL);
                })
                ->leftJoin('sale_invoice_task_trackings as sitsc', function($track) {
                    $track->on('sale_invoice_sub_details.id', '=', 'sitsc.sale_invoice_sub_details_id')
                    ->where('sitsc.tracking_type', 'SAMPLE_COLLECTED')
                    ->where('sitsc.deleted_at', NULL);
                });
            })
            ->leftJoin('sale_invoice_task_trackings as sitrc', function($track) {
                $track->on('sale_invoice_sub_details.id', '=', 'sitrc.sale_invoice_sub_details_id')
                ->where('sitrc.tracking_type', 'RESULT_RECHECK')
                ->where('sitrc.deleted_at', NULL);
            })

            /*->where('sale_invoices.voucher_type_code','PATHOLOGY')*/
            ->where(function($where) {
                $where->where('sale_invoices.voucher_type_code', 'IPD_ADMISSION')
                    ->orWhere('sale_invoices.voucher_type_code', 'PATHOLOGY');
            })
            ->whereNotNull('test_packages.id')
            ->whereNull('sale_invoices.deleted_at')

            ->when(!empty($filter['search_query']) && !empty($filter['search_type']), function ($query) use ($filter) {
                if ($filter['search_type'] == 'invoice_no') {
                    $query->where('voucher_type_invoice_no', 'LIKE', '%'.$filter['search_query'] .'%');
                } else if ($filter['search_type'] == 'pat_name') {
                    $query->where('accounts.name', 'LIKE', '%'.$filter['search_query'] .'%');
                } else if ($filter['search_type'] == 'phone') {
                    $query->where('account_contacts.phone_no', 'LIKE', '%'.$filter['search_query'] .'%');
                } else if ($filter['search_type'] == 'abha_no') {
                    $query->where('accounts.unique_register_no', 'LIKE', '%'.$filter['search_query'] .'%');
                } else if ($filter['search_type'] == 'test') {
                    $query->where('test_packages.name', 'LIKE', '%'.$filter['search_query'] .'%');
                } else if ($filter['search_type'] == 'department') {
                    $query->where('test_departments.name', 'LIKE', '%'.$filter['search_query'] .'%');
                } else if ($filter['search_type'] == 'batch_no') {
                    $query->where('sale_invoice_batches.invoice_batch_no', 'LIKE', '%'.$filter['search_query'] .'%');
                }
            })

            ->when($where != [], function($query) use ($where) {
                $query->where($where);
            })

            ->when(!empty($filter['start_date']) && !empty($filter['end_date']) && (empty($filter['search_query']) || $filter['search_type'] != 'invoice_no'), function($query) use ($filter) {
                $query->whereBetween('sale_invoices.invoice_date', [$filter['start_date'], $filter['end_date']]);
            })

            ->when(!empty($filter['from_date']) && (empty($filter['search_query']) || $filter['search_type'] != 'invoice_no'), function($query) use ($filter) {
                $query->whereDate('sale_invoices.invoice_date', $filter['from_date']);
            })
            ->when(!empty($filter['sample_collection']), function($query) use ($filter) {
                if ($filter['sample_collection'] == 'pending') {
                    $query->having('sample_collection_status', 0);
                } else if ($filter['sample_collection'] == 'collected') {
                    $query->having('sample_collection_status', 1);
                }
            })
            ->when(!empty($filter['result_update']), function($query) use ($filter) {
                if ($filter['result_update'] == 'pending') {
                    $query->having(function($q) use ($filter){
                        return $q->having('all_tests_updated', 0)
                        ->orHaving('all_tests_recheck', 1);
                    });
                } else if ($filter['result_update'] == 'done') {
                    $query->having('all_tests_updated', 1);
                    $query->having('all_tests_recheck', 0);
                }
            })
            ->when(!empty($filter['doctor_review']), function($query) use ($filter) {
                if ($filter['doctor_review'] == 'pending') {
                    $query->having('all_tests_reviewed', 0);
                } else if ($filter['doctor_review'] == 'done') {
                    $query->having('all_tests_reviewed', 1);
                }
            })
            ->when(!empty($filter['recheck']), function($query) use ($filter) {
                if ($filter['recheck'] == 'done') {
                    $query->having('all_tests_recheck', 0);
                }
            })
            ->when(!empty($filter['auth_roles']), function($query) use ($filter) {
                // $query->where('test_dept_permissions.user_id', $filter['auth_roles']);
            })
            ->when(!empty($filter['batch_id']), function($query) use ($filter) {
                $query->where('sale_invoice_details.batch_id', $filter['batch_id']);
            });

            $data = $data->groupBy('sale_invoice_batches.invoice_batch_no');
        if ($groupByDepartment == 1) {
            $data = $data->groupBy('sale_invoice_details.id')
                ->groupBy('test_departments.id');
        } else {
            $data = $data->groupBy('sale_invoices.id');
        }

        $data = $data->latest('sale_invoices.created_at');

        return $data;
    }
    public function getIpdAdmission()
    {
       return $this->hasOne(IpdAdmission::class,'sale_invoice_id','id');
    }
    static public function getStatementSaleInvoice($where = [], $filter = [])
    {
        $data = self::select([
            'sale_invoices.id',
            'sale_invoices.main_invoice_no',
            'sale_invoices.voucher_type_code',
            'sale_invoices.invoice_date',
            'sale_invoices.patient_id',
            'sale_invoices.created_at',
            'sale_invoices.billing_amount',
            'sale_invoices.received_amount',
            'sale_invoices.due_amount',
            'sale_invoice_batches.remarks_for_technician',
            'sale_invoice_batches.invoice_batch_no',
            'special_cases.name as special_case',
            'accounts.name AS account_name',
            'account_contacts.date_of_birth',
            'account_contacts.account_gender_id',
            'account_contacts.phone_no',
            'account_contacts.organization',
            'genders.name as gender',
            'account_titles.name as account_title',
            'account_addresses.address_line1',
            'doctor.name as doctor',
            'doctor_title.name as doctor_title',
            'states.name as state',
            'bill_to_account.name as bill_to_account',
            'bill_to_title.name as bill_to_title',
            'referral.name as referral',
            'refferal_title.name as refferal_title',
            'refferal_contacts.organization as refferal_organization',
            'coll_agent.name as coll_agent',
            'coll_agent_title.name as coll_agent_title',
            'users.name as operator',
            \DB::raw('SUM(sale_invoice_details.total_amount) as total_billing_amount'),
            \DB::raw("GROUP_CONCAT(DISTINCT ac_receipt_details.id SEPARATOR ',') ac_receipt_details_ids"),

        ])
            ->leftjoin('sale_invoice_details', 'sale_invoices.id', '=', 'sale_invoice_details.sale_invoice_id')
            ->leftjoin('products', 'products.id', '=', 'sale_invoice_details.product_id')
            ->leftjoin('accounts', 'accounts.id', 'sale_invoices.patient_id')
            ->leftjoin('sale_invoice_batches', 'sale_invoice_batches.invoice_id', 'sale_invoices.id')
            ->leftjoin('special_cases', 'special_cases.id', 'sale_invoices.special_case_id')
            ->leftjoin('account_contacts', 'accounts.id', 'account_contacts.account_id')
            ->leftjoin('genders', 'genders.id', 'account_contacts.account_gender_id')
            ->leftjoin('account_titles', 'account_titles.id', 'accounts.account_title_id')
            ->leftjoin('account_addresses', 'account_addresses.account_id', 'accounts.id')
            ->leftjoin('states', 'account_addresses.state_id', 'states.id')

            ->leftjoin('accounts as doctor','doctor.id','sale_invoices.doctor_id')
            ->leftjoin('account_titles as doctor_title','doctor.account_title_id','doctor_title.id')
            ->leftjoin('accounts as bill_to_account','bill_to_account.id','sale_invoices.bill_to_account_id')
            ->leftjoin('account_titles as bill_to_title','bill_to_account.account_title_id','bill_to_title.id')

            ->leftjoin('accounts as referral','referral.id','sale_invoices.refferal_lab_id')
            ->leftjoin('account_titles as refferal_title','referral.account_title_id','refferal_title.id')
            ->leftjoin('account_contacts as refferal_contacts','refferal_contacts.account_id','sale_invoices.refferal_lab_id')

            ->leftjoin('accounts as coll_agent','coll_agent.id','sale_invoices.collection_agent_id')
            ->leftjoin('account_titles as coll_agent_title','coll_agent.account_title_id','coll_agent_title.id')

            ->leftjoin('accounts as operator','operator.id','sale_invoices.operator_id')
            ->leftjoin('users', 'users.id','sale_invoices.operator_id')

            ->leftjoin('ac_receipts', function($join) {
                $join->on('ac_receipts.voucher_id', 'sale_invoices.id')
                    ->where('ac_receipts.module_code', "PATHOLOGY");
            })
            ->leftjoin('ac_receipt_details', function($join) {
                $join->on('ac_receipt_details.voucher_id', 'ac_receipts.id')
                    ->where('ac_receipt_details.transaction_type', "CREDIT");
            })
            ->withSum('getSaleInvoiceDetails', 'total_amount')
            ->where($where)
            ->when(!empty($filter['from_date']) && !empty($filter['to_date']), function($query) use ($filter) {
                $query->when($filter['from_date'] == $filter['to_date'], function($query2) use ($filter) {
                    $query2->whereDate('sale_invoices.invoice_date', $filter['from_date']);
                })
                ->when($filter['from_date'] != $filter['to_date'], function($query2) use ($filter) {
                    $query2->whereBetween('sale_invoices.invoice_date', [$filter['from_date'], $filter['to_date']]);
                });
            })
            ->when(!empty($filter['search_account']) && $filter['search_account']   !=  null  && $filter['account_filter']  == 'patient' , function($query) use ($filter) {
                $query->where('accounts.id', $filter['search_account'] );
                    
            }) 
            ->when(!empty($filter['search_account']) && $filter['search_account']  !=  null  && $filter['account_filter']  == 'doctor' , function($query) use ($filter) {
                $query->where('doctor.id', $filter['search_account'] );
                    
            }) 
            ->when(!empty($filter['search_account']) && $filter['search_account']  !=  null  && $filter['account_filter']  == 'refferal_lab' , function($query) use ($filter) {
                $query->where('referral.id', $filter['search_account'] );
            }) 
            ->when(!empty($filter['search_account']) && $filter['search_account']   !=  null  && $filter['account_filter']  == 'collection_agent' , function($query) use ($filter) {
                $query->where('coll_agent.id', $filter['search_account'] );
                    
            })
            ->when(!empty($filter['category']) && $filter['category'] != null, function($query) use ($filter) {
                $query->where('products.category_id', $filter['category']);
            })
            ->when(!empty($filter['brand']) && $filter['brand'] != null, function($query) use ($filter) {
                $query->where('products.brand_id', $filter['brand']);
            })
            ->when(!empty($filter['company']) && $filter['company'] != null, function($query) use ($filter) {
                $query->where('sale_invoices.company_id', $filter['company']);
            })
            ->when(!empty($filter['created_by']) && $filter['created_by'] != null, function($query) use ($filter) {
                $query->where('sale_invoices.operator_id', $filter['created_by']);
            })
            ->groupBy('sale_invoices.id')
            ->get();

        return $data;
    }
}
