<?php
/**
 * تذكير المرضى بمواعيدهم عبر واتساب قبل ساعتين
 * يشغّل تلقائياً عبر Cron كل 15 دقيقة
 *
 * Linux: 0,15,30,45 * * * * php /path/to/scripts/appointment_reminder_cron.php
 * Windows: schtasks لجدولة التشغيل كل 15 دقيقة
 */

$baseDir = dirname(__DIR__);
require_once $baseDir . '/src/Core/Database.php';

$config = require $baseDir . '/config/config.php';
date_default_timezone_set($config['timezone'] ?? 'Asia/Baghdad');

$db = \App\Core\Database::getInstance($config['db'])->connection();

// تشغيل الهجرات إذا لزم
try {
    $db->query('SELECT id FROM patient_appointment_reminders LIMIT 1');
} catch (Throwable $e) {
    $f = $baseDir . '/migrations/033_patient_appointment_reminders.sql';
    if (file_exists($f)) {
        foreach (array_filter(array_map('trim', explode(';', file_get_contents($f)))) as $s) {
            if (!empty($s) && strpos($s, '--') !== 0) {
                try { $db->exec($s); } catch (Throwable $x) {}
            }
        }
    }
}
try {
    $db->query('SELECT id FROM whatsapp_send_queue LIMIT 1');
} catch (Throwable $e) {
    $f = $baseDir . '/migrations/034_doctor_whatsapp_queue.sql';
    if (file_exists($f)) {
        foreach (array_filter(array_map('trim', explode(';', file_get_contents($f)))) as $s) {
            if (!empty($s) && strpos($s, '--') !== 0) {
                try { $db->exec($s); } catch (Throwable $x) {}
            }
        }
    }
}
try {
    $db->query('SELECT reminder_entity_type FROM whatsapp_send_queue LIMIT 1');
} catch (Throwable $e) {
    $f = $baseDir . '/migrations/035_whatsapp_queue_reminder_fields.sql';
    if (file_exists($f)) {
        foreach (array_filter(array_map('trim', explode(';', file_get_contents($f)))) as $s) {
            if (!empty($s) && strpos($s, '--') !== 0) {
                try { $db->exec($s); } catch (Throwable $x) {}
            }
        }
    }
}

$queued = 0;

// 1) المواعيد من doctor_appointments
try {
    $db->query('SELECT id FROM doctor_appointments LIMIT 1');
    $db->query('SELECT patient_whatsapp_remind FROM doctor_accounts LIMIT 1');
} catch (Throwable $e) {
    // الجداول غير موجودة
}

$appointments = [];
try {
    $stmt = $db->prepare("
        SELECT a.id, a.doctor_account_id, a.patient_id, a.appointment_date, a.appointment_time, 
               p.full_name AS patient_name, p.phone, da.tenant_id
        FROM doctor_appointments a
        JOIN patients p ON p.id = a.patient_id
        JOIN doctor_accounts da ON da.id = a.doctor_account_id AND da.is_active = 1 AND da.patient_whatsapp_remind = 1
        WHERE a.appointment_date = CURDATE()
        AND a.appointment_time BETWEEN ADDTIME(CURTIME(), '01:50:00') AND ADDTIME(CURTIME(), '02:10:00')
        AND a.status IN ('scheduled', 'confirmed')
        AND TRIM(COALESCE(p.phone, '')) != ''
        AND NOT EXISTS (
            SELECT 1 FROM patient_appointment_reminders r 
            WHERE r.entity_type = 'doctor_appointment' AND r.entity_id = a.id
        )
    ");
    $stmt->execute();
    foreach ($stmt->fetchAll() as $row) {
        $appointments[] = ['source' => 'doctor_appointment', 'entity_id' => $row['id'], 'row' => $row];
    }
} catch (Throwable $e) {
    // قد لا يكون patient_whatsapp_remind موجوداً بعد
    try {
        $db->exec('ALTER TABLE doctor_accounts ADD COLUMN patient_whatsapp_remind TINYINT(1) NOT NULL DEFAULT 1');
    } catch (Throwable $x) {}
}

// 2) المواعيد من patient_doctor_bookings
try {
    $stmt = $db->prepare("
        SELECT b.id, b.doctor_account_id, b.patient_id, b.appointment_date, b.appointment_time,
               p.full_name AS patient_name, p.phone, b.tenant_id
        FROM patient_doctor_bookings b
        JOIN patients p ON p.id = b.patient_id
        JOIN doctor_accounts da ON da.id = b.doctor_account_id AND da.is_active = 1 AND da.patient_whatsapp_remind = 1
        WHERE b.appointment_date = CURDATE()
        AND b.appointment_time BETWEEN ADDTIME(CURTIME(), '01:50:00') AND ADDTIME(CURTIME(), '02:10:00')
        AND b.status IN ('scheduled', 'confirmed', 'pending')
        AND TRIM(COALESCE(p.phone, '')) != ''
        AND NOT EXISTS (
            SELECT 1 FROM patient_appointment_reminders r 
            WHERE r.entity_type = 'patient_doctor_booking' AND r.entity_id = b.id
        )
    ");
    $stmt->execute();
    foreach ($stmt->fetchAll() as $row) {
        $appointments[] = ['source' => 'patient_doctor_booking', 'entity_id' => $row['id'], 'row' => $row];
    }
} catch (Throwable $e) {
    error_log('appointment_reminder_cron patient_doctor_bookings: ' . $e->getMessage());
}

foreach ($appointments as $apt) {
    $row = $apt['row'];
    $phone = trim((string)($row['phone'] ?? ''));
    if ($phone === '') continue;

    $p = preg_replace('/\D/', '', $phone);
    if (strlen($p) < 8) continue;
    if (strlen($p) === 10 && $p[0] === '0') $p = '964' . substr($p, 1);
    elseif (strlen($p) === 9 && $p[0] === '7') $p = '964' . $p;
    $phoneNorm = '+' . $p;

    $timeStr = date('H:i', strtotime($row['appointment_time']));
    $msg = "📅 *تذكير بموعدك*\n\n";
    $msg .= "مرحباً {$row['patient_name']}،\n\n";
    $msg .= "هذا تذكير بموعدك القادم:\n";
    $msg .= "📆 التاريخ: {$row['appointment_date']}\n";
    $msg .= "🕐 الوقت: {$timeStr}\n\n";
    $msg .= "نرجو الحضور في الموعد المحدد. شكراً لثقتك بنا.";

    try {
        $ins = $db->prepare('INSERT INTO whatsapp_send_queue (tenant_id, doctor_account_id, phone, message, use_doctor_config, reminder_entity_type, reminder_entity_id, reminder_doctor_account_id, reminder_patient_id, reminder_appointment_date, reminder_appointment_time) VALUES (?, ?, ?, ?, 0, ?, ?, ?, ?, ?, ?)');
        $ins->execute([
            (int)($row['tenant_id'] ?? 1),
            $row['doctor_account_id'],
            $phoneNorm,
            $msg,
            $apt['source'],
            $apt['entity_id'],
            $row['doctor_account_id'],
            $row['patient_id'],
            $row['appointment_date'],
            $row['appointment_time']
        ]);
        $queued++;
        echo "✅ تمت إضافة تذكير لـ {$row['patient_name']} ({$row['appointment_date']} {$timeStr}) - يرسل تلقائياً\n";
    } catch (Throwable $e) {
        error_log('appointment_reminder_cron queue: ' . $e->getMessage());
    }
}

echo "تمت إضافة $queued تذكير للطابور. سيُرسل تلقائياً عبر whatsapp_queue_cron.\n";
