<?php
/**
 * Telegram Payments Bot - Single File MVP
 * PHP 8.1+
 *
 * الوظيفة
 * - يستقبل صورة من تيليجرام
 * - يحملها على الاستضافة داخل /uploads/payments
 * - يرسل الصورة إلى OpenAI لاستخراج
 *   amount / date / time / reference_number / payment_method
 * - يسمح للمحاسب بتأكيد أو تعديل القيم
 * - يطلب رقم الفاتورة
 * - يسجل العملية في MySQL
 * - يفحص التكرار قبل الاعتماد
 *
 * ملاحظات
 * - ملف واحد فقط للتجربة السريعة
 * - يفضل لاحقا فصل الكود إلى ملفات وخدمات منفصلة
 */

/* =========================
   1) الإعدادات
========================= */
const TELEGRAM_BOT_TOKEN = '8776150550:AAHeAIgQk8vIACL4bJzxDzxF4XzftE3PuG8';
const OPENAI_API_KEY     = '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';
const OPENAI_MODEL       = 'gpt-4.1-mini';

const DB_HOST = '127.0.0.1';
const DB_NAME = 'khalidaladdin_driver';
const DB_USER = 'khalidaladdin_userdriver';
const DB_PASS = 'V2O8C#8PqZZQ';

const BASE_URL = 'https://hilooldigital.com/PaymentsBot'; // بدون / في الآخر
const UPLOAD_DIR = __DIR__ . '/uploads/payments';
const UPLOAD_URL = BASE_URL . '/uploads/payments';

// اكتب Telegram user id للمحاسبين المسموح لهم
const ALLOWED_TELEGRAM_USERS = [915262359,1140433303];

// لو عايز سر للويبهوك زوده هنا وخليه نفس اللي هتبعت بيه في setWebhook
const TELEGRAM_SECRET_TOKEN = 'MySecret123';
/* =========================
   2) تشغيل أولي
========================= */
error_reporting(E_ALL);
ini_set('display_errors', '0');
date_default_timezone_set('Africa/Cairo');

if (!is_dir(UPLOAD_DIR)) {
    mkdir(UPLOAD_DIR, 0775, true);
}

$pdo = db();
createTables($pdo);

// للتجهيز السريع
if (isset($_GET['health'])) {
    jsonOut(array('ok' => true, 'time' => date('Y-m-d H:i:s')));
}

if (isset($_GET['setWebhook'])) {
    $url = BASE_URL . '/' . basename(__FILE__);
    $result = telegram('setWebhook', array(
        'url' => $url,
        'secret_token' => TELEGRAM_SECRET_TOKEN,
        'allowed_updates' => json_encode(array('message', 'callback_query'))
    ));
    jsonOut($result);
}

// حماية بسيطة للويبهوك
$headers = function_exists('getallheaders') ? getallheaders() : array();
$incomingSecret = isset($headers['X-Telegram-Bot-Api-Secret-Token']) ? $headers['X-Telegram-Bot-Api-Secret-Token'] : (isset($headers['x-telegram-bot-api-secret-token']) ? $headers['x-telegram-bot-api-secret-token'] : '');
if ($_SERVER['REQUEST_METHOD'] === 'POST' && TELEGRAM_SECRET_TOKEN && $incomingSecret !== TELEGRAM_SECRET_TOKEN) {
    http_response_code(403);
    echo 'forbidden';
    exit;
}

$raw = file_get_contents('php://input');
if (!$raw) {
    echo 'ok';
    exit;
}

$update = json_decode($raw, true);
if (!$update) {
    echo 'ok';
    exit;
}

/* تسجيل المستخدمين في ملف */

if (isset($update['message'])) {

    $chatId = $update['message']['chat']['id'];

    $file = __DIR__ . '/bot_users.txt';

    if (!file_exists($file)) {
        file_put_contents($file, '');
    }

    $users = file($file, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);

    if (!in_array($chatId, $users)) {
        file_put_contents($file, $chatId . PHP_EOL, FILE_APPEND);
    }

}

handleUpdate($pdo, $update);
echo 'ok';
exit;

/* =========================
   3) المعالجة الرئيسية
========================= */
function handleUpdate(PDO $pdo, array $update)
{
    if (isset($update['message'])) {
        handleMessage($pdo, $update['message']);
        return;
    }

    if (isset($update['callback_query'])) {
        handleCallback($pdo, $update['callback_query']);
        return;
    }
}

