# Coupon & Referral System Implementation

## Overview
This document outlines the complete implementation of **discount coupons** and **referral program** features for the Sirus CRM client portal.

---

## 1. Database Schema Updates

### New Tables Created

#### `discount_coupons`
Stores discount coupon information with usage tracking and validity periods.

```sql
CREATE TABLE IF NOT EXISTS discount_coupons (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(100) NOT NULL UNIQUE,
  description VARCHAR(255) DEFAULT '',
  discount_type ENUM('percent','fixed') NOT NULL DEFAULT 'fixed',
  discount_percent DECIMAL(5,2) DEFAULT 0,
  discount_amount DECIMAL(12,2) DEFAULT 0,
  usage_limit INT DEFAULT 1,
  used_count INT DEFAULT 0,
  valid_from DATE DEFAULT NULL,
  valid_until DATE DEFAULT NULL,
  is_active TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

**Fields:**
- `code`: Unique coupon identifier (e.g., "SUMMER20", "NEWYEAR50")
- `discount_type`: Either 'percent' or 'fixed' amount
- `discount_percent`: Percentage discount (5 to 100)
- `discount_amount`: Fixed amount discount in rupees
- `usage_limit`: Maximum times coupon can be used
- `used_count`: Track current usage
- `valid_from` / `valid_until`: Date range for coupon validity
- `is_active`: Enable/disable coupon instantly

---

#### `referrals`
Tracks referrer-referee relationships and rewards.

```sql
CREATE TABLE IF NOT EXISTS referrals (
  id INT AUTO_INCREMENT PRIMARY KEY,
  referrer_id INT NOT NULL,
  referee_id INT NOT NULL,
  reward_earned DECIMAL(12,2) DEFAULT 0,
  status ENUM('pending','completed') DEFAULT 'pending',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (referrer_id) REFERENCES clients(id) ON DELETE CASCADE,
  FOREIGN KEY (referee_id) REFERENCES clients(id) ON DELETE CASCADE
);
```

**Fields:**
- `referrer_id`: Client who referred (bonus earner)
- `referee_id`: New client from referral
- `reward_earned`: 10% of referee's first purchase (to be set during purchase)
- `status`: Track if referral bonus is completed

---

#### `client_services` (Enhanced)
Added coupon and discount tracking to service purchases.

**New Columns:**
- `coupon_code VARCHAR(100)` – Applied coupon code
- `discount_amount DECIMAL(12,2)` – Final discount applied
- `total_amount DECIMAL(12,2)` – Amount after discount
- `razorpay_order_id VARCHAR(255)` – Payment gateway order ID

---

#### `clients` (Schema Updated)
Added referral tracking fields to client profile.

**New Columns:**
```sql
password VARCHAR(255) DEFAULT NULL           -- Client login password
referral_code VARCHAR(50) DEFAULT NULL       -- Unique referral code (e.g., REF3A7B2F)
referred_by INT DEFAULT NULL                 -- Client ID who referred this client
referral_bonus DECIMAL(12,2) DEFAULT 0       -- Total bonus earned from referrals
```

---

## 2. Client Signup Flow (With Referral Support)

### File: `public_html/client-signup.php`

**New Features:**
- Accept referral code from URL parameter: `?ref=REF3A7B2F`
- Generate unique referral code for new client
- Create referral record linking referrer to new client
- Update referred_by field

**Implementation:**

```php
// Extract referral code from URL
$referralCode = trim($_GET['ref'] ?? '');

// After successful client creation:
$clientId = $pdo->lastInsertId();

// Generate unique referral code
$newReferralCode = 'REF' . strtoupper(substr(md5($email . $clientId . time()), 0, 6));
$stmt = $pdo->prepare('UPDATE clients SET referral_code = :referral_code WHERE id = :id');
$stmt->execute([':referral_code' => $newReferralCode, ':id' => $clientId]);

