<?php

declare(strict_types=1);

namespace CrocoAI\DataBridge\Application;

// RuntimeException values become JSON REST errors, not raw HTML output.
// The plugin owns these custom tables; trusted identifiers cannot be SQL value placeholders.
// Release coordination must read and update current operational state without object-cache staleness.
// phpcs:disable WordPress.Security.EscapeOutput.ExceptionNotEscaped,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

use RuntimeException;
use CrocoAI\DataBridge\Domain\DiffEngine;
use CrocoAI\DataBridge\Domain\ProductValidator;
use CrocoAI\DataBridge\Domain\WooCommerceId;
use CrocoAI\DataBridge\Infrastructure\Database;
use CrocoAI\DataBridge\Infrastructure\MultilingualAdapterFactory;
use CrocoAI\DataBridge\Infrastructure\ProductBrands;
use CrocoAI\DataBridge\Infrastructure\Repository;
use CrocoAI\DataBridge\Infrastructure\WooPublisher;

final class StagedReleaseService
{
    private Repository $repository;

    public function __construct(private PublicationService $publication)
    {
        $this->repository = new Repository();
    }

    public function create(array $payload): array
    {
        global $wpdb;
        $clientKey = sanitize_key((string) ($payload['client_key'] ?? ''));
        $releaseKey = sanitize_key((string) ($payload['release_key'] ?? ''));
        if ($clientKey === '' || $releaseKey === '') {
            throw new RuntimeException('client_key and release_key are required.');
        }
        $protocol = (int) ($payload['selected_release_protocol'] ?? 0);
        if (!in_array($protocol, [2, 3, 4, 5, 6], true)) {
            throw new RuntimeException('selected_release_protocol must be 2, 3, 4, 5, or 6.');
        }
        $manifest = $payload['family_manifest'] ?? null;
        if (!is_array($manifest)) {
            throw new RuntimeException('family_manifest is required.');
        }
        if ((int) ($manifest['selected_release_protocol'] ?? 0) !== $protocol) {
            throw new RuntimeException('The staged release and family manifest protocols do not match.');
        }
        if (!empty($manifest['translated_taxonomies']) && $protocol < 3) {
            throw new RuntimeException('Translated taxonomy overrides require selected release protocol 3.');
        }
        $this->validateManifestDocument($manifest);
        $this->validateManifestLanguages($manifest);
        $manifestJson = wp_json_encode($manifest);
        $manifestHash = hash('sha256', (string) $manifestJson);
        $table = Database::table('remote_releases');
        $existing = $wpdb->get_row($wpdb->prepare(
            'SELECT * FROM %i WHERE release_key=%s',
            $table,
            $releaseKey
        ), ARRAY_A);
        if ($existing) {
            if ((int) ($existing['selected_release_protocol'] ?? 2) !== $protocol) {
                throw new RuntimeException('This release key already exists with a different protocol.');
            }
            if (!hash_equals((string) ($existing['manifest_hash'] ?? ''), $manifestHash)) {
                throw new RuntimeException('The release key already names a different family manifest.');
            }
            return $this->present($existing);
        }
        [$sourceId] = $this->ensureSource($clientKey, (array) ($payload['ownership'] ?? []));
        $runId = $this->repository->createRun($sourceId, true);
        $now = current_time('mysql', true);
        $wpdb->insert($table, [
            'client_key' => $clientKey,
            'release_key' => $releaseKey,
            'source_id' => $sourceId,
            'run_id' => $runId,
            'expected_items' => max(0, (int) ($payload['expected_items'] ?? 0)),
            'selected_release_protocol' => $protocol,
            'family_manifest' => $manifestJson,
            'manifest_hash' => $manifestHash,
            'status' => 'receiving',
            'created_at' => $now,
            'updated_at' => $now,
        ]);
        return $this->get((int) $wpdb->insert_id);
    }

