app/services/certificate_service.py

"""Certificate service: issuance, verification, PDF rendering."""

import base64
import io
import os
import random
import re
import tempfile
from datetime import datetime
from typing import Optional

import httpx
from fastapi import HTTPException
from fastapi.responses import Response

from app.core.constants import CERT_REQUIRED_COMPLETIONS, CERT_VERIFY_BASE_URL
from app.core.config import SUPABASE_URL, SUPABASE_ANON_KEY
from app.services.supabase_service import supabase_headers
from app.services.completion_service import count_completions

# --------------------------------------------------------------------------- #
#  Arabic text support (safe imports — module works without them)            #
# --------------------------------------------------------------------------- #

_HAS_ARABIC = False
try:
    import arabic_reshaper
    from bidi.algorithm import get_display
    _HAS_ARABIC = True
except ImportError:
    pass


# --------------------------------------------------------------------------- #
#  Arabic font helper — IBM Plex Sans Arabic (CDN download, cached)           #
# --------------------------------------------------------------------------- #

_ARABIC_FONT_REGISTERED = False
_ARABIC_FONT_NAME = "IBMPlexSansArabic"
_ARABIC_FONT_BOLD_NAME = "IBMPlexSansArabic-Bold"

_FONT_CACHE = os.path.join(tempfile.gettempdir(), "ca_ibmplexarabic.ttf")
_FONT_URLS = [
    "https://github.com/google/fonts/raw/main/ofl/ibmplexsansarabic/IBMPlexSansArabic-Regular.ttf",
    "https://cdn.jsdelivr.net/gh/google/fonts@main/ofl/ibmplexsansarabic/IBMPlexSansArabic-Regular.ttf",
]
_FONT_BOLD_CACHE = os.path.join(tempfile.gettempdir(), "ca_ibmplexarabic_bold.ttf")
_FONT_BOLD_URLS = [
    "https://github.com/google/fonts/raw/main/ofl/ibmplexsansarabic/IBMPlexSansArabic-Bold.ttf",
    "https://cdn.jsdelivr.net/gh/google/fonts@main/ofl/ibmplexsansarabic/IBMPlexSansArabic-Bold.ttf",
]


def _download_font(cache_path, urls):
    if os.path.exists(cache_path) and os.path.getsize(cache_path) > 10000:
        return cache_path
    for url in urls:
        try:
            r = httpx.get(url, timeout=20, follow_redirects=True)
            r.raise_for_status()
            with open(cache_path, "wb") as f:
                f.write(r.content)
            if os.path.getsize(cache_path) > 10000:
                return cache_path
        except Exception:
            continue
    return None


def _ensure_arabic_font():
    global _ARABIC_FONT_REGISTERED
    if _ARABIC_FONT_REGISTERED:
        return _ARABIC_FONT_NAME, _ARABIC_FONT_BOLD_NAME

    from reportlab.pdfbase import pdfmetrics
    from reportlab.pdfbase.ttfonts import TTFont

    regular = _download_font(_FONT_CACHE, _FONT_URLS)
    bold = _download_font(_FONT_BOLD_CACHE, _FONT_BOLD_URLS)

    if regular and bold:
        try:
            pdfmetrics.registerFont(TTFont(_ARABIC_FONT_NAME, regular))
            pdfmetrics.registerFont(TTFont(_ARABIC_FONT_BOLD_NAME, bold))
            _ARABIC_FONT_REGISTERED = True
            return _ARABIC_FONT_NAME, _ARABIC_FONT_BOLD_NAME
        except Exception:
            pass

    return "Helvetica", "Helvetica-Bold"


def _ar(text: str) -> str:
    """Reshape + reorder Arabic text (no-op if deps missing)."""
    if not text or not _HAS_ARABIC:
        return text
    try:
        reshaped = arabic_reshaper.reshape(text)
        return get_display(reshaped)
    except Exception:
        return text


