<?php
declare(strict_types=1);

namespace Help4BuilderSuite\Modules\Commerce;

use WP_Error;

if (! defined('ABSPATH')) {
    exit;
}

// Commerce customer-field reads and transactional writes use dedicated indexed tables.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

final class CustomerFieldRepository
{
    private const TYPES = [
        'text',
        'textarea',
        'email',
        'phone',
        'url',
        'number',
        'date',
        'time',
        'datetime',
        'color',
        'range',
        'select',
        'radio',
        'checkbox',
        'toggle',
        'multiselect',
        'country',
        'calculated',
    ];

    private const PLACEMENTS = ['checkout', 'account', 'admin'];
    private const SENSITIVITIES = ['standard', 'personal', 'sensitive'];
    private const CONDITION_OPERATORS = [
        'equals',
        'not_equals',
        'contains',
        'not_contains',
        'empty',
        'not_empty',
        'greater_than',
        'less_than',
        'one_of',
        'not_one_of',
    ];

    /**
     * @return array<string, string>
     */
    public static function typeLabels(): array
    {
        return [
            'text' => __('Text', 'help4-builder-suite'),
            'textarea' => __('Long text', 'help4-builder-suite'),
            'email' => __('Email', 'help4-builder-suite'),
            'phone' => __('Phone', 'help4-builder-suite'),
            'url' => __('URL', 'help4-builder-suite'),
            'number' => __('Number', 'help4-builder-suite'),
            'date' => __('Date', 'help4-builder-suite'),
            'time' => __('Time', 'help4-builder-suite'),
            'datetime' => __('Date and time', 'help4-builder-suite'),
            'color' => __('Color', 'help4-builder-suite'),
            'range' => __('Range slider', 'help4-builder-suite'),
            'select' => __('Select', 'help4-builder-suite'),
            'radio' => __('Radio choices', 'help4-builder-suite'),
            'checkbox' => __('Checkbox', 'help4-builder-suite'),
            'toggle' => __('On / off toggle', 'help4-builder-suite'),
            'multiselect' => __('Multiple choices', 'help4-builder-suite'),
            'country' => __('Country', 'help4-builder-suite'),
            'calculated' => __('Calculated value', 'help4-builder-suite'),
        ];
    }

    /**
     * @return array<string, string>
     */
    public static function conditionOperatorLabels(): array
    {
        return [
            'equals' => __('Equals', 'help4-builder-suite'),
            'not_equals' => __('Does not equal', 'help4-builder-suite'),
            'contains' => __('Contains', 'help4-builder-suite'),
            'not_contains' => __('Does not contain', 'help4-builder-suite'),
            'empty' => __('Is empty', 'help4-builder-suite'),
            'not_empty' => __('Is not empty', 'help4-builder-suite'),
            'greater_than' => __('Is greater than', 'help4-builder-suite'),
            'less_than' => __('Is less than', 'help4-builder-suite'),
            'one_of' => __('Matches one of', 'help4-builder-suite'),
            'not_one_of' => __('Matches none of', 'help4-builder-suite'),
        ];
    }

    /**
     * @param array<string, mixed> $input
     * @return int|WP_Error
     */
    public function saveDefinition(array $input)
    {
        global $wpdb;

        $id = absint($input['id'] ?? 0);
        $fieldKey = sanitize_key((string) ($input['field_key'] ?? ''));
        $label = substr(sanitize_text_field((string) ($input['label'] ?? '')), 0, 190);
        if ($fieldKey === '' || $label === '') {
            return new WP_Error('h4bs_customer_field_identity', __('A field key and label are required.', 'help4-builder-suite'));
        }
        if ($id > 0) {
            $existingDefinition = $this->getDefinition($id);
            if (is_array($existingDefinition) && (string) ($existingDefinition['field_key'] ?? '') !== $fieldKey) {
                return new WP_Error(
                    'h4bs_customer_field_key_locked',
                    __('A saved customer field key cannot be changed because values, conditions, and formulas depend on it. Duplicate the field to create a new key.', 'help4-builder-suite')
                );
            }
        }

        $fieldType = sanitize_key((string) ($input['field_type'] ?? 'text'));
        if (! in_array($fieldType, self::TYPES, true)) {
            $fieldType = 'text';
        }
        $calculationFormula = substr(sanitize_text_field((string) ($input['calculation_formula'] ?? '')), 0, 500);
        if ($fieldType === 'calculated') {
            $formulaValid = CustomerFieldFormula::validate($calculationFormula);
            if ($formulaValid instanceof WP_Error) {
                return $formulaValid;
            }
            if (in_array($fieldKey, CustomerFieldFormula::references($calculationFormula), true)) {
                return new WP_Error(
                    'h4bs_customer_field_formula_cycle',
                    __('A calculated field cannot reference itself.', 'help4-builder-suite')
                );
            }
            $formulaDependencies = $this->validateFormulaDependencies(
                $fieldKey,
                $calculationFormula,
                $id,
                is_array($input['_known_field_keys'] ?? null) ? $input['_known_field_keys'] : [],
                (string) ($input['sensitivity'] ?? 'personal')
            );
            if ($formulaDependencies instanceof WP_Error) {
                return $formulaDependencies;
            }
        }
        $placements = array_values(
            array_intersect(
                self::PLACEMENTS,
                array_map('sanitize_key', is_array($input['placements'] ?? null) ? $input['placements'] : [])
            )
        );
        if (! $placements) {
            $placements = ['admin'];
        }
        $sensitivity = sanitize_key((string) ($input['sensitivity'] ?? 'personal'));
        if (! in_array($sensitivity, self::SENSITIVITIES, true)) {
            $sensitivity = 'personal';
        }
        $status = sanitize_key((string) ($input['status'] ?? 'active'));
        if (! in_array($status, ['active', 'inactive', 'archived'], true)) {
            $status = 'active';
        }
        $referencedFieldChange = $this->validateReferencedFieldChange($fieldKey, $id, $sensitivity, $status);
        if ($referencedFieldChange instanceof WP_Error) {
            return $referencedFieldChange;
        }

        $options = $this->sanitizeOptions($input['options'] ?? []);
        $conditions = $this->sanitizeConditions($input);
        $conditionMatch = sanitize_key((string) ($input['condition_match'] ?? 'all'));
        if (! in_array($conditionMatch, ['all', 'any'], true)) {
            $conditionMatch = 'all';
        }
        $inputWidth = sanitize_key((string) ($input['input_width'] ?? 'auto'));
        if (! in_array($inputWidth, ['auto', 'full', 'half', 'third', 'two_thirds'], true)) {
            $inputWidth = 'auto';
        }
        $settings = [
            'placeholder' => substr(sanitize_text_field((string) ($input['placeholder'] ?? '')), 0, 190),
            'instructions' => substr(sanitize_textarea_field((string) ($input['instructions'] ?? '')), 0, 1000),
            'default_value' => $sensitivity === 'sensitive' || $fieldType === 'calculated' ? '' : $this->sanitizeLooseValue($input['default_value'] ?? ''),
            'autocomplete' => substr(sanitize_text_field((string) ($input['autocomplete'] ?? '')), 0, 80),
            'input_width' => $inputWidth,
            'options' => $options,
            'validation' => [
                'min_length' => max(0, min(10000, absint($input['min_length'] ?? 0))),
                'max_length' => max(0, min(10000, absint($input['max_length'] ?? 0))),
                'min' => is_numeric($input['min'] ?? null) ? (float) $input['min'] : null,
                'max' => is_numeric($input['max'] ?? null) ? (float) $input['max'] : null,
                'step' => is_numeric($input['step'] ?? null) && (float) $input['step'] > 0 ? (float) $input['step'] : null,
                'min_selections' => max(0, min(50, absint($input['min_selections'] ?? 0))),
                'max_selections' => max(0, min(50, absint($input['max_selections'] ?? 0))),
                'date_min' => $this->sanitizeIsoBoundary($input['date_min'] ?? ''),
                'date_max' => $this->sanitizeIsoBoundary($input['date_max'] ?? ''),
            ],
            // Keep the first rule for extensions that consumed the 1.3.251 schema.
            'condition' => $conditions[0] ?? ['field' => '', 'operator' => 'equals', 'value' => ''],
            'conditions' => $conditions,
            'condition_match' => $conditionMatch,
            'calculation' => [
                'formula' => $fieldType === 'calculated' ? $calculationFormula : '',
                'precision' => max(0, min(6, absint($input['calculation_precision'] ?? 2))),
                'prefix' => substr(sanitize_text_field((string) ($input['calculation_prefix'] ?? '')), 0, 32),
                'suffix' => substr(sanitize_text_field((string) ($input['calculation_suffix'] ?? '')), 0, 32),
            ],
            'visibility' => [
                'product_ids' => $this->sanitizeIdList($input['product_ids'] ?? []),
                'product_types' => $this->sanitizeKeyList($input['product_types'] ?? []),
                'customer_roles' => $this->sanitizeKeyList($input['customer_roles'] ?? []),
                'countries' => $this->sanitizeCountryList($input['countries'] ?? []),
                'min_subtotal' => is_numeric($input['min_subtotal'] ?? null) ? max(0.0, (float) $input['min_subtotal']) : null,
                'max_subtotal' => is_numeric($input['max_subtotal'] ?? null) ? max(0.0, (float) $input['max_subtotal']) : null,
            ],
        ];
        $data = [
            'field_key' => $fieldKey,
            'label' => $label,
            'field_type' => $fieldType,
            'field_group' => substr(sanitize_text_field((string) ($input['field_group'] ?? __('Customer details', 'help4-builder-suite'))), 0, 120),
            'placements' => wp_json_encode($placements),
            'status' => $status,
            'sort_order' => max(-10000, min(10000, (int) ($input['sort_order'] ?? 10))),
            'required' => $fieldType === 'calculated' ? 0 : (! empty($input['required']) ? 1 : 0),
            'customer_editable' => $fieldType === 'calculated' ? 0 : (! empty($input['customer_editable']) ? 1 : 0),
            'show_in_orders' => ! empty($input['show_in_orders']) ? 1 : 0,
            'exportable' => ! empty($input['exportable']) ? 1 : 0,
            'sensitivity' => $sensitivity,
            'settings' => wp_json_encode($settings),
        ];
        $table = $this->definitionsTable();

        if ($id > 0) {
            $updated = $wpdb->update(
                $table,
                $data,
                ['id' => $id],
                ['%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%d', '%d', '%d', '%s', '%s'],
                ['%d']
            );
            if ($updated === false) {
                return new WP_Error('h4bs_customer_field_save', __('The customer field could not be updated.', 'help4-builder-suite'));
            }
            do_action('h4bs_commerce_customer_field_definition_saved', $id, $data, true);
            return $id;
        }

        $inserted = $wpdb->insert(
            $table,
            $data,
            ['%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%d', '%d', '%d', '%s', '%s']
        );
        if ($inserted === false) {
            return new WP_Error('h4bs_customer_field_save', __('The customer field could not be created. Its key may already exist.', 'help4-builder-suite'));
        }
        $createdId = (int) $wpdb->insert_id;
        do_action('h4bs_commerce_customer_field_definition_saved', $createdId, $data, false);
        return $createdId;
    }

