2.6Using JOINs in Test Automation

def test_order_total_matches_line_items(db, api_client):
    """After creating an order, verify total equals sum of line items."""
    # Create order via API
    order = api_client.post("/orders", json={
        "items": [
            {"product_id": 1, "quantity": 2},
            {"product_id": 2, "quantity": 1}
        ]
    }).json()

    # Verify in database
    cursor = db.cursor()
    cursor.execute("""
        SELECT o.total, SUM(li.quantity * li.unit_price) AS calculated
        FROM orders o
        JOIN line_items li ON li.order_id = o.id
        WHERE o.id = %s
        GROUP BY o.total
    """, (order["id"],))
    row = cursor.fetchone()
    assert row is not None
    assert row[0] == row[1], f"Order total {row[0]} != calculated {row[1]}"

def test_no_orphaned_line_items(db):
    """No line items should exist without a parent order."""
    cursor = db.cursor()
    cursor.execute("""
        SELECT COUNT(*)
        FROM line_items li
        LEFT JOIN orders o ON o.id = li.order_id
        WHERE o.id IS NULL
    """)
    orphan_count = cursor.fetchone()[0]
    assert orphan_count == 0, f"Found {orphan_count} orphaned line items"