# --------------------------------------------------------------------------- #
#  Logo preparation (convert ALPHA-LOGO.png RGBA → RGB JPEG, at import time) #
# --------------------------------------------------------------------------- #

_LOGO_JPG_PATH: Optional[str] = None

try:
    from PIL import Image as _PIL
    from app.services.logo_data import LOGO_B64
    _raw = base64.b64decode(LOGO_B64)
    _pil = _PIL.open(io.BytesIO(_raw))
    if _pil.mode != "RGB":
        _bg = _PIL.new("RGB", _pil.size, (15, 27, 45))
        if _pil.mode == "RGBA":
            _bg.paste(_pil, mask=_pil.split()[3])
        else:
            _bg.paste(_pil)
        _pil = _bg
    _path = os.path.join(tempfile.gettempdir(), "ca_alpha_logo.jpg")
    _pil.save(_path, format="JPEG", quality=95)
    _LOGO_JPG_PATH = _path
except Exception:
    _LOGO_JPG_PATH = ""


# --------------------------------------------------------------------------- #
#  Shield logo (reportlab Drawing rendered onto canvas)                      #
# --------------------------------------------------------------------------- #

def _draw_shield(c: "canvas.Canvas", cx: float, cy: float, sz: float):
    """Draw the platform shield logo centered at (cx, cy) with size sz."""
    from reportlab.lib.colors import HexColor, Color
    from reportlab.graphics.shapes import Drawing, Path, Group, Rect, Circle
    from reportlab.graphics import renderPDF

    S = sz / 200.0
    d = Drawing(sz, sz * 1.2)  # original viewBox is 200×240

    def ox(v): return (v - 100) * S
    def oy(v): return (240 - v) * S

    G = HexColor("#10b981")
    D = HexColor("#0a0a0a")
    L = HexColor("#1a1a1a")

    g = Group()

    # Outer shield path (converted Q→C for reportlab cubic bezier)
    # Q 198 198 100 232 → C 192.67 174.67 165.33 209.33 100 232
    # Q 18 198 18 128  → C 45.33 209.33 18 174.67 18 128
    p = Path()
    p.moveTo(ox(100), oy(8))
    p.lineTo(ox(182), oy(48))
    p.lineTo(ox(182), oy(128))
    p.curveTo(ox(192.67), oy(174.67), ox(165.33), oy(209.33), ox(100), oy(232))
    p.curveTo(ox(45.33), oy(209.33), ox(18), oy(174.67), ox(18), oy(128))
    p.lineTo(ox(18), oy(48))
    p.closePath()
    p.strokeColor = G
    p.strokeWidth = max(0.5, S * 3)
    p.fillColor = L
    g.add(p)

    # Inner dashed shield
    # Q 168 188 100 216 → C 165.33 181.33 145.33 201.33 100 216
    # Q 32 188 32 128  → C 54.67 201.33 32 181.33 32 128
    p2 = Path()
    p2.moveTo(ox(100), oy(22))
    p2.lineTo(ox(168), oy(56))
    p2.lineTo(ox(168), oy(128))
    p2.curveTo(ox(165.33), oy(181.33), ox(145.33), oy(201.33), ox(100), oy(216))
    p2.curveTo(ox(54.67), oy(201.33), ox(32), oy(181.33), ox(32), oy(128))
    p2.lineTo(ox(32), oy(56))
    p2.closePath()
    p2.strokeColor = Color(16/255, 185/255, 129/255, alpha=0.25)
    p2.strokeWidth = max(0.3, S * 1)
    p2.strokeDashArray = [max(1, S * 3), max(1, S * 4)]
    p2.fillColor = None
    g.add(p2)

    # Lock body
    lock = Rect(-S*56/2, oy(154), S*56, S*48, rx=S*6, ry=S*6)
    lock.fillColor = G
    lock.strokeColor = None
    g.add(lock)

    # Lock shackle (U-shape via two lines + arc)
    sw = max(0.5, S * 6)
    shackle = Path()
    shackle.moveTo(ox(82), oy(118))
    shackle.lineTo(ox(82), oy(102))
    # Arc from (82,102) to (118,102) going top — approximated with cubic bezier
    # Semi-circle: control points at 0.552R offset
    r18 = S * 18
    k = 0.552 * r18
    shackle.curveTo(ox(82), oy(102) + k, ox(118), oy(102) + k, ox(118), oy(102))
    shackle.lineTo(ox(118), oy(118))
    shackle.strokeColor = G
    shackle.strokeWidth = sw
    # "round" line cap
    shackle.fillColor = None
    g.add(shackle)

    # Keyhole circle
    kh = Circle(ox(100), oy(138), S*5)
    kh.fillColor = D
    kh.strokeColor = None
    g.add(kh)

    # Keyhole rectangle below
    khr = Rect(ox(100)-S*2.5, oy(144)-S*7, S*5, S*14, rx=S*1, ry=S*1)
    khr.fillColor = D
    khr.strokeColor = None
    g.add(khr)

    d.add(g)
    renderPDF.draw(d, c, cx - sz/2, cy - sz*1.2/2)


