<?php
/**
 * SMS Debug Helper - Check settings and troubleshoot SMS issues
 */

require_once '../config/database.php';

echo "<h2>SMS Debug Report</h2>";
echo "<hr>";

// 1. Check all SMS related settings
echo "<h3>1. SMS Settings Check:</h3>";

try {
    $stmt = $pdo->prepare("SELECT setting_key, setting_value FROM settings WHERE setting_key LIKE '%sms%' OR setting_key LIKE '%musking%' OR setting_key LIKE '%notification%'");
    $stmt->execute();
    $allSettings = $stmt->fetchAll();
    
    echo "<table border='1' cellpadding='5'>";
    echo "<tr><th>Setting Key</th><th>Setting Value</th><th>Status</th></tr>";
    
    foreach ($allSettings as $setting) {
        $status = '';
        if ($setting['setting_key'] === 'non_musking_enabled' || $setting['setting_key'] === 'sms_send_bank_transaction_notifications') {
            $status = ($setting['setting_value'] == '1') ? "ENABLED" : "DISABLED";
        } elseif (strpos($setting['setting_key'], 'api_key') !== false) {
            $status = !empty($setting['setting_value']) ? "SET (" . strlen($setting['setting_value']) . " chars)" : "EMPTY";
        } elseif (strpos($setting['setting_key'], 'api_url') !== false) {
            $status = !empty($setting['setting_value']) ? "SET" : "EMPTY";
        }
        
        $value = htmlspecialchars($setting['setting_value'] ?? '');
        echo "<tr><td>" . $setting['setting_key'] . "</td><td>" . $value . "</td><td>" . $status . "</td></tr>";
    }
    echo "</table>";
} catch (Exception $e) {
    echo "<p style='color:red;'>Error: " . $e->getMessage() . "</p>";
}

echo "<hr>";

