<?php

namespace App\Models;

use App\Helpers\SiteHelper;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Account extends Model
{
    use HasFactory, SoftDeletes;

    protected $guarded = [];
    // protected $with = ['account_contact'];

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

    public function tax_register_category()
    {
        return $this->belongsTo(TaxRegisterCategory::class);
    }

    public function account_division()
    {
        return $this->belongsTo(AccountDivision::class, 'division_id');
    }

    public function master_type()
    {
        return $this->belongsTo(MasterType::class);
    }

    public function master_category()
    {
        return $this->belongsTo(MasterCategory::class);
    }

    public function chart_of_account_sub_type()
    {
        return $this->belongsTo(AccountingGroup::class, 'accounting_group_id')->with('paymentModes');
    }

    public function account_attachments()
    {
        return $this->hasMany(AccountAttachment::class);
    }

    public function account_images()
    {
        return $this->hasMany(AccountImage::class);
    }

    public function account_banks()
    {
        return $this->hasMany(AccountBank::class);
    }

    public function account_addresses()
    {
        return $this->hasMany(AccountAddress::class)->with('state');
    }

    public function first_account_address()
    {
        return $this->hasOne(AccountAddress::class)/*->where('is_default', '1')*/->with('state','country','city');
    }

    public function account_licenses()
    {
        return $this->hasMany(AccountLicense::class);
    }

    public function account_payments()
    {
        return $this->hasMany(AccountPayment::class);
    }

    public function account_contact()
    {
        return $this->hasOne(AccountContact::class)->with('gender');
    }

    public function account_maritial_status()
    {
        return $this->hasOne(MaritalStatus::class);
    }

    public function account_blood_group()
    {
        return $this->hasOne(BloodGroup::class);
    }

       static public function getAccount($where = [], $whereIn = [])
    {
        $query = self::select([
                    'accounts.id',
                    'accounts.code',
                    'genders.name as gender',
                    'account_contacts.phone_no as phone_no',
                    'account_contacts.address as address',
                    'account_contacts.city_id as city_id',
                    'account_contacts.email as email',
                    \DB::raw("CONCAT(
                                COALESCE(account_titles.name, ''),
                                CASE WHEN account_titles.name IS NOT NULL AND accounts.name IS NOT NULL THEN ' ' ELSE '' END,
                                COALESCE(accounts.name, '')
                            ) AS name"),
                    \DB::raw("TIMESTAMPDIFF(YEAR, account_contacts.date_of_birth, CURDATE()) as age")
                ])
                ->leftJoin('account_contacts', 'account_contacts.account_id', '=', 'accounts.id')
                ->leftJoin('account_titles', 'account_titles.id', '=', 'accounts.account_title_id')
                ->leftJoin('genders', 'genders.id', '=', 'account_contacts.account_gender_id')
                ->leftjoin('account_types','account_types.id','accounts.account_type_id')
                ->where($where)
                ->when(!empty($whereIn), function($query) use ($whereIn) {
                    $query->whereIn('account_types.type_code', $whereIn);
                })
                ->first();

        return $query;
    }
    public function account_title()
    {
        return $this->belongsTo(AccountTitle::class);
    }

    public function customer_account_addresses()
    {
        return $this->hasOne(AccountAddress::class)->with('state');
    }

    static public function getReferralVoucherDoctors($where = [], $filters = [])
    {
        $start_date = $filters['start_date'];
        $end_date   = $filters['end_date'];

        $data = self::select([
                'accounts.id',
                'accounts.name',
                'accounts.code',
            ])
            ->leftjoin('sale_invoices','sale_invoices.doctor_id','accounts.id')
            ->leftjoin('sale_invoice_details','sale_invoice_details.sale_invoice_id','sale_invoices.id')
            ->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)
            ->groupBy('accounts.code')
            ->orderBy('accounts.name' , 'ASC')
            ->whereNotNull('accounts.id')
            ->get();

        return $data;
    }

    static public function getReferralVoucherLabs($where = [], $filters = [])
    {
        $start_date = $filters['start_date'];
        $end_date   = $filters['end_date'];

        $data = self::select([
                'accounts.id',
                'account_contacts.organization'
            ])
            ->leftjoin('sale_invoices','sale_invoices.refferal_lab_id','accounts.id')
            ->leftjoin('account_contacts','account_contacts.account_id','accounts.id')
            ->leftjoin('sale_invoice_details','sale_invoice_details.sale_invoice_id','sale_invoices.id')
            ->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)
            ->groupBy('account_contacts.organization')
            ->orderBy('account_contacts.organization' , 'ASC')
            ->whereNotNull('accounts.id')
            ->get();

        return $data;
    }


    static public function getReferralVoucherAgents($where = [], $filters = [])
    {
        $start_date = $filters['start_date'];
        $end_date   = $filters['end_date'];

        $data = self::select([
                'accounts.id',
                'accounts.name',
                'accounts.code',
            ])
            ->leftjoin('sale_invoices','sale_invoices.collection_agent_id','accounts.id')
            ->leftjoin('sale_invoice_details','sale_invoice_details.sale_invoice_id','sale_invoices.id')
            ->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)
            ->groupBy('accounts.code')
            ->orderBy('accounts.name' , 'ASC')
            ->whereNotNull('accounts.id')
            ->get();

        return $data;
    }


    public function account_relative()
    {
        return $this->hasOne(AccountRelativesDetail::class,'account_id','id');
    }

}
