<?php

namespace App\Http\Controllers;

use App\Models\HeaderCoa;
use App\Models\MasterCoa;
use App\Models\Pembelian;

use App\Models\Penjualan;
use App\Models\JurnalUmum;
use App\Models\NeracaAwal;
use App\Models\Penerimaan;
use App\Models\Pengeluaran;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Yajra\DataTables\Facades\DataTables;
use App\Exports\NeracaSaldoExport;
use Maatwebsite\Excel\Facades\Excel;
use Barryvdh\DomPDF\Facade\Pdf;
use Carbon\Carbon;

class NeracaSaldoController extends Controller
{
    private function groupByAkun($query, $year, $fieldTahun, $fieldAkun = null)
    {
        // Kalau tidak diset, default pakai no_akun_debet/no_akun_kredit
        if ($fieldAkun) {
            // khusus tabel seperti NeracaAwal yg cuma punya "no_akun"
            return $query->select($fieldAkun . ' as no_akun')
                ->selectRaw('SUM(debet) as debet, SUM(kredit) as kredit')
                ->where($fieldTahun, $year)
                ->groupBy($fieldAkun)
                ->get()
                ->keyBy('no_akun');
        }

        // default untuk tabel lain (punya debet & kredit terpisah)
        return $query->selectRaw('no_akun_debet as no_akun, SUM(debet) as debet, 0 as kredit')
            ->where($fieldTahun, $year)
            ->groupBy('no_akun_debet')

            ->unionAll(
                $query->selectRaw('no_akun_kredit as no_akun, 0 as debet, SUM(kredit) as kredit')
                    ->where($fieldTahun, $year)
                    ->groupBy('no_akun_kredit')
            )
            ->get()
            ->groupBy('no_akun')
            ->map(function ($items) {
                return (object) [
                    'no_akun' => $items->first()->no_akun,
                    'debet'   => $items->sum('debet'),
                    'kredit'  => $items->sum('kredit'),
                ];
            });
    }

