<?php
/**
 * WhizzyCommerce migration connector for Magento 2.
 *
 * Drop this single file at pub/whizzy/import.php on the merchant's existing
 * Magento installation, then run it either way:
 *
 *   browser:  https://shop.com/whizzy/import.php?k=wz_xxxxxxxx
 *   ssh:      php pub/whizzy/import.php --key=wz_xxxxxxxx
 *
 * WHY THIS SHAPE
 *
 * It reads Magento's database directly rather than bootstrapping the framework.
 * Bootstrapping is slower, needs far more memory than a shared host usually
 * allows, and breaks differently on every 2.x minor version. The tables it
 * touches (catalog_product_entity, catalog_category_entity, customer_entity and
 * the EAV value tables) have been stable across the whole 2.x line.
 *
 * WHAT IT UNDERSTANDS
 *
 *   simple, virtual, downloadable   one product, one variant
 *   configurable                    one product with a variant per child, on the
 *                                   same option axes the storefront used
 *   grouped                         skipped; its members import individually
 *   bundle                          one product at its own price, flagged so the
 *                                   merchant can rebuild the options
 *
 * Both database layouts are handled: Open Source keys everything on entity_id,
 * Adobe Commerce adds content staging and keys on a surrogate row_id.
 *
 * Cart price rules and catalog price rules come across as rules, not as prices
 * already applied, so they keep working after the move. Anything that could not
 * be translated whole arrives switched off with the reason attached, because
 * every way a rule loses a piece of itself in the crossing makes it apply to
 * more rather than less.
 *
 * Order history comes across as RECORDS. Nothing is replayed, no payment is
 * taken, no stock moves, no email is sent, because the money changed hands
 * years ago and the only correct thing to do with it is write it down.
 *
 * It PUSHES to WhizzyCommerce and nothing is ever pulled from the outside. The
 * merchant's credentials never leave their server, no inbound connection is
 * opened, and they can read this file in full before running it, which is the
 * point of it being one readable file with no dependencies.
 *
 * It is DRY RUN unless the dashboard says otherwise: the first pass counts and
 * validates, writing nothing, so the merchant sees what would happen first.
 *
 * Delete it when the migration is done. It refuses to run without a valid key,
 * but a file that can read your customer table has no business staying in a
 * public directory.
 */

declare(strict_types=1);

const WHIZZY_ENDPOINT = 'https://whizzycommerce.com';
const BATCH_SIZE      = 200;
/** Products carry variants and HTML descriptions, so they go in smaller loads. */
const PRODUCT_BATCH   = 50;
const CONNECTOR_VER   = '1.3.0';

$isCli = PHP_SAPI === 'cli';
if (!$isCli) {
    header('Content-Type: text/plain; charset=utf-8');
    // Streaming output: a large catalogue takes minutes and a blank page for
    // three minutes is indistinguishable from a hang.
    while (ob_get_level() > 0) { ob_end_flush(); }
    ob_implicit_flush(true);
}

function out(string $line): void { echo $line . "\n"; @flush(); }
function fail(string $msg): void { out(''); out('FAILED: ' . $msg); exit(1); }

/* ----------------------------------------------------------- promotions */

/**
 * Cart price rules and catalog price rules, translated rather than dumped.
 *
 * WHY THE TRANSLATION HAPPENS HERE
 *
 * Magento's rules are a condition tree, four `simple_action` spellings per rule
 * kind, two separate ways of saying free shipping, and coupon codes in a
 * different table. All of that is Magento's vocabulary and none of it should
 * end up inside WhizzyCommerce, which has its own. So this file, which is the
 * one part of the system that is allowed to know Magento, does the reading, and
 * what goes over the wire is a rule the other side can price or nothing at all.
 *
 * THE RULE THAT DECIDES WHAT IS SAFE
 *
 * Every way a rule can lose a piece of itself in this crossing makes it apply
 * to MORE, never less: a condition on an attribute we have no equivalent for
 * disappears, a restriction to three customer groups disappears, a nested
 * "any of these" collapses. So anything not translated whole is sent with
 * `partial`, and the other side stores those switched off with the reason
 * attached. A promotion the merchant has to turn on is a minute of their time;
 * one that quietly went from "knitwear" to "everything" is a weekend of their
 * margin.
 */

/** Magento's operators, in ours. Anything else is untranslatable. */
const RULE_OPS = [
    '=='   => 'eq',   '!='   => 'neq',
    '>'    => 'gt',   '>='   => 'gte',
    '<'    => 'lt',   '<='   => 'lte',
    '{}'   => 'contains', '!{}' => 'not_contains',
    '()'   => 'in',   '!()'  => 'not_in',
];

/**
 * Magento attributes, in ours, with how the value has to be rescaled.
 *
 * `money` is entered in whole currency in Magento and stored in minor units
 * here; `weight` is Magento's own unit, taken as kilograms, against grams here.
 * `category` is the one that is not a rescale at all: the value is a list of
 * Magento category ids and it leaves as a list of handles, because the two
 * systems share no identity and handles are what the categories pass already
 * sent across.
 */
const RULE_FIELDS = [
    // On a product, in either kind of rule.
    'sku'                        => ['sku', 'text'],
    'name'                       => ['title', 'text'],
    'category_ids'               => ['collection', 'category'],
    'price'                      => ['price', 'money'],
    'quote_item_price'           => ['price', 'money'],
    'base_price'                 => ['price', 'money'],
    // On the basket, cart rules only.
    'base_subtotal'              => ['subtotal', 'money'],
    'base_subtotal_with_discount'=> ['subtotal', 'money'],
    'total_qty'                  => ['item_count', 'plain'],
    'weight'                     => ['total_weight', 'weight'],
    'country_id'                 => ['customer_country', 'text'],
];

/**
 * One Magento clause as one of ours, or null with the reason.
 *
 * `$categoryHandle` is the map the categories pass built. A clause naming a
 * category this Magento no longer has is dropped rather than sent as an id the
 * other side would not recognise.
 */
function translateClause(array $c, array $categoryHandle): array {
    $attr = (string)($c['attribute'] ?? '');
    $op   = (string)($c['operator'] ?? '');

    if (!isset(RULE_FIELDS[$attr])) return [null, 'condition on ' . ($attr ?: 'an unnamed attribute')];
    if (!isset(RULE_OPS[$op]))      return [null, 'the operator "' . $op . '" on ' . $attr];

    [$field, $scale] = RULE_FIELDS[$attr];
    $op = RULE_OPS[$op];

    // Magento stores a multi-value clause as an array or as a comma string.
    $raw = $c['value'] ?? '';
    $parts = is_array($raw) ? $raw : explode(',', (string)$raw);
    $parts = array_values(array_filter(array_map('trim', array_map('strval', $parts)), fn($v) => $v !== ''));
    if ($parts === []) return [null, 'an empty value on ' . $attr];

    if ($scale === 'category') {
        $handles = [];
        foreach ($parts as $id) {
            if (isset($categoryHandle[(int)$id])) $handles[] = $categoryHandle[(int)$id];
        }
        if ($handles === []) return [null, 'a category that is not in this catalogue'];
        if (count($handles) !== count($parts)) return [null, 'a category that is not in this catalogue'];
        $parts = $handles;
    } elseif ($scale === 'money' || $scale === 'weight') {
        $factor = $scale === 'money' ? 100 : 1000;
        $parts = array_map(fn($v) => (string)(int)round(((float)str_replace(',', '.', $v)) * $factor), $parts);
    }

    /*
     * Magento writes "is one of these three categories" as `==` with a list,
     * which in our vocabulary is `in`. Left alone for a single value, where
     * the two mean the same thing and `eq` reads better on the merchant's own
     * screen.
     */
    if (count($parts) > 1) {
        if ($op === 'eq')  $op = 'in';
        if ($op === 'neq') $op = 'not_in';
    }

    return [['field' => $field, 'op' => $op, 'value' => implode(',', $parts)], null];
}

