<?php
require __DIR__ . '/vendor/autoload.php';
require __DIR__ . '/includes/auth.php';
requireLogin();

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Cell\DataType;

set_time_limit(120);
ini_set('memory_limit', '512M');

$pdo      = getPDO();
$baseYear = '2011-12';

$codes           = array_filter(array_map('trim', explode(',', $_GET['codes'] ?? $_POST['codes'] ?? '')));
$frequency       = $_GET['frequency']    ?? $_POST['frequency']    ?? 'monthly';
$seriesStart     = (int)($_GET['series_start'] ?? $_POST['series_start'] ?? 4);
$assessYear      = (int)($_GET['assess_year']  ?? $_POST['assess_year']  ?? 0);
$assessMonth     = (int)($_GET['assess_month'] ?? $_POST['assess_month'] ?? 0);
$assessPeriodNum = $frequency === 'monthly'
    ? $assessMonth
    : (int)($_GET['assess_period'] ?? $_POST['assess_period'] ?? 1);
$showIndex         = isset($_GET['show_index'])         || isset($_POST['show_index']);
$showExtrapolation = isset($_GET['show_extrapolation']) || isset($_POST['show_extrapolation']);
$beginMonth        = (int)($_GET['begin_month'] ?? $_POST['begin_month'] ?? 0);
$beginYear         = (int)($_GET['begin_year']  ?? $_POST['begin_year']  ?? 0);

$monthlyInvalid = $frequency === 'monthly' && ($assessMonth < 1 || $assessMonth > 12);
if (!$codes || $assessYear < 2000 || $monthlyInvalid) {
    http_response_code(400);
    die('Missing or invalid parameters.');
}

$monthNames = ['','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'];

// Fetch commodity details
$ph = implode(',', array_fill(0, count($codes), '?'));
$stmt = $pdo->prepare("SELECT comm_code, comm_name, level, display_name FROM commodities WHERE comm_code IN ($ph) AND base_year=?");
$stmt->execute(array_merge($codes, [$baseYear]));
$commMap = [];
foreach ($stmt->fetchAll() as $r) { $commMap[$r['comm_code']] = $r; }

// Determine assessment period label and months to average
if ($frequency === 'monthly') {
    $assessLabel        = $monthNames[$assessPeriodNum] . ' ' . $assessYear;
    $assessPeriodMonths = [['year' => $assessYear, 'month' => $assessPeriodNum]];
} elseif ($frequency === 'quarterly') {
    $startOff    = ($assessPeriodNum - 1) * 3;
    $assessLabel = "Q{$assessPeriodNum} - {$assessYear}";
    $assessPeriodMonths = [];
    for ($i = 0; $i < 3; $i++) {
        $m = (($seriesStart - 1 + $startOff + $i) % 12) + 1;
        $y = $m >= $seriesStart ? $assessYear : $assessYear + 1;
        $assessPeriodMonths[] = ['year' => $y, 'month' => $m];
    }
} elseif ($frequency === 'half-yearly') {
    $startOff    = ($assessPeriodNum - 1) * 6;
    $assessLabel = "H{$assessPeriodNum} - {$assessYear}";
    $assessPeriodMonths = [];
    for ($i = 0; $i < 6; $i++) {
        $m = (($seriesStart - 1 + $startOff + $i) % 12) + 1;
        $y = $m >= $seriesStart ? $assessYear : $assessYear + 1;
        $assessPeriodMonths[] = ['year' => $y, 'month' => $m];
    }
} else { // annually
    $assessLabel = (string)$assessYear;
    $assessPeriodMonths = [];
    for ($i = 0; $i < 12; $i++) {
        $m = (($seriesStart - 1 + $i) % 12) + 1;
        $y = $m >= $seriesStart ? $assessYear : $assessYear + 1;
        $assessPeriodMonths[] = ['year' => $y, 'month' => $m];
    }
}

