# 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:
```sql
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:

```sql
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:

```sql
-- 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:

```sql
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

```sql
-- 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.