<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;

use App\Models\User;
use Carbon\Carbon;
use Exception;
use Illuminate\Database\QueryException;
use Illuminate\Http\Request;
use Illuminate\Http\Response;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Hash;

class KreditController extends Controller
{
    function getDaftarKredit(Request $request)
    {
        $daftar_kredit = DB::select(
            "SELECT rekening, km.proposal_id ,km.kelompok_id, kel.nama_kelompok, desa.nama_desa ,kredit_produk, IFNULL(kp.nama,'-') as nama_produk, km.plafon, km.bunga, km.jkw, tgl_mulai, tgl_jatuh_tempo, tgl_tutup, nomer_pk, km.bakidebet, IFNULL(admin,0) as admin, IFNULL(provisi,0) as provisi, kolektibilitas, km.status FROM data_kredit_master km LEFT JOIN data_kelompok_master kel ON kel.kelompok_id = km.kelompok_id LEFT JOIN data_kredit_produk kp ON km.kredit_produk = kp.id JOIN data_desa_master desa ON desa.desa_id = kel.desa_id ORDER BY rekening ASC"
        );

        if ($request->status == 'none') {
            $kredit_none = [];
            for ($i = 0; $i < count($daftar_kredit); $i++) {
                if ($daftar_kredit[$i]->status == 99) {
                    array_push($kredit_none, $daftar_kredit[$i]);
                }
            }
            $daftar_kredit = $kredit_none;
        }
        if ($request->status == 'ditolak') {
            $kredit_ditolak = [];
            for ($i = 0; $i < count($daftar_kredit); $i++) {
                if ($daftar_kredit[$i]->status == 98) {
                    array_push($kredit_ditolak, $daftar_kredit[$i]);
                }
            }
            $daftar_kredit = $kredit_ditolak;
        }
        if ($request->status == 'delete') {
            $kredit_delete = [];
            for ($i = 0; $i < count($daftar_kredit); $i++) {
                if ($daftar_kredit[$i]->status == 9) {
                    array_push($kredit_delete, $daftar_kredit[$i]);
                }
            }
            $daftar_kredit = $kredit_delete;
        }
        if ($request->status == 'setup') {
            $kredit_setup = [];
            for ($i = 0; $i < count($daftar_kredit); $i++) {
                if ($daftar_kredit[$i]->status == 0) {
                    array_push($kredit_setup, $daftar_kredit[$i]);
                }
            }
            $daftar_kredit = $kredit_setup;
        }
        if ($request->status == 'aktif') {
            $kredit_aktif = [];
            for ($i = 0; $i < count($daftar_kredit); $i++) {
                if ($daftar_kredit[$i]->status == 1) {
                    array_push($kredit_aktif, $daftar_kredit[$i]);
                }
            }
            $daftar_kredit = $kredit_aktif;
        }
        if ($request->status == 'lunas') {
            $kredit_lunas = [];
            for ($i = 0; $i < count($daftar_kredit); $i++) {
                if ($daftar_kredit[$i]->status == 3) {
                    array_push($kredit_lunas, $daftar_kredit[$i]);
                }
            }
            $daftar_kredit = $kredit_lunas;
        }

        if ($request->withPersetujuan == true) {
            foreach ($daftar_kredit as $kredit) {
                $persetujuan = DB::select("SELECT * FROM data_kredit_persetujuan WHERE proposal_id = ?", [$kredit->proposal_id]);

                $komite = DB::select("SELECT k.*,p.pegawai_nama, nama_jabatan FROM data_kredit_komite k JOIN data_pegawai p ON k.pegawai_id = p.pegawai_id JOIN ref_jabatan rj ON rj.id_jabatan = k.jabatan_id WHERE k.status = '1' ORDER BY level");

                $kredit->persetujuan = $persetujuan;
                $kredit->komite = $komite;
            }
        }

        return response()->json([
            'success' => true,
            'data' => $daftar_kredit,
        ]);
    }

    function getDaftarVerifiedProposal(Request $request)
    {
        $tanggal_awal = $request->input("tanggal_awal", now()->format('Y-m-d'));
        $tanggal_akhir = $request->input("tanggal_akhir", now()->format('Y-m-d'));

        $proposal = DB::table('data_kredit_proposal', 'proposal')
            ->join('data_kredit_produk AS produk', 'produk.id', 'proposal.kredit_produk')
            ->join('data_kelompok_master AS kelompok', 'kelompok.kelompok_id', 'proposal.kelompok_id')
            ->join('data_desa_master AS desa', 'desa.desa_id', 'kelompok.desa_id')
            // ->whereIn('proposal.status', [2])
            ->whereBetween('proposal.tanggal', [$tanggal_awal, $tanggal_akhir])
            ->select(['proposal.*', 'produk.nama', 'kelompok.nama_kelompok', 'desa.nama_desa'])
            ->orderBy('proposal.tanggal', 'desc')
            ->get();

        for ($i = 0; $i < count($proposal); $i++) {
            $proposal_id = $proposal[$i]->proposal_id;
            $persetujuan = DB::select("SELECT pk.*, p.pegawai_nama, rj.nama_jabatan FROM data_kredit_persetujuan pk LEFT JOIN data_pegawai p ON pk.pegawai_id = p.pegawai_id JOIN data_kredit_komite komite ON pk.komite_id = komite.id LEFT JOIN ref_jabatan rj ON komite.jabatan_id = rj.id_jabatan WHERE proposal_id = '$proposal_id' ORDER BY level ASC");

            $proposal[$i]->persetujuan = $persetujuan;

            $status = 1;
            foreach ($persetujuan as $p) {
                if ($p->persetujuan == 2) {
                    $status = 2;
                    break;
                } elseif ($p->persetujuan == 0) {
                    $status = 0;
                }
            }
            $proposal[$i]->status_persetujuan = $status;

            $komite = DB::select("SELECT k.*,p.pegawai_nama, nama_jabatan FROM data_kredit_komite k JOIN data_pegawai p ON k.pegawai_id = p.pegawai_id JOIN ref_jabatan rj ON rj.id_jabatan = k.jabatan_id WHERE k.status = '1' ORDER BY level");
            $proposal[$i]->komite = $komite;
        }

        return response()->json([
            'success' => true,
            'proposal' => $proposal,
        ]);
    }

    function getDaftarApprovedProposal()
    {
        $proposal = DB::table('data_kredit_proposal', 'proposal')
            ->join('data_kredit_produk AS produk', 'produk.id', 'proposal.kredit_produk')
            ->join('data_kelompok_master AS kelompok', 'kelompok.kelompok_id', 'proposal.kelompok_id')
            ->join('data_desa_master AS desa', 'desa.desa_id', 'kelompok.desa_id')
            ->leftJoin('data_anggota_master AS anggota', 'anggota.anggota_id', 'kelompok.ketua_id')
            ->where('proposal.status', 3)
            ->select(['proposal.*', 'produk.id AS produk_id', 'produk.nama', 'produk.inisial', 'kelompok.nama_kelompok', 'desa.nama_desa', 'anggota.nama_lengkap AS nama_ketua'])
            ->get();

        for ($i = 0; $i < count($proposal); $i++) {
            $proposal_id = $proposal[$i]->proposal_id;
            $persetujuan = DB::select("SELECT pk.*, p.pegawai_nama, rj.nama_jabatan FROM data_kredit_persetujuan pk LEFT JOIN data_pegawai p ON pk.pegawai_id = p.pegawai_id JOIN data_kredit_komite komite ON pk.komite_id = komite.id LEFT JOIN ref_jabatan rj ON komite.jabatan_id = rj.id_jabatan WHERE proposal_id = '$proposal_id'");
            $komite = DB::select("SELECT k.*,p.pegawai_nama, nama_jabatan FROM data_kredit_komite k JOIN data_pegawai p ON k.pegawai_id = p.pegawai_id JOIN ref_jabatan rj ON rj.id_jabatan = k.jabatan_id WHERE k.status = '1' ORDER BY level");
            $proposal[$i]->persetujuan = $persetujuan;
            $proposal[$i]->komite = $komite;
        }
        return response()->json([
            'success' => true,
            'proposal' => $proposal
        ]);
    }

    function getKomiteKredit()
    {
        $komite = DB::table('data_kredit_komite', 'komite')
            ->leftJoin('data_pegawai AS pegawai', 'pegawai.pegawai_id', 'komite.pegawai_id')
            ->leftJoin('ref_jabatan AS ref_jab', 'komite.jabatan_id', 'ref_jab.id_jabatan')
            ->select('komite.*', 'pegawai.pegawai_nama', 'ref_jab.*')
            ->where('komite.status', 1)
            ->get();
        return response()->json([
            'success' => true,
            'komite' => $komite
        ]);
    }

    function saveKomiteKredit(Request $request)
    {
        try {
            $komite_id = $request->id;
            if (isset($komite_id)) {
                DB::table('data_kredit_komite')
                    ->where('id', $komite_id)
                    ->update([
                        'jabatan_id' => $request->jabatan_id,
                        'pegawai_id' => $request->pegawai_id,
                        'level' => $request->level,
                    ]);
                return response()->json([
                    'success' => true,
                    'message' => 'Komite berhasil disimpan'
                ]);
            }


            $level = $request->level;
            if (!isset($level)) {
                $komite_level = DB::table('data_kredit_komite')->orderBy('level', 'desc')->select(DB::raw('IFNULL(level,0) AS level'))->first();
                $level = intval($komite_level->level ?? 0) + 1;
            }

            DB::table('data_kredit_komite')->insert([
                'jabatan_id' => $request->jabatan_id,
                'pegawai_id' => $request->pegawai_id,
                'level' => $level,
            ]);
            return response()->json([
                'success' => true,
                'message' => 'Komite berhasil disimpan'
            ]);
        } catch (Exception $error) {
            return response()->json([
                'success' => false,
                'error' => $error->getMessage()
            ]);
        }
    }

    function deleteKomiteKredit(Request $request)
    {
        $id = $request->id;
        try {
            DB::table('data_kredit_komite')->where('id', $id)->update([
                'status' => 0
            ]);
            $komite = DB::table('data_kredit_komite')
                ->where('status', 1)
                ->orderBy('level')
                ->get();
            for ($i = 0; $i < count($komite); $i++) {
                $id = $komite[$i]->id;
                DB::table('data_kredit_komite')
                    ->where('id', $id)
                    ->update([
                        'level' => $i + 1
                    ]);
            }
            return response()->json([
                'success' => true,
                'message' => "Komite berhasil dihapus",
                'komite' => $komite
            ]);
        } catch (Exception $error) {
            return response()->json([
                'success' => true,
                'error' => $error->getMessage()
            ]);
        }
    }

    function getDaftarPersetujuanKredit(Request $request)
    {
        $rekening = $request->rekening;
        $komite = DB::select("SELECT komite.id as komite_id, komite.jabatan_id, j.nama_jabatan ,komite.pegawai_id, p.pegawai_nama, komite.status, komite.level FROM data_kredit_komite komite LEFT JOIN data_kredit_persetujuan persetujuan ON komite.id = persetujuan.komite_id LEFT JOIN data_pegawai p ON p.pegawai_id = komite.pegawai_id JOIN ref_jabatan j ON komite.jabatan_id = j.id_jabatan WHERE komite.status = 1  ORDER BY komite.level ASC");

        $array_query = [];
        for ($i = 0; $i < count($komite); $i++) {
            $komite_id = $komite[$i]->komite_id;
            $sql = "SELECT id AS komite_id, p.pegawai_nama ,komite.jabatan_id, rj.nama_jabatan, IFNULL(( SELECT persetujuan FROM data_kredit_persetujuan ps WHERE ps.komite_id = '$komite_id' AND ps.rekening = '$rekening' ), 0 ) AS status, IFNULL(( SELECT keterangan FROM data_kredit_persetujuan ps WHERE ps.komite_id = '$komite_id' AND ps.rekening = '$rekening' LIMIT 1 ),'') AS keterangan, ( SELECT tanggal FROM data_kredit_persetujuan ps WHERE ps.komite_id = '$komite_id' AND ps.rekening = '$rekening' LIMIT 1 ) AS tanggal FROM data_kredit_komite komite LEFT JOIN data_pegawai p ON komite.pegawai_id = p.pegawai_id JOIN ref_jabatan rj ON rj.id_jabatan = komite.jabatan_id  WHERE komite.id = '$komite_id'";
            $persetujuan = DB::selectOne($sql);
            $komite[$i]->persetujuan = $persetujuan;
            array_push($array_query, $sql);
        }

        return response()->json([
            'success' => true,
            'data' => $komite,
            'rekening' => $rekening
        ]);
    }

