# Test Database Setup - Post-ERG

Guide for deploying the test database to production server.

---

## 🎯 Quick Deploy

```bash
just deploy-db
```

This automatically:
1. ✅ Checks remote DB doesn't exist (safety check)
2. ✅ Uploads `storage/test.db` to the server
3. ✅ Sets correct permissions (660, www-data:xamxam)

---

## 🔧 Prerequisites (One-Time Setup)

### 1. Install PHP SQLite Extension

```bash
ssh xamxam
sudo apt update
sudo apt install php8.4-sqlite3
sudo systemctl restart php8.4-fpm
```

### 2. Verify Installation

```bash
ssh xamxam
php -m | grep sqlite3
# Should output: pdo_sqlite, sqlite3
```

---

## 🧪 Complete Testing Workflow

### 1. Create Test Data Locally

```bash
# Create empty test database from schema
just init-db

# Or create with sample fixtures
just fixtures
```

### 2. Deploy Test Database

```bash
just deploy-db
```

### 3. Test the Site

Visit: https://xamxam.erg.be/

### 4. Check What Database is Being Used

```bash
ssh xamxam
php -r "require_once '/var/www/xamxam/src/Database.php'; echo 'Using: ' . Database::getInstance()->getDatabasePath() . PHP_EOL;"
```

### 5. Switch Back to Production

```bash
ssh xamxam
rm /var/www/xamxam/storage/test.db
```

---

## 🔒 Permissions Explained

```
/var/www/xamxam/storage/
drwxrwxr-x  www-data xamxam  # 775 - group writable

/var/www/xamxam/storage/test.db
-rw-rw----  www-data xamxam  # 660 - owner/group read/write
```

---

## 🐛 Troubleshooting

### "could not find driver"

```bash
ssh xamxam
sudo apt install php8.4-sqlite3
sudo systemctl restart php8.4-fpm
```

### "unable to open database file"

```bash
ssh xamxam
chown www-data:xamxam /var/www/xamxam/storage/test.db
chmod 660 /var/www/xamxam/storage/test.db
chmod 775 /var/www/xamxam/storage/
```

### "attempt to write a readonly database"

```bash
ssh xamxam
chmod 775 /var/www/xamxam/storage/
rm -f /var/www/xamxam/storage/test.db-*
```

---

## ⚠️ Important Notes

### Production Safety

`just deploy` **excludes all `.db` files** by default. Only `just deploy-db` uploads the test database.

### Backup Production Database

```bash
ssh xamxam
cp /var/www/xamxam/storage/posterg.db /var/www/xamxam/storage/posterg.db.backup.$(date +%Y%m%d)
```

---

## 📚 Related Commands

| Command | Description |
|---------|-------------|
| `just init-db` | Create empty test database |
| `just fixtures` | Create test database with sample data |
| `just deploy-db` | Deploy test database to server |

---

## ✅ Deployment Checklist

After running `just deploy-db`, verify:

- [ ] Database file exists: `ssh xamxam "ls -la /var/www/xamxam/storage/test.db"`
- [ ] Correct permissions: `-rw-rw---- www-data xamxam`
- [ ] Site loads: Visit https://xamxam.erg.be/
- [ ] No errors in logs: `ssh xamxam "tail /var/log/nginx/xamxam_error.log"`
