# MySQL Setup Guide for MLM System

## Prerequisites
- MySQL Server 5.7+ or 8.0+ installed
- Node.js 16+ installed
- Access to MySQL with CREATE DATABASE privileges

## Step 1: Install MySQL Server

### For Ubuntu/Debian:
```bash
sudo apt update
sudo apt install mysql-server
sudo mysql_secure_installation
```

### For Windows:
Download and install MySQL from: https://dev.mysql.com/downloads/mysql/

### For macOS:
```bash
brew install mysql
brew services start mysql
```

## Step 2: Create Database and User

Login to MySQL:
```bash
mysql -u root -p
```

Run these commands:
```sql
-- Create database
CREATE DATABASE mlm_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create user (optional, for security)
CREATE USER 'mlm_user'@'localhost' IDENTIFIED BY 'your_password_here';

-- Grant permissions
GRANT ALL PRIVILEGES ON mlm_system.* TO 'mlm_user'@'localhost';
FLUSH PRIVILEGES;

-- Exit
EXIT;
```

## Step 3: Import Database Schema

From the project backend directory:
```bash
cd backend
mysql -u root -p mlm_system < database/schema.sql
```

Or if you created a specific user:
```bash
mysql -u mlm_user -p mlm_system < database/schema.sql
```

## Step 4: Configure Environment Variables

Edit `backend/.env` file:
```
PORT=5000
NODE_ENV=development

# MySQL Database Configuration
DB_HOST=localhost
DB_USER=root  # or mlm_user
DB_PASSWORD=your_mysql_password
DB_NAME=mlm_system
DB_PORT=3306

# JWT Secret
JWT_SECRET=mlm_secret_key_2024_production_change_this_key

# Admin Default Credentials
ADMIN_USERNAME=admin
ADMIN_PASSWORD=admin123
```

## Step 5: Install Dependencies

```bash
cd backend
npm install
```

## Step 6: Start the Backend

```bash
npm start
```

You should see:
```
✅ MySQL Database Connected Successfully
Server running on port 5000
```

## Step 7: Test the Connection

Open your browser or use curl:
```bash
curl http://localhost:5000
```

You should see a welcome message.

## Verify Database Setup

Login to MySQL and verify tables:
```bash
mysql -u root -p mlm_system
```

```sql
SHOW TABLES;
```

You should see:
- admin_users
- members
- customers
- products
- orders
- order_items
- transactions
- withdrawals

## Default Admin Login

After setup, you can login as admin:
- Username: `admin`
- Password: `admin123`

**IMPORTANT: Change this password immediately after first login!**

## Troubleshooting

### Connection Error
If you see "MySQL Connection Error":
1. Verify MySQL is running: `sudo service mysql status`
2. Check credentials in `.env` file
3. Verify database exists: `SHOW DATABASES;`
4. Check user permissions

### Access Denied
If you see "Access denied for user":
1. Verify username and password in `.env`
2. Grant proper permissions to user
3. Flush privileges: `FLUSH PRIVILEGES;`

### Database Not Found
If database doesn't exist:
```sql
CREATE DATABASE mlm_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```

### Tables Not Created
Re-import the schema:
```bash
mysql -u root -p mlm_system < database/schema.sql
```

## Backup and Restore

### Backup Database
```bash
mysqldump -u root -p mlm_system > backup_$(date +%Y%m%d).sql
```

### Restore Database
```bash
mysql -u root -p mlm_system < backup_20240318.sql
```

## Production Configuration

For production deployment:

1. **Use Strong Passwords**
   - Change MySQL root password
   - Use strong database user password
   - Update JWT_SECRET

2. **Create Dedicated Database User**
   ```sql
   CREATE USER 'mlm_prod'@'localhost' IDENTIFIED BY 'strong_password';
   GRANT SELECT, INSERT, UPDATE, DELETE ON mlm_system.* TO 'mlm_prod'@'localhost';
   ```

3. **Enable MySQL Security**
   - Run `mysql_secure_installation`
   - Disable remote root login
   - Remove anonymous users

4. **Regular Backups**
   - Set up automated daily backups
   - Store backups securely
   - Test restore process

5. **Monitor Performance**
   - Enable slow query log
   - Monitor database size
   - Optimize queries if needed

## Common MySQL Commands

```sql
-- Show all databases
SHOW DATABASES;

-- Use a database
USE mlm_system;

-- Show all tables
SHOW TABLES;

-- Describe table structure
DESCRIBE members;

-- Count records
SELECT COUNT(*) FROM members;

-- View admin users
SELECT * FROM admin_users;

-- View recent members
SELECT member_id, full_name, email, status FROM members ORDER BY created_at DESC LIMIT 10;

-- View all orders
SELECT order_id, user_member_id, total_amount, payment_status FROM orders ORDER BY created_at DESC;
```

## System Requirements

- MySQL: 5.7+ or 8.0+
- Disk Space: Minimum 1GB free
- RAM: Minimum 512MB for MySQL
- Connection Limit: Default 10 (configurable in database.js)

## Support

For MySQL-specific issues:
- MySQL Documentation: https://dev.mysql.com/doc/
- Community Forums: https://forums.mysql.com/

For application issues, check the main README.md file.