<?php

namespace App\Controllers;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\{Fill, Alignment, Border};

class LaporanPenjualan extends BaseController
{
    // Filter channel dari UI → nilai channel di union SQL
    // Internal source: jenis_penjualan adalah ENUM('Ecer','Reseller','Maklon') dari tb_penjualan
    // TikTok source  : di-hardcode 'marketplace' (lowercase) di unionSQL()
    private const CHANNEL_MAP = [
        'pos'         => 'Ecer',
        'reseller'    => 'Reseller',
        'maklon'      => 'Maklon',
        'marketplace' => 'marketplace',
    ];

    // ─────────────────────────────────────────────
    //  UNION: internal (tb_penjualan) + TikTok
    // ─────────────────────────────────────────────
    private function unionSQL(): string
    {
        return "
            SELECT
                CAST(pj.id_penjualan AS CHAR)                                                        AS source_id,
                CONVERT(pj.kode_penjualan   USING utf8mb4) COLLATE utf8mb4_unicode_ci               AS no_transaksi,
                CONVERT(pj.jenis_penjualan  USING utf8mb4) COLLATE utf8mb4_unicode_ci               AS channel,
                CONVERT('tunai'             USING utf8mb4) COLLATE utf8mb4_unicode_ci               AS metode_bayar,
                pj.total_penjualan                                                                   AS subtotal,
                0                                                                                    AS diskon,
                pj.total_penjualan                                                                   AS total,
                pj.total_penjualan                                                                   AS bayar,
                0                                                                                    AS kembalian,
                CONVERT(COALESCE(pj.catatan_penjualan, '') USING utf8mb4) COLLATE utf8mb4_unicode_ci AS catatan,
                pj.created_at,
                CONVERT('internal' USING utf8mb4) COLLATE utf8mb4_unicode_ci                        AS source
            FROM tb_penjualan pj

            UNION ALL

            SELECT
                CONVERT(tp.order_id                              USING utf8mb4) COLLATE utf8mb4_unicode_ci AS source_id,
                CONVERT(tp.order_id                              USING utf8mb4) COLLATE utf8mb4_unicode_ci AS no_transaksi,
                CONVERT('marketplace'                            USING utf8mb4) COLLATE utf8mb4_unicode_ci AS channel,
                CONVERT(COALESCE(tp.payment_method, 'transfer')  USING utf8mb4) COLLATE utf8mb4_unicode_ci AS metode_bayar,
                tp.order_amount                                                                             AS subtotal,
                (tp.order_amount - tp.total_amount)                                                        AS diskon,
                tp.total_amount                                                                             AS total,
                tp.total_amount                                                                             AS bayar,
                0                                                                                          AS kembalian,
                CONVERT(COALESCE(tp.buyer_message, '')           USING utf8mb4) COLLATE utf8mb4_unicode_ci AS catatan,
                tp.create_time                                                                             AS created_at,
                CONVERT('tiktok'                                 USING utf8mb4) COLLATE utf8mb4_unicode_ci AS source
            FROM transaksi_pesanan tp
            WHERE tp.status_pesanan NOT IN ('Dibatalkan','Retur')
        ";
    }

    // ─────────────────────────────────────────────
    //  WHERE builder
    // ─────────────────────────────────────────────
    private function buildWhere(array $filter): array
    {
        $where  = "WHERE DATE(u.created_at) BETWEEN ? AND ?";
        $params = [$filter['tgl_mulai'], $filter['tgl_akhir']];

        if (!empty($filter['channel'])) {
            $dbChannel = self::CHANNEL_MAP[$filter['channel']] ?? $filter['channel'];
            $where    .= " AND u.channel = ?";
            $params[]  = $dbChannel;
        }
        if (!empty($filter['metode'])) {
            $where   .= " AND u.metode_bayar = ?";
            $params[] = $filter['metode'];
        }
        if (!empty($filter['search'])) {
            $where   .= " AND (u.no_transaksi LIKE ? OR u.catatan LIKE ?)";
            $params[] = "%{$filter['search']}%";
            $params[] = "%{$filter['search']}%";
        }

        return [$where, $params];
    }

