Offline First Architecture

// services/OfflineDatabase.ts
import AsyncStorage from '@react-native-async-storage/async-storage';
import * as SQLite from 'expo-sqlite';

export interface SyncableRecord {
  id: string;
  localId?: string;
  createdAt: Date;
  updatedAt: Date;
  deletedAt?: Date;
  syncStatus: 'pending' | 'synced' | 'conflict' | 'failed';
  version: number;
  data: Record<string, any>;
}

export interface SyncConflict {
  recordId: string;
  localVersion: SyncableRecord;
  remoteVersion: SyncableRecord;
  conflictType: 'update' | 'delete' | 'create';
  timestamp: Date;
}

class OfflineDatabase {
  private static instance: OfflineDatabase;
  private db: SQLite.WebSQLDatabase | null = null;
  private isInitialized = false;
  private syncQueue: SyncableRecord[] = [];

  static getInstance(): OfflineDatabase {
    if (!OfflineDatabase.instance) {
      OfflineDatabase.instance = new OfflineDatabase();
    }
    return OfflineDatabase.instance;
  }

  async initialize(): Promise<void> {
    if (this.isInitialized) return;

    try {
      this.db = SQLite.openDatabase('OfflineApp.db');
      
      await this.createTables();
      await this.loadSyncQueue();
      
      this.isInitialized = true;
      console.log('Offline database initialized successfully');
    } catch (error) {
      console.error('Database initialization error:', error);
      throw error;
    }
  }

  private createTables(): Promise<void> {
    return new Promise((resolve, reject) => {
      if (!this.db) {
        reject(new Error('Database not available'));
        return;
      }

      this.db.transaction(
        (tx) => {
          // Main data table
          tx.executeSql(`
            CREATE TABLE IF NOT EXISTS records (
              local_id TEXT PRIMARY KEY,
              server_id TEXT,
              table_name TEXT NOT NULL,
              data TEXT NOT NULL,
              created_at INTEGER NOT NULL,
              updated_at INTEGER NOT NULL,
              deleted_at INTEGER,
              sync_status TEXT NOT NULL DEFAULT 'pending',
              version INTEGER NOT NULL DEFAULT 1,
              conflict_data TEXT
            );
          `);

          // Sync queue table
          tx.executeSql(`
            CREATE TABLE IF NOT EXISTS sync_queue (
              id TEXT PRIMARY KEY,
              record_id TEXT NOT NULL,
              action TEXT NOT NULL,
              priority INTEGER NOT NULL DEFAULT 1,
              attempts INTEGER NOT NULL DEFAULT 0,
              created_at INTEGER NOT NULL,
              last_attempt INTEGER
            );
          `);

          // User preferences and app state
          tx.executeSql(`
            CREATE TABLE IF NOT EXISTS app_state (
              key TEXT PRIMARY KEY,
              value TEXT NOT NULL,
              updated_at INTEGER NOT NULL
            );
          `);

          // Create indexes for better performance
          tx.executeSql('CREATE INDEX IF NOT EXISTS idx_records_sync_status ON records(sync_status);');
          tx.executeSql('CREATE INDEX IF NOT EXISTS idx_records_table ON records(table_name);');
          tx.executeSql('CREATE INDEX IF NOT EXISTS idx_sync_queue_priority ON sync_queue(priority DESC);');
        },
        (error) => reject(error),
        () => resolve()
      );
    });
  }

  async create(tableName: string, data: Record<string, any>): Promise<string> {
    if (!this.db) throw new Error('Database not initialized');

    const localId = `local_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`;
    const now = Date.now();

    const record: SyncableRecord = {
      id: localId,
      localId,
      createdAt: new Date(now),
      updatedAt: new Date(now),
      syncStatus: 'pending',
      version: 1,
      data,
    };

    return new Promise((resolve, reject) => {
      this.db!.transaction(
        (tx) => {
          tx.executeSql(
            'INSERT INTO records (local_id, table_name, data, created_at, updated_at, sync_status, version) VALUES (?, ?, ?, ?, ?, ?, ?)',
            [localId, tableName, JSON.stringify(data), now, now, 'pending', 1],
            () => {
              this.addToSyncQueue(localId, 'create');
              resolve(localId);
            },
            (_, error) => reject(error)
          );
        },
        (error) => reject(error)
      );
    });
  }

