<?php

namespace App\Http\Controllers;

use Carbon\Carbon;
use App\Models\HeaderCoa;
use App\Models\Pembelian;

use App\Models\Penjualan;


use Illuminate\Http\Request;
use App\Exports\LabaRugiExport;
use Barryvdh\DomPDF\Facade\Pdf;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;
use Maatwebsite\Excel\Facades\Excel;
use Yajra\DataTables\Facades\DataTables;

class LabaRugiController extends Controller
{
    public function index(Request $request)
    {
        $bulanTahun = $request->query('month', date('Y-m'));
        $tahun      = substr($bulanTahun, 0, 4);

        $neracaAwal = DB::table('neraca_awal')
            ->select('no_akun', 'debet', 'kredit')
            ->where('tahun', $tahun);

        // PEMBELIAN
        $pembelian = DB::table('pembelian')
            ->selectRaw("
            '6.002.001' AS no_akun,
            0 AS debet,
            SUM(
                (persediaan_barang + biaya_angkut + ppn_masukan)
                - ((persediaan_barang + biaya_angkut + ppn_masukan) * (discount/100))
            ) AS kredit
        ")
            ->whereRaw("DATE_FORMAT(tanggal, '%Y-%m') = ?", [$bulanTahun])

            ->unionAll(
                DB::table('pembelian')
                    ->selectRaw("
                    '2.200.005' AS no_akun,
                    0 AS debet,
                    SUM(hutang_dagang) AS kredit
                ")
                    ->whereRaw("DATE_FORMAT(tanggal, '%Y-%m') = ?", [$bulanTahun])
            );

        // PENJUALAN
        // $penjualan = DB::table('penjualan')
        //     ->selectRaw("
        //     '1.200.000' AS no_akun,
        //     SUM(piutang_dagang) AS debet,
        //     0 AS kredit
        // ")
        //     ->whereRaw("DATE_FORMAT(tanggal, '%Y-%m') = ?", [$bulanTahun])

        //     ->unionAll(
        //         DB::table('penjualan')
        //             ->selectRaw("
        //             '5.100.005' AS no_akun,
        //             0 AS debet,
        //             SUM(
        //                 (penjualan + biaya_angkut + ppn_keluaran + pph_nominal)
        //                 - ((penjualan + biaya_angkut + ppn_keluaran + pph_nominal) * (discount/100))
        //             ) AS kredit
        //         ")
        //             ->whereRaw("DATE_FORMAT(tanggal, '%Y-%m') = ?", [$bulanTahun])
        //     );

        $penjualan = DB::table('penjualan')
            ->selectRaw("
        '1.200.000' AS no_akun,
        SUM(piutang_dagang) AS debet,
        0 AS kredit
    ")
            ->whereRaw("DATE_FORMAT(tanggal, '%Y-%m') = ?", [$bulanTahun])

            ->unionAll(
                DB::table('penjualan')
                    ->selectRaw("
                '5.100.005' AS no_akun,
                0 AS debet,
                SUM(
                    (
                        COALESCE(penjualan, 0) +
                        COALESCE(biaya_angkut, 0) +
                        COALESCE(ppn_keluaran, 0) -
                        COALESCE(pph_nominal, 0) -
                        COALESCE(potongan_lain, 0)
                    )
                    -
                    (
                        (
                            COALESCE(penjualan, 0) +
                            COALESCE(biaya_angkut, 0) +
                            COALESCE(ppn_keluaran, 0) -
                            COALESCE(pph_nominal, 0) -
                            COALESCE(potongan_lain, 0)
                        ) * (COALESCE(discount, 0) / 100)
                    )
                ) AS kredit
            ")
                    ->whereRaw("DATE_FORMAT(tanggal, '%Y-%m') = ?", [$bulanTahun])
            );

        // var_dump($penjualan->toSql(), $penjualan->getBindings());
        // exit;

        // PENGELUARAN
        $pengeluaranDebet = DB::table('pengeluaran')
            ->select('no_akun_debet as no_akun', 'debet', DB::raw('0 as kredit'))
            ->whereRaw("DATE_FORMAT(tanggal, '%Y-%m') = ?", [$bulanTahun]);

        //dd($pengeluaranDebet->toSql(), $pengeluaranDebet->getBindings());

        $pengeluaranKredit = DB::table('pengeluaran')
            ->select('no_akun_kredit as no_akun', DB::raw('0 as debet'), 'kredit')
            ->whereRaw("DATE_FORMAT(tanggal, '%Y-%m') = ?", [$bulanTahun]);

        // PENERIMAAN
        $penerimaanDebet = DB::table('penerimaan')
            ->select('no_akun_debet as no_akun', 'debet', DB::raw('0 as kredit'))
            ->whereRaw("DATE_FORMAT(tanggal, '%Y-%m') = ?", [$bulanTahun]);

        $penerimaanKredit = DB::table('penerimaan')
            ->select('no_akun_kredit as no_akun', DB::raw('0 as debet'), 'kredit')
            ->whereRaw("DATE_FORMAT(tanggal, '%Y-%m') = ?", [$bulanTahun]);

        // JURNAL UMUM
        $jurnalDebet = DB::table('jurnal_umum')
            ->select('no_akun_debet as no_akun', 'debet', DB::raw('0 as kredit'))
            ->whereRaw("DATE_FORMAT(tanggal, '%Y-%m') = ?", [$bulanTahun]);

        $jurnalKredit = DB::table('jurnal_umum')
            ->select('no_akun_kredit as no_akun', DB::raw('0 as debet'), 'kredit')
            ->whereRaw("DATE_FORMAT(tanggal, '%Y-%m') = ?", [$bulanTahun]);

        // ========================
        // Gabungkan semua transaksi
        // ========================
        $transaksi = DB::query()
            ->fromSub(
                $neracaAwal
                    ->unionAll($pembelian)
                    ->unionAll($penjualan)
                    ->unionAll($pengeluaranDebet)
                    ->unionAll($pengeluaranKredit)
                    ->unionAll($penerimaanDebet)
                    ->unionAll($penerimaanKredit)
                    ->unionAll($jurnalDebet)
                    ->unionAll($jurnalKredit),
                't'
            )
            ->select(
                'no_akun',
                DB::raw('SUM(debet)  as total_debet'),
                DB::raw('SUM(kredit) as total_kredit')
            )
            ->groupBy('no_akun')
            ->get();

        // Mapping ke HeaderCoa
        $headerNeraca = HeaderCoa::with('masterCoa')
            ->whereBetween('id', [22, 36])
            ->get()
            ->map(function ($header) use ($transaksi) {
                $header->masterCoa = $header->masterCoa->map(function ($coa) use ($transaksi) {
                    // ASUMSI: di tabel master_coa, kolom nomor akun namanya 'nomor_akun'
                    $match = $transaksi->firstWhere('no_akun', $coa->no_akun);

                    $coa->debet  = $match->total_debet  ?? 0;
                    $coa->kredit = $match->total_kredit ?? 0;

                    return $coa;
                });
                return $header;
            });
        // return response()->json([
        //     'status' => 'success',
        //     'laporan' => 'Neraca',
        //     'bulan' => $bulanTahun,

        //     'data' => $headerNeraca
        // ]);
        return view('laporan.laba_rugi.index', compact('headerNeraca'));
    }


    public function exportExcel(Request $request)
    {

        [$year, $month] = explode('-', $request->input('month'));
        setlocale(LC_TIME, 'id_ID.UTF-8');
        Carbon::setLocale('id');
        $bulanIndonesia = Carbon::createFromFormat('m', $month)->translatedFormat('F');


        return Excel::download(new LabaRugiExport($year, $month, $bulanIndonesia), 'laba rugi ' . $bulanIndonesia . ' ' . $year . '.xlsx');
    }

    public function exportPDF(Request $request)
    {
        $bulan = request()->get('month') ?? date('Y-m');
        $tahun = substr($bulan, 0, 4);
        $month = substr($bulan, 5, 2);
        setlocale(LC_TIME, 'id_ID.UTF-8');
        Carbon::setLocale('id');
        $bulanIndonesia = Carbon::createFromFormat('m', $month)->translatedFormat('F');
        $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("DATE_FORMAT(tanggal, '%Y-%m') = ?", [$bulan])
            ->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("DATE_FORMAT(tanggal, '%Y-%m') = ?", [$bulan])
            ->first();



        $header_coa = HeaderCoa::with(['masterCoa' => function ($query) use ($bulan, $tahun) {
            $query->with([
                'penerimaanDebet' => function ($q) use ($bulan) {
                    $q->whereRaw("DATE_FORMAT(tanggal, '%Y-%m') = ?", [$bulan]);
                },
                'penerimaanKredit' => function ($q) use ($bulan) {
                    $q->whereRaw("DATE_FORMAT(tanggal, '%Y-%m') = ?", [$bulan]);
                },
                'pengeluaranDebet' => function ($q) use ($bulan) {
                    $q->whereRaw("DATE_FORMAT(tanggal, '%Y-%m') = ?", [$bulan]);
                },
                'pengeluaranKredit' => function ($q) use ($bulan) {
                    $q->whereRaw("DATE_FORMAT(tanggal, '%Y-%m') = ?", [$bulan]);
                },
                'jurnalUmumDebet' => function ($q) use ($bulan) {
                    $q->whereRaw("DATE_FORMAT(tanggal, '%Y-%m') = ?", [$bulan]);
                },
                'jurnalUmumKredit' => function ($q) use ($bulan) {
                    $q->whereRaw("DATE_FORMAT(tanggal, '%Y-%m') = ?", [$bulan]);
                },
                'neraca_awal' => function ($q) use ($tahun) {
                    $q->whereRaw("DATE_FORMAT(tahun, '%Y') = ?", [$tahun]);
                },
            ])->orderBy('no_akun');
        }])->orderBy('no_header_akun')->get();

        $pdf = PDF::loadView('laporan.laba_rugi.pdf', compact('bulan', 'tahun', 'penjualan', 'pembelian', 'header_coa', 'bulanIndonesia'));
        return $pdf->download('laba_rugi ' . $bulanIndonesia . ' ' . $tahun . '.pdf');
    }
}