function handleMessage(PDO $pdo, array $message)
{
    $chatId = isset($message['chat']['id']) ? $message['chat']['id'] : null;
    $userId = isset($message['from']['id']) ? $message['from']['id'] : null;
    $text   = trim(isset($message['text']) ? $message['text'] : '');

    if (!$chatId || !$userId) {
        return;
    }

    if (!in_array($userId, ALLOWED_TELEGRAM_USERS, true)) {
        sendMessage($chatId, 'غير مسموح لك باستخدام البوت');
        return;
    }

    if ($text === '/start') {
        resetSession($pdo, $userId);
        sendMainMenu($chatId);
        return;
    }

    if ($text === '/menu') {
        resetSession($pdo, $userId);
        sendMainMenu($chatId);
        return;
    }

    if (strpos($text, '/search') === 0) {
        $query = trim(str_replace('/search', '', $text));
        handleQuickSearch($pdo, $chatId, $query);
        return;
    }

    $session = getSession($pdo, $userId);

    if ($text === 'إضافة سداد جديد') {
        startNewPaymentFlow($pdo, $chatId, $userId);
        return;
    }

    if ($text === 'استعلام عن تحويل') {
        createOrUpdateSession($pdo, $userId, $chatId, 'idle', $session ? sessionData($session) : array());
        askSearchType($chatId);
        return;
    }

    if (isset($message['photo']) && $session && $session['step'] === 'awaiting_screenshot') {
        receiveScreenshot($pdo, $message, $session);
        return;
    }

    if (!$session) {
        sendMessage($chatId, 'ابدأ بـ /start');
        return;
    }

    switch ($session['step']) {
        case 'awaiting_manual_amount':
            setSessionData($pdo, $userId, 'amount', normalizeAmount($text));
            updateSessionStep($pdo, $userId, 'awaiting_manual_date');
            sendMessage($chatId, 'اكتب التاريخ بالشكل ده مثلا 2026-03-15');
            break;

        case 'awaiting_manual_date':
            setSessionData($pdo, $userId, 'date', normalizeDate($text));
            updateSessionStep($pdo, $userId, 'awaiting_manual_time');
            sendMessage($chatId, 'اكتب الوقت بالشكل ده مثلا 14:32');
            break;

        case 'awaiting_manual_time':
            setSessionData($pdo, $userId, 'time', normalizeTime($text));
            updateSessionStep($pdo, $userId, 'awaiting_manual_reference');
            sendMessage($chatId, 'اكتب الرقم المرجعي');
            break;

        case 'awaiting_manual_reference':
            setSessionData($pdo, $userId, 'reference_number', trim($text));
            updateSessionStep($pdo, $userId, 'awaiting_payment_method');
            askPaymentMethod($chatId);
            break;

        case 'awaiting_invoice_number':
            $invoiceNumber = trim($text);
            setSessionData($pdo, $userId, 'invoice_number', $invoiceNumber);
            updateSessionStep($pdo, $userId, 'awaiting_route_number');
            sendMessage($chatId, 'اكتب رقم خط السير');
            break;

        case 'awaiting_route_number':
            $routeNumber = trim($text);
            setSessionData($pdo, $userId, 'route_number', $routeNumber);
            finalizePayment($pdo, $chatId, $userId);
            break;

        case 'awaiting_search_route':
            searchByField($pdo, $chatId, 'route_number', trim($text));
            updateSessionStep($pdo, $userId, 'idle');
            sendMainMenu($chatId);
            break;

        case 'awaiting_search_invoice':
            searchByField($pdo, $chatId, 'invoice_number', trim($text));
            updateSessionStep($pdo, $userId, 'idle');
            sendMainMenu($chatId);
            break;

        case 'awaiting_search_reference':
            searchByField($pdo, $chatId, 'reference_number', trim($text));
            updateSessionStep($pdo, $userId, 'idle');
            sendMainMenu($chatId);
            break;

        case 'awaiting_search_amount':
            searchByField($pdo, $chatId, 'amount', normalizeAmount($text));
            updateSessionStep($pdo, $userId, 'idle');
            sendMainMenu($chatId);
            break;

        default:
            sendMessage($chatId, 'مش فاهم الطلب استخدم /menu');
            break;
    }
}