    function simpanPersetujuanKredit(Request $request)
    {
        try {
            DB::beginTransaction();

            $proposal_id = $request->proposal_id;
            $aksi = $request->aksi;
            $komite_id = $request->komite_id;
            $pegawai_id = $request->pegawai_id;
            $keterangan = $request->keterangan ?? "";
            $level = $request->level;

            $user = User::whereRaw('BINARY username = ?', [$request->username])->first();
            if ($user && !Hash::check($request->password, $user->password)) {
                return response()->json([
                    'success' => false,
                    'error' => "Password salah",
                ]);
            }

            $isKomiteExist = DB::table('data_kredit_persetujuan')
                ->where('komite_id', $komite_id)
                ->where('level', $level)
                ->where('proposal_id', $proposal_id)
                ->count();

            if ($isKomiteExist != null && $isKomiteExist > 0) {
                DB::table('data_kredit_persetujuan')
                    ->where('komite_id', $komite_id)
                    ->where('level', $level)
                    ->where('proposal_id', $proposal_id)
                    ->update([
                        'persetujuan' => $aksi,
                        'pegawai_id' => $pegawai_id,
                        'tanggal' => date('Y-m-d'),
                        'keterangan' => $keterangan
                    ]);
            } else {
                DB::table('data_kredit_persetujuan')->insert([
                    'komite_id' => $komite_id,
                    'proposal_id' => $proposal_id,
                    'persetujuan' => $aksi,
                    'pegawai_id' => $pegawai_id,
                    'tanggal' => date('Y-m-d'),
                    'level' => $level,
                    'keterangan' => $keterangan
                ]);
            }

            // jika proposal ditolak maka update status proposal ditolak
            if ($aksi == 9) {
                DB::table('data_kredit_proposal')
                    ->where('proposal_id', $proposal_id)
                    ->update([
                        'status' => 9
                    ]);
            } else {
                // jika proposal disetujui maka update status proposal disetujui
                $last_komite = DB::table('data_kredit_komite')->where('status', 1)->orderBy('level', 'desc')->first();
                if ($last_komite->level == $level && $aksi == 1) {
                    DB::table('data_kredit_proposal')
                        ->where('proposal_id', $proposal_id)
                        ->update([
                            'status' => 3
                        ]);
                }
            }


            DB::commit();
            return response()->json([
                'success' => true,
                'message' => 'Data berhasil disimpan'
            ]);
        } catch (Exception $error) {
            DB::rollBack();
            return response()->json([
                'success' => false,
                'error' => $error->getMessage()
            ]);
        }
    }

    function getDaftarTransaksiAngsuran(Request $request)
    {
        $trans = DB::select(
            "SELECT trans_id, trans_jenis, trans_kode, t.rekening, pokok, t.bunga, denda, total, trans_catatan, trans_date ,trans_tglbuku, t.user, nama_kelompok, t.status, dm.nama_desa FROM data_kredit_trans_angsuran t JOIN data_kredit_master k ON t.rekening = k.rekening JOIN data_kelompok_master km ON k.kelompok_id = km.kelompok_id LEFT JOIN data_desa_master dm ON km.desa_id=dm.desa_id  WHERE DATE(trans_tglbuku) >= ? AND DATE(trans_tglbuku) <= ? AND trans_kode NOT IN ('23.3')",
            [$request->start_date, $request->end_date]
        );
        return response()->json($trans);
    }


    function getDaftarTransaksiRealisasi(Request $request)
    {
        $trans = DB::select(
            "SELECT trans_id, trans_kode, t.rekening, t.plafon, t.admin, t.provisi, penerimaan, t.trans_catatan, trans_tglbuku, trans_date, t.status, t.user, km.kelompok_id, nama_kelompok, (SELECT nama_desa FROM data_desa_master desa WHERE desa.desa_id = km.desa_id) as nama_desa FROM data_kredit_trans_realisasi t JOIN data_kredit_master m ON t.rekening = m.rekening JOIN data_kelompok_master km ON m.kelompok_id = km.kelompok_id WHERE trans_tglbuku >= ? AND trans_tglbuku <= ? AND trans_kode NOT IN ('23.3')",
            [$request->start_date, $request->end_date]
        );
        return response()->json($trans);
    }

    function getDaftarTransaksiRestruk(Request $request)
    {
        $trans = DB::select(
            "SELECT trans_id, '23' AS trans_kode, t.rekening, t.plafon_restruk as plafon, t.admin_restruk as admin, t.provisi_restruk as provisi, penerimaan, t.trans_catatan, trans_tglbuku, trans_date, t.status, t.user, km.kelompok_id, nama_kelompok, (SELECT nama_desa FROM data_desa_master desa WHERE desa.desa_id = km.desa_id) as nama_desa FROM data_kredit_trans_restruk t LEFT JOIN data_kredit_master m ON LEFT(t.rekening, 9) = m.rekening LEFT JOIN data_kelompok_master km ON m.kelompok_id = km.kelompok_id WHERE trans_tglbuku >= ? AND trans_tglbuku <= ?",
            [$request->start_date, $request->end_date]
        );
        return response()->json($trans);
    }

    function saveKredit(Request $request)
    {
        $kredit = DB::table('data_kredit_master')->where('proposal_id', $request->proposal_id)->get();
        if (count($kredit) != 0) {
            $kredit_proposal = $kredit[0];
            return response()->json([
                'success' => false,
                'error' => "Kredit dengan ID Proposal " . $request->proposal_id . " sudah dibuat dengan Rekening Kredit " . $kredit_proposal->rekening . "."
            ]);
        }

        DB::beginTransaction();
        try {
            $produk = $request->produk_id;
            $res = DB::selectOne("SELECT rekening FROM data_kredit_master WHERE kredit_produk = '$produk' ORDER BY rekening DESC LIMIT 1");
            $rekening =  $res == null ? 0 : $res->rekening;
            $last_rek = intval(substr($rekening, 4, 5));
            $nomer_urut = str_pad(($last_rek + 1), 5, "0", STR_PAD_LEFT);

            $prod = str_pad($produk, 2, "0", STR_PAD_LEFT);
            $rekening_kredit = "P$prod.$nomer_urut";

            $angsuran_pokok = intval(intval($request->plafon_rekomendasi) / intval($request->jkw));
            $angsuran_bunga = intval($request->plafon_rekomendasi * ($request->bunga / 100 / 12));

            $data = [
                "rekening" => $rekening_kredit,
                "proposal_id" => $request->proposal_id,
                "kelompok_id" => $request->kelompok_id,
                "kredit_produk" => $request->produk_id,
                "plafon" => $request->plafon_rekomendasi,
                "bunga" => $request->bunga,
                "jkw" => $request->jkw,
                "nomer_pk" => $request->nomer_pk,
                "bakidebet" => 0,
                "admin" => $request->admin ?? 0,
                "provisi" => $request->provisi ?? 0,
                "tgl_mulai" => $request->tgl_mulai,
                "tgl_jatuh_tempo" => $request->tgl_jatuh_tempo,
                "tgl_tutup" => '2099-12-31',
                "angsuran_pokok" => $angsuran_pokok,
                "angsuran_bunga" => $angsuran_bunga,
                "date_created" => date('Y-m-d H:i:s'),
                "date_updated" => date('Y-m-d H:i:s'),
                "status" => 0,
                "user" => $request->user,
                "kolektibilitas" => 1
            ];
            DB::table('data_kredit_master')->insert($data);

            DB::table('data_kredit_detail')->where('proposal_id', $request->proposal_id)->update([
                'rekening_kredit' => $rekening_kredit
            ]);

            DB::table('data_kredit_proposal')->where('proposal_id', $request->proposal_id)->update([
                'status' => 4,
                'rekening' => $rekening_kredit
            ]);

            DB::commit();
            return response()->json([
                "success" => true,
                "message" => "Data kredit berhasil disimpan dengan rekening $rekening_kredit",
                "rekening" => $rekening_kredit,
            ]);
        } catch (QueryException $error) {
            DB::rollBack();
            return response()->json([
                "success" => false,
                "error" => $error
            ]);
        }
    }

    function getProdukKredit()
    {
        $produk = DB::select("SELECT * FROM data_kredit_produk");
        $produk_jasa = DB::select("SELECT * FROM table_kredit_jasa j JOIN data_kredit_produk p ON j.produk_id = p.id");
        foreach ($produk as $produk_kredit) {
            $id = $produk_kredit->id;
            $produk_kredit_jasa = array_filter($produk_jasa, function ($j) use ($id) {
                return $j->id == $id;
            });
            $produk_kredit->jasa = $produk_kredit_jasa;
        }
        return response()->json([
            'produk' => $produk,
            'jasa' => $produk_jasa
        ]);
    }

    function getDetailProdukKredit(Request $request)
    {
        $produk_id = $request->id;
        $produk = DB::table('data_kredit_produk')
            ->where('id', $produk_id)
            ->where('status', 1)
            ->select([
                'data_kredit_produk.*',
                DB::raw("(SELECT nama_perkiraan FROM ref_perkiraan WHERE kode_perkiraan = data_kredit_produk.jurnal_neraca) as nama_jurnal_neraca"),
                DB::raw("(SELECT nama_perkiraan FROM ref_perkiraan WHERE kode_perkiraan = data_kredit_produk.jurnal_pendapatan) as nama_jurnal_pendapatan"),
                DB::raw("(SELECT nama_perkiraan FROM ref_perkiraan WHERE kode_perkiraan = data_kredit_produk.jurnal_denda) as nama_jurnal_denda"),
                DB::raw("(SELECT nama_perkiraan FROM ref_perkiraan WHERE kode_perkiraan = data_kredit_produk.jurnal_administrasi) as nama_jurnal_administrasi"),
                DB::raw("(SELECT nama_perkiraan FROM ref_perkiraan WHERE kode_perkiraan = data_kredit_produk.jurnal_provisi) as nama_jurnal_provisi")
            ])
            ->first();
        $produk_jasa = DB::table('table_kredit_jasa')->where('produk_id', $produk_id)->orderBy('jkw')->get();
        $produk->jasa = $produk_jasa;
        return response()->json([
            'produk' => $produk,
        ]);
    }

    function simpanProdukKredit(Request $request)
    {
        DB::beginTransaction();
        $produk_id = $request->id;
        try {
            $duplikat = DB::table("data_kredit_produk")->where('id', $produk_id)->count();
            if ($duplikat != 0) {
                return response()->json([
                    'success' => false,
                    'error' => "Produk dengan ID $produk_id sudah ada"
                ]);
            }

            DB::table('data_kredit_produk')->insert([
                'id' => $request->id,
                'nama' => $request->nama,
                'inisial' => $request->inisial,
                'plafon_max' => $request->plafon_max,
                'jkw_max' => $request->jkw_max,
                'deskripsi' => $request->deskripsi,
                'jurnal_neraca' => $request->jurnal_neraca,
                'jurnal_pendapatan' => $request->jurnal_pendapatan,
                'jurnal_denda' => $request->jurnal_denda,
                'jurnal_provisi' => $request->jurnal_provisi,
                'jurnal_administrasi' => $request->jurnal_administrasi,
                'user' => $request->user,
                'status' => 1,
                'date_created' => date('Y-m-d H:i:s'),
                'date_updated' => date('Y-m-d H:i:s'),
            ]);

            foreach ($request->jasa as $jasa) {
                DB::table('table_kredit_jasa')->insert([
                    'produk_id' => $request->id,
                    'jkw' => $jasa['jkw'],
                    'bunga' => $jasa['bunga'],
                    'rule_status' => $jasa['rule_status'],
                ]);
            }

            DB::commit();
            return response()->json([
                'success' => true,
                'message' => "Data berhasil disimpan"
            ]);
        } catch (Exception $error) {
            DB::rollBack();
            return response()->json([
                'success' => false,
                'error' => $error->getMessage()
            ]);
        }
    }