    public function addItems(int $releaseId, array $items): array
    {
        global $wpdb;
        $release = $this->row($releaseId);
        if ($release['status'] !== 'receiving') {
            throw new RuntimeException('This release no longer accepts items.');
        }
        if (!$items || count($items) > 50) {
            throw new RuntimeException('A staged batch must contain between 1 and 50 items.');
        }
        $validator = new ProductValidator();
        $diffEngine = new DiffEngine();
        $profile = $this->repository->profile((int) $this->repository->source((int) $release['source_id'])['profile_id']);
        $accepted = $unchanged = $invalid = 0;
        $batchByExternalId = [];
        foreach ($items as $batchItem) {
            if (!is_array($batchItem)) {
                continue;
            }
            $batchExternalId = sanitize_text_field((string) ($batchItem['external_id'] ?? ''));
            if ($batchExternalId !== '') {
                $batchByExternalId[$batchExternalId] = $batchItem;
            }
        }
        foreach ($items as $canonical) {
            if (!is_array($canonical)) {
                ++$invalid;
                continue;
            }
            $externalId = sanitize_text_field((string) ($canonical['external_id'] ?? ''));
            if ($externalId === '') {
                ++$invalid;
                continue;
            }
            $canonical['_crocoai_dbridge_client_key'] = (string) $release['client_key'];
            if (
                $this->hasTranslatedOverrides($canonical)
                && (int) ($release['selected_release_protocol'] ?? 2) < 3
            ) {
                $canonical['_crocoai_dbridge_protocol_error'] = 'Translated content overrides require selected release protocol 3.';
            }
            if (
                array_key_exists('_attribute_ownership', $canonical)
                && (int) ($release['selected_release_protocol'] ?? 2) < 4
            ) {
                $canonical['_crocoai_dbridge_attribute_protocol_error'] = 'Attribute ownership requires selected release protocol 4.';
            }
            if (
                array_key_exists('brands', $canonical)
                && (int) ($release['selected_release_protocol'] ?? 2) < 6
            ) {
                $canonical['_crocoai_dbridge_brand_protocol_error'] = 'Product brands require selected release protocol 6.';
            }
            $duplicate = $wpdb->get_var($wpdb->prepare(
                'SELECT id FROM %i WHERE run_id=%d AND external_id=%s',
                Database::table('records'),
                (int) $release['run_id'],
                $externalId
            ));
            if ($duplicate) {
                continue;
            }
            $validation = $validator->validate($canonical);
            $this->validateStructuralMigration(
                $canonical,
                (int) $release['selected_release_protocol'],
                (int) $release['source_id'],
                $validation['errors']
            );
            if (\CrocoAI\DataBridge\LegacyKeys::hasCanonical($canonical, 'protocol_error')) {
                $validation['errors'][] = (string) \CrocoAI\DataBridge\LegacyKeys::canonical($canonical, 'protocol_error');
                \CrocoAI\DataBridge\LegacyKeys::forgetCanonical($canonical, 'protocol_error');
            }
            if (\CrocoAI\DataBridge\LegacyKeys::hasCanonical($canonical, 'attribute_protocol_error')) {
                $validation['errors'][] = (string) \CrocoAI\DataBridge\LegacyKeys::canonical($canonical, 'attribute_protocol_error');
                \CrocoAI\DataBridge\LegacyKeys::forgetCanonical($canonical, 'attribute_protocol_error');
            }
            if (isset($canonical['_crocoai_dbridge_brand_protocol_error'])) {
                $validation['errors'][] = (string) $canonical['_crocoai_dbridge_brand_protocol_error'];
                unset($canonical['_crocoai_dbridge_brand_protocol_error']);
            }
            if (!empty($canonical['translations'])) {
                try {
                    $requestedAdapter = MultilingualAdapterFactory::fromCanonical($canonical);
                    if (!$requestedAdapter->available() || !$requestedAdapter->canPublishProducts()) {
                        $validation['errors'][] = $requestedAdapter->name()
                            . ' is absent, inactive, or cannot publish WooCommerce translations.';
                    }
                    foreach ((array) $canonical['translations'] as $translation) {
                        $language = is_array($translation)
                            ? sanitize_key((string) ($translation['language'] ?? ''))
                            : '';
                        if ($language !== '' && !$requestedAdapter->supportsLanguage($language)) {
                            $validation['errors'][] = 'Requested translation language ' . $language . ' is not registered.';
                        }
                    }
                } catch (\Throwable $error) {
                    $validation['errors'][] = $error->getMessage();
                }
            }
            $status = $validation['errors'] ? 'invalid' : 'staged';
            $recordId = $this->repository->addRecord(
                (int) $release['run_id'],
                (int) $release['source_id'],
                $externalId,
                ['release_key' => $release['release_key']],
                $canonical,
                $status,
                $validation['errors']
            );
            if ($validation['errors']) {
                ++$invalid;
                continue;
            }
            $adapter = MultilingualAdapterFactory::fromCanonical($canonical);
            $link = $this->repository->link((int) $release['source_id'], $externalId);
            if ($link && !wc_get_product((int) $link['product_id'])) {
                // The merchant deleted the product this link named. A link that
                // outlives its product would be reported back as the item's store
                // id, so a create would arrive looking like an update of a post
                // that no longer exists.
                $this->repository->deleteLink((int) $release['source_id'], $externalId);
                $link = null;
            }
            $productId = $link ? (int) $link['product_id'] : 0;
            if ($productId > 0) {
                $linkedProduct = wc_get_product($productId);
                $expectedType = (string) ($canonical['type'] ?? 'simple');
                $structural = is_array($canonical['_structural_migration'] ?? null)
                    && ($canonical['_structural_migration']['confirmed'] ?? false) === true;
                if ($linkedProduct && $linkedProduct->get_type() !== $expectedType && !$structural) {
                    $link = null;
                    $productId = 0;
                }
            }
            if ($productId <= 0 && !empty($canonical['_destination_product_id'])) {
                $candidateId = (int) $canonical['_destination_product_id'];
                $candidate = $candidateId > 0 ? wc_get_product($candidateId) : null;
                $expectedType = (string) ($canonical['type'] ?? 'simple');
                $structural = is_array($canonical['_structural_migration'] ?? null)
                    && ($canonical['_structural_migration']['confirmed'] ?? false) === true;
                if ($candidate && ($candidate->get_type() === $expectedType || $structural)) {
                    $productId = $candidateId;
                }
            }
            if ($productId <= 0 && !empty($canonical['sku'])) {
                // Local staging sources are independent of legacy feed sources. WooCommerce
                // enforces unique non-empty SKUs, so this is a guarded adoption fallback.
                $productId = $adapter->productIdBySku((string) $canonical['sku']);
                $skuProduct = $productId > 0 ? wc_get_product($productId) : null;
                if (!$skuProduct || $skuProduct->get_type() !== (string) ($canonical['type'] ?? 'simple')) {
                    $productId = 0;
                }
            }
            if ($productId > 0 && (string) ($canonical['type'] ?? '') === 'variation') {
                $candidate = wc_get_product($productId);
                $parentExternalId = (string) ($canonical['parent_external_id'] ?? '');
                $parentLink = $parentExternalId !== ''
                    ? $this->repository->link((int) $release['source_id'], $parentExternalId)
                    : null;
                $expectedParentId = (int) ($parentLink['product_id'] ?? 0);
                if ($expectedParentId <= 0 && isset($batchByExternalId[$parentExternalId])) {
                    $expectedParentId = (int) (
                        $batchByExternalId[$parentExternalId]['_destination_product_id'] ?? 0
                    );
                }
                if (
                    !($candidate instanceof \WC_Product_Variation)
                    || $expectedParentId <= 0
                    || (int) $candidate->get_parent_id() !== $expectedParentId
                ) {
                    $productId = 0;
                }
            }
            if ($productId > 0) {
                $productId = MultilingualAdapterFactory::publicationProductId(
                    $adapter,
                    $productId,
                    $canonical
                );
            }
            $product = $productId > 0 ? wc_get_product($productId) : null;
            $itemOwnership = is_array($canonical['_ownership'] ?? null)
                ? $canonical['_ownership']
                : $profile['ownership'];
            $diff = $diffEngine->compare($canonical, $product ?: null, $itemOwnership);
            $familyMeta = is_array($canonical['_family'] ?? null) ? $canonical['_family'] : [];
            if (($familyMeta['lifecycle'] ?? '') === 'retiring') {
                if (!$product || ($canonical['type'] ?? '') !== 'variation') {
                    throw new RuntimeException('Only an existing variation can enter retirement.');
                }
                $diff['action'] = 'update';
                $diff['fields']['lifecycle'] = [
                    'before' => 'active',
                    'after' => 'retiring',
                ];
            }
            if (($familyMeta['lifecycle_transition'] ?? '') === 'reactivating') {
                if (!$product || ($canonical['type'] ?? '') !== 'variation') {
                    throw new RuntimeException('Only an existing variation can be reactivated.');
                }
                $diff['action'] = 'update';
                $diff['fields']['lifecycle'] = [
                    'before' => 'retiring',
                    'after' => 'active',
                ];
            }
            if (
                $product
                && array_key_exists('images', $canonical)
                && ($itemOwnership['images'] ?? 'source') !== 'merchant'
            ) {
                $managed = $link
                    ? (json_decode((string) ($link['managed_snapshot'] ?? ''), true) ?: [])
                    : [];
                $beforeImages = $link ? array_values((array) ($managed['images'] ?? [])) : null;
                $afterImages = array_values((array) $canonical['images']);
                $attachmentsMatch = $this->repository->productHasMediaUrls(
                    (int) $product->get_id(),
                    $afterImages
                );
                if ($beforeImages === null || $beforeImages !== $afterImages || !$attachmentsMatch) {
                    $diff['action'] = 'update';
                    $diff['fields']['images'] = [
                        'before' => $beforeImages,
                        'after' => $afterImages,
                        'reason' => $attachmentsMatch ? 'source_urls_changed' : 'attachments_missing_or_different',
                    ];
                }
            }
            if ($diff['action'] === 'unchanged') {
                if (!$product) {
                    throw new RuntimeException('An unchanged item has no WooCommerce product.');
                }
                $this->repository->touchLink(
                    (int) $release['source_id'],
                    $externalId,
                    (int) $product->get_id(),
                    hash('sha256', wp_json_encode($canonical)),
                    (int) $release['run_id'],
                    $canonical
                );
                $variantExternalId = (string) ($canonical['variant_external_id'] ?? '');
                if (($canonical['type'] ?? '') === 'simple' && $variantExternalId !== '') {
                    $this->repository->touchLink(
                        (int) $release['source_id'],
                        $variantExternalId,
                        (int) $product->get_id(),
                        hash('sha256', wp_json_encode($canonical)),
                        (int) $release['run_id'],
                        $canonical
                    );
                }
                ++$unchanged;
                continue;
            }
            $this->repository->addChange(
                (int) $release['run_id'],
                $recordId,
                $product ? (int) $product->get_id() : null,
                $diff['action'],
                ['fields' => $diff['fields'], 'warnings' => $validation['warnings']],
                'approved',
                ($canonical['type'] ?? '') === 'variation' ? 2 : 0
            );
            ++$accepted;
        }
        $received = (int) $wpdb->get_var($wpdb->prepare(
            'SELECT COUNT(*) FROM %i WHERE run_id=%d',
            Database::table('records'),
            (int) $release['run_id']
        ));
        $wpdb->update(Database::table('remote_releases'), [
            'received_items' => $received,
            'updated_at' => current_time('mysql', true),
        ], ['id' => $releaseId]);
        return ['accepted' => $accepted, 'unchanged' => $unchanged, 'invalid' => $invalid, 'received_items' => $received];
    }