# --------------------------------------------------------------------------- #
#  Verifiability                                                              #
# --------------------------------------------------------------------------- #

def make_verify_code(user_id: str) -> str:
    """Generate a short, human-friendly verify code (8-10 chars)."""
    alphabet = "ABCDEFGHJKMNPQRSTUVWXYZ23456789"  # no 0/1/I/O confusion
    suffix = "".join(random.choice(alphabet) for _ in range(4))
    head = re.sub(r"[^a-f0-9]", "", (user_id or "").lower())[:6].upper()
    if len(head) < 4:
        head = (head + "USER1")[:6]
    return f"APEX-{head}-{suffix}"


def build_cert_title(category: str) -> str:
    cat = (category or "").strip().lower()
    mapping = {
        "vulnerability-hunter": "Cybersecurity Vulnerability Hunter",
        "code-fixing": "Secure Code Specialist",
        "log-analysis": "SOC Log Analysis Operator",
        "web": "Web Application Security",
        "crypto": "Applied Cryptography",
    }
    return mapping.get(cat, category or "Cybersecurity")


def category_label(category: str, lang: str = "en") -> str:
    cat = (category or "").strip().lower()
    ar_map = {
        "vulnerability-hunter": "صياد الثغرات",
        "code-fixing": "إصلاح الأكواد",
        "log-analysis": "تحليل السجلات",
        "web": "أمن الويب",
        "crypto": "التشفير",
    }
    en_map = {
        "vulnerability-hunter": "Vulnerability Hunter",
        "code-fixing": "Code Fixing",
        "log-analysis": "Log Analysis",
        "web": "Web Security",
        "crypto": "Cryptography",
    }
    return (ar_map if lang == "ar" else en_map).get(cat, category or "")


def _format_date_en(dt_value) -> str:
    if not dt_value:
        return ""
    try:
        if isinstance(dt_value, str):
            s = dt_value.replace("Z", "+00:00")
            dt = datetime.fromisoformat(s)
        else:
            dt = dt_value
        return dt.strftime("%B %d, %Y")
    except Exception:
        return str(dt_value)[:10]


# --------------------------------------------------------------------------- #
#  PDF generation                                                             #
# --------------------------------------------------------------------------- #