    function updateProdukKredit(Request $request)
    {
        DB::beginTransaction();
        $produk_id = $request->id;
        try {
            DB::table('data_kredit_produk')
                ->where('id', $produk_id)
                ->update([
                    'nama' => $request->nama,
                    'inisial' => $request->inisial,
                    'plafon_max' => $request->plafon_max,
                    'jkw_max' => $request->jkw_max,
                    'jurnal_neraca' => $request->jurnal_neraca,
                    'jurnal_pendapatan' => $request->jurnal_pendapatan,
                    'jurnal_denda' => $request->jurnal_denda,
                    'jurnal_provisi' => $request->jurnal_provisi,
                    'jurnal_administrasi' => $request->jurnal_administrasi,
                    'deskripsi' => $request->deskripsi,
                    'user' => $request->user,
                    'date_updated' => date('Y-m-d H:i:s'),
                ]);

            DB::table('table_kredit_jasa')->where('produk_id', $produk_id)->delete();
            foreach ($request->jasa as $jasa) {
                DB::table('table_kredit_jasa')->insert([
                    'produk_id' => $request->id,
                    'jkw' => $jasa['jkw'],
                    'bunga' => $jasa['bunga'],
                    'rule_status' => $jasa['rule_status'],
                ]);
            }

            DB::commit();
            return response()->json([
                'success' => true,
                'message' => "Data berhasil disimpan"
            ]);
        } catch (Exception $error) {
            DB::rollBack();
            return response()->json([
                'success' => false,
                'error' => $error->getMessage()
            ]);
        }
    }

    function getDetailKreditAktif(Request $request)
    {
        $rekening = $request->rekening;
        $rekening = str_replace("_", "#", $rekening);

        $tanggal = $request->tanggal;
        $kredit = DB::selectOne(
            "SELECT * FROM data_kredit_master WHERE rekening = ?",
            [$rekening]
        );

        $tunggakan = DB::selectOne(
            "SELECT rekening, HitungTungPokok(rekening, ?) as tunggakan_pokok, HitungTungBunga(rekening, ?) as tunggakan_bunga FROM data_kredit_master WHERE status = '1' AND rekening = ?",
            [$tanggal, $tanggal, $rekening]
        );

        $pokok = $kredit->plafon;
        $bunga = $kredit->bunga;
        $kewajiban_bunga = intval($pokok) * floatval($bunga) / 100 / 12 * intval($kredit->jkw);

        $angsuran = DB::selectOne(
            "SELECT SUM(angsuran_pokok) as pokok, SUM(angsuran_bunga) as bunga FROM data_kredit_angsuran WHERE rekening = ? AND angsuran_tgl <= ? AND anggota_id = ''",
            [$rekening, $tanggal]
        );

        $saldo_pokok = intval($kredit->plafon) - intval($angsuran->pokok);
        $saldo_bunga = intval($kewajiban_bunga) - intval($angsuran->bunga);

        $kewajiban = DB::selectOne("SELECT tagihan_pokok as pokok, tagihan_bunga as bunga FROM data_kredit_angsuran where rekening = ? AND jenis_trans = 3 limit 1", [$rekening]);

        return response()->json([
            "rekening" => $rekening,
            "tunggakan_pokok" => $tunggakan?->tunggakan_pokok ?? 0,
            "tunggakan_bunga" => $tunggakan?->tunggakan_bunga ?? 0,
            "kewajiban_pokok" => $kewajiban?->pokok ?? 0,
            "kewajiban_bunga" => $kewajiban?->bunga ?? 0,
            "saldo_pokok" => $saldo_pokok,
            "plafon" => $kredit->plafon,
            "saldo_bunga" => $saldo_bunga,
            "jatuh_tempo" => $kredit->tgl_jatuh_tempo,
        ]);
    }

    function setPlafonAnggota(Request $request)
    {
        $rekening = $request->rekening;
        $anggota_id = $request->anggota_id;
        $plafon = $request->plafon;
        $sql = "SELECT count(*) as count FROM data_kredit_detail WHERE rekening_kredit = '$rekening' AND anggota_id = '$anggota_id'";
        $count = DB::selectOne($sql)->count;
        if ($count == 0) {
            $sql = "INSERT INTO data_kredit_detail(rekening_kredit, anggota_id, plafon, bakidebet) VALUES('$rekening','$anggota_id','$plafon','$plafon')";
            DB::insert($sql);
        } else {
            DB::table('data_kredit_detail')->where('rekening_kredit', $rekening)->where('anggota_id', $anggota_id)->update([
                'plafon' => $plafon,
                'bakidebet' => $plafon
            ]);
        }
    }

    function getPlafonAnggota(Request $request)
    {
        $tanggal = $request->tanggal ?? date('Y-m-d');
        $rekening = $request->rekening;

        try {
            $detail  = DB::select(
                "SELECT detail.rekening_kredit, detail.anggota_id, detail.pengajuan, detail.rekomendasi, detail.plafon, anggota.nama_lengkap
                 FROM data_kredit_detail detail
                 JOIN data_anggota_master anggota ON detail.anggota_id = anggota.anggota_id
                 WHERE rekening_kredit = ?",
                [$rekening]
            );
            // $detail  = DB::select(
            //     "SELECT detail.rekening_kredit, detail.anggota_id, detail.pengajuan, detail.rekomendasi, detail.plafon, anggota.nama_lengkap,
            //     HitungPinjamanSaldoAnggota(?, detail.anggota_id, ?) as bakidebet,
            //     HitungTungPokokAnggota(?, anggota.anggota_id, ?) as tagihan_pokok,
            //     HitungTungBungaAnggota(?, anggota.anggota_id, ?) as tagihan_bunga
            //     FROM data_kredit_detail detail JOIN data_anggota_master anggota ON detail.anggota_id = anggota.anggota_id WHERE rekening_kredit = ?",
            //     [$rekening, $tanggal, $rekening, $tanggal, $rekening, $tanggal, $rekening]
            // );
            $rincian_anggota = [];
            foreach ($detail as $anggota) {
                $tagihan = DB::table('data_kredit_angsuran_anggota')
                    ->where('rekening', $rekening)
                    ->where('anggota_id', $anggota->anggota_id)
                    ->where('jenis_trans', 3)
                    ->select(['tagihan_pokok', 'tagihan_bunga'])
                    ->first();

                $sql = "SELECT SUM(kredit_realisasi) - SUM(angsuran_pokok) AS nominal FROM data_kredit_angsuran_anggota angsuran WHERE angsuran.rekening = '$rekening' and angsuran.anggota_id = '" . $anggota->anggota_id . "' AND angsuran_tgl <= '$tanggal'";
                $bakidebet = DB::select($sql);

                $bakidebet = count($bakidebet) > 0 ? intval($bakidebet[0]->nominal) : 0;
                $anggota->bakidebet = $bakidebet;
                $anggota->tagihan_pokok = $tagihan->tagihan_pokok ?? 0;
                $anggota->tagihan_bunga = $tagihan->tagihan_bunga ?? 0;
                $anggota->sql = $sql;
                array_push($rincian_anggota, $anggota);
            }
            return response()->json([
                'success' => true,
                'data' => $rincian_anggota,
            ]);
        } catch (Exception $error) {
            return response()->json([
                'success' => false,
                'data' => [],
                'error' => $error->getMessage()
            ]);
        }
    }

    function getPlafonAnggotaByTransID(Request $request)
    {
        try {
            $trans_id = $request->trans_id;
            $transaksi = DB::table('data_kredit_trans_angsuran')->where('trans_id', $trans_id)->first();

            $rekening = $transaksi->rekening;
            $tanggal = $transaksi->trans_tglbuku;

            $setoran_anggota = [];

            // $sql = "SELECT HitungPinjamanSaldo() FROM data_kredit_master WHERE rekening = '$rekening'";
            $kelompok = DB::table('data_kredit_master')
                ->where('rekening', $rekening)
                ->select([
                    'rekening',
                    DB::raw("HitungPinjamanSaldo( '" . $rekening . "', '" . $tanggal . "') as bakidebet")
                ])
                ->first();
            $kelompok->bakidebet = $kelompok->bakidebet + $transaksi->pokok;

            $detail = DB::table('data_kredit_detail')
                ->join('data_anggota_master', 'data_kredit_detail.anggota_id', 'data_anggota_master.anggota_id')
                ->where('rekening_kredit', $rekening)
                ->select(['data_kredit_detail.*', 'data_anggota_master.nama_lengkap'])
                ->get();

            foreach ($detail as $anggota) {
                $setoran = DB::table('data_kredit_angsuran_anggota')
                    ->where('rekening', $rekening)
                    ->where('anggota_id', $anggota->anggota_id)
                    ->where('trans_id', $trans_id)
                    ->first();

                $tagihan = DB::table('data_kredit_angsuran_anggota')
                    ->where('rekening', $rekening)
                    ->where('anggota_id', $anggota->anggota_id)
                    ->where('jenis_trans', 3)
                    ->select(['tagihan_pokok', 'tagihan_bunga'])
                    ->first();
                $sql = "SELECT SUM(kredit_realisasi) - SUM(angsuran_pokok) AS nominal FROM data_kredit_angsuran_anggota angsuran WHERE angsuran.rekening = '$rekening' and angsuran.anggota_id = '" . $anggota->anggota_id . "' AND angsuran_tgl <= '$tanggal'";
                $bakidebet = DB::select($sql);
                $bakidebet = count($bakidebet) > 0 ? intval($bakidebet[0]->nominal) : 0;
                $bakidebet = $bakidebet + ($setoran->angsuran_pokok ?? 0);
                $tagihan_pokok = $tagihan->tagihan_pokok ?? 0;
                array_push($setoran_anggota, [
                    'rekening_kredit' => $rekening,
                    'anggota_id' => $anggota->anggota_id,
                    'pengajuan' => $anggota->pengajuan,
                    'rekomendasi' => $anggota->rekomendasi,
                    'plafon' => $anggota->plafon,
                    'bakidebet' => $bakidebet,
                    'tagihan_pokok' => $tagihan_pokok > $bakidebet ? $bakidebet : $tagihan_pokok,
                    'tagihan_bunga' => $tagihan->tagihan_bunga ?? 0,
                    'setoran_pokok' => $setoran->angsuran_pokok ?? 0,
                    'setoran_bunga' => $setoran->angsuran_bunga ?? 0,
                    'nama_lengkap' => $anggota->nama_lengkap,
                ]);
            }

            return response()->json([
                'success' => true,
                'anggota' => $setoran_anggota,
                'kelompok' => $kelompok,
            ]);
        } catch (Exception $error) {
            return response()->json([
                'success' => false,
                'error' => $error->getMessage()
            ]);
        }
    }

