<?php
require_once '../includes/db.php';
header('Content-Type: application/json');
if (!isset($_SESSION['admin_logged_in'])) { exit; }

$notifications = [];
$ids_to_update = [
    'users' => [], 
    'transactions' => [], 
    'withdrawals' => [], 
    'partner_documents' => [], 
    'contact_messages' => [],
    'messages' => []
];

// Master query for all actionable notifications where is_notified = 0
// Each SELECT statement now has exactly 4 columns in the same order.
$sql = "
    (SELECT id, 'New Partner' as type, full_name as detail, created_at FROM users WHERE role LIKE '%_partner' AND status = 'pending' AND is_notified = 0)
    UNION ALL
    (SELECT id, 'Top-Up Request' as type, (SELECT full_name FROM users u WHERE u.id = t.user_id) as detail, created_at FROM transactions t WHERE transaction_type = 'Top-up' AND status = 'Pending' AND is_notified = 0)
    UNION ALL
    (SELECT id, 'Withdrawal Request' as type, (SELECT full_name FROM users u WHERE u.id = w.partner_id) as detail, created_at FROM withdrawals w WHERE status = 'Pending' AND is_notified = 0)
    UNION ALL
    (SELECT id, 'New Document' as type, (SELECT full_name FROM users u WHERE u.id = pd.partner_id) as detail, created_at FROM partner_documents pd WHERE status = 'Submitted' AND is_notified = 0)
    UNION ALL
    (SELECT id, 'New Message' as type, name as detail, created_at FROM contact_messages WHERE is_read = 0 AND is_notified = 0)
    UNION ALL
    (SELECT id, 'Profile Page Request' as type, full_name as detail, created_at FROM users WHERE profile_page_status = 'Requested' AND is_notified = 0)
    UNION ALL
    (SELECT id, 'New Chat Message' as type, (SELECT full_name FROM users u WHERE u.id = m.sender_id) as detail, created_at FROM messages m WHERE recipient_id = 1 AND is_read = 0 AND is_notified = 0)
    ORDER BY created_at ASC
";

$result = $conn->query($sql);
if ($result) {
    while ($row = $result->fetch_assoc()) {
        $notifications[] = $row;
        // Store IDs to be updated, grouped by table
        switch ($row['type']) {
            case 'New Partner':
            case 'Profile Page Request':
                $ids_to_update['users'][] = $row['id'];
                break;
            case 'Top-Up Request':
                $ids_to_update['transactions'][] = $row['id'];
                break;
            case 'Withdrawal Request':
                $ids_to_update['withdrawals'][] = $row['id'];
                break;
            case 'New Document':
                $ids_to_update['partner_documents'][] = $row['id'];
                break;
            case 'New Message':
                $ids_to_update['contact_messages'][] = $row['id'];
                break;
            case 'New Chat Message':
                $ids_to_update['messages'][] = $row['id'];
                break;
        }
    }
}

// If we found new notifications, immediately flag them
if (!empty($notifications)) {
    foreach ($ids_to_update as $table => $ids) {
        if (!empty($ids)) {
            $id_list = implode(',', array_map('intval', $ids));
            if ($table == 'users') { // Special condition for users table to update the correct field
                $conn->query("UPDATE `users` SET is_notified = 1 WHERE id IN ($id_list) AND (status='pending' OR profile_page_status='Requested')");
            } else {
                $conn->query("UPDATE `$table` SET is_notified = 1 WHERE id IN ($id_list)");
            }
        }
    }
}

echo json_encode(['success' => true, 'notifications' => $notifications]);
?>
