upsertSupabaseData method

Implementation

Future<void> upsertSupabaseData(
  List<Map<String, dynamic>> supabaseResponse,
) async {
  if (supabaseResponse.isEmpty) {
    debugPrint('Supabase response is empty, skipping upsert.');
    return;
  }

  try {
    // 1. Deserialize the root Supabase response into fully hydrated TModel instances.
    //    The fromJsonFactory (e.g., BookModel.fromJson) is responsible for
    //    deserializing its own fields and its nested related models.
    final List<TModel> rootModels =
        supabaseResponse.map((row) => fromJsonFactory(row)).toList();

    // Data structures for collecting models by their original table name.
    final Map<String, List<TetherModel<dynamic>>> modelsByTable = {};
    // Set to keep track of processed model instances to avoid cycles and redundant work.
    // Uses a composite key: "tableName_localId"
    final Set<String> processedModelKeys = {};

    // 2. Recursive helper to traverse the deserialized object graph.
    void collectModelsRecursively(
      TetherModel<dynamic> currentModel,
      String currentModelOriginalTableName,
    ) {
      // Ensure localId is not null before creating the key.
      // If localId is null, we might not be able to uniquely identify it for processing avoidance,
      // but we should still process its data if it's a new object.
      // For now, we rely on localId for cycle detection.
      if (currentModel.localId == null) {
        // Potentially log or handle models without IDs if they are not expected
        // print("Warning: Model of type associated with table '$currentModelOriginalTableName' has null localId.");
      }

      final String modelKey =
          '${currentModelOriginalTableName}_${currentModel.localId}';

      if (currentModel.localId != null &&
          processedModelKeys.contains(modelKey)) {
        return; // Already processed this specific model instance
      }
      if (currentModel.localId != null) {
        processedModelKeys.add(modelKey);
      }

      // Add the current model to its respective table group.
      // The key for modelsByTable is the simple original table name (e.g., "books").
      (modelsByTable[currentModelOriginalTableName] ??= []).add(currentModel);

      // Get SupabaseTableInfo for the current model's table.
      // tableSchemas uses fully qualified names (e.g., "public.books").
      final SupabaseTableInfo? tableInfo =
          tableSchemas['public.$currentModelOriginalTableName'];

      if (tableInfo == null) {
        debugPrint(
          "Warning: Table info not found for 'public.$currentModelOriginalTableName' in _collectModelsRecursively. Skipping relations for this model.",
        );
        return;
      }

      // Traverse forward relations (e.g., a Book's Author)
      for (final fk in tableInfo.foreignKeys) {
        // The fieldName is the Dart field name in the model (e.g., "author").
        // This should match the key used in the model's `data` map passed to super()
        // if it holds instances of related models.
        final fieldName = _getFieldNameFromFkColumn(
          fk.originalColumns.first,
          fk.originalForeignTableName,
        );

        // The `currentModel.data` map, as populated by the model's constructor `super(data)`,
        // should contain the actual instances of related models if the generated
        // constructors pass them.
        final dynamic relatedData = currentModel.data[fieldName];

        if (relatedData is TetherModel) {
          collectModelsRecursively(relatedData, fk.originalForeignTableName);
        }
      }
      log(
        "Processed model of type '$currentModelOriginalTableName' with localId '${currentModel.localId}'",
      );
      log('Table info reversed relations: ${tableInfo.reverseRelations}');
      // Traverse reverse relations (e.g., an Author's Books)
      // This requires SupabaseTableInfo to be augmented with reverse relation details.
      // Assuming `tableInfo.reverseRelations` exists and provides necessary info.
      // `ModelReverseRelationInfo` would be a class holding `fieldNameInThisModel` and `referencingTableOriginalName`.
      if (tableInfo.reverseRelations.isNotEmpty) {
        for (final reverseRel in tableInfo.reverseRelations) {
          // The field in the current model that holds the list of related models (e.g., 'books' in AuthorModel)
          //
          // CORRECTED: Access the nested data directly from the model's `data` map,
          // which was populated by the fromJsonFactory. Do not re-serialize to JSON.
          final dynamic relatedData =
              currentModel.data[reverseRel.fieldNameInThisModel];

          if (relatedData is List) {
            // The list items are already deserialized into TetherModel instances
            // by the fromJsonFactory.
            for (final relatedItem in relatedData) {
              if (relatedItem is TetherModel) {
                // Recursively collect models from the list
                collectModelsRecursively(
                  relatedItem,
                  reverseRel.referencingTableOriginalName,
                );
              }
            }
          } else if (relatedData is TetherModel) {
            // Handle one-to-one reverse relations if they exist
            collectModelsRecursively(
              relatedData,
              reverseRel.referencingTableOriginalName,
            );
          }
        }
      }
    }

    // 3. Start the recursive collection process for each root model.
    for (final model in rootModels) {
      // `this.tableName` is the simple name of the root table (e.g., "books")
      collectModelsRecursively(model, tableName);
    }

    log('Models to Upsert: $modelsByTable');

    // 4. Build and execute UPSERT statements in batches for each table.
    if (modelsByTable.isNotEmpty) {
      await localDb.writeTransaction((tx) async {
        for (final entry in modelsByTable.entries) {
          final originalTableName = entry.key;
          final modelsList = entry.value;
          if (modelsList.isEmpty) continue;

          // Determine a safe batch size. The max variables in SQLite is 999 by default.
          // Batch size = 999 / number of columns. Let's be conservative.
          final firstModel = modelsList.first;
          final columnCount = firstModel.toSqlite().keys.length;
          if (columnCount == 0) continue;

          final batchSize = (900 / columnCount).floor().clamp(1, 500);

          for (var i = 0; i < modelsList.length; i += batchSize) {
            final end = (i + batchSize < modelsList.length)
                ? i + batchSize
                : modelsList.length;
            final batch = modelsList.sublist(i, end);

            if (batch.isNotEmpty) {
              final statement = ClientManagerSqlUtils.buildUpsertSql(
                batch,
                originalTableName,
                tableSchemas: tableSchemas,
              );
              final finalSql = statement.build();
              await tx.execute(finalSql.sql, finalSql.arguments);
            }
          }
        }
      });
    }

    // FIX: Await a trivial command to ensure the write transaction is fully committed.
    // This forces the database isolate to process pending writes before this method returns.
    await localDb.get('SELECT 1');
  } catch (e, s) {
    debugPrint('Error in upsertSupabaseData: $e $s');
    // Depending on desired behavior, you might rethrow or handle specific exceptions.
  }
}