    /** @param list<string> $errors */
    private function validateStructuralMigration(
        array &$canonical,
        int $protocol,
        int $sourceId,
        array &$errors
    ): void {
        $migration = $canonical['_structural_migration'] ?? null;
        if (!is_array($migration) || (string) ($migration['direction'] ?? '') !== 'variation_to_simple') {
            return;
        }
        if ($protocol < 5) {
            $errors[] = 'variation_to_simple requires selected release protocol 5.';
            return;
        }
        if (($migration['confirmed'] ?? null) !== true) {
            return;
        }
        $destinationId = $variationId = $parentId = null;
        $idFields = [
            ['values' => $canonical, 'field' => '_destination_product_id', 'target' => 'destination'],
            ['values' => $migration, 'field' => 'source_variation_id', 'target' => 'variation'],
            ['values' => $migration, 'field' => 'source_parent_id', 'target' => 'parent'],
        ];
        foreach ($idFields as $idField) {
            try {
                $normalized = WooCommerceId::required($idField['values'], $idField['field']);
                if ($idField['target'] === 'destination') {
                    $destinationId = $normalized;
                } elseif ($idField['target'] === 'variation') {
                    $variationId = $normalized;
                } else {
                    $parentId = $normalized;
                }
            } catch (RuntimeException $error) {
                $errors[] = $error->getMessage();
            }
        }
        if ($destinationId === null || $variationId === null || $parentId === null) {
            return;
        }
        $canonical['_destination_product_id'] = $destinationId;
        $canonical['_structural_migration']['source_variation_id'] = $variationId;
        $canonical['_structural_migration']['source_parent_id'] = $parentId;
        $sourceExternalId = trim((string) ($migration['source_variation_external_id'] ?? ''));
        if ($sourceExternalId === '') {
            $errors[] = 'source_variation_external_id is required for variation_to_simple.';
            return;
        }
        if ($destinationId !== $variationId) {
            $errors[] = '_destination_product_id must equal source_variation_id for variation_to_simple.';
            return;
        }
        $product = wc_get_product($variationId);
        if (!$product) {
            $errors[] = 'The source variation ID does not exist.';
            return;
        }
        $destination = wc_get_product($destinationId);
        if (!$destination || (int) $destination->get_id() !== (int) $product->get_id()) {
            $errors[] = '_destination_product_id does not identify the source variation.';
            return;
        }
        $parent = wc_get_product($parentId);
        if (!$parent instanceof \WC_Product_Variable) {
            $errors[] = 'source_parent_id does not identify an existing WooCommerce variable product.';
            return;
        }
        $sourceLink = $sourceExternalId !== ''
            ? $this->repository->link($sourceId, $sourceExternalId)
            : null;
        $alreadyComplete = $product instanceof \WC_Product_Simple
            && get_post_type($variationId) === 'product'
            && (int) wp_get_post_parent_id($variationId) === 0
            && $sourceLink
            && (int) $sourceLink['product_id'] === $variationId;
        if (!$alreadyComplete && !$product instanceof \WC_Product_Variation) {
            $errors[] = 'The source variation ID is not a WooCommerce variation.';
            return;
        }
        if (!$alreadyComplete && (int) $product->get_parent_id('edit') !== $parentId) {
            $errors[] = 'The source variation parent does not match source_parent_id.';
        }
        $sku = trim((string) ($canonical['sku'] ?? $product->get_sku('edit')));
        if ($sku !== '') {
            $skuOwner = (int) wc_get_product_id_by_sku($sku);
            if ($skuOwner > 0 && $skuOwner !== $variationId) {
                $errors[] = 'The destination SKU belongs to another WooCommerce product.';
            }
        }
        $identityExternalIds = array_unique(array_filter([
            trim((string) ($canonical['external_id'] ?? '')),
            $sourceExternalId,
        ]));
        foreach ($identityExternalIds as $externalId) {
            $link = $this->repository->link($sourceId, $externalId);
            if ($link && (int) $link['product_id'] !== $variationId) {
                $errors[] = 'Croco identity ' . $externalId . ' belongs to another WooCommerce product.';
            }
        }
        $language = sanitize_key((string) ($canonical['language'] ?? ''));
        $clientKey = sanitize_key((string) \CrocoAI\DataBridge\LegacyKeys::canonical($canonical, 'client_key'));
        $currentIdentity = \CrocoAI\DataBridge\LegacyKeys::postMeta($variationId, 'translation_identity');
        if ($currentIdentity !== '' && $clientKey !== '' && $language !== '' && $sourceExternalId !== '') {
            $expectedIdentity = hash('sha256', $clientKey . "\n" . $sourceExternalId . "\n" . $language);
            if (!hash_equals($currentIdentity, $expectedIdentity)) {
                $errors[] = 'The source variation has a conflicting Croco identity.';
            }
        }
    }

