<?php
declare(strict_types=1);

function service_category_seed_names(): array
{
    return array(
        'Add-on',
        'Coating',
        'Detailing',
        'Interior',
        'Protection',
        'Wash',
    );
}

function ensure_service_category_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_categories (
            category_id INT AUTO_INCREMENT PRIMARY KEY,
            category_name VARCHAR(60) NOT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_service_categories_name (category_name)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    $insertStmt = db_prepare($conn, "
        INSERT INTO service_categories (category_name)
        VALUES (?)
        ON DUPLICATE KEY UPDATE
            category_name = category_name
    ");

    $serviceResult = $conn->query("
        SELECT DISTINCT category
        FROM services
        WHERE category IS NOT NULL AND category <> ''
        ORDER BY category ASC
    ");

    if ($serviceResult instanceof mysqli_result) {
        while ($row = $serviceResult->fetch_assoc()) {
            $categoryName = trim((string) ($row['category'] ?? ''));

            if ($categoryName === '') {
                continue;
            }

            $insertStmt->bind_param('s', $categoryName);
            $insertStmt->execute();
        }
    }

    $countResult = $conn->query("SELECT COUNT(*) AS total FROM service_categories");
    $categoryCount = 0;

    if ($countResult instanceof mysqli_result) {
        $countRow = $countResult->fetch_assoc();
        $categoryCount = (int) ($countRow['total'] ?? 0);
    }

    if ($categoryCount === 0) {
        foreach (service_category_seed_names() as $categoryName) {
            $insertStmt->bind_param('s', $categoryName);
            $insertStmt->execute();
        }
    }

    $insertStmt->close();
}

function service_categories(mysqli $conn): array
{
    ensure_service_category_schema($conn);

    $result = $conn->query("
        SELECT category_id, category_name
        FROM service_categories
        ORDER BY category_name ASC
    ");

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

    $categories = array();

    while ($row = $result->fetch_assoc()) {
        $categories[] = array(
            'category_id' => (int) ($row['category_id'] ?? 0),
            'category_name' => (string) ($row['category_name'] ?? ''),
        );
    }

    return $categories;
}

function is_film_service_name(string $serviceName): bool
{
    return order_option_is_film_service_name($serviceName);
}

function film_tint_percent_options(): array
{
    return order_option_film_tint_percent_options();
}

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

    if ($checked) {
        return;
    }

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

        return;
    }

    $checked = true;
    $columnResult = $conn->query("SHOW COLUMNS FROM order_details LIKE 'service_options'");

    if ($columnResult instanceof mysqli_result && $columnResult->num_rows > 0) {
        return;
    }

    if (!$conn->query("ALTER TABLE order_details ADD COLUMN service_options TEXT NULL AFTER duration_minutes")) {
        throw new RuntimeException('Cannot add service_options column: ' . $conn->error);
    }
}

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

    if ($checked) {
        return;
    }

    $checked = true;
    $columnResult = $conn->query("SHOW COLUMNS FROM order_details LIKE 'item_note'");

    if ($columnResult instanceof mysqli_result && $columnResult->num_rows > 0) {
        return;
    }

    if (!$conn->query("ALTER TABLE order_details ADD COLUMN item_note TEXT NULL AFTER service_options")) {
        throw new RuntimeException('Cannot add item_note column: ' . $conn->error);
    }
}