function handleCallback(PDO $pdo, array $callback)
{
    $data   = isset($callback['data']) ? $callback['data'] : '';
    $chatId = isset($callback['message']['chat']['id']) ? $callback['message']['chat']['id'] : null;
    $userId = isset($callback['from']['id']) ? $callback['from']['id'] : null;
    $cbId   = isset($callback['id']) ? $callback['id'] : null;

    if (!$chatId || !$userId || !$cbId) {
        return;
    }

    if (!in_array($userId, ALLOWED_TELEGRAM_USERS, true)) {
        answerCallbackQuery($cbId, 'غير مسموح');
        return;
    }

    answerCallbackQuery($cbId, 'تم');

    $session = getSession($pdo, $userId);

    if ($data === 'new_payment') {
        startNewPaymentFlow($pdo, $chatId, $userId);
        return;
    }

    if ($data === 'search_payment') {
        createOrUpdateSession($pdo, $userId, $chatId, 'idle', $session ? sessionData($session) : array());
        askSearchType($chatId);
        return;
    }

    if ($data === 'search_route') {
        createOrUpdateSession($pdo, $userId, $chatId, 'awaiting_search_route', $session ? sessionData($session) : array());
        sendMessage($chatId, 'اكتب رقم خط السير');
        return;
    }

    if ($data === 'search_invoice') {
        createOrUpdateSession($pdo, $userId, $chatId, 'awaiting_search_invoice', $session ? sessionData($session) : array());
        sendMessage($chatId, 'اكتب رقم الفاتورة');
        return;
    }

    if ($data === 'search_reference') {
        createOrUpdateSession($pdo, $userId, $chatId, 'awaiting_search_reference', $session ? sessionData($session) : array());
        sendMessage($chatId, 'اكتب الرقم المرجعي');
        return;
    }

    if ($data === 'search_amount') {
        createOrUpdateSession($pdo, $userId, $chatId, 'awaiting_search_amount', $session ? sessionData($session) : array());
        sendMessage($chatId, 'اكتب المبلغ');
        return;
    }

    if ($data === 'confirm_extraction') {
        if (!$session) {
            sendMessage($chatId, 'ابدأ بـ /start');
            return;
        }

        updateSessionStep($pdo, $userId, 'awaiting_payment_method');
        askPaymentMethod($chatId);
        return;
    }

    if ($data === 'manual_entry') {
        if (!$session) {
            sendMessage($chatId, 'ابدأ بـ /start');
            return;
        }

        updateSessionStep($pdo, $userId, 'awaiting_manual_amount');
        sendMessage($chatId, 'اكتب المبلغ');
        return;
    }

    if ($data === 'cancel_flow') {
        resetSession($pdo, $userId);
        sendMessage($chatId, 'تم الإلغاء');
        sendMainMenu($chatId);
        return;
    }

    if (strpos($data, 'method:') === 0) {
        if (!$session) {
            sendMessage($chatId, 'ابدأ بـ /start');
            return;
        }

        $parts = explode(':', $data, 2);
        $method = isset($parts[1]) ? $parts[1] : '';

        if (!in_array($method, array('instapay', 'phone_cash', 'bank_transfer'), true)) {
            sendMessage($chatId, 'نوع دفع غير صحيح');
            return;
        }

        setSessionData($pdo, $userId, 'payment_method', $method);
        updateSessionStep($pdo, $userId, 'awaiting_invoice_number');
        sendMessage($chatId, 'اكتب رقم الفاتورة');
        return;
    }
}

/* =========================
   4) الفلو
========================= */
function startNewPaymentFlow(PDO $pdo, $chatId, $userId)
{
    resetSession($pdo, $userId);
    createSession($pdo, $userId, $chatId, 'awaiting_screenshot', array());
    sendMessage($chatId, 'ارفع السكرين شوت الخاصة بالتحويل');
}

function receiveScreenshot(PDO $pdo, array $message, array $session)
{
    $chatId = $message['chat']['id'];
    $userId = $message['from']['id'];
    $photos = $message['photo'];
    $last = end($photos);

    $fileId = isset($last['file_id']) ? $last['file_id'] : null;
    $fileUniqueId = isset($last['file_unique_id']) ? $last['file_unique_id'] : null;

    if (!$fileId || !$fileUniqueId) {
        sendMessage($chatId, 'تعذر قراءة الصورة');
        return;
    }

    $tgFile = telegram('getFile', array('file_id' => $fileId));
    $filePath = isset($tgFile['result']['file_path']) ? $tgFile['result']['file_path'] : null;
    if (!$filePath) {
        sendMessage($chatId, 'تعذر تحميل الصورة من تيليجرام');
        return;
    }

    $downloadUrl = 'https://api.telegram.org/file/bot' . TELEGRAM_BOT_TOKEN . '/' . $filePath;
    $imageBinary = @file_get_contents($downloadUrl);
    if ($imageBinary === false) {
        sendMessage($chatId, 'فشل تنزيل الصورة');
        return;
    }

    $sha256 = hash('sha256', $imageBinary);
    $ext = pathinfo($filePath, PATHINFO_EXTENSION) ? pathinfo($filePath, PATHINFO_EXTENSION) : 'jpg';
    $storedName = uniqid('pay_', true) . '.' . $ext;
    $storedPath = UPLOAD_DIR . '/' . $storedName;
    file_put_contents($storedPath, $imageBinary);

    setSessionData($pdo, $userId, 'telegram_file_id', $fileId);
    setSessionData($pdo, $userId, 'telegram_file_unique_id', $fileUniqueId);
    setSessionData($pdo, $userId, 'screenshot_file', $storedName);
    setSessionData($pdo, $userId, 'sha256', $sha256);

    $extract = extractPaymentDataWithOpenAI($storedPath);

    setSessionData($pdo, $userId, 'amount', isset($extract['amount']) ? $extract['amount'] : null);
    setSessionData($pdo, $userId, 'date', isset($extract['date']) ? $extract['date'] : null);
    setSessionData($pdo, $userId, 'time', isset($extract['time']) ? $extract['time'] : null);
    setSessionData($pdo, $userId, 'reference_number', isset($extract['reference_number']) ? $extract['reference_number'] : null);
    setSessionData($pdo, $userId, 'payment_method_ai', isset($extract['payment_method']) ? $extract['payment_method'] : null);
    setSessionData($pdo, $userId, 'ai_confidence', isset($extract['confidence']) ? $extract['confidence'] : null);
    setSessionData($pdo, $userId, 'ai_raw', json_encode($extract, JSON_UNESCAPED_UNICODE));

    updateSessionStep($pdo, $userId, 'awaiting_extraction_confirmation');

    $msg = "البيانات المستخرجة\n"
        . "المبلغ: " . safeText(isset($extract['amount']) ? $extract['amount'] : 'غير واضح') . "\n"
        . "التاريخ: " . safeText(isset($extract['date']) ? $extract['date'] : 'غير واضح') . "\n"
        . "الوقت: " . safeText(isset($extract['time']) ? $extract['time'] : 'غير واضح') . "\n"
        . "الرقم المرجعي: " . safeText(isset($extract['reference_number']) ? $extract['reference_number'] : 'غير واضح') . "\n"
        . "نوع الدفع المتوقع: " . safeText(arPaymentMethod(isset($extract['payment_method']) ? $extract['payment_method'] : null) ? arPaymentMethod(isset($extract['payment_method']) ? $extract['payment_method'] : null) : 'غير واضح') . "\n"
        . "الثقة: " . safeText((string)(isset($extract['confidence']) ? $extract['confidence'] : '0'));

    sendMessage($chatId, $msg, array(
        'inline_keyboard' => array(
            array(
                array('text' => 'تأكيد', 'callback_data' => 'confirm_extraction'),
                array('text' => 'تعديل يدوي', 'callback_data' => 'manual_entry'),
                array('text' => 'إلغاء', 'callback_data' => 'cancel_flow'),
            )
        )
    ));
}

