نظام الأفلييت متعدد التجار (Multi-Vendor Affiliate Platform) هو منظومة برمجية تُمكّن عدّة متاجر وتجار مستقلين (Tenants) من عرض منتجاتهم وحملاتهم التسويقية داخل سوق مركزي موحد، مع تمكين شبكة مسوقين بالعمولة (Affiliates) من توليد روابط تتبع مخصصة، واحتساب العمولات آلياً عبر محرك إسناد مالي دقيق (Attribution Engine) يفصل حسابات كل تاجر بصورة معزولة تماماً. يُبنى هذا النظام هندسياً عبر معمارية متعددة المستأجرين (Multi-Tenant Architecture) تستند إلى قواعد بيانات علائقية مثل PostgreSQL مدعومة بسياسات أمان على مستوى الصف (Row-Level Security – RLS). أما منهجية البرمجة بالذكاء الاصطناعي التفاعلي الموجه (Vibe Coding)، فهي استراتيجية هندسية تعتمد على تحويل المواصفات الوظيفية الدقيقة ومخططات المعمارية (System Architecture) إلى كود برمجي عالي الجودة ومكتوب بلغات مثل Node.js/TypeScript أو PHP عبر التوجيه المنطقي لنماذج الذكاء الاصطناعي التوليدية. يتكامل هذا النظام مباشرة مع قنوات الاتصال الفوري عبر WhatsApp API من خلال واجهات برمجية وخطافات ويب (Webhooks) لإرسال تنبيهات لحظية ومؤتمتة للتجار والمسوقين تخص مبيعاتهم وعمولاتهم، مما يحقق معدلات تفاعل تتجاوز 95% متفوقة على البريد التقليدي.

sequenceDiagram
    autonumber
    actor C as العميل (Customer)
    actor A as المسوق (Affiliate)
    participant S as متجر التاجر (Merchant Store)
    participant P as منصة الأفلييت (Affiliate Platform)
    participant W as خدمة WhatsApp API

    A->>P: 1. توليد رابط إحالة مخصص (?ref=AFF1001)
    A->>C: 2. ترويج الرابط عبر القنوات الإعلانية
    C->>S: 3. النقر على الرابط وتخزين Click ID في Cookie
    C->>S: 4. إتمام عملية الشراء (Checkout)
    S->>P: 5. إرسال Webhook ببيانات الطلب والـ Ref ID
    P->>P: 6. التحقق من التوقيع واحتساب العمولة بدفتر الأستاذ
    P->>W: 7. استدعاء API لإرسال إشعار فوري
    W-->>A: 8. تنبيه المسوق بالعمولة المستحقة

