<?php

declare(strict_types=1);

namespace Tests\Support\Api;

use mysqli;
use Predis\Client as PredisClient;
use RuntimeException;

final class ApiFixtures
{
    private static int $sequence = 0;
    /**
     * @var array<string, bool>
     */
    private array $tableExistsCache = [];

    /**
     * @var array<string, bool>
     */
    private array $tableColumnExistsCache = [];

    public function __construct(
        private readonly mysqli $db,
        private readonly ?PredisClient $redis,
        private readonly ApiCleanup $cleanup,
    ) {
    }

    /**
     * @param array<string, mixed> $attributes
     * @param array<int, string> $permissions
     * @return array<string, mixed>
     */
    public function createUser(array $attributes = [], array $permissions = []): array
    {
        $groupId = $attributes['group_id'] ?? null;
        if ($groupId === null && $permissions !== []) {
            $group = $this->createGroup([], $permissions);
            $groupId = $group['id'];
        }

        $customerNumber = (int)($attributes['customer_number'] ?? $this->uniqueCustomerNumber());
        $displayName = (string)($attributes['display_name'] ?? ('API User ' . $customerNumber));
        $email = (string)($attributes['email'] ?? ('api+' . $customerNumber . '@example.test'));
        $passwordPlaintext = (string)($attributes['password_plaintext'] ?? 'Secret123!');
        $now = $this->now();

        $userId = $this->insertRow('users', [
            'customer_number' => $customerNumber,
            'display_name' => $displayName,
            'email' => $email,
            'phone_country_code' => 45,
            'phone' => (int)substr((string)$customerNumber, -8),
            'password' => password_hash($passwordPlaintext, PASSWORD_DEFAULT),
            'group_id' => (int)($groupId ?? 0),
            'two_factor_enabled' => (int)($attributes['two_factor_enabled'] ?? 0),
            'two_factor_secret' => $attributes['two_factor_secret'] ?? null,
            'created_at' => $attributes['created_at'] ?? $now,
            'updated_at' => $attributes['updated_at'] ?? $now,
        ]);

        $this->deleteRedisPattern('perm:user:' . $userId . ':*');
        $this->deleteRedisPattern('obj_prop:users:' . $userId . ':*');
        $this->cleanup->add(function () use ($userId, $customerNumber): void {
            $this->purgeCustomerTraceData($userId, $customerNumber);
            $this->deleteRedisKey('user_id_from_customer_number_' . $customerNumber);
            $this->deleteRedisKey('customer_number_from_user_id_' . $userId);
            $this->deleteRedisKey('users_' . $customerNumber . '_economic_customer_name');
            $this->deleteRedisKey('`users`_' . $customerNumber . '_economic_customer_name');
            $this->deleteRedisKey('users_' . $userId . '_economic_customer');
            $this->deleteRedisKey('`users`_' . $userId . '_economic_customer');
            $this->deleteRedisKey('users_' . $userId . '_economic_customer_discount_percentage');
            $this->deleteRedisPattern('perm:user:' . $userId . ':*');
            $this->deleteRedisPattern('obj_prop:users:' . $userId . ':*');
        });

        $economicName = (string)($attributes['economic_customer_name'] ?? $displayName);
        $this->seedCustomerNameCache($customerNumber, $economicName);
        $this->seedEconomicCustomerCache($userId, $customerNumber, $economicName, $email);
        $this->seedEconomicCustomerDiscountCache($userId, (int)($attributes['economic_customer_discount_percentage'] ?? 0));

        return [
            'id' => $userId,
            'group_id' => (int)($groupId ?? 0),
            'customer_number' => $customerNumber,
            'display_name' => $displayName,
            'email' => $email,
            'password_plaintext' => $passwordPlaintext,
        ];
    }

    /**
     * @param array<string, mixed> $attributes
     * @param array<int, string> $permissions
     * @return array<string, mixed>
     */
    public function createGroup(array $attributes = [], array $permissions = []): array
    {
        $groupId = $this->insertRow('groups', [
            'name' => (string)($attributes['name'] ?? ('API Group ' . $this->uniqueSuffix())),
            'description' => (string)($attributes['description'] ?? 'API test group'),
            'created_at' => $attributes['created_at'] ?? $this->now(),
            'updated_at' => $attributes['updated_at'] ?? $this->now(),
        ]);

        foreach ($permissions as $permission) {
            $permissionId = $this->insertRow('groups_permissions', [
                'group_id' => $groupId,
                'permission' => $permission,
                'created_at' => $this->now(),
            ]);
            $this->cleanup->add(fn() => $this->deleteById('groups_permissions', $permissionId));
        }

        $this->cleanup->add(fn() => $this->deleteById('groups', $groupId));

        return ['id' => $groupId];
    }

    /**
     * @param array<string, mixed> $attributes
     * @return array<string, mixed>
     */
    public function createDepartment(array $attributes = []): array
    {
        $departmentId = $this->insertRow('departments', [
            'name' => (string)($attributes['name'] ?? ('API Department ' . $this->uniqueSuffix())),
            'description' => (string)($attributes['description'] ?? 'API department'),
            'economic_department_id' => (int)($attributes['economic_department_id'] ?? 0),
            'slack_webhook' => $attributes['slack_webhook'] ?? null,
            'dimension' => (int)($attributes['dimension'] ?? 0),
            'branding' => (int)($attributes['branding'] ?? 0),
            'visible' => (int)($attributes['visible'] ?? 1),
            'archived' => (int)($attributes['archived'] ?? 0),
            'custom_pricing_only' => (int)($attributes['custom_pricing_only'] ?? 0),
            'latitude' => $attributes['latitude'] ?? 0.0,
            'longitude' => $attributes['longitude'] ?? 0.0,
            'order_priority' => (int)($attributes['order_priority'] ?? 0),
            'created_at' => $attributes['created_at'] ?? $this->now(),
            'updated_at' => $attributes['updated_at'] ?? $this->now(),
        ]);

        $this->cleanup->add(fn() => $this->deleteById('departments', $departmentId));
        $this->cleanup->add(fn() => $this->deleteRedisPattern('department_*'));

        return ['id' => $departmentId];
    }

    public function setDepartmentVariable(int $departmentId, string $variable, mixed $value): void
    {
        if ($departmentId <= 0) {
            throw new RuntimeException('Department variable fixtures require a positive department id.');
        }

        $variable = trim($variable);
        if ($variable === '') {
            throw new RuntimeException('Department variable fixtures require a variable name.');
        }

        $conditions = [
            'department_id' => $departmentId,
            'variable' => $variable,
        ];

        $this->deleteWhereIfPossible('department_variables', $conditions);
        $this->cleanupDeleteWhere('department_variables', $conditions);

        $variableId = $this->insertRowWithExistingColumns('department_variables', [
            'department_id' => $departmentId,
            'variable' => $variable,
            'value' => (string)$value,
        ]);
        $this->cleanup->add(fn() => $this->deleteById('department_variables', $variableId));
    }

    public function setDepartmentTimeBookingsEnabled(int $departmentId, bool $enabled): void
    {
        $this->setDepartmentVariable(
            $departmentId,
            'bookingsystem_time_based_enabled',
            $enabled ? 'true' : 'false'
        );
    }

    public function setDepartmentSelfServeEnabled(int $departmentId, bool $enabled): void
    {
        if ($departmentId <= 0) {
            throw new RuntimeException('Department self-serve fixtures require a positive department id.');
        }

        $this->cleanupDeleteWhere('department_variables', [
            'department_id' => $departmentId,
            'variable' => 'selfserve_enabled',
        ]);

        $existingIds = $this->fetchIntColumnWhere('department_variables', 'id', [
            'department_id' => $departmentId,
            'variable' => 'selfserve_enabled',
        ]);
        if ($existingIds !== []) {
            $this->updateById('department_variables', (int)$existingIds[0], [
                'value' => $enabled ? 'true' : 'false',
            ]);
            return;
        }

        $variableId = $this->insertRowWithExistingColumns('department_variables', [
            'department_id' => $departmentId,
            'variable' => 'selfserve_enabled',
            'value' => $enabled ? 'true' : 'false',
        ]);
        $this->cleanup->add(fn() => $this->deleteById('department_variables', $variableId));
    }

    public function setDepartmentShellyTransportMode(int $departmentId, string $mode): void
    {
        if ($departmentId <= 0) {
            throw new RuntimeException('Department Shelly transport fixtures require a positive department id.');
        }

        $mode = strtolower(trim($mode));
        if (!in_array($mode, ['cloud', 'gateway'], true)) {
            throw new RuntimeException('Invalid Shelly transport fixture mode.');
        }

        $conditions = [
            'department_id' => $departmentId,
            'variable' => 'shelly_transport_mode',
        ];
        $this->deleteWhereIfPossible('department_variables', $conditions);
        $this->cleanupDeleteWhere('department_variables', $conditions);

        $variableId = $this->insertRowWithExistingColumns('department_variables', [
            'department_id' => $departmentId,
            'variable' => 'shelly_transport_mode',
            'value' => $mode,
        ]);
        $this->cleanup->add(fn() => $this->deleteById('department_variables', $variableId));
    }

    public function setLaneSelfServeEnabled(int $laneId, bool $enabled): void
    {
        if ($laneId <= 0) {
            throw new RuntimeException('Lane self-serve fixtures require a positive lane id.');
        }

        $this->updateById('department_lanes', $laneId, [
            'selfserve_enabled' => $enabled ? 1 : 0,
        ]);
    }

    /**
     * @param array<string, mixed> $attributes
     * @return array<string, mixed>
     */
    public function createBranding(array $attributes = []): array
    {
        $brandingId = $this->insertRow('branding', [
            'name' => (string)($attributes['name'] ?? ('API Brand ' . $this->uniqueSuffix())),
            'description' => (string)($attributes['description'] ?? 'API branding'),
            'cvr' => (int)($attributes['cvr'] ?? 41004355),
            'address' => $attributes['address'] ?? null,
            'phone_country_code' => $attributes['phone_country_code'] ?? null,
            'phone' => $attributes['phone'] ?? null,
            'email' => $attributes['email'] ?? null,
            'website' => $attributes['website'] ?? null,
            'banner' => $attributes['banner'] ?? null,
            'logo' => $attributes['logo'] ?? null,
            'favicon' => $attributes['favicon'] ?? null,
            'signature' => $attributes['signature'] ?? null,
        ]);

        $this->cleanup->add(fn() => $this->deleteById('branding', $brandingId));

        return array_merge(['id' => $brandingId], $this->fetchRowById('branding', $brandingId) ?? []);
    }

    /**
     * @param array<string, mixed> $attributes
     * @return array<string, mixed>
     */
    public function createDepartmentGate(array $attributes): array
    {
        $departmentId = (int)($attributes['department'] ?? $attributes['department_id'] ?? 0);
        if ($departmentId <= 0) {
            throw new RuntimeException('Department gates require a department id.');
        }

        $gateId = $this->insertRow('department_gates', [
            'department' => $departmentId,
            'is_entrance' => (bool)($attributes['is_entrance'] ?? false),
            'is_exit' => (bool)($attributes['is_exit'] ?? false),
            'name' => (string)($attributes['name'] ?? ('API Gate ' . $this->uniqueSuffix())),
            'config' => $attributes['config'] ?? [
                'type' => 'PHONE_CALL',
                'phone_number' => '+4511122233',
                'call_duration_threshold' => 5,
            ],
            'created_at' => $attributes['created_at'] ?? $this->now(),
            'updated_at' => $attributes['updated_at'] ?? $this->now(),
            'deleted_at' => $attributes['deleted_at'] ?? null,
        ]);

        $this->cleanup->add(fn() => $this->deleteById('department_gates', $gateId));

        return ['id' => $gateId, 'department' => $departmentId];
    }