/**
 * A serialised condition tree as a flat list, plus what was lost.
 *
 * Magento 2.3 and up store JSON; older installs store PHP's own serialisation.
 * Both are read by a function that ships with PHP, which is the whole reason
 * this translation belongs on this side of the wire.
 *
 * Only the top level is walked. A nested combine is a bracketed expression and
 * this engine has one level of and/or, so a rule containing one is reported as
 * partial rather than flattened: flattening "(A and B) or C" into a list would
 * produce a rule that is not the one the merchant wrote.
 */
function flattenConditions(?string $serialised, array $categoryHandle): array {
    $notes = [];
    if ($serialised === null || trim($serialised) === '') return [[], 'all', $notes];

    $tree = json_decode($serialised, true);
    if (!is_array($tree)) {
        // PHP-serialised, from Magento 2.0 to 2.2. `unserialize` is told to
        // build no objects: this string came out of a database and an object
        // here would be somebody else's constructor running.
        $tree = @unserialize($serialised, ['allowed_classes' => false]);
    }
    if (!is_array($tree)) return [[], 'all', ['its conditions could not be read']];

    $aggregator = ($tree['aggregator'] ?? 'all') === 'any' ? 'any' : 'all';

    /*
     * `value` on a combine is 1 for "if ALL of these are TRUE" and 0 for
     * FALSE, which is the whole tree negated. There is no way to say that
     * here, and reading it as the un-negated version would invert the rule.
     */
    if ((string)($tree['value'] ?? '1') !== '1') {
        return [[], $aggregator, ['it matches when its conditions are FALSE, which cannot be expressed here']];
    }

    $out = [];
    foreach (($tree['conditions'] ?? []) as $c) {
        if (!is_array($c)) continue;
        if (isset($c['conditions']) && is_array($c['conditions'])) {
            $notes[] = 'it had a nested group of conditions';
            continue;
        }
        [$clause, $why] = translateClause($c, $categoryHandle);
        if ($clause === null) { $notes[] = $why; continue; }
        $out[] = $clause;
    }

    return [$out, $aggregator, $notes];
}

/** Magento writes dates as `Y-m-d`; null and the zero date both mean no bound. */
function ruleDate(?string $v): ?string {
    $v = trim((string)$v);
    if ($v === '' || str_starts_with($v, '0000')) return null;
    return substr($v, 0, 10);
}


/**
 * `php import.php --self-check`, and no database needed.
 *
 * The translation above is a parser, and the only part of this file whose
 * output nobody sees until a merchant's promotions are already in their new
 * shop. It runs on a merchant's server, against their Magento, so there is no
 * suite that can cover it from ours: this is the check, in the file, runnable
 * on the machine where it would actually go wrong.
 */
if (in_array('--self-check', $argv ?? [], true)) {
    $fails = 0;
    $check = function (string $what, $got, $want) use (&$fails): void {
        if ($got === $want) { out('  ok   ' . $what); return; }
        $fails++;
        out('  FAIL ' . $what);
        out('       got  ' . json_encode($got));
        out('       want ' . json_encode($want));
    };
    $cats = [12 => 'jumpers', 15 => 'coats'];

    [$c, $agg, $notes] = flattenConditions(json_encode([
        'type' => 'Magento\\SalesRule\\Model\\Rule\\Condition\\Combine',
        'aggregator' => 'all', 'value' => '1',
        'conditions' => [
            ['type' => '...\\Address', 'attribute' => 'base_subtotal', 'operator' => '>=', 'value' => '50'],
            ['type' => '...\\Address', 'attribute' => 'total_qty', 'operator' => '>', 'value' => '2'],
        ],
    ]), $cats);
    $check('money reaches minor units', $c[0], ['field' => 'subtotal', 'op' => 'gte', 'value' => '5000']);
    $check('a count is left alone', $c[1], ['field' => 'item_count', 'op' => 'gt', 'value' => '2']);
    $check('the aggregator carries', $agg, 'all');
    $check('nothing was lost', $notes, []);

    [$c, , $notes] = flattenConditions(json_encode([
        'aggregator' => 'any', 'value' => '1',
        'conditions' => [
            ['attribute' => 'category_ids', 'operator' => '==', 'value' => '12,15'],
            ['attribute' => 'attribute_set_id', 'operator' => '==', 'value' => '4'],
        ],
    ]), $cats);
    $check('categories become handles, and == becomes is-one-of',
        $c[0], ['field' => 'collection', 'op' => 'in', 'value' => 'jumpers,coats']);
    $check('an attribute with no equivalent is reported, not dropped in silence',
        count($notes), 1);

    [$c, , $notes] = flattenConditions(json_encode([
        'aggregator' => 'all', 'value' => '1',
        'conditions' => [['attribute' => 'category_ids', 'operator' => '==', 'value' => '12,99']],
    ]), $cats);
    $check('a category this catalogue does not have takes its clause with it', $c, []);
    $check('and says so', count($notes), 1);

    [$c, , $notes] = flattenConditions(json_encode([
        'aggregator' => 'all', 'value' => '0',
        'conditions' => [['attribute' => 'sku', 'operator' => '==', 'value' => 'ABC']],
    ]), $cats);
    $check('a negated tree is refused rather than inverted', [$c, count($notes)], [[], 1]);

    [$c, , $notes] = flattenConditions(json_encode([
        'aggregator' => 'all', 'value' => '1',
        'conditions' => [
            ['attribute' => 'sku', 'operator' => '{}', 'value' => 'WOOL'],
            ['aggregator' => 'any', 'value' => '1', 'conditions' => [['attribute' => 'sku', 'operator' => '==', 'value' => 'X']]],
        ],
    ]), $cats);
    $check('a nested group is reported, and its siblings still cross',
        [$c[0], count($notes)], [['field' => 'sku', 'op' => 'contains', 'value' => 'WOOL'], 1]);

    // Magento 2.0 to 2.2 wrote PHP's own serialisation into the same column.
    [$c, , ] = flattenConditions(serialize([
        'aggregator' => 'all', 'value' => '1',
        'conditions' => [['attribute' => 'sku', 'operator' => '==', 'value' => 'ABC']],
    ]), $cats);
    $check('the older serialisation reads too', $c[0], ['field' => 'sku', 'op' => 'eq', 'value' => 'ABC']);

    $check('an unreadable tree is a note, not a crash', flattenConditions('not json at all', $cats)[2], ['its conditions could not be read']);
    $check('no conditions is not a failure', flattenConditions(null, $cats), [[], 'all', []]);
    $check('the zero date is no date', ruleDate('0000-00-00 00:00:00'), null);
    $check('a real date keeps its day', ruleDate('2026-11-27 00:00:00'), '2026-11-27');

    out('');
    out($fails === 0 ? 'Self-check passed.' : $fails . ' self-check(s) FAILED.');
    exit($fails === 0 ? 0 : 1);
}

/* ------------------------------------------------------------------ key */

$key = '';
if ($isCli) {
    foreach ($argv as $arg) {
        if (str_starts_with($arg, '--key=')) { $key = substr($arg, 6); }
        if ($arg === '--key' ) { $i = array_search('--key', $argv, true); $key = $argv[$i + 1] ?? ''; }
        if ($arg === '--live') { $forceLive = true; }
    }
} else {
    $key = (string)($_GET['k'] ?? '');
}
if ($key === '' || !preg_match('/^wz_[A-Za-z0-9_-]{20,}$/', $key)) {
    fail('No valid import key. Copy the command from your WhizzyCommerce dashboard, under Import.');
}

out('WhizzyCommerce connector ' . CONNECTOR_VER . ' (PHP ' . PHP_VERSION . ')');
out(str_repeat('-', 64));

/* -------------------------------------------------------- magento config */

/**
 * Find app/etc/env.php by walking up. Standard layout puts this file at
 * pub/whizzy/, so it is two levels down, but merchants move things.
 */
$envPath = null;
$dir = __DIR__;
for ($i = 0; $i < 6; $i++) {
    $candidate = $dir . '/app/etc/env.php';
    if (is_readable($candidate)) { $envPath = $candidate; break; }
    $parent = dirname($dir);
    if ($parent === $dir) break;
    $dir = $parent;
}
if ($envPath === null) {
    fail('Could not find app/etc/env.php. Put this file in your Magento pub/ directory, e.g. pub/whizzy/import.php');
}