// If came via referral link, create referral record
if ($referralCode) {
    $referrerStmt = $pdo->prepare('SELECT id FROM clients WHERE referral_code = :referral_code');
    $referrerStmt->execute([':referral_code' => $referralCode]);
    $referrer = $referrerStmt->fetch();
    
    if ($referrer) {
        // Create referral record (status='pending' until first purchase complete)
        $stmt = $pdo->prepare('INSERT INTO referrals (referrer_id, referee_id, reward_earned, status, created_at) VALUES (:referrer_id, :referee_id, 0, \'pending\', NOW())');
        $stmt->execute([
            ':referrer_id' => $referrer['id'],
            ':referee_id' => $clientId
        ]);
        
        // Update referred_by field
        $stmt = $pdo->prepare('UPDATE clients SET referred_by = :referrer_id WHERE id = :id');
        $stmt->execute([':referrer_id' => $referrer['id'], ':id' => $clientId]);
    }
}
```

---

## 3. Coupon API & Validation

### File: `public_html/api/coupons.php`

**Endpoint:** `GET /api/coupons.php?code=SUMMER20&price=5000`

**Validation Rules:**
1. Coupon code must exist and be active
2. Must be within valid_from and valid_until date range (if set)
3. used_count must not exceed usage_limit
4. Calculate discount based on type:
   - **Percent**: `discount = (price × discount_percent) / 100`
   - **Fixed**: `discount = min(discount_amount, price)`

**Response:**
```json
{
  "success": true,
  "discount": 1000,
  "coupon": {
    "id": 5,
    "code": "SUMMER20",
    "discount_type": "percent",
    "discount_percent": 20,
    "discount_amount": 0,
    "valid_from": "2024-06-01",
    "valid_until": "2024-06-30"
  }
}
```

**Error Response:**
```json
{
  "success": false,
  "message": "Coupon is invalid, expired, or has reached its usage limit."
}
```

---

## 4. Service Purchase with Coupon Discount

### File: `public_html/api/client-services.php`

**POST Endpoint:** `/api/client-services.php`

**Request Body:**
```json
{
  "client_id": 123,
  "service_id": 5,
  "coupon": "SUMMER20"
}
```

**Logic:**
1. Fetch service base_price
2. If coupon provided:
   - Validate via `coupons.php`
   - Calculate discount
   - Increment `used_count` in discount_coupons
   - Check referral bonus if new client
3. Calculate final amount: `total = base_price - discount`
4. Create record in client_services with:
   - coupon_code
   - discount_amount
   - total_amount
   - razorpay_order_id
5. Log activity

**Response:**
```json
{
  "success": true,
  "total_amount": 4000,
  "discount": 1000,
  "razorpay_order_id": "order_67a8f9b2c3"
}
```

---

## 5. Referral Tracking API

### File: `public_html/api/referrals.php`

**Endpoint:** `GET /api/referrals.php?client_id=123`

**Returns:**
- Referral code for client
- Total earnings from completed referrals
- Count of referrals made

**Response:**
```json
{
  "code": "REF3A7B2F",
  "earnings": 5500,
  "count": 3
}
```

**Calculation:**
```sql
-- Get referral code
SELECT referral_code FROM clients WHERE id = :client_id

-- Calculate earnings
SELECT SUM(reward_earned) as earnings FROM referrals 
WHERE referrer_id = :client_id AND status = 'completed'

-- Count referrals
SELECT COUNT(*) as count FROM referrals 
WHERE referrer_id = :client_id
```

---

## 6. Client Portal Integration

### File: `public_html/client-portal.php`

**UI Components:**

1. **Referrals Section:**
   ```html
   <div class="rounded-3xl bg-white p-6 shadow-sm">
     <h2 class="text-xl font-semibold mb-4">Referrals</h2>
     <p>Your referral code: <strong>{{ referralCode }}</strong></p>
     <p>Share this link: 
       <a :href="'https://siruscrm.com/signup?ref=' + referralCode">
         https://siruscrm.com/signup?ref={{ referralCode }}
       </a>
     </p>
     <p>Earnings: ₹{{ referralEarnings }}</p>
   </div>
   ```

2. **Coupon Input & Discount Display:**
   ```html
   <input v-model="couponCode" placeholder="Coupon code" class="mt-2 w-full...">
   <p v-if="couponMessage" :class="couponValid ? 'text-green-600' : 'text-red-600'">
     {{ couponMessage }}
   </p>
   <button @click="buyService(service)">Buy Now</button>
   ```

**Vue Methods:**

```javascript
// Validate and apply coupon
async applyCoupon(code, price) {
  const response = await fetch(`api/coupons.php?code=${encodeURIComponent(code)}&price=${price}`);
  const result = await response.json();
  
  if (!result.success) {
    this.couponMessage = result.message || 'Invalid coupon code.';
    this.couponValid = false;
    return { valid: false, discount: 0 };
  }
  
  this.couponDiscount = result.discount || 0;
  this.couponValid = true;
  this.couponMessage = `Coupon applied: ₹${this.couponDiscount} off.`;
  return { valid: true, discount: this.couponDiscount };
}

// Purchase service with coupon
async buyService(service) {
  let price = service.base_price;
  let discount = 0;

  if (this.couponCode) {
    const coupon = await this.applyCoupon(this.couponCode, price);
    if (!coupon.valid) {
      alert(coupon.message || 'Invalid or expired coupon.');
      return;
    }
    discount = coupon.discount;
    price -= discount;
  }

  if (confirm(`Buy ${service.name} for ₹${price}?`)) {
    const response = await fetch('api/client-services.php', {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({
        client_id: 123,
        service_id: service.id,
        coupon: this.couponCode
      })
    });
    
    const result = await response.json();
    if (result.success) {
      alert(`Purchase initiated. Amount: ₹${result.total_amount}. Complete payment with Razorpay.`);
      this.couponCode = '';
      this.loadData();
    }
  }
}
```

---

## 7. Finance Dashboard Metrics

### File: `public_html/api/finance.php`

**New Metrics:**

```php
// Coupon Usage Tracking
$stmt = $pdo->prepare("SELECT COUNT(*) as used, SUM(discount_amount) as savings FROM client_services WHERE discount_amount > 0");
$coupon = $stmt->fetch();
$couponUsage = $coupon['used'] ?? 0;
$couponSavings = $coupon['savings'] ?? 0;