    /**
     * @param array<string, mixed> $overrides
     * @return array<string, mixed>
     */
    public function createSelfServeScenario(array $overrides = []): array
    {
        foreach ([
            'department_lanes',
            'department_selfserve_conditions',
            'department_selfserve_condition_rules',
            'department_selfserve_questions',
            'department_selfserve_tasks',
            'department_selfserve_vehicle_conditions',
            'selfserve_machine_types',
            'selfserve_wash_sessions',
            'selfserve_wash_session_answers',
            'selfserve_wash_session_tasks',
            'selfserve_wash_session_events',
        ] as $table) {
            if (!$this->tableExists($table)) {
                throw new RuntimeException('Self-serve API fixtures require table ' . $table . '.');
            }
        }

        $suffix = strtolower($this->uniqueSuffix());
        $now = $this->now();
        $relayIds = array_merge([
            'entry' => 'demo-selfserve-' . $suffix . '-entry',
            'exit' => 'demo-selfserve-' . $suffix . '-exit',
            'machine' => 'demo-selfserve-' . $suffix . '-machine',
            'program_picker' => 'demo-selfserve-' . $suffix . '-program-picker',
            'cleaner' => 'demo-selfserve-' . $suffix . '-cleaner',
        ], is_array($overrides['relay_ids'] ?? null) ? $overrides['relay_ids'] : []);

        $department = $this->createDepartment(array_merge([
            'name' => 'API Self-Serve Department ' . strtoupper($suffix),
            'description' => 'API self-serve fixture department',
            'visible' => 1,
            'latitude' => 55.6415,
            'longitude' => 12.0803,
        ], is_array($overrides['department'] ?? null) ? $overrides['department'] : []));

        $category = $this->createCategory([
            'name' => 'API Self-Serve Category ' . strtoupper($suffix),
            'description' => 'API self-serve fixture category',
        ]);
        $this->linkDepartmentCategory((int)$department['id'], (int)$category['id']);

        $productData = array_merge([
            'name' => 'API Self-Serve Wash ' . strtoupper($suffix),
            'description' => 'Comprehensive self-serve fixture wash',
            'price' => 100,
            'subscription_allowed' => 1,
            'category' => (int)$category['id'],
            'piktogram' => 'truck',
            'economic_product_id' => 0,
            'apply_category_discount' => 0,
            'requires_note' => 0,
            'is_wash' => 1,
            'display_in_booking_form' => 1,
            'order_priority' => 1,
            'created_at' => $now,
            'updated_at' => $now,
        ], is_array($overrides['product'] ?? null) ? $overrides['product'] : []);
        $productId = $this->insertRowWithExistingColumns('products', $productData);
        $this->cleanup->add(fn() => $this->deleteById('products', $productId));

        $machineTypeId = $this->insertRowWithExistingColumns('selfserve_machine_types', [
            'name' => 'Portal Machine ' . strtoupper($suffix),
            'description' => 'Fixture machine type with shared task configuration',
            'created_at' => $now,
            'updated_at' => $now,
            'deleted_at' => null,
        ]);
        $this->cleanup->add(fn() => $this->deleteById('selfserve_machine_types', $machineTypeId));

        $laneData = array_merge([
            'department' => (int)$department['id'],
            'name' => 'Demo Lane ' . strtoupper($suffix),
            'relay_in_id' => $relayIds['entry'],
            'relay_out_id' => $relayIds['exit'],
            'relay_machine_id' => $relayIds['machine'],
            'relay_machine_program_picker_id' => $relayIds['program_picker'],
            'relay_machine_cleaner_id' => $relayIds['cleaner'],
            'dynamic_image_id' => 7000 + self::$sequence,
            'machine_type_id' => $machineTypeId,
            'created_at' => $now,
            'updated_at' => $now,
            'deleted_at' => null,
        ], is_array($overrides['lane'] ?? null) ? $overrides['lane'] : []);
        $laneId = $this->insertRowWithExistingColumns('department_lanes', $laneData);
        $this->cleanup->add(fn() => $this->deleteById('department_lanes', $laneId));

        $this->setDepartmentSelfServeEnabled(
            (int)$department['id'],
            (bool)($overrides['department_selfserve_enabled'] ?? true)
        );
        if (array_key_exists('lane_selfserve_enabled', $overrides)) {
            $this->setLaneSelfServeEnabled($laneId, (bool)$overrides['lane_selfserve_enabled']);
        }

        $customer = $this->createUser(array_merge([
            'display_name' => 'API Self-Serve Customer ' . strtoupper($suffix),
            'economic_customer_name' => 'API Self-Serve Customer ' . strtoupper($suffix),
        ], is_array($overrides['customer'] ?? null) ? $overrides['customer'] : []));
        $vehicle = $this->createVehicle(array_merge([
            'customer_id' => (int)$customer['customer_number'],
            'type' => $productId,
            'reg' => 'TW' . strtoupper(substr($suffix, -4)) . '42',
            'wash_subscription' => 1,
            'reference' => 'fixture-vehicle-' . $suffix,
        ], is_array($overrides['vehicle'] ?? null) ? $overrides['vehicle'] : []));

        $conditionId = $this->insertRowWithExistingColumns('department_selfserve_conditions', [
            'department' => (int)$department['id'],
            'lane' => $laneId,
            'product' => $productId,
            'machine_type_id' => $machineTypeId,
            'condition_id' => null,
            'name' => 'Vehicle preparation complete',
            'description' => 'The driver has completed the required pre-wash checks.',
            'created_at' => $now,
            'updated_at' => $now,
            'deleted_at' => null,
        ]);
        $this->cleanup->add(fn() => $this->deleteById('department_selfserve_conditions', $conditionId));

        $questionId = $this->insertRowWithExistingColumns('department_selfserve_questions', [
            'department' => (int)$department['id'],
            'lane' => $laneId,
            'product' => $productId,
            'condition_id' => null,
            'question' => 'Is the tarp removed?',
            'description' => 'Required before the machine relay can be enabled.',
            'order_priority' => 1,
            'created_at' => $now,
            'updated_at' => $now,
            'deleted_at' => null,
        ]);
        $this->cleanup->add(fn() => $this->deleteById('department_selfserve_questions', $questionId));

        $ruleId = $this->insertRowWithExistingColumns('department_selfserve_condition_rules', [
            'condition_id' => $conditionId,
            'type' => 'IS_TRUE',
            'object_type' => 'question',
            'object_id' => $questionId,
            'name' => 'Tarp removed',
            'description' => 'Driver confirmed tarp removal.',
            'created_at' => $now,
            'updated_at' => $now,
            'deleted_at' => null,
        ]);
        $this->cleanup->add(fn() => $this->deleteById('department_selfserve_condition_rules', $ruleId));

        $prepareTaskId = $this->insertRowWithExistingColumns('department_selfserve_tasks', [
            'department' => (int)$department['id'],
            'lane' => $laneId,
            'product' => $productId,
            'machine_type_id' => $machineTypeId,
            'condition_id' => $questionId,
            'gate_type' => 'QUESTION',
            'gate_ref_id' => $questionId,
            'task' => 'Prepare the vehicle',
            'description' => 'Remove loose equipment before starting the machine.',
            'order_priority' => 1,
            'services' => [],
            'buttons' => [1],
            'dynamic_images_vehicle_type' => $productId,
            'created_at' => $now,
            'updated_at' => $now,
            'deleted_at' => null,
        ]);
        $this->cleanup->add(fn() => $this->deleteById('department_selfserve_tasks', $prepareTaskId));

        $machineTaskId = $this->insertRowWithExistingColumns('department_selfserve_tasks', [
            'department' => (int)$department['id'],
            'lane' => $laneId,
            'product' => $productId,
            'machine_type_id' => $machineTypeId,
            'condition_id' => null,
            'gate_type' => 'CONDITION',
            'gate_ref_id' => $conditionId,
            'task' => 'Machine wash access',
            'description' => 'Enables the machine relay after the pre-wash checks pass.',
            'order_priority' => 2,
            'services' => ['MACHINE'],
            'buttons' => [2, 3],
            'dynamic_images_vehicle_type' => $productId,
            'created_at' => $now,
            'updated_at' => $now,
            'deleted_at' => null,
        ]);
        $this->cleanup->add(fn() => $this->deleteById('department_selfserve_tasks', $machineTaskId));

        $vehicleConditionId = $this->insertRowWithExistingColumns('department_selfserve_vehicle_conditions', [
            'department' => (int)$department['id'],
            'lane' => $laneId,
            'customer_id' => (int)$customer['customer_number'],
            'reg' => (string)$vehicle['reg'],
            'question' => $questionId,
            'value' => 1,
            'created_at' => $now,
            'updated_at' => $now,
            'deleted_at' => null,
        ]);
        $this->cleanup->add(fn() => $this->deleteById('department_selfserve_vehicle_conditions', $vehicleConditionId));

        $sessionData = array_merge([
            'lane_id' => $laneId,
            'department_id' => (int)$department['id'],
            'machine_type_id' => $machineTypeId,
            'customer_number' => (int)$customer['customer_number'],
            'vehicle_id' => (int)$vehicle['id'],
            'vehicle_type_id' => $productId,
            'reg' => (string)$vehicle['reg'],
            'status' => 'MACHINE_STARTED',
            'allowed' => 1,
            'machine_relay_enabled' => 1,
            'machine_relay_enabled_at' => $now,
            'machine_start_triggered' => 1,
            'machine_start_triggered_at' => $now,
            'wash_started_at' => $now,
            'order_id' => null,
            'completed_at' => null,
            'metadata_json' => [
                'fixture' => 'selfserve',
                'relay_ids' => $relayIds,
                'evaluation_trace' => [
                    ['task_id' => $machineTaskId, 'satisfied' => true],
                ],
            ],
            'created_at' => $now,
            'updated_at' => $now,
            'deleted_at' => null,
        ], is_array($overrides['session'] ?? null) ? $overrides['session'] : []);
        $sessionId = $this->insertRowWithExistingColumns('selfserve_wash_sessions', $sessionData);
        $this->cleanup->add(fn() => $this->deleteById('selfserve_wash_sessions', $sessionId));

        $answerId = $this->insertRowWithExistingColumns('selfserve_wash_session_answers', [
            'session_id' => $sessionId,
            'question_id' => $questionId,
            'question_text' => 'Is the tarp removed?',
            'answer_value' => 1,
            'answered_at' => $now,
            'created_at' => $now,
            'updated_at' => $now,
            'deleted_at' => null,
        ]);
        $this->cleanup->add(fn() => $this->deleteById('selfserve_wash_session_answers', $answerId));

        $sessionTaskIds = [];
        foreach ([
            [
                'task_id' => $prepareTaskId,
                'task_text' => 'Prepare the vehicle',
                'description' => 'Remove loose equipment before starting the machine.',
                'services' => [],
                'buttons' => [1],
            ],
            [
                'task_id' => $machineTaskId,
                'task_text' => 'Machine wash access',
                'description' => 'Enables the machine relay after the pre-wash checks pass.',
                'services' => ['MACHINE'],
                'buttons' => [2, 3],
            ],
        ] as $taskSnapshot) {
            $sessionTaskId = $this->insertRowWithExistingColumns('selfserve_wash_session_tasks', [
                'session_id' => $sessionId,
                'task_id' => $taskSnapshot['task_id'],
                'task_text' => $taskSnapshot['task_text'],
                'description' => $taskSnapshot['description'],
                'services' => $taskSnapshot['services'],
                'buttons' => $taskSnapshot['buttons'],
                'dynamic_image_id' => null,
                'dynamic_images_vehicle_type' => $productId,
                'created_at' => $now,
                'updated_at' => $now,
                'deleted_at' => null,
            ]);
            $sessionTaskIds[] = $sessionTaskId;
            $this->cleanup->add(fn() => $this->deleteById('selfserve_wash_session_tasks', $sessionTaskId));
        }

        $eventIds = [];
        foreach ([
            ['SESSION_SYNCED', ['allowed' => true, 'source' => 'fixture']],
            ['MACHINE_RELAY_ENABLED', ['relay_id' => $relayIds['machine']]],
            ['MACHINE_START_TRIGGERED', ['lane_id' => $laneId]],
        ] as [$eventType, $payload]) {
            $eventId = $this->insertRowWithExistingColumns('selfserve_wash_session_events', [
                'session_id' => $sessionId,
                'event_type' => $eventType,
                'payload_json' => $payload,
                'created_at' => $now,
            ]);
            $eventIds[] = $eventId;
            $this->cleanup->add(fn() => $this->deleteById('selfserve_wash_session_events', $eventId));
        }

        $this->setModuleConfig('selfserve', 'enabled', 'true');
        $this->setModuleConfig(
            'selfserve',
            'machine_wash_enabled',
            (bool)($overrides['machine_wash_enabled'] ?? true) ? 'true' : 'false'
        );

        return [
            'department' => $department,
            'category' => $category,
            'product' => ['id' => $productId] + $productData,
            'machine_type' => ['id' => $machineTypeId],
            'lane' => ['id' => $laneId] + $laneData,
            'customer' => $customer,
            'vehicle' => $vehicle,
            'condition' => ['id' => $conditionId],
            'question' => ['id' => $questionId],
            'rule' => ['id' => $ruleId],
            'tasks' => [
                ['id' => $prepareTaskId],
                ['id' => $machineTaskId],
            ],
            'vehicle_condition' => ['id' => $vehicleConditionId],
            'session' => ['id' => $sessionId] + $sessionData,
            'answer' => ['id' => $answerId],
            'session_tasks' => array_map(static fn(int $id): array => ['id' => $id], $sessionTaskIds),
            'events' => array_map(static fn(int $id): array => ['id' => $id], $eventIds),
            'relay_ids' => $relayIds,
        ];
    }

