<?php
require_once '../includes/constants.php';
require_once '../includes/session.php';

if (!isset($_SESSION['admin_id']) || !in_array($_SESSION['admin_role'], ['manager','superadmin'])) {
    die("Access denied");
}
?>
<!DOCTYPE html>
<html>
<head>
    <title><?php echo SITE_TITLE . ' | Affiliate Export'; ?></title>
    <link rel="stylesheet" href="css/admin.css">
</head>
<body>
<div class="container">
<?php
$vertical = $_GET['vertical'] ?? null;
$flagged = $_GET['flagged'] ?? null;
$severity = $_GET['severity'] ?? null;

// Build query
$query = "SELECT a.id, a.name, a.email, a.status, a.vertical,
    (SELECT COUNT(*) FROM conversions c WHERE c.affiliate_id = a.id) AS conv_count,
    (SELECT SUM(amount) FROM affiliate_payouts p WHERE p.affiliate_id = a.id) AS payout_total,
    (SELECT COUNT(*) FROM affiliate_tests t WHERE t.affiliate_id = a.id) AS test_count,
    (SELECT COUNT(*) FROM affiliate_flags f WHERE f.affiliate_id = a.id) AS flag_count
    FROM affiliates a WHERE 1";
$params = [];

if ($vertical) {
    $query .= " AND a.vertical = ?";
    $params[] = $vertical;
}
if ($flagged === 'yes') {
    $query .= " AND EXISTS (SELECT 1 FROM affiliate_flags f WHERE f.affiliate_id = a.id)";
}
if ($severity) {
    $query .= " AND EXISTS (SELECT 1 FROM affiliate_flags f WHERE f.affiliate_id = a.id AND f.severity = ?)";
    $params[] = $severity;
}

$query .= " ORDER BY a.name ASC";
$stmt = $pdo->prepare($query);
$stmt->execute($params);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Export CSV
if (isset($_GET['export'])) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename=affiliate_export.csv');
    echo "ID,Name,Email,Status,Vertical,Conversions,Payout,Tests,Flags\n";
    foreach ($rows as $r) {
        echo "\"{$r['id']}\",\"{$r['name']}\",\"{$r['email']}\",\"{$r['status']}\",\"{$r['vertical']}\",\"{$r['conv_count']}\",\"{$r['payout_total']}\",\"{$r['test_count']}\",\"{$r['flag_count']}\"\n";
    }
    exit;
}

// UI
echo "<h2>📤 Affiliate Export</h2>
<form method='get'>
    <input name='vertical' placeholder='Vertical' value='" . htmlspecialchars($vertical) . "'>
    <select name='flagged'>
        <option value=''>All</option>
        <option value='yes'" . ($flagged === 'yes' ? ' selected' : '') . ">Flagged Only</option>
    </select>
    <select name='severity'>
        <option value=''>Any Severity</option>
        <option value='low'" . ($severity === 'low' ? ' selected' : '') . ">Low</option>
        <option value='medium'" . ($severity === 'medium' ? ' selected' : '') . ">Medium</option>
        <option value='high'" . ($severity === 'high' ? ' selected' : '') . ">High</option>
    </select>
    <button type='submit'>Filter</button>
    <button onclick=\"window.location='?vertical=$vertical&flagged=$flagged&severity=$severity&export=1';return false;\">Export CSV</button>
</form>";

echo "<table><tr>
    <th>ID</th><th>Name</th><th>Email</th><th>Status</th><th>Vertical</th>
    <th>Conversions</th><th>Payout</th><th>Tests</th><th>Flags</th>
</tr>";

$totalConv = 0;
$totalPayout = 0;
$totalTests = 0;
$totalFlags = 0;

foreach ($rows as $r) {
    $totalConv += $r['conv_count'];
    $totalPayout += $r['payout_total'];
    $totalTests += $r['test_count'];
    $totalFlags += $r['flag_count'];

    echo "<tr>
        <td>" . htmlspecialchars($r['id']) . "</td>
        <td>" . htmlspecialchars($r['name']) . "</td>
        <td>" . htmlspecialchars($r['email']) . "</td>
        <td>" . htmlspecialchars($r['status']) . "</td>
        <td>" . htmlspecialchars($r['vertical']) . "</td>
        <td>{$r['conv_count']}</td>
        <td>$" . number_format($r['payout_total'], 2) . "</td>
        <td>{$r['test_count']}</td>
        <td>{$r['flag_count']}</td>
    </tr>";
}

echo "<tr><td colspan='5'><strong>Totals</strong></td>
      <td><strong>$totalConv</strong></td>
      <td><strong>$" . number_format($totalPayout, 2) . "</strong></td>
      <td><strong>$totalTests</strong></td>
      <td><strong>$totalFlags</strong></td></tr>";
echo "</table>";
?>
</div>
</body>
</html>