    function getRincianTagihan(Request $request)
    {
        $tanggal = $request->tanggal;
        $rekening = $request->rekening;

        try {
            $kelompok = DB::table('data_kredit_master', 'kredit')
                ->join('data_kelompok_master AS kelompok', 'kredit.kelompok_id', 'kelompok.kelompok_id')
                ->where('rekening', $rekening)
                ->select(
                    'kredit.*',
                    'kelompok.*',
                    DB::raw("HitungPinjamanSaldo(kredit.rekening, '$tanggal') AS saldo_pokok"),
                    DB::raw("HitungTungPokok(rekening, '$tanggal') AS tagihan_pokok"),
                    DB::raw("HitungTungBunga(rekening, '$tanggal') AS tagihan_bunga"),
                )
                ->first();

            $rincian_anggota = "SELECT detail.*, anggota.nama_lengkap,
            HitungTungPokokAnggota('$rekening', anggota.anggota_id, '$tanggal') as tagihan_pokok,
            HitungTungBungaAnggota('$rekening', anggota.anggota_id, '$tanggal') as tagihan_bunga
            FROM data_kredit_detail detail JOIN data_anggota_master anggota ON detail.anggota_id = anggota.anggota_id WHERE rekening_kredit = '$rekening'";

            $anggota  = DB::select($rincian_anggota);
            return response()->json([
                'success' => true,
                'kelompok' => $kelompok,
                'anggota' => $anggota,
                'sql' => $rincian_anggota
            ]);
        } catch (Exception $error) {
            return response()->json([
                'success' => false,
                'kelompok' => [],
                'anggota' => [],
                'error' => $error->getMessage()
            ]);
        }
    }

    function simpanSetoranAnggota(Request $request)
    {
        $trans_id = $request->trans_id;
        $daftar_anggota = $request->daftar_anggota;

        DB::beginTransaction();
        try {
            foreach ($daftar_anggota as $anggota) {
                $anggota_id = $anggota['anggota_id'];
                $transaksi_count = DB::table('data_kredit_angsuran_anggota')
                    ->where('trans_id', $trans_id)
                    ->where('anggota_id', $anggota_id)
                    ->count();
                if ($transaksi_count == 0) {
                    DB::table('data_kredit_angsuran_anggota')
                        ->insert([
                            'trans_id' => $trans_id,
                            'jenis_trans' => $request->trans_jenis,
                            'anggota_id' => $anggota_id,
                            'rekening' => $request->rekening,
                            'angsuran_pokok' => $anggota['setoran_pokok'] ?? 0,
                            'angsuran_bunga' => $anggota['setoran_bunga'] ?? 0,
                            'angsuran_tgl' => $request->trans_tglbuku
                        ]);
                } else {
                    DB::table('data_kredit_angsuran_anggota')
                        ->where('trans_id', $trans_id)
                        ->where('anggota_id', $anggota_id)
                        ->update([
                            'angsuran_pokok' => $anggota['setoran_pokok'] ?? 0,
                            'angsuran_bunga' => $anggota['setoran_bunga'] ?? 0,
                        ]);
                }
            }

            DB::commit();
            return response()->json([
                'success' => true,
                'message' => 'Data berhasil disimpan'
            ]);
        } catch (Exception $error) {
            DB::rollBack();
            return response()->json([
                'success' => false,
                'error' => $error->getMessage()
            ]);
        }
    }

    function getHistoryAngsuran(Request $request)
    {
        $rekening = $request->rekening;
        $anggota_id = $request->anggota_id;
        $tanggal = date('Y-m-d');
        if (isset($request->anggota_id)) {
            $sql = "SELECT * FROM (SELECT ka.*, IFNULL(status,1) as status FROM data_kredit_angsuran ka LEFT JOIN data_kredit_trans_angsuran kta ON ka.trans_id = kta.trans_id WHERE ka.rekening = '$rekening' AND anggota_id = '$anggota_id' AND angsuran_tgl <= '$tanggal' ORDER BY angsuran_tgl) as table_angsuran WHERE status <> '9' ORDER BY angsuran_tgl";
        } else {
            $sql = "SELECT * FROM (SELECT ka.*, IFNULL(status,1) as status FROM data_kredit_angsuran ka LEFT JOIN data_kredit_trans_angsuran kta ON ka.trans_id = kta.trans_id WHERE ka.rekening = '$rekening' AND angsuran_tgl <= '$tanggal' ORDER BY angsuran_tgl) as table_angsuran WHERE status <> '9' ORDER BY angsuran_tgl";
        }
        $history = DB::select($sql);
        return response()->json($history);
    }

    function getDetailKredit(Request $request)
    {
        try {
            $rekening = $request->rekening;
            $rekening = str_replace("_", "#", $rekening);

            //* Ambil data detail kredit dari data kredit master
            $kredit = DB::selectOne(
                "SELECT rekening, proposal_id ,kredit_produk, kelompok_id ,plafon, bunga, jkw, admin, provisi, tgl_mulai, tgl_jatuh_tempo, tgl_tutup, nomer_pk, bakidebet, kolektibilitas, kp.nama AS nama_produk, km.status, angsuran_pokok, angsuran_bunga  FROM data_kredit_master km JOIN data_kredit_produk kp ON km.kredit_produk = kp.id WHERE rekening = ?",
                [$rekening]
            );

            //* Ambil data kelompok
            $kelompok = DB::selectOne(
                "SELECT km.kelompok_id, nama_kelompok, ketua_id, nama_lengkap as nama_ketua, km.desa_id, nama_desa, kepala_desa FROM data_kelompok_master km LEFT JOIN data_anggota_master am ON km.ketua_id = am.anggota_id JOIN data_desa_master dm ON km.desa_id = dm.desa_id WHERE km.kelompok_id = ?",
                [$kredit->kelompok_id]
            );

            //* cari pinjaman ke berapa
            $pinjaman_ke = DB::selectOne(
                "SELECT HitungPinjamanKe(?,?) as number",
                [$kredit->kelompok_id, $rekening]
            );
            $kredit = (array)$kredit;
            $kredit['pinjaman_ke'] = $pinjaman_ke->number;
            $kredit = (object)$kredit;

            $anggota = DB::select(
                "SELECT * FROM data_kredit_detail detail JOIN data_anggota_master am ON detail.anggota_id = am.anggota_id JOIN data_desa_master dm ON am.desa = dm.desa_id WHERE detail.rekening_kredit = ?",
                [$rekening]
            );

            //* Ambil history angsuran kelompok dari data_kredit_angsuran
            $history_angsuran = [];
            $history_kelompok = DB::select(
                "SELECT * FROM (SELECT ka.*, IFNULL(status,1) as status FROM data_kredit_angsuran ka LEFT JOIN data_kredit_trans_angsuran kta ON ka.trans_id = kta.trans_id WHERE ka.rekening = ? ORDER BY angsuran_tgl) as table_angsuran WHERE status <> '9' AND anggota_id = '' OR anggota_id IS NULL ORDER BY angsuran_tgl, trans_id",
                [$rekening]
            );

            array_push($history_angsuran, [
                "id" => $kredit->kelompok_id,
                "nama" => $kelompok->nama_kelompok,
                "data" => $history_kelompok
            ]);


            //* Ambil history angsuran anggota dari data_kredit_angsuran_anggota
            for ($i = 0; $i < count($anggota); $i++) {

                $anggota_id = $anggota[$i]->anggota_id;
                $history_anggota = DB::select(
                    "SELECT * FROM (SELECT ka.*, IFNULL(status,1) as status FROM data_kredit_angsuran_anggota ka LEFT JOIN data_kredit_trans_angsuran kta ON ka.trans_id = kta.trans_id WHERE ka.rekening = ? AND anggota_id = ? ORDER BY angsuran_tgl) as table_angsuran WHERE status <> '9' ORDER BY angsuran_tgl, trans_id",
                    [$rekening, $anggota_id]
                );

                array_push($history_angsuran, [
                    "id" => $anggota_id,
                    "nama" => $anggota[$i]->nama_lengkap,
                    "data" => $history_anggota
                ]);
            }

            //* Ambil detail kredit per anggota
            $kredit_detail = DB::select(
                "SELECT km.rekening AS rekening_kredit, b.proposal_id ,a.anggota_id, nama_lengkap, alamat, jkw, rt, rw ,pekerjaan, ahli_waris , bunga, tgl_mulai, b.pengajuan, b.rekomendasi, b.plafon, HitungPinjamanSaldoAnggota ( km.rekening, a.anggota_id, CURRENT_DATE()) AS bakidebet  FROM ( SELECT am.anggota_id, nama_lengkap, alamat, rt, rw, pekerjaan, ahli_waris FROM data_kredit_detail kd JOIN data_anggota_master am ON kd.anggota_id = am.anggota_id WHERE rekening_kredit = ?) AS a LEFT JOIN ( SELECT *  FROM data_kredit_detail  WHERE rekening_kredit = ?) AS b ON a.anggota_id = b.anggota_id JOIN data_kredit_master km ON km.rekening = ?",
                [$rekening, $rekening, $rekening]
            );

            foreach ($kredit_detail as $detail) {
                if ($kredit->proposal_id == null || $kredit->proposal_id == "") {
                    $detail->rekomendasi = $detail->plafon;
                }
            }

            //* Ambil data persetujuan
            $proposal = DB::table('data_kredit_proposal')->where('proposal_id', $kredit->proposal_id)->first();
            if ($proposal) {
                if ($proposal->status == 3 || $proposal->status == 4) {
                    $persetujuan = DB::select(
                        "SELECT pk.*, p.pegawai_nama, rj.nama_jabatan FROM data_kredit_persetujuan pk LEFT JOIN data_pegawai p ON pk.pegawai_id = p.pegawai_id LEFT JOIN (SELECT jabatan_id, pegawai_id FROM data_jabatan WHERE tgl_menjabat = ( SELECT MAX(tgl_menjabat) FROM data_jabatan j WHERE j.pegawai_id = data_jabatan.pegawai_id ) ) tb_jab ON tb_jab.pegawai_id = pk.pegawai_id LEFT JOIN ref_jabatan rj ON tb_jab.jabatan_id = rj.id_jabatan WHERE proposal_id = ?",
                        [$kredit->proposal_id]
                    );
                } else {
                    $persetujuan = DB::select(
                        "SELECT pk.*, p.pegawai_nama, rj.nama_jabatan FROM data_kredit_persetujuan pk LEFT JOIN data_pegawai p ON pk.pegawai_id = p.pegawai_id JOIN data_kredit_komite komite ON pk.komite_id = komite.id LEFT JOIN ref_jabatan rj ON komite.jabatan_id = rj.id_jabatan WHERE proposal_id = ?",
                        [$kredit->proposal_id]
                    );
                }
            } else {
                $persetujuan = [];
            }

            //* Ambil data komite
            $komite = DB::select("SELECT k.*,p.pegawai_nama, nama_jabatan FROM data_kredit_komite k JOIN data_pegawai p ON k.pegawai_id = p.pegawai_id JOIN ref_jabatan rj ON rj.id_jabatan = k.jabatan_id WHERE k.status = '1' ORDER BY level");

            return response()->json([
                "success" => true,
                "kredit" => $kredit,
                "kredit_detail" => $kredit_detail,
                "kelompok" => $kelompok,
                "anggota" => $anggota,
                "history_angsuran" => $history_angsuran,
                "komite" => $komite,
                "persetujuan" => $persetujuan,
            ]);
        } catch (Exception $error) {
            return response()->json([
                "success" => false,
                "error" => $error->getMessage(),
                "rekening" => $rekening,
            ]);
        }
    }

