<?php

namespace App\Models;

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

class IpdAdmission extends Model
{
    use HasFactory, SoftDeletes;

    protected $guarded = [];

    public function contacts()
    {
        return $this->belongsTo(Account::class, 'patient_id')->with('account_contact', 'account_title', 'company');
    }

    public function consultantHistory()
    {
        return $this->hasOne(IpdConsultantHistory::class, 'ipd_admission_id', 'id')->with('consultants');
    }
    public function getTpa()
    {
        return $this->belongsTo(Account::class, 'tpa_account_id')->with('account_contact', 'account_title', 'company');
    }
    public function getIpdBed()
    {
        return $this->hasOne(Bed::class, 'ipd_admission_no', 'id');
    }
    public function beds()
    {
        return $this->hasMany(Bed::class, 'ipd_admission_no');
    }

    public function bedHistory()
    {
        return $this->hasMany(IpdBedHistory::class, 'ipd_admission_id', 'id')->whereNotNull('check_out_date_time');
    }
    

    static public function getStatement($where = [], $filter = []) {

        $data = IpdAdmission::select([
                'ipd_admissions.id',
                'ipd_admissions.ipd_admission_no',
                'ipd_admissions.last_ipd_no_count',
                'ipd_admissions.ipd_file_no',
                'ipd_admissions.claim_number',
                'ipd_admissions.admission_date_time',
                'ipd_admissions.patient_id as patient_id',
                'ipd_admissions.patient_source_id',
                'ipd_admissions.tpa_account_id',
                'ipd_admissions.casualty',
                'ipd_admissions.referal_from',
                'ipd_admissions.note',
                'ipd_admissions.referral_partner_id',
                'ipd_admissions.previous_medical_issue',
                'ipd_admissions.sale_invoice_id',
                'ipd_admissions.deleted_at',
                'ipd_admissions.created_at',
                'ipd_admissions.updated_at',
                'ipd_admissions.created_by' , 
                'accounts.company_id as company_id',
                'products.category_id' ,    
                'accounts.name as account_name',
                'accounts.name as account_name',
                'accounts.name as account_name',
                'doctors.name as doctor_name',
                'beds.name as bed_name' ,
                'sale_invoices.id as sale_invoices_name',
                'sale_invoices.operator_id',
                'voucher_type_invoice_no',
                'ipd_discharges.discharge_datetime',

        ])->with(['getPatient','getIpdBed'])
        // ->leftJoin('ipd_admission_discharged', 'ipd_admission_discharged.discharge_type_id', '=', 'ipd_admissions.id')
        ->leftJoin('ipd_discharges', 'ipd_discharges.ipd_admission_id', '=', 'ipd_admissions.id')
        ->leftJoin('accounts', 'accounts.id', '=', 'ipd_admissions.patient_id')
        ->leftJoin('ipd_consultant_histories', function($join) {
            $join->on('ipd_consultant_histories.ipd_admission_id', '=', 'ipd_admissions.id')
                ->where('ipd_consultant_histories.is_main_dr', '1');
        })
        ->leftJoin('accounts as doctors', 'ipd_consultant_histories.doctor_id', '=', 'doctors.id')
        ->leftJoin('ipd_bed_histories', 'ipd_bed_histories.ipd_admission_id', '=', 'ipd_admissions.id')
        ->leftJoin('beds', 'beds.id', 'ipd_bed_histories.bed_no_id')
        ->leftJoin('companies', 'companies.id', '=', 'accounts.company_id')
        ->leftJoin('sale_invoices', function ($join) {
                $join->on('ipd_admissions.sale_invoice_id', '=', 'sale_invoices.id');
                // ->where('sale_invoices.invoice_type', '=', 'ipd-billings');
                })
        ->leftJoin('users', 'users.id', 'sale_invoices.operator_id')
        ->leftJoin('sale_invoice_details', 'sale_invoice_details.sale_invoice_id', '=', 'ipd_admissions.sale_invoice_id')
        ->leftJoin('products', 'sale_invoice_details.product_id', '=', 'products.id')
        ->leftJoin('product_categories', 'product_categories.id', '=', 'products.category_id')
        ->when(!empty($where), function($query) use ($where) {
            $query->where($where);
        })
        ->when(!empty($filter['from_date']) && !empty($filter['to_date']) && !isset($filter['discharged_statement']), function($query) use ($filter) {
            $query->when($filter['from_date'] == $filter['to_date'], function($query) use ($filter) {
                $query->whereDate('ipd_admissions.admission_date_time', '=', $filter['from_date']);
            })
            ->when($filter['from_date'] != $filter['to_date'], function($query) use ($filter) {
                $query->whereBetween('ipd_admissions.admission_date_time', [$filter['from_date'], $filter['to_date']]);
            });
        })
        ->when(!empty($filter['from_date']) && !empty($filter['to_date']) && !isset($filter['discharged_statement']), function($query) use ($filter) {
            $query->when($filter['from_date'] == $filter['to_date'], function($query) use ($filter) {
                $query->whereDate('ipd_discharges.discharge_datetime', '=', $filter['from_date']);
            })
            ->when($filter['from_date'] != $filter['to_date'], function($query) use ($filter) {
                $query->whereBetween('ipd_discharges.discharge_datetime', [$filter['from_date'], $filter['to_date']]);
            });
        })
        ->when(!empty($filter['search_account']), function($query) use ($filter) {
            $query->where(function($query) use ($filter) {
                $query->where('accounts.name', 'LIKE', '%' . $filter['search_account'] . '%');
            });
        })
        ->when(!empty($filter['category']), function($query) use ($filter) {
            $query->where('products.category_id', $filter['category']);
        })
        ->when(!empty($filter['company']), function($query) use ($filter) {
            $query->where('accounts.company_id', $filter['company']);
        })
        ->when(!empty($filter['created_by']), function($query) use ($filter) {
            $query->where('ipd_admissions.created_by', $filter['created_by']);
        })
        ->when(!empty($filter['listing_type']), function($query) use ($filter){
            if ($filter['listing_type'] == 'active') {
                $query->where(function($query) use ($filter) {
                    $query->whereNull('ipd_discharges.id')->orWhere('ipd_discharges.signed_status','0');
                });
            } elseif ($filter['listing_type'] == 'discharged') {
                $query->where('ipd_discharges.signed_status','1');
            }
        })
        ->groupBy('ipd_admissions.id')
        ->get();

        return $data;
    }

