# cockpit_stats.dspy — Returns dashboard stats cards
uid = await get_user()
org_id = await get_userorgid() or '0'

async with DBPools().sqlorContext(get_module_dbname('pipeline-sdlc')) as sor:
    # Active projects
    projs = await sor.sqlExe(
        "SELECT COUNT(*) as cnt FROM sd_projects WHERE status='active' AND org_id=${oid}$",
        {"oid": org_id})
    active_projects = projs[0].cnt if projs else 0

    # Ongoing iterations
    iters = await sor.sqlExe(
        "SELECT COUNT(*) as cnt FROM sd_iterations WHERE status='active' AND org_id=${oid}$",
        {"oid": org_id})
    active_iterations = iters[0].cnt if iters else 0

    # Pending bugs
    bugs = await sor.sqlExe(
        "SELECT COUNT(*) as cnt FROM sd_bugs WHERE status IN ('open','confirmed') AND (reporter_id=${uid}$ OR assignee_id=${uid}$ OR '1'='1')",
        {"uid": uid, "oid": org_id})
    pending_bugs = bugs[0].cnt if bugs else 0

    # Pending approvals — tasks waiting for human review
    approvals = await sor.sqlExe(
        "SELECT COUNT(*) as cnt FROM pipeline_task_steps WHERE state='waiting' AND step_type IN ('human_task','approval_gate')",
        {})
    pending_approvals = approvals[0].cnt if approvals else 0

return json.dumps({
    "active_projects": active_projects,
    "active_iterations": active_iterations,
    "pending_bugs": pending_bugs,
    "pending_approvals": pending_approvals,
}, ensure_ascii=False)