    public function archiveDefinition(int $id): bool
    {
        global $wpdb;
        $definition = $this->getDefinition($id);
        if (! is_array($definition)) {
            return false;
        }
        $dependencyChange = $this->validateReferencedFieldChange(
            (string) ($definition['field_key'] ?? ''),
            $id,
            (string) ($definition['sensitivity'] ?? 'personal'),
            'archived'
        );
        if ($dependencyChange instanceof WP_Error) {
            return false;
        }
        $archived = $id > 0 && $wpdb->update(
            $this->definitionsTable(),
            ['status' => 'archived'],
            ['id' => $id],
            ['%s'],
            ['%d']
        ) !== false;
        if ($archived) {
            do_action('h4bs_commerce_customer_field_archived', $id);
        }
        return $archived;
    }

    /**
     * @return array<string, string>
     */
    public static function presetLabels(): array
    {
        return [
            'business_profile' => __('Business profile', 'help4-builder-suite'),
            'service_intake' => __('Service intake', 'help4-builder-suite'),
            'delivery_preferences' => __('Delivery preferences', 'help4-builder-suite'),
            'membership_onboarding' => __('Membership onboarding', 'help4-builder-suite'),
        ];
    }

    /**
     * @return array{created:int,skipped:int,errors:array<int,string>}|WP_Error
     */
    public function installPreset(string $preset)
    {
        $preset = sanitize_key($preset);
        $catalog = $this->presetCatalog();
        if (! isset($catalog[$preset])) {
            return new WP_Error('h4bs_customer_field_preset', __('Choose a valid customer field pack.', 'help4-builder-suite'));
        }
        return $this->importDefinitions(
            [
                'schema' => 'h4bs/customer-fields',
                'version' => 1,
                'fields' => $catalog[$preset],
            ],
            'skip'
        );
    }

    /**
     * @return int|WP_Error
     */
    public function duplicateDefinition(int $id)
    {
        $definition = $this->getDefinition($id);
        if (! is_array($definition)) {
            return new WP_Error('h4bs_customer_field_duplicate', __('The customer field to duplicate was not found.', 'help4-builder-suite'));
        }
        $input = $this->definitionToInput($definition);
        $baseKey = substr(sanitize_key((string) ($definition['field_key'] ?? 'field')) . '_copy', 0, 180);
        $candidate = $baseKey;
        for ($suffix = 2; $this->definitionIdByKey($candidate) > 0 && $suffix <= 100; $suffix++) {
            $candidate = substr($baseKey, 0, 175) . '_' . $suffix;
        }
        if ($this->definitionIdByKey($candidate) > 0) {
            return new WP_Error('h4bs_customer_field_duplicate_key', __('A unique key could not be generated for the duplicate.', 'help4-builder-suite'));
        }
        $input['field_key'] = $candidate;
        $input['label'] = sprintf(
            /* translators: %s: original customer field label. */
            __('%s (Copy)', 'help4-builder-suite'),
            (string) ($definition['label'] ?? '')
        );
        $input['status'] = 'inactive';
        return $this->saveDefinition($input);
    }

    /**
     * @return array{schema:string,version:int,exported_at:string,fields:array<int,array<string,mixed>>}
     */
    public function exportDefinitions(): array
    {
        return [
            'schema' => 'h4bs/customer-fields',
            'version' => 1,
            'exported_at' => gmdate('c'),
            'fields' => array_map(
                fn(array $definition): array => $this->definitionToInput($definition),
                $this->listDefinitions('', [], true)
            ),
        ];
    }

    /**
     * @return array{created:int,updated:int,skipped:int,errors:array<int,string>}|WP_Error
     */
    public function importDefinitions(mixed $payload, string $conflict = 'skip')
    {
        if (is_string($payload)) {
            $payload = json_decode($payload, true);
        }
        if (! is_array($payload) || (string) ($payload['schema'] ?? '') !== 'h4bs/customer-fields' || ! is_array($payload['fields'] ?? null)) {
            return new WP_Error('h4bs_customer_field_import_schema', __('Use a Help4 customer-fields JSON export.', 'help4-builder-suite'));
        }
        $conflict = in_array($conflict, ['skip', 'update'], true) ? $conflict : 'skip';
        $formulaCatalogValid = $this->validateImportedFormulaCatalog(array_slice($payload['fields'], 0, 100), $conflict);
        if ($formulaCatalogValid instanceof WP_Error) {
            return $formulaCatalogValid;
        }
        $result = ['created' => 0, 'updated' => 0, 'skipped' => 0, 'errors' => []];
        $knownFieldKeys = array_values(
            array_unique(
                array_filter(
                    array_map(
                        static fn(mixed $row): string => is_array($row) ? sanitize_key((string) ($row['field_key'] ?? '')) : '',
                        array_slice($payload['fields'], 0, 100)
                    )
                )
            )
        );
        foreach (array_slice($payload['fields'], 0, 100) as $row) {
            if (! is_array($row)) {
                $result['errors'][] = __('A field row was not an object.', 'help4-builder-suite');
                continue;
            }
            $fieldKey = sanitize_key((string) ($row['field_key'] ?? ''));
            $existingId = $fieldKey !== '' ? $this->definitionIdByKey($fieldKey) : 0;
            if ($existingId > 0 && $conflict === 'skip') {
                $result['skipped']++;
                continue;
            }
            $row['id'] = $existingId;
            $row['_known_field_keys'] = $knownFieldKeys;
            $saved = $this->saveDefinition($row);
            if ($saved instanceof WP_Error) {
                $result['errors'][] = $saved->get_error_message();
                continue;
            }
            if ($existingId > 0) {
                $result['updated']++;
            } else {
                $result['created']++;
            }
        }
        do_action('h4bs_commerce_customer_field_definitions_imported', $result, $conflict);
        return $result;
    }

    /**
     * @return array<string, mixed>|null
     */
    public function getDefinition(int $id): ?array
    {
        global $wpdb;
        $row = $wpdb->get_row(
            $wpdb->prepare('SELECT * FROM %i WHERE id = %d LIMIT 1', $this->definitionsTable(), $id),
            ARRAY_A
        );
        return is_array($row) ? $this->normalizeDefinition($row) : null;
    }

    /**
     * @param array<string, mixed> $context
     * @return array<int, array<string, mixed>>
     */
    public function listDefinitions(string $placement = '', array $context = [], bool $includeInactive = false): array
    {
        global $wpdb;
        $rows = $includeInactive
            ? $wpdb->get_results($wpdb->prepare('SELECT * FROM %i ORDER BY sort_order ASC, id ASC', $this->definitionsTable()), ARRAY_A)
            : $wpdb->get_results($wpdb->prepare("SELECT * FROM %i WHERE status = 'active' ORDER BY sort_order ASC, id ASC", $this->definitionsTable()), ARRAY_A);
        if (! is_array($rows)) {
            return [];
        }

        $definitions = [];
        foreach ($rows as $row) {
            if (! is_array($row)) {
                continue;
            }
            $definition = $this->normalizeDefinition($row);
            if ($placement !== '' && ! in_array($placement, $definition['placements'], true)) {
                continue;
            }
            if (! $this->isVisibleInContext($definition, $context)) {
                continue;
            }
            $definitions[] = $definition;
        }

        $filtered = apply_filters(
            'h4bs_commerce_customer_field_definitions',
            $definitions,
            $placement,
            $context,
            $includeInactive
        );
        return is_array($filtered) ? array_values(array_filter($filtered, 'is_array')) : $definitions;
    }

