<?php
/*
|--------------------------------------------------------------------------
| FinoviaPay Telegram Bot - Digital Service Enablement Request Functions
|--------------------------------------------------------------------------
| Separate request flow for customers whose digital banking service is not
| enabled yet. This module is intentionally kept separate from the normal
| support ticket system.
|--------------------------------------------------------------------------
*/

if (file_exists(__DIR__ . '/bot_token.php')) {
    require_once __DIR__ . '/bot_token.php';
}

if (!function_exists('botDigitalServiceTableSql')) {
    function botDigitalServiceTableSql(): array
    {
        return [
            "CREATE TABLE IF NOT EXISTS digital_service_tickets (
                id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                reference VARCHAR(32) NOT NULL UNIQUE,
                telegram_chat_id BIGINT NULL,
                telegram_user_id BIGINT NULL,
                user_id INT NULL,
                customer_name VARCHAR(190) NOT NULL,
                document_type VARCHAR(50) NOT NULL DEFAULT 'OTHER',
                document_number VARCHAR(120) NOT NULL,
                request_message TEXT NOT NULL,
                status VARCHAR(40) NOT NULL DEFAULT 'NEW',
                priority VARCHAR(20) NOT NULL DEFAULT 'NORMAL',
                assigned_officer_id INT NULL,
                assigned_officer_name VARCHAR(190) NULL,
                service_key VARCHAR(80) NOT NULL DEFAULT 'digital_banking',
                requested_documents_text TEXT NULL,
                last_customer_reply_at DATETIME NULL,
                last_officer_reply_at DATETIME NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_status (status),
                INDEX idx_reference (reference),
                INDEX idx_chat_id (telegram_chat_id),
                INDEX idx_user_id (user_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

            "CREATE TABLE IF NOT EXISTS digital_service_ticket_messages (
                id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                ticket_id INT UNSIGNED NOT NULL,
                sender_type VARCHAR(20) NOT NULL,
                sender_name VARCHAR(190) NULL,
                message_text TEXT NOT NULL,
                is_internal TINYINT(1) NOT NULL DEFAULT 0,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                CONSTRAINT fk_digital_service_message_ticket
                    FOREIGN KEY (ticket_id) REFERENCES digital_service_tickets(id)
                    ON DELETE CASCADE,
                INDEX idx_ticket_id (ticket_id),
                INDEX idx_sender_type (sender_type)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

            "CREATE TABLE IF NOT EXISTS digital_service_ticket_documents (
                id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                ticket_id INT UNSIGNED NOT NULL,
                requested_by VARCHAR(20) NOT NULL DEFAULT 'OFFICER',
                document_label VARCHAR(190) NOT NULL,
                notes TEXT NULL,
                file_path VARCHAR(255) NULL,
                file_name VARCHAR(255) NULL,
                file_type VARCHAR(100) NULL,
                status VARCHAR(30) NOT NULL DEFAULT 'REQUESTED',
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                CONSTRAINT fk_digital_service_document_ticket
                    FOREIGN KEY (ticket_id) REFERENCES digital_service_tickets(id)
                    ON DELETE CASCADE,
                INDEX idx_ticket_doc_id (ticket_id),
                INDEX idx_doc_status (status)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
        ];
    }
}

if (!function_exists('botInstallDigitalServiceTables')) {
    function botInstallDigitalServiceTables(PDO $pdo): void
    {
        foreach (botDigitalServiceTableSql() as $sql) {
            $pdo->exec($sql);
        }
    }
}


if (!function_exists('botDsTableColumns')) {
    function botDsTableColumns(PDO $pdo, string $table): array
    {
        static $cache = [];
        $key = spl_object_hash($pdo) . '|' . $table;
        if (isset($cache[$key])) {
            return $cache[$key];
        }

        $cols = [];
        try {
            $stmt = $pdo->query("SHOW COLUMNS FROM `" . str_replace('`', '``', $table) . "`");
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $name = (string)($row['Field'] ?? '');
                if ($name !== '') {
                    $cols[$name] = true;
                }
            }
        } catch (Throwable $e) {
            $cols = [];
        }

        $cache[$key] = $cols;
        return $cols;
    }
}

if (!function_exists('botDsHasColumn')) {
    function botDsHasColumn(PDO $pdo, string $table, string $column): bool
    {
        $cols = botDsTableColumns($pdo, $table);
        return isset($cols[$column]);
    }
}

if (!function_exists('botDsTicketRefColumn')) {
    function botDsTicketRefColumn(PDO $pdo): string
    {
        return botDsHasColumn($pdo, 'digital_service_tickets', 'reference') ? 'reference' : 'reference_no';
    }
}

