<?php

namespace classes;

use mysqli;
use objects\logs_o;
use objects\products_o;
use objects\users_o;

class limited_backoffice_service
{
    public const PERMISSION_ACCESS = 'limited_backoffice_access';
    public const PERMISSION_MANAGE_PRICES = 'limited_backoffice_prices_manage';
    public const PERMISSION_VIEW_CUSTOMER_PRICING = 'limited_backoffice_customer_pricing_view';
    public const PERMISSION_MANAGE_CUSTOMER_PRICING = 'limited_backoffice_customer_pricing_manage';
    public const PERMISSION_MANAGE_EMPLOYEES = 'limited_backoffice_employees_manage';

    private const PERMISSION_PUBLIC_EMPLOYEE_DATA = 'employee_public_data';
    private const MANAGED_EMPLOYEE_CUSTOMER_NUMBER = 0;

    /**
     * Permissions required for managed employees to sign in, appear in the employee login picker,
     * and open the department admin shell used by their scoped role permissions.
     *
     * @var array<int, string>
     */
    private const MANAGED_EMPLOYEE_BASE_PERMISSIONS = [
        'admin',
        'user',
        'permissions_list_own',
        self::PERMISSION_PUBLIC_EMPLOYEE_DATA,
    ];

    /**
     * Permissions that are always granted to managed employees when present in a role preset,
     * regardless of whether the creating manager holds those permissions themselves.
     *
     * @var array<int, string>
     */
    private const ROLE_UNCONDITIONAL_PERMISSIONS = [
        'list_departments',
        'list_department_daily_reports',
        'list_notifications',
        'list_own_notifications',
        'statistics_orders_new',
        'statistics_bookings_new',
    ];

    /**
     * @var array<string, array{label:string,description:string,permissions:array<int,string>}>
     */
    private const ROLE_PRESETS = [
        'viewer' => [
            'label' => 'Deactivated',
            'description' => 'Keeps the employee registered without order, booking, or management permissions.',
            'permissions' => [
                'user',
                'permissions_list_own',
            ],
        ],
        'cashier' => [
            'label' => 'Cashier',
            'description' => 'Can work with POS orders, products, customers, vehicles, attachments, payments, scanners, and bookings for assigned departments.',
            'permissions' => [
                'user',
                'permissions_list_own',
                'list_departments',
                'list_orders',
                'fetch_order',
                'add_order',
                'edit_order',
                'mark_order_as_completed',
                'list_order_items',
                'add_order_items',
                'edit_order_items',
                'delete_order_items',
                'list_order_attachments',
                'add_order_attachments',
                'download_order_attachments',
                'list_products',
                'list_categories',
                'list_department_categories',
                'list_department_order_recommended',
                'vehicle_product_suggestions',
                'search_customers',
                'get_user_from_customer_number',
                'list_customer_notes',
                'add_customer_note',
                'list_customer_attributes',
                'search_vehicles',
                'view_vehicle_status',
                'list_unknown_customer_vehicles',
                'list_vehicle_customer_suggestions',
                'department_license_plate_lookup',
                'department_vehicle_order_last_five',
                'list_number_plate_scans',
                'list_department_number_plate_scanners',
                'charge_order',
                'get_payment_intent',
                'confirm_payment_intent',
                'modules_stripe_department_terminal_readers_list',
                'modules_stripe_invoice_send',
                'list_bookings',
                'list_own_bookings',
                'edit_bookings',
                'add_booking',
                'add_bookings',
                'complete_bookings',
                'resend_booking_confirmations',
                'list_department_daily_reports',
                'list_notifications',
                'list_own_notifications',
                'statistics_orders_new',
                'statistics_bookings_new',
            ],
        ],
        'booking_coordinator' => [
            'label' => 'Booking coordinator',
            'description' => 'Can coordinate bookings for assigned departments.',
            'permissions' => [
                'user',
                'permissions_list_own',
                'list_departments',
                'list_orders',
                'list_bookings',
                'list_own_bookings',
                'edit_bookings',
                'add_booking',
                'add_bookings',
                'complete_bookings',
                'resend_booking_confirmations',
                'department_timebookings_entries_get',
                'department_timebookings_entries_post',
                'department_timebookings_entries_put',
                'list_department_daily_reports',
                'list_notifications',
                'list_own_notifications',
                'statistics_orders_new',
                'statistics_bookings_new',
            ],
        ],
        'operations_lead' => [
            'label' => 'Operations lead',
            'description' => 'Can coordinate orders, bookings, and operations views for assigned departments.',
            'permissions' => [
                'user',
                'permissions_list_own',
                'list_departments',
                'list_orders',
                'fetch_order',
                'add_order',
                'edit_order',
                'delete_order',
                'mark_order_as_completed',
                'list_order_items',
                'add_order_items',
                'edit_order_items',
                'delete_order_items',
                'list_order_attachments',
                'add_order_attachments',
                'download_order_attachments',
                'list_products',
                'list_categories',
                'list_department_categories',
                'list_department_order_recommended',
                'vehicle_product_suggestions',
                'search_customers',
                'get_user_from_customer_number',
                'list_customer_notes',
                'add_customer_note',
                'list_customer_attributes',
                'search_vehicles',
                'view_vehicle_status',
                'list_unknown_customer_vehicles',
                'list_vehicle_customer_suggestions',
                'department_license_plate_lookup',
                'department_vehicle_order_last_five',
                'list_number_plate_scans',
                'list_department_number_plate_scanners',
                'charge_order',
                'get_payment_intent',
                'confirm_payment_intent',
                'modules_stripe_department_terminal_readers_list',
                'modules_stripe_invoice_send',
                'list_bookings',
                'list_own_bookings',
                'edit_bookings',
                'add_booking',
                'add_bookings',
                'complete_bookings',
                'resend_booking_confirmations',
                'list_department_daily_reports',
                'list_notifications',
                'list_own_notifications',
                'statistics_orders_new',
                'statistics_bookings_new',
            ],
        ],
        'department_admin' => [
            'label' => 'Department admin',
            'description' => 'Can manage limited backoffice prices and employee access for assigned departments.',
            'permissions' => [
                'user',
                'permissions_list_own',
                'list_departments',
                'list_orders',
                'fetch_order',
                'add_order',
                'edit_order',
                'delete_order',
                'mark_order_as_completed',
                'list_order_items',
                'add_order_items',
                'edit_order_items',
                'delete_order_items',
                'list_order_attachments',
                'add_order_attachments',
                'download_order_attachments',
                'list_products',
                'list_categories',
                'list_department_categories',
                'list_department_order_recommended',
                'vehicle_product_suggestions',
                'search_customers',
                'get_user_from_customer_number',
                'list_customer_notes',
                'add_customer_note',
                'list_customer_attributes',
                'search_vehicles',
                'view_vehicle_status',
                'list_unknown_customer_vehicles',
                'list_vehicle_customer_suggestions',
                'department_license_plate_lookup',
                'department_vehicle_order_last_five',
                'list_number_plate_scans',
                'list_department_number_plate_scanners',
                'charge_order',
                'get_payment_intent',
                'confirm_payment_intent',
                'modules_stripe_department_terminal_readers_list',
                'modules_stripe_invoice_send',
                'list_bookings',
                'list_own_bookings',
                'edit_bookings',
                'add_booking',
                'add_bookings',
                'complete_bookings',
                'resend_booking_confirmations',
                'list_department_daily_reports',
                'list_notifications',
                'list_own_notifications',
                'statistics_orders_new',
                'statistics_bookings_new',
                self::PERMISSION_ACCESS,
                self::PERMISSION_MANAGE_PRICES,
                self::PERMISSION_VIEW_CUSTOMER_PRICING,
                self::PERMISSION_MANAGE_CUSTOMER_PRICING,
                self::PERMISSION_MANAGE_EMPLOYEES,
            ],
        ],
    ];