    /**
     * @param array<string, mixed> $attributes
     * @return array<string, mixed>
     */
    public function createCategory(array $attributes = []): array
    {
        $categoryId = $this->insertRow('categories', [
            'name' => (string)($attributes['name'] ?? ('API Category ' . $this->uniqueSuffix())),
            'description' => (string)($attributes['description'] ?? 'API category'),
            'created_at' => $attributes['created_at'] ?? $this->now(),
            'updated_at' => $attributes['updated_at'] ?? $this->now(),
        ]);

        $this->cleanup->add(fn() => $this->deleteById('categories', $categoryId));

        return ['id' => $categoryId];
    }

    /**
     * @param array<string, mixed> $attributes
     * @return array<string, mixed>
     */
    public function createProduct(array $attributes = []): array
    {
        $categoryId = (int)($attributes['category'] ?? 0);
        if ($categoryId <= 0) {
            $category = $this->createCategory();
            $categoryId = (int)$category['id'];
        }

        $productData = [
            'name' => (string)($attributes['name'] ?? ('API Product ' . $this->uniqueSuffix())),
            'description' => (string)($attributes['description'] ?? 'API product'),
            'price' => (int)($attributes['price'] ?? 100),
            'subscription_allowed' => (int)($attributes['subscription_allowed'] ?? 1),
            'category' => $categoryId,
            'piktogram' => $attributes['piktogram'] ?? 'truck',
            'economic_product_id' => $attributes['economic_product_id'] ?? 0,
            'apply_category_discount' => (int)($attributes['apply_category_discount'] ?? 0),
            'requires_note' => (int)($attributes['requires_note'] ?? 0),
            'is_wash' => (int)($attributes['is_wash'] ?? 0),
            'display_in_booking_form' => (int)($attributes['display_in_booking_form'] ?? 1),
            'order_priority' => (int)($attributes['order_priority'] ?? 0),
            'created_at' => $attributes['created_at'] ?? $this->now(),
            'updated_at' => $attributes['updated_at'] ?? $this->now(),
            'deleted_at' => $attributes['deleted_at'] ?? null,
        ];
        if (isset($attributes['id'])) {
            $productData = ['id' => (int)$attributes['id']] + $productData;
        }

        $productId = $this->insertRowWithExistingColumns('products', $productData);

        $this->deleteRedisPattern('obj_prop:products:' . $productId . ':*');
        $this->cleanup->add(fn() => $this->deleteById('products', $productId));
        $this->cleanup->add(fn() => $this->deleteRedisPattern('obj_prop:products:' . $productId . ':*'));

        return array_merge(['id' => $productId, 'category' => $categoryId], $this->fetchRowById('products', $productId) ?? []);
    }

    /**
     * @param array<string, mixed> $attributes
     * @return array<string, mixed>
     */
    public function createPriceOverride(array $attributes): array
    {
        $userId = (int)($attributes['user_id'] ?? 0);
        $objectId = (string)($attributes['product_or_category_id'] ?? '');
        if ($userId <= 0 || $objectId === '') {
            throw new RuntimeException('Price overrides require user_id and product_or_category_id.');
        }

        $overrideId = $this->insertRowWithExistingColumns('price_overrides', [
            'user_id' => $userId,
            'is_category' => (int)($attributes['is_category'] ?? 0),
            'product_or_category_id' => $objectId,
            'percentage' => (int)($attributes['percentage'] ?? 0),
            'fixed_price' => $attributes['fixed_price'] ?? null,
            'created_at' => $attributes['created_at'] ?? $this->now(),
            'updated_at' => $attributes['updated_at'] ?? $this->now(),
        ]);

        $this->cleanup->add(fn() => $this->deleteById('price_overrides', $overrideId));

        return array_merge(['id' => $overrideId], $this->fetchRowById('price_overrides', $overrideId) ?? []);
    }

    public function linkDepartmentCategory(int $departmentId, int $categoryId): int
    {
        $linkId = $this->insertRow('department_categories', [
            'department_id' => $departmentId,
            'category_id' => $categoryId,
            'created_at' => $this->now(),
            'updated_at' => $this->now(),
            'deleted_at' => null,
        ]);

        $this->cleanup->add(fn() => $this->deleteById('department_categories', $linkId));

        return $linkId;
    }

    /**
     * @param array<string, mixed> $attributes
     * @return array<string, mixed>
     */
    public function createInvoiceCollection(array $attributes): array
    {
        $customerNumber = (int)($attributes['customer_number'] ?? 0);
        if ($customerNumber <= 0) {
            throw new RuntimeException('Invoice collections require a customer_number.');
        }

        $invoiceId = $this->insertRow('collected_order_invoices', [
            'customer_number' => $customerNumber,
            'name' => (string)($attributes['name'] ?? ('API Invoice ' . $customerNumber)),
            'notes' => $attributes['notes'] ?? '',
            'processor' => (int)($attributes['processor'] ?? 0),
            'external_id' => $attributes['external_id'] ?? null,
            'booked_invoice_id' => $attributes['booked_invoice_id'] ?? null,
            'po_number' => $attributes['po_number'] ?? null,
            'error_message' => $attributes['error_message'] ?? null,
            'closed_at' => $attributes['closed_at'] ?? null,
            'created_at' => $attributes['created_at'] ?? $this->now(),
            'updated_at' => $attributes['updated_at'] ?? $this->now(),
        ]);

        $this->cleanup->add(fn() => $this->deleteById('collected_order_invoices', $invoiceId));

        return [
            'id' => $invoiceId,
            'customer_number' => $customerNumber,
        ];
    }

    /**
     * @param array<string, mixed> $attributes
     * @return array<string, mixed>
     */
    public function createOrder(array $attributes): array
    {
        $customerId = (int)($attributes['customer_id'] ?? 0);
        $departmentId = (int)($attributes['department_id'] ?? 0);
        if ($customerId <= 0 || $departmentId <= 0) {
            throw new RuntimeException('Orders require customer_id and department_id.');
        }

        $invoiceCollectionId = (int)($attributes['invoice_collection_id'] ?? 0);
        if ($invoiceCollectionId <= 0) {
            $invoiceCollection = $this->createInvoiceCollection([
                'customer_number' => $customerId,
            ]);
            $invoiceCollectionId = (int)$invoiceCollection['id'];
        }

        $orderId = $this->insertRow('orders', [
            'customer_id' => $customerId,
            'cashier_id' => (int)($attributes['cashier_id'] ?? 1),
            'department_id' => $departmentId,
            'reference' => (string)($attributes['reference'] ?? 'API-REF'),
            'notes' => (string)($attributes['notes'] ?? 'API order'),
            'reg_1' => (string)($attributes['reg_1'] ?? 'ABCD123'),
            'reg_2' => (string)($attributes['reg_2'] ?? ''),
            'reg_3' => (string)($attributes['reg_3'] ?? ''),
            'invoice_collection_id' => $invoiceCollectionId,
            'booking_id' => $attributes['booking_id'] ?? null,
            'wash_id' => $attributes['wash_id'] ?? null,
            'lane' => $attributes['lane'] ?? null,
            'po' => $attributes['po'] ?? null,
            'safety_seal' => $attributes['safety_seal'] ?? null,
            'using_hand_held' => (int)($attributes['using_hand_held'] ?? 0),
            'include_in_invoice' => $attributes['include_in_invoice'] ?? 1,
            'created_at' => $attributes['created_at'] ?? $this->now(),
            'updated_at' => $attributes['updated_at'] ?? $this->now(),
            'completed_at' => $attributes['completed_at'] ?? null,
            'deleted_at' => null,
        ]);

        $this->cleanup->add(function () use ($orderId): void {
            $this->deleteWhere('order_items', ['order_id' => $orderId]);
            $this->deleteById('orders', $orderId);
            $this->deleteRedisKey('orders_' . $orderId . '_asArray');
            $this->deleteRedisKey('orders_' . $orderId . '_pending_handheld_cache_indicator');
        });

        return [
            'id' => $orderId,
            'invoice_collection_id' => $invoiceCollectionId,
        ];
    }

    /**
     * @param array<string, mixed> $attributes
     * @return array<string, mixed>
     */
    public function createOrderBooking(array $attributes): array
    {
        $customerNumber = (int)($attributes['customer_number'] ?? 0);
        $departmentId = (int)($attributes['department'] ?? $attributes['department_id'] ?? 0);
        if ($customerNumber <= 0 || $departmentId <= 0) {
            throw new RuntimeException('Order bookings require customer_number and department.');
        }

        $bookingId = $this->insertRow('order_bookings', [
            'customer_number' => $customerNumber,
            'department' => $departmentId,
            'reg_1' => (string)($attributes['reg_1'] ?? 'BOOK123'),
            'reg_2' => (string)($attributes['reg_2'] ?? ''),
            'reg_3' => (string)($attributes['reg_3'] ?? ''),
            'datetime' => $attributes['datetime'] ?? $this->now(),
            'note' => (string)($attributes['note'] ?? ''),
            'reference' => (string)($attributes['reference'] ?? 'API-BOOKING'),
            'po' => (string)($attributes['po'] ?? ''),
            'pickup' => (int)($attributes['pickup'] ?? 0),
            'items' => $attributes['items'] ?? [],
            'order_id' => $attributes['order_id'] ?? null,
            'created_at' => $attributes['created_at'] ?? $this->now(),
            'updated_at' => $attributes['updated_at'] ?? $this->now(),
            'deleted_at' => $attributes['deleted_at'] ?? null,
        ]);

        $this->cleanup->add(function () use ($bookingId): void {
            $this->deleteById('order_bookings', $bookingId);
            $this->deleteRedisKey('order_bookings_' . $bookingId . '_asArray');
            $this->deleteRedisPattern('order_bookings:*');
        });

        return [
            'id' => $bookingId,
            'customer_number' => $customerNumber,
            'department' => $departmentId,
            'order_id' => $attributes['order_id'] ?? null,
        ];
    }