    /**
     * @param mixed $rawValues
     * @param array<int, array<string, mixed>> $definitions
     * @return array<string, mixed>|WP_Error
     */
    public function validateValues(mixed $rawValues, array $definitions, bool $partial = false)
    {
        $rawValues = is_array($rawValues) ? $rawValues : [];
        $normalizedInput = [];
        foreach (array_slice($rawValues, 0, 100, true) as $key => $value) {
            $cleanKey = sanitize_key((string) $key);
            if ($cleanKey !== '') {
                $normalizedInput[$cleanKey] = $value;
            }
        }
        foreach ($definitions as $definition) {
            if ((string) ($definition['field_type'] ?? '') === 'calculated') {
                unset($normalizedInput[sanitize_key((string) ($definition['field_key'] ?? ''))]);
            }
        }

        $validated = [];
        foreach ($definitions as $definition) {
            $key = sanitize_key((string) ($definition['field_key'] ?? ''));
            if ($key === '' || ! $this->conditionMatches($definition, $normalizedInput)) {
                continue;
            }
            if ((string) ($definition['field_type'] ?? '') === 'calculated') {
                continue;
            }
            $hasValue = array_key_exists($key, $normalizedInput);
            if (! $hasValue && $partial) {
                continue;
            }
            $rawValue = $hasValue ? $normalizedInput[$key] : ($definition['settings']['default_value'] ?? '');
            $empty = in_array((string) ($definition['field_type'] ?? ''), ['checkbox', 'toggle'], true)
                ? empty($rawValue)
                : $this->isEmptyValue($rawValue);
            if (! empty($definition['required']) && $empty) {
                return new WP_Error(
                    'h4bs_customer_field_required',
                    sprintf(
                        /* translators: %s: customer field label. */
                        __('Complete the required field: %s.', 'help4-builder-suite'),
                        (string) ($definition['label'] ?? $key)
                    ),
                    ['field' => $key]
                );
            }
            if ($empty) {
                $validated[$key] = in_array((string) ($definition['field_type'] ?? ''), ['multiselect'], true) ? [] : '';
                continue;
            }
            $value = $this->sanitizeTypedValue($rawValue, $definition);
            if ($value instanceof WP_Error) {
                return $value;
            }
            $validated[$key] = $value;
        }

        $calculated = $this->applyCalculatedValues($validated, $normalizedInput, $definitions);
        if ($calculated instanceof WP_Error) {
            return $calculated;
        }

        $filtered = apply_filters('h4bs_commerce_customer_field_validated_values', $calculated, $definitions, $normalizedInput);
        return is_array($filtered) ? $filtered : $calculated;
    }

    /**
     * @param array<string, mixed> $values
     * @param array<int, array<string, mixed>> $definitions
     * @return true|WP_Error
     */
    public function saveValues(int $customerId, array $values, array $definitions, array $clearKeys = [])
    {
        if ($customerId <= 0) {
            return new WP_Error('h4bs_customer_field_customer', __('A valid customer is required.', 'help4-builder-suite'));
        }

        global $wpdb;
        $calculated = $this->applyCalculatedValues($values, $values, $definitions);
        if ($calculated instanceof WP_Error) {
            return $calculated;
        }
        $values = $calculated;
        $definitionMap = [];
        foreach ($definitions as $definition) {
            $definitionMap[(string) ($definition['field_key'] ?? '')] = $definition;
        }
        $clearKeys = array_values(
            array_unique(
                array_filter(
                    array_map('sanitize_key', array_slice($clearKeys, 0, 100)),
                    static fn(string $key): bool => isset($definitionMap[$key])
                )
            )
        );
        $writes = [];
        foreach ($values as $key => $value) {
            $key = sanitize_key((string) $key);
            $definition = $definitionMap[$key] ?? null;
            if (! is_array($definition)) {
                continue;
            }
            $sensitivity = (string) ($definition['sensitivity'] ?? 'personal');
            $encoded = $this->encodeValue($value, $sensitivity === 'sensitive');
            if ($encoded instanceof WP_Error) {
                return $encoded;
            }
            $searchValue = $sensitivity === 'sensitive' ? '' : $this->searchValue($value);
            $writes[] = [
                'customer_id' => $customerId,
                'field_key' => $key,
                'field_value' => $encoded,
                'search_value' => $searchValue,
                'sensitivity' => $sensitivity,
            ];
        }

        $table = $this->valuesTable();
        $transactionStarted = $this->usesSqlite()
            ? $wpdb->query('BEGIN IMMEDIATE TRANSACTION') !== false
            : $wpdb->query('START TRANSACTION') !== false;
        if (! $transactionStarted) {
            return new WP_Error('h4bs_customer_field_transaction', __('Customer fields could not start a safe write transaction.', 'help4-builder-suite'));
        }
        foreach ($clearKeys as $clearKey) {
            $deleted = $wpdb->delete(
                $table,
                ['customer_id' => $customerId, 'field_key' => $clearKey],
                ['%d', '%s']
            );
            if ($deleted === false) {
                $wpdb->query('ROLLBACK');
                return new WP_Error('h4bs_customer_field_value_clear', __('A customer field value could not be removed.', 'help4-builder-suite'));
            }
        }
        foreach ($writes as $data) {
            $existingQuery = $this->usesSqlite()
                ? $wpdb->prepare(
                    'SELECT id FROM %i WHERE customer_id = %d AND field_key = %s LIMIT 1',
                    $table,
                    $customerId,
                    (string) $data['field_key']
                )
                : $wpdb->prepare(
                    'SELECT id FROM %i WHERE customer_id = %d AND field_key = %s LIMIT 1 FOR UPDATE',
                    $table,
                    $customerId,
                    (string) $data['field_key']
                );
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Both database-engine branches prepare the complete query above.
            $existingId = (int) $wpdb->get_var($existingQuery);
            $written = $existingId > 0
                ? $wpdb->update($table, $data, ['id' => $existingId], ['%d', '%s', '%s', '%s', '%s'], ['%d'])
                : $wpdb->insert($table, $data, ['%d', '%s', '%s', '%s', '%s']);
            if ($written === false) {
                $wpdb->query('ROLLBACK');
                return new WP_Error('h4bs_customer_field_value_save', __('A customer field value could not be saved.', 'help4-builder-suite'));
            }
        }
        if ($wpdb->query('COMMIT') === false) {
            $wpdb->query('ROLLBACK');
            return new WP_Error('h4bs_customer_field_commit', __('Customer fields could not complete a safe write transaction.', 'help4-builder-suite'));
        }

        do_action(
            'h4bs_commerce_customer_fields_saved',
            $customerId,
            array_values(array_unique(array_merge(array_map('strval', array_keys($values)), $clearKeys))),
            $definitions,
            $clearKeys
        );

        return true;
    }

    /**
     * @return array<string, mixed>
     */
    public function getValues(int $customerId, bool $includeSensitive = false): array
    {
        global $wpdb;
        $rows = $wpdb->get_results(
            $wpdb->prepare('SELECT field_key, field_value, sensitivity FROM %i WHERE customer_id = %d ORDER BY id ASC', $this->valuesTable(), $customerId),
            ARRAY_A
        );
        if (! is_array($rows)) {
            return [];
        }

        $values = [];
        foreach ($rows as $row) {
            $key = sanitize_key((string) ($row['field_key'] ?? ''));
            $sensitivity = sanitize_key((string) ($row['sensitivity'] ?? 'personal'));
            if ($key === '' || ($sensitivity === 'sensitive' && ! $includeSensitive)) {
                continue;
            }
            $values[$key] = $this->decodeValue((string) ($row['field_value'] ?? ''), $sensitivity === 'sensitive');
        }
        return $values;
    }

    /**
     * Returns stored keys without decrypting or exposing their values.
     *
     * @return array<int, string>
     */
    public function getStoredKeys(int $customerId): array
    {
        global $wpdb;
        if ($customerId <= 0) {
            return [];
        }
        $keys = $wpdb->get_col(
            $wpdb->prepare('SELECT field_key FROM %i WHERE customer_id = %d ORDER BY id ASC', $this->valuesTable(), $customerId)
        );
        return is_array($keys)
            ? array_values(array_unique(array_filter(array_map('sanitize_key', $keys))))
            : [];
    }