$env = include $envPath;
$db  = $env['db']['connection']['default'] ?? null;
if (!$db) fail('app/etc/env.php has no default database connection.');

$prefix = (string)($env['db']['table_prefix'] ?? '');
$host   = (string)($db['host'] ?? 'localhost');
$port   = '3306';
if (str_contains($host, ':')) { [$host, $port] = explode(':', $host, 2); }

try {
    $pdo = new PDO(
        sprintf('mysql:host=%s;port=%s;dbname=%s;charset=utf8mb4', $host, $port, $db['dbname']),
        (string)$db['username'],
        (string)$db['password'],
        [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]
    );
} catch (Throwable $e) {
    fail('Could not connect to the Magento database: ' . $e->getMessage());
}

$t = fn(string $name): string => '`' . $prefix . $name . '`';

$magentoVersion = 'unknown';
try {
    $row = $pdo->query('SELECT * FROM ' . $t('setup_module') . " WHERE module = 'Magento_Store' LIMIT 1")->fetch();
    $magentoVersion = $row['schema_version'] ?? 'unknown';
} catch (Throwable) { /* not fatal */ }

// Prefer the secure URL: these become image URLs on a shop served over HTTPS,
// and http:// images would be blocked as mixed content.
$baseUrl = '';
foreach (['web/secure/base_url', 'web/unsecure/base_url'] as $path) {
    try {
        $st = $pdo->prepare('SELECT value FROM ' . $t('core_config_data') . ' WHERE path = ? AND value IS NOT NULL ORDER BY scope_id LIMIT 1');
        $st->execute([$path]);
        $row = $st->fetch();
        $candidate = rtrim((string)($row['value'] ?? ''), '/');
        if ($candidate !== '') { $baseUrl = $candidate; break; }
    } catch (Throwable) { /* not fatal */ }
}

out('Magento database: ' . $db['dbname'] . ' on ' . $host);
out('Store URL:        ' . ($baseUrl ?: '(unknown)'));
out('');

/* ------------------------------------------------------------- transport */

function call(string $path, array $payload, string $key): array {
    $ch = curl_init(WHIZZY_ENDPOINT . $path);
    curl_setopt_array($ch, [
        CURLOPT_POST           => true,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_TIMEOUT        => 120,
        CURLOPT_POSTFIELDS     => json_encode($payload, JSON_UNESCAPED_SLASHES | JSON_INVALID_UTF8_SUBSTITUTE),
        CURLOPT_HTTPHEADER     => [
            'Content-Type: application/json',
            'X-Whizzy-Import-Key: ' . $key,
        ],
        // The key authorises a data push; a downgraded or intercepted
        // connection would hand a merchant's customer list to whoever asked.
        CURLOPT_SSL_VERIFYPEER => true,
        CURLOPT_SSL_VERIFYHOST => 2,
    ]);
    $body = curl_exec($ch);
    $code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    $err  = curl_error($ch);
    curl_close($ch);

    if ($body === false) fail('Could not reach WhizzyCommerce: ' . $err);
    $json = json_decode((string)$body, true);
    if (!is_array($json)) fail('Unexpected reply from WhizzyCommerce (HTTP ' . $code . '): ' . substr((string)$body, 0, 200));
    if ($code >= 400) fail(($json['error'] ?? 'request rejected') . ' (HTTP ' . $code . ')');
    return $json;
}

/* --------------------------------------------------------------- counts */

$counts = [];
foreach ([
    'products'   => 'catalog_product_entity',
    'categories' => 'catalog_category_entity',
    'customers'  => 'customer_entity',
    'orders'     => 'sales_order',
] as $label => $table) {
    try {
        $counts[$label] = (int)$pdo->query('SELECT COUNT(*) c FROM ' . $t($table))->fetch()['c'];
    } catch (Throwable) { $counts[$label] = 0; }
}
out(sprintf(
    'Found: %d products, %d categories, %d customers, %d orders',
    $counts['products'], $counts['categories'], $counts['customers'], $counts['orders']
));

$hello = call('/import/hello', [
    'platform'  => 'magento2',
    'version'   => $magentoVersion,
    'connector' => CONNECTOR_VER,
    'php'       => PHP_VERSION,
    'baseUrl'   => $baseUrl,
    'counts'    => $counts,
], $key);

$dryRun = (bool)($hello['dryRun'] ?? true);

/**
 * The server decides what it wants and how much it can swallow at a time, so a
 * connector left on a merchant's server for a year does not have to be replaced
 * when either changes.
 */
$wants   = $hello['wants'] ?? ['categories', 'products', 'customers'];
$sizes   = $hello['batchSizes'] ?? [];
$sizeFor = fn(string $entity, int $fallback): int => max(1, (int)($sizes[$entity] ?? $fallback));
$wanted  = fn(string $entity): bool => in_array($entity, $wants, true);

out('');
out($dryRun
    ? '*** DRY RUN, nothing will be written. This shows what would be imported. ***'
    : '*** LIVE IMPORT, data will be written to your WhizzyCommerce shop. ***');
out('');

/* ----------------------------------------------------------- EAV helpers */

/**
 * Magento stores most product and category fields in EAV value tables. Reading
 * them per row would be thousands of queries, so each attribute is fetched once
 * for the whole batch and indexed by entity id.
 */
function attributeId(PDO $pdo, callable $t, string $entityType, string $code): ?int {
    static $cache = [];
    $ck = $entityType . '.' . $code;
    if (array_key_exists($ck, $cache)) return $cache[$ck];
    $sql = 'SELECT a.attribute_id FROM ' . $t('eav_attribute') . ' a
            JOIN ' . $t('eav_entity_type') . ' e ON e.entity_type_id = a.entity_type_id
            WHERE e.entity_type_code = ? AND a.attribute_code = ? LIMIT 1';
    $st = $pdo->prepare($sql);
    $st->execute([$entityType, $code]);
    $row = $st->fetch();
    return $cache[$ck] = $row ? (int)$row['attribute_id'] : null;
}

/**
 * @param string $keyCol 'entity_id' on Open Source, 'row_id' on Commerce.
 *                       Never merchant input, see the staging note below.
 */
function eavValues(PDO $pdo, callable $t, string $table, ?int $attrId, array $ids, string $keyCol = 'entity_id'): array {
    if ($attrId === null || $ids === []) return [];
    $in = implode(',', array_fill(0, count($ids), '?'));
    // store_id 0 is the default scope; per-store overrides are ignored on
    // purpose, since the target shop has a single storefront.
    $sql = "SELECT `$keyCol` AS k, value FROM " . $t($table) . "
            WHERE attribute_id = ? AND store_id = 0 AND `$keyCol` IN ($in)";
    $st = $pdo->prepare($sql);
    $st->execute(array_merge([$attrId], $ids));
    $out = [];
    foreach ($st->fetchAll() as $r) { $out[(int)$r['k']] = $r['value']; }
    return $out;
}

function hasColumn(PDO $pdo, callable $t, string $table, string $column): bool {
    try {
        $st = $pdo->prepare('SHOW COLUMNS FROM ' . $t($table) . ' LIKE ?');
        $st->execute([$column]);
        return (bool)$st->fetch();
    } catch (Throwable) { return false; }
}

/**
 * ADOBE COMMERCE STAGING
 *
 * Commerce adds content staging, which turns catalog_product_entity into a
 * versioned table: entity_id is no longer unique, a surrogate row_id is the
 * real primary key, and every EAV value table keys on row_id instead. A
 * connector that assumes Open Source finds nothing at all on those installs,
 * so both layouts are handled: rows are read as (entity_id, link_id) pairs and
 * only the version live right now is taken.
 */
$staged  = hasColumn($pdo, $t, 'catalog_product_entity', 'row_id');
$eavKey  = $staged ? 'row_id' : 'entity_id';
$nowTs   = time();
$liveRow = $staged ? " AND created_in <= $nowTs AND updated_in > $nowTs" : '';
if ($staged) out('Adobe Commerce staging detected, reading the version currently live.');

