<?php

namespace App\Models;

use App\Traits\SyncModelBoot;
use Illuminate\Database\Eloquent\Model;

class RetainerInvoice extends Model
{
    use SyncModelBoot;
    
    protected $detailTable = "retainer_invoice_details";
    protected $paymentPivotTable = 'payment_details';

    protected $guarded = ['id'];

    protected $appends = ['amount', 'available_amount', 'payment_amount', 'applied_amount', 'amount_due', 'current_status'];

    protected $hidden = ['status'];

    protected static function customerLogAs($model)
    {
        return 'retainer_invoice';
    }

    protected static function writeCustomerChatLog()
    {
        return true;
    }

    public function customer()
    {
        return $this->belongsTo(Customer::class);
    }
    
    public function details()
    {
        return $this->hasMany(RetainerInvoiceDetail::class);
    }

    public function payments()
    {
        return $this->belongsToMany(Payment::class, $this->paymentPivotTable)->withPivot('payment_amount');
    }

    public function invoices()
    {
        return $this->belongsToMany(Invoice::class)->withPivot('amount');
    }

    public function scopeSearch($q, $keyword)
    {
        return $q->where(function ($q) use ($keyword) {
            return $q->where('no_invoice', 'like', "%$keyword%")
                    ->orWhereHas('customer', function ($q) use ($keyword) {
                        return $q->search($keyword);
                    });
        });
    }
    
    /**
     * @param Builder $q
     * @param mixed|null $exceptPaymentId menjumlahkan total payment kecuali id yang dipassing
     * @return Builder
     */
    public function scopeUnpaid($q, $exceptPaymentId = null)
    {
        return $q->where(function ($q) use ($exceptPaymentId) {
            return $q->whereStatus('sent')->whereRaw(
                "
                (
                    COALESCE (
                        (
                            SELECT CAST(
                                SUM(subtotal) AS SIGNED
                            ) FROM {$this->detailTable}

                            WHERE retainer_invoice_id=id
                        )
                    , 0)
                )
                >
                (
                    COALESCE (
                        (
                            SELECT CAST(
                                SUM(payment_amount) AS SIGNED
                            ) FROM {$this->paymentPivotTable}

                            WHERE retainer_invoice_id=id
                            ". ($exceptPaymentId ? "AND payment_id != {$exceptPaymentId}" : "") ."
                        )
                    , 0)
                )
            "
            );
        });
    }

    /**
    * @param Builder $q
    * @param mixed|null $exceptPaymentId menjumlahkan total payment kecuali id yang dipassing
    * @return Builder
    */
    public function scopePaid($q, $exceptPaymentId = null)
    {
        return $q->where(function ($q) use ($exceptPaymentId) {
            return $q->whereStatus('sent')->whereRaw(
                "
                (
                    COALESCE (
                        (
                            SELECT CAST(
                                SUM(subtotal) AS SIGNED
                            ) FROM {$this->detailTable}

                            WHERE retainer_invoice_id=id
                        )
                    , 0)
                )
                <=
                (
                    COALESCE (
                        (
                            SELECT CAST(
                                SUM(payment_amount) AS SIGNED
                            ) FROM {$this->paymentPivotTable}

                            WHERE retainer_invoice_id=id
                            ". ($exceptPaymentId ? "AND payment_id != {$exceptPaymentId}" : "") ."
                        )
                    , 0)
                )
            "
            );
        });
    }

    /**
    * @param Builder $q
    * @return Builder
    */
    public function scopeSumTotalAmountDue($q)
    {
        return $q->selectRaw(
            "  
                (
                    COALESCE(
                        SUM(
                                (
                                    COALESCE (
                                        (
                                            SELECT CAST(
                                                SUM(subtotal) AS SIGNED
                                            ) FROM {$this->detailTable}
                
                                            WHERE retainer_invoice_id=id
                                        )
                                    , 0) 
                                    -
                                    COALESCE(
                                        (
                                            SELECT CAST(
                                                SUM(payment_amount) AS SIGNED
                                            ) FROM {$this->paymentPivotTable}

                                            WHERE retainer_invoice_id=id
                                        )
                                    , 0)
                                )
                            ),0)
                ) AS total_amount_due
            "
        );
    }

    public function getAmountAttribute()
    {
        return $this->details()->sum('subtotal');
    }

    public function getPaymentAmountAttribute()
    {
        return $this->payments()->sum("payment_amount");
    }

    public function getAppliedAmountAttribute()
    {
        return $this->invoices()->sum("invoice_retainer_invoice.amount");
    }

    public function getAmountDueAttribute()
    {
        return $this->amount - $this->paymentAmount;
    }

    public function getAvailableAmountAttribute()
    {
        return $this->amount - $this->appliedAmount;
    }

    public function getCurrentStatusAttribute()
    {
        if (count($this->payments) > 0) {
            if ($this->amount_due <= 0) {
                return "paid";
            } elseif ($this->amount_due > 0) {
                return "partially paid";
            }
        }

        return $this->status;
    }
}