<?php

namespace App\Http\Controllers;

use App\Models\BiayaKuliah;
use Illuminate\Http\Request;
use DB; // Pastikan ini ditambahkan

class BiayaKuliahController extends Controller
{
    public function __construct()
    {
        $this->middleware('auth', ['only' => ['store', 'update', 'destroy', 'index', 'show']]);
    }

    public function show($id)
    {
        // Retrieve biaya kuliah data for a specific mahasiswa
        $biayakuliahData = DB::table('m_biaya_tarif')
            ->select(
                'm_biaya_tarif.kd_jurusan',
                'm_biaya_tarif.kd_biaya',
                'm_biaya_tarif.kd_semester',
                'm_biaya_tarif.tgl_berlaku',
                'm_biaya.biaya',
                'm_jurusan.jurusan',
                'm_tahun_ajar.tahun_ajar',
                'tagihan_alias.no_registrasi',
                'tagihan_alias.no_kwitansi',
                'tagihan_alias.tanggal_tagihan',
                'tagihan_alias.tagihan',
                'm_biaya_tarif.tarif'
            )
            ->join('m_biaya', 'm_biaya_tarif.kd_biaya', '=', 'm_biaya.kd_biaya')
            ->join('m_jurusan', 'm_biaya_tarif.kd_jurusan', '=', 'm_jurusan.kd_jurusan')
            ->join('m_tahun_ajar', 'm_biaya_tarif.tgl_berlaku', '=', 'm_tahun_ajar.kd_tahun_ajar')
            ->join('t_tagihan as tagihan_alias', function($join) {
                $join->on('m_biaya_tarif.kd_jurusan', '=', 'tagihan_alias.kd_jurusan')
                     ->on('m_biaya_tarif.kd_semester', '=', 'tagihan_alias.kd_semester')
                     ->on('m_biaya_tarif.tgl_berlaku', '=', 'tagihan_alias.kd_tahun_ajar')
                     ->on('m_biaya_tarif.kd_biaya', '=', 'tagihan_alias.kd_biaya');
            })
            ->where('tagihan_alias.no_registrasi', $id)
            ->get();
    
        // Debugging
        \Log::info('Fetching Biaya Kuliah Data for No Registrasi: ' . $id, ['data' => $biayakuliahData->toArray()]);
    
        if ($biayakuliahData->isEmpty()) {
            return response()->json(['message' => 'Data not found'], 404);
        }
    
        return response()->json($biayakuliahData, 200);
    }
    
    public function showJumlahTagihan($namaUser)
    {
        // Retrieve jumlah tagihan for a specific mahasiswa
        $jumlahTagihanData = DB::table('m_biaya_tarif')
            ->select(
                'm_biaya_tarif.kd_semester',
                'tagihan_alias.no_kwitansi',
                DB::raw('SUM(tagihan_alias.tagihan) AS jumlah_tagihan')
            )
            ->join('m_biaya', 'm_biaya_tarif.kd_biaya', '=', 'm_biaya.kd_biaya')
            ->join('m_jurusan', 'm_biaya_tarif.kd_jurusan', '=', 'm_jurusan.kd_jurusan')
            ->join('m_tahun_ajar', 'm_biaya_tarif.tgl_berlaku', '=', 'm_tahun_ajar.kd_tahun_ajar')
            ->join('t_tagihan AS tagihan_alias', function ($join) {
                $join->on('m_biaya_tarif.kd_jurusan', '=', 'tagihan_alias.kd_jurusan')
                    ->on('m_biaya_tarif.kd_semester', '=', 'tagihan_alias.kd_semester')
                    ->on('m_biaya_tarif.tgl_berlaku', '=', 'tagihan_alias.kd_tahun_ajar')
                    ->on('m_biaya_tarif.kd_biaya', '=', 'tagihan_alias.kd_biaya');
            })
            ->where('tagihan_alias.no_registrasi', $namaUser)
            ->groupBy('m_biaya_tarif.kd_semester', 'tagihan_alias.no_kwitansi')
            ->get();
    
        // Debugging
        \Log::info('Fetching Jumlah Tagihan Data for No Registrasi: ' . $namaUser, ['data' => $jumlahTagihanData->toArray()]);
    
        if ($jumlahTagihanData->isEmpty()) {
            return response()->json(['message' => 'Data not found'], 404);
        }
    
        return response()->json($jumlahTagihanData, 200);
    }

    
    public function BayarBiayaKuliah($namaUser)
    {
        // Retrieve biaya kuliah data for a specific mahasiswa using subquery
        $biayakuliahData = DB::table('t_konfirmasi_bayar as a')
            ->select(
                DB::raw('SUM(a.jumlah_setor) as jumlah_bayar'),
                'a.no_kwitansi',
                DB::raw('(SELECT b.kd_semester FROM t_tagihan b WHERE b.no_kwitansi = a.no_kwitansi LIMIT 1) as kd_semester')
            )
            ->where('a.no_registrasi', $namaUser)
            ->where('a.Approve', '2')
            ->groupBy('a.no_kwitansi')
            ->get();
        
        // Log the data for debugging purposes
        \Log::info('Fetching Biaya Kuliah Data for No Registrasi: ' . $namaUser, ['data' => $biayakuliahData->toArray()]);
        
        // Check if data exists
        if ($biayakuliahData->isEmpty()) {
            return response()->json(['message' => 'Data not found'], 404);
        }
        
        // Return the data as JSON
        return response()->json($biayakuliahData, 200);
    }
    
