# 📦 Database Import Guide - Updated Schema

**Generated:** October 30, 2025  
**Updated with:** Latest schema including `hourly_rate` field and demo data

---

## 🎯 Choose Your Import File

### For cPanel/Shared Hosting
✅ Use: `faceml_database_cpanel_NEW.sql`

### For VPS/Dedicated Server
✅ Use: `faceml_database_NEW.sql`

---

## 🚀 Method 1: cPanel Import (Recommended for Shared Hosting)

### Step 1: Create Database
1. Login to **cPanel**
2. Go to **MySQL Databases**
3. Create new database: `faceml` (or any name)
4. Note the full name (e.g., `cpses_12345_faceml`)
5. Create MySQL user and grant **ALL PRIVILEGES**

### Step 2: Import via phpMyAdmin
1. Open **phpMyAdmin** from cPanel
2. Select your database from left sidebar
3. Click **Import** tab
4. Choose file: `faceml_database_cpanel_NEW.sql`
5. Click **Go** button
6. Wait for success message ✓

### Step 3: Update .env File
Create `.env` file in your backend folder:

```env
# Database Configuration
DB_HOST=localhost
DB_USER=cpses_12345_user
DB_PASSWORD=your_mysql_password
DB_NAME=cpses_12345_faceml
DB_PORT=3306

# Server Configuration
PORT=3000
NODE_ENV=production

# JWT Secret (IMPORTANT: Generate a strong secret)
JWT_SECRET=your_super_secret_jwt_key_minimum_32_characters_long

# Super Admin Credentials
SUPER_ADMIN_EMAIL=admin@faceml.com
SUPER_ADMIN_PASSWORD=Admin@123

# Demo Shop Admin (for testing)
SHOP_ADMIN_EMAIL=shop@gmail.com
SHOP_ADMIN_PASSWORD=123456
```

### Step 4: Upload Backend & Install
1. Upload `backend` folder to cPanel (via File Manager or FTP)
2. SSH into your server (or use Terminal in cPanel)
3. Navigate to backend folder:
   ```bash
   cd /home/yourusername/public_html/backend
   ```
4. Install dependencies:
   ```bash
   npm install
   ```

### Step 5: Initialize Database (Hash Passwords)
```bash
node scripts/initDatabase.js
```

This will:
- ✓ Hash the super admin password
- ✓ Hash the demo shop admin password
- ✓ Create proper bcrypt hashes

### Step 6: Start Server
```bash
# For testing
node server.js

# For production (recommended)
pm2 start server.js --name faceml-backend
pm2 save
pm2 startup
```

---

## 🖥️ Method 2: VPS/Dedicated Server Import

### Step 1: Import Database
```bash
# Import the SQL file
mysql -u root -p < faceml_database_NEW.sql

# Or if you want to specify database
mysql -u root -p faceml_db < faceml_database_NEW.sql
```

### Step 2: Verify Import
```bash
mysql -u root -p
```

```sql
USE faceml_db;
SHOW TABLES;
SELECT * FROM users;
```

You should see:
- ✓ 6 tables created
- ✓ 2 users (super admin + shop admin)
- ✓ 1 shop
- ✓ 1 branch
- ✓ 5 demo staff members

### Step 3: Update .env File
```env
DB_HOST=localhost
DB_USER=root
DB_PASSWORD=your_mysql_root_password
DB_NAME=faceml_db
DB_PORT=3306
PORT=3000
NODE_ENV=production
JWT_SECRET=generate_a_strong_random_secret_here
SUPER_ADMIN_EMAIL=admin@faceml.com
SUPER_ADMIN_PASSWORD=Admin@123
SHOP_ADMIN_EMAIL=shop@gmail.com
SHOP_ADMIN_PASSWORD=123456
```

### Step 4: Install & Initialize
```bash
cd backend
npm install
node scripts/initDatabase.js
```

### Step 5: Start Server
```bash
# Development
npm start

# Production with PM2
pm2 start server.js --name faceml-backend
pm2 save
pm2 startup
```

---

## 🔐 Generate Strong JWT Secret

### Option 1: Using Node.js
```bash
node -e "console.log(require('crypto').randomBytes(32).toString('hex'))"
```

### Option 2: Using OpenSSL
```bash
openssl rand -hex 32
```

### Option 3: Online Generator
Visit: https://randomkeygen.com/

Copy the generated string to your `.env` file as `JWT_SECRET`

---

## ✅ Database Schema Overview

### Tables Created:

1. **users** - Admin accounts
   - Super Admin: `admin@faceml.com`
   - Shop Admin: `shop@gmail.com`

2. **shops** - Shop/Company info
   - Demo Shop (ID: 1)

