<?php
/**
 * Firebase to SQL Synchronization Script using REST API
 */

// Error handling
ini_set('display_errors', 1); // Set to 1 for testing, 0 for production
error_reporting(E_ALL);
ini_set('error_log', 'firebase_sync_errors.log');

// Set execution time limit (300 seconds = 5 minutes)
set_time_limit(300);

// Require the database connection
require_once 'db_connection.php';

// Log function for tracking sync process
function logSync($message, $isError = false) {
    $timestamp = date('Y-m-d H:i:s');
    $logType = $isError ? "ERROR" : "INFO";
    $logMessage = "[$timestamp] [$logType] $message" . PHP_EOL;
    file_put_contents('firebase_sync.log', $logMessage, FILE_APPEND);
    
    if ($isError) {
        error_log($message);
    }
    
    // For testing purposes, output to screen as well
    echo $logMessage;
}

// Main synchronization function
function syncFirebaseToSQL() {
    global $conn;
    
    try {
        logSync("Starting Firebase to SQL synchronization");
        
        if (!$conn) {
            throw new Exception("Database connection failed: " . mysqli_connect_error());
        }
        
        // Get last sync timestamp from the database
        $lastSyncTimestamp = getLastSyncTimestamp($conn);
        logSync("Last sync timestamp: $lastSyncTimestamp");
        
        // Get Firestore access token
        $accessToken = getFirebaseAccessToken();
        logSync("Obtained Firebase access token");
        
        // Get attendance records from Firestore via REST API
        $attendanceRecords = getAttendanceRecordsViaREST($accessToken);
        $recordCount = count($attendanceRecords);
        logSync("Found $recordCount records to sync");
        
        // Process each attendance record
        foreach ($attendanceRecords as $record) {
            processAttendanceRecord($conn, $record);
        }
        
        // Update last sync timestamp
        $currentTimestamp = date('Y-m-d H:i:s');
        updateLastSyncTimestamp($conn, $currentTimestamp);
        
        logSync("Synchronization completed successfully at $currentTimestamp");
        
    } catch (Exception $e) {
        logSync("Synchronization failed: " . $e->getMessage(), true);
    }
}

// Get Firebase access token using service account credentials
function getFirebaseAccessToken() {
    // Path to the service account credentials JSON file
    $serviceAccountPath = __DIR__ . '/firebase-credentials.json';
    
    if (!file_exists($serviceAccountPath)) {
        throw new Exception("Firebase credentials file not found at: $serviceAccountPath");
    }
    
    // Read credentials
    $credentials = json_decode(file_get_contents($serviceAccountPath), true);
    
    // Create JWT claim
    $now = time();
    $claim = [
        "iss" => $credentials['client_email'],
        "scope" => "https://www.googleapis.com/auth/datastore",
        "aud" => "https://oauth2.googleapis.com/token",
        "exp" => $now + 3600,
        "iat" => $now
    ];
    
    // Create private key
    $privateKey = $credentials['private_key'];
    
    // Sign JWT
    $header = json_encode(['typ' => 'JWT', 'alg' => 'RS256']);
    $header = str_replace(['+', '/', '='], ['-', '_', ''], base64_encode($header));
    
    $payload = json_encode($claim);
    $payload = str_replace(['+', '/', '='], ['-', '_', ''], base64_encode($payload));
    
    $signature = '';
    openssl_sign("$header.$payload", $signature, $privateKey, 'SHA256');
    $signature = str_replace(['+', '/', '='], ['-', '_', ''], base64_encode($signature));
    
    $jwt = "$header.$payload.$signature";
    
    // Get token
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, 'https://oauth2.googleapis.com/token');
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch, CURLOPT_POST, 1);
    curl_setopt($ch, CURLOPT_POSTFIELDS, "grant_type=urn:ietf:params:oauth:grant-type:jwt-bearer&assertion=$jwt");
    curl_setopt($ch, CURLOPT_HTTPHEADER, ['Content-Type: application/x-www-form-urlencoded']);
    
    $response = curl_exec($ch);
    
    if (curl_errno($ch)) {
        throw new Exception('Curl error: ' . curl_error($ch));
    }
    
    curl_close($ch);
    
    $responseData = json_decode($response, true);
    
    if (!isset($responseData['access_token'])) {
        throw new Exception('Failed to get access token: ' . json_encode($responseData));
    }
    
    return $responseData['access_token'];
}

