<?php

namespace App\Models;

use App\Main\Attrs\Fillable\ReportCoaFilterAttr;
use App\Pivots\ItemSaleOrder;
use App\Traits\SyncModelBoot;
use Hoyvoy\CrossDatabase\Eloquent\Model;

class SaleOrder extends Model
{
    use SyncModelBoot;
    
    protected $paymentPivotTable = 'payment_details';

    protected $guarded = ['id'];

    protected $appends = ['amount', 'billed_amount', 'payment_amount', 'amount_due', 'current_status', 'customer_name'];
    // protected $appends = ['payment_amount', 'amount_due', 'current_status'];

    protected $hidden = ['status'];

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

    protected static function writeCustomerChatLog()
    {
        return true;
    }

    public function getBilledAmountAttribute() {
        return Invoice::selectRaw('
            COALESCE((
                SELECT sum(total_bill) FROM '.config('database.connections.mysql.database').'.invoices
                WHERE sale_order_id = '.$this->id.'
            ),0) as billed_amount
        ')->first()->billed_amount ?? 0;
    }

    public function items()
    {
        return $this->belongsToMany(Item::class)
            ->using(ItemSaleOrder::class)
            ->withPivot('description', 'rate', 'tax_id', 'tax_rate', 'qty', 'subtotal', 'sign', 'tax_value');
    }

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

    public function getCustomerNameAttribute()
    {
        return ($this->customer->name ?? "") . " - " . ($this->customer->customerPhone->phone_number ?? "");
    }

    public function warehouse()
    {
        return $this->belongsTo(Warehouse::class);
    }

    public function priceList()
    {
        return $this->belongsTo(PriceList::class);
    }

    public function discountAccount()
    {
        return $this->belongsTo(ChartOfAccount::class, 'discount_account_id');
    }

    public function postalFeeAccount()
    {
        return $this->belongsTo(ChartOfAccount::class, 'postal_fee_account_id');
    }

    public function chartOfAccount()
    {
        return $this->belongsTo(ChartOfAccount::class);
    }

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

    public function invoice()
    {
        return $this->hasOne(Invoice::class);
    }

    public function customerAddress()
    {
        return $this->belongsTo(CustomerAddress::class);
    }

    public function scopeSearch($q, $keyword)
    {
        return $q->where(function ($q) use ($keyword) {
            $q->where('no_so', '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 item_sale_order

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

                            WHERE sale_order_id=id
                            ". ($exceptPaymentId ? "AND payment_id != {$exceptPaymentId}" : "") ."
                        )
                    , 0)
                )
            ");
        })
        // yang tidak mempunyai invoice paid
        ->whereDoesntHave('invoice', function ($q) {
            return $q->paid();
        });
    }

    public function scopeSumTotalAmountDue($q, ?ReportCoaFilterAttr $reportCoaFilterAttr)
    {
        $addonDateQueryPayment = "";
        $addonDateQueryRetainer = "";

        if ($reportCoaFilterAttr) {
            if ($lessDate = $reportCoaFilterAttr->getLessDate()) {
                $addonDateQueryPayment = " AND transaction_date <= '$lessDate'";
                $addonDateQueryRetainer = " AND DATE(invoice_retainer_invoice.created_at) <= '$lessDate'";
            } elseif ($betweenTwoDates = $reportCoaFilterAttr->getBetweenTwoDates()) {
                if ($betweenTwoDates[0] ?? "" and $betweenTwoDates[1] ?? "") {
                    $addonDateQueryPayment = " AND transaction_date BETWEEN '$betweenTwoDates[0]' AND '$betweenTwoDates[1]'";
                    $addonDateQueryRetainer = " AND DATE(created_at) BETWEEN '$betweenTwoDates[0]' AND '$betweenTwoDates[1]' ";
                }
            }
        }
        

        return $q->selectRaw("  
                CAST((
                    COALESCE(
                        SUM(
                                (
                                    (
                                        COALESCE (
                                            (
                                                SELECT CAST(
                                                    SUM(subtotal) AS SIGNED
                                                ) FROM item_sale_order
                    
                                                WHERE sale_order_id=id
                                            )
                                        , 0)
                                        -
                                        COALESCE(discount, 0)
                                        +
                                        COALESCE(postal_fee_price, 0)
                                    )
                                    -
                                    (
                                        COALESCE(
                                            (
                                                SELECT CAST(
                                                    SUM(payment_amount) AS SIGNED
                                                ) FROM {$this->paymentPivotTable}

                                                WHERE (
                                                    sale_order_id=id
                                                    OR invoice_id = (
                                                        SELECT id FROM invoices
                                                        WHERE sale_order_id = sale_orders.id
                                                        limit 1
                                                    )
                                                ) {$addonDateQueryPayment}
                                            )
                                        , 0)
                                    )
                                    -
                                    (
                                        COALESCE(
                                            (
                                                SELECT CAST(
                                                    SUM(invoice_retainer_invoice.amount) AS SIGNED
                                                ) FROM invoice_retainer_invoice

                                                WHERE invoice_id = (
                                                    SELECT id FROM invoices
                                                    WHERE sale_order_id = sale_orders.id
                                                    limit 1
                                                )

                                                {$addonDateQueryRetainer}
                                            )
                                        , 0)
                                    )
                                )
                            ),0)
                ) AS SIGNED) AS total_amount_due
            ");
    }

    public function scopeSumItemTax($q)
    {
        return $q->selectRaw("  
                (
                    COALESCE(
                        (
                             SELECT CAST(
                                 SUM(rate * tax_rate/100) AS SIGNED
                             ) FROM item_sale_order
 
                             WHERE sale_order_id=id
                             AND tax_rate > 0
                        )
                     ,0)
                ) AS item_tax
            ");
    }

    public function getAmountAttribute()
    {
        $subtotal = 0;
        foreach ($this->items as $item) {
            if ($item->pivot->sign == "-") {
                $subtotal = $subtotal - (int) ($item->pivot->subtotal ?? 0);
            } else {
                $subtotal = $subtotal + (int) ($item->pivot->subtotal ?? 0);
            }
        }

        if ((int) $subtotal < 0) {
            return (int) $subtotal + (int) $this->discount - (int) $this->postal_fee_price;
        } else {
            return (int) $subtotal - (int) $this->discount + (int) $this->postal_fee_price;
        }


        // return $this->items->sum('pivot.subtotal') - (int) $this->discount + (int) $this->postal_fee_price;
    }

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

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

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

        return $this->status;
    }
}