<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;

use Exception;
use Illuminate\Support\Facades\DB;

class Helper extends Controller
{

    public static function jatuhTempo($date, $periode)
    {
        $date = date_create($date);
        $bulan = intval(date_format($date, 'm')); //dimulai dari 0 - 11
        $tahun = intval(date_format($date, 'Y'));
        $tgl = intval(date_format($date, 'd'));

        $blnJarak = $bulan + intval($periode);
        $blnAkhir = $blnJarak % 12;
        $thnAkhir = $tahun + floor($blnJarak / 12);
        $tglAkhir = $tgl;

        //* april, juni, september, november -> bulan yang harinya hanya 30
        if (in_array(($blnAkhir % 12), [4, 6, 9, 11])) {
            if ($tgl > 30) {
                $tglAkhir = 30;
            } else {
                $tglAkhir = $tgl;
            }
        }
        $isKabisat = $thnAkhir % 4 == 0;
        //* kondisi khusus bulan februari
        if ($blnAkhir == 2) {
            $tglAkhir = $tgl;
            if ($isKabisat) {
                if ($tgl > 29) {
                    $tglAkhir = 29;
                }
            } else {
                if ($tgl > 28) {
                    $tglAkhir = 28;
                }
            }
        }
        if ($blnAkhir == 0) {
            $blnAkhir = 12;
            $thnAkhir = $thnAkhir - 1;
        }
        $bulan_result = str_pad($blnAkhir, 2, '0', STR_PAD_LEFT);
        $tgl_result = str_pad($tglAkhir, 2, '0', STR_PAD_LEFT);
        $result = $thnAkhir . "-" . $bulan_result . "-" . $tgl_result;
        return $result;
    }

