<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use App\Models\Stocktransaction;
use App\Models\Substocktransaction;
use App\Models\Product;
use App\Models\Akun;
use App\Models\Cashtransaction;
use App\Models\Credit;
use App\Models\Contact;
use App\Models\CorporateIncomeTax;
use App\Models\CreditCashTransaction;
use App\Models\Language;
use App\Models\Licence;
use App\Models\Subcashtransaction;
use Carbon\Carbon;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class ReportController extends Controller
{
    public static function getActiveLanguage()
    {
        return Language::where('status', true)->first();
    }

    private function loadTypeMapping($langCode = 'id')
    {
        $jsonPath = config_path("lang/{$langCode}/labarugi.json");

        // Fallback ke bahasa Indonesia jika file tidak ditemukan
        if (!file_exists($jsonPath)) {
            $jsonPath = config_path('lang/id/labarugi.json');
        }

        if (!file_exists($jsonPath)) {
            return [];
        }

        return json_decode(file_get_contents($jsonPath), true) ?? [];
    }

    private function translateAccountName($accountName, $entityType, $namaMappingData)
    {
        if (!empty($entityType) && isset($namaMappingData[$entityType][$accountName])) {
            return $namaMappingData[$entityType][$accountName];
        }

        // Fallback ke translasi umum
        if (isset($namaMappingData['akun_umum'][$accountName])) {
            return $namaMappingData['akun_umum'][$accountName];
        }

        // Jika tidak ditemukan, kembalikan nama asli
        return $accountName;
    }

    function tambahNamaAkunWithTranslation(&$data, $entityType, $namaMappingData)
    {
        $translateNestedItems = function (&$items) use (&$translateNestedItems, $entityType, $namaMappingData) {
            foreach ($items as &$item) {
                $item->rename = $this->translateAccountName($item->name, $entityType, $namaMappingData);

                // Recursively translate children if they exist
                if (isset($item->children)) {
                    // Convert to collection if it's array
                    if (is_array($item->children)) {
                        $item->children = collect($item->children);
                    }

                    // Process children recursively
                    if ($item->children instanceof Collection && $item->children->isNotEmpty()) {
                        $translateNestedItems($item->children);
                    }
                }
            }
        };

        $translateNestedItems($data);
    }

    public function AkunReportLaba(Request $request)
    {
        $licence = Licence::first();
        $isYayasan = $licence && $licence->company_type === 'yayasan';

        Akun::whereNotNull('name')->update(array('total' => 0));

        // CREDIT STOCK MASUK = menghitung uang masuk dari stock
        $cash = Akun::withCount([
            'creditin as sum_stockin' => function ($credit) use ($request) {
                $credit->whereHas('stocktransaction', function ($stock) use ($request) {
                    $stock = $stock->whereNotNull('cashin_id')->whereNull('pending');
                    if (!empty($request->start_date) && !empty($request->end_date)) {
                        $request->start_date = date('Y-m-d', strtotime($request->start_date));
                        $request->end_date = date('Y-m-d', strtotime($request->end_date));
                        $stock = $stock->whereBetween('date', [$request->start_date, $request->end_date]);
                    } else {
                        $stock = $stock->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
                    }
                })->select(DB::raw("SUM(total)"));
            },
            // CREDIT STOCK KELUAR = menghitung uang keluar dari stock
            'creditout as sum_stockout' => function ($credit) use ($request) {
                $credit->whereHas('stocktransaction', function ($stock) use ($request) {
                    $stock = $stock->whereNotNull('cashout_id')->whereNull('pending');
                    if (!empty($request->start_date) && !empty($request->end_date)) {
                        $request->start_date = date('Y-m-d', strtotime($request->start_date));
                        $request->end_date = date('Y-m-d', strtotime($request->end_date));
                        $stock = $stock->whereBetween('date', [$request->start_date, $request->end_date]);
                    } else {
                        $stock = $stock->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
                    }
                })->select(DB::raw("SUM(total)"));
            },
            // CASH FROM = menghitung cash sebagai akun
            'cashtransactionfrom as sum_cashfrom' => function ($cash) use ($request) {
                if (!empty($request->start_date) && !empty($request->end_date)) {
                    $request->start_date = date('Y-m-d', strtotime($request->start_date));
                    $request->end_date = date('Y-m-d', strtotime($request->end_date));
                    $cash = $cash->whereBetween('date', [$request->start_date, $request->end_date]);
                } else {
                    $cash = $cash->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
                }
                $cash->select(DB::raw("SUM(cashout+transfer)"));
            },
            // CASH TO = menghitung cash sebagai akun
            'cashtransactionto as sum_cashto' => function ($cash) use ($request) {
                if (!empty($request->start_date) && !empty($request->end_date)) {
                    $request->start_date = date('Y-m-d', strtotime($request->start_date));
                    $request->end_date = date('Y-m-d', strtotime($request->end_date));
                    $cash = $cash->whereBetween('date', [$request->start_date, $request->end_date]);
                } else {
                    $cash = $cash->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
                }
                $cash->select(DB::raw("SUM(cashin+transfer)"));
            }
        ])->where('iscash', true)->get();

        foreach ($cash as $key => $value) {
            $value->total = ($value->sum_stockin - $value->sum_stockout) + ($value->sum_cashto - $value->sum_cashfrom);
        }
        $pendingCash = Akun::withCount([
            'creditin as sum_stockin' => function ($credit) use ($request) {
                $credit->whereHas('stocktransaction', function ($stock) use ($request) {
                    $stock = $stock->whereNotNull('cashin_id')->where('pending', 1);
                    if (!empty($request->start_date) && !empty($request->end_date)) {
                        $request->start_date = date('Y-m-d', strtotime($request->start_date));
                        $request->end_date = date('Y-m-d', strtotime($request->end_date));
                        $stock = $stock->whereBetween('date', [$request->start_date, $request->end_date]);
                    } else {
                        $stock = $stock->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
                    }
                })->select(DB::raw("SUM(total)"));
            },
            // CREDIT STOCK KELUAR = menghitung uang keluar dari stock
            'creditout as sum_stockout' => function ($credit) use ($request) {
                $credit->whereHas('stocktransaction', function ($stock) use ($request) {
                    $stock = $stock->whereNotNull('cashout_id')->where('pending', 1);
                    if (!empty($request->start_date) && !empty($request->end_date)) {
                        $request->start_date = date('Y-m-d', strtotime($request->start_date));
                        $request->end_date = date('Y-m-d', strtotime($request->end_date));
                        $stock = $stock->whereBetween('date', [$request->start_date, $request->end_date]);
                    } else {
                        $stock = $stock->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
                    }
                })->select(DB::raw("SUM(total)"));
            }
        ])->where('iscash', true)->get();

        foreach ($pendingCash as $key => $value) {
            $value->total = $value->sum_stockin - $value->sum_stockout;
        }
        // SUB CASH IN = menghitung cash sebagai akun
        $cashin = Akun::withCount(['subcashtransaction as sum_subcash' => function ($sub) use ($request) {
            $sub->select(DB::raw("SUM(total)"))->whereHas('cashtransaction', function ($cash) use ($request) {
                $cash->whereNotNull('to');
                if (!empty($request->start_date) && !empty($request->end_date)) {
                    $request->start_date = date('Y-m-d', strtotime($request->start_date));
                    $request->end_date = date('Y-m-d', strtotime($request->end_date));
                    $cash = $cash->whereBetween('date', [$request->start_date, $request->end_date]);
                } else {
                    $cash = $cash->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
                }
            });
        }])->get();

        foreach ($cashin as $key => $value) {
            $value->total = $value->sum_subcash;
        }
        // SUB CASH OUT
        $cashout = Akun::withCount(['subcashtransaction as sum_subcash' => function ($sub) use ($request) {
            $sub->select(DB::raw("SUM(total)"))->whereHas('cashtransaction', function ($cash) use ($request) {
                $cash->whereNotNull('from')->where('transaction_category', '<>', 'biaya_produksi_aktual');
                if (!empty($request->start_date) && !empty($request->end_date)) {
                    $request->start_date = date('Y-m-d', strtotime($request->start_date));
                    $request->end_date = date('Y-m-d', strtotime($request->end_date));
                    $cash = $cash->whereBetween('date', [$request->start_date, $request->end_date]);
                } else {
                    $cash = $cash->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
                }
            });
        }])->get();

        foreach ($cashout as $key => $value) {
            $value->total = $value->sum_subcash;
        }

        // PENDAPATAN
        $jasa = Substocktransaction::whereHas('product', function ($product) {
            $product->where('category', 'service');
        })->whereHas('stocktransaction', function ($stock) use ($request) {
            $stock = $stock->whereNotNull('cashin_id')->whereNull('pending');
            if (!empty($request->start_date) && !empty($request->end_date)) {
                $request->start_date = date('Y-m-d', strtotime($request->start_date));
                $request->end_date = date('Y-m-d', strtotime($request->end_date));
                $stock = $stock->whereBetween('date', [$request->start_date, $request->end_date]);
            } else {
                $stock = $stock->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
            }
        })->sum('total');

        // penjualan 
        $penjualan = Substocktransaction::whereHas('product', function ($product) {
            $product->where('category', '<>', 'service');
        })->whereHas('stocktransaction', function ($stock) use ($request) {
            $stock = $stock->whereNotNull('cashin_id')->whereNull('pending')->whereNull('return');
            if (!empty($request->start_date) && !empty($request->end_date)) {
                $request->start_date = date('Y-m-d', strtotime($request->start_date));
                $request->end_date = date('Y-m-d', strtotime($request->end_date));
                $stock = $stock->whereBetween('date', [$request->start_date, $request->end_date]);
            } else {
                $stock = $stock->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
            }
        })->sum('total');
        $return_in = Substocktransaction::whereHas('product', function ($product) {
            $product->where('category', '<>', 'service');
        })->whereHas('stocktransaction', function ($stock) use ($request) {
            $stock = $stock->whereNotNull('cashout_id')->whereNull('pending')->where('return', 'in');
            if (!empty($request->start_date) && !empty($request->end_date)) {
                $request->start_date = date('Y-m-d', strtotime($request->start_date));
                $request->end_date = date('Y-m-d', strtotime($request->end_date));
                $stock = $stock->whereBetween('date', [$request->start_date, $request->end_date]);
            } else {
                $stock = $stock->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
            }
        })->sum('total');

        $return_in_sell = Substocktransaction::whereNull('multi_unit')->whereHas('stocktransaction', function ($stock) use ($request) {
            $stock = $stock->whereNotNull('cashout_id')->whereNull('pending')->where('return', 'in');
            if (!empty($request->start_date) && !empty($request->end_date)) {
                $request->start_date = date('Y-m-d', strtotime($request->start_date));
                $request->end_date = date('Y-m-d', strtotime($request->end_date));
                $stock = $stock->whereBetween('date', [$request->start_date, $request->end_date]);
            } else {
                $stock = $stock->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
            }
        })->selectRaw('SUM(substocktransactions.selling_price * substocktransactions.qty) as total')->first()->total;

        $return_in_sell_mu = Substocktransaction::whereNotNull('multi_unit')->whereHas('stocktransaction', function ($stock) use ($request) {
            $stock = $stock->whereNotNull('cashout_id')->whereNull('pending')->where('return', 'in');
            if (!empty($request->start_date) && !empty($request->end_date)) {
                $request->start_date = date('Y-m-d', strtotime($request->start_date));
                $request->end_date = date('Y-m-d', strtotime($request->end_date));
                $stock = $stock->whereBetween('date', [$request->start_date, $request->end_date]);
            } else {
                $stock = $stock->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
            }
        })->selectRaw('SUM(substocktransactions.selling_price_unit / substocktransactions.qty_unit * substocktransactions.qty) as total')->first()->total;


        // kurang dari (less than)
        $return_out_price_diff_lt = Substocktransaction::whereHas('product', function ($product) {
            $product->where('category', '<>', 'service');
        })->whereHas('stocktransaction', function ($stock) use ($request) {
            $stock = $stock->where('return', 'out');
            if (!empty($request->start_date) && !empty($request->end_date)) {
                $request->start_date = date('Y-m-d', strtotime($request->start_date));
                $request->end_date = date('Y-m-d', strtotime($request->end_date));
                $stock = $stock->whereBetween('date', [$request->start_date, $request->end_date]);
            } else {
                $stock = $stock->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
            }
        })->whereColumn('purchase_price', '<', 'original_purchase_price')->selectRaw('SUM(substocktransactions.original_purchase_price * substocktransactions.qty) - SUM(total) as total')->first()->total;

        // lebih dari (greater than)
        $return_out_price_diff_gt = Substocktransaction::whereHas('product', function ($product) {
            $product->where('category', '<>', 'service');
        })->whereHas('stocktransaction', function ($stock) use ($request) {
            $stock = $stock->where('return', 'out');
            if (!empty($request->start_date) && !empty($request->end_date)) {
                $request->start_date = date('Y-m-d', strtotime($request->start_date));
                $request->end_date = date('Y-m-d', strtotime($request->end_date));
                $stock = $stock->whereBetween('date', [$request->start_date, $request->end_date]);
            } else {
                $stock = $stock->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
            }
        })->whereColumn('purchase_price', '>', 'original_purchase_price')->selectRaw('SUM(total) - SUM(substocktransactions.original_purchase_price * substocktransactions.qty) as total')->first()->total;

        $return_out = Substocktransaction::whereHas('product', function ($product) {
            $product->where('category', '<>', 'service');
        })->whereHas('stocktransaction', function ($stock) use ($request) {
            $stock = $stock->whereNotNull('cashout_id')->whereNull('pending')->whereNull('return')->where('return', 'out');
            if (!empty($request->start_date) && !empty($request->end_date)) {
                $request->start_date = date('Y-m-d', strtotime($request->start_date));
                $request->end_date = date('Y-m-d', strtotime($request->end_date));
                $stock = $stock->whereBetween('date', [$request->start_date, $request->end_date]);
            } else {
                $stock = $stock->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
            }
        })->sum('total');

        // PENDAPATAN barang
        $barang = Substocktransaction::whereHas('product', function ($product) {
            $product->where('category', '<>', 'service');
        })->whereHas('stocktransaction', function ($stock) use ($request) {
            $stock = $stock->where('nonmoney', 'in')->whereNull('type')->whereNull('production_id');
            if (!empty($request->start_date) && !empty($request->end_date)) {
                $request->start_date = date('Y-m-d', strtotime($request->start_date));
                $request->end_date = date('Y-m-d', strtotime($request->end_date));
                $stock = $stock->whereBetween('date', [$request->start_date, $request->end_date]);
            } else {
                $stock = $stock->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
            }
        })->sum('total');

        // Kerugian barang
        $barangrugi = Substocktransaction::whereHas('product', function ($product) {
            $product->where('category', '<>', 'service');
        })->whereHas('stocktransaction', function ($stock) use ($request) {
            $stock = $stock->where('nonmoney', 'out')->whereNull('production_id');
            if (!empty($request->start_date) && !empty($request->end_date)) {
                $request->start_date = date('Y-m-d', strtotime($request->start_date));
                $request->end_date = date('Y-m-d', strtotime($request->end_date));
                $stock = $stock->whereBetween('date', [$request->start_date, $request->end_date]);
            } else {
                $stock = $stock->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
            }
        })->sum('hpp');

        // Potongan beli
        $potonganbeli = Stocktransaction::whereNotNull('cashout_id')->whereNull('pending');
        if (!empty($request->start_date) && !empty($request->end_date)) {
            $request->start_date = date('Y-m-d', strtotime($request->start_date));
            $request->end_date = date('Y-m-d', strtotime($request->end_date));
            $potonganbeli = $potonganbeli->whereBetween('date', [$request->start_date, $request->end_date]);
        } else {
            $potonganbeli = $potonganbeli->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
        }
        $potonganbeli = $potonganbeli->sum('discount');

        // Potongan jual

        $potonganjual = Stocktransaction::whereNotNull('cashin_id')->whereNull('pending');
        if (!empty($request->start_date) && !empty($request->end_date)) {
            $request->start_date = date('Y-m-d', strtotime($request->start_date));
            $request->end_date = date('Y-m-d', strtotime($request->end_date));
            $potonganjual = $potonganjual->whereBetween('date', [$request->start_date, $request->end_date]);
        } else {
            $potonganjual = $potonganjual->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
        }
        $potonganjual = $potonganjual->sum('discount');

        // HPP

        $hpp = Substocktransaction::whereHas('product', function ($product) {
            $product->where('category', '<>', 'service');
        })->whereHas('stocktransaction', function ($stock) use ($request) {
            if (!empty($request->start_date) && !empty($request->end_date)) {
                $request->start_date = date('Y-m-d', strtotime($request->start_date));
                $request->end_date = date('Y-m-d', strtotime($request->end_date));
                $stock = $stock->whereBetween('date', [$request->start_date, $request->end_date]);
            } else {
                $stock = $stock->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
            }
            $stock = $stock->whereNotNull('cashin_id');
        })->sum('hpp');

        // Piutang jual

        $piutangjual = Stocktransaction::whereNotNull('cashin_id')->whereNull('pending');
        if (!empty($request->start_date) && !empty($request->end_date)) {
            $request->start_date = date('Y-m-d', strtotime($request->start_date));
            $request->end_date = date('Y-m-d', strtotime($request->end_date));
            $piutangjual = $piutangjual->whereBetween('date', [$request->start_date, $request->end_date]);
        } else {
            $piutangjual = $piutangjual->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
        }
        $piutangjual = $piutangjual->sum('total') - $piutangjual->sum('discount') - $piutangjual->sum('paid');

        // persedian

        $persediaanmasuk = Substocktransaction::whereHas('product', function ($product) {
            $product->where('category', '<>', 'service');
        })->whereHas('stocktransaction', function ($stock) use ($request) {
            $stock = $stock->whereNotNull('cashout_id')->whereNull('pending');
            if (!empty($request->start_date) && !empty($request->end_date)) {
                $request->start_date = date('Y-m-d', strtotime($request->start_date));
                $request->end_date = date('Y-m-d', strtotime($request->end_date));
                $stock = $stock->whereBetween('date', [$request->start_date, $request->end_date]);
            } else {
                $stock = $stock->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
            }
        })->sum('total');

        $itemmasuk = Substocktransaction::whereHas('product', function ($product) {
            $product->where('category', '<>', 'service');
        })->whereHas('stocktransaction', function ($stock) use ($request) {
            $stock = $stock->where('nonmoney', 'in');
            if (!empty($request->start_date) && !empty($request->end_date)) {
                $request->start_date = date('Y-m-d', strtotime($request->start_date));
                $request->end_date = date('Y-m-d', strtotime($request->end_date));
                $stock = $stock->whereBetween('date', [$request->start_date, $request->end_date]);
            } else {
                $stock = $stock->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
            }
        })->sum('total');

        $persediaanhpp = Substocktransaction::whereHas('product', function ($product) {
            $product->where('category', '<>', 'service');
        })->whereHas('stocktransaction', function ($stock) use ($request) {
            $stock = $stock->whereNotNull('cashin_id')->whereNull('pending');
            if (!empty($request->start_date) && !empty($request->end_date)) {
                $request->start_date = date('Y-m-d', strtotime($request->start_date));
                $request->end_date = date('Y-m-d', strtotime($request->end_date));
                $stock = $stock->whereBetween('date', [$request->start_date, $request->end_date]);
            } else {
                $stock = $stock->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
            }
        })->sum('hpp');

        $persediaankeluar = Substocktransaction::whereHas('product', function ($product) {
            $product->where('category', '<>', 'service');
        })->whereHas('stocktransaction', function ($stock) use ($request) {
            $stock = $stock->where('nonmoney', 'out');
            if (!empty($request->start_date) && !empty($request->end_date)) {
                $request->start_date = date('Y-m-d', strtotime($request->start_date));
                $request->end_date = date('Y-m-d', strtotime($request->end_date));
                $stock = $stock->whereBetween('date', [$request->start_date, $request->end_date]);
            } else {
                $stock = $stock->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
            }
        })->sum('hpp');
        // (penjualan+item masuk)(item keluar+penjualan)
        $persediaan = ($persediaanmasuk + $itemmasuk) - ($persediaankeluar + $persediaanhpp);

        $uangmukabeli = Stocktransaction::whereNotNull('cashout_id')->where('pending', 1);
        if (!empty($request->start_date) && !empty($request->end_date)) {
            $request->start_date = date('Y-m-d', strtotime($request->start_date));
            $request->end_date = date('Y-m-d', strtotime($request->end_date));
            $uangmukabeli = $uangmukabeli->whereBetween('date', [$request->start_date, $request->end_date]);
        } else {
            $uangmukabeli = $uangmukabeli->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
        }
        $uangmukabeli = $uangmukabeli->sum('paid');

        $pesanjual = Stocktransaction::whereNotNull('cashin_id')->where('pending', 1);
        if (!empty($request->start_date) && !empty($request->end_date)) {
            $request->start_date = date('Y-m-d', strtotime($request->start_date));
            $request->end_date = date('Y-m-d', strtotime($request->end_date));
            $pesanjual = $pesanjual->whereBetween('date', [$request->start_date, $request->end_date]);
        } else {
            $pesanjual = $pesanjual->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
        }
        $pesanjual = $pesanjual->sum('paid');

        $hutangbeli = Stocktransaction::whereNotNull('cashout_id')->whereNull('pending');
        if (!empty($request->start_date) && !empty($request->end_date)) {
            $request->start_date = date('Y-m-d', strtotime($request->start_date));
            $request->end_date = date('Y-m-d', strtotime($request->end_date));
            $hutangbeli = $hutangbeli->whereBetween('date', [$request->start_date, $request->end_date]);
        } else {
            $hutangbeli = $hutangbeli->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
        }
        $hutangbeli = $hutangbeli->sum('total') - $hutangbeli->sum('discount') - $hutangbeli->sum('paid');

        // Produksi
        $produksi = Akun::withCount(['subcashtransaction as sum_subcash' => function ($sub) use ($request) {
            $akunbtkldibebankan = Akun::where('name', 'biaya_tenaga_kerja_langsung_dibebankan')->first();
            $akunOverheadDibebankan = DB::table('akuns')->where('name', 'biaya_overhead_pabrik_dibebankan')->first();
            $excludeIds = [];
            if ($akunbtkldibebankan) {
                $excludeIds[] = (int)$akunbtkldibebankan->id;
            }
            if ($akunOverheadDibebankan) {
                $childrenIds = DB::table('akuns')
                    ->where('perent_id', $akunOverheadDibebankan->id)
                    ->pluck('id')
                    ->map(function ($id) {
                        return (int)$id;
                    })
                    ->toArray();
                $excludeIds = array_merge($excludeIds, $childrenIds);
            }
            if (!empty($excludeIds)) {
                $sub->whereNotIn('akun_id', $excludeIds);
            }
            $sub->whereHas('cashtransaction', function ($cash) use ($request) {
                $cash->where('transaction_category', 'biaya_produksi_aktual');
                if (!empty($request->start_date) && !empty($request->end_date)) {
                    $request->start_date = date('Y-m-d', strtotime($request->start_date));
                    $request->end_date = date('Y-m-d', strtotime($request->end_date));
                    $cash = $cash->whereBetween('date', [$request->start_date, $request->end_date]);
                } else {
                    $cash = $cash->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
                }
            })->select(DB::raw("SUM(total)"));
        }])->get();

        foreach ($produksi as $key => $value) {
            $value->total = $value->sum_subcash;
        }

        // Biaya Produksi Dibebankan
        $produksidibebankan = Akun::withCount(['subcashtransaction as sum_subcash' => function ($sub) use ($request) {
            $akunOverheadDibebankan = DB::table('akuns')->where('name', 'biaya_overhead_pabrik_dibebankan')->first();
            if ($akunOverheadDibebankan) {
                $childrenIds = DB::table('akuns')
                    ->where('perent_id', $akunOverheadDibebankan->id)
                    ->pluck('id')
                    ->map(function ($id) {
                        return (int)$id;
                    })
                    ->toArray();
                if (!empty($childrenIds)) {
                    $sub->whereIn('akun_id', $childrenIds);
                }
            }
            $sub->whereHas('cashtransaction', function ($cash) use ($request) {
                $cash->where('transaction_category', 'biaya_produksi_dibebankan');
                if (!empty($request->start_date) && !empty($request->end_date)) {
                    $request->start_date = date('Y-m-d', strtotime($request->start_date));
                    $request->end_date = date('Y-m-d', strtotime($request->end_date));
                    $cash = $cash->whereBetween('date', [$request->start_date, $request->end_date]);
                } else {
                    $cash = $cash->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
                }
            })->select(DB::raw("SUM(total)"));
        }])->get();

        foreach ($produksidibebankan as $key => $value) {
            $value->total = -abs((float) $value->sum_subcash);
        }

        $akunbtkldibebankan = Akun::where('name', 'biaya_tenaga_kerja_langsung_dibebankan')->first();
        $btkldibebankan = Subcashtransaction::where('akun_id', $akunbtkldibebankan->id)
            ->whereHas('cashtransaction', function ($query) use ($request) {
                $query->where('transaction_category', 'biaya_produksi_dibebankan');
                if (!empty($request->start_date) && !empty($request->end_date)) {
                    $start_date = date('Y-m-d', strtotime($request->start_date));
                    $end_date = date('Y-m-d', strtotime($request->end_date));
                    $query->whereBetween('date', [$start_date, $end_date]);
                } else {
                    $query->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
                }
            })->sum('total');

        $assets = Akun::where('is_productionasset', true)->pluck('id')->toArray();
        $assetsproduksi = Subcashtransaction::whereHas('cashtransaction', function ($query) use ($request) {
            $query->where('transaction_category', 'biaya_produksi_dibebankan');
            if (!empty($request->start_date) && !empty($request->end_date)) {
                $start_date = date('Y-m-d', strtotime($request->start_date));
                $end_date = date('Y-m-d', strtotime($request->end_date));
                $query->whereBetween('date', [$start_date, $end_date]);
            } else {
                $query->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
            }
        })->whereIn('akun_id', $assets)->sum('total');

        $akunpiutangpph4_2 = Akun::where('name', 'Biaya_PPh_4_2')->first();
        $biayastockpph4_2 = Credit::where('cashin_id', $akunpiutangpph4_2->id)->wherehas('stocktransaction', function ($stock) {
            $stock->whereNotNull('cashin_id')->whereNotNull('pph4_2_amount');
        });
        if (!empty($request->start_date) && !empty($request->end_date)) {
            $start_date = date('Y-m-d', strtotime($request->start_date));
            $end_date = date('Y-m-d', strtotime($request->end_date));
            $biayastockpph4_2->whereBetween('date', [$start_date, $end_date]);
        } else {
            $biayastockpph4_2->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
        }
        $biayacashpph4_2 = CreditCashTransaction::where('to_id', $akunpiutangpph4_2->id)
            ->whereHas('cashtransaction', function ($q) {
                $q->whereNotNull('to')
                    ->where(function ($pph) {
                        $pph->whereNotNull('pph4_2_amount')
                            ->orWhereNotNull('pph4_2_amount_v2');
                    });
            });

        if (!empty($request->start_date) && !empty($request->end_date)) {
            $start_date = date('Y-m-d', strtotime($request->start_date));
            $end_date = date('Y-m-d', strtotime($request->end_date));
            $biayacashpph4_2->whereBetween('date', [$start_date, $end_date]);
        } else {
            $biayacashpph4_2->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
        }
        $biayapph4_2 = $biayastockpph4_2->sum('total') + $biayacashpph4_2->sum('total');

        $corporate_income_tax_soho = CorporateIncomeTax::where('transaction_category', 'soho');
        if (!empty($request->start_date) && !empty($request->end_date)) {
            $start_date = date('Y-m-d', strtotime($request->start_date));
            $end_date = date('Y-m-d', strtotime($request->end_date));
            $corporate_income_tax_soho->whereBetween('date', [$start_date, $end_date]);
        } else {
            $corporate_income_tax_soho->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
        }
        $corporate_income_tax_soho = $corporate_income_tax_soho->sum('tax_amount');

        $corporate_income_tax_normal = CorporateIncomeTax::where('transaction_category', 'normal');
        if (!empty($request->start_date) && !empty($request->end_date)) {
            $start_date = date('Y-m-d', strtotime($request->start_date));
            $end_date = date('Y-m-d', strtotime($request->end_date));
            $corporate_income_tax_normal->whereBetween('date', [$start_date, $end_date]);
        } else {
            $corporate_income_tax_normal->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
        }
        $corporate_income_tax_normal = $corporate_income_tax_normal->sum('tax_amount');

        $corporate_income_tax_sohoout = CorporateIncomeTax::where('transaction_category', 'soho_out');
        if (!empty($request->start_date) && !empty($request->end_date)) {
            $start_date = date('Y-m-d', strtotime($request->start_date));
            $end_date = date('Y-m-d', strtotime($request->end_date));
            $corporate_income_tax_sohoout->whereBetween('date', [$start_date, $end_date]);
        } else {
            $corporate_income_tax_sohoout->whereBetween('date', [date('Y-01-01', time()), date('Y-m-d', time())]);
        }
        $corporate_income_tax_sohoout = $corporate_income_tax_sohoout->sum('tax_base');

        //AKUN BERNAMA ;
        $akunJasa = Akun::where('name', '=', 'Pendapatan Jasa')->first();
        $akunJasa->total = $jasa;
        $penjualan = $penjualan;

        $akunPenjualan = Akun::where('name', '=', 'Pendapatan Penjualan')->first();
        $akunPenjualan->total = $penjualan;

        $akunRetur = Akun::where('name', '=', 'Retur Penjualan')->first();
        $akunRetur->total = -abs((float) $return_in_sell + $return_in_sell_mu);
        Log::info('Retur Penjualan: ', [
            'return_in_sell' => $return_in_sell,
            'return_in_sell_mu' => $return_in_sell_mu,
        ]);

        $akunBarang = Akun::where('name', '=', 'Pendapatan Barang')->first();
        $akunBarang->total = $barang;

        $akunBarangRugi = Akun::where('name', '=', 'Kerugian Barang Keluar Tanpa Penjualan')->first();
        $akunBarangRugi->total = $barangrugi;

        $akunPotonganBeli = Akun::where('name', '=', 'Potongan Pembelian')->first();
        $akunPotonganBeli->total = $potonganbeli;

        $akunKerugianSelisihPembelian = Akun::where('name', '=', 'Kerugian Selisih Harga Pada Retur Pembelian')->first();
        $akunKerugianSelisihPembelian->total = $return_out_price_diff_lt;

        $akunKeuntunganSelisihPembelian = Akun::where('name', '=', 'Keuntungan Selisih Harga Pada Retur Pembelian')->first();
        $akunKeuntunganSelisihPembelian->total = $return_out_price_diff_gt;

        $hpp = $hpp - $return_in;
        $akunHpp = Akun::where('name', '=', 'Harga Pokok Penjualan')->first();
        $akunHpp->total = $hpp;

        $akunPotonganJual = Akun::where('name', '=', 'Potongan Penjualan')->first();
        $akunPotonganJual->total = $potonganjual > 0 ? -$potonganjual : 0;

        $akunPiutangJual = Akun::where('name', '=', 'Piutang Penjualan')->first();
        $akunPiutangJual->total = $piutangjual;

        $akunPersediaan = Akun::where('name', '=', 'Persediaan Barang')->first();
        $akunPersediaan->total = $persediaan;

        $akunPesanBeli = Akun::where('name', '=', 'Uang Muka Pesanan Pembelian')->first();
        $akunPesanBeli->total = $uangmukabeli;

        $akunPesanJual = Akun::where('name', '=', 'Hutang Pesanan Penjualan')->first();
        $akunPesanJual->total = $pesanjual;

        $akunPembelian = Akun::where('name', '=', 'Hutang Pembelian Non Tunai')->first();
        $akunPembelian->total = $hutangbeli;

        $akunBiayaPPh4_2 = Akun::where('name', '=', 'Biaya_PPh_4_2')->first();
        $akunBiayaPPh4_2->total = $biayapph4_2;

        $akunBebanPphFinalUMKM = Akun::where('name', '=', 'Beban_Pph_Final_UMKM')->first();
        $akunBebanPphFinalUMKM->total = $corporate_income_tax_soho;

        $akunbebanpajakkini = Akun::where('name', '=', 'beban_pajak_kini')->first();
        $akunbebanpajakkini->total = $corporate_income_tax_normal;

        $akunbebanpajaklainnya = Akun::where('name', '=', 'beban_pajak_lainnya')->first();
        $akunbebanpajaklainnya->total = $corporate_income_tax_sohoout;

        $akunTenagaKerjaLangsung = Akun::where('name', '=', 'biaya_tenaga_kerja_langsung_dibebankan')->first();
        $akunTenagaKerjaLangsung->total = -abs((float) $btkldibebankan);

        $akunAssetsProduksi = Akun::where('name', '=', 'biaya_penyusutan_pabrik_dibebankan')->first();
        $akunAssetsProduksi->total = -abs((float) $assetsproduksi);
        //TOTAL KABEH
        $data = Akun::where('name', $request->name)->with(str_repeat('children.', 10))->get();
        function akunRekursif($data, $total)
        {
            foreach ($data as $key => $valuedata) {
                if (!empty($valuedata->children)) {
                    foreach ($total as $key => $valuetotal) {
                        if ($valuedata->name == $valuetotal->name) {
                            $valuedata->total = $valuedata->total + $valuetotal->total;
                        }
                    }
                    akunRekursif($valuedata->children, $total);
                } else {
                    foreach ($total as $key => $valuetotal) {
                        if ($valuedata->name == $valuetotal->name) {
                            $valuedata->total = $valuedata->total + $valuetotal->total;
                        }
                    }
                }
            }
        }
        function rekursifTotal($data)
        {
            foreach ($data as $key => $value) {
                if (!empty($value->children)) {
                    rekursifTotal($value->children);
                    foreach ($value->children as $key => $value2) {
                        $value->total += $value2->total;
                    }
                }
            }
        }

        function hapusAkun(&$data, $namesToExclude)
        {
            foreach ($data as $key => &$value) {
                if (in_array($value->name, $namesToExclude)) {
                    unset($data[$key]);
                    continue;
                }

                if (!empty($value->children)) {
                    hapusAkun($value->children, $namesToExclude);
                }
            }
        }
        // masukin
        $akun = [];
        foreach ($cash as $key => $value) {
            array_push($akun, $value);
        }

        foreach ($cashin as $key => $value) {
            array_push($akun, $value);
        }

        foreach ($cashout as $key => $value) {
            array_push($akun, $value);
        }

        foreach ($pendingCash as $key => $value) {
            array_push($akun, $value);
        }

        foreach ($produksi as $key => $value) {
            array_push($akun, $value);
        }

        foreach ($produksidibebankan as $key => $value) {
            array_push($akun, $value);
        }
        array_push($akun, $akunJasa);
        array_push($akun, $akunPenjualan);
        array_push($akun, $akunRetur);
        array_push($akun, $akunBarang);
        array_push($akun, $akunBarangRugi);
        array_push($akun, $akunPotonganBeli);
        array_push($akun, $akunKerugianSelisihPembelian);
        array_push($akun, $akunKeuntunganSelisihPembelian);
        array_push($akun, $akunPotonganJual);
        array_push($akun, $akunHpp);
        array_push($akun, $akunPiutangJual);
        array_push($akun, $akunPersediaan);
        array_push($akun, $akunPesanBeli);
        array_push($akun, $akunPesanJual);
        array_push($akun, $akunPembelian);
        array_push($akun, $akunBiayaPPh4_2);
        array_push($akun, $akunBebanPphFinalUMKM);
        array_push($akun, $akunbebanpajakkini);
        array_push($akun, $akunbebanpajaklainnya);
        array_push($akun, $akunTenagaKerjaLangsung);
        array_push($akun, $akunAssetsProduksi);
        $pdptn = Akun::where('name', 'Pendapatan')->with(str_repeat('children.', 10))->get();
        $hpp = Akun::where('name', 'Hpp')->with(str_repeat('children.', 10))->get();
        $biaya = Akun::where('name', 'Biaya')->with(str_repeat('children.', 10))->get();
        $biayapajak = Akun::where('name', 'biaya_beban_pajak')->with(str_repeat('children.', 10))->get();
        akunRekursif($pdptn, $akun);
        akunRekursif($hpp, $akun);
        akunRekursif($biaya, $akun);
        akunRekursif($biayapajak, $akun);
        rekursifTotal($pdptn);
        rekursifTotal($hpp);
        rekursifTotal($biaya);
        rekursifTotal($biayapajak);

        $labaDitahan = 0;
        $LTB = Akun::where('name', '=', 'Laba Tahun Berjalan')->first();
        $LTB->total = ($pdptn[0]->total - $hpp[0]->total - $biaya[0]->total) - $labaDitahan;
        $LD = Akun::where('name', '=', 'Laba Ditahan')->first();
        $LD->total = $labaDitahan;

        array_push($akun, $LTB);
        array_push($akun, $LD);

        akunRekursif($data, $akun);
        // rekursifTotal($data);

        $lang = self::getActiveLanguage();
        $langCode = $lang->code ?? 'id';

        // Load mapping data
        $namaMappingData = $this->loadTypeMapping($langCode);

        $entityType = $isYayasan ? 'yayasan' : null;
        $this->tambahNamaAkunWithTranslation($data, $entityType, $namaMappingData);

        $response = [
            'success' => true,
            'akun' => $data,
        ];

        return response($response, 200);
    }
}