    function generateJadwalAngsuran($plafon_awal, $bunga, $jkw, $tgl_mulai)
    {
        $angsuran_pokok = $plafon_awal / $jkw;
        $angsuran_bunga = $plafon_awal * ($bunga / 100) / 12;
        $total_tagihan = 0;
        $table = [];
        $total_pokok = 0;
        $total_bunga = 0;
        $total_angsuran = 0;
        $index = 0;
        for ($i = 0; $i <= $jkw; $i++) {
            $index = $index + 1;
            $tgl = Helper::jatuhTempo($tgl_mulai, $i);
            if ($i == 0) {
                $plafon = $plafon_awal;
                $tagihan_pokok = 0;
                $tagihan_bunga = 0;
                $total_tagihan = $total_tagihan + $tagihan_pokok;
                $uraian = "Pencairan";
            } else if ($i == $jkw) {
                $plafon = 0;
                $tagihan_pokok = intval($angsuran_pokok);
                $tagihan_akhir = $plafon_awal - ($tagihan_pokok * ($jkw - 1));
                $tagihan_pokok = $tagihan_akhir;
                $tagihan_bunga = intval($angsuran_bunga);
                $uraian = "Tagihan ke $i";
            } else {
                $plafon = 0;
                $tagihan_pokok = intval($angsuran_pokok);
                $tagihan_bunga = intval($angsuran_bunga);
                $total_tagihan = $total_tagihan + $tagihan_pokok;
                $uraian = "Tagihan ke $i";
            }
            $total_pokok = $total_pokok + $tagihan_pokok;
            $total_bunga = $total_bunga + $tagihan_bunga;
            $total_angsuran = $total_pokok + $total_bunga;
            $data = [
                "table_id" => $index,
                "trans_id" => "",
                "jenis_trans" => "",
                "anggota_id" => "",
                "rekening" => "",
                "kredit_plafon" => $plafon_awal,
                "kredit_realisasi" => $plafon,
                "angsuran_tanggal" => $tgl,
                "tagihan_pokok" => $tagihan_pokok,
                "tagihan_bunga" => $tagihan_bunga,
                "angsuran_pokok" => 0,
                "angsuran_bunga" => 0,
                "tunggakan_pokok" => 0,
                "tunggakan_bunga" => 0,
                "kolektibilitas" => 1,
                "total_tagihan" => $tagihan_bunga + $tagihan_pokok,
                "uraian" => $uraian,
                "tagihan" => $i
            ];
            array_push($table, $data);
        }

        return ([
            "plafon" => $plafon_awal,
            "bunga" => $bunga,
            "jangka_waktu" => $jkw,
            "total_pokok" => $total_pokok,
            "total_bunga" => $total_bunga,
            "total_angsuran" => $total_angsuran,
            "table" => $table
        ]);
    }

    function deleteTransaksiKredit(Request $request)
    {
        $trans_id = $request->trans_id;
        $trans_kode = $request->trans_kode;
        $trans_jenis = $request->trans_jenis;
        DB::beginTransaction();

        $user = User::whereRaw('BINARY username = ?', [$request->username])->first();
        if ($user && !Hash::check($request->password, $user->password)) {
            return response()->json([
                'success' => false,
                'error' => 'Password salah!',
            ]);
        }

        try {
            $data = ['status' => $trans_jenis];
            $transaksi_angsuran_kredit = ['22.1', '23.1'];
            $transaksi_realisasi_kredit = ['21'];
            if (in_array($trans_kode, $transaksi_angsuran_kredit)) { //transaksi angsuran kredit
                $table = "data_kredit_trans_angsuran";
                $transaksi = DB::table('data_kredit_trans_angsuran')->where('trans_id', $trans_id)->first();
                // jika transaksi di delete maka hapus di data_kredit_angsuran
                if ($trans_jenis == '9') {
                    if ($trans_kode == '23.1') {
                        DB::table('data_kredit_master')->where('rekening', $transaksi->rekening)->update([
                            'status' => '1',
                            'tgl_tutup' => '2099-12-31'
                        ]);
                    }
                    DB::table('data_kredit_angsuran')->where('trans_id', $trans_id)->delete();
                }
            }

            if (in_array($trans_kode, $transaksi_realisasi_kredit)) { //transaksi realisasi kredit

                $table = "data_kredit_trans_realisasi";
                $transaksi = DB::table('data_kredit_trans_realisasi')->where('trans_id', $trans_id)->first();

                $trans_angsuran = DB::table('data_kredit_trans_angsuran')->where('rekening', $transaksi->rekening)->where('status', 1)->count();
                if ($trans_angsuran > 0) {
                    throw new Exception("Transaksi tidak bisa dibatalkan karena sudah ada transaksi angsuran kredit");
                }

                if ($trans_jenis == '1') {
                    DB::table('data_kredit_trans_realisasi')->where('trans_id', $trans_id)->update(['status' => 1]);
                    $this->generateNewTableAngsuran($trans_id, $transaksi->rekening, 1);
                }
                DB::table('data_kredit_master')->where('rekening', $transaksi->rekening)->update(['status' => 1]);
                // jika transaksi di delete maka hapus di data_kredit_angsuran
                if ($trans_jenis == '9') {
                    DB::table('data_kredit_master')->where('rekening', $transaksi->rekening)->update(['status' => 0]);
                    DB::table('data_kredit_angsuran')->where('rekening', $transaksi->rekening)->delete();
                }
            }
            DB::table($table)->where('trans_id', $trans_id)->update($data);
            DB::table('data_trans_jurnal')->where('trans_id', $trans_id)->update($data);
            DB::commit();

            return response()->json([
                "success" => true,
                "error" => "Transaksi berhasil dibatalkan"
            ]);
        } catch (Exception $error) {
            DB::rollBack();
            return response()->json([
                "success" => false,
                "error" => $error->getMessage()
            ]);
        }
    }

    function getDaftarTagihan(Request $request)
    {
        $tanggal = $request->tanggal;

        $daftar = DB::table('data_kredit_master', 'kredit')
            ->join('data_kelompok_master AS kelompok', 'kredit.kelompok_id', 'kelompok.kelompok_id')
            ->join('data_desa_master AS desa', 'desa.desa_id', 'kelompok.desa_id')
            ->where(DB::raw('RIGHT(tgl_jatuh_tempo,2)'), substr($tanggal, 8, 2))
            ->where('kredit.status', 1)
            ->select(
                'kredit.*',
                DB::raw("HitungPinjamanSaldo(kredit.rekening, '$tanggal') AS saldo_pokok"),
                DB::raw("HitungTungPokok(kredit.rekening, '$tanggal') AS tagihan_pokok"),
                DB::raw("HitungTungBunga(kredit.rekening, '$tanggal') AS tagihan_bunga"),
                'kelompok.nama_kelompok',
                'desa.nama_desa'
            )
            ->get();


        // cek apakah besok hari Minggu, jika besok hari minggu tampilkan data tagihan untuk hari besok
        $besok = date('Y-m-d', strtotime($tanggal . ' +1 day'));
        $hari_besok = date('w', strtotime($besok));
        // Mengecek apakah hari esok adalah Minggu
        if ($hari_besok == 0) {
            $tagihan_besok = DB::table('data_kredit_master', 'kredit')
                ->join('data_kelompok_master AS kelompok', 'kredit.kelompok_id', 'kelompok.kelompok_id')
                ->join('data_desa_master AS desa', 'desa.desa_id', 'kelompok.desa_id')
                ->where(DB::raw('RIGHT(tgl_jatuh_tempo,2)'), substr($besok, 8, 2))
                ->where('kredit.status', 1)
                ->select(
                    'kredit.*',
                    DB::raw("HitungPinjamanSaldo(kredit.rekening, '$besok') AS saldo_pokok"),
                    DB::raw("HitungTungPokok(kredit.rekening, '$besok') AS tagihan_pokok"),
                    DB::raw("HitungTungBunga(kredit.rekening, '$besok') AS tagihan_bunga"),
                    'kelompok.nama_kelompok',
                    'desa.nama_desa'
                )
                ->get();
            $daftar->merge($tagihan_besok);
        }


        return response()->json([
            'success' => true,
            'tgl' => substr($tanggal, 8, 2),
            'besok' => $hari_besok,
            'daftar_besok' => $tagihan_besok ?? [],
            'daftar' => $daftar,
        ]);
    }

    function simpanProposalKredit(Request $request)
    {
        DB::beginTransaction();
        try {
            $date = date('Y-m-d H:i:s');
            $daftar_anggota = $request->daftar_anggota;

            $tahun = date('Y');
            $subTahun = substr($tahun, 2, 2);

            $proposal_count = DB::table('data_kredit_proposal')
                ->where(DB::raw("YEAR(date_created)"), $tahun)
                ->count();
            $proposal_number = str_pad($proposal_count + 1, 4, '0', STR_PAD_LEFT);
            $proposal_id = "PR$subTahun$proposal_number";

            $kelompok = DB::table('data_kelompok_master')->where('kelompok_id', $request->kelompok)->first();

            DB::table('data_kredit_proposal')->insert([
                'proposal_id' => $proposal_id,
                'kelompok_id' => $request->kelompok,
                'ketua_kelompok' => $kelompok->ketua_id,
                'kredit_produk' => $request->produk,
                'plafon' => $request->plafon,
                'bunga' => $request->bunga,
                'jkw' => $request->jkw,
                'tanggal' => $request->tanggal ?? date('Y-m-d'),
                'date_created' => $date,
                'date_updated' => $date,
                'user_create' => $request->user,
                'user_update' => $request->user,
                'status' => 1,
            ]);

            foreach ($daftar_anggota as $anggota) {
                DB::table('data_kredit_detail')->insert([
                    'proposal_id' => $proposal_id,
                    'anggota_id' => $anggota['anggota_id'],
                    'pengajuan' => $anggota['pengajuan'],
                ]);
            }

            DB::commit();
            return response()->json([
                'success' => true,
                'message' => 'Data berhasil disimpan',
                'proposal_id' => $proposal_id
            ]);
        } catch (Exception $error) {
            DB::rollBack();
            return response()->json([
                'success' => false,
                'error' => $error->getMessage()
            ]);
        }
    }

    function updateProposalKredit(Request $request)
    {
        DB::beginTransaction();
        try {
            $proposal_id = $request->proposal_id;
            $date = date('Y-m-d H:i:s');
            $daftar_anggota = $request->daftar_anggota;

            DB::table('data_kredit_proposal')
                ->where('proposal_id', $proposal_id)
                ->update([
                    'kelompok_id' => $request->kelompok,
                    'kredit_produk' => $request->produk,
                    'plafon' => $request->plafon,
                    'bunga' => $request->bunga,
                    'jkw' => $request->jkw,
                    'tanggal' => $request->tanggal ?? date('Y-m-d'),
                    'date_updated' => $date,
                    'user_update' => $request->user,
                    'status' => 1,
                ]);

            foreach ($daftar_anggota as $anggota) {
                DB::table('data_kredit_detail')
                    ->where('proposal_id', $proposal_id)
                    ->where('anggota_id', $anggota['anggota_id'])
                    ->update([
                        'pengajuan' => $anggota['pengajuan'],
                    ]);
            }

            DB::commit();
            return response()->json([
                'success' => true,
                'message' => 'Data berhasil diupdate',
                'proposal_id' => $proposal_id
            ]);
        } catch (Exception $error) {
            DB::rollBack();
            return response()->json([
                'success' => false,
                'error' => $error->getMessage()
            ]);
        }
    }

    function getDaftarProposal()
    {
        try {
            $daftar = DB::table('data_kredit_proposal', 'proposal')
                ->leftJoin('data_kelompok_master AS kelompok', 'kelompok.kelompok_id', 'proposal.kelompok_id')
                ->leftJoin('data_desa_master AS desa', 'kelompok.desa_id', 'desa.desa_id')
                ->leftJoin('data_kredit_produk AS produk', 'proposal.kredit_produk', 'produk.id')
                // ->where('proposal.status', 1)
                ->select(['proposal.*', 'kelompok.nama_kelompok', 'desa.desa_id', 'desa.nama_desa', 'produk.nama AS nama_produk', 'produk.inisial AS inisial'])
                ->orderBy('proposal_id', 'desc')
                ->get();

            return response()->json([
                'success' => true,
                'data' => $daftar,
            ]);
        } catch (Exception $error) {
            return response()->json([
                'success' => false,
                'error' => $error->getMessage(),
            ]);
        }
    }

