<?php

namespace App\Services\Miscellaneous;

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;
use App\Tebus;
use Illuminate\Support\Carbon;


class StatistikService
{
    protected $kodcaw;
    protected $mysql;
    protected $mula;
    protected $akhir;

    public function __construct($kodcaw,$mula,$akhir)
    {
        $this->kodcaw = $kodcaw;
        $this->mysql = Cawangan::on('mysql7')->where('kodcaw', $kodcaw)->value('mysql');
        $this->mula = $mula;
        $this->akhir = $akhir;
    }

    public function LaporanStatistik($gadai) : array
    {
        $reportPembiayaan = $this->countPawnByType($gadai);
        $reportLoanAmount = $this->countPawnByLoan($gadai);
        $reportMarhunValue = $this->countMarhunByValue($gadai);
        $reportMarhunMargin = $this->countMarhunByMargin($gadai);
        $reportCustomerJob = $this->countCustomerByJob($gadai);
        $reportCustomerRace = $this->countCustomerByRace($gadai);
        //  dd($reportCustomerRace);

        return [
            'reportPembiayaan' => $reportPembiayaan,
            'reportLoanAmount' => $reportLoanAmount,
            'reportMarhunValue' => $reportMarhunValue,
            'reportMarhunMargin' => $reportMarhunMargin,
            'reportCustomerJob' => $reportCustomerJob,
            'reportCustomerRace' => $reportCustomerRace,
        ];
    }

    private function countPawnByType($gadai) 
    {
        $pembiayaanbaru = Tebus::on($this->mysql)
            ->join('gadai as g1', 'tebus.norujukan', '=', 'g1.norujukan')
            ->join('gadai as g2', function ($join) {
                $join->on('g2.nokp', '=', 'g1.nokp')
                    ->on('g2.t_gadai', '=', 'tebus.t_tebus')
                    ->whereRaw('ABS(g2.berat - g1.berat) <= 0.02')
                    ->whereColumn('g2.norujukan', '!=', 'g1.norujukan');
            })
            ->whereBetween('tebus.t_tebus', [$this->mula, $this->akhir])
            ->groupBy('tebus.norujukan')
            ->selectRaw("
            tebus.norujukan as norujukan_tebus,
            MAX(g2.norujukan) as norujukanpembiayaansemula,
            MAX(g1.taglel) as taglel_lama
            ")
            ->get()
            ->toArray();

        $pembiayaanbaruNorujukan = array_column($pembiayaanbaru, 'norujukanpembiayaansemula');
        $filteredNorujukanLelongan = array_column(
            array_filter($pembiayaanbaru, function ($item) {
                return $item['taglel_lama'] == 1;
            }),
            'norujukanpembiayaansemula'
        );
        $filteredPSNorujukan = array_column(
            array_filter($pembiayaanbaru, function ($item) {
                return $item['taglel_lama'] == 0;
            }),
            'norujukanpembiayaansemula'
        );

        
        $pembiayaanbaru = $gadai->whereIn('norujukan', $filteredPSNorujukan)->count();
        $pembiayaansemula = $gadai->whereNotIn('norujukan', $pembiayaanbaruNorujukan)->count();
        $lelongan = $gadai->whereIn('norujukan', $filteredNorujukanLelongan)->count();
        
            return [
                'pembiayaanbaru' => $pembiayaanbaru,
                'pembiayaansemula' => $pembiayaansemula,
                'lelongan' => $lelongan
            ];
    }

    private function countPawnByLoan($gadai)
    {
        return collect($gadai)->countBy(function ($item) {
            $loan = $item['pinjaman'];

            if ($loan <= 400.99) return '< 400.99';
            if ($loan >= 401 && $loan <= 2000) return '401 - 2000';
            if ($loan >= 2001 && $loan <= 24999) return '2001 - 2499';
            if ($loan >= 2500) return '>= 2500';
            if ($loan > 101000) return '>101000';
        })->toArray();
    }

    private function countMarhunByValue($gadai)
    {
        return collect($gadai)->countBy(function ($item) {
            $nilaimarhun = $item['nilaimarhun'];

            if ($nilaimarhun <= 400.99) return '< 1-400';
            if ($nilaimarhun >= 401 && $nilaimarhun <= 2000) return '401-2000';
            if ($nilaimarhun > 2001) return '> 2001';
        })->toArray();
    }

    private function countMarhunByMargin($gadai)
    {
       return collect($gadai)->countBy(function ($item) {
            $margin= $item['percentpinj'];

            if ($margin >= 10 && $margin <= 40.99) return '10-40.99';
            if ($margin >= 41 && $margin <= 60.99) return '41-60.99';
            if ($margin >= 61 && $margin <= 80) return '> 61-80';
        })->toArray();
    }

    private function countCustomerByJob($gadai)
    {
        $jobMap = [
        1 => 'Peniaga',
        2 => 'Pekerja Swasta',
        3 => 'Pekerja Kerajaan',
        4 => 'Bekerja Sendiri',
        5 => 'Suri Rumah',
        6 => 'Lain-lain',
    ];

    return collect($gadai)->countBy(function ($item) use ($jobMap) {
        $kodkerja = floatval($item['kodkerja']);
        return $jobMap[$kodkerja] ?? 'Lain-lain';
    })->toArray();

    }

    private function countCustomerByRace($gadai){

        $raceMap = [
        'M' => 'Melayu',
        'C' => 'Cina',
        'I' => 'India',
        'L' => 'Lain-lain',   
    ];

    return collect($gadai)->countBy(function ($item) use ($raceMap) {
        return $raceMap[$item['bangsa']] ?? 'Lain-lain';
        
    })->toArray();

    }
}