export.php

<?php
/**
 * file: export.php
 *
 * Handles the export of user data.
 *
 * Generates a ZIP file containing CSV exports of 'gebeurtenissen', 'verbanden',
 * and 'urls', along with the user's uploaded documents and images.
 */

require_once 'auth.php';
require_once 'config.php';

// Allow long execution time for large exports
set_time_limit(0);

if (!defined('STORAGE_PATH')) {
    define('STORAGE_PATH', __DIR__ . '/storage');
}

try {
    $user_id = $_SESSION['user_id'];

    // Fetch user's username for the filename
    $stmt_user = $pdo->prepare("SELECT username FROM users WHERE id = ?");
    $stmt_user->execute([$user_id]);
    $username = $stmt_user->fetchColumn();

    // 1. Fetch Gebeurtenissen (Joined with media to identify paths)
    $stmt_gebeurtenissen = $pdo->prepare("
        SELECT g.*,
               bg.bestandsnaam as img_filename,
               d.bestand_pad as doc_path,
               a.bestand_pad as audio_path
        FROM gebeurtenissen g
        LEFT JOIN BeeldGebeurtenissen bg ON g.beeld_gebeurtenis_id = bg.id
        LEFT JOIN documenten d ON g.document_id = d.id
        LEFT JOIN audio_files a ON g.audio_id = a.id
        WHERE g.user_id = ?
        ORDER BY g.datum ASC
    ");
    $stmt_gebeurtenissen->execute([$user_id]);
    $gebeurtenissen = $stmt_gebeurtenissen->fetchAll(PDO::FETCH_ASSOC);

    // Create a map of database ID to a temporary export ID
    $db_id_to_export_id = [];
    $export_id_counter = 1;
    foreach ($gebeurtenissen as $g) {
        $db_id_to_export_id[$g['id']] = $export_id_counter++;
    }

    // 2. Fetch Verbanden
    $stmt_verbanden = $pdo->prepare("SELECT gebeurtenis_id_begin, gebeurtenis_id_eind, link_desc FROM verbanden WHERE user_id = ?");
    $stmt_verbanden->execute([$user_id]);
    $verbanden = $stmt_verbanden->fetchAll(PDO::FETCH_ASSOC);

    // 3. Fetch User Files (Secure Media)
    $stmt_media = $pdo->prepare("SELECT * FROM user_files WHERE user_id = ?");
    $stmt_media->execute([$user_id]);
    $media_files = $stmt_media->fetchAll(PDO::FETCH_ASSOC);

    // 4. Fetch URLs
    $stmt_urls = $pdo->prepare("SELECT id, url, external_id, original_title, created_at FROM urls WHERE user_id = ?");
    $stmt_urls->execute([$user_id]);
    $urls = $stmt_urls->fetchAll(PDO::FETCH_ASSOC);

    // Create temporary files for CSVs
    $gebeurtenissen_csv_path = tempnam(sys_get_temp_dir(), 'gebeurtenissen_');
    $verbanden_csv_path = tempnam(sys_get_temp_dir(), 'verbanden_');
    $manifest_csv_path = tempnam(sys_get_temp_dir(), 'media_manifest.csv');
    $urls_csv_path = tempnam(sys_get_temp_dir(), 'urls.csv');

    // Write Gebeurtenissen CSV
    $gebeurtenissen_fp = fopen($gebeurtenissen_csv_path, 'w');
    fputcsv($gebeurtenissen_fp, ['export_id', 'datum', 'titel', 'score', 'details'], ",", "\"", "\\");

    // Arrays to collect physical files to zip
    $files_to_zip = [];

    foreach ($gebeurtenissen as $g) {
        fputcsv($gebeurtenissen_fp, [
            $db_id_to_export_id[$g['id']],
            $g['datum'],
            $g['titel'],
            $g['score'],
            $g['details']
        ], ",", "\"", "\\");

        // Collect standard media files
        if (!empty($g['img_filename'])) {
            $files_to_zip[] = ['source' => 'uploads/' . $g['img_filename'], 'dest' => 'media/uploads/' . $g['img_filename']];
        }
        if (!empty($g['doc_path'])) {
             // Handle absolute vs relative paths just in case, but usually they are relative like 'docs/file.pdf'
             $path = $g['doc_path'];
             if (file_exists($path)) {
                 $files_to_zip[] = ['source' => $path, 'dest' => 'media/' . $path];
             }
        }
        if (!empty($g['audio_path'])) {
             $path = $g['audio_path'];
             if (file_exists($path)) {
                 $files_to_zip[] = ['source' => $path, 'dest' => 'media/' . $path];
             }
        }
    }
    fclose($gebeurtenissen_fp);

    // Write Verbanden CSV
    $verbanden_fp = fopen($verbanden_csv_path, 'w');
    fputcsv($verbanden_fp, ['begin_export_id', 'end_export_id', 'link_desc'], ",", "\"", "\\");
    foreach ($verbanden as $v) {
        // Ensure the IDs exist in the map before exporting
        if (isset($db_id_to_export_id[$v['gebeurtenis_id_begin']]) && isset($db_id_to_export_id[$v['gebeurtenis_id_eind']])) {
            fputcsv($verbanden_fp, [
                $db_id_to_export_id[$v['gebeurtenis_id_begin']],
                $db_id_to_export_id[$v['gebeurtenis_id_eind']],
                $v['link_desc']
            ], ",", "\"", "\\");
        }
    }
    fclose($verbanden_fp);

    // Write Media Manifest CSV
    $manifest_fp = fopen($manifest_csv_path, 'w');
    fputcsv($manifest_fp, ['id', 'event_export_id', 'original_filename', 'mime_type', 'created_at'], ",", "\"", "\\");
    foreach ($media_files as $file) {
        $event_export_id = $db_id_to_export_id[$file['item_id']] ?? '';
        fputcsv($manifest_fp, [
            $file['id'],
            $event_export_id,
            $file['original_filename'],
            $file['mime_type'],
            $file['created_at']
        ], ",", "\"", "\\");
    }
    fclose($manifest_fp);

    // Write URLs CSV
    $urls_fp = fopen($urls_csv_path, 'w');
    fputcsv($urls_fp, ['id', 'url', 'external_id', 'original_title', 'created_at'], ",", "\"", "\\");
    foreach ($urls as $url) {
        fputcsv($urls_fp, [
            $url['id'],
            $url['url'],
            $url['external_id'],
            $url['original_title'],
            $url['created_at']
        ], ",", "\"", "\\");
    }
    fclose($urls_fp);

    // 5. Create Zip Archive
    $zip = new ZipArchive();
    $zip_filename = tempnam(sys_get_temp_dir(), 'levensloop_export_');
    $zip_public_filename = 'levensloop_export_' . preg_replace('/[^a-zA-Z0-9_-]/', '', $username) . '_' . date('Y-m-d') . '.zip';

    if ($zip->open($zip_filename, ZipArchive::OVERWRITE) === TRUE) {
        $zip->addFile($gebeurtenissen_csv_path, 'gebeurtenissen.csv');
        $zip->addFile($verbanden_csv_path, 'verbanden.csv');
        $zip->addFile($manifest_csv_path, 'media_manifest.csv');
        $zip->addFile($urls_csv_path, 'urls.csv');

        // Add Secure media files
        foreach ($media_files as $file) {
            $storagePath = STORAGE_PATH . '/' . $file['storage_filename'];
            if (file_exists($storagePath)) {
                // Store in media/secure/ subdirectory with ID prefix to prevent collisions
                $zipName = 'media/secure/' . $file['id'] . '_' . $file['original_filename'];
                $zip->addFile($storagePath, $zipName);
            }
        }

        // Add Standard media files
        foreach ($files_to_zip as $f) {
            if (file_exists($f['source'])) {
                $zip->addFile($f['source'], $f['dest']);
            }
        }

        $zip->close();

        // 6. Send the file to the browser
        header('Content-Type: application/zip');
        header('Content-Disposition: attachment; filename="' . $zip_public_filename . '"');
        header('Content-Length: ' . filesize($zip_filename));
        readfile($zip_filename);

        // 7. Clean up temporary files
        unlink($gebeurtenissen_csv_path);
        unlink($verbanden_csv_path);
        unlink($manifest_csv_path);
        unlink($urls_csv_path);
        unlink($zip_filename);
        exit;
    } else {
        throw new Exception('Kon het zip-archief niet aanmaken.');
    }

} catch (Exception $e) {
    // Basic error handling
    header('HTTP/1.1 500 Internal Server Error');
    echo 'Er is een fout opgetreden bij het genereren van de export: ' . $e->getMessage();
    exit;
}