    /**
     * @param array<string, mixed> $values
     * @param array<int, array<string, mixed>> $definitions
     * @return array<int, array<string, mixed>>
     */
    public function orderSnapshot(array $values, array $definitions): array
    {
        $snapshot = [];
        foreach ($definitions as $definition) {
            $key = (string) ($definition['field_key'] ?? '');
            if ($key === '' || empty($definition['show_in_orders']) || ! array_key_exists($key, $values)) {
                continue;
            }
            $sensitive = (string) ($definition['sensitivity'] ?? '') === 'sensitive';
            if ($sensitive) {
                continue;
            }
            $snapshot[] = [
                'key' => $key,
                'label' => (string) ($definition['label'] ?? $key),
                'type' => (string) ($definition['field_type'] ?? 'text'),
                'sensitivity' => (string) ($definition['sensitivity'] ?? 'personal'),
                'value' => $values[$key],
                'redacted' => false,
            ];
        }
        return $snapshot;
    }

    public function deleteValues(int $customerId): int
    {
        global $wpdb;
        if ($customerId <= 0) {
            return 0;
        }
        $deleted = $wpdb->delete($this->valuesTable(), ['customer_id' => $customerId], ['%d']);
        return $deleted === false ? 0 : (int) $deleted;
    }

    public function findCustomerIdByEmail(string $email): int
    {
        global $wpdb;
        $email = sanitize_email($email);
        if ($email === '') {
            return 0;
        }
        return (int) $wpdb->get_var(
            $wpdb->prepare('SELECT id FROM %i WHERE email = %s LIMIT 1', $wpdb->prefix . 'h4_customers', $email)
        );
    }

    /**
     * @param array<string, mixed> $row
     * @return array<string, mixed>
     */
    private function normalizeDefinition(array $row): array
    {
        $placements = json_decode((string) ($row['placements'] ?? ''), true);
        $settings = json_decode((string) ($row['settings'] ?? ''), true);
        $sensitivity = sanitize_key((string) ($row['sensitivity'] ?? 'personal'));
        if ($sensitivity === 'sensitive' && is_array($settings)) {
            $settings['default_value'] = '';
        }
        return [
            'id' => absint($row['id'] ?? 0),
            'field_key' => sanitize_key((string) ($row['field_key'] ?? '')),
            'label' => sanitize_text_field((string) ($row['label'] ?? '')),
            'field_type' => sanitize_key((string) ($row['field_type'] ?? 'text')),
            'field_group' => sanitize_text_field((string) ($row['field_group'] ?? __('Customer details', 'help4-builder-suite'))),
            'placements' => is_array($placements) ? array_values(array_intersect(self::PLACEMENTS, array_map('sanitize_key', $placements))) : ['admin'],
            'status' => sanitize_key((string) ($row['status'] ?? 'active')),
            'sort_order' => (int) ($row['sort_order'] ?? 10),
            'required' => ! empty($row['required']),
            'customer_editable' => ! empty($row['customer_editable']),
            'show_in_orders' => ! empty($row['show_in_orders']),
            'exportable' => ! empty($row['exportable']),
            'sensitivity' => $sensitivity,
            'settings' => is_array($settings) ? $settings : [],
        ];
    }

    /**
     * @param array<string, mixed> $definition
     * @param array<string, mixed> $context
     */
    private function isVisibleInContext(array $definition, array $context): bool
    {
        $visibility = is_array($definition['settings']['visibility'] ?? null) ? $definition['settings']['visibility'] : [];
        $requiredProductIds = $this->sanitizeIdList($visibility['product_ids'] ?? []);
        $contextProductIds = $this->sanitizeIdList($context['product_ids'] ?? ($context['product_id'] ?? []));
        if ($requiredProductIds && $this->contextHasAny($context, ['product_ids', 'product_id']) && ! array_intersect($requiredProductIds, $contextProductIds)) {
            return false;
        }
        $requiredTypes = $this->sanitizeKeyList($visibility['product_types'] ?? []);
        $contextTypes = $this->sanitizeKeyList($context['product_types'] ?? ($context['product_type'] ?? []));
        if ($requiredTypes && $this->contextHasAny($context, ['product_types', 'product_type']) && ! array_intersect($requiredTypes, $contextTypes)) {
            return false;
        }
        $requiredRoles = $this->sanitizeKeyList($visibility['customer_roles'] ?? []);
        $contextRoles = $this->sanitizeKeyList($context['customer_roles'] ?? ($context['user_roles'] ?? ($context['customer_role'] ?? [])));
        if ($requiredRoles && $this->contextHasAny($context, ['customer_roles', 'user_roles', 'customer_role']) && ! array_intersect($requiredRoles, $contextRoles)) {
            return false;
        }
        $requiredCountries = $this->sanitizeCountryList($visibility['countries'] ?? []);
        $contextCountries = $this->sanitizeCountryList($context['countries'] ?? ($context['country'] ?? []));
        if ($requiredCountries && $this->contextHasAny($context, ['countries', 'country']) && ! array_intersect($requiredCountries, $contextCountries)) {
            return false;
        }
        $subtotal = $context['order_subtotal'] ?? ($context['cart_subtotal'] ?? ($context['subtotal'] ?? null));
        if (is_numeric($subtotal)) {
            $subtotal = (float) $subtotal;
            if (($visibility['min_subtotal'] ?? null) !== null && $subtotal < (float) $visibility['min_subtotal']) {
                return false;
            }
            if (($visibility['max_subtotal'] ?? null) !== null && $subtotal > (float) $visibility['max_subtotal']) {
                return false;
            }
        }
        return true;
    }

    /**
     * @param array<string, mixed> $definition
     * @param array<string, mixed> $values
     */
    /**
     * @param array<string, mixed> $values
     * @param array<string, mixed> $rawValues
     * @param array<int, array<string, mixed>> $definitions
     * @return array<string, mixed>|WP_Error
     */
    private function applyCalculatedValues(array $values, array $rawValues, array $definitions)
    {
        $definitionMap = [];
        $pending = [];
        foreach ($definitions as $definition) {
            $key = sanitize_key((string) ($definition['field_key'] ?? ''));
            if ($key === '') {
                continue;
            }
            $definitionMap[$key] = $definition;
            if ((string) ($definition['field_type'] ?? '') === 'calculated') {
                unset($values[$key], $rawValues[$key]);
                $pending[$key] = $definition;
            }
        }
        if (! $pending) {
            return $values;
        }

        $available = array_merge($rawValues, $values);
        while ($pending) {
            $progress = false;
            foreach ($pending as $key => $definition) {
                if (! $this->conditionMatches($definition, $available)) {
                    unset($pending[$key]);
                    $progress = true;
                    continue;
                }
                $calculation = is_array($definition['settings']['calculation'] ?? null)
                    ? $definition['settings']['calculation']
                    : [];
                $formula = (string) ($calculation['formula'] ?? '');
                $references = CustomerFieldFormula::references($formula);
                $contextReferenceUnavailable = false;
                foreach ($references as $reference) {
                    if (! isset($definitionMap[$reference])) {
                        $contextReferenceUnavailable = true;
                        break;
                    }
                    if (
                        (string) ($definitionMap[$reference]['sensitivity'] ?? 'personal') === 'sensitive'
                        && (string) ($definition['sensitivity'] ?? 'personal') !== 'sensitive'
                    ) {
                        return new WP_Error(
                            'h4bs_customer_field_formula_sensitive',
                            __('A calculated field derived from sensitive data must also be marked sensitive.', 'help4-builder-suite'),
                            ['field' => $key, 'reference' => $reference]
                        );
                    }
                }
                if ($contextReferenceUnavailable) {
                    unset($pending[$key]);
                    $progress = true;
                    continue;
                }

                $waiting = array_filter(
                    $references,
                    static fn(string $reference): bool => isset($pending[$reference]) && ! array_key_exists($reference, $available)
                );
                if ($waiting) {
                    continue;
                }
                $missing = array_values(array_filter($references, static fn(string $reference): bool => ! array_key_exists($reference, $available)));
                if ($missing) {
                    unset($pending[$key]);
                    $progress = true;
                    continue;
                }

                $result = CustomerFieldFormula::evaluate($formula, $available);
                if ($result instanceof WP_Error) {
                    $result->add_data(['field' => $key]);
                    return $result;
                }
                $precision = max(0, min(6, absint($calculation['precision'] ?? 2)));
                $result = round($result, $precision);
                if (abs($result) < 1 / (10 ** ($precision + 1))) {
                    $result = 0.0;
                }
                $values[$key] = $result;
                $available[$key] = $result;
                unset($pending[$key]);
                $progress = true;
            }
            if (! $progress) {
                return new WP_Error(
                    'h4bs_customer_field_formula_cycle',
                    __('Calculated customer fields contain a dependency cycle.', 'help4-builder-suite'),
                    ['fields' => array_keys($pending)]
                );
            }
        }
        return $values;
    }

    private function conditionMatches(array $definition, array $values): bool
    {
        $settings = is_array($definition['settings'] ?? null) ? $definition['settings'] : [];
        $conditions = is_array($settings['conditions'] ?? null) ? $settings['conditions'] : [];
        if (! $conditions && is_array($settings['condition'] ?? null)) {
            $conditions = [$settings['condition']];
        }
        $conditions = array_values(
            array_filter(
                array_slice($conditions, 0, 10),
                static fn(mixed $condition): bool => is_array($condition) && sanitize_key((string) ($condition['field'] ?? '')) !== ''
            )
        );
        if (! $conditions) {
            return true;
        }
        $matches = array_map(fn(array $condition): bool => $this->conditionRuleMatches($condition, $values), $conditions);
        return sanitize_key((string) ($settings['condition_match'] ?? 'all')) === 'any'
            ? in_array(true, $matches, true)
            : ! in_array(false, $matches, true);
    }

