# Remote Database Time Log Sync Configuration

## Overview

This system enables automatic synchronization of time logs from the local ADMS server to a remote database server. When a device sends time log data, it's automatically inserted into both databases simultaneously.

## Architecture

```
Device (ZKTeco)
     ↓
     └─→ POST to /iclock/cdata
          ↓
     receiveRecords() method
          ↓
     TimeSyncHelper::insertTimeLog()
          ├─→ Insert to LOCAL database (finger_log)
          └─→ Insert to REMOTE database (finger_log)
          ↓
     Response "OK" to device
```

## Setup Instructions

### Step 1: Configure Environment Variables

Edit your `.env` file and add the remote database credentials:

```bash
# Local Database (your main ADMS server)
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=adms
DB_USERNAME=root
DB_PASSWORD=your_local_password

# Remote Database (where you want to sync time logs)
REMOTE_DB_HOST=123.45.67.89          # Remote server IP or hostname
REMOTE_DB_PORT=3306
REMOTE_DB_DATABASE=adms
REMOTE_DB_USERNAME=remote_user
REMOTE_DB_PASSWORD=remote_password
```

### Step 2: Create Remote Database Table

The remote database must have the same `finger_log` table structure. You have two options:

#### Option A: Run Migration on Remote Database (Recommended)

```bash
# This creates finger_log table on the remote database
php artisan migrate --database=remote_mysql
```

#### Option B: Manual Setup on Remote Server

Execute this SQL on the remote database:

```sql
CREATE TABLE finger_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    data LONGTEXT NOT NULL,
    url LONGTEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### Step 3: Test Remote Connection

```bash
# Test the remote database connection and view sync statistics
php artisan timesync:test --stats

# Output will show:
# - Connection status
# - Record counts (local vs remote)
# - Sync percentage
```

## How It Works

### 1. When Device Sends Data

When a ZKTeco device sends time log data via `POST /iclock/cdata`:

```php
// In receiveRecords() method
$syncResult = TimeSyncHelper::insertTimeLog($content, 'finger_log');

// Returns:
[
    'local' => [
        'status' => true,
        'message' => '✅ Data inserted to local database'
    ],
    'remote' => [
        'status' => true,
        'message' => '✅ Data synced to remote database'
    ]
]
```

### 2. Dual Insertion Process

The `TimeSyncHelper::insertTimeLog()` method:

1. **Inserts to LOCAL database first**
   - Uses default Laravel database connection
   - Logs success/failure

2. **Checks if remote database is configured**
   - Skips remote insertion if not configured or if host is localhost

3. **Inserts to REMOTE database**
   - Uses `remote_mysql` connection from config
   - Handles connection errors gracefully
   - Logs success/failure for monitoring

### 3. Error Handling

If remote database is unavailable:
- ✅ **Local insertion continues** - your primary system is NOT affected
- ⚠️ **Remote sync fails** - logged for troubleshooting
- Data is NOT queued for later sync (immediate sync only)

If you need guaranteed sync, consider implementing a queue-based approach (see below).

## Available Commands

### Test Remote Connection

```bash
php artisan timesync:test

# Output:
# 🔍 Testing Time Log Sync System...
# 1️⃣ Testing Remote Database Connection
# ✅ Remote database is CONNECTED
# Host: 123.45.67.89
# Database: adms
```

### View Sync Statistics

```bash
php artisan timesync:test --stats

# Output includes:
# - Local finger_log record count
# - Remote finger_log record count
# - Sync percentage
```

## Helper Methods

### Insert Time Log Data

```php
use App\Helpers\TimeSyncHelper;

$data = [
    'url' => json_encode($request->all()),
    'data' => $rawContent
];

$result = TimeSyncHelper::insertTimeLog($data, 'finger_log');

if ($result['local']['status'] && $result['remote']['status']) {
    // Both databases synced successfully
}
```

### Check if Remote Database is Configured

```php
if (TimeSyncHelper::isRemoteDatabaseConfigured()) {
    // Remote database is available for sync
}
```

### Test Remote Connection

```php
$test = TimeSyncHelper::testRemoteConnection();

echo $test['connected'] ? '✅ Connected' : '❌ Failed';
echo $test['message'];
```

### Get Sync Statistics

```php
$stats = TimeSyncHelper::getSyncStats();

echo "Local records: {$stats['local_total']}";
echo "Remote records: {$stats['remote_total']}";
```

## Logging

All sync operations are logged to `storage/logs/iclock-YYYY-MM-DD.log`:

```
[2026-06-02 10:15:45] iclock.INFO: Time log insertion result
Local: ✅ Data inserted to local database
Remote: ✅ Data synced to remote database