    public function index()
    {
        $year =  request()->get('year') ? request()->get('year') : date('Y');
        // $data = NeracaAwal::where('tahun', $year)->get();
        // $neraca_awal_sebelumnya = NeracaAwal::where('tahun', ($year - 1))->get();
        // $penerimaan = Penerimaan::whereYear('tanggal', $year)->get();
        // $pengeluaran = Pengeluaran::whereYear('tanggal', $year)->get();
        // $jurnal_umum = JurnalUmum::whereYear('tanggal', $year)->get();
        // // $pembelian = Pembelian::whereYear('tanggal', ($year - 1))->get();
        // // $penjualan = Penjualan::whereYear('tanggal', ($year - 1))->get();
        // $penjualan = Penjualan::selectRaw('
        // SUM(piutang_dagang) as piutang_dagang,
        // SUM(penjualan) as penjualan,
        // SUM(biaya_angkut) as biaya_angkut,
        // SUM(nominal_diskon) as nominal_diskon,
        // SUM(ppn_keluaran) as ppn_keluaran')
        //     ->whereRaw("YEAR(tanggal) = ?", [($year - 1)])
        //     ->first();

        // $pembelian = Pembelian::selectRaw('
        // SUM(hutang_dagang) as hutang_dagang,
        // SUM(persediaan_barang) as persediaan_barang,
        // SUM(biaya_angkut) as biaya_angkut,
        // SUM(nominal_diskon) as nominal_diskon,
        // SUM(ppn_masukan) as ppn_masukan')
        //     ->whereRaw("YEAR(tanggal) = ?", [($year - 1)])
        //     ->first();
        // $master_akun = MasterCoa::orderBy('no_akun', 'asc')->get();
        // return view('laporan.neraca_saldo.index', compact('data', 'master_akun', 'penerimaan', 'pengeluaran', 'jurnal_umum', 'pembelian', 'penjualan', 'neraca_awal_sebelumnya'));
        $groupByAkun = function ($query, $year = null, $yearColumn = 'tahun', $dateColumn = 'tanggal') {
            if ($year && $yearColumn) {
                $query->where($yearColumn, $year);
            } elseif ($year && $dateColumn) {
                $query->whereYear($dateColumn, $year);
            }

            // Ambil grouping dari sisi debit
            $debet = (clone $query)
                ->select('no_akun_debet as no_akun')
                ->selectRaw('SUM(debet) as debet, 0 as kredit')
                ->groupBy('no_akun_debet');

            // Ambil grouping dari sisi kredit
            $kredit = (clone $query)
                ->select('no_akun_kredit as no_akun')
                ->selectRaw('0 as debet, SUM(kredit) as kredit')
                ->groupBy('no_akun_kredit');

            // UNION keduanya lalu group ulang
            return $debet->unionAll($kredit)
                ->get()
                ->groupBy('no_akun')
                ->map(function ($rows) {
                    return [
                        'debet' => $rows->sum('debet'),
                        'kredit' => $rows->sum('kredit'),
                    ];
                });
        };

        //$data = $groupByAkun(NeracaAwal::query(), $year, 'tahun');
        //$neraca_awal_sebelumnya = $groupByAkun(NeracaAwal::query(), $year - 1, 'tahun');
        $data = $this->groupByAkun(
            NeracaAwal::query(),
            $year,
            'tahun',
            'no_akun' // field akun tunggal
        );
        $neraca_awal_sebelumnya = $this->groupByAkun(
            NeracaAwal::query(),
            $year - 1,
            'tahun',
            'no_akun' // field akun tunggal
        );
        $penerimaan = $groupByAkun(Penerimaan::query(), $year, null, 'tanggal');
        $pengeluaran = $groupByAkun(Pengeluaran::query(), $year, null, 'tanggal');
        $jurnal_umum = $groupByAkun(JurnalUmum::query(), $year, null, 'tanggal');

        // agregat pembelian & penjualan (tanpa no_akun, langsung summary)
        $penjualan = Penjualan::selectRaw('
        SUM(piutang_dagang) as piutang_dagang,
        SUM(penjualan) as penjualan,
        SUM(biaya_angkut) as biaya_angkut,
        SUM(nominal_diskon) as nominal_diskon,
        SUM(ppn_keluaran) as ppn_keluaran
    ')
            ->whereYear('tanggal', $year - 1)
            ->first();

        $pembelian = Pembelian::selectRaw('
        SUM(hutang_dagang) as hutang_dagang,
        SUM(persediaan_barang) as persediaan_barang,
        SUM(biaya_angkut) as biaya_angkut,
        SUM(nominal_diskon) as nominal_diskon,
        SUM(ppn_masukan) as ppn_masukan
    ')
            ->whereYear('tanggal', $year - 1)
            ->first();

        $master_akun = MasterCoa::orderBy('no_akun', 'asc')->get();

        return view('laporan.neraca_saldo.index', compact(
            'year',
            'master_akun',
            'data',
            'neraca_awal_sebelumnya',
            'penerimaan',
            'pengeluaran',
            'jurnal_umum',
            'penjualan',
            'pembelian'
        ));
    }

    public function exportExcel(Request $request)
    {
        $year = $request->input('year') ?? date('Y');

        $groupByAkun = function ($query, $year = null, $yearColumn = 'tahun', $dateColumn = 'tanggal') {
            if ($year && $yearColumn) {
                $query->where($yearColumn, $year);
            } elseif ($year && $dateColumn) {
                $query->whereYear($dateColumn, $year);
            }

            // Ambil grouping dari sisi debit
            $debet = (clone $query)
                ->select('no_akun_debet as no_akun')
                ->selectRaw('SUM(debet) as debet, 0 as kredit')
                ->groupBy('no_akun_debet');

            // Ambil grouping dari sisi kredit
            $kredit = (clone $query)
                ->select('no_akun_kredit as no_akun')
                ->selectRaw('0 as debet, SUM(kredit) as kredit')
                ->groupBy('no_akun_kredit');

            // UNION keduanya lalu group ulang
            return $debet->unionAll($kredit)
                ->get()
                ->groupBy('no_akun')
                ->map(function ($rows) {
                    return [
                        'debet' => $rows->sum('debet'),
                        'kredit' => $rows->sum('kredit'),
                    ];
                });
        };

        //$data = $groupByAkun(NeracaAwal::query(), $year, 'tahun');
        //$neraca_awal_sebelumnya = $groupByAkun(NeracaAwal::query(), $year - 1, 'tahun');
        $data = $this->groupByAkun(
            NeracaAwal::query(),
            $year,
            'tahun',
            'no_akun' // field akun tunggal
        );
        $neraca_awal_sebelumnya = $this->groupByAkun(
            NeracaAwal::query(),
            $year - 1,
            'tahun',
            'no_akun' // field akun tunggal
        );
        $penerimaan = $groupByAkun(Penerimaan::query(), $year, null, 'tanggal');
        $pengeluaran = $groupByAkun(Pengeluaran::query(), $year, null, 'tanggal');
        $jurnal_umum = $groupByAkun(JurnalUmum::query(), $year, null, 'tanggal');

        // agregat pembelian & penjualan (tanpa no_akun, langsung summary)
        $penjualan = Penjualan::selectRaw('
        SUM(piutang_dagang) as piutang_dagang,
        SUM(penjualan) as penjualan,
        SUM(biaya_angkut) as biaya_angkut,
        SUM(nominal_diskon) as nominal_diskon,
        SUM(ppn_keluaran) as ppn_keluaran
    ')
            ->whereYear('tanggal', $year - 1)
            ->first();

        $pembelian = Pembelian::selectRaw('
        SUM(hutang_dagang) as hutang_dagang,
        SUM(persediaan_barang) as persediaan_barang,
        SUM(biaya_angkut) as biaya_angkut,
        SUM(nominal_diskon) as nominal_diskon,
        SUM(ppn_masukan) as ppn_masukan
    ')
            ->whereYear('tanggal', $year - 1)
            ->first();
        $master_akun = MasterCoa::orderBy('no_akun', 'asc')->get();

        return Excel::download(
            new NeracaSaldoExport($data, $master_akun, $penerimaan, $pengeluaran, $jurnal_umum, $pembelian, $penjualan, $neraca_awal_sebelumnya, $year),
            'Neraca Saldo' . $year . '.xlsx'
        );
        //return Excel::download(new NeracaSaldoExport($year), 'neraca saldo ' . $year . '.xlsx');
    }

    public function exportPDF(Request $request)
    {
        $year =  request()->get('year') ? request()->get('year') : date('Y');
        $groupByAkun = function ($query, $year = null, $yearColumn = 'tahun', $dateColumn = 'tanggal') {
            if ($year && $yearColumn) {
                $query->where($yearColumn, $year);
            } elseif ($year && $dateColumn) {
                $query->whereYear($dateColumn, $year);
            }

            // Ambil grouping dari sisi debit
            $debet = (clone $query)
                ->select('no_akun_debet as no_akun')
                ->selectRaw('SUM(debet) as debet, 0 as kredit')
                ->groupBy('no_akun_debet');

            // Ambil grouping dari sisi kredit
            $kredit = (clone $query)
                ->select('no_akun_kredit as no_akun')
                ->selectRaw('0 as debet, SUM(kredit) as kredit')
                ->groupBy('no_akun_kredit');

            // UNION keduanya lalu group ulang
            return $debet->unionAll($kredit)
                ->get()
                ->groupBy('no_akun')
                ->map(function ($rows) {
                    return [
                        'debet' => $rows->sum('debet'),
                        'kredit' => $rows->sum('kredit'),
                    ];
                });
        };

        //$data = $groupByAkun(NeracaAwal::query(), $year, 'tahun');
        //$neraca_awal_sebelumnya = $groupByAkun(NeracaAwal::query(), $year - 1, 'tahun');
        $data = $this->groupByAkun(
            NeracaAwal::query(),
            $year,
            'tahun',
            'no_akun' // field akun tunggal
        );
        $neraca_awal_sebelumnya = $this->groupByAkun(
            NeracaAwal::query(),
            $year - 1,
            'tahun',
            'no_akun' // field akun tunggal
        );
        $penerimaan = $groupByAkun(Penerimaan::query(), $year, null, 'tanggal');
        $pengeluaran = $groupByAkun(Pengeluaran::query(), $year, null, 'tanggal');
        $jurnal_umum = $groupByAkun(JurnalUmum::query(), $year, null, 'tanggal');

        // agregat pembelian & penjualan (tanpa no_akun, langsung summary)
        $penjualan = Penjualan::selectRaw('
        SUM(piutang_dagang) as piutang_dagang,
        SUM(penjualan) as penjualan,
        SUM(biaya_angkut) as biaya_angkut,
        SUM(nominal_diskon) as nominal_diskon,
        SUM(ppn_keluaran) as ppn_keluaran
    ')
            ->whereYear('tanggal', $year - 1)
            ->first();

        $pembelian = Pembelian::selectRaw('
        SUM(hutang_dagang) as hutang_dagang,
        SUM(persediaan_barang) as persediaan_barang,
        SUM(biaya_angkut) as biaya_angkut,
        SUM(nominal_diskon) as nominal_diskon,
        SUM(ppn_masukan) as ppn_masukan
    ')
            ->whereYear('tanggal', $year - 1)
            ->first();
        $master_akun = MasterCoa::orderBy('no_akun', 'asc')->get();

        $pdf = PDF::loadView('laporan.neraca_saldo.pdf', compact('data', 'master_akun', 'penerimaan', 'pengeluaran', 'jurnal_umum', 'pembelian', 'penjualan', 'neraca_awal_sebelumnya', 'year'));
        return $pdf->download('neraca saldo ' . $year . '.pdf');
    }
}
