<?php
namespace App\Exports;

use App\Models\PanelTest;

class PanelTestExport
{
    protected int $panelTestId;
    protected ?int $spgId;

    private array $palette = [
        ['dark' => 'FF1D4ED8', 'mid' => 'FF3B82F6', 'light' => 'FFDBEAFE', 'xlight' => 'FFEFF6FF'],
        ['dark' => 'FF15803D', 'mid' => 'FF22C55E', 'light' => 'FFDCFCE7', 'xlight' => 'FFF0FDF4'],
        ['dark' => 'FFB45309', 'mid' => 'FFF59E0B', 'light' => 'FFFEF3C7', 'xlight' => 'FFFFFBEB'],
        ['dark' => 'FFB91C1C', 'mid' => 'FFEF4444', 'light' => 'FFFEE2E2', 'xlight' => 'FFFFF1F2'],
        ['dark' => 'FF6D28D9', 'mid' => 'FF8B5CF6', 'light' => 'FFEDE9FE', 'xlight' => 'FFF5F3FF'],
    ];

    public function __construct(int $panelTestId, ?int $spgId = null)
    {
        $this->panelTestId = $panelTestId;
        $this->spgId       = $spgId;
    }

    public function download()
    {
        $panelTest = PanelTest::with([
            'product',
            'samples.questions',
            'questions' => fn($q) => $q->whereNull('sample_id'),
            'testSessions.user',
            'testSessions.answers.question',
        ])->findOrFail($this->panelTestId);

        $sessions = $panelTest->testSessions->where('status', 'completed');
        if ($this->spgId) $sessions = $sessions->where('user_id', $this->spgId);
        $sessions = $sessions->values();
        $total    = $sessions->count();

        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $spreadsheet->getProperties()
            ->setTitle($panelTest->title)
            ->setCreator('Panel Test App')
            ->setDescription('Laporan Analisis Panel Test Produk');

        $s1 = $spreadsheet->getActiveSheet();
        $s1->setTitle('🏆 Kesimpulan Eksekutif');
        $this->sheet1_executive($s1, $panelTest, $sessions, $total);

        $s2 = $spreadsheet->createSheet();
        $s2->setTitle('📊 Perbandingan Sampel');
        $this->sheet2_comparison($s2, $panelTest, $sessions, $total);

        $s3 = $spreadsheet->createSheet();
        $s3->setTitle('🔬 Analisis Detail');
        $this->sheet3_detail($s3, $panelTest, $sessions, $total);

        $s4 = $spreadsheet->createSheet();
        $s4->setTitle('📋 Data Responden');
        $this->sheet4_data($s4, $panelTest, $sessions);

        $spreadsheet->setActiveSheetIndex(0);

        $filename = 'Laporan-PanelTest-'
            . \Illuminate\Support\Str::slug($panelTest->title)
            . '-' . date('Ymd') . '.xlsx';

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
        exit;
    }

    // ============================================================
    // HELPERS
    // ============================================================
    private function style($sheet, string $range, array $style)
    {
        $sheet->getStyle($range)->applyFromArray($style);
    }

    private function fill($sheet, string $range, string $argb)
    {
        $sheet->getStyle($range)->getFill()
            ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
            ->getStartColor()->setARGB($argb);
    }

    private function font($sheet, string $range, array $opts)
    {
        $f = $sheet->getStyle($range)->getFont();
        if (isset($opts['bold']))   $f->setBold($opts['bold']);
        if (isset($opts['size']))   $f->setSize($opts['size']);
        if (isset($opts['color']))  $f->getColor()->setARGB($opts['color']);
        if (isset($opts['italic'])) $f->setItalic($opts['italic']);
    }

    private function align($sheet, string $range, string $h = 'center', string $v = 'center', bool $wrap = false)
    {
        $a = $sheet->getStyle($range)->getAlignment();
        $a->setHorizontal($h);
        $a->setVertical($v);
        if ($wrap) $a->setWrapText(true);
    }

    private function border($sheet, string $range, string $style = 'thin', string $color = 'FFE2E8F0')
    {
        $sheet->getStyle($range)->applyFromArray([
            'borders' => ['allBorders' => [
                'borderStyle' => $style,
                'color'       => ['argb' => $color],
            ]],
        ]);
    }

    private function col($n): string
    {
        return \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($n);
    }

    private function pageHeader($sheet, string $title, string $sub, string $darkBg = 'FF0F172A')
    {
        $sheet->mergeCells('A1:P1');
        $sheet->mergeCells('A2:P2');
        $sheet->mergeCells('A3:P3');
        $this->fill($sheet, 'A1:P3', $darkBg);
        $sheet->setCellValue('A1', '  ' . $title);
        $this->font($sheet, 'A1', ['bold' => true, 'size' => 16, 'color' => 'FFFFFFFF']);
        $this->align($sheet, 'A1', 'left', 'center');
        $sheet->setCellValue('A2', '  ' . $sub);
        $this->font($sheet, 'A2', ['size' => 10, 'color' => 'FF94A3B8']);
        $this->align($sheet, 'A2', 'left', 'center');
        $sheet->setCellValue('A3', '  Generated: ' . now()->format('d M Y H:i') . '   |   Panel Test App');
        $this->font($sheet, 'A3', ['size' => 8, 'color' => 'FF475569']);
        $sheet->getRowDimension(1)->setRowHeight(36);
        $sheet->getRowDimension(2)->setRowHeight(20);
        $sheet->getRowDimension(3)->setRowHeight(16);
    }