// Fetch assessment-period indices
$assessRawPerYm = [];
if ($frequency === 'monthly') {
    $stmt = $pdo->prepare("SELECT comm_code, index_value FROM monthly_indices WHERE comm_code IN ($ph) AND base_year=? AND period_year=? AND period_month=?");
    $stmt->execute(array_merge($codes, [$baseYear, $assessYear, $assessPeriodNum]));
    $assessIndices = [];
    foreach ($stmt->fetchAll() as $r) { $assessIndices[$r['comm_code']] = (float)$r['index_value']; }
} else {
    $mConds  = implode(' OR ', array_fill(0, count($assessPeriodMonths), '(period_year=? AND period_month=?)'));
    $mParams = [];
    foreach ($assessPeriodMonths as $pm) { $mParams[] = $pm['year']; $mParams[] = $pm['month']; }
    $stmt = $pdo->prepare("SELECT comm_code, period_year, period_month, index_value FROM monthly_indices WHERE comm_code IN ($ph) AND base_year=? AND ($mConds) AND index_value IS NOT NULL");
    $stmt->execute(array_merge($codes, [$baseYear], $mParams));
    $assessRaw      = [];
    $assessRawPerYm = [];
    foreach ($stmt->fetchAll() as $r) {
        $assessRaw[$r['comm_code']][] = (float)$r['index_value'];
        $assessRawPerYm[$r['comm_code']][(int)$r['period_year'] * 100 + (int)$r['period_month']] = (float)$r['index_value'];
    }
    $assessIndices = [];
    foreach ($assessRaw as $c => $vals) { $assessIndices[$c] = count($vals) ? round(array_sum($vals) / count($vals), 2) : null; }
}

// Get latest available DB period
$rng      = $pdo->query("SELECT MAX(period_year * 100 + period_month) AS max_ym FROM monthly_indices WHERE index_value IS NOT NULL AND base_year='$baseYear'")->fetch();
$maxDbYm  = (int)($rng['max_ym'] ?? 0);
$maxYear  = intdiv($maxDbYm, 100);
$maxMonth = $maxDbYm % 100;

// Compute last ym of the assessment period and apply guard
$assessLastYm = 0;
foreach ($assessPeriodMonths as $apm) {
    $assessLastYm = max($assessLastYm, $apm['year'] * 100 + $apm['month']);
}
if ($showExtrapolation && $assessLastYm <= $maxDbYm) {
    $showExtrapolation = false;
}

// Extrapolation Part A: compute CAGR, override $assessIndices BEFORE rows building
$extrapolatedValues = [];
$extrapMonths       = [];
if ($showExtrapolation && $beginMonth >= 1 && $beginMonth <= 12 && $beginYear >= 2000) {
    $ey = $maxYear; $em_x = $maxMonth;
    while (true) {
        $em_x++; if ($em_x > 12) { $em_x = 1; $ey++; }
        if ($ey * 100 + $em_x > $assessLastYm) break;
        $extrapMonths[] = ['year' => $ey, 'month' => $em_x];
    }

    if ($extrapMonths) {
        $stmt = $pdo->prepare("SELECT comm_code, index_value FROM monthly_indices WHERE comm_code IN ($ph) AND base_year=? AND period_year=? AND period_month=?");
        $stmt->execute(array_merge($codes, [$baseYear, $beginYear, $beginMonth]));
        $beginIndices = [];
        foreach ($stmt->fetchAll() as $r) { $beginIndices[$r['comm_code']] = (float)$r['index_value']; }

        $stmt = $pdo->prepare("SELECT comm_code, index_value FROM monthly_indices WHERE comm_code IN ($ph) AND base_year=? AND period_year=? AND period_month=?");
        $stmt->execute(array_merge($codes, [$baseYear, $maxYear, $maxMonth]));
        $endIndices = [];
        foreach ($stmt->fetchAll() as $r) { $endIndices[$r['comm_code']] = (float)$r['index_value']; }

        $beginPeriodYrs = $beginYear + $beginMonth / 12;
        $endPeriodYrs   = $maxYear   + $maxMonth   / 12;
        $periodDiff     = $endPeriodYrs - $beginPeriodYrs;

        foreach ($codes as $code) {
            $bIdx = $beginIndices[$code] ?? null;
            $eIdx = $endIndices[$code]   ?? null;
            if ($bIdx !== null && $eIdx !== null && $bIdx > 0 && $periodDiff > 0) {
                $cagr = pow($eIdx / $bIdx, 1 / $periodDiff) - 1;
                foreach ($extrapMonths as $ep) {
                    $epYrs = $ep['year'] + $ep['month'] / 12;
                    $extrapolatedValues[$code][$ep['year'] * 100 + $ep['month']] =
                        round($eIdx * pow(1 + $cagr, $epYrs - $endPeriodYrs), 2);
                }
            }
        }

        // Override $assessIndices with extrapolated assessment period values
        foreach ($codes as $code) {
            if (empty($extrapolatedValues[$code])) continue;
            if ($frequency === 'monthly') {
                $ym = $assessYear * 100 + $assessPeriodNum;
                $assessIndices[$code] = $extrapolatedValues[$code][$ym] ?? null;
            } else {
                $vals = [];
                foreach ($assessPeriodMonths as $apm) {
                    $apmYm = $apm['year'] * 100 + $apm['month'];
                    $v = $apmYm <= $maxDbYm
                        ? ($assessRawPerYm[$code][$apmYm] ?? null)
                        : ($extrapolatedValues[$code][$apmYm] ?? null);
                    if ($v !== null) $vals[] = $v;
                }
                $assessIndices[$code] = count($vals) ? round(array_sum($vals) / count($vals), 2) : null;
            }
        }
    }
}

