<?php
declare(strict_types=1);

header('Content-Type: application/json; charset=utf-8');

function json_response(int $status, array $payload): void
{
    http_response_code($status);
    echo json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    exit;
}

function parse_env_file(string $path): array
{
    if (!is_file($path)) {
        return [];
    }

    $result = [];
    $lines = file($path, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
    if ($lines === false) {
        return [];
    }

    foreach ($lines as $line) {
        $trimmed = trim($line);
        if ($trimmed === '' || str_starts_with($trimmed, '#')) {
            continue;
        }

        $pos = strpos($trimmed, '=');
        if ($pos === false) {
            continue;
        }

        $key = trim(substr($trimmed, 0, $pos));
        $value = trim(substr($trimmed, $pos + 1));
        if ($key === '') {
            continue;
        }

        if (strlen($value) >= 2) {
            $first = $value[0];
            $last = $value[strlen($value) - 1];
            if (($first === '"' && $last === '"') || ($first === "'" && $last === "'")) {
                $value = substr($value, 1, -1);
            }
        }

        $result[$key] = $value;
    }

    return $result;
}

function expand_env_values(array $env): array
{
    $expanded = $env;
    $pattern = '/\$\{([A-Za-z_][A-Za-z0-9_]*)\}/';

    foreach ($expanded as $key => $value) {
        $expanded[$key] = preg_replace_callback(
            $pattern,
            static function (array $matches) use (&$expanded): string {
                $lookup = $matches[1];
                return (string) ($expanded[$lookup] ?? getenv($lookup) ?: '');
            },
            (string) $value
        ) ?? (string) $value;
    }

    return $expanded;
}

function env_value(string $key, array $localEnv, string $default = ''): string
{
    $runtime = getenv($key);
    if ($runtime !== false && $runtime !== '') {
        return $runtime;
    }

    if (isset($localEnv[$key]) && $localEnv[$key] !== '') {
        return (string) $localEnv[$key];
    }

    return $default;
}

function parse_number(mixed $value): ?float
{
    if ($value === null || $value === '') {
        return null;
    }

    if (is_int($value) || is_float($value)) {
        return (float) $value;
    }

    if (!is_string($value)) {
        return null;
    }

    $normalized = str_replace(["\u{00A0}", ' '], '', trim($value));
    $normalized = str_replace(',', '.', $normalized);

    if (!is_numeric($normalized)) {
        return null;
    }

    return (float) $normalized;
}

function lookup_method_id(PDO $pdo, string $table, ?string $code): ?int
{
    if ($code === null || $code === '') {
        return null;
    }

    $stmt = $pdo->prepare("SELECT id FROM public.{$table} WHERE code = :code LIMIT 1");
    $stmt->execute([':code' => $code]);
    $id = $stmt->fetchColumn();
    return $id === false ? null : (int) $id;
}

function map_payment_code(string $input): ?string
{
    $v = strtolower(trim($input));
    if ($v === '') {
        return null;
    }

    if (str_contains($v, 'twint')) {
        return 'twint';
    }

    if (str_contains($v, 'bank') || str_contains($v, 'vorauskasse') || str_contains($v, 'ueberweisung')) {
        return 'bank_transfer';
    }

    if (str_contains($v, 'kredit') || str_contains($v, 'debit') || str_contains($v, 'card')) {
        return 'card';
    }

    return null;
}

function map_shipping_code(string $input): ?string
{
    $v = strtolower(trim($input));
    if ($v === '') {
        return null;
    }

    if (str_contains($v, 'abholung') || str_contains($v, 'pickup')) {
        return 'pickup';
    }

    if (str_contains($v, 'post') || str_contains($v, 'versand')) {
        return 'post_standard';
    }

    return null;
}

function connect_database(array $localEnv): PDO
{
    $databaseUrl = env_value('DATABASE_URL', $localEnv);
    if ($databaseUrl !== '') {
        $parts = parse_url($databaseUrl);
        if ($parts !== false && ($parts['scheme'] ?? '') === 'postgresql') {
            $host = (string) ($parts['host'] ?? '');
            $port = (string) ($parts['port'] ?? '5432');
            $dbName = ltrim((string) ($parts['path'] ?? ''), '/');
            $user = (string) ($parts['user'] ?? '');
            $pass = (string) ($parts['pass'] ?? '');
            if ($host !== '' && $dbName !== '' && $user !== '') {
                $dsn = "pgsql:host={$host};port={$port};dbname={$dbName}";
                return new PDO($dsn, $user, $pass, [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                ]);
            }
        }
    }

    $host = env_value('DB_HOST', $localEnv);
    $port = env_value('DB_PORT', $localEnv, '5432');
    $dbName = env_value('DB_NAME', $localEnv);
    $user = env_value('DB_USER', $localEnv);
    $pass = env_value('DB_PASSWORD', $localEnv);

    if ($host === '' || $dbName === '' || $user === '') {
        throw new RuntimeException('Missing DB configuration');
    }

    $dsn = "pgsql:host={$host};port={$port};dbname={$dbName}";
    return new PDO($dsn, $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
}

function ensure_required_tables_exist(PDO $pdo): void
{
    $required = [
        'party',
        'address',
        'sales_order',
        'sales_order_line',
        'payment_method',
        'shipping_method',
    ];

    $stmt = $pdo->query(
        "SELECT table_name
         FROM information_schema.tables
         WHERE table_schema = 'public'"
    );
    $rows = $stmt->fetchAll(PDO::FETCH_COLUMN);
    $existing = array_map('strval', $rows ?: []);
    $missing = array_values(array_diff($required, $existing));

    if ($missing !== []) {
        throw new RuntimeException(
            'DB schema not initialized. Missing tables: ' . implode(', ', $missing)
        );
    }
}

function derive_label_webhook_url(array $localEnv): string
{
    $explicit = env_value('N8N_LABEL_WEBHOOK_URL', $localEnv);
    if ($explicit !== '') {
        return $explicit;
    }

    $legacy = env_value('N8N_OUTBOUND_URL_ADRESSE', $localEnv);
    if ($legacy !== '' && str_contains(strtolower($legacy), 'adressetikette')) {
        return $legacy;
    }

    $base = env_value('N8N_BASE_URL', $localEnv);
    if ($base === '') {
        return '';
    }

    $root = preg_replace('#/api/v1/?$#', '', rtrim($base, '/'));
    if (!is_string($root) || $root === '') {
        return '';
    }

    return $root . '/webhook/naurua_erp_adressetikette';
}

function post_json(string $url, array $payload, array $headers = [], int $timeoutSeconds = 15): array
{
    $body = json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    if ($body === false) {
        return ['ok' => false, 'status' => 0, 'body' => '', 'error' => 'Could not encode payload'];
    }

    $headerLines = ['Content-Type: application/json'];
    foreach ($headers as $name => $value) {
        if ($name === '' || $value === '') {
            continue;
        }
        $headerLines[] = $name . ': ' . $value;
    }

    $context = stream_context_create([
        'http' => [
            'method' => 'POST',
            'header' => implode("\r\n", $headerLines),
            'content' => $body,
            'timeout' => $timeoutSeconds,
            'ignore_errors' => true,
        ],
    ]);

    $responseBody = @file_get_contents($url, false, $context);
    $responseHeaders = $http_response_header ?? [];

    $status = 0;
    if (isset($responseHeaders[0]) && preg_match('#HTTP/\S+\s+(\d{3})#', $responseHeaders[0], $m) === 1) {
        $status = (int) $m[1];
    }

    if ($responseBody === false) {
        $responseBody = '';
    }

    return [
        'ok' => $status >= 200 && $status < 300,
        'status' => $status,
        'body' => substr($responseBody, 0, 500),
        'error' => ($status === 0) ? 'Request failed or timed out' : '',
    ];
}

function trigger_shipping_label_flow(array $order, array $localEnv): array
{
    $url = derive_label_webhook_url($localEnv);
    if ($url === '') {
        return [
            'enabled' => false,
            'ok' => false,
            'message' => 'Label webhook URL not configured',
        ];
    }

    $payload = [
        'BestellungNr' => (string) ($order['BestellungNr'] ?? ''),
        'Vorname_LfAdr' => (string) ($order['Vorname_LfAdr'] ?? $order['Vorname'] ?? ''),
        'Nachname_LfAdr' => (string) ($order['Nachname_LfAdr'] ?? $order['Nachname'] ?? ''),
        'Strasse_LfAdr' => (string) ($order['Strasse_LfAdr'] ?? $order['Strasse'] ?? ''),
        'Hausnummer_LfAdr' => (string) ($order['Hausnummer_LfAdr'] ?? $order['Hausnummer'] ?? ''),
        'PLZ_LfAdr' => (string) ($order['PLZ_LfAdr'] ?? $order['PLZ'] ?? ''),
        'Stadt_LfAdr' => (string) ($order['Stadt_LfAdr'] ?? $order['Stadt'] ?? ''),
        'Land_LfAdr' => (string) ($order['Land_LfAdr'] ?? $order['Land'] ?? ''),
        // Also include flat keys to be compatible with both mapping and direct template usage.
        'Vorname' => (string) ($order['Vorname_LfAdr'] ?? $order['Vorname'] ?? ''),
        'Nachname' => (string) ($order['Nachname_LfAdr'] ?? $order['Nachname'] ?? ''),
        'Strasse' => (string) ($order['Strasse_LfAdr'] ?? $order['Strasse'] ?? ''),
        'Hausnummer' => (string) ($order['Hausnummer_LfAdr'] ?? $order['Hausnummer'] ?? ''),
        'PLZ' => (string) ($order['PLZ_LfAdr'] ?? $order['PLZ'] ?? ''),
        'Stadt' => (string) ($order['Stadt_LfAdr'] ?? $order['Stadt'] ?? ''),
        'Land' => (string) ($order['Land_LfAdr'] ?? $order['Land'] ?? ''),
    ];

    $headers = [];
    $secret = env_value('N8N_WEBHOOK_SECRET', $localEnv);
    if ($secret !== '') {
        $headers['X-Webhook-Secret'] = $secret;
        $headers['X-N8N-Secret'] = $secret;
        $headers['X-API-Key'] = $secret;
        $headers['Authorization'] = 'Bearer ' . $secret;
    }

    $result = post_json($url, $payload, $headers, 20);

    return [
        'enabled' => true,
        'ok' => $result['ok'],
        'status' => $result['status'],
        'url' => $url,
        'message' => $result['ok'] ? 'Label flow triggered' : ($result['error'] !== '' ? $result['error'] : 'Label flow returned non-2xx'),
        'responseBody' => $result['body'],
    ];
}

function find_or_create_party(PDO $pdo, array $data): int
{
    $email = trim((string) ($data['EmailKunde'] ?? ''));
    $firstName = trim((string) ($data['Vorname_RgAdr'] ?? ''));
    $lastName = trim((string) ($data['Nachname_RgAdr'] ?? ''));
    $name = trim($firstName . ' ' . $lastName);
    if ($name === '') {
        $name = 'Online-Shop Kunde';
    }

    if ($email !== '') {
        $findStmt = $pdo->prepare('SELECT id FROM public.party WHERE lower(email) = lower(:email) ORDER BY id ASC LIMIT 1');
        $findStmt->execute([':email' => $email]);
        $existing = $findStmt->fetchColumn();
        if ($existing !== false) {
            $partyId = (int) $existing;
            $updateStmt = $pdo->prepare('UPDATE public.party SET name = :name, updated_at = NOW() WHERE id = :id');
            $updateStmt->execute([':id' => $partyId, ':name' => $name]);
            return $partyId;
        }
    }

    $insertStmt = $pdo->prepare(
        'INSERT INTO public.party (type, name, email, status, created_at, updated_at)
         VALUES (\'customer\', :name, :email, \'active\', NOW(), NOW())
         RETURNING id'
    );
    $insertStmt->execute([
        ':name' => $name,
        ':email' => $email !== '' ? $email : null,
    ]);

    $id = $insertStmt->fetchColumn();
    if ($id === false) {
        throw new RuntimeException('Could not create party');
    }

    return (int) $id;
}

function upsert_addresses(PDO $pdo, int $partyId, array $data): void
{
    $delete = $pdo->prepare('DELETE FROM public.address WHERE party_id = :party_id AND type IN (\'billing\', \'shipping\')');
    $delete->execute([':party_id' => $partyId]);

    $insert = $pdo->prepare(
        'INSERT INTO public.address (
            party_id, type, first_name, last_name, street, house_number, zip, city, state_code, country_name, raw_payload, created_at, updated_at
         ) VALUES (
            :party_id, :type, :first_name, :last_name, :street, :house_number, :zip, :city, :state_code, :country_name, :raw_payload::jsonb, NOW(), NOW()
         )'
    );

    $insert->execute([
        ':party_id' => $partyId,
        ':type' => 'billing',
        ':first_name' => trim((string) ($data['Vorname_RgAdr'] ?? '')),
        ':last_name' => trim((string) ($data['Nachname_RgAdr'] ?? '')),
        ':street' => trim((string) ($data['Strasse_RgAdr'] ?? '')),
        ':house_number' => trim((string) ($data['Hausnummer_RgAdr'] ?? '')),
        ':zip' => trim((string) ($data['PLZ_RgAdr'] ?? '')),
        ':city' => trim((string) ($data['Stadt_RgAdr'] ?? '')),
        ':state_code' => trim((string) ($data['Bundesland_RgAdr'] ?? '')),
        ':country_name' => trim((string) ($data['Land_RgAdr'] ?? '')),
        ':raw_payload' => json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
    ]);

    $insert->execute([
        ':party_id' => $partyId,
        ':type' => 'shipping',
        ':first_name' => trim((string) ($data['Vorname_LfAdr'] ?? '')),
        ':last_name' => trim((string) ($data['Nachname_LfAdr'] ?? '')),
        ':street' => trim((string) ($data['Strasse_LfAdr'] ?? '')),
        ':house_number' => trim((string) ($data['Hausnummer_LfAdr'] ?? '')),
        ':zip' => trim((string) ($data['PLZ_LfAdr'] ?? '')),
        ':city' => trim((string) ($data['Stadt_LfAdr'] ?? '')),
        ':state_code' => trim((string) ($data['Bundesland_LfAdr'] ?? '')),
        ':country_name' => trim((string) ($data['Land_LfAdr'] ?? '')),
        ':raw_payload' => json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
    ]);
}

function normalize_title_key(string $value): string
{
    $value = trim(strtolower($value));
    $value = preg_replace('/\s+/', ' ', $value) ?? $value;
    return $value;
}

function normalize_match_key(string $value): string
{
    $value = trim($value);
    if ($value === '') {
        return '';
    }

    $ascii = $value;
    if (function_exists('iconv')) {
        $converted = iconv('UTF-8', 'ASCII//TRANSLIT//IGNORE', $value);
        if (is_string($converted) && $converted !== '') {
            $ascii = $converted;
        }
    }

    $ascii = strtolower($ascii);
    $ascii = preg_replace('/[^a-z0-9]+/', ' ', $ascii) ?? $ascii;
    $ascii = preg_replace('/\s+/', ' ', trim($ascii)) ?? $ascii;
    return $ascii;
}

function find_existing_order_id(PDO $pdo, string $externalRef): ?int
{
    $stmt = $pdo->prepare('SELECT id FROM public.sales_order WHERE external_ref = :external_ref LIMIT 1');
    $stmt->execute([':external_ref' => $externalRef]);
    $id = $stmt->fetchColumn();
    return $id === false ? null : (int) $id;
}

function get_default_location_ids(PDO $pdo): array
{
    $storageId = $pdo->query("SELECT id FROM public.location WHERE type = 'storage' ORDER BY id LIMIT 1")->fetchColumn();
    $dispatchId = $pdo->query("SELECT id FROM public.location WHERE type = 'dispatch' ORDER BY id LIMIT 1")->fetchColumn();

    if ($storageId === false || $dispatchId === false) {
        $warehouseId = $pdo->query('SELECT id FROM public.warehouse ORDER BY id LIMIT 1')->fetchColumn();
        if ($warehouseId === false) {
            $createWarehouse = $pdo->prepare(
                "INSERT INTO public.warehouse (code, name, created_at, updated_at)
                 VALUES ('MAIN', 'Main Warehouse', NOW(), NOW())
                 RETURNING id"
            );
            $createWarehouse->execute();
            $warehouseId = $createWarehouse->fetchColumn();
        }
        $warehouseId = (int) $warehouseId;

        if ($storageId === false) {
            $existingStorage = $pdo->prepare(
                "SELECT id FROM public.location
                 WHERE warehouse_id = :warehouse_id AND type = 'storage'
                 ORDER BY id LIMIT 1"
            );
            $existingStorage->execute([':warehouse_id' => $warehouseId]);
            $storageId = $existingStorage->fetchColumn();
            if ($storageId === false) {
                $insertStorage = $pdo->prepare(
                    "INSERT INTO public.location (warehouse_id, code, name, type, created_at, updated_at)
                     VALUES (:warehouse_id, 'STORAGE', 'Storage', 'storage', NOW(), NOW())
                     RETURNING id"
                );
                $insertStorage->execute([':warehouse_id' => $warehouseId]);
                $storageId = $insertStorage->fetchColumn();
            }
        }

        if ($dispatchId === false) {
            $existingDispatch = $pdo->prepare(
                "SELECT id FROM public.location
                 WHERE warehouse_id = :warehouse_id AND type = 'dispatch'
                 ORDER BY id LIMIT 1"
            );
            $existingDispatch->execute([':warehouse_id' => $warehouseId]);
            $dispatchId = $existingDispatch->fetchColumn();
            if ($dispatchId === false) {
                $insertDispatch = $pdo->prepare(
                    "INSERT INTO public.location (warehouse_id, code, name, type, created_at, updated_at)
                     VALUES (:warehouse_id, 'DISPATCH', 'Dispatch', 'dispatch', NOW(), NOW())
                     RETURNING id"
                );
                $insertDispatch->execute([':warehouse_id' => $warehouseId]);
                $dispatchId = $insertDispatch->fetchColumn();
            }
        }
    }

    if ($storageId === false || $dispatchId === false) {
        throw new RuntimeException('Missing required locations after auto-bootstrap');
    }

    return [
        'storage' => (int) $storageId,
        'dispatch' => (int) $dispatchId,
    ];
}

function resolve_sellable_item_id(PDO $pdo, string $articleNumber, string $title): ?int
{
    $articleNumber = trim($articleNumber);
    $title = trim($title);
    $titleNorm = normalize_title_key($title);

    $stmt = $pdo->prepare(
        "SELECT sellable_item_id
         FROM public.external_item_alias
         WHERE source_system = 'wix'
           AND is_active = TRUE
           AND (
             (:article_number <> '' AND external_article_number = :article_number)
             OR (:title_norm <> '' AND title_normalized = :title_norm)
             OR (:title <> '' AND lower(external_title) = lower(:title))
           )
         ORDER BY
           CASE
             WHEN :article_number <> '' AND external_article_number = :article_number THEN 0
             WHEN :title_norm <> '' AND title_normalized = :title_norm THEN 1
             ELSE 2
           END,
           id
         LIMIT 1"
    );
    $stmt->execute([
        ':article_number' => $articleNumber,
        ':title_norm' => $titleNorm,
        ':title' => $title,
    ]);
    $id = $stmt->fetchColumn();

    return $id === false ? null : (int) $id;
}

function get_item_components(PDO $pdo, int $sellableItemId): array
{
    $stmt = $pdo->prepare(
        'SELECT product_id, qty_per_item
         FROM public.sellable_item_component
         WHERE sellable_item_id = :sellable_item_id
         ORDER BY id'
    );
    $stmt->execute([':sellable_item_id' => $sellableItemId]);
    return $stmt->fetchAll();
}

function resolve_product_id_fallback(PDO $pdo, string $articleNumber, string $title): ?int
{
    $title = trim($title);

    if ($title !== '') {
        $stmt = $pdo->prepare('SELECT id FROM public.product WHERE lower(name) = lower(:name) ORDER BY id LIMIT 1');
        $stmt->execute([':name' => $title]);
        $id = $stmt->fetchColumn();
        if ($id !== false) {
            return (int) $id;
        }
    }

    return null;
}

function detect_product_family_key(string $normalizedName): ?string
{
    if (str_contains($normalizedName, 'lion') && str_contains($normalizedName, 'mane')) {
        return 'lionsmane';
    }
    if (str_contains($normalizedName, 'chaga')) {
        return 'chaga';
    }
    if (str_contains($normalizedName, 'reishi')) {
        return 'reishi';
    }
    if (str_contains($normalizedName, 'shiitake')) {
        return 'shiitake';
    }
    return null;
}

function title_contains_family(string $normalizedTitle, string $familyKey): bool
{
    if ($familyKey === 'lionsmane') {
        return str_contains($normalizedTitle, 'lion') && str_contains($normalizedTitle, 'mane');
    }
    return str_contains($normalizedTitle, $familyKey);
}

function infer_components_from_title(PDO $pdo, string $title): array
{
    $normalizedTitle = normalize_match_key($title);
    if ($normalizedTitle === '') {
        return [];
    }

    $products = $pdo->query("SELECT id, name FROM public.product WHERE status = 'active' ORDER BY id")->fetchAll();
    $components = [];
    $usedProductIds = [];

    foreach ($products as $product) {
        $productId = (int) $product['id'];
        $productNameNorm = normalize_match_key((string) ($product['name'] ?? ''));
        $familyKey = detect_product_family_key($productNameNorm);
        if ($familyKey === null) {
            continue;
        }

        if (!title_contains_family($normalizedTitle, $familyKey)) {
            continue;
        }

        if (isset($usedProductIds[$productId])) {
            continue;
        }

        $components[] = [
            'product_id' => $productId,
            'qty_per_item' => 1.0,
        ];
        $usedProductIds[$productId] = true;
    }

    return $components;
}

function find_alias_sellable_item_id(PDO $pdo, string $articleNumber, string $title): ?int
{
    $articleNumber = trim($articleNumber);
    $title = trim($title);
    $titleNorm = normalize_title_key($title);

    $stmt = $pdo->prepare(
        "SELECT sellable_item_id
         FROM public.external_item_alias
         WHERE source_system = 'wix'
           AND (
             (:article_number <> '' AND external_article_number = :article_number)
             OR (:title_norm <> '' AND title_normalized = :title_norm)
             OR (:title <> '' AND lower(external_title) = lower(:title))
           )
         ORDER BY id
         LIMIT 1
         FOR UPDATE"
    );
    $stmt->execute([
        ':article_number' => $articleNumber,
        ':title_norm' => $titleNorm,
        ':title' => $title,
    ]);
    $id = $stmt->fetchColumn();
    return $id === false ? null : (int) $id;
}

function ensure_sellable_mapping_from_title_components(
    PDO $pdo,
    string $articleNumber,
    string $title,
    array $components
): array {
    if ($components === []) {
        throw new RuntimeException('Cannot create sellable mapping without components');
    }

    $sellableItemId = find_alias_sellable_item_id($pdo, $articleNumber, $title);
    $createdSellable = false;

    if ($sellableItemId === null || !sellable_item_exists($pdo, $sellableItemId)) {
        $itemCodeSeed = trim($articleNumber) !== '' ? trim($articleNumber) : $title;
        $itemCode = ensure_unique_sellable_item_code($pdo, $itemCodeSeed);
        $displayName = trim($title) !== '' ? trim($title) : $itemCode;

        $insertSellable = $pdo->prepare(
            'INSERT INTO public.sellable_item (item_code, display_name, status, created_at, updated_at)
             VALUES (:item_code, :display_name, \'active\', NOW(), NOW())
             RETURNING id'
        );
        $insertSellable->execute([
            ':item_code' => $itemCode,
            ':display_name' => $displayName,
        ]);
        $id = $insertSellable->fetchColumn();
        if ($id === false) {
            throw new RuntimeException('Could not create sellable_item from title components');
        }
        $sellableItemId = (int) $id;
        $createdSellable = true;
    } else {
        $updateName = $pdo->prepare(
            'UPDATE public.sellable_item
             SET display_name = :display_name, updated_at = NOW()
             WHERE id = :id'
        );
        $updateName->execute([
            ':display_name' => trim($title) !== '' ? trim($title) : "AUTO-ITEM-{$sellableItemId}",
            ':id' => $sellableItemId,
        ]);
    }

    $syncComponent = $pdo->prepare(
        'INSERT INTO public.sellable_item_component (sellable_item_id, product_id, qty_per_item, created_at, updated_at)
         VALUES (:sellable_item_id, :product_id, :qty_per_item, NOW(), NOW())
         ON CONFLICT (sellable_item_id, product_id)
         DO UPDATE SET qty_per_item = EXCLUDED.qty_per_item, updated_at = NOW()'
    );
    $componentIds = [];
    foreach ($components as $component) {
        $productId = (int) $component['product_id'];
        $qtyPerItem = (float) $component['qty_per_item'];
        if ($productId <= 0 || $qtyPerItem <= 0) {
            continue;
        }
        $syncComponent->execute([
            ':sellable_item_id' => $sellableItemId,
            ':product_id' => $productId,
            ':qty_per_item' => $qtyPerItem,
        ]);
        $componentIds[] = $productId;
    }

    if ($componentIds !== []) {
        $placeholders = [];
        $params = [':sellable_item_id' => $sellableItemId];
        foreach ($componentIds as $idx => $productId) {
            $key = ':product_id_' . $idx;
            $placeholders[] = $key;
            $params[$key] = $productId;
        }
        $deleteStmt = $pdo->prepare(
            'DELETE FROM public.sellable_item_component
             WHERE sellable_item_id = :sellable_item_id
               AND product_id NOT IN (' . implode(', ', $placeholders) . ')'
        );
        $deleteStmt->execute($params);
    }

    $aliasChanged = ensure_alias_points_to_sellable_item($pdo, $sellableItemId, $articleNumber, $title);

    return [
        'sellableItemId' => $sellableItemId,
        'createdSellableItem' => $createdSellable,
        'aliasCreatedOrUpdated' => $aliasChanged,
    ];
}

function sellable_item_exists(PDO $pdo, int $sellableItemId): bool
{
    $stmt = $pdo->prepare('SELECT 1 FROM public.sellable_item WHERE id = :id LIMIT 1');
    $stmt->execute([':id' => $sellableItemId]);
    return $stmt->fetchColumn() !== false;
}

function find_sellable_item_for_product(PDO $pdo, int $productId): ?int
{
    $stmt = $pdo->prepare(
        'SELECT sellable_item_id
         FROM public.sellable_item_component
         WHERE product_id = :product_id
         ORDER BY id
         LIMIT 1'
    );
    $stmt->execute([':product_id' => $productId]);
    $id = $stmt->fetchColumn();
    return $id === false ? null : (int) $id;
}

function find_product_name(PDO $pdo, int $productId): ?string
{
    $stmt = $pdo->prepare('SELECT name FROM public.product WHERE id = :id LIMIT 1');
    $stmt->execute([':id' => $productId]);
    $name = $stmt->fetchColumn();
    if ($name === false) {
        return null;
    }
    $name = trim((string) $name);
    return $name === '' ? null : $name;
}

function ensure_unique_sellable_item_code(PDO $pdo, string $preferred): string
{
    $base = preg_replace('/[^A-Za-z0-9._-]+/', '-', trim($preferred)) ?? '';
    $base = trim($base, '-');
    if ($base === '') {
        $base = 'AUTO-ITEM';
    }
    $base = strtoupper(substr($base, 0, 60));

    $existsStmt = $pdo->prepare('SELECT 1 FROM public.sellable_item WHERE item_code = :item_code LIMIT 1');
    $candidate = $base;
    $suffix = 1;

    while (true) {
        $existsStmt->execute([':item_code' => $candidate]);
        if ($existsStmt->fetchColumn() === false) {
            return $candidate;
        }

        $suffix++;
        $prefixMaxLen = max(1, 60 - strlen((string) $suffix) - 1);
        $candidate = substr($base, 0, $prefixMaxLen) . '-' . $suffix;
    }
}

function ensure_alias_points_to_sellable_item(
    PDO $pdo,
    int $sellableItemId,
    string $articleNumber,
    string $title
): bool {
    $articleNumber = trim($articleNumber);
    $title = trim($title);
    $titleNorm = normalize_title_key($title);

    if ($articleNumber === '' && $title === '') {
        return false;
    }

    $findExisting = $pdo->prepare(
        "SELECT id, sellable_item_id
         FROM public.external_item_alias
         WHERE source_system = 'wix'
           AND (
             (:article_number <> '' AND external_article_number = :article_number)
             OR (:title_norm <> '' AND title_normalized = :title_norm)
             OR (:title <> '' AND lower(external_title) = lower(:title))
           )
         ORDER BY id
         LIMIT 1
         FOR UPDATE"
    );
    $findExisting->execute([
        ':article_number' => $articleNumber,
        ':title_norm' => $titleNorm,
        ':title' => $title,
    ]);
    $existing = $findExisting->fetch();

    if (is_array($existing)) {
        $existingSellable = isset($existing['sellable_item_id']) ? (int) $existing['sellable_item_id'] : 0;
        $aliasId = isset($existing['id']) ? (int) $existing['id'] : 0;
        if ($existingSellable === $sellableItemId) {
            $touchStmt = $pdo->prepare('UPDATE public.external_item_alias SET is_active = TRUE, updated_at = NOW() WHERE id = :id');
            $touchStmt->execute([':id' => $aliasId]);
            return false;
        }

        $updateStmt = $pdo->prepare(
            'UPDATE public.external_item_alias
             SET sellable_item_id = :sellable_item_id,
                 external_article_number = :article_number,
                 external_title = :title,
                 title_normalized = :title_norm,
                 is_active = TRUE,
                 updated_at = NOW()
             WHERE id = :id'
        );
        $updateStmt->execute([
            ':sellable_item_id' => $sellableItemId,
            ':article_number' => $articleNumber !== '' ? $articleNumber : null,
            ':title' => $title !== '' ? $title : null,
            ':title_norm' => $titleNorm !== '' ? $titleNorm : null,
            ':id' => $aliasId,
        ]);
        return true;
    }

    $insertStmt = $pdo->prepare(
        "INSERT INTO public.external_item_alias (
            source_system, external_article_number, external_title, title_normalized, sellable_item_id, is_active, created_at, updated_at
         ) VALUES (
            'wix', :article_number, :title, :title_norm, :sellable_item_id, TRUE, NOW(), NOW()
         )"
    );
    $insertStmt->execute([
        ':article_number' => $articleNumber !== '' ? $articleNumber : null,
        ':title' => $title !== '' ? $title : null,
        ':title_norm' => $titleNorm !== '' ? $titleNorm : null,
        ':sellable_item_id' => $sellableItemId,
    ]);
    return true;
}

function ensure_sellable_mapping_from_product_fallback(
    PDO $pdo,
    int $productId,
    string $articleNumber,
    string $title
): array {
    $articleNumber = trim($articleNumber);
    $title = trim($title);

    $sellableItemId = find_sellable_item_for_product($pdo, $productId);
    $createdSellable = false;
    if ($sellableItemId === null || !sellable_item_exists($pdo, $sellableItemId)) {
        $productName = find_product_name($pdo, $productId);
        $itemCodeSeed = $articleNumber !== '' ? $articleNumber : "AUTO-PROD-{$productId}";
        $itemCode = ensure_unique_sellable_item_code($pdo, $itemCodeSeed);
        $displayName = $title !== '' ? $title : ($productName ?? $itemCode);

        $insertSellable = $pdo->prepare(
            'INSERT INTO public.sellable_item (item_code, display_name, status, created_at, updated_at)
             VALUES (:item_code, :display_name, \'active\', NOW(), NOW())
             RETURNING id'
        );
        $insertSellable->execute([
            ':item_code' => $itemCode,
            ':display_name' => $displayName,
        ]);
        $id = $insertSellable->fetchColumn();
        if ($id === false) {
            throw new RuntimeException("Could not create sellable_item for fallback product {$productId}");
        }
        $sellableItemId = (int) $id;
        $createdSellable = true;
    }

    $insertComponent = $pdo->prepare(
        'INSERT INTO public.sellable_item_component (sellable_item_id, product_id, qty_per_item, created_at, updated_at)
         VALUES (:sellable_item_id, :product_id, 1.0, NOW(), NOW())
         ON CONFLICT (sellable_item_id, product_id) DO NOTHING'
    );
    $insertComponent->execute([
        ':sellable_item_id' => $sellableItemId,
        ':product_id' => $productId,
    ]);

    $aliasChanged = ensure_alias_points_to_sellable_item($pdo, $sellableItemId, $articleNumber, $title);

    return [
        'sellableItemId' => $sellableItemId,
        'createdSellableItem' => $createdSellable,
        'aliasCreatedOrUpdated' => $aliasChanged,
    ];
}

function get_current_lot_balance_for_update(PDO $pdo, int $productId): array
{
    $lotStmt = $pdo->prepare(
        "SELECT id
         FROM public.stock_lot
         WHERE product_id = :product_id
           AND status = 'current'
         ORDER BY id
         LIMIT 1
         FOR UPDATE"
    );
    $lotStmt->execute([':product_id' => $productId]);
    $lotId = $lotStmt->fetchColumn();
    if ($lotId === false) {
        throw new RuntimeException("No current lot found for product {$productId}");
    }

    $balStmt = $pdo->prepare('SELECT qty_net FROM public.v_stock_lot_balance WHERE stock_lot_id = :lot_id');
    $balStmt->execute([':lot_id' => (int) $lotId]);
    $qtyNet = $balStmt->fetchColumn();

    return [
        'lot_id' => (int) $lotId,
        'qty_net' => $qtyNet === false ? 0.0 : (float) $qtyNet,
    ];
}

function switch_current_lot(PDO $pdo, int $productId, int $oldCurrentLotId, int $storageLocationId): int
{
    $closeStmt = $pdo->prepare(
        "UPDATE public.stock_lot
         SET status = 'closed', updated_at = NOW()
         WHERE id = :id AND status = 'current'"
    );
    $closeStmt->execute([':id' => $oldCurrentLotId]);

    $openStmt = $pdo->prepare(
        "SELECT id
         FROM public.stock_lot
         WHERE product_id = :product_id
           AND status = 'open'
         ORDER BY id
         LIMIT 1
         FOR UPDATE"
    );
    $openStmt->execute([':product_id' => $productId]);
    $newCurrentLotId = $openStmt->fetchColumn();
    if ($newCurrentLotId === false) {
        throw new RuntimeException("No open lot available for product {$productId} during switch");
    }

    $makeCurrentStmt = $pdo->prepare(
        "UPDATE public.stock_lot
         SET status = 'current',
             lot_number = COALESCE(lot_number, :auto_lot_number),
             updated_at = NOW()
         WHERE id = :id"
    );
    $makeCurrentStmt->execute([
        ':id' => (int) $newCurrentLotId,
        ':auto_lot_number' => 'AUTO-' . $productId . '-' . (int) $newCurrentLotId,
    ]);

    $balStmt = $pdo->prepare('SELECT qty_net FROM public.v_stock_lot_balance WHERE stock_lot_id = :lot_id');
    $balStmt->execute([':lot_id' => (int) $newCurrentLotId]);
    $newCurrentQty = $balStmt->fetchColumn();
    $newCurrentQty = $newCurrentQty === false ? 0.0 : (float) $newCurrentQty;

    // Auto-seed newly promoted current lot so allocation can continue without manual stock-in.
    if ($newCurrentQty <= 0.0000001) {
        insert_stock_move_in(
            $pdo,
            $productId,
            (int) $newCurrentLotId,
            200.0,
            $storageLocationId,
            "auto-seed-current-lot:product={$productId}:lot=" . (int) $newCurrentLotId
        );
    }

    $createOpenStmt = $pdo->prepare(
        "INSERT INTO public.stock_lot (product_id, lot_number, status, created_at, updated_at)
         VALUES (:product_id, NULL, 'open', NOW(), NOW())"
    );
    $createOpenStmt->execute([':product_id' => $productId]);

    return (int) $newCurrentLotId;
}

function insert_stock_move_out(
    PDO $pdo,
    int $productId,
    int $lotId,
    float $qty,
    int $fromLocationId,
    int $toLocationId,
    string $note
): int {
    $stmt = $pdo->prepare(
        "INSERT INTO public.stock_move (
            product_id, lot_id, from_location_id, to_location_id, qty, move_type, note, move_date, created_at, updated_at
         ) VALUES (
            :product_id, :lot_id, :from_location_id, :to_location_id, :qty, 'out', :note, NOW(), NOW(), NOW()
         )
         RETURNING id"
    );
    $stmt->execute([
        ':product_id' => $productId,
        ':lot_id' => $lotId,
        ':from_location_id' => $fromLocationId,
        ':to_location_id' => $toLocationId,
        ':qty' => $qty,
        ':note' => $note,
    ]);
    $id = $stmt->fetchColumn();
    if ($id === false) {
        throw new RuntimeException('Could not create stock_move out');
    }
    return (int) $id;
}

function insert_stock_move_in(
    PDO $pdo,
    int $productId,
    int $lotId,
    float $qty,
    int $toLocationId,
    string $note
): int {
    $stmt = $pdo->prepare(
        "INSERT INTO public.stock_move (
            product_id, lot_id, from_location_id, to_location_id, qty, move_type, note, move_date, created_at, updated_at
         ) VALUES (
            :product_id, :lot_id, NULL, :to_location_id, :qty, 'in', :note, NOW(), NOW(), NOW()
         )
         RETURNING id"
    );
    $stmt->execute([
        ':product_id' => $productId,
        ':lot_id' => $lotId,
        ':to_location_id' => $toLocationId,
        ':qty' => $qty,
        ':note' => $note,
    ]);
    $id = $stmt->fetchColumn();
    if ($id === false) {
        throw new RuntimeException('Could not create stock_move in');
    }
    return (int) $id;
}

function reverse_existing_allocations_for_order(PDO $pdo, int $orderId, int $fallbackStorageLocationId): array
{
    $stmt = $pdo->prepare(
        "SELECT
            a.id AS allocation_id,
            a.product_id,
            a.lot_id,
            a.qty,
            a.stock_move_id,
            sm.from_location_id
         FROM public.sales_order_line sol
         JOIN public.sales_order_line_lot_allocation a ON a.sales_order_line_id = sol.id
         LEFT JOIN public.stock_move sm ON sm.id = a.stock_move_id
         WHERE sol.sales_order_id = :order_id
           AND a.stock_move_id IS NOT NULL"
    );
    $stmt->execute([':order_id' => $orderId]);
    $rows = $stmt->fetchAll();

    $reversedMoves = 0;
    $reversedQty = 0.0;

    foreach ($rows as $row) {
        $qty = (float) $row['qty'];
        if ($qty <= 0) {
            continue;
        }

        $toLocationId = $row['from_location_id'] !== null
            ? (int) $row['from_location_id']
            : $fallbackStorageLocationId;

        insert_stock_move_in(
            $pdo,
            (int) $row['product_id'],
            (int) $row['lot_id'],
            $qty,
            $toLocationId,
            "order-import-reverse:order={$orderId}:alloc=" . (int) $row['allocation_id']
        );

        $reversedMoves++;
        $reversedQty += $qty;
    }

    return [
        'reversedMoves' => $reversedMoves,
        'reversedQty' => round($reversedQty, 4),
    ];
}

function has_available_stock_for_product(PDO $pdo, int $productId, float $epsilon = 0.0000001): bool
{
    $stmt = $pdo->prepare(
        "SELECT 1
         FROM public.stock_lot sl
         JOIN public.v_stock_lot_balance v ON v.stock_lot_id = sl.id
         WHERE sl.product_id = :product_id
           AND v.qty_net > :epsilon
         LIMIT 1"
    );
    $stmt->execute([
        ':product_id' => $productId,
        ':epsilon' => $epsilon,
    ]);

    return $stmt->fetchColumn() !== false;
}

function allocate_components_for_line(
    PDO $pdo,
    int $orderId,
    int $lineId,
    int $lineNo,
    array $components,
    float $lineQty,
    array $locations
): array {
    if ($components === []) {
        return [
            'allocated' => false,
            'reason' => 'no_components',
            'allocations' => [],
        ];
    }

    $savepoint = 'sp_alloc_line_' . $lineId;
    $pdo->exec("SAVEPOINT {$savepoint}");

    $allocationInsert = $pdo->prepare(
        "INSERT INTO public.sales_order_line_lot_allocation (
            sales_order_line_id, product_id, lot_id, qty, allocation_status, stock_move_id, created_at, updated_at
         ) VALUES (
            :sales_order_line_id, :product_id, :lot_id, :qty, 'allocated', :stock_move_id, NOW(), NOW()
         )"
    );

    $allocations = [];

    foreach ($components as $component) {
        $productId = (int) $component['product_id'];
        $required = $lineQty * (float) $component['qty_per_item'];
        $remaining = $required;
        $guard = 0;

        while ($remaining > 0.0000001) {
            $guard++;
            if ($guard > 100) {
                $pdo->exec("ROLLBACK TO SAVEPOINT {$savepoint}");
                $pdo->exec("RELEASE SAVEPOINT {$savepoint}");
                return [
                    'allocated' => false,
                    'reason' => "loop_guard_exceeded:product={$productId}",
                    'allocations' => [],
                ];
            }

            $current = get_current_lot_balance_for_update($pdo, $productId);
            $lotId = (int) $current['lot_id'];
            $available = (float) $current['qty_net'];

            if ($available <= 0.0000001) {
                switch_current_lot($pdo, $productId, $lotId, (int) $locations['storage']);
                continue;
            }

            $take = min($remaining, $available);
            $note = "order-import:order={$orderId}:line={$lineNo}:product={$productId}";
            $stockMoveId = insert_stock_move_out(
                $pdo,
                $productId,
                $lotId,
                $take,
                $locations['storage'],
                $locations['dispatch'],
                $note
            );

            $allocationInsert->execute([
                ':sales_order_line_id' => $lineId,
                ':product_id' => $productId,
                ':lot_id' => $lotId,
                ':qty' => $take,
                ':stock_move_id' => $stockMoveId,
            ]);

            $allocations[] = [
                'productId' => $productId,
                'lotId' => $lotId,
                'qty' => round($take, 4),
                'stockMoveId' => $stockMoveId,
            ];

            $remaining -= $take;
        }
    }

    $pdo->exec("RELEASE SAVEPOINT {$savepoint}");

    return [
        'allocated' => true,
        'reason' => '',
        'allocations' => $allocations,
    ];
}