function finalizePayment(PDO $pdo, $chatId, $userId)
{
    $session = getSession($pdo, $userId);
    if (!$session) {
        sendMessage($chatId, 'الجلسة انتهت');
        return;
    }

    $data = sessionData($session);

    $routeNumber = trim((string)(isset($data['route_number']) ? $data['route_number'] : ''));
    $invoiceNumber = trim((string)(isset($data['invoice_number']) ? $data['invoice_number'] : ''));
    $amount = normalizeAmount(isset($data['amount']) ? $data['amount'] : null);
    $date = normalizeDate(isset($data['date']) ? $data['date'] : null);
    $time = normalizeTime(isset($data['time']) ? $data['time'] : null);
    $reference = trim((string)(isset($data['reference_number']) ? $data['reference_number'] : ''));
    $paymentMethod = isset($data['payment_method']) ? $data['payment_method'] : null;
    $screenshot = isset($data['screenshot_file']) ? $data['screenshot_file'] : null;
    $sha256 = isset($data['sha256']) ? $data['sha256'] : null;
    $telegramFileUniqueId = isset($data['telegram_file_unique_id']) ? $data['telegram_file_unique_id'] : null;
    $aiRaw = isset($data['ai_raw']) ? $data['ai_raw'] : null;
    $entryMode = !empty($data['payment_method_ai']) ? 'ai_or_manual' : 'manual_only';

    if (!$routeNumber || !$invoiceNumber || !$amount || !$date || !$time || !$reference || !$paymentMethod || !$screenshot) {
        sendMessage($chatId, 'البيانات ناقصة');
        return;
    }

    $duplicate = detectDuplicate($pdo, array(
        'invoice_number' => $invoiceNumber,
        'reference_number' => $reference,
        'telegram_file_unique_id' => $telegramFileUniqueId,
        'sha256' => $sha256,
        'amount' => $amount,
        'payment_date' => $date,
        'payment_time' => $time,
    ));

    $status = $duplicate ? 'duplicate' : 'approved';
    $duplicateOfId = isset($duplicate['id']) ? $duplicate['id'] : null;
    $duplicateReason = isset($duplicate['reason']) ? $duplicate['reason'] : null;

    $stmt = $pdo->prepare(
        "INSERT INTO payments (
            route_number, invoice_number, amount, payment_date, payment_time, reference_number,
            payment_method, screenshot_file, telegram_file_unique_id, file_sha256,
            accountant_telegram_id, status, duplicate_of_id, duplicate_reason,
            ai_raw_json, entry_mode, created_at, updated_at
        ) VALUES (
            :route_number, :invoice_number, :amount, :payment_date, :payment_time, :reference_number,
            :payment_method, :screenshot_file, :telegram_file_unique_id, :file_sha256,
            :accountant_telegram_id, :status, :duplicate_of_id, :duplicate_reason,
            :ai_raw_json, :entry_mode, NOW(), NOW()
        )"
    );

    $stmt->execute(array(
        ':route_number' => $routeNumber,
        ':invoice_number' => $invoiceNumber,
        ':amount' => $amount,
        ':payment_date' => $date,
        ':payment_time' => $time,
        ':reference_number' => $reference,
        ':payment_method' => $paymentMethod,
        ':screenshot_file' => $screenshot,
        ':telegram_file_unique_id' => $telegramFileUniqueId,
        ':file_sha256' => $sha256,
        ':accountant_telegram_id' => $userId,
        ':status' => $status,
        ':duplicate_of_id' => $duplicateOfId,
        ':duplicate_reason' => $duplicateReason,
        ':ai_raw_json' => $aiRaw,
        ':entry_mode' => $entryMode,
    ));

    $paymentId = (int)$pdo->lastInsertId();
    logAudit($pdo, $paymentId, $userId, 'create', null, json_encode($data, JSON_UNESCAPED_UNICODE));

    $msg = "تم تسجيل العملية\n"
        . "رقم العملية: {$paymentId}\n"
        . "رقم خط السير: {$routeNumber}\n"
        . "رقم الفاتورة: {$invoiceNumber}\n"
        . "المبلغ: {$amount}\n"
        . "التاريخ: {$date}\n"
        . "الوقت: {$time}\n"
        . "المرجع: {$reference}\n"
        . "نوع الدفع: " . arPaymentMethod($paymentMethod) . "\n"
        . "الحالة: {$status}";

    if ($duplicate) {
        $msg .= "\nسبب التكرار: {$duplicateReason}\nمرتبطة بعملية سابقة رقم {$duplicateOfId}";
    }

    sendMessage($chatId, $msg);
    resetSession($pdo, $userId);
    sendMainMenu($chatId);
}

