<?php

namespace App\Http\Controllers\Reports;

use App\Http\Controllers\Controller;
use App\Cawangan;
use App\Gadai;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Services\Reports\GadaianService as ReportGadaianService;
use App\Helper;


class GadaianController extends Controller
{

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

    public function getSummaryLaporanGadai(Request $request)
    {
        $onDate = $request->onDate;
        $startDate = $request->startDate;
        $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();

        $summary_gadaian_data = array_map(function ($db_connection) use ($request) {
            $query = Gadai::on($db_connection);

             $query->select(DB::raw('count(norujukan) as total_gadaian, sum(berat) as sum_berat, sum(nilaimarhun) as sum_nilai_marhun, sum(pinjaman) as sum_pinjaman,  sum(upah12) as sum_upah_12'));

            $query->when($request->filled('startDate') and $request->filled('endDate'), function ($q) use ($request) {
                return $q->where('t_gadai', '>=', $request->startDate)
                    ->where('t_gadai', '<=', $request->endDate);
            });

            return $query->get()->toArray();

        }, $active_cawangan_db_connection);

        $response = array_map(function ($cawangan_detail, $summary_gadaian_data) {
            return array_merge($cawangan_detail, $summary_gadaian_data[0]);
        }, $active_cawangan_detail, $summary_gadaian_data);

        return json_encode($response);
    }

    public function getSummaryLaporanGadaiTebus(Request $request)
{
    $startDate = $request->startDate;
    $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();

    // Loop through each DB
    $summary_data = array_map(function ($db_connection) use ($startDate, $endDate) {
    $result = DB::connection($db_connection)->table('gadai as g')
        ->join('tebus as t', function ($join) {
            $join->on(DB::raw('DATE(g.t_gadai)'), '=', DB::raw('DATE(t.t_tebus)'))
                 ->on('g.norujukan', '=', 't.norujukan');
        })
        ->whereDate('g.t_gadai', '>=', $startDate)
        ->whereDate('g.t_gadai', '<=', $endDate)
        ->whereDate('t.t_tebus', '>=', $startDate)
        ->whereDate('t.t_tebus', '<=', $endDate)
        ->where('g.upah12', '>=', 0.00) 
        ->selectRaw('
            COUNT(DISTINCT g.norujukan) as total_gadai_tebus_samahari,
            SUM(g.pinjaman) as jumlah_pinjaman
        ')
        ->first(); // ambil satu hasil, bukan collection

    return (array) $result ?: [
        'total_gadai_tebus_samahari' => 0,
        'jumlah_pinjaman' => 0.00
    ];
    }, $active_cawangan_db_connection); 


    // Gabung cawangan + hasil
    $response = array_map(function ($cawangan_detail, $summary_row) {
    return array_merge($cawangan_detail, $summary_row ?: [
        'total_gadai_tebus_samahari' => 0,
        'jumlah_pinjaman' => 0.00
    ]);
    }, $active_cawangan_detail, $summary_data);

    return response()->json($response);
}
    public function getSummaryLaporanGadaiTebusMasa(Request $request)
    {
        $startDate = $request->startDate;
        $endDate = $request->endDate;

        // Ambil connection aktif
        $active_cawangan_db_connection = Helper::getActiveCawanganDbConnection();

        // Dapatkan butiran cawangan
        $cawangan_available = Cawangan::select('kodcaw', 'details_caw', 'mysql')
            ->whereIn('mysql', $active_cawangan_db_connection)
            ->get();

        $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();

        // Loop through each DB
        $summary_data = array_map(function ($db_connection) use ($startDate, $endDate) {
        $result = DB::connection($db_connection)->table('gadai as g')
            ->join('tebus as t', function ($join) {
                $join->on(DB::raw('DATE(g.t_gadai)'), '=', DB::raw('DATE(t.t_tebus)'))
                    ->on('g.norujukan', '=', 't.norujukan');
            })
            ->whereDate('g.t_gadai', '>=', $startDate)
            ->whereDate('g.t_gadai', '<=', $endDate)
            ->whereDate('t.t_tebus', '>=', $startDate)
            ->whereDate('t.t_tebus', '<=', $endDate)
            ->where('g.upah12', '>=', 0.00) 
            ->selectRaw('
                COUNT(DISTINCT g.norujukan) as total_gadai_tebus_samahari,
                SUM(g.pinjaman) as jumlah_pinjaman
            ')
            ->first(); // ambil satu hasil, bukan collection

        return (array) $result ?: [
            'total_gadai_tebus_samahari' => 0,
            'jumlah_pinjaman' => 0.00
        ];
        }, $active_cawangan_db_connection); 


        // Gabung cawangan + hasil
        $response = array_map(function ($cawangan_detail, $summary_row) {
        return array_merge($cawangan_detail, $summary_row ?: [
            'total_gadai_tebus_samahari' => 0,
            'jumlah_pinjaman' => 0.00
        ]);
        }, $active_cawangan_detail, $summary_data);

        return response()->json($response);
    }

    public function getLaporanGadaiTebusMasaCawangan(string $kod_cawangan, Request $request)
    {
         $ReportGadaianService = new ReportGadaianService($kod_cawangan, $request);
        return $ReportGadaianService->getCawanganReportGadaiTebusMasa();
    }



}