// Get attendance records from Firestore via REST API
function getAttendanceRecordsViaREST($accessToken) {
    // Path to the service account credentials JSON file
    $serviceAccountPath = __DIR__ . '/firebase-credentials.json';
    $credentials = json_decode(file_get_contents($serviceAccountPath), true);
    $projectId = $credentials['project_id'];
    
    // Firestore REST API endpoint
    $url = "https://firestore.googleapis.com/v1/projects/$projectId/databases/(default)/documents/staff_attendance";
    
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch, CURLOPT_HTTPHEADER, [
        "Authorization: Bearer $accessToken",
        "Content-Type: application/json"
    ]);
    
    $response = curl_exec($ch);
    
    if (curl_errno($ch)) {
        throw new Exception('Curl error: ' . curl_error($ch));
    }
    
    $statusCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    
    curl_close($ch);
    
    if ($statusCode != 200) {
        throw new Exception("HTTP error: $statusCode, Response: $response");
    }
    
    $responseData = json_decode($response, true);
    
    if (!isset($responseData['documents'])) {
        return [];
    }
    
    $records = [];
    
    foreach ($responseData['documents'] as $document) {
        // Extract document ID from path
        $pathParts = explode('/', $document['name']);
        $documentId = end($pathParts);
        
        // Extract fields
        $data = [];
        if (isset($document['fields'])) {
            $data = parseFirestoreDocument($document['fields']);
        }
        
        $records[] = [
            'id' => $documentId,
            'data' => $data
        ];
    }
    
    return $records;
}

// Parse Firestore document fields from REST API response
function parseFirestoreDocument($fields) {
    $result = [];
    
    foreach ($fields as $key => $field) {
        $type = array_keys($field)[0];
        $value = $field[$type];
        
        switch ($type) {
            case 'stringValue':
            case 'integerValue':
            case 'doubleValue':
            case 'booleanValue':
                $result[$key] = $value;
                break;
                
            case 'nullValue':
                $result[$key] = null;
                break;
                
            case 'timestampValue':
                $result[$key] = $value;
                break;
                
            case 'mapValue':
                if (isset($value['fields'])) {
                    $result[$key] = parseFirestoreDocument($value['fields']);
                } else {
                    $result[$key] = [];
                }
                break;
                
            case 'arrayValue':
                if (isset($value['values'])) {
                    $arrayResult = [];
                    foreach ($value['values'] as $arrayItem) {
                        $arrayType = array_keys($arrayItem)[0];
                        $arrayValue = $arrayItem[$arrayType];
                        
                        if ($arrayType === 'mapValue' && isset($arrayValue['fields'])) {
                            $arrayResult[] = parseFirestoreDocument($arrayValue['fields']);
                        } else {
                            $arrayResult[] = $arrayValue;
                        }
                    }
                    $result[$key] = $arrayResult;
                } else {
                    $result[$key] = [];
                }
                break;
                
            default:
                $result[$key] = null;
        }
    }
    
    return $result;
}

// Get the last sync timestamp from MySQL
function getLastSyncTimestamp($conn) {
    try {
        $stmt = $conn->prepare("SELECT value FROM system_settings WHERE setting_name = 'last_firebase_sync'");
        
        if (!$stmt) {
            throw new Exception("Query preparation failed: " . $conn->error);
        }
        
        if (!$stmt->execute()) {
            throw new Exception("Query execution failed: " . $stmt->error);
        }
        
        $result = $stmt->get_result();
        
        if ($result->num_rows > 0) {
            $row = $result->fetch_assoc();
            return $row['value'];
        } else {
            // If no record exists, create it with a default timestamp of 30 mins ago
            $defaultTime = date('Y-m-d H:i:s', strtotime('-30 minutes'));
            
            $insertStmt = $conn->prepare("INSERT INTO system_settings (setting_name, value) VALUES (?, ?)");
            $insertStmt->bind_param("ss", $settingName, $defaultTime);
            
            $settingName = 'last_firebase_sync';
            
            if (!$insertStmt->execute()) {
                throw new Exception("Failed to insert default timestamp: " . $insertStmt->error);
            }
            
            $insertStmt->close();
            return $defaultTime;
        }
    } catch (Exception $e) {
        logSync("Error getting last sync timestamp: " . $e->getMessage(), true);
        // Default to 30 minutes ago if there's an error
        return date('Y-m-d H:i:s', strtotime('-30 minutes'));
    } finally {
        if (isset($stmt)) {
            $stmt->close();
        }
    }
}