/**
 * Every field for a set of products, in a fixed number of queries.
 *
 * Takes rows rather than reading them, because it is called twice per page:
 * once for the products on the page and once for the children of any
 * configurable among them, which live at arbitrary offsets.
 *
 * @param array $rows each with 'entity_id' and 'link_id'
 * @return array arrays keyed by entity_id
 */
function loadProductFields(
    PDO $pdo, callable $t, array $rows, array $attrs, string $eavKey, string $baseUrl
): array {
    if ($rows === []) return [];

    $linkIds  = [];
    $entityOf = [];  // link_id => entity_id
    $sku      = [];  // entity_id => sku
    foreach ($rows as $r) {
        $e = (int)$r['entity_id'];
        $l = (int)$r['link_id'];
        $linkIds[] = $l;
        $entityOf[$l] = $e;
        $sku[$e] = (string)($r['sku'] ?? '');
    }
    $entityIds = array_values($entityOf);

    // EAV comes back keyed by link_id; the rest of the script thinks in
    // entity_id, so translate once here rather than at every use.
    $rekey = function (array $byLink) use ($entityOf): array {
        $o = [];
        foreach ($byLink as $l => $v) { if (isset($entityOf[$l])) $o[$entityOf[$l]] = $v; }
        return $o;
    };
    $eav = fn(string $table, ?int $attr) => $rekey(eavValues($pdo, $t, $table, $attr, $linkIds, $eavKey));

    $d = [
        'sku'        => $sku,
        'name'       => $eav('catalog_product_entity_varchar', $attrs['name']),
        'urlKey'     => $eav('catalog_product_entity_varchar', $attrs['url_key']),
        'desc'       => $eav('catalog_product_entity_text',    $attrs['description']),
        'shortDesc'  => $eav('catalog_product_entity_text',    $attrs['short_description']),
        'status'     => $eav('catalog_product_entity_int',     $attrs['status']),
        'visibility' => $eav('catalog_product_entity_int',     $attrs['visibility']),
        'price'      => $eav('catalog_product_entity_decimal', $attrs['price']),
        'special'    => $eav('catalog_product_entity_decimal', $attrs['special_price']),
        'weight'     => $eav('catalog_product_entity_decimal', $attrs['weight']),
        'from'       => $eav('catalog_product_entity_datetime', $attrs['special_from_date']),
        'to'         => $eav('catalog_product_entity_datetime', $attrs['special_to_date']),
        'stock'      => [],
        'images'     => [],
        'cats'       => [],
    ];

    $inE = implode(',', array_fill(0, count($entityIds), '?'));

    // Stock is addressed by entity_id even under staging: inventory is not
    // versioned content.
    try {
        $s = $pdo->prepare('SELECT product_id, qty, is_in_stock FROM ' . $t('cataloginventory_stock_item') . " WHERE product_id IN ($inE)");
        $s->execute($entityIds);
        foreach ($s->fetchAll() as $r) {
            $d['stock'][(int)$r['product_id']] = ['qty' => (float)$r['qty'], 'in' => (int)$r['is_in_stock']];
        }
    } catch (Throwable) { /* stock is optional */ }

    // The gallery link table follows the EAV key, so it needs link ids.
    try {
        $galleryKey = hasColumn($pdo, $t, 'catalog_product_entity_media_gallery_value_to_entity', 'row_id') ? 'row_id' : 'entity_id';
        $inL = implode(',', array_fill(0, count($linkIds), '?'));
        $s = $pdo->prepare(
            "SELECT g.`$galleryKey` AS k, v.value FROM " . $t('catalog_product_entity_media_gallery_value_to_entity') . ' g
             JOIN ' . $t('catalog_product_entity_media_gallery') . " v ON v.value_id = g.value_id
             WHERE g.`$galleryKey` IN ($inL)"
        );
        $s->execute($linkIds);
        foreach ($s->fetchAll() as $r) {
            $e = $entityOf[(int)$r['k']] ?? null;
            if ($e === null) continue;
            $path = (string)$r['value'];
            $d['images'][$e][] = $baseUrl ? $baseUrl . '/media/catalog/product' . $path : $path;
        }
    } catch (Throwable) { /* gallery is optional */ }

    try {
        $s = $pdo->prepare('SELECT product_id, category_id FROM ' . $t('catalog_category_product') . " WHERE product_id IN ($inE)");
        $s->execute($entityIds);
        foreach ($s->fetchAll() as $r) { $d['cats'][(int)$r['product_id']][] = (int)$r['category_id']; }
    } catch (Throwable) { /* uncategorised is fine */ }

    return $d;
}

/** Magento prices are decimal strings; the platform stores minor units. */
function minor(?string $v): ?int {
    if ($v === null || $v === '') return null;
    return (int)round(((float)$v) * 100);
}

/**
 * What the customer pays, and what to strike through.
 *
 * Magento's `special_price` is the DISCOUNTED price and `price` is the original.
 * Carrying them across as-is puts them the wrong way round, the shop would
 * advertise "€22.50, was €19.00", which is not a discount, it is a mistake the
 * merchant would have to find themselves.
 *
 * The special is also date-bounded. A sale that ended in 2019 still has its row
 * in the database, and honouring it would silently undercharge on every order.
 *
 * @return array{0:?int,1:?int} [price, compareAtPrice] in minor units
 */
function effectivePrice(?string $base, ?string $special, ?string $from, ?string $to): array {
    $price   = minor($base);
    $reduced = minor($special);
    if ($reduced === null || $reduced <= 0 || $price === null || $reduced >= $price) {
        return [$price, null];
    }

    $today = date('Y-m-d');
    if ($from !== null && $from !== '' && substr($from, 0, 10) > $today) return [$price, null];
    if ($to   !== null && $to   !== '' && substr($to,   0, 10) < $today) return [$price, null];

    return [$reduced, $price];
}

function send(string $entity, array $items, string $key, bool $last = false): array {
    if ($items === [] && !$last) return [];
    return call('/import/batch', ['entity' => $entity, 'items' => array_values($items), 'last' => $last], $key);
}

$totals = [];
function tally(string $entity, array $res): void {
    global $totals;
    if (!$res) return;
    foreach (['seen', 'imported', 'skipped', 'failed'] as $f) {
        $totals[$entity][$f] = ($totals[$entity][$f] ?? 0) + (int)($res[$f] ?? 0);
    }
}

/* ---------------------------------------------------------- categories */

out('Categories…');
$catName = attributeId($pdo, $t, 'catalog_category', 'name');
$catDesc = attributeId($pdo, $t, 'catalog_category', 'description');
$catUrl  = attributeId($pdo, $t, 'catalog_category', 'url_key');

$catStaged  = hasColumn($pdo, $t, 'catalog_category_entity', 'row_id');
$catEavKey  = $catStaged ? 'row_id' : 'entity_id';
$catLiveRow = $catStaged ? " AND created_in <= $nowTs AND updated_in > $nowTs" : '';
$catLinkSel = $catStaged ? 'row_id AS link_id' : 'entity_id AS link_id';

/**
 * Products are sent with category HANDLES, so this map is what makes the link
 * survive the crossing. Sending Magento's numeric ids would leave the other
 * side matching against identifiers it has never seen, which is precisely how
 * a catalogue arrives with every product uncategorised.
 */
$categoryHandle = [];

$rows = $pdo->query(
    'SELECT entity_id, ' . $catLinkSel . ', position FROM ' . $t('catalog_category_entity') .
    ' WHERE level > 1' . $catLiveRow
)->fetchAll();