    /**
     * @param array<string, mixed> $condition
     * @param array<string, mixed> $values
     */
    private function conditionRuleMatches(array $condition, array $values): bool
    {
        $field = sanitize_key((string) ($condition['field'] ?? ''));
        $operator = sanitize_key((string) ($condition['operator'] ?? 'equals'));
        $expected = $this->resolveConditionExpectedValue($condition['value'] ?? '', $values);
        $actual = $values[$field] ?? '';
        $actualText = is_array($actual) ? implode('|', array_map('strval', $actual)) : (string) $actual;
        $expectedText = is_array($expected) ? implode('|', array_map('strval', $expected)) : (string) $expected;
        $expectedOptions = array_values(array_filter(array_map('trim', preg_split('/[|,]+/', $expectedText) ?: []), 'strlen'));
        return match ($operator) {
            'not_equals' => $actualText !== $expectedText,
            'contains' => str_contains($actualText, $expectedText),
            'not_contains' => ! str_contains($actualText, $expectedText),
            'empty' => $this->isEmptyValue($actual),
            'not_empty' => ! $this->isEmptyValue($actual),
            'greater_than' => is_numeric($actualText) && is_numeric($expectedText) && (float) $actualText > (float) $expectedText,
            'less_than' => is_numeric($actualText) && is_numeric($expectedText) && (float) $actualText < (float) $expectedText,
            'one_of' => in_array($actualText, $expectedOptions, true),
            'not_one_of' => ! in_array($actualText, $expectedOptions, true),
            default => $actualText === $expectedText,
        };
    }

    /**
     * Resolve an exact {field_key} token without evaluating arbitrary expressions.
     *
     * @param array<string, mixed> $values
     */
    private function resolveConditionExpectedValue(mixed $expected, array $values): mixed
    {
        if (! is_string($expected)) {
            return $expected;
        }

        $token = trim($expected);
        if (! preg_match('/^\{([a-z0-9_]{1,64})\}$/', $token, $matches)) {
            return $expected;
        }

        $fieldKey = sanitize_key((string) ($matches[1] ?? ''));
        return $fieldKey !== '' ? ($values[$fieldKey] ?? '') : '';
    }

    /**
     * @param array<string, mixed> $definition
     * @return mixed|WP_Error
     */
    private function sanitizeTypedValue(mixed $rawValue, array $definition)
    {
        $type = (string) ($definition['field_type'] ?? 'text');
        $label = (string) ($definition['label'] ?? $definition['field_key'] ?? 'Field');
        $settings = is_array($definition['settings'] ?? null) ? $definition['settings'] : [];
        $validation = is_array($settings['validation'] ?? null) ? $settings['validation'] : [];
        $options = $this->sanitizeOptions($settings['options'] ?? []);

        if (in_array($type, ['checkbox', 'toggle'], true)) {
            return ! empty($rawValue);
        }
        if ($type === 'multiselect') {
            $values = is_array($rawValue) ? $rawValue : [$rawValue];
            $values = array_values(array_unique(array_map('sanitize_text_field', array_slice($values, 0, 50))));
            if ($options && array_diff($values, array_keys($options))) {
                return $this->invalidValue($label, (string) ($definition['field_key'] ?? ''));
            }
            $selectionCount = count($values);
            $minSelections = max(0, (int) ($validation['min_selections'] ?? 0));
            $maxSelections = max(0, (int) ($validation['max_selections'] ?? 0));
            if (($minSelections > 0 && $selectionCount < $minSelections) || ($maxSelections > 0 && $selectionCount > $maxSelections)) {
                return $this->invalidValue($label, (string) ($definition['field_key'] ?? ''));
            }
            return $values;
        }
        if (in_array($type, ['number', 'range'], true)) {
            if (! is_numeric($rawValue)) {
                return $this->invalidValue($label, (string) ($definition['field_key'] ?? ''));
            }
            $value = (float) $rawValue;
            if (($validation['min'] ?? null) !== null && $value < (float) $validation['min']) {
                return $this->invalidValue($label, (string) ($definition['field_key'] ?? ''));
            }
            if (($validation['max'] ?? null) !== null && $value > (float) $validation['max']) {
                return $this->invalidValue($label, (string) ($definition['field_key'] ?? ''));
            }
            $step = ($validation['step'] ?? null) !== null ? (float) $validation['step'] : 0.0;
            if ($step > 0) {
                $base = ($validation['min'] ?? null) !== null ? (float) $validation['min'] : 0.0;
                $steps = ($value - $base) / $step;
                if (abs($steps - round($steps)) > 0.0000001) {
                    return $this->invalidValue($label, (string) ($definition['field_key'] ?? ''));
                }
            }
            return $value;
        }
        if ($type === 'email') {
            $value = sanitize_email((string) $rawValue);
            return $value !== '' ? $value : $this->invalidValue($label, (string) ($definition['field_key'] ?? ''));
        }
        if ($type === 'url') {
            $value = esc_url_raw((string) $rawValue, ['http', 'https']);
            return $value !== '' ? $value : $this->invalidValue($label, (string) ($definition['field_key'] ?? ''));
        }
        if (in_array($type, ['date', 'time', 'datetime'], true)) {
            $value = sanitize_text_field((string) $rawValue);
            $formats = [
                'date' => ['!Y-m-d', 'Y-m-d'],
                'time' => ['!H:i', 'H:i'],
                'datetime' => ['!Y-m-d\TH:i', 'Y-m-d\TH:i'],
            ];
            [$parseFormat, $outputFormat] = $formats[$type];
            $parsed = \DateTimeImmutable::createFromFormat($parseFormat, $value);
            if (! $parsed || $parsed->format($outputFormat) !== $value) {
                return $this->invalidValue($label, (string) ($definition['field_key'] ?? ''));
            }
            $dateMin = (string) ($validation['date_min'] ?? '');
            $dateMax = (string) ($validation['date_max'] ?? '');
            if (($dateMin !== '' && $value < $dateMin) || ($dateMax !== '' && $value > $dateMax)) {
                return $this->invalidValue($label, (string) ($definition['field_key'] ?? ''));
            }
            return $value;
        }
        if ($type === 'color') {
            $value = strtolower(sanitize_text_field((string) $rawValue));
            return preg_match('/^#[0-9a-f]{6}$/', $value) === 1
                ? $value
                : $this->invalidValue($label, (string) ($definition['field_key'] ?? ''));
        }
        if ($type === 'country') {
            $value = strtoupper(substr(sanitize_text_field((string) $rawValue), 0, 2));
            return array_key_exists($value, CountryCatalog::all())
                ? $value
                : $this->invalidValue($label, (string) ($definition['field_key'] ?? ''));
        }
        if (in_array($type, ['select', 'radio'], true)) {
            $value = sanitize_text_field((string) $rawValue);
            return ! $options || array_key_exists($value, $options)
                ? $value
                : $this->invalidValue($label, (string) ($definition['field_key'] ?? ''));
        }

        $value = $type === 'textarea'
            ? sanitize_textarea_field((string) $rawValue)
            : sanitize_text_field((string) $rawValue);
        $length = function_exists('mb_strlen') ? mb_strlen($value) : strlen($value);
        $minLength = max(0, (int) ($validation['min_length'] ?? 0));
        $maxLength = max(0, (int) ($validation['max_length'] ?? 0));
        if (($minLength > 0 && $length < $minLength) || ($maxLength > 0 && $length > $maxLength)) {
            return $this->invalidValue($label, (string) ($definition['field_key'] ?? ''));
        }
        return substr($value, 0, $type === 'textarea' ? 10000 : 1000);
    }

    private function invalidValue(string $label, string $key): WP_Error
    {
        return new WP_Error(
            'h4bs_customer_field_invalid',
            sprintf(
                /* translators: %s: customer field label. */
                __('Enter a valid value for %s.', 'help4-builder-suite'),
                $label
            ),
            ['field' => $key]
        );
    }

    /**
     * @return string|WP_Error
     */
    private function encodeValue(mixed $value, bool $sensitive)
    {
        $json = wp_json_encode($value, JSON_UNESCAPED_SLASHES);
        if (! is_string($json)) {
            return new WP_Error('h4bs_customer_field_encode', __('A customer field value could not be encoded.', 'help4-builder-suite'));
        }
        if (! $sensitive) {
            return 'json:' . $json;
        }

        $key = hash_hmac('sha256', 'h4bs-customer-fields-v1', wp_salt('auth'), true);
        if (function_exists('sodium_crypto_secretbox') && function_exists('sodium_crypto_secretbox_keygen')) {
            $nonce = random_bytes(SODIUM_CRYPTO_SECRETBOX_NONCEBYTES);
            return 's1:' . base64_encode($nonce . sodium_crypto_secretbox($json, $nonce, $key));
        }
        if (function_exists('openssl_encrypt')) {
            $iv = random_bytes(12);
            $tag = '';
            $ciphertext = openssl_encrypt($json, 'aes-256-gcm', $key, OPENSSL_RAW_DATA, $iv, $tag);
            if (is_string($ciphertext)) {
                return 'o1:' . base64_encode($iv . $tag . $ciphertext);
            }
        }
        return new WP_Error('h4bs_customer_field_encryption', __('Sensitive customer fields require Sodium or OpenSSL encryption support.', 'help4-builder-suite'));
    }