if (!function_exists('botDsMessageBodyColumn')) {
    function botDsMessageBodyColumn(PDO $pdo): string
    {
        return botDsHasColumn($pdo, 'digital_service_ticket_messages', 'message_text') ? 'message_text' : 'message';
    }
}

if (!function_exists('botDsDocLabelColumn')) {
    function botDsDocLabelColumn(PDO $pdo): string
    {
        return botDsHasColumn($pdo, 'digital_service_ticket_documents', 'document_label') ? 'document_label' : 'document_name';
    }
}

if (!function_exists('botDsDocNotesColumn')) {
    function botDsDocNotesColumn(PDO $pdo): string
    {
        return botDsHasColumn($pdo, 'digital_service_ticket_documents', 'notes') ? 'notes' : 'document_description';
    }
}


if (!function_exists('botDsResolveTelegramToken')) {
    function botDsResolveTelegramToken(): string
    {
        $constantCandidates = [
            'TELEGRAM_BOT_TOKEN',
            'BOT_TOKEN',
            'TELEGRAM_TOKEN',
            'TOKEN',
        ];

        foreach ($constantCandidates as $constName) {
            if (defined($constName)) {
                $value = trim((string) constant($constName));
                if ($value !== '') {
                    return $value;
                }
            }
        }

        if (isset($GLOBALS['token']) && is_scalar($GLOBALS['token'])) {
            $value = trim((string) $GLOBALS['token']);
            if ($value !== '') {
                return $value;
            }
        }

        $globalCandidates = [
            'token',
            'bot_token',
            'telegram_token',
            'telegram_bot_token',
            'BOT_TOKEN',
            'TELEGRAM_BOT_TOKEN',
            'TELEGRAM_TOKEN',
        ];

        foreach ($globalCandidates as $globalKey) {
            if (!empty($GLOBALS[$globalKey]) && is_scalar($GLOBALS[$globalKey])) {
                $value = trim((string) $GLOBALS[$globalKey]);
                if ($value !== '') {
                    return $value;
                }
            }
        }

        $arrayCandidates = [
            ['config', 'telegram_bot_token'],
            ['config', 'bot_token'],
            ['config', 'telegram_token'],
            ['settings', 'telegram_bot_token'],
            ['settings', 'bot_token'],
            ['settings', 'telegram_token'],
        ];

        foreach ($arrayCandidates as [$rootKey, $childKey]) {
            if (!empty($GLOBALS[$rootKey]) && is_array($GLOBALS[$rootKey]) && !empty($GLOBALS[$rootKey][$childKey])) {
                $value = trim((string) $GLOBALS[$rootKey][$childKey]);
                if ($value !== '') {
                    return $value;
                }
            }
        }

        $envCandidates = [
            'TELEGRAM_BOT_TOKEN',
            'BOT_TOKEN',
            'TELEGRAM_TOKEN',
            'TOKEN',
        ];

        foreach ($envCandidates as $envKey) {
            $envValue = getenv($envKey);
            if ($envValue !== false) {
                $envValue = trim((string) $envValue);
                if ($envValue !== '') {
                    return $envValue;
                }
            }
        }

        return '';
    }
}

if (!function_exists('botDsSendTelegramMessage')) {
    function botDsSendTelegramMessage($chatId, string $message, ?array $replyMarkup = null): bool
    {
        $token = botDsResolveTelegramToken();
        $chatId = trim((string)$chatId);
        if ($token === '' || $chatId === '') {
            $parts = [];
            if ($token === '') {
                $parts[] = 'token missing';
            }
            if ($chatId === '') {
                $parts[] = 'chat id missing';
            }
            error_log('BOT ERROR: Digital service Telegram notification could not be sent (' . implode(', ', $parts) . ').');
            return false;
        }

        $payload = [
            'chat_id' => $chatId,
            'text' => $message,
            'parse_mode' => 'HTML'
        ];

        if (!empty($replyMarkup)) {
            $payload['reply_markup'] = json_encode($replyMarkup);
        }

        $url = "https://api.telegram.org/bot{$token}/sendMessage";

        if (function_exists('curl_init')) {
            $ch = curl_init();
            curl_setopt($ch, CURLOPT_URL, $url);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
            curl_setopt($ch, CURLOPT_POST, true);
            curl_setopt($ch, CURLOPT_POSTFIELDS, $payload);
            curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 20);
            curl_setopt($ch, CURLOPT_TIMEOUT, 30);
            curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
            curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
            $response = curl_exec($ch);
            $error = curl_error($ch);
            curl_close($ch);

            if ($response === false) {
                error_log('BOT ERROR: Digital service Telegram notification failed. ' . $error);
                return false;
            }

            return true;
        }

        $context = stream_context_create([
            'http' => [
                'method'  => 'POST',
                'header'  => "Content-type: application/x-www-form-urlencoded
",
                'content' => http_build_query($payload),
                'timeout' => 30,
            ],
            'ssl' => [
                'verify_peer'      => false,
                'verify_peer_name' => false,
            ],
        ]);

        $response = @file_get_contents($url, false, $context);
        if ($response === false) {
            error_log('BOT ERROR: Digital service Telegram notification failed via file_get_contents fallback.');
            return false;
        }

        return true;
    }
}