foreach (array_chunk($rows, BATCH_SIZE) as $chunk) {
    $linkIds  = array_map(fn($r) => (int)$r['link_id'], $chunk);
    $names = eavValues($pdo, $t, 'catalog_category_entity_varchar', $catName, $linkIds, $catEavKey);
    $urls  = eavValues($pdo, $t, 'catalog_category_entity_varchar', $catUrl,  $linkIds, $catEavKey);
    $descs = eavValues($pdo, $t, 'catalog_category_entity_text',    $catDesc, $linkIds, $catEavKey);

    $items = [];
    foreach ($chunk as $r) {
        $id   = (int)$r['entity_id'];
        $link = (int)$r['link_id'];
        $name = $names[$link] ?? null;
        if (!$name) continue; // a category with no name is not a category
        $handle = $urls[$link] ?? $name;
        $categoryHandle[$id] = $handle;
        $items[] = [
            'externalId'  => (string)$id,
            'name'        => $name,
            'handle'      => $handle,
            'description' => $descs[$link] ?? null,
            'position'    => (int)($r['position'] ?? 0),
        ];
    }
    tally('categories', send('categories', $items, $key));
    out(sprintf('  %d/%d', $totals['categories']['seen'] ?? 0, count($rows)));
}

/* ------------------------------------------------------------ products */

out('Products…');

$attrs = [];
foreach ([
    'name', 'url_key', 'description', 'short_description', 'status', 'visibility',
    'price', 'special_price', 'special_from_date', 'special_to_date', 'weight',
] as $code) {
    $attrs[$code] = attributeId($pdo, $t, 'catalog_product', $code);
}

/* --------------------------------------------- configurable relationships */

/**
 * A configurable product in Magento is a shell: it holds the name, the URL and
 * the gallery, but no price and no stock. What a customer actually buys is one
 * of its children, each a hidden simple product carrying one combination of the
 * option axes.
 *
 * Earlier versions of this connector skipped configurables outright, which sent
 * every child through as a separate top-level product. A shirt in four colours
 * and five sizes arrived as twenty unrelated listings with no size selector,
 * technically a complete import, and useless as a shop.
 *
 * Both maps are loaded up front, once. They are integer pairs, so even a
 * six-figure catalogue costs a few megabytes, and the alternative is a query
 * per product.
 */
$childToParent = [];     // child entity_id => parent link_id
$parentChildren = [];    // parent link_id => [child entity_id]
try {
    foreach ($pdo->query('SELECT parent_id, product_id FROM ' . $t('catalog_product_super_link'))->fetchAll() as $r) {
        $child  = (int)$r['product_id'];
        $parent = (int)$r['parent_id'];
        $childToParent[$child] = $parent;
        $parentChildren[$parent][] = $child;
    }
} catch (Throwable) { /* no configurables in this catalogue */ }

// Which attributes define the axes, per parent, and what to call them.
$superAxes    = [];  // parent link_id => [attribute_id => label]
$superAttrIds = [];
try {
    $sa = $pdo->query('SELECT product_super_attribute_id, product_id, attribute_id FROM ' . $t('catalog_product_super_attribute') . ' ORDER BY position')->fetchAll();
    $superAttrIds = array_values(array_unique(array_map(fn($r) => (int)$r['attribute_id'], $sa)));

    // A merchant may have relabelled an axis on the storefront ("Colour"
    // instead of "color"); that label is what their customers know.
    $customLabel = [];
    try {
        foreach ($pdo->query('SELECT product_super_attribute_id, value FROM ' . $t('catalog_product_super_attribute_label') . ' WHERE store_id = 0')->fetchAll() as $l) {
            if (($l['value'] ?? '') !== '') $customLabel[(int)$l['product_super_attribute_id']] = (string)$l['value'];
        }
    } catch (Throwable) { /* labels are optional */ }

    $attrLabel = [];
    if ($superAttrIds) {
        $in = implode(',', array_fill(0, count($superAttrIds), '?'));
        $s = $pdo->prepare('SELECT attribute_id, attribute_code, frontend_label FROM ' . $t('eav_attribute') . " WHERE attribute_id IN ($in)");
        $s->execute($superAttrIds);
        foreach ($s->fetchAll() as $a) {
            $attrLabel[(int)$a['attribute_id']] = ($a['frontend_label'] ?: $a['attribute_code']);
        }
    }

    foreach ($sa as $r) {
        $aid = (int)$r['attribute_id'];
        $superAxes[(int)$r['product_id']][$aid] =
            $customLabel[(int)$r['product_super_attribute_id']] ?? ($attrLabel[$aid] ?? ('Option ' . $aid));
    }
} catch (Throwable) { /* not a catalogue with configurables */ }

// Option ids are meaningless on the other side; carry the human labels.
$optionLabel = [];
if ($superAttrIds) {
    try {
        $in = implode(',', array_fill(0, count($superAttrIds), '?'));
        // store_id DESC so the admin label (0) is written last and wins.
        $s = $pdo->prepare(
            'SELECT o.option_id, v.value FROM ' . $t('eav_attribute_option') . ' o
             JOIN ' . $t('eav_attribute_option_value') . " v ON v.option_id = o.option_id
             WHERE o.attribute_id IN ($in) ORDER BY v.store_id DESC"
        );
        $s->execute($superAttrIds);
        foreach ($s->fetchAll() as $r) { $optionLabel[(int)$r['option_id']] = (string)$r['value']; }
    } catch (Throwable) { /* fall back to raw ids */ }
}

$configurables = count($superAxes);
$variantRows   = count($childToParent);
if ($configurables > 0) {
    out(sprintf('  %d configurable products with %d variants between them', $configurables, $variantRows));
}

/* ------------------------------------------------------------ the sweep */

$linkSel  = $staged ? 'row_id AS link_id' : 'entity_id AS link_id';
$prodSize = $sizeFor('products', PRODUCT_BATCH);
$total   = $counts['products'];
$scanned = 0;
$offset  = 0;
$asVariant = 0;
$notes = [];   // type => count, for the summary

