<?php

namespace App\Models;

use App\Main\Attrs\Fillable\ReportCoaFilterAttr;
use App\Pivots\ItemPurchaseOrder;
use App\Traits\SyncModelBoot;
use Illuminate\Database\Eloquent\Builder;
use Hoyvoy\CrossDatabase\Eloquent\Model;

class PurchaseOrder extends Model
{
    use SyncModelBoot;

    protected $paymentPivotTable = 'payment_details';

    protected $guarded = ['id'];

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

    protected $hidden = ['status'];

    public function purchaseOrderVendors()
    {
        return $this->hasMany(PurchaseOrderVendor::class);
    }

    public function purchaseOrderMaterials()
    {
        return $this->hasMany(PurchaseOrderMaterial::class);
    }

    public function purchaseOrderManuals() //umtuk project
    {
        return $this->hasMany(PurchaseOrderManual::class);
    }

    public function purchaseRequest()
    {
        return $this->belongsTo(PurchaseRequest::class);
    }

    public function project()
    {
        return $this->belongsTo(Project::class);
    }

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

    public function supplier()
    {
        return $this->belongsTo(Supplier::class);
    }

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

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

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

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

    public function bill()
    {
        return $this->hasOne(Bill::class);
    }

    public function scopeSearch($q, $keyword)
    {
        return $q->where(function ($q) use ($keyword) {
            return $q->where('no_po', 'like', "%$keyword%")
                ->orWhereHas('supplier', 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('received')->whereRaw("
                (
                    COALESCE (
                        (
                            SELECT CAST(
                                SUM(subtotal) AS SIGNED
                            ) FROM item_purchase_order

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

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

    public function scopeUnpaidForProject($q, $exceptPaymentId = null)
    {
        return $q->where(function ($q) use ($exceptPaymentId) {
            return $q->whereStatus('received');
        })
            ->where('reference', 'po_project')
            // yang tidak mempunyai bill paid
            ->whereDoesntHave('bill', function ($q) {
                return $q->paid();
            });
    }

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

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

        return $q->selectRaw("
                CAST((
                    COALESCE(
                        SUM(
                                (
                                    (
                                        COALESCE (
                                            (
                                                SELECT CAST(
                                                    SUM(subtotal) AS SIGNED
                                                ) FROM item_purchase_order

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

                                                WHERE (
                                                    purchase_order_id=id
                                                    or bill_id = (
                                                        SELECT id FROM bills
                                                        WHERE purchase_order_id = purchase_orders.id
                                                        limit 1
                                                    )
                                                ) {$addonDateQueryPayment}
                                            )
                                        , 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_purchase_order

                            WHERE purchase_order_id=id
                            AND tax_rate > 0
                       )
                    ,0)
                ) AS item_tax
            ");
    }

    public function getAmountAttribute()
    {
        // return $this->items->sum('pivot.subtotal'); // ori
        return $this->items
            ->filter(fn($item) => is_numeric($item->pivot->subtotal)) // Memastikan nilai numerik
            ->sum(fn($item) => (float) $item->pivot->subtotal);
    }

    public function getAmountProjectAttribute()
    {
        $sum = 0;

        // Daftar relasi yang membutuhkan penjumlahan harga dikali qty dari tabel detail.
        $relations = [
            'purchaseOrderMaterials' => 'purchaseOrderMaterialDetails',
            'purchaseOrderManuals' => 'purchaseOrderManualDetails',
            'purchaseOrderVendors' => 'purchaseOrderVendorDetails',
            'purchaseOrderVendorManuals' => 'purchaseOrderVendorManualDetails',
            'purchaseOrderOtherCostManuals' => 'purchaseOrderOtherCostManualDetails',
            'purchaseOrderOtherCosts' => 'purchaseOrderOtherCostDetails',
            'purchaseOrderMachines' => 'purchaseOrderMachineDetails',
            'purchaseOrderMachineManuals' => 'purchaseOrderMachineManualDetails'
        ];

        // Iterasi setiap relasi untuk menjumlahkan harga dikali qty dari tabel detail.
        foreach ($relations as $relation => $detailsRelation) {
            $relationData = $this->{$relation}();
            if ($relationData->count() > 0) {
                $relationData->each(function ($item) use (&$sum, $detailsRelation) {
                    // Ambil detail yang terkait dan hitung total berdasarkan qty dan harga
                    $qty = $item->{$detailsRelation}->sum(fn($detail) => (float)$detail->qty); // Ambil qty dari detail
                    $sum += $item->price * $qty;  // Kalikan price dengan total qty
                });
            }
        }

        return $sum;
    }


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

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

    // public function getAmountDueProjectAttribute()
    // {
    //     return $this->amount_project - $this->paymentAmount;
    // }

    public function getCurrentStatusAttribute()
    {
        if ($this->bill) {
            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;
    }

    public function additionalCosts()
    {
        return $this->belongsToMany(AdditionalCost::class, 'additional_cost_purchase_order_prices')
            ->withPivot('additional_cost_id', 'purchase_order_id', 'price');
    }

    public function purchaseOrderOtherCosts()
    {
        return $this->hasMany(PurchaseOrderOtherCost::class);
    }

    public function purchaseOrderOtherCostManuals()
    {
        return $this->hasMany(PurchaseOrderOtherCostManual::class);
    }

    public function purchaseOrderMachines()
    {
        return $this->hasMany(PurchaseOrderMachine::class);
    }

    public function purchaseOrderMachineManuals()
    {
        return $this->hasMany(PurchaseOrderMachineManual::class);
    }

    public function purchaseOrderVendorManuals()
    {
        return $this->hasMany(PurchaseOrderVendorManual::class);
    }
}