    /**
     * @var array<string, array{group:string,capability:string}>
     */
    private const ROLE_PERMISSION_CAPABILITIES = [
        'user' => [
            'group' => 'account',
            'capability' => 'sign_in',
        ],
        'permissions_list_own' => [
            'group' => 'account',
            'capability' => 'view_own_permissions',
        ],
        'list_orders' => [
            'group' => 'orders',
            'capability' => 'view_orders',
        ],
        'fetch_order' => [
            'group' => 'orders',
            'capability' => 'view_orders',
        ],
        'add_order' => [
            'group' => 'orders',
            'capability' => 'create_orders',
        ],
        'edit_order' => [
            'group' => 'orders',
            'capability' => 'edit_orders',
        ],
        'delete_order' => [
            'group' => 'orders',
            'capability' => 'delete_orders',
        ],
        'mark_order_as_completed' => [
            'group' => 'orders',
            'capability' => 'complete_orders',
        ],
        'list_order_items' => [
            'group' => 'orders',
            'capability' => 'view_order_items',
        ],
        'add_order_items' => [
            'group' => 'orders',
            'capability' => 'create_order_items',
        ],
        'edit_order_items' => [
            'group' => 'orders',
            'capability' => 'update_order_lines',
        ],
        'delete_order_items' => [
            'group' => 'orders',
            'capability' => 'remove_order_lines',
        ],
        'list_order_attachments' => [
            'group' => 'attachments',
            'capability' => 'view_order_attachments',
        ],
        'add_order_attachments' => [
            'group' => 'attachments',
            'capability' => 'add_order_attachments',
        ],
        'download_order_attachments' => [
            'group' => 'attachments',
            'capability' => 'download_order_attachments',
        ],
        'list_products' => [
            'group' => 'products',
            'capability' => 'view_product_catalog',
        ],
        'list_categories' => [
            'group' => 'products',
            'capability' => 'view_product_catalog',
        ],
        'list_department_categories' => [
            'group' => 'products',
            'capability' => 'view_product_catalog',
        ],
        'list_department_order_recommended' => [
            'group' => 'products',
            'capability' => 'view_product_recommendations',
        ],
        'vehicle_product_suggestions' => [
            'group' => 'products',
            'capability' => 'view_product_recommendations',
        ],
        'search_customers' => [
            'group' => 'customers',
            'capability' => 'search_customers',
        ],
        'get_user_from_customer_number' => [
            'group' => 'customers',
            'capability' => 'view_customer_details',
        ],
        'list_customer_notes' => [
            'group' => 'customers',
            'capability' => 'view_customer_notes',
        ],
        'add_customer_note' => [
            'group' => 'customers',
            'capability' => 'add_customer_notes',
        ],
        'list_customer_attributes' => [
            'group' => 'customers',
            'capability' => 'view_customer_flags',
        ],
        'search_vehicles' => [
            'group' => 'vehicles',
            'capability' => 'search_vehicles',
        ],
        'view_vehicle_status' => [
            'group' => 'vehicles',
            'capability' => 'search_vehicles',
        ],
        'list_unknown_customer_vehicles' => [
            'group' => 'vehicles',
            'capability' => 'view_vehicle_matches',
        ],
        'list_vehicle_customer_suggestions' => [
            'group' => 'vehicles',
            'capability' => 'view_vehicle_matches',
        ],
        'department_license_plate_lookup' => [
            'group' => 'vehicles',
            'capability' => 'view_vehicle_history',
        ],
        'department_vehicle_order_last_five' => [
            'group' => 'vehicles',
            'capability' => 'view_vehicle_history',
        ],
        'list_number_plate_scans' => [
            'group' => 'scanner',
            'capability' => 'view_plate_scans',
        ],
        'list_department_number_plate_scanners' => [
            'group' => 'scanner',
            'capability' => 'view_plate_scans',
        ],
        'charge_order' => [
            'group' => 'orders',
            'capability' => 'charge_orders',
        ],
        'get_payment_intent' => [
            'group' => 'orders',
            'capability' => 'charge_orders',
        ],
        'confirm_payment_intent' => [
            'group' => 'orders',
            'capability' => 'charge_orders',
        ],
        'modules_stripe_department_terminal_readers_list' => [
            'group' => 'orders',
            'capability' => 'charge_orders',
        ],
        'modules_stripe_invoice_send' => [
            'group' => 'orders',
            'capability' => 'charge_orders',
        ],
        'list_bookings' => [
            'group' => 'bookings',
            'capability' => 'view_department_bookings',
        ],
        'list_own_bookings' => [
            'group' => 'bookings',
            'capability' => 'view_own_bookings',
        ],
        'edit_bookings' => [
            'group' => 'bookings',
            'capability' => 'update_bookings',
        ],
        'add_booking' => [
            'group' => 'bookings',
            'capability' => 'create_bookings',
        ],
        'add_bookings' => [
            'group' => 'bookings',
            'capability' => 'create_bookings',
        ],
        'complete_bookings' => [
            'group' => 'bookings',
            'capability' => 'mark_bookings_complete',
        ],
        'resend_booking_confirmations' => [
            'group' => 'bookings',
            'capability' => 'send_booking_confirmations',
        ],
        'department_timebookings_entries_get' => [
            'group' => 'time_bookings',
            'capability' => 'view_time_booking_entries',
        ],
        'department_timebookings_entries_post' => [
            'group' => 'time_bookings',
            'capability' => 'create_time_booking_entries',
        ],
        'department_timebookings_entries_put' => [
            'group' => 'time_bookings',
            'capability' => 'edit_time_booking_entries',
        ],
        'list_departments' => [
            'group' => 'departments',
            'capability' => 'view_departments',
        ],
        'list_department_daily_reports' => [
            'group' => 'departments',
            'capability' => 'view_daily_reports',
        ],
        'list_notifications' => [
            'group' => 'notifications',
            'capability' => 'view_notifications',
        ],
        'list_own_notifications' => [
            'group' => 'notifications',
            'capability' => 'view_notifications',
        ],
        'statistics_orders_new' => [
            'group' => 'reports',
            'capability' => 'view_order_statistics',
        ],
        'statistics_bookings_new' => [
            'group' => 'reports',
            'capability' => 'view_booking_statistics',
        ],
        self::PERMISSION_ACCESS => [
            'group' => 'limited_backoffice',
            'capability' => 'open_limited_backoffice',
        ],
        self::PERMISSION_MANAGE_PRICES => [
            'group' => 'limited_backoffice',
            'capability' => 'manage_department_prices',
        ],
        self::PERMISSION_VIEW_CUSTOMER_PRICING => [
            'group' => 'limited_backoffice',
            'capability' => 'view_customer_pricing',
        ],
        self::PERMISSION_MANAGE_CUSTOMER_PRICING => [
            'group' => 'limited_backoffice',
            'capability' => 'manage_customer_pricing',
        ],
        self::PERMISSION_MANAGE_EMPLOYEES => [
            'group' => 'limited_backoffice',
            'capability' => 'manage_employee_access',
        ],
    ];

    /**
     * @var array<int, string>
     */
    private const ROLE_PERMISSION_GROUP_ORDER = [
        'account',
        'departments',
        'orders',
        'products',
        'customers',
        'vehicles',
        'attachments',
        'scanner',
        'bookings',
        'time_bookings',
        'notifications',
        'reports',
        'limited_backoffice',
    ];

    /**
     * @var array<int, true>
     */
    private const PHONE_COUNTRY_CODES = [
        45 => true,
        46 => true,
        47 => true,
        358 => true,
    ];

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

    public function __construct()
    {
        departments_schema_bootstrap::ensureTables();
        limited_backoffice_schema_bootstrap::ensureTables();
    }

    /**
     * @return array<int, array{key:string,label:string,description:string,permission_groups:array<int,array{key:string,capabilities:array<int,string>}>}>
     */
    public function rolePresets(?users_o $manager = null): array
    {
        $roles = [];
        foreach (self::ROLE_PRESETS as $key => $preset) {
            $permissions = $manager === null
                ? $preset['permissions']
                : $this->effectiveRolePermissionsForManager($manager, $key, false);

            $roles[] = [
                'key' => $key,
                'label' => $preset['label'],
                'description' => $preset['description'],
                'permission_groups' => $this->rolePermissionGroups($permissions),
            ];
        }
        return $roles;
    }

