# 📋 Database Files Summary

## 🆕 NEW Files Created (October 30, 2025)

### 1. **faceml_database_cpanel_NEW.sql** ⭐ RECOMMENDED FOR CPANEL
- **Purpose:** Import to cPanel/shared hosting
- **Size:** ~8KB
- **Features:**
  - ✅ No CREATE DATABASE command (create via cPanel first)
  - ✅ Updated schema with `hourly_rate` field
  - ✅ Includes demo data (2 admins, 1 shop, 1 branch, 5 staff)
  - ✅ Ready for phpMyAdmin import
  - ✅ Added 'late' status to attendance
  
### 2. **faceml_database_NEW.sql** ⭐ RECOMMENDED FOR VPS
- **Purpose:** Import to VPS/dedicated servers
- **Size:** ~8KB
- **Features:**
  - ✅ Includes CREATE DATABASE command
  - ✅ Updated schema with `hourly_rate` field
  - ✅ Includes demo data
  - ✅ Ready for command-line import
  - ✅ Added 'late' status to attendance

### 3. **IMPORT_GUIDE_NEW.md** 📖
- **Purpose:** Complete step-by-step import instructions
- **Contents:**
  - Detailed cPanel import steps
  - VPS/dedicated server import steps
  - .env configuration examples
  - Security checklist
  - Troubleshooting guide
  - Test procedures

---

## 📁 Old Files (Keep for Reference)

### faceml_database.sql
- Old version without `hourly_rate` field
- Missing demo data
- Can be archived or deleted

### faceml_database_cpanel.sql
- Old cPanel version
- Missing `hourly_rate` field
- Can be archived or deleted

---

## 🔄 Key Schema Changes

### What's NEW in the Updated Files:

#### 1. **staff table:**
```sql
-- NEW FIELD ADDED:
`hourly_rate` DECIMAL(10,2) DEFAULT 0.00
```
This field stores the hourly wage for each staff member, used for:
- Salary calculations in reports
- Payroll processing
- Cost analysis

#### 2. **attendance table:**
```sql
-- UPDATED STATUS ENUM:
`status` ENUM('present', 'absent', 'half_day', 'on_leave', 'late') DEFAULT 'present'
```
Added 'late' status for better attendance tracking

#### 3. **Demo Data Included:**
- 2 pre-configured admin accounts
- 1 demo shop
- 1 demo branch
- 5 staff members with realistic hourly rates ($10-$15/hr)

---

## 📊 Database Schema Overview

```
faceml_db/
├── users (2 records)
│   ├── Super Admin (admin@faceml.com)
│   └── Shop Admin (shop@gmail.com)
│
├── shops (1 record)
│   └── Demo Shop
│
├── branches (1 record)
│   └── Main Branch
│
├── staff (5 records)
│   ├── Alice Johnson ($10/hr)
│   ├── Bob Smith ($15/hr)
│   ├── Charlie Brown ($15/hr)
│   ├── Derek Wilson ($15/hr)
│   └── Eva Martinez ($10/hr)
│
├── attendance (0 records initially)
│   └── Will be populated when staff clock in/out
│
└── work_sessions (0 records initially)
    └── Will be populated for detailed time tracking
```

---

## 🎯 Which File Should You Use?

### Use `faceml_database_cpanel_NEW.sql` if:
- ✅ You're using shared hosting (GoDaddy, Bluehost, HostGator, etc.)
- ✅ You have cPanel access
- ✅ You'll import via phpMyAdmin
- ✅ You don't have SSH/command-line access

### Use `faceml_database_NEW.sql` if:
- ✅ You have VPS or dedicated server
- ✅ You have SSH/terminal access
- ✅ You can run MySQL commands
- ✅ You want more control over the import

---

## 🚀 Quick Start Guide

### For cPanel Users:

1. **Create Database:**
   - Login to cPanel → MySQL Databases
   - Create database: `faceml`
   - Create user and grant privileges

2. **Import:**
   - phpMyAdmin → Select database
   - Import → Choose `faceml_database_cpanel_NEW.sql`

3. **Configure:**
   - Update `.env` with database credentials
   - Run `node scripts/initDatabase.js`

4. **Test:**
   - Login: `shop@gmail.com` / `123456`

### For VPS Users:

1. **Import:**
   ```bash
   mysql -u root -p < faceml_database_NEW.sql
   ```

2. **Configure:**
   - Update `.env` file
   - Run `node scripts/initDatabase.js`

3. **Test:**
   ```bash
   node server.js
   ```

---

## 📋 File Comparison