    // ============================================================
    // SHEET 1: KESIMPULAN EKSEKUTIF
    // ============================================================
    private function sheet1_executive($sheet, $panelTest, $sessions, $total)
    {
        $sheet->getColumnDimension('A')->setWidth(2);
        foreach (range('B', 'P') as $c) $sheet->getColumnDimension($c)->setWidth(13);
        $sheet->getColumnDimension('B')->setWidth(28);
        $sheet->getColumnDimension('C')->setWidth(18);

        $this->pageHeader(
            $sheet,
            '🏆  KESIMPULAN EKSEKUTIF — ' . strtoupper($panelTest->title),
            $panelTest->product->name . '   ·   ' . $panelTest->test_date->format('d M Y') .
            ($panelTest->location ? '   ·   ' . $panelTest->location : '')
        );

        $sheet->getRowDimension(4)->setRowHeight(10);
        $row     = 5;
        $samples = $panelTest->samples;

        $sampleScores = [];
        foreach ($samples as $si => $sample) {
            $sc         = $this->palette[$si % 5];
            $totalScore = 0;
            $scoreCount = 0;
            $qCount     = $sample->questions->count();

            foreach ($sample->questions as $q) {
                $answers = $sessions->flatMap->answers->where('question_id', $q->id);
                $qTotal  = $answers->count();

                if ($q->question_type === 'rating') {
                    $avg        = $qTotal > 0 ? $answers->whereNotNull('rating_value')->avg('rating_value') : 0;
                    $totalScore += ($avg / 5) * 100;
                    $scoreCount++;
                } else {
                    $counts   = $answers->whereNotNull('answer_value')->groupBy('answer_value')->map->count();
                    $topCount = $counts->max() ?? 0;
                    $pct      = $qTotal > 0 ? ($topCount / $qTotal) * 100 : 0;
                    $totalScore += $pct;
                    $scoreCount++;
                }
            }

            $avgScore = $scoreCount > 0 ? round($totalScore / $scoreCount, 1) : 0;
            $sampleScores[$sample->id] = [
                'sample' => $sample, 'si' => $si,
                'sc'     => $sc, 'score' => $avgScore, 'qCount' => $qCount,
            ];
        }

        $winner = collect($sampleScores)->sortByDesc('score')->first();

        $sheet->mergeCells("B{$row}:P{$row}");
        $sheet->setCellValue("B{$row}", '  📊  SKOR KEUNGGULAN PER SAMPEL');
        $this->fill($sheet, "B{$row}:P{$row}", 'FF1E293B');
        $this->font($sheet, "B{$row}", ['bold' => true, 'size' => 11, 'color' => 'FFFFFFFF']);
        $this->align($sheet, "B{$row}", 'left', 'center');
        $sheet->getRowDimension($row)->setRowHeight(26);
        $row++;

        $cardWidth = 3;
        $cardStart = 2;

        foreach ($sampleScores as $sid => $data) {
            $si       = $data['si'];
            $sc       = $data['sc'];
            $s        = $data['sample'];
            $isWinner = $winner && $data['score'] >= $winner['score'] && $data['score'] > 0;
            $c1       = $this->col($cardStart);
            $c2       = $this->col($cardStart + $cardWidth - 1);

            $sheet->mergeCells("{$c1}{$row}:{$c2}{$row}");
            $this->fill($sheet, "{$c1}{$row}:{$c2}{$row}", $isWinner ? 'FFFBBF24' : $sc['dark']);
            $sheet->getRowDimension($row)->setRowHeight(5);

            $sheet->mergeCells("{$c1}" . ($row+1) . ":{$c2}" . ($row+1));
            $sheet->setCellValue("{$c1}" . ($row+1),
                ($isWinner ? '🥇 ' : '') . strtoupper($s->name) . ($s->code ? ' (' . $s->code . ')' : '')
            );
            $this->fill($sheet, "{$c1}" . ($row+1) . ":{$c2}" . ($row+1), $sc['dark']);
            $this->font($sheet, "{$c1}" . ($row+1), ['bold' => true, 'size' => 11, 'color' => 'FFFFFFFF']);
            $this->align($sheet, "{$c1}" . ($row+1), 'center', 'center');
            $sheet->getRowDimension($row+1)->setRowHeight(24);

            $sheet->mergeCells("{$c1}" . ($row+2) . ":{$c2}" . ($row+2));
            $sheet->setCellValue("{$c1}" . ($row+2), $data['score'] . '%');
            $this->fill($sheet, "{$c1}" . ($row+2) . ":{$c2}" . ($row+2), $sc['light']);
            $this->font($sheet, "{$c1}" . ($row+2), ['bold' => true, 'size' => 22, 'color' => $sc['dark']]);
            $this->align($sheet, "{$c1}" . ($row+2), 'center', 'center');
            $sheet->getRowDimension($row+2)->setRowHeight(36);

            $sheet->mergeCells("{$c1}" . ($row+3) . ":{$c2}" . ($row+3));
            $sheet->setCellValue("{$c1}" . ($row+3), 'SKOR KEUNGGULAN');
            $this->fill($sheet, "{$c1}" . ($row+3) . ":{$c2}" . ($row+3), $sc['light']);
            $this->font($sheet, "{$c1}" . ($row+3), ['size' => 8, 'color' => $sc['mid']]);
            $this->align($sheet, "{$c1}" . ($row+3), 'center', 'center');
            $sheet->getRowDimension($row+3)->setRowHeight(16);

            $sheet->mergeCells("{$c1}" . ($row+4) . ":{$c2}" . ($row+4));
            $sheet->setCellValue("{$c1}" . ($row+4), $data['qCount'] . ' pertanyaan');
            $this->fill($sheet, "{$c1}" . ($row+4) . ":{$c2}" . ($row+4), $sc['xlight']);
            $this->font($sheet, "{$c1}" . ($row+4), ['size' => 9, 'color' => 'FF64748B']);
            $this->align($sheet, "{$c1}" . ($row+4), 'center', 'center');
            $sheet->getRowDimension($row+4)->setRowHeight(18);

            $sheet->mergeCells("{$c1}" . ($row+5) . ":{$c2}" . ($row+5));
            $statusText = $isWinner ? '✅ PALING UNGGUL' : ($data['score'] > 60 ? '👍 BAIK' : '⚠️ PERLU EVALUASI');
            $statusBg   = $isWinner ? 'FFFBBF24' : ($data['score'] > 60 ? $sc['light'] : 'FFFECACA');
            $statusFont = $isWinner ? 'FF92400E' : ($data['score'] > 60 ? $sc['dark'] : 'FF991B1B');
            $sheet->setCellValue("{$c1}" . ($row+5), $statusText);
            $this->fill($sheet, "{$c1}" . ($row+5) . ":{$c2}" . ($row+5), $statusBg);
            $this->font($sheet, "{$c1}" . ($row+5), ['bold' => true, 'size' => 9, 'color' => $statusFont]);
            $this->align($sheet, "{$c1}" . ($row+5), 'center', 'center');
            $sheet->getRowDimension($row+5)->setRowHeight(20);

            $cardStart += $cardWidth + 1;
        }

        $row += 7;
        $sheet->getRowDimension($row)->setRowHeight(10);
        $row++;

        // Rekap per pertanyaan
        $sheet->mergeCells("B{$row}:P{$row}");
        $sheet->setCellValue("B{$row}", '  📋  REKAP KEUNGGULAN PER PERTANYAAN');
        $this->fill($sheet, "B{$row}:P{$row}", 'FF1E293B');
        $this->font($sheet, "B{$row}", ['bold' => true, 'size' => 11, 'color' => 'FFFFFFFF']);
        $this->align($sheet, "B{$row}", 'left', 'center');
        $sheet->getRowDimension($row)->setRowHeight(26);
        $row++;

        $hRow = $row;
        $sheet->setCellValue("B{$hRow}", 'No');
        $sheet->setCellValue("C{$hRow}", 'Pertanyaan');
        $colH = 4;
        foreach ($samples as $si => $sample) {
            $sc = $this->palette[$si % 5];
            $c  = $this->col($colH);
            $sheet->setCellValue("{$c}{$hRow}",
                strtoupper($sample->name) . ($sample->code ? ' (' . $sample->code . ')' : '')
            );
            $this->fill($sheet, "{$c}{$hRow}", $sc['dark']);
            $this->font($sheet, "{$c}{$hRow}", ['bold' => true, 'size' => 9, 'color' => 'FFFFFFFF']);
            $this->align($sheet, "{$c}{$hRow}", 'center', 'center', true);
            $colH++;
        }
        $winCol  = $this->col($colH);
        $nextCol = $this->col($colH + 1);
        $sheet->setCellValue("{$winCol}{$hRow}", '🏆 PEMENANG');
        $this->fill($sheet, "{$winCol}{$hRow}", 'FF7C3AED');
        $this->font($sheet, "{$winCol}{$hRow}", ['bold' => true, 'size' => 9, 'color' => 'FFFFFFFF']);
        $this->align($sheet, "{$winCol}{$hRow}", 'center', 'center');
        $sheet->setCellValue("{$nextCol}{$hRow}", 'SELISIH');
        $this->fill($sheet, "{$nextCol}{$hRow}", 'FF475569');
        $this->font($sheet, "{$nextCol}{$hRow}", ['bold' => true, 'size' => 9, 'color' => 'FFFFFFFF']);
        $this->align($sheet, "{$nextCol}{$hRow}", 'center', 'center');
        $this->fill($sheet, "B{$hRow}:C{$hRow}", 'FF334155');
        $this->font($sheet, "B{$hRow}", ['bold' => true, 'size' => 9, 'color' => 'FFFFFFFF']);
        $this->font($sheet, "C{$hRow}", ['bold' => true, 'size' => 9, 'color' => 'FFFFFFFF']);
        $this->align($sheet, "B{$hRow}:C{$hRow}", 'center', 'center', true);
        $sheet->getColumnDimension('C')->setWidth(35);
        $sheet->getRowDimension($hRow)->setRowHeight(22);
        $row++;

        $allSampleQs = $panelTest->samples->flatMap->questions;
        $grouped     = $allSampleQs->groupBy('question_text');
        $qNo         = 1;

        foreach ($grouped as $qText => $questions) {
            $isEven = $qNo % 2 === 0;
            $rowBg  = $isEven ? 'FFF8FAFC' : 'FFFFFFFF';

            $sheet->setCellValue("B{$row}", $qNo);
            $sheet->setCellValue("C{$row}", $qText);
            $this->fill($sheet, "B{$row}:C{$row}", $rowBg);
            $this->font($sheet, "C{$row}", ['size' => 10, 'bold' => true]);
            $this->align($sheet, "B{$row}", 'center', 'center');
            $this->align($sheet, "C{$row}", 'left', 'center', true);

            $colD    = 4;
            $allPcts = [];

            foreach ($samples as $si => $sample) {
                $sc      = $this->palette[$si % 5];
                $sampleQ = $questions->where('sample_id', $sample->id)->first();
                $c       = $this->col($colD);

                if (!$sampleQ) {
                    $sheet->setCellValue("{$c}{$row}", '-');
                    $this->fill($sheet, "{$c}{$row}", $rowBg);
                    $colD++;
                    continue;
                }

                $answers = $sessions->flatMap->answers->where('question_id', $sampleQ->id);
                $qTotal  = $answers->count();

                if ($sampleQ->question_type === 'rating') {
                    $avg     = $qTotal > 0 ? round($answers->whereNotNull('rating_value')->avg('rating_value'), 2) : 0;
                    $pct     = round(($avg / 5) * 100, 1);
                    $display = '⭐ ' . $avg . '/5  (' . $pct . '%)';
                    $allPcts[$sample->name] = $pct;
                } else {
                    $counts  = $answers->whereNotNull('answer_value')->groupBy('answer_value')->map->count()->sortDesc();
                    $topC    = $counts->first() ?? 0;
                    $topK    = $counts->keys()->first() ?? '-';
                    $pct     = $qTotal > 0 ? round($topC / $qTotal * 100, 1) : 0;
                    $display = '"' . $topK . '"  ' . $topC . 'org (' . $pct . '%)';
                    $allPcts[$sample->name] = $pct;
                }

                $sheet->setCellValue("{$c}{$row}", $display);
                $this->fill($sheet, "{$c}{$row}", $rowBg);
                $this->font($sheet, "{$c}{$row}", ['size' => 9, 'color' => $sc['dark']]);
                $this->align($sheet, "{$c}{$row}", 'center', 'center');
                $colD++;
            }

            if (!empty($allPcts)) {
                $maxPct  = max($allPcts);
                $minPct  = min($allPcts);
                $winners = array_keys($allPcts, $maxPct);
                $sheet->setCellValue("{$winCol}{$row}", implode(' & ', $winners));
                $this->fill($sheet, "{$winCol}{$row}", 'FFEDE9FE');
                $this->font($sheet, "{$winCol}{$row}", ['bold' => true, 'size' => 9, 'color' => 'FF6D28D9']);
                $this->align($sheet, "{$winCol}{$row}", 'center', 'center');
                $selisih = round($maxPct - $minPct, 1);
                $sheet->setCellValue("{$nextCol}{$row}", $selisih > 0 ? '+' . $selisih . '%' : '0%');
                $selisihBg   = $selisih > 20 ? 'FFBBF7D0' : ($selisih > 10 ? 'FFFEF3C7' : 'FFFEE2E2');
                $selisihFont = $selisih > 20 ? 'FF14532D' : ($selisih > 10 ? 'FF854D0E' : 'FF991B1B');
                $this->fill($sheet, "{$nextCol}{$row}", $selisihBg);
                $this->font($sheet, "{$nextCol}{$row}", ['bold' => true, 'size' => 9, 'color' => $selisihFont]);
                $this->align($sheet, "{$nextCol}{$row}", 'center', 'center');
            }

            $sheet->getRowDimension($row)->setRowHeight(22);
            $row++;
            $qNo++;
        }

        $this->border($sheet, "B{$hRow}:{$nextCol}" . ($row - 1), 'thin', 'FFE2E8F0');

        // Profil responden
        $row += 2;
        $sheet->mergeCells("B{$row}:P{$row}");
        $sheet->setCellValue("B{$row}", '  👥  PROFIL RESPONDEN');
        $this->fill($sheet, "B{$row}:P{$row}", 'FF334155');
        $this->font($sheet, "B{$row}", ['bold' => true, 'size' => 10, 'color' => 'FFFFFFFF']);
        $this->align($sheet, "B{$row}", 'left', 'center');
        $sheet->getRowDimension($row)->setRowHeight(22);
        $row++;

        $pria   = $sessions->where('respondent_gender', 'Laki-laki')->count();
        $wanita = $sessions->where('respondent_gender', 'Perempuan')->count();
        $ages   = $sessions->pluck('respondent_age')->filter();
        $avgAge = $ages->count() > 0 ? round($ages->avg()) : '-';

        $profData = [
            ['Total Responden', $total . ' orang', '100%'],
            ['Laki-laki', $pria . ' orang', ($total > 0 ? round($pria/$total*100,1) : 0) . '%'],
            ['Perempuan', $wanita . ' orang', ($total > 0 ? round($wanita/$total*100,1) : 0) . '%'],
            ['Rata-rata Usia', $avgAge . ' tahun', ''],
        ];

        foreach ($profData as $pi => $pd) {
            $bg = $pi % 2 === 0 ? 'FFF8FAFC' : 'FFFFFFFF';
            $sheet->setCellValue("B{$row}", $pd[0]);
            $sheet->setCellValue("C{$row}", $pd[1]);
            $sheet->setCellValue("D{$row}", $pd[2]);
            $this->fill($sheet, "B{$row}:D{$row}", $bg);
            $this->font($sheet, "B{$row}", ['bold' => true, 'size' => 10, 'color' => 'FF334155']);
            $this->align($sheet, "C{$row}:D{$row}", 'center', 'center');
            $sheet->getRowDimension($row)->setRowHeight(20);
            $row++;
        }

        $sheet->freezePane('B5');
    }