    private function decodeValue(string $stored, bool $sensitive): mixed
    {
        $json = '';
        if (! $sensitive && str_starts_with($stored, 'json:')) {
            $json = substr($stored, 5);
        } elseif ($sensitive && str_starts_with($stored, 's1:') && function_exists('sodium_crypto_secretbox_open')) {
            $decoded = base64_decode(substr($stored, 3), true);
            if (is_string($decoded) && strlen($decoded) > SODIUM_CRYPTO_SECRETBOX_NONCEBYTES) {
                $nonce = substr($decoded, 0, SODIUM_CRYPTO_SECRETBOX_NONCEBYTES);
                $ciphertext = substr($decoded, SODIUM_CRYPTO_SECRETBOX_NONCEBYTES);
                $opened = sodium_crypto_secretbox_open($ciphertext, $nonce, hash_hmac('sha256', 'h4bs-customer-fields-v1', wp_salt('auth'), true));
                $json = is_string($opened) ? $opened : '';
            }
        } elseif ($sensitive && str_starts_with($stored, 'o1:') && function_exists('openssl_decrypt')) {
            $decoded = base64_decode(substr($stored, 3), true);
            if (is_string($decoded) && strlen($decoded) > 28) {
                $iv = substr($decoded, 0, 12);
                $tag = substr($decoded, 12, 16);
                $ciphertext = substr($decoded, 28);
                $opened = openssl_decrypt($ciphertext, 'aes-256-gcm', hash_hmac('sha256', 'h4bs-customer-fields-v1', wp_salt('auth'), true), OPENSSL_RAW_DATA, $iv, $tag);
                $json = is_string($opened) ? $opened : '';
            }
        }
        $decoded = json_decode($json, true);
        return json_last_error() === JSON_ERROR_NONE ? $decoded : '';
    }

    /**
     * @return array<string, string>
     */
    private function sanitizeOptions(mixed $rawOptions): array
    {
        if (is_string($rawOptions)) {
            $rawOptions = preg_split('/\r\n|\r|\n/', $rawOptions) ?: [];
        }
        if (! is_array($rawOptions)) {
            return [];
        }
        $options = [];
        foreach (array_slice($rawOptions, 0, 250, true) as $key => $option) {
            if (is_array($option)) {
                continue;
            }
            $rawKey = is_int($key) ? (string) $option : (string) $key;
            $rawLabel = (string) $option;
            if (str_contains($rawLabel, '|')) {
                [$rawKey, $rawLabel] = array_map('trim', explode('|', $rawLabel, 2));
            }
            $cleanKey = substr(sanitize_text_field($rawKey), 0, 100);
            $cleanLabel = substr(sanitize_text_field($rawLabel), 0, 190);
            if ($cleanKey !== '' && $cleanLabel !== '') {
                $options[$cleanKey] = $cleanLabel;
            }
        }
        return $options;
    }

    /**
     * @param array<string, mixed> $input
     * @return array<int, array{field:string,operator:string,value:mixed}>
     */
    private function sanitizeConditions(array $input): array
    {
        if (is_array($input['conditions'] ?? null)) {
            $rows = $input['conditions'];
        } else {
            $fields = is_array($input['condition_field'] ?? null)
                ? $input['condition_field']
                : [$input['condition_field'] ?? ''];
            $operators = is_array($input['condition_operator'] ?? null)
                ? $input['condition_operator']
                : [$input['condition_operator'] ?? 'equals'];
            $values = is_array($input['condition_value'] ?? null)
                ? $input['condition_value']
                : [$input['condition_value'] ?? ''];
            $rows = [];
            foreach ($fields as $index => $field) {
                $rows[] = [
                    'field' => $field,
                    'operator' => $operators[$index] ?? 'equals',
                    'value' => $values[$index] ?? '',
                ];
            }
        }

        $conditions = [];
        foreach (array_slice($rows, 0, 10) as $row) {
            if (! is_array($row)) {
                continue;
            }
            $field = sanitize_key((string) ($row['field'] ?? ''));
            if ($field === '') {
                continue;
            }
            $operator = sanitize_key((string) ($row['operator'] ?? 'equals'));
            if (! in_array($operator, self::CONDITION_OPERATORS, true)) {
                $operator = 'equals';
            }
            $conditions[] = [
                'field' => $field,
                'operator' => $operator,
                'value' => $this->sanitizeLooseValue($row['value'] ?? ''),
            ];
        }
        return $conditions;
    }

    /**
     * @return array<int, int>
     */
    private function sanitizeIdList(mixed $raw): array
    {
        if (! is_array($raw)) {
            $raw = preg_split('/[\s,]+/', (string) $raw) ?: [];
        }
        return array_values(array_unique(array_filter(array_map('absint', array_slice($raw, 0, 500)))));
    }

    /**
     * @return array<int, string>
     */
    private function sanitizeKeyList(mixed $raw): array
    {
        if (! is_array($raw)) {
            $raw = preg_split('/[\s,]+/', (string) $raw) ?: [];
        }
        return array_values(array_unique(array_filter(array_map('sanitize_key', array_slice($raw, 0, 100)))));
    }

    /**
     * @return array<int, string>
     */
    private function sanitizeCountryList(mixed $raw): array
    {
        if (! is_array($raw)) {
            $raw = preg_split('/[\s,]+/', (string) $raw) ?: [];
        }
        $countries = array_map(
            static fn(mixed $value): string => strtoupper(substr(sanitize_text_field((string) $value), 0, 2)),
            array_slice($raw, 0, 250)
        );
        return array_values(array_unique(array_filter($countries)));
    }

    private function sanitizeIsoBoundary(mixed $raw): string
    {
        $value = substr(sanitize_text_field((string) $raw), 0, 16);
        return preg_match('/^\d{4}-\d{2}-\d{2}(?:T\d{2}:\d{2})?$/', $value) === 1 ? $value : '';
    }

    /**
     * @param array<string, mixed> $context
     * @param array<int, string> $keys
     */
    private function contextHasAny(array $context, array $keys): bool
    {
        foreach ($keys as $key) {
            if (array_key_exists($key, $context)) {
                return true;
            }
        }
        return false;
    }

    private function sanitizeLooseValue(mixed $value): mixed
    {
        if (is_array($value)) {
            return array_values(array_map('sanitize_text_field', array_slice($value, 0, 100)));
        }
        return substr(sanitize_text_field((string) $value), 0, 1000);
    }

    private function searchValue(mixed $value): string
    {
        $text = is_array($value) ? implode(' ', array_map('strval', $value)) : (string) $value;
        return substr(sanitize_text_field($text), 0, 190);
    }

    private function isEmptyValue(mixed $value): bool
    {
        return $value === '' || $value === null || $value === false || (is_array($value) && $value === []);
    }

    /**
     * @param array<string, mixed> $definition
     * @return array<string, mixed>
     */
    private function definitionToInput(array $definition): array
    {
        $settings = is_array($definition['settings'] ?? null) ? $definition['settings'] : [];
        $validation = is_array($settings['validation'] ?? null) ? $settings['validation'] : [];
        $visibility = is_array($settings['visibility'] ?? null) ? $settings['visibility'] : [];
        $calculation = is_array($settings['calculation'] ?? null) ? $settings['calculation'] : [];
        $conditions = is_array($settings['conditions'] ?? null) ? $settings['conditions'] : [];
        if (! $conditions && is_array($settings['condition'] ?? null)) {
            $conditions = [$settings['condition']];
        }
        return [
            'field_key' => (string) ($definition['field_key'] ?? ''),
            'label' => (string) ($definition['label'] ?? ''),
            'field_type' => (string) ($definition['field_type'] ?? 'text'),
            'field_group' => (string) ($definition['field_group'] ?? __('Customer details', 'help4-builder-suite')),
            'placements' => array_values((array) ($definition['placements'] ?? ['admin'])),
            'status' => (string) ($definition['status'] ?? 'active'),
            'sort_order' => (int) ($definition['sort_order'] ?? 10),
            'required' => ! empty($definition['required']),
            'customer_editable' => ! empty($definition['customer_editable']),
            'show_in_orders' => ! empty($definition['show_in_orders']),
            'exportable' => ! empty($definition['exportable']),
            'sensitivity' => (string) ($definition['sensitivity'] ?? 'personal'),
            'placeholder' => (string) ($settings['placeholder'] ?? ''),
            'instructions' => (string) ($settings['instructions'] ?? ''),
            'default_value' => $settings['default_value'] ?? '',
            'autocomplete' => (string) ($settings['autocomplete'] ?? ''),
            'input_width' => (string) ($settings['input_width'] ?? 'auto'),
            'options' => is_array($settings['options'] ?? null) ? $settings['options'] : [],
            'min_length' => (int) ($validation['min_length'] ?? 0),
            'max_length' => (int) ($validation['max_length'] ?? 0),
            'min' => $validation['min'] ?? null,
            'max' => $validation['max'] ?? null,
            'step' => $validation['step'] ?? null,
            'min_selections' => (int) ($validation['min_selections'] ?? 0),
            'max_selections' => (int) ($validation['max_selections'] ?? 0),
            'date_min' => (string) ($validation['date_min'] ?? ''),
            'date_max' => (string) ($validation['date_max'] ?? ''),
            'conditions' => array_values(array_filter($conditions, 'is_array')),
            'condition_match' => (string) ($settings['condition_match'] ?? 'all'),
            'calculation_formula' => (string) ($calculation['formula'] ?? ''),
            'calculation_precision' => max(0, min(6, absint($calculation['precision'] ?? 2))),
            'calculation_prefix' => (string) ($calculation['prefix'] ?? ''),
            'calculation_suffix' => (string) ($calculation['suffix'] ?? ''),
            'product_ids' => array_values((array) ($visibility['product_ids'] ?? [])),
            'product_types' => array_values((array) ($visibility['product_types'] ?? [])),
            'customer_roles' => array_values((array) ($visibility['customer_roles'] ?? [])),
            'countries' => array_values((array) ($visibility['countries'] ?? [])),
            'min_subtotal' => $visibility['min_subtotal'] ?? null,
            'max_subtotal' => $visibility['max_subtotal'] ?? null,
        ];
    }

