# 📋 Complete Referral System Migration Guide

This guide will help you set up the referral system in your database step by step.

## 🎯 What You'll Get

After running these migrations, you'll have:
- ✅ `referralCode` column in `users` table (each user gets a unique referral code)
- ✅ `referredByCode` column in `users` table (tracks who referred this user)
- ✅ `referrals` table (tracks all referral relationships)
- ✅ `referral_rewards` table (tracks rewards/commissions earned)

---

## 📝 Step-by-Step Migration

### Method 1: Using phpMyAdmin (Recommended)

#### Step 1: Open phpMyAdmin
1. Go to `http://localhost/phpmyadmin` (or your phpMyAdmin URL)
2. Select your database from the left sidebar

#### Step 2: Run Migration SQL

**Option A: Run the Complete Migration (All at Once)**

1. Click on the **SQL** tab at the top
2. Open the file: `backend/migrations/SETUP_REFERRAL_SYSTEM_SIMPLE.sql`
3. Copy **ALL** the SQL content
4. Paste it into the SQL tab
5. Click **Go** or press **Ctrl+Enter**

**Option B: Run Step by Step (If you get errors)**

Run these commands **one by one** in the SQL tab:

```sql
-- Step 1: Add referralCode column to users table
ALTER TABLE `users` 
ADD COLUMN `referralCode` VARCHAR(50) NULL UNIQUE AFTER `contactNumber`;

-- Step 2: Add referredByCode column to users table
ALTER TABLE `users` 
ADD COLUMN `referredByCode` VARCHAR(50) NULL AFTER `referralCode`;

-- Step 3: Add indexes for better performance
ALTER TABLE `users` 
ADD INDEX `idx_referral_code` (`referralCode`);

ALTER TABLE `users` 
ADD INDEX `idx_referred_by_code` (`referredByCode`);

-- Step 4: Create referrals table
CREATE TABLE IF NOT EXISTS `referrals` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `referrerId` INT NOT NULL,
  `referrerType` ENUM('customer', 'supplier') NOT NULL,
  `referredCustomerId` INT NOT NULL,
  `referralCode` VARCHAR(50) NOT NULL,
  `status` ENUM('pending', 'active', 'completed', 'cancelled') DEFAULT 'pending',
  `totalEarnings` DECIMAL(10, 2) DEFAULT 0,
  `totalOrders` INT DEFAULT 0,
  `totalOrderValue` DECIMAL(10, 2) DEFAULT 0,
  `commissionRate` DECIMAL(5, 2) DEFAULT 0,
  `firstOrderCompleted` BOOLEAN DEFAULT FALSE,
  `firstOrderRewardPaid` BOOLEAN DEFAULT FALSE,
  `createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_referrer` (`referrerId`),
  INDEX `idx_referred_customer` (`referredCustomerId`),
  INDEX `idx_referral_code` (`referralCode`),
  FOREIGN KEY (`referrerId`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`referredCustomerId`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Step 5: Create referral_rewards table
CREATE TABLE IF NOT EXISTS `referral_rewards` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `referralId` INT NOT NULL,
  `orderId` INT NULL,
  `rewardType` ENUM('first_order', 'commission', 'bonus') NOT NULL,
  `orderAmount` DECIMAL(10, 2) NOT NULL,
  `commissionRate` DECIMAL(5, 2) NOT NULL,
  `rewardAmount` DECIMAL(10, 2) NOT NULL,
  `status` ENUM('pending', 'paid', 'cancelled') DEFAULT 'pending',
  `paidAt` DATETIME NULL,
  `paidToWallet` BOOLEAN DEFAULT FALSE,
  `notes` TEXT NULL,
  `createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_referral` (`referralId`),
  INDEX `idx_order` (`orderId`),
  INDEX `idx_status` (`status`),
  FOREIGN KEY (`referralId`) REFERENCES `referrals`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`orderId`) REFERENCES `orders`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

#### Step 3: Verify Migration

Run these verification queries:

```sql
-- Check if columns exist in users table
SHOW COLUMNS FROM `users` LIKE 'referralCode';
SHOW COLUMNS FROM `users` LIKE 'referredByCode';

