# 🔄 Database Autosync Feature

## Overview
The Database Autosync feature allows you to easily download and restore the latest database backups from Google Drive to your local PostgreSQL instance.

## Google Drive Folder
```
https://drive.google.com/drive/folders/1tI1BS24dudGTmMSQ1s_Bv8-bpQ5obv9U?usp=sharing
```

## Quick Start

### Option 1: Interactive Menu (Recommended)
```bash
node autosync-menu.js
```

This opens an interactive menu where you can:
- Sync all databases
- Sync individual databases (Tiffany or CVReyga)
- View backup status
- Open backup folder
- Exit

### Option 2: Command Line
```bash
# Sync all databases
node autosync.js all

# Sync only Tiffany (Sudirman)
node autosync.js tiffany

# Sync only CVReyga (Gambir)
node autosync.js cvreyga
```

### Option 3: API Endpoint (from Server)
```bash
# Start server first
npm start

# Then make API calls:
# Get sync status
curl http://localhost:3000/api/autosync/status

# Trigger sync
curl -X POST http://localhost:3000/api/autosync/sync \
  -H "Content-Type: application/json" \
  -d '{"database":"all"}'
```

## Setup Steps

### 1. Download Backup Files
1. Visit: https://drive.google.com/drive/folders/1tI1BS24dudGTmMSQ1s_Bv8-bpQ5obv9U
2. Download the SQL backup files:
   - `tiffany_backup.sql` (for Tiffany/Sudirman)
   - `cvreyga_backup.sql` (for CVReyga/Gambir)

### 2. Save to Local Folder
The autosync tool expects files in: `./synced_backups/`

```
db_compare/
├── autosync.js
├── autosync-menu.js
└── synced_backups/
    ├── tiffany_backup.sql
    └── cvreyga_backup.sql
```

### 3. Run Autosync
```bash
node autosync-menu.js
```

Select option "1" to sync all databases, or choose specific databases.

## Database Configuration

The autosync uses the following databases:

### Tiffany (Sudirman)
- **Pattern**: `tiffany*.sql`
- **Target Database**: `tiffany_local`
- **Local Connection**: localhost:5432 (user: root, no password)

### CVReyga (Gambir)
- **Pattern**: `cvreyga*.sql`
- **Target Database**: `cvreyga_local`
- **Local Connection**: localhost:5432 (user: root, no password)

## Features

✅ **Automatic File Detection**
- Scans `synced_backups/` folder for backup files
- Automatically selects the latest file if multiple versions exist

✅ **Smart Restoration**
- Validates backup file before restoration
- Shows progress during restore process
- Verifies data integrity after restore

✅ **Status Monitoring**
- Check backup file details (size, modified date)
- View record counts in key tables
- Monitor sync progress in real-time

✅ **Error Handling**
- Graceful error messages
- Continues on non-critical errors
- Detailed logging for debugging

## API Endpoints

### GET /api/autosync/status
Returns the status of available backups:
```json
{
  "success": true,
  "status": {
    "tiffany": {
      "name": "Tiffany (Sudirman)",
      "hasBackup": true,
      "backupFile": "tiffany_backup.sql",
      "backupSize": "1100.50 MB",
      "lastModified": "2026-05-28T11:30:00.000Z"
    },
    "cvreyga": {
      "name": "CVReyga (Gambir)",
      "hasBackup": true,
      "backupFile": "cvreyga_backup.sql",
      "backupSize": "476.25 MB",
      "lastModified": "2026-05-28T11:30:00.000Z"
    }
  }
}
```

### POST /api/autosync/sync
Trigger a sync operation:

**Request**:
```json
{
  "database": "all"  // or "tiffany" or "cvreyga"
}
```

**Response**:
```json
{
  "success": true,
  "message": "all synced successfully"
}
```

## Sync Process

```
1. Check for backup file
   ↓
2. Verify file exists and is readable
   ↓
3. Read SQL dump file
   ↓
4. Parse SQL statements
   ↓
5. Execute statements in target database
   ↓
6. Verify data was restored correctly
   ↓
7. Report results
```

## Troubleshooting

### Backup file not found
**Problem**: "No backup file found"

**Solution**:
1. Download the file from Google Drive
2. Save to `synced_backups/` folder
3. Ensure filename matches pattern (e.g., `tiffany_*.sql`)

### Connection timeout
**Problem**: "Connection failed: timeout"

**Solution**:
1. Ensure PostgreSQL is running
2. Check if `localhost:5432` is accessible
3. Verify user/password in `autosync.js`

### SQL errors during restore
**Problem**: "Restore failed: syntax error"

**Solution**:
1. Verify backup file is complete and not corrupted
2. Check PostgreSQL version compatibility
3. Review error logs for specific SQL issues

### Large database slow restore
**Problem**: Restore taking too long

**Solution**:
1. This is normal for large databases (1+ GB)
2. Progress bar shows estimation
3. Let it complete naturally
4. Consider running during off-hours

## Automation

### Scheduled Sync (Optional)
You can use system schedulers to run autosync periodically:

**Windows Task Scheduler**:
```
Program: C:\nodejs\node.exe
Arguments: C:\path\to\db_compare\autosync.js all
Run as: Administrator
Schedule: Daily at 2:00 AM
```

**Linux/macOS Cron**:
```bash
0 2 * * * cd /path/to/db_compare && node autosync.js all >> /var/log/autosync.log 2>&1
```

## Data Validation

After sync, the system automatically verifies:
- ✓ Table count matches expected
- ✓ Key tables exist (item, sale, rcv, jnl)
- ✓ Record counts are non-zero
- ✓ Sample queries execute successfully

## Performance

**Estimated Sync Times** (varies by hardware):
- **Tiffany** (~1.1 GB): 5-15 minutes
- **CVReyga** (~476 MB): 2-5 minutes

## Next Steps

After syncing:
1. Start the application: `npm start`
2. Open browser: `http://localhost:3000`
3. Verify data in both branches
4. Test search functionality
5. View analytics dashboard

## Support

For issues or questions:
1. Check Google Drive folder for latest backups
2. Review error messages in terminal
3. Verify PostgreSQL is running: `psql -U root -l`
4. Check backup file integrity
5. Review logs in `synced_backups/` folder

---

**Last Updated**: May 28, 2026