متابعة إيميلات Brokers3

# ─── brokers3_email_tracker.py ──────────────────────────────────────────────
# المسار: frappe-bench/apps/yourapp/yourapp/api.py
# الاستدعاء من الـ JS: frappe.call({ method: 'yourapp.api.get_brokers3_email_stats' })

import frappe

@frappe.whitelist()
def get_brokers3_email_stats(filters=None):
    """
    يجلب إيميلات Brokers3 من Communication doctype
    ويكشف الإيميلات المفتوحة (opened_by_recipient = 1)
    """
    emails = frappe.db.sql("""
        SELECT
            c.name,
            c.reference_name              AS broker_name,
            c.recipients                  AS recipient_email,
            c.subject,
            c.status,
            c.sent_or_received,
            c.communication_date          AS sent_time,
            c.opened_by_recipient,
            c.read_by_recipient_on        AS opened_at,
            b.email                       AS broker_email,
            b.link                        AS broker_link,
            (
                SELECT COUNT(*) 
                FROM `tabEmail Unsubscribe` eu
                WHERE eu.reference_doctype = 'Brokers3'
                  AND eu.reference_name    = c.reference_name
            ) AS unsubscribed
        FROM  `tabCommunication`  c
        LEFT JOIN `tabBrokers3`   b ON b.name = c.reference_name
        WHERE
            c.reference_doctype      = 'Brokers3'
            AND c.sent_or_received   = 'Sent'
            AND c.communication_type = 'Communication'
        ORDER BY c.communication_date DESC
        LIMIT 500
    """, as_dict=True)

    # ─── تحديد الحالة الفعلية لكل إيميل ─────────────────────────────────────
    for e in emails:
        if e.get("status") == "Replied":
            e["display_status"] = "replied"
        elif e.get("status") in ("Bounced", "Rejected"):
            e["display_status"] = "bounced"
        elif e.get("opened_by_recipient"):
            e["display_status"] = "opened"
        else:
            e["display_status"] = "sent"

    # ─── إحصائيات مجمّعة ─────────────────────────────────────────────────────
    total   = len(emails)
    opened  = sum(1 for e in emails if e.get("opened_by_recipient"))
    replied = sum(1 for e in emails if e.get("display_status") == "replied")
    bounced = sum(1 for e in emails if e.get("display_status") == "bounced")
    rate    = round(opened / total * 100, 1) if total else 0
    brokers = len(set(e["broker_name"] for e in emails if e.get("broker_name")))

    return {
        "emails": emails,
        "stats": {
            "total"  : total,
            "opened" : opened,
            "sent"   : total - opened - bounced,
            "replied": replied,
            "bounced": bounced,
            "rate"   : rate,
            "brokers": brokers,
        }
    }