    // ============================================================
    // SHEET 2: PERBANDINGAN SAMPEL
    // ============================================================
    private function sheet2_comparison($sheet, $panelTest, $sessions, $total)
    {
        $sheet->getColumnDimension('A')->setWidth(2);
        $sheet->getColumnDimension('B')->setWidth(32);

        $this->pageHeader(
            $sheet,
            '📊  PERBANDINGAN LENGKAP ANTAR SAMPEL / GELAS',
            $panelTest->title . '   ·   ' . $panelTest->product->name . '   ·   ' . $total . ' Responden'
        );

        $sheet->getRowDimension(4)->setRowHeight(10);
        $row = 5;

        $samples     = $panelTest->samples;
        $allSampleQs = $panelTest->samples->flatMap->questions;
        $grouped     = $allSampleQs->groupBy('question_text');

        foreach ($grouped as $qText => $questions) {
            $endCol = $this->col(3 + $samples->count() * 3 + 2);
            $sheet->mergeCells("B{$row}:{$endCol}{$row}");
            $sheet->setCellValue("B{$row}", '  ' . $qText);
            $this->fill($sheet, "B{$row}:{$endCol}{$row}", 'FF0F172A');
            $this->font($sheet, "B{$row}", ['bold' => true, 'size' => 11, 'color' => 'FFFFFFFF']);
            $this->align($sheet, "B{$row}", 'left', 'center');
            $sheet->getRowDimension($row)->setRowHeight(26);
            $row++;

            $sheet->setCellValue("B{$row}", 'Pilihan');
            $this->fill($sheet, "B{$row}", 'FF334155');
            $this->font($sheet, "B{$row}", ['bold' => true, 'size' => 10, 'color' => 'FFFFFFFF']);
            $this->align($sheet, "B{$row}", 'center', 'center');

            $hCol = 3;
            foreach ($samples as $si => $sample) {
                $sc  = $this->palette[$si % 5];
                $c1  = $this->col($hCol);
                $c2  = $this->col($hCol + 2);
                $sheet->mergeCells("{$c1}{$row}:{$c2}{$row}");
                $sheet->setCellValue("{$c1}{$row}",
                    strtoupper($sample->name) . ($sample->code ? ' (' . $sample->code . ')' : '')
                );
                $this->fill($sheet, "{$c1}{$row}:{$c2}{$row}", $sc['dark']);
                $this->font($sheet, "{$c1}{$row}", ['bold' => true, 'size' => 10, 'color' => 'FFFFFFFF']);
                $this->align($sheet, "{$c1}{$row}", 'center', 'center');
                $sheet->getColumnDimension($c1)->setWidth(8);
                $sheet->getColumnDimension($this->col($hCol+1))->setWidth(8);
                $sheet->getColumnDimension($this->col($hCol+2))->setWidth(18);
                $hCol += 3;
            }

            $tCol  = $this->col($hCol);
            $tCol2 = $this->col($hCol + 1);
            $sheet->setCellValue("{$tCol}{$row}", '🏆 TERBAIK');
            $this->fill($sheet, "{$tCol}{$row}", 'FF7C3AED');
            $this->font($sheet, "{$tCol}{$row}", ['bold' => true, 'size' => 9, 'color' => 'FFFFFFFF']);
            $this->align($sheet, "{$tCol}{$row}", 'center', 'center');
            $sheet->getColumnDimension($tCol)->setWidth(18);
            $sheet->setCellValue("{$tCol2}{$row}", 'KEUNGGULAN');
            $this->fill($sheet, "{$tCol2}{$row}", 'FF059669');
            $this->font($sheet, "{$tCol2}{$row}", ['bold' => true, 'size' => 9, 'color' => 'FFFFFFFF']);
            $this->align($sheet, "{$tCol2}{$row}", 'center', 'center');
            $sheet->getColumnDimension($tCol2)->setWidth(14);
            $sheet->getRowDimension($row)->setRowHeight(22);
            $row++;

            $sheet->setCellValue("B{$row}", '');
            $dCol = 3;
            foreach ($samples as $si => $sample) {
                $sc = $this->palette[$si % 5];
                $cN = $this->col($dCol);
                $cP = $this->col($dCol + 1);
                $cB = $this->col($dCol + 2);
                $sheet->setCellValue("{$cN}{$row}", 'N');
                $sheet->setCellValue("{$cP}{$row}", '%');
                $sheet->setCellValue("{$cB}{$row}", 'Visual');
                $this->fill($sheet, "{$cN}{$row}:{$cB}{$row}", $sc['light']);
                $this->font($sheet, "{$cN}{$row}:{$cB}{$row}", ['bold' => true, 'size' => 9, 'color' => $sc['dark']]);
                $this->align($sheet, "{$cN}{$row}:{$cB}{$row}", 'center', 'center');
                $dCol += 3;
            }
            $sheet->getRowDimension($row)->setRowHeight(16);
            $row++;

            $allChoices   = collect();
            $sampleTotals = [];
            $choiceData   = [];

            foreach ($samples as $sample) {
                $sampleQ = $questions->where('sample_id', $sample->id)->first();
                if (!$sampleQ) continue;
                $answers = $sessions->flatMap->answers->where('question_id', $sampleQ->id);
                $sampleTotals[$sample->id] = $answers->count();

                if ($sampleQ->question_type === 'multiple_choice' && $sampleQ->options) {
                    $allChoices = $allChoices->merge($sampleQ->options);
                } elseif ($sampleQ->question_type === 'yes_no') {
                    $allChoices = $allChoices->merge(['Ya', 'Tidak']);
                } elseif ($sampleQ->question_type === 'rating') {
                    $allChoices = $allChoices->merge(['1','2','3','4','5']);
                }

                foreach ($answers as $ans) {
                    $key = $sampleQ->question_type === 'rating'
                        ? (string)$ans->rating_value
                        : ($ans->answer_value ?? '');
                    if ($key !== '') {
                        $choiceData[$key][$sample->id] = ($choiceData[$key][$sample->id] ?? 0) + 1;
                    }
                }
            }
            $allChoices = $allChoices->unique()->values();
            $maxBar     = 10;

            foreach ($allChoices as $ci => $choice) {
                $isEven = $ci % 2 === 0;
                $rowBg  = $isEven ? 'FFF8FAFC' : 'FFFFFFFF';
                $displayChoice = is_numeric($choice) && in_array($choice, ['1','2','3','4','5'])
                    ? str_repeat('⭐', (int)$choice) . ' (' . $choice . ')'
                    : $choice;

                $sheet->setCellValue("B{$row}", '  ' . $displayChoice);
                $this->fill($sheet, "B{$row}", $rowBg);
                $this->font($sheet, "B{$row}", ['bold' => true, 'size' => 10, 'color' => 'FF1E293B']);
                $this->align($sheet, "B{$row}", 'left', 'center');

                $dCol    = 3;
                $maxPct  = 0;
                $maxSi   = 0;
                $maxName = '';
                $allPcts = [];

                foreach ($samples as $si => $sample) {
                    $sc    = $this->palette[$si % 5];
                    $cN    = $this->col($dCol);
                    $cP    = $this->col($dCol + 1);
                    $cB    = $this->col($dCol + 2);
                    $count = $choiceData[$choice][$sample->id] ?? 0;
                    $t     = $sampleTotals[$sample->id] ?? 0;
                    $pct   = $t > 0 ? round($count / $t * 100, 1) : 0;
                    $allPcts[$si] = $pct;

                    if ($pct > $maxPct) { $maxPct = $pct; $maxSi = $si; $maxName = $sample->name; }

                    $sheet->setCellValue("{$cN}{$row}", $count);
                    $sheet->setCellValue("{$cP}{$row}", $pct . '%');
                    $barLen = $t > 0 ? (int)(($count / $t) * $maxBar) : 0;
                    $bar    = str_repeat('█', $barLen) . str_repeat('░', $maxBar - $barLen);
                    $sheet->setCellValue("{$cB}{$row}", $bar);

                    $cellBg = $pct > 0 ? $sc['xlight'] : $rowBg;
                    $this->fill($sheet, "{$cN}{$row}:{$cP}{$row}", $cellBg);
                    $this->fill($sheet, "{$cB}{$row}", $cellBg);
                    $this->font($sheet, "{$cN}{$row}:{$cP}{$row}", ['size' => 10, 'color' => $pct > 0 ? $sc['dark'] : 'FFCBD5E1']);
                    $this->font($sheet, "{$cB}{$row}", ['size' => 9, 'color' => $sc['mid']]);
                    $this->align($sheet, "{$cN}{$row}:{$cP}{$row}", 'center', 'center');
                    $dCol += 3;
                }

                if ($maxPct > 0) {
                    $sc2   = $this->palette[$maxSi % 5];
                    $bestN = $this->col(3 + $maxSi * 3);
                    $bestP = $this->col(3 + $maxSi * 3 + 1);
                    $bestB = $this->col(3 + $maxSi * 3 + 2);
                    $this->fill($sheet, "{$bestN}{$row}:{$bestB}{$row}", $sc2['light']);
                    $this->font($sheet, "{$bestN}{$row}:{$bestP}{$row}", ['bold' => true, 'size' => 10, 'color' => $sc2['dark']]);
                }

                $sheet->setCellValue("{$tCol}{$row}", $maxPct > 0 ? $maxName : '-');
                $sc2bg   = $maxPct > 0 ? $this->palette[$maxSi % 5]['light'] : $rowBg;
                $sc2font = $maxPct > 0 ? $this->palette[$maxSi % 5]['dark']  : 'FFCBD5E1';
                $this->fill($sheet, "{$tCol}{$row}", $sc2bg);
                $this->font($sheet, "{$tCol}{$row}", ['bold' => true, 'size' => 10, 'color' => $sc2font]);
                $this->align($sheet, "{$tCol}{$row}", 'center', 'center');

                $pctValues = array_values($allPcts);
                if (count($pctValues) >= 2) {
                    $selisih = round(max($pctValues) - min($pctValues), 1);
                    $label   = $selisih > 20 ? '🟢 Unggul ' . $selisih . '%'
                             : ($selisih > 10 ? '🟡 Cukup ' . $selisih . '%'
                             : '🔴 Tipis ' . $selisih . '%');
                    $labelBg = $selisih > 20 ? 'FFBBF7D0' : ($selisih > 10 ? 'FFFEF3C7' : 'FFFEE2E2');
                    $sheet->setCellValue("{$tCol2}{$row}", $selisih > 0 ? $label : '= Imbang');
                    $this->fill($sheet, "{$tCol2}{$row}", $selisih > 0 ? $labelBg : 'FFF1F5F9');
                    $this->font($sheet, "{$tCol2}{$row}", ['size' => 9]);
                    $this->align($sheet, "{$tCol2}{$row}", 'center', 'center');
                }

                $sheet->getRowDimension($row)->setRowHeight(20);
                $row++;
            }

            $sheet->setCellValue("B{$row}", '  TOTAL RESPONDEN');
            $this->fill($sheet, "B{$row}", 'FF1E293B');
            $this->font($sheet, "B{$row}", ['bold' => true, 'size' => 10, 'color' => 'FFFFFFFF']);
            $dCol = 3;
            foreach ($samples as $si => $sample) {
                $sc  = $this->palette[$si % 5];
                $c1  = $this->col($dCol);
                $c2  = $this->col($dCol + 2);
                $t   = $sampleTotals[$sample->id] ?? 0;
                $sheet->mergeCells("{$c1}{$row}:{$c2}{$row}");
                $sheet->setCellValue("{$c1}{$row}", $t . ' responden');
                $this->fill($sheet, "{$c1}{$row}:{$c2}{$row}", $sc['dark']);
                $this->font($sheet, "{$c1}{$row}", ['bold' => true, 'size' => 10, 'color' => 'FFFFFFFF']);
                $this->align($sheet, "{$c1}{$row}", 'center', 'center');
                $dCol += 3;
            }
            $sheet->getRowDimension($row)->setRowHeight(22);
            $row += 2;
        }

        $sheet->freezePane('B5');
    }