    // ─────────────────────────────────────────────
    //  Metode bayar dinamis dari DB
    // ─────────────────────────────────────────────
    public function metodeList(): \CodeIgniter\HTTP\ResponseInterface
    {
        try {
            $db = \Config\Database::connect();

            $rows = $db->query("
                SELECT DISTINCT metode_bayar
                FROM ({$this->unionSQL()}) u
                WHERE metode_bayar IS NOT NULL
                  AND TRIM(metode_bayar) != ''
                ORDER BY metode_bayar ASC
            ")->getResultArray();

            return $this->response->setJSON([
                'success' => true,
                'data'    => array_column($rows, 'metode_bayar'),
            ]);
        } catch (\Throwable $e) {
            log_message('error', '[LaporanPenjualan::metodeList] ' . $e->getMessage());
            return $this->response->setStatusCode(500)->setJSON([
                'success' => false,
                'error'   => $e->getMessage(),
            ]);
        }
    }

    // ─────────────────────────────────────────────
    //  index() — Dashboard halaman utama
    //  FIX: todayStat sekarang pakai unionSQL()
    //  agar Transfer, QRIS, Tunai benar dari semua source
    // ─────────────────────────────────────────────
    public function index(): string
    {
        $db    = \Config\Database::connect();
        $union = $this->unionSQL();
        $today = date('Y-m-d');

        // ── Stat hari ini (ALL sources via union) ──────────────────────────
        $todayStat = $db->query("
            SELECT
                COUNT(*)                                                                          AS total_transaksi,
                COALESCE(SUM(total), 0)                                                           AS total_omzet,
                COALESCE(SUM(CASE WHEN LOWER(metode_bayar) = 'tunai'           THEN total ELSE 0 END), 0) AS kas_tunai,
                COALESCE(SUM(CASE WHEN LOWER(metode_bayar) IN ('transfer','transfer bank','bank transfer')
                                                                               THEN total ELSE 0 END), 0) AS transfer,
                COALESCE(SUM(CASE WHEN LOWER(metode_bayar) = 'qris'            THEN total ELSE 0 END), 0) AS qris,
                COALESCE(SUM(CASE WHEN LOWER(metode_bayar) = 'cod'             THEN total ELSE 0 END), 0) AS cod,
                COALESCE(SUM(CASE WHEN source = 'internal' THEN total ELSE 0 END), 0)            AS omzet_internal,
                COALESCE(SUM(CASE WHEN source = 'tiktok'   THEN total ELSE 0 END), 0)            AS omzet_tiktok
            FROM ({$union}) u
            WHERE DATE(u.created_at) = ?
        ", [$today])->getRowArray();

        // ── Breakdown channel hari ini ──────────────────────────────────────
        $channelStat = $db->query("
            SELECT
                channel,
                COUNT(*)              AS total_trx,
                COALESCE(SUM(total),0) AS total_omzet
            FROM ({$union}) u
            WHERE DATE(u.created_at) = ?
            GROUP BY channel
        ", [$today])->getResultArray();

        // ── Top produk bulan ini (internal + TikTok, qty dikurangi retur) ──
        $topProduk = $db->query("
            SELECT nama_produk, SUM(total_qty) AS total_qty, SUM(total_omzet) AS total_omzet
            FROM (
                SELECT
                    p.nama_produk,
                    SUM(i.qty)      AS total_qty,
                    SUM(i.subtotal) AS total_omzet
                FROM tb_penjualan_item i
                JOIN tb_produk    p  ON p.id_produk     = i.id_produk
                JOIN tb_penjualan pj ON pj.id_penjualan = i.id_penjualan
                WHERE DATE_FORMAT(pj.created_at, '%Y-%m') = ?
                GROUP BY i.id_produk, p.nama_produk

                UNION ALL

                SELECT
                    COALESCE(
                        NULLIF(TRIM(dp.nama_produk_raw), ''),
                        dp.seller_sku
                    )                                                               AS nama_produk,
                    -- quantity dikurangi retur agar tidak double-count
                    SUM(dp.quantity - COALESCE(dp.sku_quantity_of_return, 0))       AS total_qty,
                    SUM(dp.sku_subtotal_after_discount)                             AS total_omzet
                FROM detail_pesanan dp
                JOIN transaksi_pesanan tp ON tp.order_id = dp.order_id
                WHERE DATE_FORMAT(tp.create_time, '%Y-%m') = ?
                  AND tp.status_pesanan NOT IN ('Dibatalkan','Retur')
                GROUP BY COALESCE(NULLIF(TRIM(dp.nama_produk_raw), ''), dp.seller_sku)
            ) x
            GROUP BY nama_produk
            ORDER BY total_qty DESC
            LIMIT 5
        ", [date('Y-m'), date('Y-m')])->getResultArray();

        // ── Omzet 7 hari terakhir (untuk sparkline di view) ───────────────
        $week7 = $db->query("
            SELECT
                DATE(u.created_at)         AS tgl,
                COALESCE(SUM(u.total), 0)  AS omzet,
                COUNT(*)                   AS trx
            FROM ({$union}) u
            WHERE DATE(u.created_at) BETWEEN DATE_SUB(?, INTERVAL 6 DAY) AND ?
            GROUP BY DATE(u.created_at)
            ORDER BY tgl ASC
        ", [$today, $today])->getResultArray();

        return view('laporan_penjualan/index', [
            'title'       => 'Laporan Penjualan',
            'todayStat'   => $todayStat   ?? [],
            'channelStat' => $channelStat ?? [],
            'topProduk'   => $topProduk   ?? [],
            'week7'       => $week7       ?? [],
        ]);
    }

    // ─────────────────────────────────────────────
    //  data() — AJAX endpoint untuk tabel + chart
    // ─────────────────────────────────────────────
    public function data(): \CodeIgniter\HTTP\ResponseInterface
    {
        $this->response->setContentType('application/json');

        try {
            $db = \Config\Database::connect();

            $filter = [
                'tgl_mulai' => $this->request->getGet('tgl_mulai') ?: date('Y-m-01'),
                'tgl_akhir' => $this->request->getGet('tgl_akhir') ?: date('Y-m-d'),
                'channel'   => trim($this->request->getGet('channel') ?? ''),
                'metode'    => trim($this->request->getGet('metode')  ?? ''),
                'search'    => trim($this->request->getGet('search')  ?? ''),
            ];

            $union = $this->unionSQL();

            // ── Rows utama ──────────────────────────────────────────────────
            [$where1, $params1] = $this->buildWhere($filter);
            $rows = $db->query("
                SELECT
                    u.source_id,
                    u.no_transaksi,
                    u.channel,
                    u.metode_bayar,
                    u.subtotal,
                    u.diskon,
                    u.total,
                    u.bayar,
                    u.kembalian,
                    u.catatan,
                    u.created_at,
                    u.source,
                    CASE
                        WHEN u.source = 'internal' THEN (
                            SELECT COUNT(*) FROM tb_penjualan_item
                            WHERE id_penjualan = CAST(u.source_id AS UNSIGNED)
                        )
                        ELSE (
                            SELECT COUNT(*) FROM detail_pesanan
                            WHERE order_id = u.source_id
                        )
                    END AS jumlah_item,
                    CASE
                        WHEN u.source = 'internal' THEN (
                            SELECT COALESCE(SUM(qty), 0) FROM tb_penjualan_item
                            WHERE id_penjualan = CAST(u.source_id AS UNSIGNED)
                        )
                        ELSE (
                            SELECT COALESCE(SUM(quantity), 0) FROM detail_pesanan
                            WHERE order_id = u.source_id
                        )
                    END AS total_qty
                FROM ({$union}) u
                {$where1}
                ORDER BY u.created_at DESC
            ", $params1)->getResultArray();

            // ── Summary angka ────────────────────────────────────────────────
            [$where2, $params2] = $this->buildWhere($filter);
            $summary = $db->query("
                SELECT
                    COUNT(*)                                                                                AS total_transaksi,
                    COALESCE(SUM(total),  0)                                                               AS total_omzet,
                    COALESCE(SUM(diskon), 0)                                                               AS total_diskon,
                    COALESCE(SUM(CASE WHEN LOWER(metode_bayar) = 'tunai'              THEN total ELSE 0 END), 0) AS kas_tunai,
                    COALESCE(SUM(CASE WHEN LOWER(metode_bayar) IN ('transfer','transfer bank','bank transfer')
                                                                                      THEN total ELSE 0 END), 0) AS transfer,
                    COALESCE(SUM(CASE WHEN LOWER(metode_bayar) = 'qris'               THEN total ELSE 0 END), 0) AS qris,
                    COALESCE(SUM(CASE WHEN LOWER(metode_bayar) = 'cod'                THEN total ELSE 0 END), 0) AS cod,
                    COALESCE(SUM(CASE WHEN source = 'internal' THEN total ELSE 0 END), 0)                  AS omzet_internal,
                    COALESCE(SUM(CASE WHEN source = 'tiktok'   THEN total ELSE 0 END), 0)                  AS omzet_tiktok
                FROM ({$union}) u
                {$where2}
            ", $params2)->getRowArray();

            // ── Breakdown channel ────────────────────────────────────────────
            [$where3, $params3] = $this->buildWhere($filter);
            $channelBreakdown = $db->query("
                SELECT
                    channel,
                    COUNT(*)               AS total_trx,
                    COALESCE(SUM(total),0) AS total_omzet
                FROM ({$union}) u
                {$where3}
                GROUP BY channel
                ORDER BY total_omzet DESC
            ", $params3)->getResultArray();

            // ── Chart harian ─────────────────────────────────────────────────
            [$where4, $params4] = $this->buildWhere($filter);
            $chartData = $db->query("
                SELECT
                    DATE(u.created_at)        AS tgl,
                    COALESCE(SUM(u.total), 0) AS omzet,
                    COUNT(*)                  AS trx,
                    COALESCE(SUM(CASE WHEN u.source = 'internal' THEN u.total ELSE 0 END), 0) AS omzet_internal,
                    COALESCE(SUM(CASE WHEN u.source = 'tiktok'   THEN u.total ELSE 0 END), 0) AS omzet_tiktok
                FROM ({$union}) u
                {$where4}
                GROUP BY DATE(u.created_at)
                ORDER BY tgl ASC
            ", $params4)->getResultArray();

            return $this->response->setJSON([
                'success'          => true,
                'rows'             => $rows             ?? [],
                'summary'          => $summary          ?? [],
                'chartData'        => $chartData        ?? [],
                'channelBreakdown' => $channelBreakdown ?? [],
            ]);

        } catch (\Throwable $e) {
            log_message('error', '[LaporanPenjualan::data] ' . $e->getMessage());
            return $this->response->setStatusCode(500)->setJSON([
                'success' => false,
                'error'   => $e->getMessage(),
            ]);
        }
    }

    // ─────────────────────────────────────────────
    //  detail()
    // ─────────────────────────────────────────────
    public function detail(string $id): \CodeIgniter\HTTP\ResponseInterface
    {
        try {
            $db = \Config\Database::connect();

            $isInternal = ctype_digit((string) $id);

            if ($isInternal) {
                $pj = $db->query(
                    "SELECT *, 'internal' AS source FROM tb_penjualan WHERE id_penjualan = ?",
                    [$id]
                )->getRowArray();

                if (!$pj) {
                    return $this->response->setStatusCode(404)->setJSON([
                        'success' => false, 'error' => 'Transaksi tidak ditemukan.',
                    ]);
                }

                $header = [
                    'id_penjualan' => $pj['id_penjualan'],
                    'no_transaksi' => $pj['kode_penjualan'],
                    'channel'      => $pj['jenis_penjualan'],
                    'metode_bayar' => 'tunai',
                    'subtotal'     => $pj['total_penjualan'],
                    'diskon'       => 0,
                    'total'        => $pj['total_penjualan'],
                    'bayar'        => $pj['total_penjualan'],
                    'kembalian'    => 0,
                    'catatan'      => $pj['catatan_penjualan'],
                    'created_at'   => $pj['created_at'],
                    'source'       => 'internal',
                    'id_reseller'  => $pj['id_reseller'] ?? null,
                    'id_maklon'    => $pj['id_maklon']   ?? null,
                ];

                $items = $db->query("
                    SELECT i.*, p.nama_produk, p.kode_produk, p.berat_produk
                    FROM tb_penjualan_item i
                    JOIN tb_produk p ON p.id_produk = i.id_produk
                    WHERE i.id_penjualan = ?
                    ORDER BY i.id_penjualan_item ASC
                ", [$id])->getResultArray();

            } else {
                $tp = $db->query(
                    "SELECT *, 'tiktok' AS source FROM transaksi_pesanan WHERE order_id = ?",
                    [$id]
                )->getRowArray();

                if (!$tp) {
                    return $this->response->setStatusCode(404)->setJSON([
                        'success' => false, 'error' => 'Order TikTok tidak ditemukan.',
                    ]);
                }

                $header = [
                    'id_penjualan'      => $tp['order_id'],
                    'no_transaksi'      => $tp['order_id'],
                    'channel'           => 'marketplace',
                    'metode_bayar'      => $tp['payment_method'] ?? 'transfer',
                    'subtotal'          => $tp['order_amount'],
                    'diskon'            => $tp['order_amount'] - $tp['total_amount'],
                    'total'             => $tp['total_amount'],
                    'bayar'             => $tp['total_amount'],
                    'kembalian'         => 0,
                    'catatan'           => $tp['buyer_message']    ?? '',
                    'created_at'        => $tp['create_time'],
                    'source'            => 'tiktok',
                    'buyer_username'    => $tp['buyer_username']   ?? '',
                    'status_pesanan'    => $tp['status_pesanan']   ?? '',
                    'shipping_provider' => $tp['shipping_provider'] ?? '',
                    'tracking_id'       => $tp['tracking_id']      ?? '',
                ];

                $items = $db->query("
                    SELECT
                        dp.id_detail,
                        dp.order_id,
                        dp.sku_id,
                        dp.seller_sku                                                  AS kode_produk,
                        COALESCE(
                            NULLIF(TRIM(dp.nama_produk_raw), ''),
                            dp.seller_sku
                        )                                                              AS nama_produk,
                        dp.variasi_raw,
                        dp.kombinasi_produk,
                        dp.quantity                                                    AS qty,
                        dp.sku_quantity_of_return,
                        (dp.quantity - COALESCE(dp.sku_quantity_of_return, 0))         AS qty_net,
                        dp.sku_unit_original_price                                     AS harga_satuan,
                        dp.sku_unit_original_price                                     AS harga_jual,
                        dp.sku_subtotal_before_discount                                AS subtotal_sebelum_diskon,
                        dp.sku_platform_discount,
                        dp.sku_seller_discount,
                        dp.sku_subtotal_after_discount                                 AS subtotal,
                        dp.sku_settlement_amt
                    FROM detail_pesanan dp
                    WHERE dp.order_id = ?
                    ORDER BY dp.id_detail ASC
                ", [$id])->getResultArray();
            }

            return $this->response->setJSON([
                'success' => true,
                'header'  => $header,
                'items'   => $items ?? [],
            ]);

        } catch (\Throwable $e) {
            log_message('error', '[LaporanPenjualan::detail] ' . $e->getMessage());
            return $this->response->setStatusCode(500)->setJSON([
                'success' => false,
                'error'   => $e->getMessage(),
            ]);
        }
    }

    // ─────────────────────────────────────────────
    //  exportXlsx()
    // ─────────────────────────────────────────────
    public function exportXlsx(): void
    {
        $db = \Config\Database::connect();

        $filter = [
            'tgl_mulai' => $this->request->getGet('tgl_mulai') ?: date('Y-m-01'),
            'tgl_akhir' => $this->request->getGet('tgl_akhir') ?: date('Y-m-d'),
            'channel'   => trim($this->request->getGet('channel') ?? ''),
            'metode'    => trim($this->request->getGet('metode')  ?? ''),
            'search'    => '',
        ];

        $union = $this->unionSQL();
        [$where, $params] = $this->buildWhere($filter);

        $rows = $db->query("
            SELECT
                u.no_transaksi,
                u.created_at,
                u.channel,
                u.metode_bayar,
                u.source,
                u.subtotal,
                u.diskon,
                u.total AS total_transaksi,
                u.catatan
            FROM ({$union}) u
            {$where}
            ORDER BY u.created_at DESC
        ", $params)->getResultArray();

        $spreadsheet = new Spreadsheet();
        $spreadsheet->getProperties()
            ->setCreator('SIM System')
            ->setTitle('Laporan Penjualan ' . $filter['tgl_mulai'] . ' s/d ' . $filter['tgl_akhir']);

        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Detail Transaksi');

        $colorHeader = 'F5F5F5';

        // ── Judul ──
        $sheet->mergeCells('A1:I1');
        $sheet->setCellValue('A1', 'LAPORAN PENJUALAN (Internal + TikTok)');
        $sheet->getStyle('A1')->applyFromArray([
            'font'      => ['bold' => true, 'size' => 14, 'name' => 'Arial'],
            'fill'      => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $colorHeader]],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER],
        ]);
        $sheet->getRowDimension(1)->setRowHeight(32);

        // ── Sub-judul ──
        $sheet->mergeCells('A2:I2');
        $chanLabel = $filter['channel'] ? strtoupper($filter['channel']) : 'SEMUA CHANNEL';
        $sheet->setCellValue('A2',
            'Periode: ' . $filter['tgl_mulai'] . ' s/d ' . $filter['tgl_akhir'] .
            '   |   Channel: ' . $chanLabel .
            '   |   Digenerate: ' . date('d/m/Y H:i')
        );
        $sheet->getStyle('A2')->applyFromArray([
            'font'      => ['size' => 9, 'name' => 'Arial'],
            'fill'      => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $colorHeader]],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
        ]);
        $sheet->getRowDimension(3)->setRowHeight(8);

        // ── Header kolom ──
        $headers = [
            'A' => 'No. Transaksi / Order ID',
            'B' => 'Tanggal & Waktu',
            'C' => 'Channel',
            'D' => 'Metode Bayar',
            'E' => 'Sumber',
            'F' => 'Subtotal',
            'G' => 'Diskon',
            'H' => 'Total',
            'I' => 'Catatan',
        ];
        foreach ($headers as $col => $label) {
            $sheet->setCellValue("{$col}4", $label);
        }
        $sheet->getStyle('A4:I4')->applyFromArray([
            'font'      => ['bold' => true, 'size' => 10, 'name' => 'Arial'],
            'fill'      => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $colorHeader]],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER],
            'borders'   => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'D1D5DB']]],
        ]);
        $sheet->getRowDimension(4)->setRowHeight(24);

        // ── Data rows ──
        $chanMap = [
            'Ecer'        => 'Kasir POS',
            'Reseller'    => 'Reseller',
            'Maklon'      => 'Maklon',
            'marketplace' => 'Marketplace',
        ];
        $rupiah = '#,##0';
        $r      = 5;

        foreach ($rows as $idx => $row) {
            $isEven = ($idx % 2 === 1);
            $sheet->setCellValue("A{$r}", $row['no_transaksi']);
            $sheet->setCellValue("B{$r}", $row['created_at']);
            $sheet->setCellValue("C{$r}", $chanMap[$row['channel']] ?? $row['channel']);
            $sheet->setCellValue("D{$r}", $row['metode_bayar']);
            $sheet->setCellValue("E{$r}", $row['source'] === 'tiktok' ? 'TikTok' : 'Internal');
            $sheet->setCellValue("F{$r}", (float) $row['subtotal']);
            $sheet->setCellValue("G{$r}", (float) $row['diskon']);
            $sheet->setCellValue("H{$r}", (float) $row['total_transaksi']);
            $sheet->setCellValue("I{$r}", $row['catatan'] ?? '');

            $sheet->getStyle("F{$r}:H{$r}")->getNumberFormat()->setFormatCode($rupiah);
            $sheet->getStyle("F{$r}:H{$r}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
            $sheet->getStyle("A{$r}:I{$r}")->getFont()->setName('Arial')->setSize(9);
            if ($isEven) {
                $sheet->getStyle("A{$r}:I{$r}")->getFill()->applyFromArray(
                    ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'F9FAFB']]
                );
            }
            $sheet->getStyle("A{$r}:I{$r}")->getBorders()->getBottom()
                ->setBorderStyle(Border::BORDER_HAIR)->getColor()->setRGB('E5E7EB');
            $r++;
        }

        // ── Baris TOTAL ──
        if (!empty($rows)) {
            $lastData = $r - 1;
            $sheet->mergeCells("A{$r}:E{$r}");
            $sheet->setCellValue("A{$r}", 'TOTAL');
            $sheet->setCellValue("F{$r}", "=SUM(F5:F{$lastData})");
            $sheet->setCellValue("G{$r}", "=SUM(G5:G{$lastData})");
            $sheet->setCellValue("H{$r}", "=SUM(H5:H{$lastData})");
            $sheet->getStyle("A{$r}:I{$r}")->applyFromArray([
                'font'    => ['bold' => true, 'name' => 'Arial', 'size' => 10],
                'fill'    => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $colorHeader]],
                'borders' => ['top' => ['borderStyle' => Border::BORDER_MEDIUM, 'color' => ['rgb' => 'D1D5DB']]],
            ]);
            $sheet->getStyle("F{$r}:H{$r}")->getNumberFormat()->setFormatCode($rupiah);
            $sheet->getStyle("F{$r}:H{$r}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
            $sheet->getStyle("A{$r}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        }

        // ── Layout ──
        $colWidths = ['A' => 28, 'B' => 20, 'C' => 14, 'D' => 16, 'E' => 12, 'F' => 16, 'G' => 14, 'H' => 18, 'I' => 24];
        foreach ($colWidths as $col => $w) {
            $sheet->getColumnDimension($col)->setWidth($w);
        }
        $sheet->freezePane('A5');
        $sheet->setAutoFilter('A4:I4');
        $spreadsheet->setActiveSheetIndex(0);

        $filename = 'Laporan_Penjualan_' . $filter['tgl_mulai'] . '_sd_' . $filter['tgl_akhir'] . '.xlsx';
        while (ob_get_level()) ob_end_clean();
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        (new Xlsx($spreadsheet))->save('php://output');
        exit;
    }
}