    //! fungsi untuk generate tabel engsuran langsung tanpa proses realisasi
    //*GENERATE TABLE ANGSURAN KELOMPOK TANPA ANGGOTA
    public static function generateTableAngsuranKelompok3($rekening, $trans_id_realisasi = null)
    {
        $kredit = DB::table('data_kredit_master')->where('rekening', $rekening)->first();
        $trans_id = $trans_id_realisasi;
        if ($trans_id == null) {
            $trans_realisasi = DB::table('data_kredit_trans_realisasi')->where('rekening', $kredit->rekening)->where('status', 1)->first();
            $trans_id = $trans_realisasi->trans_id;
        }

        $angsuran_pokok = $kredit->plafon / $kredit->jkw;
        $angsuran_bunga = $kredit->plafon * ($kredit->bunga / 100) / 12;

        try {
            DB::table("data_kredit_angsuran")->insert([
                [
                    "trans_id" => '',
                    "jenis_trans" => 1, //* permohonan
                    "rekening" => $rekening,
                    "kredit_plafon" => $kredit->plafon,
                    "kredit_realisasi" => 0,
                    "angsuran_tgl" => $kredit->tgl_mulai,
                    "kolektibilitas" => 1,
                ],
                [
                    "trans_id" => $trans_id,
                    "jenis_trans" => 2, //* realisasi
                    "rekening" => $rekening,
                    "kredit_plafon" => 0,
                    "kredit_realisasi" => $kredit->plafon,
                    "angsuran_tgl" => $kredit->tgl_mulai,
                    "kolektibilitas" => 1,
                ]
            ]);

            $total_tagihan = 0;
            $jkw = $kredit->jkw;
            $bunga = $kredit->bunga;
            $angsuran_pokok = $kredit->plafon / $jkw;
            $angsuran_bunga = $kredit->plafon * ($bunga / 100) / 12;
            $total_pokok = 0;
            $total_bunga = 0;
            $tagihan_pokok = 0;
            $index = 0;

            for ($i = 0; $i <= $jkw; $i++) {
                $index = $index + 1;
                $tgl = Helper::jatuhTempo($kredit->tgl_mulai, $i);
                if ($i > 0) {
                    if ($i == $jkw) {
                        $plafon = 0;
                        $kredit_realisasi = 0;
                        $tagihan_pokok = intval($angsuran_pokok);
                        $tagihan_akhir = $kredit->plafon - ($tagihan_pokok * ($jkw - 1));
                        $tagihan_pokok = $tagihan_akhir;
                        $tagihan_bunga = intval($angsuran_bunga);
                        $id_trans = "";
                        $jenis_trans = 3; //* tagihan
                    } 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; //* tagihan
                    }
                    $total_pokok = $total_pokok + $tagihan_pokok;
                    $total_bunga = $total_bunga + $tagihan_bunga;

                    DB::table("data_kredit_angsuran")->insert([
                        "trans_id" => $id_trans,
                        "jenis_trans" => $jenis_trans,
                        "anggota_id" => "",
                        "rekening" => $rekening,
                        "kredit_plafon" => $plafon,
                        "kredit_realisasi" => $kredit_realisasi,
                        "angsuran_tgl" => $tgl,
                        "tagihan_pokok" => $tagihan_pokok,
                        "tagihan_bunga" => $tagihan_bunga,
                        "kolektibilitas" => 1,
                    ]);
                }
            }
        } catch (Exception $error) {
            throw $error;
        }
    }

    //! fungsi untuk generate tabel engsuran langsung tanpa proses realisasi
    public static function generateTableAngsuran2($trans_id, $rekening)
    {
        $kredit = DB::table('data_kredit_master')->where('rekening', $rekening)->first();

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

        $anggota = DB::table('data_kredit_detail')->where('rekening_kredit', $rekening)->get();

        //* 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::generateTableAngsuranKelompok2($trans_id, $kredit->rekening, $plafon_kelompok, $tgl_mulai);
    }

    //! fungsi untuk generate tabel engsuran langsung tanpa proses realisasi
    //*TRANSAKSI REALISASI KREDIT -> GENERATE TABLE ANGSURAN
    public static function generateTableAngsuranKelompok2($trans_id, $rekening, $plafon_awal, $tgl_mulai)
    {
        $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, //* permohonan
                    "rekening" => $rekening,
                    "kredit_plafon" => $plafon_awal,
                    "kredit_realisasi" => 0,
                    "angsuran_tgl" => $tgl_mulai,
                    "kolektibilitas" => 1,
                ],
                [
                    "trans_id" => $trans_id,
                    "jenis_trans" => 2, //* realisasi
                    "rekening" => $rekening,
                    "kredit_plafon" => 0,
                    "kredit_realisasi" => $plafon_awal,
                    "angsuran_tgl" => $tgl_mulai,
                    "kolektibilitas" => 1,
                ]
            ]);
            if (empty($daftar_angsuran)) {
                $kredit = DB::table('data_kredit_master')->where('rekening', $rekening)->first();
                $total_tagihan = 0;
                $jkw = $kredit->jkw;
                $bunga = $kredit->bunga;
                $angsuran_pokok = $plafon_awal / $jkw;
                $angsuran_bunga = $plafon_awal * ($bunga / 100) / 12;
                $total_pokok = 0;
                $total_bunga = 0;
                $tagihan_pokok = 0;
                $index = 0;
                for ($i = 0; $i <= $jkw; $i++) {
                    $index = $index + 1;
                    $tgl = Helper::jatuhTempo($tgl_mulai, $i);
                    if ($i > 0) {
                        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; //* tagihan
                        } 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; //* tagihan
                        }
                        $total_pokok = $total_pokok + $tagihan_pokok;
                        $total_bunga = $total_bunga + $tagihan_bunga;
                        DB::table("data_kredit_angsuran")->insert([
                            "trans_id" => $id_trans,
                            "jenis_trans" => $jenis_trans,
                            "anggota_id" => "",
                            "rekening" => $rekening,
                            "kredit_plafon" => $plafon,
                            "kredit_realisasi" => $kredit_realisasi,
                            "angsuran_tgl" => $tgl,
                            "tagihan_pokok" => $tagihan_pokok,
                            "tagihan_bunga" => $tagihan_bunga,
                            "kolektibilitas" => 1,
                        ]);
                    }
                }
            } else {
                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,
                        ]
                    );
                }
            }
        } catch (Exception $error) {
            throw $error;
        }
    }

    //***********************************************************/
    //*TRANSAKSI REALISASI KREDIT -> GENERATE TABLE ANGSURAN
    public static function generateTableAngsuran($trans_id, $rekening)
    {
        $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);

        //* 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::generateTableAngsuranKelompok2($trans_id, $kredit->rekening, $plafon_kelompok, $tgl_mulai);
    }

    //*TRANSAKSI REALISASI KREDIT -> GENERATE TABLE ANGSURAN ANGGOTA
    public static 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) {
                    // realisasi
                    $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) {
                    // angsuran terakhir
                    $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 {
                    // angsuran
                    $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;
        }
    }

    //*TRANSAKSI REALISASI KREDIT -> GENERATE TABLE ANGSURAN
    public static 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;
        }
    }
    //***********************************************************/

    // fungsi ini untuk membuat tabel angsuran tanpa melalui transaksi realisasi
    // generate semua tabel dilihat dari transaksi realisasi yang ada
    // dipanggil langsung melalui API
    public function generateTableAngsuranDirect()
    {
        try {
            DB::beginTransaction();
            $trans_realisasi = DB::table('data_kredit_trans_realisasi')->get();
            foreach ($trans_realisasi as $realisasi) {
                $trans_id = $realisasi->trans_id;
                $rekening = $realisasi->rekening;
                Helper::generateTableAngsuran2($trans_id, $rekening);
            }
            DB::commit();
            return response()->json([
                'success' => true,
                'message' => "Table Angsuran berhasil dibuat",
            ]);
        } catch (Exception $error) {
            DB::rollBack();
            return response()->json([
                'success' => false,
                'error' => $error->getMessage()
            ]);
        }
    }
}