if (!function_exists('botDsCustomerInlineKeyboard')) {
    function botDsCustomerInlineKeyboard(): array
    {
        return [
            'inline_keyboard' => [
                [
                    ['text' => '👨‍💼 Speak with Customer Service', 'callback_data' => 'contact_customer_service']
                ],
                [
                    ['text' => '⬅️ Back to Main Menu', 'callback_data' => 'main_menu']
                ]
            ]
        ];
    }
}

if (!function_exists('botNotifyDigitalServiceOfficerReply')) {
    function botNotifyDigitalServiceOfficerReply(PDO $pdo, int $ticketId, string $reply, string $officerName = 'Banking Officer'): bool
    {
        $ticket = botGetDigitalServiceTicketById($pdo, $ticketId);
        if (!$ticket || empty($ticket['telegram_chat_id'])) {
            error_log('BOT ERROR: Digital service officer reply notification skipped. Missing ticket or telegram_chat_id.');
            return false;
        }

        $reference = htmlspecialchars((string)($ticket['reference'] ?? $ticket['reference_no'] ?? 'N/A'), ENT_QUOTES, 'UTF-8');
        $customerName = htmlspecialchars((string)($ticket['customer_name'] ?? 'Customer'), ENT_QUOTES, 'UTF-8');
        $officerName = htmlspecialchars(trim($officerName) !== '' ? $officerName : 'Banking Officer', ENT_QUOTES, 'UTF-8');
        $reply = htmlspecialchars(trim($reply), ENT_QUOTES, 'UTF-8');

        $text = "💬 <b>Digital Service Request Update</b>

"
              . "<b>Reference Number</b>
{$reference}

"
              . "<b>Customer Name</b>
{$customerName}

"
              . "<b>Officer Reply</b>
{$reply}

"
              . "This update has been issued by <b>{$officerName}</b>. Please review the message carefully and contact FinoviaPay Customer Service if further assistance is required.";

        return botDsSendTelegramMessage((string)$ticket['telegram_chat_id'], $text, botDsCustomerInlineKeyboard());
    }
}

if (!function_exists('botNotifyDigitalServiceDocumentRequest')) {
    function botNotifyDigitalServiceDocumentRequest(PDO $pdo, int $ticketId, string $documentLabel, string $notes = '', string $officerName = 'Banking Officer'): bool
    {
        $ticket = botGetDigitalServiceTicketById($pdo, $ticketId);
        if (!$ticket || empty($ticket['telegram_chat_id'])) {
            error_log('BOT ERROR: Digital service document request notification skipped. Missing ticket or telegram_chat_id.');
            return false;
        }

        $reference = htmlspecialchars((string)($ticket['reference'] ?? $ticket['reference_no'] ?? 'N/A'), ENT_QUOTES, 'UTF-8');
        $customerName = htmlspecialchars((string)($ticket['customer_name'] ?? 'Customer'), ENT_QUOTES, 'UTF-8');
        $officerName = htmlspecialchars(trim($officerName) !== '' ? $officerName : 'Banking Officer', ENT_QUOTES, 'UTF-8');
        $documentLabel = htmlspecialchars(trim($documentLabel), ENT_QUOTES, 'UTF-8');
        $notes = htmlspecialchars(trim($notes), ENT_QUOTES, 'UTF-8');

        $text = "📄 <b>Additional Documentation Required</b>

"
              . "<b>Reference Number</b>
{$reference}

"
              . "<b>Customer Name</b>
{$customerName}

"
              . "<b>Requested Document</b>
{$documentLabel}

";

        if ($notes !== '') {
            $text .= "<b>Officer Instructions</b>
{$notes}

";
        }

        $text .= "This request has been issued by <b>{$officerName}</b>. Please provide the requested document for your digital banking service activation review.";

        return botDsSendTelegramMessage((string)$ticket['telegram_chat_id'], $text, botDsCustomerInlineKeyboard());
    }
}

