cbd420/REFERRAL_POINTS_README.md

Referral Points System

This document describes the referral points system implementation for the CBD420 platform.

Overview

The referral points system allows:

1. Earning Points: Referrers earn points when their referred users make purchases
2. Spending Points: Buyers can use their referral points to discount purchases
3. Configurable Rates: Both earning and redemption rates are configurable via the referral_settings table

Database Changes

New Columns

1. buyers table: Added referral_points column (decimal(10,2), default 0.00) to track current point balance
2. sales table:
   • Added points_used column (decimal(10,2), default 0.00) to track points used in the sale
   • Added price_amount column (decimal(10,2)) to track actual amount paid
   • Added price_currency column (varchar(10), default 'chf') to track currency
3. pending_orders table: Added points_used column (decimal(10,2), default 0.00) to track points used in pending orders

New Tables

1. referral_point_transactions: Tracks all point transactions (earned/spent)

   • Records when points are earned or spent
   • Links to sales and pending orders
   • Includes description for audit trail

2. referral_settings: Stores configurable system settings

   • points_per_chf: Points earned per 1 CHF purchase (default: 10)
   • points_to_chf: Points required to redeem 1 CHF discount (default: 100)

Stored Procedures

1. award_referral_points(p_sale_id INT):

   • Awards referral points to the referrer when a sale is completed
   • Calculates points based on purchase amount and points_per_chf setting
   • Should be called after a sale is created

2. spend_referral_points(p_buyer_id, p_points_to_spend, p_pending_order_id, p_sale_id, OUT p_success):

   • Deducts points from buyer's balance
   • Records the transaction
   • Returns 1 if successful, 0 if insufficient points

Configuration

The system uses two configurable multipliers stored in referral_settings:

• points_per_chf: Number of points earned per 1 CHF purchase (default: 10)
  • Example: If set to 10, a 5 CHF purchase earns 50 points
• points_to_chf: Number of points required for 1 CHF discount (default: 100)
  • Example: If set to 100, 500 points = 5 CHF discount

To update these values:

UPDATE referral_settings SET setting_value = '20' WHERE setting_key = 'points_per_chf';
UPDATE referral_settings SET setting_value = '50' WHERE setting_key = 'points_to_chf';

Usage Examples

Awarding Points After Sale Completion

When a sale is created (e.g., in your IPN/webhook handler), call:

CALL award_referral_points(@sale_id);

This will:

1. Check if the buyer was referred by someone
2. Calculate points based on purchase amount
3. Add points to referrer's balance
4. Record the transaction

Spending Points During Purchase

Before creating a pending order, check available points and calculate discount:

-- Get buyer's available points
SELECT referral_points FROM buyers WHERE id = @buyer_id;

-- Calculate maximum discount (points / points_to_chf)
SELECT 
  b.referral_points,
  CAST(rs.setting_value AS DECIMAL(10,2)) as points_to_chf,
  b.referral_points / CAST(rs.setting_value AS DECIMAL(10,2)) as max_discount_chf
FROM buyers b
CROSS JOIN referral_settings rs
WHERE b.id = @buyer_id AND rs.setting_key = 'points_to_chf';

When creating a pending order with points:

SET @points_to_spend = 500; -- User wants to spend 500 points
SET @success = 0;

CALL spend_referral_points(@buyer_id, @points_to_spend, @pending_order_id, NULL, @success);

IF @success = 1 THEN
  -- Points deducted successfully, update pending_order with points_used
  UPDATE pending_orders SET points_used = @points_to_spend WHERE id = @pending_order_id;
ELSE
  -- Insufficient points, handle error
  SELECT 'Insufficient referral points' AS error;
END IF;

Calculating Final Price After Points Discount

-- Calculate discount amount from points
SELECT 
  @original_price as original_price,
  @points_to_spend as points_used,
  CAST((SELECT setting_value FROM referral_settings WHERE setting_key = 'points_to_chf') AS DECIMAL(10,2)) as points_to_chf,
  (@points_to_spend / CAST((SELECT setting_value FROM referral_settings WHERE setting_key = 'points_to_chf') AS DECIMAL(10,2))) as discount_amount,
  (@original_price - (@points_to_spend / CAST((SELECT setting_value FROM referral_settings WHERE setting_key = 'points_to_chf') AS DECIMAL(10,2)))) as final_price;

Implementation Notes

1. Price Tracking: The sales table now includes price_amount to accurately track the amount paid. When converting a pending_order to a sale, copy the price_amount (after points discount) to the sale.

2. Points Calculation: Points are awarded based on the actual amount paid (after any points discount), not the original price.

3. Transaction History: All point transactions are recorded in referral_point_transactions for audit and reporting purposes.

4. Currency: Currently assumes CHF as the base currency. If your system uses multiple currencies, you may need to adjust the calculation logic.

5. Application Integration: You'll need to:

   • Call award_referral_points() after creating a sale (in your IPN/webhook handler)
   • Handle point spending in your payment/invoice creation flow
   • Display available points to users in the UI
   • Allow users to select how many points to use during checkout

Migration

For existing databases, run the referral_points_migration.sql file to add all the necessary tables, columns, and procedures.

For new installations, the updated cbd420.sql includes all referral points functionality.