-- Check if tables exist
SHOW TABLES LIKE 'referrals';
SHOW TABLES LIKE 'referral_rewards';

-- Check referrals table structure
DESCRIBE `referrals`;

-- Check referral_rewards table structure
DESCRIBE `referral_rewards`;
```

You should see:
- ✅ `referralCode` column in `users` table
- ✅ `referredByCode` column in `users` table
- ✅ `referrals` table exists
- ✅ `referral_rewards` table exists

---

### Method 2: Using MySQL Command Line

```bash
# Navigate to your project directory
cd "D:\IYYO\Centalized inquiry system\app\Centralized-Inquiry-System"

# Run the migration
mysql -u your_username -p your_database_name < backend/migrations/SETUP_REFERRAL_SYSTEM_SIMPLE.sql
```

Replace:
- `your_username` with your MySQL username
- `your_database_name` with your database name

---

## ⚠️ Common Errors and Solutions

### Error: "Duplicate column name 'referralCode'"
**Solution:** The column already exists. Skip Step 1 and continue with Step 2.

### Error: "Table 'referrals' already exists"
**Solution:** The table already exists. Skip Step 4 and continue with Step 5.

### Error: "Unknown column 'referralCode' in 'field list'"
**Solution:** Run Step 1 again to add the column.

### Error: "Access denied for user 'root'@'localhost' to database 'information_schema'"
**Solution:** Use the `SETUP_REFERRAL_SYSTEM_SIMPLE.sql` file (it doesn't use INFORMATION_SCHEMA).

---

## ✅ After Migration

### 1. Restart Your Backend Server

```bash
# Stop the server (Ctrl+C)
# Then restart it
cd backend
npm run dev
```

### 2. Test the Referral System

1. **Register a new user** - They should automatically get a referral code
2. **Check referral code** - Go to customer dashboard → Referrals section
3. **Share referral link** - Copy the referral link and share it
4. **Register with referral code** - Have someone register using your referral code
5. **Check referrals** - Go back to your dashboard → Referrals section
6. **Verify referral appears** - The new user should appear in your referrals list

---

## 🔍 Verification Checklist

After migration, verify:

- [ ] `users` table has `referralCode` column
- [ ] `users` table has `referredByCode` column
- [ ] `referrals` table exists
- [ ] `referral_rewards` table exists
- [ ] Backend server starts without errors
- [ ] Users can see their referral codes
- [ ] Referral links work
- [ ] New registrations with referral codes create referral records

---

## 📚 Additional Notes

### About Referral Codes
- Each user gets a **unique** referral code (stored in `users.referralCode`)
- Multiple people can **use the same referral code** (stored in `referrals.referralCode`)
- This allows one person to refer many people

### About Referral Rewards
- **First Order Bonus:** Fixed amount (default: 500 LKR)
- **Commission:** Percentage of order value (default: 5% for customers, 3% for suppliers)
- Rewards are tracked in `referral_rewards` table
- Rewards can be paid to user's wallet

### Database Structure

```
users
├── referralCode (VARCHAR) - User's unique referral code
└── referredByCode (VARCHAR) - Code of person who referred them

referrals
├── referrerId - ID of person who made the referral
├── referredCustomerId - ID of person who was referred
├── referralCode - The code that was used
├── commissionRate - Commission percentage
└── status - pending/active/completed/cancelled

referral_rewards
├── referralId - Link to referral record
├── orderId - Link to order that generated reward
├── rewardType - first_order/commission/bonus
├── rewardAmount - Amount of reward
└── status - pending/paid/cancelled
```

---

## 🆘 Need Help?

If you encounter any errors:
1. Check the error message carefully
2. Verify which step failed
3. Check if tables/columns already exist
4. Share the error message for help

---

## 🎉 You're Done!

Once the migration is complete, your referral system is ready to use! Users can:
- Generate referral codes
- Share referral links
- Track referrals
- Earn rewards from referrals