  async read(tableName: string, id?: string): Promise<SyncableRecord[]> {
    if (!this.db) throw new Error('Database not initialized');

    return new Promise((resolve, reject) => {
      const sql = id 
        ? 'SELECT * FROM records WHERE table_name = ? AND (local_id = ? OR server_id = ?) AND deleted_at IS NULL'
        : 'SELECT * FROM records WHERE table_name = ? AND deleted_at IS NULL ORDER BY updated_at DESC';
      
      const params = id ? [tableName, id, id] : [tableName];

      this.db!.transaction(
        (tx) => {
          tx.executeSql(
            sql,
            params,
            (_, result) => {
              const records: SyncableRecord[] = [];
              
              for (let i = 0; i < result.rows.length; i++) {
                const row = result.rows.item(i);
                records.push({
                  id: row.server_id || row.local_id,
                  localId: row.local_id,
                  createdAt: new Date(row.created_at),
                  updatedAt: new Date(row.updated_at),
                  deletedAt: row.deleted_at ? new Date(row.deleted_at) : undefined,
                  syncStatus: row.sync_status as any,
                  version: row.version,
                  data: JSON.parse(row.data),
                });
              }
              
              resolve(records);
            },
            (_, error) => reject(error)
          );
        },
        (error) => reject(error)
      );
    });
  }

  async update(tableName: string, id: string, data: Record<string, any>): Promise<void> {
    if (!this.db) throw new Error('Database not initialized');

    const now = Date.now();

    return new Promise((resolve, reject) => {
      this.db!.transaction(
        (tx) => {
          // First, get the current record to increment version
          tx.executeSql(
            'SELECT version FROM records WHERE table_name = ? AND (local_id = ? OR server_id = ?)',
            [tableName, id, id],
            (_, result) => {
              if (result.rows.length === 0) {
                reject(new Error('Record not found'));
                return;
              }

              const currentVersion = result.rows.item(0).version;
              const newVersion = currentVersion + 1;

              tx.executeSql(
                'UPDATE records SET data = ?, updated_at = ?, sync_status = ?, version = ? WHERE table_name = ? AND (local_id = ? OR server_id = ?)',
                [JSON.stringify(data), now, 'pending', newVersion, tableName, id, id],
                () => {
                  this.addToSyncQueue(id, 'update');
                  resolve();
                },
                (_, error) => reject(error)
              );
            },
            (_, error) => reject(error)
          );
        },
        (error) => reject(error)
      );
    });
  }

  async delete(tableName: string, id: string, hardDelete: boolean = false): Promise<void> {
    if (!this.db) throw new Error('Database not initialized');

    const now = Date.now();

    return new Promise((resolve, reject) => {
      this.db!.transaction(
        (tx) => {
          if (hardDelete) {
            tx.executeSql(
              'DELETE FROM records WHERE table_name = ? AND (local_id = ? OR server_id = ?)',
              [tableName, id, id],
              () => resolve(),
              (_, error) => reject(error)
            );
          } else {
            // Soft delete - mark as deleted but keep for sync
            tx.executeSql(
              'UPDATE records SET deleted_at = ?, sync_status = ? WHERE table_name = ? AND (local_id = ? OR server_id = ?)',
              [now, 'pending', tableName, id, id],
              () => {
                this.addToSyncQueue(id, 'delete');
                resolve();
              },
              (_, error) => reject(error)
            );
          }
        },
        (error) => reject(error)
      );
    });
  }

  private async addToSyncQueue(recordId: string, action: 'create' | 'update' | 'delete', priority: number = 1): Promise<void> {
    if (!this.db) return;

    const queueId = `queue_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`;
    const now = Date.now();

    return new Promise((resolve, reject) => {
      this.db!.transaction(
        (tx) => {
          tx.executeSql(
            'INSERT INTO sync_queue (id, record_id, action, priority, attempts, created_at) VALUES (?, ?, ?, ?, ?, ?)',
            [queueId, recordId, action, priority, 0, now],
            () => resolve(),
            (_, error) => reject(error)
          );
        },
        (error) => reject(error)
      );
    });
  }

  async getSyncQueue(): Promise<Array<{
    id: string;
    recordId: string;
    action: string;
    priority: number;
    attempts: number;
    createdAt: Date;
  }>> {
    if (!this.db) throw new Error('Database not initialized');

    return new Promise((resolve, reject) => {
      this.db!.transaction(
        (tx) => {
          tx.executeSql(
            'SELECT * FROM sync_queue ORDER BY priority DESC, created_at ASC LIMIT 100',
            [],
            (_, result) => {
              const queue = [];
              
              for (let i = 0; i < result.rows.length; i++) {
                const row = result.rows.item(i);
                queue.push({
                  id: row.id,
                  recordId: row.record_id,
                  action: row.action,
                  priority: row.priority,
                  attempts: row.attempts,
                  createdAt: new Date(row.created_at),
                });
              }
              
              resolve(queue);
            },
            (_, error) => reject(error)
          );
        },
        (error) => reject(error)
      );
    });
  }

