buildRealtimeUpsertSql static method

Implementation

static SqlStatement buildRealtimeUpsertSql(
  List<TetherModel<dynamic>> models,
  String originalSupabaseTableName, // e.g., "users"
  Map<String, SupabaseTableInfo> tableSchemas, // Global schema map
) {
  if (models.isEmpty) {
    throw ArgumentError('Cannot build UPSERT: Model list is empty.');
  }
  final schemaKey =
      originalSupabaseTableName.contains('.')
          ? originalSupabaseTableName
          : 'public.$originalSupabaseTableName';

  final tableInfo = tableSchemas[schemaKey];
  if (tableInfo == null) {
    throw Exception(
      "Schema information for table '$schemaKey' not found. Cannot build upsert SQL.",
    );
  }

  final localTableName = tableInfo.localName;

  // Correctly get List<SupabaseColumnInfo> first
  final List<TetherColumnInfo> allColumnInfo = tableInfo.columns;

  // Then map to List<String> for local column names
  final List<String> localColumnNames =
      allColumnInfo.map((ci) => ci.localName).toList();

  if (localColumnNames.isEmpty) {
    throw ArgumentError(
      "Cannot build UPSERT: No local column names derived for table '$localTableName'.",
    );
  }

  // Determine primary keys (Supabase names)
  final pkSupabaseNames = tableInfo.primaryKeys;
  if (pkSupabaseNames.isEmpty) {
    throw ArgumentError(
      "Cannot build UPSERT: No primary keys defined for table '$schemaKey' in SupabaseTableInfo. Conflict target is required.",
    );
  }

  // Convert Supabase primary key names to local primary key names for the conflict target
  final List<String> pkLocalNames =
      pkSupabaseNames.map((supaPkName) {
        final colInfo = supaPkName;
        return colInfo.localName;
      }).toList();

  if (pkLocalNames.isEmpty) {
    throw ArgumentError(
      "Cannot build UPSERT: Could not derive local primary key names for conflict target for table '$localTableName'.",
    );
  }
  final conflictTarget = pkLocalNames.join(', ');

  // For ON CONFLICT DO UPDATE, list columns to update (all local columns except local PKs)
  final updateSetClausesList =
      localColumnNames
          .where((localColName) => !pkLocalNames.contains(localColName))
          .map((localColName) => '$localColName = excluded.$localColName')
          .toList();
  final String? updateSetClausesSql =
      updateSetClausesList.isNotEmpty
          ? updateSetClausesList.join(', ')
          : null; // Results in DO NOTHING if null/empty

  final List<Object?> allArguments = [];
  final valuePlaceholderGroup =
      '(${List.filled(localColumnNames.length, '?').join(', ')})';
  final allPlaceholders = List.filled(
    models.length,
    valuePlaceholderGroup,
  ).join(', ');

  for (final model in models) {
    final modelJson =
        model.toJson(); // Assumes toJson() keys are Supabase column names
    // Iterate based on the order of allColumnInfo to ensure argument order matches localColumnNames
    for (final colInfo in allColumnInfo) {
      var value =
          modelJson[colInfo
              .name]; // Get value using Supabase name from colInfo
      if (value is DateTime) {
        value = value.toIso8601String();
      } else if (value is Map || value is List) {
        if (value != null) {
          value = jsonEncode(value);
        }
      }
      allArguments.add(value);
    }
  }
  if (allArguments.length != localColumnNames.length * models.length) {
    throw StateError(
      "Mismatch in argument count for UPSERT. Expected ${localColumnNames.length * models.length}, got ${allArguments.length}. Check model.toJson() and SupabaseTableInfo consistency.",
    );
  }

  return SqlStatement(
    operationType: SqlOperationType.upsert,
    tableName: localTableName,
    insertColumns: localColumnNames,
    insertValuesPlaceholders: allPlaceholders,
    insertArguments: allArguments,
    upsertConflictTarget: conflictTarget,
    upsertUpdateSetClauses: updateSetClausesSql,
  );
}