    public function SisaBiayaKuliah($namaUser)
    {
        // Hitung jumlah bayar
        $jumlahBayar = DB::table('t_konfirmasi_bayar as a')
            ->select(
                'a.no_kwitansi',
                DB::raw('(SELECT b.kd_semester FROM t_tagihan b WHERE b.no_kwitansi = a.no_kwitansi LIMIT 1) as kd_semester'),
                DB::raw('SUM(a.jumlah_setor) as jumlah_bayar')
            )
            ->where('a.no_registrasi', $namaUser)
            ->where('a.Approve', '2')
            ->groupBy('a.no_kwitansi')
            ->get();
    
        // Hitung jumlah tagihan
        $jumlahTagihan = DB::table('m_biaya_tarif as a')
            ->select(
                'e.no_kwitansi',
                'a.kd_semester',
                DB::raw('SUM(e.tagihan) AS jumlah_tagihan')
            )
            ->join('m_biaya as b', 'a.kd_biaya', '=', 'b.kd_biaya')
            ->join('m_jurusan as c', 'a.kd_jurusan', '=', 'c.kd_jurusan')
            ->join('m_tahun_ajar as d', 'a.tgl_berlaku', '=', 'd.kd_tahun_ajar')
            ->join('t_tagihan as e', function($join) {
                $join->on('a.kd_jurusan', '=', 'e.kd_jurusan')
                     ->on('a.kd_semester', '=', 'e.kd_semester')
                     ->on('a.tgl_berlaku', '=', 'e.kd_tahun_ajar')
                     ->on('a.kd_biaya', '=', 'e.kd_biaya');
            })
            ->where('e.no_registrasi', $namaUser)
            ->groupBy('e.no_kwitansi', 'a.kd_semester')
            ->get();
    
        // Gabungkan data bayar dan tagihan
        $results = [];
    
        // Menghitung total bayar dan tagihan berdasarkan no_kwitansi
        foreach ($jumlahBayar as $bayar) {
            $key = $bayar->no_kwitansi;
    
            $results[$key]['no_kwitansi'] = $key;
            $results[$key]['kd_semester'] = $bayar->kd_semester;
            $results[$key]['jumlah_bayar'] = $bayar->jumlah_bayar;
            $results[$key]['jumlah_tagihan'] = 0; // Default
        }
    
        foreach ($jumlahTagihan as $tagihan) {
            $key = $tagihan->no_kwitansi;
    
            if (!isset($results[$key])) {
                $results[$key]['no_kwitansi'] = $key;
                $results[$key]['kd_semester'] = $tagihan->kd_semester;
                $results[$key]['jumlah_tagihan'] = $tagihan->jumlah_tagihan;
                $results[$key]['jumlah_bayar'] = 0; // Default
            } else {
                $results[$key]['jumlah_tagihan'] += $tagihan->jumlah_tagihan;
            }
        }
    
        // Menambahkan sisa tagihan
        foreach ($results as &$result) {
            $result['sisa_tagihan'] = $result['jumlah_tagihan'] - $result['jumlah_bayar'];
        }
    
        // Log the data for debugging purposes
        \Log::info('Fetching Biaya Kuliah Data for No Registrasi: ' . $namaUser, ['data' => $results]);
    
        // Check if data exists
        if (empty($results)) {
            return response()->json(['message' => 'Data not found'], 404);
        }
    
        // Return the data as JSON
        return response()->json(array_values($results), 200);
    }









}
