# Offline Attendance Synchronization Guide

## Overview

The AttendanceController has been updated to handle offline attendance data from mobile applications that use SQLite local storage. This guide explains the API endpoints, data mapping, and best practices for syncing offline attendance records.

## Offline Database Schema

The mobile app uses SQLite with the following schema:

```sql
CREATE TABLE IF NOT EXISTS attendance (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  employee_id TEXT NOT NULL,
  attendance_date TEXT NOT NULL,
  sign_in_time TEXT,
  sign_out_time TEXT,
  status TEXT DEFAULT 'present',
  late_minutes INTEGER DEFAULT 0,
  working_hours INTEGER DEFAULT 0,
  overtime_hours INTEGER DEFAULT 0,
  lunch_in_time TEXT,
  lunch_out_time TEXT,
  FOREIGN KEY (employee_id) REFERENCES employee(employee_id),
  UNIQUE(employee_id, attendance_date)
);
```

## API Endpoints

### 1. Single Record Sync
**POST** `/api/attendance/sync`

Syncs a single offline attendance record to the server.

**Request Body:**
```json
{
  "employee_id": "EMP001",
  "attendance_date": "2026-01-22",
  "sign_in_time": "08:30:00",
  "sign_out_time": "17:00:00",
  "lunch_out_time": "12:00:00",
  "lunch_in_time": "13:00:00",
  "status": "present",
  "late_minutes": 0,
  "working_hours": 8,
  "overtime_hours": 0,
  "sign_in_method": "QR Code",
  "sign_out_method": "QR Code",
  "notes": "Regular attendance",
  "local_uuid": "uuid-from-mobile-app"
}
```

**Response (201 Created):**
```json
{
  "status": "success",
  "message": "Attendance synced successfully",
  "action": "created",
  "id": 123
}
```

**Response (200 OK - Updated):**
```json
{
  "status": "success",
  "message": "Attendance record updated",
  "action": "updated"
}
```

### 2. Batch Record Sync
**POST** `/api/attendance/sync-batch`

Syncs multiple offline attendance records in a single request (max 100 records).

**Request Body:**
```json
{
  "records": [
    {
      "employee_id": "EMP001",
      "attendance_date": "2026-01-20",
      "sign_in_time": "08:30:00",
      "sign_out_time": "17:00:00",
      "status": "present",
      "late_minutes": 0,
      "working_hours": 8,
      "overtime_hours": 0
    },
    {
      "employee_id": "EMP002",
      "attendance_date": "2026-01-20",
      "sign_in_time": "09:00:00",
      "sign_out_time": "17:30:00",
      "status": "late",
      "late_minutes": 30,
      "working_hours": 8,
      "overtime_hours": 0.5
    }
  ]
}
```

**Response (200 OK - All Success):**
```json
{
  "status": "success",
  "message": "Processed 2 records: 2 created, 0 updated, 0 failed",
  "results": {
    "total": 2,
    "created": 2,
    "updated": 0,
    "failed": 0,
    "errors": []
  }
}
```

**Response (207 Multi-Status - Partial Success):**
```json
{
  "status": "partial",
  "message": "Processed 3 records: 2 created, 0 updated, 1 failed",
  "results": {
    "total": 3,
    "created": 2,
    "updated": 0,
    "failed": 1,
    "errors": [
      {
        "index": 2,
        "employee_id": "EMP999",
        "date": "2026-01-20",
        "message": "Employee not found"
      }
    ]
  }
}
```

### 3. Data Validation
**POST** `/api/attendance/validate-records`

Validates offline attendance data before syncing to identify potential issues.

**Request Body:**
```json
{
  "records": [
    {
      "employee_id": "EMP001",
      "attendance_date": "2026-01-22",
      "sign_in_time": "08:30:00",
      "status": "present"
    }
  ]
}
```

**Response:**
```json
{
  "status": "success",
  "message": "Validated 1 records: 1 valid, 0 invalid",
  "results": {
    "total": 1,
    "valid": 1,
    "invalid": 0,
    "issues": []
  }
}
```

## Field Mapping

### Employee ID Handling
- **Offline**: `employee_id` is stored as TEXT (e.g., "EMP001")
- **Server**: `employee_id` is INT UNSIGNED (e.g., 123)
- **Mapping**: The API automatically looks up employees by either numeric ID or employee_number

