<?php
declare(strict_types=1);

function branch_catalog_thai(): array
{
    return array(
        'srinakarin' => json_decode('"\u0e2a\u0e32\u0e02\u0e32\u0e28\u0e23\u0e35\u0e19\u0e04\u0e23\u0e34\u0e19\u0e17\u0e23\u0e4c"'),
        'bangna' => json_decode('"\u0e2a\u0e32\u0e02\u0e32\u0e1a\u0e32\u0e07\u0e19\u0e32"'),
        'ramintra' => json_decode('"\u0e2a\u0e32\u0e02\u0e32\u0e23\u0e32\u0e21\u0e2d\u0e34\u0e19\u0e17\u0e23\u0e32"'),
        'rachapruek' => json_decode('"\u0e2a\u0e32\u0e02\u0e32\u0e23\u0e32\u0e0a\u0e1e\u0e24\u0e01\u0e29\u0e4c"'),
    );
}

function canonical_service_branches(): array
{
    return array_values(array_map(static function (array $row): string {
        return (string) ($row['branch_name'] ?? '');
    }, branch_seed_rows()));
}

function branch_seed_rows(): array
{
    static $rows = null;

    if (is_array($rows)) {
        return $rows;
    }

    $catalog = branch_catalog_thai();
    $rows = array(
        array(
            'branch_code' => 'SRN',
            'branch_name' => (string) ($catalog['srinakarin'] ?? ''),
            'sort_order' => 10,
        ),
        array(
            'branch_code' => 'BNA',
            'branch_name' => (string) ($catalog['bangna'] ?? ''),
            'sort_order' => 20,
        ),
        array(
            'branch_code' => 'RMI',
            'branch_name' => (string) ($catalog['ramintra'] ?? ''),
            'sort_order' => 30,
        ),
        array(
            'branch_code' => 'RPK',
            'branch_name' => (string) ($catalog['rachapruek'] ?? ''),
            'sort_order' => 40,
        ),
    );

    return $rows;
}

function branch_seed_code_map(): array
{
    $map = array();

    foreach (branch_seed_rows() as $row) {
        $branchName = trim((string) ($row['branch_name'] ?? ''));
        $branchCode = normalize_branch_code((string) ($row['branch_code'] ?? ''));

        if ($branchName !== '' && $branchCode !== '') {
            $map[$branchName] = $branchCode;
        }
    }

    return $map;
}

function normalize_branch_code(?string $branchCode): string
{
    $branchCode = strtoupper((string) preg_replace('/[^A-Z0-9]+/', '', trim((string) $branchCode)));

    if ($branchCode === '') {
        return '';
    }

    return substr(str_pad($branchCode, 3, 'X'), 0, 3);
}

function branch_generated_code_from_name(string $branchName): string
{
    $ascii = @iconv('UTF-8', 'ASCII//TRANSLIT//IGNORE', $branchName);
    if (!is_string($ascii) || trim($ascii) === '') {
        $ascii = strtoupper(substr(hash('crc32b', $branchName), 0, 3));
    }

    $candidate = strtoupper((string) preg_replace('/[^A-Z0-9]+/', '', $ascii));
    if ($candidate === '') {
        $candidate = 'BRX';
    }

    return normalize_branch_code($candidate);
}

function branch_resolve_connection(?mysqli $conn = null): ?mysqli
{
    if ($conn instanceof mysqli) {
        return $conn;
    }

    $globalConnection = $GLOBALS['conn'] ?? null;

    return $globalConnection instanceof mysqli ? $globalConnection : null;
}

function branch_master_table_exists(mysqli $conn): bool
{
    $result = $conn->query("SHOW TABLES LIKE 'branches'");

    return $result instanceof mysqli_result && $result->num_rows > 0;
}

function branch_table_has_column(mysqli $conn, string $tableName, string $columnName): bool
{
    $tableName = trim($tableName);
    $columnName = trim($columnName);

    if ($tableName === '' || $columnName === '') {
        return false;
    }

    $result = $conn->query(
        "SHOW COLUMNS FROM `" . $conn->real_escape_string($tableName) . "` LIKE '" . $conn->real_escape_string($columnName) . "'"
    );

    return $result instanceof mysqli_result && $result->num_rows > 0;
}

