<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;

use Carbon\Carbon;
use Exception;
use Illuminate\Database\QueryException;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class InventarisController extends Controller
{
    function getDaftarInventaris()
    {
        // $sql = "SELECT inventaris_id, inventaris_nama, inventaris_umur, inventaris_jenis, inventaris_jml, inventaris_nilai , inventaris_tgl_beli, inventaris_nilai_susut, status FROM data_inventaris_master ORDER BY inventaris_id ASC";
        $tanggal = date('Y-m-d');
        $sql = "SELECT inventaris_id, inventaris_nama, inventaris_umur, inventaris_jenis,inventaris_jml,inventaris_nilai, inventaris_tgl_beli, penyusutan as inventaris_nilai_susut, status FROM (SELECT *, IFNULL((SELECT SUM(nominal) FROM data_inventaris_trans WHERE inventaris_id = tb_asset.inventaris_id AND trans_kode = '32' AND trans_tglbuku <= '$tanggal' AND status = '1'),0) as penyusutan FROM (SELECT * FROM data_inventaris_master WHERE inventaris_tgl_beli <= '$tanggal') as tb_asset) as tb JOIN data_inventaris_produk ip ON tb.inventaris_jenis = ip.id;";
        $inventaris = DB::select($sql);
        return response()->json($inventaris);
    }

    function getDetailInvetaris(Request $request)
    {
        $inventaris_id = $request->inventaris_id;
        $inventaris = DB::selectOne("SELECT inventaris_id, inventaris_jenis, inventaris_nama ,nama as inventaris_nama_jenis, inventaris_sumber, inventaris_umur ,inventaris_jml, inventaris_tgl_beli, inventaris_tgl_habis, inventaris_tgl_hapus, inventaris_nilai, inventaris_nilai_susut, inventaris_catatan, im.status FROM data_inventaris_master im JOIN data_inventaris_produk ip ON im.inventaris_jenis = ip.id WHERE inventaris_id = '$inventaris_id'");
        return response()->json($inventaris);
    }

    function createDataMasterInventaris(Request $request)
    {
        $count = DB::table('data_inventaris_master')->count() + 1;
        $number = str_pad($count, 5, "0", STR_PAD_LEFT);
        $inventaris_id = "INV.$number";

        $umur_inventaris = $request->umur;
        $tgl_habis = date_create($request->tanggal);
        date_add($tgl_habis, date_interval_create_from_date_string("$umur_inventaris months"));

        try {
            DB::table('data_inventaris_master')->insert([
                "inventaris_id" => $inventaris_id,
                "inventaris_jenis" => $request->jenis,
                "inventaris_sumber" => $request->sumber,
                "inventaris_nama" => $request->nama,
                "inventaris_jml" => $request->jumlah,
                "inventaris_umur" => $umur_inventaris,
                "inventaris_nilai" => $request->nilai_perolehan,
                "inventaris_nilai_susut" => 0,
                "inventaris_tgl_beli" => $request->tanggal,
                "inventaris_tgl_habis" => $tgl_habis,
                "inventaris_tgl_hapus" => '2099-12-31',
                "inventaris_catatan" => $request->keterangan,
                "date_created" => date('Y-m-d H:i:s'),
                "date_updated" => date('Y-m-d H:i:s'),
                "user" => $request->user,
                "status" => 0
            ]);
            return response()->json([
                'success' => true,
                'message' => 'Data berhasil disimpan',
                "inventaris_id" => $inventaris_id
            ]);
        } catch (QueryException $error) {
            return response()->json([
                'success' => false,
                'error' => $error
            ]);
        }
    }

    // function getDataNominatif()
    // {
    //     $sql = "SELECT
    //             c.*,
    //             inventaris_nilai - IFNULL(total_penyusutan, 0) AS nilai_buku
    //         FROM (
    //             SELECT
    //                 a.inventaris_id,
    //                 a.inventaris_tgl_beli,
    //                 b.nama,
    //                 a.inventaris_nilai,
    //                 (
    //                     SELECT SUM(nominal)
    //                     FROM data_inventaris_trans
    //                     WHERE data_inventaris_trans.inventaris_id = a.inventaris_id
    //                     AND data_inventaris_trans.trans_kode = 32 and data_inventaris_trans.trans_tglbuku <= '" . $_POST['tanggal'] . "'
    //                 ) AS total_penyusutan
    //             FROM
    //                 data_inventaris_master a
    //             LEFT JOIN
    //                 data_inventaris_produk b ON a.inventaris_jenis = b.id where a.inventaris_tgl_beli <= '" . $_POST['tanggal'] . "'
    //         ) AS c;";
    //     if ($_POST['jenis'] !== '0') {
    //         $sql = "SELECT
    //             c.*,
    //             inventaris_nilai - IFNULL(total_penyusutan, 0) AS nilai_buku
    //         FROM (
    //             SELECT
    //                 a.inventaris_id,
    //                 a.inventaris_tgl_beli,
    //                 b.nama,
    //                 a.inventaris_nilai,
    //                 (
    //                     SELECT SUM(nominal)
    //                     FROM data_inventaris_trans
    //                     WHERE data_inventaris_trans.inventaris_id = a.inventaris_id
    //                     AND data_inventaris_trans.trans_kode = 32 and data_inventaris_trans.trans_tglbuku <= '" . $_POST['tanggal'] . "'
    //                 ) AS total_penyusutan
    //             FROM
    //                 data_inventaris_master a
    //             LEFT JOIN
    //                 data_inventaris_produk b ON a.inventaris_jenis = b.id where a.inventaris_tgl_beli <= '" . $_POST['tanggal'] . "' and a.inventaris_jenis='" . $_POST['jenis'] . "'
    //         ) AS c;";
    //     }
    //     $data = DB::select($sql);
    //     return response()->json($data);
    // }

    function getJenisInventaris()
    {
        $sql = "SELECT * FROM data_inventaris_produk";
        $inventaris = DB::select($sql);
        return response()->json($inventaris);
    }

    function getStatementPenyusutan(Request $request)
    {
        $inventaris_id = $request->inventaris_id;
        // $sql = "SELECT * FROM log_penyusutan_inventaris pi JOIN data_inventaris_trans it ON pi.trans_id = it.trans_id WHERE pi.inventaris_id = '$inventaris_id' AND pi.status = '1' ORDER BY tanggal";
        $sql = "SELECT *, trans_tglbuku as tanggal FROM data_inventaris_trans WHERE inventaris_id = '$inventaris_id' AND trans_kode = '32' AND status = '1' ORDER BY trans_date";
        $statement = DB::select($sql);
        $sql = "SELECT inventaris_nilai as nilai FROM data_inventaris_master WHERE inventaris_id = '$inventaris_id'";
        $inventaris_nilai = DB::selectOne($sql);
        return response()->json([
            "success" => true,
            "data" => $statement,
            "inventaris_nilai" => $inventaris_nilai->nilai,
            "query" => $sql
        ]);
    }

    function getDaftarTransaksiInventaris(Request $request)
    {
        $trans = DB::select(
            "SELECT trans_id, trans_kode, kode_perkiraan, t.inventaris_id, inventaris_nama, inventaris_tgl_beli, inventaris_tgl_habis ,nominal, trans_catatan, trans_tglbuku, trans_date, t.user, t.status FROM data_inventaris_trans t JOIN data_inventaris_master m ON t.inventaris_id = m.inventaris_id WHERE DATE(trans_tglbuku) >= ? AND DATE(trans_tglbuku) <= ? ORDER BY trans_tglbuku",
            [$request->start_date, $request->end_date]
        );
        return response()->json($trans);
    }

    function getDaftarTransaksiPenyusutanInventaris(Request $request)
    {
        try {
            $sql = "SELECT * FROM (SELECT count(id) AS jumlah, sum(jumlah) AS penyusutan, tanggal, trans_date ,user, status FROM log_penyusutan_inventaris GROUP BY tanggal, trans_date, user, status) tb_trans WHERE status = 1 ORDER BY tanggal DESC";
            $trans = DB::select($sql);
            return response()->json([
                "success" => true,
                "data" => $trans
            ]);
        } catch (Exception $error) {
            return response()->json([
                "success" => false,
                "error" => $error
            ]);
        }
    }

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

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

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

            // jika transaksi adalah transaksi pembelian
            if ($transaksi->trans_kode == '31.1') {
                DB::table('data_inventaris_master')->where('inventaris_id', $transaksi->inventaris_id)->update(["status" => 0]);
            }
            // jika transaksi adalah transaksi pejualan
            if ($transaksi->trans_kode == '34') {
                DB::table('data_inventaris_master')->where('inventaris_id', $transaksi->inventaris_id)->update(["status" => 1]);
            }

            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 validasiTransaksi(Request $request)
    {
        $trans_id = $request->trans_id;

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

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

            if ($transaksi->trans_kode == '31.1') {
                DB::table('data_inventaris_master')->where('inventaris_id', $transaksi->inventaris_id)->update(["status" => 1]);
            }
            if ($transaksi->trans_kode == '34') {
                DB::table('data_inventaris_master')->where('inventaris_id', $transaksi->inventaris_id)->update(["status" => 2]);
            }

            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 getDaftarHapusBuku(Request $request)
    {
        try {
            $data = DB::select("SELECT hapus.*, master.inventaris_nama, master.inventaris_tgl_beli, master.inventaris_umur, master.inventaris_nilai, master.inventaris_tgl_hapus FROM data_inventaris_hapus_buku hapus JOIN data_inventaris_master master ON hapus.inventaris_id = master.inventaris_id WHERE hapus.status = 1");

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

    function hapusInventaris(Request $request)
    {
        DB::beginTransaction();
        try {
            $trans_date = Carbon::now()->format('Y-m-d');
            $trans_tgl = Carbon::parse($request->tanggal)->format('Ymd');
            $trans_tglbuku = Carbon::parse($request->tanggal)->format('Y-m-d');
            $trans_kode = 39;

            $urut = DB::table('data_inventaris_hapus_buku')->where('status', 1)->count();
            $trans_nomer = str_pad($urut + 1, 3, '0', STR_PAD_LEFT);
            $trans_id = "$trans_kode.$trans_tgl.$trans_nomer";

            $jurnal = new JurnalController();
            $inventaris = DB::selectOne(
                "SELECT inventaris_id, kode_perkiraan, kode_perkiraan_penyusutan ,jurnal ,inventaris_nilai ,penyusutan as inventaris_nilai_susut FROM (SELECT *, IFNULL((SELECT SUM(nominal) FROM data_inventaris_trans WHERE inventaris_id = tb_asset.inventaris_id AND trans_kode = '32' AND trans_tglbuku <= NOW() AND status = '1'),0) as penyusutan FROM (SELECT * FROM data_inventaris_master WHERE inventaris_tgl_beli <= NOW()) as tb_asset) as tb JOIN data_inventaris_produk ip ON tb.inventaris_jenis = ip.id WHERE inventaris_id = ?",
                [$request->id_inventaris]
            );

            DB::table('data_inventaris_hapus_buku')->insert([
                "trans_id" => $trans_id,
                "trans_kode" => $trans_kode,
                "kode_perkiraan" => "",
                "inventaris_id" => $request->id_inventaris,
                "nominal" => $inventaris->inventaris_nilai,
                "trans_catatan" => "Hapus buku inventaris " . $request->id_inventaris,
                "trans_tglbuku" => $trans_tglbuku,
                "trans_date" => $trans_date,
                "user" => $request->user,
                "status" => 1,
            ]);

            $uraian = "Hapus buku inventaris " . $request->id_inventaris;
            $request_data = new Request([
                "trans_kode" => $trans_kode,
                "rekening" => $request->id_inventaris,
                "tanggal" => $trans_tglbuku,
                "user" => $request->user
            ]);

            $biaya_penyusutan = $inventaris->inventaris_nilai - $inventaris->inventaris_nilai_susut;
            $jurnal->jurnalTransaksi($trans_id, $inventaris->kode_perkiraan, 0, $inventaris->inventaris_nilai, $trans_date, $uraian, $request_data);
            $jurnal->jurnalTransaksi($trans_id, $inventaris->kode_perkiraan_penyusutan, $inventaris->inventaris_nilai_susut, 0, $trans_date, $uraian, $request_data);
            $jurnal->jurnalTransaksi($trans_id, $inventaris->jurnal, $biaya_penyusutan, 0, $trans_date, $uraian, $request_data);

            DB::table('data_inventaris_master')->where('inventaris_id', $request->id_inventaris)->update([
                'status' => 3,
                'inventaris_tgl_hapus' => $trans_date,
            ]);

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

    function validasiTransaksiHapusBuku(Request $request)
    {
        try {
            throw new Exception("Fungsi belum diimplementasikan");
            return Response()->json([
                'success' => true,
                'message' => "Transaksi berhasil divalidasi"
            ]);
        } catch (Exception $error) {
            return Response()->json([
                'success' => false,
                'error' => $error->getMessage()
            ]);
        }
    }

    function deleteTransaksiHapusBuku(Request $request)
    {
        try {
            throw new Exception("Fungsi belum diimplementasikan");
            return Response()->json([
                'success' => true,
                'message' => "Transaksi berhasil dibatalkan"
            ]);
        } catch (Exception $error) {
            return Response()->json([
                'success' => false,
                'error' => $error->getMessage()
            ]);
        }
    }
}
