<?php
$B = htmlspecialchars(rtrim((string) ($baseUrl ?? ''), '/'));
if (!function_exists('pp_where')) {
    function pp_where(string $path, string $lang = 'php'): string {
        $l = $lang === 'js' ? 'JS' : 'PHP';
        return '<div class="help-where"><span class="help-where-tag"><i class="bi bi-geo-alt-fill"></i> Where</span>'
             . '<span class="help-where-path">' . $path . '</span>'
             . '<span class="help-where-lang ' . htmlspecialchars($lang) . '">' . $l . '</span></div>';
    }
}
?>
<h1><i class="bi bi-arrow-down-up"></i> Export &amp; Import</h1>
<p class="lead">Move data in and out — CSV, Excel, JSON, XML. You can reshape rows on the way out, and validate or
transform rows on the way in. Each recipe says <strong>exactly where the code goes</strong>.</p>

<h2>Events these pages fire</h2>
<table class="help-table">
    <thead><tr><th>Event</th><th>Where</th><th>Use for</th></tr></thead>
    <tbody>
        <tr><td>Before Export</td><td>Table → <b>Page Events</b> (PHP)</td><td>Choose/limit the rows, add columns, mask sensitive fields.</td></tr>
        <tr><td>After Export</td><td>Table → <b>Page Events</b> (PHP)</td><td>Log the export, notify someone.</td></tr>
        <tr><td>Before Import</td><td>Table → <b>Page Events</b> (PHP)</td><td>Validate + transform each incoming row; skip bad ones.</td></tr>
        <tr><td>After Import</td><td>Table → <b>Page Events</b> (PHP)</td><td>Summarise, recalculate, or notify after the load.</td></tr>
        <tr><td>Custom WHERE</td><td>Table → <b>SQL Modifiers</b></td><td>Export respects the same row filter as the List page.</td></tr>
    </tbody>
</table>

<h2>Export recipes</h2>

<h3>1 · Add a computed column to the export</h3>
<?= pp_where('Events → <b>orders</b> → Page Events → <b>Before Export</b>', 'php') ?>
<pre><code>foreach ($data as &$row) {
    $row['margin'] = (float) $row['price'] - (float) $row['cost'];
}
unset($row);</code></pre>

<h3>2 · Mask a sensitive field in exports</h3>
<?= pp_where('Events → <b>customers</b> → Page Events → <b>Before Export</b>', 'php') ?>
<pre><code>foreach ($data as &$row) {
    if (!empty($row['email'])) $row['email'] = preg_replace('/(?&lt;=.).(?=.*@)/', '*', $row['email']);
}
unset($row);</code></pre>

<h3>3 · Only export the current user's rows</h3>
<?= pp_where('Events → <b>orders</b> → SQL Modifiers → <b>Custom WHERE</b>', 'php') ?>
<pre><code>if (!\PpPhp\Core\Auth\Permissions::isAdmin()) {
    return 'created_by = ' . (int) (\PpPhp\Core\Security::CurrentUserData()['id'] ?? 0);
}
return '';</code></pre>

<h3>4 · Log every export</h3>
<?= pp_where('Events → <b>orders</b> → Page Events → <b>After Export</b>', 'php') ?>
<pre><code>dbExecute('INSERT INTO export_log (table_name, rows, "by", at) VALUES (?,?,?,?)',
          ['orders', count($data), \PpPhp\Core\Security::CurrentUsername(), now()]);</code></pre>

<h2>Import recipes</h2>

<h3>5 · Validate &amp; clean each incoming row</h3>
<?= pp_where('Events → <b>customers</b> → Page Events → <b>Before Import</b>', 'php') ?>
<pre><code>// $data is one incoming row (by reference). Return false to skip it.
$data['email'] = strtolower(trim($data['email'] ?? ''));
if (!filter_var($data['email'], FILTER_VALIDATE_EMAIL)) {
    return false;   // skip rows without a valid email
}
$data['imported_at'] = now();</code></pre>

<h3>6 · Look up a foreign key from a name during import</h3>
<?= pp_where('Events → <b>orders</b> → Page Events → <b>Before Import</b>', 'php') ?>
<pre><code>// the CSV has a customer NAME; store the id
$id = dbValue('SELECT id FROM customers WHERE name = ?', [$data['customer_name'] ?? '']);
if (!$id) return false;              // unknown customer → skip
$data['customer_id'] = (int) $id;
unset($data['customer_name']);</code></pre>

<h3>7 · Recalculate totals after a bulk import</h3>
<?= pp_where('Events → <b>orders</b> → Page Events → <b>After Import</b>', 'php') ?>
<pre><code>dbExecute('UPDATE orders o SET total = (SELECT COALESCE(SUM(qty*price),0) FROM order_items WHERE order_id = o.id)');</code></pre>

<div class="help-callout help-callout-warn">
    <i class="bi bi-shield-check"></i>
    <div>Import events run on <strong>trusted, author-written code</strong> and every value is bound as a parameter
        in your DB calls — never glue an imported value into SQL. See <a href="<?= $B ?>/help/DB">Database Helpers</a>.</div>
</div>
