<?php
// api/guests_export.php — Download filtered guest lists as CSV or Excel
require_once __DIR__ . '/../config.php';
startSecureSession();
if (empty($_SESSION['admin_id'])) { http_response_code(401); exit('Unauthorized'); }

$adminId = (int)$_SESSION['admin_id'];
$eventId = (int)($_GET['event_id'] ?? 0);
$filter  = $_GET['filter']  ?? 'all';
$format  = $_GET['format']  ?? 'csv';

if (!$eventId) { http_response_code(400); exit('No event_id'); }

$db = db();
$ev = $db->prepare('SELECT * FROM events WHERE id=? AND admin_id=?');
$ev->execute([$eventId, $adminId]);
$event = $ev->fetch();
if (!$event) { http_response_code(404); exit('Event not found'); }

// Detect optional columns
$colCheck       = $db->query("SHOW COLUMNS FROM guests")->fetchAll(PDO::FETCH_COLUMN);
$hasEmailSent   = in_array('email_sent',      $colCheck);
$hasEmailSentAt = in_array('email_sent_at',   $colCheck);
$hasCheckMethod = in_array('check_in_method', $colCheck);

$emailSentCol   = $hasEmailSent   ? 'g.email_sent'      : '0';
$emailSentAtCol = $hasEmailSentAt ? 'g.email_sent_at'   : 'NULL';
$checkMethodCol = $hasCheckMethod ? 'g.check_in_method' : 'NULL';

// Check if wa_send_log table exists
$waTbl = $db->query("SHOW TABLES LIKE 'wa_send_log'")->fetchColumn();

if ($waTbl) {
    $waSentAtExpr = "COALESCE(
        (SELECT w.sent_at FROM wa_send_log w WHERE w.guest_id=g.id AND w.status='sent' ORDER BY w.sent_at DESC LIMIT 1),
        g.invite_sent_at
    )";
    $waStatusExpr = "COALESCE(
        (SELECT w.status FROM wa_send_log w WHERE w.guest_id=g.id ORDER BY w.sent_at DESC LIMIT 1),
        CASE WHEN g.invite_sent=1 THEN 'sent' ELSE 'pending' END
    )";
    $waErrorExpr  = "COALESCE(
        (SELECT w.error_msg FROM wa_send_log w WHERE w.guest_id=g.id AND w.status='failed' ORDER BY w.sent_at DESC LIMIT 1),
        NULL
    )";
} else {
    $waSentAtExpr = 'g.invite_sent_at';
    $waStatusExpr = "CASE WHEN g.invite_sent=1 THEN 'sent' ELSE 'pending' END";
    $waErrorExpr  = 'NULL';
}

$sql = "SELECT
    g.name, g.email, g.phone, g.table_number, g.seat_number, g.category,
    g.invite_sent,
    g.invite_sent_at,
    ({$waSentAtExpr})  AS wa_sent_at,
    ({$waStatusExpr})  AS wa_last_status,
    ({$waErrorExpr})   AS wa_error,
    ({$emailSentCol})  AS email_sent,
    ({$emailSentAtCol}) AS email_sent_at,
    g.checked_in,
    g.check_in_time,
    ({$checkMethodCol}) AS check_in_method,
    c.name AS checked_by_name,
    CASE WHEN g.invite_path IS NOT NULL AND g.invite_path!='' THEN 'Yes' ELSE 'No' END AS has_ticket,
    CASE WHEN g.checked_in=1 THEN 'Yes' ELSE 'No' END AS attended
FROM guests g
LEFT JOIN checkers c ON c.id=g.checked_by
WHERE g.event_id=?";

$params = [$eventId];

switch ($filter) {
    case 'sent':      $sql .= ' AND g.invite_sent=1'; break;
    case 'unsent':    $sql .= ' AND g.invite_sent=0 AND g.invite_path IS NOT NULL AND g.invite_path!=""'; break;
    case 'no_ticket': $sql .= ' AND (g.invite_path IS NULL OR g.invite_path="")'; break;
    case 'checked_in':$sql .= ' AND g.checked_in=1'; break;
    case 'pending':   $sql .= ' AND g.checked_in=0'; break;
    case 'email_sent':if ($hasEmailSent) $sql .= ' AND g.email_sent=1'; break;
    case 'wa_failed':
        if ($waTbl) $sql .= ' AND EXISTS (SELECT 1 FROM wa_send_log w WHERE w.guest_id=g.id AND w.status="failed")';
        break;
}
$sql .= ' ORDER BY g.name ASC';

$stmt = $db->prepare($sql);
$stmt->execute($params);
$guests = $stmt->fetchAll();

// Headers
$headers = [
    'Name','Email','Phone','Table','Seat','Category',
    'Has Ticket',
    'WA Invite','WA Sent At','WA Status','WA Error',
    'Email Invite','Email Sent At',
    'Checked In','Check-in Time','Check-in Method','Checked By',
];