if (!function_exists('botDigitalServiceKeyboard')) {
    function botDigitalServiceKeyboard(): array
    {
        return [
            [['text' => '👨‍💼 Speak with Customer Service', 'callback_data' => 'contact_customer_service']],
            [['text' => '📝 Submit Enablement Request', 'callback_data' => 'start_digital_service_request']],
            [['text' => '⬅️ Back to Main Menu', 'callback_data' => 'main_menu']],
        ];
    }
}

if (!function_exists('botDigitalServiceDocTypeKeyboard')) {
    function botDigitalServiceDocTypeKeyboard(): array
    {
        return [
            [
                ['text' => '🪪 National ID', 'callback_data' => 'ds_doc_type:NATIONAL_ID'],
                ['text' => '🛂 Passport', 'callback_data' => 'ds_doc_type:PASSPORT']
            ],
            [
                ['text' => '🧾 Driver License', 'callback_data' => 'ds_doc_type:DRIVER_LICENSE'],
                ['text' => '📄 Other Document', 'callback_data' => 'ds_doc_type:OTHER']
            ],
            [
                ['text' => '⬅️ Back', 'callback_data' => 'main_menu']
            ]
        ];
    }
}

if (!function_exists('botDigitalServiceStatuses')) {
    function botDigitalServiceStatuses(): array
    {
        return ['NEW', 'UNDER_REVIEW', 'DOCUMENTS_REQUIRED', 'CUSTOMER_REPLIED', 'APPROVED', 'REJECTED', 'SERVICE_ENABLED', 'CLOSED'];
    }
}

if (!function_exists('botGenerateDigitalServiceReference')) {
    function botGenerateDigitalServiceReference(): string
    {
        try {
            $rand = strtoupper(bin2hex(random_bytes(3)));
        } catch (Throwable $e) {
            $rand = strtoupper(substr(md5((string)mt_rand()), 0, 6));
        }

        return 'DSR-' . date('ymd') . '-' . $rand;
    }
}

if (!function_exists('botStartDigitalServiceRequestFlow')) {
    function botStartDigitalServiceRequestFlow(array &$states, string $state_file, $chat_id, array $userContext = []): array
    {
        $states[$chat_id]['step'] = 'digital_service_request_name';
        $states[$chat_id]['digital_service_request'] = [
            'customer_name'   => trim((string)($userContext['name'] ?? '')),
            'document_type'   => '',
            'document_number' => '',
            'request_message' => '',
            'user_id'         => $userContext['id'] ?? null,
            'telegram_user_id'=> $userContext['telegram_user_id'] ?? null,
        ];

        if (function_exists('saveStates')) {
            saveStates($states, $state_file);
        }

        return [
            'text' => "📝 <b>Digital Banking Service Enablement Request</b>\n\nPlease enter your full name exactly as it appears on your official banking or identity record.",
            'buttons' => function_exists('botStepNavigationKeyboard') ? botStepNavigationKeyboard(true) : []
        ];
    }
}

if (!function_exists('botHandleDigitalServiceCallback')) {
    function botHandleDigitalServiceCallback(PDO $pdo, array &$states, string $state_file, $chat_id, string $callbackData, array $userContext = []): ?array
    {
        if ($callbackData === 'start_digital_service_request') {
            return botStartDigitalServiceRequestFlow($states, $state_file, $chat_id, $userContext);
        }

        if (strpos($callbackData, 'ds_doc_type:') === 0) {
            $type = strtoupper(trim(substr($callbackData, strlen('ds_doc_type:'))));
            if (!in_array($type, ['NATIONAL_ID', 'PASSPORT', 'DRIVER_LICENSE', 'OTHER'], true)) {
                return null;
            }

            if (!isset($states[$chat_id]['digital_service_request'])) {
                return null;
            }

            $states[$chat_id]['digital_service_request']['document_type'] = $type;
            $states[$chat_id]['step'] = 'digital_service_request_document_number';
            if (function_exists('saveStates')) {
                saveStates($states, $state_file);
            }

            return [
                'text' => "🔐 <b>Document Verification</b>\n\nSelected document type: <b>" . htmlspecialchars(str_replace('_', ' ', $type), ENT_QUOTES, 'UTF-8') . "</b>\n\nPlease enter your document number exactly as shown on the selected document.",
                'buttons' => function_exists('botStepNavigationKeyboard') ? botStepNavigationKeyboard(true) : []
            ];
        }

        return null;
    }
}