/* =========================
   5) البحث
========================= */
function askSearchType($chatId)
{
    sendMessage($chatId, 'اختر طريقة الاستعلام', array(
        'inline_keyboard' => array(
            array(
                array('text' => 'برقم خط السير', 'callback_data' => 'search_route'),
                array('text' => 'برقم الفاتورة', 'callback_data' => 'search_invoice'),
            ),
            array(
                array('text' => 'بالرقم المرجعي', 'callback_data' => 'search_reference'),
                array('text' => 'بالمبلغ', 'callback_data' => 'search_amount'),
            )
        )
    ));
}

function handleQuickSearch(PDO $pdo, $chatId, $query)
{
    if ($query === '') {
        sendMessage($chatId, 'مثال' . "\n" . '/search INV-1001' . "\n" . 'أو' . "\n" . '/search 847291');
        return;
    }

    $stmt = $pdo->prepare(
        "SELECT * FROM payments
         WHERE route_number = :q
            OR invoice_number = :q
            OR reference_number = :q
            OR amount = :amount
         ORDER BY id DESC
         LIMIT 10"
    );
    $stmt->execute(array(
        ':q' => $query,
        ':amount' => is_numeric(str_replace(',', '', $query)) ? normalizeAmount($query) : -1,
    ));
    $rows = $stmt->fetchAll();
    sendSearchResults($chatId, $rows);
}

function searchByField(PDO $pdo, $chatId, $field, $value)
{
    $allowed = array('route_number', 'invoice_number', 'reference_number', 'amount');

    if (!in_array($field, $allowed, true)) {
        sendMessage($chatId, 'نوع بحث غير مدعوم');
        return;
    }

    if ($value === null || $value === '') {
        sendMessage($chatId, 'القيمة غير صحيحة');
        return;
    }

    $sql = "SELECT * FROM payments WHERE {$field} = :value ORDER BY id DESC LIMIT 20";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array(':value' => $value));
    $rows = $stmt->fetchAll();
    sendSearchResults($chatId, $rows);
}

function sendSearchResults($chatId, array $rows)
{
    if (!$rows) {
        sendMessage($chatId, 'لا توجد نتائج');
        return;
    }

    foreach ($rows as $row) {
        $msg = "رقم العملية: {$row['id']}\n"
            . "رقم خط السير: {$row['route_number']}\n"
            . "رقم الفاتورة: {$row['invoice_number']}\n"
            . "المبلغ: {$row['amount']}\n"
            . "التاريخ: {$row['payment_date']}\n"
            . "الوقت: {$row['payment_time']}\n"
            . "المرجع: {$row['reference_number']}\n"
            . "نوع الدفع: " . arPaymentMethod($row['payment_method']) . "\n"
            . "الحالة: {$row['status']}\n"
            . "المحاسب: {$row['accountant_telegram_id']}\n"
            . "وقت التسجيل: {$row['created_at']}";

        sendMessage($chatId, $msg);

        $photoPath = UPLOAD_DIR . '/' . $row['screenshot_file'];
        if (is_file($photoPath)) {
            sendPhoto($chatId, $photoPath, "صورة العملية {$row['id']}");
        }
    }
}

/* =========================
   6) التكرار
========================= */
function detectDuplicate(PDO $pdo, array $data)
{
    // 1 الرقم المرجعي
    $stmt = $pdo->prepare("SELECT id FROM payments WHERE reference_number = :reference_number ORDER BY id DESC LIMIT 1");
    $stmt->execute(array(':reference_number' => $data['reference_number']));
    $row = $stmt->fetch();
    if ($row) {
        return array('id' => (int)$row['id'], 'reason' => 'نفس الرقم المرجعي موجود مسبقا');
    }

    // 2 file unique id من تيليجرام
    if (!empty($data['telegram_file_unique_id'])) {
        $stmt = $pdo->prepare("SELECT id FROM payments WHERE telegram_file_unique_id = :fid ORDER BY id DESC LIMIT 1");
        $stmt->execute(array(':fid' => $data['telegram_file_unique_id']));
        $row = $stmt->fetch();
        if ($row) {
            return array('id' => (int)$row['id'], 'reason' => 'نفس الصورة مرفوعة مسبقا من تيليجرام');
        }
    }

    // 3 sha256
    if (!empty($data['sha256'])) {
        $stmt = $pdo->prepare("SELECT id FROM payments WHERE file_sha256 = :sha ORDER BY id DESC LIMIT 1");
        $stmt->execute(array(':sha' => $data['sha256']));
        $row = $stmt->fetch();
        if ($row) {
            return array('id' => (int)$row['id'], 'reason' => 'نفس الملف موجود مسبقا');
        }
    }

    // 4 تطابق منطقي
    $stmt = $pdo->prepare(
        "SELECT id FROM payments
         WHERE amount = :amount
           AND payment_date = :payment_date
           AND payment_time = :payment_time
         ORDER BY id DESC LIMIT 1"
    );
    $stmt->execute(array(
        ':amount' => $data['amount'],
        ':payment_date' => $data['payment_date'],
        ':payment_time' => $data['payment_time'],
    ));
    $row = $stmt->fetch();
    if ($row) {
        return array('id' => (int)$row['id'], 'reason' => 'نفس المبلغ والتاريخ والوقت موجودين مسبقا');
    }

    return null;
}

