<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use App\Models\Anggota;
use App\Models\DataNeraca;
use App\Models\Deposito;
use App\Models\JurnalTrans;
use App\Models\Kas;
use App\Models\ProdukTabungan;
use App\Models\Tabungan;
use App\Models\TransaksiAkuntansi;
use App\Models\TransaksiDeposito;
use App\Models\TransaksiPenempatanBank;
use App\Models\TransaksiTabungan;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class LaporanController extends Controller
{
    public function laporanTransaksiRincian(Request $request)
    {
        $transaksi = $request->input('jenis_transaksi', null);
        $tgl_awal = $request->input('tgl_awal', Carbon::now());
        $tgl_akhir = $request->input('tgl_akhir', Carbon::now());

        $collection_transaksi = collect([]);

        if ($transaksi == 'tabungan' || $transaksi == null) {
            $produk_tabungan = ProdukTabungan::where('status', 1)->get();
            $akun_simpanan = $produk_tabungan->pluck('akun_simpanan_id');

            $transaksi_tabungan = JurnalTrans::whereIn('akun_jurnal', $akun_simpanan)
                ->whereBetween('trans_tglbuku', [$tgl_awal, $tgl_akhir])
                ->where('status', 1)
                ->select([DB::raw("'Tabungan' as jenis_transaksi"), 'trans_id', 'trans_tglbuku', 'trans_date', 'kredit as masuk', 'debet as keluar', 'keterangan', 'rekening'])
                ->get();
            if ($transaksi_tabungan->count() > 0) {
                $collection_transaksi = $collection_transaksi->push([
                    "jenis_transaksi" => "Tabungan",
                    "data" => $transaksi_tabungan
                ]);
            }
        }

        if ($transaksi == 'deposito' || $transaksi == null) {
            $transaksi_deposito = TransaksiDeposito::whereBetween('trans_tglbuku', [$tgl_awal, $tgl_akhir])
                ->where('status', 1)
                ->select([DB::raw("'Deposito' as jenis_transaksi"), 'trans_id', 'trans_tglbuku', 'trans_date', 'setor as masuk', 'tarik as keluar', 'keterangan', 'rekening'])
                ->get();
            if ($transaksi_deposito->count() > 0) {
                $collection_transaksi = $collection_transaksi->push([
                    "jenis_transaksi" => "Deposito",
                    "data" => $transaksi_deposito
                ]);
            }
        }

        $kas = Kas::where('status', 1)->get();
        $akun_kas = $kas->pluck('akun_kas');
        if ($transaksi == 'kas' || $transaksi == null) {

            $transaksi_kas = JurnalTrans::whereBetween('trans_tglbuku', [$tgl_awal, $tgl_akhir])
                ->where('status', 1)
                ->whereIn('akun_jurnal', $akun_kas)
                ->select([DB::raw("'Kas' as jenis_transaksi"), 'trans_id', 'trans_tglbuku', 'trans_date', 'debet as masuk', 'kredit as keluar', 'keterangan', 'rekening'])
                ->get();

            if ($transaksi_kas->count() > 0) {
                $collection_transaksi = $collection_transaksi->push([
                    "jenis_transaksi" => "Kas",
                    "data" => $transaksi_kas
                ]);
            }
        }

        if ($transaksi == 'penempatan_bank' || $transaksi == null) {
            $transaksi_penempatan = TransaksiPenempatanBank::with('penempatan')->whereBetween('trans_tglbuku', [$tgl_awal, $tgl_akhir])
                ->where('status', 1)
                ->select([DB::raw("'Penempatan Bank' as jenis_transaksi"), 'trans_id', 'trans_tglbuku', 'trans_date', 'debet as masuk', 'kredit as keluar', 'keterangan', 'penempatan_id'])
                ->get();
            $transaksi_penempatan = $transaksi_penempatan->map(function ($item) {
                $item['rekening'] = $item->penempatan->rekening;
                return $item;
            });

            if ($transaksi_penempatan->count() > 0) {
                $collection_transaksi = $collection_transaksi->push([
                    "jenis_transaksi" => "Penempatan Bank",
                    "data" => $transaksi_penempatan
                ]);
            }
        }

        // mendapatkan kas awal
        $neraca = DataNeraca::whereIn('akun', $kas->pluck('akun_kas'))
            ->select([DB::raw('sum(saldo) as saldo'), 'tanggal'])
            ->groupBy('tanggal')
            ->orderBy('tanggal', 'desc')
            ->first();

        $kas_awal = 0;

        if ($neraca) {
            $kas_awal = JurnalTrans::whereIn('akun_jurnal', $kas->pluck('akun_kas'))
                ->where('trans_tglbuku', '>', $neraca->tanggal)
                ->where('trans_tglbuku', '<', $tgl_awal)
                ->select(DB::raw('sum(debet) - sum(kredit) as saldo'))
                ->where('status', 1)
                ->groupBy('akun_jurnal')
                ->first();
            $kas_awal = $kas_awal?->saldo + $neraca?->saldo;
        } else {
            $kas_awal = JurnalTrans::whereIn('akun_jurnal', $kas->pluck('akun_kas'))
                ->where('trans_tglbuku', '<=', Carbon::parse($tgl_awal)->subDay(1)->format('Y-m-d'))
                ->select(DB::raw('sum(debet) - sum(kredit) as saldo'))
                ->where('status', 1)
                ->groupBy('akun_jurnal')
                ->first();
            $kas_awal = $kas_awal?->saldo;
        }

        return response()->json([
            'kas_awal' => $kas_awal ?? 0,
            'statement' => $collection_transaksi
        ]);
    }

    public function laporanTransaksiRekap(Request $request)
    {
        $tgl_awal = $request->input('tgl_awal', Carbon::now());
        $tgl_akhir = $request->input('tgl_akhir', Carbon::now());

        $collection_transaksi = collect([]);

        $produk_tabungan = ProdukTabungan::where('status', 1)->get();
        $akun_simpanan = $produk_tabungan->pluck('akun_simpanan_id');

        $transaksi_tabungan = JurnalTrans::with('transaksi')->whereIn('akun_jurnal', $akun_simpanan)
            ->whereBetween('trans_tglbuku', [$tgl_awal, $tgl_akhir])
            ->where('status', 1)
            ->select([DB::raw("'Tabungan' as jenis_transaksi"), 'trans_kode', DB::raw('sum(kredit) as masuk'), DB::raw('sum(debet) as keluar')])
            ->groupBy('trans_kode')
            ->get();

        if ($transaksi_tabungan->count() > 0) {
            $collection_transaksi = $collection_transaksi->push([
                "jenis_transaksi" => "Tabungan",
                "data" => $transaksi_tabungan
            ]);
        }

        $transaksi_deposito = TransaksiDeposito::with('transaksi')->whereBetween('trans_tglbuku', [$tgl_awal, $tgl_akhir])
            ->where('status', 1)
            ->select([DB::raw("'Deposito' as jenis_transaksi"), 'trans_kode', DB::raw('sum(setor) as masuk'), DB::raw('sum(tarik) as keluar')])
            ->groupBy('trans_kode')
            ->get();

        if ($transaksi_deposito->count() > 0) {
            $collection_transaksi = $collection_transaksi->push([
                "jenis_transaksi" => "Deposito",
                "data" => $transaksi_deposito
            ]);
        }

        $kas = Kas::where('status', 1)->get();
        $akun_kas = $kas->pluck('akun_kas');

        $transaksi_kas = TransaksiAkuntansi::with('transaksi')->whereBetween('trans_tglbuku', [$tgl_awal, $tgl_akhir])
            ->where('status', 1)
            ->select([DB::raw("'Kas' as jenis_transaksi"), 'trans_kode', DB::raw('sum(masuk) as masuk'), DB::raw('sum(keluar) as keluar')])
            ->groupBy('trans_kode')
            ->get();

        if ($transaksi_kas->count() > 0) {
            $collection_transaksi = $collection_transaksi->push([
                "jenis_transaksi" => "Kas",
                "data" => $transaksi_kas
            ]);
        }

        $transaksi_penempatan = TransaksiPenempatanBank::with('transaksi')->whereBetween('trans_tglbuku', [$tgl_awal, $tgl_akhir])
            ->where('status', 1)
            ->select([DB::raw("'Penempatan Bank' as jenis_transaksi"), 'trans_kode', DB::raw('sum(debet) as masuk'), DB::raw('sum(kredit) as keluar')])
            ->groupBy('trans_kode')
            ->get();

        if ($transaksi_penempatan->count() > 0) {
            $collection_transaksi = $collection_transaksi->push([
                "jenis_transaksi" => "Penempatan Bank",
                "data" => $transaksi_penempatan
            ]);
        }

        // mendapatkan kas awal
        $neraca = DataNeraca::whereIn('akun', $kas->pluck('akun_kas'))
            ->select([DB::raw('sum(saldo) as saldo'), 'tanggal'])
            ->groupBy('tanggal')
            ->orderBy('tanggal', 'desc')
            ->first();

        $kas_awal = 0;

        if ($neraca) {
            $kas_awal = JurnalTrans::whereIn('akun_jurnal', $kas->pluck('akun_kas'))
                ->where('trans_tglbuku', '>', $neraca->tanggal)
                ->where('trans_tglbuku', '<', $tgl_awal)
                ->select(DB::raw('sum(debet) - sum(kredit) as saldo'))
                ->where('status', 1)
                ->groupBy('akun_jurnal')
                ->first();
            $kas_awal = $kas_awal?->saldo + $neraca?->saldo;
        } else {
            $kas_awal = JurnalTrans::whereIn('akun_jurnal', $kas->pluck('akun_kas'))
                ->where('trans_tglbuku', '<=', Carbon::parse($tgl_awal)->subDay(1)->format('Y-m-d'))
                ->select(DB::raw('sum(debet) - sum(kredit) as saldo'))
                ->where('status', 1)
                ->groupBy('akun_jurnal')
                ->first();
            $kas_awal = $kas_awal?->saldo;
        }

        return response()->json([
            'kas_awal' => $kas_awal ?? 0,
            'statement' => $collection_transaksi
        ]);
    }

    public function laporanNominatifAnggota(Request $request)
    {
        $kelurahan = $request->input('kelurahan', null);
        $kecamatan = $request->input('kecamatan', null);
        $kabupaten = $request->input('kabupaten', null);
        $status_marital = $request->input('status_marital', null);
        $jenis_kelamin = $request->input('jenis_kelamin', null);
        $status = $request->input('status', null);
        $agama = $request->input('agama', null);

        $anggota = Anggota::query();

        if ($status !== null) {
            $anggota->where('status', $status);
        }
        if ($kelurahan !== null) {
            $anggota->where('kelurahan', $kelurahan);
        }
        if ($kecamatan !== null) {
            $anggota->where('kecamatan', $kecamatan);
        }
        if ($kabupaten !== null) {
            $anggota->where('kabupaten', $kabupaten);
        }
        if ($status_marital !== null) {
            $anggota->where('status_marital', $status_marital);
        }
        if ($jenis_kelamin !== null) {
            $anggota->where('jenis_kelamin', $jenis_kelamin);
        }
        if ($agama !== null) {
            $anggota->where('agama', $agama);
        }

        return response()->json($anggota->get());
    }

    public function laporanNominatifTabungan(Request $request)
    {

        $status = $request->input('status', null);
        $produk = $request->input('produk', null);

        $tabungan = Tabungan::with('anggota', 'produk');

        if ($produk !== null) {
            $tabungan->where('produk_id', $produk);
        }
        if ($status !== null) {
            $tabungan->where('status', $status);
        }

        return response()->json($tabungan->get());
    }

    public function laporanNominatifDeposito(Request $request)
    {

        $status = $request->input('status', null);
        $produk = $request->input('produk', null);

        $deposito = Deposito::with('anggota', 'produk');

        if ($produk !== null) {
            $deposito->where('produk_id', $produk);
        }
        if ($status !== null) {
            $deposito->where('status', $status);
        }

        return response()->json($deposito->get());
    }

    public function laporanBukuBesar(Request $request)
    {
        $kode_akun = $request->input('kode_akun', null);
        $tgl_awal = $request->input('tgl_awal', Carbon::now());
        $tgl_akhir = $request->input('tgl_akhir', Carbon::now());

        $data = JurnalTrans::with('transaksi')->whereBetween('trans_tglbuku', [$tgl_awal, $tgl_akhir])
            ->where('status', 1)
            ->where('akun_jurnal', $kode_akun)
            ->get();

        return response()->json($data);
    }
}
