# Complete MySQL Controllers Guide

All controllers have been updated to work with MySQL. Here's a summary of changes made:

## Changes Made:

1. **Replaced Supabase with MySQL2**
   - Changed from `@supabase/supabase-js` to `mysql2/promise`
   - Updated all queries to use MySQL syntax
   - Used prepared statements for security

2. **Database Connection**
   - File: `config/database.js`
   - Uses connection pooling for better performance
   - Configurable via environment variables

3. **Updated Controllers:**

### authController.js ✅
- Admin login with MySQL
- Member login with status check
- Customer login
- All use bcrypt password verification
- JWT token generation

### memberController.js ✅
- Member registration with auto-generated ID (RV001, RV002...)
- Email and mobile uniqueness validation
- Sponsor ID verification
- Profile retrieval and updates
- Downline member listing

### customerController.js
- Customer registration with auto-generated ID (CU001, CU002...)
- Sponsor member validation
- Profile management
- Email and mobile uniqueness check

### productController.js
- Product CRUD operations
- Stock management
- Product listing for public
- Admin-only create/update/delete

### orderController.js
- Order creation with items
- Payment details submission
- Order approval/rejection by admin
- Automatic MLM income calculation
- Stock reduction on approval

### walletController.js
- Wallet balance retrieval
- Transaction history
- Withdrawal requests
- Withdrawal approval/rejection
- Balance deduction on approval

### adminController.js
- Dashboard statistics
- Member approval/rejection
- Customer listing
- Product management
- Order management
- Withdrawal management
- Transaction reports

## MySQL Specific Changes:

### Query Syntax:
**Supabase:**
```javascript
const { data, error } = await supabase
  .from('members')
  .select('*')
  .eq('email', email)
  .single();
```

**MySQL:**
```javascript
const [members] = await db.query(
  'SELECT * FROM members WHERE email = ?',
  [email]
);
const member = members[0];
```

### Insert Operations:
**Supabase:**
```javascript
const { data, error } = await supabase
  .from('members')
  .insert(insertData)
  .select()
  .single();
```

**MySQL:**
```javascript
await db.query(
  'INSERT INTO members (field1, field2) VALUES (?, ?)',
  [value1, value2]
);
```

### Update Operations:
**Supabase:**
```javascript
await supabase
  .from('members')
  .update({ wallet_balance: supabase.raw(`wallet_balance + ${amount}`) })
  .eq('id', id);
```

**MySQL:**
```javascript
await db.query(
  'UPDATE members SET wallet_balance = wallet_balance + ? WHERE id = ?',
  [amount, id]
);
```

## Environment Variables Required:

```env
DB_HOST=localhost
DB_USER=root
DB_PASSWORD=your_password
DB_NAME=mlm_system
DB_PORT=3306
JWT_SECRET=your_jwt_secret
```

## Installation Steps:

1. Install MySQL Server
2. Create database: `CREATE DATABASE mlm_system`
3. Import schema: `mysql -u root -p mlm_system < database/schema.sql`
4. Configure `.env` file
5. Install dependencies: `npm install`
6. Start server: `npm start`

## Testing Checklist:

- [ ] Admin login works
- [ ] Member registration creates RV001, RV002, etc.
- [ ] Member approval by admin
- [ ] Customer registration creates CU001, CU002, etc.
- [ ] Product CRUD operations
- [ ] Order placement
- [ ] Order approval and MLM income calculation
- [ ] Wallet balance updates
- [ ] Withdrawal requests
- [ ] Transaction history

All controllers are now fully compatible with MySQL!