// Rows
$rows = [];
foreach ($guests as $g) {
    $rows[] = [
        $g['name'],
        $g['email']        ?? '',
        $g['phone']        ?? '',
        $g['table_number'] ?? '',
        $g['seat_number']  ?? '',
        $g['category']     ?? '',
        $g['has_ticket'],
        $g['invite_sent']   ? 'Sent' : 'Pending',
        $g['wa_sent_at']    ? date('Y-m-d H:i', strtotime($g['wa_sent_at']))     : '',
        ucfirst($g['wa_last_status'] ?? 'pending'),
        $g['wa_error']      ?? '',
        $g['email_sent']    ? 'Sent' : 'Pending',
        $g['email_sent_at'] ? date('Y-m-d H:i', strtotime($g['email_sent_at'])) : '',
        $g['attended'],
        $g['check_in_time']   ? date('Y-m-d H:i', strtotime($g['check_in_time'])) : '',
        $g['check_in_method'] ?? '',
        $g['checked_by_name'] ?? '',
    ];
}

$slug        = preg_replace('/[^a-z0-9_\-]/', '', strtolower($event['slug']));
$filterSlug  = str_replace('_', '-', $filter);
$dateStamp   = date('Ymd-Hi');
$filename    = "guests-{$slug}-{$filterSlug}-{$dateStamp}";
$filterLabel = match($filter) {
    'sent'       => 'WA Sent',
    'unsent'     => 'WA Pending',
    'no_ticket'  => 'No Ticket',
    'checked_in' => 'Attended',
    'pending'    => 'Not Checked In',
    'wa_failed'  => 'WA Failed',
    default      => 'All Guests',
};

// ── CSV ───────────────────────────────────────────────────────
if ($format === 'csv') {
    header('Content-Type: text/csv; charset=UTF-8');
    header('Content-Disposition: attachment; filename="' . $filename . '.csv"');
    header('Pragma: no-cache');
    $out = fopen('php://output', 'w');
    fputs($out, "\xEF\xBB\xBF");
    fputcsv($out, $headers);
    foreach ($rows as $row) fputcsv($out, $row);
    fclose($out);
    exit;
}

// ── Excel (SpreadsheetML) ─────────────────────────────────────
if ($format === 'excel') {
    header('Content-Type: application/vnd.ms-excel; charset=UTF-8');
    header('Content-Disposition: attachment; filename="' . $filename . '.xls"');
    header('Pragma: no-cache');
    $e = fn($s) => htmlspecialchars((string)$s, ENT_XML1, 'UTF-8');

    echo '<?xml version="1.0" encoding="UTF-8"?>' . "\n";
    echo '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
         xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<Styles>
  <Style ss:ID="H"><Font ss:Bold="1" ss:Color="#FFFFFF" ss:Size="10"/>
    <Interior ss:Color="#1A1A24" ss:Pattern="Solid"/></Style>
  <Style ss:ID="S"><Interior ss:Color="#0D3320" ss:Pattern="Solid"/>
    <Font ss:Size="10"/></Style>
  <Style ss:ID="F"><Interior ss:Color="#330D0D" ss:Pattern="Solid"/>
    <Font ss:Size="10" ss:Color="#FF6B6B"/></Style>
  <Style ss:ID="D"><Font ss:Size="10"/></Style>
  <Style ss:ID="M"><Interior ss:Color="#1A1500" ss:Pattern="Solid"/>
    <Font ss:Size="10" ss:Color="#C59E59"/></Style>
</Styles>
<Worksheet ss:Name="' . $e($event['title'] . ' — ' . $filterLabel) . '">
<Table>' . "\n";

    $widths = [180,160,110,60,60,90,70,70,130,70,200,80,130,80,130,90,130];
    foreach ($widths as $w) echo '  <Column ss:Width="' . $w . '"/>' . "\n";

    echo '  <Row ss:Height="20">' . "\n";
    foreach ($headers as $h)
        echo '    <Cell ss:StyleID="H"><Data ss:Type="String">' . $e($h) . '</Data></Cell>' . "\n";
    echo '  </Row>' . "\n";

    foreach ($rows as $row) {
        $waStatus = strtolower($row[9] ?? '');
        $style = match(true) {
            $waStatus === 'sent'   => 'S',
            $waStatus === 'failed' => 'F',
            !empty($row[10])       => 'F',
            default                => 'D',
        };
        echo '  <Row>' . "\n";
        foreach ($row as $cell)
            echo '    <Cell ss:StyleID="' . $style . '"><Data ss:Type="String">' . $e($cell) . '</Data></Cell>' . "\n";
        echo '  </Row>' . "\n";
    }

    echo '  <Row ss:Height="6"></Row>' . "\n";
    echo '  <Row>
    <Cell ss:StyleID="M"><Data ss:Type="String">Exported: ' . $e(date('Y-m-d H:i')) . '</Data></Cell>
    <Cell ss:StyleID="M"><Data ss:Type="String">Filter: ' . $e($filterLabel) . '</Data></Cell>
    <Cell ss:StyleID="M"><Data ss:Type="String">Total: ' . count($rows) . ' guests</Data></Cell>
  </Row>' . "\n";

    echo '</Table></Worksheet></Workbook>';
    exit;
}

http_response_code(400); exit('Unknown format');