### Time Fields
All time fields accept either `HH:MM:SS` or `HH:MM` format.

| Offline Field | Server Field | Type | Description |
|--------------|--------------|------|-------------|
| sign_in_time | sign_in_time | TIME | Time employee signed in |
| sign_out_time | sign_out_time | TIME | Time employee signed out |
| lunch_out_time | lunch_out_time | TIME | Time employee left for lunch |
| lunch_in_time | lunch_in_time | TIME | Time employee returned from lunch |

### Numeric Fields
| Offline Field | Server Field | Conversion | Description |
|--------------|--------------|------------|-------------|
| late_minutes | late_minutes | INTEGER | Minutes employee was late |
| working_hours | working_hours | DECIMAL(5,2) | Total working hours |
| overtime_hours | overtime_hours | DECIMAL(5,2) | Overtime hours worked |

### Status Values
Valid status values: `present`, `absent`, `late`, `half-day`, `on-leave`

## Key Features

### 1. Intelligent Data Merging
The API uses intelligent data merging instead of replacing existing records. This allows partial updates throughout the day.

**Merge Behavior:**
- **Sign-in/Sign-out times**: Only updates if the field is currently empty
- **Lunch times**: Only updates if the field is currently empty
- **Status, hours, notes**: Always updates when new data is provided

**Example Scenario:**

**Morning Sync (Sign-in only):**
```json
{
  "employee_id": "EMP001",
  "attendance_date": "2026-01-22",
  "sign_in_time": "08:30:00",
  "sign_in_method": "QR Code"
}
```
Database after sync:
```
sign_in_time: "08:30:00"
sign_out_time: NULL
lunch_out_time: NULL
lunch_in_time: NULL
```

**Afternoon Sync (Sign-out added):**
```json
{
  "employee_id": "EMP001",
  "attendance_date": "2026-01-22",
  "sign_out_time": "17:00:00",
  "sign_out_method": "QR Code"
}
```
Database after merge:
```
sign_in_time: "08:30:00"     ← Kept from morning
sign_out_time: "17:00:00"    ← Added from afternoon
lunch_out_time: NULL
lunch_in_time: NULL
working_hours: 8.50          ← Auto-calculated
```

**Lunch Break Sync:**
```json
{
  "employee_id": "EMP001",
  "attendance_date": "2026-01-22",
  "lunch_out_time": "12:00:00",
  "lunch_in_time": "13:00:00"
}
```
Database after merge:
```
sign_in_time: "08:30:00"           ← Kept from morning
sign_out_time: "17:00:00"          ← Kept from afternoon
lunch_out_time: "12:00:00"         ← Added from lunch sync
lunch_in_time: "13:00:00"          ← Added from lunch sync
lunch_duration_minutes: 60         ← Auto-calculated
working_hours: 8.50                ← Kept from previous
```

### 2. Duplicate Detection
The API prevents duplicate records using:
- `local_uuid` (if provided by mobile app)
- Combination of `employee_id` + `attendance_date`

If a duplicate is found:
- With single sync: Merges new data with existing record
- With batch sync: Counts as "updated"

### 3. Automatic Calculations
The API automatically calculates derived fields when relevant data is present:

- **Lunch Duration**: Calculated when both `lunch_out_time` and `lunch_in_time` are present
- **Working Hours**: Calculated when both `sign_in_time` and `sign_out_time` are present
- **Late Status**: `is_late` flag automatically set if `late_minutes > 0`

These calculations happen both on initial insert and when merging updates.

### 4. Error Handling
- Individual record failures in batch sync don't stop the entire process
- Detailed error messages for each failed record
- HTTP status codes:
  - `200 OK`: All records processed successfully
  - `201 Created`: New record created
  - `207 Multi-Status`: Partial success in batch
  - `400 Bad Request`: Validation errors
  - `500 Internal Server Error`: Server errors

### 5. Data Integrity
- Employee validation before accepting records
- Date and time format validation
- Numeric field type checking
- Required field validation

## Merge Behavior in Practice

### Real-World Scenario: Employee's Daily Attendance

Let's walk through a typical day showing how the merge system works:

**8:30 AM - Employee signs in**
```javascript
// Mobile app syncs sign-in
fetch('/api/attendance/sync', {
  method: 'POST',
  body: JSON.stringify({
    employee_id: "EMP001",
    attendance_date: "2026-01-22",
    sign_in_time: "08:30:00",
    sign_in_method: "Facial Recognition"
  })
});
```
**Database State:**
| Field | Value |
|-------|-------|
| sign_in_time | 08:30:00 |
| sign_out_time | NULL |
| lunch_out_time | NULL |
| lunch_in_time | NULL |

---

**12:00 PM - Employee starts lunch**
```javascript
fetch('/api/attendance/sync', {
  method: 'POST',
  body: JSON.stringify({
    employee_id: "EMP001",
    attendance_date: "2026-01-22",
    lunch_out_time: "12:00:00"
  })
});
```
**Database State:**
| Field | Value |
|-------|-------|
| sign_in_time | 08:30:00 ← Preserved |
| sign_out_time | NULL |
| lunch_out_time | 12:00:00 ← Added |
| lunch_in_time | NULL |

---

**1:00 PM - Employee returns from lunch**
```javascript
fetch('/api/attendance/sync', {
  method: 'POST',
  body: JSON.stringify({
    employee_id: "EMP001",
    attendance_date: "2026-01-22",
    lunch_in_time: "13:00:00"
  })
});
```
**Database State:**
| Field | Value |
|-------|-------|
| sign_in_time | 08:30:00 ← Preserved |
| sign_out_time | NULL |
| lunch_out_time | 12:00:00 ← Preserved |
| lunch_in_time | 13:00:00 ← Added |
| lunch_duration_minutes | 60 ← Auto-calculated |

---

**5:00 PM - Employee signs out**
```javascript
fetch('/api/attendance/sync', {
  method: 'POST',
  body: JSON.stringify({
    employee_id: "EMP001",
    attendance_date: "2026-01-22",
    sign_out_time: "17:00:00",
    sign_out_method: "QR Code"
  })
});
```
**Final Database State:**
| Field | Value |
|-------|-------|
| sign_in_time | 08:30:00 ← Preserved |
| sign_out_time | 17:00:00 ← Added |
| lunch_out_time | 12:00:00 ← Preserved |
| lunch_in_time | 13:00:00 ← Preserved |
| lunch_duration_minutes | 60 ← Preserved |
| working_hours | 8.50 ← Auto-calculated |

### What Gets Merged vs. Replaced

**Mergeable Fields (Only updates if empty):**
- `sign_in_time` / `sign_in_method`
- `sign_out_time` / `sign_out_method`
- `lunch_out_time` / `lunch_out_method`
- `lunch_in_time` / `lunch_in_method`

**Always Updated Fields (Replaces existing value):**
- `status`
- `late_minutes` / `is_late`
- `working_hours`
- `overtime_hours`
- `notes`
- `local_uuid`
- `device_time`

## Best Practices

### 1. Sync Incrementally Throughout the Day
The merge system is designed for incremental syncs:
```javascript
// Good - Sync each event as it happens
await syncSignIn();      // Morning
await syncLunchOut();    // Noon
await syncLunchIn();     // Afternoon
await syncSignOut();     // Evening
```

Each sync only sends the new data, and the server intelligently merges it with existing records.

### 2. Use Batch Sync for Multiple Records
Instead of syncing records one by one:
```javascript
// Bad - Multiple requests
for (const record of offlineRecords) {
  await fetch('/api/attendance/sync', {
    method: 'POST',
    body: JSON.stringify(record)
  });
}

// Good - Single batch request
await fetch('/api/attendance/sync-batch', {
  method: 'POST',
  body: JSON.stringify({ records: offlineRecords })
});
```

### 2. Validate Before Syncing
```javascript
// Validate first
const validation = await fetch('/api/attendance/validate-records', {
  method: 'POST',
  body: JSON.stringify({ records: offlineRecords })
});

const validationResult = await validation.json();

if (validationResult.results.invalid === 0) {
  // All valid, proceed with sync
  await fetch('/api/attendance/sync-batch', {
    method: 'POST',
    body: JSON.stringify({ records: offlineRecords })
  });
} else {
  // Handle validation errors
  console.error('Validation issues:', validationResult.results.issues);
}
```