function order_detail_option_lines(?string $serviceOptions): array
{
    return order_option_detail_lines($serviceOptions);
}

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

    if ($checked) {
        return;
    }

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

        return;
    }

    $checked = true;
    $columnResult = $conn->query("SHOW COLUMNS FROM orders LIKE 'bill_number'");

    if (!($columnResult instanceof mysqli_result) || $columnResult->num_rows === 0) {
        if (!$conn->query("ALTER TABLE orders ADD COLUMN bill_number VARCHAR(30) NULL AFTER order_id")) {
            throw new RuntimeException('Cannot add bill_number column: ' . $conn->error);
        }
    }

    $indexResult = $conn->query("SHOW INDEX FROM orders WHERE Key_name = 'uniq_orders_bill_number'");
    if (!($indexResult instanceof mysqli_result) || $indexResult->num_rows === 0) {
        $conn->query("ALTER TABLE orders ADD UNIQUE KEY uniq_orders_bill_number (bill_number)");
    }

    $result = $conn->query("
        SELECT order_id, branch_name, order_date
        FROM orders
        WHERE bill_number IS NULL OR bill_number = ''
        ORDER BY order_id ASC
    ");

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

    $updateStmt = db_prepare($conn, "
        UPDATE orders
        SET bill_number = ?
        WHERE order_id = ?
            AND (bill_number IS NULL OR bill_number = '')
    ");

    while ($row = $result->fetch_assoc()) {
        $orderId = (int) $row['order_id'];
        $billNumber = build_order_bill_number((string) ($row['branch_name'] ?? ''), (string) ($row['order_date'] ?? ''), $orderId);
        $updateStmt->bind_param('si', $billNumber, $orderId);
        $updateStmt->execute();
    }

    $updateStmt->close();
}

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

    if ($checked) {
        return;
    }

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

        return;
    }

    $checked = true;
    $columnResult = $conn->query("SHOW COLUMNS FROM orders LIKE 'branch_name'");

    if ($columnResult instanceof mysqli_result && $columnResult->num_rows > 0) {
        return;
    }

    if (!$conn->query("ALTER TABLE orders ADD COLUMN branch_name VARCHAR(120) NULL AFTER employee_id")) {
        throw new RuntimeException('Cannot add branch_name column: ' . $conn->error);
    }

    $conn->query("ALTER TABLE orders ADD INDEX idx_orders_branch_name (branch_name)");
}

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

    if ($checked) {
        return;
    }

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

        return;
    }

    $checked = true;
    $columnResult = $conn->query("SHOW COLUMNS FROM orders LIKE 'return_at'");

    if ($columnResult instanceof mysqli_result && $columnResult->num_rows > 0) {
        return;
    }

    if (!$conn->query("ALTER TABLE orders ADD COLUMN return_at DATETIME NULL AFTER scheduled_at")) {
        throw new RuntimeException('Cannot add return_at column: ' . $conn->error);
    }

    $conn->query("ALTER TABLE orders ADD INDEX idx_orders_return_at (return_at)");
}

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

    if ($checked) {
        return;
    }

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

        return;
    }

    $checked = true;
    $columnResult = $conn->query("SHOW COLUMNS FROM orders LIKE 'slide_send_at'");

    if ($columnResult instanceof mysqli_result && $columnResult->num_rows > 0) {
        return;
    }

    if (!$conn->query("ALTER TABLE orders ADD COLUMN slide_send_at DATETIME NULL AFTER scheduled_at")) {
        throw new RuntimeException('Cannot add slide_send_at column: ' . $conn->error);
    }
}

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

    if ($checked) {
        return;
    }

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

        return;
    }

    $checked = true;
    $columnResult = $conn->query("SHOW COLUMNS FROM orders LIKE 'service_at'");

    if ($columnResult instanceof mysqli_result && $columnResult->num_rows > 0) {
        return;
    }

    ensure_order_slide_send_column($conn);

    if (!$conn->query("ALTER TABLE orders ADD COLUMN service_at DATETIME NULL AFTER slide_send_at")) {
        throw new RuntimeException('Cannot add service_at column: ' . $conn->error);
    }
}

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

    if ($checked) {
        return;
    }

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

        return;
    }

    $checked = true;
    $columnResult = $conn->query("SHOW COLUMNS FROM orders LIKE 'deposit_method'");

    if (!($columnResult instanceof mysqli_result) || $columnResult->num_rows === 0) {
        if (!$conn->query("ALTER TABLE orders ADD COLUMN deposit_method VARCHAR(30) NULL AFTER deposit_amount")) {
            throw new RuntimeException('Cannot add deposit_method column: ' . $conn->error);
        }

        $conn->query("ALTER TABLE orders ADD INDEX idx_orders_deposit_method (deposit_method)");
    }

    $conn->query("
        UPDATE orders o
        SET o.deposit_method = (
            SELECT p.payment_method
            FROM payments p
            WHERE p.order_id = o.order_id
            ORDER BY CASE WHEN p.note = 'Deposit at booking' THEN 0 ELSE 1 END, p.payment_date ASC, p.payment_id ASC
            LIMIT 1
        )
        WHERE o.deposit_amount > 0
            AND (o.deposit_method IS NULL OR o.deposit_method = '')
    ");
}

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

    if ($checked) {
        return;
    }

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

        return;
    }

    $checked = true;

    ensure_order_deposit_method_column($conn);

    $columns = array(
        'admin_deposit_amount' => "ALTER TABLE orders ADD COLUMN admin_deposit_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER deposit_method",
        'admin_deposit_method' => "ALTER TABLE orders ADD COLUMN admin_deposit_method VARCHAR(30) NULL AFTER admin_deposit_amount",
        'branch_deposit_amount' => "ALTER TABLE orders ADD COLUMN branch_deposit_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER admin_deposit_method",
        'branch_deposit_method' => "ALTER TABLE orders ADD COLUMN branch_deposit_method VARCHAR(30) NULL AFTER branch_deposit_amount",
    );

    foreach ($columns as $columnName => $alterSql) {
        $columnResult = $conn->query("SHOW COLUMNS FROM orders 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: ' . $conn->error);
            }
        }
    }

    $indexResult = $conn->query("SHOW INDEX FROM orders WHERE Key_name = 'idx_orders_admin_deposit_method'");
    if (!($indexResult instanceof mysqli_result) || $indexResult->num_rows === 0) {
        $conn->query("ALTER TABLE orders ADD INDEX idx_orders_admin_deposit_method (admin_deposit_method)");
    }

    $branchIndexResult = $conn->query("SHOW INDEX FROM orders WHERE Key_name = 'idx_orders_branch_deposit_method'");
    if (!($branchIndexResult instanceof mysqli_result) || $branchIndexResult->num_rows === 0) {
        $conn->query("ALTER TABLE orders ADD INDEX idx_orders_branch_deposit_method (branch_deposit_method)");
    }

    $conn->query("
        UPDATE orders
        SET admin_deposit_amount = deposit_amount
        WHERE deposit_amount > 0
            AND admin_deposit_amount = 0
            AND branch_deposit_amount = 0
    ");

    $conn->query("
        UPDATE orders
        SET admin_deposit_method = deposit_method
        WHERE admin_deposit_amount > 0
            AND (admin_deposit_method IS NULL OR admin_deposit_method = '')
    ");
}

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

    if ($checked) {
        return;
    }

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

        return;
    }

    $checked = true;

    $receivedAmountResult = $conn->query("SHOW COLUMNS FROM payments LIKE 'received_amount'");
    if (!($receivedAmountResult instanceof mysqli_result) || $receivedAmountResult->num_rows === 0) {
        if (!$conn->query("ALTER TABLE payments ADD COLUMN received_amount DECIMAL(10,2) NULL AFTER amount")) {
            throw new RuntimeException('Cannot add received_amount column: ' . $conn->error);
        }
    }

    $changeAmountResult = $conn->query("SHOW COLUMNS FROM payments LIKE 'change_amount'");
    if (!($changeAmountResult instanceof mysqli_result) || $changeAmountResult->num_rows === 0) {
        if (!$conn->query("ALTER TABLE payments ADD COLUMN change_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER received_amount")) {
            throw new RuntimeException('Cannot add change_amount column: ' . $conn->error);
        }
    }

    $conn->query("
        ALTER TABLE payments
        MODIFY COLUMN received_amount DECIMAL(10,2) NULL,
        MODIFY COLUMN change_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00
    ");

    $conn->query("
        UPDATE payments
        SET received_amount = amount
        WHERE received_amount IS NULL
    ");
}

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

    if ($checked) {
        return;
    }

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

        return;
    }

    $checked = true;

    ensure_payment_tender_columns($conn);

    $columns = array(
        'slip_file_path' => "ALTER TABLE payments ADD COLUMN slip_file_path VARCHAR(255) NULL AFTER note",
        'slip_file_name' => "ALTER TABLE payments ADD COLUMN slip_file_name VARCHAR(255) NULL AFTER slip_file_path",
        'slip_mime_type' => "ALTER TABLE payments ADD COLUMN slip_mime_type VARCHAR(100) NULL AFTER slip_file_name",
        'slip_uploaded_at' => "ALTER TABLE payments ADD COLUMN slip_uploaded_at DATETIME NULL AFTER slip_mime_type",
    );

    foreach ($columns as $columnName => $alterSql) {
        $columnResult = $conn->query("SHOW COLUMNS FROM payments 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: ' . $conn->error);
            }
        }
    }
}

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

    if ($checked) {
        return;
    }

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

        return;
    }

    $checked = true;

    $columnResult = $conn->query("SHOW COLUMNS FROM customers LIKE 'customer_group'");

    if (!($columnResult instanceof mysqli_result) || $columnResult->num_rows === 0) {
        if (!$conn->query("ALTER TABLE customers ADD COLUMN customer_group ENUM('member', 'guest') NOT NULL DEFAULT 'member' AFTER loyalty_points")) {
            throw new RuntimeException('Cannot add customer_group column: ' . $conn->error);
        }
    }

    $indexResult = $conn->query("SHOW INDEX FROM customers WHERE Key_name = 'idx_customers_group'");
    if (!($indexResult instanceof mysqli_result) || $indexResult->num_rows === 0) {
        $conn->query("ALTER TABLE customers ADD INDEX idx_customers_group (customer_group)");
    }

    $phoneColumnResult = $conn->query("SHOW COLUMNS FROM customers LIKE 'normalized_phone'");
    if (!($phoneColumnResult instanceof mysqli_result) || $phoneColumnResult->num_rows === 0) {
        if (!$conn->query("ALTER TABLE customers ADD COLUMN normalized_phone VARCHAR(32) NOT NULL DEFAULT '' AFTER phone")) {
            throw new RuntimeException('Cannot add normalized_phone column: ' . $conn->error);
        }
    }

    $phoneIndexResult = $conn->query("SHOW INDEX FROM customers WHERE Key_name = 'idx_customers_normalized_phone'");
    if (!($phoneIndexResult instanceof mysqli_result) || $phoneIndexResult->num_rows === 0) {
        $conn->query("ALTER TABLE customers ADD INDEX idx_customers_normalized_phone (normalized_phone)");
    }

    $conn->query("
        UPDATE customers c
        SET c.normalized_phone = " . normalized_phone_sql('COALESCE(c.phone, \'\')') . "
        WHERE c.normalized_phone <> " . normalized_phone_sql('COALESCE(c.phone, \'\')') . "
            OR c.normalized_phone IS NULL
    ");

    $conn->query("
        UPDATE customers c
        SET c.customer_group = 'guest'
        WHERE (c.customer_group IS NULL OR c.customer_group = '' OR c.customer_group = 'member')
            AND (c.normalized_phone = '' OR c.phone IS NULL)
            AND EXISTS (
                SELECT 1
                FROM orders o
                WHERE o.customer_id = c.customer_id
                    AND o.order_source = 'quick_service'
            )
            AND NOT EXISTS (
                SELECT 1
                FROM orders o2
                WHERE o2.customer_id = c.customer_id
                    AND o2.order_source <> 'quick_service'
            )
    ");

    $conn->query("
        UPDATE customers
        SET customer_group = 'member'
        WHERE customer_group IS NULL
            OR customer_group = ''
    ");
}

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

    if ($checked) {
        return;
    }

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

        return;
    }

    $checked = true;
    $columns = array(
        'cancelled_at' => "ALTER TABLE orders ADD COLUMN cancelled_at DATETIME NULL AFTER finished_at",
        'cancelled_by_employee_id' => "ALTER TABLE orders ADD COLUMN cancelled_by_employee_id INT NULL AFTER cancelled_at",
        'cancellation_reason' => "ALTER TABLE orders ADD COLUMN cancellation_reason TEXT NULL AFTER cancelled_by_employee_id",
    );

    foreach ($columns as $columnName => $alterSql) {
        $columnResult = $conn->query("SHOW COLUMNS FROM orders 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: ' . $conn->error);
            }
        }
    }

    $indexResult = $conn->query("SHOW INDEX FROM orders WHERE Key_name = 'idx_orders_cancelled_at'");
    if (!($indexResult instanceof mysqli_result) || $indexResult->num_rows === 0) {
        $conn->query("ALTER TABLE orders ADD INDEX idx_orders_cancelled_at (cancelled_at)");
    }

    $employeeIndexResult = $conn->query("SHOW INDEX FROM orders WHERE Key_name = 'idx_orders_cancelled_by_employee'");
    if (!($employeeIndexResult instanceof mysqli_result) || $employeeIndexResult->num_rows === 0) {
        $conn->query("ALTER TABLE orders ADD INDEX idx_orders_cancelled_by_employee (cancelled_by_employee_id)");
    }

    $conn->query("
        UPDATE orders
        SET cancelled_at = COALESCE(cancelled_at, updated_at, order_date)
        WHERE status = 'cancelled'
            AND cancelled_at IS NULL
    ");
}

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

    if ($checked) {
        return;
    }

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

        return;
    }

    $checked = true;
    $columnResult = $conn->query("SHOW COLUMNS FROM orders LIKE 'order_source'");

    if (!($columnResult instanceof mysqli_result) || $columnResult->num_rows === 0) {
        if (!$conn->query("ALTER TABLE orders ADD COLUMN order_source ENUM('booking', 'quick_service') NOT NULL DEFAULT 'booking' AFTER return_at")) {
            throw new RuntimeException('Cannot add order_source column: ' . $conn->error);
        }
    }

    $conn->query("
        ALTER TABLE orders
        MODIFY COLUMN order_source ENUM('booking', 'quick_service') NOT NULL DEFAULT 'booking'
    ");

    $indexResult = $conn->query("SHOW INDEX FROM orders WHERE Key_name = 'idx_orders_order_source'");
    if (!($indexResult instanceof mysqli_result) || $indexResult->num_rows === 0) {
        $conn->query("ALTER TABLE orders ADD INDEX idx_orders_order_source (order_source)");
    }

    $conn->query("
        UPDATE orders
        SET order_source = 'booking'
        WHERE order_source IS NULL OR order_source = ''
    ");
}

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

    if ($checked) {
        return;
    }

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

        return;
    }

    $checked = true;
    $columnResult = $conn->query("SHOW COLUMNS FROM orders LIKE 'reopened_from_order_id'");

    if (!($columnResult instanceof mysqli_result) || $columnResult->num_rows === 0) {
        $cancellationColumnResult = $conn->query("SHOW COLUMNS FROM orders LIKE 'cancellation_reason'");
        $afterColumn = ($cancellationColumnResult instanceof mysqli_result && $cancellationColumnResult->num_rows > 0)
            ? 'cancellation_reason'
            : 'notes';

        if (!$conn->query("ALTER TABLE orders ADD COLUMN reopened_from_order_id INT NULL AFTER {$afterColumn}")) {
            throw new RuntimeException('Cannot add reopened_from_order_id column: ' . $conn->error);
        }
    }

    $indexResult = $conn->query("SHOW INDEX FROM orders WHERE Key_name = 'idx_orders_reopened_from'");
    if (!($indexResult instanceof mysqli_result) || $indexResult->num_rows === 0) {
        $conn->query("ALTER TABLE orders ADD INDEX idx_orders_reopened_from (reopened_from_order_id)");
    }
}

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

    if ($checked) {
        return;
    }

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

        return;
    }

    $checked = true;

    ensure_order_branch_column($conn);
    ensure_order_return_column($conn);
    ensure_order_source_column($conn);
    ensure_order_cancellation_columns($conn);

    $indexStatements = array(
        'idx_orders_report_period' => "ALTER TABLE orders ADD INDEX idx_orders_report_period (order_date, branch_name, status, order_source, payment_status)",
        'idx_orders_status_recent' => "ALTER TABLE orders ADD INDEX idx_orders_status_recent (status, order_date, order_id)",
        'idx_orders_customer_recent' => "ALTER TABLE orders ADD INDEX idx_orders_customer_recent (customer_id, order_date, order_id)",
        'idx_orders_queue_board' => "ALTER TABLE orders ADD INDEX idx_orders_queue_board (status, order_source, branch_name, order_id)",
        'idx_orders_pickup_board' => "ALTER TABLE orders ADD INDEX idx_orders_pickup_board (order_source, return_at, branch_name, order_id)",
        'idx_orders_void_report' => "ALTER TABLE orders ADD INDEX idx_orders_void_report (status, cancelled_at, branch_name, order_id)",
        'idx_payments_date_order' => "ALTER TABLE payments ADD INDEX idx_payments_date_order (payment_date, order_id)",
        'idx_payments_order_date_id' => "ALTER TABLE payments ADD INDEX idx_payments_order_date_id (order_id, payment_date, payment_id)",
        'idx_order_details_order_detail' => "ALTER TABLE order_details ADD INDEX idx_order_details_order_detail (order_id, detail_id)",
        'idx_queue_order_number' => "ALTER TABLE queue ADD INDEX idx_queue_order_number (order_id, queue_number)",
    );

    foreach ($indexStatements as $indexName => $statement) {
        if (str_starts_with($indexName, 'idx_payments_')) {
            $tableName = 'payments';
        } elseif (str_starts_with($indexName, 'idx_order_details_')) {
            $tableName = 'order_details';
        } elseif (str_starts_with($indexName, 'idx_queue_')) {
            $tableName = 'queue';
        } else {
            $tableName = 'orders';
        }

        $indexResult = $conn->query("SHOW INDEX FROM {$tableName} WHERE Key_name = '" . $conn->real_escape_string($indexName) . "'");
        if (!($indexResult instanceof mysqli_result) || $indexResult->num_rows === 0) {
            $conn->query($statement);
        }
    }
}