while (true) {
    $st = $pdo->prepare(
        'SELECT entity_id, ' . $linkSel . ', sku, type_id FROM ' . $t('catalog_product_entity') .
        ' WHERE 1' . $liveRow . ' ORDER BY entity_id LIMIT ? OFFSET ?'
    );
    $st->bindValue(1, $prodSize, PDO::PARAM_INT);
    $st->bindValue(2, $offset, PDO::PARAM_INT);
    $st->execute();
    $chunk = $st->fetchAll();
    if ($chunk === []) break;
    $offset += $prodSize;
    $scanned += count($chunk);

    $d = loadProductFields($pdo, $t, $chunk, $attrs, $eavKey, $baseUrl);

    /* Children of any configurable on this page, loaded as a second set. They
       sit at arbitrary entity ids, so they cannot come from the page itself. */
    $childIds = [];
    foreach ($chunk as $r) {
        if ($r['type_id'] !== 'configurable') continue;
        foreach ($parentChildren[(int)$r['link_id']] ?? [] as $c) { $childIds[$c] = true; }
    }
    $childIds = array_keys($childIds);

    $cd = [];            // child field data
    $childOptions = [];  // child entity_id => [attribute_id => option_id]
    if ($childIds) {
        $in = implode(',', array_fill(0, count($childIds), '?'));
        $s = $pdo->prepare(
            'SELECT entity_id, ' . $linkSel . ', sku, type_id FROM ' . $t('catalog_product_entity') .
            " WHERE entity_id IN ($in)" . $liveRow
        );
        $s->execute($childIds);
        $childRows = $s->fetchAll();
        $cd = loadProductFields($pdo, $t, $childRows, $attrs, $eavKey, $baseUrl);

        if ($superAttrIds && $childRows) {
            $childLinks = array_map(fn($r) => (int)$r['link_id'], $childRows);
            $entityOfLink = [];
            foreach ($childRows as $r) { $entityOfLink[(int)$r['link_id']] = (int)$r['entity_id']; }
            $inL = implode(',', array_fill(0, count($childLinks), '?'));
            $inA = implode(',', array_fill(0, count($superAttrIds), '?'));
            try {
                $s = $pdo->prepare(
                    "SELECT `$eavKey` AS k, attribute_id, value FROM " . $t('catalog_product_entity_int') .
                    " WHERE store_id = 0 AND attribute_id IN ($inA) AND `$eavKey` IN ($inL)"
                );
                $s->execute(array_merge($superAttrIds, $childLinks));
                foreach ($s->fetchAll() as $r) {
                    $e = $entityOfLink[(int)$r['k']] ?? null;
                    if ($e !== null) $childOptions[$e][(int)$r['attribute_id']] = (int)$r['value'];
                }
            } catch (Throwable) { /* variants degrade to unlabelled options */ }
        }
    }

    $items = [];
    foreach ($chunk as $r) {
        $id   = (int)$r['entity_id'];
        $link = (int)$r['link_id'];
        $sku  = (string)$r['sku'];
        $type = (string)$r['type_id'];

        // Children are not products here, they arrive as their parent's
        // variants, and emitting them twice would double the catalogue.
        if (isset($childToParent[$id])) { $asVariant++; continue; }

        // Grouped products are a display device: the members are ordinary
        // products already coming through on their own. Importing the wrapper
        // would create an unbuyable listing.
        if ($type === 'grouped') { $notes['grouped'] = ($notes['grouped'] ?? 0) + 1; continue; }

        $variants = [];
        if ($type === 'configurable') {
            $axes = $superAxes[$link] ?? [];
            foreach ($parentChildren[$link] ?? [] as $childId) {
                [$childPrice, $childWas] = effectivePrice(
                    $cd['price'][$childId] ?? null,
                    $cd['special'][$childId] ?? null,
                    $cd['from'][$childId] ?? null,
                    $cd['to'][$childId] ?? null,
                );
                // An unpriced child is not sellable. Counted, not swallowed:
                // a combination that quietly disappears is worse than one the
                // merchant is told to go and price.
                if ($childPrice === null) {
                    $notes['unpriced_variants'] = ($notes['unpriced_variants'] ?? 0) + 1;
                    continue;
                }

                $options = [];
                foreach ($axes as $attrId => $label) {
                    $optId = $childOptions[$childId][$attrId] ?? null;
                    if ($optId !== null) $options[$label] = $optionLabel[$optId] ?? ('#' . $optId);
                }

                $variants[] = [
                    'externalId'     => (string)$childId,
                    'title'          => implode(' / ', array_values($options)),
                    'sku'            => $cd['sku'][$childId] ?? null,
                    'price'          => $childPrice,
                    'compareAtPrice' => $childWas,
                    'inventoryQty'   => isset($cd['stock'][$childId]) ? (int)$cd['stock'][$childId]['qty'] : null,
                    'options'        => $options,
                ];

                // Variant photography is usually only on the children, so the
                // parent gallery alone would show one colour of five.
                foreach (array_slice($cd['images'][$childId] ?? [], 0, 2) as $img) {
                    $d['images'][$id][] = $img;
                }
            }

            if ($variants === []) {
                $notes['configurable_empty'] = ($notes['configurable_empty'] ?? 0) + 1;
                continue;
            }
        }

        if ($type === 'bundle') {
            // A bundle is a customer-assembled kit; there is no flat equivalent.
            // It comes across as a single product at its own price so the
            // listing and its SEO survive, and is flagged for rebuilding rather
            // than dropped silently.
            $notes['bundle'] = ($notes['bundle'] ?? 0) + 1;
        }

        [$ownPrice, $ownWas] = effectivePrice(
            $d['price'][$id] ?? null,
            $d['special'][$id] ?? null,
            $d['from'][$id] ?? null,
            $d['to'][$id] ?? null,
        );
        // A configurable's own price attribute is empty or stale; what it costs
        // is whatever its cheapest buyable child costs.
        $price = $variants ? min(array_map(fn($v) => $v['price'], $variants)) : ($ownPrice ?? 0);

        $description = $d['desc'][$id] ?? ($d['shortDesc'][$id] ?? null);

        // Handles must be unique on the target. Magento allows two products to
        // share a url_key across store scopes, so the SKU is the tiebreaker.
        $handle = $d['urlKey'][$id] ?? null;

        $items[] = [
            'externalId'     => (string)$id,
            'sku'            => $sku,
            'handle'         => $handle,
            'title'          => $d['name'][$id] ?? $sku,
            'description'    => $description,
            'status'         => (int)($d['status'][$id] ?? 1) === 1 ? 'active' : 'draft',
            'price'          => $price,
            'compareAtPrice' => $variants ? null : $ownWas,
            'inventoryQty'   => $variants ? null : (isset($d['stock'][$id]) ? (int)$d['stock'][$id]['qty'] : null),
            'images'         => array_values(array_unique(array_slice($d['images'][$id] ?? [], 0, 12))),
            'categories'     => array_values(array_filter(array_map(
                fn($cid) => $categoryHandle[$cid] ?? null,
                $d['cats'][$id] ?? []
            ))),
            'variants'       => $variants,
        ];
    }

    tally('products', send('products', $items, $key));
    out(sprintf('  %d/%d scanned', $scanned, $total));
}

if ($asVariant > 0) out(sprintf('  %d products were folded into their parent as variants', $asVariant));
if (!empty($notes['grouped'])) out(sprintf('  %d grouped products skipped, their members import individually', $notes['grouped']));
if (!empty($notes['bundle'])) out(sprintf('  %d bundles imported as single products, rebuild their options in the dashboard', $notes['bundle']));
if (!empty($notes['configurable_empty'])) out(sprintf('  %d configurables had no priced children and were skipped', $notes['configurable_empty']));
if (!empty($notes['unpriced_variants'])) out(sprintf('  %d variant combinations had no price in Magento and were left out, price them there and re-run, or add them in the dashboard', $notes['unpriced_variants']));

/* ----------------------------------------------------------- customers */

out('Customers…');
$offset = 0;
$done = 0;
while (true) {
    $st = $pdo->prepare(
        'SELECT entity_id, email, firstname, lastname, is_active FROM ' . $t('customer_entity') . ' ORDER BY entity_id LIMIT ? OFFSET ?'
    );
    $st->bindValue(1, BATCH_SIZE, PDO::PARAM_INT);
    $st->bindValue(2, $offset, PDO::PARAM_INT);
    $st->execute();
    $chunk = $st->fetchAll();
    if ($chunk === []) break;
    $offset += BATCH_SIZE;

    $items = [];
    foreach ($chunk as $r) {
        $items[] = [
            'externalId' => (string)$r['entity_id'],
            'email'      => (string)$r['email'],
            'firstName'  => $r['firstname'] ?: null,
            'lastName'   => $r['lastname'] ?: null,
            // Newsletter opt-in lives in a separate table; not carried over
            // deliberately, because importing a consent flag you cannot prove
            // is a GDPR problem waiting to happen.
            'acceptsMarketing' => false,
        ];
    }
    tally('customers', send('customers', $items, $key));
    $done += count($chunk);
    out(sprintf('  %d/%d', $done, $counts['customers']));
}

/* -------------------------------------------------------------- orders */

/**
 * WHAT AN IMPORTED ORDER IS
 *
 * A record, not a transaction. Nothing is replayed: no payment is taken, no
 * stock moves, no confirmation email is sent. The money changed hands years ago
 * on this very server, and the only correct thing to do with that fact is write
 * it down so the merchant keeps their history, their customers' lifetime value
 * and their ability to answer "what did I ship you in 2019?".
 *
 * Orders go last because they reference products by SKU and customers by email,
 * both of which have already been sent.
 */