// Fetch all monthly indices
$stmt = $pdo->prepare("SELECT comm_code, period_year, period_month, index_value, is_provisional FROM monthly_indices WHERE comm_code IN ($ph) AND base_year=? AND index_value IS NOT NULL ORDER BY period_year, period_month");
$stmt->execute(array_merge($codes, [$baseYear]));
$allData = [];
foreach ($stmt->fetchAll() as $r) { $allData[$r['comm_code']][] = $r; }

// Build result rows (same logic as calculate.php)
$rows = [];
foreach ($codes as $code) {
    $assessIdx = $assessIndices[$code] ?? null;
    if ($assessIdx === null) continue;
    $data = $allData[$code] ?? [];
    $comm = $commMap[$code] ?? ['comm_name'=>$code, 'level'=>0, 'display_name'=>$code];

    if ($frequency === 'monthly') {
        foreach ($data as $r) {
            $val = (float)$r['index_value'];
            $factor = $val > 0 ? round($assessIdx / $val, 4) : null;
            $rows[] = [
                'level'        => $comm['level'],
                'display_name' => $comm['display_name'],
                'comm_name'    => $comm['comm_name'],
                'comm_code'    => $code,
                'period'       => sprintf('INDX%02d%04d', (int)$r['period_month'], (int)$r['period_year']),
                'period_label' => $monthNames[(int)$r['period_month']] . ' ' . $r['period_year'],
                'index_value'  => $val,
                'factor'       => $factor,
                'sort'         => (int)$r['period_year'] * 100 + (int)$r['period_month'],
            ];
        }
    } else {
        $grouped   = [];
        $startStep = $frequency === 'quarterly' ? 3 : ($frequency === 'half-yearly' ? 6 : 0);
        $periodLen = $frequency === 'quarterly' ? 3 : ($frequency === 'half-yearly' ? 6 : 12);
        foreach ($data as $r) {
            $y = (int)$r['period_year'];
            $m = (int)$r['period_month'];
            $offset = ($m - $seriesStart + 12) % 12;
            $fy = $m >= $seriesStart ? $y : $y - 1;
            if ($frequency === 'quarterly') {
                $n = intdiv($offset, 3) + 1;
                $key = "Q{$n} - {$fy}"; $sort = $fy * 10 + $n;
                $grouped[$key]['period_num'] = $n;
            } elseif ($frequency === 'half-yearly') {
                $n = $offset < 6 ? 1 : 2;
                $key = "H{$n} - {$fy}"; $sort = $fy * 10 + $n;
                $grouped[$key]['period_num'] = $n;
            } else {
                $key = (string)$fy; $sort = $fy;
                $grouped[$key]['period_num'] = 1;
            }
            $grouped[$key]['values'][] = (float)$r['index_value'];
            $grouped[$key]['sort']     = $sort;
            $grouped[$key]['fy']       = $fy;
        }
        // Fill extrapolated months into partially-covered periods
        if ($showExtrapolation && !empty($extrapolatedValues[$code])) {
            foreach ($grouped as $gKey => &$gData) {
                $gFy      = $gData['fy'];
                $gPNum    = $gData['period_num'];
                $gStartOff = ($gPNum - 1) * $startStep;
                for ($gi = 0; $gi < $periodLen; $gi++) {
                    $gm  = (($seriesStart - 1 + $gStartOff + $gi) % 12) + 1;
                    $gy  = $gm >= $seriesStart ? $gFy : $gFy + 1;
                    $gym = $gy * 100 + $gm;
                    if ($gym <= $maxDbYm) continue;
                    $ev = $extrapolatedValues[$code][$gym] ?? null;
                    if ($ev !== null) { $gData['values'][] = $ev; $gData['extrap'] = true; }
                }
            }
            unset($gData);
        }
        foreach ($grouped as $label => $g) {
            $avg = round(array_sum($g['values']) / count($g['values']), 2);
            $factor = $avg > 0 ? round($assessIdx / $avg, 4) : null;
            $rows[] = [
                'level'        => $comm['level'],
                'display_name' => $comm['display_name'],
                'comm_name'    => $comm['comm_name'],
                'comm_code'    => $code,
                'period'       => $label,
                'period_label' => $label,
                'index_value'  => $avg,
                'factor'       => $factor,
                'sort'         => $g['sort'],
                'extrap'       => $g['extrap'] ?? false,
            ];
        }
    }
}