    function getDetailProposal(Request $request)
    {
        $proposal_id = $request->proposal_id;

        try {
            $proposal = DB::table('data_kredit_proposal', 'proposal')
                ->leftJoin('data_kelompok_master AS kelompok', 'kelompok.kelompok_id', 'proposal.kelompok_id')
                ->leftJoin('data_desa_master AS desa', 'kelompok.desa_id', 'desa.desa_id')
                ->leftJoin('data_kredit_produk AS produk', 'proposal.kredit_produk', 'produk.id')
                ->where('proposal.proposal_id', $proposal_id)
                ->select(['proposal.*', 'kelompok.nama_kelompok', 'desa.desa_id', 'desa.nama_desa', 'produk.nama AS nama_produk'])
                ->first();

            $anggota = DB::table('data_kredit_detail', 'detail')
                ->leftJoin('data_anggota_master AS anggota', 'anggota.anggota_id', 'detail.anggota_id')
                ->where('proposal_id', $proposal_id)
                ->select(['detail.*', 'anggota.nama_lengkap', 'anggota.nik', 'anggota.tanggal_lahir', 'anggota.alamat'])
                ->get();

            $proposal->daftar_anggota = $anggota;

            return response()->json([
                'success' => true,
                'data' => $proposal
            ]);
        } catch (Exception $error) {
            return response()->json([
                'success' => false,
                'error' => $error->getMessage()
            ]);
        }
    }

    function simpanAnalisaPinjaman(Request $request)
    {
        DB::beginTransaction();
        try {
            $proposal_id = $request->proposal_id;
            $date = date('Y-m-d H:i:s');
            $daftar_anggota = $request->daftar_anggota;

            DB::table('data_kredit_proposal')
                ->where('proposal_id', $proposal_id)
                ->update([
                    'plafon_rekomendasi' => $request->rekomendasi,
                    'date_updated' => $date,
                    'user_update' => $request->user,
                ]);

            foreach ($daftar_anggota as $anggota) {
                DB::table('data_kredit_detail')
                    ->where('proposal_id', $proposal_id)
                    ->where('anggota_id', $anggota['anggota_id'])
                    ->update([
                        'pengajuan' => $anggota['pengajuan'],
                        'rekomendasi' => $anggota['rekomendasi'] ?? 0,
                        'plafon_rekomendasi' => $anggota['rekomendasi'] ?? 0,
                        'penghasilan_kotor' => $anggota['penghasilan_kotor'],
                        'pengeluaran' => $anggota['pengeluaran'],
                        'catatan_pengajuan' => $anggota['catatan_pengajuan'],
                        'catatan_verifikasi' => $anggota['catatan_verifikasi'],
                        'character' => $anggota['character'],
                        'capacity' => $anggota['capacity'],
                        'capital' => $anggota['capital'],
                        'cashflow' => $anggota['cashflow'],
                        'condition' => $anggota['condition'],
                    ]);
            }

            DB::commit();
            return response()->json([
                'success' => true,
                'message' => 'Data berhasil diupdate',
                'proposal_id' => $proposal_id
            ]);
        } catch (Exception $error) {
            DB::rollBack();
            return response()->json([
                'success' => false,
                'error' => $error->getMessage()
            ]);
        }
    }

    function verifikasiAnalisaPinjaman(Request $request)
    {
        DB::beginTransaction();
        try {
            $user = User::whereRaw('BINARY username = ?', [$request->username])->first();
            if ($user && !Hash::check($request->password, $user->password)) {
                return response()->json([
                    'success' => false,
                    'error' => 'Password salah!',
                ]);
            }

            $proposal_id = $request->proposal_id;
            $date = date('Y-m-d H:i:s');

            DB::table('data_kredit_proposal')
                ->where('proposal_id', $proposal_id)
                ->update([
                    'date_verified' => $date,
                    'user_verifikasi' => $request->user,
                    'status' => 2
                ]);

            DB::commit();
            return response()->json([
                'success' => true,
                'message' => 'Proposal berhasil diverifikasi',
            ]);
        } catch (Exception $error) {
            DB::rollBack();
            return response()->json([
                'success' => false,
                'error' => $error->getMessage()
            ]);
        }
    }

    function deleteTransaksiAngsuran(Request $request)
    {
        $trans_id = $request->trans_id;

        DB::beginTransaction();
        try {
            $transaksi = DB::table('data_kredit_trans_angsuran')->where('trans_id', $trans_id)->first();

            DB::table('data_kredit_trans_angsuran')->where('trans_id', $trans_id)->update(['status' => 9]);
            DB::table('data_trans_jurnal')->where('trans_id', $trans_id)->update(['status' => 9]);

            // jika transaksi adalah pelunasan maka ganti status kredit menjadi aktif kembali
            if ($transaksi->trans_kode == '23.1') {
                DB::table('data_kredit_master')->where('rekening', $transaksi->rekening)->update([
                    'status' => 1,
                    'tgl_tutup' => '2999-12-31'
                ]);
            }

            DB::table('data_kredit_angsuran')->where('trans_id', $trans_id)->delete();

            //* update bakidebet
            DB::update("UPDATE data_kredit_master SET bakidebet = HitungPinjamanSaldo(rekening, CURRENT_DATE()) WHERE rekening = ?", [$transaksi->rekening]);

            DB::commit();
            return response()->json([
                "success" => true,
                "message" => "Transaksi berhasil dibatalkan"
            ]);
        } catch (Exception $error) {
            DB::rollBack();
            return response()->json([
                "success" => false,
                "error" => $error->getMessage()
            ]);
        }
    }

    function validasiTransaksiAngsuran(Request $request)
    {
        $trans_id = $request->trans_id;

        DB::beginTransaction();
        try {
            $transaksi = DB::table('data_kredit_trans_angsuran')->where('trans_id', $trans_id)->first();

            DB::table('data_kredit_trans_angsuran')->where('trans_id', $trans_id)->update(['status' => 1]);
            DB::table('data_trans_jurnal')->where('trans_id', $trans_id)->update(['status' => 1]);

            DB::table('data_kredit_angsuran')->insert([
                "trans_id" => $trans_id,
                "jenis_trans" => $transaksi->trans_jenis,
                "rekening" => $transaksi->rekening,
                "kredit_plafon" => 0,
                "kredit_realisasi" => 0,
                "angsuran_tgl" => $transaksi->trans_tglbuku,
                "tagihan_pokok" => 0,
                "tagihan_bunga" => 0,
                "angsuran_pokok" => $transaksi->pokok,
                "angsuran_bunga" => $transaksi->bunga,
                "tunggakan_pokok" => 0,
                "tunggakan_bunga" => 0,
                "kolektibilitas" => 1,
            ]);
            // jika transaksi pelunasan maka ganti status dan update tanggal tutup
            if ($transaksi->trans_kode == '23.1') {
                DB::table('data_kredit_master')->where('rekening', $transaksi->rekening)->update([
                    'status' => 3,
                    'tgl_tutup' => $transaksi->trans_tglbuku
                ]);
            }

            //* update bakidebet
            DB::update("UPDATE data_kredit_master SET bakidebet = HitungPinjamanSaldo(rekening, CURRENT_DATE()) WHERE rekening = ?", [$transaksi->rekening]);

            DB::commit();
            return response()->json([
                "success" => true,
                "message" => "Transaksi berhasil divalidasi"
            ]);
        } catch (Exception $error) {
            DB::rollBack();
            return response()->json([
                "success" => false,
                "error" => $error->getMessage()
            ]);
        }
    }

    function deleteTransaksiRealisasi(Request $request)
    {
        $trans_id = $request->trans_id;

        DB::beginTransaction();
        try {
            $transaksi = DB::table('data_kredit_trans_realisasi')->where('trans_id', $trans_id)->first();

            $trans_angsuran = DB::table('data_kredit_trans_angsuran')->where('rekening', $transaksi->rekening)->where('status', 1)->count();
            if ($trans_angsuran > 0) {
                throw new Exception("Transaksi tidak bisa dibatalkan karena sudah ada transaksi angsuran kredit.");
            }

            //* update status transaksi menjadi 9
            DB::table('data_kredit_trans_realisasi')->where('trans_id', $trans_id)->update(['status' => 9]);
            DB::table('data_trans_jurnal')->where('trans_id', $trans_id)->update(['status' => 9]);

            //* kembalikan status kredit menjadi 0
            DB::table('data_kredit_master')->where('rekening', $transaksi->rekening)->update(['status' => 0]);
            //* hapus table angsuran kelompok
            DB::table('data_kredit_angsuran')->where('rekening', $transaksi->rekening)->delete();
            //* hapus table angsuran anggota
            DB::table('data_kredit_angsuran_anggota')->where('rekening', $transaksi->rekening)->delete();

            //* kembalikan plafon dari data_kredit_detail menjadi 0
            DB::table('data_kredit_detail')->where('rekening_kredit', $transaksi->rekening)->update(['bakidebet' => 0, 'plafon' => 0]);

            DB::commit();
            return response()->json([
                "success" => true,
                "message" => "Transaksi berhasil dibatalkan"
            ]);
        } catch (Exception $error) {
            DB::rollBack();
            return response()->json([
                "success" => false,
                "error" => $error->getMessage()
            ]);
        }
    }

    function validasiTransaksiRealisasi(Request $request)
    {
        $trans_id = $request->trans_id;

        DB::beginTransaction();
        try {

            $transaksi = DB::table('data_kredit_trans_realisasi')->where('trans_id', $trans_id)->first();
            $trans_validasi =  DB::table('data_kredit_trans_realisasi')->where('status', 1)->where('rekening', $transaksi->rekening)->get();
            if (count($trans_validasi) > 0) {
                return response()->json([
                    'success' => false,
                    'error' => 'Rekening ' . $transaksi->rekening . ' sudah dilakukan realisasi dengan transid ' . $trans_validasi[0]->trans_id
                ]);
            }

            //* update status transaksi menjadi 1
            DB::table('data_kredit_trans_realisasi')->where('trans_id', $trans_id)->update(['status' => 1]);
            DB::table('data_trans_jurnal')->where('trans_id', $trans_id)->update(['status' => 1]);

            //* generate kembali table angsuran
            $this->generateNewTableAngsuran($trans_id, $transaksi->rekening, 1);

            //* update status kredit menjadi 1 : aktif
            DB::table('data_kredit_master')->where('rekening', $transaksi->rekening)->update(['status' => 1]);

            //* update plafon menjadi senilai plafon yang direkomendasikan
            $kredit_detail = DB::table('data_kredit_detail')->where('rekening_kredit', $transaksi->rekening)->get();
            foreach ($kredit_detail as $detail) {
                DB::table('data_kredit_detail')->where('rekening_kredit', $transaksi->rekening)
                    ->where('anggota_id', $detail->anggota_id)
                    ->update(['plafon' => $detail->rekomendasi]);
            }

            DB::commit();
            return response()->json([
                "success" => true,
                "message" => "Transaksi berhasil divalidasi"
            ]);
        } catch (Exception $error) {
            DB::rollBack();
            return response()->json([
                "success" => false,
                "error" => $error->getMessage()
            ]);
        }
    }

