<?php
require '../vendor/autoload.php'; // PhpSpreadsheet via Composer

include("core/init.php");

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Get location filter from URL
$location = isset($_GET['location']) ? trim($_GET['location']) : '';

// Base query
$sql = "SELECT email, location_name, subscribed_at FROM newsletter_subscribers";

// Add filter if location is passed
if (!empty($location)) {
    // Use addslashes as fallback if no escape method
    $safeLocation = addslashes($location);
    $sql .= " WHERE location_name = '{$safeLocation}'";
}

$sql .= " ORDER BY subscribed_at DESC";

// Fetch data
$db = Db::getInstance();
$results = $db->get_results($sql);

if (empty($results)) {
    die("No data found.");
}

// Create spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Set column headers
$sheet->setCellValue('A1', 'Email');
$sheet->setCellValue('B1', 'Location');
$sheet->setCellValue('C1', 'Subscribed At');

// Make headers bold
$headerStyle = [
    'font' => [
        'bold' => true
    ]
];
$sheet->getStyle('A1:C1')->applyFromArray($headerStyle);

// Add data rows
$row = 2;
foreach ($results as $record) {
    $date = new DateTime($record["subscribed_at"]);

    $sheet->setCellValue("A$row", $record['email']);
    $sheet->setCellValue("B$row", $record['location_name']);
    $sheet->setCellValue("C$row", $date->format("Y-m-d h:i a"));
    $row++;
}

// Download headers
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="newsletter_subscribers_' . date("Y-m-d-h-i-A") . '.xlsx"');
header('Cache-Control: max-age=0');

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