function ensure_branch_master_schema(mysqli $conn): void
{
    static $checked = false;

    if ($checked) {
        return;
    }

    $checked = true;

    $conn->query("
        CREATE TABLE IF NOT EXISTS branches (
            branch_id INT AUTO_INCREMENT PRIMARY KEY,
            branch_code VARCHAR(8) NOT NULL,
            branch_name VARCHAR(120) NOT NULL,
            sort_order INT NOT NULL DEFAULT 100,
            is_active TINYINT(1) NOT NULL DEFAULT 1,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_branches_code (branch_code),
            UNIQUE KEY uniq_branches_name (branch_name),
            INDEX idx_branches_active_order (is_active, sort_order, branch_name)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    $columns = array(
        'branch_code' => "ALTER TABLE branches ADD COLUMN branch_code VARCHAR(8) NOT NULL DEFAULT '' AFTER branch_id",
        'branch_name' => "ALTER TABLE branches ADD COLUMN branch_name VARCHAR(120) NOT NULL DEFAULT '' AFTER branch_code",
        'sort_order' => "ALTER TABLE branches ADD COLUMN sort_order INT NOT NULL DEFAULT 100 AFTER branch_name",
        'is_active' => "ALTER TABLE branches ADD COLUMN is_active TINYINT(1) NOT NULL DEFAULT 1 AFTER sort_order",
        'created_at' => "ALTER TABLE branches ADD COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER is_active",
        'updated_at' => "ALTER TABLE branches ADD COLUMN updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at",
    );

    foreach ($columns as $columnName => $alterSql) {
        if (!branch_table_has_column($conn, 'branches', $columnName)) {
            $conn->query($alterSql);
        }
    }

    branch_master_seed_defaults($conn);
    branch_master_import_existing_names($conn);
}

function branch_master_seed_defaults(mysqli $conn): void
{
    $stmt = db_prepare($conn, "
        INSERT IGNORE INTO branches (branch_code, branch_name, sort_order, is_active)
        VALUES (?, ?, ?, 1)
    ");

    foreach (branch_seed_rows() as $seedRow) {
        $branchCode = normalize_branch_code((string) ($seedRow['branch_code'] ?? ''));
        $branchName = trim((string) ($seedRow['branch_name'] ?? ''));
        $sortOrder = (int) ($seedRow['sort_order'] ?? 100);

        if ($branchCode === '' || $branchName === '') {
            continue;
        }

        $stmt->bind_param('ssi', $branchCode, $branchName, $sortOrder);
        $stmt->execute();
    }

    $stmt->close();
}

function branch_master_import_existing_names(mysqli $conn): void
{
    if (!branch_master_table_exists($conn)) {
        return;
    }

    $tables = array(
        'orders',
        'audit_logs',
        'employee_branch_access',
        'quick_service_settings_profiles',
    );

    $knownNames = array_values(array_filter(array_map(static function (array $row): string {
        return trim((string) ($row['branch_name'] ?? ''));
    }, branch_master_rows($conn, true))));
    $knownCodes = array_values(array_filter(array_map(static function (array $row): string {
        return normalize_branch_code((string) ($row['branch_code'] ?? ''));
    }, branch_master_rows($conn, true))));

    $nextSortOrder = 1000;
    foreach (branch_master_rows($conn, true) as $row) {
        $nextSortOrder = max($nextSortOrder, (int) ($row['sort_order'] ?? 0) + 10);
    }

    $insertStmt = db_prepare($conn, "
        INSERT IGNORE INTO branches (branch_code, branch_name, sort_order, is_active)
        VALUES (?, ?, ?, 1)
    ");

    foreach ($tables as $tableName) {
        if (!branch_table_has_column($conn, $tableName, 'branch_name')) {
            continue;
        }

        $result = $conn->query("
            SELECT DISTINCT branch_name
            FROM {$tableName}
            WHERE branch_name IS NOT NULL AND branch_name <> ''
            ORDER BY branch_name ASC
        ");

        if (!($result instanceof mysqli_result)) {
            continue;
        }

        while ($row = $result->fetch_assoc()) {
            $branchName = normalize_branch_name((string) ($row['branch_name'] ?? ''));
            if ($branchName === '' || in_array($branchName, $knownNames, true)) {
                continue;
            }

            $branchCode = branch_generated_code_from_name($branchName);
            if (in_array($branchCode, $knownCodes, true)) {
                $hash = strtoupper(substr(hash('crc32b', $branchName), 0, 6));

                for ($offset = 0; $offset <= 3; $offset++) {
                    $candidate = normalize_branch_code(substr($hash, $offset, 3));
                    if ($candidate !== '' && !in_array($candidate, $knownCodes, true)) {
                        $branchCode = $candidate;
                        break;
                    }
                }
            }

            $insertStmt->bind_param('ssi', $branchCode, $branchName, $nextSortOrder);
            $insertStmt->execute();

            $knownNames[] = $branchName;
            $knownCodes[] = $branchCode;
            $nextSortOrder += 10;
        }
    }

    $insertStmt->close();
}

function branch_master_rows(mysqli $conn, bool $includeInactive = false): array
{
    ensure_branch_master_schema($conn);

    $rows = array();
    $where = $includeInactive ? '' : 'WHERE is_active = 1';
    $result = $conn->query("
        SELECT branch_id, branch_code, branch_name, sort_order, is_active
        FROM branches
        {$where}
        ORDER BY is_active DESC, sort_order ASC, branch_name ASC
    ");

    if (!($result instanceof mysqli_result)) {
        return $rows;
    }

    while ($row = $result->fetch_assoc()) {
        $rows[] = array(
            'branch_id' => (int) ($row['branch_id'] ?? 0),
            'branch_code' => normalize_branch_code((string) ($row['branch_code'] ?? '')),
            'branch_name' => trim((string) ($row['branch_name'] ?? '')),
            'sort_order' => (int) ($row['sort_order'] ?? 100),
            'is_active' => (int) ($row['is_active'] ?? 0) === 1 ? 1 : 0,
        );
    }

    return $rows;
}

function branch_master_row_by_id(mysqli $conn, int $branchId): ?array
{
    ensure_branch_master_schema($conn);

    if ($branchId <= 0) {
        return null;
    }

    $stmt = db_prepare($conn, "
        SELECT branch_id, branch_code, branch_name, sort_order, is_active
        FROM branches
        WHERE branch_id = ?
        LIMIT 1
    ");
    $stmt->bind_param('i', $branchId);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();
    $stmt->close();

    if (!$row) {
        return null;
    }

    return array(
        'branch_id' => (int) ($row['branch_id'] ?? 0),
        'branch_code' => normalize_branch_code((string) ($row['branch_code'] ?? '')),
        'branch_name' => trim((string) ($row['branch_name'] ?? '')),
        'sort_order' => (int) ($row['sort_order'] ?? 100),
        'is_active' => (int) ($row['is_active'] ?? 0) === 1 ? 1 : 0,
    );
}

function branch_master_active_count(mysqli $conn): int
{
    ensure_branch_master_schema($conn);

    $result = $conn->query("SELECT COUNT(*) AS total FROM branches WHERE is_active = 1");
    $row = $result instanceof mysqli_result ? $result->fetch_assoc() : null;

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

function branch_master_code_in_use(mysqli $conn, string $branchCode, int $excludeBranchId = 0): bool
{
    $branchCode = normalize_branch_code($branchCode);
    if ($branchCode === '') {
        return false;
    }

    if ($excludeBranchId > 0) {
        $stmt = db_prepare($conn, "
            SELECT branch_id
            FROM branches
            WHERE branch_code = ? AND branch_id <> ?
            LIMIT 1
        ");
        $stmt->bind_param('si', $branchCode, $excludeBranchId);
    } else {
        $stmt = db_prepare($conn, "
            SELECT branch_id
            FROM branches
            WHERE branch_code = ?
            LIMIT 1
        ");
        $stmt->bind_param('s', $branchCode);
    }

    $stmt->execute();
    $exists = $stmt->get_result()->fetch_assoc() !== null;
    $stmt->close();

    return $exists;
}

function branch_master_unique_code(mysqli $conn, string $baseCode, int $excludeBranchId = 0): string
{
    $baseCode = normalize_branch_code($baseCode);
    if ($baseCode === '') {
        $baseCode = 'BRX';
    }

    if (!branch_master_code_in_use($conn, $baseCode, $excludeBranchId)) {
        return $baseCode;
    }

    $alphabet = str_split('ABCDEFGHJKLMNPQRSTUVWXYZ23456789');
    $prefix = substr($baseCode, 0, 2);

    foreach ($alphabet as $suffix) {
        $candidate = normalize_branch_code($prefix . $suffix);
        if (!branch_master_code_in_use($conn, $candidate, $excludeBranchId)) {
            return $candidate;
        }
    }

    return normalize_branch_code(strtoupper(substr(hash('crc32b', $baseCode . '|' . microtime(true)), 0, 3)));
}

function branch_master_save(mysqli $conn, array $payload): int
{
    ensure_branch_master_schema($conn);

    $branchId = (int) ($payload['branch_id'] ?? 0);
    $branchName = normalize_branch_name((string) ($payload['branch_name'] ?? ''));
    $branchCodeInput = normalize_branch_code((string) ($payload['branch_code'] ?? ''));
    $sortOrder = (int) ($payload['sort_order'] ?? 100);
    $isActive = (int) ($payload['is_active'] ?? 1) === 1 ? 1 : 0;

    if ($branchName === '') {
        throw new RuntimeException('Branch name is required.');
    }

    if ($sortOrder < 0) {
        $sortOrder = 0;
    }

    $currentRow = $branchId > 0 ? branch_master_row_by_id($conn, $branchId) : null;
    if ($branchId > 0 && !$currentRow) {
        throw new RuntimeException('Branch not found.');
    }

    if ($isActive === 0 && $currentRow && (int) ($currentRow['is_active'] ?? 0) === 1 && branch_master_active_count($conn) <= 1) {
        throw new RuntimeException('At least one active branch is required.');
    }

    if ($isActive === 0 && $currentRow && (int) ($currentRow['is_active'] ?? 0) === 1) {
        branch_master_assert_can_deactivate($conn, (string) ($currentRow['branch_name'] ?? ''));
    }

    if ($branchCodeInput !== '') {
        if (branch_master_code_in_use($conn, $branchCodeInput, $branchId)) {
            throw new RuntimeException('Branch code already exists.');
        }
        $branchCode = $branchCodeInput;
    } else {
        $branchCode = branch_master_unique_code($conn, branch_generated_code_from_name($branchName), $branchId);
    }

    if ($branchId > 0) {
        $nameStmt = db_prepare($conn, "
            SELECT branch_id
            FROM branches
            WHERE branch_name = ? AND branch_id <> ?
            LIMIT 1
        ");
        $nameStmt->bind_param('si', $branchName, $branchId);
    } else {
        $nameStmt = db_prepare($conn, "
            SELECT branch_id
            FROM branches
            WHERE branch_name = ?
            LIMIT 1
        ");
        $nameStmt->bind_param('s', $branchName);
    }
    $nameStmt->execute();
    $nameExists = $nameStmt->get_result()->fetch_assoc() !== null;
    $nameStmt->close();

    if ($nameExists) {
        throw new RuntimeException('Branch name already exists.');
    }

    if ($branchId > 0) {
        $oldBranchName = (string) ($currentRow['branch_name'] ?? '');
        $stmt = db_prepare($conn, "
            UPDATE branches
            SET branch_code = ?, branch_name = ?, sort_order = ?, is_active = ?
            WHERE branch_id = ?
            LIMIT 1
        ");
        $stmt->bind_param('ssiii', $branchCode, $branchName, $sortOrder, $isActive, $branchId);
        $stmt->execute();
        $stmt->close();

        if ($oldBranchName !== '' && $branchName !== '' && $oldBranchName !== $branchName) {
            branch_master_propagate_name_change($conn, $oldBranchName, $branchName);
        }

        return $branchId;
    }

    $stmt = db_prepare($conn, "
        INSERT INTO branches (branch_code, branch_name, sort_order, is_active)
        VALUES (?, ?, ?, ?)
    ");
    $stmt->bind_param('ssii', $branchCode, $branchName, $sortOrder, $isActive);
    $stmt->execute();
    $newBranchId = (int) $conn->insert_id;
    $stmt->close();

    return $newBranchId;
}

function branch_master_usage_counts(mysqli $conn, string $branchName): array
{
    $branchName = normalize_branch_name($branchName);
    if ($branchName === '') {
        return array(
            'orders_total' => 0,
            'orders_open' => 0,
            'employees_assigned' => 0,
            'audit_logs_total' => 0,
        );
    }

    $countFor = static function (string $tableName, string $whereClause) use ($conn, $branchName): int {
        if (!branch_table_has_column($conn, $tableName, 'branch_name')) {
            return 0;
        }

        $stmt = db_prepare($conn, "
            SELECT COUNT(*) AS total
            FROM {$tableName}
            WHERE {$whereClause}
        ");
        $stmt->bind_param('s', $branchName);
        $stmt->execute();
        $row = $stmt->get_result()->fetch_assoc();
        $stmt->close();

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

    return array(
        'orders_total' => $countFor('orders', 'branch_name = ?'),
        'orders_open' => $countFor('orders', "branch_name = ? AND status IN ('waiting', 'washing')"),
        'employees_assigned' => $countFor('employee_branch_access', 'branch_name = ?'),
        'audit_logs_total' => $countFor('audit_logs', 'branch_name = ?'),
    );
}

function branch_master_propagate_name_change(mysqli $conn, string $oldBranchName, string $newBranchName): void
{
    $oldBranchName = normalize_branch_name($oldBranchName);
    $newBranchName = normalize_branch_name($newBranchName);

    if ($oldBranchName === '' || $newBranchName === '' || $oldBranchName === $newBranchName) {
        return;
    }

    $tables = array(
        'orders',
        'audit_logs',
        'employee_branch_access',
        'quick_service_settings_profiles',
    );

    foreach ($tables as $tableName) {
        if (!branch_table_has_column($conn, $tableName, 'branch_name')) {
            continue;
        }

        $stmt = db_prepare($conn, "
            UPDATE {$tableName}
            SET branch_name = ?
            WHERE branch_name = ?
        ");
        $stmt->bind_param('ss', $newBranchName, $oldBranchName);
        $stmt->execute();
        $stmt->close();
    }
}

function branch_master_assert_can_deactivate(mysqli $conn, string $branchName): void
{
    $usage = branch_master_usage_counts($conn, $branchName);
    $openOrders = (int) ($usage['orders_open'] ?? 0);

    if ($openOrders > 0) {
        throw new RuntimeException('Cannot deactivate branch while it has open jobs.');
    }
}

function branch_name_replacements(): array
{
    static $cache = null;

    if (is_array($cache)) {
        return $cache;
    }

    $cache = array();
    $seedRows = branch_seed_rows();
    $targetByCode = array();

    $resolvedConnection = branch_resolve_connection();
    if ($resolvedConnection instanceof mysqli) {
        foreach (branch_master_rows($resolvedConnection, true) as $row) {
            $branchCode = normalize_branch_code((string) ($row['branch_code'] ?? ''));
            $branchName = trim((string) ($row['branch_name'] ?? ''));
            if ($branchCode !== '' && $branchName !== '') {
                $targetByCode[$branchCode] = $branchName;
            }
        }
    }

    foreach ($seedRows as $seedRow) {
        $seedCode = normalize_branch_code((string) ($seedRow['branch_code'] ?? ''));
        $seedName = trim((string) ($seedRow['branch_name'] ?? ''));

        if ($seedCode === '' || $seedName === '') {
            continue;
        }

        $targetName = $targetByCode[$seedCode] ?? $seedName;
        $cache[$targetName] = $targetName;
        $cache[strtolower($seedCode)] = $targetName;
        $cache[$seedCode] = $targetName;

        if ($seedCode === 'SRN') {
            $cache[json_decode('"\u0e28\u0e23\u0e35\u0e19\u0e04\u0e23\u0e34\u0e19\u0e17\u0e23\u0e4c"')] = $targetName;
            $cache['srinakarin'] = $targetName;
            $cache['srinakharin'] = $targetName;
        } elseif ($seedCode === 'BNA') {
            $cache[json_decode('"\u0e1a\u0e32\u0e07\u0e19\u0e32"')] = $targetName;
            $cache['bangna'] = $targetName;
        } elseif ($seedCode === 'RMI') {
            $cache[json_decode('"\u0e23\u0e32\u0e21\u0e2d\u0e34\u0e19\u0e17\u0e23\u0e32"')] = $targetName;
            $cache['ramintra'] = $targetName;
            $cache['ramindra'] = $targetName;
        } elseif ($seedCode === 'RPK') {
            $cache[json_decode('"\u0e23\u0e32\u0e0a\u0e1e\u0e24\u0e01\u0e29\u0e4c"')] = $targetName;
            $cache['rachapruek'] = $targetName;
            $cache['ratchapruek'] = $targetName;
            $cache['ratchaphruek'] = $targetName;
            $cache['rajapruek'] = $targetName;
        }
    }

    if ($resolvedConnection instanceof mysqli) {
        foreach (branch_master_rows($resolvedConnection, true) as $row) {
            $branchName = trim((string) ($row['branch_name'] ?? ''));
            if ($branchName !== '') {
                $cache[$branchName] = $branchName;
            }
        }
    }

    return $cache;
}

function normalize_branch_name(?string $branchName): string
{
    $branchName = trim((string) $branchName);

    if ($branchName === '') {
        return '';
    }

    $replacements = branch_name_replacements();

    if (isset($replacements[$branchName])) {
        return $replacements[$branchName];
    }

    $asciiKey = strtolower($branchName);

    return $replacements[$asciiKey] ?? $branchName;
}

function normalize_branch_names_in_database(mysqli $conn): void
{
    static $normalized = false;

    if ($normalized) {
        return;
    }

    ensure_branch_master_schema($conn);

    $replacements = branch_name_replacements();
    $tables = array('orders', 'audit_logs', 'quick_service_settings_profiles', 'employee_branch_access', 'branches');

    foreach ($tables as $tableName) {
        if (!branch_table_has_column($conn, $tableName, 'branch_name')) {
            continue;
        }

        foreach ($replacements as $sourceName => $targetName) {
            if ($sourceName === $targetName) {
                continue;
            }

            $stmt = db_prepare($conn, "UPDATE {$tableName} SET branch_name = ? WHERE branch_name = ?");

            if ($stmt === false) {
                continue;
            }

            $stmt->bind_param('ss', $targetName, $sourceName);
            $stmt->execute();
            $stmt->close();
        }
    }

    if (branch_table_has_column($conn, 'branches', 'branch_code')) {
        $rows = branch_master_rows($conn, true);
        $updateStmt = db_prepare($conn, "
            UPDATE branches
            SET branch_code = ?
            WHERE branch_id = ?
            LIMIT 1
        ");

        foreach ($rows as $row) {
            $branchId = (int) ($row['branch_id'] ?? 0);
            $branchName = (string) ($row['branch_name'] ?? '');
            $branchCode = normalize_branch_code((string) ($row['branch_code'] ?? ''));

            if ($branchId <= 0 || $branchCode !== '') {
                continue;
            }

            $branchCode = branch_master_unique_code($conn, branch_generated_code_from_name($branchName), $branchId);
            $updateStmt->bind_param('si', $branchCode, $branchId);
            $updateStmt->execute();
        }

        $updateStmt->close();
    }

    branch_master_seed_defaults($conn);
    branch_master_import_existing_names($conn);

    $normalized = true;
}

function service_branches(?mysqli $conn = null, bool $includeInactive = false): array
{
    $resolvedConnection = branch_resolve_connection($conn);

    if ($resolvedConnection instanceof mysqli) {
        $rows = branch_master_rows($resolvedConnection, $includeInactive);
        $branchNames = array_values(array_filter(array_map(static function (array $row): string {
            return trim((string) ($row['branch_name'] ?? ''));
        }, $rows)));

        if (!$includeInactive && empty($branchNames)) {
            $rows = branch_master_rows($resolvedConnection, true);
            $branchNames = array_values(array_filter(array_map(static function (array $row): string {
                return trim((string) ($row['branch_name'] ?? ''));
            }, $rows)));
        }

        if (!empty($branchNames)) {
            return $branchNames;
        }
    }

    return canonical_service_branches();
}

function normalized_branch_options(array $branchNames): array
{
    $normalized = array();

    foreach ($branchNames as $branchName) {
        $branchName = normalize_branch_name((string) $branchName);

        if ($branchName === '' || in_array($branchName, $normalized, true)) {
            continue;
        }

        $normalized[] = $branchName;
    }

    return $normalized;
}

function distinct_branch_options_from_table(mysqli $conn, string $tableName): array
{
    return normalized_branch_options(service_branches($conn));
}

function order_branch_options(mysqli $conn): array
{
    return distinct_branch_options_from_table($conn, 'orders');
}

function authorized_order_branch_options(mysqli $conn): array
{
    static $cache = null;

    if (is_array($cache)) {
        return $cache;
    }

    $cache = auth_filter_branch_options($conn, order_branch_options($conn));

    return $cache;
}

function audit_branch_options(mysqli $conn): array
{
    return distinct_branch_options_from_table($conn, 'audit_logs');
}

function authorized_audit_branch_options(mysqli $conn): array
{
    static $cache = null;

    if (is_array($cache)) {
        return $cache;
    }

    $cache = auth_filter_branch_options($conn, audit_branch_options($conn));

    return $cache;
}