function allocate_line_inventory(
    PDO $pdo,
    int $orderId,
    int $lineId,
    int $lineNo,
    float $lineQty,
    int $sellableItemId,
    array $locations
): array {
    $components = get_item_components($pdo, $sellableItemId);
    return allocate_components_for_line($pdo, $orderId, $lineId, $lineNo, $components, $lineQty, $locations);
}

function allocate_line_inventory_fallback_product(
    PDO $pdo,
    int $orderId,
    int $lineId,
    int $lineNo,
    float $lineQty,
    int $productId,
    array $locations
): array {
    $components = [[
        'product_id' => $productId,
        'qty_per_item' => 1.0,
    ]];
    return allocate_components_for_line($pdo, $orderId, $lineId, $lineNo, $components, $lineQty, $locations);
}

if (($_SERVER['REQUEST_METHOD'] ?? '') !== 'POST') {
    json_response(405, ['ok' => false, 'error' => 'Method Not Allowed']);
}

$env = parse_env_file(__DIR__ . '/.env');
$env = array_merge($env, parse_env_file(dirname(__DIR__) . '/.env'));
$env = expand_env_values($env);

$expectedSecret = env_value('N8N_WEBHOOK_SECRET', $env);
$providedSecret = (string) ($_SERVER['HTTP_X_WEBHOOK_SECRET'] ?? '');