  async removeSyncQueueItem(queueId: string): Promise<void> {
    if (!this.db) return;

    return new Promise((resolve, reject) => {
      this.db!.transaction(
        (tx) => {
          tx.executeSql(
            'DELETE FROM sync_queue WHERE id = ?',
            [queueId],
            () => resolve(),
            (_, error) => reject(error)
          );
        },
        (error) => reject(error)
      );
    });
  }

  async markSyncComplete(recordId: string, serverId?: string): Promise<void> {
    if (!this.db) return;

    return new Promise((resolve, reject) => {
      this.db!.transaction(
        (tx) => {
          const sql = serverId 
            ? 'UPDATE records SET sync_status = ?, server_id = ? WHERE local_id = ?'
            : 'UPDATE records SET sync_status = ? WHERE local_id = ?';
          
          const params = serverId ? ['synced', serverId, recordId] : ['synced', recordId];

          tx.executeSql(
            sql,
            params,
            () => resolve(),
            (_, error) => reject(error)
          );
        },
        (error) => reject(error)
      );
    });
  }

  private async loadSyncQueue(): Promise<void> {
    try {
      const queue = await this.getSyncQueue();
      this.syncQueue = queue as any[];
      console.log(`Loaded ${queue.length} items in sync queue`);
    } catch (error) {
      console.error('Error loading sync queue:', error);
    }
  }

  async getStorageStats(): Promise<{
    totalRecords: number;
    pendingSync: number;
    conflictedRecords: number;
    storageSize: number;
  }> {
    if (!this.db) throw new Error('Database not initialized');

    return new Promise((resolve, reject) => {
      this.db!.transaction(
        (tx) => {
          tx.executeSql(
            'SELECT COUNT(*) as total FROM records WHERE deleted_at IS NULL',
            [],
            (_, totalResult) => {
              const totalRecords = totalResult.rows.item(0).total;

              tx.executeSql(
                'SELECT COUNT(*) as pending FROM records WHERE sync_status = "pending"',
                [],
                (_, pendingResult) => {
                  const pendingSync = pendingResult.rows.item(0).pending;

                  tx.executeSql(
                    'SELECT COUNT(*) as conflicts FROM records WHERE sync_status = "conflict"',
                    [],
                    async (_, conflictResult) => {
                      const conflictedRecords = conflictResult.rows.item(0).conflicts;
                      
                      // Estimate storage size (simplified)
                      const storageSize = totalRecords * 1000; // Rough estimate

                      resolve({
                        totalRecords,
                        pendingSync,
                        conflictedRecords,
                        storageSize,
                      });
                    },
                    (_, error) => reject(error)
                  );
                },
                (_, error) => reject(error)
              );
            },
            (_, error) => reject(error)
          );
        },
        (error) => reject(error)
      );
    });
  }
}

export default OfflineDatabase;

// services/DataSyncService.ts
import OfflineDatabase, { SyncableRecord, SyncConflict } from './OfflineDatabase';
import NetInfo from '@react-native-community/netinfo';

interface SyncOptions {
  priority?: number;
  batchSize?: number;
  retryAttempts?: number;
  conflictResolution?: 'client-wins' | 'server-wins' | 'manual' | 'merge';
}

interface SyncResult {
  success: boolean;
  synced: number;
  failed: number;
  conflicts: number;
  errors: string[];
}

class DataSyncService {
  private static instance: DataSyncService;
  private database: OfflineDatabase;
  private isSyncing = false;
  private syncCallbacks: Array<(status: string, progress: number) => void> = [];
  private apiBaseUrl: string;
  private authToken: string | null = null;

  constructor() {
    this.database = OfflineDatabase.getInstance();
    this.apiBaseUrl = 'https://your-api.com/api/v1';
    this.startNetworkMonitoring();
  }

  static getInstance(): DataSyncService {
    if (!DataSyncService.instance) {
      DataSyncService.instance = new DataSyncService();
    }
    return DataSyncService.instance;
  }

  setAuthToken(token: string): void {
    this.authToken = token;
  }

  addSyncStatusCallback(callback: (status: string, progress: number) => void): void {
    this.syncCallbacks.push(callback);
  }

  private notifySyncStatus(status: string, progress: number): void {
    this.syncCallbacks.forEach(callback => {
      try {
        callback(status, progress);
      } catch (error) {
        console.error('Sync callback error:', error);
      }
    });
  }

  private async startNetworkMonitoring(): Promise<void> {
    NetInfo.addEventListener(state => {
      if (state.isConnected && !this.isSyncing) {
        // Auto-sync when network becomes available
        this.syncAll({ priority: 1 }).catch(error => {
          console.error('Auto-sync error:', error);
        });
      }
    });
  }