def build_certificate_pdf(
    user_name: str,
    category: str,
    title: str,
    issue_date: str,
    verify_code: str,
    cert_id: str,
    lang: str = "en",
) -> bytes:
    from reportlab.lib.pagesizes import landscape, A4
    from reportlab.lib.colors import HexColor, Color
    from reportlab.lib.units import mm
    from reportlab.pdfgen import canvas
    import qrcode
    from qrcode.image.pil import PilImage

    W, H = landscape(A4)
    buf = io.BytesIO()
    c = canvas.Canvas(buf, pagesize=landscape(A4))

    # ── Landing Page color palette (cream/white, emerald, amber) ──
    BG_CARD   = HexColor("#FFFFFF")  # white card
    GREEN     = HexColor("#10B981")  # emerald — primary brand accent
    AMBER      = HexColor("#F59E0B")  # amber/gold accent
    TEXT_PRI   = HexColor("#0A0A0A")  # near black
    TEXT_SEC   = HexColor("#5A5A58")  # medium gray
    TEXT_MUTED = Color(90/255, 90/255, 88/255, alpha=0.5)
    BORDER     = Color(10/255, 10/255, 10/255, alpha=0.08)

    ar_font, ar_font_bold = _ensure_arabic_font()
    en_font      = "Helvetica"
    en_font_bold = "Helvetica-Bold"

    _AR = re.compile(r'[\u0600-\u06FF\u0750-\u077F\u08A0-\u08FF\uFB50-\uFDFF\uFE70-\uFEFF]', re.UNICODE)

    def _is_ar(text):
        return bool(_AR.search(str(text or "")))

    def _font(style="regular"):
        return en_font_bold if style == "bold" else en_font

    def _font_for(text, style="regular"):
        return (ar_font_bold if style == "bold" else ar_font) if _is_ar(text) else (en_font_bold if style == "bold" else en_font)

    def _tx(text):
        return _ar(text) if _is_ar(text) else text

    # ── Background: cream gradient ──
    for y in range(0, int(H), 3):
        t = 1 - y / H
        r = 0.953 + 0.01 * t
        g = 0.945 + 0.01 * t
        b = 0.925 + 0.01 * t
        c.setFillColorRGB(min(r, 1), min(g, 1), min(b, 1))
        c.rect(0, y, W, 3, fill=1, stroke=0)

    # ── Double-border card (white outer, inner with green border) ──
    MARGIN = 10*mm
    c.setFillColor(BG_CARD)
    c.roundRect(MARGIN, MARGIN, W - 2*MARGIN, H - 2*MARGIN, 6*mm, fill=1, stroke=0)
    c.setStrokeColor(Color(16/255, 185/255, 129/255, alpha=0.15))
    c.setLineWidth(0.3)
    MARGIN2 = 13*mm
    c.roundRect(MARGIN2, MARGIN2, W - 2*MARGIN2, H - 2*MARGIN2, 5*mm, fill=0, stroke=1)

    # Corner ornaments (small L-shapes)
    def _corner(x, y, flip_x=False, flip_y=False):
        s = 4*mm
        c.setStrokeColor(GREEN)
        c.setLineWidth(0.4)
        if flip_x:
            x -= s
        if flip_y:
            y -= s
        c.line(x, y, x + s, y) if not flip_x else c.line(x, y, x - s, y)
        c.line(x, y, x, y + s) if not flip_y else c.line(x, y, x, y - s)

    _corner(MARGIN2 + 3*mm, H - MARGIN2 - 3*mm)                          # top-left
    _corner(W - MARGIN2 - 3*mm, H - MARGIN2 - 3*mm, flip_x=True)         # top-right
    _corner(MARGIN2 + 3*mm, MARGIN2 + 3*mm, flip_y=True)                 # bottom-left
    _corner(W - MARGIN2 - 3*mm, MARGIN2 + 3*mm, flip_x=True, flip_y=True) # bottom-right

    CX = W / 2
    INNER_L = MARGIN2 + 10*mm
    INNER_R = W - MARGIN2 - 10*mm
    TOP = H - MARGIN2 - 14*mm
    BOT = MARGIN2 + 12*mm

    # ── Top gradient stripe (amber → emerald → amber) ──
    stripe_y = TOP + 8*mm
    for x in range(0, int(W - 2*MARGIN2), 2):
        t2 = x / (W - 2*MARGIN2)
        if t2 < 0.5:
            mix = t2 * 2
            r = 245/255 * (1-mix) + 16/255 * mix
            g = 158/255 * (1-mix) + 185/255 * mix
            b = 11/255 * (1-mix) + 129/255 * mix
        else:
            mix = (t2 - 0.5) * 2
            r = 16/255 * (1-mix) + 245/255 * mix
            g = 185/255 * (1-mix) + 158/255 * mix
            b = 129/255 * (1-mix) + 11/255 * mix
        c.setFillColorRGB(min(r, 1), min(g, 1), min(b, 1))
        c.rect(MARGIN2 + x, stripe_y, 2, 1.5*mm, fill=1, stroke=0)

    # ── Header: logos + brand left / CYBERARENA right ──
    head_y = stripe_y - 4*mm
    logo_size = 9 * mm
    # ALPHA logo
    if _LOGO_JPG_PATH and os.path.exists(_LOGO_JPG_PATH):
        c.drawImage(_LOGO_JPG_PATH, INNER_L, head_y - 2*mm, width=logo_size, height=logo_size, preserveAspectRatio=True)
    # Shield logo (next to ALPHA logo)
    shield_sz = logo_size * 0.85
    _draw_shield(c, INNER_L + logo_size + shield_sz/2 + 2*mm, head_y + shield_sz/2, shield_sz)

    text_x = INNER_L + logo_size + shield_sz + 5*mm
    c.setFillColor(TEXT_PRI)
    c.setFont(_font("bold"), 9)
    c.drawString(text_x, head_y + 2*mm, _tx("ALPHA TEAM"))
    c.setFillColor(TEXT_MUTED)
    c.setFont(_font(), 6)
    c.drawString(text_x, head_y - 3*mm, _tx("CYBERSECURITY"))

    c.setFillColor(GREEN)
    c.setFont(_font("bold"), 11)
    c.drawRightString(INNER_R, head_y + 2*mm, _tx("CYBERARENA"))
    c.setFillColor(TEXT_MUTED)
    c.setFont(_font(), 6)
    c.drawRightString(INNER_R, head_y - 3*mm, _tx("CERTIFICATE OF COMPLETION"))

    # divider
    div_y = head_y - 8*mm
    c.setStrokeColor(Color(16/255, 185/255, 129/255, alpha=0.15))
    c.setLineWidth(0.3)
    c.line(INNER_L, div_y, INNER_R + 2*mm, div_y)

    # ── Body ──
    # Label "CERTIFICATE" (small, muted, uppercase)
    c.setFillColor(TEXT_MUTED)
    c.setFont(_font("bold"), 7)
    c.drawCentredString(CX, div_y - 8*mm, "CERTIFICATE")

    # Intro
    c.setFillColor(TEXT_SEC)
    c.setFont(_font(), 9)
    c.drawCentredString(CX, div_y - 15*mm, "This is to certify that")

    # Name (hero)
    name = (user_name or "Trainee")[:60]
    c.setFillColor(TEXT_PRI)
    c.setFont(_font_for(name, "bold"), 26)
    c.drawCentredString(CX, div_y - 28*mm, _tx(name))

    # Name bar: amber dot + green line + amber dot
    nw = c.stringWidth(_tx(name), _font_for(name, "bold"), 26)
    bar_top = div_y - 32*mm
    c.setFillColor(AMBER)
    c.circle(CX - nw/2 - 5, bar_top + 2.5, 1.2, fill=1, stroke=0)
    c.setStrokeColor(Color(16/255, 185/255, 129/255, alpha=0.25))
    c.setLineWidth(0.3)
    c.line(CX - nw/2 - 3, bar_top + 1.5, CX + nw/2 + 3, bar_top + 1.5)
    c.setFillColor(AMBER)
    c.circle(CX + nw/2 + 5, bar_top + 2.5, 1.2, fill=1, stroke=0)

    # Body text
    c.setFillColor(TEXT_SEC)
    c.setFont(_font(), 8)
    c.drawCentredString(CX, div_y - 40*mm, "has successfully completed all interactive challenges and practical labs in")

    # Category pill
    cat_label = category_label(category, lang="en")
    bw = c.stringWidth(_tx(cat_label), _font("bold"), 14) + 12*mm
    c.setFillColor(Color(16/255, 185/255, 129/255, alpha=0.07))
    c.roundRect(CX - bw/2, div_y - 50*mm - 3*mm, bw, 9*mm, 4.5*mm, fill=1, stroke=0)
    c.setStrokeColor(Color(16/255, 185/255, 129/255, alpha=0.2))
    c.setLineWidth(0.3)
    c.roundRect(CX - bw/2, div_y - 50*mm - 3*mm, bw, 9*mm, 4.5*mm, fill=0, stroke=1)
    c.setFillColor(GREEN)
    c.setFont(_font("bold"), 14)
    c.drawCentredString(CX, div_y - 50*mm, _tx(cat_label))

    # Subtitle
    c.setFillColor(TEXT_MUTED)
    c.setFont(_font_for(title), 6.5)
    c.drawCentredString(CX, div_y - 55*mm, _tx(title))

    # ── Footer (three-column grid: QR | info | signature) ──
    foot_top = BOT + 28*mm
    c.setStrokeColor(Color(10/255, 10/255, 10/255, alpha=0.05))
    c.setLineWidth(0.5)
    c.line(INNER_L, foot_top, INNER_R, foot_top)

    # Column 1: QR
    qr_payload = (CERT_VERIFY_BASE_URL or "https://cyberarena.app/verify") + "/verify?code=" + verify_code
    qr = qrcode.QRCode(box_size=3, border=0)
    qr.add_data(qr_payload)
    qr.make(fit=True)
    qr_img = qr.make_image(fill_color="#10B981", back_color="#FFFFFF", image_factory=PilImage)
    qr_path = os.path.join(tempfile.gettempdir(), f"ca_qr_{verify_code.replace(' ', '_')}.png")
    qr_img.save(qr_path, format="PNG")
    qr_s = 14*mm
    qr_x = INNER_L + 2*mm
    c.drawImage(qr_path, qr_x, foot_top - qr_s - 8*mm, width=qr_s, height=qr_s, preserveAspectRatio=True, mask="auto")
    c.setStrokeColor(Color(10/255, 10/255, 10/255, alpha=0.07))
    c.setLineWidth(0.3)
    c.roundRect(qr_x - 0.8*mm, foot_top - qr_s - 8.8*mm, qr_s + 1.6*mm, qr_s + 1.6*mm, 1.5*mm, fill=0, stroke=1)
    c.setFillColor(TEXT_SEC)
    c.setFont(_font_for("SCAN TO VERIFY", "bold"), 5)
    c.drawCentredString(qr_x + qr_s/2, foot_top - qr_s - 11*mm, "SCAN TO VERIFY")
    c.setFillColor(TEXT_MUTED)
    c.setFont(_font_for(verify_code), 4.5)
    c.drawCentredString(qr_x + qr_s/2, foot_top - qr_s - 14*mm, _tx(verify_code))

    # Column 2: Info
    infox = INNER_L + 28*mm
    date_str = _format_date_en(issue_date) or "—"
    info_items = [
        ("ISSUE DATE", date_str, TEXT_PRI, 7),
        ("CERTIFICATE ID", str(cert_id)[:28], TEXT_SEC, 6.5),
        ("VERIFICATION CODE", verify_code, GREEN, 6.5),
    ]
    for i, (label, val, vcolor, vsize) in enumerate(info_items):
        ypos = foot_top - 3.5*mm - i * 6.5*mm
        c.setFillColor(TEXT_MUTED)
        c.setFont(_font_for(label, "bold"), 5)
        c.drawString(infox, ypos, _tx(label))
        c.setFillColor(vcolor)
        vfont = "Courier" if not _is_ar(val) else _font_for(val)
        c.setFont(vfont, vsize)
        c.drawString(infox, ypos - 2.5*mm, _tx(val))

    # Column 3: Signature
    sig_x = INNER_R
    c.setStrokeColor(Color(90/255, 90/255, 88/255, alpha=0.15))
    c.setLineWidth(0.3)
    c.line(sig_x - 18*mm, foot_top - 8*mm, sig_x, foot_top - 8*mm)
    c.setFillColor(TEXT_SEC)
    c.setFont(_font_for("Alpha Team Academic Board", "bold"), 5.5)
    c.drawRightString(sig_x, foot_top - 11*mm, "Alpha Team Academic Board")
    c.setFillColor(TEXT_MUTED)
    c.setFont(_font_for("Alpha Team"), 5)
    c.drawRightString(sig_x, foot_top - 14*mm, "Alpha Team")

    # Bottom gradient stripe
    for x in range(0, int(W), 2):
        t2 = x / W
        if t2 < 0.5:
            mix = t2 * 2
            r = 245/255 * (1-mix) + 16/255 * mix
            g = 158/255 * (1-mix) + 185/255 * mix
            b = 11/255 * (1-mix) + 129/255 * mix
        else:
            mix = (t2 - 0.5) * 2
            r = 16/255 * (1-mix) + 245/255 * mix
            g = 185/255 * (1-mix) + 158/255 * mix
            b = 129/255 * (1-mix) + 11/255 * mix
        c.setFillColorRGB(min(r, 1), min(g, 1), min(b, 1))
        c.rect(x, 0, 2, 0.8*mm, fill=1, stroke=0)

    c.showPage()
    c.save()
    return buf.getvalue()


