<?php
declare(strict_types=1);

/* ================= AUTH ================= */
require_once __DIR__ . '/../../security/auth_guard.php';

/* ================= CORE ================= */
require_once __DIR__ . '/../../config/database.php';

$db = Database::getInstance();

/* ================= FILTERS (SAME AS LIST API) ================= */
$status   = $_GET['status'] ?? null;
$search   = trim($_GET['search'] ?? '');
$fromDate = $_GET['from_date'] ?? null;
$toDate   = $_GET['to_date'] ?? null;

$where = [];
$params = [];

if ($status === 'active') {
    $where[] = 'is_active = 1';
}
if ($status === 'inactive') {
    $where[] = 'is_active = 0';
}

if ($search !== '') {
    $where[] = '(email LIKE :search OR name LIKE :search)';
    $params['search'] = '%' . $search . '%';
}

if ($fromDate) {
    $where[] = 'DATE(subscribed_at) >= :from_date';
    $params['from_date'] = $fromDate;
}

if ($toDate) {
    $where[] = 'DATE(subscribed_at) <= :to_date';
    $params['to_date'] = $toDate;
}

$whereSql = $where ? 'WHERE ' . implode(' AND ', $where) : '';

/* ================= QUERY ================= */
$stmt = $db->prepare("
    SELECT
        name,
        email,
        is_active,
        is_verified,
        source_page_url,
        referrer_url,
        source,
        medium,
        campaign,
        ip_address,
        subscribed_at,
        unsubscribed_at
    FROM website_subscribers
    $whereSql
    ORDER BY subscribed_at DESC
");

$stmt->execute($params);

/* ================= CSV HEADERS ================= */
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=subscribers_' . date('Ymd_His') . '.csv');

$output = fopen('php://output', 'w');

/* ================= CSV COLUMNS ================= */
fputcsv($output, [
    'Name',
    'Email',
    'Status',
    'Verified',
    'Source Page',
    'Referrer',
    'UTM Source',
    'UTM Medium',
    'UTM Campaign',
    'IP Address',
    'Subscribed At',
    'Unsubscribed At'
]);

/* ================= CSV ROWS ================= */
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

    /* Prevent CSV injection */
    foreach ($row as &$value) {
        if (is_string($value) && preg_match('/^[=+\-@]/', $value)) {
            $value = "'" . $value;
        }
    }

    fputcsv($output, [
        $row['name'],
        $row['email'],
        $row['is_active'] ? 'Active' : 'Inactive',
        $row['is_verified'] ? 'Yes' : 'No',
        $row['source_page_url'],
        $row['referrer_url'],
        $row['source'],
        $row['medium'],
        $row['campaign'],
        $row['ip_address'],
        $row['subscribed_at'],
        $row['unsubscribed_at']
    ]);
}

fclose($output);
exit;