    /** @param array<string,mixed> $mediaGate */
    public function finalize(int $releaseId, bool $publish, array $mediaGate = []): array
    {
        global $wpdb;
        $normalizedMediaGate = $publish ? $this->normalizeMediaGate($mediaGate) : [];
        $release = $this->row($releaseId);
        if ($release['status'] === 'ready' && $publish) {
            $stats = $this->statistics($release);
            $hasChanges = ($stats['create'] + $stats['update']) > 0;
            $nextStatus = $hasChanges ? 'queued' : 'completed';
            $changed = $wpdb->update(
                Database::table('remote_releases'),
                ['status' => $nextStatus, 'updated_at' => current_time('mysql', true)],
                ['id' => $releaseId, 'status' => 'ready']
            );
            $result = ['release' => $this->get($releaseId), 'run_id' => (int) $release['run_id'], 'stats' => $stats];
            if ($changed && $hasChanges) {
                $result['job'] = $this->publication->queueRun(
                    (int) $release['run_id'],
                    $normalizedMediaGate
                );
            }
            return $this->withExistingJob($this->row($releaseId), $result);
        }
        if ($release['status'] !== 'receiving') {
            $result = [
                'release' => $this->get($releaseId),
                'run_id' => (int) $release['run_id'],
                'stats' => $this->statistics($release),
            ];
            if ($publish && in_array($release['status'], ['queued', 'completed'], true)) {
                $result = $this->withExistingJob($release, $result);
            }
            return $result;
        }
        if ((int) $release['expected_items'] > 0 && (int) $release['received_items'] !== (int) $release['expected_items']) {
            throw new RuntimeException('The received item count does not match expected_items.');
        }
        $invalid = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM %i WHERE run_id=%d AND status='invalid'",
            Database::table('records'),
            (int) $release['run_id']
        ));
        if ($invalid > 0) {
            throw new RuntimeException(sprintf('Release contains %d invalid staged records.', $invalid));
        }
        $this->validateFamilyScope($release);
        $this->validateTranslationFamilies($release);
        $stats = $this->statistics($release);
        $this->repository->updateRun((int) $release['run_id'], [
            'status' => 'completed', 'stage' => 'review', 'stats' => $stats,
            'finished_at' => current_time('mysql', true),
        ]);
        $hasChanges = ($stats['create'] + $stats['update']) > 0;
        $wpdb->update(Database::table('remote_releases'), [
            'status' => $publish ? ($hasChanges ? 'queued' : 'completed') : 'ready',
            'updated_at' => current_time('mysql', true),
        ], ['id' => $releaseId]);
        $result = [
            'release' => $this->get($releaseId),
            'run_id' => (int) $release['run_id'],
            'stats' => $stats,
        ];
        if ($publish && $hasChanges) {
            $result['job'] = $this->publication->queueRun(
                (int) $release['run_id'],
                $normalizedMediaGate
            );
        }
        return $result;
    }

    /**
     * @param array<string,mixed> $request
     * @return array<string,mixed>
     */
    private function normalizeMediaGate(array $request): array
    {
        if (!$request) {
            return [];
        }
        $unknown = array_diff(array_keys($request), ['hold', 'timeout_seconds']);
        if ($unknown || ($request['hold'] ?? null) !== true) {
            throw new RuntimeException('The media_gate request is invalid.');
        }
        $timeout = $request['timeout_seconds'] ?? 1800;
        if (!is_int($timeout) || $timeout < 60 || $timeout > 3600) {
            throw new RuntimeException('media_gate.timeout_seconds must be an integer from 60 to 3600.');
        }
        return [
            'enabled' => true,
            'ready' => false,
            'deadline' => time() + $timeout,
            'released_at' => null,
            'timed_out' => false,
        ];
    }

    public function get(int $id): array
    {
        return $this->present($this->row($id));
    }

    public function results(int $releaseId): array
    {
        global $wpdb;
        $release = $this->row($releaseId);
        $records = $wpdb->get_results($wpdb->prepare(
            'SELECT r.id,r.external_id,r.canonical,r.status,r.error,'
            . 'c.action,c.status change_status,c.product_id change_product_id,c.error change_error,'
            . "(SELECT GROUP_CONCAT(m.error SEPARATOR '; ') FROM %i m "
            . "WHERE m.change_id=c.id AND m.status='failed') media_error,"
            . 'l.product_id linked_product_id '
            . 'FROM %i r '
            . 'LEFT JOIN %i c ON c.record_id=r.id '
            . 'LEFT JOIN %i l ON l.source_id=r.source_id AND l.external_id=r.external_id '
            . 'WHERE r.run_id=%d ORDER BY r.id ASC',
            Database::table('media_queue'),
            Database::table('records'),
            Database::table('changes'),
            Database::table('links'),
            (int) $release['run_id']
        ), ARRAY_A) ?: [];
        $items = [];
        $translationRequirements = [];
        foreach ($records as $record) {
            $canonical = json_decode((string) $record['canonical'], true) ?: [];
            foreach ((array) ($canonical['translations'] ?? []) as $translation) {
                if (is_array($translation) && ($translation['mode'] ?? 'duplicate') === 'translate') {
                    $translationRequirements[(string) $record['external_id']][] = sanitize_key(
                        (string) ($translation['language'] ?? '')
                    );
                }
            }
            $action = (string) ($record['action'] ?: ($record['status'] === 'invalid' ? 'invalid' : 'unchanged'));
            $status = (string) ($record['change_status'] ?: ($record['status'] === 'invalid' ? 'invalid' : 'unchanged'));
            $error = (string) ($record['media_error'] ?: $record['change_error'] ?: $record['error'] ?: '');
            $productId = ($record['change_product_id'] ?: $record['linked_product_id'])
                ? (int) ($record['change_product_id'] ?: $record['linked_product_id'])
                : null;
            $remoteMeta = [];
            $product = $productId ? wc_get_product($productId) : null;
            if (!$product) {
                // Report only a store id that still resolves to a product. A
                // deleted product is no identity, and naming one contradicts the
                // create action the item is carrying.
                $productId = null;
            }
            if ($product) {
                foreach ((array) ($canonical['meta_data'] ?? []) as $key => $_value) {
                    $key = sanitize_key((string) $key);
                    if ($key !== '') {
                        $remoteMeta[$key] = $product->get_meta($key, true, 'edit');
                    }
                }
            }
            $translations = $product
                ? (new WooPublisher())->translationResultMap((int) $product->get_id(), $canonical)
                : [];
            $items[] = [
                'external_id' => (string) $record['external_id'],
                'type' => (string) ($canonical['type'] ?? ''),
                'parent_external_id' => (string) ($canonical['parent_external_id'] ?? ''),
                'family_external_id' => (string) (($canonical['_family']['product_external_id'] ?? '') ?: ($canonical['parent_external_id'] ?? $canonical['external_id'] ?? '')),
                'action' => $action,
                'status' => $status,
                'woocommerce_product_id' => $productId,
                'translations' => $translations,
                'brands' => $product && !$product instanceof \WC_Product_Variation
                    ? ProductBrands::namesForProduct((int) $product->get_id())
                    : [],
                'meta_data' => $remoteMeta,
                'error' => $error === '' ? null : $error,
            ];
        }
        foreach ($items as $parentIndex => $parent) {
            if ($parent['type'] !== 'variable' || !empty($parent['error'])) {
                continue;
            }
            $required = array_values(array_filter(array_unique(
                $translationRequirements[$parent['external_id']] ?? []
            )));
            foreach ($required as $language) {
                $complete = !empty($parent['translations'][$language]);
                foreach ($items as $variation) {
                    if (
                        $variation['type'] !== 'variation'
                        || $variation['parent_external_id'] !== $parent['external_id']
                    ) {
                        continue;
                    }
                    $complete = $complete
                        && in_array($variation['status'], ['published', 'unchanged'], true)
                        && !empty($variation['translations'][$language]);
                }
                if (!$complete) {
                    $items[$parentIndex]['status'] = 'failed';
                    $items[$parentIndex]['error'] = sprintf(
                        'Translated variable family is incomplete for language %s.',
                        $language
                    );
                    break;
                }
            }
        }
        return [
            'release' => $this->present($release),
            'stats' => $this->statistics($release, $items),
            'items' => $items,
        ];
    }

    private function withExistingJob(array $release, array $result): array
    {
        global $wpdb;
        $jobId = (int) $wpdb->get_var($wpdb->prepare(
            'SELECT id FROM %i WHERE run_id=%d ORDER BY id DESC LIMIT 1',
            Database::table('jobs'),
            (int) $release['run_id']
        ));
        if ($jobId > 0) {
            $job = $this->repository->job($jobId);
            $result['job'] = $job;
            if (
                $release['status'] === 'queued'
                && in_array($job['status'], ['completed', 'failed'], true)
            ) {
                $this->repository->completeRemoteReleaseForRun(
                    (int) $release['run_id'],
                    (string) $job['status']
                );
                $result['release'] = $this->get((int) $release['id']);
            }
        }
        return $result;
    }

    private function statistics(array $release, ?array $items = null): array
    {
        if ($items === null) {
            $items = $this->results((int) $release['id'])['items'];
        }
        $stats = [
            'items' => count($items),
            'valid' => 0,
            'invalid' => 0,
            'create' => 0,
            'update' => 0,
            'unchanged' => 0,
            'dependency' => 0,
            'parent' => 0,
            'variation' => 0,
            'families' => [],
        ];
        $changedParents = [];
        foreach ($items as $item) {
            $action = (string) $item['action'];
            if ($action === 'invalid') {
                ++$stats['invalid'];
            } else {
                ++$stats['valid'];
                if (isset($stats[$action])) {
                    ++$stats[$action];
                }
            }
            if ($item['type'] === 'variable') {
                ++$stats['parent'];
            } elseif ($item['type'] === 'variation') {
                ++$stats['variation'];
                if (in_array($action, ['create', 'update'], true)) {
                    $changedParents[(string) $item['parent_external_id']] = true;
                }
            }
            $familyKey = (string) ($item['family_external_id'] ?? '');
            if ($familyKey !== '') {
                if (!isset($stats['families'][$familyKey])) {
                    $stats['families'][$familyKey] = [
                        'items' => 0, 'invalid' => 0, 'changed' => 0, 'failed' => 0,
                    ];
                }
                ++$stats['families'][$familyKey]['items'];
                if ($action === 'invalid') {
                    ++$stats['families'][$familyKey]['invalid'];
                }
                if (in_array($action, ['create', 'update'], true)) {
                    ++$stats['families'][$familyKey]['changed'];
                }
                if (($item['status'] ?? '') === 'failed') {
                    ++$stats['families'][$familyKey]['failed'];
                }
            }
        }
        foreach ($items as $item) {
            if ($item['type'] === 'variable' && $item['action'] === 'unchanged' && isset($changedParents[$item['external_id']])) {
                ++$stats['dependency'];
            }
        }
        return $stats;
    }

    private function validateManifestDocument(array $manifest): void
    {
        if (!in_array((int) ($manifest['selected_release_protocol'] ?? 0), [2, 3, 4, 5, 6], true)) {
            throw new RuntimeException('The family manifest must use selected-release protocol 2, 3, 4, 5, or 6.');
        }
        $families = $manifest['families'] ?? null;
        if (!is_array($families) || $families === []) {
            throw new RuntimeException('The family manifest must contain at least one complete family.');
        }
        $products = [];
        $variants = [];
        foreach ($families as $family) {
            if (!is_array($family)) {
                throw new RuntimeException('Each family manifest entry must be an object.');
            }
            $productId = sanitize_text_field((string) ($family['product_external_id'] ?? ''));
            $mode = (string) ($family['mode'] ?? '');
            if ($productId === '' || isset($products[$productId])) {
                throw new RuntimeException('Family product identities must be present and unique.');
            }
            if (!in_array($mode, ['simple', 'variable'], true)) {
                throw new RuntimeException('A family mode must be simple or variable.');
            }
            $products[$productId] = true;
            $familyVariants = $family['variants'] ?? null;
            if (!is_array($familyVariants) || $familyVariants === []) {
                throw new RuntimeException('A product family must contain at least one variant.');
            }
            foreach ($familyVariants as $variant) {
                if (!is_array($variant)) {
                    throw new RuntimeException('Each family variant must be an object.');
                }
                $variantId = sanitize_text_field((string) ($variant['external_id'] ?? ''));
                if ($variantId === '' || isset($variants[$variantId])) {
                    throw new RuntimeException('Family variant identities must be present and unique.');
                }
                $variants[$variantId] = true;
            }
        }
    }

    private function validateFamilyScope(array $release): void
    {
        global $wpdb;
        $manifest = json_decode((string) ($release['family_manifest'] ?? ''), true);
        if (!is_array($manifest)) {
            throw new RuntimeException('The staged release has no readable family manifest.');
        }
        $this->validateManifestDocument($manifest);
        $recordRows = $wpdb->get_results($wpdb->prepare(
            'SELECT external_id,canonical FROM %i WHERE run_id=%d',
            Database::table('records'),
            (int) $release['run_id']
        ), ARRAY_A) ?: [];
        $records = [];
        foreach ($recordRows as $row) {
            $records[(string) $row['external_id']] = json_decode((string) $row['canonical'], true) ?: [];
        }
        $allowed = [];
        foreach ((array) $manifest['families'] as $family) {
            $productId = (string) $family['product_external_id'];
            $mode = (string) $family['mode'];
            $axes = array_values(array_map('strval', (array) ($family['axis_keys'] ?? [])));
            if (count($axes) !== count(array_unique($axes)) || in_array('', $axes, true)) {
                throw new RuntimeException('Family axes must be non-empty and unique.');
            }
            $parent = $records[$productId] ?? null;
            if (!is_array($parent) || (string) ($parent['type'] ?? '') !== $mode) {
                throw new RuntimeException('The complete family parent is absent from staged records.');
            }
            $allowed[$productId] = true;
            $active = 0;
            $combinations = [];
            $union = array_fill_keys($axes, []);
            $familyVariants = (array) $family['variants'];
            if ($mode === 'simple' && count($familyVariants) !== 1) {
                throw new RuntimeException('A simple product must contain one optionless variant.');
            }
            foreach ($familyVariants as $variant) {
                $variantId = (string) $variant['external_id'];
                $lifecycle = (string) ($variant['lifecycle'] ?? 'active');
                $options = (array) ($variant['option_values'] ?? []);
                if (!in_array($lifecycle, ['active', 'retiring'], true)) {
                    throw new RuntimeException('A staged family has an invalid lifecycle transition.');
                }
                if ($lifecycle === 'active') {
                    ++$active;
                }
                if ($mode === 'simple') {
                    if ($options !== [] || (string) ($parent['variant_external_id'] ?? '') !== $variantId) {
                        throw new RuntimeException('A simple family manifest does not match its optionless variant.');
                    }
                    continue;
                }
                $child = $records[$variantId] ?? null;
                if (!is_array($child) || (string) ($child['type'] ?? '') !== 'variation') {
                    throw new RuntimeException('The complete family has an orphan or missing variation.');
                }
                if ((string) ($child['parent_external_id'] ?? '') !== $productId) {
                    throw new RuntimeException('A staged variation names a conflicting parent.');
                }
                $allowed[$variantId] = true;
                if ($lifecycle === 'active') {
                    $optionKeys = array_keys($options);
                    sort($optionKeys);
                    $sortedAxes = $axes;
                    sort($sortedAxes);
                    if ($optionKeys !== $sortedAxes) {
                        throw new RuntimeException('A variation does not contain exactly one value for every axis.');
                    }
                    $combination = [];
                    foreach ($axes as $axis) {
                        $value = trim((string) ($options[$axis] ?? ''));
                        if ($value === '' || $value === '*') {
                            throw new RuntimeException('Missing and wildcard option values are not allowed.');
                        }
                        $combination[] = $value;
                        if (!in_array($value, $union[$axis], true)) {
                            $union[$axis][] = $value;
                        }
                    }
                    $combinationKey = wp_json_encode($combination);
                    if (isset($combinations[$combinationKey])) {
                        throw new RuntimeException('A family contains a duplicate option combination.');
                    }
                    $combinations[$combinationKey] = true;
                }
                $childFamily = (array) ($child['_family'] ?? []);
                if ((string) ($childFamily['product_external_id'] ?? '') !== $productId) {
                    throw new RuntimeException('A staged variation is outside its declared family.');
                }
            }
            if ($active < 1) {
                throw new RuntimeException('A product family cannot have zero active variants.');
            }
            if ($mode === 'variable') {
                $parentAxes = array_values(array_map('strval', (array) ($parent['variation_attributes'] ?? [])));
                sort($parentAxes);
                $sortedAxes = $axes;
                sort($sortedAxes);
                if ($parentAxes !== $sortedAxes) {
                    throw new RuntimeException('The parent axes do not match the family manifest.');
                }
                $parentValues = [];
                foreach ((array) ($parent['attributes'] ?? []) as $attribute) {
                    if (is_array($attribute) && isset($union[(string) ($attribute['key'] ?? '')])) {
                        $values = array_values(array_unique(array_map('strval', (array) ($attribute['values'] ?? []))));
                        sort($values);
                        $parentValues[(string) $attribute['key']] = $values;
                    }
                }
                foreach ($union as $axis => $values) {
                    sort($values);
                    if (($parentValues[$axis] ?? []) !== $values) {
                        throw new RuntimeException('Parent attribute values must equal the union of variation values.');
                    }
                }
            }
        }
        $outside = array_diff(array_keys($records), array_keys($allowed));
        if ($outside !== []) {
            throw new RuntimeException('The staged release contains members outside the family manifest.');
        }
    }

    private function ensureSource(string $clientKey, array $ownership): array
    {
        $name = 'Staging: ' . $clientKey;
        foreach ($this->repository->sources() as $source) {
            if ($source['name'] === $name) {
                $profileId = (int) $source['profile_id'];
                $current = $this->repository->profile($profileId);
                if ($current['ownership'] !== $ownership) {
                    $this->repository->saveProfile(['name' => $current['name'], 'mapping' => [], 'ownership' => $ownership], $profileId);
                }
                return [(int) $source['id'], $profileId];
            }
        }
        $profileId = $this->repository->saveProfile(['name' => $name, 'mapping' => [], 'ownership' => $ownership]);
        $sourceId = $this->repository->saveSource(['name' => $name, 'type' => 'csv', 'profile_id' => $profileId, 'enabled' => false]);
        return [$sourceId, $profileId];
    }

    private function row(int $id): array
    {
        global $wpdb;
        $row = $wpdb->get_row($wpdb->prepare(
            'SELECT * FROM %i WHERE id=%d',
            Database::table('remote_releases'),
            $id
        ), ARRAY_A);
        if (!$row) {
            throw new RuntimeException('Staged release not found.');
        }
        return $row;
    }

    private function present(array $row): array
    {
        global $wpdb;
        foreach (['id', 'source_id', 'run_id', 'expected_items', 'received_items', 'selected_release_protocol'] as $key) {
            $row[$key] = (int) $row[$key];
        }
        $row['family_manifest'] = json_decode((string) ($row['family_manifest'] ?? ''), true) ?: [];
        $jobId = (int) $wpdb->get_var($wpdb->prepare(
            'SELECT id FROM %i WHERE run_id=%d ORDER BY id DESC LIMIT 1',
            Database::table('jobs'),
            (int) $row['run_id']
        ));
        $row['job_id'] = $jobId > 0 ? $jobId : null;
        return $row;
    }

    private function hasTranslatedOverrides(array $canonical): bool
    {
        foreach ((array) ($canonical['translations'] ?? []) as $translation) {
            if (is_array($translation) && ($translation['mode'] ?? 'duplicate') === 'translate') {
                return true;
            }
        }
        return false;
    }

    private function validateManifestLanguages(array $manifest): void
    {
        $translated = (array) ($manifest['translated_taxonomies'] ?? []);
        if ($translated === []) {
            return;
        }
        $adapter = MultilingualAdapterFactory::fromCanonical($manifest);
        if (!$adapter->available() || !$adapter->canPublishProducts()) {
            throw new RuntimeException(
                ($manifest['multilingual_plugin'] ?? 'Requested multilingual plugin')
                . ' is absent, inactive, or cannot publish WooCommerce translations.'
            );
        }
        foreach (array_keys($translated) as $language) {
            $language = sanitize_key((string) $language);
            if ($language === '' || !$adapter->supportsLanguage($language)) {
                throw new RuntimeException('Requested translation language ' . $language . ' is not registered.');
            }
        }
    }

    private function validateTranslationFamilies(array $release): void
    {
        global $wpdb;
        if ((int) ($release['selected_release_protocol'] ?? 2) < 3) {
            return;
        }
        $records = $wpdb->get_results($wpdb->prepare(
            'SELECT external_id,canonical FROM %i WHERE run_id=%d',
            Database::table('records'),
            (int) $release['run_id']
        ), ARRAY_A) ?: [];
        $parents = [];
        $variations = [];
        foreach ($records as $record) {
            $canonical = json_decode((string) $record['canonical'], true) ?: [];
            if (($canonical['type'] ?? '') === 'variable') {
                $parents[(string) $record['external_id']] = $canonical;
            } elseif (($canonical['type'] ?? '') === 'variation') {
                $variations[(string) ($canonical['parent_external_id'] ?? '')][] = $canonical;
            }
        }
        foreach ($parents as $externalId => $parent) {
            $languages = [];
            foreach ((array) ($parent['translations'] ?? []) as $translation) {
                if (is_array($translation) && ($translation['mode'] ?? 'duplicate') === 'translate') {
                    $languages[] = sanitize_key((string) ($translation['language'] ?? ''));
                }
            }
            foreach (array_filter(array_unique($languages)) as $language) {
                $manifest = json_decode((string) ($release['family_manifest'] ?? ''), true) ?: [];
                foreach ((array) ($manifest['families'] ?? []) as $family) {
                    if (
                        !is_array($family)
                        || (string) ($family['product_external_id'] ?? '') !== $externalId
                    ) {
                        continue;
                    }
                    $recordedIds = array_map(
                        static fn (array $item): string => (string) ($item['external_id'] ?? ''),
                        (array) ($variations[$externalId] ?? [])
                    );
                    foreach ((array) ($family['variants'] ?? []) as $variant) {
                        $variantId = is_array($variant) ? (string) ($variant['external_id'] ?? '') : '';
                        if ($variantId !== '' && !in_array($variantId, $recordedIds, true)) {
                            throw new RuntimeException(sprintf(
                                'Translated variable family %s is incomplete: required variation %s is absent.',
                                $externalId,
                                $variantId
                            ));
                        }
                    }
                }
                foreach ((array) ($variations[$externalId] ?? []) as $variation) {
                    $found = false;
                    foreach ((array) ($variation['translations'] ?? []) as $translation) {
                        if (
                            is_array($translation)
                            && ($translation['mode'] ?? 'duplicate') === 'translate'
                            && sanitize_key((string) ($translation['language'] ?? '')) === $language
                        ) {
                            $found = true;
                            break;
                        }
                    }
                    if (!$found) {
                        throw new RuntimeException(sprintf(
                            'Translated variable family %s is incomplete: variation %s has no %s override.',
                            $externalId,
                            (string) ($variation['external_id'] ?? ''),
                            $language
                        ));
                    }
                }
            }
        }
    }
}