<?php
declare(strict_types=1);

function service_catalog_text(string $thaiText, string $englishText): string
{
    if (function_exists('localized_text_value')) {
        return localized_text_value($thaiText, $englishText);
    }

    if (function_exists('ui_text')) {
        return ui_text($thaiText, $englishText);
    }

    return $englishText;
}

function service_type_options(): array
{
    return array(
        'main_service' => service_catalog_text('บริการหลัก', 'Main Service'),
        'sub_service' => service_catalog_text('บริการย่อย', 'Sub Service'),
        'package' => service_catalog_text('แพ็กเกจ', 'Package'),
    );
}

function package_vehicle_scope_options(): array
{
    return array(
        'member' => service_catalog_text('รถทุกคันของสมาชิก', 'All member vehicles'),
        'car' => service_catalog_text('เฉพาะรถที่ซื้อแพ็กเกจ', 'Only purchasing vehicle'),
    );
}

function normalize_package_vehicle_scope(string $scope): string
{
    return array_key_exists($scope, package_vehicle_scope_options()) ? $scope : 'member';
}

function package_vehicle_scope_label(string $scope): string
{
    $scope = normalize_package_vehicle_scope($scope);

    return package_vehicle_scope_options()[$scope];
}

function package_vehicle_scope_note(array $row): string
{
    $scope = normalize_package_vehicle_scope((string) ($row['vehicle_scope'] ?? $row['package_vehicle_scope'] ?? 'member'));

    if ($scope !== 'car') {
        return package_vehicle_scope_label($scope);
    }

    $plateNumber = trim((string) ($row['plate_number'] ?? ''));

    return $plateNumber !== ''
        ? strtr(service_catalog_text('เฉพาะรถ {plate}', 'Only vehicle {plate}'), array('{plate}' => $plateNumber))
        : package_vehicle_scope_label($scope);
}