### 3. Handle Partial Failures
```javascript
const response = await fetch('/api/attendance/sync-batch', {
  method: 'POST',
  body: JSON.stringify({ records: offlineRecords })
});

const result = await response.json();

if (result.status === 'partial') {
  // Some records failed
  console.log(`${result.results.created + result.results.updated} succeeded`);
  console.log(`${result.results.failed} failed`);

  // Retry failed records or log for manual review
  result.results.errors.forEach(error => {
    console.error(`Record ${error.index} failed:`, error.message);
  });
}
```

### 4. Include Optional Fields for Better Tracking
```json
{
  "employee_id": "EMP001",
  "attendance_date": "2026-01-22",
  "sign_in_time": "08:30:00",
  "local_uuid": "uuid-generated-on-mobile",
  "device_time": "2026-01-22 08:30:00",
  "sign_in_method": "Facial Recognition",
  "notes": "Regular shift"
}
```

## Migration from Old Schema

If you're upgrading from a previous version:

1. **Add missing fields** to your offline database:
```sql
ALTER TABLE attendance ADD COLUMN lunch_out_time TEXT;
ALTER TABLE attendance ADD COLUMN lunch_in_time TEXT;
ALTER TABLE attendance ADD COLUMN late_minutes INTEGER DEFAULT 0;
ALTER TABLE attendance ADD COLUMN working_hours INTEGER DEFAULT 0;
ALTER TABLE attendance ADD COLUMN overtime_hours INTEGER DEFAULT 0;
```

2. **Update your sync logic** to use the new endpoints

3. **Test with validation endpoint** before deploying to production

## Troubleshooting

### Common Errors

**"Employee not found with ID: XXX"**
- Verify the employee exists in the server database
- Check if you're using the correct employee_id or employee_number

**"Batch size exceeds maximum limit of 100 records"**
- Split your batch into multiple requests with max 100 records each

**"Invalid status value"**
- Ensure status is one of: present, absent, late, half-day, on-leave

**"attendance_date is not a valid date"**
- Use format: YYYY-MM-DD (e.g., 2026-01-22)

**"sign_in_time has invalid format"**
- Use format: HH:MM:SS or HH:MM (e.g., 08:30:00 or 08:30)

## Example Implementation

```javascript
class OfflineAttendanceSync {
  constructor(apiBaseUrl) {
    this.apiBaseUrl = apiBaseUrl;
  }

  async syncOfflineRecords(records) {
    // Step 1: Validate
    const validation = await this.validate(records);

    if (validation.results.invalid > 0) {
      console.warn('Some records are invalid:', validation.results.issues);
    }

    // Step 2: Filter out invalid records
    const validRecords = records.filter((_, index) =>
      !validation.results.issues.some(issue => issue.index === index)
    );

    // Step 3: Batch sync in chunks of 100
    const chunks = this.chunkArray(validRecords, 100);
    const results = [];

    for (const chunk of chunks) {
      const result = await this.syncBatch(chunk);
      results.push(result);
    }

    return results;
  }

  async validate(records) {
    const response = await fetch(`${this.apiBaseUrl}/api/attendance/validate-records`, {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({ records })
    });
    return response.json();
  }

  async syncBatch(records) {
    const response = await fetch(`${this.apiBaseUrl}/api/attendance/sync-batch`, {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({ records })
    });
    return response.json();
  }

  chunkArray(array, size) {
    const chunks = [];
    for (let i = 0; i < array.length; i += size) {
      chunks.push(array.slice(i, i + size));
    }
    return chunks;
  }
}

// Usage
const syncService = new OfflineAttendanceSync('https://your-server.com');
const offlineRecords = getOfflineRecordsFromSQLite();
const results = await syncService.syncOfflineRecords(offlineRecords);
```

## Summary

The updated AttendanceController provides a robust solution for syncing offline attendance data with features including:

- ✅ Single and batch sync endpoints
- ✅ Data validation before sync
- ✅ Automatic employee lookup by ID or number
- ✅ Duplicate detection and prevention
- ✅ Automatic calculations (lunch duration, late status)
- ✅ Comprehensive error handling
- ✅ Partial failure support in batch operations
- ✅ Field type conversions (TEXT to INT, INTEGER to DECIMAL)

For questions or issues, refer to the API response messages which provide detailed error information.
