<?php

namespace App\Services\Reports;

use App\Cawangan;
use App\CawanganDetail;
use App\Customer;
use App\Lelong;
use App\Marhun;
use App\LelMarhun;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;
use App\Helper;

class LelonganService
{

    public $kod_cawangan;
    public $startDate;
    public $endDate;

    public function __construct($kod_cawangan, Request $request)
    {
        $this->kod_cawangan = $kod_cawangan;
        $this->startDate = $request->startDate;
        $this->endDate = $request->endDate;
    }

    public function getCawanganReport()
    {
        $cawangan_connection = Cawangan::where('kodcaw', $this->kod_cawangan)->first()->mysql;

        $query_total_lelongan = Lelong::on($cawangan_connection)
        ->join('gadai', 'gadai.norujukan', '=', 'lelong.norujukan') // Joining gadai table
        ->select(
            'lelong.norujukan', 
            'gadai.nokp', 
            'gadai.masa', 
            'lelong.berat', 
            'lelong.nilaimarhun', 
            'gadai.pinjaman', 
            'gadai.percentpinj', 
            'lelong.teller',
            'gadai.bakipjm', // Fetch pinjaman from gadai
            'gadai.marhun' // Fetch marhun from gadai
        );

        $query_total_lelongan->when(!empty($this->startDate) and !empty($this->endDate), function ($q) {
            return $q->where('lelong.t_jual', '>=', $this->startDate)
                ->where('lelong.t_jual', '<=', $this->endDate);
        });

        $total_lelongan = $this->getTotalLelongan($query_total_lelongan);
        // dd($total_lelongan);
        $total_summary_lelongan_teller = $this->getTotalSummaryLelonganGroupByTeller($query_total_lelongan);
        $total_summary_lelongan = $this->getTotalSummaryLelongan($query_total_lelongan);
        $customer_info = $this->getCustomerInfo($total_lelongan);
        $marhun_lelongan = $this->getMarhunLelongan($total_lelongan, $cawangan_connection);
        $cawangan_detail = $this->getCawanganDetail($cawangan_connection);

        $total_lelongan = $total_lelongan->zip($customer_info, $marhun_lelongan)->map(function ($data) use ($total_lelongan) {

            [$lelong, $customer_info, $marhun_lelongan] = $data;

            return [
                'no_rujukan' => $lelong['norujukan'],
                'nama' => $customer_info['nama'],
                'alamat' => $customer_info['alamat1'],
                'no_kp' => $lelong['nokp'],
                'masa' => $lelong['masa'],
                'marhun' => $marhun_lelongan,
                'berat' => $lelong['berat'],
                'nilai_marhun' => $lelong['nilaimarhun'],
                'pinjaman' => $lelong['pinjaman'],
                'peratusan_margin_pinjaman' => $lelong['percentpinj'],
                'bakipjm' => $lelong['bakipjm'],
                'keuntungan' => $lelong['bakiupah'],
                'cajlelong' => $lelong['cajlelong'],
                'hargaasas' => $lelong['hargaasas'],
                'hargajual' => $lelong['hargajual'],
                'baki' => $lelong['baki'],
                'teller' => $lelong['teller'],
            ];
        });
       

        $total_lelongan = $total_lelongan->groupBy('teller');

        $result = [];

        foreach ($total_lelongan as $teller => $lelongan) {
            $result[$teller] = [
                'lelongan' => $lelongan->values(),
                'summary_lelongan_teller' => $total_summary_lelongan_teller[$teller] ?? [
                    'total_lelongan' => 0,
                    'total_berat' => 0,
                    'total_nilai_marhun' => 0,
                    'total_pembiayaan' => 0,
                    'total_bakipjm' => 0,
                    'total_keuntungan' => 0,
                    'total_cajlelong' => 0,
                    'total_hargaasas' => 0,
                    'total_hargajual' => 0,
                    'total_baki' => 0,
                ],
            ];
        }

        $total_lelongan = collect($result);


        $data = [
            'query' => [
                'startDate' => Helper::formatDate($this->startDate),
                'endDate' => Helper::formatDate($this->endDate),
            ],
            'cawangan_detail' => $cawangan_detail,
            'lelongan' => $total_lelongan,
            'summary_lelongan' => $total_summary_lelongan,
        ];

        return json_encode($data);

    }

    private function getCawanganDetail($cawangan_connection)
    {
        $cawangan_detail = CawanganDetail::on($cawangan_connection)->get()
            ->map(function ($detail) {
                return [
                    'nama' => $detail['namacaw'],
                    'code' => $detail['kodcaw'] . ' Ar-Rahn ' . $detail['cawangan'],
                    'alamat' => $detail['alamat1'],
                    'waktu_operasi' => $detail['alamat4'],
                ];
            });

        return $cawangan_detail[0];

    }