/* =========================
   7) OpenAI Vision
========================= */
function extractPaymentDataWithOpenAI($imagePath)
{
    $mime = mime_content_type($imagePath);
    if (!$mime) {
        $mime = 'image/jpeg';
    }

    $base64 = base64_encode(file_get_contents($imagePath));

    $prompt = <<<TXT
افحص صورة إيصال أو سكرين شوت تحويل مالي
استخرج فقط القيم الظاهرة بوضوح بدون تخمين
أرجع JSON فقط بهذه المفاتيح
amount
date
time
reference_number
payment_method
confidence
notes

قواعد مهمة
- payment_method يكون واحد فقط من instapay أو phone_cash أو bank_transfer أو null
- التاريخ يكون بصيغة YYYY-MM-DD لو واضح
- الوقت يكون بصيغة HH:MM لو واضح
- amount رقم فقط بدون عملة إن أمكن
- لو أي قيمة غير واضحة رجعها null
- confidence من 0 إلى 100
TXT;

    $payload = array(
        'model' => OPENAI_MODEL,
        'input' => array(
            array(
                'role' => 'user',
                'content' => array(
                    array('type' => 'input_text', 'text' => $prompt),
                    array('type' => 'input_image', 'image_url' => "data:{$mime};base64,{$base64}")
                )
            )
        ),
        'max_output_tokens' => 400,
    );

    $response = httpJson('https://api.openai.com/v1/responses', $payload, array(
        'Authorization: Bearer ' . OPENAI_API_KEY,
        'Content-Type: application/json',
    ));

    $text = isset($response['output_text']) ? $response['output_text'] : null;

    if (!$text && !empty($response['output'][0]['content'][0]['text'])) {
        $text = $response['output'][0]['content'][0]['text'];
    }

    if (!$text) {
        return array(
            'amount' => null,
            'date' => null,
            'time' => null,
            'reference_number' => null,
            'payment_method' => null,
            'confidence' => 0,
            'notes' => 'No model output'
        );
    }

    $json = extractJson($text);
    $data = json_decode($json, true);

    if (!is_array($data)) {
        return array(
            'amount' => null,
            'date' => null,
            'time' => null,
            'reference_number' => null,
            'payment_method' => null,
            'confidence' => 0,
            'notes' => 'JSON parse failed',
            'raw' => $text,
        );
    }

    return array(
        'amount' => normalizeAmount(isset($data['amount']) ? $data['amount'] : null),
        'date' => normalizeDate(isset($data['date']) ? $data['date'] : null),
        'time' => normalizeTime(isset($data['time']) ? $data['time'] : null),
        'reference_number' => trim((string)(isset($data['reference_number']) ? $data['reference_number'] : '')) ?: null,
        'payment_method' => normalizePaymentMethod(isset($data['payment_method']) ? $data['payment_method'] : null),
        'confidence' => (int)(isset($data['confidence']) ? $data['confidence'] : 0),
        'notes' => isset($data['notes']) ? $data['notes'] : null,
    );
}

/* =========================
   8) Telegram helpers
========================= */
function sendMainMenu($chatId)
{
    sendMessage($chatId, 'اختر من القائمة', array(
        'keyboard' => array(
            array(
                array('text' => 'إضافة سداد جديد'),
                array('text' => 'استعلام عن تحويل')
            )
        ),
        'resize_keyboard' => true,
        'one_time_keyboard' => false,
    ));
}

function askPaymentMethod($chatId)
{
    sendMessage($chatId, 'اختر نوع الدفع', array(
        'inline_keyboard' => array(
            array(
                array('text' => 'انستا باي', 'callback_data' => 'method:instapay'),
                array('text' => 'فون كاش', 'callback_data' => 'method:phone_cash'),
            ),
            array(
                array('text' => 'تحويل بنكي', 'callback_data' => 'method:bank_transfer'),
            )
        )
    ));
}

function sendMessage($chatId, $text, $replyMarkup = null)
{
    $params = array(
        'chat_id' => $chatId,
        'text' => $text,
    );

    if ($replyMarkup) {
        $params['reply_markup'] = json_encode($replyMarkup, JSON_UNESCAPED_UNICODE);
    }

    telegram('sendMessage', $params);
}

