<?php

namespace App\Models;

use App\Main\Attrs\Fillable\CoaTransactionTypeAttr;
use App\Main\Attrs\Fillable\ReportCoaFilterAttr;
use App\Traits\SyncModelBoot;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class ChartOfAccount extends Model
{
    use SyncModelBoot;

    protected $guarded = ['id'];

    protected static function withCityBranchId()
    {
        return false;
    }

    protected static function withSiteProjectId()
    {
        return false;
    }

    public function scopeWhereImportId($q, $id, $serviceCategoryId)
    {
        return $q->where([
            ['import_id', $id],
            ['service_category_id', $serviceCategoryId]
        ]);
    }

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

    public function childs()
    {
        return $this->hasMany(ChartOfAccount::class)->orderBy('code', 'asc');
    }

    public function scopeRecursiveChilds($q)
    {
        return $q->with(['childs' => function ($q) {
            return $q->recursiveChilds()->with(['parent', 'masterCoa:id,name,code,debit,credit,category']);
        }, 'masterCoa:id,name,code,debit,credit,category']);
    }

    public function scopeRecursiveChildsTransactionAmount($chartOfAccount, ?CoaTransactionTypeAttr $coaTransactionTypeAttr, ReportCoaFilterAttr $reportCoaFilterAttr)
    {
        return $chartOfAccount->with(['childs' => function ($q) use ($coaTransactionTypeAttr, $reportCoaFilterAttr) {
            return $q->recursiveChildsTransactionAmount($coaTransactionTypeAttr, $reportCoaFilterAttr);
        }])->withTransactionAmount($coaTransactionTypeAttr, $reportCoaFilterAttr);
    }

    /**
     * Menjumlahkan total di relasi tabel tax_deducated
     *
     * @param ReportCoaFilterAttr $reportCoaFilterAttr
     * @return string
     */
    private function sumTotalTaxDeducatedRawQuery(ReportCoaFilterAttr $reportCoaFilterAttr)
    {
        $query = "SELECT COALESCE(SUM(CAST(amount AS SIGNED)),0)
            FROM tax_deducated WHERE tax_deducated.chart_of_account_id = chart_of_accounts.id
        ";

        if ($lessDate = $reportCoaFilterAttr->getLessDate()) {
            $query .= " AND (
                date(
                    (
                        SELECT `payment_date` FROM payments
                        WHERE payments.id = tax_deducated.payment_id
                    )
                ) <= '$lessDate'
                OR
                date(
                    (
                        SELECT `expense_date` FROM expenses
                        WHERE expenses.id = tax_deducated.expense_id
                    )
                ) <= '$lessDate'
            )";
        } elseif ($betweenTwoDates = $reportCoaFilterAttr->getBetweenTwoDates()) {
            if ($betweenTwoDates[0] ?? "" and $betweenTwoDates[1] ?? "") {
                $query .= " AND
                (
                    date(
                        (
                            SELECT `payment_date` FROM payments
                            WHERE payments.id = tax_deducated.payment_id
                        )
                    ) BETWEEN '$betweenTwoDates[0]' AND '$betweenTwoDates[1]'
                    OR
                    date(
                        (
                            SELECT `expense_date` FROM expenses
                            WHERE expenses.id = tax_deducated.expense_id
                        )
                    ) BETWEEN '$betweenTwoDates[0]' AND '$betweenTwoDates[1]'
                )";
            }
        }

        return $query;
    }

    /**
     * menjumlahkan total transaksi pada "item_sale_order"
     * berdasarkan "info penjualan akun" di "produk"
     *
     * @param ReportCoaFilterAttr $reportCoaFilterAttr
     * @return string
     */
    private function sumTotalItemSaleOrderRawQuery(ReportCoaFilterAttr $reportCoaFilterAttr)
    {
        $query = "SELECT COALESCE(SUM(CAST(subtotal AS SIGNED)),0)
            FROM item_sale_order
            WHERE EXISTS (
                SELECT * FROM item_sale_infos
                WHERE item_sale_infos.chart_of_account_id = chart_of_accounts.id
                AND item_sale_infos.item_id = item_sale_order.item_id
            )
        ";

        if ($lessDate = $reportCoaFilterAttr->getLessDate()) {
            $query .= " AND date(
                (
                    SELECT `transaction_date` FROM sale_orders
                    WHERE sale_orders.id = item_sale_order.sale_order_id
                )
            ) <= '$lessDate'";
        } elseif ($betweenTwoDates = $reportCoaFilterAttr->getBetweenTwoDates()) {
            if ($betweenTwoDates[0] ?? "" and $betweenTwoDates[1] ?? "") {
                $query .= " AND date(
                    (
                        SELECT `transaction_date` FROM sale_orders
                        WHERE sale_orders.id = item_sale_order.sale_order_id
                    )
                ) BETWEEN '$betweenTwoDates[0]' AND '$betweenTwoDates[1]'";
            }
        }

        return $query;
    }

    /**
     * menjumlahkan total transaksi pada "item_purchase_order"
     * berdasarkan "info pembelian akun" di "produk"
     *
     * @param ReportCoaFilterAttr $reportCoaFilterAttr
     * @return string
     */
    private function sumTotalItemPurchaseOrderRawQuery(ReportCoaFilterAttr $reportCoaFilterAttr)
    {
        $query = "SELECT COALESCE(SUM(CAST(subtotal AS SIGNED)),0)
            FROM item_purchase_order
            WHERE EXISTS (
                SELECT * FROM item_purchase_infos
                WHERE item_purchase_infos.chart_of_account_id = chart_of_accounts.id
                AND item_purchase_infos.item_id = item_purchase_order.item_id
            )
        ";

        if ($lessDate = $reportCoaFilterAttr->getLessDate()) {
            $query .= " AND date(
                (
                    SELECT `transaction_date` FROM purchase_orders
                    WHERE purchase_orders.id = item_purchase_order.purchase_order_id
                )
            ) <= '$lessDate'";
        } elseif ($betweenTwoDates = $reportCoaFilterAttr->getBetweenTwoDates()) {
            if ($betweenTwoDates[0] ?? "" and $betweenTwoDates[1] ?? "") {
                $query .= " AND date(
                    (
                        SELECT `transaction_date` FROM purchase_orders
                        WHERE purchase_orders.id = item_purchase_order.purchase_order_id
                    )
                ) BETWEEN '$betweenTwoDates[0]' AND '$betweenTwoDates[1]'";
            }
        }

        return $query;
    }

    /**
     * menjumlahkan total transaksi pengeluaran
     * di filter berdasarkan chart_of_account_id pada tabel
     * payment. lihat trigger "expense_create/update_triggger"
     *
     * @param ReportCoaFilterAttr $reportCoaFilterAttr
     * @return string
     */
    private function sumTotalExpenseRawQuery(ReportCoaFilterAttr $reportCoaFilterAttr)
    {
        $query = $this->sumTotalInTransactionRawQuery($reportCoaFilterAttr);

        $query .= " AND expense_id is not null";

        return $query;
    }


    /**
     * menjumlahkan total transaksi pengeluaran
     * di filter berdasarkan chart_of_account_id pada tabel
     * payment. lihat trigger "manual_journal_create/update_triggger"
     *
     * @param ReportCoaFilterAttr $reportCoaFilterAttr
     * @return string
     */
    private function sumTotalManualJournalRawQuery(ReportCoaFilterAttr $reportCoaFilterAttr)
    {
        $query = $this->sumTotalInTransactionRawQuery($reportCoaFilterAttr);

        $query .= " AND chart_of_account_manual_journal_id is not null";

        return $query;
    }

    /**
     * menjumlahkan total (deposit - withdrawls) transaksi
     *
     * @param ReportCoaFilterAttr $reportCoaFilterAttr
     * @return string
     */
    private function sumTotalInTransactionRawQuery(ReportCoaFilterAttr $reportCoaFilterAttr)
    {
        $query = "SELECT COALESCE(CAST(ABS(SUM(deposit) - SUM(withdrawls)) AS SIGNED), 0)
            FROM chart_of_account_transactions
            WHERE chart_of_account_transactions.chart_of_account_id = chart_of_accounts.id";

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

        return $query;
    }

    /**
     * total ongkir pada sale_order
     *
     * @param ReportCoaFilterAttr $reportCoaFilterAttr
     * @return void
     */
    private function sumPostalFeeRawQuery(ReportCoaFilterAttr $reportCoaFilterAttr)
    {
        $query = "SELECT COALESCE((CAST(SUM(postal_fee_price) AS SIGNED)), 0)
            FROM sale_orders
            WHERE sale_orders.postal_fee_account_id = chart_of_accounts.id";

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

        return $query;
    }

    /**
     * total discount pada sale_order
     *
     * @param ReportCoaFilterAttr $reportCoaFilterAttr
     * @return void
     */
    private function sumDiscountRawQuery(ReportCoaFilterAttr $reportCoaFilterAttr)
    {
        $query = "SELECT COALESCE((CAST(SUM(discount) AS SIGNED)) * -1, 0)
            FROM sale_orders
            WHERE sale_orders.discount_account_id = chart_of_accounts.id";

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

        return $query;
    }

    /**
     * total asset pada sale_order
     *
     * @param ReportCoaFilterAttr $reportCoaFilterAttr
     * @return void
     */
    private function sumAssetRawQuery(ReportCoaFilterAttr $reportCoaFilterAttr)
    {
        $assetDbName = DB::connection('mysql_aset')->getDatabaseName();

        $query = "SELECT COALESCE((CAST(SUM(harga) AS SIGNED)) * -1, 0)
            FROM {$assetDbName}.assets
            WHERE {$assetDbName}.assets.coa_id = chart_of_accounts.id";

        return $query;
    }

    public function scopeWithTransactionAmount($chartOfAccount, ?CoaTransactionTypeAttr $coaTransactionTypeAttr, ReportCoaFilterAttr $reportCoaFilterAttr)
    {
        $sumQuery = "(";

        $sumQuery .= "(COALESCE(chart_of_accounts.debits,0) - COALESCE(chart_of_accounts.credits,0))";


        if ($coaTransactionTypeAttr) {
            if (!is_array($reportCoaFilterAttr->getType()) and strtolower($reportCoaFilterAttr->getType()) === "fixed asset") {
                $sumQuery .= "+({$this->sumAssetRawQuery($reportCoaFilterAttr)})";
            } else {
                // ongkir order
                $sumQuery .= "+({$this->sumPostalFeeRawQuery($reportCoaFilterAttr)})";

                // diskon order
                $sumQuery .= "+({$this->sumDiscountRawQuery($reportCoaFilterAttr)})";
                if ($coaTransactionTypeAttr->getManualJournal()) {
                    $sumQuery .= "+({$this->sumTotalManualJournalRawQuery($reportCoaFilterAttr)})";
                }

                if ($coaTransactionTypeAttr->getExpense()) {
                    $sumQuery .= "+({$this->sumTotalExpenseRawQuery($reportCoaFilterAttr)})";
                }

                if ($coaTransactionTypeAttr->getPurchase()) {
                    $sumQuery .= "+({$this->sumTotalItemPurchaseOrderRawQuery($reportCoaFilterAttr)})";
                }

                if ($coaTransactionTypeAttr->getSale()) {
                    $sumQuery .= "+({$this->sumTotalItemSaleOrderRawQuery($reportCoaFilterAttr)})";
                }

                if ($coaTransactionTypeAttr->getTaxDeducated()) {
                    $sumQuery .= "+({$this->sumTotalTaxDeducatedRawQuery($reportCoaFilterAttr)})";
                }
            }
        } // ini harusnya dari "CASH" atau "BANK"
        else {
            $sumQuery .= "+({$this->sumTotalInTransactionRawQuery($reportCoaFilterAttr)})";
        }


        $sumQuery .= ")";

        return $chartOfAccount->selectRaw("
            id,
            chart_of_account_id,
            name,
            type,
            $sumQuery as amount
        ");
    }

    public function scopeWithTransactionAmountNew($chartOfAccount, ?CoaTransactionTypeAttr $coaTransactionTypeAttr, ReportCoaFilterAttr $reportCoaFilterAttr)
    {
        $sumQuery = "(";

        $sumQuery .= "(COALESCE(master_coas.debits,0) - COALESCE(master_coas.credits,0))";

        if ($coaTransactionTypeAttr) {
            if (!is_array($reportCoaFilterAttr->getMasterCoaId()) and $reportCoaFilterAttr->getMasterCoaId() === "1-200.000") { //ini akan pake id masterCoa yang Fixed Aset (Aktiva Tetap)
                $sumQuery .= "+({$this->sumAssetRawQuery($reportCoaFilterAttr)})";
            } else {
                // ongkir order
                $sumQuery .= "+({$this->sumPostalFeeRawQuery($reportCoaFilterAttr)})";

                // diskon order
                $sumQuery .= "+({$this->sumDiscountRawQuery($reportCoaFilterAttr)})";
                if ($coaTransactionTypeAttr->getManualJournal()) {
                    $sumQuery .= "+({$this->sumTotalManualJournalRawQuery($reportCoaFilterAttr)})";
                }

                if ($coaTransactionTypeAttr->getExpense()) {
                    $sumQuery .= "+({$this->sumTotalExpenseRawQuery($reportCoaFilterAttr)})";
                }

                if ($coaTransactionTypeAttr->getPurchase()) {
                    $sumQuery .= "+({$this->sumTotalItemPurchaseOrderRawQuery($reportCoaFilterAttr)})";
                }

                if ($coaTransactionTypeAttr->getSale()) {
                    $sumQuery .= "+({$this->sumTotalItemSaleOrderRawQuery($reportCoaFilterAttr)})";
                }

                if ($coaTransactionTypeAttr->getTaxDeducated()) {
                    $sumQuery .= "+({$this->sumTotalTaxDeducatedRawQuery($reportCoaFilterAttr)})";
                }
            }
        } else {
            $sumQuery .= "+({$this->sumTotalInTransactionRawQuery($reportCoaFilterAttr)})";
        }

        $sumQuery .= ")";

        return $chartOfAccount->join('master_coas', 'chart_of_accounts.master_coa_id', '=', 'master_coas.id')
            ->selectRaw("
            chart_of_accounts.id,
            chart_of_accounts.master_coa_id,
            master_coas.name as type,
            $sumQuery as amount
        ");
    }

    public function scopeWhereDoesntHaveParent($q)
    {
        return $q->whereDoesntHave('parent');
    }

    public function scopeSearch($prevBuilder, $keyword): Builder
    {
        return $prevBuilder
            ->where(function ($q) use ($keyword) {
                $q->where(
                    'name',
                    'like',
                    "%$keyword%"
                )->orWhereHas('masterCoa', function ($q) use ($keyword) {
                    $q->where(
                        'name',
                        'like',
                        "%$keyword%"
                    );
                })->orWhere(
                    'code',
                    'like',
                    "%$keyword%"
                );
            });
    }

    public function masterCoa()
    {
        return $this->belongsTo(MasterCoa::class);
    }

    public function services() //Invoice Services (buat dapetin Layanan Jasa di Master yang sudah di Plot)
    {
        return $this->hasMany(Service::class);
    }

    public function payments()
    {
        return $this->hasMany(Payment::class, 'chart_of_account_id');
    }
}