Local Write → SQLite → Sync Queue → Background Worker → Supabase
                              ↑____________↓ (retry on failure)

CREATE TABLE sync_queue (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  table_name TEXT NOT NULL,
  record_id TEXT NOT NULL,
  operation TEXT NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
  data TEXT, -- JSON string for INSERT/UPDATE
  retry_count INTEGER DEFAULT 0,
  error_message TEXT,
  created_at INTEGER NOT NULL,
  processed_at INTEGER
);
 
CREATE INDEX idx_sync_queue_created ON sync_queue(created_at);
CREATE INDEX idx_sync_queue_processed ON sync_queue(processed_at) WHERE processed_at IS NULL;

interface SyncQueueItem {
  id?: number;
  table_name: string;
  record_id: string;
  operation: 'INSERT' | 'UPDATE' | 'DELETE';
  data?: string;
  retry_count: number;
  created_at: number;
}
async function enqueueSync(
  db: SQLiteDatabase,
  table: string,
  recordId: string,
  operation: 'INSERT' | 'UPDATE' | 'DELETE',
  data?: object,
): Promise<void> {
  await db.runAsync(
    `INSERT INTO sync_queue (table_name, record_id, operation, data, retry_count, created_at)
     VALUES (?, ?, ?, ?, 0, ?)`,
    table,
    recordId,
    operation,
    data ? JSON.stringify(data) : null,
    Date.now(),
  );
}

async function processSyncQueue(db: SQLiteDatabase, supabase: SupabaseClient): Promise<void> {
  const pending = await db.getAllAsync<SyncQueueItem>(
    `SELECT * FROM sync_queue 
     WHERE processed_at IS NULL AND retry_count < 5
     ORDER BY created_at ASC
     LIMIT 50`,
  );
  // Process DELETEs first to avoid FK conflicts
  const deletes = pending.filter((p) => p.operation === 'DELETE');
  const others = pending.filter((p) => p.operation !== 'DELETE');
  for (const item of [...deletes, ...others]) {
    try {
      await processQueueItem(db, supabase, item);
    } catch (error) {
      await markFailed(db, item.id!, error.message);
    }
  }
}

async function processQueueItem(
  db: SQLiteDatabase,
  supabase: SupabaseClient,
  item: SyncQueueItem,
): Promise<void> {
  const { error } = await supabase.from(item.table_name).upsert(
    {
      id: item.record_id,
      ...(item.data ? JSON.parse(item.data) : {}),
      updated_at: new Date().toISOString(),
    },
    { onConflict: 'id' },
  );
  if (error) throw error;
  // Mark as processed
  await db.runAsync('UPDATE sync_queue SET processed_at = ? WHERE id = ?', Date.now(), item.id);
}

export function SyncProvider({ children }: { children: React.ReactNode }) {
  const { db } = useDatabase();
  const { supabase } = useSupabase();
  const netInfo = useNetInfo();
  useEffect(() => {
    if (!db || !supabase || !netInfo.isConnected) return;
    // Sync on connection restore
    const interval = setInterval(() => {
      processSyncQueue(db, supabase);
    }, 30000); // Every 30 seconds
    return () => clearInterval(interval);
  }, [db, supabase, netInfo.isConnected]);
  return children;
}

function useCreateJournal() {
  const queryClient = useQueryClient();
  const { db } = useDatabase();
  return useMutation({
    mutationFn: async (entry: JournalEntry) => {
      // 1. Save locally
      await db.runAsync(
        'INSERT INTO journal (id, encrypted_body, created_at) VALUES (?, ?, ?)',
        entry.id,
        await encryptContent(entry.content),
        entry.created_at,
      );
      // 2. Queue for sync
      await enqueueSync(db, 'journal', entry.id, 'INSERT', entry);
      return entry;
    },
    onSuccess: () => {
      queryClient.invalidateQueries({ queryKey: ['journal'] });
    },
  });
}

async function resolveConflict(local: JournalEntry, remote: JournalEntry): Promise<JournalEntry> {
  const localTime = new Date(local.updated_at).getTime();
  const remoteTime = new Date(remote.updated_at).getTime();
  return remoteTime > localTime ? remote : local;
}

import * as BackgroundFetch from 'expo-background-fetch';
import * as TaskManager from 'expo-task-manager';
const SYNC_TASK = 'background-sync';
TaskManager.defineTask(SYNC_TASK, async () => {
  const db = await openDatabase();
  const supabase = createClient();
  try {
    await processSyncQueue(db, supabase);
    return BackgroundFetch.BackgroundFetchResult.NewData;
  } catch {
    return BackgroundFetch.BackgroundFetchResult.Failed;
  }
});
async function registerBackgroundSync() {
  await BackgroundFetch.registerTaskAsync(SYNC_TASK, {
    minimumInterval: 15 * 60, // 15 minutes
    stopOnTerminate: false,
    startOnBoot: true,
  });
}

async function markFailed(db: SQLiteDatabase, queueId: number, error: string): Promise<void> {
  await db.runAsync(
    `UPDATE sync_queue 
     SET retry_count = retry_count + 1,
         error_message = ?,
         created_at = ? -- Delay retry
     WHERE id = ?`,
    error,
    Date.now() + Math.pow(2, retry_count) * 60000, // Exponential backoff
    queueId,
  );
}