if ($wanted('orders') && $counts['orders'] > 0) {
    out('Orders…');

    /**
     * Magento's state, not its status: `status` is a merchant-editable label
     * ("Awaiting Pickup") and varies by shop, while `state` is the fixed
     * machine value underneath it.
     */
    $mapState = function (string $state, float $paid, float $refunded, float $total): array {
        $payment = 'paid';
        if ($refunded > 0 && $refunded + 0.001 >= $total)  { $payment = 'refunded'; }
        elseif ($refunded > 0)                              { $payment = 'partially_refunded'; }
        elseif ($paid <= 0)                                 { $payment = 'unpaid'; }

        return match ($state) {
            'new', 'holded'   => ['pending',   $paid > 0 ? $payment : 'unpaid', 'unfulfilled'],
            'pending_payment' => ['pending',   'unpaid',     'unfulfilled'],
            'payment_review'  => ['pending',   'authorized', 'unfulfilled'],
            'processing'      => ['paid',      $payment,     'unfulfilled'],
            // Only a FULL refund makes the order refunded. A partial one is a
            // fulfilled order that gave some money back, and saying otherwise
            // would misstate the merchant's revenue.
            'complete'        => [$payment === 'refunded' ? 'refunded' : 'fulfilled', $payment, 'fulfilled'],
            'closed'          => ['refunded',  $refunded > 0 ? $payment : 'refunded', 'fulfilled'],
            'canceled'        => ['cancelled', $refunded > 0 ? $payment : 'unpaid', 'unfulfilled'],
            default           => ['paid',      $payment,     'unfulfilled'],
        };
    };

    $street = function (?string $s): array {
        $parts = array_values(array_filter(array_map('trim', explode("\n", (string)$s))));
        return [$parts[0] ?? null, count($parts) > 1 ? implode(', ', array_slice($parts, 1)) : null];
    };

    $orderBatch = $sizeFor('orders', 50);
    $offset = 0;
    $done   = 0;
    $noItems = 0;

    while (true) {
        $st = $pdo->prepare(
            'SELECT entity_id, increment_id, customer_email, status, state, created_at,
                    order_currency_code, subtotal, discount_amount, shipping_amount, tax_amount,
                    grand_total, total_paid, total_refunded, coupon_code
             FROM ' . $t('sales_order') . ' ORDER BY entity_id LIMIT ? OFFSET ?'
        );
        $st->bindValue(1, $orderBatch, PDO::PARAM_INT);
        $st->bindValue(2, $offset, PDO::PARAM_INT);
        $st->execute();
        $chunk = $st->fetchAll();
        if ($chunk === []) break;
        $offset += $orderBatch;

        $ids = array_map(fn($r) => (int)$r['entity_id'], $chunk);
        $in  = implode(',', array_fill(0, count($ids), '?'));

        // Line items and addresses for the whole page in one query each.
        $lines = [];
        try {
            $s = $pdo->prepare(
                'SELECT item_id, order_id, parent_item_id, sku, name, product_type,
                        qty_ordered, price, row_total
                 FROM ' . $t('sales_order_item') . " WHERE order_id IN ($in) ORDER BY item_id"
            );
            $s->execute($ids);
            foreach ($s->fetchAll() as $r) { $lines[(int)$r['order_id']][] = $r; }
        } catch (Throwable $e) {
            fail('Could not read order line items: ' . $e->getMessage());
        }

        $addr = [];
        try {
            $s = $pdo->prepare(
                'SELECT parent_id, address_type, firstname, lastname, company, street,
                        city, region, postcode, country_id, telephone
                 FROM ' . $t('sales_order_address') . " WHERE parent_id IN ($in)"
            );
            $s->execute($ids);
            foreach ($s->fetchAll() as $r) { $addr[(int)$r['parent_id']][(string)$r['address_type']] = $r; }
        } catch (Throwable) { /* addresses are optional */ }

        $items = [];
        foreach ($chunk as $o) {
            $id = (int)$o['entity_id'];

            $paid     = (float)($o['total_paid'] ?? 0);
            $refunded = (float)($o['total_refunded'] ?? 0);
            $total    = (float)($o['grand_total'] ?? 0);
            [$status, $paymentStatus, $fulfillmentStatus] = $mapState((string)$o['state'], $paid, $refunded, $total);

            /**
             * A configurable sale writes TWO rows: the configurable itself,
             * carrying the price and quantity, and the simple child underneath
             * it at zero. Sending both would double every such line, so only
             * top-level rows are taken, but the child's SKU is the one that
             * identifies the variant, so it is lifted onto the parent line.
             */
            $childSku = [];
            foreach ($lines[$id] ?? [] as $l) {
                if ($l['parent_item_id'] !== null) { $childSku[(int)$l['parent_item_id']] = (string)$l['sku']; }
            }

            $orderLines = [];
            foreach ($lines[$id] ?? [] as $l) {
                if ($l['parent_item_id'] !== null) continue;
                $qty = (float)$l['qty_ordered'];
                if ($qty <= 0) continue;
                $orderLines[] = [
                    'title'     => (string)$l['name'],
                    'sku'       => $childSku[(int)$l['item_id']] ?? (string)$l['sku'],
                    'quantity'  => (int)round($qty),
                    'unitPrice' => (int)round(((float)$l['price']) * 100),
                    'total'     => (int)round(((float)$l['row_total']) * 100),
                ];
            }
            if ($orderLines === []) { $noItems++; }

            $mk = function (?array $a) use ($street): ?array {
                if (!$a) return null;
                [$l1, $l2] = $street($a['street'] ?? null);
                return [
                    'firstName'  => $a['firstname'] ?: null,
                    'lastName'   => $a['lastname'] ?: null,
                    'company'    => $a['company'] ?: null,
                    'line1'      => $l1,
                    'line2'      => $l2,
                    'city'       => $a['city'] ?: null,
                    'province'   => $a['region'] ?: null,
                    'postalCode' => $a['postcode'] ?: null,
                    'country'    => $a['country_id'] ?: null,
                    'phone'      => $a['telephone'] ?: null,
                ];
            };

            $items[] = [
                'externalId'        => (string)$id,
                'reference'         => (string)$o['increment_id'],
                'email'             => (string)$o['customer_email'],
                'placedAt'          => (string)$o['created_at'],
                'paidAt'            => $paid > 0 ? (string)$o['created_at'] : null,
                'currency'          => $o['order_currency_code'] ?: 'EUR',
                'status'            => $status,
                'paymentStatus'     => $paymentStatus,
                'fulfillmentStatus' => $fulfillmentStatus,
                'subtotal'          => (int)round(((float)$o['subtotal']) * 100),
                // Magento stores discounts as a negative amount.
                'discountTotal'     => (int)round(abs((float)$o['discount_amount']) * 100),
                'shippingTotal'     => (int)round(((float)$o['shipping_amount']) * 100),
                'taxTotal'          => (int)round(((float)$o['tax_amount']) * 100),
                'total'             => (int)round($total * 100),
                'discountCode'      => $o['coupon_code'] ?: null,
                'shippingAddress'   => $mk($addr[$id]['shipping'] ?? null),
                'billingAddress'    => $mk($addr[$id]['billing'] ?? null),
                'items'             => $orderLines,
            ];
        }

        tally('orders', send('orders', $items, $key));
        $done += count($chunk);
        out(sprintf('  %d/%d', $done, $counts['orders']));
    }

    if ($noItems > 0) out(sprintf('  %d orders had no line items and came across as totals only', $noItems));
} elseif ($counts['orders'] > 0) {
    out('Orders… skipped (this WhizzyCommerce shop is not accepting order history)');
}


/* ----------------------------------------------------------- promotions */

