exports.php

<?php
require_once __DIR__ . '/../bootstrap.php';
require_once __DIR__ . '/../lib/feature_modules.php';
wp_feature_require_admin();
$pageTitle = 'Exports';
wp_feature_ensure_rota_schema();

function wp_export_clock_csv(PDO $pdo): void {
    wp_feature_ensure_time_clock_schema();
    $stmt = $pdo->query("SELECT * FROM time_clock_entries ORDER BY clock_in_at DESC");
    $rows = [];
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) {
        $hours = '';
        if (!empty($r['clock_in_at']) && !empty($r['clock_out_at'])) {
            $minutes = (strtotime((string)$r['clock_out_at']) - strtotime((string)$r['clock_in_at'])) / 60;
            $hours = number_format(max(0, $minutes) / 60, 2);
        }
        $startMeta = !empty($r['start_meta_json']) ? json_decode((string)$r['start_meta_json'], true) : [];
        $endMeta = !empty($r['end_meta_json']) ? json_decode((string)$r['end_meta_json'], true) : [];
        if (!is_array($startMeta)) $startMeta = [];
        if (!is_array($endMeta)) $endMeta = [];
        $rows[] = [
            $r['id'],
            $r['username'],
            $r['display_name'],
            $r['role_name'],
            $r['van_label'] ?? '',
            $r['van_reg'] ?? '',
            $r['partner_name'] ?? '',
            $r['mileage_in'] ?? '',
            $r['mileage_out'] ?? '',
            $r['clock_in_at'],
            $r['clock_out_at'],
            $hours,
            $r['status'],
            json_encode($startMeta['checks'] ?? [], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
            json_encode($endMeta['checks'] ?? [], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
            $r['notes'],
            $r['photo_count'] ?? 0,
            $r['ip_address'],
        ];
    }
    wp_feature_csv_download('time_clock_' . date('Ymd_His') . '.csv', ['ID','Username','Display Name','Role','Van / Assignment','Van Reg','Working With','Odometer In','Odometer Out','Clock In','Clock Out','Hours','Status','Start Checks','End Checks','Notes','Photo Count','IP Address'], $rows);
}

function wp_export_calendar_csv(PDO $pdo): void {
    $stmt = $pdo->query("SELECT id, title, run_number, van_id, van_plate_temp, driver_user_id, driver_name_other, porter_user_id, porter_name_other, office_user_id, office_user_other, is_run_event, description, location, meeting_with, meeting_by_user_id, meeting_status, start_at, end_at, all_day, category_id, meta, created_by, updated_by, created_at, updated_at FROM calendar_events ORDER BY start_at DESC");
    $rows = [];
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) {
        $rows[] = [$r['id'], $r['title'], $r['run_number'], $r['van_id'], $r['van_plate_temp'], $r['driver_user_id'], $r['driver_name_other'], $r['porter_user_id'], $r['porter_name_other'], $r['office_user_id'], $r['office_user_other'], $r['is_run_event'], $r['description'], $r['location'], $r['meeting_with'], $r['meeting_by_user_id'], $r['meeting_status'], $r['start_at'], $r['end_at'], $r['all_day'], $r['category_id'], $r['meta'], $r['created_by'], $r['updated_by'], $r['created_at'], $r['updated_at']];
    }
    wp_feature_csv_download('calendar_events_' . date('Ymd_His') . '.csv', ['ID','Title','Run Number','Van ID','Van Temp','Driver User ID','Driver Other','Porter User ID','Porter Other','Office User ID','Office Other','Run Event','Description','Location','Meeting With','Meeting By','Meeting Status','Start At','End At','All Day','Category ID','Meta','Created By','Updated By','Created At','Updated At'], $rows);
}

$exports = [
    'rota' => ['title' => 'Rota', 'description' => 'All rota entries and assignments.', 'count_query' => 'SELECT COUNT(*) FROM rota_entries', 'handler' => app_url('management/rota?export=csv')],
    'holidays' => ['title' => 'Holiday Requests', 'description' => 'Holiday request queue with statuses.', 'count_query' => 'SELECT COUNT(*) FROM holiday_requests', 'handler' => app_url('management/holidays?export=csv')],
    'incidents' => ['title' => 'Incidents', 'description' => 'Incident log including photo counts.', 'count_query' => 'SELECT COUNT(*) FROM incidents', 'handler' => app_url('management/incidents?export=csv')],
    'clock' => ['title' => 'Clock In / Out', 'description' => 'Clock in, clock out and worked hours.', 'count_query' => 'SELECT COUNT(*) FROM time_clock_entries', 'handler' => 'self'],
    'calendar' => ['title' => 'Calendar Events', 'description' => 'Full calendar event dataset.', 'count_query' => 'SELECT COUNT(*) FROM calendar_events', 'handler' => 'self'],
];

$exportKey = trim((string)($_GET['export'] ?? ''));
if ($exportKey !== '' && isset($exports[$exportKey])) {
    if ($exportKey === 'clock') {
        wp_export_clock_csv($pdo);
        exit;
    } elseif ($exportKey === 'calendar') {
        wp_export_calendar_csv($pdo);
        exit;
    }
}

$counts = [];
foreach ($exports as $key => $cfg) {
    try {
        $counts[$key] = (int)$pdo->query($cfg['count_query'])->fetchColumn();
    } catch (Throwable $e) {
        $counts[$key] = 0;
    }
}
include __DIR__ . '/../partials/header.php';
?>
<div class="content-header">
  <div>
    <h1 class="content-title">📤 Exports</h1>
    <p class="content-subtitle">Spreadsheet-ready CSV exports for operational data, calendar events and time clock entries.</p>
  </div>
  <div class="d-flex gap-md" style="flex-wrap:wrap;"><a class="btn btn-secondary" href="<?= e(app_url('admin')) ?>">← Back to Admin</a></div>
</div>
<div class="grid grid-2">
<?php foreach ($exports as $key => $cfg): ?>
  <div class="card">
    <div class="card-header"><h3 class="card-title"><?= e($cfg['title']) ?></h3></div>
    <p class="text-muted"><?= e($cfg['description']) ?></p>
    <p class="text-muted">Rows in database: <strong><?= (int)$counts[$key] ?></strong></p>
    <a class="btn btn-primary" href="<?= e($cfg['handler'] === 'self' ? app_url('admin/exports?export=' . $key) : $cfg['handler']) ?>">Download CSV</a>
  </div>
<?php endforeach; ?>
</div>
<?php include __DIR__ . '/../partials/footer.php'; ?>