if ($expectedSecret === '') {
    json_response(500, ['ok' => false, 'error' => 'N8N_WEBHOOK_SECRET not configured']);
}

if ($providedSecret === '' || !hash_equals($expectedSecret, $providedSecret)) {
    json_response(401, ['ok' => false, 'error' => 'Unauthorized']);
}

$rawPayload = file_get_contents('php://input');
if ($rawPayload === false || trim($rawPayload) === '') {
    json_response(400, ['ok' => false, 'error' => 'Empty payload']);
}

try {
    $data = json_decode($rawPayload, true, 512, JSON_THROW_ON_ERROR);
} catch (JsonException) {
    json_response(400, ['ok' => false, 'error' => 'Invalid JSON payload']);
}

if (!is_array($data)) {
    json_response(400, ['ok' => false, 'error' => 'JSON object expected']);
}

// n8n can send either a JSON object or a single-item array with the order object.
if (array_is_list($data)) {
    if (!isset($data[0]) || !is_array($data[0])) {
        json_response(400, ['ok' => false, 'error' => 'Array payload must contain one order object']);
    }
    $data = $data[0];
}

$externalRef = trim((string) ($data['BestellungNr'] ?? ''));
if ($externalRef === '') {
    json_response(422, ['ok' => false, 'error' => 'BestellungNr is required']);
}