// 2. Check students with phone numbers
echo "<h3>2. Students with Phone Numbers:</h3>";
try {
    $stmt = $pdo->prepare("SELECT id, first_name, last_name, student_id, phone FROM students WHERE phone IS NOT NULL AND phone != '' LIMIT 10");
    $stmt->execute();
    $students = $stmt->fetchAll();
    
    if (count($students) > 0) {
        echo "<table border='1' cellpadding='5'>";
        echo "<tr><th>ID</th><th>Name</th><th>Student ID</th><th>Phone</th></tr>";
        foreach ($students as $student) {
            echo "<tr>";
            echo "<td>" . $student['id'] . "</td>";
            echo "<td>" . $student['first_name'] . " " . $student['last_name'] . "</td>";
            echo "<td>" . $student['student_id'] . "</td>";
            echo "<td>" . $student['phone'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
        
        // Count students without phone
        $stmt = $pdo->prepare("SELECT COUNT(*) as count FROM students WHERE phone IS NULL OR phone = ''");
        $stmt->execute();
        $noPhoneCount = $stmt->fetch()['count'];
        echo "<p style='color:red;'><strong>Warning:</strong> " . $noPhoneCount . " students have no phone number</p>";
    } else {
        echo "<p style='color:red;'>No students found with phone numbers!</p>";
    }
} catch (Exception $e) {
    echo "<p style='color:red;'>Error: " . $e->getMessage() . "</p>";
}

echo "<hr>";

// 3. Check recent bank transactions
echo "<h3>3. Recent Bank Transactions:</h3>";
try {
    $stmt = $pdo->prepare("
        SELECT bt.*, s.first_name, s.last_name, s.phone 
        FROM bank_transactions bt 
        JOIN students s ON bt.student_id = s.id 
        ORDER BY bt.id DESC 
        LIMIT 5
    ");
    $stmt->execute();
    $transactions = $stmt->fetchAll();
    
    echo "<table border='1' cellpadding='5'>";
    echo "<tr><th>ID</th><th>Student</th><th>Amount</th><th>Status</th><th>Student Phone</th><th>Phone Empty?</th></tr>";
    foreach ($transactions as $t) {
        $phoneEmpty = empty($t['phone']) ? "NO PHONE" : "HAS PHONE";
        $phoneVal = isset($t['phone']) ? $t['phone'] : 'NULL';
        echo "<tr>";
        echo "<td>" . $t['id'] . "</td>";
        echo "<td>" . $t['first_name'] . " " . $t['last_name'] . "</td>";
        echo "<td>" . $t['amount'] . "</td>";
        echo "<td>" . $t['status'] . "</td>";
        echo "<td>" . $phoneVal . "</td>";
        echo "<td>" . $phoneEmpty . "</td>";
        echo "</tr>";
    }
    echo "</table>";
} catch (Exception $e) {
    echo "<p style='color:red;'>Error: " . $e->getMessage() . "</p>";
}

echo "<hr>";

// 4. Check SMS log
echo "<h3>4. Recent SMS Log Entries:</h3>";
try {
    $stmt = $pdo->prepare("SELECT * FROM messages_log ORDER BY created_at DESC LIMIT 10");
    $stmt->execute();
    $logs = $stmt->fetchAll();
    
    if (count($logs) > 0) {
        echo "<table border='1' cellpadding='5'>";
        echo "<tr><th>Time</th><th>Recipient</th><th>Status</th><th>Message Preview</th></tr>";
        foreach ($logs as $log) {
            $messagePreview = substr($log['message_content'], 0, 50) . '...';
            $statusColor = ($log['status'] === 'sent') ? 'green' : (($log['status'] === 'failed') ? 'red' : 'orange');
            echo "<tr>";
            echo "<td>" . $log['created_at'] . "</td>";
            echo "<td>" . $log['recipient'] . "</td>";
            echo "<td style='color:" . $statusColor . "'>" . $log['status'] . "</td>";
            echo "<td>" . $messagePreview . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "<p style='color:orange;'>No SMS log entries found. SMS was never attempted.</p>";
    }
} catch (Exception $e) {
    echo "<p style='color:red;'>Error accessing messages_log: " . $e->getMessage() . "</p>";
}

echo "<hr>";

// 5. Test SMS conditions
echo "<h3>5. SMS Send Condition Test:</h3>";
try {
    $smsEnabledStmt = $pdo->prepare("SELECT setting_value FROM settings WHERE setting_key = 'non_musking_enabled'");
    $smsEnabledStmt->execute();
    $smsEnabledResult = $smsEnabledStmt->fetch();
    $smsEnabled = ($smsEnabledResult && $smsEnabledResult['setting_value'] == '1');
    
    $bankSmsEnabledStmt = $pdo->prepare("SELECT setting_value FROM settings WHERE setting_key = 'sms_send_bank_transaction_notifications'");
    $bankSmsEnabledStmt->execute();
    $bankSmsEnabledResult = $bankSmsEnabledStmt->fetch();
    $bankSmsNotificationsEnabled = ($bankSmsEnabledResult && $bankSmsEnabledResult['setting_value'] === '1');
    
    $canSendSms = $smsEnabled && $bankSmsNotificationsEnabled;
    
    echo "<ul>";
    echo "<li>General SMS Enabled: " . ($smsEnabled ? "YES" : "NO") . "</li>";
    echo "<li>Bank Transaction SMS Enabled: " . ($bankSmsNotificationsEnabled ? "YES" : "NO") . "</li>";
    echo "<li><strong>Can Send SMS: " . ($canSendSms ? "YES" : "NO - SMS WILL NOT BE SENT!") . "</strong></li>";
    echo "</ul>";
    
    if (!$canSendSms) {
        echo "<p style='color:red;'><strong>Problem identified:</strong> SMS cannot be sent because one or both conditions are not met.</p>";
        echo "<p>To fix, run this SQL:</p>";
        echo "<pre>";
        echo "INSERT INTO settings (setting_key, setting_value) VALUES ('non_musking_enabled', '1') ON DUPLICATE KEY UPDATE setting_value = '1';\n";
        echo "INSERT INTO settings (setting_key, setting_value) VALUES ('sms_send_bank_transaction_notifications', '1') ON DUPLICATE KEY UPDATE setting_value = '1';";
        echo "</pre>";
    }
} catch (Exception $e) {
    echo "<p style='color:red;'>Error: " . $e->getMessage() . "</p>";
}

echo "<hr>";
echo "<p><a href='verify_bank_transactions.php' style='padding:10px; background:#007bff; color:white; text-decoration:none; border-radius:5px;'>Back to Verify Bank Transactions</a></p>";
?>