if (!function_exists('botHandleDigitalServiceTextStep')) {
    function botHandleDigitalServiceTextStep(PDO $pdo, array &$states, string $state_file, $chat_id, string $text): ?array
    {
        $state = $states[$chat_id] ?? [];
        $step  = $state['step'] ?? '';

        if (strpos($step, 'digital_service_request_') !== 0) {
            return null;
        }

        if (!isset($states[$chat_id]['digital_service_request'])) {
            $states[$chat_id]['digital_service_request'] = [];
        }

        $payload = &$states[$chat_id]['digital_service_request'];
        $text = trim($text);

        if ($step === 'digital_service_request_name') {
            if ($text === '' || mb_strlen($text) < 3) {
                return ['text' => "⚠️ Please enter a valid full name to continue.", 'buttons' => function_exists('botStepNavigationKeyboard') ? botStepNavigationKeyboard(true) : []];
            }

            $payload['customer_name'] = mb_substr($text, 0, 190);
            $states[$chat_id]['step'] = 'digital_service_request_document_type';
            if (function_exists('saveStates')) {
                saveStates($states, $state_file);
            }

            return [
                'text' => "🪪 <b>Select Document Type</b>\n\nPlease choose the document you want to use for identity verification for this request.",
                'buttons' => botDigitalServiceDocTypeKeyboard()
            ];
        }

        if ($step === 'digital_service_request_document_number') {
            if ($text === '' || mb_strlen($text) < 4) {
                return ['text' => "⚠️ Please enter a valid document number.", 'buttons' => function_exists('botStepNavigationKeyboard') ? botStepNavigationKeyboard(true) : []];
            }

            $payload['document_number'] = mb_substr($text, 0, 120);
            $states[$chat_id]['step'] = 'digital_service_request_message';
            if (function_exists('saveStates')) {
                saveStates($states, $state_file);
            }

            return [
                'text' => "💬 <b>Request Details</b>\n\nPlease write your message for the banking officer.\n\nExample: I would like to request activation of my digital banking access. Please review my profile and let me know if any additional documents are required.",
                'buttons' => function_exists('botStepNavigationKeyboard') ? botStepNavigationKeyboard(true) : []
            ];
        }

        if ($step === 'digital_service_request_message') {
            if ($text === '' || mb_strlen($text) < 10) {
                return ['text' => "⚠️ Please write a complete request message so the banking officer can review it properly.", 'buttons' => function_exists('botStepNavigationKeyboard') ? botStepNavigationKeyboard(true) : []];
            }

            $payload['request_message'] = mb_substr($text, 0, 5000);
            $ticket = botCreateDigitalServiceTicket($pdo, [
                'telegram_chat_id'   => $chat_id,
                'telegram_user_id'   => $payload['telegram_user_id'] ?? null,
                'user_id'            => $payload['user_id'] ?? null,
                'customer_name'      => (string)($payload['customer_name'] ?? ''),
                'document_type'      => (string)($payload['document_type'] ?? 'OTHER'),
                'document_number'    => (string)($payload['document_number'] ?? ''),
                'request_message'    => (string)($payload['request_message'] ?? ''),
                'service_key'        => 'digital_banking',
            ]);

            unset($states[$chat_id]['digital_service_request']);
            $states[$chat_id]['step'] = 'digital_banking_service_disabled';
            if (function_exists('saveStates')) {
                saveStates($states, $state_file);
            }

            return [
                'text' => botFormatDigitalServiceTicketSubmittedMessage($ticket),
                'buttons' => [
                    [['text' => '👨‍💼 Speak with Customer Service', 'callback_data' => 'contact_customer_service']],
                    [['text' => '⬅️ Back to Main Menu', 'callback_data' => 'main_menu']]
                ]
            ];
        }

        return null;
    }
}