function sendPhoto($chatId, $filePath, $caption = '')
{
    $url = 'https://api.telegram.org/bot' . TELEGRAM_BOT_TOKEN . '/sendPhoto';
    $ch = curl_init($url);
    curl_setopt_array($ch, array(
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_POST => true,
        CURLOPT_POSTFIELDS => array(
            'chat_id' => $chatId,
            'caption' => $caption,
            'photo' => new CURLFile($filePath),
        ),
    ));
    curl_exec($ch);
    curl_close($ch);
}

function answerCallbackQuery($callbackQueryId, $text = '')
{
    telegram('answerCallbackQuery', array(
        'callback_query_id' => $callbackQueryId,
        'text' => $text,
        'show_alert' => false,
    ));
}

function telegram($method, array $params = array())
{
    return httpForm('https://api.telegram.org/bot' . TELEGRAM_BOT_TOKEN . '/' . $method, $params);
}

/* =========================
   9) Session
========================= */
function createSession(PDO $pdo, $userId, $chatId, $step, array $data)
{
    $stmt = $pdo->prepare(
        "INSERT INTO bot_sessions (telegram_user_id, chat_id, step, data_json, updated_at)
         VALUES (:telegram_user_id, :chat_id, :step, :data_json, NOW())
         ON DUPLICATE KEY UPDATE
            chat_id = VALUES(chat_id),
            step = VALUES(step),
            data_json = VALUES(data_json),
            updated_at = NOW()"
    );

    $stmt->execute(array(
        ':telegram_user_id' => $userId,
        ':chat_id' => $chatId,
        ':step' => $step,
        ':data_json' => json_encode($data, JSON_UNESCAPED_UNICODE),
    ));
}

function createOrUpdateSession(PDO $pdo, $userId, $chatId, $step, array $data = array())
{
    createSession($pdo, $userId, $chatId, $step, $data);
}

function getSession(PDO $pdo, $userId)
{
    $stmt = $pdo->prepare("SELECT * FROM bot_sessions WHERE telegram_user_id = :uid LIMIT 1");
    $stmt->execute(array(':uid' => $userId));
    $row = $stmt->fetch();
    return $row ? $row : null;
}

function resetSession(PDO $pdo, $userId)
{
    $stmt = $pdo->prepare("DELETE FROM bot_sessions WHERE telegram_user_id = :uid");
    $stmt->execute(array(':uid' => $userId));
}

function updateSessionStep(PDO $pdo, $userId, $step)
{
    $stmt = $pdo->prepare("UPDATE bot_sessions SET step = :step, updated_at = NOW() WHERE telegram_user_id = :uid");
    $stmt->execute(array(':step' => $step, ':uid' => $userId));
}

function setSessionData(PDO $pdo, $userId, $key, $value)
{
    $session = getSession($pdo, $userId);
    if (!$session) {
        return;
    }

    $data = sessionData($session);
    $data[$key] = $value;

    $stmt = $pdo->prepare("UPDATE bot_sessions SET data_json = :data_json, updated_at = NOW() WHERE telegram_user_id = :uid");
    $stmt->execute(array(
        ':data_json' => json_encode($data, JSON_UNESCAPED_UNICODE),
        ':uid' => $userId,
    ));
}

function sessionData(array $session)
{
    $data = json_decode(isset($session['data_json']) ? $session['data_json'] : '{}', true);
    return is_array($data) ? $data : array();
}

/* =========================
   10) Database
========================= */
function db()
{
    static $pdo = null;
    if ($pdo instanceof PDO) {
        return $pdo;
    }

    $dsn = 'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME . ';charset=utf8mb4';
    $pdo = new PDO($dsn, DB_USER, DB_PASS, array(
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ));
    return $pdo;
}