function service_type_sort_rank(string $serviceType): int
{
    if ($serviceType === 'main_service') {
        return 1;
    }

    if ($serviceType === 'sub_service') {
        return 2;
    }

    if ($serviceType === 'package') {
        return 3;
    }

    return 9;
}

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

    if ($checked) {
        return;
    }

    if (runtime_schema_bootstrap_is_current()) {
        $checked = true;

        return;
    }

    $checked = true;

    $conn->query("
        CREATE TABLE IF NOT EXISTS service_groups (
            group_id INT AUTO_INCREMENT PRIMARY KEY,
            category_id INT NOT NULL,
            main_service_id INT NULL,
            group_name VARCHAR(80) NOT NULL,
            show_window_film_details TINYINT(1) NOT NULL DEFAULT 0,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_service_group_main (main_service_id, group_name),
            INDEX idx_service_groups_category_id (category_id),
            INDEX idx_service_groups_main_service_id (main_service_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    $serviceGroupColumns = array(
        'main_service_id' => "ALTER TABLE service_groups ADD COLUMN main_service_id INT NULL AFTER category_id",
        'show_window_film_details' => "ALTER TABLE service_groups ADD COLUMN show_window_film_details TINYINT(1) NOT NULL DEFAULT 0 AFTER group_name",
    );

    foreach ($serviceGroupColumns as $columnName => $alterSql) {
        $columnResult = $conn->query("SHOW COLUMNS FROM service_groups LIKE '" . $conn->real_escape_string($columnName) . "'");

        if (!($columnResult instanceof mysqli_result) || $columnResult->num_rows === 0) {
            if (!$conn->query($alterSql)) {
                throw new RuntimeException('Cannot add ' . $columnName . ' column to service_groups: ' . $conn->error);
            }
        }
    }

    $legacyGroupIndexResult = $conn->query("SHOW INDEX FROM service_groups WHERE Key_name = 'uniq_service_group_name'");
    if ($legacyGroupIndexResult instanceof mysqli_result && $legacyGroupIndexResult->num_rows > 0) {
        $conn->query("ALTER TABLE service_groups DROP INDEX uniq_service_group_name");
    }

    $serviceGroupIndexes = array(
        'uniq_service_group_main' => 'ALTER TABLE service_groups ADD UNIQUE KEY uniq_service_group_main (main_service_id, group_name)',
        'idx_service_groups_category_id' => 'ALTER TABLE service_groups ADD INDEX idx_service_groups_category_id (category_id)',
        'idx_service_groups_main_service_id' => 'ALTER TABLE service_groups ADD INDEX idx_service_groups_main_service_id (main_service_id)',
    );

    foreach ($serviceGroupIndexes as $indexName => $alterSql) {
        $indexResult = $conn->query("SHOW INDEX FROM service_groups WHERE Key_name = '" . $conn->real_escape_string($indexName) . "'");

        if (!($indexResult instanceof mysqli_result) || $indexResult->num_rows === 0) {
            $conn->query($alterSql);
        }
    }

    $serviceColumns = array(
        'category_id' => "ALTER TABLE services ADD COLUMN category_id INT NULL AFTER category",
        'main_service_id' => "ALTER TABLE services ADD COLUMN main_service_id INT NULL AFTER category_id",
        'group_id' => "ALTER TABLE services ADD COLUMN group_id INT NULL AFTER main_service_id",
        'package_vehicle_scope' => "ALTER TABLE services ADD COLUMN package_vehicle_scope ENUM('member', 'car') NOT NULL DEFAULT 'member' AFTER group_id",
        'is_quick_service' => "ALTER TABLE services ADD COLUMN is_quick_service TINYINT(1) NOT NULL DEFAULT 0 AFTER package_vehicle_scope",
        'service_type' => "ALTER TABLE services ADD COLUMN service_type ENUM('main_service', 'sub_service', 'package') NOT NULL DEFAULT 'sub_service' AFTER service_name",
    );

    foreach ($serviceColumns as $columnName => $alterSql) {
        $columnResult = $conn->query("SHOW COLUMNS FROM services LIKE '" . $conn->real_escape_string($columnName) . "'");

        if (!($columnResult instanceof mysqli_result) || $columnResult->num_rows === 0) {
            if (!$conn->query($alterSql)) {
                throw new RuntimeException('Cannot add ' . $columnName . ' column to services: ' . $conn->error);
            }
        }
    }

    $conn->query("
        ALTER TABLE services
        MODIFY COLUMN service_type ENUM('main_service', 'sub_service', 'package') NOT NULL DEFAULT 'sub_service',
        MODIFY COLUMN package_vehicle_scope ENUM('member', 'car') NOT NULL DEFAULT 'member',
        MODIFY COLUMN is_quick_service TINYINT(1) NOT NULL DEFAULT 0
    ");

    $serviceIndexes = array(
        'idx_services_category_id' => 'ALTER TABLE services ADD INDEX idx_services_category_id (category_id)',
        'idx_services_main_service_id' => 'ALTER TABLE services ADD INDEX idx_services_main_service_id (main_service_id)',
        'idx_services_group_id' => 'ALTER TABLE services ADD INDEX idx_services_group_id (group_id)',
        'idx_services_service_type' => 'ALTER TABLE services ADD INDEX idx_services_service_type (service_type)',
        'idx_services_quick_service' => 'ALTER TABLE services ADD INDEX idx_services_quick_service (is_quick_service)',
    );

    foreach ($serviceIndexes as $indexName => $alterSql) {
        $indexResult = $conn->query("SHOW INDEX FROM services WHERE Key_name = '" . $conn->real_escape_string($indexName) . "'");

        if (!($indexResult instanceof mysqli_result) || $indexResult->num_rows === 0) {
            $conn->query($alterSql);
        }
    }

    $conn->query("
        CREATE TABLE IF NOT EXISTS service_package_items (
            package_item_id INT AUTO_INCREMENT PRIMARY KEY,
            package_service_id INT NOT NULL,
            sub_service_id INT NOT NULL,
            included_uses INT NOT NULL DEFAULT 1,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_package_service_sub_service (package_service_id, sub_service_id),
            INDEX idx_package_items_package_service (package_service_id),
            INDEX idx_package_items_sub_service (sub_service_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    $conn->query("
        CREATE TABLE IF NOT EXISTS customer_packages (
            customer_package_id INT AUTO_INCREMENT PRIMARY KEY,
            customer_id INT NOT NULL,
            car_id INT NULL,
            vehicle_scope ENUM('member', 'car') NOT NULL DEFAULT 'member',
            package_service_id INT NOT NULL,
            order_id INT NOT NULL,
            package_name_snapshot VARCHAR(120) NOT NULL,
            status ENUM('active', 'cancelled') NOT NULL DEFAULT 'active',
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            cancelled_at DATETIME NULL,
            INDEX idx_customer_packages_customer (customer_id),
            INDEX idx_customer_packages_car (car_id),
            INDEX idx_customer_packages_order (order_id),
            INDEX idx_customer_packages_status (status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    $customerPackageColumns = array(
        'car_id' => "ALTER TABLE customer_packages ADD COLUMN car_id INT NULL AFTER customer_id",
        'vehicle_scope' => "ALTER TABLE customer_packages ADD COLUMN vehicle_scope ENUM('member', 'car') NOT NULL DEFAULT 'member' AFTER car_id",
    );

    foreach ($customerPackageColumns as $columnName => $alterSql) {
        $columnResult = $conn->query("SHOW COLUMNS FROM customer_packages LIKE '" . $conn->real_escape_string($columnName) . "'");

        if (!($columnResult instanceof mysqli_result) || $columnResult->num_rows === 0) {
            if (!$conn->query($alterSql)) {
                throw new RuntimeException('Cannot add ' . $columnName . ' column to customer_packages: ' . $conn->error);
            }
        }
    }

    $customerPackageIndexes = array(
        'idx_customer_packages_car' => 'ALTER TABLE customer_packages ADD INDEX idx_customer_packages_car (car_id)',
    );

    foreach ($customerPackageIndexes as $indexName => $alterSql) {
        $indexResult = $conn->query("SHOW INDEX FROM customer_packages WHERE Key_name = '" . $conn->real_escape_string($indexName) . "'");

        if (!($indexResult instanceof mysqli_result) || $indexResult->num_rows === 0) {
            $conn->query($alterSql);
        }
    }

    $conn->query("
        CREATE TABLE IF NOT EXISTS customer_package_items (
            customer_package_item_id INT AUTO_INCREMENT PRIMARY KEY,
            customer_package_id INT NOT NULL,
            sub_service_id INT NOT NULL,
            service_name_snapshot VARCHAR(120) NOT NULL,
            included_uses INT NOT NULL DEFAULT 1,
            used_uses INT NOT NULL DEFAULT 0,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_customer_package_items_package (customer_package_id),
            INDEX idx_customer_package_items_sub_service (sub_service_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    $conn->query("
        CREATE TABLE IF NOT EXISTS order_package_usages (
            usage_id INT AUTO_INCREMENT PRIMARY KEY,
            order_id INT NOT NULL,
            customer_package_item_id INT NOT NULL,
            service_id INT NOT NULL,
            service_name_snapshot VARCHAR(120) NOT NULL,
            used_uses INT NOT NULL DEFAULT 1,
            is_reversed TINYINT(1) NOT NULL DEFAULT 0,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            reversed_at DATETIME NULL,
            INDEX idx_order_package_usages_order (order_id),
            INDEX idx_order_package_usages_package_item (customer_package_item_id),
            INDEX idx_order_package_usages_service (service_id),
            INDEX idx_order_package_usages_reversed (is_reversed)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    $conn->query("
        UPDATE services s
        INNER JOIN service_categories sc ON sc.category_name = s.category
        SET s.category_id = sc.category_id
        WHERE (s.category_id IS NULL OR s.category_id = 0)
            AND s.category IS NOT NULL
            AND s.category <> ''
    ");

    $conn->query("
        UPDATE services s
        INNER JOIN services ms ON ms.service_id = s.main_service_id
        LEFT JOIN service_categories sc ON sc.category_id = ms.category_id
        SET s.category_id = COALESCE(s.category_id, ms.category_id),
            s.category = COALESCE(sc.category_name, s.category)
        WHERE s.main_service_id IS NOT NULL
    ");

    $conn->query("
        UPDATE services s
        INNER JOIN service_groups sg ON sg.group_id = s.group_id
        SET s.main_service_id = COALESCE(s.main_service_id, sg.main_service_id),
            s.category_id = COALESCE(s.category_id, sg.category_id)
        WHERE s.group_id IS NOT NULL
    ");

    $conn->query("
        UPDATE service_groups sg
        INNER JOIN services ms ON ms.service_id = sg.main_service_id
        SET sg.category_id = COALESCE(ms.category_id, sg.category_id)
        WHERE sg.main_service_id IS NOT NULL
    ");

    $conn->query("
        UPDATE services s
        LEFT JOIN service_categories sc ON sc.category_id = s.category_id
        SET s.category = COALESCE(sc.category_name, s.category)
        WHERE s.category_id IS NOT NULL
    ");

    $conn->query("
        UPDATE customer_packages cp
        INNER JOIN services s ON s.service_id = cp.package_service_id
        SET cp.vehicle_scope = COALESCE(cp.vehicle_scope, s.package_vehicle_scope)
        WHERE cp.vehicle_scope IS NULL OR cp.vehicle_scope = ''
    ");

    $conn->query("
        UPDATE customer_packages cp
        INNER JOIN orders o ON o.order_id = cp.order_id
        SET cp.car_id = COALESCE(cp.car_id, o.car_id)
        WHERE cp.car_id IS NULL OR cp.car_id = 0
    ");
}

function service_row_is_film(array $service): bool
{
    $fields = array(
        (string) ($service['service_name'] ?? ''),
        (string) ($service['main_service_name'] ?? ''),
        (string) ($service['category_name'] ?? $service['category'] ?? ''),
        (string) ($service['group_name'] ?? ''),
    );

    foreach ($fields as $field) {
        if ($field !== '' && order_option_is_film_service_name($field)) {
            return true;
        }
    }

    return false;
}

function service_name_compare(array $left, array $right): int
{
    $leftName = trim((string) ($left['service_name'] ?? $left['group_name'] ?? ''));
    $rightName = trim((string) ($right['service_name'] ?? $right['group_name'] ?? ''));

    return strcasecmp($leftName, $rightName);
}

function service_group_options(mysqli $conn, int $categoryId = 0, int $mainServiceId = 0): array
{
    ensure_service_catalog_schema($conn);

    $sql = "
        SELECT
            sg.group_id,
            sg.category_id,
            sg.main_service_id,
            sg.group_name,
            sg.show_window_film_details,
            sc.category_name,
            ms.service_name AS main_service_name,
            COUNT(s.service_id) AS service_count
        FROM service_groups sg
        LEFT JOIN service_categories sc ON sc.category_id = sg.category_id
        LEFT JOIN services ms ON ms.service_id = sg.main_service_id
        LEFT JOIN services s ON s.group_id = sg.group_id AND s.service_type = 'sub_service'
    ";
    $where = array();
    $params = array();
    $types = '';

    if ($categoryId > 0) {
        $where[] = 'sg.category_id = ?';
        $params[] = $categoryId;
        $types .= 'i';
    }

    if ($mainServiceId > 0) {
        $where[] = 'sg.main_service_id = ?';
        $params[] = $mainServiceId;
        $types .= 'i';
    }

    if (!empty($where)) {
        $sql .= ' WHERE ' . implode(' AND ', $where);
    }

    $sql .= "
        GROUP BY
            sg.group_id,
            sg.category_id,
            sg.main_service_id,
            sg.group_name,
            sg.show_window_film_details,
            sc.category_name,
            ms.service_name
        ORDER BY
            COALESCE(sc.category_name, '') ASC,
            COALESCE(ms.service_name, '') ASC,
            sg.group_name ASC
    ";

    if ($types === '') {
        $result = $conn->query($sql);
    } else {
        $stmt = db_prepare($conn, $sql);
        $stmt->bind_param($types, ...$params);
        $stmt->execute();
        $result = $stmt->get_result();
    }

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

    $groups = array();

    while ($row = $result->fetch_assoc()) {
        $row['group_id'] = (int) ($row['group_id'] ?? 0);
        $row['category_id'] = (int) ($row['category_id'] ?? 0);
        $row['main_service_id'] = (int) ($row['main_service_id'] ?? 0);
        $row['service_count'] = (int) ($row['service_count'] ?? 0);
        $row['group_name'] = (string) ($row['group_name'] ?? '');
        $row['show_window_film_details'] = (int) ($row['show_window_film_details'] ?? 0);
        $row['category_name'] = (string) ($row['category_name'] ?? '');
        $row['main_service_name'] = (string) ($row['main_service_name'] ?? '');
        $groups[] = $row;
    }

    if (isset($stmt)) {
        $stmt->close();
    }

    return $groups;
}

function service_catalog_rows(mysqli $conn, array $options = array()): array
{
    ensure_service_catalog_schema($conn);

    $where = array();
    $params = array();
    $types = '';

    $serviceTypes = array();
    $singleServiceType = trim((string) ($options['service_type'] ?? ''));
    if ($singleServiceType !== '') {
        $serviceTypes[] = $singleServiceType;
    }

    foreach ((array) ($options['service_types'] ?? array()) as $serviceType) {
        $serviceType = trim((string) $serviceType);

        if ($serviceType !== '') {
            $serviceTypes[] = $serviceType;
        }
    }

    $serviceTypes = array_values(array_unique($serviceTypes));
    if (!empty($serviceTypes)) {
        $where[] = 's.service_type IN (' . implode(', ', array_fill(0, count($serviceTypes), '?')) . ')';
        foreach ($serviceTypes as $serviceType) {
            $params[] = $serviceType;
            $types .= 's';
        }
    }

    $serviceIds = array_values(array_unique(array_filter(array_map('intval', (array) ($options['service_ids'] ?? array())))));
    $includeInactiveIds = array_values(array_unique(array_filter(array_map('intval', (array) ($options['include_inactive_ids'] ?? array())))));
    $activeOnly = !empty($options['active_only']);
    $quickServiceOnly = !empty($options['quick_service_only']);

    if (!empty($serviceIds)) {
        $where[] = 's.service_id IN (' . implode(',', $serviceIds) . ')';
    }

    if ($activeOnly) {
        if (!empty($includeInactiveIds)) {
            $where[] = '(s.is_active = 1 OR s.service_id IN (' . implode(',', $includeInactiveIds) . '))';
        } else {
            $where[] = 's.is_active = 1';
        }
    }

    if ($quickServiceOnly) {
        $where[] = 's.is_quick_service = 1';
    }

    $categoryId = (int) ($options['category_id'] ?? 0);
    if ($categoryId > 0) {
        $where[] = 's.category_id = ?';
        $params[] = $categoryId;
        $types .= 'i';
    }

    $mainServiceId = (int) ($options['main_service_id'] ?? 0);
    if ($mainServiceId > 0) {
        $where[] = 's.main_service_id = ?';
        $params[] = $mainServiceId;
        $types .= 'i';
    }

    $groupId = (int) ($options['group_id'] ?? 0);
    if ($groupId > 0) {
        $where[] = 's.group_id = ?';
        $params[] = $groupId;
        $types .= 'i';
    }

    $search = trim((string) ($options['search'] ?? ''));
    if ($search !== '') {
        $searchLike = '%' . $search . '%';
        $where[] = '('
            . 's.service_name LIKE ?'
            . ' OR COALESCE(s.description, \'\') LIKE ?'
            . ' OR COALESCE(sc.category_name, s.category, \'\') LIKE ?'
            . ' OR COALESCE(ms.service_name, \'\') LIKE ?'
            . ' OR COALESCE(sg.group_name, \'\') LIKE ?'
            . ')';
        array_push($params, $searchLike, $searchLike, $searchLike, $searchLike, $searchLike);
        $types .= 'sssss';
    }

    $sql = "
        SELECT
            s.service_id,
            s.service_name,
            s.service_type,
            s.category,
            s.category_id,
            s.main_service_id,
            s.group_id,
            s.package_vehicle_scope,
            s.is_quick_service,
            s.price,
            0 AS duration_minutes,
            s.description,
            s.is_active,
            sc.category_name,
            CASE
                WHEN s.service_type = 'main_service' THEN s.service_name
                ELSE COALESCE(ms.service_name, '')
            END AS main_service_name,
            sg.group_name,
            COUNT(od.detail_id) AS usage_count,
            (
                SELECT COUNT(*)
                FROM services child
                WHERE child.main_service_id = s.service_id
                    AND child.service_type = 'sub_service'
            ) AS linked_sub_service_count,
            (
                SELECT COUNT(*)
                FROM service_groups child_group
                WHERE child_group.main_service_id = s.service_id
            ) AS linked_group_count
        FROM services s
        LEFT JOIN service_categories sc ON sc.category_id = s.category_id
        LEFT JOIN services ms ON ms.service_id = s.main_service_id
        LEFT JOIN service_groups sg ON sg.group_id = s.group_id
        LEFT JOIN order_details od ON od.service_id = s.service_id
    ";

    if (!empty($where)) {
        $sql .= ' WHERE ' . implode(' AND ', $where);
    }

    $sql .= "
        GROUP BY
            s.service_id,
            s.service_name,
            s.service_type,
            s.category,
            s.category_id,
            s.main_service_id,
            s.group_id,
            s.package_vehicle_scope,
            s.is_quick_service,
            s.price,
            s.description,
            s.is_active,
            sc.category_name,
            ms.service_name,
            sg.group_name
        ORDER BY
            CASE s.service_type
                WHEN 'main_service' THEN 1
                WHEN 'sub_service' THEN 2
                WHEN 'package' THEN 3
                ELSE 9
            END ASC,
            COALESCE(sc.category_name, s.category) ASC,
            CASE
                WHEN s.service_type = 'main_service' THEN s.service_name
                ELSE COALESCE(ms.service_name, '')
            END ASC,
            COALESCE(sg.group_name, '') ASC,
            s.service_name ASC
    ";

    if ($types === '') {
        $result = $conn->query($sql);
    } else {
        $stmt = db_prepare($conn, $sql);
        $stmt->bind_param($types, ...$params);
        $stmt->execute();
        $result = $stmt->get_result();
    }

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

    $rows = array();

    while ($row = $result->fetch_assoc()) {
        $row['service_id'] = (int) ($row['service_id'] ?? 0);
        $row['category_id'] = (int) ($row['category_id'] ?? 0);
        $row['main_service_id'] = (int) ($row['main_service_id'] ?? 0);
        $row['group_id'] = (int) ($row['group_id'] ?? 0);
        $row['package_vehicle_scope'] = normalize_package_vehicle_scope((string) ($row['package_vehicle_scope'] ?? 'member'));
        $row['is_quick_service'] = (int) ($row['is_quick_service'] ?? 0);
        $row['price'] = (float) ($row['price'] ?? 0);
        $row['duration_minutes'] = 0;
        $row['is_active'] = (int) ($row['is_active'] ?? 0);
        $row['usage_count'] = (int) ($row['usage_count'] ?? 0);
        $row['linked_sub_service_count'] = (int) ($row['linked_sub_service_count'] ?? 0);
        $row['linked_group_count'] = (int) ($row['linked_group_count'] ?? 0);
        $row['category_name'] = trim((string) ($row['category_name'] ?? $row['category'] ?? ''));
        $row['main_service_name'] = trim((string) ($row['main_service_name'] ?? ''));
        $row['group_name'] = trim((string) ($row['group_name'] ?? ''));

        if ((string) ($row['service_type'] ?? '') === 'main_service' && $row['main_service_name'] === '') {
            $row['main_service_name'] = (string) ($row['service_name'] ?? '');
        }

        $row['has_children'] = (int) $row['linked_sub_service_count'] > 0 || (int) $row['linked_group_count'] > 0;
        $rows[] = $row;
    }

    if (isset($stmt)) {
        $stmt->close();
    }

    return $rows;
}

function service_group_row(mysqli $conn, int $groupId): ?array
{
    $rows = service_group_options($conn);

    foreach ($rows as $row) {
        if ((int) ($row['group_id'] ?? 0) === $groupId) {
            return $row;
        }
    }

    return null;
}

function service_main_service_options(mysqli $conn, int $categoryId = 0, bool $activeOnly = false, array $includeInactiveIds = array()): array
{
    return service_catalog_rows($conn, array(
        'service_type' => 'main_service',
        'category_id' => $categoryId,
        'active_only' => $activeOnly,
        'include_inactive_ids' => $includeInactiveIds,
    ));
}

function service_selection_catalog(mysqli $conn, array $options = array()): array
{
    ensure_service_catalog_schema($conn);

    $activeOnly = !empty($options['active_only']);
    $includeInactiveIds = array_values(array_unique(array_filter(array_map('intval', (array) ($options['include_inactive_ids'] ?? array())))));
    $serviceRows = service_catalog_rows($conn, array(
        'service_types' => array('main_service', 'sub_service'),
        'active_only' => $activeOnly,
        'include_inactive_ids' => $includeInactiveIds,
    ));
    $groupRows = service_group_options($conn);

    $categoryMap = array();
    foreach (service_categories($conn) as $category) {
        $categoryId = (int) ($category['category_id'] ?? 0);
        $categoryMap[$categoryId] = array(
            'category_id' => $categoryId,
            'category_name' => (string) ($category['category_name'] ?? ''),
            'direct_services' => array(),
            'main_services' => array(),
        );
    }

    $groupMap = array();
    foreach ($groupRows as $groupRow) {
        $mainServiceId = (int) ($groupRow['main_service_id'] ?? 0);
        $groupId = (int) ($groupRow['group_id'] ?? 0);

        if ($groupId <= 0 || $mainServiceId <= 0) {
            continue;
        }

        $groupRow['sub_services'] = array();

        if (!isset($groupMap[$mainServiceId])) {
            $groupMap[$mainServiceId] = array();
        }

        $groupMap[$mainServiceId][$groupId] = $groupRow;
    }

    $mainServiceMap = array();
    foreach ($serviceRows as $serviceRow) {
        if ((string) ($serviceRow['service_type'] ?? '') !== 'main_service') {
            continue;
        }

        $serviceId = (int) ($serviceRow['service_id'] ?? 0);
        $serviceRow['direct_sub_services'] = array();
        $serviceRow['groups'] = isset($groupMap[$serviceId]) ? array_values($groupMap[$serviceId]) : array();
        $serviceRow['has_children'] = !empty($serviceRow['groups']) || (int) ($serviceRow['linked_sub_service_count'] ?? 0) > 0;
        $mainServiceMap[$serviceId] = $serviceRow;
    }

    foreach ($serviceRows as $serviceRow) {
        if ((string) ($serviceRow['service_type'] ?? '') !== 'sub_service') {
            continue;
        }

        $categoryId = (int) ($serviceRow['category_id'] ?? 0);
        $mainServiceId = (int) ($serviceRow['main_service_id'] ?? 0);
        $groupId = (int) ($serviceRow['group_id'] ?? 0);

        if ($mainServiceId > 0 && isset($mainServiceMap[$mainServiceId])) {
            if ($groupId > 0) {
                $attachedToGroup = false;

                foreach ($mainServiceMap[$mainServiceId]['groups'] as $groupIndex => $groupRow) {
                    if ((int) ($groupRow['group_id'] ?? 0) !== $groupId) {
                        continue;
                    }

                    $mainServiceMap[$mainServiceId]['groups'][$groupIndex]['sub_services'][] = $serviceRow;
                    $attachedToGroup = true;
                    break;
                }

                if ($attachedToGroup) {
                    continue;
                }
            }

            $mainServiceMap[$mainServiceId]['direct_sub_services'][] = $serviceRow;
            $mainServiceMap[$mainServiceId]['has_children'] = true;
            continue;
        }

        if (!isset($categoryMap[$categoryId])) {
            $categoryMap[$categoryId] = array(
                'category_id' => $categoryId,
                'category_name' => (string) ($serviceRow['category_name'] ?? $serviceRow['category'] ?? 'Uncategorized'),
                'direct_services' => array(),
                'main_services' => array(),
            );
        }

        $categoryMap[$categoryId]['direct_services'][] = $serviceRow;
    }

    foreach ($mainServiceMap as $mainServiceRow) {
        $categoryId = (int) ($mainServiceRow['category_id'] ?? 0);

        if (!isset($categoryMap[$categoryId])) {
            $categoryMap[$categoryId] = array(
                'category_id' => $categoryId,
                'category_name' => (string) ($mainServiceRow['category_name'] ?? $mainServiceRow['category'] ?? 'Uncategorized'),
                'direct_services' => array(),
                'main_services' => array(),
            );
        }

        usort($mainServiceRow['direct_sub_services'], 'service_name_compare');
        foreach ($mainServiceRow['groups'] as $groupIndex => $groupRow) {
            usort($groupRow['sub_services'], 'service_name_compare');
            $mainServiceRow['groups'][$groupIndex] = $groupRow;
        }

        usort($mainServiceRow['groups'], static function (array $left, array $right): int {
            return strcasecmp((string) ($left['group_name'] ?? ''), (string) ($right['group_name'] ?? ''));
        });

        if (!empty($mainServiceRow['groups']) || !empty($mainServiceRow['direct_sub_services'])) {
            $mainServiceRow['has_children'] = true;
        }

        if ($mainServiceRow['has_children']) {
            $categoryMap[$categoryId]['main_services'][] = $mainServiceRow;
        } else {
            $categoryMap[$categoryId]['direct_services'][] = $mainServiceRow;
        }
    }

    foreach ($categoryMap as $categoryId => $categoryRow) {
        usort($categoryRow['direct_services'], static function (array $left, array $right): int {
            $rankDiff = service_type_sort_rank((string) ($left['service_type'] ?? '')) - service_type_sort_rank((string) ($right['service_type'] ?? ''));

            if ($rankDiff !== 0) {
                return $rankDiff;
            }

            return service_name_compare($left, $right);
        });

        usort($categoryRow['main_services'], 'service_name_compare');
        $categoryMap[$categoryId] = $categoryRow;
    }

    $catalog = array_values(array_filter($categoryMap, static function (array $categoryRow): bool {
        return !empty($categoryRow['direct_services']) || !empty($categoryRow['main_services']);
    }));

    usort($catalog, static function (array $left, array $right): int {
        return strcasecmp((string) ($left['category_name'] ?? ''), (string) ($right['category_name'] ?? ''));
    });

    return $catalog;
}

function service_catalog_row(mysqli $conn, int $serviceId): ?array
{
    $rows = service_catalog_rows($conn, array('service_ids' => array($serviceId)));

    return empty($rows) ? null : $rows[0];
}

function service_package_item_rows(mysqli $conn, int $packageServiceId): array
{
    ensure_service_catalog_schema($conn);

    $stmt = db_prepare($conn, "
        SELECT
            spi.package_item_id,
            spi.package_service_id,
            spi.sub_service_id,
            spi.included_uses,
            s.service_name,
            COALESCE(ms.service_name, '') AS main_service_name,
            COALESCE(sc.category_name, s.category) AS category_name,
            sg.group_name
        FROM service_package_items spi
        INNER JOIN services s ON s.service_id = spi.sub_service_id
        LEFT JOIN services ms ON ms.service_id = s.main_service_id
        LEFT JOIN service_categories sc ON sc.category_id = s.category_id
        LEFT JOIN service_groups sg ON sg.group_id = s.group_id
        WHERE spi.package_service_id = ?
        ORDER BY
            COALESCE(sc.category_name, s.category) ASC,
            COALESCE(ms.service_name, '') ASC,
            sg.group_name ASC,
            s.service_name ASC
    ");
    $stmt->bind_param('i', $packageServiceId);
    $stmt->execute();
    $result = $stmt->get_result();
    $items = array();

    while ($row = $result->fetch_assoc()) {
        $items[] = $row;
    }

    $stmt->close();

    return $items;
}

function service_save_package_items(mysqli $conn, int $packageServiceId, array $packageServiceCounts): void
{
    ensure_service_catalog_schema($conn);

    $deleteStmt = db_prepare($conn, "
        DELETE FROM service_package_items
        WHERE package_service_id = ?
    ");
    $deleteStmt->bind_param('i', $packageServiceId);
    $deleteStmt->execute();
    $deleteStmt->close();

    $counts = array();
    foreach ($packageServiceCounts as $serviceId => $count) {
        $subServiceId = (int) $serviceId;
        $includedUses = (int) $count;

        if ($subServiceId > 0 && $includedUses > 0) {
            $counts[$subServiceId] = $includedUses;
        }
    }

    if (empty($counts)) {
        return;
    }

    $validIds = implode(',', array_keys($counts));
    $validRows = array();
    $result = $conn->query("
        SELECT service_id
        FROM services
        WHERE service_type = 'sub_service'
            AND service_id IN ({$validIds})
    ");

    if ($result instanceof mysqli_result) {
        while ($row = $result->fetch_assoc()) {
            $validRows[(int) $row['service_id']] = true;
        }
    }

    $insertStmt = db_prepare($conn, "
        INSERT INTO service_package_items (package_service_id, sub_service_id, included_uses)
        VALUES (?, ?, ?)
    ");

    foreach ($counts as $subServiceId => $includedUses) {
        if (!isset($validRows[$subServiceId])) {
            continue;
        }

        $insertStmt->bind_param('iii', $packageServiceId, $subServiceId, $includedUses);
        $insertStmt->execute();
    }

    $insertStmt->close();
}

function service_customer_package_balance_rows(mysqli $conn, int $customerId, int $carId = 0): array
{
    ensure_service_catalog_schema($conn);

    $sql = "
        SELECT
            cpi.customer_package_item_id,
            cpi.customer_package_id,
            cpi.sub_service_id,
            cpi.service_name_snapshot,
            cpi.included_uses,
            cpi.used_uses,
            (cpi.included_uses - cpi.used_uses) AS remaining_uses,
            cp.package_service_id,
            cp.package_name_snapshot,
            cp.car_id,
            cp.vehicle_scope,
            cp.order_id,
            cp.created_at,
            car.plate_number
        FROM customer_package_items cpi
        INNER JOIN customer_packages cp ON cp.customer_package_id = cpi.customer_package_id
        LEFT JOIN cars car ON car.car_id = cp.car_id
        WHERE cp.customer_id = ?
            AND cp.status = 'active'
            AND cpi.used_uses < cpi.included_uses
    ";

    if ($carId > 0) {
        $sql .= "
            AND (
                cp.vehicle_scope = 'member'
                OR cp.car_id IS NULL
                OR cp.car_id = 0
                OR cp.car_id = ?
            )
        ";
    }

    $sql .= " ORDER BY cp.created_at ASC, cp.customer_package_id ASC, cpi.customer_package_item_id ASC";

    $stmt = db_prepare($conn, $sql);

    if ($carId > 0) {
        $stmt->bind_param('ii', $customerId, $carId);
    } else {
        $stmt->bind_param('i', $customerId);
    }
    $stmt->execute();
    $result = $stmt->get_result();
    $rows = array();

    while ($row = $result->fetch_assoc()) {
        $row['car_id'] = (int) ($row['car_id'] ?? 0);
        $row['vehicle_scope'] = normalize_package_vehicle_scope((string) ($row['vehicle_scope'] ?? 'member'));
        $rows[] = $row;
    }

    $stmt->close();

    return $rows;
}

function service_customer_package_balance_map(mysqli $conn, int $customerId, int $carId = 0): array
{
    $rows = service_customer_package_balance_rows($conn, $customerId, $carId);
    $map = array();

    foreach ($rows as $row) {
        $subServiceId = (int) ($row['sub_service_id'] ?? 0);

        if ($subServiceId <= 0) {
            continue;
        }

        if (!isset($map[$subServiceId])) {
            $map[$subServiceId] = array();
        }

        $map[$subServiceId][] = $row;
    }

    return $map;
}

function service_customer_package_balance_rows_for_customer_cars(mysqli $conn, array $requests): array
{
    ensure_service_catalog_schema($conn);

    $normalizedRequests = array();
    $customerIds = array();

    foreach ($requests as $request) {
        if (!is_array($request)) {
            continue;
        }

        $customerId = (int) ($request['customer_id'] ?? $request['customerId'] ?? 0);
        $carId = (int) ($request['car_id'] ?? $request['carId'] ?? 0);

        if ($customerId <= 0) {
            continue;
        }

        $lookupKey = $customerId . ':' . $carId;
        $normalizedRequests[$lookupKey] = array(
            'customer_id' => $customerId,
            'car_id' => $carId,
        );
        $customerIds[$customerId] = $customerId;
    }

    if ($normalizedRequests === array()) {
        return array();
    }

    $customerIds = array_values($customerIds);
    $placeholders = implode(',', array_fill(0, count($customerIds), '?'));
    $types = str_repeat('i', count($customerIds));
    $stmt = db_prepare($conn, "
        SELECT
            cp.customer_id,
            cpi.customer_package_item_id,
            cpi.customer_package_id,
            cpi.sub_service_id,
            cpi.service_name_snapshot,
            cpi.included_uses,
            cpi.used_uses,
            (cpi.included_uses - cpi.used_uses) AS remaining_uses,
            cp.package_service_id,
            cp.package_name_snapshot,
            cp.car_id,
            cp.vehicle_scope,
            cp.order_id,
            cp.created_at,
            car.plate_number
        FROM customer_package_items cpi
        INNER JOIN customer_packages cp ON cp.customer_package_id = cpi.customer_package_id
        LEFT JOIN cars car ON car.car_id = cp.car_id
        WHERE cp.customer_id IN ({$placeholders})
            AND cp.status = 'active'
            AND cpi.used_uses < cpi.included_uses
        ORDER BY cp.customer_id ASC, cp.created_at ASC, cp.customer_package_id ASC, cpi.customer_package_item_id ASC
    ");
    $stmt->bind_param($types, ...$customerIds);
    $stmt->execute();
    $result = $stmt->get_result();

    $rowsByCustomer = array();
    while ($row = $result->fetch_assoc()) {
        $customerId = (int) ($row['customer_id'] ?? 0);
        if ($customerId <= 0) {
            continue;
        }

        $row['car_id'] = (int) ($row['car_id'] ?? 0);
        $row['vehicle_scope'] = normalize_package_vehicle_scope((string) ($row['vehicle_scope'] ?? 'member'));

        if (!isset($rowsByCustomer[$customerId])) {
            $rowsByCustomer[$customerId] = array();
        }

        $rowsByCustomer[$customerId][] = $row;
    }

    $stmt->close();

    $response = array();
    foreach ($normalizedRequests as $lookupKey => $request) {
        $customerId = (int) ($request['customer_id'] ?? 0);
        $carId = (int) ($request['car_id'] ?? 0);
        $rows = $rowsByCustomer[$customerId] ?? array();

        if ($carId <= 0) {
            $response[$lookupKey] = $rows;
            continue;
        }

        $response[$lookupKey] = array_values(array_filter($rows, static function (array $row) use ($carId): bool {
            $packageCarId = (int) ($row['car_id'] ?? 0);
            $vehicleScope = normalize_package_vehicle_scope((string) ($row['vehicle_scope'] ?? 'member'));

            return $vehicleScope === 'member'
                || $packageCarId <= 0
                || $packageCarId === $carId;
        }));
    }

    return $response;
}

function service_customer_package_balance_row(mysqli $conn, int $customerId, int $customerPackageItemId, int $carId = 0): ?array
{
    ensure_service_catalog_schema($conn);

    $sql = "
        SELECT
            cpi.customer_package_item_id,
            cpi.customer_package_id,
            cpi.sub_service_id,
            cpi.service_name_snapshot,
            cpi.included_uses,
            cpi.used_uses,
            (cpi.included_uses - cpi.used_uses) AS remaining_uses,
            cp.package_service_id,
            cp.package_name_snapshot,
            cp.car_id,
            cp.vehicle_scope,
            cp.order_id,
            cp.created_at,
            car.plate_number
        FROM customer_package_items cpi
        INNER JOIN customer_packages cp ON cp.customer_package_id = cpi.customer_package_id
        LEFT JOIN cars car ON car.car_id = cp.car_id
        WHERE cp.customer_id = ?
            AND cp.status = 'active'
            AND cpi.customer_package_item_id = ?
            AND cpi.used_uses < cpi.included_uses
    ";

    if ($carId > 0) {
        $sql .= "
            AND (
                cp.vehicle_scope = 'member'
                OR cp.car_id IS NULL
                OR cp.car_id = 0
                OR cp.car_id = ?
            )
        ";
    }

    $sql .= " LIMIT 1";

    $stmt = db_prepare($conn, $sql);

    if ($carId > 0) {
        $stmt->bind_param('iii', $customerId, $customerPackageItemId, $carId);
    } else {
        $stmt->bind_param('ii', $customerId, $customerPackageItemId);
    }
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();
    $stmt->close();

    if (is_array($row)) {
        $row['car_id'] = (int) ($row['car_id'] ?? 0);
        $row['vehicle_scope'] = normalize_package_vehicle_scope((string) ($row['vehicle_scope'] ?? 'member'));
    }

    return $row ?: null;
}

function service_package_purchase_snapshot(mysqli $conn, int $packageServiceId): array
{
    $snapshot = array();

    foreach (service_package_item_rows($conn, $packageServiceId) as $packageItem) {
        $snapshot[] = array(
            'sub_service_id' => (int) ($packageItem['sub_service_id'] ?? 0),
            'service_name' => (string) ($packageItem['service_name'] ?? ''),
            'included_uses' => (int) ($packageItem['included_uses'] ?? 0),
        );
    }

    return $snapshot;
}

function service_sub_service_package_blockers(mysqli $conn, int $subServiceId, int $nameLimit = 3): array
{
    ensure_service_catalog_schema($conn);

    $blockers = array(
        'package_settings_count' => 0,
        'package_settings_names' => array(),
        'customer_package_count' => 0,
        'customer_package_active_count' => 0,
        'customer_package_names' => array(),
    );

    if ($subServiceId <= 0) {
        return $blockers;
    }

    $settingsStmt = db_prepare($conn, "
        SELECT DISTINCT
            spi.package_service_id,
            package_service.service_name
        FROM service_package_items spi
        INNER JOIN services package_service ON package_service.service_id = spi.package_service_id
        WHERE spi.sub_service_id = ?
        ORDER BY package_service.service_name ASC, spi.package_service_id ASC
    ");
    $settingsStmt->bind_param('i', $subServiceId);
    $settingsStmt->execute();
    $settingsResult = $settingsStmt->get_result();

    while ($row = $settingsResult->fetch_assoc()) {
        $blockers['package_settings_count']++;

        if (count($blockers['package_settings_names']) < $nameLimit) {
            $blockers['package_settings_names'][] = (string) ($row['service_name'] ?? '');
        }
    }

    $settingsStmt->close();

    $customerPackageStmt = db_prepare($conn, "
        SELECT DISTINCT
            cp.customer_package_id,
            cp.package_name_snapshot,
            cp.status,
            cp.created_at
        FROM customer_package_items cpi
        INNER JOIN customer_packages cp ON cp.customer_package_id = cpi.customer_package_id
        WHERE cpi.sub_service_id = ?
        ORDER BY cp.created_at DESC, cp.customer_package_id DESC
    ");
    $customerPackageStmt->bind_param('i', $subServiceId);
    $customerPackageStmt->execute();
    $customerPackageResult = $customerPackageStmt->get_result();

    while ($row = $customerPackageResult->fetch_assoc()) {
        $blockers['customer_package_count']++;

        if ((string) ($row['status'] ?? '') === 'active') {
            $blockers['customer_package_active_count']++;
        }

        if (count($blockers['customer_package_names']) < $nameLimit) {
            $blockers['customer_package_names'][] = (string) ($row['package_name_snapshot'] ?? '');
        }
    }

    $customerPackageStmt->close();

    return $blockers;
}

function service_sub_service_package_blocker_message(array $blockers, string $action = 'delete'): string
{
    return resolve_localized_flash_message(service_sub_service_package_blocker_flash($blockers, $action));
}

function service_sub_service_package_blocker_flash(array $blockers, string $action = 'delete'): array
{
    $isChangeType = $action === 'change_type';
    $th = static fn(string $unicode): string => (string) json_decode('"' . $unicode . '"');
    $thaiParts = array(
        $isChangeType
            ? $th('\u0e44\u0e21\u0e48\u0e2a\u0e32\u0e21\u0e32\u0e23\u0e16\u0e40\u0e1b\u0e25\u0e35\u0e48\u0e22\u0e19\u0e1b\u0e23\u0e30\u0e40\u0e20\u0e17\u0e1a\u0e23\u0e34\u0e01\u0e32\u0e23\u0e22\u0e48\u0e2d\u0e22\u0e19\u0e35\u0e49\u0e44\u0e14\u0e49 \u0e40\u0e1e\u0e23\u0e32\u0e30\u0e22\u0e31\u0e07\u0e16\u0e39\u0e01\u0e43\u0e0a\u0e49\u0e43\u0e19\u0e41\u0e1e\u0e47\u0e01\u0e40\u0e01\u0e08')
            : $th('\u0e44\u0e21\u0e48\u0e2a\u0e32\u0e21\u0e32\u0e23\u0e16\u0e25\u0e1a\u0e1a\u0e23\u0e34\u0e01\u0e32\u0e23\u0e22\u0e48\u0e2d\u0e22\u0e19\u0e35\u0e49\u0e44\u0e14\u0e49 \u0e40\u0e1e\u0e23\u0e32\u0e30\u0e22\u0e31\u0e07\u0e16\u0e39\u0e01\u0e43\u0e0a\u0e49\u0e43\u0e19\u0e41\u0e1e\u0e47\u0e01\u0e40\u0e01\u0e08'),
    );
    $englishParts = array(
        $isChangeType
            ? 'This sub service cannot change type because it is still used in packages.'
            : 'This sub service cannot be deleted because it is still used in packages.',
    );

    $settingsNames = array_values(array_filter(array_map('trim', (array) ($blockers['package_settings_names'] ?? array()))));
    $settingsCount = (int) ($blockers['package_settings_count'] ?? 0);
    if ($settingsCount > 0 && !empty($settingsNames)) {
        $thaiSuffix = $settingsCount > count($settingsNames) ? ' ' . $th('\u0e41\u0e25\u0e30\u0e2d\u0e37\u0e48\u0e19 \u0e46') : '';
        $englishSuffix = $settingsCount > count($settingsNames) ? ' and more' : '';
        $thaiParts[] = $th('\u0e41\u0e1e\u0e47\u0e01\u0e40\u0e01\u0e08\u0e17\u0e35\u0e48\u0e15\u0e31\u0e49\u0e07\u0e04\u0e48\u0e32\u0e44\u0e27\u0e49') . ': ' . implode(', ', $settingsNames) . $thaiSuffix;
        $englishParts[] = 'Package settings: ' . implode(', ', $settingsNames) . $englishSuffix;
    }

    $customerPackageNames = array_values(array_filter(array_map('trim', (array) ($blockers['customer_package_names'] ?? array()))));
    $customerPackageCount = (int) ($blockers['customer_package_count'] ?? 0);
    $activePackageCount = (int) ($blockers['customer_package_active_count'] ?? 0);
    if ($customerPackageCount > 0 && !empty($customerPackageNames)) {
        $thaiSuffix = $customerPackageCount > count($customerPackageNames) ? ' ' . $th('\u0e41\u0e25\u0e30\u0e2d\u0e37\u0e48\u0e19 \u0e46') : '';
        $englishSuffix = $customerPackageCount > count($customerPackageNames) ? ' and more' : '';
        $thaiReferenceSummary = $activePackageCount > 0
            ? $th('\u0e22\u0e31\u0e07\u0e21\u0e35\u0e41\u0e1e\u0e47\u0e01\u0e40\u0e01\u0e08\u0e17\u0e35\u0e48\u0e43\u0e0a\u0e49\u0e07\u0e32\u0e19\u0e2d\u0e22\u0e39\u0e48') . ' ' . number_format($activePackageCount) . ' ' . $th('\u0e23\u0e32\u0e22\u0e01\u0e32\u0e23')
            : $th('\u0e21\u0e35\u0e1b\u0e23\u0e30\u0e27\u0e31\u0e15\u0e34\u0e2d\u0e49\u0e32\u0e07\u0e2d\u0e34\u0e07') . ' ' . number_format($customerPackageCount) . ' ' . $th('\u0e23\u0e32\u0e22\u0e01\u0e32\u0e23');
        $englishReferenceSummary = $activePackageCount > 0
            ? 'still active in ' . number_format($activePackageCount) . ' package(s)'
            : 'referenced by ' . number_format($customerPackageCount) . ' package record(s)';
        $thaiParts[] = $th('\u0e41\u0e1e\u0e47\u0e01\u0e40\u0e01\u0e08\u0e25\u0e39\u0e01\u0e04\u0e49\u0e32\u0e17\u0e35\u0e48\u0e2d\u0e49\u0e32\u0e07\u0e2d\u0e34\u0e07') . ': ' . implode(', ', $customerPackageNames) . $thaiSuffix . ' (' . $thaiReferenceSummary . ')';
        $englishParts[] = 'Customer packages referencing this service: ' . implode(', ', $customerPackageNames) . $englishSuffix . ' (' . $englishReferenceSummary . ')';
    }

    $thaiParts[] = $isChangeType
        ? $th('\u0e01\u0e23\u0e38\u0e13\u0e32\u0e19\u0e33\u0e1a\u0e23\u0e34\u0e01\u0e32\u0e23\u0e19\u0e35\u0e49\u0e2d\u0e2d\u0e01\u0e08\u0e32\u0e01\u0e41\u0e1e\u0e47\u0e01\u0e40\u0e01\u0e08\u0e01\u0e48\u0e2d\u0e19 \u0e41\u0e25\u0e49\u0e27\u0e08\u0e36\u0e07\u0e40\u0e1b\u0e25\u0e35\u0e48\u0e22\u0e19\u0e1b\u0e23\u0e30\u0e40\u0e20\u0e17')
        : $th('\u0e01\u0e23\u0e38\u0e13\u0e32\u0e19\u0e33\u0e1a\u0e23\u0e34\u0e01\u0e32\u0e23\u0e19\u0e35\u0e49\u0e2d\u0e2d\u0e01\u0e08\u0e32\u0e01\u0e41\u0e1e\u0e47\u0e01\u0e40\u0e01\u0e08\u0e01\u0e48\u0e2d\u0e19 \u0e2b\u0e23\u0e37\u0e2d\u0e1b\u0e34\u0e14\u0e43\u0e0a\u0e49\u0e07\u0e32\u0e19\u0e41\u0e17\u0e19\u0e01\u0e32\u0e23\u0e25\u0e1a');
    $englishParts[] = $isChangeType
        ? 'Remove this service from packages before changing its type.'
        : 'Remove this service from packages first, or set it inactive instead of deleting it.';

    return localized_flash_message(
        implode(' | ', $thaiParts),
        implode(' | ', $englishParts)
    );
}

function service_order_audit_context(mysqli $conn, int $orderId): array
{
    static $cache = array();

    if ($orderId <= 0) {
        return array();
    }

    if (isset($cache[$orderId])) {
        return $cache[$orderId];
    }

    $stmt = db_prepare($conn, "
        SELECT order_id, customer_id, car_id, branch_name, bill_number, order_source, status, payment_status
        FROM orders
        WHERE order_id = ?
        LIMIT 1
    ");
    $stmt->bind_param('i', $orderId);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();
    $stmt->close();

    $cache[$orderId] = is_array($row) ? $row : array();

    return $cache[$orderId];
}

function service_create_customer_package_purchase(mysqli $conn, int $customerId, int $carId, int $orderId, array $packageService): int
{
    $packageServiceId = (int) ($packageService['service_id'] ?? 0);
    $packageServiceName = (string) ($packageService['service_name'] ?? '');
    $vehicleScope = normalize_package_vehicle_scope((string) ($packageService['package_vehicle_scope'] ?? 'member'));

    if ($packageServiceId <= 0 || $packageServiceName === '') {
        throw new RuntimeException('Package service data is invalid.');
    }

    if ($vehicleScope === 'car' && $carId <= 0) {
        throw new RuntimeException('Car-linked package requires a vehicle.');
    }

    $items = service_package_purchase_snapshot($conn, $packageServiceId);

    if (empty($items)) {
        throw new RuntimeException('Package has no configured sub services.');
    }

    $packageStmt = db_prepare($conn, "
        INSERT INTO customer_packages (customer_id, car_id, vehicle_scope, package_service_id, order_id, package_name_snapshot)
        VALUES (?, NULLIF(?, 0), ?, ?, ?, ?)
    ");
    $packageStmt->bind_param('iisiis', $customerId, $carId, $vehicleScope, $packageServiceId, $orderId, $packageServiceName);
    $packageStmt->execute();
    $customerPackageId = (int) $conn->insert_id;
    $packageStmt->close();

    $itemStmt = db_prepare($conn, "
        INSERT INTO customer_package_items (customer_package_id, sub_service_id, service_name_snapshot, included_uses, used_uses)
        VALUES (?, ?, ?, ?, 0)
    ");

    foreach ($items as $item) {
        $subServiceId = (int) ($item['sub_service_id'] ?? 0);
        $serviceName = (string) ($item['service_name'] ?? '');
        $includedUses = (int) ($item['included_uses'] ?? 0);

        if ($subServiceId <= 0 || $serviceName === '' || $includedUses <= 0) {
            continue;
        }

        $itemStmt->bind_param('iisi', $customerPackageId, $subServiceId, $serviceName, $includedUses);
        $itemStmt->execute();
    }

    $itemStmt->close();

    $orderContext = service_order_audit_context($conn, $orderId);
    audit_log($conn, array(
        'module_key' => 'package',
        'action_key' => 'create',
        'entity_type' => 'customer_package',
        'entity_id' => $customerPackageId,
        'order_id' => $orderId,
        'customer_id' => $customerId,
        'car_id' => $carId,
        'branch_name' => (string) ($orderContext['branch_name'] ?? ''),
        'bill_number' => (string) ($orderContext['bill_number'] ?? ''),
        'message' => 'Package purchase created.',
        'after' => array(
            'package_service_id' => $packageServiceId,
            'package_name' => $packageServiceName,
            'vehicle_scope' => $vehicleScope,
            'included_items' => $items,
            'order_source' => (string) ($orderContext['order_source'] ?? ''),
        ),
    ));

    return $customerPackageId;
}

function service_apply_package_usage(mysqli $conn, int $orderId, array $serviceRow, array $balanceRow): array
{
    $customerPackageItemId = (int) ($balanceRow['customer_package_item_id'] ?? 0);
    $remainingUses = (int) ($balanceRow['remaining_uses'] ?? 0);
    $serviceId = (int) ($serviceRow['service_id'] ?? 0);
    $serviceName = (string) ($serviceRow['service_name'] ?? '');

    if ($customerPackageItemId <= 0 || $remainingUses <= 0 || $serviceId <= 0 || $serviceName === '') {
        throw new RuntimeException('Package usage is invalid.');
    }

    $incrementStmt = db_prepare($conn, "
        UPDATE customer_package_items
        SET used_uses = used_uses + 1
        WHERE customer_package_item_id = ?
            AND used_uses < included_uses
    ");
    $incrementStmt->bind_param('i', $customerPackageItemId);
    $incrementStmt->execute();
    $affected = $incrementStmt->affected_rows;
    $incrementStmt->close();

    if ($affected <= 0) {
        throw new RuntimeException('Package balance is no longer available.');
    }

    $usageStmt = db_prepare($conn, "
        INSERT INTO order_package_usages (order_id, customer_package_item_id, service_id, service_name_snapshot, used_uses, is_reversed)
        VALUES (?, ?, ?, ?, 1, 0)
    ");
    $usageStmt->bind_param('iiis', $orderId, $customerPackageItemId, $serviceId, $serviceName);
    $usageStmt->execute();
    $usageId = (int) $conn->insert_id;
    $usageStmt->close();

    $orderContext = service_order_audit_context($conn, $orderId);
    audit_log($conn, array(
        'module_key' => 'package',
        'action_key' => 'usage',
        'entity_type' => 'package_usage',
        'entity_id' => $usageId,
        'order_id' => $orderId,
        'customer_id' => (int) ($orderContext['customer_id'] ?? 0),
        'car_id' => (int) ($orderContext['car_id'] ?? 0),
        'branch_name' => (string) ($orderContext['branch_name'] ?? ''),
        'bill_number' => (string) ($orderContext['bill_number'] ?? ''),
        'message' => 'Package usage applied.',
        'after' => array(
            'customer_package_id' => (int) ($balanceRow['customer_package_id'] ?? 0),
            'customer_package_item_id' => $customerPackageItemId,
            'package_name' => (string) ($balanceRow['package_name_snapshot'] ?? ''),
            'service_id' => $serviceId,
            'service_name' => $serviceName,
            'used_uses' => 1,
            'remaining_after_use' => max(0, $remainingUses - 1),
        ),
    ));

    return array(
        'customer_package_item_id' => $customerPackageItemId,
        'customer_package_id' => (int) ($balanceRow['customer_package_id'] ?? 0),
        'package_name' => (string) ($balanceRow['package_name_snapshot'] ?? ''),
        'remaining_after_use' => max(0, $remainingUses - 1),
    );
}

function service_order_package_usage_rows(mysqli $conn, int $orderId, bool $includeReversed = false): array
{
    ensure_service_catalog_schema($conn);

    $sql = "
        SELECT
            opu.usage_id,
            opu.order_id,
            opu.customer_package_item_id,
            opu.service_id,
            opu.service_name_snapshot,
            opu.used_uses,
            opu.is_reversed,
            cp.package_name_snapshot,
            cp.order_id AS package_order_id,
            cp.vehicle_scope,
            cp.car_id,
            package_car.plate_number
        FROM order_package_usages opu
        INNER JOIN customer_package_items cpi ON cpi.customer_package_item_id = opu.customer_package_item_id
        INNER JOIN customer_packages cp ON cp.customer_package_id = cpi.customer_package_id
        LEFT JOIN cars package_car ON package_car.car_id = cp.car_id
        WHERE opu.order_id = ?
    ";

    if (!$includeReversed) {
        $sql .= " AND opu.is_reversed = 0";
    }

    $sql .= " ORDER BY opu.usage_id ASC";

    $stmt = db_prepare($conn, $sql);
    $stmt->bind_param('i', $orderId);
    $stmt->execute();
    $result = $stmt->get_result();
    $rows = array();

    while ($row = $result->fetch_assoc()) {
        $rows[] = $row;
    }

    $stmt->close();

    return $rows;
}

function service_release_order_package_effects(mysqli $conn, int $orderId): void
{
    ensure_service_catalog_schema($conn);

    $orderContext = service_order_audit_context($conn, $orderId);
    $usageRows = service_order_package_usage_rows($conn, $orderId, false);

    foreach ($usageRows as $usageRow) {
        $customerPackageItemId = (int) ($usageRow['customer_package_item_id'] ?? 0);
        $usedUses = max(1, (int) ($usageRow['used_uses'] ?? 1));
        $usageId = (int) ($usageRow['usage_id'] ?? 0);

        $itemUpdateStmt = db_prepare($conn, "
            UPDATE customer_package_items
            SET used_uses = GREATEST(0, used_uses - ?)
            WHERE customer_package_item_id = ?
        ");
        $itemUpdateStmt->bind_param('ii', $usedUses, $customerPackageItemId);
        $itemUpdateStmt->execute();
        $itemUpdateStmt->close();

        $usageUpdateStmt = db_prepare($conn, "
            UPDATE order_package_usages
            SET is_reversed = 1,
                reversed_at = NOW()
            WHERE usage_id = ?
        ");
        $usageUpdateStmt->bind_param('i', $usageId);
        $usageUpdateStmt->execute();
        $usageUpdateStmt->close();

        audit_log($conn, array(
            'module_key' => 'package',
            'action_key' => 'reverse',
            'entity_type' => 'package_usage',
            'entity_id' => $usageId,
            'order_id' => $orderId,
            'customer_id' => (int) ($orderContext['customer_id'] ?? 0),
            'car_id' => (int) ($orderContext['car_id'] ?? 0),
            'branch_name' => (string) ($orderContext['branch_name'] ?? ''),
            'bill_number' => (string) ($orderContext['bill_number'] ?? ''),
            'message' => 'Package usage reversed.',
            'before' => array(
                'customer_package_item_id' => $customerPackageItemId,
                'package_name' => (string) ($usageRow['package_name_snapshot'] ?? ''),
                'service_id' => (int) ($usageRow['service_id'] ?? 0),
                'service_name' => (string) ($usageRow['service_name_snapshot'] ?? ''),
                'used_uses' => $usedUses,
                'vehicle_scope' => (string) ($usageRow['vehicle_scope'] ?? ''),
                'package_car_plate' => (string) ($usageRow['plate_number'] ?? ''),
            ),
            'after' => array(
                'is_reversed' => 1,
            ),
        ));
    }

    $purchaseStmt = db_prepare($conn, "
        SELECT customer_package_id, package_name_snapshot, vehicle_scope, car_id
        FROM customer_packages
        WHERE order_id = ?
            AND status = 'active'
        ORDER BY customer_package_id ASC
    ");
    $purchaseStmt->bind_param('i', $orderId);
    $purchaseStmt->execute();
    $purchaseResult = $purchaseStmt->get_result();
    $customerPackageIds = array();

    while ($row = $purchaseResult->fetch_assoc()) {
        $customerPackageIds[] = $row;
    }

    $purchaseStmt->close();

    foreach ($customerPackageIds as $purchaseRow) {
        $customerPackageId = (int) ($purchaseRow['customer_package_id'] ?? 0);
        $usageCountStmt = db_prepare($conn, "
            SELECT COALESCE(SUM(used_uses), 0) AS used_total
            FROM customer_package_items
            WHERE customer_package_id = ?
        ");
        $usageCountStmt->bind_param('i', $customerPackageId);
        $usageCountStmt->execute();
        $usageCount = $usageCountStmt->get_result()->fetch_assoc();
        $usageCountStmt->close();

        if ((int) ($usageCount['used_total'] ?? 0) > 0) {
            throw new RuntimeException('A package from this bill has already been used and cannot be removed.');
        }

        $cancelPackageStmt = db_prepare($conn, "
            UPDATE customer_packages
            SET status = 'cancelled',
                cancelled_at = NOW()
            WHERE customer_package_id = ?
        ");
        $cancelPackageStmt->bind_param('i', $customerPackageId);
        $cancelPackageStmt->execute();
        $cancelPackageStmt->close();

        audit_log($conn, array(
            'module_key' => 'package',
            'action_key' => 'cancel',
            'entity_type' => 'customer_package',
            'entity_id' => $customerPackageId,
            'order_id' => $orderId,
            'customer_id' => (int) ($orderContext['customer_id'] ?? 0),
            'car_id' => (int) ($orderContext['car_id'] ?? 0),
            'branch_name' => (string) ($orderContext['branch_name'] ?? ''),
            'bill_number' => (string) ($orderContext['bill_number'] ?? ''),
            'message' => 'Package purchase cancelled.',
            'before' => array(
                'package_name' => (string) ($purchaseRow['package_name_snapshot'] ?? ''),
                'vehicle_scope' => (string) ($purchaseRow['vehicle_scope'] ?? ''),
                'car_id' => (int) ($purchaseRow['car_id'] ?? 0),
                'status' => 'active',
            ),
            'after' => array(
                'status' => 'cancelled',
            ),
        ));
    }
}

function service_customer_package_purchase_rows(mysqli $conn, int $customerId): array
{
    ensure_service_catalog_schema($conn);

    $stmt = db_prepare($conn, "
        SELECT
            cp.customer_package_id,
            cp.package_service_id,
            cp.package_name_snapshot,
            cp.car_id,
            cp.vehicle_scope,
            cp.order_id,
            cp.status,
            cp.created_at,
            package_car.plate_number,
            COALESCE(SUM(cpi.included_uses), 0) AS included_uses,
            COALESCE(SUM(cpi.used_uses), 0) AS used_uses
        FROM customer_packages cp
        LEFT JOIN customer_package_items cpi ON cpi.customer_package_id = cp.customer_package_id
        LEFT JOIN cars package_car ON package_car.car_id = cp.car_id
        WHERE cp.customer_id = ?
        GROUP BY
            cp.customer_package_id,
            cp.package_service_id,
            cp.package_name_snapshot,
            cp.car_id,
            cp.vehicle_scope,
            cp.order_id,
            cp.status,
            cp.created_at,
            package_car.plate_number
        ORDER BY cp.created_at DESC, cp.customer_package_id DESC
    ");
    $stmt->bind_param('i', $customerId);
    $stmt->execute();
    $result = $stmt->get_result();
    $rows = array();

    while ($row = $result->fetch_assoc()) {
        $row['car_id'] = (int) ($row['car_id'] ?? 0);
        $row['vehicle_scope'] = normalize_package_vehicle_scope((string) ($row['vehicle_scope'] ?? 'member'));
        $rows[] = $row;
    }

    $stmt->close();

    return $rows;
}

function service_customer_vehicle_package_service_rows(mysqli $conn, int $customerId, int $carId): array
{
    ensure_service_catalog_schema($conn);

    if ($customerId <= 0 || $carId <= 0) {
        return array();
    }

    $stmt = db_prepare($conn, "
        SELECT
            cp.customer_package_id,
            cp.package_service_id,
            cp.package_name_snapshot,
            cp.car_id,
            cp.vehicle_scope,
            cp.order_id,
            cp.status,
            cp.created_at,
            package_car.plate_number,
            cpi.customer_package_item_id,
            cpi.sub_service_id,
            cpi.service_name_snapshot,
            cpi.included_uses,
            cpi.used_uses,
            (cpi.included_uses - cpi.used_uses) AS remaining_uses
        FROM customer_packages cp
        INNER JOIN customer_package_items cpi ON cpi.customer_package_id = cp.customer_package_id
        LEFT JOIN cars package_car ON package_car.car_id = cp.car_id
        WHERE cp.customer_id = ?
            AND cp.status = 'active'
            AND (
                cp.vehicle_scope = 'member'
                OR cp.car_id IS NULL
                OR cp.car_id = 0
                OR cp.car_id = ?
            )
        ORDER BY
            cp.created_at DESC,
            cp.customer_package_id DESC,
            cpi.service_name_snapshot ASC,
            cpi.customer_package_item_id ASC
    ");
    $stmt->bind_param('ii', $customerId, $carId);
    $stmt->execute();
    $result = $stmt->get_result();
    $rows = array();

    while ($row = $result->fetch_assoc()) {
        $row['car_id'] = (int) ($row['car_id'] ?? 0);
        $row['vehicle_scope'] = normalize_package_vehicle_scope((string) ($row['vehicle_scope'] ?? 'member'));
        $rows[] = $row;
    }

    $stmt->close();

    return $rows;
}

function service_customer_package_usage_history(mysqli $conn, int $customerId, int $carId = 0): array
{
    ensure_service_catalog_schema($conn);

    $sql = "
        SELECT
            opu.usage_id,
            opu.order_id,
            opu.service_name_snapshot,
            opu.used_uses,
            opu.created_at,
            cp.package_name_snapshot,
            cp.order_id AS package_order_id,
            cp.vehicle_scope,
            cp.car_id,
            package_car.plate_number,
            o.bill_number,
            o.order_date,
            used_car.plate_number AS used_plate_number
        FROM order_package_usages opu
        INNER JOIN customer_package_items cpi ON cpi.customer_package_item_id = opu.customer_package_item_id
        INNER JOIN customer_packages cp ON cp.customer_package_id = cpi.customer_package_id
        INNER JOIN orders o ON o.order_id = opu.order_id
        INNER JOIN cars used_car ON used_car.car_id = o.car_id
        LEFT JOIN cars package_car ON package_car.car_id = cp.car_id
        WHERE cp.customer_id = ?
            AND opu.is_reversed = 0
    ";

    if ($carId > 0) {
        $sql .= " AND o.car_id = ?";
    }

    $sql .= " ORDER BY o.order_date DESC, opu.usage_id DESC";

    $stmt = db_prepare($conn, $sql);

    if ($carId > 0) {
        $stmt->bind_param('ii', $customerId, $carId);
    } else {
        $stmt->bind_param('i', $customerId);
    }
    $stmt->execute();
    $result = $stmt->get_result();
    $rows = array();

    while ($row = $result->fetch_assoc()) {
        $row['car_id'] = (int) ($row['car_id'] ?? 0);
        $row['vehicle_scope'] = normalize_package_vehicle_scope((string) ($row['vehicle_scope'] ?? 'member'));
        $rows[] = $row;
    }

    $stmt->close();

    return $rows;
}
