# 📋 How to Run Database Migrations

This project uses SQL migration files that need to be executed manually on your database.

## 🚀 Quick Start

### Method 1: Using phpMyAdmin (Recommended for Windows)

1. **Open phpMyAdmin**
   - Usually accessible at `http://localhost/phpmyadmin` or through your hosting control panel

2. **Select Your Database**
   - Click on your database name in the left sidebar

3. **Go to SQL Tab**
   - Click on the "SQL" tab at the top

4. **Run Migration**
   - Open the migration file (e.g., `FIX_REFERRAL_CODE_FINAL.sql`)
   - Copy the SQL content
   - Paste it into the SQL tab
   - Click "Go" or press Ctrl+Enter

5. **Verify Success**
   - You should see "Query OK" or similar success message
   - Check for any errors in red

---

### Method 2: Using MySQL Command Line

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

# Run a migration file
mysql -u your_username -p your_database_name < backend/migrations/FIX_REFERRAL_CODE_FINAL.sql
```

**Note:** Replace:
- `your_username` with your MySQL username
- `your_database_name` with your database name
- You'll be prompted for your password

---

### Method 3: Using MySQL Workbench

1. Open MySQL Workbench
2. Connect to your database
3. Open a new SQL tab
4. Open the migration file
5. Execute the SQL (Ctrl+Enter)

---

## 🔧 Current Migration to Run

### Fix Referral Code Unique Constraint

**File:** `backend/migrations/FIX_REFERRAL_CODE_FINAL.sql`

**What it does:**
- Removes the UNIQUE constraint from `referralCode` in the `referrals` table
- Allows multiple people to use the same referral code

**SQL to run:**
```sql
-- Step 1: Drop the unique index
ALTER TABLE `referrals` DROP INDEX `referralCode_3`;

-- Step 2: Create a non-unique index for performance
CREATE INDEX `idx_referral_code` ON `referrals`(`referralCode`);

-- Step 3: Verify it worked
SHOW INDEX FROM `referrals` WHERE Column_name = 'referralCode';
```

**Note:** If `referralCode_3` doesn't exist, check what unique indexes exist first:
```sql
SHOW INDEX FROM `referrals` WHERE Column_name = 'referralCode';
```

Then drop the one that shows `Non_unique = 0` (unique index).

---

## 📝 Step-by-Step: Fix Referral Code Issue

### Step 1: Check Current Indexes

Run this in phpMyAdmin SQL tab:
```sql
SHOW INDEX FROM `referrals` WHERE Column_name = 'referralCode';
```

This shows all indexes on `referralCode`. Look for ones with `Non_unique = 0` (these are unique).

### Step 2: Drop All Unique Indexes

Based on your earlier error, you have indexes like `referralCode_3`, `referralCode_4`, `referralCode_5`, etc.

Run this to drop them all:
```sql
-- Drop all unique indexes (replace with actual index names from Step 1)
ALTER TABLE `referrals` DROP INDEX `referralCode_3`;
ALTER TABLE `referrals` DROP INDEX `referralCode_4`;
ALTER TABLE `referrals` DROP INDEX `referralCode_5`;
-- ... continue for all unique indexes
```

### Step 3: Create Non-Unique Index

```sql
CREATE INDEX `idx_referral_code` ON `referrals`(`referralCode`);
```

### Step 4: Verify

```sql
SHOW INDEX FROM `referrals` WHERE Column_name = 'referralCode';
```

You should see:
- `idx_referral_code` with `Non_unique = 1` ✅
- No other unique indexes ✅

---

## 🔍 Other Important Migrations

### 1. Admin Logs Table
**File:** `backend/migrations/create_admin_logs_table.sql`
**When:** If admin logs feature isn't working

### 2. Supplier Rates
**File:** `backend/migrations/add_supplier_rates.sql`
**When:** If per-supplier commission/cashback rates aren't working

### 3. Referral Tables
**File:** `backend/migrations/create_referral_tables.sql`
**When:** If referral system isn't working

---

## ⚠️ Important Notes

1. **Always Backup First!**
   ```sql
   -- In phpMyAdmin: Export tab → Export method: Quick → Go
   -- Or use command line:
   mysqldump -u username -p database_name > backup.sql
   ```

2. **Run Migrations One at a Time**
   - Don't run multiple migrations at once
   - Check for errors after each one

3. **Check for Existing Columns/Tables**
   - Some migrations check if columns exist
   - If you get "column already exists" error, that's OK - skip that migration

4. **Test After Migration**
   - Try the feature that required the migration
   - Check server logs for any errors

---

## 🐛 Troubleshooting

### Error: "Unknown index"
**Solution:** The index doesn't exist. Check what indexes exist first with `SHOW INDEX`.

### Error: "Duplicate key name"
**Solution:** The index already exists. You can skip creating it or drop it first.

### Error: "Table doesn't exist"
**Solution:** Run the table creation migration first (e.g., `create_referral_tables.sql`).

---

## ✅ Verification Queries

After running migrations, verify they worked:

```sql
-- Check referral indexes
SHOW INDEX FROM `referrals` WHERE Column_name = 'referralCode';

-- Check if admin_logs table exists
SHOW TABLES LIKE 'admin_logs';

-- Check if supplier rate columns exist
SHOW COLUMNS FROM suppliers LIKE 'commissionRate';
```

---

## 📚 Migration Files Reference

- `FIX_REFERRAL_CODE_FINAL.sql` - Fix referral code unique constraint
- `create_admin_logs_table.sql` - Create admin logs table
- `add_supplier_rates.sql` - Add per-supplier commission/cashback rates
- `create_referral_tables.sql` - Create referral system tables
- `add_referral_columns_to_users_simple.sql` - Add referral columns to users table