-- Local SQLCipher Database Schema
-- 1. Domain Table: Work Orders
CREATE TABLE work_orders (
    id TEXT PRIMARY KEY,
    tenant_id TEXT NOT NULL,
    asset_id TEXT NOT NULL,
    status TEXT NOT NULL, -- 'pending', 'in_progress', 'completed'
    notes TEXT,
    updated_at_utc INTEGER NOT NULL,
    version INTEGER NOT NULL DEFAULT 1
);
-- 2. Append-Only Mutation Changelog (The Outbox)
CREATE TABLE outbox_mutations (
    mutation_id TEXT PRIMARY KEY,
    entity_table TEXT NOT NULL,
    entity_id TEXT NOT NULL,
    operation_type TEXT NOT NULL, -- 'INSERT', 'UPDATE', 'DELETE'
    payload_json TEXT NOT NULL,
    created_at_utc INTEGER NOT NULL,
    client_sequence INTEGER NOT NULL,
    sync_status TEXT NOT NULL DEFAULT 'PENDING' -- 'PENDING', 'IN_FLIGHT', 'COMMITTED'
);
-- Index for instant delta extraction
CREATE INDEX idx_outbox_pending ON outbox_mutations(client_sequence) WHERE sync_status = 'PENDING';

// lib/features/work_orders/domain/work_order_notifier.dart
import 'dart:convert';
import 'package:flutter_riverpod/flutter_riverpod.dart';
import 'package:sqlite3/sqlite3.dart';
import 'package:uuid/uuid.dart';
class WorkOrderState {
  final String id;
  final String status;
  final String notes;
  final bool isPendingSync;
  const WorkOrderState({
    required this.id,
    required this.status,
    required this.notes,
    this.isPendingSync = false,
  });
}
class WorkOrderNotifier extends StateNotifier<AsyncValue<WorkOrderState>> {
  final Database _db;
  final String _workOrderId;
  WorkOrderNotifier(this._db, this._workOrderId) : super(const AsyncValue.loading()) {
    _loadFromLocalCache();
  }
  void _loadFromLocalCache() {
    final ResultSet results = _db.select(
      'SELECT id, status, notes FROM work_orders WHERE id = ? LIMIT 1;',
      [_workOrderId],
    );
    if (results.isEmpty) {
      state = AsyncValue.error('Work order not found locally', StackTrace.current);
      return;
    }
    final row = results.first;
    state = AsyncValue.data(WorkOrderState(
      id: row['id'] as String,
      status: row['status'] as String,
      notes: row['notes'] as String? ?? '',
    ));
  }
  Future<void> updateStatus({required String newStatus, required String notes}) async {
    final currentState = state.value;
    if (currentState == null) return;
    // 1. Optimistic UI update
    state = AsyncValue.data(WorkOrderState(
      id: _workOrderId,
      status: newStatus,
      notes: notes,
      isPendingSync: true,
    ));
    // 2. Atomic SQLite Transaction: Update domain record & stage mutation
    _db.execute('BEGIN TRANSACTION;');
    try {
      final int nowUtc = DateTime.now().toUtc().millisecondsSinceEpoch;
      
      // Update local domain table
      _db.execute(
        '''
        UPDATE work_orders 
        SET status = ?, notes = ?, updated_at_utc = ?, version = version + 1 
        WHERE id = ?;
        ''',
        [newStatus, notes, nowUtc, _workOrderId],
      );
      // Append to Mutation Outbox
      final String mutationId = const Uuid().v4();
      final Map<String, dynamic> deltaPayload = {
        'status': newStatus,
        'notes': notes,
        'client_timestamp': nowUtc,
      };
      _db.execute(
        '''
        INSERT INTO outbox_mutations (
          mutation_id, entity_table, entity_id, operation_type, 
          payload_json, created_at_utc, client_sequence, sync_status
        ) VALUES (
          ?, 'work_orders', ?, 'UPDATE', ?, ?, 
          (SELECT COALESCE(MAX(client_sequence), 0) + 1 FROM outbox_mutations), 'PENDING'
        );
        ''',
        [mutationId, _workOrderId, jsonEncode(deltaPayload), nowUtc],
      );
      _db.execute('COMMIT;');
    } catch (e, st) {
      _db.execute('ROLLBACK;');
      // Revert optimistic update on disk failure
      _loadFromLocalCache();
      state = AsyncValue.error(e, st);
    }
  }
}

// protos/sync_delta.proto
syntax = "proto3";
package live.knetwork.sync;
enum OperationType {
  OP_INSERT = 0;
  OP_UPDATE = 1;
  OP_DELETE = 2;
}
message EntityDelta {
  string entity_id = 1;
  string entity_table = 2;
  OperationType operation = 3;
  int64 timestamp_utc = 4;
  uint32 client_version = 5;
  bytes field_mask_payload = 6; // Compact key-value binary map
}
message SyncRequest {
  string device_id = 1;
  string tenant_id = 2;
  uint64 last_acknowledged_server_version = 3;
  repeated EntityDelta pending_mutations = 4;
}
message SyncResponse {
  uint64 new_server_version = 1;
  repeated string acknowledged_mutation_ids = 2;
  repeated EntityDelta incoming_server_deltas = 3;
}

                  Concurrent Field Mutation Race Condition
                  
   Time (UTC)       Technician A (Basement A)      Technician B (Basement B)
   10:00:00 AM      Offline: Edits notes to "V1"   Offline: Edits status "Complete"
   10:05:00 AM      Re-connects (Uploads Delta)    Still Offline...
                    Server Version Vector: A=1, B=0
   10:12:00 AM                                     Re-connects (Uploads Delta)
                                                   Server Version Vector: A=1, B=1
                                                   
   Resolution Rule: Field-Level CRDT LWW
   • Tech A touched: [notes] -> Updated at 10:00:00 AM (Accepted)
   • Tech B touched: [status] -> Updated at 10:05:00 AM (Accepted)
   • Final Reconciled Record: Merges BOTH updates without overwriting.