function createTables(PDO $pdo)
{
    $pdo->exec("CREATE TABLE IF NOT EXISTS payments (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        route_number VARCHAR(100) NULL,
        invoice_number VARCHAR(100) NOT NULL,
        amount DECIMAL(15,2) NOT NULL,
        payment_date DATE NOT NULL,
        payment_time TIME NOT NULL,
        reference_number VARCHAR(191) NOT NULL,
        payment_method ENUM('instapay','phone_cash','bank_transfer') NOT NULL,
        screenshot_file VARCHAR(255) NOT NULL,
        telegram_file_unique_id VARCHAR(255) NULL,
        file_sha256 VARCHAR(64) NULL,
        accountant_telegram_id BIGINT NOT NULL,
        status ENUM('approved','duplicate','rejected','under_review') NOT NULL DEFAULT 'approved',
        duplicate_of_id BIGINT UNSIGNED NULL,
        duplicate_reason VARCHAR(255) NULL,
        ai_raw_json JSON NULL,
        entry_mode VARCHAR(50) NULL,
        created_at DATETIME NOT NULL,
        updated_at DATETIME NOT NULL,
        INDEX idx_route_number (route_number),
        INDEX idx_invoice_number (invoice_number),
        INDEX idx_reference_number (reference_number),
        INDEX idx_amount (amount),
        INDEX idx_payment_date (payment_date),
        INDEX idx_status (status),
        INDEX idx_file_sha256 (file_sha256),
        INDEX idx_tg_file_unique (telegram_file_unique_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS bot_sessions (
        telegram_user_id BIGINT PRIMARY KEY,
        chat_id BIGINT NOT NULL,
        step VARCHAR(100) NOT NULL,
        data_json JSON NULL,
        updated_at DATETIME NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS audit_logs (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        payment_id BIGINT UNSIGNED NOT NULL,
        telegram_user_id BIGINT NOT NULL,
        action_type VARCHAR(100) NOT NULL,
        old_value LONGTEXT NULL,
        new_value LONGTEXT NULL,
        created_at DATETIME NOT NULL,
        INDEX idx_payment_id (payment_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
}

function logAudit(PDO $pdo, $paymentId, $userId, $action, $oldValue, $newValue)
{
    $stmt = $pdo->prepare(
        "INSERT INTO audit_logs (payment_id, telegram_user_id, action_type, old_value, new_value, created_at)
         VALUES (:payment_id, :telegram_user_id, :action_type, :old_value, :new_value, NOW())"
    );
    $stmt->execute(array(
        ':payment_id' => $paymentId,
        ':telegram_user_id' => $userId,
        ':action_type' => $action,
        ':old_value' => $oldValue,
        ':new_value' => $newValue,
    ));
}

/* =========================
   11) Utilities
========================= */
function httpJson($url, array $payload, array $headers = array())
{
    $ch = curl_init($url);
    curl_setopt_array($ch, array(
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_POST => true,
        CURLOPT_HTTPHEADER => $headers,
        CURLOPT_POSTFIELDS => json_encode($payload, JSON_UNESCAPED_UNICODE),
        CURLOPT_TIMEOUT => 90,
    ));

    $response = curl_exec($ch);
    $error = curl_error($ch);
    curl_close($ch);

    if ($response === false) {
        return array('error' => $error ? $error : 'HTTP request failed');
    }

    $data = json_decode($response, true);
    return is_array($data) ? $data : array('raw' => $response);
}

function httpForm($url, array $payload)
{
    $ch = curl_init($url);
    curl_setopt_array($ch, array(
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_POST => true,
        CURLOPT_POSTFIELDS => $payload,
        CURLOPT_TIMEOUT => 60,
    ));
    $response = curl_exec($ch);
    $error = curl_error($ch);
    curl_close($ch);

    if ($response === false) {
        return array('ok' => false, 'error' => $error ? $error : 'HTTP request failed');
    }

    $data = json_decode($response, true);
    return is_array($data) ? $data : array('ok' => false, 'raw' => $response);
}

function extractJson($text)
{
    $text = trim($text);
    if (strpos($text, '```') === 0) {
        $text = preg_replace('/^```[a-zA-Z]*\s*/', '', $text);
        $text = preg_replace('/```$/', '', $text);
        $text = trim($text);
    }

    $start = strpos($text, '{');
    $end = strrpos($text, '}');
    if ($start !== false && $end !== false && $end > $start) {
        return substr($text, $start, $end - $start + 1);
    }
    return $text;
}

function normalizeAmount($value)
{
    if ($value === null || $value === '') return null;
    $value = str_replace(array(',', 'جنيه', 'EGP', 'egp', ' '), '', (string)$value);
    return is_numeric($value) ? round((float)$value, 2) : null;
}

function normalizeDate($date)
{
    if (!$date) return null;
    $date = trim($date);

    if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
        return $date;
    }

    $date = str_replace(array('/', '.'), '-', $date);
    if (preg_match('/^(\d{2})-(\d{2})-(\d{4})$/', $date, $m)) {
        return $m[3] . '-' . $m[2] . '-' . $m[1];
    }

    $ts = strtotime($date);
    return $ts ? date('Y-m-d', $ts) : null;
}

function normalizeTime($time)
{
    if (!$time) return null;
    $time = trim($time);
    if (preg_match('/^(\d{1,2}):(\d{2})(?::\d{2})?$/', $time, $m)) {
        return str_pad($m[1], 2, '0', STR_PAD_LEFT) . ':' . $m[2] . ':00';
    }
    return null;
}

function normalizePaymentMethod($method)
{
    if (!$method) return null;
    $m = mb_strtolower(trim($method));

    if (in_array($m, array('instapay', 'انستا باي', 'انستاباي', 'insta pay'), true)) {
        return 'instapay';
    }
    if (in_array($m, array('phone_cash', 'فون كاش', 'phone cash'), true)) {
        return 'phone_cash';
    }
    if (in_array($m, array('bank_transfer', 'تحويل بنكي', 'bank transfer'), true)) {
        return 'bank_transfer';
    }

    return null;
}

function arPaymentMethod($method)
{
    if ($method === 'instapay') return 'انستا باي';
    if ($method === 'phone_cash') return 'فون كاش';
    if ($method === 'bank_transfer') return 'تحويل بنكي';
    return null;
}

function safeText($value)
{
    return trim((string)$value) !== '' ? (string)$value : 'غير واضح';
}

function jsonOut(array $data)
{
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode($data, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
    exit;
}