// Drop periods that come after the assessment period
if ($frequency === 'monthly') {
    $assessPeriodSort = $assessYear * 100 + $assessPeriodNum;
} elseif ($frequency === 'quarterly' || $frequency === 'half-yearly') {
    $assessPeriodSort = $assessYear * 10 + $assessPeriodNum;
} else {
    $assessPeriodSort = $assessYear;
}
$rows = array_values(array_filter($rows, fn($r) => $r['sort'] <= $assessPeriodSort));

usort($rows, function ($a, $b) {
    $c = strcmp($a['comm_code'], $b['comm_code']);
    return $c !== 0 ? $c : $b['sort'] - $a['sort'];
});

// Group rows by commodity, preserving sort order
$byCommodity = [];
foreach ($rows as $row) {
    $byCommodity[$row['comm_code']][] = $row;
}
$commodities = array_keys($byCommodity);

// Collect all unique periods in descending sort order (union across all commodities)
$periodOrder = []; // sort_key => period label
foreach ($rows as $row) {
    $periodOrder[$row['sort']] = $row['period'];
}
krsort($periodOrder);

// Build lookup: comm_code => [sort_key => [index_value, factor]]
$dataMap = [];
foreach ($rows as $row) {
    $dataMap[$row['comm_code']][$row['sort']] = [
        'iv' => $row['index_value'],
        'f'  => $row['factor'],
    ];
}