    private function definitionIdByKey(string $fieldKey): int
    {
        global $wpdb;
        $fieldKey = sanitize_key($fieldKey);
        if ($fieldKey === '') {
            return 0;
        }
        return (int) $wpdb->get_var(
            $wpdb->prepare('SELECT id FROM %i WHERE field_key = %s LIMIT 1', $this->definitionsTable(), $fieldKey)
        );
    }

    /**
     * @param array<int, mixed> $additionalKnownKeys
     * @return true|WP_Error
     */
    private function validateFormulaDependencies(
        string $fieldKey,
        string $formula,
        int $definitionId,
        array $additionalKnownKeys,
        string $sensitivity
    ) {
        $definitions = $this->listDefinitions('', [], true);
        $knownKeys = array_fill_keys(array_filter(array_map('sanitize_key', $additionalKnownKeys)), true);
        $sensitivityMap = [];
        $graph = [];
        foreach ($definitions as $definition) {
            $key = sanitize_key((string) ($definition['field_key'] ?? ''));
            if ($key === '' || absint($definition['id'] ?? 0) === $definitionId) {
                continue;
            }
            $knownKeys[$key] = true;
            $sensitivityMap[$key] = (string) ($definition['sensitivity'] ?? 'personal');
            if ((string) ($definition['field_type'] ?? '') === 'calculated') {
                $calculation = is_array($definition['settings']['calculation'] ?? null)
                    ? $definition['settings']['calculation']
                    : [];
                $graph[$key] = CustomerFieldFormula::references((string) ($calculation['formula'] ?? ''));
            }
        }
        $knownKeys[$fieldKey] = true;
        $sensitivityMap[$fieldKey] = in_array($sensitivity, self::SENSITIVITIES, true) ? $sensitivity : 'personal';
        $references = CustomerFieldFormula::references($formula);
        $unknown = array_values(array_filter($references, static fn(string $reference): bool => ! isset($knownKeys[$reference])));
        if ($unknown) {
            return new WP_Error(
                'h4bs_customer_field_formula_reference',
                sprintf(
                    /* translators: %s: unknown customer field keys. */
                    __('The calculation references unknown field keys: %s.', 'help4-builder-suite'),
                    implode(', ', $unknown)
                ),
                ['field' => $fieldKey, 'references' => $unknown]
            );
        }
        if ($sensitivityMap[$fieldKey] !== 'sensitive') {
            $sensitiveReferences = array_values(
                array_filter(
                    $references,
                    static fn(string $reference): bool => ($sensitivityMap[$reference] ?? '') === 'sensitive'
                )
            );
            if ($sensitiveReferences) {
                return new WP_Error(
                    'h4bs_customer_field_formula_sensitive',
                    __('A calculated field derived from sensitive data must also be marked sensitive.', 'help4-builder-suite'),
                    ['field' => $fieldKey, 'references' => $sensitiveReferences]
                );
            }
        }
        $graph[$fieldKey] = $references;
        $states = [];
        $visit = static function (string $key) use (&$visit, &$states, $graph): bool {
            if (($states[$key] ?? 0) === 1) {
                return false;
            }
            if (($states[$key] ?? 0) === 2) {
                return true;
            }
            $states[$key] = 1;
            foreach ($graph[$key] ?? [] as $reference) {
                if (isset($graph[$reference]) && ! $visit($reference)) {
                    return false;
                }
            }
            $states[$key] = 2;
            return true;
        };
        foreach (array_keys($graph) as $key) {
            if (! $visit($key)) {
                return new WP_Error(
                    'h4bs_customer_field_formula_cycle',
                    __('Calculated customer fields contain a dependency cycle.', 'help4-builder-suite'),
                    ['field' => $fieldKey]
                );
            }
        }
        return true;
    }

    /**
     * Prevents active formulas from being broken or from downgrading sensitive data.
     *
     * @return true|WP_Error
     */
    private function validateReferencedFieldChange(
        string $fieldKey,
        int $definitionId,
        string $sensitivity,
        string $status
    ) {
        $fieldKey = sanitize_key($fieldKey);
        if ($fieldKey === '') {
            return true;
        }
        foreach ($this->listDefinitions('', [], true) as $definition) {
            if (
                absint($definition['id'] ?? 0) === $definitionId
                || (string) ($definition['field_type'] ?? '') !== 'calculated'
                || (string) ($definition['status'] ?? '') !== 'active'
            ) {
                continue;
            }
            $calculation = is_array($definition['settings']['calculation'] ?? null)
                ? $definition['settings']['calculation']
                : [];
            if (! in_array($fieldKey, CustomerFieldFormula::references((string) ($calculation['formula'] ?? '')), true)) {
                continue;
            }
            if ($status !== 'active') {
                return new WP_Error(
                    'h4bs_customer_field_formula_dependency_status',
                    sprintf(
                        /* translators: %s: active calculated customer field label. */
                        __('This field is required by the active calculated field %s. Update or archive that calculation first.', 'help4-builder-suite'),
                        (string) ($definition['label'] ?? $definition['field_key'] ?? '')
                    ),
                    ['field' => $fieldKey, 'dependent' => (string) ($definition['field_key'] ?? '')]
                );
            }
            if ($sensitivity === 'sensitive' && (string) ($definition['sensitivity'] ?? 'personal') !== 'sensitive') {
                return new WP_Error(
                    'h4bs_customer_field_formula_sensitive',
                    sprintf(
                        /* translators: %s: calculated customer field label. */
                        __('Mark the dependent calculated field %s as sensitive before protecting this source field.', 'help4-builder-suite'),
                        (string) ($definition['label'] ?? $definition['field_key'] ?? '')
                    ),
                    ['field' => $fieldKey, 'dependent' => (string) ($definition['field_key'] ?? '')]
                );
            }
        }
        return true;
    }

    /**
     * @param array<int, mixed> $rows
     * @return true|WP_Error
     */
    private function validateImportedFormulaCatalog(array $rows, string $conflict)
    {
        $catalog = [];
        foreach ($this->listDefinitions('', [], true) as $definition) {
            $key = sanitize_key((string) ($definition['field_key'] ?? ''));
            if ($key !== '') {
                $catalog[$key] = $this->definitionToInput($definition);
            }
        }
        foreach ($rows as $row) {
            if (! is_array($row)) {
                continue;
            }
            $key = sanitize_key((string) ($row['field_key'] ?? ''));
            if ($key === '' || ($conflict === 'skip' && isset($catalog[$key]))) {
                continue;
            }
            $catalog[$key] = $row;
        }

        $graph = [];
        foreach ($catalog as $key => $definition) {
            if (sanitize_key((string) ($definition['field_type'] ?? 'text')) !== 'calculated') {
                continue;
            }
            $formula = substr(sanitize_text_field((string) ($definition['calculation_formula'] ?? '')), 0, 500);
            $valid = CustomerFieldFormula::validate($formula);
            if ($valid instanceof WP_Error) {
                return $valid;
            }
            $references = CustomerFieldFormula::references($formula);
            $unknown = array_values(array_filter($references, static fn(string $reference): bool => ! isset($catalog[$reference])));
            if ($unknown) {
                return new WP_Error(
                    'h4bs_customer_field_formula_reference',
                    sprintf(
                        /* translators: %s: unknown customer field keys. */
                        __('The calculation import references unknown field keys: %s.', 'help4-builder-suite'),
                        implode(', ', $unknown)
                    ),
                    ['field' => $key, 'references' => $unknown]
                );
            }
            $sensitivity = sanitize_key((string) ($definition['sensitivity'] ?? 'personal'));
            if ($sensitivity !== 'sensitive') {
                $sensitiveReferences = array_values(
                    array_filter(
                        $references,
                        static fn(string $reference): bool => sanitize_key((string) ($catalog[$reference]['sensitivity'] ?? 'personal')) === 'sensitive'
                    )
                );
                if ($sensitiveReferences) {
                    return new WP_Error(
                        'h4bs_customer_field_formula_sensitive',
                        __('A calculated field derived from sensitive data must also be marked sensitive.', 'help4-builder-suite'),
                        ['field' => $key, 'references' => $sensitiveReferences]
                    );
                }
            }
            $graph[$key] = $references;
        }

        $states = [];
        $visit = static function (string $key) use (&$visit, &$states, $graph): bool {
            if (($states[$key] ?? 0) === 1) {
                return false;
            }
            if (($states[$key] ?? 0) === 2) {
                return true;
            }
            $states[$key] = 1;
            foreach ($graph[$key] ?? [] as $reference) {
                if (isset($graph[$reference]) && ! $visit($reference)) {
                    return false;
                }
            }
            $states[$key] = 2;
            return true;
        };
        foreach (array_keys($graph) as $key) {
            if (! $visit($key)) {
                return new WP_Error(
                    'h4bs_customer_field_formula_cycle',
                    __('The customer-field import contains a calculation dependency cycle.', 'help4-builder-suite'),
                    ['field' => $key]
                );
            }
        }
        return true;
    }