    /**
     * @param array<string, mixed> $attributes
     * @return array<string, mixed>
     */
    public function createLegacyBooking(array $attributes): array
    {
        $customerNumber = (int)($attributes['customer_number'] ?? 0);
        $departmentId = (int)($attributes['department'] ?? $attributes['department_id'] ?? 0);
        if ($customerNumber <= 0 || $departmentId <= 0) {
            throw new RuntimeException('Legacy bookings require customer_number and department.');
        }

        $bookingId = $this->insertRowWithExistingColumns('bookings', [
            'customer_number' => $customerNumber,
            'wash_type' => (string)($attributes['wash_type'] ?? 'API wash'),
            'contact_email' => (string)($attributes['contact_email'] ?? 'customer@example.test'),
            'reference_number' => (string)($attributes['reference_number'] ?? 'API-LEGACY-BOOKING'),
            'regNrTraekker' => (string)($attributes['regNrTraekker'] ?? 'LEG123'),
            'regNrTrailer' => (string)($attributes['regNrTrailer'] ?? ''),
            'washCertificateEmail' => (string)($attributes['washCertificateEmail'] ?? ''),
            'date' => $attributes['date'] ?? $this->now(),
            'department' => $departmentId,
            'pickup_bool' => (int)($attributes['pickup_bool'] ?? 0),
            'notes' => (string)($attributes['notes'] ?? ''),
            'washCertificateStatus' => (string)($attributes['washCertificateStatus'] ?? 'pending'),
            'washCertificateUrl' => (string)($attributes['washCertificateUrl'] ?? ''),
            'wash_certificate_pdf' => $attributes['wash_certificate_pdf'] ?? null,
            'status' => (string)($attributes['status'] ?? 'pending'),
            'data' => json_encode($attributes['data'] ?? [], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
            'virtual_cart' => (int)($attributes['virtual_cart'] ?? 0),
            'created_at' => $attributes['created_at'] ?? $this->now(),
            'updated_at' => $attributes['updated_at'] ?? $this->now(),
            'deleted_at' => $attributes['deleted_at'] ?? null,
        ]);

        $this->cleanup->add(function () use ($bookingId): void {
            $this->deleteById('bookings', $bookingId);
            $this->deleteRedisKey('bookings_' . $bookingId . '_asArray');
            $this->deleteRedisPattern('bookings:*');
        });

        return [
            'id' => $bookingId,
            'customer_number' => $customerNumber,
            'department' => $departmentId,
        ];
    }

    /**
     * @param array<string, mixed> $attributes
     * @return array<string, mixed>
     */
    public function createVehicle(array $attributes): array
    {
        $customerId = (int)($attributes['customer_id'] ?? 0);
        $type = (int)($attributes['type'] ?? 0);
        $reg = trim((string)($attributes['reg'] ?? ''));
        if ($customerId <= 0 || $type <= 0 || $reg === '') {
            throw new RuntimeException('Vehicles require customer_id, type, and reg.');
        }

        $vehicleId = $this->insertRow('customer_vehicles', [
            'customer_id' => $customerId,
            'type' => $type,
            'reg' => $reg,
            'wash_subscription' => (int)($attributes['wash_subscription'] ?? 0),
            'notes' => $attributes['notes'] ?? null,
            'reference' => $attributes['reference'] ?? null,
            'created_at' => $attributes['created_at'] ?? $this->now(),
            'updated_at' => $attributes['updated_at'] ?? $this->now(),
        ]);

        $this->cleanup->add(fn() => $this->deleteById('customer_vehicles', $vehicleId));

        return [
            'id' => $vehicleId,
            'customer_id' => $customerId,
            'type' => $type,
            'reg' => $reg,
            'wash_subscription' => (bool)($attributes['wash_subscription'] ?? false),
            'reference' => $attributes['reference'] ?? null,
        ];
    }

    /**
     * @param array<string, mixed> $attributes
     * @return array<string, mixed>
     */
    public function createOrderItem(array $attributes): array
    {
        $orderId = (int)($attributes['order_id'] ?? 0);
        $productId = (int)($attributes['product_id'] ?? 0);
        $cashierId = (int)($attributes['cashier_id'] ?? 0);
        if ($orderId <= 0 || $productId <= 0 || $cashierId <= 0) {
            throw new RuntimeException('Order items require order_id, product_id, and cashier_id.');
        }

        $orderItemId = $this->insertRow('order_items', [
            'order_id' => $orderId,
            'product_id' => $productId,
            'reference' => (string)($attributes['reference'] ?? ''),
            'notes' => $attributes['notes'] ?? null,
            'cashier_id' => $cashierId,
            'price' => (int)($attributes['price'] ?? 0),
            'quantity' => (int)($attributes['quantity'] ?? 1),
            'related_item_id' => $attributes['related_item_id'] ?? null,
            'include_in_invoice' => (int)($attributes['include_in_invoice'] ?? 1),
            'created_at' => $attributes['created_at'] ?? $this->now(),
            'updated_at' => $attributes['updated_at'] ?? $this->now(),
            'deleted_at' => $attributes['deleted_at'] ?? null,
        ]);

        $this->cleanup->add(fn() => $this->deleteById('order_items', $orderItemId));

        return [
            'id' => $orderItemId,
            'order_id' => $orderId,
            'product_id' => $productId,
        ];
    }

    /**
     * @param array<string, mixed> $attributes
     * @return array<string, mixed>
     */
    public function createOrderAttachment(array $attributes): array
    {
        $orderId = (int)($attributes['order_id'] ?? 0);
        if ($orderId <= 0) {
            throw new RuntimeException('Order attachments require order_id.');
        }

        $attachmentId = $this->insertRow('object_attachments', [
            'object_type' => 'orders',
            'object_id' => $orderId,
            'content' => $attributes['content'] ?? '{"document":"api-test.pdf","other":"api-test.pdf"}',
            'created_at' => $attributes['created_at'] ?? $this->now(),
            'updated_at' => $attributes['updated_at'] ?? $this->now(),
            'deleted_at' => $attributes['deleted_at'] ?? null,
        ]);

        $this->cleanup->add(fn() => $this->deleteById('object_attachments', $attachmentId));

        return [
            'id' => $attachmentId,
            'order_id' => $orderId,
        ];
    }

    /**
     * @param array<string, mixed> $attributes
     * @return array<string, mixed>
     */
    public function createSubuser(array $attributes = []): array
    {
        $username = (string)($attributes['username'] ?? ('api-subuser-' . strtolower($this->uniqueSuffix())));
        $passwordPlaintext = array_key_exists('password_plaintext', $attributes)
            ? $attributes['password_plaintext']
            : 'Secret123!';
        $name = (string)($attributes['name'] ?? 'API Subuser');
        $email = (string)($attributes['email'] ?? ($username . '@example.test'));
        $now = $this->now();

        $subuserId = $this->insertRow('subusers', [
            'username' => $username,
            'password' => $passwordPlaintext === null ? null : password_hash((string)$passwordPlaintext, PASSWORD_DEFAULT),
            'name' => $name,
            'email' => $email,
            'email_verified_at' => $attributes['email_verified_at'] ?? null,
            'phone_country_code' => (int)($attributes['phone_country_code'] ?? 45),
            'phone' => (int)($attributes['phone'] ?? (10000000 + (++self::$sequence))),
            'phone_verified_at' => $attributes['phone_verified_at'] ?? null,
            'two_factor_enabled' => 0,
            'two_factor_secret' => null,
            'created_at' => $attributes['created_at'] ?? $now,
            'updated_at' => $attributes['updated_at'] ?? $now,
            'suspended_at' => $attributes['suspended_at'] ?? null,
        ]);

        $this->cleanup->add(function () use ($subuserId): void {
            $this->deleteWhere('subuser_grants', ['subuser' => $subuserId]);
            $this->deleteWhere('tokens', ['user_id' => $subuserId, 'type' => 'AUTH_TOKEN_SUBUSER']);
            $this->deleteById('subusers', $subuserId);
            $this->deleteRedisPattern('session_token:*');
        });

        return [
            'id' => $subuserId,
            'username' => $username,
            'password_plaintext' => $passwordPlaintext,
        ];
    }

    public function grantSubuser(int $subuserId, int $customerNumber, array $permissions, array $attributes = []): int
    {
        $grantId = $this->insertRow('subuser_grants', [
            'billing_customer_number' => $customerNumber,
            'subuser' => $subuserId,
            'assigned_vehicle_id' => $attributes['assigned_vehicle_id'] ?? null,
            'enabled' => $attributes['enabled'] ?? 1,
            'note' => $attributes['note'] ?? 'API test grant',
            'permissions' => json_encode(array_values($permissions), JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
            'created_at' => $attributes['created_at'] ?? $this->now(),
            'updated_at' => $attributes['updated_at'] ?? $this->now(),
            'deleted_at' => null,
        ]);

        $this->cleanup->add(fn() => $this->deleteById('subuser_grants', $grantId));

        return $grantId;
    }

    /**
     * @return array{user:array<string,mixed>,token:string,headers:array<string,string>}
     */
    public function createUserSession(array $permissions = [], array $userAttributes = []): array
    {
        if ($permissions === [] && !array_key_exists('group_id', $userAttributes)) {
            $group = $this->createGroup();
            $userAttributes['group_id'] = $group['id'];
        }

        $user = $this->createUser($userAttributes, $permissions);
        $token = $this->createAuthToken((int)$user['id']);

        return [
            'user' => $user,
            'token' => $token,
            'headers' => $this->bearerHeaders($token),
        ];
    }

    /**
     * @param array<int, string> $permissions
     * @param array<string, mixed> $userAttributes
     * @return array{user:array<string,mixed>,token:string,headers:array<string,string>}
     */
    public function createEdgeOperatorSession(int $departmentId, array $permissions = [], array $userAttributes = []): array
    {
        if ($departmentId <= 0) {
            throw new RuntimeException('Edge operator sessions require a positive department id.');
        }

        $permissions = array_values(array_unique(array_merge(
            ['modules_shelly_config', 'department_access_' . $departmentId],
            $permissions
        )));

        return $this->createUserSession($permissions, $userAttributes);
    }

    /**
     * @param array<int, string> $permissions
     * @return array{user:array<string,mixed>,subuser:array<string,mixed>,token:string,headers:array<string,string>}
     */
    public function createSubuserSession(int $customerNumber, array $permissions, array $subuserAttributes = []): array
    {
        $subuser = $this->createSubuser($subuserAttributes);
        $this->grantSubuser((int)$subuser['id'], $customerNumber, $permissions);
        $token = $this->createCachedSubuserSessionToken((int)$subuser['id']);

        return [
            'user' => [
                'customer_number' => $customerNumber,
            ],
            'subuser' => $subuser,
            'token' => $token,
            'headers' => $this->bearerHeaders($token, [
                'X-Customer-Number' => (string)$customerNumber,
            ]),
        ];
    }

    private function createCachedSubuserSessionToken(int $subuserId): string
    {
        if ($this->redis === null) {
            throw new RuntimeException('API tests require Redis for subuser session fixtures.');
        }

        $token = bin2hex(random_bytes(32));
        $cacheKey = '`subusers`_subuser_sessions_session_token:' . $token;

        $this->redis->set($cacheKey, (string)$subuserId);
        $this->redis->expire($cacheKey, 7 * 24 * 60 * 60);
        $this->cleanup->add(fn() => $this->deleteRedisKey($cacheKey));

        return $token;
    }

    public function createAuthToken(int $userId, string $type = 'AUTH_TOKEN', ?string $token = null): string
    {
        $token = $token ?: bin2hex(random_bytes(32));
        $tokenId = $this->insertRow('tokens', [
            'user_id' => $userId,
            'type' => $type,
            'description' => 'API test token',
            'token' => $token,
            'created_at' => $this->now(),
        ]);

        $this->cleanup->add(function () use ($tokenId, $token): void {
            $this->deleteById('tokens', $tokenId);
            $this->deleteRedisKey('token_' . $token);
            $this->deleteRedisKey('auth_session_' . $token);
        });

        return $token;
    }

    /**
     * @param array<string, mixed> $attributes
     * @return array<string, mixed>
     */
    public function createPasskey(array $attributes): array
    {
        $userId = (int)($attributes['user_id'] ?? 0);
        if ($userId <= 0) {
            throw new RuntimeException('Passkey fixtures require user_id.');
        }

        $credentialId = (string)($attributes['credential_id'] ?? ('credential-' . strtolower($this->uniqueSuffix())));
        $passkeyId = $this->insertRow('passkeys', [
            'user_id' => $userId,
            'is_subuser' => !empty($attributes['is_subuser']) ? 1 : 0,
            'credential_id' => $credentialId,
            'public_key' => $attributes['public_key'] ?? str_repeat('A', 64),
            'algorithm' => $attributes['algorithm'] ?? 'ES256',
            'transports' => json_encode($attributes['transports'] ?? ['internal'], JSON_UNESCAPED_SLASHES),
            'sign_count' => (int)($attributes['sign_count'] ?? 0),
            'backup_state' => json_encode($attributes['backup_state'] ?? new \stdClass(), JSON_UNESCAPED_SLASHES),
            'name' => $attributes['name'] ?? 'API test passkey',
            'created_at' => $attributes['created_at'] ?? $this->now(),
            'updated_at' => $attributes['updated_at'] ?? $this->now(),
            'deleted_at' => $attributes['deleted_at'] ?? null,
        ]);

        $this->cleanup->add(fn() => $this->deleteById('passkeys', $passkeyId));

        return [
            'id' => $passkeyId,
            'credential_id' => $credentialId,
            'user_id' => $userId,
        ];
    }

    public function addCustomerAttribute(int $userId, string $attribute): int
    {
        $attributeId = $this->insertRow('customer_attributes', [
            'user_id' => $userId,
            'attribute' => $attribute,
        ]);

        $this->cleanup->add(fn() => $this->deleteById('customer_attributes', $attributeId));

        return $attributeId;
    }

    public function preserveModuleConfig(string $module, string $variable): void
    {
        $existing = $this->fetchModuleConfig($module, $variable);
        $conditions = [
            'module' => $module,
            'variable' => $variable,
        ];

        $this->cleanup->add(function () use ($conditions, $existing): void {
            if ($existing === null) {
                $this->deleteWhereIfPossible('module_config', $conditions);
                return;
            }

            $current = $this->fetchModuleConfig((string)$conditions['module'], (string)$conditions['variable']);
            $data = [
                'value' => $existing['value'] ?? null,
                'type' => $existing['type'] ?? null,
                'created_at' => $existing['created_at'] ?? null,
                'updated_at' => $existing['updated_at'] ?? null,
            ];

            if ($current === null) {
                $this->insertRow('module_config', [
                    'module' => $existing['module'] ?? $conditions['module'],
                    'variable' => $existing['variable'] ?? $conditions['variable'],
                    ...$data,
                ]);
                return;
            }

            $this->updateWhere('module_config', $conditions, $data);
        });
    }

    public function setModuleConfig(string $module, string $variable, string $value, string $type = 'bool'): void
    {
        $existing = $this->fetchModuleConfig($module, $variable);

        if ($existing !== null) {
            $conditions = [
                'module' => $module,
                'variable' => $variable,
            ];

            $this->updateWhere('module_config', $conditions, [
                'value' => $value,
                'type' => $type,
                'updated_at' => $this->now(),
            ]);

            $this->cleanup->add(function () use ($conditions, $existing): void {
                $this->updateWhere('module_config', $conditions, [
                    'value' => $existing['value'] ?? null,
                    'type' => $existing['type'] ?? null,
                    'updated_at' => $existing['updated_at'] ?? null,
                    'created_at' => $existing['created_at'] ?? null,
                ]);
            });

            return;
        }

        $id = $this->insertRow('module_config', [
            'module' => $module,
            'variable' => $variable,
            'value' => $value,
            'type' => $type,
            'created_at' => $this->now(),
            'updated_at' => $this->now(),
        ]);

        $this->cleanup->add(fn() => $this->deleteById('module_config', $id));
    }

    /**
     * @param array<int, string> $permissions
     * @param array<string, mixed>|null $overrides
     */
    public function cacheAuthSessionForUser(array $user, string $token, array $permissions, ?array $overrides = null): void
    {
        $payload = [
            'id' => (int)$user['id'],
            'customer_number' => (int)$user['customer_number'],
            'customer_name' => (string)$user['display_name'],
            'display_name' => (string)$user['display_name'],
            'group_id' => (int)$user['group_id'],
            'phone' => [
                'country_code' => 45,
                'number' => (int)substr((string)$user['customer_number'], -8),
            ],
            'email' => (string)$user['email'],
            'notifications' => [
                'sms_notifications_enabled' => false,
                'email_notifications_enabled' => false,
                'wash_certificate_email' => null,
                'superuser_new_customer_email_notifications_enabled' => false,
            ],
            'created_at' => $this->now(),
            'updated_at' => $this->now(),
            'economic_customer' => [
                'customerNumber' => (int)$user['customer_number'],
                'name' => (string)$user['display_name'],
            ],
            'permissions' => array_values($permissions),
            'two_factor_enabled' => false,
        ];

        if ($overrides !== null) {
            $payload = array_replace_recursive($payload, $overrides);
        }

        $this->setRedisJson('auth_session_' . $token, $payload);
    }

    /**
     * @param array<string, string> $extraHeaders
     * @return array<string, string>
     */
    public function bearerHeaders(string $token, array $extraHeaders = []): array
    {
        return array_merge([
            'Authorization' => 'Bearer ' . $token,
        ], $extraHeaders);
    }

    public function clearEdgeGatewayViewCache(): void
    {
        $this->deleteRedisPattern('edge_gateway:view:v1:*');

        if (class_exists(\classes\edge_gateway_view_cache::class)) {
            \classes\edge_gateway_view_cache::clearAll();
        }
    }

    public function fetchRowById(string $table, int $id): ?array
    {
        $table = $this->sanitizeIdentifier($table);

        return $this->queryOneBySql("SELECT * FROM `{$table}` WHERE id = {$id} LIMIT 1");
    }

    /**
     * @param array<string, mixed> $attributes
     * @return array<string, mixed>
     */
    public function createEdgeInstallToken(array $attributes): array
    {
        $departmentId = (int)($attributes['department_id'] ?? 0);
        if ($departmentId <= 0) {
            throw new RuntimeException('Edge install tokens require department_id.');
        }

        $token = (string)($attributes['token'] ?? (bin2hex(random_bytes(18)) . $this->uniqueSuffix()));
        $createdAt = (string)($attributes['created_at'] ?? $this->now());
        $expiresAt = (string)($attributes['expires_at'] ?? date('Y-m-d H:i:s', strtotime($createdAt) + 1800));
        $installSession = [
            'status' => (string)($attributes['status'] ?? 'PENDING'),
            'step' => (string)($attributes['step'] ?? 'PENDING'),
            'message' => (string)($attributes['message'] ?? 'Installer command generated. Run it on the gateway host.'),
            'started_at' => $createdAt,
            'updated_at' => $createdAt,
            'terminal' => false,
            'gateway_id' => $attributes['gateway_id'] ?? null,
            'last_error' => $attributes['last_error'] ?? null,
            'diagnostics' => isset($attributes['diagnostics']) && is_array($attributes['diagnostics'])
                ? (array)$attributes['diagnostics']
                : [],
            'events' => isset($attributes['events']) && is_array($attributes['events'])
                ? (array)$attributes['events']
                : [
                    [
                        'status' => (string)($attributes['status'] ?? 'PENDING'),
                        'step' => (string)($attributes['step'] ?? 'PENDING'),
                        'message' => (string)($attributes['message'] ?? 'Installer command generated. Run it on the gateway host.'),
                        'at' => $createdAt,
                    ],
                ],
        ];

        $claimTokenId = $this->insertRow('edge_gateway_claim_tokens', [
            'department_id' => $departmentId,
            'label' => $attributes['label'] ?? ('Edge Install ' . $this->uniqueSuffix()),
            'token_hash' => hash('sha256', $token),
            'created_by' => $attributes['created_by'] ?? null,
            'expires_at' => $expiresAt,
            'used_at' => $attributes['used_at'] ?? null,
            'metadata_json' => array_merge(
                isset($attributes['metadata']) && is_array($attributes['metadata']) ? (array)$attributes['metadata'] : [],
                ['install_session' => $installSession]
            ),
            'created_at' => $createdAt,
            'updated_at' => $attributes['updated_at'] ?? $createdAt,
            'deleted_at' => $attributes['deleted_at'] ?? null,
        ]);

        $this->cleanup->add(fn() => $this->deleteById('edge_gateway_claim_tokens', $claimTokenId));

        return [
            'claim_token_id' => $claimTokenId,
            'department_id' => $departmentId,
            'label' => $attributes['label'] ?? null,
            'token' => $token,
            'expires_at' => $expiresAt,
        ];
    }

    /**
     * @param array<string, mixed> $attributes
     * @return array<string, mixed>
     */
    public function createClaimedEdgeGateway(array $attributes): array
    {
        if (class_exists(\classes\edge_gateway_schema_bootstrap::class)) {
            \classes\edge_gateway_schema_bootstrap::ensureTables();
        }

        $departmentId = (int)($attributes['department_id'] ?? 0);
        if ($departmentId <= 0) {
            throw new RuntimeException('Claimed edge gateways require department_id.');
        }

        $agentToken = (string)($attributes['agent_token'] ?? (bin2hex(random_bytes(24)) . $this->uniqueSuffix()));
        $createdAt = (string)($attributes['created_at'] ?? $this->now());
        $metadata = array_merge([
            'credentials_rotated_at' => $createdAt,
            'agent_runtime' => 'compose-php',
            'runtime_mode' => 'compose',
            'update_window' => '02:00-04:00',
            'container_health' => [
                'overall_status' => 'PENDING',
                'services' => [],
            ],
            'outbox_status' => [
                'depth' => 0,
                'oldest_age_seconds' => 0,
                'last_flushed_at' => null,
                'pending_types' => [],
            ],
            'rollback_status' => [
                'state' => 'NONE',
                'reason' => null,
                'at' => null,
            ],
            'last_sync_at' => null,
        ], isset($attributes['metadata']) && is_array($attributes['metadata']) ? (array)$attributes['metadata'] : []);

        $gatewayId = $this->insertRow('edge_gateways', [
            'department_id' => $departmentId,
            'label' => (string)($attributes['label'] ?? ('Edge Gateway ' . $this->uniqueSuffix())),
            'hostname' => $attributes['hostname'] ?? ('edge-' . $this->uniqueSuffix()),
            'agent_token_hash' => hash('sha256', $agentToken),
            'status' => (string)($attributes['status'] ?? 'ONLINE'),
            'transport_mode' => (string)($attributes['transport_mode'] ?? 'gateway'),
            'release_channel' => (string)($attributes['release_channel'] ?? 'stable'),
            'installed_version' => $attributes['installed_version'] ?? 'php-agent-v1',
            'target_version' => $attributes['target_version'] ?? ($attributes['installed_version'] ?? 'php-agent-v1'),
            'last_heartbeat_at' => $attributes['last_heartbeat_at'] ?? $createdAt,
            'last_seen_ip' => $attributes['last_seen_ip'] ?? '127.0.0.1',
            'discovery_status' => (string)($attributes['discovery_status'] ?? 'PENDING'),
            'is_primary' => $attributes['is_primary'] ?? 1,
            'metadata_json' => $metadata,
            'created_at' => $createdAt,
            'updated_at' => $attributes['updated_at'] ?? $createdAt,
            'deleted_at' => $attributes['deleted_at'] ?? null,
        ]);

        $this->cleanup->add(function () use ($gatewayId): void {
            $this->deleteWhere('edge_gateway_operation_events', ['gateway_id' => $gatewayId]);
            $this->deleteWhere('edge_gateway_operations', ['gateway_id' => $gatewayId]);
            $this->deleteWhere('edge_gateway_command_jobs', ['gateway_id' => $gatewayId]);
            $this->deleteWhere('edge_gateway_expected_relay_states', ['gateway_id' => $gatewayId]);
            $this->deleteWhere('edge_gateway_device_inventory', ['gateway_id' => $gatewayId]);
            $this->deleteWhere('edge_gateway_relay_bindings', ['gateway_id' => $gatewayId]);
            $this->deleteWhere('edge_gateway_log_entries', ['gateway_id' => $gatewayId]);
            $this->deleteWhere('edge_gateway_shell_sessions', ['gateway_id' => $gatewayId]);
            $this->deleteWhere('edge_gateway_audit_logs', ['gateway_id' => $gatewayId]);
            $this->deleteById('edge_gateways', $gatewayId);
            $this->clearEdgeGatewayViewCache();
        });

        return [
            'id' => $gatewayId,
            'department_id' => $departmentId,
            'label' => (string)($attributes['label'] ?? ''),
            'agent_token' => $agentToken,
        ];
    }

    /**
     * @param array<string, mixed> $attributes
     * @return array<string, mixed>
     */
    public function createEdgeRelayBinding(array $attributes): array
    {
        if (class_exists(\classes\edge_gateway_schema_bootstrap::class)) {
            \classes\edge_gateway_schema_bootstrap::ensureTables();
        }

        $gatewayId = (int)($attributes['gateway_id'] ?? 0);
        $departmentId = (int)($attributes['department_id'] ?? 0);
        $relayId = trim((string)($attributes['relay_id'] ?? ''));
        if ($gatewayId <= 0 || $departmentId <= 0 || $relayId === '') {
            throw new RuntimeException('Edge relay bindings require gateway_id, department_id, and relay_id.');
        }

        $bindingId = $this->insertRow('edge_gateway_relay_bindings', [
            'gateway_id' => $gatewayId,
            'department_id' => $departmentId,
            'relay_id' => $relayId,
            'device_id' => (string)($attributes['device_id'] ?? $relayId),
            'local_ip' => $attributes['local_ip'] ?? '10.31.0.' . max(2, $gatewayId % 250),
            'channel' => $attributes['channel'] ?? 0,
            'fallback_mode' => (string)($attributes['fallback_mode'] ?? 'LOCAL_ONLY'),
            'binding_source' => (string)($attributes['binding_source'] ?? 'TEST'),
            'approved_by' => $attributes['approved_by'] ?? null,
            'approved_at' => $attributes['approved_at'] ?? $this->now(),
            'metadata_json' => isset($attributes['metadata']) && is_array($attributes['metadata'])
                ? (array)$attributes['metadata']
                : ['device_type' => 'SHELLY_SWITCH'],
            'created_at' => $attributes['created_at'] ?? $this->now(),
            'updated_at' => $attributes['updated_at'] ?? $this->now(),
            'deleted_at' => $attributes['deleted_at'] ?? null,
        ]);

        $this->cleanup->add(fn() => $this->deleteById('edge_gateway_relay_bindings', $bindingId));

        return [
            'id' => $bindingId,
            'gateway_id' => $gatewayId,
            'department_id' => $departmentId,
            'relay_id' => $relayId,
        ];
    }

    /**
     * @param array<string, mixed> $attributes
     * @return array<string, mixed>
     */
    public function createEdgeCommandJob(array $attributes): array
    {
        $gatewayId = (int)($attributes['gateway_id'] ?? 0);
        if ($gatewayId <= 0) {
            throw new RuntimeException('Edge command jobs require gateway_id.');
        }

        $jobId = $this->insertRow('edge_gateway_command_jobs', [
            'gateway_id' => $gatewayId,
            'command_type' => (string)($attributes['command_type'] ?? 'DISCOVER_SHELLY'),
            'status' => (string)($attributes['status'] ?? 'PENDING'),
            'request_json' => isset($attributes['request']) && is_array($attributes['request']) ? (array)$attributes['request'] : [],
            'response_json' => isset($attributes['response']) && is_array($attributes['response']) ? (array)$attributes['response'] : [],
            'delivery_json' => isset($attributes['delivery']) && is_array($attributes['delivery']) ? (array)$attributes['delivery'] : [],
            'correlation_id' => (string)($attributes['correlation_id'] ?? ('edge-command-' . $this->uniqueSuffix())),
            'requested_by' => $attributes['requested_by'] ?? null,
            'requested_at' => $attributes['requested_at'] ?? $this->now(),
            'completed_at' => $attributes['completed_at'] ?? null,
            'error_message' => $attributes['error_message'] ?? null,
            'created_at' => $attributes['created_at'] ?? $this->now(),
            'updated_at' => $attributes['updated_at'] ?? $this->now(),
            'deleted_at' => $attributes['deleted_at'] ?? null,
        ]);

        $this->cleanup->add(fn() => $this->deleteById('edge_gateway_command_jobs', $jobId));

        return [
            'id' => $jobId,
            'gateway_id' => $gatewayId,
        ];
    }

    /**
     * @param array<string, mixed> $attributes
     * @return array<string, mixed>
     */
    public function createEdgeOperation(array $attributes): array
    {
        $gatewayId = (int)($attributes['gateway_id'] ?? 0);
        if ($gatewayId <= 0) {
            throw new RuntimeException('Edge operations require gateway_id.');
        }

        $operationId = $this->insertRow('edge_gateway_operations', [
            'gateway_id' => $gatewayId,
            'type' => (string)($attributes['type'] ?? 'DISCOVERY'),
            'operation_type' => (string)($attributes['operation_type'] ?? ($attributes['type'] ?? 'DISCOVERY')),
            'status' => (string)($attributes['status'] ?? 'PENDING'),
            'request_json' => isset($attributes['request']) && is_array($attributes['request']) ? (array)$attributes['request'] : [],
            'summary_json' => isset($attributes['summary']) && is_array($attributes['summary']) ? (array)$attributes['summary'] : [
                'label' => 'Queued',
                'progress' => 0,
                'retryable' => true,
            ],
            'result_json' => isset($attributes['result']) && is_array($attributes['result']) ? (array)$attributes['result'] : [],
            'error_code' => $attributes['error_code'] ?? null,
            'error_message' => $attributes['error_message'] ?? null,
            'correlation_id' => (string)($attributes['correlation_id'] ?? ('edge-operation-' . $this->uniqueSuffix())),
            'agent_instance_id' => $attributes['agent_instance_id'] ?? null,
            'lease_expires_at' => $attributes['lease_expires_at'] ?? null,
            'last_progress_at' => $attributes['last_progress_at'] ?? null,
            'attempt_count' => $attributes['attempt_count'] ?? 0,
            'requested_by' => $attributes['requested_by'] ?? null,
            'requested_at' => $attributes['requested_at'] ?? $this->now(),
            'started_at' => $attributes['started_at'] ?? null,
            'completed_at' => $attributes['completed_at'] ?? null,
            'created_at' => $attributes['created_at'] ?? $this->now(),
            'updated_at' => $attributes['updated_at'] ?? $this->now(),
            'deleted_at' => $attributes['deleted_at'] ?? null,
        ]);

        $this->cleanup->add(function () use ($gatewayId, $operationId): void {
            $this->deleteWhere('edge_gateway_operation_events', [
                'gateway_id' => $gatewayId,
                'operation_id' => $operationId,
            ]);
            $this->deleteById('edge_gateway_operations', $operationId);
        });

        return [
            'id' => $operationId,
            'gateway_id' => $gatewayId,
        ];
    }

    /**
     * @param array<string, mixed> $attributes
     * @return array<string, mixed>
     */
    public function createEdgeOperationEvent(array $attributes): array
    {
        $gatewayId = (int)($attributes['gateway_id'] ?? 0);
        $operationId = (int)($attributes['operation_id'] ?? 0);
        if ($gatewayId <= 0 || $operationId <= 0) {
            throw new RuntimeException('Edge operation events require gateway_id and operation_id.');
        }

        $eventId = $this->insertRow('edge_gateway_operation_events', [
            'gateway_id' => $gatewayId,
            'operation_id' => $operationId,
            'stage' => (string)($attributes['stage'] ?? 'RECORDED'),
            'level' => (string)($attributes['level'] ?? 'INFO'),
            'code' => $attributes['code'] ?? null,
            'message' => (string)($attributes['message'] ?? 'Edge operation event'),
            'context_json' => isset($attributes['context']) && is_array($attributes['context']) ? (array)$attributes['context'] : [],
            'counts_json' => isset($attributes['counts']) && is_array($attributes['counts']) ? (array)$attributes['counts'] : [],
            'payload_json' => isset($attributes['payload']) && is_array($attributes['payload']) ? (array)$attributes['payload'] : [],
            'created_at' => $attributes['created_at'] ?? $this->now(),
            'deleted_at' => $attributes['deleted_at'] ?? null,
        ]);

        $this->cleanup->add(fn() => $this->deleteById('edge_gateway_operation_events', $eventId));

        return [
            'id' => $eventId,
            'gateway_id' => $gatewayId,
            'operation_id' => $operationId,
        ];
    }

    /**
     * @param array<string, mixed> $attributes
     * @return array<string, mixed>
     */
    public function createEdgeAuditLog(array $attributes): array
    {
        $gatewayId = (int)($attributes['gateway_id'] ?? 0);
        $departmentId = (int)($attributes['department_id'] ?? 0);
        if ($gatewayId <= 0 || $departmentId <= 0) {
            throw new RuntimeException('Edge audit logs require gateway_id and department_id.');
        }

        $auditLogId = $this->insertRow('edge_gateway_audit_logs', [
            'gateway_id' => $gatewayId,
            'department_id' => $departmentId,
            'action' => (string)($attributes['action'] ?? 'EDGE_AUDIT'),
            'actor_user_id' => $attributes['actor_user_id'] ?? null,
            'actor_type' => (string)($attributes['actor_type'] ?? 'USER'),
            'severity' => (string)($attributes['severity'] ?? 'INFO'),
            'context_json' => isset($attributes['context']) && is_array($attributes['context']) ? (array)$attributes['context'] : [],
            'created_at' => $attributes['created_at'] ?? $this->now(),
            'updated_at' => $attributes['updated_at'] ?? $this->now(),
        ]);

        $this->cleanup->add(fn() => $this->deleteById('edge_gateway_audit_logs', $auditLogId));

        return [
            'id' => $auditLogId,
            'gateway_id' => $gatewayId,
            'department_id' => $departmentId,
        ];
    }

    /**
     * @param array<string, mixed> $attributes
     * @return array<string, mixed>
     */
    public function createEdgeLogEntry(array $attributes): array
    {
        $gatewayId = (int)($attributes['gateway_id'] ?? 0);
        if ($gatewayId <= 0) {
            throw new RuntimeException('Edge log entries require gateway_id.');
        }

        $gatewayRow = $this->fetchRowById('edge_gateways', $gatewayId);
        $departmentId = (int)($attributes['department_id'] ?? ($gatewayRow['department_id'] ?? 0));

        $logEntryId = $this->insertRow('edge_gateway_log_entries', [
            'gateway_id' => $gatewayId,
            'department_id' => $departmentId > 0 ? $departmentId : null,
            'level' => (string)($attributes['level'] ?? 'INFO'),
            'stream' => (string)($attributes['stream'] ?? 'agent'),
            'source' => (string)($attributes['source'] ?? 'BROKER'),
            'message' => (string)($attributes['message'] ?? 'Edge gateway log entry'),
            'context_json' => isset($attributes['context']) && is_array($attributes['context']) ? (array)$attributes['context'] : [],
            'created_at' => $attributes['created_at'] ?? $this->now(),
            'updated_at' => $attributes['updated_at'] ?? $this->now(),
        ]);

        $this->cleanup->add(fn() => $this->deleteById('edge_gateway_log_entries', $logEntryId));

        return [
            'id' => $logEntryId,
            'gateway_id' => $gatewayId,
        ];
    }

    /**
     * @param array<string, mixed> $attributes
     * @return array<string, mixed>
     */
    public function createEdgeShellSession(array $attributes): array
    {
        $gatewayId = (int)($attributes['gateway_id'] ?? 0);
        if ($gatewayId <= 0) {
            throw new RuntimeException('Edge shell sessions require gateway_id.');
        }

        $gatewayRow = $this->fetchRowById('edge_gateways', $gatewayId);
        $departmentId = (int)($attributes['department_id'] ?? ($gatewayRow['department_id'] ?? 0));
        if ($departmentId <= 0) {
            throw new RuntimeException('Edge shell sessions require department_id or a valid gateway row.');
        }

        $sessionToken = (string)($attributes['token'] ?? bin2hex(random_bytes(24)));
        $createdAt = (string)($attributes['created_at'] ?? $this->now());
        $expiresAt = (string)($attributes['expires_at'] ?? date('Y-m-d H:i:s', strtotime($createdAt) + 900));

        $sessionId = $this->insertRow('edge_gateway_shell_sessions', [
            'gateway_id' => $gatewayId,
            'department_id' => $departmentId,
            'actor_user_id' => $attributes['actor_user_id'] ?? null,
            'session_token_hash' => hash('sha256', $sessionToken),
            'status' => (string)($attributes['status'] ?? 'PENDING'),
            'reason' => (string)($attributes['reason'] ?? 'Diagnostic shell session'),
            'connection_id' => $attributes['connection_id'] ?? null,
            'cwd' => $attributes['cwd'] ?? '/opt/truckwash-edge-agent',
            'shell_command' => $attributes['shell_command'] ?? null,
            'shell_args_json' => isset($attributes['shell_args']) && is_array($attributes['shell_args']) ? (array)$attributes['shell_args'] : [],
            'cols' => $attributes['cols'] ?? 120,
            'terminal_rows' => $attributes['rows'] ?? 32,
            'transcript' => $attributes['transcript'] ?? null,
            'metadata_json' => isset($attributes['metadata']) && is_array($attributes['metadata']) ? (array)$attributes['metadata'] : [],
            'expires_at' => $expiresAt,
            'approved_at' => $attributes['approved_at'] ?? $createdAt,
            'opened_at' => $attributes['opened_at'] ?? null,
            'closed_at' => $attributes['closed_at'] ?? null,
            'created_at' => $createdAt,
            'updated_at' => $attributes['updated_at'] ?? $createdAt,
            'deleted_at' => $attributes['deleted_at'] ?? null,
        ]);

        $this->cleanup->add(fn() => $this->deleteById('edge_gateway_shell_sessions', $sessionId));

        return [
            'id' => $sessionId,
            'gateway_id' => $gatewayId,
            'department_id' => $departmentId,
            'token' => $sessionToken,
            'expires_at' => $expiresAt,
        ];
    }

    public function cleanupDeleteById(string $table, int $id): void
    {
        $this->cleanup->add(fn() => $this->deleteById($table, $id));
    }

    /**
     * @param array<string, mixed> $conditions
     */
    public function cleanupDeleteWhere(string $table, array $conditions): void
    {
        $this->cleanup->add(fn() => $this->deleteWhere($table, $conditions));
    }

    public function cacheEconomicCustomerDiscountPercentage(int $userId, int $discountPercentage): void
    {
        if ($this->redis === null) {
            throw new RuntimeException('API tests require Redis for cache-backed endpoint flows.');
        }

        $key = 'users_' . $userId . '_economic_customer_discount_percentage';
        $this->redis->set($key, (string)$discountPercentage);
        $this->cleanup->add(fn() => $this->deleteRedisKey($key));
    }

    private function purgeCustomerTraceData(int $userId, int $customerNumber): void
    {
        $invoiceCollectionIds = $this->fetchIntColumnWhere('collected_order_invoices', 'id', [
            'customer_number' => $customerNumber,
        ]);
        $orderIds = $this->fetchIntColumnWhere('orders', 'id', [
            'customer_id' => $customerNumber,
        ]);
        $vehicleIds = $this->fetchIntColumnWhere('customer_vehicles', 'id', [
            'customer_id' => $customerNumber,
        ]);

        if ($orderIds !== []) {
            $this->deleteWhereInIfPossible('order_items', 'order_id', $orderIds);
            $this->deleteWhereInIfPossible('economic_module_orders', 'id', $orderIds);
            $this->deleteWhereInIfPossible('stripe_module_orders', 'id', $orderIds);
            $this->deleteWhereInIfPossible('stripe_payment_intents', 'order_id', $orderIds);
            $this->deleteWhereInIfPossible('object_attachments', 'object_id', $orderIds, [
                'object_type' => 'orders',
            ]);
        }

        if ($vehicleIds !== []) {
            $this->deleteWhereInIfPossible('customer_vehicles_addons', 'vehicle_id', $vehicleIds);
            $this->deleteWhereInIfPossible('object_attachments', 'object_id', $vehicleIds, [
                'object_type' => 'customer_vehicles',
            ]);
        }

        if ($invoiceCollectionIds !== []) {
            $this->deleteWhereInIfPossible('object_attachments', 'object_id', $invoiceCollectionIds, [
                'object_type' => 'collected_order_invoices',
            ]);
        }

        $this->deleteWhereIfPossible('customer_attributes', ['user_id' => $userId]);
        $this->deleteWhereIfPossible('tokens', ['user_id' => $userId]);
        $this->deleteWhereIfPossible('user_key_value_pairs', ['user_id' => $userId]);
        $this->deleteWhereIfPossible('price_overrides', ['user_id' => $userId]);
        $this->deleteWhereIfPossible('department_customer_price_overrides', ['user_id' => $userId]);
        $this->deleteWhereIfPossible('limited_backoffice_employees', ['user_id' => $userId]);
        $this->deleteWhereIfPossible('customer_default_department', ['customer_number' => $customerNumber]);
        $this->deleteWhereIfPossible('customer_fixed_pricing', ['customer_number' => $customerNumber]);
        $this->deleteWhereIfPossible('customer_fixed_pricing_versions', ['customer_number' => $customerNumber]);
        $this->deleteWhereIfPossible('customer_vehicle_subscription_versions', ['customer_number' => $customerNumber]);
        $this->deleteWhereIfPossible('customer_discount_override_versions', ['customer_number' => $customerNumber]);
        $this->deleteWhereIfPossible('customer_discount_override_versions', ['user_id' => $userId]);
        $this->deleteWhereIfPossible('subuser_grants', ['billing_customer_number' => $customerNumber]);
        $this->deleteWhereIfPossible('system_search_economic_customer_index', ['customer_number' => $customerNumber]);
        $this->deleteWhereIfPossible('system_search_economic_customer_index', ['user_id' => $userId]);
        $this->deleteWhereIfPossible('object_attachments', [
            'object_type' => 'users',
            'object_id' => $userId,
        ]);
        $this->deleteWhereIfPossible('bookings', ['customer_number' => $customerNumber]);
        $this->deleteWhereIfPossible('orders', ['customer_id' => $customerNumber]);
        $this->deleteWhereIfPossible('customer_vehicles', ['customer_id' => $customerNumber]);
        $this->deleteWhereIfPossible('collected_order_invoices', ['customer_number' => $customerNumber]);
        $this->deleteById('users', $userId);
    }

    private function seedCustomerNameCache(int $customerNumber, string $name): void
    {
        $payload = [
            'name' => $name,
        ];

        $this->setRedisJson('users_' . $customerNumber . '_economic_customer_name', $payload);
        $this->setRedisJson('`users`_' . $customerNumber . '_economic_customer_name', $payload);
    }

    private function seedEconomicCustomerCache(int $userId, int $customerNumber, string $name, string $email): void
    {
        $payload = [
            'customerNumber' => $customerNumber,
            'name' => $name,
            'email' => $email,
            'country' => 'DK',
            'currency' => 'DKK',
            'barred' => false,
        ];

        $this->setRedisJson('users_' . $userId . '_economic_customer', $payload);
        $this->setRedisJson('`users`_' . $userId . '_economic_customer', $payload);
    }

    private function seedEconomicCustomerDiscountCache(int $userId, int $discountPercentage): void
    {
        $this->setRedisValue('users_' . $userId . '_economic_customer_discount_percentage', (string)$discountPercentage);
    }

    /**
     * @param array<string, mixed> $data
     */
    private function insertRowWithExistingColumns(string $table, array $data): int
    {
        $filtered = [];
        foreach ($data as $column => $value) {
            if ($this->tableHasColumn($table, (string)$column)) {
                $filtered[(string)$column] = $value;
            }
        }

        return $this->insertRow($table, $filtered);
    }

    /**
     * @param array<string, mixed> $data
     */
    private function insertRow(string $table, array $data): int
    {
        $table = $this->sanitizeIdentifier($table);
        if ($data === []) {
            throw new RuntimeException('Cannot insert an empty row into ' . $table . '.');
        }

        $columns = [];
        $placeholders = [];
        $types = '';
        $values = [];

        foreach ($data as $column => $value) {
            $columns[] = '`' . $this->sanitizeIdentifier((string)$column) . '`';
            $placeholders[] = '?';
            [$type, $normalizedValue] = $this->normalizeValue($value);
            $types .= $type;
            $values[] = $normalizedValue;
        }

        $sql = sprintf(
            'INSERT INTO `%s` (%s) VALUES (%s)',
            $table,
            implode(', ', $columns),
            implode(', ', $placeholders),
        );

        $statement = $this->db->prepare($sql);
        if ($statement === false) {
            throw new RuntimeException('Failed to prepare insert for ' . $table . '.');
        }

        $statement->bind_param($types, ...$values);
        $statement->execute();
        $statement->close();

        return (int)$this->db->insert_id;
    }

    /**
     * @param array<string, mixed> $conditions
     */
    private function deleteWhere(string $table, array $conditions): void
    {
        if ($conditions === []) {
            return;
        }

        $table = $this->sanitizeIdentifier($table);
        $parts = [];
        $types = '';
        $values = [];

        foreach ($conditions as $column => $value) {
            $column = $this->sanitizeIdentifier((string)$column);
            if ($value === null) {
                $parts[] = '`' . $column . '` IS NULL';
                continue;
            }

            $parts[] = '`' . $column . '` = ?';
            [$type, $normalizedValue] = $this->normalizeValue($value);
            $types .= $type;
            $values[] = $normalizedValue;
        }

        $sql = 'DELETE FROM `' . $table . '` WHERE ' . implode(' AND ', $parts);
        $statement = $this->db->prepare($sql);
        if ($statement === false) {
            throw new RuntimeException('Failed to prepare delete for ' . $table . '.');
        }

        if ($values !== []) {
            $statement->bind_param($types, ...$values);
        }
        $statement->execute();
        $statement->close();
    }

    /**
     * @param array<string, mixed> $conditions
     */
    private function deleteWhereIfPossible(string $table, array $conditions): void
    {
        if ($conditions === [] || !$this->tableExists($table)) {
            return;
        }

        foreach (array_keys($conditions) as $column) {
            if (!$this->tableHasColumn($table, (string)$column)) {
                return;
            }
        }

        $this->deleteWhere($table, $conditions);
    }

    /**
     * @param array<string, mixed> $conditions
     * @return array<int, int>
     */
    private function fetchIntColumnWhere(string $table, string $column, array $conditions): array
    {
        if (!$this->tableExists($table) || !$this->tableHasColumn($table, $column)) {
            return [];
        }

        foreach (array_keys($conditions) as $conditionColumn) {
            if (!$this->tableHasColumn($table, (string)$conditionColumn)) {
                return [];
            }
        }

        $table = $this->sanitizeIdentifier($table);
        $column = $this->sanitizeIdentifier($column);
        $parts = [];
        $types = '';
        $values = [];

        foreach ($conditions as $conditionColumn => $value) {
            $conditionColumn = $this->sanitizeIdentifier((string)$conditionColumn);
            if ($value === null) {
                $parts[] = '`' . $conditionColumn . '` IS NULL';
                continue;
            }

            $parts[] = '`' . $conditionColumn . '` = ?';
            [$type, $normalizedValue] = $this->normalizeValue($value);
            $types .= $type;
            $values[] = $normalizedValue;
        }

        $sql = 'SELECT `' . $column . '` FROM `' . $table . '`';
        if ($parts !== []) {
            $sql .= ' WHERE ' . implode(' AND ', $parts);
        }

        $statement = $this->db->prepare($sql);
        if ($statement === false) {
            throw new RuntimeException('Failed to prepare select for ' . $table . '.');
        }

        if ($values !== []) {
            $statement->bind_param($types, ...$values);
        }

        $statement->execute();
        $statement->bind_result($selectedValue);

        $selected = [];
        while ($statement->fetch()) {
            $selectedValue = (int)$selectedValue;
            if ($selectedValue > 0) {
                $selected[] = $selectedValue;
            }
        }

        $statement->close();

        return array_values(array_unique($selected));
    }

    /**
     * @param array<int, int> $values
     * @param array<string, mixed> $conditions
     */
    private function deleteWhereInIfPossible(string $table, string $column, array $values, array $conditions = []): void
    {
        $values = array_values(array_unique(array_filter(
            array_map('intval', $values),
            static fn(int $value): bool => $value > 0
        )));
        if ($values === [] || !$this->tableExists($table) || !$this->tableHasColumn($table, $column)) {
            return;
        }

        foreach (array_keys($conditions) as $conditionColumn) {
            if (!$this->tableHasColumn($table, (string)$conditionColumn)) {
                return;
            }
        }

        $table = $this->sanitizeIdentifier($table);
        $column = $this->sanitizeIdentifier($column);
        $parts = ['`' . $column . '` IN (' . implode(', ', array_fill(0, count($values), '?')) . ')'];
        $types = str_repeat('i', count($values));
        $boundValues = $values;

        foreach ($conditions as $conditionColumn => $value) {
            $conditionColumn = $this->sanitizeIdentifier((string)$conditionColumn);
            if ($value === null) {
                $parts[] = '`' . $conditionColumn . '` IS NULL';
                continue;
            }

            $parts[] = '`' . $conditionColumn . '` = ?';
            [$type, $normalizedValue] = $this->normalizeValue($value);
            $types .= $type;
            $boundValues[] = $normalizedValue;
        }

        $sql = 'DELETE FROM `' . $table . '` WHERE ' . implode(' AND ', $parts);
        $statement = $this->db->prepare($sql);
        if ($statement === false) {
            throw new RuntimeException('Failed to prepare delete for ' . $table . '.');
        }

        $statement->bind_param($types, ...$boundValues);
        $statement->execute();
        $statement->close();
    }

    private function deleteById(string $table, int $id): void
    {
        $table = $this->sanitizeIdentifier($table);
        if ($id <= 0) {
            return;
        }

        $this->db->query('DELETE FROM `' . $table . '` WHERE id = ' . $id . ' LIMIT 1');
    }

    /**
     * @param array<string, mixed> $data
     */
    private function updateById(string $table, int $id, array $data): void
    {
        $table = $this->sanitizeIdentifier($table);
        if ($id <= 0 || $data === []) {
            return;
        }

        $parts = [];
        $types = '';
        $values = [];
        foreach ($data as $column => $value) {
            $column = $this->sanitizeIdentifier((string)$column);
            if ($value === null) {
                $parts[] = '`' . $column . '` = NULL';
                continue;
            }

            $parts[] = '`' . $column . '` = ?';
            [$type, $normalizedValue] = $this->normalizeValue($value);
            $types .= $type;
            $values[] = $normalizedValue;
        }

        $sql = 'UPDATE `' . $table . '` SET ' . implode(', ', $parts) . ' WHERE id = ? LIMIT 1';
        $types .= 'i';
        $values[] = $id;

        $statement = $this->db->prepare($sql);
        if ($statement === false) {
            throw new RuntimeException('Failed to prepare update for ' . $table . '.');
        }

        $statement->bind_param($types, ...$values);
        $statement->execute();
        $statement->close();
    }

    /**
     * @param array<string, mixed> $conditions
     * @param array<string, mixed> $data
     */
    private function updateWhere(string $table, array $conditions, array $data): void
    {
        $table = $this->sanitizeIdentifier($table);
        if ($conditions === [] || $data === []) {
            return;
        }

        $setParts = [];
        $whereParts = [];
        $types = '';
        $values = [];

        foreach ($data as $column => $value) {
            $column = $this->sanitizeIdentifier((string)$column);
            if ($value === null) {
                $setParts[] = '`' . $column . '` = NULL';
                continue;
            }

            $setParts[] = '`' . $column . '` = ?';
            [$type, $normalizedValue] = $this->normalizeValue($value);
            $types .= $type;
            $values[] = $normalizedValue;
        }

        foreach ($conditions as $column => $value) {
            $column = $this->sanitizeIdentifier((string)$column);
            if ($value === null) {
                $whereParts[] = '`' . $column . '` IS NULL';
                continue;
            }

            $whereParts[] = '`' . $column . '` = ?';
            [$type, $normalizedValue] = $this->normalizeValue($value);
            $types .= $type;
            $values[] = $normalizedValue;
        }

        $sql = 'UPDATE `' . $table . '` SET ' . implode(', ', $setParts) . ' WHERE ' . implode(' AND ', $whereParts);
        $statement = $this->db->prepare($sql);
        if ($statement === false) {
            throw new RuntimeException('Failed to prepare update for ' . $table . '.');
        }

        $statement->bind_param($types, ...$values);
        $statement->execute();
        $statement->close();
    }

    private function queryOneBySql(string $sql): ?array
    {
        $result = $this->db->query($sql);
        if ($result === false) {
            throw new RuntimeException('Query failed: ' . $sql);
        }

        $row = $result->fetch_assoc();
        $result->free();

        return $row ?: null;
    }

    private function fetchModuleConfig(string $module, string $variable): ?array
    {
        $moduleEscaped = $this->db->real_escape_string($module);
        $variableEscaped = $this->db->real_escape_string($variable);

        return $this->queryOneBySql(
            "SELECT * FROM module_config WHERE module = '{$moduleEscaped}' AND variable = '{$variableEscaped}' LIMIT 1"
        );
    }

    private function setRedisJson(string $key, array $payload): void
    {
        if ($this->redis === null) {
            throw new RuntimeException('API tests require Redis for cache-backed endpoint flows.');
        }

        $encoded = json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        if (!is_string($encoded)) {
            throw new RuntimeException('Unable to encode Redis payload for API tests.');
        }

        $this->redis->set($key, $encoded);
        $this->cleanup->add(fn() => $this->deleteRedisKey($key));
    }

    private function setRedisValue(string $key, string $value): void
    {
        if ($this->redis === null) {
            throw new RuntimeException('API tests require Redis for cache-backed endpoint flows.');
        }

        $this->redis->set($key, $value);
        $this->cleanup->add(fn() => $this->deleteRedisKey($key));
    }

    private function deleteRedisKey(string $key): void
    {
        if ($this->redis === null) {
            return;
        }

        $this->redis->del([$key]);
    }

    private function deleteRedisPattern(string $pattern): void
    {
        if ($this->redis === null) {
            return;
        }

        $keys = $this->redis->keys($pattern);
        if ($keys === []) {
            return;
        }

        $this->redis->del($keys);
    }

    /**
     * @return array{0:string,1:mixed}
     */
    private function normalizeValue(mixed $value): array
    {
        if (is_bool($value)) {
            return ['i', $value ? 1 : 0];
        }

        if (is_int($value)) {
            return ['i', $value];
        }

        if (is_float($value)) {
            return ['d', $value];
        }

        if (is_array($value)) {
            $encoded = json_encode($value, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
            if (!is_string($encoded)) {
                throw new RuntimeException('Unable to encode array value for API fixture data.');
            }

            return ['s', $encoded];
        }

        if ($value === null) {
            return ['s', null];
        }

        return ['s', (string)$value];
    }

    private function sanitizeIdentifier(string $identifier): string
    {
        if (!preg_match('/^[A-Za-z0-9_]+$/', $identifier)) {
            throw new RuntimeException('Invalid SQL identifier: ' . $identifier);
        }

        return $identifier;
    }

    private function now(): string
    {
        return date('Y-m-d H:i:s');
    }

    private function tableExists(string $table): bool
    {
        $table = $this->sanitizeIdentifier($table);
        if (array_key_exists($table, $this->tableExistsCache)) {
            return $this->tableExistsCache[$table];
        }

        $escapedTable = $this->db->real_escape_string($table);
        $result = $this->db->query(
            "SELECT 1
             FROM information_schema.TABLES
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = '{$escapedTable}'
             LIMIT 1"
        );
        if ($result === false) {
            return $this->tableExistsCache[$table] = false;
        }

        $exists = $result->fetch_assoc() !== null;
        $result->free();

        return $this->tableExistsCache[$table] = $exists;
    }

    private function tableHasColumn(string $table, string $column): bool
    {
        $table = $this->sanitizeIdentifier($table);
        $column = $this->sanitizeIdentifier($column);
        $cacheKey = $table . ':' . $column;

        if (array_key_exists($cacheKey, $this->tableColumnExistsCache)) {
            return $this->tableColumnExistsCache[$cacheKey];
        }

        if (!$this->tableExists($table)) {
            return $this->tableColumnExistsCache[$cacheKey] = false;
        }

        $escapedTable = $this->db->real_escape_string($table);
        $escapedColumn = $this->db->real_escape_string($column);
        $result = $this->db->query(
            "SELECT 1
             FROM information_schema.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = '{$escapedTable}'
               AND COLUMN_NAME = '{$escapedColumn}'
             LIMIT 1"
        );
        if ($result === false) {
            return $this->tableColumnExistsCache[$cacheKey] = false;
        }

        $exists = $result->fetch_assoc() !== null;
        $result->free();

        return $this->tableColumnExistsCache[$cacheKey] = $exists;
    }

    private function uniqueCustomerNumber(): int
    {
        return 80000000 + (++self::$sequence);
    }

    private function uniqueSuffix(): string
    {
        return strtoupper(dechex(time()) . dechex(getmypid()) . dechex(++self::$sequence));
    }
}