| Feature | OLD Files | NEW Files |
|---------|-----------|-----------|
| `hourly_rate` field | ❌ | ✅ |
| 'late' status | ❌ | ✅ |
| Demo data | ❌ | ✅ |
| Complete docs | ❌ | ✅ |
| cPanel optimized | Partial | ✅ |
| Ready to use | ⚠️ | ✅ |

---

## 🔒 Security Notes

**Important:** The NEW files include placeholder passwords that MUST be changed:

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

**After import, run:**
```bash
node scripts/initDatabase.js
```

This will:
- Hash passwords properly with bcrypt
- Create secure password hashes
- Initialize admin accounts

**Then immediately:**
- Login and change all default passwords
- Update JWT_SECRET in .env
- Enable SSL/HTTPS

---

## 🧪 Testing After Import

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

### Test 2: Check Tables
```bash
mysql -u your_user -p your_database -e "SHOW TABLES;"
```
Expected: 6 tables listed

### Test 3: Check Data
```bash
mysql -u your_user -p your_database -e "SELECT * FROM users;"
```
Expected: 2 users (super admin + shop admin)

### Test 4: API Test
```bash
curl -X POST http://localhost:3000/api/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email": "shop@gmail.com", "password": "123456"}'
```
Expected: JSON with token

---

## 📝 Changelog

### Version 2.0 (October 30, 2025)
- ✅ Added `hourly_rate` field to staff table
- ✅ Added 'late' status to attendance
- ✅ Included demo data (5 staff, 2 admins, 1 shop)
- ✅ Created separate cPanel-optimized version
- ✅ Added comprehensive import guide
- ✅ Improved documentation

### Version 1.0 (October 24, 2025)
- Initial database schema
- Basic tables and relationships
- No demo data

---

## 🗂️ File Locations

```
/backend/
├── faceml_database_cpanel_NEW.sql  ⭐ NEW (for cPanel)
├── faceml_database_NEW.sql         ⭐ NEW (for VPS)
├── IMPORT_GUIDE_NEW.md             ⭐ NEW (documentation)
├── DATABASE_FILES_SUMMARY.md       ⭐ NEW (this file)
│
├── faceml_database.sql             📦 OLD (can archive)
├── faceml_database_cpanel.sql      📦 OLD (can archive)
└── CPANEL_IMPORT_GUIDE.md          📦 OLD (can archive)
```

---

## 💡 Recommendations

### For Production:
1. ✅ Use the NEW SQL files
2. ✅ Follow IMPORT_GUIDE_NEW.md
3. ✅ Run initDatabase.js script
4. ✅ Change default passwords
5. ✅ Enable SSL/HTTPS
6. ✅ Set up automated backups

### For Development:
1. ✅ Use the NEW SQL files
2. ✅ Keep demo data for testing
3. ✅ Test with shop@gmail.com account
4. ✅ Use dummyData.js to populate attendance

### For Migration:
If you already have the old database:
1. Export your current data
2. Import NEW SQL file to create updated schema
3. Migrate your data to new schema
4. Update staff records with hourly_rate values

---

## 🆘 Support & Troubleshooting

### Common Issues:

**1. "Foreign key constraint fails"**
- Solution: Import tables in correct order (NEW files handle this automatically)

**2. "Access denied for user"**
- Solution: Check .env credentials, verify MySQL user permissions

**3. "Unknown column 'hourly_rate'"**
- Solution: You're using OLD SQL file, use NEW files instead

**4. "Invalid credentials" when logging in**
- Solution: Run `node scripts/initDatabase.js` to hash passwords

### Getting Help:

1. Check `IMPORT_GUIDE_NEW.md` for detailed steps
2. Review error logs: `pm2 logs faceml-backend`
3. Test MySQL: `mysql -u user -p database -e "SHOW TABLES;"`
4. Verify .env configuration

---

## ✅ Quick Checklist

Before importing:
- [ ] Choose correct SQL file (cPanel vs VPS)
- [ ] Database user has ALL PRIVILEGES
- [ ] Backed up any existing data
- [ ] Have .env file ready

After importing:
- [ ] Verify 6 tables created
- [ ] Run `node scripts/initDatabase.js`
- [ ] Test database connection
- [ ] Test API login
- [ ] Change default passwords
- [ ] Update JWT_SECRET
- [ ] Enable HTTPS (production)

---

**Created:** October 30, 2025  
**Version:** 2.0  
**Compatible with:** FaceML v1.0+  
**Node.js:** 14.x or higher  
**MySQL:** 5.7 or higher (8.0 recommended)

