Hamza-AyedandClaude Opus 5
39b5a7fc7f
Storing the verification phone as a keyed HMAC made OTP lookups independent of the encryption mode, but the hash is one-way — and customer service reads those same rows to chase people who requested a code and never finished registering. That workflow would have lost the number entirely. The verification tables now carry both forms: phone_number holds the lookup key, and a new phone_enc column holds the encrypted number, which is decryptable when a human needs to call. The two follow-up queries also compared the verification row against the driver/passengers tables and the notes tables by matching ciphertext, which only ever worked because encryption was deterministic. Under GCM every number would have looked unregistered and every note would have disappeared. Both now read the number from phone_enc and match on normalised plaintext, so they are correct under either mode. Rows written before phone_enc existed are skipped rather than shown without a number. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
73 lines
2.8 KiB
PHP
73 lines
2.8 KiB
PHP
<?php
|
|
require_once __DIR__ . '/../connect.php';
|
|
|
|
/**
|
|
* الركاب الذين طلبوا رمز تحقق ولم يُكملوا التسجيل — لمتابعتهم.
|
|
*
|
|
* سابقاً كان الاستعلام يقارن phone_number (مشفّراً) بعمود phone في جدول
|
|
* الركاب، ويجلب الملاحظات بربط على نفس القيمة. هذا يعمل فقط ما دام التشفير
|
|
* حتمياً؛ ومع AES-GCM تختلف القيمتان لنفس الرقم فيُصبح كل رقم "غير مسجَّل"
|
|
* وتختفي الملاحظات.
|
|
*
|
|
* الآن: يُقرأ الرقم من phone_enc (نسخة قابلة للاسترجاع)، ثم تتم المطابقة
|
|
* والاستبعاد في PHP على الأرقام الأصلية — صحيح تحت أي نمط تشفير.
|
|
*/
|
|
|
|
$sql = "SELECT id, phone_number, phone_enc, created_at
|
|
FROM phone_verification_passenger
|
|
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 4 DAY)
|
|
ORDER BY created_at DESC
|
|
LIMIT 200";
|
|
|
|
$stmt = $con->prepare($sql);
|
|
$stmt->execute();
|
|
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
// أرقام الركاب المسجَّلين فعلاً، بصيغتها الأصلية
|
|
$registered = [];
|
|
foreach ($con->query("SELECT phone FROM passengers WHERE phone IS NOT NULL")->fetchAll(PDO::FETCH_COLUMN) as $enc) {
|
|
$plain = $encryptionHelper->decryptData($enc);
|
|
if ($plain) $registered[normalizePhone($plain)] = true;
|
|
}
|
|
|
|
// الملاحظات المسجَّلة سابقاً عن كل رقم
|
|
$notes = [];
|
|
try {
|
|
$noteRows = $con->query("SELECT phone, note, editor, createdAt FROM notesForPassengerService")->fetchAll(PDO::FETCH_ASSOC);
|
|
foreach ($noteRows as $n) {
|
|
$plain = $encryptionHelper->decryptData($n['phone']) ?: $n['phone'];
|
|
if ($plain) $notes[normalizePhone($plain)] = $n;
|
|
}
|
|
} catch (PDOException $e) {
|
|
error_log('[getPassengersNotCompleteRegistration] notes unavailable: ' . $e->getMessage());
|
|
}
|
|
|
|
$result = [];
|
|
foreach ($rows as $row) {
|
|
$phone = $encryptionHelper->decryptData($row['phone_enc'] ?? null);
|
|
|
|
// السجلات التي سبقت إضافة phone_enc لا يمكن استرجاع رقمها من المفتاح
|
|
if (!$phone) continue;
|
|
|
|
$key = normalizePhone($phone);
|
|
if (isset($registered[$key])) continue; // أكمل تسجيله فعلاً
|
|
|
|
$note = $notes[$key] ?? null;
|
|
$result[] = [
|
|
'id' => $row['id'],
|
|
'phone_number' => $phone,
|
|
'created_at' => $row['created_at'],
|
|
'note' => $note['note'] ?? null,
|
|
'editor' => $note['editor'] ?? null,
|
|
'note_created_at' => $note['createdAt'] ?? null,
|
|
];
|
|
|
|
if (count($result) >= 25) break;
|
|
}
|
|
|
|
if ($result) {
|
|
jsonSuccess($result);
|
|
} else {
|
|
jsonError("No records found");
|
|
}
|