if ($wanted('promotions')) {
    out('Promotions…');

    /**
     * Rules restricted to some customer groups, not all of them.
     *
     * There are no customer groups here, so the restriction cannot come
     * across, and a rule for wholesale accounts would become a rule for
     * everybody. Counted rather than assumed: a Magento where every rule names
     * every group is the ordinary case and must not be flagged.
     */
    $groupCount = 0;
    try {
        $groupCount = (int)$pdo->query('SELECT COUNT(*) c FROM ' . $t('customer_group'))->fetch()['c'];
    } catch (Throwable) { $groupCount = 0; }

    $groupsNamed = function (string $table, string $idCol): array {
        try {
            $rows = $pdo->query('SELECT ' . $idCol . ' rid, COUNT(*) n FROM ' . $t($table) . ' GROUP BY ' . $idCol)->fetchAll();
        } catch (Throwable) { return []; }
        $out = [];
        foreach ($rows as $r) $out[(int)$r['rid']] = (int)$r['n'];
        return $out;
    };

    /* ------------------------------------------------------- cart rules */

    $items = [];
    $cartGroups = $groupsNamed('salesrule_customer_group', 'rule_id');

    try {
        $rules = $pdo->query('SELECT * FROM ' . $t('salesrule'))->fetchAll();
    } catch (Throwable) { $rules = []; }

    // The primary code per rule. Auto-generated codes are deliberately not
    // read: a rule can have tens of thousands of them, they are one-shot by
    // design, and importing them as one rule each would be nonsense.
    $codes = [];
    try {
        foreach ($pdo->query('SELECT rule_id, code, usage_limit, usage_per_customer, times_used, type FROM ' . $t('salesrule_coupon') . ' WHERE type = 0')->fetchAll() as $c) {
            $codes[(int)$c['rule_id']] = $c;
        }
    } catch (Throwable) { $codes = []; }

    foreach ($rules as $r) {
        $id     = (int)$r['rule_id'];
        $notes  = [];
        $action = (string)($r['simple_action'] ?? '');
        $amount = (float)($r['discount_amount'] ?? 0);
        $freeShipping = (int)($r['simple_free_shipping'] ?? 0);

        $kind = null; $value = 0; $buy = 0; $get = 0;
        if ($action === 'by_percent')      { $kind = 'percentage';     $value = (int)round($amount * 100); }
        elseif ($action === 'by_fixed')    { $kind = 'fixed_per_item'; $value = (int)round($amount * 100); }
        elseif ($action === 'cart_fixed')  { $kind = 'fixed';          $value = (int)round($amount * 100); }
        elseif ($action === 'buy_x_get_y') {
            $kind = 'buy_x_get_y';
            $buy  = max(1, (int)($r['discount_step'] ?? 0));
            $get  = max(1, (int)round($amount));
        } elseif ($freeShipping > 0)       { $kind = 'free_shipping'; }

        if ($kind === null) {
            tally('promotions', ['seen' => 1, 'skipped' => 1]);
            continue; // an action this shop has no equivalent for
        }

        /*
         * Magento's 1 is "free shipping on the matching items only", which
         * here can only be free shipping on the order. That is more generous
         * than the merchant wrote, so it goes across switched off.
         */
        if ($freeShipping === 1) $notes[] = 'it gave free shipping on matching items only, which becomes free shipping on the whole order';
        if ((int)($r['apply_to_shipping'] ?? 0) === 1) $notes[] = 'it also discounted the shipping charge';

        if ($groupCount > 0 && ($cartGroups[$id] ?? $groupCount) < $groupCount) {
            $notes[] = 'it was limited to some customer groups, which this shop does not have';
        }

        [$cond,  $agg,     $n1] = flattenConditions($r['conditions_serialized'] ?? null, $categoryHandle);
        [$items_, $itemAgg, $n2] = flattenConditions($r['actions_serialized'] ?? null, $categoryHandle);
        $notes = array_merge($notes, $n1, $n2);

        $coupon = $codes[$id] ?? null;
        $couponType = (int)($r['coupon_type'] ?? 1);
        if ($couponType === 3 && $coupon === null) {
            $notes[] = 'its coupon codes were auto-generated, and those were not brought across';
        }

        $items[] = [
            'externalId'       => (string)$id,
            'scope'            => 'cart',
            'name'             => (string)($r['name'] ?? ('Rule ' . $id)),
            'description'      => (string)($r['description'] ?? ''),
            'code'             => $coupon['code'] ?? null,
            'kind'             => $kind,
            'value'            => $value,
            'buyQty'           => $buy,
            'getQty'           => $get,
            'freeShipping'     => $freeShipping > 0,
            'maxQty'           => ((int)($r['discount_qty'] ?? 0)) ?: null,
            'usageLimit'       => isset($coupon['usage_limit']) ? ((int)$coupon['usage_limit'] ?: null) : null,
            'perCustomerLimit' => ((int)($r['uses_per_customer'] ?? 0)) ?: null,
            'usageCount'       => (int)($coupon['times_used'] ?? $r['times_used'] ?? 0),
            'priority'         => (int)($r['sort_order'] ?? 0),
            'stopFurther'      => (bool)($r['stop_rules_processing'] ?? false),
            'isActive'         => (bool)($r['is_active'] ?? false),
            'startsAt'         => ruleDate($r['from_date'] ?? null),
            'endsAt'           => ruleDate($r['to_date'] ?? null),
            'matchMode'        => $agg,
            'conditions'       => $cond,
            'itemMatchMode'    => $itemAgg,
            'itemConditions'   => $items_,
            'partial'          => $notes !== [],
            'notes'            => $notes,
        ];
    }

    /* ---------------------------------------------------- catalog rules */

    $catGroups = $groupsNamed('catalogrule_customer_group', 'rule_id');
    try {
        $catRules = $pdo->query('SELECT * FROM ' . $t('catalogrule'))->fetchAll();
    } catch (Throwable) { $catRules = []; }

    foreach ($catRules as $r) {
        $id     = (int)$r['rule_id'];
        $notes  = [];
        $action = (string)($r['simple_action'] ?? '');
        $amount = (float)($r['discount_amount'] ?? 0);

        $kind = null; $value = 0;
        if ($action === 'by_percent')     { $kind = 'percentage';  $value = (int)round($amount * 100); }
        elseif ($action === 'by_fixed')   { $kind = 'fixed';       $value = (int)round($amount * 100); }
        elseif ($action === 'to_fixed')   { $kind = 'fixed_price'; $value = (int)round($amount * 100); }
        elseif ($action === 'to_percent') {
            // "To 80% of the original" is a 20% discount. Same offer, said the
            // way this engine says it, rather than a fifth kind nobody needs.
            $kind  = 'percentage';
            $value = (int)round(max(0, 100 - $amount) * 100);
        }

        if ($kind === null) {
            tally('promotions', ['seen' => 1, 'skipped' => 1]);
            continue;
        }

        if ($groupCount > 0 && ($catGroups[$id] ?? $groupCount) < $groupCount) {
            $notes[] = 'it was limited to some customer groups, which this shop does not have';
        }

        [$cond, $agg, $n1] = flattenConditions($r['conditions_serialized'] ?? null, $categoryHandle);
        $notes = array_merge($notes, $n1);

        $items[] = [
            'externalId'  => 'cat-' . $id,
            'scope'       => 'catalog',
            'name'        => (string)($r['name'] ?? ('Rule ' . $id)),
            'description' => (string)($r['description'] ?? ''),
            'kind'        => $kind,
            'value'       => $value,
            'priority'    => (int)($r['sort_order'] ?? 0),
            'stopFurther' => (bool)($r['stop_rules_processing'] ?? false),
            'isActive'    => (bool)($r['is_active'] ?? false),
            'startsAt'    => ruleDate($r['from_date'] ?? null),
            'endsAt'      => ruleDate($r['to_date'] ?? null),
            'matchMode'   => $agg,
            'conditions'  => $cond,
            'partial'     => $notes !== [],
            'notes'       => $notes,
        ];
    }

    foreach (array_chunk($items, $sizeFor('promotions', 100)) as $chunk) {
        tally('promotions', send('promotions', $chunk, $key));
    }
    out(sprintf('  %d cart rules, %d catalogue rules', count($rules), count($catRules)));
    if ($items !== []) {
        $off = count(array_filter($items, fn($i) => $i['partial'] || !$i['isActive']));
        if ($off > 0) out(sprintf('  %d will arrive switched off, see the problems below', $off));
    }
}

/* -------------------------------------------------------------- finish */

$final = call('/import/finish', ['platform' => 'magento2', 'version' => $magentoVersion, 'counts' => $counts], $key);

out('');
out(str_repeat('-', 64));
foreach (($final['progress'] ?? []) as $entity => $c) {
    out(sprintf(
        '%-12s seen %-7d imported %-7d skipped %-7d failed %d',
        $entity, $c['seen'] ?? 0, $c['imported'] ?? 0, $c['skipped'] ?? 0, $c['failed'] ?? 0
    ));
}
if (!empty($final['problems'])) {
    out('');
    out('Problems (first ' . count($final['problems']) . '):');
    foreach ($final['problems'] as $p) {
        out(sprintf('  %-10s %-12s %s', $p['entity'] ?? '?', $p['ref'] ?? '?', $p['reason'] ?? ''));
    }
}
out('');
out($dryRun
    ? 'Dry run complete. Nothing was written. Review the numbers above, then start the real import from your dashboard.'
    : 'Import complete. Check your products and customers in the WhizzyCommerce dashboard.');
out('');
out('Now DELETE this file: rm ' . __FILE__);