    // ============================================================
    // SHEET 3: ANALISIS DETAIL
    // ============================================================
    private function sheet3_detail($sheet, $panelTest, $sessions, $total)
    {
        $sheet->getColumnDimension('A')->setWidth(2);
        $sheet->getColumnDimension('B')->setWidth(30);
        foreach (range('C', 'N') as $c) $sheet->getColumnDimension($c)->setWidth(13);

        $this->pageHeader(
            $sheet,
            '🔬  ANALISIS DETAIL PER SAMPEL',
            $panelTest->title . '   ·   ' . $total . ' Responden'
        );

        $sheet->getRowDimension(4)->setRowHeight(10);
        $row = 5;

        foreach ($panelTest->samples as $si => $sample) {
            $sc = $this->palette[$si % 5];

            $sheet->mergeCells("B{$row}:N{$row}");
            $sheet->setCellValue("B{$row}",
                '  📦  SAMPEL ' . ($si + 1) . ': ' . strtoupper($sample->name) .
                ($sample->code ? ' (' . $sample->code . ')' : '') .
                ($sample->description ? '  —  ' . $sample->description : '')
            );
            $this->fill($sheet, "B{$row}:N{$row}", $sc['dark']);
            $this->font($sheet, "B{$row}", ['bold' => true, 'size' => 12, 'color' => 'FFFFFFFF']);
            $this->align($sheet, "B{$row}", 'left', 'center');
            $sheet->getRowDimension($row)->setRowHeight(28);
            $row++;

            foreach ($sample->questions as $qi => $question) {
                $answers = $sessions->flatMap->answers->where('question_id', $question->id);
                $qTotal  = $answers->count();

                $sheet->mergeCells("B{$row}:N{$row}");
                $sheet->setCellValue("B{$row}", '  ' . ($qi + 1) . '.  ' . $question->question_text);
                $this->fill($sheet, "B{$row}:N{$row}", $sc['light']);
                $this->font($sheet, "B{$row}", ['bold' => true, 'size' => 10, 'color' => $sc['dark']]);
                $this->align($sheet, "B{$row}", 'left', 'center');
                $sheet->getRowDimension($row)->setRowHeight(22);
                $row++;

                foreach (['B'=>'Pilihan','C'=>'Jumlah','D'=>'%','E'=>'Visualisasi','J'=>'Peringkat','K'=>'Interpretasi'] as $c => $h) {
                    $sheet->setCellValue("{$c}{$row}", $h);
                }
                $sheet->mergeCells("E{$row}:I{$row}");
                $this->fill($sheet, "B{$row}:K{$row}", 'FFF1F5F9');
                $this->font($sheet, "B{$row}:K{$row}", ['bold' => true, 'size' => 9, 'color' => 'FF475569']);
                $this->align($sheet, "B{$row}:K{$row}", 'center', 'center');
                $this->align($sheet, "B{$row}", 'left', 'center');
                $sheet->getRowDimension($row)->setRowHeight(18);
                $row++;

                if ($question->question_type === 'rating') {
                    $ratingColors = [
                        1 => ['bg'=>'FFFECACA','font'=>'FF991B1B','label'=>'Sangat Buruk'],
                        2 => ['bg'=>'FFFED7AA','font'=>'FF9A3412','label'=>'Buruk'],
                        3 => ['bg'=>'FFFEF9C3','font'=>'FF854D0E','label'=>'Cukup'],
                        4 => ['bg'=>'FFD1FAE5','font'=>'FF065F46','label'=>'Baik'],
                        5 => ['bg'=>'FFBBF7D0','font'=>'FF14532D','label'=>'Sangat Baik'],
                    ];
                    $maxCount = 0;
                    for ($r = 1; $r <= 5; $r++) {
                        $c = $answers->where('rating_value', $r)->count();
                        if ($c > $maxCount) $maxCount = $c;
                    }

                    for ($r = 1; $r <= 5; $r++) {
                        $count  = $answers->where('rating_value', $r)->count();
                        $pct    = $qTotal > 0 ? round($count / $qTotal * 100, 1) : 0;
                        $rc     = $ratingColors[$r];
                        $barLen = $maxCount > 0 ? (int)(($count / $maxCount) * 12) : 0;
                        $bar    = str_repeat('█', $barLen) . str_repeat('░', 12 - $barLen);
                        $isBest = $count === $maxCount && $maxCount > 0;

                        $sheet->setCellValue("B{$row}", str_repeat('⭐', $r) . '  Bintang ' . $r);
                        $sheet->setCellValue("C{$row}", $count . ' org');
                        $sheet->setCellValue("D{$row}", $pct . '%');
                        $sheet->mergeCells("E{$row}:I{$row}");
                        $sheet->setCellValue("E{$row}", $bar . '  ' . $pct . '%');
                        $sheet->setCellValue("J{$row}", $isBest ? '🥇 #1' : '#' . (6 - $r));
                        $sheet->setCellValue("K{$row}", $rc['label']);

                        $bg = $isBest ? $rc['bg'] : 'FFFFFFFF';
                        $this->fill($sheet, "B{$row}:K{$row}", $bg);
                        $this->font($sheet, "B{$row}:K{$row}", ['size' => 10, 'bold' => $isBest, 'color' => $isBest ? $rc['font'] : 'FF334155']);
                        $this->align($sheet, "C{$row}:K{$row}", 'center', 'center');
                        $this->align($sheet, "E{$row}", 'left', 'center');
                        $this->font($sheet, "E{$row}", ['size' => 9, 'color' => $rc['font']]);
                        $this->fill($sheet, "E{$row}", $rc['bg']);
                        $sheet->getRowDimension($row)->setRowHeight(20);
                        $row++;
                    }

                    $avg = $qTotal > 0 ? round($answers->whereNotNull('rating_value')->avg('rating_value'), 2) : 0;
                    $stars = str_repeat('⭐', (int)round($avg));
                    $sheet->mergeCells("B{$row}:K{$row}");
                    $sheet->setCellValue("B{$row}",
                        '  ✅  RATA-RATA: ' . $avg . ' / 5.0   ' . $stars . '   |   ' . $qTotal . ' jawaban   |   ' .
                        ($avg >= 4 ? 'PENILAIAN: SANGAT BAIK 🟢' : ($avg >= 3 ? 'PENILAIAN: CUKUP BAIK 🟡' : 'PENILAIAN: PERLU PERBAIKAN 🔴'))
                    );
                    $summaryBg = $avg >= 4 ? 'FFBBF7D0' : ($avg >= 3 ? 'FFFEF3C7' : 'FFFECACA');
                    $summaryFt = $avg >= 4 ? 'FF14532D' : ($avg >= 3 ? 'FF854D0E' : 'FF991B1B');
                    $this->fill($sheet, "B{$row}:K{$row}", $summaryBg);
                    $this->font($sheet, "B{$row}", ['bold' => true, 'size' => 10, 'color' => $summaryFt]);
                    $this->align($sheet, "B{$row}", 'left', 'center');
                    $sheet->getRowDimension($row)->setRowHeight(22);
                    $row++;
                } else {
                    $choices = $question->question_type === 'yes_no'
                        ? ['Ya', 'Tidak']
                        : ($question->options ?? []);

                    $counts = [];
                    $maxC   = 0;
                    foreach ($choices as $ch) {
                        $c = $answers->where('answer_value', $ch)->count();
                        $counts[$ch] = $c;
                        if ($c > $maxC) $maxC = $c;
                    }
                    arsort($counts);
                    $rank = 1;

                    foreach ($counts as $choice => $count) {
                        $pct    = $qTotal > 0 ? round($count / $qTotal * 100, 1) : 0;
                        $barLen = $maxC > 0 ? (int)(($count / $maxC) * 12) : 0;
                        $bar    = str_repeat('█', $barLen) . str_repeat('░', 12 - $barLen);
                        $isBest = $rank === 1;
                        $isEven = $rank % 2 === 0;
                        $pctBg   = $pct >= 60 ? 'FFBBF7D0' : ($pct >= 40 ? 'FFFEF3C7' : ($pct >= 20 ? 'FFFFE4E6' : 'FFFAFAFA'));
                        $pctFont = $pct >= 60 ? 'FF14532D' : ($pct >= 40 ? 'FF854D0E' : ($pct >= 20 ? 'FF9F1239' : 'FF64748B'));

                        $sheet->setCellValue("B{$row}", '  ' . $choice);
                        $sheet->setCellValue("C{$row}", $count . ' org');
                        $sheet->setCellValue("D{$row}", $pct . '%');
                        $sheet->mergeCells("E{$row}:I{$row}");
                        $sheet->setCellValue("E{$row}", $bar . '  ' . $pct . '%');
                        $medal = match($rank) { 1=>'🥇 #1', 2=>'🥈 #2', 3=>'🥉 #3', default=>'#'.$rank };
                        $sheet->setCellValue("J{$row}", $medal);
                        $sheet->setCellValue("K{$row}", $pct >= 60 ? 'DOMINAN' : ($pct >= 40 ? 'KUAT' : ($pct >= 20 ? 'SEDANG' : 'LEMAH')));

                        $rowBg = $isBest ? $sc['light'] : ($isEven ? 'FFF8FAFC' : 'FFFFFFFF');
                        $this->fill($sheet, "B{$row}:K{$row}", $rowBg);
                        $this->font($sheet, "B{$row}:K{$row}", ['size' => 10, 'bold' => $isBest, 'color' => $isBest ? $sc['dark'] : 'FF334155']);
                        $this->align($sheet, "C{$row}:K{$row}", 'center', 'center');
                        $this->fill($sheet, "D{$row}", $pctBg);
                        $this->font($sheet, "D{$row}", ['bold' => true, 'size' => 10, 'color' => $pctFont]);
                        $this->fill($sheet, "E{$row}", $rowBg);
                        $this->font($sheet, "E{$row}", ['size' => 9, 'color' => $sc['mid']]);
                        $this->align($sheet, "E{$row}", 'left', 'center');
                        $sheet->getRowDimension($row)->setRowHeight(20);
                        $row++;
                        $rank++;
                    }

                    $winner  = array_key_first($counts);
                    $winPct  = $qTotal > 0 ? round(($counts[$winner] ?? 0) / $qTotal * 100, 1) : 0;
                    $sheet->mergeCells("B{$row}:K{$row}");
                    $sheet->setCellValue("B{$row}",
                        '  🏆  PILIHAN TERBANYAK: "' . $winner . '"   —   ' .
                        ($counts[$winner] ?? 0) . ' dari ' . $qTotal . ' responden (' . $winPct . '%)' .
                        ($winPct >= 50 ? '   ✅ MAYORITAS' : '   ⚠️ BELUM MAYORITAS')
                    );
                    $summBg = $winPct >= 50 ? $sc['light'] : 'FFFEF3C7';
                    $summFt = $winPct >= 50 ? $sc['dark']  : 'FF854D0E';
                    $this->fill($sheet, "B{$row}:K{$row}", $summBg);
                    $this->font($sheet, "B{$row}", ['bold' => true, 'size' => 10, 'color' => $summFt]);
                    $this->align($sheet, "B{$row}", 'left', 'center');
                    $sheet->getRowDimension($row)->setRowHeight(22);
                    $row++;
                }

                $sheet->getRowDimension($row)->setRowHeight(6);
                $row++;
            }

            $sheet->getRowDimension($row)->setRowHeight(12);
            $row++;
        }

        $sheet->freezePane('B5');
    }