# --------------------------------------------------------------------------- #
#  HTTP handlers (called by the api/certificates router)                      #
# --------------------------------------------------------------------------- #

async def handle_certificates(req) -> dict:
    headers = {
        "apikey": SUPABASE_ANON_KEY,
        "Authorization": f"Bearer {SUPABASE_ANON_KEY}",
        "Content-Type": "application/json",
        "Prefer": "return=representation",
    }

    if req.action == "list":
        url = f"{SUPABASE_URL}/rest/v1/certificates?select=*"
        async with httpx.AsyncClient() as client:
            resp = await client.get(url, headers=headers)
            if resp.status_code != 200:
                raise HTTPException(status_code=resp.status_code, detail="Failed to fetch certificates")
            certs = resp.json()
        user_certs = [c for c in certs if str(c.get("user_id")) == str(req.user_id)]
        return {"certificates": user_certs}

    if req.action == "issue":
        async with httpx.AsyncClient() as client:
            # 1) already-issued?
            check_url = (
                f"{SUPABASE_URL}/rest/v1/certificates?user_id=eq.{req.user_id}"
                f"&category=eq.{req.category}&select=*&limit=1"
            )
            check_resp = await client.get(check_url, headers=headers)
            if check_resp.status_code == 200 and check_resp.json():
                return {"status": "already_issued", "certificate": check_resp.json()[0]}

            # 2) Eligibility
            completions = await count_completions(req.user_id, req.category or "")
            if completions < CERT_REQUIRED_COMPLETIONS:
                return {
                    "status": "not_eligible",
                    "error": "completion_threshold_not_met",
                    "message": (
                        f"You need {CERT_REQUIRED_COMPLETIONS} completed challenges "
                        f"in {req.category} to earn this certificate "
                        f"(you currently have {completions})."
                    ),
                    "completions": completions,
                    "required": CERT_REQUIRED_COMPLETIONS,
                }

            # 3) User name
            user_name = ""
            try:
                users_resp = await client.get(
                    f"{SUPABASE_URL}/rest/v1/users?id=eq.{req.user_id}&select=name&limit=1",
                    headers=headers,
                )
                if users_resp.status_code == 200 and users_resp.json():
                    user_name = (users_resp.json()[0].get("name") or "").strip()
            except Exception:
                pass

            # 4) Issue
            verify_code = req.verify_code or make_verify_code(req.user_id)
            category = req.category or "general"
            payload = {
                "user_id": req.user_id,
                "user_name": user_name,
                "category": category,
                "title": (
                    req.details.get("title")
                    if isinstance(req.details, dict) and req.details.get("title")
                    else build_cert_title(category)
                ),
                "verify_code": verify_code,
                "issue_date": datetime.utcnow().isoformat() + "Z",
                "details": req.details or {"issue_reason": "50 challenges completed"},
            }
            resp = await client.post(
                f"{SUPABASE_URL}/rest/v1/certificates",
                headers={**headers, "Prefer": "return=representation"},
                json=payload,
            )
            if resp.status_code not in (200, 201):
                raise HTTPException(
                    status_code=resp.status_code,
                    detail=f"Failed to issue certificate: {resp.text[:200]}",
                )
            certs = resp.json()
            return {"status": "issued", "certificate": certs[0] if certs else {}}

    raise HTTPException(status_code=400, detail="Invalid action")