    /**
     * @return array<int, array{key:string,label:string,description:string,permissions:array<int,string>}>
     */
    public function rolePermissionTemplates(): array
    {
        $templates = [];
        foreach (self::ROLE_PRESETS as $key => $preset) {
            $templates[] = [
                'key' => $key,
                'label' => $preset['label'],
                'description' => $preset['description'],
                'permissions' => array_values($preset['permissions']),
            ];
        }
        return $templates;
    }

    /**
     * @param array<int, string> $permissions
     * @return array<int, array{key:string,capabilities:array<int,string>}>
     */
    private function rolePermissionGroups(array $permissions): array
    {
        $groups = [];
        foreach ($permissions as $permission) {
            $capability = self::ROLE_PERMISSION_CAPABILITIES[$permission] ?? null;
            if ($capability === null) {
                throw new \RuntimeException('Missing limited backoffice role capability for permission: ' . $permission);
            }

            $group = $capability['group'];
            $groups[$group] ??= [];
            $groups[$group][] = $capability['capability'];
        }

        $payload = [];
        foreach (self::ROLE_PERMISSION_GROUP_ORDER as $group) {
            if (!isset($groups[$group])) {
                continue;
            }

            $payload[] = [
                'key' => $group,
                'capabilities' => array_values(array_unique($groups[$group])),
            ];
        }

        return $payload;
    }