  async syncAll(options: SyncOptions = {}): Promise<SyncResult> {
    if (this.isSyncing) {
      return { success: false, synced: 0, failed: 0, conflicts: 0, errors: ['Sync already in progress'] };
    }

    this.isSyncing = true;
    this.notifySyncStatus('starting', 0);

    try {
      const networkState = await NetInfo.fetch();
      if (!networkState.isConnected) {
        throw new Error('No network connection available');
      }

      const syncQueue = await this.database.getSyncQueue();
      const totalItems = syncQueue.length;

      if (totalItems === 0) {
        this.notifySyncStatus('completed', 100);
        return { success: true, synced: 0, failed: 0, conflicts: 0, errors: [] };
      }

      let synced = 0;
      let failed = 0;
      let conflicts = 0;
      const errors: string[] = [];

      // Process sync queue in batches
      const batchSize = options.batchSize || 10;
      
      for (let i = 0; i < totalItems; i += batchSize) {
        const batch = syncQueue.slice(i, i + batchSize);
        const batchResults = await Promise.allSettled(
          batch.map(item => this.syncRecord(item))
        );

        batchResults.forEach((result, index) => {
          if (result.status === 'fulfilled') {
            const { success, conflict, error } = result.value;
            if (success) {
              synced++;
            } else if (conflict) {
              conflicts++;
            } else {
              failed++;
              if (error) errors.push(error);
            }
          } else {
            failed++;
            errors.push(result.reason?.message || 'Unknown sync error');
          }

          // Remove successfully synced items from queue
          if (result.status === 'fulfilled' && result.value.success) {
            this.database.removeSyncQueueItem(batch[index].id);
          }
        });

        // Update progress
        const progress = Math.round(((i + batch.length) / totalItems) * 100);
        this.notifySyncStatus('syncing', progress);

        // Small delay between batches to avoid overwhelming the server
        await new Promise(resolve => setTimeout(resolve, 100));
      }

      this.notifySyncStatus('completed', 100);
      return { success: errors.length === 0, synced, failed, conflicts, errors };
    } catch (error) {
      this.notifySyncStatus('error', 0);
      console.error('Sync error:', error);
      return { 
        success: false, 
        synced: 0, 
        failed: 0, 
        conflicts: 0, 
        errors: [error instanceof Error ? error.message : 'Unknown error'] 
      };
    } finally {
      this.isSyncing = false;
    }
  }

  private async syncRecord(queueItem: any): Promise<{
    success: boolean;
    conflict: boolean;
    error?: string;
  }> {
    try {
      const records = await this.database.read('', queueItem.recordId);
      if (records.length === 0) {
        throw new Error(`Record ${queueItem.recordId} not found`);
      }

      const record = records[0];
      
      switch (queueItem.action) {
        case 'create':
          return await this.syncCreate(record);
        case 'update':
          return await this.syncUpdate(record);
        case 'delete':
          return await this.syncDelete(record);
        default:
          throw new Error(`Unknown sync action: ${queueItem.action}`);
      }
    } catch (error) {
      return {
        success: false,
        conflict: false,
        error: error instanceof Error ? error.message : 'Unknown error',
      };
    }
  }

