buildUpsertSql<T> static method

Implementation

static SqlStatement buildUpsertSql<T>(
  List<TetherModel<T>> models,
  String originalSupabaseTableName, {
  required Map<String, SupabaseTableInfo> tableSchemas,
}) {
  if (models.isEmpty) {
    throw ArgumentError(
      'Cannot build UPSERT statement from empty model list.',
    );
  }

  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;
  final firstModelMap = models.first.toSqlite();
  if (firstModelMap.isEmpty) {
    throw ArgumentError('Cannot build UPSERT: First model has no data.');
  }

  // --- START of new logic to determine conflict target ---
  String conflictTarget;

  // 1. Look for multi-column unique constraints first.
  final multiColumnUniqueConstraints = tableInfo.uniqueConstraints
      .where((constraint) => constraint.columns.length > 1)
      .toList();

  String? selectedConstraint;
  if (multiColumnUniqueConstraints.isNotEmpty) {
    // Find the first multi-column constraint where all columns are present in the model data
    for (final constraint in multiColumnUniqueConstraints) {
      // The constraint columns are Supabase names, we need to check against local names in the model map
      final constraintLocalNames =
          constraint.columns.map((supaName) => tableInfo.columns.firstWhere((c) => c.name == supaName).localName).toList();
      if (constraintLocalNames.every((localName) => firstModelMap.containsKey(localName))) {
        selectedConstraint = constraintLocalNames.join(', ');
        break;
      }
    }
  }

  // 2. If a suitable multi-column constraint is found, use it. Otherwise, fall back to the primary key.
  if (selectedConstraint != null) {
    conflictTarget = selectedConstraint;
  } else {
    // Fallback to primary key (original behavior)
    final pkInfos = tableInfo.primaryKeys;
    if (pkInfos.isEmpty) {
      throw ArgumentError(
        "Cannot build UPSERT: No primary keys defined for table '$schemaKey' and no suitable multi-column unique constraint found. Conflict target is required.",
      );
    }
    final pkLocalNames = pkInfos.map((pk) => pk.localName).toList();
    conflictTarget = pkLocalNames.join(', ');
  }
  // --- END of new logic ---

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

  final updateColumns =
      columns.where((key) => !conflictTarget.split(', ').contains(key)).toList();
  final updateSetClauses =
      updateColumns.map((key) => '$key = excluded.$key').join(', ');

  for (final model in models) {
    final map = model.toSqlite();
    if (map.length != columnCount ||
        map.keys.join(',') != columns.join(',')) {
      throw ArgumentError(
        'Inconsistent model structure detected for bulk UPSERT.',
      );
    }
    allArguments.addAll(map.values);
  }

  return SqlStatement(
    operationType: SqlOperationType.upsert,
    tableName: localTableName,
    insertColumns: columns,
    insertValuesPlaceholders: allPlaceholders,
    insertArguments: allArguments,
    upsertConflictTarget: conflictTarget,
    upsertUpdateSetClauses:
        updateSetClauses.isNotEmpty ? updateSetClauses : null,
  );
}