    // ============================================================
    // SHEET 4: DATA RESPONDEN (dengan kolom marketing lengkap)
    // ============================================================
    private function sheet4_data($sheet, $panelTest, $sessions)
    {
        $isInternal = $panelTest->type === 'internal';

        $this->pageHeader(
            $sheet,
            '📋  DATA LENGKAP RESPONDEN',
            $panelTest->title . '   ·   ' . $sessions->count() . ' Responden'
        );
        $sheet->getRowDimension(4)->setRowHeight(10);

        // ===== Build header =====
        $colIdx = 1;

        if ($isInternal) {
            $fixedHeaders = [
                'No', 'No. Perner', 'Nama Responden',
                'Thn Lahir', 'Generasi', 'Usia', 'Gender', 'Waktu',
            ];
            $fixedWidths = [5, 14, 24, 10, 18, 8, 12, 18];
        } else {
            $fixedHeaders = [
                'No', 'Nama SPG', 'Area Office', 'Area Promotion', 'SP',
                'Nama Responden', 'Thn Lahir', 'Generasi', 'Usia', 'Gender',
                'Provinsi', 'Kota/Kabupaten', 'Kecamatan', 'Kelurahan',
                'Deskripsi Lokasi', 'Waktu',
            ];
            $fixedWidths = [5, 20, 16, 18, 18, 24, 10, 18, 8, 12, 16, 18, 16, 16, 24, 18];
        }

        // Fixed headers (biru gelap)
        foreach ($fixedHeaders as $idx => $h) {
            $c = $this->col($colIdx);
            $sheet->mergeCells("{$c}5:{$c}6");
            $sheet->setCellValue("{$c}5", $h);
            $this->fill($sheet, "{$c}5:{$c}6", 'FF1E293B');
            $this->font($sheet, "{$c}5", ['bold' => true, 'size' => 10, 'color' => 'FFFFFFFF']);
            $this->align($sheet, "{$c}5:{$c}6", 'center', 'center', true);
            $sheet->getColumnDimension($c)->setWidth($fixedWidths[$idx] ?? 16);
            $colIdx++;
        }

        // Marketing headers (ungu)
        $marketingHeaders = [
            'Brand Aktivasi',
            'Gender Konsumen',
            'Usia Konsumen',
            'Profesi',
            'Pengeluaran RT/Bulan',
            'Funnel',
            'Alasan Tidak Konversi',
            'Sumber Tahu Produk',
            'Sumber Lainnya',
            'Brand Kompetitor',
            'Kompetitor Lainnya',
            'Alasan Pakai Kompetitor',
        ];
        $marketingColStart = $colIdx;

        foreach ($marketingHeaders as $h) {
            $c = $this->col($colIdx);
            $sheet->mergeCells("{$c}5:{$c}6");
            $sheet->setCellValue("{$c}5", $h);
            $this->fill($sheet, "{$c}5:{$c}6", 'FF6D28D9');
            $this->font($sheet, "{$c}5", ['bold' => true, 'size' => 9, 'color' => 'FFFFFFFF']);
            $this->align($sheet, "{$c}5:{$c}6", 'center', 'center', true);
            $sheet->getColumnDimension($c)->setWidth(22);
            $colIdx++;
        }

        // Question headers
        $qColMap = [];

        foreach ($panelTest->questions as $q) {
            $c  = $this->col($colIdx);
            $cN = $this->col($colIdx + 1);
            $sheet->mergeCells("{$c}5:{$cN}5");
            $sheet->setCellValue("{$c}5", $q->question_text);
            $this->fill($sheet, "{$c}5:{$cN}5", 'FF334155');
            $this->font($sheet, "{$c}5", ['bold' => true, 'size' => 9, 'color' => 'FFFFFFFF']);
            $this->align($sheet, "{$c}5:{$cN}5", 'center', 'center', true);
            $sheet->setCellValue("{$c}6", 'Jawaban');
            $sheet->setCellValue("{$cN}6", 'Catatan');
            $this->fill($sheet, "{$c}6:{$cN}6", 'FFF1F5F9');
            $this->font($sheet, "{$c}6:{$cN}6", ['bold' => true, 'size' => 9, 'color' => 'FF475569']);
            $this->align($sheet, "{$c}6:{$cN}6", 'center', 'center');
            $sheet->getColumnDimension($c)->setWidth(20);
            $sheet->getColumnDimension($cN)->setWidth(14);
            $qColMap[$q->id] = ['ans' => $c, 'note' => $cN, 'si' => null];
            $colIdx += 2;
        }

        foreach ($panelTest->samples as $si => $sample) {
            $sc = $this->palette[$si % 5];
            foreach ($sample->questions as $q) {
                $c  = $this->col($colIdx);
                $cN = $this->col($colIdx + 1);
                $sheet->mergeCells("{$c}5:{$cN}5");
                $sheet->setCellValue("{$c}5", '[' . strtoupper($sample->name) . '] ' . $q->question_text);
                $this->fill($sheet, "{$c}5:{$cN}5", $sc['dark']);
                $this->font($sheet, "{$c}5", ['bold' => true, 'size' => 9, 'color' => 'FFFFFFFF']);
                $this->align($sheet, "{$c}5:{$cN}5", 'center', 'center', true);
                $sheet->setCellValue("{$c}6", 'Jawaban');
                $sheet->setCellValue("{$cN}6", 'Catatan');
                $this->fill($sheet, "{$c}6:{$cN}6", $sc['light']);
                $this->font($sheet, "{$c}6:{$cN}6", ['bold' => true, 'size' => 9, 'color' => $sc['dark']]);
                $this->align($sheet, "{$c}6:{$cN}6", 'center', 'center');
                $sheet->getColumnDimension($c)->setWidth(20);
                $sheet->getColumnDimension($cN)->setWidth(14);
                $qColMap[$q->id] = ['ans' => $c, 'note' => $cN, 'si' => $si];
                $colIdx += 2;
            }
        }

        $lastCol = $this->col($colIdx - 1);
        $sheet->getRowDimension(5)->setRowHeight(40);
        $sheet->getRowDimension(6)->setRowHeight(18);

        // ===== Data rows =====
        $dataRow = 7;

        foreach ($sessions->values() as $i => $session) {
            $isEven = $i % 2 === 0;
            $bg     = $isEven ? 'FFF8FAFC' : 'FFFFFFFF';
            $col    = 1;

            // Fixed data
            if ($isInternal) {
                $sheet->setCellValue($this->col($col++) . $dataRow, $i + 1);
                $sheet->setCellValue($this->col($col++) . $dataRow, $session->perner_number ?? '-');
                $sheet->setCellValue($this->col($col++) . $dataRow, $session->respondent_name ?? '-');
                $sheet->setCellValue($this->col($col++) . $dataRow, $session->respondent_birth_year ?? '-');
                $sheet->setCellValue($this->col($col++) . $dataRow, $session->respondent_generation ?? '-');
                $sheet->setCellValue($this->col($col++) . $dataRow, $session->respondent_age ?? '-');
                $sheet->setCellValue($this->col($col++) . $dataRow, $session->respondent_gender ?? '-');
                $sheet->setCellValue($this->col($col++) . $dataRow, $session->completed_at?->format('d/m/Y H:i') ?? '-');
            } else {
                $sheet->setCellValue($this->col($col++) . $dataRow, $i + 1);
                $sheet->setCellValue($this->col($col++) . $dataRow, $session->user?->name ?? '-');
                $sheet->setCellValue($this->col($col++) . $dataRow, $session->user?->area_office ?? '-');
                $sheet->setCellValue($this->col($col++) . $dataRow, $session->area_promotion_name ?? '-');
                $sheet->setCellValue($this->col($col++) . $dataRow, $session->sp_name ?? '-');
                $sheet->setCellValue($this->col($col++) . $dataRow, $session->respondent_name ?? '-');
                $sheet->setCellValue($this->col($col++) . $dataRow, $session->respondent_birth_year ?? '-');
                $sheet->setCellValue($this->col($col++) . $dataRow, $session->respondent_generation ?? '-');
                $sheet->setCellValue($this->col($col++) . $dataRow, $session->respondent_age ?? '-');
                $sheet->setCellValue($this->col($col++) . $dataRow, $session->respondent_gender ?? '-');
                $sheet->setCellValue($this->col($col++) . $dataRow, $session->provinsi ?? '-');
                $sheet->setCellValue($this->col($col++) . $dataRow, $session->kota_kabupaten ?? '-');
                $sheet->setCellValue($this->col($col++) . $dataRow, $session->kecamatan ?? '-');
                $sheet->setCellValue($this->col($col++) . $dataRow, $session->kelurahan ?? '-');
                $sheet->setCellValue($this->col($col++) . $dataRow, $session->contact_description ?? '-');
                $sheet->setCellValue($this->col($col++) . $dataRow, $session->completed_at?->format('d/m/Y H:i') ?? '-');
            }

            // Fill warna fixed cols
            $fixedEndCol = $this->col($marketingColStart - 1);
            $this->fill($sheet, "A{$dataRow}:{$fixedEndCol}{$dataRow}", $bg);
            $this->font($sheet, "A{$dataRow}:{$fixedEndCol}{$dataRow}", ['size' => 10]);
            $this->align($sheet, "A{$dataRow}", 'center', 'center');

            // Marketing data (ungu muda jika ada data)
            $marketingValues = [
                $session->brand_aktivasi          ?? '-',
                $session->konsumen_gender          ?? '-',
                $session->konsumen_usia            ?? '-',
                $session->konsumen_profesi         ?? '-',
                $session->konsumen_pengeluaran     ?? '-',
                $session->konsumen_funnel          ?? '-',
                $session->alasan_tidak_konversi    ?? '-',
                $session->sumber_tahu_produk       ?? '-',
                $session->sumber_tahu_produk_other ?? '-',
                $session->brand_kompetitor         ?? '-',
                $session->brand_kompetitor_other   ?? '-',
                $session->alasan_kompetitor        ?? '-',
            ];

            $mCol = $marketingColStart;
            foreach ($marketingValues as $val) {
                $c      = $this->col($mCol);
                $hasDat = ($val && $val !== '-');
                $cellBg = $hasDat ? 'FFEDE9FE' : $bg;
                $cellFt = $hasDat ? 'FF5B21B6' : 'FFCBD5E1';
                $sheet->setCellValue("{$c}{$dataRow}", $val);
                $this->fill($sheet, "{$c}{$dataRow}", $cellBg);
                $this->font($sheet, "{$c}{$dataRow}", ['size' => 9, 'color' => $cellFt]);
                $this->align($sheet, "{$c}{$dataRow}", 'center', 'center', true);
                $mCol++;
            }

            // Jawaban pertanyaan
            foreach ($session->answers as $answer) {
                if (!$answer->question || !isset($qColMap[$answer->question_id])) continue;
                $map = $qColMap[$answer->question_id];
                $si  = $map['si'];
                $sc  = $si !== null ? $this->palette[$si % 5] : null;

                if ($answer->rating_value !== null) {
                    $val = str_repeat('⭐', $answer->rating_value) . ' (' . $answer->rating_value . '/5)';
                    $rBg = match(true) {
                        $answer->rating_value >= 5 => 'FFBBF7D0',
                        $answer->rating_value >= 4 => 'FFD1FAE5',
                        $answer->rating_value >= 3 => 'FFFEF3C7',
                        $answer->rating_value >= 2 => 'FFFED7AA',
                        default                    => 'FFFECACA',
                    };
                    $rFt = match(true) {
                        $answer->rating_value >= 4 => 'FF14532D',
                        $answer->rating_value >= 3 => 'FF854D0E',
                        default                    => 'FF991B1B',
                    };
                    $sheet->setCellValue($map['ans'] . $dataRow, $val);
                    $this->fill($sheet, $map['ans'] . $dataRow, $rBg);
                    $this->font($sheet, $map['ans'] . $dataRow, ['bold' => true, 'size' => 9, 'color' => $rFt]);
                    $this->align($sheet, $map['ans'] . $dataRow, 'center', 'center');
                } else {
                    $sheet->setCellValue($map['ans'] . $dataRow, $answer->answer_value ?? '-');
                    $cellBg = $sc ? $sc['xlight'] : $bg;
                    $cellFt = $sc ? $sc['dark']   : 'FF334155';
                    $this->fill($sheet, $map['ans'] . $dataRow, $cellBg);
                    $this->font($sheet, $map['ans'] . $dataRow, ['size' => 10, 'color' => $cellFt]);
                    $this->align($sheet, $map['ans'] . $dataRow, 'center', 'center');
                }

                $sheet->setCellValue($map['note'] . $dataRow, $answer->note ?? '');
                $this->fill($sheet, $map['note'] . $dataRow, $bg);
                $this->font($sheet, $map['note'] . $dataRow, ['size' => 9, 'italic' => true, 'color' => 'FF94A3B8']);
            }

            $sheet->getRowDimension($dataRow)->setRowHeight(20);
            $dataRow++;
        }

        // Border
        $this->border($sheet, "A5:{$lastCol}" . ($dataRow - 1), 'thin', 'FFE2E8F0');
        $sheet->getStyle("A5:{$lastCol}" . ($dataRow - 1))->applyFromArray([
            'borders' => ['outline' => [
                'borderStyle' => 'medium',
                'color'       => ['argb' => 'FF94A3B8'],
            ]],
        ]);
        $sheet->freezePane('A7');
    }
}