  private async syncCreate(record: SyncableRecord): Promise<{
    success: boolean;
    conflict: boolean;
    error?: string;
  }> {
    try {
      const response = await fetch(`${this.apiBaseUrl}/records`, {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
          'Authorization': `Bearer ${this.authToken}`,
        },
        body: JSON.stringify({
          data: record.data,
          client_created_at: record.createdAt.toISOString(),
          client_version: record.version,
        }),
      });

      if (!response.ok) {
        throw new Error(`Server error: ${response.status}`);
      }

      const serverData = await response.json();
      
      // Update local record with server ID
      await this.database.markSyncComplete(record.localId!, serverData.id);

      return { success: true, conflict: false };
    } catch (error) {
      return {
        success: false,
        conflict: false,
        error: error instanceof Error ? error.message : 'Create sync failed',
      };
    }
  }

  private async syncUpdate(record: SyncableRecord): Promise<{
    success: boolean;
    conflict: boolean;
    error?: string;
  }> {
    try {
      // First, get the current server version
      const getResponse = await fetch(`${this.apiBaseUrl}/records/${record.id}`, {
        headers: {
          'Authorization': `Bearer ${this.authToken}`,
        },
      });

      if (!getResponse.ok) {
        throw new Error(`Server error: ${getResponse.status}`);
      }

      const serverRecord = await getResponse.json();
      
      // Check for version conflicts
      if (serverRecord.version !== record.version - 1) {
        return await this.handleConflict(record, serverRecord);
      }

      // Update on server
      const updateResponse = await fetch(`${this.apiBaseUrl}/records/${record.id}`, {
        method: 'PUT',
        headers: {
          'Content-Type': 'application/json',
          'Authorization': `Bearer ${this.authToken}`,
        },
        body: JSON.stringify({
          data: record.data,
          version: record.version,
          client_updated_at: record.updatedAt.toISOString(),
        }),
      });

      if (!updateResponse.ok) {
        throw new Error(`Server error: ${updateResponse.status}`);
      }

      await this.database.markSyncComplete(record.localId!);
      return { success: true, conflict: false };
    } catch (error) {
      return {
        success: false,
        conflict: false,
        error: error instanceof Error ? error.message : 'Update sync failed',
      };
    }
  }

  private async syncDelete(record: SyncableRecord): Promise<{
    success: boolean;
    conflict: boolean;
    error?: string;
  }> {
    try {
      const response = await fetch(`${this.apiBaseUrl}/records/${record.id}`, {
        method: 'DELETE',
        headers: {
          'Authorization': `Bearer ${this.authToken}`,
        },
      });

      if (!response.ok && response.status !== 404) {
        throw new Error(`Server error: ${response.status}`);
      }

      // Hard delete locally after successful server deletion
      await this.database.delete('', record.localId!, true);
      return { success: true, conflict: false };
    } catch (error) {
      return {
        success: false,
        conflict: false,
        error: error instanceof Error ? error.message : 'Delete sync failed',
      };
    }
  }

  private async handleConflict(localRecord: SyncableRecord, serverRecord: any): Promise<{
    success: boolean;
    conflict: boolean;
    error?: string;
  }> {
    // This is a simplified conflict resolution
    // In a real app, you'd implement more sophisticated strategies
    
    console.log('Sync conflict detected:', {
      localVersion: localRecord.version,
      serverVersion: serverRecord.version,
      recordId: localRecord.id,
    });

    // For now, mark as conflict and let user resolve
    // In a real implementation, you'd apply conflict resolution strategy
    return { success: false, conflict: true, error: 'Version conflict detected' };
  }

  async forcePullFromServer(tableName: string): Promise<SyncResult> {
    try {
      this.notifySyncStatus('pulling', 0);

      const response = await fetch(`${this.apiBaseUrl}/records?table=${tableName}`, {
        headers: {
          'Authorization': `Bearer ${this.authToken}`,
        },
      });

      if (!response.ok) {
        throw new Error(`Server error: ${response.status}`);
      }

      const serverRecords = await response.json();
      let synced = 0;
      let failed = 0;

      for (let i = 0; i < serverRecords.length; i++) {
        try {
          const serverRecord = serverRecords[i];
          
          // Check if record exists locally
          const localRecords = await this.database.read(tableName, serverRecord.id);
          
          if (localRecords.length === 0) {
            // Create new local record
            await this.database.create(tableName, serverRecord.data);
            await this.database.markSyncComplete(serverRecord.id, serverRecord.id);
          } else {
            // Update existing record if server version is newer
            const localRecord = localRecords[0];
            if (serverRecord.version > localRecord.version) {
              await this.database.update(tableName, localRecord.id, serverRecord.data);
              await this.database.markSyncComplete(localRecord.localId!, serverRecord.id);
            }
          }
          
          synced++;
        } catch (error) {
          console.error('Error syncing server record:', error);
          failed++;
        }

        // Update progress
        const progress = Math.round(((i + 1) / serverRecords.length) * 100);
        this.notifySyncStatus('pulling', progress);
      }

      this.notifySyncStatus('completed', 100);
      return { success: failed === 0, synced, failed, conflicts: 0, errors: [] };
    } catch (error) {
      this.notifySyncStatus('error', 0);
      return {
        success: false,
        synced: 0,
        failed: 0,
        conflicts: 0,
        errors: [error instanceof Error ? error.message : 'Pull sync failed'],
      };
    }
  }

  async getSyncStatus(): Promise<{
    isOnline: boolean;
    isSyncing: boolean;
    pendingItems: number;
    lastSyncTime?: Date;
  }> {
    const networkState = await NetInfo.fetch();
    const syncQueue = await this.database.getSyncQueue();
    
    return {
      isOnline: networkState.isConnected || false,
      isSyncing: this.isSyncing,
      pendingItems: syncQueue.length,
      lastSyncTime: new Date(), // Would store actual last sync time
    };
  }
}

export default DataSyncService;