if (!function_exists('botCreateDigitalServiceTicket')) {
    function botCreateDigitalServiceTicket(PDO $pdo, array $data): array
    {
        botInstallDigitalServiceTables($pdo);

        $reference = botGenerateDigitalServiceReference();
        $refCol = botDsTicketRefColumn($pdo);

        $ticketColumns = botDsTableColumns($pdo, 'digital_service_tickets');
        $cols = [$refCol, 'telegram_chat_id', 'user_id', 'customer_name', 'document_type', 'document_number', 'request_message', 'status'];
        $vals = [':reference', ':telegram_chat_id', ':user_id', ':customer_name', ':document_type', ':document_number', ':request_message', "'NEW'"];

        if (isset($ticketColumns['telegram_user_id'])) {
            $cols[] = 'telegram_user_id';
            $vals[] = ':telegram_user_id';
        }
        if (isset($ticketColumns['priority'])) {
            $cols[] = 'priority';
            $vals[] = "'NORMAL'";
        }
        if (isset($ticketColumns['service_key'])) {
            $cols[] = 'service_key';
            $vals[] = ':service_key';
        }

        $sql = "INSERT INTO digital_service_tickets (" . implode(', ', $cols) . ") VALUES (" . implode(', ', $vals) . ")";
        $stmt = $pdo->prepare($sql);

        $params = [
            ':reference'        => $reference,
            ':telegram_chat_id' => $data['telegram_chat_id'] ?? null,
            ':user_id'          => $data['user_id'] ?? null,
            ':customer_name'    => $data['customer_name'],
            ':document_type'    => $data['document_type'] ?: 'OTHER',
            ':document_number'  => $data['document_number'],
            ':request_message'  => $data['request_message'],
        ];
        if (isset($ticketColumns['telegram_user_id'])) {
            $params[':telegram_user_id'] = $data['telegram_user_id'] ?? null;
        }
        if (isset($ticketColumns['service_key'])) {
            $params[':service_key'] = $data['service_key'] ?? 'digital_banking';
        }

        $stmt->execute($params);
        $ticketId = (int)$pdo->lastInsertId();

        $messageCol = botDsMessageBodyColumn($pdo);
        $messageCols = ['ticket_id', 'sender_type', 'sender_name', $messageCol];
        $messageVals = [':ticket_id', ':sender_type', ':sender_name', ':message_body'];
        if (botDsHasColumn($pdo, 'digital_service_ticket_messages', 'is_internal')) {
            $messageCols[] = 'is_internal';
            $messageVals[] = '0';
        }

        $msgSql = "INSERT INTO digital_service_ticket_messages (" . implode(', ', $messageCols) . ")
                   VALUES (" . implode(', ', $messageVals) . ")";
        $msg = $pdo->prepare($msgSql);
        $msg->execute([
            ':ticket_id' => $ticketId,
            ':sender_type' => botDsHasColumn($pdo, 'digital_service_ticket_messages', 'sender_type') ? 'CUSTOMER' : 'customer',
            ':sender_name' => $data['customer_name'],
            ':message_body' => $data['request_message'],
        ]);

        return botGetDigitalServiceTicketById($pdo, $ticketId) ?: [
            'id' => $ticketId,
            'reference' => $reference,
            'reference_no' => $reference,
            'customer_name' => $data['customer_name'],
            'status' => 'NEW'
        ];
    }
}

