<?php

namespace App\Services\Reports;

use App\Cawangan;
use App\CawanganDetail;
use App\Customer;
use App\Gadai;
use App\Marhun;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;
use App\Helper;
use Illuminate\Support\Facades\DB;

class GadaianService
{

    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_gadaian = Gadai::on($cawangan_connection);

        $query_total_gadaian->when(!empty($this->startDate) and !empty($this->endDate), function ($q) {
            return $q->where('t_gadai', '>=', $this->startDate)
                ->where('t_gadai', '<=', $this->endDate);
        });

        $total_gadaian = $this->getTotalGadaian($query_total_gadaian);
        $total_summary_gadaian_teller = $this->getTotalSummaryGadaianGroupByTeller($query_total_gadaian);
        $total_summary_gadaian = $this->getTotalSummaryGadaian($query_total_gadaian);
        $customer_info = $this->getCustomerInfo($total_gadaian);
        $marhun_gadaian = $this->getMarhunGadaian($total_gadaian, $cawangan_connection);
        $cawangan_detail = $this->getCawanganDetail($cawangan_connection);

        $total_gadaian = $total_gadaian->zip($customer_info, $marhun_gadaian)->map(function ($data) {

            [$gadai, $customer_info, $marhun_gadaian] = $data;

            return [
                'no_rujukan' => $gadai['norujukan'],
                'nama' => $customer_info['nama'],
                'alamat' => $customer_info['alamat1'],
                'no_kp' => $gadai['nokp'],
                'masa' => $gadai['masa'],
                'marhun' => $marhun_gadaian,
                'berat' => $gadai['berat'],
                'nilai_marhun' => $gadai['nilaimarhun'],
                'pembiayaan' => $gadai['pinjaman'],
                'peratusan_margin_pinjaman' => $gadai['percentpinj'],
                'teller' => $gadai['teller'],
            ];

        });

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

        $gadaian_tellers = $total_gadaian->keys();

        $total_gadaian = $total_gadaian
            ->zip($total_summary_gadaian_teller)
            ->map(function ($data) {

                [$gadaian, $summary] = $data;

                return [
                    'gadaian' => $gadaian,
                    'summary_gadaian_teller' => $summary,
                ];
            });

        $total_gadaian = $gadaian_tellers->combine($total_gadaian);

        $data = [
            'query' => [
                'startDate' => Helper::formatDate($this->startDate),
                'endDate' => Helper::formatDate($this->endDate),
            ],
            'cawangan_detail' => $cawangan_detail,
            'gadaian' => $total_gadaian,
            'summary_gadaian' => $total_summary_gadaian,
        ];

        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 getTotalSummaryGadaian(Builder $query_total_gadaian)
    {

        $total_summary_gadaian = (clone $query_total_gadaian)
            ->selectRaw('sum(berat) as total_berat, sum(nilaimarhun) as total_nilai_marhun, sum(pinjaman) as total_pembiayaan')
            ->get()
            ->map(function ($x) {
                return [
                    'total_berat' => $x['total_berat'],
                    'total_nilai_marhun' => $x['total_nilai_marhun'],
                    'total_pembiayaan' => $x['total_pembiayaan'],
                ];
            });

        return $total_summary_gadaian[0];
    }
    private function getTotalSummaryGadaianGroupByTeller(Builder $query_total_gadaian)
    {

        $total_summary_gadaian_groupByTeller = (clone $query_total_gadaian)
            ->selectRaw('sum(berat) as total_berat, sum(nilaimarhun) as total_nilai_marhun, sum(pinjaman) as total_pembiayaan')
            ->groupBy('teller')
            ->get();
        return $total_summary_gadaian_groupByTeller;
    }

    private function getTotalGadaian(Builder $query_total_gadaian)
    {
        $total_gadaian = (clone $query_total_gadaian)
            ->select('t_gadai', 'norujukan', 'nokp', 'semakbarcode', 'masa', 'berat', 'nilaimarhun', 'pinjaman', 'upah12', 'percentpinj', 'teller', 'nokp')
            ->orderBy('teller')
            ->orderBy('sirig')
            ->get();
        return $total_gadaian;
    }