// Extrapolation Part B: add extrapolated entries to $periodOrder and $dataMap
if ($showExtrapolation && $extrapMonths) {
    if ($frequency === 'monthly') {
        foreach (array_reverse($extrapMonths) as $ep) {
            $ym  = $ep['year'] * 100 + $ep['month'];
            $lbl = $monthNames[$ep['month']] . ' ' . $ep['year'];
            $periodOrder = [$ym => $lbl] + $periodOrder;
            foreach ($codes as $code) {
                $ev   = $extrapolatedValues[$code][$ym] ?? null;
                $aIdx = $assessIndices[$code] ?? null;
                if ($ev !== null && $aIdx !== null && $ev > 0) {
                    $dataMap[$code][$ym] = ['iv' => $ev, 'f' => round($aIdx / $ev, 4), 'extrap' => true];
                }
            }
        }
    } else {
        // Q/H/Y: group extrapolated months into period buckets, add missing periods only
        $startStep   = $frequency === 'quarterly' ? 3 : ($frequency === 'half-yearly' ? 6 : 0);
        $periodLen   = $frequency === 'quarterly' ? 3 : ($frequency === 'half-yearly' ? 6 : 12);
        $aAssessSort = $frequency === 'annually' ? $assessYear : $assessYear * 10 + $assessPeriodNum;
        foreach ($extrapMonths as $ep) {
            $em = $ep['month']; $ey = $ep['year'];
            $offset = ($em - $seriesStart + 12) % 12;
            $pFy    = $em >= $seriesStart ? $ey : $ey - 1;
            if ($frequency === 'quarterly') {
                $pNum  = intdiv($offset, 3) + 1;
                $pKey  = "Q{$pNum} - {$pFy}"; $pSort = $pFy * 10 + $pNum;
            } elseif ($frequency === 'half-yearly') {
                $pNum  = $offset < 6 ? 1 : 2;
                $pKey  = "H{$pNum} - {$pFy}"; $pSort = $pFy * 10 + $pNum;
            } else {
                $pNum  = 1; $pKey = (string)$pFy; $pSort = $pFy;
            }
            if (isset($periodOrder[$pSort]) || $pSort > $aAssessSort) continue;
            $periodOrder[$pSort] = $pKey;
            $pStartOff = ($pNum - 1) * $startStep;
            foreach ($codes as $code) {
                $pVals = [];
                for ($gi = 0; $gi < $periodLen; $gi++) {
                    $gm  = (($seriesStart - 1 + $pStartOff + $gi) % 12) + 1;
                    $gy  = $gm >= $seriesStart ? $pFy : $pFy + 1;
                    $gym = $gy * 100 + $gm;
                    $ev  = $gym <= $maxDbYm
                        ? ($assessRawPerYm[$code][$gym] ?? null)
                        : ($extrapolatedValues[$code][$gym] ?? null);
                    if ($ev !== null) $pVals[] = $ev;
                }
                if ($pVals) {
                    $avg  = round(array_sum($pVals) / count($pVals), 2);
                    $aIdx = $assessIndices[$code] ?? null;
                    $dataMap[$code][$pSort] = [
                        'iv' => $avg,
                        'f'  => ($avg > 0 && $aIdx !== null) ? round($aIdx / $avg, 4) : null,
                        'extrap' => true,
                    ];
                }
            }
        }
        krsort($periodOrder);
    }
}

// Create Excel
// Layout: col A = shared Period, then 2 cols per commodity (Index Value + Index Factor)
$spreadsheet = new Spreadsheet();
$sheet       = $spreadsheet->getActiveSheet();
$sheet->setTitle('Index Factor');

$blue  = '4472C4';
$gray  = 'EEEEEE';
$white = 'FFFFFF';
$black = '000000';
$thin  = ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => $black]];

$blueStyle = [
    'font'      => ['bold' => true, 'color' => ['rgb' => $white]],
    'fill'      => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $blue]],
    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
    'borders'   => ['allBorders' => $thin],
];
$mergedValueStyle = [
    'font'      => ['bold' => false, 'color' => ['rgb' => $black]],
    'fill'      => ['fillType' => Fill::FILL_NONE],
    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
    'borders'   => ['outline' => $thin],
];

// Helper: column letter for a commodity given showIndex mode
// showIndex=true  → 2 cols each: IV=2+n*2, IF=3+n*2
// showIndex=false → 1 col each:          IF=2+n
function commCol(int $n, bool $showIndex, bool $iv): string {
    if ($showIndex) {
        return Coordinate::stringFromColumnIndex($iv ? 2 + $n * 2 : 3 + $n * 2);
    }
    return Coordinate::stringFromColumnIndex(2 + $n); // IF only
}

// ── Rows 1–2: labels + commodity code/name cells ─────────────────────────────
$sheet->setCellValue('A1', 'COMM_CODE');
$sheet->getStyle('A1')->applyFromArray($blueStyle);
$sheet->setCellValue('A2', 'COMM_NAME');
$sheet->getStyle('A2')->applyFromArray($blueStyle);

foreach ($commodities as $n => $code) {
    $comm  = $commMap[$code] ?? ['comm_name' => $code];
    $colIF = commCol($n, $showIndex, false);

    if ($showIndex) {
        $colIV = commCol($n, $showIndex, true);
        // Merge Index Value + Index Factor cols for code/name
        $sheet->mergeCells("{$colIV}1:{$colIF}1");
        $sheet->setCellValueExplicit("{$colIV}1", $code, DataType::TYPE_STRING);
        $sheet->getStyle("{$colIV}1:{$colIF}1")->applyFromArray($mergedValueStyle);

        $sheet->mergeCells("{$colIV}2:{$colIF}2");
        $sheet->setCellValue("{$colIV}2", $comm['comm_name']);
        $sheet->getStyle("{$colIV}2:{$colIF}2")->applyFromArray($mergedValueStyle);
    } else {
        // Single column — no merge needed
        $sheet->setCellValueExplicit("{$colIF}1", $code, DataType::TYPE_STRING);
        $sheet->getStyle("{$colIF}1")->applyFromArray($mergedValueStyle);
        $sheet->setCellValue("{$colIF}2", $comm['comm_name']);
        $sheet->getStyle("{$colIF}2")->applyFromArray($mergedValueStyle);
    }
}