    static public function getStatementIpdAdmission( $filter = [])
    {
        $data = self::select([
            'ipd_admissions.id',
            'ipd_admissions.admission_date_time',
            'ipd_admissions.ipd_admission_no',
            'ipd_admissions.patient_id',
            'ipd_discharges.id',
            'ipd_discharges.discharge_datetime',
            'ipd_discharges.ipd_admission_id', 
            'genders.name as gender',
            'account_contacts.account_gender_id',
            'account_contacts.phone_no',
            'accounts.name as patient_name',
            'accounts.company_id',
            'ipd_consultant_histories.ipd_admission_id',
            'doctor.name as doctor',
            'accounts.id',
            'ipd_discharges.discharge_type_code',

        ])
            ->leftjoin('ipd_discharges', 'ipd_discharges.ipd_admission_id', 'ipd_admissions.id')
            ->leftjoin('accounts', 'accounts.id', 'ipd_admissions.patient_id')
            ->leftjoin('account_contacts', 'accounts.id', 'account_contacts.account_id')
            ->leftjoin('genders', 'genders.id', 'account_contacts.account_gender_id') 
            ->leftjoin('ipd_consultant_histories', 'ipd_consultant_histories.ipd_admission_id','ipd_admissions.id')
            ->leftjoin('accounts as doctor', 'doctor.id','ipd_consultant_histories.doctor_id')
            ->where('ipd_discharges.id', '!=', null )
            ->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('ipd_discharges.discharge_datetime', $filter['from_date']);
                })
                ->when($filter['from_date'] != $filter['to_date'], function($query2) use ($filter) {
                    $query2->whereBetween('ipd_discharges.discharge_datetime', [$filter['from_date'], $filter['to_date']]);
                });
            })
            ->when(!empty($filter['discharge_type']) && $filter['discharge_type'] != null, function($query) use ($filter) {
                $query->where('ipd_discharges.discharge_type_code', $filter['discharge_type']);
            })
            ->when(!empty($filter['search_account']) && $filter['search_account'] != null, function($query) use ($filter) {
                $query->where('accounts.name', 'LIKE', '%' . $filter['search_account'] . '%');
            })
            ->when(!empty($filter['company']) && $filter['company'] != null, function($query) use ($filter) {
                $query->where('accounts.company_id', $filter['company']);
            })
            ->when(!empty($filter['created_by']) && $filter['created_by'] != null, function($query) use ($filter) {
                $query->where('ipd_discharges.created_by_id', $filter['created_by']);
            })
            ->groupBy('ipd_discharges.id')
            ->get();

        return $data;
    }

    public function getIpdDischarge()
    {
        return $this->hasOne(IpdDischarge::class, 'ipd_admission_id', 'id');
    }

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

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