// Update the last sync timestamp in MySQL
function updateLastSyncTimestamp($conn, $timestamp) {
    try {
        $stmt = $conn->prepare("UPDATE system_settings SET value = ? WHERE setting_name = 'last_firebase_sync'");
        
        if (!$stmt) {
            throw new Exception("Query preparation failed: " . $conn->error);
        }
        
        $stmt->bind_param("s", $timestamp);
        
        if (!$stmt->execute()) {
            throw new Exception("Query execution failed: " . $stmt->error);
        }
        
    } catch (Exception $e) {
        logSync("Error updating last sync timestamp: " . $e->getMessage(), true);
    } finally {
        if (isset($stmt)) {
            $stmt->close();
        }
    }
}

// Process and save attendance record to MySQL
function processAttendanceRecord($conn, $record) {
    try {
        $firebaseId = $record['id'];
        $data = $record['data'];
        
        logSync("Processing record: $firebaseId");
        
        // Extract staff ID from document ID (based on your format M15029_9 Apr 2025)
        $idParts = explode('_', $firebaseId);
        $staffId = $idParts[0]; // M15029
        
        // Extract data fields based on actual structure from screenshots
        $date = isset($data['date']) ? $data['date'] : '';
        
        // Handle staff_checkin data
        $checkinTime = null;
        $lateReason = null;
        $locationCheckin = null;
        $geopointCheckinLat = null;
        $geopointCheckinLong = null;
        $imagepathCheckin = null;
        $statusCheckin = null;
        $timestampCheckin = null;
        
        if (isset($data['staff_checkin'])) {
            $checkinTime = isset($data['staff_checkin']['time_checkin']) ? $data['staff_checkin']['time_checkin'] : null;
            $lateReason = isset($data['staff_checkin']['late_reason']) ? $data['staff_checkin']['late_reason'] : null;
            
            if (isset($data['staff_checkin']['location_checkin'])) {
                $locationCheckin = isset($data['staff_checkin']['location_checkin']['address']) ? 
                                  $data['staff_checkin']['location_checkin']['address'] : null;
                $geopointCheckinLat = isset($data['staff_checkin']['location_checkin']['latitude']) ? 
                                     $data['staff_checkin']['location_checkin']['latitude'] : null;
                $geopointCheckinLong = isset($data['staff_checkin']['location_checkin']['longitude']) ? 
                                      $data['staff_checkin']['location_checkin']['longitude'] : null;
            }
            
            $imagepathCheckin = isset($data['staff_checkin']['imagepath_checkin']) ? 
                               $data['staff_checkin']['imagepath_checkin'] : null;
            $statusCheckin = isset($data['staff_checkin']['status_checkin']) ? 
                            $data['staff_checkin']['status_checkin'] : null;
            $timestampCheckin = isset($data['staff_checkin']['timestamp']) ? 
                              $data['staff_checkin']['timestamp'] : null;
        }
        
        // Handle staff_checkout data
        $checkoutTime = null;
        $earlyReason = null; // Added early_reason field
        $locationCheckout = null;
        $geopointCheckoutLat = null;
        $geopointCheckoutLong = null;
        $imagepathCheckout = null;
        $statusCheckout = null;
        $timestampCheckout = null;
        
        if (isset($data['staff_checkout'])) {
            $checkoutTime = isset($data['staff_checkout']['time_checkout']) ? 
                           $data['staff_checkout']['time_checkout'] : null;
            
            // Add early_reason extraction
            $earlyReason = isset($data['staff_checkout']['early_reason']) ? 
                          $data['staff_checkout']['early_reason'] : null;
            
            if (isset($data['staff_checkout']['location_checkout'])) {
                $locationCheckout = isset($data['staff_checkout']['location_checkout']['address']) ? 
                                   $data['staff_checkout']['location_checkout']['address'] : null;
                $geopointCheckoutLat = isset($data['staff_checkout']['location_checkout']['latitude']) ? 
                                      $data['staff_checkout']['location_checkout']['latitude'] : null;
                $geopointCheckoutLong = isset($data['staff_checkout']['location_checkout']['longitude']) ? 
                                       $data['staff_checkout']['location_checkout']['longitude'] : null;
            }
            
            $imagepathCheckout = isset($data['staff_checkout']['imagepath_checkout']) ? 
                                $data['staff_checkout']['imagepath_checkout'] : null;
            $statusCheckout = isset($data['staff_checkout']['status_checkout']) ? 
                             $data['staff_checkout']['status_checkout'] : null;
            $timestampCheckout = isset($data['staff_checkout']['timestamp']) ? 
                               $data['staff_checkout']['timestamp'] : null;
        }
        
        // Debug info
        logSync("Staff ID: $staffId, Date: $date");
        if ($checkinTime) logSync("Check-in time: $checkinTime");
        if ($checkoutTime) logSync("Check-out time: $checkoutTime");
        if ($earlyReason) logSync("Early reason: $earlyReason"); // Log early reason if present
        
        // Check if record already exists
        $checkStmt = $conn->prepare("SELECT id FROM staff_attendance WHERE firebase_id = ?");
        $checkStmt->bind_param("s", $firebaseId);
        $checkStmt->execute();
        $checkResult = $checkStmt->get_result();
        $recordExists = $checkResult->num_rows > 0;
        $checkStmt->close();
        
        if ($recordExists) {
            // Update existing record
            $stmt = $conn->prepare(
                "UPDATE staff_attendance SET 
                 staff_id = ?, 
                 date = ?, 
                 checkin_time = ?, 
                 checkout_time = ?, 
                 late_reason = ?, 
                 early_reason = ?, 
                 location_checkin = ?, 
                 location_checkout = ?, 
                 geocoord_checkin_lat = ?,
                 geocoord_checkin_long = ?,
                 geocoord_checkout_lat = ?,
                 geocoord_checkout_long = ?,
                 status_checkin = ?, 
                 status_checkout = ?, 
                 timestamp_checkin = ?,
                 timestamp_checkout = ?,
                 imagepath_checkin = ?, 
                 imagepath_checkout = ?, 
                 last_updated = NOW() 
                 WHERE firebase_id = ?"
            );
            
            $stmt->bind_param(
                "ssssssssddddsssssss",
                $staffId,
                $date,
                $checkinTime,
                $checkoutTime,
                $lateReason,
                $earlyReason, // Added early_reason parameter
                $locationCheckin,
                $locationCheckout,
                $geopointCheckinLat,
                $geopointCheckinLong,
                $geopointCheckoutLat,
                $geopointCheckoutLong,
                $statusCheckin,
                $statusCheckout,
                $timestampCheckin,
                $timestampCheckout,
                $imagepathCheckin,
                $imagepathCheckout,
                $firebaseId
            );
            
            if (!$stmt->execute()) {
                throw new Exception("Failed to update record: " . $stmt->error);
            }
            
            logSync("Updated attendance record for $firebaseId");
            
        } else {
            // Insert new record
            $stmt = $conn->prepare(
                "INSERT INTO staff_attendance 
                (firebase_id, staff_id, date, checkin_time, checkout_time, late_reason, 
                early_reason, location_checkin, location_checkout, geocoord_checkin_lat, geocoord_checkin_long,
                geocoord_checkout_lat, geocoord_checkout_long, status_checkin, status_checkout, 
                timestamp_checkin, timestamp_checkout, imagepath_checkin, imagepath_checkout, 
                created_at, last_updated) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())"
            );
            
            $stmt->bind_param(
                "ssssssssddddsssssss",
                $firebaseId,
                $staffId,
                $date,
                $checkinTime,
                $checkoutTime,
                $lateReason,
                $earlyReason, // Added early_reason parameter
                $locationCheckin,
                $locationCheckout,
                $geopointCheckinLat,
                $geopointCheckinLong,
                $geopointCheckoutLat,
                $geopointCheckoutLong,
                $statusCheckin,
                $statusCheckout,
                $timestampCheckin,
                $timestampCheckout,
                $imagepathCheckin,
                $imagepathCheckout
            );
            
            if (!$stmt->execute()) {
                throw new Exception("Failed to insert record: " . $stmt->error);
            }
            
            logSync("Inserted new attendance record for $firebaseId");
        }
        
        if (isset($stmt)) {
            $stmt->close();
        }
        
    } catch (Exception $e) {
        logSync("Error processing record $firebaseId: " . $e->getMessage(), true);
    }
}

// Execute the sync function
syncFirebaseToSQL();
?>