The Intelligent Payment Routing Engine

sqlCREATE TABLE payin_methods (
    id TEXT PRIMARY KEY,
    code TEXT UNIQUE NOT NULL,     -- e.g., "PAYIN_ORANGE_CI"
    name TEXT NOT NULL,            -- e.g., "Orange Money Ivory Coast"
    country_code TEXT NOT NULL,    -- e.g., "CI"
    currency TEXT NOT NULL,        -- e.g., "XOF"
    type TEXT NOT NULL,            -- e.g., "mobile_money"
    operator TEXT,                 -- e.g., "Orange"
    is_active INTEGER DEFAULT 1,
    min_amount INTEGER,
    max_amount INTEGER,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

pythonasync def get_payment_methods_for_country(
    country_code: str
) -> list[dict]:
    """
    Get all available payment methods for a country.
    Returns methods sorted by type (mobile_money first, then card).
    """
    with get_db() as conn:
        methods = conn.execute(
            """
            SELECT code, name, type, operator, currency,
                   min_amount, max_amount
            FROM payin_methods
            WHERE country_code = ? AND is_active = 1
            ORDER BY
                CASE type
                    WHEN 'mobile_money' THEN 1
                    WHEN 'card' THEN 2
                    ELSE 3
                END,
                name
            """,
            (country_code.upper(),)
        ).fetchall()

    return [dict(m) for m in methods]

sqlCREATE TABLE provider_routing (
    id TEXT PRIMARY KEY,
    payin_method_code TEXT NOT NULL,   -- e.g., "PAYIN_ORANGE_CI"
    provider_id TEXT NOT NULL,          -- e.g., "paiementpro"
    provider_method_code TEXT,          -- e.g., "OMCIV2"
    priority INTEGER NOT NULL DEFAULT 1,
    environment TEXT DEFAULT 'production',
    is_active INTEGER DEFAULT 1,
    UNIQUE(payin_method_code, provider_id, environment)
);

PAYIN_ORANGE_CI routing:
  Priority 1: paiementpro  (method code: OMCIV2)
  Priority 2: pawapay      (method code: ORANGE_CIV)
  Priority 3: hub2         (method code: Orange)

PAYIN_MTN_GH routing:
  Priority 1: pawapay      (method code: MTN_GHA)
  Priority 2: hub2         (method code: MTN)

pythonasync def get_provider_for_unified_method(
    payment_method: str,
    app_id: str,
    environment: str = "production",
    skip_providers: list[str] = None
) -> dict | None:
    """
    Find the best provider for a unified payment method.

    Args:
        payment_method: Unified code like "PAYIN_ORANGE_CI"
        app_id: The merchant's app ID
        environment: "production" or "sandbox"
        skip_providers: List of provider IDs to skip (for fallback)

    Returns:
        Dict with provider_id, provider_method_code, priority,
        or None if no provider available.
    """
    skip_providers = skip_providers or []

    with get_db() as conn:
        # Get routing entries ordered by priority
        routes = conn.execute(
            """
            SELECT
                pr.provider_id,
                pr.provider_method_code,
                pr.priority
            FROM provider_routing pr
            WHERE pr.payin_method_code = ?
              AND pr.environment = ?
              AND pr.is_active = 1
              AND pr.provider_id NOT IN ({})
            ORDER BY pr.priority ASC
            """.format(
                ",".join("?" * len(skip_providers))
            ),
            (payment_method, environment, *skip_providers)
        ).fetchall()

        if not routes:
            return None

        # Check which providers the merchant has credentials for
        for route in routes:
            has_creds = conn.execute(
                """
                SELECT 1 FROM provider_credentials
                WHERE app_id = ?
                  AND provider_id = ?
                  AND environment = ?
                  AND is_active = 1
                """,
                (app_id, route["provider_id"], environment)
            ).fetchone()

            if has_creds:
                return {
                    "provider_id": route["provider_id"],
                    "provider_method_code":
                        route["provider_method_code"],
                    "priority": route["priority"],
                }

        return None

pythonasync def initiate_payment_with_fallback(
    payment_method: str,
    payment_data: dict,
    app_id: str,
    environment: str,
    max_attempts: int = 3
) -> tuple[InitPaymentResult, str]:
    """
    Try to initiate a payment, falling back to lower-priority
    providers if the primary fails.

    Returns:
        Tuple of (result, provider_id)
    """
    skip_providers = []

    for attempt in range(max_attempts):
        route = await get_provider_for_unified_method(
            payment_method, app_id, environment,
            skip_providers=skip_providers
        )

        if not route:
            break

        provider_id = route["provider_id"]

        # Get provider instance with merchant's credentials
        credentials = await get_decrypted_credentials(
            app_id, provider_id, environment
        )
        provider = provider_registry.get_instance(
            provider_id, credentials, app_id
        )

        # Add provider-specific method code to payment data
        payment_data["provider_method_code"] = (
            route["provider_method_code"]
        )

        # Try the payment
        result = await provider.initiate_payment(payment_data)

        if result.status != "failed":
            return result, provider_id

        # This provider failed -- skip it and try next
        skip_providers.append(provider_id)

    # All providers failed
    return InitPaymentResult(
        provider_ref="",
        status="failed",
        instructions="No available provider could process this payment"
    ), ""

pythondef extract_country_from_method(payment_method: str) -> str:
    """
    Extract country code from unified payment method.

    Examples:
        PAYIN_ORANGE_CI -> CI
        PAYIN_MTN_GH    -> GH
        PAYIN_MPESA_KE  -> KE
        PAYIN_WAVE_SN   -> SN
        PAYIN_CARD_GLOBAL -> GLOBAL
    """
    parts = payment_method.split("_")
    return parts[-1] if parts else ""

# Country to currency mapping
COUNTRY_CURRENCY = {
    "CI": "XOF",  # Ivory Coast -> CFA Franc (BCEAO)
    "SN": "XOF",  # Senegal
    "ML": "XOF",  # Mali
    "BF": "XOF",  # Burkina Faso
    "BJ": "XOF",  # Benin
    "TG": "XOF",  # Togo
    "NE": "XOF",  # Niger
    "GW": "XOF",  # Guinea-Bissau
    "GH": "GHS",  # Ghana -> Cedi
    "KE": "KES",  # Kenya -> Shilling
    "NG": "NGN",  # Nigeria -> Naira
    "TZ": "TZS",  # Tanzania -> Shilling
    "UG": "UGX",  # Uganda -> Shilling
    "RW": "RWF",  # Rwanda -> Franc
    "ZA": "ZAR",  # South Africa -> Rand
    "CM": "XAF",  # Cameroon -> CFA Franc (BEAC)
    "GLOBAL": None,  # Multi-currency
}

def get_currency_for_method(payment_method: str) -> str | None:
    country = extract_country_from_method(payment_method)
    return COUNTRY_CURRENCY.get(country)

json{
    "amount": 5000,
    "payment_method": "PAYIN_ORANGE_CI",
    "customer": {
        "phone": "+2250709757296"
    }
}