// ── Row 3: column headers ─────────────────────────────────────────────────────
$headerRow = 3;
$sheet->setCellValue("A{$headerRow}", 'Period');
$sheet->getStyle("A{$headerRow}")->applyFromArray($blueStyle);

foreach ($commodities as $n => $code) {
    $colIF = commCol($n, $showIndex, false);
    if ($showIndex) {
        $colIV = commCol($n, $showIndex, true);
        $sheet->setCellValue("{$colIV}{$headerRow}", 'Index Value');
        $sheet->getStyle("{$colIV}{$headerRow}")->applyFromArray($blueStyle);
    }
    $sheet->setCellValue("{$colIF}{$headerRow}", 'Index Factor');
    $sheet->getStyle("{$colIF}{$headerRow}")->applyFromArray($blueStyle);
}

// ── Rows after headers: data ──────────────────────────────────────────────────
$amberFill = ['fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'FFF3CD']]];
$dr = $headerRow + 1;
foreach ($periodOrder as $sort => $periodLabel) {
    $isExtrap = false;
    $sheet->setCellValueExplicit("A{$dr}", $periodLabel, DataType::TYPE_STRING);

    foreach ($commodities as $n => $code) {
        $d     = $dataMap[$code][$sort] ?? null;
        $colIF = commCol($n, $showIndex, false);
        if ($d !== null) {
            if (!empty($d['extrap'])) $isExtrap = true;
            if ($showIndex) {
                $colIV = commCol($n, $showIndex, true);
                $sheet->setCellValue("{$colIV}{$dr}", $d['iv']);
                $sheet->getStyle("{$colIV}{$dr}")->getNumberFormat()->setFormatCode('0.00');
            }
            $sheet->setCellValue("{$colIF}{$dr}", $d['f']);
            $sheet->getStyle("{$colIF}{$dr}")->getNumberFormat()->setFormatCode('0.0000');
        }
    }

    // Amber background for extrapolated rows
    if ($isExtrap) {
        $lastDataCol = Coordinate::stringFromColumnIndex($showIndex ? 1 + count($commodities) * 2 : 1 + count($commodities));
        $sheet->getStyle("A{$dr}:{$lastDataCol}{$dr}")->applyFromArray($amberFill);
    }
    $dr++;
}

// ── Borders + gray fill ───────────────────────────────────────────────────────
$totalCols = $showIndex ? 1 + count($commodities) * 2 : 1 + count($commodities);
$lastCol   = Coordinate::stringFromColumnIndex($totalCols);
$dataStart = $headerRow + 1;
if ($dr > $dataStart) {
    $lastDataRow = $dr - 1;
    $sheet->getStyle("A{$dataStart}:{$lastCol}{$lastDataRow}")->applyFromArray([
        'borders' => ['allBorders' => $thin],
    ]);
    foreach ($commodities as $n => $code) {
        if ($n % 2 === 0) {
            $colIF    = commCol($n, $showIndex, false);
            $grayFrom = $showIndex ? commCol($n, $showIndex, true) : $colIF;
            $sheet->getStyle("{$grayFrom}{$dataStart}:{$colIF}{$lastDataRow}")->applyFromArray([
                'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $gray]],
            ]);
        }
    }
}

// Auto-width for all used columns
for ($i = 1; $i <= $totalCols; $i++) {
    $sheet->getColumnDimension(Coordinate::stringFromColumnIndex($i))->setAutoSize(true);
}

// Output
$safeLabel = preg_replace('/[^A-Za-z0-9_\-]/', '_', $assessLabel);
$filename  = "IndexFactor_{$frequency}_{$safeLabel}.xlsx";
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Cache-Control: max-age=0');

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