    /**
     * @return array<int, int>
     */
    public function accessibleDepartmentIds(users_o $user): array
    {
        if (!$user->exists()) {
            return [];
        }

        $groupId = (int)$user->group_id->value();
        if ($groupId <= 0) {
            return [];
        }

        if ($user->hasPermission('superuser')) {
            global $db;
            $rows = $db->fetch_all($db->query(
                'SELECT `id` FROM `departments` ORDER BY `id` ASC'
            ));

            return array_values(array_map(static fn(array $row): int => (int)$row['id'], $rows));
        }

        global $db;
        $statement = $this->mysqli()->prepare(
            'SELECT `permission` FROM `groups_permissions` WHERE `group_id` = ?'
        );
        if ($statement === false) {
            throw new limited_backoffice_exception('Unable to load department access.', 500);
        }

        $statement->bind_param('i', $groupId);
        $statement->execute();
        $result = $statement->get_result();
        $rows = $db->fetch_all($result);
        $statement->close();

        $departmentIds = [];
        foreach ($rows as $row) {
            $permission = (string)($row['permission'] ?? '');
            if (preg_match('/^department_access_([0-9]+)$/', $permission, $matches) !== 1) {
                continue;
            }
            $departmentId = (int)$matches[1];
            if ($departmentId > 0) {
                $departmentIds[] = $departmentId;
            }
        }

        sort($departmentIds);
        return array_values(array_unique($departmentIds));
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    public function departmentsForUser(users_o $user): array
    {
        $departmentIds = $this->accessibleDepartmentIds($user);
        if ($departmentIds === []) {
            return [];
        }

        $in = implode(',', array_map('intval', $departmentIds));
        $sql = "
            SELECT `id`, `name`, `description`, `visible`, `archived`, `custom_pricing_only`
            FROM `departments`
            WHERE `id` IN ($in)
            ORDER BY `order_priority` ASC, `name` ASC, `id` ASC
        ";

        global $db;
        $rows = $db->fetch_all($db->query($sql));

        return array_map(static fn(array $row): array => [
            'id' => (int)$row['id'],
            'name' => (string)$row['name'],
            'description' => (string)($row['description'] ?? ''),
            'visible' => (bool)($row['visible'] ?? false),
            'archived' => (bool)($row['archived'] ?? false),
            'custom_pricing_only' => (bool)(int)($row['custom_pricing_only'] ?? 0),
        ], $rows);
    }

    /**
     * @return array<string, mixed>
     */
    public function getDepartmentPrices(users_o $user, int $departmentId): array
    {
        $this->assertDepartmentAccess($user, $departmentId);

        $department = $this->fetchDepartment($departmentId);
        if ($department === null) {
            throw new limited_backoffice_exception('Department not found', 404);
        }

        $customPricingOnly = (bool)($department['custom_pricing_only'] ?? false);
        $catalog = $this->departmentProductCatalog($departmentId, $customPricingOnly);
        if (!$customPricingOnly && $catalog['missing_products'] !== []) {
            throw new limited_backoffice_exception('Department price setup is incomplete.', 409, [
                'message' => 'Department price setup is incomplete.',
                'code' => 'department_price_setup_required',
                'department' => $department,
                'missing_products' => $catalog['missing_products'],
            ]);
        }

        return [
            'department' => $department,
            'categories' => $catalog['categories'],
        ];
    }

    /**
     * @param array<string, mixed> $payload
     * @return array<string, mixed>
     */
    public function updateDepartmentPrices(users_o $user, int $departmentId, array $payload): array
    {
        $this->assertDepartmentAccess($user, $departmentId);

        if (array_key_exists('department_id', $payload) && (int)$payload['department_id'] !== $departmentId) {
            throw new limited_backoffice_exception('Department ID in body does not match the route.', 400);
        }

        $department = $this->fetchDepartment($departmentId);
        if ($department === null) {
            throw new limited_backoffice_exception('Department not found', 404);
        }

        $customPricingOnly = (bool)($department['custom_pricing_only'] ?? false);
        $catalog = $this->departmentProductCatalog($departmentId, $customPricingOnly);
        if ($catalog['required_product_ids'] === []) {
            throw new limited_backoffice_exception('Department has no products configured.', 409);
        }

        $prices = $payload['prices'] ?? null;
        if (!is_array($prices) || $prices === []) {
            throw new limited_backoffice_exception('Prices are required.', 400);
        }

        $normalizedPrices = $this->normalizePricePayload($prices, $departmentId);
        $requiredProductIds = $catalog['required_product_ids'];
        $allowedLookup = array_fill_keys($requiredProductIds, true);
        $providedProductIds = array_keys($normalizedPrices);
        sort($providedProductIds);
        $missingProductIds = array_values(array_diff($requiredProductIds, $providedProductIds));

        if (!$customPricingOnly && $missingProductIds !== []) {
            throw new limited_backoffice_exception('Price is required for every department product.', 400, [
                'message' => 'Price is required for every department product.',
                'missing_product_ids' => $missingProductIds,
            ]);
        }

        foreach ($providedProductIds as $productId) {
            if (!isset($allowedLookup[$productId])) {
                throw new limited_backoffice_exception('Product is not available for this department.', 400);
            }
        }

        $mysqli = $this->mysqli();
        $mysqli->begin_transaction();

        try {
            $deleteStatement = $mysqli->prepare(
                'DELETE FROM `product_department_prices` WHERE `department_id` = ? AND `product_id` = ?'
            );
            $insertStatement = $mysqli->prepare(
                'INSERT INTO `product_department_prices` (`department_id`, `product_id`, `price`) VALUES (?, ?, ?)'
            );
            if ($deleteStatement === false || $insertStatement === false) {
                throw new \RuntimeException('Unable to prepare department price update.');
            }

            foreach ($normalizedPrices as $productId => $price) {
                $deleteStatement->bind_param('ii', $departmentId, $productId);
                $deleteStatement->execute();

                $insertStatement->bind_param('iii', $departmentId, $productId, $price);
                $insertStatement->execute();
            }

            $deleteStatement->close();
            $insertStatement->close();
            $mysqli->commit();
        } catch (\Throwable $throwable) {
            $mysqli->rollback();
            throw new limited_backoffice_exception('Unable to update department prices.', 500);
        }

        return $this->getDepartmentPrices($user, $departmentId);
    }

    /**
     * @return array<string, mixed>
     */
    public function getDepartmentCustomerPricing(users_o $user, int $departmentId, int $customerUserId): array
    {
        $this->assertDepartmentAccess($user, $departmentId);
        return (new department_customer_pricing_service())->getPricing($departmentId, $customerUserId);
    }

    /**
     * @param array<string, mixed> $payload
     * @return array<string, mixed>
     */
    public function updateDepartmentCustomerPricing(users_o $user, int $departmentId, int $customerUserId, array $payload): array
    {
        $this->assertDepartmentAccess($user, $departmentId);

        if (array_key_exists('department_id', $payload) && (int)$payload['department_id'] !== $departmentId) {
            throw new limited_backoffice_exception('Department ID in body does not match the route.', 400);
        }

        return (new department_customer_pricing_service())->updatePricing($departmentId, $customerUserId, [
            ...$payload,
            'department_id' => $departmentId,
            'user_id' => $customerUserId,
        ]);
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    public function employeesForUser(users_o $user, bool $includeInactive = false): array
    {
        $managerDepartmentIds = $this->accessibleDepartmentIds($user);
        if ($managerDepartmentIds === []) {
            return [];
        }

        $userDeletedAtSelect = $this->tableHasColumn('users', 'deleted_at')
            ? 'u.`deleted_at` AS `user_deleted_at`'
            : 'NULL AS `user_deleted_at`';

        global $db;
        $rows = $db->fetch_all($db->query("
            SELECT
                lbe.*,
                u.`customer_number`,
                u.`display_name`,
                u.`email`,
                u.`phone_country_code`,
                u.`phone`,
                u.`group_id`,
                {$userDeletedAtSelect}
            FROM `limited_backoffice_employees` lbe
            INNER JOIN `users` u ON u.`id` = lbe.`user_id`
            ORDER BY u.`display_name` ASC, lbe.`user_id` ASC
        "));

        $employees = [];
        foreach ($rows as $row) {
            $employeeDepartmentIds = $this->decodeDepartmentIds((string)($row['department_ids'] ?? '[]'));
            if (!$this->isSubset($employeeDepartmentIds, $managerDepartmentIds)) {
                continue;
            }

            $active = $this->isEmployeeRowActive($row);
            if (!$includeInactive && !$active) {
                continue;
            }

            $employees[] = $this->formatEmployee($row, $employeeDepartmentIds, $active);
        }

        return $employees;
    }

    /**
     * @param array<string, mixed> $payload
     * @return array<string, mixed>
     */
    public function createEmployee(users_o $manager, array $payload): array
    {
        $this->rejectRawPermissionPayload($payload);

        $departmentIds = $this->normalizeDepartmentIds($payload['department_ids'] ?? null);
        $this->assertDepartmentSubset($manager, $departmentIds);

        $roleKey = $this->normalizeRoleKey($payload['role_key'] ?? null);
        $displayName = $this->normalizeRequiredString($payload['display_name'] ?? null, 'Display name is required.');
        $password = $this->normalizePassword($payload['password'] ?? null, true);
        $email = $this->normalizeEmail($payload['email'] ?? null, true);
        $phone = $this->normalizeOptionalPhonePair($payload);

        $mysqli = $this->mysqli();
        $mysqli->begin_transaction();

        try {
            $groupId = $this->insertManagedGroup($manager, $roleKey, $departmentIds);
            $customerNumber = self::MANAGED_EMPLOYEE_CUSTOMER_NUMBER;
            $passwordHash = password_hash($password, PASSWORD_DEFAULT);

            $statement = $mysqli->prepare(
                'INSERT INTO `users`
                    (`customer_number`, `display_name`, `email`, `password`, `group_id`, `phone_country_code`, `phone`)
                 VALUES (?, ?, ?, ?, ?, ?, ?)'
            );
            if ($statement === false) {
                throw new \RuntimeException('Unable to prepare employee insert.');
            }
            $statement->bind_param(
                'isssiii',
                $customerNumber,
                $displayName,
                $email,
                $passwordHash,
                $groupId,
                $phone['phone_country_code'],
                $phone['phone']
            );
            $statement->execute();
            $employeeId = (int)$mysqli->insert_id;
            $statement->close();

            $this->renameManagedGroup($groupId, $employeeId);

            $departmentJson = json_encode($departmentIds, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
            if (!is_string($departmentJson)) {
                throw new \RuntimeException('Unable to encode department metadata.');
            }

            $managerId = (int)$manager->id;
            $statement = $mysqli->prepare(
                'INSERT INTO `limited_backoffice_employees`
                    (`user_id`, `managed_group_id`, `role_key`, `department_ids`, `created_by_user_id`, `updated_by_user_id`)
                 VALUES (?, ?, ?, ?, ?, ?)'
            );
            if ($statement === false) {
                throw new \RuntimeException('Unable to prepare employee metadata insert.');
            }
            $statement->bind_param('iissii', $employeeId, $groupId, $roleKey, $departmentJson, $managerId, $managerId);
            $statement->execute();
            $statement->close();

            $mysqli->commit();
        } catch (\Throwable) {
            $mysqli->rollback();
            throw new limited_backoffice_exception('Unable to create employee.', 500);
        }

        $employee = $this->loadManagedEmployee($employeeId);
        if ($employee === null) {
            throw new limited_backoffice_exception('Unable to load created employee.', 500);
        }

        return $this->formatEmployee($employee, $departmentIds, true);
    }

    /**
     * @param array<string, mixed> $payload
     * @return array<string, mixed>
     */
    public function migrateEmployee(users_o $manager, int $employeeId, array $payload): array
    {
        $this->rejectRawPermissionPayload($payload);
        $this->assertNotSelfEdit($manager, $employeeId);

        if ($this->loadManagedEmployee($employeeId) !== null) {
            throw new limited_backoffice_exception('User is already a limited backoffice employee.', 409);
        }

        $target = $this->loadMigratableUser($employeeId);
        if ($target === null) {
            throw new limited_backoffice_exception('User not found.', 404);
        }
        $this->assertMigrationTargetIsSafe($target);

        $departmentIds = $this->normalizeDepartmentIds($payload['department_ids'] ?? null);
        $this->assertDepartmentSubset($manager, $departmentIds);
        $roleKey = $this->normalizeRoleKey($payload['role_key'] ?? null);

        $departmentJson = json_encode($departmentIds, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        if (!is_string($departmentJson)) {
            throw new limited_backoffice_exception('Unable to encode department metadata.', 500);
        }

        $mysqli = $this->mysqli();
        $mysqli->begin_transaction();

        try {
            $groupId = $this->insertManagedGroup($manager, $roleKey, $departmentIds);
            $this->renameManagedGroup($groupId, $employeeId);
            $this->updateUserFields($employeeId, [
                'group_id' => $groupId,
            ]);

            $managerId = (int)$manager->id;
            $statement = $mysqli->prepare(
                'INSERT INTO `limited_backoffice_employees`
                    (`user_id`, `managed_group_id`, `role_key`, `department_ids`, `created_by_user_id`, `updated_by_user_id`)
                 VALUES (?, ?, ?, ?, ?, ?)'
            );
            if ($statement === false) {
                throw new \RuntimeException('Unable to prepare migrated employee metadata insert.');
            }
            $statement->bind_param('iissii', $employeeId, $groupId, $roleKey, $departmentJson, $managerId, $managerId);
            $statement->execute();
            $statement->close();

            $this->clearUserSessionCache($employeeId);
            $mysqli->commit();
        } catch (\Throwable) {
            $mysqli->rollback();
            throw new limited_backoffice_exception('Unable to migrate employee.', 500);
        }

        $employee = $this->loadManagedEmployee($employeeId);
        if ($employee === null) {
            throw new limited_backoffice_exception('Unable to load migrated employee.', 500);
        }

        return $this->formatEmployee($employee, $departmentIds, true);
    }

    /**
     * @param array<string, mixed> $payload
     * @return array<string, mixed>
     */
    public function updateEmployee(users_o $manager, int $employeeId, array $payload): array
    {
        $this->rejectRawPermissionPayload($payload);
        $this->assertNotSelfEdit($manager, $employeeId);

        $employee = $this->loadManagedEmployee($employeeId);
        if ($employee === null) {
            throw new limited_backoffice_exception('Managed employee not found.', 404);
        }

        $oldDepartmentIds = $this->decodeDepartmentIds((string)$employee['department_ids']);
        $this->assertDepartmentSubset($manager, $oldDepartmentIds);
        $this->assertManagedTargetIsSafe($employee);

        $newDepartmentIds = array_key_exists('department_ids', $payload)
            ? $this->normalizeDepartmentIds($payload['department_ids'])
            : $oldDepartmentIds;
        $this->assertDepartmentSubset($manager, $newDepartmentIds);

        $roleKey = array_key_exists('role_key', $payload)
            ? $this->normalizeRoleKey($payload['role_key'])
            : (string)$employee['role_key'];

        $displayName = array_key_exists('display_name', $payload)
            ? $this->normalizeRequiredString($payload['display_name'], 'Display name is required.')
            : null;
        $email = array_key_exists('email', $payload)
            ? $this->normalizeEmail($payload['email'], true)
            : null;
        $password = array_key_exists('password', $payload)
            ? $this->normalizePassword($payload['password'], false)
            : null;
        $phone = $this->normalizeOptionalPhonePair($payload, false);
        $active = array_key_exists('active', $payload)
            ? (bool)$payload['active']
            : $this->isEmployeeRowActive($employee);

        $managedGroupId = (int)$employee['managed_group_id'];
        $managerId = (int)$manager->id;
        $departmentJson = json_encode($newDepartmentIds, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        if (!is_string($departmentJson)) {
            throw new limited_backoffice_exception('Unable to encode department metadata.', 500);
        }

        $mysqli = $this->mysqli();
        $mysqli->begin_transaction();

        try {
            if ($active) {
                $this->replaceGroupPermissions($managedGroupId, $this->permissionsForRoleAndDepartments($manager, $roleKey, $newDepartmentIds));
            }

            $userUpdates = [];
            if ($displayName !== null) {
                $userUpdates['display_name'] = $displayName;
            }
            if (array_key_exists('email', $payload)) {
                $userUpdates['email'] = $email;
            }
            if ($password !== null) {
                $userUpdates['password'] = password_hash($password, PASSWORD_DEFAULT);
            }
            if ($phone !== null) {
                $userUpdates['phone_country_code'] = $phone['phone_country_code'];
                $userUpdates['phone'] = $phone['phone'];
            }
            $usersHaveDeletedAt = $this->tableHasColumn('users', 'deleted_at');
            if ($active) {
                $userUpdates['group_id'] = $managedGroupId;
                if ($usersHaveDeletedAt) {
                    $userUpdates['deleted_at'] = null;
                }
            } else {
                $userUpdates['group_id'] = 0;
                $userUpdates['password'] = null;
                if ($usersHaveDeletedAt) {
                    $userUpdates['deleted_at'] = date('Y-m-d H:i:s');
                }
            }
            $this->updateUserFields($employeeId, $userUpdates);

            $deactivatedAtSql = $active ? null : date('Y-m-d H:i:s');
            $statement = $mysqli->prepare(
                'UPDATE `limited_backoffice_employees`
                 SET `role_key` = ?, `department_ids` = ?, `updated_by_user_id` = ?, `deactivated_at` = ?
                 WHERE `user_id` = ? LIMIT 1'
            );
            if ($statement === false) {
                throw new \RuntimeException('Unable to prepare employee metadata update.');
            }
            $statement->bind_param('ssisi', $roleKey, $departmentJson, $managerId, $deactivatedAtSql, $employeeId);
            $statement->execute();
            $statement->close();

            if (!$active) {
                $this->deleteUserTokens($employeeId);
            } else {
                $this->clearUserSessionCache($employeeId);
            }

            $mysqli->commit();
        } catch (\Throwable) {
            $mysqli->rollback();
            throw new limited_backoffice_exception('Unable to update employee.', 500);
        }

        $updated = $this->loadManagedEmployee($employeeId);
        if ($updated === null) {
            throw new limited_backoffice_exception('Unable to load updated employee.', 500);
        }

        return $this->formatEmployee($updated, $newDepartmentIds, $this->isEmployeeRowActive($updated));
    }

    /**
     * @return array<string, mixed>
     */
    public function deactivateEmployee(users_o $manager, int $employeeId): array
    {
        return $this->updateEmployee($manager, $employeeId, ['active' => false]);
    }

    /**
     * @return array{employee_id:int,login_path:string}
     */
    public function createEmployeeLoginLink(users_o $manager, int $employeeId): array
    {
        $this->assertNotSelfEdit($manager, $employeeId);

        $employee = $this->loadManagedEmployee($employeeId);
        if ($employee === null) {
            throw new limited_backoffice_exception('Managed employee not found.', 404);
        }

        $departmentIds = $this->decodeDepartmentIds((string)$employee['department_ids']);
        $this->assertDepartmentSubset($manager, $departmentIds);
        $this->assertManagedTargetIsSafe($employee);

        if (!$this->isEmployeeRowActive($employee)) {
            throw new limited_backoffice_exception('Cannot create a login link for an inactive employee.', 409);
        }

        $token = (new authentication())->create_employee_token($employeeId);

        try {
            (new logs_o())->add(
                'auth',
                'global',
                1,
                (int)$manager->id,
                'AUTH_SUCCESS_LIMITED_BACKOFFICE_EMPLOYEE_LOGIN_LINK',
                'Created limited backoffice login link for employee: ' . $employeeId
            );
        } catch (\Throwable) {
            // Audit logging should not block login-link generation.
        }

        return [
            'employee_id' => $employeeId,
            'login_path' => '/login/qr?token=' . $token,
        ];
    }

    private function mysqli(): mysqli
    {
        global $db;
        return $db->conn();
    }

    private function tableHasColumn(string $table, string $column): bool
    {
        $cacheKey = $table . '.' . $column;
        if (array_key_exists($cacheKey, $this->columnExistsCache)) {
            return $this->columnExistsCache[$cacheKey];
        }

        global $db;
        $tableSql = $this->escapeIdentifierLookup($table);
        $columnSql = $this->escapeIdentifierLookup($column);
        $result = $db->query("SHOW COLUMNS FROM `{$tableSql}` LIKE '{$columnSql}'");
        $exists = $result !== false && is_object($result) && property_exists($result, 'num_rows') && (int)$result->num_rows > 0;

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

    private function escapeIdentifierLookup(string $value): string
    {
        return str_replace(['\\', "'", '`'], ['\\\\', "\\'", ''], $value);
    }

    private function assertDepartmentAccess(users_o $user, int $departmentId): void
    {
        if ($departmentId <= 0) {
            throw new limited_backoffice_exception('Department ID must be a positive integer.', 400);
        }

        $departmentIds = $this->accessibleDepartmentIds($user);
        if (!in_array($departmentId, $departmentIds, true)) {
            throw new limited_backoffice_exception('Missing department access.', 403, [
                'message' => 'Missing permission(s)',
                'permissions' => ['department_access_' . $departmentId],
            ]);
        }
    }

    /**
     * @return array{id:int,name:string,description:string,custom_pricing_only:bool}
     */
    private function fetchDepartment(int $departmentId): ?array
    {
        global $db;
        $statement = $this->mysqli()->prepare(
            'SELECT `id`, `name`, `description`, `custom_pricing_only` FROM `departments` WHERE `id` = ? LIMIT 1'
        );
        if ($statement === false) {
            throw new limited_backoffice_exception('Unable to load department.', 500);
        }
        $statement->bind_param('i', $departmentId);
        $statement->execute();
        $result = $statement->get_result();
        $row = $db->fetch_assoc($result);
        $statement->close();

        if (!is_array($row)) {
            return null;
        }

        return [
            'id' => (int)$row['id'],
            'name' => (string)$row['name'],
            'description' => (string)($row['description'] ?? ''),
            'custom_pricing_only' => (bool)(int)($row['custom_pricing_only'] ?? 0),
        ];
    }

    /**
     * @return array{categories:array<int,array<string,mixed>>,missing_products:array<int,array<string,mixed>>,required_product_ids:array<int,int>}
     */
    private function departmentProductCatalog(int $departmentId, bool $customPricingOnly = false): array
    {
        global $db;

        $where = [
            'dc.`department_id` = ?',
            'dc.`deleted_at` IS NULL',
        ];
        if ($this->tableHasColumn('products', 'deleted_at')) {
            $where[] = 'p.`deleted_at` IS NULL';
        }

        $statement = $this->mysqli()->prepare(
            'SELECT
                c.`id` AS `category_id`,
                c.`name` AS `category_name`,
                c.`description` AS `category_description`,
                p.`id` AS `product_id`,
                p.`name` AS `product_name`,
                p.`description` AS `product_description`,
                pdp.`id` AS `department_price_id`,
                pdp.`price` AS `department_price`
            FROM `department_categories` dc
            INNER JOIN `categories` c ON c.`id` = dc.`category_id`
            INNER JOIN `products` p ON p.`category` = dc.`category_id`
            LEFT JOIN `product_department_prices` pdp
                ON pdp.`id` = (
                    SELECT pdp_latest.`id`
                    FROM `product_department_prices` pdp_latest
                    WHERE pdp_latest.`department_id` = dc.`department_id`
                        AND pdp_latest.`product_id` = p.`id`
                    ORDER BY pdp_latest.`id` DESC
                    LIMIT 1
                )
            WHERE ' . implode(' AND ', $where) . '
            ORDER BY c.`name` ASC, c.`id` ASC, p.`order_priority` ASC, p.`name` ASC, p.`id` ASC'
        );
        if ($statement === false) {
            throw new limited_backoffice_exception('Unable to load department products.', 500);
        }
        $statement->bind_param('i', $departmentId);
        $statement->execute();
        $result = $statement->get_result();
        $rows = $db->fetch_all($result);
        $statement->close();

        $categories = [];
        $missing = [];
        $requiredProductIds = [];
        $seenProductIds = [];
        foreach ($rows as $row) {
            $categoryId = (int)$row['category_id'];
            $productId = (int)$row['product_id'];

            if (isset($seenProductIds[$productId])) {
                continue;
            }
            $seenProductIds[$productId] = true;
            $requiredProductIds[] = $productId;

            if (!isset($categories[$categoryId])) {
                $categories[$categoryId] = [
                    'id' => $categoryId,
                    'name' => (string)$row['category_name'],
                    'description' => (string)($row['category_description'] ?? ''),
                    'products' => [],
                ];
            }

            $product = [
                'id' => $productId,
                'name' => (string)$row['product_name'],
                'description' => (string)($row['product_description'] ?? ''),
                'price' => $row['department_price'] === null
                    ? ($customPricingOnly ? products_o::CUSTOM_PRICING_MISSING_PRICE : null)
                    : (int)$row['department_price'],
            ];

            if ($row['department_price_id'] === null && !$customPricingOnly) {
                $missing[] = [
                    'id' => $productId,
                    'name' => (string)$row['product_name'],
                    'category' => [
                        'id' => $categoryId,
                        'name' => (string)$row['category_name'],
                    ],
                ];
            }

            $categories[$categoryId]['products'][] = $product;
        }

        sort($requiredProductIds);

        return [
            'categories' => array_values($categories),
            'missing_products' => $missing,
            'required_product_ids' => array_values($requiredProductIds),
        ];
    }

    /**
     * @param array<int|string, mixed> $prices
     * @return array<int, int>
     */
    private function normalizePricePayload(array $prices, int $departmentId): array
    {
        $normalized = [];
        foreach ($prices as $key => $item) {
            if (is_array($item)) {
                if (!array_key_exists('product_id', $item)) {
                    throw new limited_backoffice_exception('Product ID is required for every price.', 400);
                }
                if (array_key_exists('department_id', $item) && (int)$item['department_id'] !== $departmentId) {
                    throw new limited_backoffice_exception('Department ID in price row does not match the route.', 400);
                }
                $productId = $this->normalizePositiveInt($item['product_id'], 'Product ID must be a positive integer.');
                if (!array_key_exists('price', $item)) {
                    throw new limited_backoffice_exception('Price is required for every product.', 400);
                }
                $price = $this->normalizePrice($item['price']);
            } else {
                $productId = $this->normalizePositiveInt($key, 'Product ID must be a positive integer.');
                $price = $this->normalizePrice($item);
            }

            if (isset($normalized[$productId])) {
                throw new limited_backoffice_exception('Duplicate product price rows are not allowed.', 400);
            }
            $normalized[$productId] = $price;
        }

        ksort($normalized);
        return $normalized;
    }

    private function normalizePrice(mixed $value): int
    {
        if ($value === null || $value === '') {
            throw new limited_backoffice_exception('Price is required for every product.', 400);
        }

        if (is_int($value)) {
            $price = $value;
        } elseif (is_float($value) && floor($value) === $value) {
            $price = (int)$value;
        } elseif (is_string($value) && preg_match('/^[0-9]+$/', trim($value)) === 1) {
            $price = (int)trim($value);
        } else {
            throw new limited_backoffice_exception('Price must be a number.', 400);
        }

        if ($price < 0) {
            throw new limited_backoffice_exception('Price cannot be negative.', 400);
        }

        return $price;
    }

    private function normalizePositiveInt(mixed $value, string $message): int
    {
        if (is_int($value)) {
            $id = $value;
        } elseif (is_string($value) && ctype_digit($value)) {
            $id = (int)$value;
        } else {
            throw new limited_backoffice_exception($message, 400);
        }

        if ($id <= 0) {
            throw new limited_backoffice_exception($message, 400);
        }

        return $id;
    }

    /**
     * @param array<int, int> $departmentIds
     */
    private function assertDepartmentSubset(users_o $manager, array $departmentIds): void
    {
        if ($departmentIds === []) {
            throw new limited_backoffice_exception('At least one department is required.', 400);
        }

        $managerDepartmentIds = $this->accessibleDepartmentIds($manager);
        $outside = array_values(array_diff($departmentIds, $managerDepartmentIds));
        if ($outside !== []) {
            $permissions = array_map(static fn(int $id): string => 'department_access_' . $id, $outside);
            throw new limited_backoffice_exception('Missing department access.', 403, [
                'message' => 'Missing permission(s)',
                'permissions' => $permissions,
            ]);
        }
    }

    /**
     * @return array<int, int>
     */
    private function normalizeDepartmentIds(mixed $value): array
    {
        if (!is_array($value)) {
            throw new limited_backoffice_exception('Department IDs are required.', 400);
        }

        $ids = [];
        foreach ($value as $departmentId) {
            $ids[] = $this->normalizePositiveInt($departmentId, 'Department ID must be a positive integer.');
        }

        sort($ids);
        return array_values(array_unique($ids));
    }

    /**
     * @return array<int, int>
     */
    private function decodeDepartmentIds(string $json): array
    {
        $decoded = json_decode($json, true);
        if (!is_array($decoded)) {
            return [];
        }

        $ids = [];
        foreach ($decoded as $value) {
            if (is_int($value) && $value > 0) {
                $ids[] = $value;
            } elseif (is_string($value) && ctype_digit($value) && (int)$value > 0) {
                $ids[] = (int)$value;
            }
        }

        sort($ids);
        return array_values(array_unique($ids));
    }

    /**
     * @param array<int, int> $candidate
     * @param array<int, int> $allowed
     */
    private function isSubset(array $candidate, array $allowed): bool
    {
        return array_values(array_diff($candidate, $allowed)) === [];
    }

    private function normalizeRoleKey(mixed $value): string
    {
        if (!is_string($value) || trim($value) === '') {
            throw new limited_backoffice_exception('Role is required.', 400);
        }

        $roleKey = trim($value);
        if (!isset(self::ROLE_PRESETS[$roleKey])) {
            throw new limited_backoffice_exception('Unknown role.', 400);
        }

        return $roleKey;
    }

    private function normalizeRequiredString(mixed $value, string $message): string
    {
        if (!is_string($value) || trim($value) === '') {
            throw new limited_backoffice_exception($message, 400);
        }

        return trim($value);
    }

    private function normalizeOptionalString(mixed $value): ?string
    {
        if ($value === null) {
            return null;
        }
        if (!is_string($value)) {
            throw new limited_backoffice_exception('Invalid text value.', 400);
        }
        $value = trim($value);
        return $value === '' ? null : $value;
    }

    private function normalizeEmail(mixed $value, bool $required): ?string
    {
        $email = $this->normalizeOptionalString($value);
        if ($email === null) {
            if ($required) {
                throw new limited_backoffice_exception('Email is required.', 400);
            }
            return null;
        }

        if (filter_var($email, FILTER_VALIDATE_EMAIL) === false) {
            throw new limited_backoffice_exception('Email must be a valid email address.', 400);
        }

        return $email;
    }

    /**
     * @param array<string, mixed> $payload
     * @return array{phone_country_code:int|null,phone:int|null}|null
     */
    private function normalizeOptionalPhonePair(array $payload, bool $defaultWhenMissing = true): ?array
    {
        $hasCountryCode = array_key_exists('phone_country_code', $payload);
        $hasPhone = array_key_exists('phone', $payload);
        if (!$hasCountryCode && !$hasPhone) {
            return $defaultWhenMissing
                ? ['phone_country_code' => null, 'phone' => null]
                : null;
        }

        if (!$hasCountryCode || !$hasPhone) {
            throw new limited_backoffice_exception('Phone country code and phone number must be provided together.', 400);
        }

        $countryCode = $this->normalizeOptionalDigits($payload['phone_country_code']);
        $phone = $this->normalizeOptionalDigits($payload['phone']);
        if ($countryCode === null && $phone === null) {
            return ['phone_country_code' => null, 'phone' => null];
        }

        if ($countryCode === null || $phone === null) {
            throw new limited_backoffice_exception('Phone country code and phone number must be provided together.', 400);
        }

        if (!isset(self::PHONE_COUNTRY_CODES[$countryCode])) {
            throw new limited_backoffice_exception('Phone country code is not supported.', 400);
        }

        $phoneText = (string)$phone;
        if (!preg_match('/^\d{4,15}$/', $phoneText)) {
            throw new limited_backoffice_exception('Phone number must be 4-15 digits.', 400);
        }

        return [
            'phone_country_code' => $countryCode,
            'phone' => $phone,
        ];
    }

    private function normalizeOptionalDigits(mixed $value): ?int
    {
        if ($value === null) {
            return null;
        }

        if (is_int($value)) {
            return $value > 0 ? $value : null;
        }

        if (is_string($value)) {
            $value = trim($value);
            if ($value === '') {
                return null;
            }
            if (ctype_digit($value)) {
                return (int)$value;
            }
        }

        throw new limited_backoffice_exception('Phone values must contain digits only.', 400);
    }

    private function normalizePassword(mixed $value, bool $required): ?string
    {
        if ($value === null || $value === '') {
            if ($required) {
                throw new limited_backoffice_exception('Password is required.', 400);
            }
            return null;
        }
        if (!is_string($value) || strlen($value) < 8) {
            throw new limited_backoffice_exception('Password must be at least 8 characters long.', 400);
        }
        return $value;
    }

    /**
     * @param array<string, mixed> $payload
     */
    private function rejectRawPermissionPayload(array $payload): void
    {
        foreach (['permissions', 'permission', 'raw_permissions', 'group_id', 'role'] as $key) {
            if (array_key_exists($key, $payload)) {
                throw new limited_backoffice_exception('Raw permission and group assignment is not allowed.', 400);
            }
        }
    }

    /**
     * @param array<int, int> $departmentIds
     */
    private function insertManagedGroup(users_o $manager, string $roleKey, array $departmentIds): int
    {
        $name = 'Limited employee';
        $description = 'Managed by limited backoffice.';
        $statement = $this->mysqli()->prepare('INSERT INTO `groups` (`name`, `description`) VALUES (?, ?)');
        if ($statement === false) {
            throw new \RuntimeException('Unable to prepare group insert.');
        }
        $statement->bind_param('ss', $name, $description);
        $statement->execute();
        $groupId = (int)$this->mysqli()->insert_id;
        $statement->close();

        $this->replaceGroupPermissions($groupId, $this->permissionsForRoleAndDepartments($manager, $roleKey, $departmentIds));

        return $groupId;
    }

    private function renameManagedGroup(int $groupId, int $employeeId): void
    {
        $groupName = 'Limited employee #' . $employeeId;
        $groupDescription = 'Managed by limited backoffice.';
        $statement = $this->mysqli()->prepare('UPDATE `groups` SET `name` = ?, `description` = ? WHERE `id` = ? LIMIT 1');
        if ($statement === false) {
            throw new \RuntimeException('Unable to prepare group update.');
        }
        $statement->bind_param('ssi', $groupName, $groupDescription, $groupId);
        $statement->execute();
        $statement->close();
    }

    /**
     * @return array<int, string>
     */
    private function effectiveRolePermissionsForManager(users_o $manager, string $roleKey, bool $includePublicVisibility): array
    {
        $permissions = $includePublicVisibility ? self::MANAGED_EMPLOYEE_BASE_PERMISSIONS : ['user'];

        foreach (self::ROLE_PRESETS[$roleKey]['permissions'] ?? [] as $permission) {
            if (in_array($permission, self::MANAGED_EMPLOYEE_BASE_PERMISSIONS, true)) {
                $permissions[] = $permission;
                continue;
            }

            if (in_array($permission, self::ROLE_UNCONDITIONAL_PERMISSIONS, true)) {
                $permissions[] = $permission;
                continue;
            }

            if ($manager->hasPermission($permission)) {
                $permissions[] = $permission;
            }
        }

        return array_values(array_unique($permissions));
    }

    /**
     * @param array<int, int> $departmentIds
     * @return array<int, string>
     */
    private function permissionsForRoleAndDepartments(users_o $manager, string $roleKey, array $departmentIds): array
    {
        $permissions = $this->effectiveRolePermissionsForManager($manager, $roleKey, true);
        foreach ($departmentIds as $departmentId) {
            $permissions[] = 'department_access_' . $departmentId;
        }
        sort($permissions);
        return array_values(array_unique($permissions));
    }

    /**
     * @param array<int, string> $permissions
     */
    private function replaceGroupPermissions(int $groupId, array $permissions): void
    {
        if ($groupId <= 0 || $groupId === 1) {
            throw new \RuntimeException('Refusing to edit a protected group.');
        }

        $mysqli = $this->mysqli();
        $delete = $mysqli->prepare('DELETE FROM `groups_permissions` WHERE `group_id` = ?');
        if ($delete === false) {
            throw new \RuntimeException('Unable to prepare permission reset.');
        }
        $delete->bind_param('i', $groupId);
        $delete->execute();
        $delete->close();

        $insert = $mysqli->prepare('INSERT INTO `groups_permissions` (`group_id`, `permission`) VALUES (?, ?)');
        if ($insert === false) {
            throw new \RuntimeException('Unable to prepare permission insert.');
        }

        foreach ($permissions as $permission) {
            if ($permission === '' || str_contains($permission, "\0")) {
                continue;
            }
            $insert->bind_param('is', $groupId, $permission);
            $insert->execute();
        }

        $insert->close();
    }

    /**
     * @return array<string, mixed>|null
     */
    private function loadManagedEmployee(int $employeeId): ?array
    {
        global $db;
        $userDeletedAtSelect = $this->tableHasColumn('users', 'deleted_at')
            ? 'u.`deleted_at` AS `user_deleted_at`'
            : 'NULL AS `user_deleted_at`';

        $statement = $this->mysqli()->prepare(
            'SELECT
                lbe.*,
                u.`customer_number`,
                u.`display_name`,
                u.`email`,
                u.`phone_country_code`,
                u.`phone`,
                u.`group_id`,
                ' . $userDeletedAtSelect . '
            FROM `limited_backoffice_employees` lbe
            INNER JOIN `users` u ON u.`id` = lbe.`user_id`
            WHERE lbe.`user_id` = ?
            LIMIT 1'
        );
        if ($statement === false) {
            throw new limited_backoffice_exception('Unable to load employee.', 500);
        }
        $statement->bind_param('i', $employeeId);
        $statement->execute();
        $result = $statement->get_result();
        $row = $db->fetch_assoc($result);
        $statement->close();

        return is_array($row) ? $row : null;
    }

    /**
     * @return array<string, mixed>|null
     */
    private function loadMigratableUser(int $employeeId): ?array
    {
        global $db;
        $userDeletedAtSelect = $this->tableHasColumn('users', 'deleted_at')
            ? 'u.`deleted_at` AS `user_deleted_at`'
            : 'NULL AS `user_deleted_at`';

        $statement = $this->mysqli()->prepare(
            'SELECT
                u.`id`,
                u.`customer_number`,
                u.`display_name`,
                u.`email`,
                u.`phone_country_code`,
                u.`phone`,
                u.`group_id`,
                ' . $userDeletedAtSelect . '
            FROM `users` u
            WHERE u.`id` = ?
            LIMIT 1'
        );
        if ($statement === false) {
            throw new limited_backoffice_exception('Unable to load user.', 500);
        }
        $statement->bind_param('i', $employeeId);
        $statement->execute();
        $result = $statement->get_result();
        $row = $db->fetch_assoc($result);
        $statement->close();

        return is_array($row) ? $row : null;
    }

    /**
     * @param array<string, mixed> $row
     */
    private function isEmployeeRowActive(array $row): bool
    {
        return ($row['deactivated_at'] ?? null) === null && ($row['user_deleted_at'] ?? null) === null;
    }

    /**
     * @param array<string, mixed> $row
     * @param array<int, int> $departmentIds
     * @return array<string, mixed>
     */
    private function formatEmployee(array $row, array $departmentIds, bool $active): array
    {
        return [
            'id' => (int)$row['user_id'],
            'user_id' => (int)$row['user_id'],
            'customer_number' => (int)$row['customer_number'],
            'display_name' => (string)($row['display_name'] ?? ''),
            'email' => $row['email'] === null ? null : (string)$row['email'],
            'phone_country_code' => $row['phone_country_code'] === null ? null : (int)$row['phone_country_code'],
            'phone' => $row['phone'] === null ? null : (int)$row['phone'],
            'active' => $active,
            'role' => $this->rolePayload((string)$row['role_key']),
            'departments' => $this->departmentSummaries($departmentIds),
            'created_at' => $row['created_at'] ?? null,
            'updated_at' => $row['updated_at'] ?? null,
        ];
    }

    /**
     * @return array{key:string,label:string,description:string}
     */
    private function rolePayload(string $roleKey): array
    {
        $preset = self::ROLE_PRESETS[$roleKey] ?? self::ROLE_PRESETS['viewer'];
        return [
            'key' => $roleKey,
            'label' => $preset['label'],
            'description' => $preset['description'],
        ];
    }

    /**
     * @param array<int, int> $departmentIds
     * @return array<int, array{id:int,name:string}>
     */
    private function departmentSummaries(array $departmentIds): array
    {
        if ($departmentIds === []) {
            return [];
        }

        $in = implode(',', array_map('intval', $departmentIds));
        global $db;
        $rows = $db->fetch_all($db->query(
            "SELECT `id`, `name` FROM `departments` WHERE `id` IN ($in) ORDER BY `name` ASC, `id` ASC"
        ));

        return array_map(static fn(array $row): array => [
            'id' => (int)$row['id'],
            'name' => (string)$row['name'],
        ], $rows);
    }

    private function assertNotSelfEdit(users_o $manager, int $employeeId): void
    {
        if ((int)$manager->id === $employeeId) {
            throw new limited_backoffice_exception('Managers cannot edit themselves.', 403);
        }
    }

    /**
     * @param array<string, mixed> $employee
     */
    private function assertManagedTargetIsSafe(array $employee): void
    {
        $groupId = (int)($employee['group_id'] ?? 0);
        $managedGroupId = (int)($employee['managed_group_id'] ?? 0);

        if ($groupId === 1 || $managedGroupId === 1 || $this->groupHasPermission($managedGroupId, 'superuser')) {
            throw new limited_backoffice_exception('Cannot manage superuser accounts.', 403);
        }

        if ($this->isEmployeeRowActive($employee) && $groupId !== $managedGroupId) {
            throw new limited_backoffice_exception('Cannot manage employees assigned to shared or unmanaged groups.', 403);
        }

        if ($managedGroupId <= 0 || $this->groupUserCount($managedGroupId) > 1) {
            throw new limited_backoffice_exception('Cannot manage shared groups.', 403);
        }
    }

    /**
     * @param array<string, mixed> $target
     */
    private function assertMigrationTargetIsSafe(array $target): void
    {
        if ((int)($target['customer_number'] ?? -1) !== self::MANAGED_EMPLOYEE_CUSTOMER_NUMBER) {
            throw new limited_backoffice_exception('Only employee accounts with customer number 0 can be migrated.', 400);
        }

        $groupId = (int)($target['group_id'] ?? 0);
        if ($groupId === 1 || $this->groupHasPermission($groupId, 'superuser')) {
            throw new limited_backoffice_exception('Cannot migrate superuser accounts.', 403);
        }

        if (($target['user_deleted_at'] ?? null) !== null) {
            throw new limited_backoffice_exception('Cannot migrate inactive users.', 409);
        }
    }

    private function groupHasPermission(int $groupId, string $permission): bool
    {
        if ($groupId <= 0) {
            return false;
        }

        $statement = $this->mysqli()->prepare(
            'SELECT `id` FROM `groups_permissions` WHERE `group_id` = ? AND `permission` = ? LIMIT 1'
        );
        if ($statement === false) {
            return false;
        }
        $statement->bind_param('is', $groupId, $permission);
        $statement->execute();
        $result = $statement->get_result();
        $exists = $result->num_rows > 0;
        $statement->close();

        return $exists;
    }

    private function groupUserCount(int $groupId): int
    {
        $statement = $this->mysqli()->prepare('SELECT COUNT(*) AS `count` FROM `users` WHERE `group_id` = ?');
        if ($statement === false) {
            return 0;
        }
        $statement->bind_param('i', $groupId);
        $statement->execute();
        $result = $statement->get_result();
        $row = $result->fetch_assoc();
        $statement->close();

        return (int)($row['count'] ?? 0);
    }

    /**
     * @param array<string, mixed> $fields
     */
    private function updateUserFields(int $userId, array $fields): void
    {
        if ($fields === []) {
            return;
        }

        $assignments = [];
        $types = '';
        $values = [];
        foreach ($fields as $field => $value) {
            if (!in_array($field, ['display_name', 'email', 'password', 'group_id', 'deleted_at', 'phone_country_code', 'phone'], true)) {
                continue;
            }
            if ($value === null) {
                $assignments[] = '`' . $field . '` = NULL';
                continue;
            }
            $assignments[] = '`' . $field . '` = ?';
            if (is_int($value)) {
                $types .= 'i';
                $values[] = $value;
            } else {
                $types .= 's';
                $values[] = (string)$value;
            }
        }

        if ($assignments === []) {
            return;
        }

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

        $statement = $this->mysqli()->prepare($sql);
        if ($statement === false) {
            throw new \RuntimeException('Unable to prepare user update.');
        }
        $statement->bind_param($types, ...$values);
        $statement->execute();
        $statement->close();
    }

    private function deleteUserTokens(int $userId): void
    {
        global $db;
        $rows = $db->fetch_all($db->query('SELECT `token` FROM `tokens` WHERE `user_id` = ' . (int)$userId));
        foreach ($rows as $row) {
            $token = (string)($row['token'] ?? '');
            if ($token !== '' && defined('redis')) {
                try {
                    redis->clear_auth_session($token);
                    redis->delete('token_' . $token);
                } catch (\Throwable) {
                }
            }
        }
        $db->query('DELETE FROM `tokens` WHERE `user_id` = ' . (int)$userId);
        $this->clearUserSessionCache($userId);
    }

    private function clearUserSessionCache(int $userId): void
    {
        if (!defined('redis')) {
            return;
        }

        try {
            redis->clear_keys('perm:user:' . $userId . ':*');
        } catch (\Throwable) {
        }

        global $db;
        try {
            $rows = $db->fetch_all($db->query('SELECT `token` FROM `tokens` WHERE `user_id` = ' . (int)$userId));
            foreach ($rows as $row) {
                $token = (string)($row['token'] ?? '');
                if ($token !== '') {
                    redis->clear_auth_session($token);
                }
            }
        } catch (\Throwable) {
        }
    }
}
