<?php
// ─────────────────────────────────────────────────────────────
//  lib/CsvExporter.php — CSV generation from submissions JSON
//  P4 deliverable
// ─────────────────────────────────────────────────────────────

class CsvExporter {

    /**
     * Stream a CSV of all submissions for a given form_id to stdout.
     * Sets appropriate headers and exits.
     *
     * @param int    $form_id   The form to export
     * @param string $form_title Used to name the download file
     * @param PDO    $pdo       DB connection (use db() from lib.php)
     */
    public static function streamCsv(int $form_id, string $form_title, PDO $pdo): void {
        // Fetch fields definition
        $fstmt = $pdo->prepare('SELECT fields FROM fb_forms WHERE id = ?');
        $fstmt->execute([$form_id]);
        $form_row = $fstmt->fetch(PDO::FETCH_ASSOC);
        if (!$form_row) {
            http_response_code(404);
            echo 'Form not found';
            exit;
        }
        $fields = json_decode($form_row['fields'], true) ?: [];

        // Stream headers
        $safe_title = preg_replace('/[^A-Za-z0-9\-_]/', '-', $form_title);
        $filename   = 'submissions-' . $safe_title . '-' . date('Ymd') . '.csv';
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: no-cache, no-store');
        header('Pragma: no-cache');

        $out = fopen('php://output', 'w');
        // BOM for Excel UTF-8 compatibility
        fwrite($out, "\xEF\xBB\xBF");

        // Build header row
        $header = ['Ref', 'Submitted At', 'IP', 'Email Sent'];
        $field_ids = [];
        foreach ($fields as $f) {
            $field_ids[] = $f['id'] ?? '';
            $header[]    = $f['label'] ?? ($f['id'] ?? 'Field');
        }
        fputcsv($out, $header);

        // Fetch all submissions in batches
        $offset = 0;
        $batch  = 200;
        do {
            $stmt = $pdo->prepare(
                "SELECT ref, data, files, ip, email_sent, created_at
                 FROM fb_submissions
                 WHERE form_id = ?
                 ORDER BY id ASC
                 LIMIT $batch OFFSET $offset"
            );
            $stmt->execute([$form_id]);
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

            foreach ($rows as $row) {
                $data  = json_decode($row['data'],  true) ?: [];
                $files = json_decode($row['files'], true) ?: [];

                $line = [
                    $row['ref'],
                    $row['created_at'],
                    $row['ip'],
                    $row['email_sent'] ? 'Yes' : 'No',
                ];

                foreach ($field_ids as $fid) {
                    $val = $data[$fid] ?? '';
                    if (is_array($val)) {
                        // checkbox multi-select
                        $val = implode('; ', array_filter($val, 'strlen'));
                    }
                    // For file fields, list original names
                    if (isset($files[$fid]) && is_array($files[$fid])) {
                        $names = array_column($files[$fid], 'original_name');
                        $val   = implode('; ', $names);
                    }
                    // For signature fields, just note presence
                    if (is_string($val) && str_starts_with($val, 'data:image')) {
                        $val = '[signature]';
                    }
                    $line[] = (string)$val;
                }

                fputcsv($out, $line);
            }

            $offset += $batch;
        } while (count($rows) === $batch);

        fclose($out);
        exit;
    }
}