$lineItems = $data['lineItems'] ?? [];
if (!is_array($lineItems)) {
    $lineItems = [];
}

try {
    $pdo = connect_database($env);
    ensure_required_tables_exist($pdo);
    $pdo->beginTransaction();
    $locations = get_default_location_ids($pdo);
    $existingOrderId = find_existing_order_id($pdo, $externalRef);

    $partyId = find_or_create_party($pdo, $data);
    upsert_addresses($pdo, $partyId, $data);

    $paymentMethodId = lookup_method_id($pdo, 'payment_method', map_payment_code((string) ($data['Zahlungsmethode'] ?? '')));
    $shippingMethodId = lookup_method_id($pdo, 'shipping_method', map_shipping_code((string) ($data['Liefermethode'] ?? '')));

    $orderStmt = $pdo->prepare(
        'INSERT INTO public.sales_order (
            external_ref, party_id, order_source, order_status, payment_status, payment_method_id, shipping_method_id,
            amount_net, amount_shipping, amount_tax, amount_discount, total_amount, currency, webhook_payload, imported_at, created_at, updated_at
         ) VALUES (
            :external_ref, :party_id, \'wix\', \'imported\', \'paid\', :payment_method_id, :shipping_method_id,
            :amount_net, :amount_shipping, :amount_tax, :amount_discount, :total_amount, \'CHF\', :webhook_payload::jsonb, NOW(), NOW(), NOW()
         )
         ON CONFLICT (external_ref) DO UPDATE SET
            party_id = EXCLUDED.party_id,
            order_source = EXCLUDED.order_source,
            order_status = EXCLUDED.order_status,
            payment_status = EXCLUDED.payment_status,
            payment_method_id = EXCLUDED.payment_method_id,
            shipping_method_id = EXCLUDED.shipping_method_id,
            amount_net = EXCLUDED.amount_net,
            amount_shipping = EXCLUDED.amount_shipping,
            amount_tax = EXCLUDED.amount_tax,
            amount_discount = EXCLUDED.amount_discount,
            total_amount = EXCLUDED.total_amount,
            currency = EXCLUDED.currency,
            webhook_payload = EXCLUDED.webhook_payload,
            imported_at = NOW(),
            updated_at = NOW()
         RETURNING id'
    );

    $orderStmt->execute([
        ':external_ref' => $externalRef,
        ':party_id' => $partyId,
        ':payment_method_id' => $paymentMethodId,
        ':shipping_method_id' => $shippingMethodId,
        ':amount_net' => parse_number($data['Netto'] ?? null),
        ':amount_shipping' => parse_number($data['Versandkosten'] ?? null),
        ':amount_tax' => parse_number($data['Mehrwertsteuer'] ?? null),
        ':amount_discount' => parse_number($data['Rabatt'] ?? null),
        ':total_amount' => parse_number($data['Gesamtsumme'] ?? null),
        ':webhook_payload' => json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
    ]);

    $orderId = $orderStmt->fetchColumn();
    if ($orderId === false) {
        throw new RuntimeException('Could not upsert order');
    }
    $orderId = (int) $orderId;

    $inventoryRollback = [
        'reversedMoves' => 0,
        'reversedQty' => 0.0,
    ];
    if ($existingOrderId !== null) {
        $inventoryRollback = reverse_existing_allocations_for_order($pdo, $existingOrderId, $locations['storage']);
    }

    $deleteLines = $pdo->prepare('DELETE FROM public.sales_order_line WHERE sales_order_id = :sales_order_id');
    $deleteLines->execute([':sales_order_id' => $orderId]);

    $lineInsert = $pdo->prepare(
        'INSERT INTO public.sales_order_line (
            sales_order_id, line_no, sellable_item_id, raw_external_article_number, raw_external_title,
            qty, unit_price, line_total, created_at, updated_at
         ) VALUES (
            :sales_order_id, :line_no, :sellable_item_id, :article_number, :title,
            :qty, :unit_price, :line_total, NOW(), NOW()
         )
         RETURNING id'
    );

    $insertedLines = 0;
    $inventory = [
        'linesMapped' => 0,
        'linesMappedViaFallbackProduct' => 0,
        'linesUnmapped' => 0,
        'allocationCount' => 0,
        'warnings' => [],
    ];

    foreach ($lineItems as $index => $lineItem) {
        if (!is_array($lineItem)) {
            continue;
        }

        $articleNumber = trim((string) ($lineItem['artikelnummer'] ?? ''));
        $title = trim((string) ($lineItem['titel'] ?? ''));
        $qty = parse_number($lineItem['artikelanzahl'] ?? null);
        if ($qty === null || $qty <= 0) {
            continue;
        }

        $unitPrice = parse_number($lineItem['preisEinheit'] ?? null);
        $lineTotal = $unitPrice !== null ? round($qty * $unitPrice, 2) : null;
        $sellableItemId = resolve_sellable_item_id($pdo, $articleNumber, $title);
        $autoMappingMeta = null;
        $lineNo = $index + 1;

        $lineInsert->execute([
            ':sales_order_id' => $orderId,
            ':line_no' => $lineNo,
            ':sellable_item_id' => $sellableItemId,
            ':article_number' => $articleNumber,
            ':title' => $title,
            ':qty' => $qty,
            ':unit_price' => $unitPrice,
            ':line_total' => $lineTotal,
        ]);
        $lineId = $lineInsert->fetchColumn();
        if ($lineId === false) {
            throw new RuntimeException("Could not insert sales_order_line for line {$lineNo}");
        }
        $lineId = (int) $lineId;

        if ($sellableItemId === null) {
            $inventory['linesUnmapped']++;
            $inventory['warnings'][] = "No sellable item mapping for line {$lineNo} (artikelnummer='{$articleNumber}', titel='{$title}')";
        } else {
            if (is_array($autoMappingMeta)) {
                $inventory['linesMappedViaFallbackProduct']++;
                $inventory['warnings'][] = "Line {$lineNo} auto-mapped from title/product fallback (sellable_item_id={$sellableItemId})";
            }
            $inventory['linesMapped']++;
            $allocationResult = allocate_line_inventory(
                $pdo,
                $orderId,
                $lineId,
                $lineNo,
                (float) $qty,
                $sellableItemId,
                $locations
            );
            if ($allocationResult['allocated'] === false) {
                $inventory['warnings'][] = "No inventory allocation for line {$lineNo}: " . $allocationResult['reason'];
            } else {
                $inventory['allocationCount'] += count($allocationResult['allocations']);
            }
        }

        $insertedLines++;
    }

    $pdo->commit();

    $labelTrigger = trigger_shipping_label_flow($data, $env);

    json_response(200, [
        'ok' => true,
        'orderId' => $orderId,
        'externalRef' => $externalRef,
        'lineItemsImported' => $insertedLines,
        'inventory' => $inventory,
        'inventoryRollback' => $inventoryRollback,
        'labelTrigger' => $labelTrigger,
    ]);
} catch (Throwable $e) {
    if (isset($pdo) && $pdo instanceof PDO && $pdo->inTransaction()) {
        $pdo->rollBack();
    }

    json_response(500, [
        'ok' => false,
        'error' => 'Order import failed',
        'detail' => $e->getMessage(),
    ]);
}