upsertSupabaseData method

Implementation

Future<void> upsertSupabaseData(
  List<Map<String, dynamic>> supabaseResponse,
) async {
  if (supabaseResponse.isEmpty) {
    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) {
        log(
          "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 UPSERT SQL statements for each table that has collected models.
    final List<SqlStatement> upsertStatements = [];
    modelsByTable.forEach((originalTableName, modelsList) {
      if (modelsList.isNotEmpty) {
        // Pass the tableSchemas map to the updated buildUpsertSql method.
        upsertStatements.add(
          ClientManagerSqlUtils.buildUpsertSql(
            modelsList,
            originalTableName,
            tableSchemas: tableSchemas,
          ),
        );
      }
    });

    // 5. Execute all UPSERT statements in a single transaction.
    if (upsertStatements.isNotEmpty) {
      await localDb.writeTransaction((tx) async {
        for (final statement in upsertStatements) {
          final finalSql =
              statement.build(); // Get the FinalSqlStatement object
          // Pass both the SQL string and the arguments to tx.execute()
          await tx.execute(finalSql.sql, finalSql.arguments);
        }
      });
    }
  } catch (e, s) {
    log('Error in upsertSupabaseData: $e $s');
    // Depending on desired behavior, you might rethrow or handle specific exceptions.
  }
}