    /**
     * @return array<string, array<int, array<string, mixed>>>
     */
    private function presetCatalog(): array
    {
        $common = [
            'placements' => ['checkout', 'account', 'admin'],
            'status' => 'active',
            'customer_editable' => true,
            'show_in_orders' => true,
            'exportable' => true,
            'sensitivity' => 'personal',
        ];
        return [
            'business_profile' => [
                array_merge($common, [
                    'field_key' => 'company_name',
                    'label' => __('Company name', 'help4-builder-suite'),
                    'field_type' => 'text',
                    'field_group' => __('Business profile', 'help4-builder-suite'),
                    'required' => true,
                    'autocomplete' => 'organization',
                    'input_width' => 'full',
                    'sort_order' => 10,
                ]),
                array_merge($common, [
                    'field_key' => 'company_size',
                    'label' => __('Company size', 'help4-builder-suite'),
                    'field_type' => 'select',
                    'field_group' => __('Business profile', 'help4-builder-suite'),
                    'options' => ['solo' => __('Just me', 'help4-builder-suite'), 'small' => __('2 to 10 people', 'help4-builder-suite'), 'medium' => __('11 to 50 people', 'help4-builder-suite'), 'large' => __('More than 50 people', 'help4-builder-suite')],
                    'input_width' => 'half',
                    'sort_order' => 20,
                ]),
                array_merge($common, [
                    'field_key' => 'industry',
                    'label' => __('Industry', 'help4-builder-suite'),
                    'field_type' => 'text',
                    'field_group' => __('Business profile', 'help4-builder-suite'),
                    'input_width' => 'half',
                    'sort_order' => 30,
                ]),
                array_merge($common, [
                    'field_key' => 'tax_identifier',
                    'label' => __('Tax identifier', 'help4-builder-suite'),
                    'field_type' => 'text',
                    'field_group' => __('Business profile', 'help4-builder-suite'),
                    'sensitivity' => 'sensitive',
                    'show_in_orders' => false,
                    'exportable' => false,
                    'sort_order' => 40,
                ]),
            ],
            'service_intake' => [
                array_merge($common, [
                    'field_key' => 'project_goal',
                    'label' => __('What should this project accomplish?', 'help4-builder-suite'),
                    'field_type' => 'textarea',
                    'field_group' => __('Project intake', 'help4-builder-suite'),
                    'required' => true,
                    'min_length' => 20,
                    'input_width' => 'full',
                    'sort_order' => 10,
                ]),
                array_merge($common, [
                    'field_key' => 'budget_range',
                    'label' => __('Budget range', 'help4-builder-suite'),
                    'field_type' => 'select',
                    'field_group' => __('Project intake', 'help4-builder-suite'),
                    'options' => ['under_1k' => __('Under $1,000', 'help4-builder-suite'), '1k_5k' => __('$1,000 to $5,000', 'help4-builder-suite'), '5k_20k' => __('$5,000 to $20,000', 'help4-builder-suite'), '20k_plus' => __('$20,000+', 'help4-builder-suite')],
                    'input_width' => 'half',
                    'sort_order' => 20,
                ]),
                array_merge($common, [
                    'field_key' => 'preferred_launch_date',
                    'label' => __('Preferred launch date', 'help4-builder-suite'),
                    'field_type' => 'date',
                    'field_group' => __('Project intake', 'help4-builder-suite'),
                    'input_width' => 'half',
                    'sort_order' => 30,
                ]),
                array_merge($common, [
                    'field_key' => 'contact_preference',
                    'label' => __('Preferred contact method', 'help4-builder-suite'),
                    'field_type' => 'radio',
                    'field_group' => __('Project intake', 'help4-builder-suite'),
                    'options' => ['email' => __('Email', 'help4-builder-suite'), 'phone' => __('Phone', 'help4-builder-suite'), 'video' => __('Video call', 'help4-builder-suite')],
                    'input_width' => 'full',
                    'sort_order' => 40,
                ]),
            ],
            'delivery_preferences' => [
                array_merge($common, [
                    'field_key' => 'delivery_window',
                    'label' => __('Preferred delivery window', 'help4-builder-suite'),
                    'field_type' => 'select',
                    'field_group' => __('Delivery preferences', 'help4-builder-suite'),
                    'options' => ['morning' => __('Morning', 'help4-builder-suite'), 'afternoon' => __('Afternoon', 'help4-builder-suite'), 'evening' => __('Evening', 'help4-builder-suite')],
                    'input_width' => 'half',
                    'sort_order' => 10,
                ]),
                array_merge($common, [
                    'field_key' => 'leave_at_door',
                    'label' => __('Allow contactless delivery', 'help4-builder-suite'),
                    'field_type' => 'toggle',
                    'field_group' => __('Delivery preferences', 'help4-builder-suite'),
                    'input_width' => 'half',
                    'sort_order' => 20,
                ]),
                array_merge($common, [
                    'field_key' => 'delivery_instructions',
                    'label' => __('Delivery instructions', 'help4-builder-suite'),
                    'field_type' => 'textarea',
                    'field_group' => __('Delivery preferences', 'help4-builder-suite'),
                    'input_width' => 'full',
                    'sort_order' => 30,
                ]),
            ],
            'membership_onboarding' => [
                array_merge($common, [
                    'field_key' => 'experience_level',
                    'label' => __('Experience level', 'help4-builder-suite'),
                    'field_type' => 'select',
                    'field_group' => __('Membership profile', 'help4-builder-suite'),
                    'options' => ['new' => __('Just getting started', 'help4-builder-suite'), 'growing' => __('Building experience', 'help4-builder-suite'), 'advanced' => __('Experienced', 'help4-builder-suite')],
                    'input_width' => 'half',
                    'sort_order' => 10,
                ]),
                array_merge($common, [
                    'field_key' => 'weekly_goal',
                    'label' => __('Weekly goal', 'help4-builder-suite'),
                    'field_type' => 'range',
                    'field_group' => __('Membership profile', 'help4-builder-suite'),
                    'min' => 1,
                    'max' => 20,
                    'step' => 1,
                    'default_value' => 3,
                    'input_width' => 'half',
                    'sort_order' => 20,
                ]),
                array_merge($common, [
                    'field_key' => 'interest_areas',
                    'label' => __('Interest areas', 'help4-builder-suite'),
                    'field_type' => 'multiselect',
                    'field_group' => __('Membership profile', 'help4-builder-suite'),
                    'options' => ['strategy' => __('Strategy', 'help4-builder-suite'), 'marketing' => __('Marketing', 'help4-builder-suite'), 'operations' => __('Operations', 'help4-builder-suite'), 'technology' => __('Technology', 'help4-builder-suite')],
                    'min_selections' => 1,
                    'max_selections' => 3,
                    'input_width' => 'full',
                    'sort_order' => 30,
                ]),
            ],
        ];
    }

    private function definitionsTable(): string
    {
        global $wpdb;
        return $wpdb->prefix . 'h4_customer_fields';
    }

    private function valuesTable(): string
    {
        global $wpdb;
        return $wpdb->prefix . 'h4_customer_field_values';
    }

    private function usesSqlite(): bool
    {
        global $wpdb;
        if (defined('DB_ENGINE') && strtolower((string) constant('DB_ENGINE')) === 'sqlite') {
            return true;
        }
        if (defined('DATABASE_TYPE') && strtolower((string) constant('DATABASE_TYPE')) === 'sqlite') {
            return true;
        }
        return str_contains(strtolower(get_class($wpdb)), 'sqlite');
    }
}