async def download_certificate_pdf(cert_id: str, lang: str = "en") -> Response:
    if not SUPABASE_URL or not SUPABASE_ANON_KEY:
        raise HTTPException(status_code=503, detail="Supabase not configured")
    url = f"{SUPABASE_URL}/rest/v1/certificates?id=eq.{cert_id}&select=*&limit=1"
    try:
        async with httpx.AsyncClient(timeout=15) as client:
            r = await client.get(url, headers=supabase_headers())
            if r.status_code != 200:
                raise HTTPException(status_code=502, detail=f"Supabase error: {r.text[:200]}")
            rows = r.json()
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=502, detail=str(e))
    if not rows:
        raise HTTPException(status_code=404, detail="الشهادة غير موجودة / Certificate not found")
    cert = rows[0]

    pdf_bytes = build_certificate_pdf(
        user_name=cert.get("user_name") or "",
        category=cert.get("category") or "",
        title=cert.get("title") or build_cert_title(cert.get("category", "")),
        issue_date=cert.get("issue_date") or cert.get("issued_at") or "",
        verify_code=cert.get("verify_code") or "",
        cert_id=cert.get("id") or cert_id,
        lang="en",
    )
    cat = (cert.get("category") or "CyberArena").replace(" ", "_")
    fname = f"CyberArena-Certificate-{cat}-{cert.get('verify_code', cert_id)[:20]}.pdf"
    return Response(
        content=pdf_bytes,
        media_type="application/pdf",
        headers={
            "Content-Disposition": f'attachment; filename="{fname}"',
            "Cache-Control": "no-store",
        },
    )


