<?php

namespace App\Http\Controllers\Reports;

use App\Http\Controllers\Controller;
use App\Cawangan;
use App\Gadai;
use App\Vault;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Services\Reports\OutstandingService as ReportOutstandingService;
use App\Helper;


class OutstandingController extends Controller
{

    public function getLaporanCawangan(string $kod_cawangan, Request $request)
    {
        $reportOutstandingService = new ReportOutstandingService($kod_cawangan, $request);
        return $reportOutstandingService->getCawanganReport();
    }

    public function getSummaryLaporanOutstanding(Request $request)
    {
        $endDate = $request->endDate;
    
        // Get active branch DBs from helper
        $active_cawangan_db_connection = Helper::getActiveCawanganDbConnection();
    
        // Fetch branch details with mysql info
        $cawangan_available = Cawangan::select('kodcaw', 'details_caw', 'mysql')
            ->whereIn('mysql', $active_cawangan_db_connection)
            ->get();
    
        // Get db connections
        $active_cawangan_db_connection = array_column($cawangan_available->toArray(), 'mysql');
    
        // Map branch info for response
        $active_cawangan_detail = $cawangan_available->map(function ($c) {
            return [
                'kodcaw' => $c->kodcaw,
                'details_caw' => $c->details_caw,
            ];
        })->values()->toArray();
    
        // Query outstanding summary for each branch
        $summary_Outstanding_data = array_map(function ($db_connection) use ($endDate) {
            // SAG KES
            $queryKes = Gadai::on($db_connection)
                ->selectRaw('COUNT(norujukan) as total_gadaian_kes, SUM(pinjaman) as sum_pinjaman_kes')
                ->where('tagbaru', 0)
                ->where('tagpalsu', 1)
                ->where('sttebus', '');
            // SAG LAMA
            $queryLama = Gadai::on($db_connection)
                ->selectRaw('COUNT(norujukan) as total_gadaian, SUM(pinjaman) as sum_pinjaman')
                ->where('tagbaru', 0)
                ->where('tagpalsu', 0)
                ->where('sttebus', '');
    
            // SAG BARU
            $queryBaru = Gadai::on($db_connection)
                ->selectRaw('COUNT(norujukan) as total_gadaian2, SUM(pinjaman) as sum_pinjaman2')
                ->where('tagbaru', 1)
                ->where('sttebus', '');
    
            // SEMUA
            $queryAll = Gadai::on($db_connection)
                ->selectRaw('COUNT(norujukan) as total_gadaian_all, SUM(pinjaman) as sum_pinjaman_all')
                ->where('sttebus', '');
    
            if (!empty($endDate)) {
                $queryKes->where('t_gadai', '<=', $endDate);
                $queryLama->where('t_gadai', '<=', $endDate);
                $queryBaru->where('t_gadai', '<=', $endDate);
                $queryAll->where('t_gadai', '<=', $endDate);
            }
    
            return array_merge(
                $queryKes->first()->toArray(),
                $queryLama->first()->toArray(),
                $queryBaru->first()->toArray(),
                $queryAll->first()->toArray()
            );
        }, $active_cawangan_db_connection);
    
        // Combine data with branch info
        $response = array_map(function ($cawangan_detail, $outstanding_data) {
            return array_merge($cawangan_detail, $outstanding_data);
        }, $active_cawangan_detail, $summary_Outstanding_data);
    
        return response()->json($response);
    }

    public function getSummaryLaporanPetibesi(Request $request)
    {
        $endDate = $request->endDate;
    
        // Get active branch DBs from helper
        $active_cawangan_db_connection = Helper::getActiveCawanganDbConnection();
    
        // Fetch branch details with mysql info
        $cawangan_available = Cawangan::select('kodcaw', 'details_caw', 'mysql')
            ->whereIn('mysql', $active_cawangan_db_connection)
            ->get();
    
        // Get db connections
        $active_cawangan_db_connection = array_column($cawangan_available->toArray(), 'mysql');
    
        // Map branch info for response
        $active_cawangan_detail = $cawangan_available->map(function ($c) {
            return [
                'kodcaw' => $c->kodcaw,
                'details_caw' => $c->details_caw,
            ];
        })->values()->toArray();
    
        // Query petibesi summary for each branch
        $summary_petibesi_data = array_map(function ($db_connection) use ($endDate) {

    
            // SEMUA
            $queryAll = Vault::on($db_connection)
                ->selectRaw('bakieod');
    
            if (!empty($endDate)) {
                $queryAll->where('tarikh', '=', $endDate);
            }
            $record = $queryAll->first();

            return $record
                ? $record->toArray()
                : ['bakieod' => 0];

        }, $active_cawangan_db_connection);
    
        // Combine data with branch info
        $response = array_map(function ($cawangan_detail, $petibesi_data) {
            return array_merge($cawangan_detail, $petibesi_data);
        }, $active_cawangan_detail, $summary_petibesi_data);
    
        return response()->json($response);
    }
    


}