[2026-06-02 10:15:45] iclock.ERROR: Remote insertion exception
error: Connection refused
```

## Disabling Remote Sync

To disable remote sync without changing code, set these in `.env`:

```bash
# Keep localhost to disable remote sync
REMOTE_DB_HOST=127.0.0.1
REMOTE_DB_DATABASE=
```

Or leave them commented out in `.env`:

```bash
# REMOTE_DB_HOST=123.45.67.89
# REMOTE_DB_PORT=3306
# REMOTE_DB_DATABASE=adms
```

## Troubleshooting

### Connection Refused

```
Error: Remote database connection failed: Connection refused
```

**Causes:**
- Remote server firewall blocking port 3306
- Remote MySQL service not running
- Wrong IP address or hostname

**Solution:**
```bash
# Test from server command line
telnet 123.45.67.89 3306

# If telnet fails, check:
# 1. Remote server firewall rules
# 2. Remote MySQL is running: sudo systemctl status mysql
# 3. MySQL binds to 0.0.0.0 (not just localhost)
```

### Authentication Failed

```
Error: Access denied for user 'remote_user'@'your.server.ip'
```

**Solution:**
- Check credentials in `.env`
- On remote server, verify user has privileges:
  ```sql
  GRANT ALL ON adms.* TO 'remote_user'@'%' IDENTIFIED BY 'password';
  FLUSH PRIVILEGES;
  ```

### Table Doesn't Exist

```
Error: Table 'adms.finger_log' doesn't exist
```

**Solution:**
Run migration to create table:
```bash
php artisan migrate --database=remote_mysql
```

### Connection Works but Data Not Syncing

```
php artisan timesync:test --stats

# Shows records in local but not remote
```

**Solution:**
1. Check application logs for errors
2. Verify INSERT privileges on remote database
3. Ensure remote database table schema matches local

## Performance Considerations

### Dual Insert Performance Impact

**Scenario:** Device sends 100 time logs

- **Before (Local only):** ~50ms
- **After (Local + Remote):** ~100ms (includes network latency)

**Impact:** Minimal - typically adds 50-100ms per transaction

### Network Optimization

If remote server is far away:

1. **Use persistent connection** (already configured)
2. **Monitor network latency:**
   ```bash
   ping 123.45.67.89  # Check latency
   ```

3. **Consider async queue for batch processing** (future enhancement)

## Database Replication Alternative

For high-volume scenarios, consider native MySQL replication instead:

1. **Advantages:**
   - Automatic real-time sync
   - No application code needed
   - Better performance

2. **Disadvantages:**
   - More complex setup
   - Requires dedicated DBA

## Advanced: Queue-Based Sync

For guaranteed sync even if remote is temporarily down:

1. Enable Laravel queues
2. Queue remote insert in separate job
3. Retry on failure with backoff

**This is a future enhancement** - current implementation does immediate sync.

## Security Considerations

1. **Firewall:** Open MySQL port 3306 only to trusted IPs
2. **Credentials:** Never commit `.env` with real passwords
3. **SSL:** Consider SSL for connections over internet
   ```php
   // In config/database.php
   'remote_mysql' => [
       'options' => [
           PDO::MYSQL_ATTR_SSL_CA => '/path/to/ca.pem',
           PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
       ]
   ]
   ```

## Monitoring & Alerts

### Monitor Sync Health

```bash
# Check logs for errors
tail -f storage/logs/iclock-$(date +%Y-%m-%d).log | grep -i "remote"

# View sync stats hourly
*/60 * * * * cd /var/www/html/adms/adms-server && php artisan timesync:test --stats >> storage/logs/sync-health.log
```

### Track Sync Percentage

```php
// Add to your dashboard or API
$stats = TimeSyncHelper::getSyncStats();
$syncPercentage = ($stats['remote_total'] / $stats['local_total']) * 100;

if ($syncPercentage < 95) {
    // Alert admin
}
```

## Summary

✅ **Local database first** - primary system always works
✅ **Automatic remote sync** - no manual intervention needed
✅ **Error isolation** - remote failures don't affect local operations
✅ **Easy testing** - `php artisan timesync:test --stats`
✅ **Production ready** - fully logged and monitored

---

**Next Steps:**
1. Configure `.env` with remote database credentials
2. Run `php artisan timesync:test` to verify connection
3. Run `php artisan migrate --database=remote_mysql` to create table
4. Monitor logs and sync statistics