<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\Sale;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;

class LaporanController extends Controller
{
    private $namaBulan = [
        1 => 'Januari', 2 => 'Februari', 3 => 'Maret',
        4 => 'April',   5 => 'Mei',      6 => 'Juni',
        7 => 'Juli',    8 => 'Agustus',  9 => 'September',
        10 => 'Oktober',11 => 'November',12 => 'Desember',
    ];

    public function index(Request $request)
    {
        $tahun       = $request->get('tahun', date('Y'));
        $bulanFilter = $request->get('bulan_filter');

        // ── 1. MAPPER OTOMATIS BULAN FILTER ──
        $mapBulanAngka = [
            'Januari' => 1, 'Februari' => 2, 'Maret' => 3, 'April' => 4,
            'Mei' => 5, 'Juni' => 6, 'Juli' => 7, 'Agustus' => 8,
            'September' => 9, 'Oktober' => 10, 'November' => 11, 'Desember' => 12
        ];

        // Konversi teks bulan ke angka
        if (isset($mapBulanAngka[$bulanFilter])) {
            $bulanFilter = $mapBulanAngka[$bulanFilter];
        } else {
            $bulanFilter = (int) $bulanFilter;
        }

        // Ambil daftar bulan yang tersedia di database tahun ini
        $bulanTersedia = Sale::selectRaw('EXTRACT(MONTH FROM tgl)::int as bulan')
            ->whereYear('tgl', $tahun)
            ->groupBy(DB::raw('EXTRACT(MONTH FROM tgl)::int'))
            ->orderBy('bulan')
            ->pluck('bulan')
            ->toArray();

        if (empty($bulanTersedia)) {
            $bulanTersedia = range(1, 12);
        }

        $bulanDipilih = $request->get('bulan', $bulanTersedia);
        if (empty($bulanDipilih)) $bulanDipilih = $bulanTersedia;
        $bulanDipilih = array_map('intval', $bulanDipilih);

        // Jika bulan filter kosong/0, default ke bulan terakhir yang ada datanya atau bulan ini
        if ($bulanFilter === 0) {
            $bulanFilter = !empty($bulanTersedia) ? (int) end($bulanTersedia) : (int) date('m');
        }

        // ── 2. QUERY GMV PER CHANNEL PER BULAN ──
        $gmvChannel = []; $gmvBaseCust = [];
        foreach ($bulanDipilih as $bulan) {
            $rows = Sale::selectRaw('channel_id, SUM(nominal_gmv) as total_gmv')
                ->whereYear('tgl', $tahun)
                ->whereMonth('tgl', $bulan)
                ->where('status', '!=', 'reject')
                ->with('channel')->groupBy('channel_id')->orderByDesc('total_gmv')->get();
            $gmvChannel[$bulan] = ['rows' => $rows, 'grand_total' => $rows->sum('total_gmv')];

            // GMV per Base Customer
            $baseCustRows = Sale::selectRaw("
                    CASE 
                        WHEN tipe_pembelian = '' OR tipe_pembelian IS NULL THEN 'BELUM DIISI' 
                        ELSE tipe_pembelian 
                    END as tipe_pembelian, 
                    COUNT(*) as total_beli, 
                    SUM(nominal_gmv) as total_gmv
                ")
                ->whereYear('tgl', $tahun)
                ->whereMonth('tgl', $bulan)
                ->where('status', '!=', 'reject')
                ->groupBy(DB::raw("
                    CASE 
                        WHEN tipe_pembelian = '' OR tipe_pembelian IS NULL THEN 'BELUM DIISI' 
                        ELSE tipe_pembelian 
                    END
                "))
                ->orderByDesc('total_gmv')
                ->get();
                
            // SINKRONISASI DISINI: Ditambahkan grand_beli agar View Blade tidak crash mencari property-nya
            $gmvBaseCust[$bulan] = [
                'rows' => $baseCustRows, 
                'grand_total' => $baseCustRows->sum('total_gmv'),
                'grand_beli' => $baseCustRows->sum('total_beli')
            ];
        }

        // ── 3. QUERY GMV PER CS PER BULAN ──
        $gmvCs = [];
        foreach ($bulanDipilih as $bulan) {
            $rows = Sale::selectRaw('nama_cs, SUM(nominal_gmv) as total_gmv, COUNT(*) as total_trx')
                ->whereYear('tgl', $tahun)
                ->whereMonth('tgl', $bulan)
                ->where('status', '!=', 'reject')->whereNotNull('nama_cs')
                ->groupBy('nama_cs')->orderByDesc('total_gmv')->get();
            $gmvCs[$bulan] = ['rows' => $rows, 'grand_total' => $rows->sum('total_gmv')];
        }

        // ── 4. QUERY GMV INDIVIDU RANKING ──
        $gmvIndividu = Sale::selectRaw('nama_cs, SUM(nominal_gmv) as total_gmv')
            ->whereYear('tgl', $tahun)
            ->whereIn(DB::raw('EXTRACT(MONTH FROM tgl)::int'), $bulanDipilih)
            ->where('status', '!=', 'reject')->whereNotNull('nama_cs')
            ->groupBy('nama_cs')->orderByDesc('total_gmv')->get();

        // ── 5. SECTION 4: QTY PER CS & CHANNEL ──
        $qtyCs = Sale::selectRaw('
                nama_cs, channel_id,
                SUM(COALESCE(qty_kue_keranjang, 0)) as s_kue,
                SUM(COALESCE(qty_cookies, 0)) as s_cookies,
                SUM(COALESCE(qty_toples, 0)) as s_toples,
                SUM(COALESCE(qty_mini, 0)) as s_mini,
                SUM(COALESCE(qty_pouch, 0)) as s_pouch,
                SUM(COALESCE(qty_toples, 0) + COALESCE(qty_mini, 0) + COALESCE(qty_pouch, 0)) as s_total
            ')
            ->whereYear('tgl', $tahun)
            ->whereMonth('tgl', $bulanFilter)
            ->where('status', '!=', 'reject')
            ->whereNotNull('nama_cs')->with('channel')
            ->groupBy('nama_cs', 'channel_id')->orderBy('nama_cs')->get();

        // ── 6. SECTION 5: QTY PER CHANNEL ──
        $qtyChannel = Sale::selectRaw('
                channel_id,
                SUM(COALESCE(qty_kue_keranjang, 0)) as s_kue,
                SUM(COALESCE(qty_cookies, 0)) as s_cookies,
                SUM(COALESCE(qty_toples, 0)) as s_toples,
                SUM(COALESCE(qty_mini, 0)) as s_mini,
                SUM(COALESCE(qty_pouch, 0)) as s_pouch,
                SUM(COALESCE(qty_toples, 0) + COALESCE(qty_mini, 0) + COALESCE(qty_pouch, 0)) as s_total
            ')
            ->whereYear('tgl', $tahun)
            ->whereMonth('tgl', $bulanFilter)
            ->where('status', '!=', 'reject')
            ->with('channel')->groupBy('channel_id')->orderByDesc('s_toples')->get();

        // ── 7. GMV CHANNEL BASE CUSTOMER ──
        $gmvChannelBase = Sale::selectRaw('channel_id, SUM(nominal_gmv) as total_gmv')
            ->whereYear('tgl', $tahun)
            ->whereMonth('tgl', $bulanFilter)
            ->where('status', '!=', 'reject')
            ->with('channel')->groupBy('channel_id')->orderByDesc('total_gmv')->get();

        return view('laporan', compact(
            'gmvChannel', 'gmvCs', 'gmvIndividu', 'gmvBaseCust',
            'qtyCs', 'qtyChannel', 'gmvChannelBase',
            'bulanDipilih', 'bulanTersedia', 'tahun', 'bulanFilter'
        ) + ['namaBulan' => $this->namaBulan]);
    }

    public function exportCsv(Request $request)
    {
        $tahun         = $request->get('tahun', date('Y'));
        $namaBulan     = $this->namaBulan;

        $mapBulanAngka = [
            'Januari'=>1,'Februari'=>2,'Maret'=>3,'April'=>4,
            'Mei'=>5,'Juni'=>6,'Juli'=>7,'Agustus'=>8,
            'September'=>9,'Oktober'=>10,'November'=>11,'Desember'=>12,
        ];

        $bulanTersedia = Sale::selectRaw('EXTRACT(MONTH FROM tgl)::int as bulan')
            ->whereYear('tgl', $tahun)
            ->groupBy(DB::raw('EXTRACT(MONTH FROM tgl)::int'))->orderBy('bulan')
            ->pluck('bulan')->toArray();
        if (empty($bulanTersedia)) $bulanTersedia = range(1, 12);

        $bulanDipilih = $request->get('bulan', $bulanTersedia);
        if (empty($bulanDipilih)) $bulanDipilih = $bulanTersedia;
        $bulanDipilih = array_map('intval', $bulanDipilih);

        $bulanFilterRaw = $request->get('bulan_filter');
        if (isset($mapBulanAngka[$bulanFilterRaw])) {
            $bulanFilter = $mapBulanAngka[$bulanFilterRaw];
        } elseif (!empty($bulanFilterRaw)) {
            $bulanFilter = (int) $bulanFilterRaw;
        } else {
            $bulanFilter = !empty($bulanDipilih) ? (int) end($bulanDipilih) : (int) date('m');
        }
        if ($bulanFilter === 0) $bulanFilter = !empty($bulanDipilih) ? (int) end($bulanDipilih) : (int) date('m');

        // Ambil semua data sama seperti exportExcel
        $gmvChannel = []; $gmvBaseCust = []; $gmvCs = [];
        foreach ($bulanDipilih as $bulan) {
            $r1 = Sale::selectRaw('channel_id, SUM(nominal_gmv) as total_gmv')
                ->whereYear('tgl', $tahun)->whereMonth('tgl', $bulan)
                ->where('status', '!=', 'reject')
                ->with('channel')->groupBy('channel_id')->orderByDesc('total_gmv')->get();
            $gmvChannel[$bulan] = ['rows' => $r1, 'grand_total' => $r1->sum('total_gmv')];

            $r2 = Sale::selectRaw("CASE WHEN tipe_pembelian = '' OR tipe_pembelian IS NULL THEN 'BELUM DIISI' ELSE tipe_pembelian END as tipe_pembelian, COUNT(*) as total_beli, SUM(nominal_gmv) as total_gmv")
                ->whereYear('tgl', $tahun)->whereMonth('tgl', $bulan)
                ->where('status', '!=', 'reject')
                ->groupBy(DB::raw("CASE WHEN tipe_pembelian = '' OR tipe_pembelian IS NULL THEN 'BELUM DIISI' ELSE tipe_pembelian END"))
                ->orderByDesc('total_gmv')->get();
            $gmvBaseCust[$bulan] = ['rows' => $r2, 'grand_total' => $r2->sum('total_gmv'), 'grand_beli' => $r2->sum('total_beli')];

            $r3 = Sale::selectRaw('nama_cs, SUM(nominal_gmv) as total_gmv, COUNT(*) as total_trx')
                ->whereYear('tgl', $tahun)->whereMonth('tgl', $bulan)
                ->where('status', '!=', 'reject')->whereNotNull('nama_cs')
                ->groupBy('nama_cs')->orderByDesc('total_gmv')->get();
            $gmvCs[$bulan] = ['rows' => $r3, 'grand_total' => $r3->sum('total_gmv')];
        }

        $gmvIndividu = Sale::selectRaw('nama_cs, SUM(nominal_gmv) as total_gmv')
            ->whereYear('tgl', $tahun)
            ->whereIn(DB::raw('EXTRACT(MONTH FROM tgl)::int'), $bulanDipilih)
            ->where('status', '!=', 'reject')->whereNotNull('nama_cs')
            ->groupBy('nama_cs')->orderByDesc('total_gmv')->get();

        $qtyCs = [];
        foreach ($bulanDipilih as $bulan) {
            $qtyCs[$bulan] = Sale::selectRaw('nama_cs, channel_id, SUM(COALESCE(qty_kue_keranjang,0)) as s_kue, SUM(COALESCE(qty_cookies,0)) as s_cookies, SUM(COALESCE(qty_toples,0)) as s_toples, SUM(COALESCE(qty_mini,0)) as s_mini, SUM(COALESCE(qty_pouch,0)) as s_pouch, SUM(COALESCE(qty_toples,0)+COALESCE(qty_mini,0)+COALESCE(qty_pouch,0)) as s_total')
                ->whereYear('tgl', $tahun)->whereMonth('tgl', $bulan)
                ->where('status', '!=', 'reject')->whereNotNull('nama_cs')
                ->with('channel')->groupBy('nama_cs', 'channel_id')->orderBy('nama_cs')->get();
        }

        $qtyChannel = [];
        foreach ($bulanDipilih as $bulan) {
            $qtyChannel[$bulan] = Sale::selectRaw('channel_id, SUM(COALESCE(qty_kue_keranjang,0)) as s_kue, SUM(COALESCE(qty_cookies,0)) as s_cookies, SUM(COALESCE(qty_toples,0)) as s_toples, SUM(COALESCE(qty_mini,0)) as s_mini, SUM(COALESCE(qty_pouch,0)) as s_pouch, SUM(COALESCE(qty_toples,0)+COALESCE(qty_mini,0)+COALESCE(qty_pouch,0)) as s_total')
                ->whereYear('tgl', $tahun)->whereMonth('tgl', $bulan)
                ->where('status', '!=', 'reject')
                ->with('channel')->groupBy('channel_id')->orderByDesc('s_toples')->get();
        }

        $transaksi = Sale::with('channel')
            ->whereYear('tgl', $tahun)
            ->whereIn(DB::raw('EXTRACT(MONTH FROM tgl)::int'), $bulanDipilih)
            ->orderBy('tgl')->orderBy('id')->get();

        $statusLabel = [
            'pending'=>'Pending','dikemas'=>'Dikemas','siap_kirim'=>'Siap Kirim',
            'dikirim'=>'Dikirim','selesai'=>'Selesai','dibatalkan'=>'Dibatalkan',
        ];

        $fmt = fn($v) => 'Rp ' . number_format((float)$v, 0, ',', '.');

        $filename = 'Laporan_GMV_' . $tahun . '.csv';
        $headers  = [
            'Content-Type'        => 'text/csv; charset=UTF-8',
            'Content-Disposition' => 'attachment; filename="' . $filename . '"',
            'Cache-Control'       => 'max-age=0',
        ];

        $callback = function () use (
            $tahun, $namaBulan, $bulanDipilih,
            $gmvChannel, $gmvBaseCust, $gmvCs, $gmvIndividu,
            $qtyCs, $qtyChannel, $transaksi, $statusLabel, $fmt
        ) {
            $file  = fopen('php://output', 'w');
            fputs($file, "\xEF\xBB\xBF");
            $sep   = fn() => fputcsv($file, []);
            $title = fn($t) => fputcsv($file, [$t]);

            // SECTION 1: GMV PER CHANNEL
            $title('=== GMV PER CHANNEL - LAPORAN ' . $tahun . ' ===');
            foreach ($bulanDipilih as $bulan) {
                $sep();
                $title('GMV - CHANNEL - ' . strtoupper($namaBulan[$bulan] ?? $bulan));
                fputcsv($file, ['NO', 'CHANNEL', 'NOMINAL GMV']);
                foreach ($gmvChannel[$bulan]['rows'] as $i => $row) {
                    fputcsv($file, [$i + 1, $row->channel->nama_channel ?? '-', $fmt($row->total_gmv)]);
                }
                fputcsv($file, ['GRAND TOTAL', '', $fmt($gmvChannel[$bulan]['grand_total'])]);
            }

            // SECTION 2: GMV INDIVIDU RANKING
            $sep(); $sep();
            $title('=== GMV INDIVIDU - RANKING ' . $tahun . ' ===');
            $sep();
            fputcsv($file, ['RANK', 'CS / ADMIN', 'NOMINAL GMV']);
            foreach ($gmvIndividu as $i => $cs) {
                fputcsv($file, [$i + 1, $cs->nama_cs ?? '-', $fmt($cs->total_gmv)]);
            }
            fputcsv($file, ['GRAND TOTAL', '', $fmt($gmvIndividu->sum('total_gmv'))]);

            // SECTION 3: GMV BASE CUSTOMER
            $sep(); $sep();
            $title('=== GMV PER BASE CUSTOMER (TIPE PEMBELIAN) - ' . $tahun . ' ===');
            foreach ($bulanDipilih as $bulan) {
                $sep();
                $title('GMV - BASE CUSTOMER - ' . strtoupper($namaBulan[$bulan] ?? $bulan));
                fputcsv($file, ['NO', 'TIPE PEMBELIAN', 'TOTAL BELI', 'NOMINAL GMV']);
                foreach ($gmvBaseCust[$bulan]['rows'] as $i => $row) {
                    fputcsv($file, [$i + 1, $row->tipe_pembelian ?? '-', (int)$row->total_beli, $fmt($row->total_gmv)]);
                }
                fputcsv($file, ['GRAND TOTAL', '', (int)$gmvBaseCust[$bulan]['grand_beli'], $fmt($gmvBaseCust[$bulan]['grand_total'])]);
            }

            // SECTION 4: QTY CS & CHANNEL
            $sep(); $sep();
            $title('=== QTY PRODUK - CS & CHANNEL - ' . $tahun . ' ===');
            foreach ($bulanDipilih as $bulan) {
                $sep();
                $title('QTY - CS & CHANNEL - ' . strtoupper($namaBulan[$bulan] ?? $bulan));
                fputcsv($file, ['CS/ADMIN', 'CHANNEL', 'KUE KERANJANG', 'COOKIES', 'TOPLES', 'MINI', 'POUCH', 'TOTAL']);
                $gKue=$gCoo=$gTop=$gMini=$gPouch=$gTotal = 0;
                foreach ($qtyCs[$bulan] as $row) {
                    fputcsv($file, [
                        $row->nama_cs ?? '-', $row->channel->nama_channel ?? '-',
                        (int)$row->s_kue, (int)$row->s_cookies, (int)$row->s_toples,
                        (int)$row->s_mini, (int)$row->s_pouch, (int)$row->s_total,
                    ]);
                    $gKue+=$row->s_kue; $gCoo+=$row->s_cookies; $gTop+=$row->s_toples;
                    $gMini+=$row->s_mini; $gPouch+=$row->s_pouch; $gTotal+=$row->s_total;
                }
                fputcsv($file, ['GRAND TOTAL', '', (int)$gKue, (int)$gCoo, (int)$gTop, (int)$gMini, (int)$gPouch, (int)$gTotal]);
            }

            // SECTION 5: QTY PER CHANNEL
            $sep(); $sep();
            $title('=== QTY PRODUK - PER CHANNEL - ' . $tahun . ' ===');
            foreach ($bulanDipilih as $bulan) {
                $sep();
                $title('QTY - CHANNEL - ' . strtoupper($namaBulan[$bulan] ?? $bulan));
                fputcsv($file, ['CHANNEL', 'KUE KERANJANG', 'COOKIES', 'TOPLES', 'MINI', 'POUCH', 'TOTAL']);
                $gKue=$gCoo=$gTop=$gMini=$gPouch=$gTotal = 0;
                foreach ($qtyChannel[$bulan] as $row) {
                    fputcsv($file, [
                        $row->channel->nama_channel ?? '-',
                        (int)$row->s_kue, (int)$row->s_cookies, (int)$row->s_toples,
                        (int)$row->s_mini, (int)$row->s_pouch, (int)$row->s_total,
                    ]);
                    $gKue+=$row->s_kue; $gCoo+=$row->s_cookies; $gTop+=$row->s_toples;
                    $gMini+=$row->s_mini; $gPouch+=$row->s_pouch; $gTotal+=$row->s_total;
                }
                fputcsv($file, ['GRAND TOTAL', (int)$gKue, (int)$gCoo, (int)$gTop, (int)$gMini, (int)$gPouch, (int)$gTotal]);
            }

            // SECTION 6: DETAIL TRANSAKSI
            $sep(); $sep();
            $title('=== DETAIL TRANSAKSI - ' . $tahun . ' ===');
            $sep();
            fputcsv($file, [
                'No','Tanggal','No Invoice','Nama CS','Channel','Nama Pemesan',
                'Status Bayar','Nominal GMV','Biaya Kirim','Grand Total',
                'Jasa Kirim','Status Pesanan','Nama Penerima','No Telp',
                'Kota','Provinsi','No Resi','Catatan',
            ]);
            $totalGmv = 0; $totalGrand = 0;
            foreach ($transaksi as $i => $s) {
                $gmv   = (float)($s->nominal_gmv ?? 0);
                $grand = (float)($s->grand_total ?? 0);
                $totalGmv += $gmv; $totalGrand += $grand;
                fputcsv($file, [
                    $i + 1,
                    \Carbon\Carbon::parse($s->tgl)->format('d/m/Y'),
                    $s->nomor_invoice ?? '-',
                    $s->nama_cs ?? '-',
                    $s->channel->nama_channel ?? '-',
                    $s->nama_pemesan ?? '-',
                    $s->status_bayar ?? '-',
                    $fmt($gmv),
                    $fmt((float)($s->biaya_kirim ?? 0)),
                    $fmt($grand),
                    $s->jasa_kirim ?? '-',
                    $statusLabel[$s->status] ?? ucfirst($s->status ?? '-'),
                    $s->nama_penerima ?? '-',
                    $s->no_telp_penerima ?? '-',
                    $s->kota ?? '-',
                    $s->provinsi ?? '-',
                    $s->nomor_resi ?? '-',
                    $s->catatan ?? '-',
                ]);
            }
            $sep();
            fputcsv($file, ['','','','','','','TOTAL', $fmt($totalGmv), '', $fmt($totalGrand),'','','','','','','','']);
            fputcsv($file, ['','','','','','','JUMLAH TRANSAKSI', $transaksi->count(),'','','','','','','','','','']);

            fclose($file);
        };

        return response()->stream($callback, 200, $headers);
    }

    public function exportExcel(Request $request)
    {
        $tahun = $request->get('tahun', date('Y'));
        $bulanTersedia = Sale::selectRaw('EXTRACT(MONTH FROM tgl)::int as bulan')
            ->whereYear('tgl', $tahun)
            ->groupBy(DB::raw('EXTRACT(MONTH FROM tgl)::int'))->orderBy('bulan')
            ->pluck('bulan')->toArray();

        if (empty($bulanTersedia)) $bulanTersedia = range(1, 12);
        $bulanDipilih = $request->get('bulan', $bulanTersedia);
        if (empty($bulanDipilih)) $bulanDipilih = $bulanTersedia;
        $bulanDipilih = array_map('intval', $bulanDipilih);

        // Baca bulan_filter dari request (sama seperti di index())
        $mapBulanAngka = [
            'Januari' => 1, 'Februari' => 2, 'Maret' => 3, 'April' => 4,
            'Mei' => 5, 'Juni' => 6, 'Juli' => 7, 'Agustus' => 8,
            'September' => 9, 'Oktober' => 10, 'November' => 11, 'Desember' => 12,
        ];
        $bulanFilterRaw = $request->get('bulan_filter');
        if (isset($mapBulanAngka[$bulanFilterRaw])) {
            $bulanFilter = $mapBulanAngka[$bulanFilterRaw];
        } elseif (!empty($bulanFilterRaw)) {
            $bulanFilter = (int) $bulanFilterRaw;
        } else {
            $bulanFilter = !empty($bulanDipilih) ? (int) end($bulanDipilih) : (int) date('m');
        }
        // Pastikan bulanFilter valid, fallback ke bulan terakhir dipilih
        if ($bulanFilter === 0) {
            $bulanFilter = !empty($bulanDipilih) ? (int) end($bulanDipilih) : (int) date('m');
        }

        $namaBulan = $this->namaBulan;

        $statusLabel = [
            'pending' => 'Pending', 'dikemas' => 'Dikemas', 'siap_kirim' => 'Siap Kirim',
            'dikirim' => 'Dikirim', 'selesai' => 'Selesai', 'dibatalkan' => 'Dibatalkan',
        ];

        // ── AMBIL SEMUA DATA ──
        $gmvChannel = []; $gmvBaseCust = []; $gmvCs = [];
        foreach ($bulanDipilih as $bulan) {
            $r1 = Sale::selectRaw('channel_id, SUM(nominal_gmv) as total_gmv')
                ->whereYear('tgl', $tahun)->whereMonth('tgl', $bulan)
                ->where('status', '!=', 'reject')
                ->with('channel')->groupBy('channel_id')->orderByDesc('total_gmv')->get()->values();
            $gmvChannel[$bulan] = ['rows' => $r1, 'grand_total' => $r1->sum('total_gmv')];

            $r2 = Sale::selectRaw("CASE WHEN tipe_pembelian = '' OR tipe_pembelian IS NULL THEN 'BELUM DIISI' ELSE tipe_pembelian END as tipe_pembelian, COUNT(*) as total_beli, SUM(nominal_gmv) as total_gmv")
                ->whereYear('tgl', $tahun)->whereMonth('tgl', $bulan)
                ->where('status', '!=', 'reject')
                ->groupBy(DB::raw("CASE WHEN tipe_pembelian = '' OR tipe_pembelian IS NULL THEN 'BELUM DIISI' ELSE tipe_pembelian END"))
                ->orderByDesc('total_gmv')->get();
            $gmvBaseCust[$bulan] = ['rows' => $r2, 'grand_total' => $r2->sum('total_gmv'), 'grand_beli' => $r2->sum('total_beli')];

            $r3 = Sale::selectRaw('nama_cs, SUM(nominal_gmv) as total_gmv, COUNT(*) as total_trx')
                ->whereYear('tgl', $tahun)->whereMonth('tgl', $bulan)
                ->where('status', '!=', 'reject')->whereNotNull('nama_cs')
                ->groupBy('nama_cs')->orderByDesc('total_gmv')->get();
            $gmvCs[$bulan] = ['rows' => $r3, 'grand_total' => $r3->sum('total_gmv')];
        }

        $gmvIndividu = Sale::selectRaw('nama_cs, SUM(nominal_gmv) as total_gmv')
            ->whereYear('tgl', $tahun)
            ->whereIn(DB::raw('EXTRACT(MONTH FROM tgl)::int'), $bulanDipilih)
            ->where('status', '!=', 'reject')->whereNotNull('nama_cs')
            ->groupBy('nama_cs')->orderByDesc('total_gmv')->get();

        $qtyCs = [];
        foreach ($bulanDipilih as $bulan) {
            $r = Sale::selectRaw('nama_cs, channel_id, SUM(COALESCE(qty_kue_keranjang,0)) as s_kue, SUM(COALESCE(qty_cookies,0)) as s_cookies, SUM(COALESCE(qty_toples,0)) as s_toples, SUM(COALESCE(qty_mini,0)) as s_mini, SUM(COALESCE(qty_pouch,0)) as s_pouch, SUM(COALESCE(qty_toples,0)+COALESCE(qty_mini,0)+COALESCE(qty_pouch,0)) as s_total')
                ->whereYear('tgl', $tahun)->whereMonth('tgl', $bulan)
                ->where('status', '!=', 'reject')->whereNotNull('nama_cs')
                ->with('channel')->groupBy('nama_cs', 'channel_id')->orderBy('nama_cs')->get();
            $qtyCs[$bulan] = $r;
        }

        $qtyChannel = [];
        foreach ($bulanDipilih as $bulan) {
            $r = Sale::selectRaw('channel_id, SUM(COALESCE(qty_kue_keranjang,0)) as s_kue, SUM(COALESCE(qty_cookies,0)) as s_cookies, SUM(COALESCE(qty_toples,0)) as s_toples, SUM(COALESCE(qty_mini,0)) as s_mini, SUM(COALESCE(qty_pouch,0)) as s_pouch, SUM(COALESCE(qty_toples,0)+COALESCE(qty_mini,0)+COALESCE(qty_pouch,0)) as s_total')
                ->whereYear('tgl', $tahun)->whereMonth('tgl', $bulan)
                ->where('status', '!=', 'reject')
                ->with('channel')->groupBy('channel_id')->orderByDesc('s_toples')->get();
            $qtyChannel[$bulan] = $r;
        }

        $transaksi = Sale::with('channel')
            ->whereYear('tgl', $tahun)
            ->whereIn(DB::raw('EXTRACT(MONTH FROM tgl)::int'), $bulanDipilih)
            ->orderBy('tgl')->orderBy('id')->get();

        // ── SPREADSHEET SETUP ──
        $spreadsheet = new Spreadsheet();
        $spreadsheet->getDefaultStyle()->getFont()->setName('Calibri')->setSize(9);

        $navy    = '0F172A';
        $amber   = 'FBBF24';
        $white   = 'FFFFFF';
        $gray1   = 'F8FAFC';
        $gray2   = 'E2E8F0';

        $hdrStyle = function($bg = null) use ($navy, $white) {
            return [
                'font'      => ['bold' => true, 'color' => ['rgb' => $white], 'size' => 9],
                'fill'      => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $bg ?? $navy]],
                'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER, 'wrapText' => true],
                'borders'   => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => '334155']]],
            ];
        };

        $rowStyle = function($i) use ($gray1, $gray2) {
            return [
                'fill'    => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $i % 2 === 0 ? 'FFFFFF' : $gray1]],
                'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => $gray2]]],
                'font'    => ['size' => 9],
            ];
        };

        $totalStyle = function() use ($navy, $amber) {
            return [
                'font'      => ['bold' => true, 'color' => ['rgb' => $amber], 'size' => 9],
                'fill'      => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $navy]],
                'borders'   => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => '334155']]],
                'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
            ];
        };

        $rp = '"Rp "#,##0';

        // ═══════════════════════════════════════════
        // SHEET 1: GMV PER CHANNEL (VERTIKAL)
        // ═══════════════════════════════════════════
        $sh1 = $spreadsheet->getActiveSheet()->setTitle('GMV Per Channel');

        $sh1->setCellValue('A1', 'GMV PER CHANNEL — LAPORAN ' . $tahun);
        $sh1->mergeCells('A1:C1');
        $sh1->getStyle('A1')->applyFromArray([
            'font'      => ['bold' => true, 'size' => 13, 'color' => ['rgb' => $navy]],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
        ]);
        $sh1->getRowDimension(1)->setRowHeight(26);
        $sh1->getColumnDimension('A')->setWidth(5);
        $sh1->getColumnDimension('B')->setWidth(22);
        $sh1->getColumnDimension('C')->setWidth(18);

        $startRow1 = 2;
        foreach ($bulanDipilih as $bulan) {
            // Header bulan
            $sh1->setCellValue('A' . $startRow1, 'GMV — CHANNEL · ' . strtoupper($namaBulan[$bulan] ?? $bulan));
            $sh1->mergeCells('A' . $startRow1 . ':C' . $startRow1);
            $sh1->getStyle('A' . $startRow1 . ':C' . $startRow1)->applyFromArray($hdrStyle($navy));
            $sh1->getRowDimension($startRow1)->setRowHeight(20);

            // Sub header
            $hRow = $startRow1 + 1;
            $sh1->setCellValue('A' . $hRow, 'NO');
            $sh1->setCellValue('B' . $hRow, 'CHANNEL');
            $sh1->setCellValue('C' . $hRow, 'NOMINAL GMV');
            $sh1->getStyle('A' . $hRow . ':C' . $hRow)->applyFromArray($hdrStyle('1E293B'));

            $r = $hRow + 1;
            foreach ($gmvChannel[$bulan]['rows'] as $i => $row) {
                $sh1->setCellValue('A' . $r, $i + 1);
                $sh1->setCellValue('B' . $r, $row->channel->nama_channel ?? '-');
                $sh1->setCellValue('C' . $r, (float)$row->total_gmv);
                $sh1->getStyle('C' . $r)->getNumberFormat()->setFormatCode($rp);
                $sh1->getStyle('C' . $r)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
                $sh1->getStyle('A' . $r . ':C' . $r)->applyFromArray($rowStyle($i));
                $sh1->getStyle('A' . $r)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                $r++;
            }

            // Grand total
            $sh1->setCellValue('A' . $r, 'GRAND TOTAL');
            $sh1->mergeCells('A' . $r . ':B' . $r);
            $sh1->setCellValue('C' . $r, (float)$gmvChannel[$bulan]['grand_total']);
            $sh1->getStyle('A' . $r . ':C' . $r)->applyFromArray($totalStyle());
            $sh1->getStyle('C' . $r)->getNumberFormat()->setFormatCode($rp);
            $sh1->getStyle('C' . $r)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

            $startRow1 = $r + 2;
        }

        // ═══════════════════════════════════════════
        // SHEET 2: GMV INDIVIDU RANKING
        // ═══════════════════════════════════════════
        $sh2 = $spreadsheet->createSheet()->setTitle('GMV Individu');

        $sh2->setCellValue('A1', 'GMV INDIVIDU — RANKING ' . $tahun);
        $sh2->mergeCells('A1:C1');
        $sh2->getStyle('A1')->applyFromArray([
            'font'      => ['bold' => true, 'size' => 13, 'color' => ['rgb' => $navy]],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
        ]);
        $sh2->getRowDimension(1)->setRowHeight(26);

        $sh2->setCellValue('A2', 'GMV — INDIVIDU');
        $sh2->mergeCells('A2:C2');
        $sh2->getStyle('A2:C2')->applyFromArray($hdrStyle($navy));
        $sh2->getRowDimension(2)->setRowHeight(20);

        $sh2->setCellValue('A3', 'RANK');
        $sh2->setCellValue('B3', 'CS / ADMIN');
        $sh2->setCellValue('C3', 'NOMINAL GMV');
        $sh2->getStyle('A3:C3')->applyFromArray($hdrStyle('1E293B'));
        $sh2->getColumnDimension('A')->setWidth(8);
        $sh2->getColumnDimension('B')->setWidth(22);
        $sh2->getColumnDimension('C')->setWidth(18);

        foreach ($gmvIndividu as $i => $cs) {
            $r = $i + 4;
            $sh2->setCellValue('A' . $r, $i + 1);
            $sh2->setCellValue('B' . $r, $cs->nama_cs ?? '-');
            $sh2->setCellValue('C' . $r, (float)$cs->total_gmv);
            $sh2->getStyle('C' . $r)->getNumberFormat()->setFormatCode($rp);
            $sh2->getStyle('C' . $r)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
            $sh2->getStyle('A' . $r . ':C' . $r)->applyFromArray($rowStyle($i));
            $sh2->getStyle('A' . $r)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            if ($i < 3) $sh2->getStyle('B' . $r)->getFont()->setBold(true);
        }

        $tr = $gmvIndividu->count() + 4;
        $sh2->setCellValue('A' . $tr, 'GRAND TOTAL');
        $sh2->mergeCells('A' . $tr . ':B' . $tr);
        $sh2->setCellValue('C' . $tr, (float)$gmvIndividu->sum('total_gmv'));
        $sh2->getStyle('A' . $tr . ':C' . $tr)->applyFromArray($totalStyle());
        $sh2->getStyle('C' . $tr)->getNumberFormat()->setFormatCode($rp);
        $sh2->getStyle('C' . $tr)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

        // ═══════════════════════════════════════════
        // SHEET 3: GMV BASE CUSTOMER (VERTIKAL)
        // ═══════════════════════════════════════════
        $sh3 = $spreadsheet->createSheet()->setTitle('GMV Base Customer');

        $sh3->setCellValue('A1', 'GMV PER BASE CUSTOMER (TIPE PEMBELIAN) — ' . $tahun);
        $sh3->mergeCells('A1:D1');
        $sh3->getStyle('A1')->applyFromArray([
            'font'      => ['bold' => true, 'size' => 13, 'color' => ['rgb' => $navy]],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
        ]);
        $sh3->getRowDimension(1)->setRowHeight(26);
        $sh3->getColumnDimension('A')->setWidth(5);
        $sh3->getColumnDimension('B')->setWidth(22);
        $sh3->getColumnDimension('C')->setWidth(14);
        $sh3->getColumnDimension('D')->setWidth(18);

        $startRow3 = 2;
        foreach ($bulanDipilih as $bulan) {
            $sh3->setCellValue('A' . $startRow3, 'GMV — BASE CUSTOMER · ' . strtoupper($namaBulan[$bulan] ?? $bulan));
            $sh3->mergeCells('A' . $startRow3 . ':D' . $startRow3);
            $sh3->getStyle('A' . $startRow3 . ':D' . $startRow3)->applyFromArray($hdrStyle($navy));
            $sh3->getRowDimension($startRow3)->setRowHeight(20);

            $hRow = $startRow3 + 1;
            $sh3->setCellValue('A' . $hRow, 'NO');
            $sh3->setCellValue('B' . $hRow, 'TIPE PEMBELIAN');
            $sh3->setCellValue('C' . $hRow, 'TOTAL BELI');
            $sh3->setCellValue('D' . $hRow, 'NOMINAL GMV');
            $sh3->getStyle('A' . $hRow . ':D' . $hRow)->applyFromArray($hdrStyle('1E293B'));

            $r = $hRow + 1;
            foreach ($gmvBaseCust[$bulan]['rows'] as $i => $row) {
                $sh3->setCellValue('A' . $r, $i + 1);
                $sh3->setCellValue('B' . $r, $row->tipe_pembelian ?? '-');
                $sh3->setCellValue('C' . $r, (int)$row->total_beli);
                $sh3->setCellValue('D' . $r, (float)$row->total_gmv);
                $sh3->getStyle('D' . $r)->getNumberFormat()->setFormatCode($rp);
                $sh3->getStyle('C' . $r . ':D' . $r)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
                $sh3->getStyle('A' . $r . ':D' . $r)->applyFromArray($rowStyle($i));
                $sh3->getStyle('A' . $r)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                $r++;
            }

            $sh3->setCellValue('A' . $r, 'GRAND TOTAL');
            $sh3->mergeCells('A' . $r . ':B' . $r);
            $sh3->setCellValue('C' . $r, (int)$gmvBaseCust[$bulan]['grand_beli']);
            $sh3->setCellValue('D' . $r, (float)$gmvBaseCust[$bulan]['grand_total']);
            $sh3->getStyle('A' . $r . ':D' . $r)->applyFromArray($totalStyle());
            $sh3->getStyle('C' . $r . ':D' . $r)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
            $sh3->getStyle('D' . $r)->getNumberFormat()->setFormatCode($rp);

            $startRow3 = $r + 2;
        }

        // ═══════════════════════════════════════════
        // SHEET 4: QTY CS & CHANNEL
        // ═══════════════════════════════════════════
        $sh4 = $spreadsheet->createSheet()->setTitle('Qty CS & Channel');

        $sh4->setCellValue('A1', 'TOPLES, MINI, POUCH — CS & CHANNEL — ' . $tahun);
        $sh4->mergeCells('A1:I1');
        $sh4->getStyle('A1')->applyFromArray([
            'font'      => ['bold' => true, 'size' => 13, 'color' => ['rgb' => $navy]],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
        ]);
        $sh4->getRowDimension(1)->setRowHeight(26);

        $startRow4 = 2;
        foreach ($bulanDipilih as $bulan) {
            $title = 'TOPLES, MINI, POUCH — CS & CHANNEL · ' . strtoupper($namaBulan[$bulan] ?? $bulan);
            $sh4->setCellValue('A' . $startRow4, $title);
            $sh4->mergeCells('A' . $startRow4 . ':I' . $startRow4);
            $sh4->getStyle('A' . $startRow4 . ':I' . $startRow4)->applyFromArray($hdrStyle($navy));
            $sh4->getRowDimension($startRow4)->setRowHeight(20);

            $hRow = $startRow4 + 1;
            foreach (['A'=>'CS/ADMIN','B'=>'CHANNEL','C'=>'KUE KERANJANG','D'=>'COOKIES','E'=>'TOPLES','F'=>'MINI','G'=>'POUCH','H'=>'TOTAL'] as $c => $h) {
                $sh4->setCellValue($c . $hRow, $h);
            }
            $sh4->getStyle('A' . $hRow . ':H' . $hRow)->applyFromArray($hdrStyle('1E293B'));

            $r = $hRow + 1;
            $gKue=$gCoo=$gTop=$gMini=$gPouch=$gTotal = 0;
            foreach ($qtyCs[$bulan] as $i => $row) {
                $sh4->setCellValue('A' . $r, $row->nama_cs ?? '-');
                $sh4->setCellValue('B' . $r, $row->channel->nama_channel ?? '-');
                $sh4->setCellValue('C' . $r, (int)$row->s_kue);
                $sh4->setCellValue('D' . $r, (int)$row->s_cookies);
                $sh4->setCellValue('E' . $r, (int)$row->s_toples);
                $sh4->setCellValue('F' . $r, (int)$row->s_mini);
                $sh4->setCellValue('G' . $r, (int)$row->s_pouch);
                $sh4->setCellValue('H' . $r, (int)$row->s_total);
                $sh4->getStyle('C' . $r . ':H' . $r)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                $sh4->getStyle('A' . $r . ':H' . $r)->applyFromArray($rowStyle($i));
                $gKue+=$row->s_kue; $gCoo+=$row->s_cookies; $gTop+=$row->s_toples;
                $gMini+=$row->s_mini; $gPouch+=$row->s_pouch; $gTotal+=$row->s_total;
                $r++;
            }

            $sh4->setCellValue('A' . $r, 'GRAND TOTAL');
            $sh4->mergeCells('A' . $r . ':B' . $r);
            foreach (['C'=>$gKue,'D'=>$gCoo,'E'=>$gTop,'F'=>$gMini,'G'=>$gPouch,'H'=>$gTotal] as $c => $v) {
                $sh4->setCellValue($c . $r, (int)$v);
            }
            $sh4->getStyle('A' . $r . ':H' . $r)->applyFromArray($totalStyle());
            $sh4->getStyle('C' . $r . ':H' . $r)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

            $startRow4 = $r + 2;
        }

        foreach (['A'=>16,'B'=>16,'C'=>14,'D'=>10,'E'=>10,'F'=>8,'G'=>8,'H'=>10] as $c => $w) {
            $sh4->getColumnDimension($c)->setWidth($w);
        }

        // ═══════════════════════════════════════════
        // SHEET 5: QTY PER CHANNEL
        // ═══════════════════════════════════════════
        $sh5 = $spreadsheet->createSheet()->setTitle('Qty Per Channel');

        $sh5->setCellValue('A1', 'TOPLES, MINI, POUCH — PER CHANNEL — ' . $tahun);
        $sh5->mergeCells('A1:H1');
        $sh5->getStyle('A1')->applyFromArray([
            'font'      => ['bold' => true, 'size' => 13, 'color' => ['rgb' => $navy]],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
        ]);
        $sh5->getRowDimension(1)->setRowHeight(26);

        $startRow5 = 2;
        foreach ($bulanDipilih as $bulan) {
            $title = 'TOPLES, MINI, POUCH — CHANNEL · ' . strtoupper($namaBulan[$bulan] ?? $bulan);
            $sh5->setCellValue('A' . $startRow5, $title);
            $sh5->mergeCells('A' . $startRow5 . ':G' . $startRow5);
            $sh5->getStyle('A' . $startRow5 . ':G' . $startRow5)->applyFromArray($hdrStyle($navy));
            $sh5->getRowDimension($startRow5)->setRowHeight(20);

            $hRow = $startRow5 + 1;
            foreach (['A'=>'CHANNEL','B'=>'KUE KERANJANG','C'=>'COOKIES','D'=>'TOPLES','E'=>'MINI','F'=>'POUCH','G'=>'TOTAL'] as $c => $h) {
                $sh5->setCellValue($c . $hRow, $h);
            }
            $sh5->getStyle('A' . $hRow . ':G' . $hRow)->applyFromArray($hdrStyle('1E293B'));

            $r = $hRow + 1;
            $gKue=$gCoo=$gTop=$gMini=$gPouch=$gTotal = 0;
            foreach ($qtyChannel[$bulan] as $i => $row) {
                $sh5->setCellValue('A' . $r, $row->channel->nama_channel ?? '-');
                $sh5->setCellValue('B' . $r, (int)$row->s_kue);
                $sh5->setCellValue('C' . $r, (int)$row->s_cookies);
                $sh5->setCellValue('D' . $r, (int)$row->s_toples);
                $sh5->setCellValue('E' . $r, (int)$row->s_mini);
                $sh5->setCellValue('F' . $r, (int)$row->s_pouch);
                $sh5->setCellValue('G' . $r, (int)$row->s_total);
                $sh5->getStyle('B' . $r . ':G' . $r)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                $sh5->getStyle('A' . $r . ':G' . $r)->applyFromArray($rowStyle($i));
                $gKue+=$row->s_kue; $gCoo+=$row->s_cookies; $gTop+=$row->s_toples;
                $gMini+=$row->s_mini; $gPouch+=$row->s_pouch; $gTotal+=$row->s_total;
                $r++;
            }

            $sh5->setCellValue('A' . $r, 'GRAND TOTAL');
            foreach (['B'=>$gKue,'C'=>$gCoo,'D'=>$gTop,'E'=>$gMini,'F'=>$gPouch,'G'=>$gTotal] as $c => $v) {
                $sh5->setCellValue($c . $r, (int)$v);
            }
            $sh5->getStyle('A' . $r . ':G' . $r)->applyFromArray($totalStyle());
            $sh5->getStyle('B' . $r . ':G' . $r)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

            $startRow5 = $r + 2;
        }

        foreach (['A'=>18,'B'=>14,'C'=>10,'D'=>10,'E'=>8,'F'=>8,'G'=>10] as $c => $w) {
            $sh5->getColumnDimension($c)->setWidth($w);
        }

        // ═══════════════════════════════════════════
        // SHEET 6: DETAIL TRANSAKSI
        // ═══════════════════════════════════════════
        $sh6 = $spreadsheet->createSheet()->setTitle('Detail Transaksi');

        $headers6 = ['No','Tanggal','No Invoice','Nama CS','Channel','Nama Pemesan',
            'Status Bayar','Nominal GMV','Biaya Kirim','Grand Total',
            'Jasa Kirim','Status','Nama Penerima','No Telp',
            'Kota','Provinsi','No Resi','Catatan'];

        $sh6->setCellValue('A1', 'DETAIL TRANSAKSI — ' . $tahun);
        $sh6->mergeCells('A1:R1');
        $sh6->getStyle('A1')->applyFromArray([
            'font'      => ['bold' => true, 'size' => 13, 'color' => ['rgb' => $navy]],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
        ]);
        $sh6->getRowDimension(1)->setRowHeight(26);

        foreach ($headers6 as $ci => $h) {
            $cell = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($ci + 1) . '2';
            $sh6->setCellValue($cell, $h);
        }
        $sh6->getStyle('A2:R2')->applyFromArray($hdrStyle($navy));
        $sh6->getRowDimension(2)->setRowHeight(20);

        $totalGmv = 0; $totalGrand = 0;
        foreach ($transaksi as $i => $s) {
            $r = $i + 3;
            $gmv   = (float)($s->nominal_gmv ?? 0);
            $grand = (float)($s->grand_total ?? 0);
            $totalGmv += $gmv; $totalGrand += $grand;

            $rowData = [
                'A' => $i + 1,
                'B' => \Carbon\Carbon::parse($s->tgl)->format('d/m/Y'),
                'C' => $s->nomor_invoice ?? '-',
                'D' => $s->nama_cs ?? '-',
                'E' => $s->channel->nama_channel ?? '-',
                'F' => $s->nama_pemesan ?? '-',
                'G' => $s->status_bayar ?? '-',
                'H' => $gmv, 'I' => (float)($s->biaya_kirim ?? 0), 'J' => $grand,
                'K' => $s->jasa_kirim ?? '-',
                'L' => $statusLabel[$s->status] ?? ucfirst($s->status ?? '-'),
                'M' => $s->nama_penerima ?? '-',
                'O' => $s->kota ?? '-', 'P' => $s->provinsi ?? '-',
                'R' => $s->catatan ?? '-',
            ];
            foreach ($rowData as $c => $val) $sh6->setCellValue($c . $r, $val);
            $sh6->setCellValueExplicit('N' . $r, (string)($s->no_telp_penerima ?? '-'), \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
            $sh6->setCellValueExplicit('Q' . $r, (string)($s->nomor_resi ?? '-'), \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);

            foreach (['H','I','J'] as $c) $sh6->getStyle($c . $r)->getNumberFormat()->setFormatCode($rp);
            $sh6->getStyle('A' . $r . ':R' . $r)->applyFromArray($rowStyle($i));
            $sh6->getStyle('A' . $r)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        }

        $sumR = $transaksi->count() + 4;
        $sh6->setCellValue('G' . $sumR, 'TOTAL'); $sh6->setCellValue('H' . $sumR, $totalGmv); $sh6->setCellValue('J' . $sumR, $totalGrand);
        $sh6->setCellValue('G' . ($sumR+1), 'JUMLAH TRANSAKSI'); $sh6->setCellValue('H' . ($sumR+1), $transaksi->count());
        $sh6->getStyle('G' . $sumR . ':R' . $sumR)->applyFromArray($totalStyle());
        foreach (['H','J'] as $c) $sh6->getStyle($c . $sumR)->getNumberFormat()->setFormatCode($rp);

        foreach (range('A','R') as $c) $sh6->getColumnDimension($c)->setAutoSize(true);
        $sh6->getColumnDimension('N')->setAutoSize(false)->setWidth(18);
        $sh6->getColumnDimension('Q')->setAutoSize(false)->setWidth(22);

        // ── OUTPUT DIRECT STREAM ──
        $spreadsheet->setActiveSheetIndex(0);
        $filename = 'Laporan_GMV_' . $tahun . '.xlsx';
        $writer = new Xlsx($spreadsheet);

        $response = response()->streamDownload(function () use ($writer) {
            if (ob_get_level()) ob_end_clean();
            $writer->save('php://output');
            flush();
        }, $filename, [
            'Content-Type'        => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'Content-Disposition' => 'attachment; filename="' . $filename . '"',
            'Cache-Control'       => 'max-age=0',
            'Pragma'              => 'no-cache',
            'Expires'             => '0',
        ]);

        $response->send();
        exit;
    }

    public function detail(Request $request)
    {
        $channelId   = $request->get('channel_id');
        $tahun       = $request->get('tahun', date('Y'));
        $bulan       = (int) $request->get('bulan', date('m'));
        $namaBulan   = $this->namaBulan;

        $rows = Sale::where('channel_id', $channelId)
            ->whereYear('tgl', $tahun)
            ->whereMonth('tgl', $bulan)
            ->orderBy('tgl', 'asc')
            ->get([
                'tgl', 'nama_cs',
                'qty_kue_keranjang', 'qty_cookies',
                'qty_toples', 'qty_mini', 'qty_pouch', 'qty_reject',
                'nominal_gmv', 'status'
            ]);

        $subtotal = [
            'qty_kue_keranjang' => $rows->sum('qty_kue_keranjang'),
            'qty_cookies'       => $rows->sum('qty_cookies'),
            'qty_toples'        => $rows->sum('qty_toples'),
            'qty_mini'          => $rows->sum('qty_mini'),
            'qty_pouch'         => $rows->sum('qty_pouch'),
            'qty_reject'        => $rows->sum('qty_reject'),
            'nominal_gmv'       => $rows->sum('nominal_gmv'),
        ];

        return response()->json([
            'rows'        => $rows,
            'subtotal'    => $subtotal,
            'total_rows'  => $rows->count(),
            'bulan_label' => $namaBulan[$bulan] ?? '',
            'tahun'       => $tahun,
        ]);
    }

    public function detailDaily(Request $request)
    {
        $tahun       = $request->get('tahun', date('Y'));
        $bulan       = (int) $request->get('bulan', date('m'));
        $namaBulan   = $this->namaBulan;

        $hariTersedia = Sale::selectRaw('EXTRACT(DAY FROM tgl)::int as hari')
            ->whereYear('tgl', $tahun)
            ->whereMonth('tgl', $bulan)
            ->groupBy(DB::raw('EXTRACT(DAY FROM tgl)::int'))
            ->orderBy('hari')
            ->pluck('hari')
            ->toArray();

        $hariDipilih = $request->get('hari', $hariTersedia);
        if (empty($hariDipilih)) $hariDipilih = $hariTersedia;
        $hariDipilih = array_map('intval', $hariDipilih);

        $dataHarian = [];
        foreach ($hariDipilih as $hari) {
            $tglStr = sprintf('%04d-%02d-%02d', $tahun, $bulan, $hari);

            // 1. GMV per Channel Daily
            $gmvChannel = Sale::selectRaw('channel_id, SUM(nominal_gmv) as total_gmv')
                ->where('tgl', $tglStr)
                ->where('status', '!=', 'reject')
                ->with('channel')->groupBy('channel_id')->orderByDesc('total_gmv')->get();

            // 2. GMV per CS Daily
            $gmvIndividu = Sale::selectRaw('nama_cs, SUM(nominal_gmv) as total_gmv')
                ->where('tgl', $tglStr)
                ->where('status', '!=', 'reject')->whereNotNull('nama_cs')
                ->groupBy('nama_cs')->orderByDesc('total_gmv')->get();

            // 3. Qty per CS & Channel Daily
            $qtyCs = Sale::selectRaw('
                    nama_cs, channel_id,
                    SUM(COALESCE(qty_kue_keranjang, 0)) as s_kue, 
                    SUM(COALESCE(qty_cookies, 0)) as s_cookies,
                    SUM(COALESCE(qty_toples, 0)) as s_toples, 
                    SUM(COALESCE(qty_mini, 0)) as s_mini,
                    SUM(COALESCE(qty_pouch, 0)) as s_pouch,
                    SUM(COALESCE(qty_toples, 0) + COALESCE(qty_mini, 0) + COALESCE(qty_pouch, 0)) as s_total
                ')
                ->where('tgl', $tglStr)
                ->where('status', '!=', 'reject')
                ->whereNotNull('nama_cs')
                ->with('channel')->groupBy('nama_cs', 'channel_id')->orderBy('nama_cs')->get();

            // 4. Qty per Channel Daily
            $qtyChannel = Sale::selectRaw('
                    channel_id,
                    SUM(COALESCE(qty_kue_keranjang, 0)) as s_kue, 
                    SUM(COALESCE(qty_cookies, 0)) as s_cookies,
                    SUM(COALESCE(qty_toples, 0)) as s_toples, 
                    SUM(COALESCE(qty_mini, 0)) as s_mini,
                    SUM(COALESCE(qty_pouch, 0)) as s_pouch,
                    SUM(COALESCE(qty_toples, 0) + COALESCE(qty_mini, 0) + COALESCE(qty_pouch, 0)) as s_total
                ')
                ->where('tgl', $tglStr)
                ->where('status', '!=', 'reject')
                ->with('channel')->groupBy('channel_id')->orderByDesc('s_toples')->get();

            // 5. BOT Type Category Daily
            $botType = Sale::selectRaw('nama_cs, tipe_pembelian, SUM(nominal_gmv) as total_gmv, COUNT(*) as total_trx')
                ->where('tgl', $tglStr)
                ->where('status', '!=', 'reject')
                ->whereHas('channel', fn($q) => $q->where('nama_channel', 'like', '%Qontak%')->orWhere('nama_channel', 'like', '%BOT%'))
                ->whereNotNull('nama_cs')
                ->groupBy('nama_cs', 'tipe_pembelian')->orderBy('nama_cs')->get();

            // 6. Reseller Type Category Daily
            $resellerType = Sale::selectRaw('nama_cs, tipe_pembelian, SUM(nominal_gmv) as total_gmv, COUNT(*) as total_trx')
                ->where('tgl', $tglStr)
                ->where('status', '!=', 'reject')
                ->whereHas('channel', fn($q) => $q->where('nama_channel', 'like', '%Reseller%'))
                ->whereNotNull('nama_cs')
                ->groupBy('nama_cs', 'tipe_pembelian')->orderBy('nama_cs')->get();

            $dataHarian[$hari] = compact(
                'gmvChannel', 'gmvIndividu',
                'qtyCs', 'qtyChannel',
                'botType', 'resellerType'
            );
        }

        $bulanTersedia = Sale::selectRaw('EXTRACT(MONTH FROM tgl)::int as bulan')
            ->whereYear('tgl', $tahun)
            ->groupBy(DB::raw('EXTRACT(MONTH FROM tgl)::int'))
            ->orderBy('bulan')->pluck('bulan')->toArray();

        if (empty($bulanTersedia)) $bulanTersedia = range(1, 12);

        return view('laporan_daily', compact(
            'dataHarian', 'hariDipilih', 'hariTersedia',
            'tahun', 'bulan', 'bulanTersedia', 'namaBulan'
        ));
    }
}