if (!function_exists('botGetDigitalServiceTicketById')) {
    function botGetDigitalServiceTicketById(PDO $pdo, int $ticketId): ?array
    {
        $refCol = botDsTicketRefColumn($pdo);
        $stmt = $pdo->prepare("SELECT *, {$refCol} AS reference FROM digital_service_tickets WHERE id = :id LIMIT 1");
        $stmt->execute([':id' => $ticketId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }
}

if (!function_exists('botGetDigitalServiceTicketByReference')) {
    function botGetDigitalServiceTicketByReference(PDO $pdo, string $reference): ?array
    {
        $refCol = botDsTicketRefColumn($pdo);
        $stmt = $pdo->prepare("SELECT *, {$refCol} AS reference FROM digital_service_tickets WHERE {$refCol} = :reference LIMIT 1");
        $stmt->execute([':reference' => $reference]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }
}

if (!function_exists('botAddDigitalServiceOfficerReply')) {
    function botAddDigitalServiceOfficerReply(PDO $pdo, int $ticketId, string $message, string $officerName = 'Banking Officer', string $newStatus = 'UNDER_REVIEW'): bool
    {
        $message = trim($message);
        if ($message === '') {
            return false;
        }

        $pdo->beginTransaction();
        try {
            $messageCol = botDsMessageBodyColumn($pdo);
            $messageCols = ['ticket_id', 'sender_type', 'sender_name', $messageCol];
            $messageVals = [':ticket_id', ':sender_type', ':sender_name', ':message_body'];
            if (botDsHasColumn($pdo, 'digital_service_ticket_messages', 'is_internal')) {
                $messageCols[] = 'is_internal';
                $messageVals[] = '0';
            }

            $stmt = $pdo->prepare("INSERT INTO digital_service_ticket_messages (" . implode(', ', $messageCols) . ")
                                   VALUES (" . implode(', ', $messageVals) . ")");
            $stmt->execute([
                ':ticket_id' => $ticketId,
                ':sender_type' => 'OFFICER',
                ':sender_name' => $officerName,
                ':message_body' => $message,
            ]);

            $set = ["status = :status"];
            if (botDsHasColumn($pdo, 'digital_service_tickets', 'assigned_officer_name')) {
                $set[] = "assigned_officer_name = :officer_name";
            } elseif (botDsHasColumn($pdo, 'digital_service_tickets', 'officer_note')) {
                $set[] = "officer_note = :officer_name";
            }
            if (botDsHasColumn($pdo, 'digital_service_tickets', 'last_officer_reply_at')) {
                $set[] = "last_officer_reply_at = NOW()";
            }

            $upd = $pdo->prepare("UPDATE digital_service_tickets SET " . implode(', ', $set) . " WHERE id = :id");
            $params = [':status' => $newStatus, ':id' => $ticketId];
            if (strpos($upd->queryString, ':officer_name') !== false) {
                $params[':officer_name'] = $officerName;
            }
            $upd->execute($params);

            $pdo->commit();
            return true;
        } catch (Throwable $e) {
            $pdo->rollBack();
            throw $e;
        }
    }
}

if (!function_exists('botRequestDigitalServiceDocuments')) {
    function botRequestDigitalServiceDocuments(PDO $pdo, int $ticketId, string $documentLabel, string $notes = '', string $officerName = 'Banking Officer'): bool
    {
        $documentLabel = trim($documentLabel);
        if ($documentLabel === '') {
            return false;
        }

        $pdo->beginTransaction();
        try {
            $labelCol = botDsDocLabelColumn($pdo);
            $notesCol = botDsDocNotesColumn($pdo);
            $docCols = ['ticket_id', 'requested_by', $labelCol, $notesCol];
            $docVals = [':ticket_id', ':requested_by', ':document_label', ':notes'];

            if (botDsHasColumn($pdo, 'digital_service_ticket_documents', 'status')) {
                $docCols[] = 'status';
                $docVals[] = "'REQUESTED'";
            }
            if (botDsHasColumn($pdo, 'digital_service_ticket_documents', 'is_required')) {
                $docCols[] = 'is_required';
                $docVals[] = '1';
            }
            if (botDsHasColumn($pdo, 'digital_service_ticket_documents', 'is_received')) {
                $docCols[] = 'is_received';
                $docVals[] = '0';
            }

            $doc = $pdo->prepare("INSERT INTO digital_service_ticket_documents (" . implode(', ', $docCols) . ")
                                  VALUES (" . implode(', ', $docVals) . ")");
            $doc->execute([
                ':ticket_id' => $ticketId,
                ':requested_by' => botDsHasColumn($pdo, 'digital_service_ticket_documents', 'requested_by') ? 'OFFICER' : 'officer',
                ':document_label' => $documentLabel,
                ':notes' => $notes,
            ]);

            $message = "Required document requested: {$documentLabel}";
            if ($notes !== '') {
                $message .= "\nNotes: {$notes}";
            }

            $messageCol = botDsMessageBodyColumn($pdo);
            $msgCols = ['ticket_id', 'sender_type', 'sender_name', $messageCol];
            $msgVals = [':ticket_id', ':sender_type', ':sender_name', ':message_body'];
            if (botDsHasColumn($pdo, 'digital_service_ticket_messages', 'is_internal')) {
                $msgCols[] = 'is_internal';
                $msgVals[] = '0';
            }

            $msg = $pdo->prepare("INSERT INTO digital_service_ticket_messages (" . implode(', ', $msgCols) . ")
                                  VALUES (" . implode(', ', $msgVals) . ")");
            $msg->execute([
                ':ticket_id' => $ticketId,
                ':sender_type' => 'OFFICER',
                ':sender_name' => $officerName,
                ':message_body' => $message,
            ]);

            $set = ["status = 'DOCUMENTS_REQUIRED'"];
            if (botDsHasColumn($pdo, 'digital_service_tickets', 'requested_documents_text')) {
                $set[] = "requested_documents_text = CONCAT_WS('\n', NULLIF(requested_documents_text, ''), :requested_doc)";
            } elseif (botDsHasColumn($pdo, 'digital_service_tickets', 'required_documents_note')) {
                $set[] = "required_documents_note = CONCAT_WS('\n', NULLIF(required_documents_note, ''), :requested_doc)";
            }
            if (botDsHasColumn($pdo, 'digital_service_tickets', 'assigned_officer_name')) {
                $set[] = "assigned_officer_name = :officer_name";
            }
            if (botDsHasColumn($pdo, 'digital_service_tickets', 'last_officer_reply_at')) {
                $set[] = "last_officer_reply_at = NOW()";
            }

            $upd = $pdo->prepare("UPDATE digital_service_tickets SET " . implode(', ', $set) . " WHERE id = :ticket_id");
            $params = [':ticket_id' => $ticketId];
            if (strpos($upd->queryString, ':requested_doc') !== false) {
                $params[':requested_doc'] = $documentLabel . ($notes !== '' ? ' - ' . $notes : '');
            }
            if (strpos($upd->queryString, ':officer_name') !== false) {
                $params[':officer_name'] = $officerName;
            }
            $upd->execute($params);

            $pdo->commit();
            return true;
        } catch (Throwable $e) {
            $pdo->rollBack();
            throw $e;
        }
    }
}

if (!function_exists('botGetDigitalServiceTickets')) {
    function botGetDigitalServiceTickets(PDO $pdo, array $filters = []): array
    {
        botInstallDigitalServiceTables($pdo);

        $refCol = botDsTicketRefColumn($pdo);
        $where = [];
        $params = [];

        if (!empty($filters['status'])) {
            $where[] = 'status = :status';
            $params[':status'] = $filters['status'];
        }

        if (!empty($filters['reference'])) {
            $where[] = "{$refCol} = :reference";
            $params[':reference'] = $filters['reference'];
        }

        if (!empty($filters['search'])) {
            $where[] = "({$refCol} LIKE :search OR customer_name LIKE :search OR document_number LIKE :search)";
            $params[':search'] = '%' . $filters['search'] . '%';
        }

        $sql = "SELECT *, {$refCol} AS reference FROM digital_service_tickets";
        if ($where) {
            $sql .= ' WHERE ' . implode(' AND ', $where);
        }
        $sql .= ' ORDER BY created_at DESC';

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }
}

if (!function_exists('botGetDigitalServiceTicketMessages')) {
    function botGetDigitalServiceTicketMessages(PDO $pdo, int $ticketId): array
    {
        $messageCol = botDsMessageBodyColumn($pdo);
        $stmt = $pdo->prepare("SELECT *, {$messageCol} AS message_text FROM digital_service_ticket_messages WHERE ticket_id = :ticket_id ORDER BY created_at ASC, id ASC");
        $stmt->execute([':ticket_id' => $ticketId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }
}

if (!function_exists('botGetDigitalServiceTicketMessages')) {
    function botGetDigitalServiceTicketMessages(PDO $pdo, int $ticketId): array
    {
        $stmt = $pdo->prepare("SELECT * FROM digital_service_ticket_messages WHERE ticket_id = :ticket_id ORDER BY created_at ASC, id ASC");
        $stmt->execute([':ticket_id' => $ticketId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }
}

if (!function_exists('botGetDigitalServiceRequestedDocuments')) {
    function botGetDigitalServiceRequestedDocuments(PDO $pdo, int $ticketId): array
    {
        $labelCol = botDsDocLabelColumn($pdo);
        $notesCol = botDsDocNotesColumn($pdo);
        $statusExpr = botDsHasColumn($pdo, 'digital_service_ticket_documents', 'status') ? "status" : "CASE WHEN COALESCE(is_received,0)=1 THEN 'RECEIVED' ELSE 'PENDING' END";
        $stmt = $pdo->prepare("SELECT *, {$labelCol} AS document_label, {$notesCol} AS notes, {$statusExpr} AS status FROM digital_service_ticket_documents WHERE ticket_id = :ticket_id ORDER BY created_at DESC, id DESC");
        $stmt->execute([':ticket_id' => $ticketId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }
}

if (!function_exists('botFormatDigitalServiceTicketSubmittedMessage')) {
    function botFormatDigitalServiceTicketSubmittedMessage(array $ticket): string
    {
        $reference = htmlspecialchars((string)($ticket['reference'] ?? $ticket['reference_no'] ?? ''), ENT_QUOTES, 'UTF-8');
        $name = htmlspecialchars((string)($ticket['customer_name'] ?? 'Customer'), ENT_QUOTES, 'UTF-8');

        return "✅ <b>Enablement Request Submitted Successfully</b>\n\n"
            . "<b>Reference Number</b>\n{$reference}\n\n"
            . "<b>Customer Name</b>\n{$name}\n\n"
            . "Your digital banking service enablement request has been recorded successfully and forwarded for banking officer review.\n\n"
            . "A customer service or banking operations officer may contact you and may request additional supporting documentation for identity verification and service activation review.";
    }
}