// Referral Earnings
$stmt = $pdo->prepare("SELECT SUM(reward_earned) as earnings FROM referrals WHERE status = 'completed'");
$referralEarnings = $stmt->fetch()['earnings'] ?? 0;

// Response
echo json_encode([
    'couponUsage' => $couponUsage,      // Number of times coupons were used
    'couponSavings' => $couponSavings,  // Total discount given to clients
    'referralEarnings' => $referralEarnings  // Total referral bonus paid out
]);
```

---

## 8. Admin Operations (Future)

### Create Coupon
```php
INSERT INTO discount_coupons (code, description, discount_type, discount_percent, discount_amount, usage_limit, valid_from, valid_until, is_active)
VALUES ('SUMMER20', '20% off summer sale', 'percent', 20, 0, 100, '2024-06-01', '2024-06-30', 1);
```

### Track Referral Bonus
After referee completes first purchase:
```php
$rewardAmount = ($totalAmount * 0.10); // 10% bonus

UPDATE referrals 
SET reward_earned = :reward, status = 'completed' 
WHERE referrer_id = :referrer_id AND referee_id = :referee_id;

UPDATE clients 
SET referral_bonus = referral_bonus + :reward 
WHERE id = :referrer_id;
```

---

## 9. Testing Scenarios

### Test Case 1: Apply Coupon
1. Client views service ₹5000
2. Enters coupon "SUMMER20" (20% off)
3. System calculates: ₹5000 × 20% = ₹1000 discount
4. Final price: ₹4000
5. Click "Buy Now" → Redirects to Razorpay

### Test Case 2: Referral Link
1. Client A shares: `siruscrm.com/signup?ref=REFA7B2F`
2. Client B signs up via link
3. System creates referral record
4. Client B completes purchase of ₹10,000
5. Referral bonus = ₹1000 (10% of ₹10,000)
6. Client A sees updated earnings in referrals section

### Test Case 3: Expired Coupon
1. Coupon "OLD2023" has valid_until = 2023-12-31
2. Client tries to apply on 2024-01-15
3. System returns: "Coupon is invalid, expired, or has reached its usage limit."
4. User cannot complete purchase with expired coupon

---

## 10. Future Enhancements

- **Tiered Referral Rewards**: 10% for first referral, 15% for 5+ referrals, 20% for 10+ referrals
- **Coupon Notifications**: Send coupon codes via WhatsApp/Email to target clients
- **Referral Leaderboard**: Display top referrers in client portal
- **Bonus Points**: Accumulate points for referrals, redeem for discounts
- **Affiliate Program**: Admin dashboard to manage affiliate partners
- **Automated Bonus Payout**: Auto-credit referral bonuses to client wallet

---

## 11. Security Considerations

1. **Coupon Validation**: Always validate server-side before discount
2. **Referral Verification**: Check referral_code legitimacy before creating link
3. **Double-Spend Prevention**: Lock used_count on coupon to prevent race conditions
4. **Reward Fraud**: Verify referee completion before marking referral complete
5. **SQL Injection**: Use prepared statements (already implemented with PDO)

---

## 12. Database Migrations

Run these SQL commands to update existing database:

```sql
-- Add columns to clients table
ALTER TABLE clients ADD COLUMN password VARCHAR(255) DEFAULT NULL;
ALTER TABLE clients ADD COLUMN referral_code VARCHAR(50) DEFAULT NULL;
ALTER TABLE clients ADD COLUMN referred_by INT DEFAULT NULL;
ALTER TABLE clients ADD COLUMN referral_bonus DECIMAL(12,2) DEFAULT 0;
ALTER TABLE clients ADD FOREIGN KEY (referred_by) REFERENCES clients(id) ON DELETE SET NULL;

-- Add columns to client_services
ALTER TABLE client_services ADD COLUMN coupon_code VARCHAR(100) DEFAULT NULL;
ALTER TABLE client_services ADD COLUMN discount_amount DECIMAL(12,2) DEFAULT 0;
ALTER TABLE client_services ADD COLUMN total_amount DECIMAL(12,2) DEFAULT 0;
ALTER TABLE client_services ADD COLUMN razorpay_order_id VARCHAR(255) DEFAULT NULL;

-- Create new tables
CREATE TABLE IF NOT EXISTS discount_coupons (...);
CREATE TABLE IF NOT EXISTS referrals (...);
```

---

## Summary

This implementation provides:
- ✅ Coupon management with percent/fixed discounts
- ✅ Usage tracking and validity date ranges
- ✅ Referral program with unique codes per client
- ✅ Automatic referral record creation on signup
- ✅ 10% bonus calculation on referred client purchases
- ✅ UI integration in client portal
- ✅ Finance dashboard metrics
- ✅ Full error handling and validation

**Status**: Ready for integration and testing.