    private function getCustomerInfo($total_gadaian)
    {

        $extract_nokp = $total_gadaian->pluck('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();

        $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 getMarhunGadaian($total_gadaian, $cawangan_connection)
    {

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

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

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

        $marhun_gadaian = array_map(function ($gadaian) {
            return implode(',', $gadaian);
        }, $marhun_gadaian);

        return $marhun_gadaian;
    }

    public function getCawanganReportGadaiTebusMasa()
    {
        $cawangan_connection = Cawangan::where('kodcaw', $this->kod_cawangan)->firstOrFail()->mysql;
        $online_connection = Helper::getOnlineArrahnConnection();
        $startDate = $this->startDate;
        $endDate = $this->endDate;

        // 1. Ambil data gadai dari cawangan
        $gadai_data = DB::connection($cawangan_connection)->table('gadai')
            ->whereBetween('t_gadai', [$startDate, $endDate])
            ->get();

        if ($gadai_data->isEmpty()) {
            return response()->json([
                'query' => [
                    'startDate' => Helper::formatDate($startDate),
                    'endDate' => Helper::formatDate($endDate),
                ],
                'cawangan_detail' => $this->getCawanganDetail($cawangan_connection),
                'gadaian' => [],
                'summary_gadaian' => [],
            ]);
        }

        // 2. Ambil norujukan & tarikh dari gadai
        $norujukans = $gadai_data->pluck('norujukan')->unique();
        $tarikh_set = $gadai_data->pluck('t_gadai')->map(fn($d) => date('Y-m-d', strtotime($d)))->unique();

        // 3. Ambil transaksi tebus dari online server
        $transaksi_tebus = DB::connection($online_connection)->table('transaction')
            ->whereIn('trans_type', ['T', 'AT'])
            ->whereIn('norujukan', $norujukans)
            ->whereIn(DB::raw('DATE(created_at)'), $tarikh_set)
            ->get()
            ->groupBy(fn($trans) => $trans->norujukan . '|' . date('Y-m-d', strtotime($trans->created_at)));

        // 4. Padankan
        $matched = collect();

        foreach ($gadai_data as $item) {
            $tarikh = date('Y-m-d', strtotime($item->t_gadai));
            $key = $item->norujukan . '|' . $tarikh;

            if ($transaksi_tebus->has($key)) {
                $trans = $transaksi_tebus[$key]->first(); // satu transaksi je

                $masa_gadai = $item->masa;
                $masa_tebus = date('H:i:s', strtotime($trans->created_at));
                $beza_saat = abs(strtotime($masa_tebus) - strtotime($masa_gadai));
                $beza_masa = gmdate("H:i:s", $beza_saat);

                $matched->push((object)[
                    't_gadai' => $item->t_gadai,
                    'norujukan' => $item->norujukan,
                    'nokp' => $item->nokp,
                    'semakbarcode' => $item->semakbarcode,
                    'masa' => $masa_gadai,
                    'masa_tebus' => $masa_tebus,
                    'beza_minit' => $beza_masa,
                    'berat' => $item->berat,
                    'nilaimarhun' => $item->nilaimarhun,
                    'pinjaman' => $item->pinjaman,
                    'upah12' => $item->upah12,
                    'percentpinj' => $item->percentpinj,
                    'teller' => $item->teller,
                    'bakipjm' => $item->bakipjm,
                    'bakiupah' => $item->bakiupah,
                ]);
            }
        }

        // 5. Data tambahan
        $customer_info = $this->getCustomerInfo($matched);
        $marhun_info = $this->getMarhunGadaiTebusMasa($matched, $cawangan_connection);
        $cawangan_detail = $this->getCawanganDetail($cawangan_connection);
        $summary = $this->getTotalSummaryGadaiTebusMasa($matched);

        // 6. Gabung akhir
        $report_data = $matched->zip($customer_info, $marhun_info)->map(function ($data) {
            [$gadai, $customer, $marhun] = $data;

            return [
                'no_rujukan' => $gadai->norujukan,
                'nama' => $customer['nama'] ?? '-',
                'alamat' => $customer['alamat1'] ?? '-',
                'no_kp' => $gadai->nokp,
                'masa' => $gadai->masa,
                'masa_tebus' => $gadai->masa_tebus,
                'beza_minit' => $gadai->beza_minit,
                'marhun' => $marhun,
                'berat' => $gadai->berat,
                'nilai_marhun' => $gadai->nilaimarhun,
                'pinjaman' => $gadai->pinjaman,
                'peratusan_margin_pinjaman' => $gadai->percentpinj,
                'bakipjm' => $gadai->bakipjm,
                'keuntungan' => $gadai->bakiupah,
                'teller' => $gadai->teller,
                't_gadai' => $gadai->t_gadai,
            ];
        });

        $report_data = $report_data->sortBy(function ($item) {
            list($hours, $minutes, $seconds) = explode(':', $item['beza_minit']);
            return ((int)$hours * 3600) + ((int)$minutes * 60) + (int)$seconds;
        })->values();

        return response()->json([
            'query' => [
                'startDate' => Helper::formatDate($startDate),
                'endDate' => Helper::formatDate($endDate),
            ],
            'cawangan_detail' => $cawangan_detail,
            'gadaian' => $report_data,
            'summary_gadaian' => $summary,
        ]);
    }

    private function formatBezaMinit($minit)
    {
        if (is_null($minit)) {
            return '-';
        }

        $abs = abs($minit);
        $jam = floor($abs / 60);
        $baki = $abs % 60;
        $tanda = $minit < 0 ? '-' : '';

        return "{$tanda}{$jam} jam {$baki} minit";
    }


     private function getTotalGadaiTebusMasa($query_total_gadaitebusmasa)
    {
        $total_gadaitebusmasa = (clone $query_total_gadaitebusmasa)
            ->select('gadai.t_gadai', 
            'gadai.norujukan', 
            'gadai.nokp', 
            'gadai.semakbarcode', 
            'gadai.masa', 
            'gadai.berat', 
            'gadai.nilaimarhun', 
            'gadai.pinjaman', 
            'gadai.upah12', 
            'gadai.percentpinj', 
            'gadai.teller', 
            'gadai.nokp',
            'gadai.bakipjm',
            'gadai.bakiupah',
            DB::raw('TIMEDIFF(TIME(trans.created_at), gadai.masa) as beza_minit'))
            ->where('gadai.upah12', '>=', 0.00) 
            ->orderBy('gadai.teller')
            ->orderBy('gadai.sirig')
            ->get();
        return $total_gadaitebusmasa;
    }

    private function getTotalSummaryGadaiTebusMasa($collection)
    {
        return [
            'total_gadaitebusmasa' => $collection->count(),
            'total_berat' => $collection->sum('berat'),
            'total_nilai_marhun' => $collection->sum('nilaimarhun'),
            'total_pembiayaan' => $collection->sum('pinjaman'),
            'total_bakipjm' => $collection->sum('bakipjm'),
            'total_keuntungan' => $collection->sum('bakiupah'),
        ];
    }

    private function getTotalSummaryGadaiTebusMasaGroupByTeller($query_total_gadaitebusmasa)
    {

        $total_summary_gadaian_groupByTeller = (clone $query_total_gadaitebusmasa)
            ->selectRaw('count(gadai.norujukan) as total_gadaitebusmasa,
            sum(gadai.berat) as total_berat, 
            sum(gadai.nilaimarhun) as total_nilai_marhun, 
            sum(gadai.pinjaman) as total_pembiayaan,
            sum(gadai.bakipjm) as total_bakipjm,
            sum(gadai.bakiupah) as total_keuntungan')
            ->where('gadai.upah12', '>=', 0.00) 
            ->groupBy('gadai.teller')
            ->get();
        return $total_summary_gadaian_groupByTeller;
    }

    private function getMarhunGadaiTebusMasa($total_gadaitebusmasa, $cawangan_connection)
    {

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

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

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

        $marhun_gadaitebusmasa = array_map(function ($gadaian) {
            return implode(',', $gadaian);
        }, $marhun_gadaitebusmasa);
        return $marhun_gadaitebusmasa;
    }
}