التعامل مع العمولات دون دفتر أستاذ مالي مزدوج (Double-Entry Ledger) يؤدي حتماً إلى أخطاء مالية كارثية عند حدوث إلغاء جزئي أو استرجاع للطلبات في بيئة متعددة التجار.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    phone_number VARCHAR(50) UNIQUE,
    full_name VARCHAR(255) NOT NULL,
    role VARCHAR(50) NOT NULL CHECK (role IN ('super_admin', 'merchant_admin', 'merchant_staff', 'affiliate')),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE merchants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    business_name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    webhook_secret VARCHAR(255) NOT NULL,
    api_key_hash VARCHAR(255) NOT NULL,
    default_commission_rate NUMERIC(5, 2) DEFAULT 10.00 CHECK (default_commission_rate >= 0),
    cookie_duration_days INT DEFAULT 30 CHECK (cookie_duration_days > 0),
    currency VARCHAR(10) DEFAULT 'EGP',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE merchant_members (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    merchant_id UUID NOT NULL REFERENCES merchants(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    permissions JSONB DEFAULT '{"can_manage_orders": true, "can_view_reports": true}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(merchant_id, user_id)
);

CREATE TABLE affiliates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    referral_code VARCHAR(50) UNIQUE NOT NULL,
    payout_details JSONB NOT NULL DEFAULT '{}',
    total_earned NUMERIC(12, 2) DEFAULT 0.00,
    balance_available NUMERIC(12, 2) DEFAULT 0.00,
    balance_pending NUMERIC(12, 2) DEFAULT 0.00,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE affiliate_campaigns (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    merchant_id UUID NOT NULL REFERENCES merchants(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    commission_type VARCHAR(20) DEFAULT 'percentage' CHECK (commission_type IN ('percentage', 'fixed')),
    commission_value NUMERIC(10, 2) NOT NULL CHECK (commission_value >= 0),
    is_active BOOLEAN DEFAULT TRUE,
    start_date TIMESTAMP WITH TIME ZONE,
    end_date TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE affiliate_links (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    affiliate_id UUID NOT NULL REFERENCES affiliates(id) ON DELETE CASCADE,
    merchant_id UUID NOT NULL REFERENCES merchants(id) ON DELETE CASCADE,
    campaign_id UUID REFERENCES affiliate_campaigns(id) ON DELETE SET NULL,
    custom_slug VARCHAR(100) NOT NULL,
    target_url TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(affiliate_id, merchant_id, custom_slug)
);

CREATE TABLE visitors (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ip_hash VARCHAR(64) NOT NULL,
    user_agent TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE clicks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    link_id UUID NOT NULL REFERENCES affiliate_links(id) ON DELETE CASCADE,
    affiliate_id UUID NOT NULL REFERENCES affiliates(id) ON DELETE CASCADE,
    merchant_id UUID NOT NULL REFERENCES merchants(id) ON DELETE CASCADE,
    visitor_id UUID REFERENCES visitors(id) ON DELETE SET NULL,
    click_id VARCHAR(100) UNIQUE NOT NULL,
    ip_hash VARCHAR(64) NOT NULL,
    is_fraudulent BOOLEAN DEFAULT FALSE,
    fraud_reason VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    merchant_id UUID NOT NULL REFERENCES merchants(id) ON DELETE CASCADE,
    external_store_product_id VARCHAR(100) NOT NULL,
    title VARCHAR(255) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    sku VARCHAR(100),
    is_available BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(merchant_id, external_store_product_id)
);

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    merchant_id UUID NOT NULL REFERENCES merchants(id) ON DELETE CASCADE,
    affiliate_id UUID REFERENCES affiliates(id) ON DELETE SET NULL,
    click_id VARCHAR(100) REFERENCES clicks(click_id) ON DELETE SET NULL,
    external_order_id VARCHAR(100) NOT NULL,
    order_status VARCHAR(50) NOT NULL CHECK (order_status IN ('pending', 'processing', 'completed', 'cancelled', 'refunded')),
    subtotal_amount NUMERIC(12, 2) NOT NULL,
    tax_amount NUMERIC(10, 2) DEFAULT 0.00,
    shipping_amount NUMERIC(10, 2) DEFAULT 0.00,
    total_amount NUMERIC(12, 2) NOT NULL,
    commission_eligible_amount NUMERIC(12, 2) NOT NULL,
    currency VARCHAR(10) NOT NULL,
    customer_phone VARCHAR(50),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(merchant_id, external_order_id)
);

CREATE TABLE order_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id UUID REFERENCES products(id) ON DELETE SET NULL,
    merchant_id UUID NOT NULL REFERENCES merchants(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(10, 2) NOT NULL,
    total_price NUMERIC(10, 2) NOT NULL,
    is_commissionable BOOLEAN DEFAULT TRUE
);

CREATE TABLE commissions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    merchant_id UUID NOT NULL REFERENCES merchants(id) ON DELETE CASCADE,
    affiliate_id UUID NOT NULL REFERENCES affiliates(id) ON DELETE CASCADE,
    rate_applied NUMERIC(5, 2) NOT NULL,
    amount NUMERIC(10, 2) NOT NULL,
    status VARCHAR(50) DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'reversed', 'paid')),
    hold_until TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE commission_ledger (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    commission_id UUID NOT NULL REFERENCES commissions(id) ON DELETE CASCADE,
    affiliate_id UUID NOT NULL REFERENCES affiliates(id) ON DELETE CASCADE,
    merchant_id UUID NOT NULL REFERENCES merchants(id) ON DELETE CASCADE,
    entry_type VARCHAR(20) NOT NULL CHECK (entry_type IN ('credit_pending', 'credit_available', 'debit_payout', 'debit_reversal')),
    amount NUMERIC(10, 2) NOT NULL,
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE payouts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    affiliate_id UUID NOT NULL REFERENCES affiliates(id) ON DELETE CASCADE,
    amount NUMERIC(10, 2) NOT NULL CHECK (amount > 0),
    status VARCHAR(50) DEFAULT 'requested' CHECK (status IN ('requested', 'processing', 'completed', 'rejected')),
    payout_method VARCHAR(50) NOT NULL,
    transaction_reference VARCHAR(255),
    processed_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE webhook_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    merchant_id UUID REFERENCES merchants(id) ON DELETE CASCADE,
    event_id VARCHAR(255) NOT NULL,
    event_type VARCHAR(100) NOT NULL,
    idempotency_key VARCHAR(255) UNIQUE NOT NULL,
    payload JSONB NOT NULL,
    status VARCHAR(50) DEFAULT 'received' CHECK (status IN ('received', 'processed', 'failed')),
    error_message TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE notifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    recipient_user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    channel VARCHAR(20) NOT NULL CHECK (channel IN ('whatsapp', 'sms', 'email', 'in_app')),
    destination VARCHAR(100) NOT NULL,
    message_content TEXT NOT NULL,
    external_message_id VARCHAR(255),
    delivery_status VARCHAR(50) DEFAULT 'queued' CHECK (delivery_status IN ('queued', 'sent', 'delivered', 'failed')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    merchant_id UUID REFERENCES merchants(id) ON DELETE SET NULL,
    action VARCHAR(100) NOT NULL,
    entity_name VARCHAR(100) NOT NULL,
    entity_id VARCHAR(100) NOT NULL,
    ip_address VARCHAR(50),
    details JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_clicks_click_id ON clicks(click_id);
CREATE INDEX idx_clicks_merchant_affiliate ON clicks(merchant_id, affiliate_id);
CREATE INDEX idx_orders_merchant_external ON orders(merchant_id, external_order_id);
CREATE INDEX idx_orders_click_id ON orders(click_id);
CREATE INDEX idx_commissions_order ON commissions(order_id);
CREATE INDEX idx_commissions_status ON commissions(status);
CREATE INDEX idx_ledger_affiliate ON commission_ledger(affiliate_id);
CREATE INDEX idx_webhook_events_idempotency ON webhook_events(idempotency_key);

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE commissions ENABLE ROW LEVEL SECURITY;
ALTER TABLE products ENABLE ROW LEVEL SECURITY;

CREATE POLICY merchant_orders_isolation_policy ON orders
    FOR ALL
    USING (
        merchant_id = NULLIF(current_setting('app.current_merchant_id', true), '')::UUID
        OR current_setting('app.is_super_admin', true) = 'true'
    )
    WITH CHECK (
        merchant_id = NULLIF(current_setting('app.current_merchant_id', true), '')::UUID
        OR current_setting('app.is_super_admin', true) = 'true'
    );

CREATE POLICY merchant_products_isolation_policy ON products
    FOR ALL
    USING (
        merchant_id = NULLIF(current_setting('app.current_merchant_id', true), '')::UUID
        OR current_setting('app.is_super_admin', true) = 'true'
    );

import crypto from 'crypto';
import Redis from 'ioredis';

const redis = new Redis(process.env.REDIS_URL || 'redis://localhost:6379');
const SIGNING_SECRET = process.env.ATTRIBUTION_SECRET || 'secret-system-key-360';

interface ClickContext {
  affiliateId: string;
  merchantId: string;
  campaignId?: string;
  ipAddress: string;
  userAgent: string;
}

export class ClickTrackerService {
  public static generateSecureClickId(affiliateId: string, merchantId: string): string {
    const rawData = `${affiliateId}:${merchantId}:${Date.now()}:${crypto.randomBytes(8).toString('hex')}`;
    const signature = crypto.createHmac('sha256', SIGNING_SECRET).update(rawData).digest('hex').substring(0, 16);
    return Buffer.from(`${rawData}:${signature}`).toString('base64url');
  }

  public async registerClick(context: ClickContext, targetUrl: string): Promise<{ redirectUrl: string; clickId: string }> {
    const clickId = ClickTrackerService.generateSecureClickId(context.affiliateId, context.merchantId);

    const ipThrottleKey = `throttle:click:${context.ipAddress}:${context.affiliateId}`;
    const isSpam = await redis.set(ipThrottleKey, '1', 'EX', 5, 'NX');

    if (!isSpam) {
      await redis.lpush('queue:suspicious_clicks', JSON.stringify({ ...context, clickId, reason: 'High-frequency IP burst' }));
    }

    const clickDataKey = `attribution:click:${clickId}`;
    await redis.set(
      clickDataKey,
      JSON.stringify({
        affiliateId: context.affiliateId,
        merchantId: context.merchantId,
        campaignId: context.campaignId,
        timestamp: Date.now(),
      }),
      'EX',
      60 * 60 * 24 * 30
    );

    const redirectUrlObj = new URL(targetUrl);
    redirectUrlObj.searchParams.set('aff_click_id', clickId);
    redirectUrlObj.searchParams.set('aff_id', context.affiliateId);

    return { redirectUrl: redirectUrlObj.toString(), clickId };
  }
}

(function () {
  const urlParams = new URLSearchParams(window.location.search);
  const clickId = urlParams.get('aff_click_id');
  const affId = urlParams.get('aff_id');

  if (clickId && affId) {
    const cookiePayload = JSON.stringify({ clickId, affId, t: Date.now() });
    const expiryDays = 30;
    const date = new Date();
    date.setTime(date.getTime() + expiryDays * 24 * 60 * 60 * 1000);
    
    document.cookie = `aff_attribution=${encodeURIComponent(cookiePayload)}; expires=${date.toUTCString()}; path=/; SameSite=Lax; Secure`;
  }
})();

import { Pool, PoolClient } from 'pg';

export interface OrderFinancialData {
  orderId: string;
  merchantId: string;
  affiliateId: string;
  clickId: string;
  subtotal: number;
  shipping: number;
  tax: number;
  total: number;
  commissionRate: number;
  holdPeriodDays: number;
}

export class CommissionEngine {
  constructor(private pool: Pool) {}

  public async calculateAndRecordPendingCommission(data: OrderFinancialData): Promise {
    const client: PoolClient = await this.pool.connect();

    try {
      await client.query('BEGIN');

      const commissionAmount = Number(((data.subtotal * data.commissionRate) / 100).toFixed(2));
      const holdUntilDate = new Date();
      holdUntilDate.setDate(holdUntilDate.getDate() + data.holdPeriodDays);

      const checkExisting = await client.query(
        'SELECT id FROM commissions WHERE order_id = $1 AND merchant_id = $2 FOR UPDATE',
        [data.orderId, data.merchantId]
      );

      if (checkExisting.rows.length > 0) {
        throw new Error(`Commission already exists for Order ${data.orderId}`);
      }

      const insertCommissionQuery = `
        INSERT INTO commissions (
          order_id, merchant_id, affiliate_id, rate_applied, amount, status, hold_until
        ) VALUES ($1, $2, $3, $4, $5, 'pending', $6)
        RETURNING id;
      `;
      const commissionRes = await client.query(insertCommissionQuery, [
        data.orderId,
        data.merchantId,
        data.affiliateId,
        data.commissionRate,
        commissionAmount,
        holdUntilDate,
      ]);
      const commissionId = commissionRes.rows[0].id;

      const insertLedgerQuery = `
        INSERT INTO commission_ledger (
          commission_id, affiliate_id, merchant_id, entry_type, amount, notes
        ) VALUES ($1, $2, $3, 'credit_pending', $4, $5);
      `;
      await client.query(insertLedgerQuery, [
        commissionId,
        data.affiliateId,
        data.merchantId,
        commissionAmount,
        `Pending commission from Order ${data.orderId}. Eligible base: ${data.subtotal}`,
      ]);

      await client.query(
        'UPDATE affiliates SET balance_pending = balance_pending + $1, updated_at = CURRENT_TIMESTAMP WHERE id = $2',
        [commissionAmount, data.affiliateId]
      );

      await client.query('COMMIT');
      return commissionId;
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }

  public async reverseCommission(commissionId: string, reason: string): Promise {
    const client = await this.pool.connect();

    try {
      await client.query('BEGIN');

      const commRes = await client.query(
        'SELECT id, affiliate_id, merchant_id, amount, status FROM commissions WHERE id = $1 FOR UPDATE',
        [commissionId]
      );

      if (commRes.rows.length === 0) {
        throw new Error('Commission record not found');
      }

      const commission = commRes.rows[0];

      if (commission.status === 'reversed') {
        throw new Error('Commission is already reversed');
      }

      await client.query(
        `INSERT INTO commission_ledger (commission_id, affiliate_id, merchant_id, entry_type, amount, notes)
         VALUES ($1, $2, $3, 'debit_reversal', $4, $5)`,
        [commission.id, commission.affiliate_id, commission.merchant_id, commission.amount, `Reversal Reason: ${reason}`]
      );

      await client.query(
        "UPDATE commissions SET status = 'reversed', updated_at = CURRENT_TIMESTAMP WHERE id = $1",
        [commission.id]
      );

      if (commission.status === 'pending') {
        await client.query(
          'UPDATE affiliates SET balance_pending = GREATEST(balance_pending - $1, 0) WHERE id = $2',
          [commission.amount, commission.affiliate_id]
        );
      } else if (commission.status === 'approved') {
        await client.query(
          'UPDATE affiliates SET balance_available = balance_available - $1 WHERE id = $2',
          [commission.amount, commission.affiliate_id]
        );
      }

      await client.query('COMMIT');
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }
}

Role: Senior Distributed Systems & Fintech Engineer.
Task: Write a production-ready Fastify (Node.js/TypeScript) route handler for:
POST /api/v1/merchants/:merchantId/webhooks/orders

Requirements:
1. Validate HMAC-SHA256 signature from 'X-Merchant-Signature' header using merchant's stored secret.
2. Implement strict idempotency check using 'X-Idempotency-Key' with Redis.
3. If valid, parse order payload, extract 'aff_click_id' from metadata, and calculate commission.
4. Use PostgreSQL transaction for all balance updates.
5. Log event in 'webhook_events' table.
6. Return HTTP 200 within 200ms. Handle queueing for heavy jobs.
7. Avoid any pseudo-code; write full error handling and type-safety.

// Request Body
{
  "affiliate_code": "AFF1001",
  "merchant_slug": "tech-store-eg",
  "target_url": "https://store.example.com/product/laptop-pro"
}

// Response (200 OK)
{
  "success": true,
  "data": {
    "click_id": "YWZmMTAwMTpjMTJiYTM3YToxNzM2MTI0NDAwOmFiY2RlZjEyMzQ1Njc4OTA6YjM0ZjJhN2U",
    "redirect_url": "https://store.example.com/product/laptop-pro?aff_click_id=YWZmMTAwMTpjMTJiYTM3YToxNzM2MTI0NDAwOmFiY2RlZjEyMzQ1Njc4OTA6YjM0ZjJhN2U&aff_id=c12ba37a-514d-49d7-84ec-86bbd79b884d"
  }
}

// Response (200 OK)
{
  "success": true,
  "data": {
    "currency": "EGP",
    "balance_available": 4500.00,
    "balance_pending": 1250.00,
    "total_withdrawn": 18200.00,
    "active_referral_links": 14,
    "last_payout_at": "2026-02-28T14:30:00Z"
  }
}

import { FastifyRequest, FastifyReply } from 'fastify';
import crypto from 'crypto';
import Redis from 'ioredis';
import { Pool } from 'pg';

const redis = new Redis(process.env.REDIS_URL || 'redis://localhost:6379');
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

export async function handleStoreWebhook(request: FastifyRequest, reply: FastifyReply) {
  const signature = request.headers['x-platform-signature'] as string;
  const timestamp = request.headers['x-platform-timestamp'] as string;
  const idempotencyKey = request.headers['x-idempotency-key'] as string;
  const merchantId = (request.params as { merchantId: string }).merchantId;

  if (!signature || !timestamp || !idempotencyKey) {
    return reply.status(400).send({ error: 'Missing security headers' });
  }

  const currentTime = Math.floor(Date.now() / 1000);
  if (Math.abs(currentTime - parseInt(timestamp, 10)) > 300) {
    return reply.status(400).send({ error: 'Request timestamp expired' });
  }

  const merchantRes = await pool.query('SELECT webhook_secret FROM merchants WHERE id = $1', [merchantId]);
  if (merchantRes.rows.length === 0) {
    return reply.status(404).send({ error: 'Merchant not found' });
  }
  const secret = merchantRes.rows[0].webhook_secret;

  const rawBody = JSON.stringify(request.body);
  const expectedSignature = crypto
    .createHmac('sha256', secret)
    .update(`${timestamp}.${rawBody}`)
    .digest('hex');

  const isValid = crypto.timingSafeEqual(Buffer.from(signature), Buffer.from(expectedSignature));
  if (!isValid) {
    return reply.status(401).send({ error: 'Invalid HMAC signature' });
  }

  const hasSeen = await redis.set(`idempotency:${idempotencyKey}`, 'processed', 'EX', 86400, 'NX');
  if (!hasSeen) {
    return reply.status(200).send({ success: true, message: 'Event already processed' });
  }

  await pool.query(
    `INSERT INTO webhook_events (merchant_id, event_id, event_type, idempotency_key, payload, status)
     VALUES ($1, $2, $3, $4, $5, 'received')`,
    [merchantId, idempotencyKey, (request.body as any).event, idempotencyKey, request.body]
  );

  await redis.lpush('queue:order_processing', JSON.stringify({ merchantId, payload: request.body }));

  return reply.status(200).send({ success: true, message: 'Webhook received successfully' });
}

<?php
if (!defined('ABSPATH')) exit;

class Affiegy_WC_Sync {
    private static $api_endpoint = 'https://api.affiegy-platform.com/api/v1/merchants/MERCHANT_UUID_HERE/webhooks/orders';
    private static $webhook_secret = 'SECURE_MERCHANT_SECRET_KEY_HERE';

    public static function init() {
        add_action('woocommerce_order_status_completed', [__CLASS__, 'dispatch_order_completed']);
        add_action('woocommerce_order_status_refunded', [__CLASS__, 'dispatch_order_refunded']);
        add_action('woocommerce_checkout_order_processed', [__CLASS__, 'attach_cookie_to_order_meta'], 10, 1);
    }

    public static function attach_cookie_to_order_meta($order_id) {
        if (isset($_COOKIE['aff_attribution'])) {
            $attribution_data = json_decode(stripslashes($_COOKIE['aff_attribution']), true);
            if (!empty($attribution_data['clickId'])) {
                update_post_meta($order_id, '_aff_click_id', sanitize_text_field($attribution_data['clickId']));
                update_post_meta($order_id, '_aff_affiliate_id', sanitize_text_field($attribution_data['affId']));
            }
        }
    }

    public static function dispatch_order_completed($order_id) {
        $order = wc_get_order($order_id);
        if (!$order) return;

        $click_id = get_post_meta($order_id, '_aff_click_id', true);
        if (empty($click_id)) return;

        $payload = [
            'event' => 'order.completed',
            'order_id' => (string)$order->get_id(),
            'click_id' => $click_id,
            'affiliate_id' => get_post_meta($order_id, '_aff_affiliate_id', true),
            'currency' => $order->get_currency(),
            'subtotal' => (float)$order->get_subtotal(),
            'tax' => (float)$order->get_total_tax(),
            'shipping' => (float)$order->get_shipping_total(),
            'total' => (float)$order->get_total(),
            'customer_phone' => $order->get_billing_phone(),
            'timestamp' => time()
        ];

        self::send_signed_payload($payload);
    }

    public static function dispatch_order_refunded($order_id) {
        $payload = [
            'event' => 'order.refunded',
            'order_id' => (string)$order_id,
            'timestamp' => time()
        ];
        self::send_signed_payload($payload);
    }

    private static function send_signed_payload($payload) {
        $json_payload = wp_json_encode($payload);
        $timestamp = time();
        $signature = hash_hmac('sha256', $timestamp . '.' . $json_payload, self::$webhook_secret);
        $idempotency_key = 'wc_' . $payload['order_id'] . '_' . $payload['event'] . '_' . $timestamp;

        wp_remote_post(self::$api_endpoint, [
            'headers' => [
                'Content-Type' => 'application/json',
                'X-Platform-Signature' => $signature,
                'X-Platform-Timestamp' => (string)$timestamp,
                'X-Idempotency-Key' => $idempotency_key,
            ],
            'body' => $json_payload,
            'timeout' => 15,
            'blocking' => false
        ]);
    }
}

Affiegy_WC_Sync::init();

import axios, { AxiosInstance } from 'axios';
import { Pool } from 'pg';

export interface WhatsAppNotificationPayload {
  recipientPhone: string;
  templateMessage: string;
  metadata?: Record;
}

export class WhatsAppNotificationService {
  private httpClient: AxiosInstance;
  private apiToken: string;
  private instanceId: string;
  private baseUrl: string;

  constructor(private pool: Pool) {
    this.apiToken = process.env.WHATS360_API_TOKEN || '';
    this.instanceId = process.env.WHATS360_INSTANCE_ID || '';
    this.baseUrl = process.env.WHATS360_BASE_URL || 'https://whats360.live/api/v1';

    this.httpClient = axios.create({
      baseURL: this.baseUrl,
      timeout: 10000,
    });
  }

  public async sendTextMessage(phone: string, message: string): Promise<{ success: boolean; messageId?: string; error?: string }> {
    try {
      const cleanPhone = phone.replace(/[^0-9]/g, '');
      const jid = `${cleanPhone}@s.whatsapp.net`;

      const response = await this.httpClient.get('/send-text', {
        params: {
          token: this.apiToken,
          instance_id: this.instanceId,
          jid: jid,
          msg: message,
        },
      });

      if (response.data && response.data.success) {
        await this.logNotification(cleanPhone, message, 'sent', response.data.response?.key?.id);
        return { success: true, messageId: response.data.response?.key?.id };
      } else {
        await this.logNotification(cleanPhone, message, 'failed', null);
        return { success: false, error: response.data?.error || 'Unknown API failure' };
      }
    } catch (error: any) {
      const errorMessage = error.response?.data?.error || error.message;
      await this.logNotification(phone, message, 'failed', null);

      if (error.response?.status === 463) {
        console.warn(`[WhatsApp API 463] Number ${phone} is not opened for chat or requires prior interaction.`);
      }

      return { success: false, error: errorMessage };
    }
  }

  public async notifyMerchantNewOrder(merchantPhone: string, merchantName: string, orderId: string, total: number, currency: string) {
    const message = `مرحباً يا ${merchantName} 🌟\nتم تسجيل طلب شراء جديد برقم (#${orderId}) في متجرك.\nإجمالي الطلب: ${total} ${currency}.\nيرجى مراجعة وتجهيز الشحنة عبر لوحة التحكم الخاصة بك.`;
    return this.sendTextMessage(merchantPhone, message);
  }

  public async notifyAffiliateCommissionApproved(affiliatePhone: string, affiliateName: string, amount: number, currency: string, newBalance: number) {
    const message = `أهلاً ${affiliateName} 🎉\nبشرى سارة! تم اعتماد عمولة بيع جديدة لك بقيمة: ${amount} ${currency}.\nرصيدك القابل للسحب الآن هو: ${newBalance} ${currency}.\nواصل الترويج عبر روابطك المخصصة!`;
    return this.sendTextMessage(affiliatePhone, message);
  }

  private async logNotification(phone: string, content: string, status: string, externalId: string | null) {
    await this.pool.query(
      `INSERT INTO notifications (channel, destination, message_content, external_message_id, delivery_status)
       VALUES ('whatsapp', $1, $2, $3, $4)`,
      [phone, content, externalId, status]
    );
  }
}

import { Worker, Job } from 'bullmq';
import { CommissionEngine } from '../finance/commission-engine';
import { WhatsAppNotificationService } from '../notifications/whatsapp-service';
import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const commissionEngine = new CommissionEngine(pool);
const whatsappService = new WhatsAppNotificationService(pool);

export const orderWorker = new Worker(
  'order-processing-queue',
  async (job: Job) => {
    const { merchantId, payload } = job.data;

    if (payload.event === 'order.completed') {
      const commissionId = await commissionEngine.calculateAndRecordPendingCommission({
        orderId: payload.order_id,
        merchantId: merchantId,
        affiliateId: payload.affiliate_id,
        clickId: payload.click_id,
        subtotal: payload.subtotal,
        shipping: payload.shipping,
        tax: payload.tax,
        total: payload.total,
        commissionRate: 10,
        holdPeriodDays: 14,
      });

      await whatsappService.notifyMerchantNewOrder(
        payload.merchant_phone || '201000000000',
        'التاجر',
        payload.order_id,
        payload.total,
        payload.currency
      );

      await whatsappService.sendTextMessage(
        payload.affiliate_phone || '201111111111',
        `أهلاً بك! تم إسناد بيع جديد لطلبك رقم #${payload.order_id}. عمولتك المعلقة: ${(payload.subtotal * 0.1).toFixed(2)} ${payload.currency}.`
      );

      return { status: 'completed', commissionId };
    }

    if (payload.event === 'order.refunded') {
      const commRes = await pool.query('SELECT id FROM commissions WHERE order_id = $1 AND merchant_id = $2', [
        payload.order_id,
        merchantId,
      ]);
      if (commRes.rows.length > 0) {
        await commissionEngine.reverseCommission(commRes.rows[0].id, 'Customer Refund via Store Webhook');
      }
      return { status: 'reversed' };
    }
  },
  {
    connection: { host: 'localhost', port: 6379 },
    concurrency: 5,
    limiter: { max: 10, duration: 1000 },
    attempts: 5,
    backoff: {
      type: 'exponential',
      delay: 3000,
    },
  }
);

orderWorker.on('failed', (job, err) => {
  console.error(`[Job ${job?.id}] Failed with error: ${err.message}.`);
});

import request from 'supertest';
import { app } from '../../src/app';
import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.TEST_DATABASE_URL });

describe('End-to-End Affiliate Multi-Vendor Lifecycle', () => {
  let merchantId: string;
  let affiliateId: string;
  let clickId: string;

  beforeAll(async () => {
    const mRes = await pool.query(
      "INSERT INTO merchants (business_name, slug, webhook_secret, api_key_hash) VALUES ('Test Store', 'test-store', 'test_secret_123', 'hash') RETURNING id"
    );
    merchantId = mRes.rows[0].id;

    const uRes = await pool.query(
      "INSERT INTO users (email, password_hash, full_name, role) VALUES ('aff@test.com', 'hash', 'Test Affiliate', 'affiliate') RETURNING id"
    );
    const aRes = await pool.query(
      "INSERT INTO affiliates (user_id, referral_code) VALUES ($1, 'TESTCODE') RETURNING id",
      [uRes.rows[0].id]
    );
    affiliateId = aRes.rows[0].id;
  });

  afterAll(async () => {
    await pool.query('DELETE FROM merchants WHERE id = $1', [merchantId]);
    await pool.end();
  });

  it('1. يجب أن يسجل النقرة وينشئ معرّف Click ID مشفر بنجاح', async () => {
    const res = await request(app.server)
      .post('/api/v1/tracking/clicks')
      .send({
        affiliate_code: 'TESTCODE',
        merchant_slug: 'test-store',
        target_url: 'https://teststore.com/item/1',
      });

    expect(res.status).toBe(200);
    expect(res.body.data).toHaveProperty('clickId');
    clickId = res.body.data.clickId;
  });

  it('2. يجب معالجة الـ Webhook واحتساب العمولة بدقة بدفتر الأستاذ', async () => {
    const orderPayload = {
      event: 'order.completed',
      order_id: 'ORDER_9999',
      click_id: clickId,
      affiliate_id: affiliateId,
      subtotal: 1000.0,
      shipping: 50.0,
      tax: 50.0,
      total: 1100.0,
      currency: 'EGP',
    };

    const timestamp = Math.floor(Date.now() / 1000).toString();
    const signature = require('crypto')
      .createHmac('sha256', 'test_secret_123')
      .update(`${timestamp}.${JSON.stringify(orderPayload)}`)
      .digest('hex');

    const res = await request(app.server)
      .post(`/api/v1/merchants/${merchantId}/webhooks/orders`)
      .set('X-Platform-Signature', signature)
      .set('X-Platform-Timestamp', timestamp)
      .set('X-Idempotency-Key', 'unique_test_order_9999')
      .send(orderPayload);

    expect(res.status).toBe(200);

    const affCheck = await pool.query('SELECT balance_pending FROM affiliates WHERE id = $1', [affiliateId]);
    expect(Number(affCheck.rows[0].balance_pending)).toBe(100.0);
  });

  it('3. يجب منع تكرار معالجة نفس الـ Webhook بناءً على مفتاح Idempotency', async () => {
    const orderPayload = { event: 'order.completed', order_id: 'ORDER_9999' };
    const timestamp = Math.floor(Date.now() / 1000).toString();
    const signature = require('crypto')
      .createHmac('sha256', 'test_secret_123')
      .update(`${timestamp}.${JSON.stringify(orderPayload)}`)
      .digest('hex');

    const res = await request(app.server)
      .post(`/api/v1/merchants/${merchantId}/webhooks/orders`)
      .set('X-Platform-Signature', signature)
      .set('X-Platform-Timestamp', timestamp)
      .set('X-Idempotency-Key', 'unique_test_order_9999')
      .send(orderPayload);

    expect(res.status).toBe(200);
    expect(res.body.message).toContain('already processed');

    const affCheck = await pool.query('SELECT balance_pending FROM affiliates WHERE id = $1', [affiliateId]);
    expect(Number(affCheck.rows[0].balance_pending)).toBe(100.0);
  });
});

• الأيام (1 – 3): تجهيز بيئة PostgreSQL على سحابة آمنة، تشغيل ملفات الـ DDL، وضبط إعدادات الـ RLS.
• الأيام (4 – 7): برمجة محرك التتبع باستخدام Vibe Coding، ونشر إضافة WooCommerce في متاجر التجار المستهدفين.
• الأيام (8 – 10): دمج حساب واتساب عبر Whats360 لربط جلسات الرسائل واختبار استقبال خطافات الويب للأوردرات.
• الأيام (11 – 12): تشغيل طوابير Redis واختبار فترات الإسناد وتعديلات العملة.
• الأيام (13 – 14): بدء الاختبارات الحية مع 3 تجار و 20 مسوقاً للتحقق من وصول الإشعارات وتوازن الدفاتر المحاسبية.

هل تحتاج إلى بنية مخصصة لشبكتك بالكامل؟

فريق المهندسين في Beincode جاهز لبناء المعمارية المتكاملة مع الربط اللحظي بأنظمة الدفع والواتساب.

اطلب عرض سعر لتطوير المنصة مخصصة

الكلمات المفتاحية المستهدفة (Keywords Index)

بناء نظام أفلييت متعدد التجار, نظام تسويق بالعمولة متعدد التجار, إنشاء منصة أفلييت, برمجة نظام تسويق بالعمولة, Vibe Coding, Multi-Tenant Architecture, Affiliate Tracking System, Affiliate Commission Management, WhatsApp API Integration, Webhook Integration, REST API Development, WooCommerce API Integration.

الأسئلة الشائعة التي يجيب عنها المقال (FAQ Schema Ready Section)

1. ما هو نظام الأفلييت متعدد التجار وكيف يختلف عن المتجر الفردي والماركت بليس؟
2. كيف تضمن معمارية Multi-Tenant عزل بيانات التجار والمسوقين عبر PostgreSQL RLS؟
3. ما هو الدور الهندسي لأسلوب Vibe Coding في تسريع برمجة محرك التتبع والإسناد؟
4. كيف يتم احتساب العمولات بدقة واستبعاد الضرائب وتكاليف الشحن بدفتر الأستاذ المالي المزدوج؟
5. ما هي الآلية البرمجية لتأمين خطافات الويب (Webhooks) ومنع هجمات الإعادة وتكرار العمولات (Idempotency)؟
6. كيف يتم دمج WooCommerce برمجياً لالتقاط معرّف الإحالة وإرسال الطلبات المكتملة والمسترجعة؟
7. كيف يتم إرسال إشعارات فورية للتجار والمسوقين عبر WhatsApp API دون التعرض للحظر أو الخطأ 463؟
8. متى يجب الاعتماد على التطوير الذاتي ومتى يجب التوجه نحو التطوير المخصص عبر Beincode؟

🛠️ ملحق التطبيق الهندسي المتقدم: مسارات API، تكاملات Webhooks، وأتمتة المراسلات

يقدم هذا الملحق الفني مواصفات برمجية تفصيلية مكملة لما ورد في الدليل الرئيسي؛ يركز على بنية واجهات برمجة التطبيقات (REST Endpoints)، وتنسيقات الأحداث المشفرة لخطافات الويب (Webhook Payloads)، وطرق ضبط مصادقة JWT، وتدفق الإشعارات الآمن عبر بوابات واتساب لحماية المنظومة من ثغرات الحقن، والتكرار المالي، والحظر التشغيلي.

نموذج طلب احتساب العمولة (Request Body)

{
  "merchant_id": "c12ba37a-514d-49d7-84ec-86bbd79b884d",
  "order_id": "ORD-2026-9988",
  "click_id": "YWZmMTAwMTpjMTJiYTM3YToxNzM2MTI0NDAwOmFiY2RlZjEyMzQ1Njc4OTA6YjM0ZjJhN2U",
  "subtotal_amount": 1000.00,
  "shipping_amount": 50.00,
  "tax_amount": 50.00,
  "total_amount": 1100.00,
  "currency": "EGP",
  "items": [
    {
      "product_id": "prd-101",
      "quantity": 1,
      "unit_price": 1000.00,
      "is_commissionable": true
    }
  ]
}

نموذج استجابة احتساب العمولة (Response Body – 201 Created)

{
  "success": true,
  "status_code": 201,
  "data": {
    "commission_id": "f47ac10b-58cc-4372-a567-0e02b2c3d479",
    "order_id": "ORD-2026-9988",
    "affiliate_id": "a98cb21c-43bb-4211-b112-9f01a1b2c3d4",
    "commissionable_base": 1000.00,
    "applied_rate_percentage": 10.00,
    "earned_commission": 100.00,
    "currency": "EGP",
    "status": "pending",
    "hold_until": "2026-03-20T12:00:00Z",
    "ledger_entry_id": "e31ba20a-11aa-4222-c333-8b01c2d3e4f5"
  }
}

نموذج الاستجابة عند حدوث خطأ تحقق (422 Unprocessable Entity)

{
  "success": false,
  "status_code": 422,
  "error": "Validation failed",
  "details": [
    {
      "field": "subtotal_amount",
      "message": "subtotal_amount must be greater than zero and equal to the sum of commissionable items."
    }
  ]
}

{
  "event": "order.completed",
  "event_id": "evt_ord_9988_comp",
  "timestamp": 1772884800,
  "tenant_id": "c12ba37a-514d-49d7-84ec-86bbd79b884d",
  "data": {
    "order_id": "ORD-2026-9988",
    "external_order_id": "12345",
    "click_id": "YWZmMTAwMTpjMTJiYTM3YToxNzM2MTI0NDAwOmFiY2RlZjEyMzQ1Njc4OTA6YjM0ZjJhN2U",
    "affiliate_id": "a98cb21c-43bb-4211-b112-9f01a1b2c3d4",
    "customer": {
      "first_name": "أحمد",
      "phone": "+201234567890"
    },
    "totals": {
      "subtotal": 1000.00,
      "shipping": 50.00,
      "tax": 50.00,
      "total": 1100.00,
      "currency": "EGP"
    }
  }
}

كود توثيق JWT وعزل المستأجر البرمجي (Node.js Middleware)

import { FastifyRequest, FastifyReply } from 'fastify';
import jwt from 'jsonwebtoken';

interface DecodedToken {
  userId: string;
  merchantId?: string;
  role: 'super_admin' | 'merchant_admin' | 'merchant_staff' | 'affiliate';
}

export async function verifyAuthAndTenant(request: FastifyRequest, reply: FastifyReply) {
  const authHeader = request.headers.authorization;
  if (!authHeader || !authHeader.startsWith('Bearer ')) {
    return reply.status(401).send({ error: 'Missing or malformed Authorization header' });
  }

  const token = authHeader.split(' ')[1];
  try {
    const decoded = jwt.verify(token, process.env.JWT_SECRET || 'SYS_SECRET') as DecodedToken;
    (request as any).user = decoded;

    // تثبيت هوية التاجر لضمان عدم تمرير استعلامات عابرة للمستأجرين
    if (decoded.role === 'merchant_admin' || decoded.role === 'merchant_staff') {
      if (!decoded.merchantId) {
        return reply.status(403).send({ error: 'Merchant context required for this role' });
      }
      (request as any).merchantId = decoded.merchantId;
    }
  } catch (err) {
    return reply.status(401).send({ error: 'Invalid or expired token' });
  }
}

مصفوفة مقارنة بوابات الإرسال (WhatsApp Channels Comparison)

أسئلة بحث تقنية إضافية (Extended Technical FAQ)

استكشف المزيد في هذا التخصص عبر محرك البحث الداخلي

• أفضل ممارسات تصميم قواعد البيانات متعددة المستأجرين Multi-Tenant
• دليل تأمين خطافات الويب والتحقق من التوقيع الرقمي HMAC
• معالجة المهام غير المتزامنة وإعادة المحاولة عبر Redis وBullMQ
• طرق ربط منصة Whats360 مع أنظمة إدارة التجارة الإلكترونية