    private function getTotalSummaryLelongan(Builder $query_total_lelongan)
    {

        $total_summary_lelongan = (clone $query_total_lelongan)
            ->selectRaw('count(lelong.norujukan) as total_lelongan,
            sum(lelong.berat) as total_berat, 
            sum(lelong.nilaimarhun) as total_nilai_marhun, 
            sum(lelong.pinjaman) as total_pembiayaan,
            sum(gadai.bakipjm) as total_bakipjm,
            sum(lelong.bakiupah) as total_keuntungan,
            sum(lelong.cajlelong) as total_cajlelong,
            sum(lelong.hargaasas) as total_hargaasas,
            sum(lelong.hargajual) as total_hargajual,
            sum(lelong.baki) as total_baki')
            ->get()
            ->map(function ($x) {
                return [
                    'total_lelongan' => $x['total_lelongan'],
                    'total_berat' => $x['total_berat'],
                    'total_nilai_marhun' => $x['total_nilai_marhun'],
                    'total_pembiayaan' => $x['total_pembiayaan'],
                    'total_bakipjm' => $x['total_bakipjm'],
                    'total_keuntungan' => $x['total_keuntungan'],
                    'total_cajlelong' => $x['total_cajlelong'],
                    'total_hargaasas' => $x['total_hargaasas'],
                    'total_hargajual' => $x['total_hargajual'],
                    'total_baki' => $x['total_baki']
                ];
            });

        return $total_summary_lelongan[0];
    }
    // private function getTotalSummaryLelonganGroupByTeller(Builder $query_total_lelongan)
    // {

    //     $total_summary_lelongan_groupByTeller = (clone $query_total_lelongan)
    //         ->selectRaw('count(lelong.norujukan) as total_lelongan,
    //         sum(lelong.berat) as total_berat, 
    //         sum(lelong.nilaimarhun) as total_nilai_marhun, 
    //         sum(lelong.pinjaman) as total_pembiayaan,
    //         sum(gadai.bakipjm) as total_bakipjm,
    //         sum(lelong.bakiupah) as total_keuntungan,
    //         sum(lelong.cajlelong) as total_cajlelong,
    //         sum(lelong.hargaasas) as total_hargaasas,
    //         sum(lelong.hargajual) as total_hargajual,
    //         sum(lelong.baki) as total_baki')
    //         ->groupBy('lelong.teller')
    //         ->get();
    //     return $total_summary_lelongan_groupByTeller;
    // }
    private function getTotalSummaryLelonganGroupByTeller(Builder $query_total_lelongan)
    {
        return (clone $query_total_lelongan)
            ->selectRaw('
                lelong.teller,
                count(lelong.norujukan) as total_lelongan,
                sum(lelong.berat) as total_berat, 
                sum(lelong.nilaimarhun) as total_nilai_marhun, 
                sum(lelong.pinjaman) as total_pembiayaan,
                sum(gadai.bakipjm) as total_bakipjm,
                sum(lelong.bakiupah) as total_keuntungan,
                sum(lelong.cajlelong) as total_cajlelong,
                sum(lelong.hargaasas) as total_hargaasas,
                sum(lelong.hargajual) as total_hargajual,
                sum(lelong.baki) as total_baki
            ')
            ->groupBy('lelong.teller')
            ->get()
            ->keyBy('teller'); // ⭐ PENTING
    }


    private function getTotalLelongan(Builder $query_total_lelongan)
    {
        $total_lelongan = (clone $query_total_lelongan)
            ->select('lelong.t_lelong', 
            'lelong.norujukan', 
            'gadai.nokp', 
            'gadai.semakbarcode', 
            'gadai.masa', 
            'lelong.berat', 
            'lelong.nilaimarhun', 
            'lelong.pinjaman', 
            'gadai.upah12', 
            'gadai.percentpinj', 
            'lelong.teller', 
            'gadai.nokp',
            'gadai.bakipjm',
            'lelong.bakiupah',
            'lelong.cajlelong',
            'lelong.hargaasas',
            'lelong.hargajual',
            'lelong.baki')
            ->orderBy('lelong.teller')
            ->orderBy('gadai.sirig')
            ->get();
        return $total_lelongan;
    }

    private function getCustomerInfo($total_lelongan)
    {

        $extract_nokp = $total_lelongan->pluck('nokp');
        // dd($extract_nokp);

        $customer_info = Customer::on('mysql7')
            ->select(['nama', 'alamat1', 'kpbaru', 'kplama'])
            ->where(function ($query) use ($extract_nokp) {
                $query->whereIn('kpbaru', $extract_nokp)
                    ->orWhereIn('kplama', $extract_nokp);
            })
            ->get();
        // dd($customer_info);

        $customer_info = collect($extract_nokp)->map(function ($nokp) use ($customer_info) {
            return $customer_info->where('kpbaru', $nokp)->first() ?? $customer_info->where('kplama', $nokp)->first();
        })->toArray();

        return $customer_info;
    }

    private function getMarhunLelongan($total_lelongan, $cawangan_connection)
    {

        $extract_norujukan = $total_lelongan->pluck('norujukan');

        $marhun_lelongan = LelMarhun::on($cawangan_connection)
            ->select('norujukan', 'bil', 'marhun')
            ->whereIn('norujukan', $extract_norujukan)
            ->get()
            ->groupBy('norujukan')
            ->toArray();

        $marhun_lelongan = array_map(function ($lelongan) {
            return array_map(function ($norujukan) {
                return $norujukan['bil'] . ' ' . $norujukan['marhun'];
            }, $lelongan);
        }, $marhun_lelongan);

        $marhun_lelongan = array_map(function ($lelongan) {
            return implode(',', $lelongan);
        }, $marhun_lelongan);
        return $marhun_lelongan;
    }

     public function getCawanganReportBakiLelongBelumTuntut()
    {
        $cawangan_connection = Cawangan::where('kodcaw', $this->kod_cawangan)->first()->mysql;
        $endDate = $this->endDate;
        $query_total_lelongan = Lelong::on($cawangan_connection)
        ->join('gadai', 'gadai.norujukan', '=', 'lelong.norujukan') // Joining gadai table
        ->select(
            'lelong.t_lelong',
            'lelong.norujukan', 
            'gadai.nokp', 
            'gadai.masa', 
            'lelong.berat', 
            'lelong.nilaimarhun', 
            'gadai.pinjaman', 
            'gadai.percentpinj', 
            'lelong.teller',
            'gadai.bakipjm', // Fetch pinjaman from gadai
            'gadai.marhun' // Fetch marhun from gadai
        )
         ->where(function ($query) use ($endDate) {
        $query->whereNull('lelong.trkhtuntut')
              ->orWhere('lelong.trkhtuntut', '>', $endDate); // or use a specific date: '2025-06-01'
        })
        ->where('lelong.baki', '>', 0.00)
        ->orderBy('lelong.norujukan', 'asc');


        $query_total_lelongan->when(!empty($this->startDate) and !empty($this->endDate), function ($q) {
            return $q->where('lelong.t_lelong', '>=', $this->startDate)
                ->where('lelong.t_lelong', '<=', $this->endDate);
        });

        $total_lelongan = $this->getTotalLelongan($query_total_lelongan);
        // dd($total_lelongan);
        $total_summary_lelongan_teller = $this->getTotalSummaryLelonganGroupByTeller($query_total_lelongan);
        $total_summary_lelongan = $this->getTotalSummaryLelongan($query_total_lelongan);
        $customer_info = $this->getCustomerInfo($total_lelongan);
        $marhun_lelongan = $this->getMarhunLelongan($total_lelongan, $cawangan_connection);
        $cawangan_detail = $this->getCawanganDetail($cawangan_connection);

        $total_lelongan = $total_lelongan->zip($customer_info, $marhun_lelongan)->map(function ($data) {

            [$lelong, $customer_info, $marhun_lelongan] = $data;

            return [
                'no_rujukan' => $lelong['norujukan'],
                'nama' => $customer_info['nama'] ?? '-',
                'alamat' => $customer_info['alamat1'] ?? '-',
                'no_kp' => $lelong['nokp'],
                'masa' => $lelong['masa'],
                'marhun' => $marhun_lelongan,
                'berat' => $lelong['berat'],
                'nilai_marhun' => $lelong['nilaimarhun'],
                'pinjaman' => $lelong['pinjaman'],
                'peratusan_margin_pinjaman' => $lelong['percentpinj'],
                'bakipjm' => $lelong['bakipjm'],
                'keuntungan' => $lelong['bakiupah'],
                'cajlelong' => $lelong['cajlelong'],
                'hargaasas' => $lelong['hargaasas'],
                'hargajual' => $lelong['hargajual'],
                'baki' => $lelong['baki'],
                'teller' => $lelong['teller'],
                't_lelong' => $lelong['t_lelong'],
            ];
        });
       

        $total_lelongan = $total_lelongan->groupBy('teller');

        $lelongan_tellers = $total_lelongan->keys();

        $total_lelongan = $total_lelongan
            ->zip($total_summary_lelongan_teller)
            ->map(function ($data) {

                [$lelongan, $summary] = $data;

                return [
                    'lelongan' => $lelongan,
                    'summary_lelongan_teller' => $summary,
                ];
            });

        $total_lelongan = $lelongan_tellers->combine($total_lelongan);

        $data = [
            'query' => [
                'startDate' => Helper::formatDate($this->startDate),
                'endDate' => Helper::formatDate($this->endDate),
            ],
            'cawangan_detail' => $cawangan_detail,
            'lelongan' => $total_lelongan,
            'summary_lelongan' => $total_summary_lelongan,
        ];

        return json_encode($data);

    }
}