async def verify_certificate(verify_code: str) -> dict:
    if not SUPABASE_URL or not SUPABASE_ANON_KEY:
        raise HTTPException(status_code=503, detail="Supabase not configured")
    url = f"{SUPABASE_URL}/rest/v1/certificates?verify_code=eq.{verify_code}&select=*&limit=1"
    try:
        async with httpx.AsyncClient(timeout=15) as client:
            r = await client.get(url, headers=supabase_headers())
            if r.status_code != 200:
                raise HTTPException(status_code=502, detail=f"Supabase error: {r.text[:200]}")
            rows = r.json()
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=502, detail=str(e))
    if not rows:
        return {"valid": False, "verify_code": verify_code, "message": "Certificate not found"}
    c = rows[0]
    return {
        "valid": True,
        "verify_code": c.get("verify_code"),
        "certificate_id": c.get("id"),
        "user_name": c.get("user_name"),
        "category": c.get("category"),
        "title": c.get("title") or build_cert_title(c.get("category", "")),
        "issue_date": c.get("issue_date") or c.get("issued_at"),
    }


async def cert_progress(user_id: str, category: str) -> dict:
    n = await count_completions(user_id, category)
    return {
        "category": category,
        "completions": n,
        "required": CERT_REQUIRED_COMPLETIONS,
        "ready": n >= CERT_REQUIRED_COMPLETIONS,
    }