<?php

namespace App\Models;

use App\Main\Attrs\Fillable\ReportCoaFilterAttr;
use App\Traits\SyncModelBoot;
use Illuminate\Database\Eloquent\Model;

class LabourOrder extends Model
{
    use SyncModelBoot;

    protected $paymentPivotTable = 'payment_details';

    protected $guarded = ['id'];

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

    protected $hidden = ['status'];

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

    public function supplier() // Update 2024: ga ada sambungan ke supplier, semua labourOrder sambungan ke proyek yang memiliki craftMan
    {
        return $this->belongsTo(Supplier::class)->select('id', 'name');
    }

    public function labourOrderWorker()
    {
        return $this->hasMany(LabourOrderWorker::class);
    }

    public function foreman()
    {
        return $this->setConnection('mysql_proj')->hasOne(User::class, 'id', 'foreman_id')->select('id', 'name');
    }

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

    public function billLo()
    {
        return $this->hasOne(BillLabourOrder::class, 'lo_id');
    }

    public function getAmountAttribute()
    {
        return $this->labourOrderWorker->sum('total_wages');
    }

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

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

    public function getCurrentStatusAttribute()
    {
        if ($this->billLo) {
            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 scopeSearch($q, $keyword)
    {
        return $q->where(function ($q) use ($keyword) {
            return $q->where('lo_number', 'like', "%$keyword%")
                ->orWhereHas('foreman', function ($q) use ($keyword) {
                    return $q->search($keyword);
                });
        });
    }

    // TODO: Udah di update tapi blm di test
    /**
     * @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(2)->whereRaw("
                (
                    COALESCE (
                        (
                            SELECT CAST(
                                SUM(total_wages) AS SIGNED
                            ) FROM labour_order_workers

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

                            WHERE labour_order_id = id
                            " . ($exceptPaymentId ? "AND payment_id != {$exceptPaymentId}" : "") . "
                        )
                    , 0)
                )
            ");
        })
            ->whereDoesntHave('billLo', function ($q) {
                return $q->paid();
            });
    }

    // TODO: Udah di update tapi blm di test
    public function scopeUnpaidForProject($q, $exceptPaymentId = null)
    {
        return $q->where(function ($q) use ($exceptPaymentId) {
            return $q->whereStatus(2);
        })
            ->whereNotNull('project_id')
            // yang tidak mempunyai billLo paid
            ->whereDoesntHave('billLo', function ($q) {
                return $q->paid();
            });
    }

    // TODO: Udah di update tapi blm di test
    public function scopeSumTotalAmountDue($q, ?ReportCoaFilterAttr $reportCoaFilterAttr)
    {
        // dd($this->paymentPivotTable);
        $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(total_wages) AS SIGNED
                                    ) FROM labour_order_workers

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

                                    WHERE (
                                        labour_order_id = id
                                        OR labour_order_id = (
                                            SELECT lo_id FROM bill_labour_orders
                                            WHERE lo_id = labour_orders.id
                                            LIMIT 1
                                        )
                                    ) {$addonDateQueryPayment}
                                )
                            , 0)
                        )
                    )
                , 0)
            ) AS SIGNED) AS total_amount_due
        ");

        // 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
        //     ");
    }
}