3. **branches** - Branch locations
   - Main Branch (ID: 1)

4. **staff** - Staff members with face recognition
   - 5 demo staff included
   - **NEW:** `hourly_rate` field for salary calculations

5. **attendance** - Clock in/out records
   - Tracks daily attendance
   - **NEW:** Supports 'late' status

6. **work_sessions** - Detailed work tracking

---

## 🧪 Test Your Setup

### 1. Test Database Connection
```bash
cd backend
node -e "require('./config/database')"
```

Expected: `✓ Database connected successfully`

### 2. Test API Login

**Super Admin Login:**
```bash
curl -X POST http://your-domain.com:3000/api/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email": "admin@faceml.com", "password": "Admin@123"}'
```

**Shop Admin Login:**
```bash
curl -X POST http://your-domain.com:3000/api/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email": "shop@gmail.com", "password": "123456"}'
```

Expected: JSON response with `token` field

### 3. Test in Flutter App
1. Update `API_URL` in Flutter app
2. Try logging in with:
   - Email: `shop@gmail.com`
   - Password: `123456`

---

## 🛠️ Troubleshooting

### Error: "Database connection failed"
**Fix:**
1. Check `.env` file has correct credentials
2. Verify MySQL is running: `service mysql status`
3. Test connection: `mysql -u your_user -p`
4. Check firewall allows MySQL port 3306

### Error: "ECONNREFUSED"
**Fix:**
1. Ensure backend server is running
2. Check PORT in `.env` file
3. Verify firewall allows the port
4. For cPanel: Use Node.js Selector to start app

### Error: "Invalid credentials"
**Fix:**
1. Make sure you ran: `node scripts/initDatabase.js`
2. This script properly hashes the passwords
3. Check `.env` has correct email/password

### Error: "Cannot find module"
**Fix:**
```bash
cd backend
rm -rf node_modules package-lock.json
npm install
```

### Foreign Key Constraint Errors
**Fix:**
1. Drop all tables first
2. Re-import the SQL file
3. Tables must be created in order (users → shops → branches → staff → attendance → work_sessions)

---

## 📊 Demo Data Included

The SQL file includes:

✅ **2 Admin Accounts:**
- Super Admin: `admin@faceml.com` / `Admin@123`
- Shop Admin: `shop@gmail.com` / `123456`

✅ **1 Demo Shop:**
- Name: "Demo Shop"
- Address: "123 Demo Street"

✅ **1 Branch:**
- Name: "Main Branch"

✅ **5 Staff Members:**
1. Alice Johnson - $10/hr
2. Bob Smith - $15/hr
3. Charlie Brown - $15/hr
4. Derek Wilson - $15/hr
5. Eva Martinez - $10/hr

---

## 🔒 Security Checklist

After successful import:

- [ ] Change default super admin password
- [ ] Change demo shop admin password
- [ ] Update JWT_SECRET with strong random string
- [ ] Enable HTTPS/SSL certificate
- [ ] Restrict MySQL to localhost only
- [ ] Set up firewall rules (UFW/iptables)
- [ ] Enable fail2ban for SSH protection
- [ ] Set up automated database backups
- [ ] Never commit `.env` file to git
- [ ] Review file permissions (644 for files, 755 for directories)
- [ ] Disable directory listing in web server
- [ ] Keep Node.js and npm packages updated

---

## 📝 What's New in This Version

✅ **Added `hourly_rate` field** to staff table
- Stores hourly salary rate
- DECIMAL(10,2) format
- Used for salary calculations in reports

✅ **Added 'late' status** to attendance
- Track late arrivals
- Better attendance reporting

✅ **Improved demo data**
- 5 realistic staff members
- Proper hourly rates
- Ready for testing salary features

✅ **Better documentation**
- Clear step-by-step instructions
- Multiple import methods
- Comprehensive troubleshooting

---

## 🆘 Need Help?

If you encounter issues:

1. Check backend logs:
   ```bash
   pm2 logs faceml-backend
   ```

2. Check MySQL error logs:
   ```bash
   tail -f /var/log/mysql/error.log
   ```

3. Verify all environment variables:
   ```bash
   cat .env
   ```

4. Test database connection directly:
   ```bash
   mysql -u your_user -p faceml_db -e "SHOW TABLES;"
   ```

---

## 📞 Support

For issues specific to:
- **cPanel**: Contact your hosting provider
- **MySQL**: Check MySQL documentation
- **Node.js**: Verify version compatibility (14.x or higher)

---

**Last Updated:** October 30, 2025  
**Database Version:** 2.0 (with hourly_rate support)  
**Compatible with:** FaceML Flutter App v1.0+