    function deleteTransaksiRestruk(Request $request)
    {
        DB::beginTransaction();
        try {
            $trans_restruk = DB::table('data_kredit_trans_restruk')->where('trans_id', $request->trans_id)->first();
            $trans_pelunasan = DB::table('data_kredit_trans_angsuran')->where('trans_id', $trans_restruk->trans_id_pelunasan)->first();

            $this->deleteTransaksiAngsuran(new Request(["trans_id" => $trans_restruk->trans_id_pelunasan]));
            $this->deleteTransaksiRealisasi(new Request(["trans_id" => $trans_restruk->trans_id_realisasi]));

            // DB::table('data_kredit_angsuran')->where('trans_id', $trans_restruk->trans_id_pelunasan)->delete();
            // DB::table('data_kredit_angsuran')->where('trans_id', $trans_restruk->trans_id_realisasi)->delete();

            DB::table('data_kredit_trans_restruk')->where('trans_id', $request->trans_id)->update(["status" => 9]);
            // DB::table('data_kredit_master')->where('rekening', $trans_restruk->rekening)->update(['status' => 99]);
            DB::table('data_kredit_master')->where('rekening', $trans_restruk->rekening)->delete();

            DB::table('data_kredit_master')->where('rekening', $trans_pelunasan->rekening)->update(
                [
                    'status' => 1,
                    'tgl_tutup' => '2999-12-31',
                    // 'bakidebet' => $trans_restruk->bakidebet
                ]
            );

            DB::commit();
            return response()->json([
                'success' => true,
                'message' => "Transaksi berhasil dibatalkan"
            ]);
        } catch (Exception $error) {
            DB::rollBack();
            return response()->json([
                'success' => false,
                'error' => $error->getMessage()
            ]);
        }
    }

    function validasiTransaksiRestruk(Request $request)
    {
        DB::beginTransaction();
        try {
            $trans_restruk = DB::table('data_kredit_trans_restruk')->where('trans_id', $request->trans_id)->first();

            $trans_pelunasan = DB::table('data_kredit_trans_angsuran')->where('trans_id', $trans_restruk->trans_id_pelunasan)->first();

            $sudah_ada_rekening = DB::table('data_kredit_master')->where('rekening', $trans_restruk->rekening)->where('status', 1)->count() > 0;

            if ($sudah_ada_rekening) {
                return response()->json([
                    'success' => false,
                    'error' => "Transaksi tidak bisa divalidasi karena sudah ada restruk dengan rekening " . $trans_restruk->rekening
                ]);
                // throw new Exception("Transaksi tidak bisa divalidasi karena sudah ada restruk dengan rekening " . $trans_restruk->rekening);
            }

            $this->validasiTransaksiAngsuran(new Request(["trans_id" => $trans_restruk->trans_id_pelunasan]));
            $this->validasiTransaksiRealisasi(new Request(["trans_id" => $trans_restruk->trans_id_realisasi]));

            DB::table('data_kredit_master')->where('rekening', $trans_pelunasan->rekening)->update(["status" => 4, 'tgl_tutup' => $trans_pelunasan->trans_tglbuku]);

            DB::table('data_kredit_master')->where('rekening', $trans_restruk->rekening)->update(["status" => 1]);
            $kredit = DB::table('data_kredit_master')->where('rekening', $trans_pelunasan->rekening)->first();

            $angsuran_pokok = intval(intval($trans_restruk->plafon_restruk) / intval($trans_restruk->jkw_restruk));
            $angsuran_bunga = intval($trans_restruk->plafon_restruk * ($trans_restruk->bunga_restruk / 100 / 12));
            $jatuh_tempo_restruk = Helper::jatuhTempo($trans_restruk->tgl_mulai_restruk, $trans_restruk->jkw_restruk);

            DB::table('data_kredit_master')->insert([
                "rekening" => $trans_restruk->rekening,
                "proposal_id" => "",
                "kelompok_id" => $kredit->kelompok_id,
                "kredit_produk" => $kredit->kredit_produk,
                "plafon" => $trans_restruk->plafon_restruk,
                "bunga" => $trans_restruk->bunga_restruk,
                "jkw" => $trans_restruk->jkw_restruk,
                "nomer_pk" => $trans_restruk->nomer_pk,
                "bakidebet" => $trans_restruk->bakidebet,
                "admin" => $trans_restruk->admin_restruk ?? 0,
                "provisi" => $trans_restruk->provisi_restruk ?? 0,
                "tgl_mulai" => $trans_restruk->tgl_mulai_restruk,
                "tgl_jatuh_tempo" => $jatuh_tempo_restruk,
                "tgl_tutup" => '2099-12-31',
                "angsuran_pokok" => $angsuran_pokok,
                "angsuran_bunga" => $angsuran_bunga,
                "date_created" => date('Y-m-d H:i:s'),
                "date_updated" => date('Y-m-d H:i:s'),
                "status" => 1,
                "user" => $trans_restruk->user,
                "kolektibilitas" => 1
            ]);

            DB::table('data_kredit_trans_restruk')->where('trans_id', $request->trans_id)->update(["status" => 1]);

            //* GENERATE TABLE ANGSURAN KELOMPOK
            Helper::generateTableAngsuranKelompok3($trans_restruk->rekening, $trans_restruk->trans_id_realisasi);
            //* GENERATE TABLE ANGSURAN KELOMPOK

            DB::commit();
            return response()->json([
                'success' => true,
                'message' => "Transaksi berhasil divalidasi"
            ]);
        } catch (Exception $error) {
            DB::rollBack();
            return response()->json([
                'success' => false,
                'error' => $error->getMessage()
            ]);
        }
    }

    function generateNewTableAngsuran($trans_id, $rekening, $status)
    {
        $sql = "SELECT * FROM data_kredit_master WHERE rekening = '$rekening'";
        $kredit = DB::selectOne($sql);

        $plafon_kelompok = $kredit->plafon;
        $bunga = $kredit->bunga;
        $jkw = $kredit->jkw;
        $tgl_mulai = $kredit->tgl_mulai;

        $sql = "SELECT * FROM data_kredit_detail WHERE rekening_kredit = '$rekening'";
        $anggota = DB::select($sql);

        if ($status == '1') {
            try {

                DB::table('data_kredit_angsuran_anggota')->where('rekening', $rekening)->whereIn('jenis_trans', [1, 2, 3])->delete();

                //* Generate Jadwal Individu
                for ($i = 0; $i < count($anggota); $i++) {
                    $anggota_id = $anggota[$i]->anggota_id;
                    $plafon_anggota = $anggota[$i]->rekomendasi;
                    Helper::generateTableAngsuranAnggota($trans_id, $anggota_id, $kredit->rekening, $plafon_anggota, $bunga, $jkw, $tgl_mulai);
                }

                //* Generate Jadwal Kelompok
                return Helper::generateTableAngsuranKelompok($trans_id, $kredit->rekening, $plafon_kelompok, $tgl_mulai);
            } catch (Exception $error) {
                throw $error;
            }
        }
    }

    // //*TRANSAKSI REALISASI KREDIT -> GENERATE TABLE ANGSURAN ANGGOTA
    // function generateTableAngsuranAnggota($trans_id, $anggota_id, $rekening, $plafon_awal, $bunga, $jkw, $tgl_mulai)
    // {
    //     try {
    //         DB::table('data_kredit_detail')->where('rekening_kredit', $rekening)->where('anggota_id', $anggota_id)->update([
    //             'plafon' => $plafon_awal
    //         ]);

    //         $angsuran_pokok = $plafon_awal / $jkw;
    //         $angsuran_bunga = $plafon_awal * ($bunga / 100) / 12;
    //         $total_tagihan = 0;
    //         $total_pokok = 0;
    //         $total_bunga = 0;
    //         $index = 0;
    //         $data = [
    //             "trans_id" => "",
    //             "jenis_trans" => 1,
    //             "anggota_id" => $anggota_id,
    //             "rekening" => $rekening,
    //             "kredit_plafon" => $plafon_awal,
    //             "kredit_realisasi" => 0,
    //             "angsuran_tgl" => $tgl_mulai,
    //             "tagihan_pokok" => 0,
    //             "tagihan_bunga" => 0,
    //             "angsuran_pokok" => 0,
    //             "angsuran_bunga" => 0,
    //             "tunggakan_pokok" => 0,
    //             "tunggakan_bunga" => 0,
    //             "kolektibilitas" => 1,
    //         ];
    //         DB::table("data_kredit_angsuran_anggota")->insert($data);
    //         for ($i = 0; $i <= $jkw; $i++) {
    //             $index = $index + 1;
    //             $tgl = Helper::jatuhTempo($tgl_mulai, $i);
    //             if ($i == 0) {
    //                 $plafon = 0;
    //                 $kredit_realisasi = $plafon_awal;
    //                 $tagihan_pokok = 0;
    //                 $tagihan_bunga = 0;
    //                 $total_tagihan = $total_tagihan + $tagihan_pokok;
    //                 $id_trans = $trans_id;
    //                 $jenis_trans = 2;
    //             } else if ($i == $jkw) {
    //                 $plafon = 0;
    //                 $kredit_realisasi = 0;
    //                 $tagihan_pokok = intval($angsuran_pokok);
    //                 $tagihan_akhir = $plafon_awal - ($tagihan_pokok * ($jkw - 1));
    //                 $tagihan_pokok = $tagihan_akhir;
    //                 $tagihan_bunga = intval($angsuran_bunga);
    //                 $id_trans = "";
    //                 $jenis_trans = 3;
    //             } else {
    //                 $plafon = 0;
    //                 $kredit_realisasi = 0;
    //                 $tagihan_pokok = intval($angsuran_pokok);
    //                 $tagihan_bunga = intval($angsuran_bunga);
    //                 $total_tagihan = $total_tagihan + $tagihan_pokok;
    //                 $id_trans = "";
    //                 $jenis_trans = 3;
    //             }
    //             $total_pokok = $total_pokok + $tagihan_pokok;
    //             $total_bunga = $total_bunga + $tagihan_bunga;
    //             $data = [
    //                 "trans_id" => $id_trans,
    //                 "jenis_trans" => $jenis_trans,
    //                 "anggota_id" => $anggota_id,
    //                 "rekening" => $rekening,
    //                 "kredit_plafon" => $plafon,
    //                 "kredit_realisasi" => $kredit_realisasi,
    //                 "angsuran_tgl" => $tgl,
    //                 "tagihan_pokok" => $tagihan_pokok,
    //                 "tagihan_bunga" => $tagihan_bunga,
    //                 "angsuran_pokok" => 0,
    //                 "angsuran_bunga" => 0,
    //                 "tunggakan_pokok" => 0,
    //                 "tunggakan_bunga" => 0,
    //                 "kolektibilitas" => 1,
    //             ];
    //             DB::table("data_kredit_angsuran_anggota")->insert($data);
    //         }
    //     } catch (Exception $error) {
    //         throw $error;
    //     }
    // }

    function refreshTableKreditAngsuranAnggota(Request $request)
    {
        DB::beginTransaction();
        $rekening = str_replace("_", "#", $request->rekening);
        // dapatkan data kredit
        $kredit = DB::table('data_kredit_master', 'master')
            ->join('data_kredit_produk AS produk', 'master.kredit_produk', 'produk.id')
            ->where('master.rekening', $rekening)->first();
        // jika kredit sudah aktif / sudah di realisasi
        if ($kredit->status == 1) {
            // dapatkan transaksi realisasi
            $trans = DB::table('data_kredit_trans_realisasi')->where('rekening', $rekening)->where('status', 1)->first();
            // dapatkan daftar anggota di kredit detail
            $anggota = DB::table('data_kredit_detail')->where('rekening_kredit', $rekening)->get();
            // hapus table sebelumnya, kecuali data transaksi
            DB::table('data_kredit_angsuran_anggota')->where('rekening', $rekening)->whereIn('jenis_trans', [1, 2, 3])->delete();

            $transaksi = DB::table('data_kredit_trans_realisasi')->where('rekening', $rekening)->where('status', 1)->first();

            // looping untuk membuat table
            for ($i = 0; $i < count($anggota); $i++) {
                Helper::generateTableAngsuranAnggota(
                    $trans?->trans_id,
                    $anggota[$i]->anggota_id,
                    $rekening,
                    intval($anggota[$i]->plafon),
                    $kredit->bunga,
                    $kredit->jkw,
                    $kredit->tgl_mulai
                );
            }

            Helper::generateTableAngsuranKelompok($transaksi?->trans_id, $rekening, $kredit->plafon, $kredit->tgl_mulai);

            // $this->generateNewTableAngsuran($transaksi->trans_id, $request->rekening, 1);

            DB::commit();
            return response()->json([
                'success' => true,
                'message' => 'Table anggota sudah diperbaiki',
                'anggota' => $anggota,
                'kredit' => $kredit
            ]);
        } else {
            // jika belum
            return response()->json([
                'success' => false,
                'error' => 'Kredit belum aktif'
            ]);
        }
        try {
        } catch (Exception $error) {
            DB::rollBack();
            return response()->json([
                'success' => false,
                'error' => $error->getMessage()
            ]);
        }
    }

    function simpanRincianDetailAnggota(Request $request)
    {
        DB::beginTransaction();
        try {
            $rekening = str_replace("_", "#", $request->rekening);
            $jumlah_anggota = DB::table('data_kredit_detail')->where('rekening_kredit', $rekening)->get();
            // jika jumlah anggota baru berbeda
            if (count($jumlah_anggota) != count($request->anggota)) {
                $new_daftar_anggota = array_map(
                    function ($agt) {
                        return $agt['anggota_id'];
                    },
                    $request->anggota
                );
                // hapus anggota yang diluar anggota baru
                DB::table('data_kredit_detail')->where('rekening_kredit', $rekening)->whereNotIn('anggota_id', $new_daftar_anggota)->delete();
            }

            for ($i = 0; $i < count($request->anggota); $i++) {
                $anggota = $request->anggota[$i];
                // jika anggota berbeda/berubah
                if (!in_array($anggota['anggota_id'], $jumlah_anggota->toArray())) {
                    // hapus anggota lama
                    DB::table('data_kredit_detail')->where('rekening_kredit', $rekening)->where('anggota_id', $anggota['anggota_id'])->delete();
                    // insert anggota baru
                    DB::table('data_kredit_detail')
                        ->insert([
                            'rekening_kredit' => $rekening,
                            'anggota_id' => $anggota['anggota_id'],
                            'pengajuan' => $anggota['plafon'] ?? 0,
                            'rekomendasi' => $anggota['plafon'] ?? 0,
                            'plafon' => $anggota['plafon'] ?? 0,
                            'bakidebet' => 0,
                        ]);
                } else {
                    // jika tidak lakukan update
                    DB::table('data_kredit_detail')
                        ->where('rekening_kredit', $rekening)
                        ->where('anggota_id', $anggota['anggota_id'])
                        ->update([
                            'rekomendasi' => $anggota['plafon'] ?? 0,
                            'plafon' => $anggota['plafon'] ?? 0,
                        ]);
                }
            }

            $this->refreshTableKreditAngsuranAnggota($request);

            DB::commit();
            return response()->json([
                'success' => true,
                'message' => "Rincian anggota sudah disimpan",
            ]);
        } catch (Exception $error) {
            DB::rollBack();
            return response()->json([
                'success' => false,
                'error' => $error->getMessage(),
                'anggota' => $request->anggota
            ]);
        }
    }

    // ! TESTING
    function testGenerateAngsuranAnggota()
    {
        $array = [];
        for ($i = 0; $i < 1; $i++) {
            array_push(
                $array,
                $this->tesGenerateTableAngsuranAnggota(
                    '$trans->trans_id',
                    'anggota $i',
                    '123',
                    1000000,
                    12,
                    12,
                    '2014-07-15'
                )
            );
        }
        return response()->json($array);
    }

    // ! TESTING

    function tesGenerateTableAngsuranAnggota($trans_id, $anggota_id, $rekening, $plafon_awal, $bunga, $jkw, $tgl_mulai)
    {
        try {
            $array_data = [];

            $angsuran_pokok = $plafon_awal / $jkw;
            $angsuran_bunga = $plafon_awal * ($bunga / 100) / 12;
            $total_tagihan = 0;
            $total_pokok = 0;
            $total_bunga = 0;
            $index = 0;
            $data = [
                "trans_id" => "",
                "jenis_trans" => 1,
                "anggota_id" => $anggota_id,
                "rekening" => $rekening,
                "kredit_plafon" => $plafon_awal,
                "kredit_realisasi" => 0,
                "angsuran_tgl" => $tgl_mulai,
                "tagihan_pokok" => 0,
                "tagihan_bunga" => 0,
                "angsuran_pokok" => 0,
                "angsuran_bunga" => 0,
                "tunggakan_pokok" => 0,
                "tunggakan_bunga" => 0,
                "kolektibilitas" => 1,
            ];
            array_push($array_data, $data);
            for ($i = 0; $i <= $jkw; $i++) {
                $index = $index + 1;
                $tgl = Helper::jatuhTempo($tgl_mulai, $index);
                if ($i == 0) {
                    $plafon = 0;
                    $kredit_realisasi = $plafon_awal;
                    $tagihan_pokok = 0;
                    $tagihan_bunga = 0;
                    $total_tagihan = $total_tagihan + $tagihan_pokok;
                    $id_trans = $trans_id;
                    $jenis_trans = 2;
                } else if ($i == $jkw) {
                    $plafon = 0;
                    $kredit_realisasi = 0;
                    $tagihan_pokok = intval($angsuran_pokok);
                    $tagihan_akhir = $plafon_awal - ($tagihan_pokok * ($jkw - 1));
                    $tagihan_pokok = $tagihan_akhir;
                    $tagihan_bunga = intval($angsuran_bunga);
                    $id_trans = "";
                    $jenis_trans = 3;
                } else {
                    $plafon = 0;
                    $kredit_realisasi = 0;
                    $tagihan_pokok = intval($angsuran_pokok);
                    $tagihan_bunga = intval($angsuran_bunga);
                    $total_tagihan = $total_tagihan + $tagihan_pokok;
                    $id_trans = "";
                    $jenis_trans = 3;
                }
                $total_pokok = $total_pokok + $tagihan_pokok;
                $total_bunga = $total_bunga + $tagihan_bunga;
                $data = [
                    "trans_id" => $id_trans,
                    "jenis_trans" => $jenis_trans,
                    "anggota_id" => $anggota_id,
                    "rekening" => $rekening,
                    "kredit_plafon" => $plafon,
                    "kredit_realisasi" => $kredit_realisasi,
                    "angsuran_tgl" => $tgl,
                    "tagihan_pokok" => $tagihan_pokok,
                    "tagihan_bunga" => $tagihan_bunga,
                    "angsuran_pokok" => 0,
                    "angsuran_bunga" => 0,
                    "tunggakan_pokok" => 0,
                    "tunggakan_bunga" => 0,
                    "kolektibilitas" => 1,
                ];
                array_push($array_data, $data);
            }
            return $array_data;
        } catch (Exception $error) {
            throw $error;
        }
    }

    // ! TESTING

    function getRincianDetailAnggota(Request $request)
    {
        $rekening = $request->rekening;
        $rincian = DB::select(
            "SELECT km.rekening AS rekening_kredit, b.proposal_id ,a.anggota_id, nama_lengkap, alamat, jkw, rt, rw ,pekerjaan, ahli_waris , bunga, tgl_mulai, b.pengajuan, b.rekomendasi, b.plafon, HitungPinjamanSaldoAnggota ( km.rekening, a.anggota_id, CURRENT_DATE()) AS bakidebet  FROM ( SELECT am.anggota_id, nama_lengkap, alamat, rt, rw, pekerjaan, ahli_waris FROM data_kredit_detail kd JOIN data_anggota_master am ON kd.anggota_id = am.anggota_id WHERE rekening_kredit = ?) AS a LEFT JOIN ( SELECT *  FROM data_kredit_detail  WHERE rekening_kredit = ?) AS b ON a.anggota_id = b.anggota_id JOIN data_kredit_master km ON km.rekening = ?",
            [$rekening, $rekening, $rekening]
        );
        return $rincian;
    }

    function cancelVerifikasiProposal(Request $request)
    {
        try {
            DB::table('data_kredit_proposal')->where('proposal_id', $request->proposal_id)->update(['status' => 1]);
            return response()->json([
                'success' => true,
                'message' => 'Verifikasi proposal berhasil dibatalkan'
            ]);
        } catch (Exception $error) {
            return response()->json([
                'success' => false,
                'error' => $error->getMessage()
            ]);
        }
    }

    function updateNomerPK(Request $request)
    {
        try {
            DB::table('data_kredit_master')
                ->where('rekening', $request->rekening)
                ->update([
                    'nomer_pk' => $request->nomer_pk
                ]);
            return response()->json([
                'success' => true,
                'message' => "Nomer Akad kredit berhasil disimpan"
            ]);
        } catch (Exception $error) {
            return response()->json([
                'success' => false,
                'error' => $error->getMessage()
            ]);
        }
    }

    // //*TRANSAKSI REALISASI KREDIT -> GENERATE TABLE ANGSURAN
    // function generateTableAngsuranKelompok($trans_id, $rekening, $plafon_awal, $tgl_mulai)
    // {
    //     DB::table('data_kredit_angsuran')->where('rekening', $rekening)->whereIn('jenis_trans', [1, 2, 3])->delete();

    //     $daftar_angsuran = DB::select(
    //         "SELECT jenis_trans, rekening, angsuran_tgl, sum(tagihan_pokok) as tagihan_pokok, sum(tagihan_bunga) as tagihan_bunga FROM data_kredit_angsuran_anggota WHERE rekening = ? AND jenis_trans = 3 GROUP BY angsuran_tgl",
    //         [$rekening]
    //     );
    //     try {
    //         DB::table("data_kredit_angsuran")->insert([
    //             [
    //                 "trans_id" => '',
    //                 "jenis_trans" => 1,
    //                 "rekening" => $rekening,
    //                 "kredit_plafon" => $plafon_awal,
    //                 "kredit_realisasi" => 0,
    //                 "angsuran_tgl" => $tgl_mulai,
    //                 "kolektibilitas" => 1,
    //             ],
    //             [
    //                 "trans_id" => $trans_id,
    //                 "jenis_trans" => 2,
    //                 "rekening" => $rekening,
    //                 "kredit_plafon" => 0,
    //                 "kredit_realisasi" => $plafon_awal,
    //                 "angsuran_tgl" => $tgl_mulai,
    //                 "kolektibilitas" => 1,
    //             ]
    //         ]);

    //         foreach ($daftar_angsuran as $angsuran) {
    //             DB::table("data_kredit_angsuran")->insert(
    //                 [
    //                     "jenis_trans" => 3,
    //                     "rekening" => $rekening,
    //                     "angsuran_tgl" => $angsuran->angsuran_tgl,
    //                     "tagihan_pokok" => $angsuran->tagihan_pokok,
    //                     "tagihan_bunga" => $angsuran->tagihan_bunga,
    //                     "kolektibilitas" => 1,
    //                 ]
    //             );
    //         }
    //         return $angsuran;
    //     } catch (Exception $error) {
    //         throw $error;
    //     }
    // }

    function getDetailTransaksiRestruk(Request $request)
    {
        try {
            $trans_restruk = DB::table('data_kredit_trans_restruk')->where('trans_id', $request->trans_id)->first();

            $trans_pelunasan = DB::table('data_kredit_trans_angsuran', 'trans')
                ->join('data_kredit_master as master', 'trans.rekening', 'master.rekening')
                ->where('trans_id', $trans_restruk->trans_id_pelunasan)
                ->select(['trans.rekening', 'trans.pokok', 'trans.bunga'])
                ->first();
            $trans_pelunasan->pokok = intval($trans_pelunasan->pokok);
            $trans_pelunasan->bunga = intval($trans_pelunasan->bunga);

            return response()->json([
                'success' => true,
                'data' => $trans_pelunasan
            ]);
        } catch (Exception $error) {
            return response()->json([
                'success' => false,
                'error' => $error->getMessage()
            ]);
        }
    }
}
